| DATABASE stores_demo |
| |
| MAIN |
| DEFINE |
| load_data SMALLINT, |
| db_type CHAR(80) |
| |
| |
| 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 |
| |
| |
| 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 |
| |
| |
| |
| 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), |
| colname nchar(18), |
| attrname nchar(10), |
| attrval nchar(64) |
| ) |
| 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 |
| |