A URL shortener service that uses ATProto to allow self hosting and ensuring the user owns their data
willdot.net
1package database
2
3import (
4 "database/sql"
5 "fmt"
6 "log/slog"
7
8 atshorter "tangled.sh/willdot.net/at-shorter-url"
9)
10
11func createURLsTable(db *sql.DB) error {
12 createURLsTableSQL := `CREATE TABLE IF NOT EXISTS urls (
13 "id" TEXT NOT NULL PRIMARY KEY,
14 "url" TEXT NOT NULL,
15 "did" TEXT NOT NULL,
16 "createdAt" integer
17 );`
18
19 slog.Info("Create urls table...")
20 statement, err := db.Prepare(createURLsTableSQL)
21 if err != nil {
22 return fmt.Errorf("prepare DB statement to create urls table: %w", err)
23 }
24 _, err = statement.Exec()
25 if err != nil {
26 return fmt.Errorf("exec sql statement to create urls table: %w", err)
27 }
28 slog.Info("status urls created")
29
30 return nil
31}
32
33func (d *DB) CreateURL(id, url, did string, createdAt int64) error {
34 sql := `INSERT INTO urls (id, url, did, createdAt) VALUES (?, ?, ?, ?) ON CONFLICT(id) DO NOTHING;`
35 _, err := d.db.Exec(sql, id, url, did, createdAt)
36 if err != nil {
37 // TODO: catch already exists
38 return fmt.Errorf("exec insert url: %w", err)
39 }
40
41 return nil
42}
43
44func (d *DB) GetURLs(did string) ([]atshorter.ShortURL, error) {
45 sql := "SELECT id, url, did FROM urls WHERE did = ?;"
46 rows, err := d.db.Query(sql, did)
47 if err != nil {
48 return nil, fmt.Errorf("run query to get URLS': %w", err)
49 }
50 defer rows.Close()
51
52 var results []atshorter.ShortURL
53 for rows.Next() {
54 var shortURL atshorter.ShortURL
55 if err := rows.Scan(&shortURL.ID, &shortURL.URL, &shortURL.Did); err != nil {
56 return nil, fmt.Errorf("scan row: %w", err)
57 }
58
59 results = append(results, shortURL)
60 }
61 return results, nil
62}
63
64func (d *DB) GetURLByID(id string) (atshorter.ShortURL, error) {
65 sql := "SELECT id, url, did FROM urls WHERE id = ?;"
66 rows, err := d.db.Query(sql, id)
67 if err != nil {
68 return atshorter.ShortURL{}, fmt.Errorf("run query to get URL by id': %w", err)
69 }
70 defer rows.Close()
71
72 var result atshorter.ShortURL
73 for rows.Next() {
74 if err := rows.Scan(&result.ID, &result.URL, &result.Did); err != nil {
75 return atshorter.ShortURL{}, fmt.Errorf("scan row: %w", err)
76 }
77 return result, nil
78 }
79 return atshorter.ShortURL{}, atshorter.ErrorNotFound
80}
81
82func (s *DB) DeleteURL(id, did string) error {
83 sql := "DELETE FROM urls WHERE id = ? AND did = ?;"
84 _, err := s.db.Exec(sql, id, did)
85 if err != nil {
86 return fmt.Errorf("exec delete URL by id and DID: %w", err)
87 }
88 return nil
89}