BEGIN;
CREATE TABLE "photolog_comment" (
    "revision_id" integer NOT NULL PRIMARY KEY
);
CREATE TABLE "photolog_photo" (
    "photo" varchar(100) NOT NULL,
    "priority" integer unsigned NOT NULL,
    "revision_id" integer NOT NULL PRIMARY KEY
);
CREATE TABLE "photolog_tag" (
    "id" integer NOT NULL PRIMARY KEY,
    "tag" varchar(50) NOT NULL
);
CREATE TABLE "photolog_entry" (
    "date" datetime NOT NULL,
    "author" varchar(255) NOT NULL,
    "entry_link" varchar(255) NOT NULL,
    "title" varchar(255) NOT NULL,
    "body" text NOT NULL,
    "revision_id" integer NOT NULL PRIMARY KEY
);
CREATE TABLE "photolog_thumbnail" (
    "photo_id" integer NOT NULL PRIMARY KEY REFERENCES "photolog_photo" ("revision_id"),
    "width" integer unsigned NOT NULL,
    "height" integer unsigned NOT NULL,
    "revision_id" integer NOT NULL PRIMARY KEY
);
CREATE TABLE "photolog_revision" (
    "id" integer NOT NULL PRIMARY KEY,
    "revision" integer unsigned NOT NULL
);
CREATE TABLE "photolog_photo_tags" (
    "id" integer NOT NULL PRIMARY KEY,
    "photo_id" integer NOT NULL REFERENCES "photolog_photo" ("revision_id"),
    "tag_id" integer NOT NULL REFERENCES "photolog_tag" ("id"),
    UNIQUE ("photo_id", "tag_id")
);
CREATE TABLE "photolog_entry_tags" (
    "id" integer NOT NULL PRIMARY KEY,
    "entry_id" integer NOT NULL REFERENCES "photolog_entry" ("revision_id"),
    "tag_id" integer NOT NULL REFERENCES "photolog_tag" ("id"),
    UNIQUE ("entry_id", "tag_id")
);
CREATE TABLE "photolog_entry_photos" (
    "id" integer NOT NULL PRIMARY KEY,
    "entry_id" integer NOT NULL REFERENCES "photolog_entry" ("revision_id"),
    "photo_id" integer NOT NULL REFERENCES "photolog_photo" ("revision_id"),
    UNIQUE ("entry_id", "photo_id")
);
COMMIT;