2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Sat Jun 11 00:39:51 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
65 CREATE TABLE fourkeys (
68 hello integer NOT NULL,
69 goodbye integer NOT NULL,
70 sensors character(10) NOT NULL,
72 PRIMARY KEY (foo, bar, hello, goodbye)
79 genreid INTEGER PRIMARY KEY NOT NULL,
80 name varchar(100) NOT NULL
83 CREATE UNIQUE INDEX genre_name ON genre (name);
89 id INTEGER PRIMARY KEY NOT NULL,
97 CREATE TABLE money_test (
98 id INTEGER PRIMARY KEY NOT NULL,
103 -- Table: noprimarykey
105 CREATE TABLE noprimarykey (
106 foo integer NOT NULL,
107 bar integer NOT NULL,
111 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
116 CREATE TABLE onekey (
117 id INTEGER PRIMARY KEY NOT NULL,
118 artist integer NOT NULL,
125 CREATE TABLE owners (
126 id INTEGER PRIMARY KEY NOT NULL,
127 name varchar(100) NOT NULL
133 CREATE TABLE producer (
134 producerid INTEGER PRIMARY KEY NOT NULL,
135 name varchar(100) NOT NULL
138 CREATE UNIQUE INDEX prod_name ON producer (name);
143 CREATE TABLE self_ref (
144 id INTEGER PRIMARY KEY NOT NULL,
145 name varchar(100) NOT NULL
149 -- Table: sequence_test
151 CREATE TABLE sequence_test (
152 pkid1 integer NOT NULL,
153 pkid2 integer NOT NULL,
154 nonpkid integer NOT NULL,
156 PRIMARY KEY (pkid1, pkid2)
162 CREATE TABLE serialized (
163 id INTEGER PRIMARY KEY NOT NULL,
164 serialized text NOT NULL
168 -- Table: timestamp_primary_key_test
170 CREATE TABLE timestamp_primary_key_test (
171 id timestamp NOT NULL DEFAULT current_timestamp,
178 CREATE TABLE treelike (
179 id INTEGER PRIMARY KEY NOT NULL,
181 name varchar(100) NOT NULL
184 CREATE INDEX treelike_idx_parent ON treelike (parent);
187 -- Table: twokeytreelike
189 CREATE TABLE twokeytreelike (
190 id1 integer NOT NULL,
191 id2 integer NOT NULL,
192 parent1 integer NOT NULL,
193 parent2 integer NOT NULL,
194 name varchar(100) NOT NULL,
195 PRIMARY KEY (id1, id2)
198 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
200 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
203 -- Table: typed_object
205 CREATE TABLE typed_object (
206 objectid INTEGER PRIMARY KEY NOT NULL,
207 type varchar(100) NOT NULL,
208 value varchar(100) NOT NULL
212 -- Table: artist_undirected_map
214 CREATE TABLE artist_undirected_map (
215 id1 integer NOT NULL,
216 id2 integer NOT NULL,
217 PRIMARY KEY (id1, id2)
220 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
222 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
227 CREATE TABLE bookmark (
228 id INTEGER PRIMARY KEY NOT NULL,
232 CREATE INDEX bookmark_idx_link ON bookmark (link);
238 id INTEGER PRIMARY KEY NOT NULL,
239 source varchar(100) NOT NULL,
240 owner integer NOT NULL,
241 title varchar(100) NOT NULL,
245 CREATE INDEX books_idx_owner ON books (owner);
247 CREATE UNIQUE INDEX books_title ON books (title);
252 CREATE TABLE employee (
253 employee_id INTEGER PRIMARY KEY NOT NULL,
254 position integer NOT NULL,
262 CREATE INDEX employee_idx_encoded ON employee (encoded);
265 -- Table: forceforeign
267 CREATE TABLE forceforeign (
268 artist INTEGER PRIMARY KEY NOT NULL,
273 -- Table: self_ref_alias
275 CREATE TABLE self_ref_alias (
276 self_ref integer NOT NULL,
277 alias integer NOT NULL,
278 PRIMARY KEY (self_ref, alias)
281 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
283 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
289 trackid INTEGER PRIMARY KEY NOT NULL,
291 position int NOT NULL,
292 title varchar(100) NOT NULL,
293 last_updated_on datetime,
294 last_updated_at datetime
297 CREATE INDEX track_idx_cd ON track (cd);
299 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
301 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
307 cdid INTEGER PRIMARY KEY NOT NULL,
308 artist integer NOT NULL,
309 title varchar(100) NOT NULL,
310 year varchar(100) NOT NULL,
315 CREATE INDEX cd_idx_artist ON cd (artist);
317 CREATE INDEX cd_idx_genreid ON cd (genreid);
319 CREATE INDEX cd_idx_single_track ON cd (single_track);
321 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
324 -- Table: collection_object
326 CREATE TABLE collection_object (
327 collection integer NOT NULL,
328 object integer NOT NULL,
329 PRIMARY KEY (collection, object)
332 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
334 CREATE INDEX collection_object_idx_object ON collection_object (object);
339 CREATE TABLE lyrics (
340 lyric_id INTEGER PRIMARY KEY NOT NULL,
341 track_id integer NOT NULL
344 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
349 CREATE TABLE cd_artwork (
350 cd_id INTEGER PRIMARY KEY NOT NULL
354 -- Table: liner_notes
356 CREATE TABLE liner_notes (
357 liner_id INTEGER PRIMARY KEY NOT NULL,
358 notes varchar(100) NOT NULL
362 -- Table: lyric_versions
364 CREATE TABLE lyric_versions (
365 id INTEGER PRIMARY KEY NOT NULL,
366 lyric_id integer NOT NULL,
367 text varchar(100) NOT NULL
370 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
376 tagid INTEGER PRIMARY KEY NOT NULL,
378 tag varchar(100) NOT NULL
381 CREATE INDEX tags_idx_cd ON tags (cd);
383 CREATE UNIQUE INDEX tagid_cd ON tags (tagid, cd);
385 CREATE UNIQUE INDEX tagid_cd_tag ON tags (tagid, cd, tag);
387 CREATE UNIQUE INDEX tags_tagid_tag ON tags (tagid, tag);
389 CREATE UNIQUE INDEX tags_tagid_tag_cd ON tags (tagid, tag, cd);
392 -- Table: cd_to_producer
394 CREATE TABLE cd_to_producer (
396 producer integer NOT NULL,
398 PRIMARY KEY (cd, producer)
401 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
403 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
408 CREATE TABLE images (
409 id INTEGER PRIMARY KEY NOT NULL,
410 artwork_id integer NOT NULL,
411 name varchar(100) NOT NULL,
415 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
420 CREATE TABLE twokeys (
421 artist integer NOT NULL,
423 PRIMARY KEY (artist, cd)
426 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
429 -- Table: artwork_to_artist
431 CREATE TABLE artwork_to_artist (
432 artwork_cd_id integer NOT NULL,
433 artist_id integer NOT NULL,
434 PRIMARY KEY (artwork_cd_id, artist_id)
437 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
439 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
442 -- Table: fourkeys_to_twokeys
444 CREATE TABLE fourkeys_to_twokeys (
445 f_foo integer NOT NULL,
446 f_bar integer NOT NULL,
447 f_hello integer NOT NULL,
448 f_goodbye integer NOT NULL,
449 t_artist integer NOT NULL,
450 t_cd integer NOT NULL,
451 autopilot character NOT NULL,
452 pilot_sequence integer,
453 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
456 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);
458 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
463 CREATE VIEW year2000cds AS
464 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000";