Coverage for downtify/db/migrations/schema/monitor.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 Playlist Monitor table group.
3Mirrors the final SQLite schema that :class:`downtify.monitor.PlaylistMonitorDB`
4(with :class:`downtify.artist_release_store.ArtistReleaseStore`,
5:class:`downtify.download_policy.DownloadPolicy` and
6:class:`downtify.watch_history.WatchHistoryStore`) ends up with after its
7CREATE and ALTER migrations. Parents come before children so that every
8foreign key resolves when the statements run in order.
9"""
11from __future__ import annotations
13DDL: list[str] = [
14 """
15 CREATE TABLE IF NOT EXISTS monitored_playlists (
16 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
17 spotify_id TEXT NOT NULL,
18 name TEXT NOT NULL,
19 url TEXT NOT NULL,
20 interval_minutes BIGINT NOT NULL DEFAULT 60,
21 enabled BIGINT NOT NULL DEFAULT 1,
22 last_checked TEXT,
23 last_track_count BIGINT NOT NULL DEFAULT 0,
24 created_at TEXT NOT NULL,
25 user_id BIGINT NOT NULL DEFAULT 0,
26 release_types TEXT NOT NULL DEFAULT 'album,single,ep',
27 new_only BIGINT NOT NULL DEFAULT 0,
28 baseline_pending BIGINT NOT NULL DEFAULT 0,
29 kind TEXT NOT NULL DEFAULT 'playlist',
30 deezer_id TEXT NOT NULL DEFAULT '',
31 musicbrainz_id TEXT NOT NULL DEFAULT '',
32 musicbrainz_kinds TEXT,
33 UNIQUE (spotify_id, user_id)
34 )
35 """,
36 """
37 CREATE TABLE IF NOT EXISTS downloaded_tracks (
38 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
39 playlist_id BIGINT NOT NULL,
40 track_spotify_id TEXT NOT NULL,
41 downloaded_at TEXT NOT NULL,
42 filename TEXT,
43 FOREIGN KEY (playlist_id) REFERENCES monitored_playlists (id)
44 ON DELETE CASCADE,
45 UNIQUE (playlist_id, track_spotify_id)
46 )
47 """,
48 """
49 CREATE TABLE IF NOT EXISTS seen_albums (
50 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
51 playlist_id BIGINT NOT NULL,
52 album_id TEXT NOT NULL,
53 name TEXT,
54 seen_at TEXT NOT NULL,
55 skipped BIGINT NOT NULL DEFAULT 0,
56 last_attempt TEXT,
57 FOREIGN KEY (playlist_id) REFERENCES monitored_playlists (id)
58 ON DELETE CASCADE,
59 UNIQUE (playlist_id, album_id)
60 )
61 """,
62 """
63 CREATE TABLE IF NOT EXISTS artist_release_tracks (
64 playlist_id BIGINT NOT NULL,
65 album_id TEXT NOT NULL,
66 position BIGINT NOT NULL,
67 track_id TEXT NOT NULL,
68 filename TEXT NOT NULL,
69 PRIMARY KEY (playlist_id, album_id, position),
70 FOREIGN KEY (playlist_id) REFERENCES monitored_playlists (id)
71 ON DELETE CASCADE
72 )
73 """,
74 """
75 CREATE TABLE IF NOT EXISTS artist_catalogue (
76 playlist_id BIGINT NOT NULL,
77 album_id TEXT NOT NULL,
78 name TEXT NOT NULL,
79 release_type TEXT NOT NULL DEFAULT '',
80 kinds TEXT NOT NULL DEFAULT '',
81 year TEXT NOT NULL DEFAULT '',
82 source TEXT NOT NULL DEFAULT '',
83 cover_url TEXT NOT NULL DEFAULT '',
84 url TEXT NOT NULL DEFAULT '',
85 position BIGINT NOT NULL DEFAULT 0,
86 track_total BIGINT NOT NULL DEFAULT 0,
87 not_own BIGINT NOT NULL DEFAULT 0,
88 choice TEXT NOT NULL DEFAULT '',
89 listed_at TEXT NOT NULL,
90 release_date TEXT NOT NULL DEFAULT '',
91 PRIMARY KEY (playlist_id, album_id),
92 FOREIGN KEY (playlist_id) REFERENCES monitored_playlists (id)
93 ON DELETE CASCADE
94 )
95 """,
96 """
97 CREATE TABLE IF NOT EXISTS artist_catalogue_tracks (
98 playlist_id BIGINT NOT NULL,
99 album_id TEXT NOT NULL,
100 position BIGINT NOT NULL,
101 name TEXT NOT NULL,
102 artists TEXT NOT NULL DEFAULT '[]',
103 duration DOUBLE PRECISION NOT NULL DEFAULT 0,
104 PRIMARY KEY (playlist_id, album_id, position),
105 FOREIGN KEY (playlist_id) REFERENCES monitored_playlists (id)
106 ON DELETE CASCADE
107 )
108 """,
109 """
110 CREATE TABLE IF NOT EXISTS download_policy (
111 id TEXT PRIMARY KEY,
112 track_id TEXT NOT NULL DEFAULT '',
113 title TEXT NOT NULL,
114 artist TEXT NOT NULL,
115 album TEXT NOT NULL,
116 duration BIGINT NOT NULL,
117 title_key TEXT NOT NULL,
118 artist_key TEXT NOT NULL,
119 album_key TEXT NOT NULL,
120 fallback_key TEXT NOT NULL,
121 elapsed DOUBLE PRECISION NOT NULL DEFAULT 0,
122 blocked BIGINT NOT NULL DEFAULT 0,
123 reason TEXT NOT NULL DEFAULT '',
124 requested_by BIGINT NOT NULL DEFAULT 0,
125 updated DOUBLE PRECISION NOT NULL
126 )
127 """,
128 """
129 CREATE INDEX IF NOT EXISTS idx_policy_track
130 ON download_policy (track_id)
131 """,
132 """
133 CREATE INDEX IF NOT EXISTS idx_policy_match
134 ON download_policy (title_key, artist_key)
135 """,
136 """
137 CREATE TABLE IF NOT EXISTS watch_events (
138 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
139 playlist_id BIGINT NOT NULL,
140 watch_key TEXT NOT NULL,
141 watch_name TEXT NOT NULL,
142 user_id BIGINT NOT NULL DEFAULT 0,
143 track_id TEXT NOT NULL,
144 outcome TEXT NOT NULL,
145 filename TEXT,
146 provider TEXT NOT NULL DEFAULT '',
147 initial BIGINT NOT NULL DEFAULT 0,
148 happened_at TEXT NOT NULL
149 )
150 """,
151 """
152 CREATE INDEX IF NOT EXISTS idx_watch_events_watch
153 ON watch_events (playlist_id, happened_at)
154 """,
155 """
156 CREATE INDEX IF NOT EXISTS idx_watch_events_track
157 ON watch_events (track_id)
158 """,
159 """
160 CREATE INDEX IF NOT EXISTS idx_watch_events_file
161 ON watch_events (filename)
162 """,
163 """
164 CREATE TABLE IF NOT EXISTS watch_failures (
165 playlist_id BIGINT NOT NULL,
166 track_id TEXT NOT NULL,
167 name TEXT NOT NULL DEFAULT '',
168 artists TEXT NOT NULL DEFAULT '',
169 error TEXT NOT NULL DEFAULT '',
170 attempts BIGINT NOT NULL DEFAULT 1,
171 first_failed_at TEXT NOT NULL,
172 last_failed_at TEXT NOT NULL,
173 PRIMARY KEY (playlist_id, track_id),
174 FOREIGN KEY (playlist_id) REFERENCES monitored_playlists (id)
175 ON DELETE CASCADE
176 )
177 """,
178]