trunk/example/demo1.sql

-- Gedafe Demo Application 1 - A very simple customers/products/orders database
-- Released as Public Domain. Do with it what you want.

--CREATE DATABASE demo1;
COMMENT ON DATABASE demo1 IS 'Gedafe Demo Application 1';

--###################
-- Gedafe Meta Tables
--###################

DROP TABLE meta_tables;
CREATE TABLE meta_tables (
        -- Table Name
        meta_tables_table       NAME    NOT NULL PRIMARY KEY,
        -- Attribute
        meta_tables_attribute   TEXT    NOT NULL,
        -- Value
        meta_tables_value       TEXT
);
-- standard attributes: filterfirst, hide

DROP TABLE meta_fields;
CREATE TABLE meta_fields (
        -- Table Name
        meta_fields_table       NAME    NOT NULL,
        -- Field Name
        meta_fields_field       NAME    NOT NULL,
        -- Attribute
        meta_fields_attribute   TEXT    NOT NULL,
        -- Value
        meta_fields_value       TEXT
);
-- standard attributes: widget, copy, sortfunc

GRANT SELECT ON meta_fields, meta_tables TO PUBLIC;


--##########
-- Customer
--##########

DROP TABLE customer;
DROP SEQUENCE customer_customer_id_seq;
CREATE TABLE customer (
        customer_id             SERIAL  NOT NULL PRIMARY KEY,
        customer_name           TEXT    CHECK (customer_name != ''),
        customer_address        TEXT    CHECK (customer_address != ''),
        customer_email          TEXT,
        customer_last_modified  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        customer_last_modified_by NAME NOT NULL DEFAULT CURRENT_USER
);
GRANT ALL ON customer TO PUBLIC ;

-- comments
COMMENT ON TABLE customer IS 'Customers';
COMMENT ON COLUMN customer.customer_id IS 'ID';
COMMENT ON COLUMN customer.customer_name IS 'Name';
COMMENT ON COLUMN customer.customer_address IS 'Address';
COMMENT ON COLUMN customer.customer_email IS 'E-mail';
COMMENT ON COLUMN customer.customer_last_modified IS 'Last modified';
COMMENT ON COLUMN customer.customer_last_modified_by IS 'Last modified by';

-- meta information
INSERT INTO meta_fields VALUES ('customer', 'customer_address', 'widget', 'area');
INSERT INTO meta_fields VALUES ('customer', 'customer_email', 'markup', 1);

-- combo-box
DROP VIEW customer_combo;
CREATE VIEW customer_combo AS
        SELECT customer_id AS id,
                customer_id || ' -- ' || customer_name AS text
        FROM customer;
GRANT SELECT ON customer_combo TO PUBLIC ;

-- Log timestamp and user
-- ======================
-- Creates a trigger function which saves timestamp and user of
-- last modification
--  Note: you need to register PL/pgSQL (see top of useful-functions.sql)

DROP FUNCTION update_last_modified();
CREATE FUNCTION update_last_modified()
RETURNS OPAQUE
AS  '
BEGIN
        NEW.customer_last_modified_by = CURRENT_USER;
        NEW.customer_last_modified    = CURRENT_TIMESTAMP;
        RETURN NEW;
END;'
LANGUAGE 'plpgsql';

-- trigger before each row update, for logging last modification time with
-- user who modified each record
DROP TRIGGER customer_last_modified ON customer;
CREATE TRIGGER customer_last_modified
  BEFORE UPDATE
  ON customer FOR EACH ROW
  EXECUTE PROCEDURE update_last_modified();


--#########
-- Product
--#########

DROP TABLE product;
DROP SEQUENCE product_product_id_seq;
CREATE TABLE product (
        product_id              SERIAL  NOT NULL PRIMARY KEY,
        product_hid             CHAR(5) NOT NULL UNIQUE,
        product_description     TEXT    CHECK (product_description != ''),
        product_url             TEXT
);
GRANT ALL ON product TO PUBLIC ;

-- comments
COMMENT ON TABLE product IS 'Products';
COMMENT ON COLUMN product.product_id IS 'ID';
COMMENT ON COLUMN product.product_hid IS 'HID';
COMMENT ON COLUMN product.product_description IS 'Description';
COMMENT ON COLUMN product.product_url IS 'WWW-URL';

-- meta information
INSERT INTO meta_fields VALUES ('product', 'product_description', 'widget', 'area');
INSERT INTO meta_fields VALUES ('product', 'product_url', 'markup', 1);

-- combo-box
DROP VIEW product_combo;
CREATE VIEW product_combo AS
        SELECT product_id AS id,
                product_hid || ' -- ' || product_description AS text
        FROM product;
GRANT SELECT ON product_combo TO PUBLIC ;

--#######
-- Order
--#######

DROP TABLE orders;
DROP SEQUENCE orders_orders_id_seq;
CREATE TABLE orders (
        orders_id               SERIAL  NOT NULL PRIMARY KEY,
        orders_date             DATE    NOT NULL DEFAULT CURRENT_DATE,
        orders_customer         INT4    NOT NULL REFERENCES customer,
        orders_product          INT4    NOT NULL REFERENCES product,
        orders_qty              INT4,
        orders_shipped          BOOLEAN
);
GRANT ALL ON orders TO PUBLIC;

-- comments
COMMENT ON TABLE orders IS 'Orders';
COMMENT ON COLUMN orders.orders_id IS 'ID';
COMMENT ON COLUMN orders.orders_date IS 'Date';
COMMENT ON COLUMN orders.orders_customer IS 'Customer';
COMMENT ON COLUMN orders.orders_product IS 'Product';
COMMENT ON COLUMN orders.orders_qty IS 'Quantity';
COMMENT ON COLUMN orders.orders_shipped IS 'Shipped?';

-- meta information
-- (copy date and customer on the next form while adding)
INSERT INTO meta_fields VALUES ('orders', 'orders_date', 'copy', '1');
INSERT INTO meta_fields VALUES ('orders', 'orders_customer', 'copy', '1');
INSERT INTO meta_fields VALUES ('orders', 'orders_customer', 'widget', 'isearch');

-- presentation view
DROP VIEW orders_list;
CREATE VIEW orders_list AS
        SELECT  orders_id, orders_date, customer_name, orders_qty,
                product_hid, product_description, orders_shipped,
                customer_name || ',' || product_hid AS meta_sort
        FROM    orders, customer, product
        WHERE   customer_id = orders_customer AND
                product_id = orders_product;
GRANT SELECT ON orders_list TO PUBLIC;

--###############################
-- Report: Due Product Shipments
--###############################

DROP VIEW due_shipments_rep;
CREATE VIEW due_shipments_rep AS
        SELECT SUM(orders_qty) AS orders_total, product_hid, product_description
        FROM orders, product
        WHERE orders_product = product_id AND orders_shipped = FALSE
        GROUP BY product_hid, product_description;

COMMENT ON VIEW due_shipments_rep IS 'Due Product Shipments';
COMMENT ON COLUMN due_shipments_rep.orders_total IS 'Orders';

GRANT SELECT ON due_shipments_rep TO PUBLIC;

--#######
-- Filetable
--#######

DROP TABLE filetable;
DROP SEQUENCE filetable_filetable_id_seq;
CREATE TABLE filetable (
        filetable_id            SERIAL  NOT NULL PRIMARY KEY,
        filetable_date          DATE    NOT NULL DEFAULT CURRENT_DATE,
        filetable_file          BYTEA
);

GRANT ALL ON filetable TO PUBLIC;

COMMENT ON TABLE filetable IS 'Filetable';
COMMENT ON COLUMN filetable.filetable_id IS 'ID';
COMMENT ON COLUMN filetable.filetable_date IS 'Date';
COMMENT ON COLUMN filetable.filetable_file IS 'File';

INSERT INTO meta_fields VALUES ('filetable', 'filetable_date', 'widget', 'date(from=2000,to=2005)');


GRANT ALL ON customer_customer_id_seq TO PUBLIC;
GRANT ALL ON product_product_id_seq TO PUBLIC;
GRANT ALL ON orders_orders_id_seq TO PUBLIC;
GRANT ALL ON filetable_filetable_id_seq TO PUBLIC;
1	dpavlin	1	-- Gedafe Demo Application 1 - A very simple customers/products/orders database
2			-- Released as Public Domain. Do with it what you want.
3
4			--CREATE DATABASE demo1;
5			COMMENT ON DATABASE demo1 IS 'Gedafe Demo Application 1';
6
7			--###################
8			-- Gedafe Meta Tables
9			--###################
10
11			DROP TABLE meta_tables;
12			CREATE TABLE meta_tables (
13			-- Table Name
14			meta_tables_table NAME NOT NULL PRIMARY KEY,
15			-- Attribute
16			meta_tables_attribute TEXT NOT NULL,
17			-- Value
18			meta_tables_value TEXT
19			);
20			-- standard attributes: filterfirst, hide
21
22			DROP TABLE meta_fields;
23			CREATE TABLE meta_fields (
24			-- Table Name
25			meta_fields_table NAME NOT NULL,
26			-- Field Name
27			meta_fields_field NAME NOT NULL,
28			-- Attribute
29			meta_fields_attribute TEXT NOT NULL,
30			-- Value
31			meta_fields_value TEXT
32			);
33			-- standard attributes: widget, copy, sortfunc
34
35			GRANT SELECT ON meta_fields, meta_tables TO PUBLIC;
36
37
38			--##########
39			-- Customer
40			--##########
41
42			DROP TABLE customer;
43			DROP SEQUENCE customer_customer_id_seq;
44			CREATE TABLE customer (
45			customer_id SERIAL NOT NULL PRIMARY KEY,
46			customer_name TEXT CHECK (customer_name != ''),
47			customer_address TEXT CHECK (customer_address != ''),
48			customer_email TEXT,
49			customer_last_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
50			customer_last_modified_by NAME NOT NULL DEFAULT CURRENT_USER
51			);
52			GRANT ALL ON customer TO PUBLIC ;
53
54			-- comments
55			COMMENT ON TABLE customer IS 'Customers';
56			COMMENT ON COLUMN customer.customer_id IS 'ID';
57			COMMENT ON COLUMN customer.customer_name IS 'Name';
58			COMMENT ON COLUMN customer.customer_address IS 'Address';
59			COMMENT ON COLUMN customer.customer_email IS 'E-mail';
60			COMMENT ON COLUMN customer.customer_last_modified IS 'Last modified';
61			COMMENT ON COLUMN customer.customer_last_modified_by IS 'Last modified by';
62
63			-- meta information
64			INSERT INTO meta_fields VALUES ('customer', 'customer_address', 'widget', 'area');
65			INSERT INTO meta_fields VALUES ('customer', 'customer_email', 'markup', 1);
66
67			-- combo-box
68			DROP VIEW customer_combo;
69			CREATE VIEW customer_combo AS
70			SELECT customer_id AS id,
71			customer_id \|\| ' -- ' \|\| customer_name AS text
72			FROM customer;
73			GRANT SELECT ON customer_combo TO PUBLIC ;
74
75			-- Log timestamp and user
76			-- ======================
77			-- Creates a trigger function which saves timestamp and user of
78			-- last modification
79			-- Note: you need to register PL/pgSQL (see top of useful-functions.sql)
80
81			DROP FUNCTION update_last_modified();
82			CREATE FUNCTION update_last_modified()
83			RETURNS OPAQUE
84			AS '
85			BEGIN
86			NEW.customer_last_modified_by = CURRENT_USER;
87			NEW.customer_last_modified = CURRENT_TIMESTAMP;
88			RETURN NEW;
89			END;'
90			LANGUAGE 'plpgsql';
91
92			-- trigger before each row update, for logging last modification time with
93			-- user who modified each record
94			DROP TRIGGER customer_last_modified ON customer;
95			CREATE TRIGGER customer_last_modified
96			BEFORE UPDATE
97			ON customer FOR EACH ROW
98			EXECUTE PROCEDURE update_last_modified();
99
100
101			--#########
102			-- Product
103			--#########
104
105			DROP TABLE product;
106			DROP SEQUENCE product_product_id_seq;
107			CREATE TABLE product (
108			product_id SERIAL NOT NULL PRIMARY KEY,
109			product_hid CHAR(5) NOT NULL UNIQUE,
110			product_description TEXT CHECK (product_description != ''),
111			product_url TEXT
112			);
113			GRANT ALL ON product TO PUBLIC ;
114
115			-- comments
116			COMMENT ON TABLE product IS 'Products';
117			COMMENT ON COLUMN product.product_id IS 'ID';
118			COMMENT ON COLUMN product.product_hid IS 'HID';
119			COMMENT ON COLUMN product.product_description IS 'Description';
120			COMMENT ON COLUMN product.product_url IS 'WWW-URL';
121
122			-- meta information
123			INSERT INTO meta_fields VALUES ('product', 'product_description', 'widget', 'area');
124			INSERT INTO meta_fields VALUES ('product', 'product_url', 'markup', 1);
125
126			-- combo-box
127			DROP VIEW product_combo;
128			CREATE VIEW product_combo AS
129			SELECT product_id AS id,
130			product_hid \|\| ' -- ' \|\| product_description AS text
131			FROM product;
132			GRANT SELECT ON product_combo TO PUBLIC ;
133
134			--#######
135			-- Order
136			--#######
137
138			DROP TABLE orders;
139			DROP SEQUENCE orders_orders_id_seq;
140			CREATE TABLE orders (
141			orders_id SERIAL NOT NULL PRIMARY KEY,
142			orders_date DATE NOT NULL DEFAULT CURRENT_DATE,
143			orders_customer INT4 NOT NULL REFERENCES customer,
144			orders_product INT4 NOT NULL REFERENCES product,
145			orders_qty INT4,
146			orders_shipped BOOLEAN
147			);
148			GRANT ALL ON orders TO PUBLIC;
149
150			-- comments
151			COMMENT ON TABLE orders IS 'Orders';
152			COMMENT ON COLUMN orders.orders_id IS 'ID';
153			COMMENT ON COLUMN orders.orders_date IS 'Date';
154			COMMENT ON COLUMN orders.orders_customer IS 'Customer';
155			COMMENT ON COLUMN orders.orders_product IS 'Product';
156			COMMENT ON COLUMN orders.orders_qty IS 'Quantity';
157			COMMENT ON COLUMN orders.orders_shipped IS 'Shipped?';
158
159			-- meta information
160			-- (copy date and customer on the next form while adding)
161			INSERT INTO meta_fields VALUES ('orders', 'orders_date', 'copy', '1');
162			INSERT INTO meta_fields VALUES ('orders', 'orders_customer', 'copy', '1');
163			INSERT INTO meta_fields VALUES ('orders', 'orders_customer', 'widget', 'isearch');
164
165			-- presentation view
166			DROP VIEW orders_list;
167			CREATE VIEW orders_list AS
168			SELECT orders_id, orders_date, customer_name, orders_qty,
169			product_hid, product_description, orders_shipped,
170			customer_name \|\| ',' \|\| product_hid AS meta_sort
171			FROM orders, customer, product
172			WHERE customer_id = orders_customer AND
173			product_id = orders_product;
174			GRANT SELECT ON orders_list TO PUBLIC;
175
176			--###############################
177			-- Report: Due Product Shipments
178			--###############################
179
180			DROP VIEW due_shipments_rep;
181			CREATE VIEW due_shipments_rep AS
182			SELECT SUM(orders_qty) AS orders_total, product_hid, product_description
183			FROM orders, product
184			WHERE orders_product = product_id AND orders_shipped = FALSE
185			GROUP BY product_hid, product_description;
186
187			COMMENT ON VIEW due_shipments_rep IS 'Due Product Shipments';
188			COMMENT ON COLUMN due_shipments_rep.orders_total IS 'Orders';
189
190			GRANT SELECT ON due_shipments_rep TO PUBLIC;
191
192			--#######
193			-- Filetable
194			--#######
195
196			DROP TABLE filetable;
197			DROP SEQUENCE filetable_filetable_id_seq;
198			CREATE TABLE filetable (
199			filetable_id SERIAL NOT NULL PRIMARY KEY,
200			filetable_date DATE NOT NULL DEFAULT CURRENT_DATE,
201			filetable_file BYTEA
202			);
203
204			GRANT ALL ON filetable TO PUBLIC;
205
206			COMMENT ON TABLE filetable IS 'Filetable';
207			COMMENT ON COLUMN filetable.filetable_id IS 'ID';
208			COMMENT ON COLUMN filetable.filetable_date IS 'Date';
209			COMMENT ON COLUMN filetable.filetable_file IS 'File';
210
211			INSERT INTO meta_fields VALUES ('filetable', 'filetable_date', 'widget', 'date(from=2000,to=2005)');
212
213
214
215			GRANT ALL ON customer_customer_id_seq TO PUBLIC;
216			GRANT ALL ON product_product_id_seq TO PUBLIC;
217			GRANT ALL ON orders_orders_id_seq TO PUBLIC;
218			GRANT ALL ON filetable_filetable_id_seq TO PUBLIC;