appserver/tests/v2-tests/appserv-tests/devtests/cmp/orderapp/sql/orderapp_psql.sql

DROP TABLE LINEITEM cascade;
DROP TABLE ORDERS cascade;
DROP TABLE VENDOR_PART cascade;
DROP TABLE VENDOR cascade;
DROP TABLE PART_DETAIL cascade;
DROP TABLE PART cascade;

/* Self-ref to identify Bill of Material (BOM)
 * Compound PK
 */
CREATE TABLE PART (
    PART_NUMBER        VARCHAR(15)    NOT NULL,
    DESCRIPTION        VARCHAR(255)    NULL,
    REVISION        NUMERIC(2)    NOT NULL,
    REVISION_DATE        TIMESTAMP    NOT NULL,
    BOM_PART_NUMBER        VARCHAR(15)    NULL,
    BOM_REVISION        NUMERIC(2)    NULL,
    PRIMARY KEY (PART_NUMBER, REVISION),
    FOREIGN KEY (BOM_PART_NUMBER, BOM_REVISION) REFERENCES PART (PART_NUMBER, REVISION)
);


/* Bean will be mapped to 2 tables (PART and PART_DETAIL)
 * BYTEA column type
 * CLOB column type */
CREATE TABLE PART_DETAIL (
    PART_NUMBER    VARCHAR(15)    NOT NULL,
    REVISION    NUMERIC(2)    NOT NULL,
    SPECIFICATION    TEXT        NULL,
    DRAWING        BYTEA        NULL,
    PRIMARY KEY (PART_NUMBER, REVISION)
);

/* PK can be mapped to a primitive PK field type */
CREATE TABLE VENDOR (
    VENDOR_ID    INTEGER        PRIMARY KEY,
    NAME        VARCHAR(30)    NOT NULL,
    ADDRESS        VARCHAR(255)    NOT NULL,
    CONTACT        VARCHAR(255)    NOT NULL,
    PHONE        VARCHAR(30)    NOT NULL
);

/* Can be used for unknown PK
 * 1-1 to PART
 * Compound FK
 */
CREATE TABLE VENDOR_PART (
    VENDOR_PART_NUMBER    NUMERIC(19)        PRIMARY KEY,
    DESCRIPTION        VARCHAR(255)        NULL,
    PRICE            DOUBLE PRECISION    NOT NULL,
    VENDOR_ID        INTEGER            NOT NULL,
    PART_NUMBER        VARCHAR(15)        NOT NULL,
    PART_REVISION        NUMERIC(2)        NOT NULL,
    FOREIGN KEY (VENDOR_ID) REFERENCES VENDOR (VENDOR_ID),
    FOREIGN KEY (PART_NUMBER, PART_REVISION) REFERENCES PART (PART_NUMBER, REVISION),
    UNIQUE (PART_NUMBER, PART_REVISION)
);

CREATE TABLE ORDERS (
    ORDER_ID    INTEGER        PRIMARY KEY,
    STATUS        CHAR(1)        NOT NULL,
    LAST_UPDATE    TIMESTAMP    NOT NULL,
    DISCOUNT    NUMERIC(2)    NOT NULL,
    SHIPMENT_INFO    VARCHAR(255)    NULL
);

/* Overlapping PK-FK
 * Uni-directional to VENDOR_PART */
CREATE TABLE LINEITEM (
    ORDER_ID        INTEGER        NOT NULL,
    ITEM_ID            NUMERIC(3)    NOT NULL,
    QUANTITY        NUMERIC(3)    NOT NULL,
    VENDOR_PART_NUMBER    NUMERIC(19)    NOT NULL,
    FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID),
    FOREIGN KEY (VENDOR_PART_NUMBER) REFERENCES VENDOR_PART (VENDOR_PART_NUMBER),
    PRIMARY KEY (ORDER_ID, ITEM_ID)
);

quit;