2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Tue Aug 25 12:34:34 2009
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,
265 CREATE INDEX forceforeign_idx_artist ON forceforeign (artist);
268 -- Table: self_ref_alias
270 CREATE TABLE self_ref_alias (
271 self_ref integer NOT NULL,
272 alias integer NOT NULL,
273 PRIMARY KEY (self_ref, alias)
276 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
278 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
284 trackid INTEGER PRIMARY KEY NOT NULL,
286 position int NOT NULL,
287 title varchar(100) NOT NULL,
288 last_updated_on datetime,
289 last_updated_at datetime,
290 small_dt smalldatetime
293 CREATE INDEX track_idx_cd ON track (cd);
295 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
297 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
303 cdid INTEGER PRIMARY KEY NOT NULL,
304 artist integer NOT NULL,
305 title varchar(100) NOT NULL,
306 year varchar(100) NOT NULL,
311 CREATE INDEX cd_idx_artist ON cd (artist);
313 CREATE INDEX cd_idx_genreid ON cd (genreid);
315 CREATE INDEX cd_idx_single_track ON cd (single_track);
317 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
320 -- Table: collection_object
322 CREATE TABLE collection_object (
323 collection integer NOT NULL,
324 object integer NOT NULL,
325 PRIMARY KEY (collection, object)
328 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
330 CREATE INDEX collection_object_idx_object ON collection_object (object);
335 CREATE TABLE lyrics (
336 lyric_id INTEGER PRIMARY KEY NOT NULL,
337 track_id integer NOT NULL
340 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
345 CREATE TABLE cd_artwork (
346 cd_id INTEGER PRIMARY KEY NOT NULL
349 CREATE INDEX cd_artwork_idx_cd_id ON cd_artwork (cd_id);
352 -- Table: liner_notes
354 CREATE TABLE liner_notes (
355 liner_id INTEGER PRIMARY KEY NOT NULL,
356 notes varchar(100) NOT NULL
359 CREATE INDEX liner_notes_idx_liner_id ON liner_notes (liner_id);
362 -- Table: lyric_versions
364 CREATE TABLE lyric_versions (
365 id INTEGER PRIMARY KEY NOT NULL,
366 lyric_id integer NOT NULL,
367 text varchar(100) NOT NULL
370 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
376 tagid INTEGER PRIMARY KEY NOT NULL,
378 tag varchar(100) NOT NULL
381 CREATE INDEX tags_idx_cd ON tags (cd);
384 -- Table: cd_to_producer
386 CREATE TABLE cd_to_producer (
388 producer integer NOT NULL,
390 PRIMARY KEY (cd, producer)
393 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
395 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
400 CREATE TABLE images (
401 id INTEGER PRIMARY KEY NOT NULL,
402 artwork_id integer NOT NULL,
403 name varchar(100) NOT NULL,
407 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
412 CREATE TABLE twokeys (
413 artist integer NOT NULL,
415 PRIMARY KEY (artist, cd)
418 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
421 -- Table: artwork_to_artist
423 CREATE TABLE artwork_to_artist (
424 artwork_cd_id integer NOT NULL,
425 artist_id integer NOT NULL,
426 PRIMARY KEY (artwork_cd_id, artist_id)
429 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
431 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
434 -- Table: fourkeys_to_twokeys
436 CREATE TABLE fourkeys_to_twokeys (
437 f_foo integer NOT NULL,
438 f_bar integer NOT NULL,
439 f_hello integer NOT NULL,
440 f_goodbye integer NOT NULL,
441 t_artist integer NOT NULL,
442 t_cd integer NOT NULL,
443 autopilot character NOT NULL,
444 pilot_sequence integer,
445 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
448 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);
450 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
455 CREATE VIEW year2000cds AS
456 SELECT cdid, artist, title FROM cd WHERE year ='2000';