CREATE DATABASE 'contact.gdb'
  USER 'SYSDBA'
  PASSWORD 'masterkey';

//------------------------------------------------------------------------------
CREATE DOMAIN NAME_DM AS VARCHAR(35);
CREATE DOMAIN ADDR_DM AS VARCHAR(60);
CREATE DOMAIN GENDER_DM AS CHAR(1)
       CHECK ( VALUE IN ('M','F') OR
               VALUE IS NULL );
CREATE DOMAIN USASTATE_DM AS VARCHAR(2)
       CHECK ( VALUE LIKE ('__') OR
               VALUE IS NULL );
CREATE DOMAIN USAZIP_DM AS VARCHAR(10)
       CHECK ( VALUE LIKE ('_____-____') OR
               VALUE LIKE ('_____') OR
               VALUE IS NULL );
CREATE DOMAIN USAPHONE_DM AS VARCHAR(13)
       CHECK ( VALUE LIKE ('(___)___-____') OR
               VALUE LIKE ('___-___-____') OR
               VALUE LIKE ('___-____') OR
               VALUE IS NULL );
CREATE DOMAIN BOOLEAN_DM AS CHARACTER(1)
       CHECK ( VALUE IN (' ', 'Y', 'N') OR
               VALUE IS NULL );
CREATE DOMAIN CNTCT_TYPE_DM AS VARCHAR(10);
COMMIT;
//------------------------------------------------------------------------------
CREATE TABLE CONTACT_TYPE (
       CON_TYP_NAME CNTCT_TYPE_DM  NOT NULL,
       CONSTRAINT CONTACT_TYPE_PK PRIMARY KEY ( CON_TYP_NAME )
);
COMMIT;
INSERT INTO CONTACT_TYPE ( CON_TYP_NAME ) VALUES ( 'BUSINESS' );
INSERT INTO CONTACT_TYPE ( CON_TYP_NAME ) VALUES ( 'CHURCH'   );
INSERT INTO CONTACT_TYPE ( CON_TYP_NAME ) VALUES ( 'DOCTOR'   );
INSERT INTO CONTACT_TYPE ( CON_TYP_NAME ) VALUES ( 'FAMILY'   );
INSERT INTO CONTACT_TYPE ( CON_TYP_NAME ) VALUES ( 'FRIEND'   );
COMMIT;
//------------------------------------------------------------------------------
CREATE GENERATOR CONTACT_ID;
SET GENERATOR CONTACT_ID TO 999;
CREATE TABLE CONTACT (
     ID            INTEGER              NOT NULL,
     LASTNAME      NAME_DM                 NOT NULL,
     FIRSTNAME     NAME_DM                 NOT NULL,
     CON_TYPE      CNTCT_TYPE_DM           NOT NULL,
     ADDR1         ADDR_DM,
     CITY          NAME_DM,
     USASTATE      USASTATE_DM,
     USAZIP        USAZIP_DM,
     USAPHONE      USAPHONE_DM,
     EMAIL         ADDR_DM,
     GENDER        GENDER_DM,
     BDAY          DATE,
     BUSNAME       NAME_DM,
     BUSADDR1      ADDR_DM,
     BUSCITY       NAME_DM,
     BUSUSASTATE   USASTATE_DM,
     BUSUSAZIP     USAZIP_DM,
     BUSUSAPHONE   USAPHONE_DM,
     SURVEY_REPLY  decimal( 9, 0 ),
     PICTURE       BLOB( 4096, 0 ),
     COMMENT       BLOB( 80, 1 ),
     ADDDATE       DATE DEFAULT 'TODAY' NOT NULL,
     CHGDATE       DATE DEFAULT 'NOW'   NOT NULL
);
COMMIT;
ALTER TABLE CONTACT
     ADD CONSTRAINT CONTACT_PK PRIMARY KEY ( ID );
COMMIT;
CREATE INDEX FIRSTNAME_IDX ON CONTACT( FIRSTNAME );
CREATE INDEX NAME_IDX      ON CONTACT( LASTNAME, FIRSTNAME );
CREATE INDEX BUSNAME_IDX   ON CONTACT( BUSNAME, LASTNAME, FIRSTNAME );
CREATE INDEX CITY_IDX      ON CONTACT( CITY );
CREATE INDEX USAPHONE_IDX  ON CONTACT( USAPHONE );
CREATE INDEX ADDDATE_IDX   ON CONTACT( ADDDATE );
CREATE INDEX CHGDATE_IDX   ON CONTACT( CHGDATE );
CREATE DESCENDING INDEX FIRSTNAME_DIDX ON CONTACT( FIRSTNAME );
CREATE DESCENDING INDEX NAME_DIDX      ON CONTACT( LASTNAME, FIRSTNAME );
CREATE DESCENDING INDEX BUSNAME_DIDX   ON CONTACT( BUSNAME, LASTNAME, FIRSTNAME );
CREATE DESCENDING INDEX CITY_DIDX      ON CONTACT( CITY );
CREATE DESCENDING INDEX USAPHONE_DIDX  ON CONTACT( USAPHONE );
CREATE DESCENDING INDEX ADDDATE_DIDX   ON CONTACT( ADDDATE );
CREATE DESCENDING INDEX CHGDATE_DIDX   ON CONTACT( CHGDATE );
COMMIT;
//------------------------------------------------------------------------------
CREATE TABLE HISTORY (
     ID        INTEGER      NOT NULL,
     LASTNAME  NAME_DM,
     FIRSTNAME NAME_DM,
     USAPHONE  USAPHONE_DM,
     CHGDATE   DATE         NOT NULL
);
COMMIT;
CREATE            INDEX HISID_IDX          ON HISTORY( ID       );
CREATE DESCENDING INDEX HISCHGDATE_DESCIDX ON HISTORY( CHGDATE  );
COMMIT;
ALTER TABLE HISTORY
     ADD CONSTRAINT HISTORY_PK PRIMARY KEY ( ID, CHGDATE );
ALTER TABLE HISTORY
     ADD CONSTRAINT CONTACT_FK FOREIGN KEY ( ID ) REFERENCES CONTACT;
COMMIT;
//------------------------------------------------------------------------------
SET AUTODDL ON;

CREATE TRIGGER CASCADE_TYPE_UPDATE FOR CONTACT_TYPE
AFTER UPDATE AS
BEGIN
  IF (OLD.CON_TYP_NAME <> NEW.CON_TYP_NAME) THEN BEGIN
    UPDATE CONTACT
      SET CON_TYPE = NEW.CON_TYP_NAME
      WHERE CON_TYPE = OLD.CON_TYP_NAME;
  END
END;
//------------------------------------------------------------------------------
CREATE TRIGGER INSERT_HISADDDATE FOR HISTORY
BEFORE INSERT AS
BEGIN
  IF ( NEW.CHGDATE IS NULL ) THEN NEW.CHGDATE = 'NOW';
END;
CREATE TRIGGER INSERT_ADDDATE FOR CONTACT
BEFORE INSERT AS
BEGIN
  NEW.ADDDATE = 'NOW';
  NEW.CHGDATE = NEW.ADDDATE;
END;
CREATE TRIGGER UPDATE_CHDDATE FOR CONTACT
BEFORE UPDATE AS
DECLARE VARIABLE NEED_LOG SMALLINT;
BEGIN
  NEED_LOG = 0;
  IF ( OLD.LASTNAME  <> NEW.LASTNAME  ) THEN NEED_LOG = 1;
  IF ( OLD.FIRSTNAME <> NEW.FIRSTNAME ) THEN NEED_LOG = 1;
  IF ( OLD.USAPHONE  <> NEW.USAPHONE  ) THEN NEED_LOG = 1;
  IF ( OLD.USAPHONE IS     NULL ) THEN
  IF ( NEW.USAPHONE IS NOT NULL ) THEN NEED_LOG = 1;
  IF ( NEED_LOG > 0 ) THEN NEW.CHGDATE = 'NOW';
END;
CREATE TRIGGER UPDATE_HISTORY FOR CONTACT
AFTER UPDATE AS
BEGIN
  IF ( OLD.CHGDATE <> NEW.CHGDATE ) THEN
    INSERT INTO HISTORY
    ( ID,
      LASTNAME,
      FIRSTNAME,
      USAPHONE,
      CHGDATE )
    VALUES
    ( OLD.ID,
      OLD.LASTNAME,
      OLD.FIRSTNAME,
      OLD.USAPHONE,
      OLD.CHGDATE );
END;

COMMIT;

INSERT INTO CONTACT 
( ID, LASTNAME, FIRSTNAME, CON_TYPE, ADDR1, CITY, USASTATE,  USAZIP,  USAPHONE,  GENDER )
VALUES 
( GEN_ID( CONTACT_ID, 1 ), 'Wharton', 'Jason', 'FAMILY', '619 N. Macdonald St.', 'Mesa', 'AZ', '85201',  '(602)834-7454', 'M' );
INSERT INTO CONTACT 
( ID, LASTNAME, FIRSTNAME, CON_TYPE, ADDR1, CITY, USASTATE,  USAZIP,  USAPHONE,  GENDER ) 
VALUES 
( GEN_ID( CONTACT_ID, 1 ), 'Wharton', 'Sara', 'FAMILY', '619 N. Macdonald St.', 'Mesa', 'AZ', '85201',  '(602)834-7454', 'F' ); 
INSERT INTO CONTACT 
( ID, LASTNAME, FIRSTNAME, CON_TYPE, ADDR1, CITY, USASTATE,  USAZIP,  USAPHONE,  GENDER ) 
VALUES 
( GEN_ID( CONTACT_ID, 1 ), 'Wharton', 'Jason Allen', 'FAMILY', '619 N. Macdonald St.', 'Mesa', 'AZ', '85201',  '(602)834-7454', 'M' ); 
INSERT INTO CONTACT 
( ID, LASTNAME, FIRSTNAME, CON_TYPE, ADDR1, CITY, USASTATE,  USAZIP,  USAPHONE,  GENDER ) 
VALUES 
( GEN_ID( CONTACT_ID, 1 ), 'Wharton', 'Rachel Marie', 'FAMILY', '619 N. Macdonald St.', 'Mesa', 'AZ', '85201',  '(602)834-7454', 'F' ); 
INSERT INTO CONTACT 
( ID, LASTNAME, FIRSTNAME, CON_TYPE, ADDR1, CITY, USASTATE,  USAZIP,  USAPHONE,  GENDER ) 
VALUES 
( GEN_ID( CONTACT_ID, 1 ), 'Wharton', 'Benjamin LeRoy', 'FAMILY', '619 N. Macdonald St.', 'Mesa', 'AZ', '85201',  '(602)834-7454', 'M' );
INSERT INTO CONTACT 
( ID, LASTNAME, FIRSTNAME, CON_TYPE, ADDR1, CITY, USASTATE,  USAZIP,  USAPHONE,  GENDER ) 
VALUES 
( GEN_ID( CONTACT_ID, 1 ), 'Wagenman', 'Ben', 'FRIEND', 'Somewhere', 'Mesa', 'AZ', '85201',  '(602)000-0000', 'M' );
INSERT INTO CONTACT 
( ID, LASTNAME, FIRSTNAME, CON_TYPE, ADDR1, CITY, USASTATE,  USAZIP,  USAPHONE,  GENDER ) 
VALUES 
( GEN_ID( CONTACT_ID, 1 ), 'Scott', 'James', 'DOCTOR', 'Somewhere', 'Scottsdale', 'AZ', '85200',  '(602)000-0000', 'M' ); 
 
COMMIT;
