2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Tue Mar 16 16:49:23 2010
10 artistid INTEGER PRIMARY KEY NOT NULL,
12 rank integer NOT NULL DEFAULT '13',
16 CREATE INDEX artist_name_hookidx ON artist (name);
19 -- Table: bindtype_test
21 CREATE TABLE bindtype_test (
22 id INTEGER PRIMARY KEY NOT NULL,
31 CREATE TABLE collection (
32 collectionid INTEGER PRIMARY KEY NOT NULL,
33 name varchar(100) NOT NULL
39 CREATE TABLE encoded (
40 id INTEGER PRIMARY KEY NOT NULL,
48 id INTEGER PRIMARY KEY NOT NULL,
49 starts_at date NOT NULL,
50 created_on timestamp NOT NULL,
51 varchar_date varchar(20),
52 varchar_datetime varchar(20),
53 skip_inflation datetime,
54 ts_without_tz datetime
58 -- Table: file_columns
60 CREATE TABLE file_columns (
61 id INTEGER PRIMARY KEY NOT NULL,
62 file varchar(255) NOT NULL
68 CREATE TABLE fourkeys (
71 hello integer NOT NULL,
72 goodbye integer NOT NULL,
73 sensors character(10) NOT NULL,
75 PRIMARY KEY (foo, bar, hello, goodbye)
82 genreid INTEGER PRIMARY KEY NOT NULL,
83 name varchar(100) NOT NULL
86 CREATE UNIQUE INDEX genre_name ON genre (name);
92 id INTEGER PRIMARY KEY NOT NULL,
100 CREATE TABLE money_test (
101 id INTEGER PRIMARY KEY NOT NULL,
106 -- Table: noprimarykey
108 CREATE TABLE noprimarykey (
109 foo integer NOT NULL,
110 bar integer NOT NULL,
114 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
119 CREATE TABLE onekey (
120 id INTEGER PRIMARY KEY NOT NULL,
121 artist integer NOT NULL,
128 CREATE TABLE owners (
129 id INTEGER PRIMARY KEY NOT NULL,
130 name varchar(100) NOT NULL
136 CREATE TABLE producer (
137 producerid INTEGER PRIMARY KEY NOT NULL,
138 name varchar(100) NOT NULL
141 CREATE UNIQUE INDEX prod_name ON producer (name);
146 CREATE TABLE self_ref (
147 id INTEGER PRIMARY KEY NOT NULL,
148 name varchar(100) NOT NULL
152 -- Table: sequence_test
154 CREATE TABLE sequence_test (
155 pkid1 integer NOT NULL,
156 pkid2 integer NOT NULL,
157 nonpkid integer NOT NULL,
159 PRIMARY KEY (pkid1, pkid2)
165 CREATE TABLE serialized (
166 id INTEGER PRIMARY KEY NOT NULL,
167 serialized text NOT NULL
173 CREATE TABLE treelike (
174 id INTEGER PRIMARY KEY NOT NULL,
176 name varchar(100) NOT NULL
179 CREATE INDEX treelike_idx_parent ON treelike (parent);
182 -- Table: twokeytreelike
184 CREATE TABLE twokeytreelike (
185 id1 integer NOT NULL,
186 id2 integer NOT NULL,
187 parent1 integer NOT NULL,
188 parent2 integer NOT NULL,
189 name varchar(100) NOT NULL,
190 PRIMARY KEY (id1, id2)
193 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
195 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
198 -- Table: typed_object
200 CREATE TABLE typed_object (
201 objectid INTEGER PRIMARY KEY NOT NULL,
202 type varchar(100) NOT NULL,
203 value varchar(100) NOT NULL
207 -- Table: artist_undirected_map
209 CREATE TABLE artist_undirected_map (
210 id1 integer NOT NULL,
211 id2 integer NOT NULL,
212 PRIMARY KEY (id1, id2)
215 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
217 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
222 CREATE TABLE bookmark (
223 id INTEGER PRIMARY KEY NOT NULL,
227 CREATE INDEX bookmark_idx_link ON bookmark (link);
233 id INTEGER PRIMARY KEY NOT NULL,
234 source varchar(100) NOT NULL,
235 owner integer NOT NULL,
236 title varchar(100) NOT NULL,
240 CREATE INDEX books_idx_owner ON books (owner);
245 CREATE TABLE employee (
246 employee_id INTEGER PRIMARY KEY NOT NULL,
247 position integer NOT NULL,
255 CREATE INDEX employee_idx_encoded ON employee (encoded);
258 -- Table: forceforeign
260 CREATE TABLE forceforeign (
261 artist INTEGER PRIMARY KEY NOT NULL,
266 -- Table: self_ref_alias
268 CREATE TABLE self_ref_alias (
269 self_ref integer NOT NULL,
270 alias integer NOT NULL,
271 PRIMARY KEY (self_ref, alias)
274 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
276 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
282 trackid INTEGER PRIMARY KEY NOT NULL,
284 position int NOT NULL,
285 title varchar(100) NOT NULL,
286 last_updated_on datetime,
287 last_updated_at datetime,
288 small_dt smalldatetime
291 CREATE INDEX track_idx_cd ON track (cd);
293 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
295 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
301 cdid INTEGER PRIMARY KEY NOT NULL,
302 artist integer NOT NULL,
303 title varchar(100) NOT NULL,
304 year varchar(100) NOT NULL,
309 CREATE INDEX cd_idx_artist ON cd (artist);
311 CREATE INDEX cd_idx_genreid ON cd (genreid);
313 CREATE INDEX cd_idx_single_track ON cd (single_track);
315 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
318 -- Table: collection_object
320 CREATE TABLE collection_object (
321 collection integer NOT NULL,
322 object integer NOT NULL,
323 PRIMARY KEY (collection, object)
326 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
328 CREATE INDEX collection_object_idx_object ON collection_object (object);
333 CREATE TABLE lyrics (
334 lyric_id INTEGER PRIMARY KEY NOT NULL,
335 track_id integer NOT NULL
338 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
343 CREATE TABLE cd_artwork (
344 cd_id INTEGER PRIMARY KEY NOT NULL
348 -- Table: liner_notes
350 CREATE TABLE liner_notes (
351 liner_id INTEGER PRIMARY KEY NOT NULL,
352 notes varchar(100) NOT NULL
356 -- Table: lyric_versions
358 CREATE TABLE lyric_versions (
359 id INTEGER PRIMARY KEY NOT NULL,
360 lyric_id integer NOT NULL,
361 text varchar(100) NOT NULL
364 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
370 tagid INTEGER PRIMARY KEY NOT NULL,
372 tag varchar(100) NOT NULL
375 CREATE INDEX tags_idx_cd ON tags (cd);
378 -- Table: cd_to_producer
380 CREATE TABLE cd_to_producer (
382 producer integer NOT NULL,
384 PRIMARY KEY (cd, producer)
387 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
389 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
394 CREATE TABLE images (
395 id INTEGER PRIMARY KEY NOT NULL,
396 artwork_id integer NOT NULL,
397 name varchar(100) NOT NULL,
401 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
406 CREATE TABLE twokeys (
407 artist integer NOT NULL,
409 PRIMARY KEY (artist, cd)
412 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
415 -- Table: artwork_to_artist
417 CREATE TABLE artwork_to_artist (
418 artwork_cd_id integer NOT NULL,
419 artist_id integer NOT NULL,
420 PRIMARY KEY (artwork_cd_id, artist_id)
423 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
425 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
428 -- Table: fourkeys_to_twokeys
430 CREATE TABLE fourkeys_to_twokeys (
431 f_foo integer NOT NULL,
432 f_bar integer NOT NULL,
433 f_hello integer NOT NULL,
434 f_goodbye integer NOT NULL,
435 t_artist integer NOT NULL,
436 t_cd integer NOT NULL,
437 autopilot character NOT NULL,
438 pilot_sequence integer,
439 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
442 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);
444 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
449 CREATE VIEW year2000cds AS
450 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000";