Return to regressdatabase.sql CVS log

Up to [pgFoundry] / autodoc / autodoc

Annotation of autodoc/autodoc/regressdatabase.sql, Revision HEAD

1.1       rbt         1: --
1.2       rbt         2: -- $Id: regressdatabase.sql,v 1.1.1.1 2004/05/12 16:00:37 rbt Exp $
1.1       rbt         3: --
                      4:
                      5: BEGIN;
                      6: --
                      7: -- Foreign key'd structure, check constraints, primary keys
                      8: -- and duplicate table names in different schemas
                      9: --
                     10: CREATE SCHEMA product
                     11:  CREATE TABLE product
                     12:  ( product_id SERIAL PRIMARY KEY
                     13:  , product_code text NOT NULL UNIQUE
                     14:                      CHECK(product_code = upper(product_code))
                     15:  , product_description text
                     16:  );
                     17:
                     18: CREATE SCHEMA store
                     19:  CREATE TABLE store
                     20:  ( store_id SERIAL PRIMARY KEY
                     21:  , store_code text NOT NULL UNIQUE
                     22:                    CHECK(store_code = upper(store_code))
                     23:  , store_description text
                     24:  )
                     25:
                     26:  CREATE TABLE inventory
                     27:  ( store_id integer REFERENCES store
                     28:                        ON UPDATE CASCADE ON DELETE RESTRICT
                     29:  , product_id integer REFERENCES product.product
                     30:                         ON UPDATE CASCADE ON DELETE RESTRICT
                     31:  , PRIMARY KEY(store_id, product_id)
                     32:  , quantity integer NOT NULL CHECK(quantity > 0)
                     33:  );
                     34:
                     35: --
                     36: -- Another schema with
                     37: --
                     38: CREATE SCHEMA warehouse
                     39:  CREATE TABLE warehouse
                     40:  ( warehouse_id SERIAL PRIMARY KEY
                     41:  , warehouse_code text NOT NULL UNIQUE
                     42:                        CHECK(warehouse_code = upper(warehouse_code))
                     43:  , warehouse_manager text NOT NULL
                     44:  , warehouse_supervisor text UNIQUE
                     45:  , warehouse_description text
                     46:  , CHECK (upper(warehouse_manager) != upper(warehouse_supervisor))
                     47:  )
                     48:  CREATE TABLE inventory
                     49:  ( warehouse_id integer REFERENCES warehouse
                     50:                           ON UPDATE CASCADE
                     51:                           ON DELETE RESTRICT
                     52:  , product_id integer REFERENCES product.product
                     53:                         ON UPDATE CASCADE
                     54:                         ON DELETE RESTRICT
                     55:  , PRIMARY KEY(warehouse_id, product_id)
                     56:  , quantity integer NOT NULL
                     57:                     CHECK(quantity > 0)
                     58:  )
                     59:  CREATE VIEW products AS
                     60:    SELECT DISTINCT product.*
                     61:      FROM inventory
                     62:      JOIN product.product USING (product_id);
                     63:
                     64: -- Sample index
                     65: CREATE INDEX quantity_index ON warehouse.inventory (quantity);
                     66:
                     67: --
                     68: -- Simple text comments
                     69: --
                     70: --COMMENT ON DATABASE IS
                     71: --'This database has been created for the purpose of simple
                     72: -- tests on PostgreSQL Autodoc.';
                     73:
                     74: COMMENT ON SCHEMA product IS
                     75: 'This schema stores a list of products and information
                     76:  about the product';
                     77:
                     78: COMMENT ON SCHEMA warehouse IS
                     79: 'A list of warehouses and information on warehouses';
                     80:
                     81: COMMENT ON TABLE warehouse.inventory IS
                     82: 'Warehouse inventory';
                     83:
                     84: COMMENT ON TABLE store.inventory IS
                     85: 'Store inventory';
                     86:
                     87: COMMENT ON COLUMN warehouse.warehouse.warehouse_code IS
                     88: 'Internal code which represents warehouses for
                     89:  invoice purposes';
                     90:
                     91: COMMENT ON COLUMN warehouse.warehouse.warehouse_supervisor IS
                     92: 'Supervisors name for a warehouse when one
                     93:  has been assigned.  The same supervisor may not
                     94:  be assigned to more than one warehouse, per company
                     95:  policy XYZ.';
                     96:
                     97: COMMENT ON COLUMN warehouse.warehouse.warehouse_manager IS
                     98: 'Name of Warehouse Manager';
                     99:
                    100: --
                    101: -- A few simple functions
                    102: --
                    103: CREATE FUNCTION product.worker(integer, integer) RETURNS integer AS
                    104: 'SELECT $1 + $1;' LANGUAGE sql;
                    105:
                    106: CREATE FUNCTION warehouse.worker(integer, integer) RETURNS integer AS
                    107: 'SELECT $1 * $1;' LANGUAGE sql;
                    108:
                    109: COMMENT ON FUNCTION product.worker(integer, integer) IS
                    110: 'Worker function appropriate for products';
                    111:
                    112: COMMENT ON FUNCTION warehouse.worker(integer, integer) IS
                    113: 'Worker function appropriate for warehouses.';
                    114:
1.2       rbt       115:
                    116: --
                    117: -- Inheritance
                    118: --
                    119: CREATE SCHEMA inherit
                    120:   CREATE TABLE taba (cola integer)
                    121:   CREATE TABLE tabb (colb integer) inherits(taba)
                    122:   CREATE TABLE tab1 (col1 integer)
                    123:   CREATE TABLE tab1b (col1b integer) inherits(tab1, tabb);
                    124:
                    125: COMMIT;