\Q-uote column/alias names in regexes in _resolve_aliastypes_from_select_args
[dbsrgits/DBIx-Class.git] / t / lib / sqlite.sql
CommitLineData
4cd3d124 1CREATE 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
8CREATE INDEX "artist_name_hookidx" ON "artist" ("name");
9
10CREATE UNIQUE INDEX "artist_name" ON "artist" ("name");
11
12CREATE UNIQUE INDEX "u_nullable" ON "artist" ("charfield", "rank");
13
14CREATE 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
22CREATE TABLE "collection" (
23 "collectionid" INTEGER PRIMARY KEY NOT NULL,
24 "name" varchar(100) NOT NULL
25);
ebf846e8 26
4cd3d124 27CREATE TABLE "encoded" (
28 "id" INTEGER PRIMARY KEY NOT NULL,
29 "encoded" varchar(100)
887ce227 30);
31
4cd3d124 32CREATE 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
42CREATE 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
52CREATE TABLE "genre" (
53 "genreid" INTEGER PRIMARY KEY NOT NULL,
54 "name" varchar(100) NOT NULL
55);
ebf846e8 56
4cd3d124 57CREATE UNIQUE INDEX "genre_name" ON "genre" ("name");
58
59CREATE TABLE "link" (
60 "id" INTEGER PRIMARY KEY NOT NULL,
61 "url" varchar(100),
62 "title" varchar(100)
63);
64
65CREATE TABLE "money_test" (
66 "id" INTEGER PRIMARY KEY NOT NULL,
67 "amount" money
68);
69
70CREATE TABLE "noprimarykey" (
71 "foo" integer NOT NULL,
72 "bar" integer NOT NULL,
73 "baz" integer NOT NULL
74);
75
76CREATE UNIQUE INDEX "foo_bar" ON "noprimarykey" ("foo", "bar");
77
78CREATE TABLE "onekey" (
79 "id" INTEGER PRIMARY KEY NOT NULL,
80 "artist" integer NOT NULL,
81 "cd" integer NOT NULL
887ce227 82);
83
4cd3d124 84CREATE TABLE "owners" (
85 "id" INTEGER PRIMARY KEY NOT NULL,
86 "name" varchar(100) NOT NULL
9fcda149 87);
4cd3d124 88
89CREATE UNIQUE INDEX "owners_name" ON "owners" ("name");
90
91CREATE TABLE "producer" (
92 "producerid" INTEGER PRIMARY KEY NOT NULL,
93 "name" varchar(100) NOT NULL
94);
95
96CREATE UNIQUE INDEX "prod_name" ON "producer" ("name");
97
98CREATE TABLE "self_ref" (
99 "id" INTEGER PRIMARY KEY NOT NULL,
100 "name" varchar(100) NOT NULL
101);
102
103CREATE 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 111CREATE TABLE "serialized" (
112 "id" INTEGER PRIMARY KEY NOT NULL,
113 "serialized" text NOT NULL
78060df8 114);
115
4cd3d124 116CREATE TABLE "timestamp_primary_key_test" (
117 "id" timestamp NOT NULL DEFAULT current_timestamp,
118 PRIMARY KEY ("id")
4d4dc518 119);
120
4cd3d124 121CREATE 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 128CREATE INDEX "treelike_idx_parent" ON "treelike" ("parent");
eb0bc670 129
4cd3d124 130CREATE 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 140CREATE INDEX "twokeytreelike_idx_parent1_parent2" ON "twokeytreelike" ("parent1", "parent2");
eb0bc670 141
4cd3d124 142CREATE UNIQUE INDEX "tktlnameunique" ON "twokeytreelike" ("name");
eb0bc670 143
4cd3d124 144CREATE 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 150CREATE 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 158CREATE INDEX "artist_undirected_map_idx_id1" ON "artist_undirected_map" ("id1");
eb0bc670 159
4cd3d124 160CREATE INDEX "artist_undirected_map_idx_id2" ON "artist_undirected_map" ("id2");
eb0bc670 161
4cd3d124 162CREATE 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 168CREATE INDEX "bookmark_idx_link" ON "bookmark" ("link");
eb0bc670 169
4cd3d124 170CREATE 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 179CREATE INDEX "books_idx_owner" ON "books" ("owner");
eb0bc670 180
4cd3d124 181CREATE UNIQUE INDEX "books_title" ON "books" ("title");
8c7b6644 182
4cd3d124 183CREATE 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 194CREATE INDEX "employee_idx_encoded" ON "employee" ("encoded");
68888c09 195
4cd3d124 196CREATE 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 202CREATE 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 210CREATE INDEX "self_ref_alias_idx_alias" ON "self_ref_alias" ("alias");
eb0bc670 211
4cd3d124 212CREATE INDEX "self_ref_alias_idx_self_ref" ON "self_ref_alias" ("self_ref");
ebf846e8 213
4cd3d124 214CREATE 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 224CREATE INDEX "track_idx_cd" ON "track" ("cd");
084a2c0a 225
4cd3d124 226CREATE UNIQUE INDEX "track_cd_position" ON "track" ("cd", "position");
084a2c0a 227
4cd3d124 228CREATE UNIQUE INDEX "track_cd_title" ON "track" ("cd", "title");
ebf846e8 229
4cd3d124 230CREATE 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 242CREATE INDEX "cd_idx_artist" ON "cd" ("artist");
eb0bc670 243
4cd3d124 244CREATE INDEX "cd_idx_single_track" ON "cd" ("single_track");
eb0bc670 245
0077982b 246CREATE INDEX "cd_idx_genreid" ON "cd" ("genreid");
eb0bc670 247
4cd3d124 248CREATE UNIQUE INDEX "cd_artist_title" ON "cd" ("artist", "title");
eb0bc670 249
4cd3d124 250CREATE 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 258CREATE INDEX "collection_object_idx_collection" ON "collection_object" ("collection");
eb0bc670 259
4cd3d124 260CREATE INDEX "collection_object_idx_object" ON "collection_object" ("object");
eb0bc670 261
4cd3d124 262CREATE 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 268CREATE INDEX "lyrics_idx_track_id" ON "lyrics" ("track_id");
eb0bc670 269
4cd3d124 270CREATE 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 275CREATE 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 281CREATE 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 288CREATE INDEX "lyric_versions_idx_lyric_id" ON "lyric_versions" ("lyric_id");
ebf846e8 289
0077982b 290CREATE UNIQUE INDEX "lyric_versions_lyric_id_text" ON "lyric_versions" ("lyric_id", "text");
ebf846e8 291
4cd3d124 292CREATE 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 299CREATE INDEX "tags_idx_cd" ON "tags" ("cd");
eb0bc670 300
4cd3d124 301CREATE UNIQUE INDEX "tagid_cd" ON "tags" ("tagid", "cd");
84f7e8a1 302
4cd3d124 303CREATE UNIQUE INDEX "tagid_cd_tag" ON "tags" ("tagid", "cd", "tag");
84f7e8a1 304
4cd3d124 305CREATE UNIQUE INDEX "tags_tagid_tag" ON "tags" ("tagid", "tag");
84f7e8a1 306
4cd3d124 307CREATE UNIQUE INDEX "tags_tagid_tag_cd" ON "tags" ("tagid", "tag", "cd");
84f7e8a1 308
4cd3d124 309CREATE 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 318CREATE INDEX "cd_to_producer_idx_cd" ON "cd_to_producer" ("cd");
eb0bc670 319
4cd3d124 320CREATE INDEX "cd_to_producer_idx_producer" ON "cd_to_producer" ("producer");
eb0bc670 321
4cd3d124 322CREATE 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 330CREATE INDEX "images_idx_artwork_id" ON "images" ("artwork_id");
ebf846e8 331
4cd3d124 332CREATE 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 340CREATE INDEX "twokeys_idx_artist" ON "twokeys" ("artist");
ebf846e8 341
4cd3d124 342CREATE 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 350CREATE INDEX "artwork_to_artist_idx_artist_id" ON "artwork_to_artist" ("artist_id");
eb0bc670 351
4cd3d124 352CREATE INDEX "artwork_to_artist_idx_artwork_cd_id" ON "artwork_to_artist" ("artwork_cd_id");
eb0bc670 353
4cd3d124 354CREATE 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 368CREATE 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 370CREATE INDEX "fourkeys_to_twokeys_idx_t_artist_t_cd" ON "fourkeys_to_twokeys" ("t_artist", "t_cd");
eb0bc670 371
4cd3d124 372CREATE VIEW "year2000cds" AS
8c7b6644 373 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000";