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 "originHost" TEXT NOT NULL,
17 "createdAt" integer
18 );`
19
20 slog.Info("Create urls table...")
21 statement, err := db.Prepare(createURLsTableSQL)
22 if err != nil {
23 return fmt.Errorf("prepare DB statement to create urls table: %w", err)
24 }
25 _, err = statement.Exec()
26 if err != nil {
27 return fmt.Errorf("exec sql statement to create urls table: %w", err)
28 }
29 slog.Info("status urls created")
30
31 return nil
32}
33
34func (d *DB) CreateURL(id, url, did, originHost string, createdAt int64) error {
35 sql := `INSERT INTO urls (id, url, did, originHost, createdAt) VALUES (?, ?, ?, ?, ?) ON CONFLICT(id) DO NOTHING;`
36 _, err := d.db.Exec(sql, id, url, did, originHost, createdAt)
37 if err != nil {
38 // TODO: catch already exists
39 return fmt.Errorf("exec insert url: %w", err)
40 }
41
42 return nil
43}
44
45func (d *DB) GetURLs(did string) ([]atshorter.ShortURL, error) {
46 sql := "SELECT id, url, did, originHost FROM urls WHERE did = ?;"
47 rows, err := d.db.Query(sql, did)
48 if err != nil {
49 return nil, fmt.Errorf("run query to get URLS': %w", err)
50 }
51 defer rows.Close()
52
53 var results []atshorter.ShortURL
54 for rows.Next() {
55 var shortURL atshorter.ShortURL
56 if err := rows.Scan(&shortURL.ID, &shortURL.URL, &shortURL.Did, &shortURL.OriginHost); err != nil {
57 return nil, fmt.Errorf("scan row: %w", err)
58 }
59
60 results = append(results, shortURL)
61 }
62 return results, nil
63}
64
65func (d *DB) GetURLByID(id string) (atshorter.ShortURL, error) {
66 sql := "SELECT id, url, did, originHost FROM urls WHERE id = ?;"
67 rows, err := d.db.Query(sql, id)
68 if err != nil {
69 return atshorter.ShortURL{}, fmt.Errorf("run query to get URL by id': %w", err)
70 }
71 defer rows.Close()
72
73 var result atshorter.ShortURL
74 for rows.Next() {
75 if err := rows.Scan(&result.ID, &result.URL, &result.Did, &result.OriginHost); err != nil {
76 return atshorter.ShortURL{}, fmt.Errorf("scan row: %w", err)
77 }
78 return result, nil
79 }
80 return atshorter.ShortURL{}, atshorter.ErrorNotFound
81}
82
83func (s *DB) DeleteURL(id, did string) error {
84 sql := "DELETE FROM urls WHERE id = ? AND did = ?;"
85 _, err := s.db.Exec(sql, id, did)
86 if err != nil {
87 return fmt.Errorf("exec delete URL by id and DID: %w", err)
88 }
89 return nil
90}