Coverage for downtify/db/migrations/schema/library_a.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, playlist and download SQLite stores.
3Mirrors the final SQLite schema each store produces (after its ``ALTER``
4and rebuild steps). Statements are ordered so foreign keys resolve.
5"""
7from __future__ import annotations
9DDL: list[str] = [
10 # --- playlists -------------------------------------------------------
11 """
12 CREATE TABLE IF NOT EXISTS playlists (
13 name TEXT PRIMARY KEY,
14 spotify_id TEXT,
15 updated_at TEXT NOT NULL,
16 user_id BIGINT NOT NULL DEFAULT 0
17 )
18 """,
19 """
20 CREATE TABLE IF NOT EXISTS manual_playlist_owners (
21 name_key TEXT PRIMARY KEY,
22 name TEXT NOT NULL,
23 user_id BIGINT NOT NULL
24 )
25 """,
26 """
27 CREATE TABLE IF NOT EXISTS playlist_tracks (
28 playlist_name TEXT NOT NULL,
29 track_spotify_id TEXT NOT NULL,
30 content_key TEXT,
31 filename TEXT NOT NULL,
32 track_order BIGINT NOT NULL DEFAULT 0,
33 PRIMARY KEY (playlist_name, track_spotify_id),
34 FOREIGN KEY (playlist_name) REFERENCES playlists (name)
35 ON DELETE CASCADE
36 )
37 """,
38 """
39 CREATE INDEX IF NOT EXISTS idx_playlist_tracks_content_key
40 ON playlist_tracks (content_key)
41 WHERE content_key IS NOT NULL
42 """,
43 # --- playlist shares -------------------------------------------------
44 """
45 CREATE TABLE IF NOT EXISTS playlist_shares (
46 name_key TEXT PRIMARY KEY,
47 visibility TEXT NOT NULL DEFAULT 'private'
48 )
49 """,
50 """
51 CREATE TABLE IF NOT EXISTS playlist_share_users (
52 name_key TEXT NOT NULL,
53 user_id BIGINT NOT NULL,
54 seen BIGINT NOT NULL DEFAULT 0,
55 PRIMARY KEY (name_key, user_id)
56 )
57 """,
58 """
59 CREATE TABLE IF NOT EXISTS playlist_follows (
60 user_id BIGINT NOT NULL,
61 name_key TEXT NOT NULL,
62 PRIMARY KEY (user_id, name_key)
63 )
64 """,
65 """
66 CREATE TABLE IF NOT EXISTS playlist_editors (
67 name_key TEXT NOT NULL,
68 user_id BIGINT NOT NULL,
69 PRIMARY KEY (name_key, user_id)
70 )
71 """,
72 """
73 CREATE TABLE IF NOT EXISTS playlist_display_names (
74 identity TEXT PRIMARY KEY,
75 display_name TEXT NOT NULL
76 )
77 """,
78 """
79 CREATE TABLE IF NOT EXISTS playlist_favorites (
80 user_id BIGINT NOT NULL,
81 name_key TEXT NOT NULL,
82 PRIMARY KEY (user_id, name_key)
83 )
84 """,
85 # --- playlist hidden songs -------------------------------------------
86 # ``id`` is added because the store orders by SQLite's implicit
87 # ``rowid`` (``ORDER BY hidden_at DESC, rowid DESC``).
88 """
89 CREATE TABLE IF NOT EXISTS playlist_hidden_songs (
90 id BIGINT GENERATED BY DEFAULT AS IDENTITY,
91 name_key TEXT NOT NULL,
92 item_key TEXT NOT NULL,
93 track_id TEXT NOT NULL DEFAULT '',
94 content_key TEXT NOT NULL DEFAULT '',
95 file TEXT NOT NULL DEFAULT '',
96 label TEXT NOT NULL DEFAULT '',
97 extinf TEXT NOT NULL DEFAULT '',
98 position BIGINT NOT NULL DEFAULT -1,
99 hidden_at TEXT NOT NULL,
100 PRIMARY KEY (name_key, item_key)
101 )
102 """,
103 """
104 CREATE INDEX IF NOT EXISTS idx_playlist_hidden_content_key
105 ON playlist_hidden_songs (content_key)
106 """,
107 # --- playlist batches ------------------------------------------------
108 """
109 CREATE TABLE IF NOT EXISTS playlist_batches (
110 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
111 spotify_playlist_id TEXT NOT NULL,
112 playlist_name TEXT NOT NULL,
113 playlist_url TEXT NOT NULL,
114 expected_count BIGINT NOT NULL DEFAULT 0,
115 started_at TEXT NOT NULL,
116 finished_at TEXT,
117 status TEXT NOT NULL DEFAULT 'in_progress',
118 succeeded_count BIGINT NOT NULL DEFAULT 0,
119 failed_count BIGINT NOT NULL DEFAULT 0,
120 user_id BIGINT NOT NULL DEFAULT 0
121 )
122 """,
123 """
124 CREATE INDEX IF NOT EXISTS idx_playlist_batches_spotify
125 ON playlist_batches (spotify_playlist_id, id DESC)
126 """,
127 # --- external playlist imports ---------------------------------------
128 """
129 CREATE TABLE IF NOT EXISTS external_playlist_imports (
130 user_id BIGINT NOT NULL,
131 source_id TEXT NOT NULL,
132 playlist_name TEXT NOT NULL,
133 revision TEXT NOT NULL,
134 PRIMARY KEY (user_id, source_id)
135 )
136 """,
137 # --- library tracks --------------------------------------------------
138 """
139 CREATE TABLE IF NOT EXISTS library_tracks (
140 spotify_track_id TEXT PRIMARY KEY,
141 filename TEXT NOT NULL,
142 content_key TEXT,
143 registered_at TEXT NOT NULL
144 )
145 """,
146 """
147 CREATE INDEX IF NOT EXISTS idx_library_tracks_content_key
148 ON library_tracks (content_key)
149 WHERE content_key IS NOT NULL
150 """,
151 # --- navidrome index -------------------------------------------------
152 """
153 CREATE TABLE IF NOT EXISTS navidrome_tracks (
154 content_key TEXT PRIMARY KEY,
155 filename TEXT NOT NULL,
156 navidrome_song_id TEXT NOT NULL,
157 spotify_track_id TEXT,
158 resolved_at TEXT NOT NULL
159 )
160 """,
161 """
162 CREATE INDEX IF NOT EXISTS idx_navidrome_filename
163 ON navidrome_tracks (filename)
164 """,
165 """
166 CREATE INDEX IF NOT EXISTS idx_navidrome_spotify
167 ON navidrome_tracks (spotify_track_id)
168 WHERE spotify_track_id IS NOT NULL
169 """,
170 # --- library metadata cache ------------------------------------------
171 """
172 CREATE TABLE IF NOT EXISTS library_metadata (
173 content_key TEXT PRIMARY KEY,
174 filename TEXT NOT NULL,
175 title TEXT NOT NULL DEFAULT '',
176 artist TEXT NOT NULL DEFAULT '',
177 album TEXT NOT NULL DEFAULT '',
178 has_cover BIGINT NOT NULL DEFAULT 0,
179 cover_px BIGINT NOT NULL DEFAULT 0,
180 album_artist TEXT NOT NULL DEFAULT '',
181 track_number BIGINT NOT NULL DEFAULT 0,
182 year TEXT NOT NULL DEFAULT '',
183 release_date TEXT NOT NULL DEFAULT '',
184 duration DOUBLE PRECISION NOT NULL DEFAULT 0,
185 meta_version BIGINT NOT NULL DEFAULT 1,
186 codec TEXT NOT NULL DEFAULT '',
187 bitrate BIGINT NOT NULL DEFAULT 0,
188 sample_rate BIGINT NOT NULL DEFAULT 0,
189 channels BIGINT NOT NULL DEFAULT 0,
190 genre TEXT NOT NULL DEFAULT '',
191 explicit BIGINT NOT NULL DEFAULT 0,
192 artists TEXT NOT NULL DEFAULT '[]',
193 compilation BIGINT NOT NULL DEFAULT 0,
194 isrc TEXT NOT NULL DEFAULT '',
195 file_mtime_ns BIGINT NOT NULL,
196 file_size BIGINT NOT NULL,
197 cached_at TEXT NOT NULL
198 )
199 """,
200 """
201 CREATE INDEX IF NOT EXISTS idx_library_metadata_filename
202 ON library_metadata (filename)
203 """,
204 # --- library listing snapshot ----------------------------------------
205 """
206 CREATE TABLE IF NOT EXISTS library_listing (
207 cache_key TEXT PRIMARY KEY,
208 fingerprint TEXT NOT NULL,
209 listing_format BIGINT NOT NULL,
210 meta_version BIGINT NOT NULL,
211 paths_json TEXT NOT NULL,
212 playlists_json TEXT NOT NULL,
213 entries_json TEXT NOT NULL
214 )
215 """,
216 # --- hand-replaced files ---------------------------------------------
217 """
218 CREATE TABLE IF NOT EXISTS hand_replaced_files (
219 file TEXT PRIMARY KEY,
220 source TEXT NOT NULL,
221 replaced_at DOUBLE PRECISION NOT NULL
222 )
223 """,
224 # --- file providers --------------------------------------------------
225 """
226 CREATE TABLE IF NOT EXISTS file_providers (
227 content_key TEXT PRIMARY KEY,
228 path TEXT NOT NULL DEFAULT '',
229 provider TEXT NOT NULL,
230 recorded_at TEXT NOT NULL,
231 source TEXT NOT NULL DEFAULT ''
232 )
233 """,
234 """
235 CREATE INDEX IF NOT EXISTS idx_file_providers_path
236 ON file_providers (path)
237 """,
238 # --- download log ----------------------------------------------------
239 """
240 CREATE TABLE IF NOT EXISTS download_log (
241 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
242 at TEXT NOT NULL,
243 outcome TEXT NOT NULL,
244 provider TEXT NOT NULL DEFAULT '',
245 bytes BIGINT NOT NULL DEFAULT 0,
246 filename TEXT NOT NULL DEFAULT ''
247 )
248 """,
249 """
250 CREATE INDEX IF NOT EXISTS idx_download_log_at
251 ON download_log (at)
252 """,
253 # --- library usage snapshots -----------------------------------------
254 """
255 CREATE TABLE IF NOT EXISTS library_usage_snapshots (
256 day TEXT PRIMARY KEY,
257 taken_at TEXT NOT NULL,
258 files BIGINT NOT NULL DEFAULT 0,
259 bytes BIGINT NOT NULL DEFAULT 0,
260 working_files BIGINT NOT NULL DEFAULT 0,
261 working_bytes BIGINT NOT NULL DEFAULT 0,
262 detail_json TEXT NOT NULL DEFAULT '{}'
263 )
264 """,
265 # --- loudness --------------------------------------------------------
266 """
267 CREATE TABLE IF NOT EXISTS loudness_measurements (
268 file TEXT PRIMARY KEY,
269 signature TEXT NOT NULL,
270 profile TEXT NOT NULL
271 )
272 """,
273 """
274 CREATE TABLE IF NOT EXISTS loudness_jobs (
275 file TEXT PRIMARY KEY,
276 position BIGINT NOT NULL,
277 status TEXT NOT NULL
278 )
279 """,
280 """
281 CREATE INDEX IF NOT EXISTS loudness_jobs_pending
282 ON loudness_jobs (status, position)
283 """,
284 # --- download queue --------------------------------------------------
285 """
286 CREATE TABLE IF NOT EXISTS pending_downloads (
287 seq BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
288 song_id TEXT NOT NULL UNIQUE,
289 song TEXT NOT NULL,
290 options TEXT NOT NULL DEFAULT '{}',
291 user_ids TEXT NOT NULL DEFAULT '[]'
292 )
293 """,
294 """
295 CREATE TABLE IF NOT EXISTS download_queue_state (
296 key TEXT PRIMARY KEY,
297 value TEXT NOT NULL
298 )
299 """,
300]