DATABASE stores_demo
MAIN
DEFINE
load_data SMALLINT,
db_type CHAR(80)
-- EXIT PROGRAM
LET db_type = db_get_database_type()
LET load_data = 1
WHENEVER ERROR CONTINUE
DROP TABLE cust_calls
DROP TABLE call_type
DROP TABLE items
DROP TABLE orders
DROP TABLE customer
DROP TABLE catalog
DROP TABLE some_orders
DROP TABLE stock
DROP TABLE manufact
DROP TABLE custview
DROP TABLE someorders
-- Tables, views and synonyms for new test cases
DROP VIEW view_for_select
DROP SYNONYM syn_for_select
DROP TABLE table_for_select
DROP TABLE table_for_subquery
DROP VIEW view_for_insert
DROP SYNONYM syn_for_insert
DROP TABLE table_for_insert
DROP VIEW view_with_serial
DROP SYNONYM syn_with_serial
DROP TABLE table_with_serial
DROP VIEW view_for_update
DROP SYNONYM syn_for_update
DROP TABLE table_for_update
DROP TABLE table_referenced
DROP VIEW view_for_delete
DROP SYNONYM syn_for_delete
DROP TABLE table_for_delete
DROP TABLE detail_report
DROP TABLE group_report
WHENEVER ERROR STOP
CREATE TABLE customer (
customer_num SERIAL NOT NULL,
fname CHAR(15),
lname CHAR(15),
company CHAR(20),
address1 CHAR(20),
address2 CHAR(20),
city CHAR(15),
state CHAR(2),
zipcode CHAR(5),
phone CHAR(18),
PRIMARY KEY (customer_num)
CONSTRAINT u100_1
)
IF load_data THEN
LOAD FROM "unl/stores_demo/customer.unl" INSERT INTO customer
END IF
CREATE TEMP TABLE orders (
order_num SERIAL NOT NULL,
order_date DATE,
customer_num INTEGER NOT NULL,
ship_instruct CHAR(40),
backlog CHAR(1),
po_num CHAR(10),
ship_date DATE,
ship_weight DECIMAL(8, 2),
ship_charge MONEY(6, 2),
paid_date DATE,
PRIMARY KEY (order_num)
CONSTRAINT u101_3,
FOREIGN KEY (customer_num)
REFERENCES customer (customer_num)
CONSTRAINT r101_4
)
IF load_data THEN
LOAD FROM "unl/stores_demo/orders.unl" INSERT INTO orders
END IF
CREATE TABLE manufact (
manu_code CHAR(3),
manu_name CHAR(15),
lead_time INTERVAL DAY(3) TO DAY,
PRIMARY KEY (manu_code)
CONSTRAINT u102_7
)
IF load_data THEN
LOAD FROM "unl/stores_demo/manufact.unl" INSERT INTO manufact
END IF
CREATE TABLE stock (
stock_num SMALLINT,
manu_code CHAR(3),
description CHAR(15),
unit_price MONEY(6, 2),
unit CHAR(4),
unit_descr CHAR(15),
PRIMARY KEY (stock_num, manu_code)
CONSTRAINT u103_8,
FOREIGN KEY (manu_code)
REFERENCES manufact (manu_code)
CONSTRAINT r103_9
)
IF load_data THEN
LOAD FROM "unl/stores_demo/stock.unl" INSERT INTO stock
END IF
CREATE TABLE items (
item_num SMALLINT,
order_num INTEGER,
stock_num SMALLINT NOT NULL,
manu_code CHAR(3) NOT NULL,
quantity SMALLINT,
total_price MONEY(8, 2),
CHECK ((quantity >= 1 ))
CONSTRAINT c104_15,
PRIMARY KEY (item_num, order_num)
CONSTRAINT u104_10,
FOREIGN KEY (order_num)
REFERENCES orders (order_num)
CONSTRAINT r104_11,
FOREIGN KEY (stock_num, manu_code)
REFERENCES stock (stock_num, manu_code)
CONSTRAINT r104_12
)
IF load_data THEN
LOAD FROM "unl/stores_demo/items.unl" INSERT INTO items
END IF
WHENEVER ERROR CONTINUE
DROP TABLE state
WHENEVER ERROR STOP
CREATE TABLE state (
code CHAR(2),
sname CHAR(15),
PRIMARY KEY (code)
CONSTRAINT u105_16
)
IF load_data THEN
LOAD FROM "unl/stores_demo/state.unl" INSERT INTO state
END IF
WHENEVER ERROR CONTINUE
DROP TABLE call_type
WHENEVER ERROR STOP
CREATE TABLE call_type (
call_code CHAR(1),
code_descr CHAR(30),
PRIMARY KEY (call_code)
CONSTRAINT u106_17
)
IF load_data THEN
LOAD FROM "unl/stores_demo/call_type.unl" INSERT INTO call_type
END IF
WHENEVER ERROR CONTINUE
DROP TABLE cust_calls
WHENEVER ERROR STOP
CREATE TABLE cust_calls (
customer_num INTEGER,
call_dtime DATETIME YEAR TO MINUTE,
user_id CHAR(18),
call_code CHAR(1),
call_descr CHAR(240),
res_dtime DATETIME YEAR TO MINUTE,
res_descr CHAR(240),
PRIMARY KEY (customer_num, call_dtime)
CONSTRAINT u107_18,
FOREIGN KEY (customer_num)
REFERENCES customer (customer_num)
CONSTRAINT r107_19,
FOREIGN KEY (call_code)
REFERENCES call_type (call_code)
CONSTRAINT r107_20
)
IF load_data THEN
LOAD FROM "unl/stores_demo/cust_calls.unl" INSERT INTO cust_calls
END IF
CREATE TABLE custview (
firstname CHAR(15),
lastname CHAR(15),
company CHAR(20),
city CHAR(15)
)
CREATE TABLE someorders (
custnum SERIAL,
ocustnum INTEGER,
newprice MONEY(24, 5)
)
CREATE TABLE catalog (
catalog_num SERIAL(10001),
stock_num SMALLINT NOT NULL,
manu_code CHAR(3) NOT NULL,
cat_descr TEXT,
cat_picture BYTE,
cat_advert VARCHAR(255, 65),
PRIMARY KEY(catalog_num),
FOREIGN KEY(stock_num, manu_code)
REFERENCES stock (stock_num, manu_code)
)
IF load_data THEN
LOAD FROM "unl/stores_demo/catalog.unl" INSERT INTO catalog
END IF
-- Tables, views and synonyms for Data Manipulation Statements testing
CREATE TABLE Table_for_select
(
col_integer INTEGER,
col_decimal DECIMAL(10,2),
col_char CHAR(20),
col_date DATE
)
IF load_data THEN
LOAD FROM "unl/stores_demo/table_for_select.unl" INSERT INTO table_for_select
END IF
CREATE VIEW view_for_select AS SELECT * FROM table_for_select
IF db_type <> "PGS" THEN
CREATE SYNONYM syn_for_select FOR table_for_select
END IF
CREATE TABLE table_for_subquery
(
col_integer INTEGER,
col_decimal DECIMAL(10,2),
col_char CHAR(20),
col_date DATE
)
IF load_data THEN
LOAD FROM "unl/stores_demo/table_for_subquery.unl" INSERT INTO table_for_subquery
END IF
CREATE TABLE table_for_insert
(
col_integer INTEGER,
col_decimal DECIMAL(10,2),
col_char CHAR(20),
col_date DATE
)
CREATE VIEW view_for_insert AS SELECT * FROM table_for_insert
IF db_type <> "PGS" THEN
CREATE SYNONYM syn_for_insert FOR table_for_insert
END IF
CREATE TABLE table_with_serial
(
col_serial SERIAL,
col_char CHAR(20)
)
CREATE VIEW view_with_serial AS SELECT * FROM table_with_serial
IF db_type <> "PGS" THEN
CREATE SYNONYM syn_with_serial FOR table_with_serial
END IF
CREATE TABLE table_for_update
(
id INTEGER,
col_integer INTEGER,
col_decimal DECIMAL(10,2),
col_char CHAR(20),
col_date DATE
)
CREATE VIEW view_for_update AS SELECT * FROM table_for_update
IF db_type <> "PGS" THEN
CREATE SYNONYM syn_for_update FOR table_for_update
END IF
CREATE TABLE table_for_delete
(
id INTEGER NOT NULL,
col_integer INTEGER,
col_decimal DECIMAL(10,2),
col_char CHAR(20),
col_date DATE,
PRIMARY KEY (id) CONSTRAINT p_table_for_delete
)
CREATE VIEW view_for_delete AS SELECT * FROM table_for_delete
IF db_type <> "PGS" THEN
CREATE SYNONYM syn_for_delete FOR table_for_delete
END IF
CREATE TABLE table_referenced
(
id INTEGER,
col_char CHAR(20),
FOREIGN KEY (id) REFERENCES table_for_delete
ON DELETE CASCADE CONSTRAINT f_table_referenced
)
CREATE TABLE group_report
(
id_group INTEGER NOT NULL,
name_group CHAR(80),
PRIMARY KEY (id_group) CONSTRAINT p_group_report
)
IF load_data THEN
LOAD FROM "unl/stores_demo/group_report.unl" INSERT INTO group_report
END IF
CREATE TABLE detail_report
(
id_group INTEGER,
name_int_group CHAR(80),
col_integer INTEGER,
col_decimal DECIMAL(10,2),
col_char CHAR(256),
col_date DATE,
FOREIGN KEY (id_group) REFERENCES group_report
)
IF load_data THEN
LOAD FROM "unl/stores_demo/detail_report.unl" INSERT INTO detail_report
END IF
CALL like_upscol()
DISPLAY "All tables recreated and populated"
END MAIN
FUNCTION like_upscol()
WHENEVER ERROR CONTINUE
DROP TABLE syscolval
WHENEVER ERROR STOP
CREATE TABLE syscolval
(
tabname nchar(18), -- contains the name of a table for the column of which values are specified
colname nchar(18), -- contains the name of a column of which values are specified
attrname nchar(10), -- contains the type of the value (either INCLUDE - for the acceptable value, or DEFAULT - for the default value)
attrval nchar(64) -- contains the value to be assigned
)
CREATE index sysvcomp on syscolval (tabname,colname)
CREATE index sysvtabname on syscolval (tabname)
INSERT INTO syscolval VALUES("table_for_insert","col_integer","INCLUDE","1")
INSERT INTO syscolval VALUES("table_for_insert","col_integer","INCLUDE","2")
INSERT INTO syscolval VALUES("table_for_insert","col_integer","INCLUDE","3")
END FUNCTION