t/lib/sqlite.sql

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