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,
22 CREATE TABLE "collection" (
23 "collectionid" INTEGER PRIMARY KEY NOT NULL,
24 "name" varchar(100) NOT NULL
27 CREATE TABLE "encoded" (
28 "id" INTEGER PRIMARY KEY NOT NULL,
29 "encoded" varchar(100)
32 CREATE TABLE "event" (
33 "id" INTEGER PRIMARY KEY NOT NULL,
34 "starts_at" date NOT NULL,
35 "created_on" timestamp NOT NULL,
36 "varchar_date" varchar(20),
37 "varchar_datetime" varchar(20),
38 "skip_inflation" datetime,
39 "ts_without_tz" datetime
42 CREATE TABLE "fourkeys" (
43 "foo" integer NOT NULL,
44 "bar" integer NOT NULL,
45 "hello" integer NOT NULL,
46 "goodbye" integer NOT NULL,
47 "sensors" character(10) NOT NULL,
49 PRIMARY KEY ("foo", "bar", "hello", "goodbye")
52 CREATE TABLE "genre" (
53 "genreid" INTEGER PRIMARY KEY NOT NULL,
54 "name" varchar(100) NOT NULL
57 CREATE UNIQUE INDEX "genre_name" ON "genre" ("name");
60 "id" INTEGER PRIMARY KEY NOT NULL,
65 CREATE TABLE "money_test" (
66 "id" INTEGER PRIMARY KEY NOT NULL,
70 CREATE TABLE "noprimarykey" (
71 "foo" integer NOT NULL,
72 "bar" integer NOT NULL,
73 "baz" integer NOT NULL
76 CREATE UNIQUE INDEX "foo_bar" ON "noprimarykey" ("foo", "bar");
78 CREATE TABLE "onekey" (
79 "id" INTEGER PRIMARY KEY NOT NULL,
80 "artist" integer NOT NULL,
84 CREATE TABLE "owners" (
85 "id" INTEGER PRIMARY KEY NOT NULL,
86 "name" varchar(100) NOT NULL
89 CREATE UNIQUE INDEX "owners_name" ON "owners" ("name");
91 CREATE TABLE "producer" (
92 "producerid" INTEGER PRIMARY KEY NOT NULL,
93 "name" varchar(100) NOT NULL
96 CREATE UNIQUE INDEX "prod_name" ON "producer" ("name");
98 CREATE TABLE "self_ref" (
99 "id" INTEGER PRIMARY KEY NOT NULL,
100 "name" varchar(100) NOT NULL
103 CREATE TABLE "sequence_test" (
104 "pkid1" integer NOT NULL,
105 "pkid2" integer NOT NULL,
106 "nonpkid" integer NOT NULL,
108 PRIMARY KEY ("pkid1", "pkid2")
111 CREATE TABLE "serialized" (
112 "id" INTEGER PRIMARY KEY NOT NULL,
113 "serialized" text NOT NULL
116 CREATE TABLE "timestamp_primary_key_test" (
117 "id" timestamp NOT NULL DEFAULT current_timestamp,
121 CREATE TABLE "treelike" (
122 "id" INTEGER PRIMARY KEY NOT NULL,
124 "name" varchar(100) NOT NULL,
125 FOREIGN KEY ("parent") REFERENCES "treelike"("id") ON DELETE CASCADE ON UPDATE CASCADE
128 CREATE INDEX "treelike_idx_parent" ON "treelike" ("parent");
130 CREATE TABLE "twokeytreelike" (
131 "id1" integer NOT NULL,
132 "id2" integer NOT NULL,
133 "parent1" integer NOT NULL,
134 "parent2" integer NOT NULL,
135 "name" varchar(100) NOT NULL,
136 PRIMARY KEY ("id1", "id2"),
137 FOREIGN KEY ("parent1", "parent2") REFERENCES "twokeytreelike"("id1", "id2")
140 CREATE INDEX "twokeytreelike_idx_parent1_parent2" ON "twokeytreelike" ("parent1", "parent2");
142 CREATE UNIQUE INDEX "tktlnameunique" ON "twokeytreelike" ("name");
144 CREATE TABLE "typed_object" (
145 "objectid" INTEGER PRIMARY KEY NOT NULL,
146 "type" varchar(100) NOT NULL,
147 "value" varchar(100) NOT NULL
150 CREATE TABLE "artist_undirected_map" (
151 "id1" integer NOT NULL,
152 "id2" integer NOT NULL,
153 PRIMARY KEY ("id1", "id2"),
154 FOREIGN KEY ("id1") REFERENCES "artist"("artistid") ON DELETE RESTRICT ON UPDATE CASCADE,
155 FOREIGN KEY ("id2") REFERENCES "artist"("artistid")
158 CREATE INDEX "artist_undirected_map_idx_id1" ON "artist_undirected_map" ("id1");
160 CREATE INDEX "artist_undirected_map_idx_id2" ON "artist_undirected_map" ("id2");
162 CREATE TABLE "bookmark" (
163 "id" INTEGER PRIMARY KEY NOT NULL,
165 FOREIGN KEY ("link") REFERENCES "link"("id") ON DELETE SET NULL ON UPDATE CASCADE
168 CREATE INDEX "bookmark_idx_link" ON "bookmark" ("link");
170 CREATE TABLE "books" (
171 "id" INTEGER PRIMARY KEY NOT NULL,
172 "source" varchar(100) NOT NULL,
173 "owner" integer NOT NULL,
174 "title" varchar(100) NOT NULL,
176 FOREIGN KEY ("owner") REFERENCES "owners"("id") ON DELETE CASCADE ON UPDATE CASCADE
179 CREATE INDEX "books_idx_owner" ON "books" ("owner");
181 CREATE UNIQUE INDEX "books_title" ON "books" ("title");
183 CREATE TABLE "employee" (
184 "employee_id" INTEGER PRIMARY KEY NOT NULL,
185 "position" integer NOT NULL,
187 "group_id_2" integer,
188 "group_id_3" integer,
191 FOREIGN KEY ("encoded") REFERENCES "encoded"("id") ON DELETE CASCADE ON UPDATE CASCADE
194 CREATE INDEX "employee_idx_encoded" ON "employee" ("encoded");
196 CREATE TABLE "forceforeign" (
197 "artist" INTEGER PRIMARY KEY NOT NULL,
198 "cd" integer NOT NULL,
199 FOREIGN KEY ("artist") REFERENCES "artist"("artistid")
202 CREATE TABLE "self_ref_alias" (
203 "self_ref" integer NOT NULL,
204 "alias" integer NOT NULL,
205 PRIMARY KEY ("self_ref", "alias"),
206 FOREIGN KEY ("alias") REFERENCES "self_ref"("id"),
207 FOREIGN KEY ("self_ref") REFERENCES "self_ref"("id") ON DELETE CASCADE ON UPDATE CASCADE
210 CREATE INDEX "self_ref_alias_idx_alias" ON "self_ref_alias" ("alias");
212 CREATE INDEX "self_ref_alias_idx_self_ref" ON "self_ref_alias" ("self_ref");
214 CREATE TABLE "track" (
215 "trackid" INTEGER PRIMARY KEY NOT NULL,
216 "cd" integer NOT NULL,
217 "position" int NOT NULL,
218 "title" varchar(100) NOT NULL,
219 "last_updated_on" datetime,
220 "last_updated_at" datetime,
221 FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE
224 CREATE INDEX "track_idx_cd" ON "track" ("cd");
226 CREATE UNIQUE INDEX "track_cd_position" ON "track" ("cd", "position");
228 CREATE UNIQUE INDEX "track_cd_title" ON "track" ("cd", "title");
231 "cdid" INTEGER PRIMARY KEY NOT NULL,
232 "artist" integer NOT NULL,
233 "title" varchar(100) NOT NULL,
234 "year" varchar(100) NOT NULL,
236 "single_track" integer,
237 FOREIGN KEY ("artist") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE,
238 FOREIGN KEY ("genreid") REFERENCES "genre"("genreid") ON DELETE SET NULL ON UPDATE CASCADE,
239 FOREIGN KEY ("single_track") REFERENCES "track"("trackid") ON DELETE CASCADE
242 CREATE INDEX "cd_idx_artist" ON "cd" ("artist");
244 CREATE INDEX "cd_idx_genreid" ON "cd" ("genreid");
246 CREATE INDEX "cd_idx_single_track" ON "cd" ("single_track");
248 CREATE UNIQUE INDEX "cd_artist_title" ON "cd" ("artist", "title");
250 CREATE TABLE "collection_object" (
251 "collection" integer NOT NULL,
252 "object" integer NOT NULL,
253 PRIMARY KEY ("collection", "object"),
254 FOREIGN KEY ("collection") REFERENCES "collection"("collectionid") ON DELETE CASCADE ON UPDATE CASCADE,
255 FOREIGN KEY ("object") REFERENCES "typed_object"("objectid") ON DELETE CASCADE ON UPDATE CASCADE
258 CREATE INDEX "collection_object_idx_collection" ON "collection_object" ("collection");
260 CREATE INDEX "collection_object_idx_object" ON "collection_object" ("object");
262 CREATE TABLE "lyrics" (
263 "lyric_id" INTEGER PRIMARY KEY NOT NULL,
264 "track_id" integer NOT NULL,
265 FOREIGN KEY ("track_id") REFERENCES "track"("trackid") ON DELETE CASCADE
268 CREATE INDEX "lyrics_idx_track_id" ON "lyrics" ("track_id");
270 CREATE TABLE "cd_artwork" (
271 "cd_id" INTEGER PRIMARY KEY NOT NULL,
272 FOREIGN KEY ("cd_id") REFERENCES "cd"("cdid") ON DELETE CASCADE
275 CREATE TABLE "liner_notes" (
276 "liner_id" INTEGER PRIMARY KEY NOT NULL,
277 "notes" varchar(100) NOT NULL,
278 FOREIGN KEY ("liner_id") REFERENCES "cd"("cdid") ON DELETE CASCADE
281 CREATE TABLE "lyric_versions" (
282 "id" INTEGER PRIMARY KEY NOT NULL,
283 "lyric_id" integer NOT NULL,
284 "text" varchar(100) NOT NULL,
285 FOREIGN KEY ("lyric_id") REFERENCES "lyrics"("lyric_id") ON DELETE CASCADE ON UPDATE CASCADE
288 CREATE INDEX "lyric_versions_idx_lyric_id" ON "lyric_versions" ("lyric_id");
290 CREATE TABLE "tags" (
291 "tagid" INTEGER PRIMARY KEY NOT NULL,
292 "cd" integer NOT NULL,
293 "tag" varchar(100) NOT NULL,
294 FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE
297 CREATE INDEX "tags_idx_cd" ON "tags" ("cd");
299 CREATE UNIQUE INDEX "tagid_cd" ON "tags" ("tagid", "cd");
301 CREATE UNIQUE INDEX "tagid_cd_tag" ON "tags" ("tagid", "cd", "tag");
303 CREATE UNIQUE INDEX "tags_tagid_tag" ON "tags" ("tagid", "tag");
305 CREATE UNIQUE INDEX "tags_tagid_tag_cd" ON "tags" ("tagid", "tag", "cd");
307 CREATE TABLE "cd_to_producer" (
308 "cd" integer NOT NULL,
309 "producer" integer NOT NULL,
311 PRIMARY KEY ("cd", "producer"),
312 FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE,
313 FOREIGN KEY ("producer") REFERENCES "producer"("producerid")
316 CREATE INDEX "cd_to_producer_idx_cd" ON "cd_to_producer" ("cd");
318 CREATE INDEX "cd_to_producer_idx_producer" ON "cd_to_producer" ("producer");
320 CREATE TABLE "images" (
321 "id" INTEGER PRIMARY KEY NOT NULL,
322 "artwork_id" integer NOT NULL,
323 "name" varchar(100) NOT NULL,
325 FOREIGN KEY ("artwork_id") REFERENCES "cd_artwork"("cd_id") ON DELETE CASCADE ON UPDATE CASCADE
328 CREATE INDEX "images_idx_artwork_id" ON "images" ("artwork_id");
330 CREATE TABLE "twokeys" (
331 "artist" integer NOT NULL,
332 "cd" integer NOT NULL,
333 PRIMARY KEY ("artist", "cd"),
334 FOREIGN KEY ("artist") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE,
335 FOREIGN KEY ("cd") REFERENCES "cd"("cdid")
338 CREATE INDEX "twokeys_idx_artist" ON "twokeys" ("artist");
340 CREATE TABLE "artwork_to_artist" (
341 "artwork_cd_id" integer NOT NULL,
342 "artist_id" integer NOT NULL,
343 PRIMARY KEY ("artwork_cd_id", "artist_id"),
344 FOREIGN KEY ("artist_id") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE,
345 FOREIGN KEY ("artwork_cd_id") REFERENCES "cd_artwork"("cd_id") ON DELETE CASCADE ON UPDATE CASCADE
348 CREATE INDEX "artwork_to_artist_idx_artist_id" ON "artwork_to_artist" ("artist_id");
350 CREATE INDEX "artwork_to_artist_idx_artwork_cd_id" ON "artwork_to_artist" ("artwork_cd_id");
352 CREATE TABLE "fourkeys_to_twokeys" (
353 "f_foo" integer NOT NULL,
354 "f_bar" integer NOT NULL,
355 "f_hello" integer NOT NULL,
356 "f_goodbye" integer NOT NULL,
357 "t_artist" integer NOT NULL,
358 "t_cd" integer NOT NULL,
359 "autopilot" character NOT NULL,
360 "pilot_sequence" integer,
361 PRIMARY KEY ("f_foo", "f_bar", "f_hello", "f_goodbye", "t_artist", "t_cd"),
362 FOREIGN KEY ("f_foo", "f_bar", "f_hello", "f_goodbye") REFERENCES "fourkeys"("foo", "bar", "hello", "goodbye") ON DELETE CASCADE ON UPDATE CASCADE,
363 FOREIGN KEY ("t_artist", "t_cd") REFERENCES "twokeys"("artist", "cd") ON DELETE CASCADE ON UPDATE CASCADE
366 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");
368 CREATE INDEX "fourkeys_to_twokeys_idx_t_artist_t_cd" ON "fourkeys_to_twokeys" ("t_artist", "t_cd");
370 CREATE VIEW "year2000cds" AS
371 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000";