1package sqlite
2
3import (
4 "context"
5 "database/sql"
6 "testing"
7
8 _ "github.com/bnema/purego-sqlite/driver"
9 "github.com/pressly/goose/v3"
10)
11
12func openMigrationTestDB(t *testing.T) *sql.DB {
13 t.Helper()
14 db, err := sql.Open("sqlite3", ":memory:")
15 if err != nil {
16 t.Fatalf("open sqlite: %v", err)
17 }
18 db.SetMaxOpenConns(1)
19 t.Cleanup(func() { _ = db.Close() })
20 return db
21}
22
23func runMigrationsThroughFavoritesSchema(t *testing.T, db *sql.DB) {
24 t.Helper()
25 goose.SetBaseFS(embedMigrations)
26 if err := goose.SetDialect("sqlite3"); err != nil {
27 t.Fatalf("set dialect: %v", err)
28 }
29 goose.SetLogger(goose.NopLogger())
30 if err := goose.UpTo(db, "migrations", 10); err != nil {
31 t.Fatalf("migrate through favorites schema: %v", err)
32 }
33}
34
35func runAllMigrations(t *testing.T, db *sql.DB) {
36 t.Helper()
37 if err := RunMigrations(context.Background(), db); err != nil {
38 t.Fatalf("run migrations: %v", err)
39 }
40}
41
42func countRows(t *testing.T, db *sql.DB, query string, args ...any) int {
43 t.Helper()
44 var count int
45 if err := db.QueryRow(query, args...).Scan(&count); err != nil {
46 t.Fatalf("count rows %q: %v", query, err)
47 }
48 return count
49}
50
51func TestFavoritesTagsFirstMigrationFolderPathToTag(t *testing.T) {
52 db := openMigrationTestDB(t)
53 runMigrationsThroughFavoritesSchema(t, db)
54
55 mustExec(t, db, `INSERT INTO favorite_folders(id, name, parent_id) VALUES (1, 'Research', NULL), (2, 'Browser Engines', 1)`)
56 mustExec(t, db, `INSERT INTO favorites(id, url, title, folder_id) VALUES (1, 'https://webkit.org', 'WebKit', 2)`)
57
58 runAllMigrations(t, db)
59
60 var tagID int64
61 if err := db.QueryRow(`SELECT id FROM favorite_tags WHERE name = 'research-browser-engines'`).Scan(&tagID); err != nil {
62 t.Fatalf("expected folder path tag: %v", err)
63 }
64 if got := countRows(t, db, `SELECT COUNT(*) FROM favorite_tag_assignments WHERE favorite_id = 1 AND tag_id = ?`, tagID); got != 1 {
65 t.Fatalf("expected assignment to folder path tag, got %d", got)
66 }
67}
68
69func TestFavoritesTagsFirstMigrationFolderTagCollisionSuffix(t *testing.T) {
70 db := openMigrationTestDB(t)
71 runMigrationsThroughFavoritesSchema(t, db)
72
73 mustExec(t, db, `INSERT INTO favorite_tags(id, name, color) VALUES (1, 'dev-go', '#111111')`)
74 mustExec(t, db, `INSERT INTO favorite_folders(id, name, parent_id) VALUES (7, 'Dev', NULL), (8, 'Go', 7)`)
75 mustExec(t, db, `INSERT INTO favorites(id, url, title, folder_id) VALUES (1, 'https://go.dev', 'Go', 8)`)
76
77 runAllMigrations(t, db)
78
79 var tagID int64
80 if err := db.QueryRow(`SELECT id FROM favorite_tags WHERE name = 'dev-go-folder-8'`).Scan(&tagID); err != nil {
81 t.Fatalf("expected suffixed collision tag: %v", err)
82 }
83 if got := countRows(t, db, `SELECT COUNT(*) FROM favorite_tag_assignments WHERE favorite_id = 1 AND tag_id = ?`, tagID); got != 1 {
84 t.Fatalf("expected assignment to suffixed tag, got %d", got)
85 }
86}
87
88func TestFavoritesTagsFirstMigrationGeneratedSuffixAvoidsExistingTag(t *testing.T) {
89 db := openMigrationTestDB(t)
90 runMigrationsThroughFavoritesSchema(t, db)
91
92 mustExec(t, db, `INSERT INTO favorite_tags(id, name, color) VALUES (1, 'foo', '#111111'), (2, 'foo-folder-3', '#222222')`)
93 mustExec(t, db, `INSERT INTO favorite_folders(id, name, parent_id) VALUES (3, 'Foo', NULL)`)
94 mustExec(t, db, `INSERT INTO favorites(id, url, title, folder_id) VALUES (1, 'https://foo.example', 'Foo', 3)`)
95
96 runAllMigrations(t, db)
97
98 var tagID int64
99 if err := db.QueryRow(`SELECT id FROM favorite_tags WHERE name = 'foo-folder-3-2'`).Scan(&tagID); err != nil {
100 t.Fatalf("expected second-order suffixed collision tag: %v", err)
101 }
102 if got := countRows(t, db, `SELECT COUNT(*) FROM favorite_tag_assignments WHERE favorite_id = 1 AND tag_id = ?`, tagID); got != 1 {
103 t.Fatalf("expected assignment to second-order suffixed tag, got %d", got)
104 }
105}
106
107func TestFavoritesTagsFirstMigrationGeneratedSuffixAvoidsFolderNaturalSlug(t *testing.T) {
108 db := openMigrationTestDB(t)
109 runMigrationsThroughFavoritesSchema(t, db)
110
111 mustExec(t, db, `INSERT INTO favorite_tags(id, name, color) VALUES (1, 'foo', '#111111')`)
112 mustExec(t, db, `INSERT INTO favorite_folders(id, name, parent_id) VALUES (3, 'Foo', NULL), (4, 'foo-folder-3', NULL)`)
113 mustExec(t, db, `INSERT INTO favorites(id, url, title, folder_id) VALUES (1, 'https://foo.example', 'Foo', 3), (2, 'https://folder.example', 'Folder', 4)`)
114
115 runAllMigrations(t, db)
116
117 var folder3TagID int64
118 if err := db.QueryRow(`SELECT id FROM favorite_tags WHERE name = 'foo-folder-3-2'`).Scan(&folder3TagID); err != nil {
119 t.Fatalf("expected generated suffix to avoid folder natural slug: %v", err)
120 }
121 if got := countRows(t, db, `SELECT COUNT(*) FROM favorite_tag_assignments WHERE favorite_id = 1 AND tag_id = ?`, folder3TagID); got != 1 {
122 t.Fatalf("expected assignment to second-order suffixed tag, got %d", got)
123 }
124
125 var folder4TagID int64
126 if err := db.QueryRow(`SELECT id FROM favorite_tags WHERE name = 'foo-folder-3'`).Scan(&folder4TagID); err != nil {
127 t.Fatalf("expected natural folder slug tag: %v", err)
128 }
129 if got := countRows(t, db, `SELECT COUNT(*) FROM favorite_tag_assignments WHERE favorite_id = 2 AND tag_id = ?`, folder4TagID); got != 1 {
130 t.Fatalf("expected assignment to natural slug tag, got %d", got)
131 }
132}
133
134func TestFavoritesTagsFirstMigrationMergesFoldedDuplicateTags(t *testing.T) {
135 db := openMigrationTestDB(t)
136 runMigrationsThroughFavoritesSchema(t, db)
137
138 mustExec(t, db, `INSERT INTO favorite_tags(id, name, color) VALUES (1, ' Dev-Go ', '#111111'), (2, 'dev-go', '#222222')`)
139 mustExec(t, db, `INSERT INTO favorites(id, url, title) VALUES (1, 'https://one.example', 'One'), (2, 'https://two.example', 'Two')`)
140 mustExec(t, db, `INSERT INTO favorite_tag_assignments(favorite_id, tag_id) VALUES (1, 1), (2, 2)`)
141
142 runAllMigrations(t, db)
143
144 if got := countRows(t, db, `SELECT COUNT(*) FROM favorite_tags WHERE lower(trim(name)) = 'dev-go'`); got != 1 {
145 t.Fatalf("expected one folded dev-go tag, got %d", got)
146 }
147 if got := countRows(t, db, `SELECT COUNT(*) FROM favorite_tag_assignments WHERE tag_id = 1`); got != 2 {
148 t.Fatalf("expected assignments moved to canonical tag 1, got %d", got)
149 }
150 mustExec(t, db, `INSERT INTO favorite_tags(name, color) VALUES ('another', '#333333')`)
151 if _, err := db.Exec(`INSERT INTO favorite_tags(name, color) VALUES (' DEV-GO ', '#444444')`); err == nil {
152 t.Fatal("expected folded unique index to reject duplicate tag")
153 }
154}
155
156func mustExec(t *testing.T, db *sql.DB, query string) {
157 t.Helper()
158 if _, err := db.Exec(query); err != nil {
159 t.Fatalf("exec %s: %v", query, err)
160 }
161}