BEGIN;
CREATE TABLE "blog_entry" (
    "id" serial NOT NULL PRIMARY KEY,
    "pub_date" timestamp with time zone NOT NULL,
    "slug" varchar(120) NOT NULL,
    "headline" varchar(255) NOT NULL,
    "summary" varchar(255) NULL,
    "image" varchar(100) NULL,
    "copyright" varchar(255) NULL,
    "body" text NOT NULL,
    "active" boolean NOT NULL,
    "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
    "externalId" integer NULL
);
CREATE TABLE "blog_tag" (
    "id" serial NOT NULL PRIMARY KEY,
    "tag" varchar(255) NOT NULL UNIQUE,
    "slug" varchar(120) NOT NULL UNIQUE
);
CREATE TABLE "blog_pingurl" (
    "id" serial NOT NULL PRIMARY KEY,
    "ping_url" varchar(200) NOT NULL,
    "blog_url" varchar(200) NOT NULL,
    "blog_name" varchar(200) NOT NULL
);
CREATE TABLE "blog_trackbackstatus" (
    "id" serial NOT NULL PRIMARY KEY,
    "entry_id" integer NOT NULL REFERENCES "blog_entry" ("id") DEFERRABLE INITIALLY DEFERRED,
    "link" varchar(300) NOT NULL,
    "trackbackUrl" varchar(200) NOT NULL,
    "attempted" timestamp with time zone NULL,
    "status" varchar(10) NOT NULL,
    "message" text NOT NULL
);
CREATE TABLE "blog_entry_tags" (
    "id" serial NOT NULL PRIMARY KEY,
    "entry_id" integer NOT NULL REFERENCES "blog_entry" ("id") DEFERRABLE INITIALLY DEFERRED,
    "tag_id" integer NOT NULL REFERENCES "blog_tag" ("id") DEFERRABLE INITIALLY DEFERRED,
    UNIQUE ("entry_id", "tag_id")
);
CREATE TABLE "blog_entry_related_entries" (
    "id" serial NOT NULL PRIMARY KEY,
    "from_entry_id" integer NOT NULL REFERENCES "blog_entry" ("id") DEFERRABLE INITIALLY DEFERRED,
    "to_entry_id" integer NOT NULL REFERENCES "blog_entry" ("id") DEFERRABLE INITIALLY DEFERRED,
    UNIQUE ("from_entry_id", "to_entry_id")
);
CREATE INDEX blog_entry_pub_date ON "blog_entry" ("pub_date");
CREATE INDEX blog_entry_slug ON "blog_entry" ("slug");
CREATE INDEX blog_entry_user_id ON "blog_entry" ("user_id");
CREATE UNIQUE INDEX blog_tag_slug ON "blog_tag" ("slug");
CREATE INDEX blog_trackbackstatus_entry_id ON "blog_trackbackstatus" ("entry_id");
COMMIT;