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

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

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


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

/* 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
) ENGINE=INNODB;

/* 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,
    UNIQUE (PART_NUMBER, PART_REVISION)
) ENGINE=INNODB;

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

/* 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,
    PRIMARY KEY (ORDER_ID, ITEM_ID)
) ENGINE=INNODB;

quit;