t1c.dev
forked from
rocksky.app/rocksky
A decentralized music tracking and discovery platform built on AT Protocol 馃幍
tsiry-sandratraina.com
1CREATE TABLE `album_tracks` (
2 `id` text PRIMARY KEY NOT NULL,
3 `album_id` text NOT NULL,
4 `track_id` text NOT NULL,
5 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
6 `updated_at` integer DEFAULT (unixepoch()) NOT NULL,
7 FOREIGN KEY (`album_id`) REFERENCES `albums`(`id`) ON UPDATE no action ON DELETE no action,
8 FOREIGN KEY (`track_id`) REFERENCES `tracks`(`id`) ON UPDATE no action ON DELETE no action
9);
10--> statement-breakpoint
11CREATE UNIQUE INDEX `album_tracks_unique_index` ON `album_tracks` (`album_id`,`track_id`);--> statement-breakpoint
12CREATE TABLE `albums` (
13 `id` text PRIMARY KEY NOT NULL,
14 `title` text NOT NULL,
15 `artist` text NOT NULL,
16 `release_date` text,
17 `year` integer,
18 `album_art` text,
19 `uri` text,
20 `cid` text NOT NULL,
21 `artist_uri` text,
22 `apple_music_link` text,
23 `spotify_link` text,
24 `tidal_link` text,
25 `youtube_link` text,
26 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
27 `updated_at` integer DEFAULT (unixepoch()) NOT NULL
28);
29--> statement-breakpoint
30CREATE UNIQUE INDEX `albums_uri_unique` ON `albums` (`uri`);--> statement-breakpoint
31CREATE UNIQUE INDEX `albums_cid_unique` ON `albums` (`cid`);--> statement-breakpoint
32CREATE UNIQUE INDEX `albums_apple_music_link_unique` ON `albums` (`apple_music_link`);--> statement-breakpoint
33CREATE UNIQUE INDEX `albums_spotify_link_unique` ON `albums` (`spotify_link`);--> statement-breakpoint
34CREATE UNIQUE INDEX `albums_tidal_link_unique` ON `albums` (`tidal_link`);--> statement-breakpoint
35CREATE UNIQUE INDEX `albums_youtube_link_unique` ON `albums` (`youtube_link`);--> statement-breakpoint
36CREATE TABLE `artist_albums` (
37 `id` text PRIMARY KEY NOT NULL,
38 `artist_id` text NOT NULL,
39 `album_id` text NOT NULL,
40 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
41 `updated_at` integer DEFAULT (unixepoch()) NOT NULL,
42 FOREIGN KEY (`artist_id`) REFERENCES `artists`(`id`) ON UPDATE no action ON DELETE no action,
43 FOREIGN KEY (`album_id`) REFERENCES `albums`(`id`) ON UPDATE no action ON DELETE no action
44);
45--> statement-breakpoint
46CREATE UNIQUE INDEX `artist_albums_unique_index` ON `artist_albums` (`artist_id`,`album_id`);--> statement-breakpoint
47CREATE TABLE `artist_genres ` (
48 `id` text PRIMARY KEY NOT NULL,
49 `artist_id` text NOT NULL,
50 `genre_id` text NOT NULL
51);
52--> statement-breakpoint
53CREATE UNIQUE INDEX `artist_genre_unique_index` ON `artist_genres ` (`artist_id`,`genre_id`);--> statement-breakpoint
54CREATE TABLE `artist_tracks` (
55 `id` text PRIMARY KEY NOT NULL,
56 `artist_id` text NOT NULL,
57 `track_id` text NOT NULL,
58 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
59 `updated_at` integer DEFAULT (unixepoch()) NOT NULL,
60 FOREIGN KEY (`artist_id`) REFERENCES `artists`(`id`) ON UPDATE no action ON DELETE no action,
61 FOREIGN KEY (`track_id`) REFERENCES `tracks`(`id`) ON UPDATE no action ON DELETE no action
62);
63--> statement-breakpoint
64CREATE UNIQUE INDEX `artist_tracks_unique_index` ON `artist_tracks` (`artist_id`,`track_id`);--> statement-breakpoint
65CREATE TABLE `artists` (
66 `id` text PRIMARY KEY NOT NULL,
67 `name` text NOT NULL,
68 `biography` text,
69 `born` integer,
70 `born_in` text,
71 `died` integer,
72 `picture` text,
73 `uri` text,
74 `cid` text NOT NULL,
75 `apple_music_link` text,
76 `spotify_link` text,
77 `tidal_link` text,
78 `youtube_link` text,
79 `genres` text,
80 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
81 `updated_at` integer DEFAULT (unixepoch()) NOT NULL
82);
83--> statement-breakpoint
84CREATE UNIQUE INDEX `artists_uri_unique` ON `artists` (`uri`);--> statement-breakpoint
85CREATE UNIQUE INDEX `artists_cid_unique` ON `artists` (`cid`);--> statement-breakpoint
86CREATE TABLE `auth_sessions` (
87 `key` text PRIMARY KEY NOT NULL,
88 `session` text NOT NULL,
89 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
90 `updated_at` integer DEFAULT (unixepoch()) NOT NULL
91);
92--> statement-breakpoint
93CREATE TABLE `genres` (
94 `id` text PRIMARY KEY NOT NULL,
95 `name` text NOT NULL,
96 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
97 `updated_at` integer DEFAULT (unixepoch()) NOT NULL
98);
99--> statement-breakpoint
100CREATE UNIQUE INDEX `genres_name_unique` ON `genres` (`name`);--> statement-breakpoint
101CREATE TABLE `loved_tracks` (
102 `id` text PRIMARY KEY NOT NULL,
103 `user_id` text NOT NULL,
104 `track_id` text NOT NULL,
105 `uri` text,
106 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
107 FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action,
108 FOREIGN KEY (`track_id`) REFERENCES `tracks`(`id`) ON UPDATE no action ON DELETE no action
109);
110--> statement-breakpoint
111CREATE UNIQUE INDEX `loved_tracks_uri_unique` ON `loved_tracks` (`uri`);--> statement-breakpoint
112CREATE UNIQUE INDEX `loved_tracks_unique_index` ON `loved_tracks` (`user_id`,`track_id`);--> statement-breakpoint
113CREATE TABLE `scrobbles` (
114 `xata_id` text PRIMARY KEY NOT NULL,
115 `user_id` text,
116 `track_id` text,
117 `album_id` text,
118 `artist_id` text,
119 `uri` text,
120 `cid` text,
121 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
122 `updated_at` integer DEFAULT (unixepoch()) NOT NULL,
123 `timestamp` integer DEFAULT (unixepoch()) NOT NULL,
124 FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action,
125 FOREIGN KEY (`track_id`) REFERENCES `tracks`(`id`) ON UPDATE no action ON DELETE no action,
126 FOREIGN KEY (`album_id`) REFERENCES `albums`(`id`) ON UPDATE no action ON DELETE no action,
127 FOREIGN KEY (`artist_id`) REFERENCES `artists`(`id`) ON UPDATE no action ON DELETE no action
128);
129--> statement-breakpoint
130CREATE UNIQUE INDEX `scrobbles_uri_unique` ON `scrobbles` (`uri`);--> statement-breakpoint
131CREATE UNIQUE INDEX `scrobbles_cid_unique` ON `scrobbles` (`cid`);--> statement-breakpoint
132CREATE TABLE `tracks` (
133 `id` text PRIMARY KEY NOT NULL,
134 `title` text NOT NULL,
135 `artist` text NOT NULL,
136 `album_artist` text NOT NULL,
137 `album_art` text,
138 `album` text NOT NULL,
139 `track_number` integer,
140 `duration` integer NOT NULL,
141 `mb_id` text,
142 `youtube_link` text,
143 `spotify_link` text,
144 `apple_music_link` text,
145 `tidal_link` text,
146 `disc_number` integer,
147 `lyrics` text,
148 `composer` text,
149 `genre` text,
150 `label` text,
151 `copyright_message` text,
152 `uri` text,
153 `cid` text NOT NULL,
154 `album_uri` text,
155 `artist_uri` text,
156 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
157 `updated_at` integer DEFAULT (unixepoch()) NOT NULL
158);
159--> statement-breakpoint
160CREATE UNIQUE INDEX `tracks_mb_id_unique` ON `tracks` (`mb_id`);--> statement-breakpoint
161CREATE UNIQUE INDEX `tracks_youtube_link_unique` ON `tracks` (`youtube_link`);--> statement-breakpoint
162CREATE UNIQUE INDEX `tracks_spotify_link_unique` ON `tracks` (`spotify_link`);--> statement-breakpoint
163CREATE UNIQUE INDEX `tracks_apple_music_link_unique` ON `tracks` (`apple_music_link`);--> statement-breakpoint
164CREATE UNIQUE INDEX `tracks_tidal_link_unique` ON `tracks` (`tidal_link`);--> statement-breakpoint
165CREATE UNIQUE INDEX `tracks_uri_unique` ON `tracks` (`uri`);--> statement-breakpoint
166CREATE UNIQUE INDEX `tracks_cid_unique` ON `tracks` (`cid`);--> statement-breakpoint
167CREATE TABLE `user_albums` (
168 `id` text PRIMARY KEY NOT NULL,
169 `user_id` text NOT NULL,
170 `album_id` text NOT NULL,
171 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
172 `updated_at` integer DEFAULT (unixepoch()) NOT NULL,
173 `scrobbles` integer,
174 `uri` text NOT NULL,
175 FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action,
176 FOREIGN KEY (`album_id`) REFERENCES `albums`(`id`) ON UPDATE no action ON DELETE no action
177);
178--> statement-breakpoint
179CREATE UNIQUE INDEX `user_albums_uri_unique` ON `user_albums` (`uri`);--> statement-breakpoint
180CREATE UNIQUE INDEX `user_albums_unique_index` ON `user_albums` (`user_id`,`album_id`);--> statement-breakpoint
181CREATE TABLE `user_artists` (
182 `id` text PRIMARY KEY NOT NULL,
183 `user_id` text NOT NULL,
184 `artist_id` text NOT NULL,
185 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
186 `updated_at` integer DEFAULT (unixepoch()) NOT NULL,
187 `scrobbles` integer,
188 `uri` text NOT NULL,
189 FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action,
190 FOREIGN KEY (`artist_id`) REFERENCES `artists`(`id`) ON UPDATE no action ON DELETE no action
191);
192--> statement-breakpoint
193CREATE UNIQUE INDEX `user_artists_uri_unique` ON `user_artists` (`uri`);--> statement-breakpoint
194CREATE UNIQUE INDEX `user_artists_unique_index` ON `user_artists` (`user_id`,`artist_id`);--> statement-breakpoint
195CREATE TABLE `user_tracks` (
196 `id` text PRIMARY KEY NOT NULL,
197 `user_id` text NOT NULL,
198 `track_id` text NOT NULL,
199 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
200 `updated_at` integer DEFAULT (unixepoch()) NOT NULL,
201 `scrobbles` integer,
202 `uri` text NOT NULL,
203 FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action,
204 FOREIGN KEY (`track_id`) REFERENCES `tracks`(`id`) ON UPDATE no action ON DELETE no action
205);
206--> statement-breakpoint
207CREATE UNIQUE INDEX `user_tracks_uri_unique` ON `user_tracks` (`uri`);--> statement-breakpoint
208CREATE UNIQUE INDEX `user_tracks_unique_index` ON `user_tracks` (`user_id`,`track_id`);--> statement-breakpoint
209CREATE TABLE `users` (
210 `id` text PRIMARY KEY NOT NULL,
211 `did` text NOT NULL,
212 `display_name` text,
213 `handle` text NOT NULL,
214 `avatar` text NOT NULL,
215 `created_at` integer DEFAULT (unixepoch()) NOT NULL,
216 `updated_at` integer DEFAULT (unixepoch()) NOT NULL
217);
218--> statement-breakpoint
219CREATE UNIQUE INDEX `users_did_unique` ON `users` (`did`);--> statement-breakpoint
220CREATE UNIQUE INDEX `users_handle_unique` ON `users` (`handle`);