Merge 'trunk' into 'sybase'
[dbsrgits/DBIx-Class-Historic.git] / t / lib / sqlite.sql
CommitLineData
22d6d8e1 1-- Created on Thu Jul 30 09:37:43 2009
0009fa49 2--
0009fa49 3
0009fa49 4
084a2c0a 5BEGIN TRANSACTION;
6
0009fa49 7--
8-- Table: artist
9--
10CREATE TABLE artist (
11 artistid INTEGER PRIMARY KEY NOT NULL,
39da2a2b 12 name varchar(100),
a0dd8679 13 rank integer NOT NULL DEFAULT '13',
14 charfield char(10)
0009fa49 15);
16
17--
6ec7d1bb 18-- Table: bindtype_test
19--
20CREATE TABLE bindtype_test (
21 id INTEGER PRIMARY KEY NOT NULL,
22 bytea blob,
23 blob blob,
24 clob clob
25);
26
6ec7d1bb 27--
ebf846e8 28-- Table: collection
0009fa49 29--
ebf846e8 30CREATE TABLE collection (
31 collectionid INTEGER PRIMARY KEY NOT NULL,
32 name varchar(100) NOT NULL
0009fa49 33);
34
35--
ebf846e8 36-- Table: employee
887ce227 37--
ebf846e8 38CREATE TABLE employee (
39 employee_id INTEGER PRIMARY KEY NOT NULL,
40 position integer NOT NULL,
41 group_id integer,
42 group_id_2 integer,
9beded8a 43 group_id_3 integer,
ebf846e8 44 name varchar(100)
887ce227 45);
46
084a2c0a 47--
48-- Table: encoded
49--
50CREATE TABLE encoded (
51 id INTEGER PRIMARY KEY NOT NULL,
52 encoded varchar(100)
53);
ebf846e8 54
9fcda149 55--
ebf846e8 56-- Table: event
ab8481f5 57--
ebf846e8 58CREATE TABLE event (
ab8481f5 59 id INTEGER PRIMARY KEY NOT NULL,
ebf846e8 60 starts_at datetime NOT NULL,
a97fe7e0 61 created_on timestamp NOT NULL,
62 varchar_date varchar(20),
ff8a6e3b 63 varchar_datetime varchar(20),
41308e82 64 skip_inflation datetime,
65 ts_without_tz datetime
ab8481f5 66);
67
68--
4740bdb7 69-- Table: file_columns
70--
71CREATE TABLE file_columns (
72 id INTEGER PRIMARY KEY NOT NULL,
ebf846e8 73 file varchar(255) NOT NULL
4740bdb7 74);
75
76--
ebf846e8 77-- Table: fourkeys
9c2c91ea 78--
ebf846e8 79CREATE TABLE fourkeys (
80 foo integer NOT NULL,
81 bar integer NOT NULL,
82 hello integer NOT NULL,
83 goodbye integer NOT NULL,
ff1234ad 84 sensors character(10) NOT NULL,
85 read_count integer,
ebf846e8 86 PRIMARY KEY (foo, bar, hello, goodbye)
0009fa49 87);
88
89--
ebf846e8 90-- Table: genre
887ce227 91--
ebf846e8 92CREATE TABLE genre (
87310237 93 genreid INTEGER PRIMARY KEY NOT NULL,
56b73f83 94 name varchar(100) NOT NULL
9c2c91ea 95);
96
eb0bc670 97CREATE UNIQUE INDEX genre_name ON genre (name);
ebf846e8 98
99--
100-- Table: link
101--
102CREATE TABLE link (
103 id INTEGER PRIMARY KEY NOT NULL,
104 url varchar(100),
105 title varchar(100)
106);
107
9c2c91ea 108--
41308e82 109-- Table: money_test
110--
111CREATE TABLE money_test (
112 id INTEGER PRIMARY KEY NOT NULL,
113 amount money
114);
115
116--
89034887 117-- Table: noprimarykey
118--
119CREATE TABLE noprimarykey (
120 foo integer NOT NULL,
121 bar integer NOT NULL,
122 baz integer NOT NULL
123);
124
eb0bc670 125CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
ebf846e8 126
89034887 127--
ebf846e8 128-- Table: onekey
9c2c91ea 129--
ebf846e8 130CREATE TABLE onekey (
131 id INTEGER PRIMARY KEY NOT NULL,
132 artist integer NOT NULL,
133 cd integer NOT NULL
9c2c91ea 134);
135
136--
ebf846e8 137-- Table: owners
9c2c91ea 138--
ebf846e8 139CREATE TABLE owners (
bed3a173 140 id INTEGER PRIMARY KEY NOT NULL,
ebf846e8 141 name varchar(100) NOT NULL
9c2c91ea 142);
143
144--
145-- Table: producer
146--
147CREATE TABLE producer (
148 producerid INTEGER PRIMARY KEY NOT NULL,
8fcf21b3 149 name varchar(100) NOT NULL
887ce227 150);
151
eb0bc670 152CREATE UNIQUE INDEX prod_name ON producer (name);
ebf846e8 153
9fcda149 154--
ebf846e8 155-- Table: self_ref
9fcda149 156--
ebf846e8 157CREATE TABLE self_ref (
8fcf21b3 158 id INTEGER PRIMARY KEY NOT NULL,
ebf846e8 159 name varchar(100) NOT NULL
9fcda149 160);
161
78060df8 162--
ebf846e8 163-- Table: sequence_test
78060df8 164--
ebf846e8 165CREATE TABLE sequence_test (
166 pkid1 integer NOT NULL,
167 pkid2 integer NOT NULL,
168 nonpkid integer NOT NULL,
169 name varchar(100),
170 PRIMARY KEY (pkid1, pkid2)
78060df8 171);
172
173--
ebf846e8 174-- Table: serialized
78060df8 175--
ebf846e8 176CREATE TABLE serialized (
177 id INTEGER PRIMARY KEY NOT NULL,
178 serialized text NOT NULL
78060df8 179);
180
181--
eb0bc670 182-- Table: treelike
78060df8 183--
eb0bc670 184CREATE TABLE treelike (
185 id INTEGER PRIMARY KEY NOT NULL,
186 parent integer,
187 name varchar(100) NOT NULL
188);
189
190CREATE INDEX treelike_idx_parent ON treelike (parent);
191
192--
193-- Table: twokeytreelike
194--
195CREATE TABLE twokeytreelike (
196 id1 integer NOT NULL,
197 id2 integer NOT NULL,
198 parent1 integer NOT NULL,
199 parent2 integer NOT NULL,
200 name varchar(100) NOT NULL,
201 PRIMARY KEY (id1, id2)
202);
203
204CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
205
206CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
207
208--
209-- Table: typed_object
210--
211CREATE TABLE typed_object (
212 objectid INTEGER PRIMARY KEY NOT NULL,
213 type varchar(100) NOT NULL,
214 value varchar(100) NOT NULL
215);
216
217--
218-- Table: artist_undirected_map
219--
220CREATE TABLE artist_undirected_map (
221 id1 integer NOT NULL,
222 id2 integer NOT NULL,
223 PRIMARY KEY (id1, id2)
224);
225
226CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
227
228CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
229
230--
231-- Table: bookmark
232--
233CREATE TABLE bookmark (
234 id INTEGER PRIMARY KEY NOT NULL,
41308e82 235 link integer
eb0bc670 236);
237
238CREATE INDEX bookmark_idx_link ON bookmark (link);
239
240--
241-- Table: books
242--
243CREATE TABLE books (
244 id INTEGER PRIMARY KEY NOT NULL,
245 source varchar(100) NOT NULL,
246 owner integer NOT NULL,
247 title varchar(100) NOT NULL,
248 price integer
249);
250
251CREATE INDEX books_idx_owner ON books (owner);
252
253--
254-- Table: forceforeign
255--
256CREATE TABLE forceforeign (
257 artist INTEGER PRIMARY KEY NOT NULL,
258 cd integer NOT NULL
259);
260
261CREATE INDEX forceforeign_idx_artist ON forceforeign (artist);
262
263--
264-- Table: self_ref_alias
265--
266CREATE TABLE self_ref_alias (
267 self_ref integer NOT NULL,
268 alias integer NOT NULL,
269 PRIMARY KEY (self_ref, alias)
78060df8 270);
271
eb0bc670 272CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
273
274CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
ebf846e8 275
78060df8 276--
ebf846e8 277-- Table: track
78060df8 278--
ebf846e8 279CREATE TABLE track (
280 trackid INTEGER PRIMARY KEY NOT NULL,
281 cd integer NOT NULL,
282 position integer NOT NULL,
283 title varchar(100) NOT NULL,
abc914bd 284 last_updated_on datetime,
09451687 285 last_updated_at datetime,
286 small_dt smalldatetime
ebf846e8 287);
288
eb0bc670 289CREATE INDEX track_idx_cd ON track (cd);
084a2c0a 290
eb0bc670 291CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
084a2c0a 292
eb0bc670 293CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
ebf846e8 294
295--
eb0bc670 296-- Table: cd
ebf846e8 297--
eb0bc670 298CREATE TABLE cd (
299 cdid INTEGER PRIMARY KEY NOT NULL,
300 artist integer NOT NULL,
301 title varchar(100) NOT NULL,
302 year varchar(100) NOT NULL,
303 genreid integer,
304 single_track integer
305);
306
307CREATE INDEX cd_idx_artist ON cd (artist);
308
309CREATE INDEX cd_idx_genreid ON cd (genreid);
310
311CREATE INDEX cd_idx_single_track ON cd (single_track);
312
313CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
314
315--
316-- Table: collection_object
317--
318CREATE TABLE collection_object (
319 collection integer NOT NULL,
320 object integer NOT NULL,
321 PRIMARY KEY (collection, object)
322);
323
324CREATE INDEX collection_object_idx_collection ON collection_object (collection);
325
326CREATE INDEX collection_object_idx_object ON collection_object (object);
327
328--
329-- Table: lyrics
330--
331CREATE TABLE lyrics (
332 lyric_id INTEGER PRIMARY KEY NOT NULL,
333 track_id integer NOT NULL
334);
335
336CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
337
338--
339-- Table: cd_artwork
340--
341CREATE TABLE cd_artwork (
342 cd_id INTEGER PRIMARY KEY NOT NULL
343);
344
345CREATE INDEX cd_artwork_idx_cd_id ON cd_artwork (cd_id);
346
347--
348-- Table: liner_notes
349--
350CREATE TABLE liner_notes (
351 liner_id INTEGER PRIMARY KEY NOT NULL,
352 notes varchar(100) NOT NULL
353);
354
355CREATE INDEX liner_notes_idx_liner_id ON liner_notes (liner_id);
356
357--
358-- Table: lyric_versions
359--
360CREATE TABLE lyric_versions (
78060df8 361 id INTEGER PRIMARY KEY NOT NULL,
eb0bc670 362 lyric_id integer NOT NULL,
363 text varchar(100) NOT NULL
ebf846e8 364);
365
eb0bc670 366CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
ebf846e8 367
368--
eb0bc670 369-- Table: tags
ebf846e8 370--
eb0bc670 371CREATE TABLE tags (
372 tagid INTEGER PRIMARY KEY NOT NULL,
373 cd integer NOT NULL,
374 tag varchar(100) NOT NULL
375);
376
377CREATE INDEX tags_idx_cd ON tags (cd);
378
379--
380-- Table: cd_to_producer
381--
382CREATE TABLE cd_to_producer (
383 cd integer NOT NULL,
384 producer integer NOT NULL,
385 attribute integer,
386 PRIMARY KEY (cd, producer)
ebf846e8 387);
388
eb0bc670 389CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
390
391CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
392
393--
394-- Table: images
395--
396CREATE TABLE images (
397 id INTEGER PRIMARY KEY NOT NULL,
398 artwork_id integer NOT NULL,
399 name varchar(100) NOT NULL,
400 data blob
401);
084a2c0a 402
eb0bc670 403CREATE INDEX images_idx_artwork_id ON images (artwork_id);
ebf846e8 404
405--
406-- Table: twokeys
407--
408CREATE TABLE twokeys (
409 artist integer NOT NULL,
410 cd integer NOT NULL,
411 PRIMARY KEY (artist, cd)
412);
413
eb0bc670 414CREATE INDEX twokeys_idx_artist ON twokeys (artist);
ebf846e8 415
416--
eb0bc670 417-- Table: artwork_to_artist
ebf846e8 418--
eb0bc670 419CREATE TABLE artwork_to_artist (
420 artwork_cd_id integer NOT NULL,
421 artist_id integer NOT NULL,
422 PRIMARY KEY (artwork_cd_id, artist_id)
78060df8 423);
424
eb0bc670 425CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
426
427CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
428
429--
430-- Table: fourkeys_to_twokeys
431--
432CREATE TABLE fourkeys_to_twokeys (
433 f_foo integer NOT NULL,
434 f_bar integer NOT NULL,
435 f_hello integer NOT NULL,
436 f_goodbye integer NOT NULL,
437 t_artist integer NOT NULL,
438 t_cd integer NOT NULL,
439 autopilot character NOT NULL,
440 pilot_sequence integer,
441 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
442);
443
444CREATE 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);
445
446CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
447
0e80c4ca 448--
084a2c0a 449-- View: year2000cds
0e80c4ca 450--
084a2c0a 451CREATE VIEW year2000cds AS
452 SELECT cdid, artist, title FROM cd WHERE year ='2000';
78060df8 453
0009fa49 454COMMIT;