Trunk passes tests again - todoify everything multicreate related to branch it out...
[dbsrgits/DBIx-Class.git] / t / lib / sqlite.sql
CommitLineData
0009fa49 1--
2-- Created by SQL::Translator::Producer::SQLite
4f6386b0 3-- Created on Mon Nov 17 02:53:11 2008
0009fa49 4--
5BEGIN TRANSACTION;
6
0009fa49 7
8--
9-- Table: artist
10--
11CREATE TABLE artist (
12 artistid INTEGER PRIMARY KEY NOT NULL,
39da2a2b 13 name varchar(100),
a0dd8679 14 rank integer NOT NULL DEFAULT '13',
15 charfield char(10)
0009fa49 16);
17
ebf846e8 18
0009fa49 19--
ebf846e8 20-- Table: artist_undirected_map
9c2c91ea 21--
ebf846e8 22CREATE TABLE artist_undirected_map (
9c2c91ea 23 id1 integer NOT NULL,
24 id2 integer NOT NULL,
9c2c91ea 25 PRIMARY KEY (id1, id2)
26);
27
ebf846e8 28CREATE INDEX artist_undirected_map_idx_id1_ ON artist_undirected_map (id1);
29CREATE INDEX artist_undirected_map_idx_id2_ ON artist_undirected_map (id2);
30
9c2c91ea 31--
4f6386b0 32-- Table: cd_artwork
33--
34CREATE TABLE cd_artwork (
35 cd_id INTEGER PRIMARY KEY NOT NULL
36);
37
38CREATE INDEX cd_artwork_idx_cd_id_cd_artwor ON cd_artwork (cd_id);
39
40--
ebf846e8 41-- Table: bookmark
3bd6e3e0 42--
ebf846e8 43CREATE TABLE bookmark (
44 id INTEGER PRIMARY KEY NOT NULL,
45 link integer NOT NULL
3bd6e3e0 46);
47
ebf846e8 48CREATE INDEX bookmark_idx_link_bookmark ON bookmark (link);
49
3bd6e3e0 50--
ebf846e8 51-- Table: books
0009fa49 52--
ebf846e8 53CREATE TABLE books (
54 id INTEGER PRIMARY KEY NOT NULL,
55 source varchar(100) NOT NULL,
56 owner integer NOT NULL,
cda5e082 57 title varchar(100) NOT NULL,
58 price integer
0009fa49 59);
60
ebf846e8 61
0009fa49 62--
0009fa49 63-- Table: cd
64--
65CREATE TABLE cd (
66 cdid INTEGER PRIMARY KEY NOT NULL,
67 artist integer NOT NULL,
8fcf21b3 68 title varchar(100) NOT NULL,
370f2ba2 69 year varchar(100) NOT NULL,
a1cb5921 70 genreid integer,
71 single_track integer
370f2ba2 72);
73
ebf846e8 74CREATE INDEX cd_idx_artist_cd ON cd (artist);
75CREATE INDEX cd_idx_genreid_cd ON cd (genreid);
a1cb5921 76CREATE INDEX cd_idx_single_track_cd ON cd (single_track);
ebf846e8 77CREATE UNIQUE INDEX cd_artist_title_cd ON cd (artist, title);
78
370f2ba2 79--
ebf846e8 80-- Table: cd_to_producer
370f2ba2 81--
ebf846e8 82CREATE TABLE cd_to_producer (
83 cd integer NOT NULL,
84 producer integer NOT NULL,
85 PRIMARY KEY (cd, producer)
0009fa49 86);
87
ebf846e8 88CREATE INDEX cd_to_producer_idx_cd_cd_to_pr ON cd_to_producer (cd);
89CREATE INDEX cd_to_producer_idx_producer_cd ON cd_to_producer (producer);
90
0009fa49 91--
ebf846e8 92-- Table: collection
0009fa49 93--
ebf846e8 94CREATE TABLE collection (
95 collectionid INTEGER PRIMARY KEY NOT NULL,
96 name varchar(100) NOT NULL
0009fa49 97);
98
ebf846e8 99
0009fa49 100--
ebf846e8 101-- Table: collection_object
0009fa49 102--
ebf846e8 103CREATE TABLE collection_object (
104 collection integer NOT NULL,
105 object integer NOT NULL,
106 PRIMARY KEY (collection, object)
0009fa49 107);
108
ebf846e8 109CREATE INDEX collection_object_idx_collection_collection_obj ON collection_object (collection);
110CREATE INDEX collection_object_idx_object_c ON collection_object (object);
111
0009fa49 112--
ebf846e8 113-- Table: employee
887ce227 114--
ebf846e8 115CREATE TABLE employee (
116 employee_id INTEGER PRIMARY KEY NOT NULL,
117 position integer NOT NULL,
118 group_id integer,
119 group_id_2 integer,
120 name varchar(100)
887ce227 121);
122
ebf846e8 123
9fcda149 124--
ebf846e8 125-- Table: event
ab8481f5 126--
ebf846e8 127CREATE TABLE event (
ab8481f5 128 id INTEGER PRIMARY KEY NOT NULL,
ebf846e8 129 starts_at datetime NOT NULL,
a97fe7e0 130 created_on timestamp NOT NULL,
131 varchar_date varchar(20),
ff8a6e3b 132 varchar_datetime varchar(20),
a0dd8679 133 skip_inflation datetime
ab8481f5 134);
135
ebf846e8 136
ab8481f5 137--
4740bdb7 138-- Table: file_columns
139--
140CREATE TABLE file_columns (
141 id INTEGER PRIMARY KEY NOT NULL,
ebf846e8 142 file varchar(255) NOT NULL
4740bdb7 143);
144
ebf846e8 145
4740bdb7 146--
ebf846e8 147-- Table: forceforeign
77211009 148--
ebf846e8 149CREATE TABLE forceforeign (
150 artist INTEGER PRIMARY KEY NOT NULL,
151 cd integer NOT NULL
77211009 152);
153
ebf846e8 154CREATE INDEX forceforeign_idx_artist_forcef ON forceforeign (artist);
155
77211009 156--
ebf846e8 157-- Table: fourkeys
9c2c91ea 158--
ebf846e8 159CREATE TABLE fourkeys (
160 foo integer NOT NULL,
161 bar integer NOT NULL,
162 hello integer NOT NULL,
163 goodbye integer NOT NULL,
164 sensors character NOT NULL,
165 PRIMARY KEY (foo, bar, hello, goodbye)
0009fa49 166);
167
ebf846e8 168
0009fa49 169--
ebf846e8 170-- Table: fourkeys_to_twokeys
0009fa49 171--
ebf846e8 172CREATE TABLE fourkeys_to_twokeys (
173 f_foo integer NOT NULL,
174 f_bar integer NOT NULL,
175 f_hello integer NOT NULL,
176 f_goodbye integer NOT NULL,
177 t_artist integer NOT NULL,
178 t_cd integer NOT NULL,
179 autopilot character NOT NULL,
180 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
0009fa49 181);
182
ebf846e8 183CREATE 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);
184CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd_fourkeys_to ON fourkeys_to_twokeys (t_artist, t_cd);
185
887ce227 186--
ebf846e8 187-- Table: genre
887ce227 188--
ebf846e8 189CREATE TABLE genre (
87310237 190 genreid INTEGER PRIMARY KEY NOT NULL,
191 name varchar(100) NOT NULL
9c2c91ea 192);
193
87310237 194CREATE UNIQUE INDEX genre_name_genre ON genre (name);
ebf846e8 195
196--
4f6386b0 197-- Table: images
198--
199CREATE TABLE images (
200 id INTEGER PRIMARY KEY NOT NULL,
201 artwork_id integer NOT NULL,
202 name varchar(100) NOT NULL,
203 data blob
204);
205
206CREATE INDEX images_idx_artwork_id_images ON images (artwork_id);
207
208--
ebf846e8 209-- Table: liner_notes
210--
211CREATE TABLE liner_notes (
212 liner_id INTEGER PRIMARY KEY NOT NULL,
213 notes varchar(100) NOT NULL
214);
215
6bf6ba2f 216CREATE INDEX liner_notes_idx_liner_id_liner ON liner_notes (liner_id);
ebf846e8 217
218--
219-- Table: link
220--
221CREATE TABLE link (
222 id INTEGER PRIMARY KEY NOT NULL,
223 url varchar(100),
224 title varchar(100)
225);
226
227
9c2c91ea 228--
4f6386b0 229-- Table: lyric_versions
230--
231CREATE TABLE lyric_versions (
232 id INTEGER PRIMARY KEY NOT NULL,
233 lyric_id integer NOT NULL,
234 text varchar(100) NOT NULL
235);
236
237CREATE INDEX lyric_versions_idx_lyric_id_ly ON lyric_versions (lyric_id);
238
239--
240-- Table: lyrics
241--
242CREATE TABLE lyrics (
243 lyric_id INTEGER PRIMARY KEY NOT NULL,
244 track_id integer NOT NULL
245);
246
247CREATE INDEX lyrics_idx_track_id_lyrics ON lyrics (track_id);
248
249--
89034887 250-- Table: noprimarykey
251--
252CREATE TABLE noprimarykey (
253 foo integer NOT NULL,
254 bar integer NOT NULL,
255 baz integer NOT NULL
256);
257
ebf846e8 258CREATE UNIQUE INDEX foo_bar_noprimarykey ON noprimarykey (foo, bar);
259
89034887 260--
ebf846e8 261-- Table: onekey
9c2c91ea 262--
ebf846e8 263CREATE TABLE onekey (
264 id INTEGER PRIMARY KEY NOT NULL,
265 artist integer NOT NULL,
266 cd integer NOT NULL
9c2c91ea 267);
268
ebf846e8 269
9c2c91ea 270--
ebf846e8 271-- Table: owners
9c2c91ea 272--
ebf846e8 273CREATE TABLE owners (
274 ownerid INTEGER PRIMARY KEY NOT NULL,
275 name varchar(100) NOT NULL
9c2c91ea 276);
277
ebf846e8 278
9c2c91ea 279--
280-- Table: producer
281--
282CREATE TABLE producer (
283 producerid INTEGER PRIMARY KEY NOT NULL,
8fcf21b3 284 name varchar(100) NOT NULL
887ce227 285);
286
ebf846e8 287CREATE UNIQUE INDEX prod_name_producer ON producer (name);
288
9fcda149 289--
ebf846e8 290-- Table: self_ref
9fcda149 291--
ebf846e8 292CREATE TABLE self_ref (
8fcf21b3 293 id INTEGER PRIMARY KEY NOT NULL,
ebf846e8 294 name varchar(100) NOT NULL
9fcda149 295);
296
ebf846e8 297
78060df8 298--
ebf846e8 299-- Table: self_ref_alias
78060df8 300--
ebf846e8 301CREATE TABLE self_ref_alias (
302 self_ref integer NOT NULL,
303 alias integer NOT NULL,
304 PRIMARY KEY (self_ref, alias)
78060df8 305);
306
ebf846e8 307CREATE INDEX self_ref_alias_idx_alias_self_ ON self_ref_alias (alias);
308CREATE INDEX self_ref_alias_idx_self_ref_se ON self_ref_alias (self_ref);
309
78060df8 310--
ebf846e8 311-- Table: sequence_test
78060df8 312--
ebf846e8 313CREATE TABLE sequence_test (
314 pkid1 integer NOT NULL,
315 pkid2 integer NOT NULL,
316 nonpkid integer NOT NULL,
317 name varchar(100),
318 PRIMARY KEY (pkid1, pkid2)
78060df8 319);
320
ebf846e8 321
78060df8 322--
ebf846e8 323-- Table: serialized
78060df8 324--
ebf846e8 325CREATE TABLE serialized (
326 id INTEGER PRIMARY KEY NOT NULL,
327 serialized text NOT NULL
78060df8 328);
329
ebf846e8 330
78060df8 331--
ebf846e8 332-- Table: tags
78060df8 333--
ebf846e8 334CREATE TABLE tags (
335 tagid INTEGER PRIMARY KEY NOT NULL,
336 cd integer NOT NULL,
337 tag varchar(100) NOT NULL
78060df8 338);
339
ebf846e8 340CREATE INDEX tags_idx_cd_tags ON tags (cd);
341
78060df8 342--
ebf846e8 343-- Table: track
78060df8 344--
ebf846e8 345CREATE TABLE track (
346 trackid INTEGER PRIMARY KEY NOT NULL,
347 cd integer NOT NULL,
348 position integer NOT NULL,
349 title varchar(100) NOT NULL,
350 last_updated_on datetime
351);
352
353CREATE INDEX track_idx_cd_track ON track (cd);
354CREATE UNIQUE INDEX track_cd_position_track ON track (cd, position);
355CREATE UNIQUE INDEX track_cd_title_track ON track (cd, title);
356
357--
358-- Table: treelike
359--
360CREATE TABLE treelike (
78060df8 361 id INTEGER PRIMARY KEY NOT NULL,
ebf846e8 362 parent integer,
363 name varchar(100) NOT NULL
364);
365
366CREATE INDEX treelike_idx_parent_treelike ON treelike (parent);
367
368--
369-- Table: twokeytreelike
370--
371CREATE TABLE twokeytreelike (
372 id1 integer NOT NULL,
373 id2 integer NOT NULL,
374 parent1 integer NOT NULL,
375 parent2 integer NOT NULL,
376 name varchar(100) NOT NULL,
377 PRIMARY KEY (id1, id2)
378);
379
380CREATE INDEX twokeytreelike_idx_parent1_parent2_twokeytre ON twokeytreelike (parent1, parent2);
381CREATE UNIQUE INDEX tktlnameunique_twokeytreelike ON twokeytreelike (name);
382
383--
384-- Table: twokeys
385--
386CREATE TABLE twokeys (
387 artist integer NOT NULL,
388 cd integer NOT NULL,
389 PRIMARY KEY (artist, cd)
390);
391
392CREATE INDEX twokeys_idx_artist_twokeys ON twokeys (artist);
393
394--
395-- Table: typed_object
396--
397CREATE TABLE typed_object (
398 objectid INTEGER PRIMARY KEY NOT NULL,
399 type varchar(100) NOT NULL,
400 value varchar(100) NOT NULL
78060df8 401);
402
403
0009fa49 404COMMIT;