2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Tue Jan 19 12:46:12 2010
13 artistid INTEGER PRIMARY KEY NOT NULL,
15 rank integer NOT NULL DEFAULT '13',
19 CREATE INDEX artist_name_hookidx ON artist (name);
22 -- Table: bindtype_test
24 CREATE TABLE bindtype_test (
25 id INTEGER PRIMARY KEY NOT NULL,
34 CREATE TABLE collection (
35 collectionid INTEGER PRIMARY KEY NOT NULL,
36 name varchar(100) NOT NULL
42 CREATE TABLE employee (
43 employee_id INTEGER PRIMARY KEY NOT NULL,
44 position integer NOT NULL,
54 CREATE TABLE encoded (
55 id INTEGER PRIMARY KEY NOT NULL,
63 id INTEGER PRIMARY KEY NOT NULL,
64 starts_at datetime NOT NULL,
65 created_on timestamp NOT NULL,
66 varchar_date varchar(20),
67 varchar_datetime varchar(20),
68 skip_inflation datetime,
69 ts_without_tz datetime
73 -- Table: file_columns
75 CREATE TABLE file_columns (
76 id INTEGER PRIMARY KEY NOT NULL,
77 file varchar(255) NOT NULL
83 CREATE TABLE fourkeys (
86 hello integer NOT NULL,
87 goodbye integer NOT NULL,
88 sensors character(10) NOT NULL,
90 PRIMARY KEY (foo, bar, hello, goodbye)
97 genreid INTEGER PRIMARY KEY NOT NULL,
98 name varchar(100) NOT NULL
101 CREATE UNIQUE INDEX genre_name ON genre (name);
107 id INTEGER PRIMARY KEY NOT NULL,
115 CREATE TABLE money_test (
116 id INTEGER PRIMARY KEY NOT NULL,
121 -- Table: noprimarykey
123 CREATE TABLE noprimarykey (
124 foo integer NOT NULL,
125 bar integer NOT NULL,
129 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
134 CREATE TABLE onekey (
135 id INTEGER PRIMARY KEY NOT NULL,
136 artist integer NOT NULL,
143 CREATE TABLE owners (
144 id INTEGER PRIMARY KEY NOT NULL,
145 name varchar(100) NOT NULL
151 CREATE TABLE producer (
152 producerid INTEGER PRIMARY KEY NOT NULL,
153 name varchar(100) NOT NULL
156 CREATE UNIQUE INDEX prod_name ON producer (name);
161 CREATE TABLE self_ref (
162 id INTEGER PRIMARY KEY NOT NULL,
163 name varchar(100) NOT NULL
167 -- Table: sequence_test
169 CREATE TABLE sequence_test (
170 pkid1 integer NOT NULL,
171 pkid2 integer NOT NULL,
172 nonpkid integer NOT NULL,
174 PRIMARY KEY (pkid1, pkid2)
180 CREATE TABLE serialized (
181 id INTEGER PRIMARY KEY NOT NULL,
182 serialized text NOT NULL
188 CREATE TABLE treelike (
189 id INTEGER PRIMARY KEY NOT NULL,
191 name varchar(100) NOT NULL
194 CREATE INDEX treelike_idx_parent ON treelike (parent);
197 -- Table: twokeytreelike
199 CREATE TABLE twokeytreelike (
200 id1 integer NOT NULL,
201 id2 integer NOT NULL,
202 parent1 integer NOT NULL,
203 parent2 integer NOT NULL,
204 name varchar(100) NOT NULL,
205 PRIMARY KEY (id1, id2)
208 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
210 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
213 -- Table: typed_object
215 CREATE TABLE typed_object (
216 objectid INTEGER PRIMARY KEY NOT NULL,
217 type varchar(100) NOT NULL,
218 value varchar(100) NOT NULL
222 -- Table: artist_undirected_map
224 CREATE TABLE artist_undirected_map (
225 id1 integer NOT NULL,
226 id2 integer NOT NULL,
227 PRIMARY KEY (id1, id2)
230 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
232 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
237 CREATE TABLE bookmark (
238 id INTEGER PRIMARY KEY NOT NULL,
242 CREATE INDEX bookmark_idx_link ON bookmark (link);
248 id INTEGER PRIMARY KEY NOT NULL,
249 source varchar(100) NOT NULL,
250 owner integer NOT NULL,
251 title varchar(100) NOT NULL,
255 CREATE INDEX books_idx_owner ON books (owner);
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";