gistfile1.sql

DROP TABLE IF EXISTS "user";
CREATE TABLE "user" (
    id serial PRIMARY KEY,
    username varchar(256) NOT NULL,
    email varchar(256) NOT NULL,
    email_verified boolean DEFAULT FALSE,
    created_on timestamp DEFAULT now(),
    admin boolean DEFAULT FALSE
);

DROP TABLE IF EXISTS page_latest;
CREATE TABLE page_latest (
    id serial PRIMARY KEY,
    title varchar(256) NOT NULL,
    slug varchar(256) NOT NULL,
    namespace varchar(64) DEFAULT '',
    content text DEFAULT '',
    num_lines int NOT NULL,
    revision int DEFAULT 0 CONSTRAINT "revision must be positive" CHECK (revision >= 0),
    editor int REFERENCES "user"(id),
    markup varchar(64) DEFAULT 'plain',
    language varchar(8) NOT NULL,
    edited_on timestamp DEFAULT now() NOT NULL
);

DROP TABLE IF EXISTS page_diff;
CREATE TABLE page_diff (
    page_id int, 
    revision int,
    editor int REFERENCES "user"(id),
    created_on timestamp DEFAULT now() NOT NULL,
    comment text DEFAULT '',
    PRIMARY KEY (page_id, revision),
    FOREIGN KEY (page_id) REFERENCES page_latest(id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS page_diff_hunk;
CREATE TABLE page_diff_hunk (
    page_id int,
    revision int,
    start int NOT NULL CONSTRAINT "start must be positive" CHECK (start >= 0),
    content text NOT NULL DEFAULT '',
    lines_added int NOT NULL DEFAULT 0,
    lines_deleted int NOT NULL DEFAULT 0,
    lines_context int NOT NULL DEFAULT 0,
    PRIMARY KEY (page_id, revision, start),
    FOREIGN KEY (page_id, revision) REFERENCES page_diff ON DELETE CASCADE
    -- EXCLUDE USING gist (page_id WITH =, revision WITH =, [overlapping])
    -- Would need to define a page_diff_hunk_meta type, define the "&&" operation
    -- over it, and add a "meta" type to this table. Not worth the efford right now.
    -- See: http://www.pgcon.org/2010/schedule/attachments/136_exclusion_constraints2.pdf
);