Coverage for downtify/db/migrations/schema/library_b.py: 100%
2 statements
« prev ^ index » next coverage.py v7.17.4, created at 2026-11-21 22:18 +0000
« prev ^ index » next coverage.py v7.17.4, created at 2026-11-21 22:18 +0000
1"""PostgreSQL DDL for the library database tables (group B).
3Mirrors the final SQLite shape produced by the library stores: playlist
4Spotify cache, lyrics lookups, likes, track owners, library visibility,
5compilation marks, podcasts, library trash, library sync, discover,
6library upgrade and deemix audio replacement stores.
8Every statement is idempotent (``IF NOT EXISTS``) and has no trailing
9semicolon. Tables are ordered so foreign keys resolve.
10"""
12from __future__ import annotations
14DDL: list[str] = [
15 # --- playlist_spotify_cache -------------------------------------------
16 """
17 CREATE TABLE IF NOT EXISTS spotify_playlist_cache (
18 spotify_playlist_id TEXT PRIMARY KEY,
19 playlist_name TEXT NOT NULL,
20 fetched_at TEXT NOT NULL,
21 tracks_json TEXT NOT NULL
22 )
23 """,
24 """
25 CREATE TABLE IF NOT EXISTS spotify_playlist_tracks (
26 spotify_playlist_id TEXT NOT NULL,
27 track_spotify_id TEXT NOT NULL,
28 track_order BIGINT NOT NULL DEFAULT 0,
29 track_json TEXT NOT NULL,
30 PRIMARY KEY (spotify_playlist_id, track_order)
31 )
32 """,
33 """
34 CREATE INDEX IF NOT EXISTS idx_spotify_pl_tracks_track
35 ON spotify_playlist_tracks (track_spotify_id)
36 """,
37 """
38 CREATE TABLE IF NOT EXISTS spotify_track_cache (
39 track_spotify_id TEXT PRIMARY KEY,
40 track_json TEXT NOT NULL,
41 fetched_at TEXT NOT NULL
42 )
43 """,
44 # --- lyrics_cache -----------------------------------------------------
45 """
46 CREATE TABLE IF NOT EXISTS lyrics_lookups (
47 song_key TEXT NOT NULL,
48 provider TEXT NOT NULL,
49 found BIGINT NOT NULL,
50 checked_at TEXT NOT NULL,
51 PRIMARY KEY (song_key, provider)
52 )
53 """,
54 # --- likes ------------------------------------------------------------
55 # `id` mirrors SQLite rowid, which the store orders by (insertion order).
56 """
57 CREATE TABLE IF NOT EXISTS liked_tracks (
58 stored_path TEXT NOT NULL,
59 user_id BIGINT NOT NULL DEFAULT 0,
60 content_key TEXT NOT NULL DEFAULT '',
61 title TEXT NOT NULL DEFAULT '',
62 artist TEXT NOT NULL DEFAULT '',
63 duration DOUBLE PRECISION NOT NULL DEFAULT 0,
64 liked_at TEXT NOT NULL,
65 id BIGINT GENERATED BY DEFAULT AS IDENTITY,
66 PRIMARY KEY (user_id, stored_path)
67 )
68 """,
69 # --- track_owners -----------------------------------------------------
70 # `id` mirrors SQLite rowid, used for ORDER BY added_at, rowid.
71 """
72 CREATE TABLE IF NOT EXISTS track_owners (
73 content_key TEXT NOT NULL,
74 user_id BIGINT NOT NULL,
75 added_at TEXT NOT NULL,
76 stored_path TEXT NOT NULL DEFAULT '',
77 id BIGINT GENERATED BY DEFAULT AS IDENTITY,
78 PRIMARY KEY (content_key, user_id)
79 )
80 """,
81 """
82 CREATE INDEX IF NOT EXISTS idx_track_owners_user
83 ON track_owners (user_id)
84 """,
85 """
86 CREATE INDEX IF NOT EXISTS idx_track_owners_path
87 ON track_owners (stored_path)
88 """,
89 # --- library_visibility -----------------------------------------------
90 # `id` mirrors SQLite rowid, used for ORDER BY hidden_at DESC, rowid DESC.
91 """
92 CREATE TABLE IF NOT EXISTS hidden_library_items (
93 kind TEXT NOT NULL,
94 item_key TEXT NOT NULL,
95 item_path TEXT NOT NULL DEFAULT '',
96 label TEXT NOT NULL DEFAULT '',
97 detail TEXT NOT NULL DEFAULT '',
98 hidden_at TEXT NOT NULL,
99 id BIGINT GENERATED BY DEFAULT AS IDENTITY,
100 PRIMARY KEY (kind, item_key)
101 )
102 """,
103 """
104 CREATE INDEX IF NOT EXISTS idx_hidden_library_path
105 ON hidden_library_items (item_path)
106 """,
107 # --- compilation ------------------------------------------------------
108 """
109 CREATE TABLE IF NOT EXISTS album_compilation_marks (
110 artist_key TEXT NOT NULL,
111 album_key TEXT NOT NULL,
112 artist TEXT NOT NULL,
113 album TEXT NOT NULL,
114 compilation BIGINT NOT NULL,
115 album_artist TEXT NOT NULL,
116 marked_at TEXT NOT NULL,
117 PRIMARY KEY (artist_key, album_key)
118 )
119 """,
120 # --- podcasts ---------------------------------------------------------
121 """
122 CREATE TABLE IF NOT EXISTS podcast_shows (
123 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
124 feed_url TEXT NOT NULL,
125 name TEXT NOT NULL,
126 author TEXT NOT NULL DEFAULT '',
127 description TEXT NOT NULL DEFAULT '',
128 artwork_url TEXT NOT NULL DEFAULT '',
129 source_url TEXT NOT NULL DEFAULT '',
130 folder_name TEXT NOT NULL,
131 retention BIGINT NOT NULL DEFAULT 0,
132 created_at TEXT NOT NULL,
133 user_id BIGINT NOT NULL DEFAULT 0,
134 UNIQUE (feed_url, user_id)
135 )
136 """,
137 """
138 CREATE TABLE IF NOT EXISTS podcast_episodes (
139 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
140 show_id BIGINT NOT NULL,
141 guid TEXT NOT NULL,
142 title TEXT NOT NULL DEFAULT '',
143 description TEXT NOT NULL DEFAULT '',
144 published_at TEXT NOT NULL DEFAULT '',
145 duration_seconds DOUBLE PRECISION NOT NULL DEFAULT 0,
146 season_number BIGINT,
147 episode_number BIGINT,
148 enclosure_url TEXT NOT NULL DEFAULT '',
149 enclosure_type TEXT NOT NULL DEFAULT '',
150 filename TEXT,
151 downloaded_at TEXT,
152 dismissed BIGINT NOT NULL DEFAULT 0,
153 FOREIGN KEY (show_id) REFERENCES podcast_shows (id)
154 ON DELETE CASCADE,
155 UNIQUE (show_id, guid)
156 )
157 """,
158 """
159 CREATE TABLE IF NOT EXISTS podcast_playback (
160 episode_id BIGINT NOT NULL,
161 user_id BIGINT NOT NULL DEFAULT 0,
162 position_seconds DOUBLE PRECISION NOT NULL DEFAULT 0,
163 played BIGINT NOT NULL DEFAULT 0,
164 updated_at TEXT NOT NULL,
165 PRIMARY KEY (episode_id, user_id),
166 FOREIGN KEY (episode_id) REFERENCES podcast_episodes (id)
167 ON DELETE CASCADE
168 )
169 """,
170 # --- library_trash ----------------------------------------------------
171 """
172 CREATE TABLE IF NOT EXISTS library_trash (
173 id TEXT PRIMARY KEY,
174 batch TEXT NOT NULL,
175 user_id BIGINT NOT NULL,
176 root_key TEXT NOT NULL,
177 relative TEXT NOT NULL,
178 stored TEXT NOT NULL,
179 track_id TEXT NOT NULL DEFAULT '',
180 row_json TEXT NOT NULL,
181 manifest TEXT NOT NULL,
182 state TEXT NOT NULL,
183 action TEXT NOT NULL DEFAULT 'move',
184 created DOUBLE PRECISION NOT NULL,
185 moved DOUBLE PRECISION,
186 expires DOUBLE PRECISION,
187 error TEXT NOT NULL DEFAULT '',
188 blocked BIGINT NOT NULL DEFAULT 1,
189 requested_by BIGINT NOT NULL DEFAULT 0
190 )
191 """,
192 """
193 CREATE INDEX IF NOT EXISTS idx_trash_batch
194 ON library_trash (batch)
195 """,
196 """
197 CREATE INDEX IF NOT EXISTS idx_trash_stored
198 ON library_trash (stored, state)
199 """,
200 """
201 CREATE TABLE IF NOT EXISTS library_trash_blocks (
202 identity TEXT NOT NULL,
203 item_id TEXT NOT NULL,
204 PRIMARY KEY (identity, item_id)
205 )
206 """,
207 """
208 CREATE TABLE IF NOT EXISTS library_trash_meta (
209 key TEXT PRIMARY KEY,
210 value BIGINT NOT NULL
211 )
212 """,
213 # --- library_sync -----------------------------------------------------
214 """
215 CREATE SEQUENCE IF NOT EXISTS library_sync_seq
216 """,
217 """
218 CREATE TABLE IF NOT EXISTS sync_tracks (
219 track_id TEXT PRIMARY KEY,
220 path TEXT,
221 content_key TEXT NOT NULL DEFAULT '',
222 tag_key TEXT NOT NULL DEFAULT '',
223 row_json TEXT NOT NULL DEFAULT '{}',
224 row_hash TEXT NOT NULL DEFAULT '',
225 seq BIGINT NOT NULL,
226 deleted_at TEXT
227 )
228 """,
229 """
230 CREATE UNIQUE INDEX IF NOT EXISTS idx_sync_tracks_path
231 ON sync_tracks (path) WHERE path IS NOT NULL
232 """,
233 """
234 CREATE INDEX IF NOT EXISTS idx_sync_tracks_seq
235 ON sync_tracks (seq)
236 """,
237 """
238 CREATE TABLE IF NOT EXISTS sync_meta (
239 key TEXT PRIMARY KEY,
240 value BIGINT NOT NULL
241 )
242 """,
243 # --- discover ---------------------------------------------------------
244 """
245 CREATE TABLE IF NOT EXISTS discover_listens (
246 artist_key TEXT NOT NULL,
247 user_id BIGINT NOT NULL DEFAULT 0,
248 name TEXT NOT NULL,
249 plays BIGINT NOT NULL DEFAULT 0,
250 last_played TEXT NOT NULL,
251 PRIMARY KEY (artist_key, user_id)
252 )
253 """,
254 """
255 CREATE TABLE IF NOT EXISTS discover_plays (
256 play_id TEXT PRIMARY KEY,
257 recorded_at TEXT NOT NULL
258 )
259 """,
260 """
261 CREATE TABLE IF NOT EXISTS discover_blocked (
262 artist_key TEXT NOT NULL,
263 user_id BIGINT NOT NULL DEFAULT 0,
264 name TEXT NOT NULL,
265 blocked_at TEXT NOT NULL,
266 PRIMARY KEY (artist_key, user_id)
267 )
268 """,
269 """
270 CREATE TABLE IF NOT EXISTS discover_search (
271 artist_key TEXT PRIMARY KEY,
272 results_json TEXT NOT NULL DEFAULT '{}',
273 fetched_at TEXT NOT NULL
274 )
275 """,
276 """
277 CREATE TABLE IF NOT EXISTS discover_related (
278 artist_key TEXT PRIMARY KEY,
279 deezer_id TEXT NOT NULL DEFAULT '',
280 related_json TEXT NOT NULL DEFAULT '[]',
281 fetched_at TEXT NOT NULL
282 )
283 """,
284 """
285 CREATE TABLE IF NOT EXISTS discover_deezer (
286 artist_key TEXT PRIMARY KEY,
287 albums_json TEXT,
288 albums_at TEXT,
289 playlist_json TEXT,
290 playlist_at TEXT
291 )
292 """,
293 """
294 CREATE TABLE IF NOT EXISTS discover_album_map (
295 spotify_id TEXT PRIMARY KEY,
296 deezer_album_id TEXT NOT NULL DEFAULT '',
297 deezer_artist_id TEXT NOT NULL DEFAULT '',
298 cover_url TEXT NOT NULL DEFAULT '',
299 fetched_at TEXT NOT NULL
300 )
301 """,
302 # --- library_upgrade_db -----------------------------------------------
303 """
304 CREATE TABLE IF NOT EXISTS library_upgrade_runs (
305 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
306 state TEXT NOT NULL,
307 categories TEXT NOT NULL DEFAULT '[]',
308 options TEXT NOT NULL DEFAULT '{}',
309 total_tracks BIGINT NOT NULL DEFAULT 0,
310 total_bytes BIGINT NOT NULL DEFAULT 0,
311 summary TEXT NOT NULL DEFAULT '{}',
312 created_at TEXT NOT NULL,
313 finished_at TEXT NOT NULL DEFAULT ''
314 )
315 """,
316 """
317 CREATE TABLE IF NOT EXISTS library_upgrade_jobs (
318 run_id BIGINT NOT NULL,
319 stored_path TEXT NOT NULL,
320 status TEXT NOT NULL,
321 stage TEXT NOT NULL DEFAULT '',
322 categories TEXT NOT NULL DEFAULT '[]',
323 size_bytes BIGINT NOT NULL DEFAULT 0,
324 title TEXT NOT NULL DEFAULT '',
325 artist TEXT NOT NULL DEFAULT '',
326 detail TEXT NOT NULL DEFAULT '',
327 changed TEXT NOT NULL DEFAULT '[]',
328 updated_at TEXT NOT NULL,
329 PRIMARY KEY (run_id, stored_path)
330 )
331 """,
332 """
333 CREATE INDEX IF NOT EXISTS idx_upgrade_jobs_status
334 ON library_upgrade_jobs (run_id, status)
335 """,
336 """
337 CREATE TABLE IF NOT EXISTS library_upgrade_checks (
338 stored_path TEXT NOT NULL,
339 category TEXT NOT NULL,
340 checked_at TEXT NOT NULL,
341 app_version TEXT NOT NULL DEFAULT '',
342 artwork_px BIGINT NOT NULL DEFAULT 0,
343 artwork_source TEXT NOT NULL DEFAULT '',
344 PRIMARY KEY (stored_path, category)
345 )
346 """,
347 # --- deemix_upgrade_store ---------------------------------------------
348 """
349 CREATE TABLE IF NOT EXISTS library_audio_replacements (
350 old_file TEXT PRIMARY KEY,
351 run_id BIGINT NOT NULL,
352 new_file TEXT NOT NULL,
353 old_key TEXT NOT NULL,
354 new_key TEXT NOT NULL,
355 old_sha TEXT NOT NULL,
356 new_sha TEXT NOT NULL,
357 new_metadata TEXT NOT NULL,
358 catalog TEXT NOT NULL,
359 delete_original BIGINT NOT NULL DEFAULT 0,
360 state TEXT NOT NULL DEFAULT 'prepared',
361 detail TEXT NOT NULL DEFAULT '',
362 source_deleted BIGINT NOT NULL DEFAULT 0,
363 updated_at TEXT NOT NULL
364 )
365 """,
366 """
367 CREATE INDEX IF NOT EXISTS idx_audio_replacement_run
368 ON library_audio_replacements (run_id)
369 """,
370 """
371 CREATE TABLE IF NOT EXISTS library_audio_sync_aliases (
372 track_id TEXT PRIMARY KEY,
373 old_file TEXT NOT NULL
374 )
375 """,
376]