1 -- Created on Tue Aug 25 12:34:34 2009
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
71 -- Table: file_columns
73 CREATE TABLE file_columns (
74 id INTEGER PRIMARY KEY NOT NULL,
75 file varchar(255) NOT NULL
81 CREATE TABLE fourkeys (
84 hello integer NOT NULL,
85 goodbye integer NOT NULL,
86 sensors character(10) NOT NULL,
88 PRIMARY KEY (foo, bar, hello, goodbye)
95 genreid INTEGER PRIMARY KEY NOT NULL,
96 name varchar(100) NOT NULL
99 CREATE UNIQUE INDEX genre_name ON genre (name);
105 id INTEGER PRIMARY KEY NOT NULL,
113 CREATE TABLE money_test (
114 id INTEGER PRIMARY KEY NOT NULL,
119 -- Table: noprimarykey
121 CREATE TABLE noprimarykey (
122 foo integer NOT NULL,
123 bar integer NOT NULL,
127 CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);
132 CREATE TABLE onekey (
133 id INTEGER PRIMARY KEY NOT NULL,
134 artist integer NOT NULL,
141 CREATE TABLE owners (
142 id INTEGER PRIMARY KEY NOT NULL,
143 name varchar(100) NOT NULL
149 CREATE TABLE producer (
150 producerid INTEGER PRIMARY KEY NOT NULL,
151 name varchar(100) NOT NULL
154 CREATE UNIQUE INDEX prod_name ON producer (name);
159 CREATE TABLE self_ref (
160 id INTEGER PRIMARY KEY NOT NULL,
161 name varchar(100) NOT NULL
165 -- Table: sequence_test
167 CREATE TABLE sequence_test (
168 pkid1 integer NOT NULL,
169 pkid2 integer NOT NULL,
170 nonpkid integer NOT NULL,
172 PRIMARY KEY (pkid1, pkid2)
178 CREATE TABLE serialized (
179 id INTEGER PRIMARY KEY NOT NULL,
180 serialized text NOT NULL
186 CREATE TABLE treelike (
187 id INTEGER PRIMARY KEY NOT NULL,
189 name varchar(100) NOT NULL
192 CREATE INDEX treelike_idx_parent ON treelike (parent);
195 -- Table: twokeytreelike
197 CREATE TABLE twokeytreelike (
198 id1 integer NOT NULL,
199 id2 integer NOT NULL,
200 parent1 integer NOT NULL,
201 parent2 integer NOT NULL,
202 name varchar(100) NOT NULL,
203 PRIMARY KEY (id1, id2)
206 CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);
208 CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);
211 -- Table: typed_object
213 CREATE TABLE typed_object (
214 objectid INTEGER PRIMARY KEY NOT NULL,
215 type varchar(100) NOT NULL,
216 value varchar(100) NOT NULL
220 -- Table: artist_undirected_map
222 CREATE TABLE artist_undirected_map (
223 id1 integer NOT NULL,
224 id2 integer NOT NULL,
225 PRIMARY KEY (id1, id2)
228 CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);
230 CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);
235 CREATE TABLE bookmark (
236 id INTEGER PRIMARY KEY NOT NULL,
240 CREATE INDEX bookmark_idx_link ON bookmark (link);
246 id INTEGER PRIMARY KEY NOT NULL,
247 source varchar(100) NOT NULL,
248 owner integer NOT NULL,
249 title varchar(100) NOT NULL,
253 CREATE INDEX books_idx_owner ON books (owner);
256 -- Table: forceforeign
258 CREATE TABLE forceforeign (
259 artist INTEGER PRIMARY KEY NOT NULL,
263 CREATE INDEX forceforeign_idx_artist ON forceforeign (artist);
266 -- Table: self_ref_alias
268 CREATE TABLE self_ref_alias (
269 self_ref integer NOT NULL,
270 alias integer NOT NULL,
271 PRIMARY KEY (self_ref, alias)
274 CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);
276 CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);
282 trackid INTEGER PRIMARY KEY NOT NULL,
284 position int NOT NULL,
285 title varchar(100) NOT NULL,
286 last_updated_on datetime,
287 last_updated_at datetime,
288 small_dt smalldatetime
291 CREATE INDEX track_idx_cd ON track (cd);
293 CREATE UNIQUE INDEX track_cd_position ON track (cd, position);
295 CREATE UNIQUE INDEX track_cd_title ON track (cd, title);
301 cdid INTEGER PRIMARY KEY NOT NULL,
302 artist integer NOT NULL,
303 title varchar(100) NOT NULL,
304 year varchar(100) NOT NULL,
309 CREATE INDEX cd_idx_artist ON cd (artist);
311 CREATE INDEX cd_idx_genreid ON cd (genreid);
313 CREATE INDEX cd_idx_single_track ON cd (single_track);
315 CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);
318 -- Table: collection_object
320 CREATE TABLE collection_object (
321 collection integer NOT NULL,
322 object integer NOT NULL,
323 PRIMARY KEY (collection, object)
326 CREATE INDEX collection_object_idx_collection ON collection_object (collection);
328 CREATE INDEX collection_object_idx_object ON collection_object (object);
333 CREATE TABLE lyrics (
334 lyric_id INTEGER PRIMARY KEY NOT NULL,
335 track_id integer NOT NULL
338 CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);
343 CREATE TABLE cd_artwork (
344 cd_id INTEGER PRIMARY KEY NOT NULL
347 CREATE INDEX cd_artwork_idx_cd_id ON cd_artwork (cd_id);
350 -- Table: liner_notes
352 CREATE TABLE liner_notes (
353 liner_id INTEGER PRIMARY KEY NOT NULL,
354 notes varchar(100) NOT NULL
357 CREATE INDEX liner_notes_idx_liner_id ON liner_notes (liner_id);
360 -- Table: lyric_versions
362 CREATE TABLE lyric_versions (
363 id INTEGER PRIMARY KEY NOT NULL,
364 lyric_id integer NOT NULL,
365 text varchar(100) NOT NULL
368 CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);
374 tagid INTEGER PRIMARY KEY NOT NULL,
376 tag varchar(100) NOT NULL
379 CREATE INDEX tags_idx_cd ON tags (cd);
382 -- Table: cd_to_producer
384 CREATE TABLE cd_to_producer (
386 producer integer NOT NULL,
388 PRIMARY KEY (cd, producer)
391 CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);
393 CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);
398 CREATE TABLE images (
399 id INTEGER PRIMARY KEY NOT NULL,
400 artwork_id integer NOT NULL,
401 name varchar(100) NOT NULL,
405 CREATE INDEX images_idx_artwork_id ON images (artwork_id);
410 CREATE TABLE twokeys (
411 artist integer NOT NULL,
413 PRIMARY KEY (artist, cd)
416 CREATE INDEX twokeys_idx_artist ON twokeys (artist);
419 -- Table: artwork_to_artist
421 CREATE TABLE artwork_to_artist (
422 artwork_cd_id integer NOT NULL,
423 artist_id integer NOT NULL,
424 PRIMARY KEY (artwork_cd_id, artist_id)
427 CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);
429 CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);
432 -- Table: fourkeys_to_twokeys
434 CREATE TABLE fourkeys_to_twokeys (
435 f_foo integer NOT NULL,
436 f_bar integer NOT NULL,
437 f_hello integer NOT NULL,
438 f_goodbye integer NOT NULL,
439 t_artist integer NOT NULL,
440 t_cd integer NOT NULL,
441 autopilot character NOT NULL,
442 pilot_sequence integer,
443 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
446 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);
448 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);
453 CREATE VIEW year2000cds AS
454 SELECT cdid, artist, title FROM cd WHERE year ='2000';