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