1 -- Created on Wed Aug 12 16:10:43 2009
11 artistid INTEGER PRIMARY KEY NOT NULL,
13 rank integer NOT NULL DEFAULT '13',
18 -- Table: bindtype_test
20 CREATE TABLE bindtype_test (
21 id INTEGER PRIMARY KEY NOT NULL,
30 CREATE TABLE collection (
31 collectionid INTEGER PRIMARY KEY NOT NULL,
32 name varchar(100) NOT NULL
38 CREATE TABLE employee (
39 employee_id INTEGER PRIMARY KEY NOT NULL,
40 position integer NOT NULL,
50 CREATE TABLE encoded (
51 id INTEGER PRIMARY KEY NOT NULL,
59 id INTEGER PRIMARY KEY NOT NULL,
60 starts_at datetime NOT NULL,
61 created_on timestamp NOT NULL,
62 varchar_date varchar(20),
63 varchar_datetime varchar(20),
64 skip_inflation datetime,
65 ts_without_tz datetime
69 -- Table: file_columns
71 CREATE TABLE file_columns (
72 id INTEGER PRIMARY KEY NOT NULL,
73 file varchar(255) NOT NULL
79 CREATE TABLE fourkeys (
82 hello integer NOT NULL,
83 goodbye integer NOT NULL,
84 sensors character(10) NOT NULL,
86 PRIMARY KEY (foo, bar, hello, goodbye)
93 genreid INTEGER PRIMARY KEY NOT NULL,
94 name varchar(100) NOT NULL
97 CREATE UNIQUE INDEX genre_name ON genre (name);
103 id INTEGER PRIMARY KEY NOT NULL,
111 CREATE TABLE money_test (
112 id INTEGER PRIMARY KEY NOT NULL,
117 -- Table: noprimarykey
119 CREATE TABLE noprimarykey (
120 foo integer NOT NULL,
121 bar integer NOT NULL,
125 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
130 CREATE TABLE onekey (
131 id INTEGER PRIMARY KEY NOT NULL,
132 artist integer NOT NULL,
139 CREATE TABLE owners (
140 id INTEGER PRIMARY KEY NOT NULL,
141 name varchar(100) NOT NULL
147 CREATE TABLE producer (
148 producerid INTEGER PRIMARY KEY NOT NULL,
149 name varchar(100) NOT NULL
152 CREATE UNIQUE INDEX prod_name ON producer (name);
157 CREATE TABLE self_ref (
158 id INTEGER PRIMARY KEY NOT NULL,
159 name varchar(100) NOT NULL
163 -- Table: sequence_test
165 CREATE TABLE sequence_test (
166 pkid1 integer NOT NULL,
167 pkid2 integer NOT NULL,
168 nonpkid integer NOT NULL,
170 PRIMARY KEY (pkid1, pkid2)
176 CREATE TABLE serialized (
177 id INTEGER PRIMARY KEY NOT NULL,
178 serialized text NOT NULL
184 CREATE TABLE treelike (
185 id INTEGER PRIMARY KEY NOT NULL,
187 name varchar(100) NOT NULL
190 CREATE INDEX treelike_idx_parent ON treelike (parent);
193 -- Table: twokeytreelike
195 CREATE TABLE twokeytreelike (
196 id1 integer NOT NULL,
197 id2 integer NOT NULL,
198 parent1 integer NOT NULL,
199 parent2 integer NOT NULL,
200 name varchar(100) NOT NULL,
201 PRIMARY KEY (id1, id2)
204 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
206 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
209 -- Table: typed_object
211 CREATE TABLE typed_object (
212 objectid INTEGER PRIMARY KEY NOT NULL,
213 type varchar(100) NOT NULL,
214 value varchar(100) NOT NULL
218 -- Table: artist_undirected_map
220 CREATE TABLE artist_undirected_map (
221 id1 integer NOT NULL,
222 id2 integer NOT NULL,
223 PRIMARY KEY (id1, id2)
226 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
228 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
233 CREATE TABLE bookmark (
234 id INTEGER PRIMARY KEY NOT NULL,
238 CREATE INDEX bookmark_idx_link ON bookmark (link);
244 id INTEGER PRIMARY KEY NOT NULL,
245 source varchar(100) NOT NULL,
246 owner integer NOT NULL,
247 title varchar(100) NOT NULL,
251 CREATE INDEX books_idx_owner ON books (owner);
254 -- Table: forceforeign
256 CREATE TABLE forceforeign (
257 artist INTEGER PRIMARY KEY NOT NULL,
261 CREATE INDEX forceforeign_idx_artist ON forceforeign (artist);
264 -- Table: self_ref_alias
266 CREATE TABLE self_ref_alias (
267 self_ref integer NOT NULL,
268 alias integer NOT NULL,
269 PRIMARY KEY (self_ref, alias)
272 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
274 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
280 trackid INTEGER PRIMARY KEY NOT NULL,
282 position integer NOT NULL,
283 title varchar(100) NOT NULL,
284 last_updated_on datetime,
285 last_updated_at datetime,
286 small_dt smalldatetime
289 CREATE INDEX track_idx_cd ON track (cd);
291 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
293 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
299 cdid INTEGER PRIMARY KEY NOT NULL,
300 artist integer NOT NULL,
301 title varchar(100) NOT NULL,
302 year varchar(100) NOT NULL,
307 CREATE INDEX cd_idx_artist ON cd (artist);
309 CREATE INDEX cd_idx_genreid ON cd (genreid);
311 CREATE INDEX cd_idx_single_track ON cd (single_track);
313 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
316 -- Table: collection_object
318 CREATE TABLE collection_object (
319 collection integer NOT NULL,
320 object integer NOT NULL,
321 PRIMARY KEY (collection, object)
324 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
326 CREATE INDEX collection_object_idx_object ON collection_object (object);
331 CREATE TABLE lyrics (
332 lyric_id INTEGER PRIMARY KEY NOT NULL,
333 track_id integer NOT NULL
336 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
341 CREATE TABLE cd_artwork (
342 cd_id INTEGER PRIMARY KEY NOT NULL
345 CREATE INDEX cd_artwork_idx_cd_id ON cd_artwork (cd_id);
348 -- Table: liner_notes
350 CREATE TABLE liner_notes (
351 liner_id INTEGER PRIMARY KEY NOT NULL,
352 notes varchar(100) NOT NULL
355 CREATE INDEX liner_notes_idx_liner_id ON liner_notes (liner_id);
358 -- Table: lyric_versions
360 CREATE TABLE lyric_versions (
361 id INTEGER PRIMARY KEY NOT NULL,
362 lyric_id integer NOT NULL,
363 text varchar(100) NOT NULL
366 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
372 tagid INTEGER PRIMARY KEY NOT NULL,
374 tag varchar(100) NOT NULL
377 CREATE INDEX tags_idx_cd ON tags (cd);
380 -- Table: cd_to_producer
382 CREATE TABLE cd_to_producer (
384 producer integer NOT NULL,
386 PRIMARY KEY (cd, producer)
389 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
391 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
396 CREATE TABLE images (
397 id INTEGER PRIMARY KEY NOT NULL,
398 artwork_id integer NOT NULL,
399 name varchar(100) NOT NULL,
403 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
408 CREATE TABLE twokeys (
409 artist integer NOT NULL,
411 PRIMARY KEY (artist, cd)
414 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
417 -- Table: artwork_to_artist
419 CREATE TABLE artwork_to_artist (
420 artwork_cd_id integer NOT NULL,
421 artist_id integer NOT NULL,
422 PRIMARY KEY (artwork_cd_id, artist_id)
425 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
427 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
430 -- Table: fourkeys_to_twokeys
432 CREATE TABLE fourkeys_to_twokeys (
433 f_foo integer NOT NULL,
434 f_bar integer NOT NULL,
435 f_hello integer NOT NULL,
436 f_goodbye integer NOT NULL,
437 t_artist integer NOT NULL,
438 t_cd integer NOT NULL,
439 autopilot character NOT NULL,
440 pilot_sequence integer,
441 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
444 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);
446 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
451 CREATE VIEW year2000cds AS
452 SELECT cdid, artist, title FROM cd WHERE year ='2000';