Java 1.2 How-To/JavaHowTo.iso/3rdParty/jbuilder/TRIAL/JBUILDER/JREFRNCE.Z/db.sql

Text File | 1998-05-08 | 11.5 KB | 503 lines

/*******************************************************************************/
/* The script contains the DDL to create the database and the tables. */
/*******************************************************************************/

/* Insert the path to the cliffhanger.gdb file below.  By default, it will be  */
/* created in the directory that this file (db.sql) is in.                     */

CREATE DATABASE "cliffhanger.gdb"
   USER "SYSDBA"
   PASSWORD "masterkey"
   PAGE_SIZE 1024;



CREATE TABLE CUSTOMER (
   ID                INTEGER NOT NULL,
   FIRSTNAME         VARCHAR(25),
   MI                CHAR(1),
   LASTNAME          VARCHAR(40),
   PHONE             VARCHAR(15),
   FAX               VARCHAR(15),
   EMAIL             VARCHAR(128),
   ADDR1             VARCHAR(30),
   ADDR2             VARCHAR(30),
   CITY              VARCHAR(30),
   STATE             VARCHAR(15),
   POSTALCODE        VARCHAR(12),
   COUNTRY           VARCHAR(20),
   SHIPNAME          VARCHAR(60),
   SHIPADDR1         VARCHAR(30),
   SHIPADDR2         VARCHAR(30),
   SHIPCITY          VARCHAR(30),
   SHIPSTATE         VARCHAR(15),
   SHIPPOSTALCODE    VARCHAR(12),
   SHIPCOUNTRY       VARCHAR(20),

   CONSTRAINT PK_CUSTOMER PRIMARY KEY (ID)
);
COMMIT;



CREATE TABLE L_PAYMETHOD (
   PAYMETHOD   VARCHAR(20) NOT NULL,

   CONSTRAINT PK_PAYMETHOD PRIMARY KEY (PAYMETHOD)
);
COMMIT;



CREATE TABLE L_PRODUCTCATEGORY (
   CATEGORY    VARCHAR(25) NOT NULL,

   CONSTRAINT PK_CONPRODCAT PRIMARY KEY (CATEGORY)
);
COMMIT;



CREATE TABLE L_SHIPMETHOD (
   SHIPMETHOD  VARCHAR(10) NOT NULL,

   CONSTRAINT PK_SHIPMETHOD PRIMARY KEY (SHIPMETHOD)
);
COMMIT;



CREATE TABLE L_STATUS (
   STATUS   VARCHAR(15) NOT NULL,

   CONSTRAINT PK_STATUS PRIMARY KEY (STATUS)
);
COMMIT;


CREATE TABLE ORDERITEM (
   ORDERID     INTEGER NOT NULL,
   PRODUCTID   INTEGER NOT NULL,
   QTY         INTEGER,
   SALEPRICE   DOUBLE PRECISION,

   CONSTRAINT PK_ORDERITEM PRIMARY KEY (ORDERID, PRODUCTID)
);
COMMIT;



CREATE TABLE Orders (
   ID                INTEGER NOT NULL,
   CUSTOMERID        INTEGER NOT NULL,
   ORDERDATE         DATE,
   STATUS            VARCHAR(15),
   SHIPDATE          DATE,
   ORDERTRACKNUM     VARCHAR(20),
   CUSTOMERPONUM     VARCHAR(10),
   PAYMETHOD         VARCHAR(20),
   CREDITCARDNUM     VARCHAR(16),
   WIREPAYMENTINSTR  VARCHAR(255),
   SHIPMETHOD        VARCHAR(10),
   AMTPAID           DOUBLE PRECISION ,
   SHIPNAME          VARCHAR(60),
   SHIPADDR1         VARCHAR(30),
   SHIPADDR2         VARCHAR(30),
   SHIPCITY          VARCHAR(30),
   SHIPSTATE         VARCHAR(15),
   SHIPPOSTALCODE    VARCHAR(12),
   SHIPCOUNTRY       VARCHAR(20),
   BILLADDR1         VARCHAR(30),
   BILLADDR2         VARCHAR(30),
   BILLCITY          VARCHAR(30),
   BILLSTATE         VARCHAR(15),
   BILLPOSTALCODE    VARCHAR(12),
   BILLCOUNTRY       VARCHAR(20),

   CONSTRAINT PK_ORDER PRIMARY KEY (ID)
);
COMMIT;



CREATE TABLE PRODUCT (
   ID                INTEGER NOT NULL,
   ISACTIVE          SMALLINT,
   NAME              VARCHAR(255),
   CATEGORY          VARCHAR(25),
   BASEPRICE         DOUBLE PRECISION,
   DISCOUNTPCT       DOUBLE PRECISION,
   STOCKQTY          INTEGER,
   MINREORDERQTY     INTEGER,

   CONSTRAINT PK_PRODUCT PRIMARY KEY (ID)
);
COMMIT;



CREATE TABLE ACCOUNT (
   ID             INTEGER NOT NULL,
   CREDITCARDNUMBER  CHAR(16),
   STATUS         CHAR(10),
   LIMIT         DOUBLE PRECISION,
   BALANCE         DOUBLE PRECISION,
   DOB             DATE,
   IDENTIFICATION    VARCHAR(30),
   EXPIRATIONDATE    DATE,

   CONSTRAINT PK_ACCOUNT PRIMARY KEY (ID)
);
COMMIT;



CREATE TABLE ACCOUNTSTATUS (
   STATUS         CHAR(20) NOT NULL,
   DESCRIPTION         VARCHAR(60),

   CONSTRAINT PK_ACCOUNTST PRIMARY KEY (STATUS)
);
COMMIT;



CREATE TABLE CARDNUMBER (
   CARDNUMBER    INTEGER NOT NULL,

   CONSTRAINT PK_CARDNUMBER PRIMARY KEY (CARDNUMBER)
);
COMMIT;



ALTER TABLE Orders      ADD CONSTRAINT FK1_ORDER     FOREIGN  KEY (CUSTOMERID)  REFERENCES CUSTOMER(ID);
ALTER TABLE ORDERITEM   ADD CONSTRAINT FK1_ORDERITEM FOREIGN  KEY (ORDERID)     REFERENCES Orders(ID);
ALTER TABLE ORDERITEM   ADD CONSTRAINT FK2_ORDERITEM FOREIGN  KEY (PRODUCTID)   REFERENCES PRODUCT(ID);
COMMIT;



/*******************************************************************************/
/* Exceptions. */
/*******************************************************************************/
CREATE EXCEPTION EOrderWithNoCust         "An order cannot be posted without a valid customer.";
CREATE EXCEPTION EOrderItemWithNoOrder    "An order item cannot be posted without a valid order.";
CREATE EXCEPTION EOrderItemWithNoProduct  "An order item cannot be posted without a valid product.";
CREATE EXCEPTION EDelCustWithOrders       "This customer has existing orders and can not be deleted.";
CREATE EXCEPTION EDelOrderWithOrderItems  "This order has existing order items and can not be deleted.";
COMMIT;




/*******************************************************************************/
/* Stored Procedures. Since these stored procs may be called by triggers, they
   must be defined before the trigger. */
/*******************************************************************************/
SET TERM !!;
CREATE PROCEDURE spCheckOrderItemOrderID
   (
   nNewOrderID integer
   )
   AS
   DECLARE VARIABLE nOrderCount integer;
   BEGIN

   /* An order item must have a valid order associated with it. */

   /* Note that this business rule is also implemented simply by the 'not null' in the
      OrderItem.OrderID filed definition in conjunction with the foreign key
      constraint already existing. However, by checking it in the trigger, we
      can deliver a more meaningful error message back to the user. */

   IF ( :nNewOrderID IS NULL ) THEN
      BEGIN
      EXCEPTION EOrderItemWithNoOrder;
      END
   ELSE
      BEGIN
      SELECT count(*) FROM Orders o
         WHERE o.ID = :nNewOrderID
         INTO :nOrderCount;

      IF ( :nOrderCount = 0 ) THEN
         BEGIN
         EXCEPTION EOrderItemWithNoOrder;
         END
      END

END !!
SET TERM ; !!
COMMIT;



SET TERM !!;
CREATE PROCEDURE spCheckOrderItemProductID
   (
   nNewProductID integer
   )
   AS
   DECLARE VARIABLE nProductCount integer;
   BEGIN

   /* An order item must have a valid product associated with it. */

   /* Note that this business rule is also implemented simply by the 'not null' in the
      OrderItem.ProductID filed definition in conjunction with the foreign key
      constraint already existing. However, by checking it in the trigger, we
      can deliver a more meaningful error message back to the user. */

   IF ( :nNewProductID IS NULL ) THEN
      BEGIN
      EXCEPTION EOrderItemWithNoProduct;
      END
   ELSE
      BEGIN
      SELECT count(*) FROM Product p
         WHERE p.ID = :nNewProductID
         INTO :nProductCount;

      IF ( :nProductCount = 0 ) THEN
         BEGIN
         EXCEPTION EOrderItemWithNoProduct;
         END
      END

END !!
SET TERM ; !!
COMMIT;




SET TERM !!;
CREATE PROCEDURE spCheckOrderCustID
   (
   nNewCustID integer
   )
   AS
   DECLARE VARIABLE nCustmrCount integer;
   BEGIN

   /* An order must have a valid customer associated with it. */

   /* Note that this business rule is also implemented simply by the 'not null' in the
      order.CustomerID filed definition in conjunction with the foreign key
      constraint already existing. However, by checking it in the trigger, we
      can deliver a more meaningful error message back to the user. */

   IF ( :nNewCustID IS NULL ) THEN
      BEGIN
      EXCEPTION EOrderWithNoCust;
      END
   ELSE
      BEGIN
      SELECT count(*) FROM Customer c
         WHERE c.ID = :nNewCustID
         INTO :nCustmrCount;

      IF ( :nCustmrCount = 0 ) THEN
         BEGIN
         EXCEPTION EOrderWithNoCust;
         END
      END

END !!
SET TERM ; !!
COMMIT;



/*******************************************************************************/
/* Triggers. */
/*******************************************************************************/

/* PRODUCT Table */

CREATE GENERATOR genProductID;
SET GENERATOR genProductID TO 0;

SET TERM !!;
CREATE TRIGGER trgProductBeforeInsert
   FOR Product
   BEFORE INSERT
   POSITION 5
   AS
   BEGIN

   IF ( New.ID is null ) THEN
      BEGIN
      New.ID = Gen_ID(genProductID , 1);
      END

END !!
SET TERM ; !!
COMMIT;



/* ORDERS Table */

CREATE GENERATOR genOrderID;
SET GENERATOR genOrderID TO 0;

SET TERM !!;
CREATE TRIGGER trgOrderBeforeInsert
   FOR Orders
   BEFORE INSERT
   POSITION 5
   AS
   BEGIN

   IF ( New.ID is null ) THEN
      BEGIN
      New.ID = Gen_ID(genOrderID , 1);
      END

   /* An order item must have a valid customer associated with it. */
   EXECUTE PROCEDURE spCheckOrderCustID(New.CustomerID);

END !!
SET TERM ; !!
COMMIT;

/* Stored Procedure to generate and return a new Order ID
   This is used by the client to get the Order ID when inserting a
   new order, so that the client can populate the OrderID field
   of OrderItem records for the new order.
*/
SET TERM !!;
CREATE PROCEDURE spNextOrderID
RETURNS (NEXTID INTEGER)
   AS
   BEGIN

   NEXTID = Gen_ID(genOrderID , 1);
   SUSPEND;

END !!
SET TERM ; !!
COMMIT;



SET TERM !!;
CREATE TRIGGER trgOrderItemsBeforeInsert
   FOR OrderItem
   BEFORE INSERT
   POSITION 0
   AS
   BEGIN

   /* An order item must have a valid order associated with it. */
   EXECUTE PROCEDURE spCheckOrderItemOrderID(New.OrderID);

   /* An order item must have a valid product associated with it. */
   EXECUTE PROCEDURE spCheckOrderItemProductID(New.ProductID);

END !!
SET TERM ; !!
COMMIT;



SET TERM !!;
CREATE TRIGGER trgOrderBeforeDelete
   FOR Orders
   BEFORE DELETE
   POSITION 0
   AS
   DECLARE VARIABLE nOrderItemCount integer;
   BEGIN

   /* Orders cannot be deleted if they have order items. */
   SELECT count(*) FROM OrderItem oi
      WHERE oi.OrderID = Old.ID
      INTO :nOrderItemCount;

   IF ( :nOrderItemCount != 0 ) THEN
      BEGIN
      EXCEPTION EDelOrderWithOrderItems;
      END

END !!
SET TERM ; !!
COMMIT;



/* CUSTOMER Table */

CREATE GENERATOR genCustomerID;
SET GENERATOR genCustomerID TO 0;

SET TERM !!;
CREATE TRIGGER trgCustomerBeforeInsert
   FOR Customer
   BEFORE INSERT
   POSITION 5
   AS
   BEGIN

   IF ( New.ID is null ) THEN
      BEGIN
      New.ID = Gen_ID(genCustomerID , 1);
      END

END !!
SET TERM ; !!
COMMIT;



/* Stored Procedure to generate and return a new Customer ID
   This is used by the client to get the Customer ID when inserting a
   new customer, so that the client can populate the CustomerID field
   of Customer records for the new customer.
*/
SET TERM !!;
CREATE PROCEDURE spNextCustomerID
RETURNS (NEXTID INTEGER)
   AS
   BEGIN

   NEXTID = Gen_ID(genCustomerID , 1);
   SUSPEND;

END !!
SET TERM ; !!
COMMIT;



SET TERM !!;
CREATE TRIGGER trgCustBeforeDelete
   FOR Customer
   BEFORE DELETE
   POSITION 0
   AS
   DECLARE VARIABLE nOrderCount integer;
   BEGIN

   /* Customers cannot be deleted if they have orders. */
   SELECT count(*) FROM Orders o
      WHERE o.CustomerID = Old.ID
      INTO :nOrderCount;

   IF ( :nOrderCount != 0 ) THEN
      BEGIN
      EXCEPTION EDelCustWithOrders;
      END

END !!
SET TERM ; !!
COMMIT;


/* Grant permissions for this database */



EXIT;