root/blog.sql

--DROP TABLE comments; DROP TABLE entries; DROP TABLE categories; DROP TABLE users;

CREATE TABLE users (
        id serial NOT NULL PRIMARY KEY,
        username text NOT NULL UNIQUE,
        password text NOT NULL,
        type integer DEFAULT 1,
        name text,
        title text,
        description text,
        css text DEFAULT 'blog.css',
        enabled bool NOT NULL DEFAULT true,
        moderate bool NOT NULL DEFAULT true,
        editor bool NOT NULL DEFAULT true
);

CREATE TABLE categories (
        id serial NOT NULL PRIMARY KEY,
        name text NOT NULL,
        description text,
        user_id integer REFERENCES users ON DELETE CASCADE
);

CREATE TABLE entries (
        id serial NOT NULL PRIMARY KEY,
        category integer REFERENCES categories ON DELETE RESTRICT NOT NULL,
        subject text NOT NULL,
        shortsubject text NOT NULL,
        body text NOT NULL,
        "timestamp" timestamp(0) with time zone DEFAULT ('now'::text)::timestamp(0) with time zone, 
        user_id integer REFERENCES users ON DELETE CASCADE NOT NULL
);
CREATE UNIQUE INDEX entries_shortsubject_key ON entries (shortsubject, user_id);

CREATE TABLE comments (
        id serial NOT NULL PRIMARY KEY,
        post integer REFERENCES entries ON DELETE CASCADE NOT NULL,
        "timestamp" timestamp(0) with time zone DEFAULT ('now'::text)::timestamp(0) with time zone, 
        name text NOT NULL,
        email text,
        body text NOT NULL,
        host text NOT NULL,
        moderated bool NOT NULL DEFAULT false,
        spam bool NOT NULL DEFAULT false
);

CREATE TABLE authorised_emails (
        user_id integer REFERENCES users ON DELETE CASCADE NOT NULL,
        email text NOT NULL,
        name text
);
CREATE UNIQUE INDEX authorised_emails_email_key ON authorised_emails (email, user_id);