2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Thu Feb 11 07:23: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,
30 -- Table: bindtype_test2
32 CREATE TABLE bindtype_test2 (
33 id INTEGER PRIMARY KEY NOT NULL,
42 CREATE TABLE collection (
43 collectionid INTEGER PRIMARY KEY NOT NULL,
44 name varchar(100) NOT NULL
50 CREATE TABLE employee (
51 employee_id INTEGER PRIMARY KEY NOT NULL,
52 position integer NOT NULL,
62 CREATE TABLE encoded (
63 id INTEGER PRIMARY KEY NOT NULL,
71 id INTEGER PRIMARY KEY NOT NULL,
72 starts_at date NOT NULL,
73 created_on timestamp NOT NULL,
74 varchar_date varchar(20),
75 varchar_datetime varchar(20),
76 skip_inflation datetime,
77 ts_without_tz datetime
81 -- Table: file_columns
83 CREATE TABLE file_columns (
84 id INTEGER PRIMARY KEY NOT NULL,
85 file varchar(255) NOT NULL
91 CREATE TABLE fourkeys (
94 hello integer NOT NULL,
95 goodbye integer NOT NULL,
96 sensors character(10) NOT NULL,
98 PRIMARY KEY (foo, bar, hello, goodbye)
105 genreid INTEGER PRIMARY KEY NOT NULL,
106 name varchar(100) NOT NULL
109 CREATE UNIQUE INDEX genre_name ON genre (name);
115 id INTEGER PRIMARY KEY NOT NULL,
123 CREATE TABLE money_test (
124 id INTEGER PRIMARY KEY NOT NULL,
129 -- Table: noprimarykey
131 CREATE TABLE noprimarykey (
132 foo integer NOT NULL,
133 bar integer NOT NULL,
137 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
142 CREATE TABLE onekey (
143 id INTEGER PRIMARY KEY NOT NULL,
144 artist integer NOT NULL,
151 CREATE TABLE owners (
152 id INTEGER PRIMARY KEY NOT NULL,
153 name varchar(100) NOT NULL
159 CREATE TABLE producer (
160 producerid INTEGER PRIMARY KEY NOT NULL,
161 name varchar(100) NOT NULL
164 CREATE UNIQUE INDEX prod_name ON producer (name);
169 CREATE TABLE self_ref (
170 id INTEGER PRIMARY KEY NOT NULL,
171 name varchar(100) NOT NULL
175 -- Table: sequence_test
177 CREATE TABLE sequence_test (
178 pkid1 integer NOT NULL,
179 pkid2 integer NOT NULL,
180 nonpkid integer NOT NULL,
182 PRIMARY KEY (pkid1, pkid2)
188 CREATE TABLE serialized (
189 id INTEGER PRIMARY KEY NOT NULL,
190 serialized text NOT NULL
196 CREATE TABLE treelike (
197 id INTEGER PRIMARY KEY NOT NULL,
199 name varchar(100) NOT NULL
202 CREATE INDEX treelike_idx_parent ON treelike (parent);
205 -- Table: twokeytreelike
207 CREATE TABLE twokeytreelike (
208 id1 integer NOT NULL,
209 id2 integer NOT NULL,
210 parent1 integer NOT NULL,
211 parent2 integer NOT NULL,
212 name varchar(100) NOT NULL,
213 PRIMARY KEY (id1, id2)
216 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
218 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
221 -- Table: typed_object
223 CREATE TABLE typed_object (
224 objectid INTEGER PRIMARY KEY NOT NULL,
225 type varchar(100) NOT NULL,
226 value varchar(100) NOT NULL
230 -- Table: artist_undirected_map
232 CREATE TABLE artist_undirected_map (
233 id1 integer NOT NULL,
234 id2 integer NOT NULL,
235 PRIMARY KEY (id1, id2)
238 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
240 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
245 CREATE TABLE bookmark (
246 id INTEGER PRIMARY KEY NOT NULL,
250 CREATE INDEX bookmark_idx_link ON bookmark (link);
256 id INTEGER PRIMARY KEY NOT NULL,
257 source varchar(100) NOT NULL,
258 owner integer NOT NULL,
259 title varchar(100) NOT NULL,
263 CREATE INDEX books_idx_owner ON books (owner);
266 -- Table: forceforeign
268 CREATE TABLE forceforeign (
269 artist INTEGER PRIMARY KEY NOT NULL,
274 -- Table: self_ref_alias
276 CREATE TABLE self_ref_alias (
277 self_ref integer NOT NULL,
278 alias integer NOT NULL,
279 PRIMARY KEY (self_ref, alias)
282 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
284 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
290 trackid INTEGER PRIMARY KEY NOT NULL,
292 position int NOT NULL,
293 title varchar(100) NOT NULL,
294 last_updated_on datetime,
295 last_updated_at datetime,
296 small_dt smalldatetime
299 CREATE INDEX track_idx_cd ON track (cd);
301 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
303 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
309 cdid INTEGER PRIMARY KEY NOT NULL,
310 artist integer NOT NULL,
311 title varchar(100) NOT NULL,
312 year varchar(100) NOT NULL,
317 CREATE INDEX cd_idx_artist ON cd (artist);
319 CREATE INDEX cd_idx_genreid ON cd (genreid);
321 CREATE INDEX cd_idx_single_track ON cd (single_track);
323 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
326 -- Table: collection_object
328 CREATE TABLE collection_object (
329 collection integer NOT NULL,
330 object integer NOT NULL,
331 PRIMARY KEY (collection, object)
334 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
336 CREATE INDEX collection_object_idx_object ON collection_object (object);
341 CREATE TABLE lyrics (
342 lyric_id INTEGER PRIMARY KEY NOT NULL,
343 track_id integer NOT NULL
346 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
351 CREATE TABLE cd_artwork (
352 cd_id INTEGER PRIMARY KEY NOT NULL
356 -- Table: liner_notes
358 CREATE TABLE liner_notes (
359 liner_id INTEGER PRIMARY KEY NOT NULL,
360 notes varchar(100) NOT NULL
364 -- Table: lyric_versions
366 CREATE TABLE lyric_versions (
367 id INTEGER PRIMARY KEY NOT NULL,
368 lyric_id integer NOT NULL,
369 text varchar(100) NOT NULL
372 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
378 tagid INTEGER PRIMARY KEY NOT NULL,
380 tag varchar(100) NOT NULL
383 CREATE INDEX tags_idx_cd ON tags (cd);
386 -- Table: cd_to_producer
388 CREATE TABLE cd_to_producer (
390 producer integer NOT NULL,
392 PRIMARY KEY (cd, producer)
395 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
397 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
402 CREATE TABLE images (
403 id INTEGER PRIMARY KEY NOT NULL,
404 artwork_id integer NOT NULL,
405 name varchar(100) NOT NULL,
409 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
414 CREATE TABLE twokeys (
415 artist integer NOT NULL,
417 PRIMARY KEY (artist, cd)
420 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
423 -- Table: artwork_to_artist
425 CREATE TABLE artwork_to_artist (
426 artwork_cd_id integer NOT NULL,
427 artist_id integer NOT NULL,
428 PRIMARY KEY (artwork_cd_id, artist_id)
431 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
433 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
436 -- Table: fourkeys_to_twokeys
438 CREATE TABLE fourkeys_to_twokeys (
439 f_foo integer NOT NULL,
440 f_bar integer NOT NULL,
441 f_hello integer NOT NULL,
442 f_goodbye integer NOT NULL,
443 t_artist integer NOT NULL,
444 t_cd integer NOT NULL,
445 autopilot character NOT NULL,
446 pilot_sequence integer,
447 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
450 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);
452 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
457 CREATE VIEW year2000cds AS
458 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000"