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