1 CREATE TABLE "artist" (
2 "artistid" INTEGER PRIMARY KEY NOT NULL,
4 "rank" integer NOT NULL DEFAULT 13,
8 CREATE INDEX "artist_name_hookidx" ON "artist" ("name");
10 CREATE UNIQUE INDEX "artist_name" ON "artist" ("name");
12 CREATE UNIQUE INDEX "u_nullable" ON "artist" ("charfield", "rank");
14 CREATE TABLE "bindtype_test" (
15 "id" INTEGER PRIMARY KEY NOT NULL,
24 CREATE TABLE "collection" (
25 "collectionid" INTEGER PRIMARY KEY NOT NULL,
26 "name" varchar(100) NOT NULL
29 CREATE TABLE "encoded" (
30 "id" INTEGER PRIMARY KEY NOT NULL,
31 "encoded" varchar(100)
34 CREATE TABLE "event" (
35 "id" INTEGER PRIMARY KEY NOT NULL,
36 "starts_at" date NOT NULL,
37 "created_on" timestamp NOT NULL,
38 "varchar_date" varchar(20),
39 "varchar_datetime" varchar(20),
40 "skip_inflation" datetime,
41 "ts_without_tz" datetime
44 CREATE TABLE "fourkeys" (
45 "foo" integer NOT NULL,
46 "bar" integer NOT NULL,
47 "hello" integer NOT NULL,
48 "goodbye" integer NOT NULL,
49 "sensors" character(10) NOT NULL,
51 PRIMARY KEY ("foo", "bar", "hello", "goodbye")
54 CREATE TABLE "genre" (
55 "genreid" INTEGER PRIMARY KEY NOT NULL,
56 "name" varchar(100) NOT NULL
59 CREATE UNIQUE INDEX "genre_name" ON "genre" ("name");
62 "id" INTEGER PRIMARY KEY NOT NULL,
67 CREATE TABLE "money_test" (
68 "id" INTEGER PRIMARY KEY NOT NULL,
72 CREATE TABLE "noprimarykey" (
73 "foo" integer NOT NULL,
74 "bar" integer NOT NULL,
75 "baz" integer NOT NULL
78 CREATE UNIQUE INDEX "foo_bar" ON "noprimarykey" ("foo", "bar");
80 CREATE TABLE "onekey" (
81 "id" INTEGER PRIMARY KEY NOT NULL,
82 "artist" integer NOT NULL,
86 CREATE TABLE "owners" (
87 "id" INTEGER PRIMARY KEY NOT NULL,
88 "name" varchar(100) NOT NULL
91 CREATE UNIQUE INDEX "owners_name" ON "owners" ("name");
93 CREATE TABLE "producer" (
94 "producerid" INTEGER PRIMARY KEY NOT NULL,
95 "name" varchar(100) NOT NULL
98 CREATE UNIQUE INDEX "prod_name" ON "producer" ("name");
100 CREATE TABLE "self_ref" (
101 "id" INTEGER PRIMARY KEY NOT NULL,
102 "name" varchar(100) NOT NULL
105 CREATE TABLE "sequence_test" (
106 "pkid1" integer NOT NULL,
107 "pkid2" integer NOT NULL,
108 "nonpkid" integer NOT NULL,
110 PRIMARY KEY ("pkid1", "pkid2")
113 CREATE TABLE "serialized" (
114 "id" INTEGER PRIMARY KEY NOT NULL,
115 "serialized" text NOT NULL
118 CREATE TABLE "timestamp_primary_key_test" (
119 "id" timestamp NOT NULL DEFAULT current_timestamp,
123 CREATE TABLE "treelike" (
124 "id" INTEGER PRIMARY KEY NOT NULL,
126 "name" varchar(100) NOT NULL,
127 FOREIGN KEY ("parent") REFERENCES "treelike"("id") ON DELETE CASCADE ON UPDATE CASCADE
130 CREATE INDEX "treelike_idx_parent" ON "treelike" ("parent");
132 CREATE TABLE "twokeytreelike" (
133 "id1" integer NOT NULL,
134 "id2" integer NOT NULL,
135 "parent1" integer NOT NULL,
136 "parent2" integer NOT NULL,
137 "name" varchar(100) NOT NULL,
138 PRIMARY KEY ("id1", "id2"),
139 FOREIGN KEY ("parent1", "parent2") REFERENCES "twokeytreelike"("id1", "id2")
142 CREATE INDEX "twokeytreelike_idx_parent1_parent2" ON "twokeytreelike" ("parent1", "parent2");
144 CREATE UNIQUE INDEX "tktlnameunique" ON "twokeytreelike" ("name");
146 CREATE TABLE "typed_object" (
147 "objectid" INTEGER PRIMARY KEY NOT NULL,
148 "type" varchar(100) NOT NULL,
149 "value" varchar(100) NOT NULL
152 CREATE TABLE "artist_undirected_map" (
153 "id1" integer NOT NULL,
154 "id2" integer NOT NULL,
155 PRIMARY KEY ("id1", "id2"),
156 FOREIGN KEY ("id1") REFERENCES "artist"("artistid") ON DELETE RESTRICT ON UPDATE CASCADE,
157 FOREIGN KEY ("id2") REFERENCES "artist"("artistid")
160 CREATE INDEX "artist_undirected_map_idx_id1" ON "artist_undirected_map" ("id1");
162 CREATE INDEX "artist_undirected_map_idx_id2" ON "artist_undirected_map" ("id2");
164 CREATE TABLE "bookmark" (
165 "id" INTEGER PRIMARY KEY NOT NULL,
167 FOREIGN KEY ("link") REFERENCES "link"("id") ON DELETE SET NULL ON UPDATE CASCADE
170 CREATE INDEX "bookmark_idx_link" ON "bookmark" ("link");
172 CREATE TABLE "books" (
173 "id" INTEGER PRIMARY KEY NOT NULL,
174 "source" varchar(100) NOT NULL,
175 "owner" integer NOT NULL,
176 "title" varchar(100) NOT NULL,
178 FOREIGN KEY ("owner") REFERENCES "owners"("id") ON DELETE CASCADE ON UPDATE CASCADE
181 CREATE INDEX "books_idx_owner" ON "books" ("owner");
183 CREATE UNIQUE INDEX "books_title" ON "books" ("title");
185 CREATE TABLE "employee" (
186 "employee_id" INTEGER PRIMARY KEY NOT NULL,
187 "position" integer NOT NULL,
189 "group_id_2" integer,
190 "group_id_3" integer,
193 FOREIGN KEY ("encoded") REFERENCES "encoded"("id") ON DELETE CASCADE ON UPDATE CASCADE
196 CREATE INDEX "employee_idx_encoded" ON "employee" ("encoded");
198 CREATE TABLE "forceforeign" (
199 "artist" INTEGER PRIMARY KEY NOT NULL,
200 "cd" integer NOT NULL,
201 FOREIGN KEY ("artist") REFERENCES "artist"("artistid")
204 CREATE TABLE "self_ref_alias" (
205 "self_ref" integer NOT NULL,
206 "alias" integer NOT NULL,
207 PRIMARY KEY ("self_ref", "alias"),
208 FOREIGN KEY ("alias") REFERENCES "self_ref"("id"),
209 FOREIGN KEY ("self_ref") REFERENCES "self_ref"("id") ON DELETE CASCADE ON UPDATE CASCADE
212 CREATE INDEX "self_ref_alias_idx_alias" ON "self_ref_alias" ("alias");
214 CREATE INDEX "self_ref_alias_idx_self_ref" ON "self_ref_alias" ("self_ref");
216 CREATE TABLE "track" (
217 "trackid" INTEGER PRIMARY KEY NOT NULL,
218 "cd" integer NOT NULL,
219 "position" int NOT NULL,
220 "title" varchar(100) NOT NULL,
221 "last_updated_on" datetime,
222 "last_updated_at" datetime,
223 FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE
226 CREATE INDEX "track_idx_cd" ON "track" ("cd");
228 CREATE UNIQUE INDEX "track_cd_position" ON "track" ("cd", "position");
230 CREATE UNIQUE INDEX "track_cd_title" ON "track" ("cd", "title");
233 "cdid" INTEGER PRIMARY KEY NOT NULL,
234 "artist" integer NOT NULL,
235 "title" varchar(100) NOT NULL,
236 "year" varchar(100) NOT NULL,
238 "single_track" integer,
239 FOREIGN KEY ("artist") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE,
240 FOREIGN KEY ("single_track") REFERENCES "track"("trackid") ON DELETE CASCADE,
241 FOREIGN KEY ("genreid") REFERENCES "genre"("genreid") ON DELETE SET NULL ON UPDATE CASCADE
244 CREATE INDEX "cd_idx_artist" ON "cd" ("artist");
246 CREATE INDEX "cd_idx_single_track" ON "cd" ("single_track");
248 CREATE INDEX "cd_idx_genreid" ON "cd" ("genreid");
250 CREATE UNIQUE INDEX "cd_artist_title" ON "cd" ("artist", "title");
252 CREATE TABLE "collection_object" (
253 "collection" integer NOT NULL,
254 "object" integer NOT NULL,
255 PRIMARY KEY ("collection", "object"),
256 FOREIGN KEY ("collection") REFERENCES "collection"("collectionid") ON DELETE CASCADE ON UPDATE CASCADE,
257 FOREIGN KEY ("object") REFERENCES "typed_object"("objectid") ON DELETE CASCADE ON UPDATE CASCADE
260 CREATE INDEX "collection_object_idx_collection" ON "collection_object" ("collection");
262 CREATE INDEX "collection_object_idx_object" ON "collection_object" ("object");
264 CREATE TABLE "lyrics" (
265 "lyric_id" INTEGER PRIMARY KEY NOT NULL,
266 "track_id" integer NOT NULL,
267 FOREIGN KEY ("track_id") REFERENCES "track"("trackid") ON DELETE CASCADE
270 CREATE INDEX "lyrics_idx_track_id" ON "lyrics" ("track_id");
272 CREATE TABLE "cd_artwork" (
273 "cd_id" INTEGER PRIMARY KEY NOT NULL,
274 FOREIGN KEY ("cd_id") REFERENCES "cd"("cdid") ON DELETE CASCADE
277 CREATE TABLE "liner_notes" (
278 "liner_id" INTEGER PRIMARY KEY NOT NULL,
279 "notes" varchar(100) NOT NULL,
280 FOREIGN KEY ("liner_id") REFERENCES "cd"("cdid") ON DELETE CASCADE
283 CREATE TABLE "lyric_versions" (
284 "id" INTEGER PRIMARY KEY NOT NULL,
285 "lyric_id" integer NOT NULL,
286 "text" varchar(100) NOT NULL,
287 FOREIGN KEY ("lyric_id") REFERENCES "lyrics"("lyric_id") ON DELETE CASCADE ON UPDATE CASCADE
290 CREATE INDEX "lyric_versions_idx_lyric_id" ON "lyric_versions" ("lyric_id");
292 CREATE UNIQUE INDEX "lyric_versions_lyric_id_text" ON "lyric_versions" ("lyric_id", "text");
294 CREATE TABLE "tags" (
295 "tagid" INTEGER PRIMARY KEY NOT NULL,
296 "cd" integer NOT NULL,
297 "tag" varchar(100) NOT NULL,
298 FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE
301 CREATE INDEX "tags_idx_cd" ON "tags" ("cd");
303 CREATE UNIQUE INDEX "tagid_cd" ON "tags" ("tagid", "cd");
305 CREATE UNIQUE INDEX "tagid_cd_tag" ON "tags" ("tagid", "cd", "tag");
307 CREATE UNIQUE INDEX "tags_tagid_tag" ON "tags" ("tagid", "tag");
309 CREATE UNIQUE INDEX "tags_tagid_tag_cd" ON "tags" ("tagid", "tag", "cd");
311 CREATE TABLE "cd_to_producer" (
312 "cd" integer NOT NULL,
313 "producer" integer NOT NULL,
315 PRIMARY KEY ("cd", "producer"),
316 FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE,
317 FOREIGN KEY ("producer") REFERENCES "producer"("producerid")
320 CREATE INDEX "cd_to_producer_idx_cd" ON "cd_to_producer" ("cd");
322 CREATE INDEX "cd_to_producer_idx_producer" ON "cd_to_producer" ("producer");
324 CREATE TABLE "images" (
325 "id" INTEGER PRIMARY KEY NOT NULL,
326 "artwork_id" integer NOT NULL,
327 "name" varchar(100) NOT NULL,
329 FOREIGN KEY ("artwork_id") REFERENCES "cd_artwork"("cd_id") ON DELETE CASCADE ON UPDATE CASCADE
332 CREATE INDEX "images_idx_artwork_id" ON "images" ("artwork_id");
334 CREATE TABLE "twokeys" (
335 "artist" integer NOT NULL,
336 "cd" integer NOT NULL,
337 PRIMARY KEY ("artist", "cd"),
338 FOREIGN KEY ("artist") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE,
339 FOREIGN KEY ("cd") REFERENCES "cd"("cdid")
342 CREATE INDEX "twokeys_idx_artist" ON "twokeys" ("artist");
344 CREATE TABLE "artwork_to_artist" (
345 "artwork_cd_id" integer NOT NULL,
346 "artist_id" integer NOT NULL,
347 PRIMARY KEY ("artwork_cd_id", "artist_id"),
348 FOREIGN KEY ("artist_id") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE,
349 FOREIGN KEY ("artwork_cd_id") REFERENCES "cd_artwork"("cd_id") ON DELETE CASCADE ON UPDATE CASCADE
352 CREATE INDEX "artwork_to_artist_idx_artist_id" ON "artwork_to_artist" ("artist_id");
354 CREATE INDEX "artwork_to_artist_idx_artwork_cd_id" ON "artwork_to_artist" ("artwork_cd_id");
356 CREATE TABLE "fourkeys_to_twokeys" (
357 "f_foo" integer NOT NULL,
358 "f_bar" integer NOT NULL,
359 "f_hello" integer NOT NULL,
360 "f_goodbye" integer NOT NULL,
361 "t_artist" integer NOT NULL,
362 "t_cd" integer NOT NULL,
363 "autopilot" character NOT NULL,
364 "pilot_sequence" integer,
365 PRIMARY KEY ("f_foo", "f_bar", "f_hello", "f_goodbye", "t_artist", "t_cd"),
366 FOREIGN KEY ("f_foo", "f_bar", "f_hello", "f_goodbye") REFERENCES "fourkeys"("foo", "bar", "hello", "goodbye") ON DELETE CASCADE ON UPDATE CASCADE,
367 FOREIGN KEY ("t_artist", "t_cd") REFERENCES "twokeys"("artist", "cd") ON DELETE CASCADE ON UPDATE CASCADE
370 CREATE INDEX "fourkeys_to_twokeys_idx_f_foo_f_bar_f_hello_f_goodbye" ON "fourkeys_to_twokeys" ("f_foo", "f_bar", "f_hello", "f_goodbye");
372 CREATE INDEX "fourkeys_to_twokeys_idx_t_artist_t_cd" ON "fourkeys_to_twokeys" ("t_artist", "t_cd");
374 CREATE VIEW "year2000cds" AS
375 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000";