1-- 2-- Created by SQL::Translator::Producer::SQLite 3-- Created on Sat Jan 30 19:18:55 2010 4-- 5; 6 7-- 8-- Table: artist 9-- 10CREATE TABLE artist ( 11 artistid INTEGER PRIMARY KEY NOT NULL, 12 name varchar(100), 13 rank integer NOT NULL DEFAULT '13', 14 charfield char(10) 15); 16 17CREATE INDEX artist_name_hookidx ON artist (name); 18 19-- 20-- Table: bindtype_test 21-- 22CREATE TABLE bindtype_test ( 23 id INTEGER PRIMARY KEY NOT NULL, 24 bytea blob, 25 blob blob, 26 clob clob 27); 28 29-- 30-- Table: collection 31-- 32CREATE TABLE collection ( 33 collectionid INTEGER PRIMARY KEY NOT NULL, 34 name varchar(100) NOT NULL 35); 36 37-- 38-- Table: employee 39-- 40CREATE TABLE employee ( 41 employee_id INTEGER PRIMARY KEY NOT NULL, 42 position integer NOT NULL, 43 group_id integer, 44 group_id_2 integer, 45 group_id_3 integer, 46 name varchar(100) 47); 48 49-- 50-- Table: encoded 51-- 52CREATE TABLE encoded ( 53 id INTEGER PRIMARY KEY NOT NULL, 54 encoded varchar(100) 55); 56 57-- 58-- Table: event 59-- 60CREATE TABLE event ( 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 68); 69 70-- 71-- Table: file_columns 72-- 73CREATE TABLE file_columns ( 74 id INTEGER PRIMARY KEY NOT NULL, 75 file varchar(255) NOT NULL 76); 77 78-- 79-- Table: fourkeys 80-- 81CREATE TABLE fourkeys ( 82 foo integer NOT NULL, 83 bar integer NOT NULL, 84 hello integer NOT NULL, 85 goodbye integer NOT NULL, 86 sensors character(10) NOT NULL, 87 read_count integer, 88 PRIMARY KEY (foo, bar, hello, goodbye) 89); 90 91-- 92-- Table: genre 93-- 94CREATE TABLE genre ( 95 genreid INTEGER PRIMARY KEY NOT NULL, 96 name varchar(100) NOT NULL 97); 98 99CREATE UNIQUE INDEX genre_name ON genre (name); 100 101-- 102-- Table: link 103-- 104CREATE TABLE link ( 105 id INTEGER PRIMARY KEY NOT NULL, 106 url varchar(100), 107 title varchar(100) 108); 109 110-- 111-- Table: money_test 112-- 113CREATE TABLE money_test ( 114 id INTEGER PRIMARY KEY NOT NULL, 115 amount money 116); 117 118-- 119-- Table: noprimarykey 120-- 121CREATE TABLE noprimarykey ( 122 foo integer NOT NULL, 123 bar integer NOT NULL, 124 baz integer NOT NULL 125); 126 127CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar); 128 129-- 130-- Table: onekey 131-- 132CREATE TABLE onekey ( 133 id INTEGER PRIMARY KEY NOT NULL, 134 artist integer NOT NULL, 135 cd integer NOT NULL 136); 137 138-- 139-- Table: owners 140-- 141CREATE TABLE owners ( 142 id INTEGER PRIMARY KEY NOT NULL, 143 name varchar(100) NOT NULL 144); 145 146-- 147-- Table: producer 148-- 149CREATE TABLE producer ( 150 producerid INTEGER PRIMARY KEY NOT NULL, 151 name varchar(100) NOT NULL 152); 153 154CREATE UNIQUE INDEX prod_name ON producer (name); 155 156-- 157-- Table: self_ref 158-- 159CREATE TABLE self_ref ( 160 id INTEGER PRIMARY KEY NOT NULL, 161 name varchar(100) NOT NULL 162); 163 164-- 165-- Table: sequence_test 166-- 167CREATE TABLE sequence_test ( 168 pkid1 integer NOT NULL, 169 pkid2 integer NOT NULL, 170 nonpkid integer NOT NULL, 171 name varchar(100), 172 PRIMARY KEY (pkid1, pkid2) 173); 174 175-- 176-- Table: serialized 177-- 178CREATE TABLE serialized ( 179 id INTEGER PRIMARY KEY NOT NULL, 180 serialized text NOT NULL 181); 182 183-- 184-- Table: treelike 185-- 186CREATE TABLE treelike ( 187 id INTEGER PRIMARY KEY NOT NULL, 188 parent integer, 189 name varchar(100) NOT NULL 190); 191 192CREATE INDEX treelike_idx_parent ON treelike (parent); 193 194-- 195-- Table: twokeytreelike 196-- 197CREATE 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) 204); 205 206CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2); 207 208CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name); 209 210-- 211-- Table: typed_object 212-- 213CREATE TABLE typed_object ( 214 objectid INTEGER PRIMARY KEY NOT NULL, 215 type varchar(100) NOT NULL, 216 value varchar(100) NOT NULL 217); 218 219-- 220-- Table: artist_undirected_map 221-- 222CREATE TABLE artist_undirected_map ( 223 id1 integer NOT NULL, 224 id2 integer NOT NULL, 225 PRIMARY KEY (id1, id2) 226); 227 228CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1); 229 230CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2); 231 232-- 233-- Table: bookmark 234-- 235CREATE TABLE bookmark ( 236 id INTEGER PRIMARY KEY NOT NULL, 237 link integer 238); 239 240CREATE INDEX bookmark_idx_link ON bookmark (link); 241 242-- 243-- Table: books 244-- 245CREATE TABLE books ( 246 id INTEGER PRIMARY KEY NOT NULL, 247 source varchar(100) NOT NULL, 248 owner integer NOT NULL, 249 title varchar(100) NOT NULL, 250 price integer 251); 252 253CREATE INDEX books_idx_owner ON books (owner); 254 255-- 256-- Table: forceforeign 257-- 258CREATE TABLE forceforeign ( 259 artist INTEGER PRIMARY KEY NOT NULL, 260 cd integer NOT NULL 261); 262 263-- 264-- Table: self_ref_alias 265-- 266CREATE TABLE self_ref_alias ( 267 self_ref integer NOT NULL, 268 alias integer NOT NULL, 269 PRIMARY KEY (self_ref, alias) 270); 271 272CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias); 273 274CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref); 275 276-- 277-- Table: track 278-- 279CREATE TABLE track ( 280 trackid INTEGER PRIMARY KEY NOT NULL, 281 cd integer NOT NULL, 282 position int NOT NULL, 283 title varchar(100) NOT NULL, 284 last_updated_on datetime, 285 last_updated_at datetime, 286 small_dt smalldatetime 287); 288 289CREATE INDEX track_idx_cd ON track (cd); 290 291CREATE UNIQUE INDEX track_cd_position ON track (cd, position); 292 293CREATE UNIQUE INDEX track_cd_title ON track (cd, title); 294 295-- 296-- Table: cd 297-- 298CREATE TABLE cd ( 299 cdid INTEGER PRIMARY KEY NOT NULL, 300 artist integer NOT NULL, 301 title varchar(100) NOT NULL, 302 year varchar(100) NOT NULL, 303 genreid integer, 304 single_track integer 305); 306 307CREATE INDEX cd_idx_artist ON cd (artist); 308 309CREATE INDEX cd_idx_genreid ON cd (genreid); 310 311CREATE INDEX cd_idx_single_track ON cd (single_track); 312 313CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title); 314 315-- 316-- Table: collection_object 317-- 318CREATE TABLE collection_object ( 319 collection integer NOT NULL, 320 object integer NOT NULL, 321 PRIMARY KEY (collection, object) 322); 323 324CREATE INDEX collection_object_idx_collection ON collection_object (collection); 325 326CREATE INDEX collection_object_idx_object ON collection_object (object); 327 328-- 329-- Table: lyrics 330-- 331CREATE TABLE lyrics ( 332 lyric_id INTEGER PRIMARY KEY NOT NULL, 333 track_id integer NOT NULL 334); 335 336CREATE INDEX lyrics_idx_track_id ON lyrics (track_id); 337 338-- 339-- Table: cd_artwork 340-- 341CREATE TABLE cd_artwork ( 342 cd_id INTEGER PRIMARY KEY NOT NULL 343); 344 345-- 346-- Table: liner_notes 347-- 348CREATE TABLE liner_notes ( 349 liner_id INTEGER PRIMARY KEY NOT NULL, 350 notes varchar(100) NOT NULL 351); 352 353-- 354-- Table: lyric_versions 355-- 356CREATE TABLE lyric_versions ( 357 id INTEGER PRIMARY KEY NOT NULL, 358 lyric_id integer NOT NULL, 359 text varchar(100) NOT NULL 360); 361 362CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id); 363 364-- 365-- Table: tags 366-- 367CREATE TABLE tags ( 368 tagid INTEGER PRIMARY KEY NOT NULL, 369 cd integer NOT NULL, 370 tag varchar(100) NOT NULL 371); 372 373CREATE INDEX tags_idx_cd ON tags (cd); 374 375-- 376-- Table: cd_to_producer 377-- 378CREATE TABLE cd_to_producer ( 379 cd integer NOT NULL, 380 producer integer NOT NULL, 381 attribute integer, 382 PRIMARY KEY (cd, producer) 383); 384 385CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd); 386 387CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer); 388 389-- 390-- Table: images 391-- 392CREATE TABLE images ( 393 id INTEGER PRIMARY KEY NOT NULL, 394 artwork_id integer NOT NULL, 395 name varchar(100) NOT NULL, 396 data blob 397); 398 399CREATE INDEX images_idx_artwork_id ON images (artwork_id); 400 401-- 402-- Table: twokeys 403-- 404CREATE TABLE twokeys ( 405 artist integer NOT NULL, 406 cd integer NOT NULL, 407 PRIMARY KEY (artist, cd) 408); 409 410CREATE INDEX twokeys_idx_artist ON twokeys (artist); 411 412-- 413-- Table: artwork_to_artist 414-- 415CREATE TABLE artwork_to_artist ( 416 artwork_cd_id integer NOT NULL, 417 artist_id integer NOT NULL, 418 PRIMARY KEY (artwork_cd_id, artist_id) 419); 420 421CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id); 422 423CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id); 424 425-- 426-- Table: fourkeys_to_twokeys 427-- 428CREATE TABLE fourkeys_to_twokeys ( 429 f_foo integer NOT NULL, 430 f_bar integer NOT NULL, 431 f_hello integer NOT NULL, 432 f_goodbye integer NOT NULL, 433 t_artist integer NOT NULL, 434 t_cd integer NOT NULL, 435 autopilot character NOT NULL, 436 pilot_sequence integer, 437 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd) 438); 439 440CREATE 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 442CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd); 443 444-- 445-- View: year2000cds 446-- 447CREATE VIEW year2000cds AS 448 SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000"