2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Sat Mar 6 12:26:40 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 employee (
41 employee_id INTEGER PRIMARY KEY NOT NULL,
42 position integer NOT NULL,
52 CREATE TABLE encoded (
53 id INTEGER PRIMARY KEY NOT NULL,
61 id INTEGER PRIMARY KEY NOT NULL,
62 starts_at datetime NOT NULL,
63 created_on timestamp NOT NULL,
64 varchar_date varchar(20),
65 varchar_datetime varchar(20),
66 skip_inflation datetime,
67 ts_without_tz datetime
73 CREATE TABLE fourkeys (
76 hello integer NOT NULL,
77 goodbye integer NOT NULL,
78 sensors character(10) NOT NULL,
80 PRIMARY KEY (foo, bar, hello, goodbye)
87 genreid INTEGER PRIMARY KEY NOT NULL,
88 name varchar(100) NOT NULL
91 CREATE UNIQUE INDEX genre_name ON genre (name);
97 id INTEGER PRIMARY KEY NOT NULL,
105 CREATE TABLE money_test (
106 id INTEGER PRIMARY KEY NOT NULL,
111 -- Table: noprimarykey
113 CREATE TABLE noprimarykey (
114 foo integer NOT NULL,
115 bar integer NOT NULL,
119 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
124 CREATE TABLE onekey (
125 id INTEGER PRIMARY KEY NOT NULL,
126 artist integer NOT NULL,
133 CREATE TABLE owners (
134 id INTEGER PRIMARY KEY NOT NULL,
135 name varchar(100) NOT NULL
141 CREATE TABLE producer (
142 producerid INTEGER PRIMARY KEY NOT NULL,
143 name varchar(100) NOT NULL
146 CREATE UNIQUE INDEX prod_name ON producer (name);
151 CREATE TABLE self_ref (
152 id INTEGER PRIMARY KEY NOT NULL,
153 name varchar(100) NOT NULL
157 -- Table: sequence_test
159 CREATE TABLE sequence_test (
160 pkid1 integer NOT NULL,
161 pkid2 integer NOT NULL,
162 nonpkid integer NOT NULL,
164 PRIMARY KEY (pkid1, pkid2)
170 CREATE TABLE serialized (
171 id INTEGER PRIMARY KEY NOT NULL,
172 serialized text NOT NULL
178 CREATE TABLE treelike (
179 id INTEGER PRIMARY KEY NOT NULL,
181 name varchar(100) NOT NULL
184 CREATE INDEX treelike_idx_parent ON treelike (parent);
187 -- Table: twokeytreelike
189 CREATE TABLE twokeytreelike (
190 id1 integer NOT NULL,
191 id2 integer NOT NULL,
192 parent1 integer NOT NULL,
193 parent2 integer NOT NULL,
194 name varchar(100) NOT NULL,
195 PRIMARY KEY (id1, id2)
198 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
200 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
203 -- Table: typed_object
205 CREATE TABLE typed_object (
206 objectid INTEGER PRIMARY KEY NOT NULL,
207 type varchar(100) NOT NULL,
208 value varchar(100) NOT NULL
212 -- Table: artist_undirected_map
214 CREATE TABLE artist_undirected_map (
215 id1 integer NOT NULL,
216 id2 integer NOT NULL,
217 PRIMARY KEY (id1, id2)
220 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
222 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
227 CREATE TABLE bookmark (
228 id INTEGER PRIMARY KEY NOT NULL,
232 CREATE INDEX bookmark_idx_link ON bookmark (link);
238 id INTEGER PRIMARY KEY NOT NULL,
239 source varchar(100) NOT NULL,
240 owner integer NOT NULL,
241 title varchar(100) NOT NULL,
245 CREATE INDEX books_idx_owner ON books (owner);
248 -- Table: forceforeign
250 CREATE TABLE forceforeign (
251 artist INTEGER PRIMARY KEY NOT NULL,
256 -- Table: self_ref_alias
258 CREATE TABLE self_ref_alias (
259 self_ref integer NOT NULL,
260 alias integer NOT NULL,
261 PRIMARY KEY (self_ref, alias)
264 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
266 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
272 trackid INTEGER PRIMARY KEY NOT NULL,
274 position int NOT NULL,
275 title varchar(100) NOT NULL,
276 last_updated_on datetime,
277 last_updated_at datetime,
278 small_dt smalldatetime
281 CREATE INDEX track_idx_cd ON track (cd);
283 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
285 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
291 cdid INTEGER PRIMARY KEY NOT NULL,
292 artist integer NOT NULL,
293 title varchar(100) NOT NULL,
294 year varchar(100) NOT NULL,
299 CREATE INDEX cd_idx_artist ON cd (artist);
301 CREATE INDEX cd_idx_genreid ON cd (genreid);
303 CREATE INDEX cd_idx_single_track ON cd (single_track);
305 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
308 -- Table: collection_object
310 CREATE TABLE collection_object (
311 collection integer NOT NULL,
312 object integer NOT NULL,
313 PRIMARY KEY (collection, object)
316 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
318 CREATE INDEX collection_object_idx_object ON collection_object (object);
323 CREATE TABLE lyrics (
324 lyric_id INTEGER PRIMARY KEY NOT NULL,
325 track_id integer NOT NULL
328 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
333 CREATE TABLE cd_artwork (
334 cd_id INTEGER PRIMARY KEY NOT NULL
338 -- Table: liner_notes
340 CREATE TABLE liner_notes (
341 liner_id INTEGER PRIMARY KEY NOT NULL,
342 notes varchar(100) NOT NULL
346 -- Table: lyric_versions
348 CREATE TABLE lyric_versions (
349 id INTEGER PRIMARY KEY NOT NULL,
350 lyric_id integer NOT NULL,
351 text varchar(100) NOT NULL
354 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
360 tagid INTEGER PRIMARY KEY NOT NULL,
362 tag varchar(100) NOT NULL
365 CREATE INDEX tags_idx_cd ON tags (cd);
368 -- Table: cd_to_producer
370 CREATE TABLE cd_to_producer (
372 producer integer NOT NULL,
374 PRIMARY KEY (cd, producer)
377 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
379 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
384 CREATE TABLE images (
385 id INTEGER PRIMARY KEY NOT NULL,
386 artwork_id integer NOT NULL,
387 name varchar(100) NOT NULL,
391 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
396 CREATE TABLE twokeys (
397 artist integer NOT NULL,
399 PRIMARY KEY (artist, cd)
402 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
405 -- Table: artwork_to_artist
407 CREATE TABLE artwork_to_artist (
408 artwork_cd_id integer NOT NULL,
409 artist_id integer NOT NULL,
410 PRIMARY KEY (artwork_cd_id, artist_id)
413 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
415 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
418 -- Table: fourkeys_to_twokeys
420 CREATE TABLE fourkeys_to_twokeys (
421 f_foo integer NOT NULL,
422 f_bar integer NOT NULL,
423 f_hello integer NOT NULL,
424 f_goodbye integer NOT NULL,
425 t_artist integer NOT NULL,
426 t_cd integer NOT NULL,
427 autopilot character NOT NULL,
428 pilot_sequence integer,
429 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
432 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);
434 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
439 CREATE VIEW year2000cds AS
440 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000"