2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Mon Mar 22 11:08:33 2010
11 artistid INTEGER PRIMARY KEY NOT NULL,
13 rank integer NOT NULL DEFAULT '13',
17 CREATE INDEX artist_name_hookidx ON artist (name);
20 -- Table: bindtype_test
22 CREATE TABLE bindtype_test (
23 id INTEGER PRIMARY KEY NOT NULL,
32 CREATE TABLE collection (
33 collectionid INTEGER PRIMARY KEY NOT NULL,
34 name varchar(100) NOT NULL
40 CREATE TABLE encoded (
41 id INTEGER PRIMARY KEY NOT NULL,
49 id INTEGER PRIMARY KEY NOT NULL,
50 starts_at date NOT NULL,
51 created_on timestamp NOT NULL,
52 varchar_date varchar(20),
53 varchar_datetime varchar(20),
54 skip_inflation datetime,
55 ts_without_tz datetime
59 -- Table: file_columns
61 CREATE TABLE file_columns (
62 id INTEGER PRIMARY KEY NOT NULL,
63 file varchar(255) NOT NULL
69 CREATE TABLE fourkeys (
72 hello integer NOT NULL,
73 goodbye integer NOT NULL,
74 sensors character(10) NOT NULL,
76 PRIMARY KEY (foo, bar, hello, goodbye)
83 genreid INTEGER PRIMARY KEY NOT NULL,
84 name varchar(100) NOT NULL
87 CREATE UNIQUE INDEX genre_name ON genre (name);
93 id INTEGER PRIMARY KEY NOT NULL,
101 CREATE TABLE money_test (
102 id INTEGER PRIMARY KEY NOT NULL,
107 -- Table: noprimarykey
109 CREATE TABLE noprimarykey (
110 foo integer NOT NULL,
111 bar integer NOT NULL,
115 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
120 CREATE TABLE onekey (
121 id INTEGER PRIMARY KEY NOT NULL,
122 artist integer NOT NULL,
129 CREATE TABLE owners (
130 id INTEGER PRIMARY KEY NOT NULL,
131 name varchar(100) NOT NULL
137 CREATE TABLE producer (
138 producerid INTEGER PRIMARY KEY NOT NULL,
139 name varchar(100) NOT NULL
142 CREATE UNIQUE INDEX prod_name ON producer (name);
147 CREATE TABLE self_ref (
148 id INTEGER PRIMARY KEY NOT NULL,
149 name varchar(100) NOT NULL
153 -- Table: sequence_test
155 CREATE TABLE sequence_test (
156 pkid1 integer NOT NULL,
157 pkid2 integer NOT NULL,
158 nonpkid integer NOT NULL,
160 PRIMARY KEY (pkid1, pkid2)
166 CREATE TABLE serialized (
167 id INTEGER PRIMARY KEY NOT NULL,
168 serialized text NOT NULL
172 -- Table: timestamp_primary_key_test
174 CREATE TABLE timestamp_primary_key_test (
175 id timestamp NOT NULL DEFAULT current_timestamp,
182 CREATE TABLE treelike (
183 id INTEGER PRIMARY KEY NOT NULL,
185 name varchar(100) NOT NULL
188 CREATE INDEX treelike_idx_parent ON treelike (parent);
191 -- Table: twokeytreelike
193 CREATE TABLE twokeytreelike (
194 id1 integer NOT NULL,
195 id2 integer NOT NULL,
196 parent1 integer NOT NULL,
197 parent2 integer NOT NULL,
198 name varchar(100) NOT NULL,
199 PRIMARY KEY (id1, id2)
202 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
204 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
207 -- Table: typed_object
209 CREATE TABLE typed_object (
210 objectid INTEGER PRIMARY KEY NOT NULL,
211 type varchar(100) NOT NULL,
212 value varchar(100) NOT NULL
216 -- Table: artist_undirected_map
218 CREATE TABLE artist_undirected_map (
219 id1 integer NOT NULL,
220 id2 integer NOT NULL,
221 PRIMARY KEY (id1, id2)
224 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
226 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
231 CREATE TABLE bookmark (
232 id INTEGER PRIMARY KEY NOT NULL,
236 CREATE INDEX bookmark_idx_link ON bookmark (link);
242 id INTEGER PRIMARY KEY NOT NULL,
243 source varchar(100) NOT NULL,
244 owner integer NOT NULL,
245 title varchar(100) NOT NULL,
249 CREATE INDEX books_idx_owner ON books (owner);
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,
297 small_dt smalldatetime
300 CREATE INDEX track_idx_cd ON track (cd);
302 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
304 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
310 cdid INTEGER PRIMARY KEY NOT NULL,
311 artist integer NOT NULL,
312 title varchar(100) NOT NULL,
313 year varchar(100) NOT NULL,
318 CREATE INDEX cd_idx_artist ON cd (artist);
320 CREATE INDEX cd_idx_genreid ON cd (genreid);
322 CREATE INDEX cd_idx_single_track ON cd (single_track);
324 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
327 -- Table: collection_object
329 CREATE TABLE collection_object (
330 collection integer NOT NULL,
331 object integer NOT NULL,
332 PRIMARY KEY (collection, object)
335 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
337 CREATE INDEX collection_object_idx_object ON collection_object (object);
342 CREATE TABLE lyrics (
343 lyric_id INTEGER PRIMARY KEY NOT NULL,
344 track_id integer NOT NULL
347 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
352 CREATE TABLE cd_artwork (
353 cd_id INTEGER PRIMARY KEY NOT NULL
357 -- Table: liner_notes
359 CREATE TABLE liner_notes (
360 liner_id INTEGER PRIMARY KEY NOT NULL,
361 notes varchar(100) NOT NULL
365 -- Table: lyric_versions
367 CREATE TABLE lyric_versions (
368 id INTEGER PRIMARY KEY NOT NULL,
369 lyric_id integer NOT NULL,
370 text varchar(100) NOT NULL
373 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
379 tagid INTEGER PRIMARY KEY NOT NULL,
381 tag varchar(100) NOT NULL
384 CREATE INDEX tags_idx_cd ON tags (cd);
387 -- Table: cd_to_producer
389 CREATE TABLE cd_to_producer (
391 producer integer NOT NULL,
393 PRIMARY KEY (cd, producer)
396 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
398 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
403 CREATE TABLE images (
404 id INTEGER PRIMARY KEY NOT NULL,
405 artwork_id integer NOT NULL,
406 name varchar(100) NOT NULL,
410 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
415 CREATE TABLE twokeys (
416 artist integer NOT NULL,
418 PRIMARY KEY (artist, cd)
421 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
424 -- Table: artwork_to_artist
426 CREATE TABLE artwork_to_artist (
427 artwork_cd_id integer NOT NULL,
428 artist_id integer NOT NULL,
429 PRIMARY KEY (artwork_cd_id, artist_id)
432 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
434 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
437 -- Table: fourkeys_to_twokeys
439 CREATE TABLE fourkeys_to_twokeys (
440 f_foo integer NOT NULL,
441 f_bar integer NOT NULL,
442 f_hello integer NOT NULL,
443 f_goodbye integer NOT NULL,
444 t_artist integer NOT NULL,
445 t_cd integer NOT NULL,
446 autopilot character NOT NULL,
447 pilot_sequence integer,
448 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
451 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);
453 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
458 CREATE VIEW year2000cds AS
459 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000"