2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Sat Mar 6 11:57:45 2010
10 artistid INTEGER PRIMARY KEY NOT NULL,
12 rank integer NOT NULL DEFAULT '13',
16 CREATE INDEX artist_name_hookidx ON artist (name);
19 -- Table: bindtype_test
21 CREATE TABLE bindtype_test (
22 id INTEGER PRIMARY KEY NOT NULL,
31 CREATE TABLE collection (
32 collectionid INTEGER PRIMARY KEY NOT NULL,
33 name varchar(100) NOT NULL
39 CREATE TABLE employee (
40 employee_id INTEGER PRIMARY KEY NOT NULL,
41 position integer NOT NULL,
51 CREATE TABLE encoded (
52 id INTEGER PRIMARY KEY NOT NULL,
60 id INTEGER PRIMARY KEY NOT NULL,
61 starts_at date NOT NULL,
62 created_on timestamp NOT NULL,
63 varchar_date varchar(20),
64 varchar_datetime varchar(20),
65 skip_inflation datetime,
66 ts_without_tz datetime
70 -- Table: file_columns
72 CREATE TABLE file_columns (
73 id INTEGER PRIMARY KEY NOT NULL,
74 file varchar(255) NOT NULL
80 CREATE TABLE fourkeys (
83 hello integer NOT NULL,
84 goodbye integer NOT NULL,
85 sensors character(10) NOT NULL,
87 PRIMARY KEY (foo, bar, hello, goodbye)
94 genreid INTEGER PRIMARY KEY NOT NULL,
95 name varchar(100) NOT NULL
98 CREATE UNIQUE INDEX genre_name ON genre (name);
104 id INTEGER PRIMARY KEY NOT NULL,
112 CREATE TABLE money_test (
113 id INTEGER PRIMARY KEY NOT NULL,
118 -- Table: noprimarykey
120 CREATE TABLE noprimarykey (
121 foo integer NOT NULL,
122 bar integer NOT NULL,
126 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
131 CREATE TABLE onekey (
132 id INTEGER PRIMARY KEY NOT NULL,
133 artist integer NOT NULL,
140 CREATE TABLE owners (
141 id INTEGER PRIMARY KEY NOT NULL,
142 name varchar(100) NOT NULL
148 CREATE TABLE producer (
149 producerid INTEGER PRIMARY KEY NOT NULL,
150 name varchar(100) NOT NULL
153 CREATE UNIQUE INDEX prod_name ON producer (name);
158 CREATE TABLE self_ref (
159 id INTEGER PRIMARY KEY NOT NULL,
160 name varchar(100) NOT NULL
164 -- Table: sequence_test
166 CREATE TABLE sequence_test (
167 pkid1 integer NOT NULL,
168 pkid2 integer NOT NULL,
169 nonpkid integer NOT NULL,
171 PRIMARY KEY (pkid1, pkid2)
177 CREATE TABLE serialized (
178 id INTEGER PRIMARY KEY NOT NULL,
179 serialized text NOT NULL
185 CREATE TABLE treelike (
186 id INTEGER PRIMARY KEY NOT NULL,
188 name varchar(100) NOT NULL
191 CREATE INDEX treelike_idx_parent ON treelike (parent);
194 -- Table: twokeytreelike
196 CREATE TABLE twokeytreelike (
197 id1 integer NOT NULL,
198 id2 integer NOT NULL,
199 parent1 integer NOT NULL,
200 parent2 integer NOT NULL,
201 name varchar(100) NOT NULL,
202 PRIMARY KEY (id1, id2)
205 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
207 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
210 -- Table: typed_object
212 CREATE TABLE typed_object (
213 objectid INTEGER PRIMARY KEY NOT NULL,
214 type varchar(100) NOT NULL,
215 value varchar(100) NOT NULL
219 -- Table: artist_undirected_map
221 CREATE TABLE artist_undirected_map (
222 id1 integer NOT NULL,
223 id2 integer NOT NULL,
224 PRIMARY KEY (id1, id2)
227 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
229 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
234 CREATE TABLE bookmark (
235 id INTEGER PRIMARY KEY NOT NULL,
239 CREATE INDEX bookmark_idx_link ON bookmark (link);
245 id INTEGER PRIMARY KEY NOT NULL,
246 source varchar(100) NOT NULL,
247 owner integer NOT NULL,
248 title varchar(100) NOT NULL,
252 CREATE INDEX books_idx_owner ON books (owner);
255 -- Table: forceforeign
257 CREATE TABLE forceforeign (
258 artist INTEGER PRIMARY KEY NOT NULL,
263 -- Table: self_ref_alias
265 CREATE TABLE self_ref_alias (
266 self_ref integer NOT NULL,
267 alias integer NOT NULL,
268 PRIMARY KEY (self_ref, alias)
271 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
273 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
279 trackid INTEGER PRIMARY KEY NOT NULL,
281 position int NOT NULL,
282 title varchar(100) NOT NULL,
283 last_updated_on datetime,
284 last_updated_at datetime,
285 small_dt smalldatetime
288 CREATE INDEX track_idx_cd ON track (cd);
290 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
292 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
298 cdid INTEGER PRIMARY KEY NOT NULL,
299 artist integer NOT NULL,
300 title varchar(100) NOT NULL,
301 year varchar(100) NOT NULL,
306 CREATE INDEX cd_idx_artist ON cd (artist);
308 CREATE INDEX cd_idx_genreid ON cd (genreid);
310 CREATE INDEX cd_idx_single_track ON cd (single_track);
312 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
315 -- Table: collection_object
317 CREATE TABLE collection_object (
318 collection integer NOT NULL,
319 object integer NOT NULL,
320 PRIMARY KEY (collection, object)
323 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
325 CREATE INDEX collection_object_idx_object ON collection_object (object);
330 CREATE TABLE lyrics (
331 lyric_id INTEGER PRIMARY KEY NOT NULL,
332 track_id integer NOT NULL
335 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
340 CREATE TABLE cd_artwork (
341 cd_id INTEGER PRIMARY KEY NOT NULL
345 -- Table: liner_notes
347 CREATE TABLE liner_notes (
348 liner_id INTEGER PRIMARY KEY NOT NULL,
349 notes varchar(100) NOT NULL
353 -- Table: lyric_versions
355 CREATE TABLE lyric_versions (
356 id INTEGER PRIMARY KEY NOT NULL,
357 lyric_id integer NOT NULL,
358 text varchar(100) NOT NULL
361 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
367 tagid INTEGER PRIMARY KEY NOT NULL,
369 tag varchar(100) NOT NULL
372 CREATE INDEX tags_idx_cd ON tags (cd);
375 -- Table: cd_to_producer
377 CREATE TABLE cd_to_producer (
379 producer integer NOT NULL,
381 PRIMARY KEY (cd, producer)
384 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
386 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
391 CREATE TABLE images (
392 id INTEGER PRIMARY KEY NOT NULL,
393 artwork_id integer NOT NULL,
394 name varchar(100) NOT NULL,
398 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
403 CREATE TABLE twokeys (
404 artist integer NOT NULL,
406 PRIMARY KEY (artist, cd)
409 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
412 -- Table: artwork_to_artist
414 CREATE TABLE artwork_to_artist (
415 artwork_cd_id integer NOT NULL,
416 artist_id integer NOT NULL,
417 PRIMARY KEY (artwork_cd_id, artist_id)
420 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
422 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
425 -- Table: fourkeys_to_twokeys
427 CREATE TABLE fourkeys_to_twokeys (
428 f_foo integer NOT NULL,
429 f_bar integer NOT NULL,
430 f_hello integer NOT NULL,
431 f_goodbye integer NOT NULL,
432 t_artist integer NOT NULL,
433 t_cd integer NOT NULL,
434 autopilot character NOT NULL,
435 pilot_sequence integer,
436 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
439 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);
441 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
446 CREATE VIEW year2000cds AS
447 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000"