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