Ticket #3201: ticket_3201_sample.2.sql

File ticket_3201_sample.2.sql, 3.5 KB (added by j.states@…, 18 years ago)

Use this to create a schema and table, After creating the schema, change your settings to that schema and table, then try inspectdb. The errors should be replicated.

Line 
1/* $ld: theirry_pg.sql v.1.25 2006/3/28 21:00:12 jstates Exp $
2 *
3 * This is the table setup necessary for use with Theirry CMS
4 * Copyright (c) 2004-2006 Productivity Media, LLC. <theirry[at]productivitymedia[dot]com>
5 *
6 * !! IMPORTANT !!
7 * This database application uses triggers to automate the tasks. Use createlang
8 * on the database.
9 *
10 * Usage -> /path/to/pgsql/bin/createdb -E UTF8 -U useranme -e thierry
11 * -> /path/to/pgsql/bin/createlang plpgsql -U username -d theirry
12 */
13
14--create the theirry schema in order to not mess-up the other tables in your database
15CREATE SCHEMA ticket;
16
17-- Table for system staff (helping with content)
18CREATE TABLE ticket.staff (
19 staff_id serial PRIMARY KEY NOT NULL,
20 firstname varchar(100) NOT NULL DEFAULT 0,
21 lastname varchar(150) NOT NULL DEFAULT 0,
22 username varchar(35) UNIQUE NOT NULL DEFAULT 0,
23 identifier varchar(40) UNIQUE NOT NULL,
24 email varchar(128) UNIQUE NOT NULL DEFAULT 0,
25 password varchar(88) UNIQUE NOT NULL DEFAULT '-',
26 company varchar(255) NOT NULL DEFAULT 0,
27 department varchar(150) NOT NULL DEFAULT '-',
28 address varchar(180) NOT NULL DEFAULT 0,
29 address2 varchar(180) NULL DEFAULT '-',
30 city varchar(140) NOT NULL DEFAULT '-',
31 state_province varchar(150) NOT NULL DEFAULT '-',
32 country varchar(80) NOT NULL DEFAULT 0,
33 mail_code varchar(15) NOT NULL DEFAULT 0,
34 contact_number varchar(100) NOT NULL DEFAULT 0,
35 staff_question varchar(255) NOT NULL DEFAULT 0,
36 staff_answer varchar(255) NOT NULL DEFAULT 0,
37 register_date date NOT NULL,
38 last_login timestamptz UNIQUE NOT NULL,
39 is_admin boolean NOT NULL DEFAULT FALSE,
40 activated boolean NOT NULL DEFAULT FALSE,
41 activated_keys varchar NOT NULL DEFAULT 0,
42 last_updated timestamptz UNIQUE NULL
43);
44
45-- Table for system adminitrators
46CREATE TABLE ticket.admin (
47 admin_id serial PRIMARY KEY NOT NULL,
48 firstname varchar(100) NOT NULL DEFAULT '-',
49 lastname varchar(150) NOT NULL DEFAULT '-',
50 username varchar(50) NOT NULL DEFAULT '-' REFERENCES ticket.staff(username) ON DELETE CASCADE ON UPDATE CASCADE,
51 email varchar(150) UNIQUE NOT NULL DEFAULT '-',
52 department varchar(150) NOT NULL DEFAULT '-',
53 last_login timestamptz NOT NULL REFERENCES ticket.staff(last_login) ON DELETE CASCADE ON UPDATE CASCADE,
54 admin_level smallint NOT NULL DEFAULT 3 CHECK(admin_level = 0 OR admin_level = 1 OR admin_level = 2 OR admin_level = 3),
55 staff_id smallint NULL DEFAULT 0 REFERENCES ticket.staff(staff_id) ON DELETE CASCADE ON UPDATE CASCADE,
56 mail_comments boolean DEFAULT FALSE,
57 mail_trackback boolean DEFAULT FALSE,
58 publish_right boolean DEFAULT FALSE,
59 last_updated timestamptz NULL REFERENCES ticket.staff(last_updated) ON DELETE CASCADE ON UPDATE CASCADE
60);
61
62--Table for the users to the site
63CREATE TABLE ticket.users (
64 user_id serial PRIMARY KEY NOT NULL,
65 firstname varchar(100) NOT NULL DEFAULT '-',
66 lastname varchar(100) NOT NULL DEFAULT '-',
67 username varchar(50) NOT NULL DEFAULT '-',
68 password varchar(88) NOT NULL DEFAULT '-',
69 identifier varchar(40) UNIQUE NOT NULL,
70 email varchar(150) UNIQUE NOT NULL DEFAULT '-',
71 country varchar(255) NOT NULL,
72 token varchar(40) NOT NULL
73);
74
75-- Table for site categories
76CREATE TABLE ticket.categories (
77 category_id serial PRIMARY KEY NOT NULL,
78 name varchar(255) NULL,
79 icon varchar(255) NULL,
80 description text NULL,
81 author_id smallint NOT NULL REFERENCES ticket.staff(staff_id) ON DELETE CASCADE ON UPDATE CASCADE,
82 category_left smallint NULL DEFAULT '0',
83 catefory_right smallint NULL DEFAULT '0',
84 parent_id smallint NOT NULL DEFAULT '0'
85);
Back to Top