2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Thu Jun 3 11:52:50 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
171 -- Table: timestamp_primary_key_test
173 CREATE TABLE timestamp_primary_key_test (
174 id timestamp NOT NULL DEFAULT current_timestamp,
181 CREATE TABLE treelike (
182 id INTEGER PRIMARY KEY NOT NULL,
184 name varchar(100) NOT NULL
187 CREATE INDEX treelike_idx_parent ON treelike (parent);
190 -- Table: twokeytreelike
192 CREATE TABLE twokeytreelike (
193 id1 integer NOT NULL,
194 id2 integer NOT NULL,
195 parent1 integer NOT NULL,
196 parent2 integer NOT NULL,
197 name varchar(100) NOT NULL,
198 PRIMARY KEY (id1, id2)
201 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
203 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
206 -- Table: typed_object
208 CREATE TABLE typed_object (
209 objectid INTEGER PRIMARY KEY NOT NULL,
210 type varchar(100) NOT NULL,
211 value varchar(100) NOT NULL
215 -- Table: artist_undirected_map
217 CREATE TABLE artist_undirected_map (
218 id1 integer NOT NULL,
219 id2 integer NOT NULL,
220 PRIMARY KEY (id1, id2)
223 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
225 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
230 CREATE TABLE bookmark (
231 id INTEGER PRIMARY KEY NOT NULL,
235 CREATE INDEX bookmark_idx_link ON bookmark (link);
241 id INTEGER PRIMARY KEY NOT NULL,
242 source varchar(100) NOT NULL,
243 owner integer NOT NULL,
244 title varchar(100) NOT NULL,
248 CREATE INDEX books_idx_owner ON books (owner);
250 CREATE UNIQUE INDEX books_title ON books (title);
255 CREATE TABLE employee (
256 employee_id INTEGER PRIMARY KEY NOT NULL,
257 position integer NOT NULL,
265 CREATE INDEX employee_idx_encoded ON employee (encoded);
268 -- Table: forceforeign
270 CREATE TABLE forceforeign (
271 artist INTEGER PRIMARY KEY NOT NULL,
276 -- Table: self_ref_alias
278 CREATE TABLE self_ref_alias (
279 self_ref integer NOT NULL,
280 alias integer NOT NULL,
281 PRIMARY KEY (self_ref, alias)
284 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
286 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
292 trackid INTEGER PRIMARY KEY NOT NULL,
294 position int NOT NULL,
295 title varchar(100) NOT NULL,
296 last_updated_on datetime,
297 last_updated_at datetime,
298 small_dt smalldatetime
301 CREATE INDEX track_idx_cd ON track (cd);
303 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
305 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
311 cdid INTEGER PRIMARY KEY NOT NULL,
312 artist integer NOT NULL,
313 title varchar(100) NOT NULL,
314 year varchar(100) NOT NULL,
319 CREATE INDEX cd_idx_artist ON cd (artist);
321 CREATE INDEX cd_idx_genreid ON cd (genreid);
323 CREATE INDEX cd_idx_single_track ON cd (single_track);
325 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
328 -- Table: collection_object
330 CREATE TABLE collection_object (
331 collection integer NOT NULL,
332 object integer NOT NULL,
333 PRIMARY KEY (collection, object)
336 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
338 CREATE INDEX collection_object_idx_object ON collection_object (object);
343 CREATE TABLE lyrics (
344 lyric_id INTEGER PRIMARY KEY NOT NULL,
345 track_id integer NOT NULL
348 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
353 CREATE TABLE cd_artwork (
354 cd_id INTEGER PRIMARY KEY NOT NULL
358 -- Table: liner_notes
360 CREATE TABLE liner_notes (
361 liner_id INTEGER PRIMARY KEY NOT NULL,
362 notes varchar(100) NOT NULL
366 -- Table: lyric_versions
368 CREATE TABLE lyric_versions (
369 id INTEGER PRIMARY KEY NOT NULL,
370 lyric_id integer NOT NULL,
371 text varchar(100) NOT NULL
374 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
380 tagid INTEGER PRIMARY KEY NOT NULL,
382 tag varchar(100) NOT NULL
385 CREATE INDEX tags_idx_cd ON tags (cd);
388 -- Table: cd_to_producer
390 CREATE TABLE cd_to_producer (
392 producer integer NOT NULL,
394 PRIMARY KEY (cd, producer)
397 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
399 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
404 CREATE TABLE images (
405 id INTEGER PRIMARY KEY NOT NULL,
406 artwork_id integer NOT NULL,
407 name varchar(100) NOT NULL,
411 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
416 CREATE TABLE twokeys (
417 artist integer NOT NULL,
419 PRIMARY KEY (artist, cd)
422 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
425 -- Table: artwork_to_artist
427 CREATE TABLE artwork_to_artist (
428 artwork_cd_id integer NOT NULL,
429 artist_id integer NOT NULL,
430 PRIMARY KEY (artwork_cd_id, artist_id)
433 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
435 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
438 -- Table: fourkeys_to_twokeys
440 CREATE TABLE fourkeys_to_twokeys (
441 f_foo integer NOT NULL,
442 f_bar integer NOT NULL,
443 f_hello integer NOT NULL,
444 f_goodbye integer NOT NULL,
445 t_artist integer NOT NULL,
446 t_cd integer NOT NULL,
447 autopilot character NOT NULL,
448 pilot_sequence integer,
449 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
452 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);
454 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
459 CREATE VIEW year2000cds AS
460 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000";