2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Sat Oct 16 16:17:01 2010
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);
21 -- Table: bindtype_test
23 CREATE TABLE bindtype_test (
24 id INTEGER PRIMARY KEY NOT NULL,
33 CREATE TABLE collection (
34 collectionid INTEGER PRIMARY KEY NOT NULL,
35 name varchar(100) NOT NULL
41 CREATE TABLE encoded (
42 id INTEGER PRIMARY KEY NOT NULL,
50 id INTEGER PRIMARY KEY NOT NULL,
51 starts_at date NOT NULL,
52 created_on timestamp NOT NULL,
53 varchar_date varchar(20),
54 varchar_datetime varchar(20),
55 skip_inflation datetime,
56 ts_without_tz datetime
60 -- Table: file_columns
62 CREATE TABLE file_columns (
63 id INTEGER PRIMARY KEY NOT NULL,
64 file varchar(255) NOT NULL
70 CREATE TABLE fourkeys (
73 hello integer NOT NULL,
74 goodbye integer NOT NULL,
75 sensors character(10) NOT NULL,
77 PRIMARY KEY (foo, bar, hello, goodbye)
84 genreid INTEGER PRIMARY KEY NOT NULL,
85 name varchar(100) NOT NULL
88 CREATE UNIQUE INDEX genre_name ON genre (name);
94 id INTEGER PRIMARY KEY NOT NULL,
102 CREATE TABLE money_test (
103 id INTEGER PRIMARY KEY NOT NULL,
108 -- Table: noprimarykey
110 CREATE TABLE noprimarykey (
111 foo integer NOT NULL,
112 bar integer NOT NULL,
116 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
121 CREATE TABLE onekey (
122 id INTEGER PRIMARY KEY NOT NULL,
123 artist integer NOT NULL,
130 CREATE TABLE owners (
131 id INTEGER PRIMARY KEY NOT NULL,
132 name varchar(100) NOT NULL
138 CREATE TABLE producer (
139 producerid INTEGER PRIMARY KEY NOT NULL,
140 name varchar(100) NOT NULL
143 CREATE UNIQUE INDEX prod_name ON producer (name);
148 CREATE TABLE self_ref (
149 id INTEGER PRIMARY KEY NOT NULL,
150 name varchar(100) NOT NULL
154 -- Table: sequence_test
156 CREATE TABLE sequence_test (
157 pkid1 integer NOT NULL,
158 pkid2 integer NOT NULL,
159 nonpkid integer NOT NULL,
161 PRIMARY KEY (pkid1, pkid2)
167 CREATE TABLE serialized (
168 id INTEGER PRIMARY KEY NOT NULL,
169 serialized text NOT NULL
173 -- Table: timestamp_primary_key_test
175 CREATE TABLE timestamp_primary_key_test (
176 id timestamp NOT NULL DEFAULT current_timestamp,
183 CREATE TABLE treelike (
184 id INTEGER PRIMARY KEY NOT NULL,
186 name varchar(100) NOT NULL
189 CREATE INDEX treelike_idx_parent ON treelike (parent);
192 -- Table: twokeytreelike
194 CREATE TABLE twokeytreelike (
195 id1 integer NOT NULL,
196 id2 integer NOT NULL,
197 parent1 integer NOT NULL,
198 parent2 integer NOT NULL,
199 name varchar(100) NOT NULL,
200 PRIMARY KEY (id1, id2)
203 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
205 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
208 -- Table: typed_object
210 CREATE TABLE typed_object (
211 objectid INTEGER PRIMARY KEY NOT NULL,
212 type varchar(100) NOT NULL,
213 value varchar(100) NOT NULL
217 -- Table: artist_undirected_map
219 CREATE TABLE artist_undirected_map (
220 id1 integer NOT NULL,
221 id2 integer NOT NULL,
222 PRIMARY KEY (id1, id2)
225 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
227 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
232 CREATE TABLE bookmark (
233 id INTEGER PRIMARY KEY NOT NULL,
237 CREATE INDEX bookmark_idx_link ON bookmark (link);
243 id INTEGER PRIMARY KEY NOT NULL,
244 source varchar(100) NOT NULL,
245 owner integer NOT NULL,
246 title varchar(100) NOT NULL,
250 CREATE INDEX books_idx_owner ON books (owner);
252 CREATE UNIQUE INDEX books_title ON books (title);
257 CREATE TABLE employee (
258 employee_id INTEGER PRIMARY KEY NOT NULL,
259 position integer NOT NULL,
267 CREATE INDEX employee_idx_encoded ON employee (encoded);
270 -- Table: forceforeign
272 CREATE TABLE forceforeign (
273 artist INTEGER PRIMARY KEY NOT NULL,
278 -- Table: self_ref_alias
280 CREATE TABLE self_ref_alias (
281 self_ref integer NOT NULL,
282 alias integer NOT NULL,
283 PRIMARY KEY (self_ref, alias)
286 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
288 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
294 trackid INTEGER PRIMARY KEY NOT NULL,
296 position int NOT NULL,
297 title varchar(100) NOT NULL,
298 last_updated_on datetime,
299 last_updated_at datetime,
300 small_dt smalldatetime
303 CREATE INDEX track_idx_cd ON track (cd);
305 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
307 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
313 cdid INTEGER PRIMARY KEY NOT NULL,
314 artist integer NOT NULL,
315 title varchar(100) NOT NULL,
316 year varchar(100) NOT NULL,
321 CREATE INDEX cd_idx_artist ON cd (artist);
323 CREATE INDEX cd_idx_genreid ON cd (genreid);
325 CREATE INDEX cd_idx_single_track ON cd (single_track);
327 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
330 -- Table: collection_object
332 CREATE TABLE collection_object (
333 collection integer NOT NULL,
334 object integer NOT NULL,
335 PRIMARY KEY (collection, object)
338 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
340 CREATE INDEX collection_object_idx_object ON collection_object (object);
345 CREATE TABLE lyrics (
346 lyric_id INTEGER PRIMARY KEY NOT NULL,
347 track_id integer NOT NULL
350 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
355 CREATE TABLE cd_artwork (
356 cd_id INTEGER PRIMARY KEY NOT NULL
360 -- Table: liner_notes
362 CREATE TABLE liner_notes (
363 liner_id INTEGER PRIMARY KEY NOT NULL,
364 notes varchar(100) NOT NULL
368 -- Table: lyric_versions
370 CREATE TABLE lyric_versions (
371 id INTEGER PRIMARY KEY NOT NULL,
372 lyric_id integer NOT NULL,
373 text varchar(100) NOT NULL
376 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
382 tagid INTEGER PRIMARY KEY NOT NULL,
384 tag varchar(100) NOT NULL
387 CREATE INDEX tags_idx_cd ON tags (cd);
389 CREATE UNIQUE INDEX tagid_cd ON tags (tagid, cd);
391 CREATE UNIQUE INDEX tagid_cd_tag ON tags (tagid, cd, tag);
393 CREATE UNIQUE INDEX tags_tagid_tag ON tags (tagid, tag);
395 CREATE UNIQUE INDEX tags_tagid_tag_cd ON tags (tagid, tag, cd);
398 -- Table: cd_to_producer
400 CREATE TABLE cd_to_producer (
402 producer integer NOT NULL,
404 PRIMARY KEY (cd, producer)
407 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
409 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
414 CREATE TABLE images (
415 id INTEGER PRIMARY KEY NOT NULL,
416 artwork_id integer NOT NULL,
417 name varchar(100) NOT NULL,
421 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
426 CREATE TABLE twokeys (
427 artist integer NOT NULL,
429 PRIMARY KEY (artist, cd)
432 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
435 -- Table: artwork_to_artist
437 CREATE TABLE artwork_to_artist (
438 artwork_cd_id integer NOT NULL,
439 artist_id integer NOT NULL,
440 PRIMARY KEY (artwork_cd_id, artist_id)
443 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
445 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
448 -- Table: fourkeys_to_twokeys
450 CREATE TABLE fourkeys_to_twokeys (
451 f_foo integer NOT NULL,
452 f_bar integer NOT NULL,
453 f_hello integer NOT NULL,
454 f_goodbye integer NOT NULL,
455 t_artist integer NOT NULL,
456 t_cd integer NOT NULL,
457 autopilot character NOT NULL,
458 pilot_sequence integer,
459 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
462 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);
464 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
469 CREATE VIEW year2000cds AS
470 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000";