Commit | Line | Data |
4cd3d124 |
1 | CREATE TABLE "artist" ( |
2 | "artistid" INTEGER PRIMARY KEY NOT NULL, |
3 | "name" varchar(100), |
4 | "rank" integer NOT NULL DEFAULT 13, |
5 | "charfield" char(10) |
6 | ); |
7 | |
8 | CREATE INDEX "artist_name_hookidx" ON "artist" ("name"); |
9 | |
10 | CREATE UNIQUE INDEX "artist_name" ON "artist" ("name"); |
11 | |
12 | CREATE UNIQUE INDEX "u_nullable" ON "artist" ("charfield", "rank"); |
13 | |
14 | CREATE TABLE "bindtype_test" ( |
15 | "id" INTEGER PRIMARY KEY NOT NULL, |
16 | "bytea" blob, |
17 | "blob" blob, |
74b5397c |
18 | "blob2" blob, |
4cd3d124 |
19 | "clob" clob, |
74b5397c |
20 | "clob2" clob, |
4cd3d124 |
21 | "a_memo" memo |
22 | ); |
23 | |
24 | CREATE TABLE "collection" ( |
25 | "collectionid" INTEGER PRIMARY KEY NOT NULL, |
26 | "name" varchar(100) NOT NULL |
27 | ); |
ebf846e8 |
28 | |
4cd3d124 |
29 | CREATE TABLE "encoded" ( |
30 | "id" INTEGER PRIMARY KEY NOT NULL, |
31 | "encoded" varchar(100) |
887ce227 |
32 | ); |
33 | |
4cd3d124 |
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 |
9fcda149 |
42 | ); |
4cd3d124 |
43 | |
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, |
50 | "read_count" int, |
51 | PRIMARY KEY ("foo", "bar", "hello", "goodbye") |
52 | ); |
53 | |
54 | CREATE TABLE "genre" ( |
55 | "genreid" INTEGER PRIMARY KEY NOT NULL, |
56 | "name" varchar(100) NOT NULL |
57 | ); |
ebf846e8 |
58 | |
4cd3d124 |
59 | CREATE UNIQUE INDEX "genre_name" ON "genre" ("name"); |
60 | |
61 | CREATE TABLE "link" ( |
62 | "id" INTEGER PRIMARY KEY NOT NULL, |
63 | "url" varchar(100), |
64 | "title" varchar(100) |
65 | ); |
66 | |
67 | CREATE TABLE "money_test" ( |
68 | "id" INTEGER PRIMARY KEY NOT NULL, |
69 | "amount" money |
70 | ); |
71 | |
72 | CREATE TABLE "noprimarykey" ( |
73 | "foo" integer NOT NULL, |
74 | "bar" integer NOT NULL, |
75 | "baz" integer NOT NULL |
76 | ); |
77 | |
78 | CREATE UNIQUE INDEX "foo_bar" ON "noprimarykey" ("foo", "bar"); |
79 | |
80 | CREATE TABLE "onekey" ( |
81 | "id" INTEGER PRIMARY KEY NOT NULL, |
82 | "artist" integer NOT NULL, |
83 | "cd" integer NOT NULL |
887ce227 |
84 | ); |
85 | |
4cd3d124 |
86 | CREATE TABLE "owners" ( |
87 | "id" INTEGER PRIMARY KEY NOT NULL, |
88 | "name" varchar(100) NOT NULL |
9fcda149 |
89 | ); |
4cd3d124 |
90 | |
91 | CREATE UNIQUE INDEX "owners_name" ON "owners" ("name"); |
92 | |
93 | CREATE TABLE "producer" ( |
94 | "producerid" INTEGER PRIMARY KEY NOT NULL, |
95 | "name" varchar(100) NOT NULL |
96 | ); |
97 | |
98 | CREATE UNIQUE INDEX "prod_name" ON "producer" ("name"); |
99 | |
100 | CREATE TABLE "self_ref" ( |
101 | "id" INTEGER PRIMARY KEY NOT NULL, |
102 | "name" varchar(100) NOT NULL |
103 | ); |
104 | |
105 | CREATE TABLE "sequence_test" ( |
106 | "pkid1" integer NOT NULL, |
107 | "pkid2" integer NOT NULL, |
108 | "nonpkid" integer NOT NULL, |
109 | "name" varchar(100), |
110 | PRIMARY KEY ("pkid1", "pkid2") |
78060df8 |
111 | ); |
112 | |
4cd3d124 |
113 | CREATE TABLE "serialized" ( |
114 | "id" INTEGER PRIMARY KEY NOT NULL, |
115 | "serialized" text NOT NULL |
78060df8 |
116 | ); |
117 | |
4cd3d124 |
118 | CREATE TABLE "timestamp_primary_key_test" ( |
119 | "id" timestamp NOT NULL DEFAULT current_timestamp, |
120 | PRIMARY KEY ("id") |
4d4dc518 |
121 | ); |
122 | |
4cd3d124 |
123 | CREATE TABLE "treelike" ( |
124 | "id" INTEGER PRIMARY KEY NOT NULL, |
125 | "parent" integer, |
126 | "name" varchar(100) NOT NULL, |
127 | FOREIGN KEY ("parent") REFERENCES "treelike"("id") ON DELETE CASCADE ON UPDATE CASCADE |
eb0bc670 |
128 | ); |
129 | |
4cd3d124 |
130 | CREATE INDEX "treelike_idx_parent" ON "treelike" ("parent"); |
eb0bc670 |
131 | |
4cd3d124 |
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") |
eb0bc670 |
140 | ); |
141 | |
4cd3d124 |
142 | CREATE INDEX "twokeytreelike_idx_parent1_parent2" ON "twokeytreelike" ("parent1", "parent2"); |
eb0bc670 |
143 | |
4cd3d124 |
144 | CREATE UNIQUE INDEX "tktlnameunique" ON "twokeytreelike" ("name"); |
eb0bc670 |
145 | |
4cd3d124 |
146 | CREATE TABLE "typed_object" ( |
147 | "objectid" INTEGER PRIMARY KEY NOT NULL, |
148 | "type" varchar(100) NOT NULL, |
149 | "value" varchar(100) NOT NULL |
eb0bc670 |
150 | ); |
151 | |
4cd3d124 |
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") |
eb0bc670 |
158 | ); |
159 | |
4cd3d124 |
160 | CREATE INDEX "artist_undirected_map_idx_id1" ON "artist_undirected_map" ("id1"); |
eb0bc670 |
161 | |
4cd3d124 |
162 | CREATE INDEX "artist_undirected_map_idx_id2" ON "artist_undirected_map" ("id2"); |
eb0bc670 |
163 | |
4cd3d124 |
164 | CREATE TABLE "bookmark" ( |
165 | "id" INTEGER PRIMARY KEY NOT NULL, |
166 | "link" integer, |
167 | FOREIGN KEY ("link") REFERENCES "link"("id") ON DELETE SET NULL ON UPDATE CASCADE |
eb0bc670 |
168 | ); |
169 | |
4cd3d124 |
170 | CREATE INDEX "bookmark_idx_link" ON "bookmark" ("link"); |
eb0bc670 |
171 | |
4cd3d124 |
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, |
177 | "price" integer, |
178 | FOREIGN KEY ("owner") REFERENCES "owners"("id") ON DELETE CASCADE ON UPDATE CASCADE |
eb0bc670 |
179 | ); |
180 | |
4cd3d124 |
181 | CREATE INDEX "books_idx_owner" ON "books" ("owner"); |
eb0bc670 |
182 | |
4cd3d124 |
183 | CREATE UNIQUE INDEX "books_title" ON "books" ("title"); |
8c7b6644 |
184 | |
4cd3d124 |
185 | CREATE TABLE "employee" ( |
186 | "employee_id" INTEGER PRIMARY KEY NOT NULL, |
187 | "position" integer NOT NULL, |
188 | "group_id" integer, |
189 | "group_id_2" integer, |
190 | "group_id_3" integer, |
191 | "name" varchar(100), |
192 | "encoded" integer, |
193 | FOREIGN KEY ("encoded") REFERENCES "encoded"("id") ON DELETE CASCADE ON UPDATE CASCADE |
68888c09 |
194 | ); |
195 | |
4cd3d124 |
196 | CREATE INDEX "employee_idx_encoded" ON "employee" ("encoded"); |
68888c09 |
197 | |
4cd3d124 |
198 | CREATE TABLE "forceforeign" ( |
199 | "artist" INTEGER PRIMARY KEY NOT NULL, |
200 | "cd" integer NOT NULL, |
201 | FOREIGN KEY ("artist") REFERENCES "artist"("artistid") |
eb0bc670 |
202 | ); |
203 | |
4cd3d124 |
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 |
78060df8 |
210 | ); |
211 | |
4cd3d124 |
212 | CREATE INDEX "self_ref_alias_idx_alias" ON "self_ref_alias" ("alias"); |
eb0bc670 |
213 | |
4cd3d124 |
214 | CREATE INDEX "self_ref_alias_idx_self_ref" ON "self_ref_alias" ("self_ref"); |
ebf846e8 |
215 | |
4cd3d124 |
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 |
ebf846e8 |
224 | ); |
225 | |
4cd3d124 |
226 | CREATE INDEX "track_idx_cd" ON "track" ("cd"); |
084a2c0a |
227 | |
4cd3d124 |
228 | CREATE UNIQUE INDEX "track_cd_position" ON "track" ("cd", "position"); |
084a2c0a |
229 | |
4cd3d124 |
230 | CREATE UNIQUE INDEX "track_cd_title" ON "track" ("cd", "title"); |
ebf846e8 |
231 | |
4cd3d124 |
232 | CREATE TABLE "cd" ( |
233 | "cdid" INTEGER PRIMARY KEY NOT NULL, |
234 | "artist" integer NOT NULL, |
235 | "title" varchar(100) NOT NULL, |
236 | "year" varchar(100) NOT NULL, |
237 | "genreid" integer, |
238 | "single_track" integer, |
239 | FOREIGN KEY ("artist") REFERENCES "artist"("artistid") ON DELETE CASCADE ON UPDATE CASCADE, |
0077982b |
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 |
eb0bc670 |
242 | ); |
243 | |
4cd3d124 |
244 | CREATE INDEX "cd_idx_artist" ON "cd" ("artist"); |
eb0bc670 |
245 | |
4cd3d124 |
246 | CREATE INDEX "cd_idx_single_track" ON "cd" ("single_track"); |
eb0bc670 |
247 | |
0077982b |
248 | CREATE INDEX "cd_idx_genreid" ON "cd" ("genreid"); |
eb0bc670 |
249 | |
4cd3d124 |
250 | CREATE UNIQUE INDEX "cd_artist_title" ON "cd" ("artist", "title"); |
eb0bc670 |
251 | |
4cd3d124 |
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 |
eb0bc670 |
258 | ); |
259 | |
4cd3d124 |
260 | CREATE INDEX "collection_object_idx_collection" ON "collection_object" ("collection"); |
eb0bc670 |
261 | |
4cd3d124 |
262 | CREATE INDEX "collection_object_idx_object" ON "collection_object" ("object"); |
eb0bc670 |
263 | |
4cd3d124 |
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 |
eb0bc670 |
268 | ); |
269 | |
4cd3d124 |
270 | CREATE INDEX "lyrics_idx_track_id" ON "lyrics" ("track_id"); |
eb0bc670 |
271 | |
4cd3d124 |
272 | CREATE TABLE "cd_artwork" ( |
273 | "cd_id" INTEGER PRIMARY KEY NOT NULL, |
274 | FOREIGN KEY ("cd_id") REFERENCES "cd"("cdid") ON DELETE CASCADE |
eb0bc670 |
275 | ); |
276 | |
4cd3d124 |
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 |
eb0bc670 |
281 | ); |
282 | |
4cd3d124 |
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 |
ebf846e8 |
288 | ); |
289 | |
4cd3d124 |
290 | CREATE INDEX "lyric_versions_idx_lyric_id" ON "lyric_versions" ("lyric_id"); |
ebf846e8 |
291 | |
0077982b |
292 | CREATE UNIQUE INDEX "lyric_versions_lyric_id_text" ON "lyric_versions" ("lyric_id", "text"); |
ebf846e8 |
293 | |
4cd3d124 |
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 |
eb0bc670 |
299 | ); |
300 | |
4cd3d124 |
301 | CREATE INDEX "tags_idx_cd" ON "tags" ("cd"); |
eb0bc670 |
302 | |
4cd3d124 |
303 | CREATE UNIQUE INDEX "tagid_cd" ON "tags" ("tagid", "cd"); |
84f7e8a1 |
304 | |
4cd3d124 |
305 | CREATE UNIQUE INDEX "tagid_cd_tag" ON "tags" ("tagid", "cd", "tag"); |
84f7e8a1 |
306 | |
4cd3d124 |
307 | CREATE UNIQUE INDEX "tags_tagid_tag" ON "tags" ("tagid", "tag"); |
84f7e8a1 |
308 | |
4cd3d124 |
309 | CREATE UNIQUE INDEX "tags_tagid_tag_cd" ON "tags" ("tagid", "tag", "cd"); |
84f7e8a1 |
310 | |
4cd3d124 |
311 | CREATE TABLE "cd_to_producer" ( |
312 | "cd" integer NOT NULL, |
313 | "producer" integer NOT NULL, |
314 | "attribute" integer, |
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") |
ebf846e8 |
318 | ); |
319 | |
4cd3d124 |
320 | CREATE INDEX "cd_to_producer_idx_cd" ON "cd_to_producer" ("cd"); |
eb0bc670 |
321 | |
4cd3d124 |
322 | CREATE INDEX "cd_to_producer_idx_producer" ON "cd_to_producer" ("producer"); |
eb0bc670 |
323 | |
4cd3d124 |
324 | CREATE TABLE "images" ( |
325 | "id" INTEGER PRIMARY KEY NOT NULL, |
326 | "artwork_id" integer NOT NULL, |
327 | "name" varchar(100) NOT NULL, |
328 | "data" blob, |
329 | FOREIGN KEY ("artwork_id") REFERENCES "cd_artwork"("cd_id") ON DELETE CASCADE ON UPDATE CASCADE |
eb0bc670 |
330 | ); |
084a2c0a |
331 | |
4cd3d124 |
332 | CREATE INDEX "images_idx_artwork_id" ON "images" ("artwork_id"); |
ebf846e8 |
333 | |
4cd3d124 |
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") |
ebf846e8 |
340 | ); |
341 | |
4cd3d124 |
342 | CREATE INDEX "twokeys_idx_artist" ON "twokeys" ("artist"); |
ebf846e8 |
343 | |
4cd3d124 |
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 |
78060df8 |
350 | ); |
351 | |
4cd3d124 |
352 | CREATE INDEX "artwork_to_artist_idx_artist_id" ON "artwork_to_artist" ("artist_id"); |
eb0bc670 |
353 | |
4cd3d124 |
354 | CREATE INDEX "artwork_to_artist_idx_artwork_cd_id" ON "artwork_to_artist" ("artwork_cd_id"); |
eb0bc670 |
355 | |
4cd3d124 |
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 |
eb0bc670 |
368 | ); |
369 | |
4cd3d124 |
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"); |
eb0bc670 |
371 | |
4cd3d124 |
372 | CREATE INDEX "fourkeys_to_twokeys_idx_t_artist_t_cd" ON "fourkeys_to_twokeys" ("t_artist", "t_cd"); |
eb0bc670 |
373 | |
4cd3d124 |
374 | CREATE VIEW "year2000cds" AS |
8c7b6644 |
375 | SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000"; |