tracktimedb.sql

/********************* ROLES **********************/

/********************* UDFS ***********************/

/********************* FUNCTIONS ***********************/

/****************** SEQUENCES ********************/

/******************** DOMAINS *********************/

/******************* PROCEDURES ******************/

/******************* PACKAGES ******************/

/******************** TABLES **********************/

CREATE TABLE CUSTOMER
(
  CUSTOMER_ID bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  CUSTOMER_NAME varchar(50) NOT NULL,
  PHONE varchar(20),
  EMAIL varchar(100),
  CUSTOMER_STATE integer NOT NULL,
  NOTES blob sub_type 1,
  CONSTRAINT PK_CUSTOMER_ID PRIMARY KEY (CUSTOMER_ID)
);
CREATE TABLE TIME_ENTRY
(
  TIME_ENTRY_ID bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  DESCRIPTION varchar(100),
  TIME_START timestamp NOT NULL,
  TIME_END timestamp,
  BEEN_BILLED boolean NOT NULL,
  NOTES blob sub_type 1,
  WORK_ITEM_ID bigint,
  CONSTRAINT PK_TIME_ENTRY_ID PRIMARY KEY (TIME_ENTRY_ID)
);
CREATE TABLE WORK_ITEM
(
  WORK_ITEM_ID bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  TITLE varchar(50) NOT NULL,
  DESCRIPTION varchar(100),
  IS_BILLABLE boolean NOT NULL,
  IS_COMPLETED boolean NOT NULL,
  IS_FIXED_PRICE boolean NOT NULL,
  DATE_CREATED date NOT NULL,
  DUE_DATE date,
  BEEN_BILLED boolean NOT NULL,
  NOTES blob sub_type 1,
  CUSTOMER_ID bigint NOT NULL,
  CONSTRAINT PK_WORK_ITEM_ID PRIMARY KEY (WORK_ITEM_ID)
);
/********************* VIEWS **********************/

/******************* EXCEPTIONS *******************/

/******************** TRIGGERS ********************/

/******************** DB TRIGGERS ********************/

/******************** DDL TRIGGERS ********************/


ALTER TABLE TIME_ENTRY ADD CONSTRAINT FK_TIME_ENTRY_WORK_ITEM_ID
  FOREIGN KEY (WORK_ITEM_ID) REFERENCES WORK_ITEM (WORK_ITEM_ID) ON DELETE CASCADE;
ALTER TABLE WORK_ITEM ADD CONSTRAINT FK_WORK_ITEM_CUSTOMER_ID
  FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON CUSTOMER TO  "PUBLIC";

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON CUSTOMER TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TIME_ENTRY TO  "PUBLIC";

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TIME_ENTRY TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON WORK_ITEM TO  "PUBLIC";

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON WORK_ITEM TO  SYSDBA WITH GRANT OPTION;