1 CREATE TABLE "`with` [some] ""quotes""" (
2 "`has` [more] ""quotes""" integer NOT NULL
5 CREATE TABLE "artist" (
6 "artistid" INTEGER PRIMARY KEY NOT NULL,
8 "rank" integer NOT NULL DEFAULT 13,
12 CREATE INDEX "artist_name_hookidx" ON "artist" ("name");
14 CREATE UNIQUE INDEX "artist_name" ON "artist" ("name");
16 CREATE UNIQUE INDEX "u_nullable" ON "artist" ("charfield", "rank");
18 CREATE TABLE "bindtype_test" (
19 "id" INTEGER PRIMARY KEY NOT NULL,
26 CREATE TABLE "collection" (
27 "collectionid" INTEGER PRIMARY KEY NOT NULL,
28 "name" varchar(100) NOT NULL
31 CREATE TABLE "encoded" (
32 "id" INTEGER PRIMARY KEY NOT NULL,
33 "encoded" varchar(100)
36 CREATE TABLE "event" (
37 "id" INTEGER PRIMARY KEY NOT NULL,
38 "starts_at" date NOT NULL,
39 "created_on" timestamp NOT NULL,
40 "varchar_date" varchar(20),
41 "varchar_datetime" varchar(20),
42 "skip_inflation" datetime,
43 "ts_without_tz" datetime
46 CREATE TABLE "fourkeys" (
47 "foo" integer NOT NULL,
48 "bar" integer NOT NULL,
49 "hello" integer NOT NULL,
50 "goodbye" integer NOT NULL,
51 "sensors" character(10) NOT NULL,
53 PRIMARY KEY ("foo", "bar", "hello", "goodbye")
56 CREATE TABLE "genre" (
57 "genreid" INTEGER PRIMARY KEY NOT NULL,
58 "name" varchar(100) NOT NULL
61 CREATE UNIQUE INDEX "genre_name" ON "genre" ("name");
64 "id" INTEGER PRIMARY KEY NOT NULL,
69 CREATE TABLE "money_test" (
70 "id" INTEGER PRIMARY KEY NOT NULL,
74 CREATE TABLE "noprimarykey" (
75 "foo" integer NOT NULL,
76 "bar" integer NOT NULL,
77 "baz" integer NOT NULL
80 CREATE UNIQUE INDEX "foo_bar" ON "noprimarykey" ("foo", "bar");
82 CREATE TABLE "onekey" (
83 "id" INTEGER PRIMARY KEY NOT NULL,
84 "artist" integer NOT NULL,
88 CREATE TABLE "owners" (
89 "id" INTEGER PRIMARY KEY NOT NULL,
90 "name" varchar(100) NOT NULL
93 CREATE UNIQUE INDEX "owners_name" ON "owners" ("name");
95 CREATE TABLE "producer" (
96 "producerid" INTEGER PRIMARY KEY NOT NULL,
97 "name" varchar(100) NOT NULL
100 CREATE UNIQUE INDEX "prod_name" ON "producer" ("name");
102 CREATE TABLE "self_ref" (
103 "id" INTEGER PRIMARY KEY NOT NULL,
104 "name" varchar(100) NOT NULL
107 CREATE TABLE "sequence_test" (
108 "pkid1" integer NOT NULL,
109 "pkid2" integer NOT NULL,
110 "nonpkid" integer NOT NULL,
112 PRIMARY KEY ("pkid1", "pkid2")
115 CREATE TABLE "serialized" (
116 "id" INTEGER PRIMARY KEY NOT NULL,
117 "serialized" text NOT NULL
120 CREATE TABLE "timestamp_primary_key_test" (
121 "id" timestamp NOT NULL DEFAULT current_timestamp,
125 CREATE TABLE "treelike" (
126 "id" INTEGER PRIMARY KEY NOT NULL,
128 "name" varchar(100) NOT NULL,
129 FOREIGN KEY ("parent") REFERENCES "treelike"("id") ON DELETE CASCADE ON UPDATE CASCADE
132 CREATE INDEX "treelike_idx_parent" ON "treelike" ("parent");
134 CREATE TABLE "twokeytreelike" (
135 "id1" integer NOT NULL,
136 "id2" integer NOT NULL,
137 "parent1" integer NOT NULL,
138 "parent2" integer NOT NULL,
139 "name" varchar(100) NOT NULL,
140 PRIMARY KEY ("id1", "id2"),
141 FOREIGN KEY ("parent1", "parent2") REFERENCES "twokeytreelike"("id1", "id2")
144 CREATE INDEX "twokeytreelike_idx_parent1_parent2" ON "twokeytreelike" ("parent1", "parent2");
146 CREATE UNIQUE INDEX "tktlnameunique" ON "twokeytreelike" ("name");
148 CREATE TABLE "typed_object" (
149 "objectid" INTEGER PRIMARY KEY NOT NULL,
150 "type" varchar(100) NOT NULL,
151 "value" varchar(100) NOT NULL
154 CREATE TABLE "artist_undirected_map" (
155 "id1" integer NOT NULL,
156 "id2" integer NOT NULL,
157 PRIMARY KEY ("id1", "id2"),
158 FOREIGN KEY ("id1") REFERENCES "artist"("artistid") ON DELETE RESTRICT ON UPDATE CASCADE,
159 FOREIGN KEY ("id2") REFERENCES "artist"("artistid")
162 CREATE INDEX "artist_undirected_map_idx_id1" ON "artist_undirected_map" ("id1");
164 CREATE INDEX "artist_undirected_map_idx_id2" ON "artist_undirected_map" ("id2");
166 CREATE TABLE "bookmark" (
167 "id" INTEGER PRIMARY KEY NOT NULL,
169 FOREIGN KEY ("link") REFERENCES "link"("id") ON DELETE SET NULL ON UPDATE CASCADE
172 CREATE INDEX "bookmark_idx_link" ON "bookmark" ("link");
174 CREATE TABLE "books" (
175 "id" INTEGER PRIMARY KEY NOT NULL,
176 "source" varchar(100) NOT NULL,
177 "owner" integer NOT NULL,
178 "title" varchar(100) NOT NULL,
180 FOREIGN KEY ("owner") REFERENCES "owners"("id") ON DELETE CASCADE ON UPDATE CASCADE
183 CREATE INDEX "books_idx_owner" ON "books" ("owner");
185 CREATE UNIQUE INDEX "books_title" ON "books" ("title");
187 CREATE TABLE "employee" (
188 "employee_id" INTEGER PRIMARY KEY NOT NULL,
189 "position" integer NOT NULL,
191 "group_id_2" integer,
192 "group_id_3" integer,
195 FOREIGN KEY ("encoded") REFERENCES "encoded"("id") ON DELETE CASCADE ON UPDATE CASCADE
198 CREATE INDEX "employee_idx_encoded" ON "employee" ("encoded");
200 CREATE TABLE "forceforeign" (
201 "artist" INTEGER PRIMARY KEY NOT NULL,
202 "cd" integer NOT NULL,
203 FOREIGN KEY ("artist") REFERENCES "artist"("artistid")
206 CREATE TABLE "self_ref_alias" (
207 "self_ref" integer NOT NULL,
208 "alias" integer NOT NULL,
209 PRIMARY KEY ("self_ref", "alias"),
210 FOREIGN KEY ("alias") REFERENCES "self_ref"("id"),
211 FOREIGN KEY ("self_ref") REFERENCES "self_ref"("id") ON DELETE CASCADE ON UPDATE CASCADE
214 CREATE INDEX "self_ref_alias_idx_alias" ON "self_ref_alias" ("alias");
216 CREATE INDEX "self_ref_alias_idx_self_ref" ON "self_ref_alias" ("self_ref");
218 CREATE TABLE "track" (
219 "trackid" INTEGER PRIMARY KEY NOT NULL,
220 "cd" integer NOT NULL,
221 "position" int NOT NULL,
222 "title" varchar(100) NOT NULL,
223 "last_updated_on" datetime,
224 "last_updated_at" datetime,
225 FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE
228 CREATE INDEX "track_idx_cd" ON "track" ("cd");
230 CREATE UNIQUE INDEX "track_cd_position" ON "track" ("cd", "position");
232 CREATE UNIQUE INDEX "track_cd_title" ON "track" ("cd", "title");
235 "cdid" INTEGER PRIMARY KEY NOT NULL,
236 "artist" integer NOT NULL,
237 "title" varchar(100) NOT NULL,
238 "year" varchar(100) NOT NULL,
240 "single_track" integer,
241 FOREIGN KEY ("artist") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE,
242 FOREIGN KEY ("single_track") REFERENCES "track"("trackid") ON DELETE CASCADE,
243 FOREIGN KEY ("genreid") REFERENCES "genre"("genreid") ON DELETE SET NULL ON UPDATE CASCADE
246 CREATE INDEX "cd_idx_artist" ON "cd" ("artist");
248 CREATE INDEX "cd_idx_single_track" ON "cd" ("single_track");
250 CREATE INDEX "cd_idx_genreid" ON "cd" ("genreid");
252 CREATE UNIQUE INDEX "cd_artist_title" ON "cd" ("artist", "title");
254 CREATE TABLE "collection_object" (
255 "collection" integer NOT NULL,
256 "object" integer NOT NULL,
257 PRIMARY KEY ("collection", "object"),
258 FOREIGN KEY ("collection") REFERENCES "collection"("collectionid") ON DELETE CASCADE ON UPDATE CASCADE,
259 FOREIGN KEY ("object") REFERENCES "typed_object"("objectid") ON DELETE CASCADE ON UPDATE CASCADE
262 CREATE INDEX "collection_object_idx_collection" ON "collection_object" ("collection");
264 CREATE INDEX "collection_object_idx_object" ON "collection_object" ("object");
266 CREATE TABLE "lyrics" (
267 "lyric_id" INTEGER PRIMARY KEY NOT NULL,
268 "track_id" integer NOT NULL,
269 FOREIGN KEY ("track_id") REFERENCES "track"("trackid") ON DELETE CASCADE
272 CREATE INDEX "lyrics_idx_track_id" ON "lyrics" ("track_id");
274 CREATE TABLE "cd_artwork" (
275 "cd_id" INTEGER PRIMARY KEY NOT NULL,
276 FOREIGN KEY ("cd_id") REFERENCES "cd"("cdid") ON DELETE CASCADE
279 CREATE TABLE "liner_notes" (
280 "liner_id" INTEGER PRIMARY KEY NOT NULL,
281 "notes" varchar(100) NOT NULL,
282 FOREIGN KEY ("liner_id") REFERENCES "cd"("cdid") ON DELETE CASCADE
285 CREATE TABLE "lyric_versions" (
286 "id" INTEGER PRIMARY KEY NOT NULL,
287 "lyric_id" integer NOT NULL,
288 "text" varchar(100) NOT NULL,
289 FOREIGN KEY ("lyric_id") REFERENCES "lyrics"("lyric_id") ON DELETE CASCADE ON UPDATE CASCADE
292 CREATE INDEX "lyric_versions_idx_lyric_id" ON "lyric_versions" ("lyric_id");
294 CREATE UNIQUE INDEX "lyric_versions_lyric_id_text" ON "lyric_versions" ("lyric_id", "text");
296 CREATE TABLE "tags" (
297 "tagid" INTEGER PRIMARY KEY NOT NULL,
298 "cd" integer NOT NULL,
299 "tag" varchar(100) NOT NULL,
300 FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE
303 CREATE INDEX "tags_idx_cd" ON "tags" ("cd");
305 CREATE UNIQUE INDEX "tagid_cd" ON "tags" ("tagid", "cd");
307 CREATE UNIQUE INDEX "tagid_cd_tag" ON "tags" ("tagid", "cd", "tag");
309 CREATE UNIQUE INDEX "tags_tagid_tag" ON "tags" ("tagid", "tag");
311 CREATE UNIQUE INDEX "tags_tagid_tag_cd" ON "tags" ("tagid", "tag", "cd");
313 CREATE TABLE "cd_to_producer" (
314 "cd" integer NOT NULL,
315 "producer" integer NOT NULL,
317 PRIMARY KEY ("cd", "producer"),
318 FOREIGN KEY ("cd") REFERENCES "cd"("cdid") ON DELETE CASCADE ON UPDATE CASCADE,
319 FOREIGN KEY ("producer") REFERENCES "producer"("producerid")
322 CREATE INDEX "cd_to_producer_idx_cd" ON "cd_to_producer" ("cd");
324 CREATE INDEX "cd_to_producer_idx_producer" ON "cd_to_producer" ("producer");
326 CREATE TABLE "images" (
327 "id" INTEGER PRIMARY KEY NOT NULL,
328 "artwork_id" integer NOT NULL,
329 "name" varchar(100) NOT NULL,
331 FOREIGN KEY ("artwork_id") REFERENCES "cd_artwork"("cd_id") ON DELETE CASCADE ON UPDATE CASCADE
334 CREATE INDEX "images_idx_artwork_id" ON "images" ("artwork_id");
336 CREATE TABLE "twokeys" (
337 "artist" integer NOT NULL,
338 "cd" integer NOT NULL,
339 PRIMARY KEY ("artist", "cd"),
340 FOREIGN KEY ("artist") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE,
341 FOREIGN KEY ("cd") REFERENCES "cd"("cdid")
344 CREATE INDEX "twokeys_idx_artist" ON "twokeys" ("artist");
346 CREATE TABLE "artwork_to_artist" (
347 "artwork_cd_id" integer NOT NULL,
348 "artist_id" integer NOT NULL,
349 PRIMARY KEY ("artwork_cd_id", "artist_id"),
350 FOREIGN KEY ("artist_id") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE,
351 FOREIGN KEY ("artwork_cd_id") REFERENCES "cd_artwork"("cd_id") ON DELETE CASCADE ON UPDATE CASCADE
354 CREATE INDEX "artwork_to_artist_idx_artist_id" ON "artwork_to_artist" ("artist_id");
356 CREATE INDEX "artwork_to_artist_idx_artwork_cd_id" ON "artwork_to_artist" ("artwork_cd_id");
358 CREATE TABLE "fourkeys_to_twokeys" (
359 "f_foo" integer NOT NULL,
360 "f_bar" integer NOT NULL,
361 "f_hello" integer NOT NULL,
362 "f_goodbye" integer NOT NULL,
363 "t_artist" integer NOT NULL,
364 "t_cd" integer NOT NULL,
365 "autopilot" character NOT NULL,
366 "pilot_sequence" integer,
367 PRIMARY KEY ("f_foo", "f_bar", "f_hello", "f_goodbye", "t_artist", "t_cd"),
368 FOREIGN KEY ("f_foo", "f_bar", "f_hello", "f_goodbye") REFERENCES "fourkeys"("foo", "bar", "hello", "goodbye") ON DELETE CASCADE ON UPDATE CASCADE,
369 FOREIGN KEY ("t_artist", "t_cd") REFERENCES "twokeys"("artist", "cd") ON DELETE CASCADE ON UPDATE CASCADE
372 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");
374 CREATE INDEX "fourkeys_to_twokeys_idx_t_artist_t_cd" ON "fourkeys_to_twokeys" ("t_artist", "t_cd");
376 CREATE VIEW "year2000cds" AS
377 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000";