Ticket #3201: ticket_3201_sample.2.sql

/* $ld: theirry_pg.sql v.1.25 2006/3/28 21:00:12 jstates Exp $
  *
  * This is the table setup necessary for use with Theirry CMS 
  * Copyright (c) 2004-2006 Productivity Media, LLC. <theirry[at]productivitymedia[dot]com>
  *     
  * !! IMPORTANT !!
  * This database application uses triggers to automate the tasks.  Use createlang
  * on the database.
  *
  * Usage -> /path/to/pgsql/bin/createdb -E UTF8 -U useranme -e thierry
  *  -> /path/to/pgsql/bin/createlang plpgsql -U username -d theirry
  */

--create the theirry schema in order to not mess-up the other tables in your database
CREATE SCHEMA ticket;

-- Table for system staff (helping with content) 
CREATE TABLE ticket.staff (
        staff_id serial PRIMARY KEY NOT NULL,
    firstname varchar(100) NOT NULL DEFAULT 0,
    lastname varchar(150) NOT NULL DEFAULT 0,
    username varchar(35) UNIQUE NOT NULL DEFAULT 0,
    identifier varchar(40) UNIQUE NOT NULL,
        email varchar(128) UNIQUE NOT NULL DEFAULT 0,
    password varchar(88) UNIQUE NOT NULL DEFAULT '-',
    company varchar(255) NOT NULL DEFAULT 0,
        department varchar(150) NOT NULL DEFAULT '-',
    address varchar(180) NOT NULL DEFAULT 0,
    address2 varchar(180) NULL DEFAULT '-',
    city varchar(140) NOT NULL DEFAULT '-',
    state_province varchar(150) NOT NULL DEFAULT '-',
    country varchar(80) NOT NULL DEFAULT 0,
    mail_code varchar(15) NOT NULL DEFAULT 0,
    contact_number varchar(100) NOT NULL DEFAULT 0,
    staff_question varchar(255) NOT NULL DEFAULT 0,
    staff_answer varchar(255) NOT NULL DEFAULT 0,
    register_date date NOT NULL,
    last_login timestamptz UNIQUE NOT NULL,
        is_admin boolean NOT NULL DEFAULT FALSE,
    activated boolean NOT NULL DEFAULT FALSE,
    activated_keys varchar NOT NULL DEFAULT 0,
        last_updated timestamptz UNIQUE NULL
);

-- Table for system adminitrators
CREATE TABLE ticket.admin (
        admin_id serial PRIMARY KEY NOT NULL,
        firstname varchar(100) NOT NULL DEFAULT '-',
        lastname varchar(150) NOT NULL DEFAULT '-',
        username varchar(50) NOT NULL DEFAULT '-' REFERENCES ticket.staff(username) ON DELETE CASCADE ON UPDATE CASCADE,
        email varchar(150) UNIQUE NOT NULL DEFAULT '-',
        department varchar(150) NOT NULL DEFAULT '-',
        last_login timestamptz NOT NULL REFERENCES ticket.staff(last_login) ON DELETE CASCADE ON UPDATE CASCADE,
        admin_level smallint NOT NULL DEFAULT 3 CHECK(admin_level = 0 OR admin_level = 1 OR admin_level = 2 OR admin_level = 3),
        staff_id smallint NULL DEFAULT 0 REFERENCES ticket.staff(staff_id) ON DELETE CASCADE ON UPDATE CASCADE,
        mail_comments boolean DEFAULT FALSE,
        mail_trackback boolean DEFAULT FALSE,
        publish_right boolean DEFAULT FALSE,
        last_updated timestamptz NULL REFERENCES ticket.staff(last_updated) ON DELETE CASCADE ON UPDATE CASCADE
);

--Table for the users to the site
CREATE TABLE ticket.users (
    user_id serial PRIMARY KEY NOT NULL,
    firstname varchar(100) NOT NULL DEFAULT '-',
    lastname varchar(100) NOT NULL DEFAULT '-',
    username varchar(50) NOT NULL DEFAULT '-',
    password varchar(88) NOT NULL DEFAULT '-',
    identifier varchar(40) UNIQUE NOT NULL,
    email varchar(150) UNIQUE NOT NULL DEFAULT '-',
    country varchar(255) NOT NULL,
    token varchar(40) NOT NULL
);

-- Table for site categories
CREATE TABLE ticket.categories (
        category_id serial PRIMARY KEY NOT NULL,
        name varchar(255) NULL,
        icon varchar(255) NULL,
        description text NULL,
        author_id smallint NOT NULL REFERENCES ticket.staff(staff_id) ON DELETE CASCADE ON UPDATE CASCADE, 
        category_left smallint NULL DEFAULT '0',
        catefory_right smallint NULL DEFAULT '0',
        parent_id smallint NOT NULL DEFAULT '0'
);