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 ("single_track") REFERENCES "track"("trackid") ON DELETE CASCADE,
239 FOREIGN KEY ("genreid") REFERENCES "genre"("genreid") ON DELETE SET NULL ON UPDATE CASCADE
242 CREATE INDEX "cd_idx_artist" ON "cd" ("artist");
244 CREATE INDEX "cd_idx_single_track" ON "cd" ("single_track");
246 CREATE INDEX "cd_idx_genreid" ON "cd" ("genreid");
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 UNIQUE INDEX "lyric_versions_lyric_id_text" ON "lyric_versions" ("lyric_id", "text");
292 CREATE TABLE "tags" (
293 "tagid" INTEGER PRIMARY KEY NOT NULL,
294 "cd" integer NOT NULL,
295 "tag" varchar(100) NOT NULL,
296 FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE
299 CREATE INDEX "tags_idx_cd" ON "tags" ("cd");
301 CREATE UNIQUE INDEX "tagid_cd" ON "tags" ("tagid", "cd");
303 CREATE UNIQUE INDEX "tagid_cd_tag" ON "tags" ("tagid", "cd", "tag");
305 CREATE UNIQUE INDEX "tags_tagid_tag" ON "tags" ("tagid", "tag");
307 CREATE UNIQUE INDEX "tags_tagid_tag_cd" ON "tags" ("tagid", "tag", "cd");
309 CREATE TABLE "cd_to_producer" (
310 "cd" integer NOT NULL,
311 "producer" integer NOT NULL,
313 PRIMARY KEY ("cd", "producer"),
314 FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE,
315 FOREIGN KEY ("producer") REFERENCES "producer"("producerid")
318 CREATE INDEX "cd_to_producer_idx_cd" ON "cd_to_producer" ("cd");
320 CREATE INDEX "cd_to_producer_idx_producer" ON "cd_to_producer" ("producer");
322 CREATE TABLE "images" (
323 "id" INTEGER PRIMARY KEY NOT NULL,
324 "artwork_id" integer NOT NULL,
325 "name" varchar(100) NOT NULL,
327 FOREIGN KEY ("artwork_id") REFERENCES "cd_artwork"("cd_id") ON DELETE CASCADE ON UPDATE CASCADE
330 CREATE INDEX "images_idx_artwork_id" ON "images" ("artwork_id");
332 CREATE TABLE "twokeys" (
333 "artist" integer NOT NULL,
334 "cd" integer NOT NULL,
335 PRIMARY KEY ("artist", "cd"),
336 FOREIGN KEY ("artist") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE,
337 FOREIGN KEY ("cd") REFERENCES "cd"("cdid")
340 CREATE INDEX "twokeys_idx_artist" ON "twokeys" ("artist");
342 CREATE TABLE "artwork_to_artist" (
343 "artwork_cd_id" integer NOT NULL,
344 "artist_id" integer NOT NULL,
345 PRIMARY KEY ("artwork_cd_id", "artist_id"),
346 FOREIGN KEY ("artist_id") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE,
347 FOREIGN KEY ("artwork_cd_id") REFERENCES "cd_artwork"("cd_id") ON DELETE CASCADE ON UPDATE CASCADE
350 CREATE INDEX "artwork_to_artist_idx_artist_id" ON "artwork_to_artist" ("artist_id");
352 CREATE INDEX "artwork_to_artist_idx_artwork_cd_id" ON "artwork_to_artist" ("artwork_cd_id");
354 CREATE TABLE "fourkeys_to_twokeys" (
355 "f_foo" integer NOT NULL,
356 "f_bar" integer NOT NULL,
357 "f_hello" integer NOT NULL,
358 "f_goodbye" integer NOT NULL,
359 "t_artist" integer NOT NULL,
360 "t_cd" integer NOT NULL,
361 "autopilot" character NOT NULL,
362 "pilot_sequence" integer,
363 PRIMARY KEY ("f_foo", "f_bar", "f_hello", "f_goodbye", "t_artist", "t_cd"),
364 FOREIGN KEY ("f_foo", "f_bar", "f_hello", "f_goodbye") REFERENCES "fourkeys"("foo", "bar", "hello", "goodbye") ON DELETE CASCADE ON UPDATE CASCADE,
365 FOREIGN KEY ("t_artist", "t_cd") REFERENCES "twokeys"("artist", "cd") ON DELETE CASCADE ON UPDATE CASCADE
368 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");
370 CREATE INDEX "fourkeys_to_twokeys_idx_t_artist_t_cd" ON "fourkeys_to_twokeys" ("t_artist", "t_cd");
372 CREATE VIEW "year2000cds" AS
373 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = '2000';