-- hdb_table_def.sql
--
-- HDB v4.00 Jeremy S. and Stan K.
-- 
-- Modified:
--          May 26, 1999 Jeremy S.  SINGLE TABLE FORMAT
--          May 27, 1999 Stan K.    Restored FIELD_TYPE column in EPICS_DEFINITIONS table
--          Jun  7, 1999 Stan K.    Added DFLT_ORDER column to EPICS_DFLTS table
--          Jun 30, 1999 Stan K.    Added FIELD_SUMMARY column to EPICS_DEFINITIONS table
--          Jul 26, 1999 Stan K.    v4.00 :
--                                  Split EPICS_RECORDS into RECORDS and FIELDS
--                                  Split EPICS_DFLTS into TEMPLATES and DEFAULTS
--          Aug  3, 1999 Stan K.    FIELD_VALUE VARCHAR2(40) -> VARCHAR2(100) in DEFAULTS
-- 	    Dec  6, 1999 Jeremy S.  Added table USER_TEMPLATES
--	    Mar  8, 2000 Stan K.    Added TEMPL_NAME column to TEMPLATES table
--          Mar 27, 2000 Jeremy S.  Added PARAMETERS Table
--          Mar 31, 2000 Stan K.    Removed USER_TEMPLATES table
--                                  Added TEMPL_NAME, DFLT_VALUE columns into PARAMETERS
--                                  table
--          Apr  2, 2000 Stan K.    Added table TEMPLATE_IDS
--                                  In PARAMETERS table: DEV_TYPE_ID --> DEV_TYPE
--	    Apr  5, 2000 Jeremy S.  Added table MENUS
--          Jun  8, 2000 Stan K.    Added HOUSE, CRATE_NAME columns to LOCATORS table
--                                  Added NODE_TYPE, LOCATOR_ID, TERMINAL_SERVER, TERMINAL_PORT to
--                                  NODES table
--                                  Added DETECTOR_DEVICES table
--          Sep 15, 2000 Vlad S.    Added TEMPL_NAME columns to DEVICE table
--          Sep 27, 2000 Stan K.    Added GENERATORS table
--          Oct 16, 2000 Stan K.    Split TEMPLATES into TEMPL_RECORDS and TEMPL_FIELDS
--                                  Removed RECORDS and FIELDS tables
--                                  Added ALARMS table
--          Nov 22, 2000 Stan K.    Increased FIELD_VALUE and MENU_VAL from 40 to 50.
--          May 14, 2001 Stan K.    Eliminated DFLT_NAME column from DEFAULTS table.
--          Oct 16, 2001 Stan K.    Increased DFLT_NAME from 40 to 50 in
--                                  TEMPL_RECORDS and  TEMPL_FIELDS
--          Oct 29, 2001 Stan K.    Increased FIELD_VALUE from 50 to 60 in TEMPL_FIELDS.
--                                  Added COMMAND and GUIDANCE columns to DEVICES.
--          Mar  7, 2002 Stan K.    Increased DFLT_NAME from 50 to 60 in
--                                  TEMPL_RECORDS and  TEMPL_FIELDS.
--          Mar 21, 2002 Stan K.    Redesigned table MENUS.
--                                  Increased MENU from 35 to 40 in EPICS_DEFINITIONS.
--          Mat  7, 2002 Stan K.    Removed NOT NULL constraint for PARAM_VALUE of
--                                  GENERATORS table.
--          Mar 12, 2004 Stan K.    Increased DFLT_NAME from 60 to 65 in
--                                  TEMPL_RECORDS and  TEMPL_FIELDS.
--                                  Increased FIELD_VALUE from 60 to 65 in TEMPL_FIELDS.
--          Mar 12, 2004 Stan K.    Increased DFLT_NAME from 65 to 80 in
--                                  TEMPL_RECORDS and  TEMPL_FIELDS.
--                                  Increased FIELD_VALUE from 65 to 80 in TEMPL_FIELDS.
--

PROMPT Creating Table 'DEVICE_TYPES'
DROP TABLE DEVICE_TYPES
/
CREATE TABLE DEVICE_TYPES
 (ID VARCHAR2(40) NOT NULL
 ,DESCRIPTION VARCHAR2(64) NOT NULL
 )
/

PROMPT Creating Table 'LOCATORS'
DROP TABLE LOCATORS
/
CREATE TABLE LOCATORS
 (ID VARCHAR2(62) NOT NULL
 ,HOUSE VARCHAR2(20) NOT NULL 
 ,RACK_NAME VARCHAR2(20) NOT NULL
 ,CRATE_NAME VARCHAR2(20)
 )
/

PROMPT Creating Table 'NODES'
DROP TABLE NODES
/
CREATE TABLE NODES
 (ID VARCHAR2(40) NOT NULL
 ,LOCATOR_ID VARCHAR2(62) NOT NULL
 ,NODE_TYPE VARCHAR2(40)
 ,TERMINAL_SERVER VARCHAR2(40)
 ,TERMINAL_PORT VARCHAR2(10)
 ,DESCRIPTION VARCHAR2(64)
 )
/

PROMPT Creating Table 'DETECTORS'
DROP TABLE DETECTORS
/
CREATE TABLE DETECTORS
 (DESCRIPTION VARCHAR2(64)
 ,DET_TYPE VARCHAR2(40) NOT NULL
 )
/

PROMPT Creating Table 'DETECTOR_DEVICES'
DROP TABLE DETECTOR_DEVICES
/
CREATE TABLE DETECTOR_DEVICES
 (DET_TYPE VARCHAR2(40) NOT NULL
 ,DEV_TYPE_ID VARCHAR2(40) NOT NULL
 )
/

PROMPT Creating Table 'DEVICES'
DROP TABLE DEVICES
/
CREATE TABLE DEVICES
 (ID VARCHAR2(40) NOT NULL
 ,DEV_TYPE_ID VARCHAR2(40) NOT NULL
 ,DET_TYPE VARCHAR2(40) NOT NULL
 ,LOCATOR_ID VARCHAR2(62) NOT NULL
 ,NODE_ID VARCHAR(40) NOT NULL
 ,TEMPL_NAME VARCHAR2(40) NOT NULL
 ,CREATE_DATE DATE NOT NULL
 ,CREATOR VARCHAR2(40) NOT NULL
 ,MODIFY_DATE DATE NOT NULL
 ,MODIFIER VARCHAR2(40) NOT NULL
 ,DESCRIPTION VARCHAR2(64)
 ,COMMAND VARCHAR2(256)
 ,GUIDANCE VARCHAR2(4000)
 )
/

--PROMPT Creating Table 'RECORDS'
--DROP TABLE RECORDS
--/
--CREATE TABLE RECORDS
-- (DEVICE_ID VARCHAR2(40) NOT NULL
-- ,NAME VARCHAR2(40) NOT NULL
-- ,RECORD_TYPE VARCHAR2(40) NOT NULL
-- )
--/

--PROMPT Creating Table 'FIELDS'
--DROP TABLE FIELDS
--/
--CREATE TABLE FIELDS
-- (RECORD_NAME VARCHAR2(40) NOT NULL
-- ,RECORD_TYPE VARCHAR2(40) NOT NULL
-- ,FIELD_NAME VARCHAR2(40) NOT NULL
-- ,FIELD_VALUE VARCHAR2(40) NOT NULL
-- )
--/

PROMPT Creating Table 'TEMPLATE_IDS'
DROP TABLE TEMPLATE_IDS
/
CREATE TABLE TEMPLATE_IDS
 (TEMPL_NAME VARCHAR2(40) NOT NULL
 ,DEV_TYPE VARCHAR2(40) NOT NULL
 )
/

--PROMPT Creating Table 'TEMPLATES'
--DROP TABLE TEMPLATES
--/
--CREATE TABLE TEMPLATES
-- (TEMPL_NAME VARCHAR2(40) NOT NULL
-- ,DEV_TYPE VARCHAR2(40) NOT NULL
-- ,DFLT_NAME VARCHAR2(40) NOT NULL
-- ,FIELD_NAME VARCHAR2(40) NOT NULL
-- ,FIELD_VALUE VARCHAR2(40) NOT NULL
-- ,RECORD_TYPE VARCHAR2(40) NOT NULL
-- ,DFLT_ORDER VARCHAR2(3) NOT NULL
-- )
--/

PROMPT Creating Table 'TEMPL_RECORDS'
DROP TABLE TEMPL_RECORDS
/
CREATE TABLE TEMPL_RECORDS
 (TEMPL_NAME VARCHAR2(40) NOT NULL
 ,DEV_TYPE VARCHAR2(40) NOT NULL
 ,DFLT_NAME VARCHAR2(80) NOT NULL
 ,RECORD_TYPE VARCHAR2(40) NOT NULL
 ,ALARM_ID VARCHAR2(40) NOT NULL
 )
/

PROMPT Creating Table 'TEMPL_FIELDS'
DROP TABLE TEMPL_FIELDS
/
CREATE TABLE TEMPL_FIELDS
 (DFLT_NAME VARCHAR2(80) NOT NULL
 ,TEMPL_NAME VARCHAR2(40) NOT NULL
 ,FIELD_NAME VARCHAR2(40) NOT NULL
 ,FIELD_VALUE VARCHAR2(80) NOT NULL
 ,RECORD_TYPE VARCHAR2(40) NOT NULL
 ,DFLT_ORDER VARCHAR2(3) NOT NULL
 )
/

PROMPT Creating Table 'ALARMS'
DROP TABLE ALARMS
/
CREATE TABLE ALARMS
 (ID VARCHAR2(40) NOT NULL
 ,GUIDANCE VARCHAR2(40)
 ,COMMAND VARCHAR2(40)
 ,SEVERITY_CMD VARCHAR2(40)
 ,STATUS_CMD VARCHAR2(40)
 ,STATEMENT VARCHAR2(40)
)
/

PROMPT Creating Table 'PARAMETERS'
DROP TABLE PARAMETERS
/
CREATE TABLE PARAMETERS
 (TEMPL_NAME VARCHAR2(40) NOT NULL
 ,DEV_TYPE  VARCHAR2(40) NOT NULL
 ,PARAM_NAME  VARCHAR2(40) NOT NULL
 ,DFLT_VALUE VARCHAR2(40)
 )
/

PROMPT Creating Table 'MENUS'
DROP TABLE MENUS
/
--CREATE TABLE MENUS
-- (FIELD_NAME VARCHAR2(40) NOT NULL
-- ,MENU_VAL VARCHAR2(50) NOT NULL
-- )
CREATE TABLE MENUS
 (MENU VARCHAR2(40) NOT NULL
 ,CHOICE_NAME VARCHAR2(50) NOT NULL
 ,CHOICE_VALUE VARCHAR2(50)
 )
/


PROMPT Creating Table 'DEFAULTS'
DROP TABLE DEFAULTS
/
CREATE TABLE DEFAULTS
 (RECORD_TYPE VARCHAR2(40) NOT NULL
 ,FIELD_NAME VARCHAR2(40) NOT NULL
 ,FIELD_VALUE VARCHAR2(100)
 ,DFLT_ORDER VARCHAR2(3) NOT NULL
 )
/

PROMPT Creating Table 'EPICS_DEFINITIONS'
DROP TABLE EPICS_DEFINITIONS
/
CREATE TABLE EPICS_DEFINITIONS
 (RECORD_TYPE VARCHAR2(40) NOT NULL
 ,FIELD_NAME VARCHAR2(40) NOT NULL
 ,FIELD_TYPE VARCHAR2(35) NOT NULL
 ,FIELD_SUMMARY VARCHAR2(64)
 ,ASL VARCHAR2(10)
 ,BASE VARCHAR2(10)
 ,EXTRA VARCHAR2(40)
 ,EPICS_DEF_INITIAL VARCHAR2(10)
 ,EPICS_DEF_SIZE NUMBER
 ,INTEREST NUMBER
 ,MENU VARCHAR2(40)
 ,PP VARCHAR2(10)
 ,PROMPT VARCHAR2(40)
 ,PROMPTGROUP VARCHAR2(35) 
 ,SPECIAL VARCHAR2(35)
 )
/

PROMPT Creating Table 'GENERATORS'
DROP TABLE GENERATORS
/
CREATE TABLE GENERATORS
 (DEVICE_ID VARCHAR2(40) NOT NULL
 ,PARAM_NAME VARCHAR2(40) NOT NULL
 ,PARAM_VALUE VARCHAR2(40)
 )
/
commit
/
