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; |