/[gedafe]/trunk/example/demo1.sql
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /trunk/example/demo1.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1 - (show annotations)
Mon Feb 14 18:52:26 2005 UTC (19 years, 1 month ago) by dpavlin
File size: 6580 byte(s)
import of Gedafe 1.2.2

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;

  ViewVC Help
Powered by ViewVC 1.1.26