2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Mon Feb 21 12:10:04 2011
10 artistid INTEGER PRIMARY KEY NOT NULL,
12 rank integer NOT NULL DEFAULT 13,
16 CREATE INDEX artist_name_hookidx ON artist (name);
18 CREATE UNIQUE INDEX artist_name ON artist (name);
20 CREATE UNIQUE INDEX u_nullable ON artist (charfield, rank);
23 -- Table: bindtype_test
25 CREATE TABLE bindtype_test (
26 id INTEGER PRIMARY KEY NOT NULL,
36 CREATE TABLE collection (
37 collectionid INTEGER PRIMARY KEY NOT NULL,
38 name varchar(100) NOT NULL
44 CREATE TABLE encoded (
45 id INTEGER PRIMARY KEY NOT NULL,
53 id INTEGER PRIMARY KEY NOT NULL,
54 starts_at date NOT NULL,
55 created_on timestamp NOT NULL,
56 varchar_date varchar(20),
57 varchar_datetime varchar(20),
58 skip_inflation datetime,
59 ts_without_tz datetime
63 -- Table: file_columns
65 CREATE TABLE file_columns (
66 id INTEGER PRIMARY KEY NOT NULL,
67 file varchar(255) NOT NULL
73 CREATE TABLE fourkeys (
76 hello integer NOT NULL,
77 goodbye integer NOT NULL,
78 sensors character(10) NOT NULL,
80 PRIMARY KEY (foo, bar, hello, goodbye)
87 genreid INTEGER PRIMARY KEY NOT NULL,
88 name varchar(100) NOT NULL
91 CREATE UNIQUE INDEX genre_name ON genre (name);
97 id INTEGER PRIMARY KEY NOT NULL,
105 CREATE TABLE money_test (
106 id INTEGER PRIMARY KEY NOT NULL,
111 -- Table: noprimarykey
113 CREATE TABLE noprimarykey (
114 foo integer NOT NULL,
115 bar integer NOT NULL,
119 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
124 CREATE TABLE onekey (
125 id INTEGER PRIMARY KEY NOT NULL,
126 artist integer NOT NULL,
133 CREATE TABLE owners (
134 id INTEGER PRIMARY KEY NOT NULL,
135 name varchar(100) NOT NULL
141 CREATE TABLE producer (
142 producerid INTEGER PRIMARY KEY NOT NULL,
143 name varchar(100) NOT NULL
146 CREATE UNIQUE INDEX prod_name ON producer (name);
151 CREATE TABLE self_ref (
152 id INTEGER PRIMARY KEY NOT NULL,
153 name varchar(100) NOT NULL
157 -- Table: sequence_test
159 CREATE TABLE sequence_test (
160 pkid1 integer NOT NULL,
161 pkid2 integer NOT NULL,
162 nonpkid integer NOT NULL,
164 PRIMARY KEY (pkid1, pkid2)
170 CREATE TABLE serialized (
171 id INTEGER PRIMARY KEY NOT NULL,
172 serialized text NOT NULL
176 -- Table: timestamp_primary_key_test
178 CREATE TABLE timestamp_primary_key_test (
179 id timestamp NOT NULL DEFAULT current_timestamp,
186 CREATE TABLE treelike (
187 id INTEGER PRIMARY KEY NOT NULL,
189 name varchar(100) NOT NULL
192 CREATE INDEX treelike_idx_parent ON treelike (parent);
195 -- Table: twokeytreelike
197 CREATE TABLE twokeytreelike (
198 id1 integer NOT NULL,
199 id2 integer NOT NULL,
200 parent1 integer NOT NULL,
201 parent2 integer NOT NULL,
202 name varchar(100) NOT NULL,
203 PRIMARY KEY (id1, id2)
206 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
208 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
211 -- Table: typed_object
213 CREATE TABLE typed_object (
214 objectid INTEGER PRIMARY KEY NOT NULL,
215 type varchar(100) NOT NULL,
216 value varchar(100) NOT NULL
220 -- Table: artist_undirected_map
222 CREATE TABLE artist_undirected_map (
223 id1 integer NOT NULL,
224 id2 integer NOT NULL,
225 PRIMARY KEY (id1, id2)
228 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
230 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
235 CREATE TABLE bookmark (
236 id INTEGER PRIMARY KEY NOT NULL,
240 CREATE INDEX bookmark_idx_link ON bookmark (link);
246 id INTEGER PRIMARY KEY NOT NULL,
247 source varchar(100) NOT NULL,
248 owner integer NOT NULL,
249 title varchar(100) NOT NULL,
253 CREATE INDEX books_idx_owner ON books (owner);
255 CREATE UNIQUE INDEX books_title ON books (title);
260 CREATE TABLE employee (
261 employee_id INTEGER PRIMARY KEY NOT NULL,
262 position integer NOT NULL,
270 CREATE INDEX employee_idx_encoded ON employee (encoded);
273 -- Table: forceforeign
275 CREATE TABLE forceforeign (
276 artist INTEGER PRIMARY KEY NOT NULL,
281 -- Table: self_ref_alias
283 CREATE TABLE self_ref_alias (
284 self_ref integer NOT NULL,
285 alias integer NOT NULL,
286 PRIMARY KEY (self_ref, alias)
289 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
291 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
297 trackid INTEGER PRIMARY KEY NOT NULL,
299 position int NOT NULL,
300 title varchar(100) NOT NULL,
301 last_updated_on datetime,
302 last_updated_at datetime
305 CREATE INDEX track_idx_cd ON track (cd);
307 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
309 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
315 cdid INTEGER PRIMARY KEY NOT NULL,
316 artist integer NOT NULL,
317 title varchar(100) NOT NULL,
318 year varchar(100) NOT NULL,
323 CREATE INDEX cd_idx_artist ON cd (artist);
325 CREATE INDEX cd_idx_genreid ON cd (genreid);
327 CREATE INDEX cd_idx_single_track ON cd (single_track);
329 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
332 -- Table: collection_object
334 CREATE TABLE collection_object (
335 collection integer NOT NULL,
336 object integer NOT NULL,
337 PRIMARY KEY (collection, object)
340 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
342 CREATE INDEX collection_object_idx_object ON collection_object (object);
347 CREATE TABLE lyrics (
348 lyric_id INTEGER PRIMARY KEY NOT NULL,
349 track_id integer NOT NULL
352 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
357 CREATE TABLE cd_artwork (
358 cd_id INTEGER PRIMARY KEY NOT NULL
362 -- Table: liner_notes
364 CREATE TABLE liner_notes (
365 liner_id INTEGER PRIMARY KEY NOT NULL,
366 notes varchar(100) NOT NULL
370 -- Table: lyric_versions
372 CREATE TABLE lyric_versions (
373 id INTEGER PRIMARY KEY NOT NULL,
374 lyric_id integer NOT NULL,
375 text varchar(100) NOT NULL
378 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
384 tagid INTEGER PRIMARY KEY NOT NULL,
386 tag varchar(100) NOT NULL
389 CREATE INDEX tags_idx_cd ON tags (cd);
391 CREATE UNIQUE INDEX tagid_cd ON tags (tagid, cd);
393 CREATE UNIQUE INDEX tagid_cd_tag ON tags (tagid, cd, tag);
395 CREATE UNIQUE INDEX tags_tagid_tag ON tags (tagid, tag);
397 CREATE UNIQUE INDEX tags_tagid_tag_cd ON tags (tagid, tag, cd);
400 -- Table: cd_to_producer
402 CREATE TABLE cd_to_producer (
404 producer integer NOT NULL,
406 PRIMARY KEY (cd, producer)
409 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
411 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
416 CREATE TABLE images (
417 id INTEGER PRIMARY KEY NOT NULL,
418 artwork_id integer NOT NULL,
419 name varchar(100) NOT NULL,
423 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
428 CREATE TABLE twokeys (
429 artist integer NOT NULL,
431 PRIMARY KEY (artist, cd)
434 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
437 -- Table: artwork_to_artist
439 CREATE TABLE artwork_to_artist (
440 artwork_cd_id integer NOT NULL,
441 artist_id integer NOT NULL,
442 PRIMARY KEY (artwork_cd_id, artist_id)
445 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
447 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
450 -- Table: fourkeys_to_twokeys
452 CREATE TABLE fourkeys_to_twokeys (
453 f_foo integer NOT NULL,
454 f_bar integer NOT NULL,
455 f_hello integer NOT NULL,
456 f_goodbye integer NOT NULL,
457 t_artist integer NOT NULL,
458 t_cd integer NOT NULL,
459 autopilot character NOT NULL,
460 pilot_sequence integer,
461 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
464 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);
466 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
471 CREATE VIEW year2000cds AS
472 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000";