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