2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Fri Mar 2 18:22:33 2012
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
130 CREATE UNIQUE INDEX owners_name ON owners (name);
135 CREATE TABLE producer (
136 producerid INTEGER PRIMARY KEY NOT NULL,
137 name varchar(100) NOT NULL
140 CREATE UNIQUE INDEX prod_name ON producer (name);
145 CREATE TABLE self_ref (
146 id INTEGER PRIMARY KEY NOT NULL,
147 name varchar(100) NOT NULL
151 -- Table: sequence_test
153 CREATE TABLE sequence_test (
154 pkid1 integer NOT NULL,
155 pkid2 integer NOT NULL,
156 nonpkid integer NOT NULL,
158 PRIMARY KEY (pkid1, pkid2)
164 CREATE TABLE serialized (
165 id INTEGER PRIMARY KEY NOT NULL,
166 serialized text NOT NULL
170 -- Table: timestamp_primary_key_test
172 CREATE TABLE timestamp_primary_key_test (
173 id timestamp NOT NULL DEFAULT current_timestamp,
180 CREATE TABLE treelike (
181 id INTEGER PRIMARY KEY NOT NULL,
183 name varchar(100) NOT NULL
186 CREATE INDEX treelike_idx_parent ON treelike (parent);
189 -- Table: twokeytreelike
191 CREATE TABLE twokeytreelike (
192 id1 integer NOT NULL,
193 id2 integer NOT NULL,
194 parent1 integer NOT NULL,
195 parent2 integer NOT NULL,
196 name varchar(100) NOT NULL,
197 PRIMARY KEY (id1, id2)
200 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
202 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
205 -- Table: typed_object
207 CREATE TABLE typed_object (
208 objectid INTEGER PRIMARY KEY NOT NULL,
209 type varchar(100) NOT NULL,
210 value varchar(100) NOT NULL
214 -- Table: artist_undirected_map
216 CREATE TABLE artist_undirected_map (
217 id1 integer NOT NULL,
218 id2 integer NOT NULL,
219 PRIMARY KEY (id1, id2)
222 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
224 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
229 CREATE TABLE bookmark (
230 id INTEGER PRIMARY KEY NOT NULL,
234 CREATE INDEX bookmark_idx_link ON bookmark (link);
240 id INTEGER PRIMARY KEY NOT NULL,
241 source varchar(100) NOT NULL,
242 owner integer NOT NULL,
243 title varchar(100) NOT NULL,
247 CREATE INDEX books_idx_owner ON books (owner);
249 CREATE UNIQUE INDEX books_title ON books (title);
254 CREATE TABLE employee (
255 employee_id INTEGER PRIMARY KEY NOT NULL,
256 position integer NOT NULL,
264 CREATE INDEX employee_idx_encoded ON employee (encoded);
267 -- Table: forceforeign
269 CREATE TABLE forceforeign (
270 artist INTEGER PRIMARY KEY NOT NULL,
275 -- Table: self_ref_alias
277 CREATE TABLE self_ref_alias (
278 self_ref integer NOT NULL,
279 alias integer NOT NULL,
280 PRIMARY KEY (self_ref, alias)
283 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
285 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
291 trackid INTEGER PRIMARY KEY NOT NULL,
293 position int NOT NULL,
294 title varchar(100) NOT NULL,
295 last_updated_on datetime,
296 last_updated_at datetime
299 CREATE INDEX track_idx_cd ON track (cd);
301 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
303 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
309 cdid INTEGER PRIMARY KEY NOT NULL,
310 artist integer NOT NULL,
311 title varchar(100) NOT NULL,
312 year varchar(100) NOT NULL,
317 CREATE INDEX cd_idx_artist ON cd (artist);
319 CREATE INDEX cd_idx_genreid ON cd (genreid);
321 CREATE INDEX cd_idx_single_track ON cd (single_track);
323 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
326 -- Table: collection_object
328 CREATE TABLE collection_object (
329 collection integer NOT NULL,
330 object integer NOT NULL,
331 PRIMARY KEY (collection, object)
334 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
336 CREATE INDEX collection_object_idx_object ON collection_object (object);
341 CREATE TABLE lyrics (
342 lyric_id INTEGER PRIMARY KEY NOT NULL,
343 track_id integer NOT NULL
346 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
351 CREATE TABLE cd_artwork (
352 cd_id INTEGER PRIMARY KEY NOT NULL
356 -- Table: liner_notes
358 CREATE TABLE liner_notes (
359 liner_id INTEGER PRIMARY KEY NOT NULL,
360 notes varchar(100) NOT NULL
364 -- Table: lyric_versions
366 CREATE TABLE lyric_versions (
367 id INTEGER PRIMARY KEY NOT NULL,
368 lyric_id integer NOT NULL,
369 text varchar(100) NOT NULL
372 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
378 tagid INTEGER PRIMARY KEY NOT NULL,
380 tag varchar(100) NOT NULL
383 CREATE INDEX tags_idx_cd ON tags (cd);
385 CREATE UNIQUE INDEX tagid_cd ON tags (tagid, cd);
387 CREATE UNIQUE INDEX tagid_cd_tag ON tags (tagid, cd, tag);
389 CREATE UNIQUE INDEX tags_tagid_tag ON tags (tagid, tag);
391 CREATE UNIQUE INDEX tags_tagid_tag_cd ON tags (tagid, tag, cd);
394 -- Table: cd_to_producer
396 CREATE TABLE cd_to_producer (
398 producer integer NOT NULL,
400 PRIMARY KEY (cd, producer)
403 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
405 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
410 CREATE TABLE images (
411 id INTEGER PRIMARY KEY NOT NULL,
412 artwork_id integer NOT NULL,
413 name varchar(100) NOT NULL,
417 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
422 CREATE TABLE twokeys (
423 artist integer NOT NULL,
425 PRIMARY KEY (artist, cd)
428 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
431 -- Table: artwork_to_artist
433 CREATE TABLE artwork_to_artist (
434 artwork_cd_id integer NOT NULL,
435 artist_id integer NOT NULL,
436 PRIMARY KEY (artwork_cd_id, artist_id)
439 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
441 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
444 -- Table: fourkeys_to_twokeys
446 CREATE TABLE fourkeys_to_twokeys (
447 f_foo integer NOT NULL,
448 f_bar integer NOT NULL,
449 f_hello integer NOT NULL,
450 f_goodbye integer NOT NULL,
451 t_artist integer NOT NULL,
452 t_cd integer NOT NULL,
453 autopilot character NOT NULL,
454 pilot_sequence integer,
455 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
458 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);
460 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
465 CREATE VIEW year2000cds AS
466 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000";