/* Extract Database D:\X\Delphi Books\D5DG\Data\Sales.gdb */
CREATE DATABASE "D:\X\Delphi Books\D5DG\Data\Sales.gdb" PAGE_SIZE 1024 
;

/* Domain definitions */
CREATE DOMAIN DCUSTOMERID AS INTEGER;
CREATE DOMAIN DCREDITLINE AS SMALLINT
          default 0

         CHECK (VALUE BETWEEN 0 AND 3000);
CREATE DOMAIN DNAME AS CHAR(20);
CREATE DOMAIN DADDRESS AS VARCHAR(50);
CREATE DOMAIN DCITY AS VARCHAR(20);
CREATE DOMAIN DSTATE AS VARCHAR(20);
CREATE DOMAIN DZIP AS VARCHAR(10);
CREATE DOMAIN DPHONE AS VARCHAR(20);
CREATE DOMAIN DPRICE AS NUMERIC(15, 2)
          default 0.00
;

/* Table: CUSTOMER, Owner: SYSDBA */
CREATE TABLE CUSTOMER (CUSTOMER_ID INTEGER NOT NULL,
        FNAME DNAME NOT NULL,
        LNAME DNAME NOT NULL,
        CREDIT_LINE DCREDITLINE NOT NULL,
        WORK_ADDRESS DADDRESS,
        ALT_ADDRESS DADDRESS,
        CITY DCITY,
        STATE DSTATE,
        ZIP DZIP,
        WORK_PHONE DPHONE,
        ALT_PHONE DPHONE,
        COMMENTS BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
        COMPANY VARCHAR(40),
CONSTRAINT PCUSTOMER_ID PRIMARY KEY (CUSTOMER_ID));

/* Table: ITEMS, Owner: SYSDBA */
CREATE TABLE ITEMS (SALE_NUMBER INTEGER,
        ITEM_NO INTEGER,
        PART_NO VARCHAR(10),
        QTY SMALLINT);

/* Table: PART, Owner: SYSDBA */
CREATE TABLE PART (PART_NUMBER VARCHAR(10) NOT NULL,
        DESCRIPTION VARCHAR(18),
        QUANTITY SMALLINT NOT NULL,
        LIST_PRICE DPRICE NOT NULL,
        RETAIL_PRICE DPRICE NOT NULL,
        DEALER_PRICE DPRICE NOT NULL,
        JOBBER_PRICE DPRICE NOT NULL,
CONSTRAINT PPART_NUMBER PRIMARY KEY (PART_NUMBER));

/* Table: SALES, Owner: SYSDBA */
CREATE TABLE SALES (SALE_NUMBER INTEGER NOT NULL,
        CUSTOMER_ID INTEGER,
        SALE_DATE DATE,
        TOTAL_PRICE DOUBLE PRECISION,
CONSTRAINT PSALE_NUMBER PRIMARY KEY (SALE_NUMBER));

/*  Index definitions for all user tables */
CREATE UNIQUE INDEX IFULLNAME ON CUSTOMER(LNAME, FNAME);
ALTER TABLE ITEMS ADD FOREIGN KEY (PART_NO) REFERENCES PART(PART_NUMBER);
ALTER TABLE ITEMS ADD FOREIGN KEY (PART_NO) REFERENCES PART(PART_NUMBER);
ALTER TABLE SALES ADD FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID);
ALTER TABLE ITEMS ADD FOREIGN KEY (SALE_NUMBER) REFERENCES SALES(SALE_NUMBER);

CREATE GENERATOR GEN_CUSTID;
CREATE GENERATOR GEN_ITEMNO;
CREATE GENERATOR GEN_SALENO;


/*  Exceptions */
CREATE EXCEPTION EXP_EXCESS_ORDER "Not enough items for specified order.";
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */
CREATE PROCEDURE INSERT_SALE_ITEM AS BEGIN EXIT; END ^
CREATE PROCEDURE INSERT_SALE AS BEGIN EXIT; END ^

ALTER PROCEDURE INSERT_SALE_ITEM (ISALE_NUMBER INTEGER,
IPART_NO VARCHAR(10),
IQTY SMALLINT)
AS 

  DECLARE VARIABLE Actual_Qty VARCHAR(10);
BEGIN
/* CHECK IF iQTY ITEMS EXISTS IN THE PARTS TABLE */
  SELECT QUANTITY FROM PART
    WHERE PART_NUMBER = :iPART_NO
    INTO Actual_Qty;
  IF (Actual_Qty < iQTY) THEN
    EXCEPTION EXP_EXCESS_ORDER;
  ELSE BEGIN
    /* First remove the quantity of parts from the PART table */
    UPDATE PART
    SET QUANTITY = (:Actual_Qty - :iQty)
    WHERE PART_NUMBER = :iPART_NO;

    /* Now Insert the new order */
    INSERT INTO ITEMS(
      SALE_NUMBER,
      PART_NO,
      QTY)
    VALUES(
      :iSALE_NUMBER,
      :iPART_NO,
      :iQTY);
  END
END
 ^

ALTER PROCEDURE INSERT_SALE (ICUSTOMER_ID INTEGER,
ISALE_DATE DATE,
ITOTAL_PRICE DOUBLE PRECISION)
RETURNS (RSALE_NUMBER INTEGER)
AS 
  

BEGIN
  /* First obtain a new Sale identifier from the         */
  /* GEN_SALENO generator. This value is being stored in */
  /* the rSale parameter which is defined as a return    */
  /* value and will therefore be returned to the calling */
  /* client.                                             */

  rSALE_NUMBER = gen_id(GEN_SALENO, 1);
  /* Now insert the record into the SALES table */
  INSERT INTO SALES(
    SALE_NUMBER,
    CUSTOMER_ID,
    SALE_DATE,
    TOTAL_PRICE)
  VALUES(
    :rSALE_NUMBER,
    :iCUSTOMER_ID,
    :iSALE_DATE,
    :iTOTAL_PRICE);
END
 ^
SET TERM ; ^
COMMIT WORK ;
SET AUTODDL ON;
SET TERM ^ ;

/* Triggers only will work for SQL triggers */
CREATE TRIGGER CHECK_STATE_UPDATE FOR CUSTOMER                        
ACTIVE BEFORE UPDATE POSITION 0 
AS BEGIN
  IF (UPPER(NEW.STATE) = "WA") THEN BEGIN
    NEW.STATE = "Washington"; END
  IF (UPPER(NEW.STATE) = "OR") THEN BEGIN
    NEW.STATE = "Oregon"; END
  IF (UPPER(NEW.STATE) = "CA") THEN BEGIN
    NEW.STATE = "California"; END
  IF (UPPER(NEW.STATE) = "ID") THEN BEGIN
    NEW.STATE = "Idaho"; END
  IF (UPPER(NEW.STATE) = "NV") THEN BEGIN
    NEW.STATE = "Nevada"; END
  IF (UPPER(NEW.STATE) = "AZ") THEN BEGIN
    NEW.STATE = "Arizona"; END
  IF (UPPER(NEW.STATE) = "UT") THEN BEGIN
    NEW.STATE = "Utah"; END
  IF (UPPER(NEW.STATE) = "WY") THEN BEGIN
    NEW.STATE = "Wyoming"; END
  IF (UPPER(NEW.STATE) = "MT") THEN BEGIN
    NEW.STATE = "Montana"; END
  IF (UPPER(NEW.STATE) = "ND") THEN BEGIN
    NEW.STATE = "North Dakota"; END
  IF (UPPER(NEW.STATE) = "SD") THEN BEGIN
    NEW.STATE = "South Dakota"; END
  IF (UPPER(NEW.STATE) = "CO") THEN BEGIN
    NEW.STATE = "Colorado"; END
  IF (UPPER(NEW.STATE) = "NM") THEN BEGIN
    NEW.STATE = "New Mexico"; END
  IF (UPPER(NEW.STATE) = "NE") THEN BEGIN
    NEW.STATE = "Nebraska"; END
  IF (UPPER(NEW.STATE) = "KS") THEN BEGIN
    NEW.STATE = "Kansas"; END
  IF (UPPER(NEW.STATE) = "OK") THEN BEGIN
    NEW.STATE = "Oklahoma"; END
  IF (UPPER(NEW.STATE) = "TX") THEN BEGIN
    NEW.STATE = "Texas"; END
  IF (UPPER(NEW.STATE) = "LA") THEN BEGIN
    NEW.STATE = "Louisiana"; END
  IF (UPPER(NEW.STATE) = "AR") THEN BEGIN
    NEW.STATE = "Arkansas"; END
  IF (UPPER(NEW.STATE) = "MO") THEN BEGIN
    NEW.STATE = "Missouri"; END
  IF (UPPER(NEW.STATE) = "IO") THEN BEGIN
    NEW.STATE = "Iowa"; END
  IF (UPPER(NEW.STATE) = "MN") THEN BEGIN
    NEW.STATE = "Minnesota"; END
  IF (UPPER(NEW.STATE) = "IL") THEN BEGIN
    NEW.STATE = "Illinois"; END
  IF (UPPER(NEW.STATE) = "WI") THEN BEGIN
    NEW.STATE = "Wisconsin"; END
  IF (UPPER(NEW.STATE) = "MI") THEN BEGIN
    NEW.STATE = "Michigan"; END
  IF (UPPER(NEW.STATE) = "IN") THEN BEGIN
    NEW.STATE = "Indiana"; END
  IF (UPPER(NEW.STATE) = "KY") THEN BEGIN
    NEW.STATE = "Kentucky"; END
  IF (UPPER(NEW.STATE) = "TN") THEN BEGIN
    NEW.STATE = "Tennessee"; END
  IF (UPPER(NEW.STATE) = "MS") THEN BEGIN
    NEW.STATE = "Mississippi"; END
  IF (UPPER(NEW.STATE) = "AL") THEN BEGIN
    NEW.STATE = "Alabama"; END
  IF (UPPER(NEW.STATE) = "GA") THEN BEGIN
    NEW.STATE = "Georgia"; END
  IF (UPPER(NEW.STATE) = "FL") THEN BEGIN
    NEW.STATE = "Florida"; END
  IF (UPPER(NEW.STATE) = "SC") THEN BEGIN
    NEW.STATE = "South Carolina"; END
  IF (UPPER(NEW.STATE) = "NC") THEN BEGIN
    NEW.STATE = "North Carolina"; END
  IF (UPPER(NEW.STATE) = "VA") THEN BEGIN
    NEW.STATE = "Virginia"; END
  IF (UPPER(NEW.STATE) = "WV") THEN BEGIN
    NEW.STATE = "West Virginia"; END
  IF (UPPER(NEW.STATE) = "OH") THEN BEGIN
    NEW.STATE = "Ohio"; END
  IF (UPPER(NEW.STATE) = "NY") THEN BEGIN
    NEW.STATE = "New York"; END
  IF (UPPER(NEW.STATE) = "DE") THEN BEGIN
    NEW.STATE = "Delaware"; END
  IF (UPPER(NEW.STATE) = "MD") THEN BEGIN
    NEW.STATE = "Maryland"; END
  IF (UPPER(NEW.STATE) = "PA") THEN BEGIN
    NEW.STATE = "Pennsylvania"; END
  IF (UPPER(NEW.STATE) = "NJ") THEN BEGIN
    NEW.STATE = "New Jersey"; END
  IF (UPPER(NEW.STATE) = "CT") THEN BEGIN
    NEW.STATE = "Connecticut"; END
  IF (UPPER(NEW.STATE) = "RI") THEN BEGIN
    NEW.STATE = "Rhode Island"; END
  IF (UPPER(NEW.STATE) = "MA") THEN BEGIN
    NEW.STATE = "Massachusetts"; END
  IF (UPPER(NEW.STATE) = "VT") THEN BEGIN
    NEW.STATE = "Vermont"; END
  IF (UPPER(NEW.STATE) = "NH") THEN BEGIN
    NEW.STATE = "New Hampshire"; END
  IF (UPPER(NEW.STATE) = "ME") THEN BEGIN
    NEW.STATE = "Maine"; END
  IF (UPPER(NEW.STATE) = "HI") THEN BEGIN
    NEW.STATE = "Hawaii"; END
  IF (UPPER(NEW.STATE) = "AK") THEN BEGIN
    NEW.STATE = "Alaska"; END
END
 ^
CREATE TRIGGER TCUSTOMER_ID FOR CUSTOMER                        
ACTIVE BEFORE INSERT POSITION 0 
as begin
  new.customer_id = gen_id(gen_custid, 1);
end
 ^
CREATE TRIGGER TITEM_NO FOR ITEMS                           
ACTIVE BEFORE INSERT POSITION 0 
as begin
  new.item_no = gen_id(gen_itemno, 1);
end
 ^
CREATE TRIGGER CHECK_STATE FOR CUSTOMER                        
ACTIVE BEFORE INSERT POSITION 1 
AS BEGIN
  IF (UPPER(NEW.STATE) = "WA") THEN BEGIN
    NEW.STATE = "Washington"; END
  IF (UPPER(NEW.STATE) = "OR") THEN BEGIN
    NEW.STATE = "Oregon"; END
  IF (UPPER(NEW.STATE) = "CA") THEN BEGIN
    NEW.STATE = "California"; END
  IF (UPPER(NEW.STATE) = "ID") THEN BEGIN
    NEW.STATE = "Idaho"; END
  IF (UPPER(NEW.STATE) = "NV") THEN BEGIN
    NEW.STATE = "Nevada"; END
  IF (UPPER(NEW.STATE) = "AZ") THEN BEGIN
    NEW.STATE = "Arizona"; END
  IF (UPPER(NEW.STATE) = "UT") THEN BEGIN
    NEW.STATE = "Utah"; END
  IF (UPPER(NEW.STATE) = "WY") THEN BEGIN
    NEW.STATE = "Wyoming"; END
  IF (UPPER(NEW.STATE) = "MT") THEN BEGIN
    NEW.STATE = "Montana"; END
  IF (UPPER(NEW.STATE) = "ND") THEN BEGIN
    NEW.STATE = "North Dakota"; END
  IF (UPPER(NEW.STATE) = "SD") THEN BEGIN
    NEW.STATE = "South Dakota"; END
  IF (UPPER(NEW.STATE) = "CO") THEN BEGIN
    NEW.STATE = "Colorado"; END
  IF (UPPER(NEW.STATE) = "NM") THEN BEGIN
    NEW.STATE = "New Mexico"; END
  IF (UPPER(NEW.STATE) = "NE") THEN BEGIN
    NEW.STATE = "Nebraska"; END
  IF (UPPER(NEW.STATE) = "KS") THEN BEGIN
    NEW.STATE = "Kansas"; END
  IF (UPPER(NEW.STATE) = "OK") THEN BEGIN
    NEW.STATE = "Oklahoma"; END
  IF (UPPER(NEW.STATE) = "TX") THEN BEGIN
    NEW.STATE = "Texas"; END
  IF (UPPER(NEW.STATE) = "LA") THEN BEGIN
    NEW.STATE = "Louisiana"; END
  IF (UPPER(NEW.STATE) = "AR") THEN BEGIN
    NEW.STATE = "Arkansas"; END
  IF (UPPER(NEW.STATE) = "MO") THEN BEGIN
    NEW.STATE = "Missouri"; END
  IF (UPPER(NEW.STATE) = "IO") THEN BEGIN
    NEW.STATE = "Iowa"; END
  IF (UPPER(NEW.STATE) = "MN") THEN BEGIN
    NEW.STATE = "Minnesota"; END
  IF (UPPER(NEW.STATE) = "IL") THEN BEGIN
    NEW.STATE = "Illinois"; END
  IF (UPPER(NEW.STATE) = "WI") THEN BEGIN
    NEW.STATE = "Wisconsin"; END
  IF (UPPER(NEW.STATE) = "MI") THEN BEGIN
    NEW.STATE = "Michigan"; END
  IF (UPPER(NEW.STATE) = "IN") THEN BEGIN
    NEW.STATE = "Indiana"; END
  IF (UPPER(NEW.STATE) = "KY") THEN BEGIN
    NEW.STATE = "Kentucky"; END
  IF (UPPER(NEW.STATE) = "TN") THEN BEGIN
    NEW.STATE = "Tennessee"; END
  IF (UPPER(NEW.STATE) = "MS") THEN BEGIN
    NEW.STATE = "Mississippi"; END
  IF (UPPER(NEW.STATE) = "AL") THEN BEGIN
    NEW.STATE = "Alabama"; END
  IF (UPPER(NEW.STATE) = "GA") THEN BEGIN
    NEW.STATE = "Georgia"; END
  IF (UPPER(NEW.STATE) = "FL") THEN BEGIN
    NEW.STATE = "Florida"; END
  IF (UPPER(NEW.STATE) = "SC") THEN BEGIN
    NEW.STATE = "South Carolina"; END
  IF (UPPER(NEW.STATE) = "NC") THEN BEGIN
    NEW.STATE = "North Carolina"; END
  IF (UPPER(NEW.STATE) = "VA") THEN BEGIN
    NEW.STATE = "Virginia"; END
  IF (UPPER(NEW.STATE) = "WV") THEN BEGIN
    NEW.STATE = "West Virginia"; END
  IF (UPPER(NEW.STATE) = "OH") THEN BEGIN
    NEW.STATE = "Ohio"; END
  IF (UPPER(NEW.STATE) = "NY") THEN BEGIN
    NEW.STATE = "New York"; END
  IF (UPPER(NEW.STATE) = "DE") THEN BEGIN
    NEW.STATE = "Delaware"; END
  IF (UPPER(NEW.STATE) = "MD") THEN BEGIN
    NEW.STATE = "Maryland"; END
  IF (UPPER(NEW.STATE) = "PA") THEN BEGIN
    NEW.STATE = "Pennsylvania"; END
  IF (UPPER(NEW.STATE) = "NJ") THEN BEGIN
    NEW.STATE = "New Jersey"; END
  IF (UPPER(NEW.STATE) = "CT") THEN BEGIN
    NEW.STATE = "Connecticut"; END
  IF (UPPER(NEW.STATE) = "RI") THEN BEGIN
    NEW.STATE = "Rhode Island"; END
  IF (UPPER(NEW.STATE) = "MA") THEN BEGIN
    NEW.STATE = "Massachusetts"; END
  IF (UPPER(NEW.STATE) = "VT") THEN BEGIN
    NEW.STATE = "Vermont"; END
  IF (UPPER(NEW.STATE) = "NH") THEN BEGIN
    NEW.STATE = "New Hampshire"; END
  IF (UPPER(NEW.STATE) = "ME") THEN BEGIN
    NEW.STATE = "Maine"; END
  IF (UPPER(NEW.STATE) = "HI") THEN BEGIN
    NEW.STATE = "Hawaii"; END
  IF (UPPER(NEW.STATE) = "AK") THEN BEGIN
    NEW.STATE = "Alaska"; END
END
 ^
COMMIT WORK ^
SET TERM ; ^

/* Grant permissions for this database */
GRANT SELECT, UPDATE ON CUSTOMER TO PUBLIC WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON ITEMS TO PUBLIC WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON PART TO PUBLIC WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON SALES TO PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON PROCEDURE INSERT_SALE TO PUBLIC;
GRANT EXECUTE ON PROCEDURE INSERT_SALE_ITEM TO PUBLIC;

