2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Sat Jun 27 14:02:39 2009
13 artistid INTEGER PRIMARY KEY NOT NULL,
15 rank integer NOT NULL DEFAULT '13',
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
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,
110 -- Table: noprimarykey
112 CREATE TABLE noprimarykey (
113 foo integer NOT NULL,
114 bar integer NOT NULL,
118 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
123 CREATE TABLE onekey (
124 id INTEGER PRIMARY KEY NOT NULL,
125 artist integer NOT NULL,
132 CREATE TABLE owners (
133 id INTEGER PRIMARY KEY NOT NULL,
134 name varchar(100) NOT NULL
140 CREATE TABLE producer (
141 producerid INTEGER PRIMARY KEY NOT NULL,
142 name varchar(100) NOT NULL
145 CREATE UNIQUE INDEX prod_name ON producer (name);
150 CREATE TABLE self_ref (
151 id INTEGER PRIMARY KEY NOT NULL,
152 name varchar(100) NOT NULL
156 -- Table: sequence_test
158 CREATE TABLE sequence_test (
159 pkid1 integer NOT NULL,
160 pkid2 integer NOT NULL,
161 nonpkid integer NOT NULL,
163 PRIMARY KEY (pkid1, pkid2)
169 CREATE TABLE serialized (
170 id INTEGER PRIMARY KEY NOT NULL,
171 serialized text NOT NULL
177 CREATE TABLE treelike (
178 id INTEGER PRIMARY KEY NOT NULL,
180 name varchar(100) NOT NULL
183 CREATE INDEX treelike_idx_parent ON treelike (parent);
186 -- Table: twokeytreelike
188 CREATE TABLE twokeytreelike (
189 id1 integer NOT NULL,
190 id2 integer NOT NULL,
191 parent1 integer NOT NULL,
192 parent2 integer NOT NULL,
193 name varchar(100) NOT NULL,
194 PRIMARY KEY (id1, id2)
197 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
199 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
202 -- Table: typed_object
204 CREATE TABLE typed_object (
205 objectid INTEGER PRIMARY KEY NOT NULL,
206 type varchar(100) NOT NULL,
207 value varchar(100) NOT NULL
211 -- Table: artist_undirected_map
213 CREATE TABLE artist_undirected_map (
214 id1 integer NOT NULL,
215 id2 integer NOT NULL,
216 PRIMARY KEY (id1, id2)
219 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
221 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
226 CREATE TABLE bookmark (
227 id INTEGER PRIMARY KEY NOT NULL,
228 link integer NOT NULL
231 CREATE INDEX bookmark_idx_link ON bookmark (link);
237 id INTEGER PRIMARY KEY NOT NULL,
238 source varchar(100) NOT NULL,
239 owner integer NOT NULL,
240 title varchar(100) NOT NULL,
244 CREATE INDEX books_idx_owner ON books (owner);
247 -- Table: forceforeign
249 CREATE TABLE forceforeign (
250 artist INTEGER PRIMARY KEY NOT NULL,
254 CREATE INDEX forceforeign_idx_artist ON forceforeign (artist);
257 -- Table: self_ref_alias
259 CREATE TABLE self_ref_alias (
260 self_ref integer NOT NULL,
261 alias integer NOT NULL,
262 PRIMARY KEY (self_ref, alias)
265 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
267 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
273 trackid INTEGER PRIMARY KEY NOT NULL,
275 position integer NOT NULL,
276 title varchar(100) NOT NULL,
277 last_updated_on datetime,
278 last_updated_at datetime
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
337 CREATE INDEX cd_artwork_idx_cd_id ON cd_artwork (cd_id);
340 -- Table: liner_notes
342 CREATE TABLE liner_notes (
343 liner_id INTEGER PRIMARY KEY NOT NULL,
344 notes varchar(100) NOT NULL
347 CREATE INDEX liner_notes_idx_liner_id ON liner_notes (liner_id);
350 -- Table: lyric_versions
352 CREATE TABLE lyric_versions (
353 id INTEGER PRIMARY KEY NOT NULL,
354 lyric_id integer NOT NULL,
355 text varchar(100) NOT NULL
358 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
364 tagid INTEGER PRIMARY KEY NOT NULL,
366 tag varchar(100) NOT NULL
369 CREATE INDEX tags_idx_cd ON tags (cd);
372 -- Table: cd_to_producer
374 CREATE TABLE cd_to_producer (
376 producer integer NOT NULL,
378 PRIMARY KEY (cd, producer)
381 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
383 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
388 CREATE TABLE images (
389 id INTEGER PRIMARY KEY NOT NULL,
390 artwork_id integer NOT NULL,
391 name varchar(100) NOT NULL,
395 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
400 CREATE TABLE twokeys (
401 artist integer NOT NULL,
403 PRIMARY KEY (artist, cd)
406 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
409 -- Table: artwork_to_artist
411 CREATE TABLE artwork_to_artist (
412 artwork_cd_id integer NOT NULL,
413 artist_id integer NOT NULL,
414 PRIMARY KEY (artwork_cd_id, artist_id)
417 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
419 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
422 -- Table: fourkeys_to_twokeys
424 CREATE TABLE fourkeys_to_twokeys (
425 f_foo integer NOT NULL,
426 f_bar integer NOT NULL,
427 f_hello integer NOT NULL,
428 f_goodbye integer NOT NULL,
429 t_artist integer NOT NULL,
430 t_cd integer NOT NULL,
431 autopilot character NOT NULL,
432 pilot_sequence integer,
433 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
436 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);
438 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
443 CREATE VIEW year2000cds AS
444 SELECT cdid, artist, title FROM cd WHERE year ='2000';