2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Sat Mar 6 18:04:27 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
174 CREATE TABLE treelike (
175 id INTEGER PRIMARY KEY NOT NULL,
177 name varchar(100) NOT NULL
180 CREATE INDEX treelike_idx_parent ON treelike (parent);
183 -- Table: twokeytreelike
185 CREATE TABLE twokeytreelike (
186 id1 integer NOT NULL,
187 id2 integer NOT NULL,
188 parent1 integer NOT NULL,
189 parent2 integer NOT NULL,
190 name varchar(100) NOT NULL,
191 PRIMARY KEY (id1, id2)
194 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
196 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
199 -- Table: typed_object
201 CREATE TABLE typed_object (
202 objectid INTEGER PRIMARY KEY NOT NULL,
203 type varchar(100) NOT NULL,
204 value varchar(100) NOT NULL
208 -- Table: artist_undirected_map
210 CREATE TABLE artist_undirected_map (
211 id1 integer NOT NULL,
212 id2 integer NOT NULL,
213 PRIMARY KEY (id1, id2)
216 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
218 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
223 CREATE TABLE bookmark (
224 id INTEGER PRIMARY KEY NOT NULL,
228 CREATE INDEX bookmark_idx_link ON bookmark (link);
234 id INTEGER PRIMARY KEY NOT NULL,
235 source varchar(100) NOT NULL,
236 owner integer NOT NULL,
237 title varchar(100) NOT NULL,
241 CREATE INDEX books_idx_owner ON books (owner);
246 CREATE TABLE employee (
247 employee_id INTEGER PRIMARY KEY NOT NULL,
248 position integer NOT NULL,
256 CREATE INDEX employee_idx_encoded ON employee (encoded);
259 -- Table: forceforeign
261 CREATE TABLE forceforeign (
262 artist INTEGER PRIMARY KEY NOT NULL,
267 -- Table: self_ref_alias
269 CREATE TABLE self_ref_alias (
270 self_ref integer NOT NULL,
271 alias integer NOT NULL,
272 PRIMARY KEY (self_ref, alias)
275 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
277 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
283 trackid INTEGER PRIMARY KEY NOT NULL,
285 position int NOT NULL,
286 title varchar(100) NOT NULL,
287 last_updated_on datetime,
288 last_updated_at datetime,
289 small_dt smalldatetime
292 CREATE INDEX track_idx_cd ON track (cd);
294 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
296 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
302 cdid INTEGER PRIMARY KEY NOT NULL,
303 artist integer NOT NULL,
304 title varchar(100) NOT NULL,
305 year varchar(100) NOT NULL,
310 CREATE INDEX cd_idx_artist ON cd (artist);
312 CREATE INDEX cd_idx_genreid ON cd (genreid);
314 CREATE INDEX cd_idx_single_track ON cd (single_track);
316 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
319 -- Table: collection_object
321 CREATE TABLE collection_object (
322 collection integer NOT NULL,
323 object integer NOT NULL,
324 PRIMARY KEY (collection, object)
327 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
329 CREATE INDEX collection_object_idx_object ON collection_object (object);
334 CREATE TABLE lyrics (
335 lyric_id INTEGER PRIMARY KEY NOT NULL,
336 track_id integer NOT NULL
339 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
344 CREATE TABLE cd_artwork (
345 cd_id INTEGER PRIMARY KEY NOT NULL
349 -- Table: liner_notes
351 CREATE TABLE liner_notes (
352 liner_id INTEGER PRIMARY KEY NOT NULL,
353 notes varchar(100) NOT NULL
357 -- Table: lyric_versions
359 CREATE TABLE lyric_versions (
360 id INTEGER PRIMARY KEY NOT NULL,
361 lyric_id integer NOT NULL,
362 text varchar(100) NOT NULL
365 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
371 tagid INTEGER PRIMARY KEY NOT NULL,
373 tag varchar(100) NOT NULL
376 CREATE INDEX tags_idx_cd ON tags (cd);
379 -- Table: cd_to_producer
381 CREATE TABLE cd_to_producer (
383 producer integer NOT NULL,
385 PRIMARY KEY (cd, producer)
388 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
390 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
395 CREATE TABLE images (
396 id INTEGER PRIMARY KEY NOT NULL,
397 artwork_id integer NOT NULL,
398 name varchar(100) NOT NULL,
402 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
407 CREATE TABLE twokeys (
408 artist integer NOT NULL,
410 PRIMARY KEY (artist, cd)
413 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
416 -- Table: artwork_to_artist
418 CREATE TABLE artwork_to_artist (
419 artwork_cd_id integer NOT NULL,
420 artist_id integer NOT NULL,
421 PRIMARY KEY (artwork_cd_id, artist_id)
424 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
426 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
429 -- Table: fourkeys_to_twokeys
431 CREATE TABLE fourkeys_to_twokeys (
432 f_foo integer NOT NULL,
433 f_bar integer NOT NULL,
434 f_hello integer NOT NULL,
435 f_goodbye integer NOT NULL,
436 t_artist integer NOT NULL,
437 t_cd integer NOT NULL,
438 autopilot character NOT NULL,
439 pilot_sequence integer,
440 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
443 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);
445 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
450 CREATE VIEW year2000cds AS
451 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000"