-- h:\xconnect\TableGeneration\XCTableGen.con
--

SET ECHO ON
SPOOL XCTableGen.con.OUT
  
undefine tbl_owner
undefine tbl_hdb
define tbl_owner = &1
define tbl_hdb = &2

-- Create function-based index on HDB_SITE.SITE_COMMON_NAME
-- Because XConnect always key on STATION_ID, this index can 
-- improve performance and avoid full table scan
CREATE INDEX &&tbl_owner..XC_UPPER_SITE_COMMON_NAME_IDX 
ON &&tbl_hdb..HDB_SITE(UPPER(SITE_COMMON_NAME))
  TABLESPACE "HDB_IDX";

CREATE INDEX &&tbl_owner..XC_UPPER_STATION_ID_IDX 
ON &&tbl_owner..XC_EXT_SITES(UPPER(STATION_ID))
  TABLESPACE "HDB_IDX";

CREATE INDEX &&tbl_owner..XC_UPPER_SENSORNAME_IDX 
ON &&tbl_owner..XC_EXT_SITESENSORS(UPPER(STE_STATION_ID), UPPER(SENSORNAME))
  TABLESPACE "HDB_IDX";

-- PROMPT Creating Primary Key on 'XC_EXT_SITES'
ALTER TABLE &&tbl_owner..XC_EXT_SITES
 ADD (CONSTRAINT STE_PK PRIMARY KEY 
  (STATION_ID) using index tablespace hdb_idx storage (initial 50k next 50k pctincrease 0));

-- PROMPT Make hdb_site.site_name unique
--alter table &&tbl_owner..hdb_site
--    ADD ( CONSTRAINT UNIQUE_SITE_NAME UNIQUE(site_name)
--      using index tablespace hdb_idx storage (initial 50k next 50k pctincrease 0) ); 

-- PROMPT Creating Foreign Keys on 'XC_EXT_SITES'
--ALTER TABLE &&tbl_owner..XC_EXT_SITES 
--ADD ( CONSTRAINT SITE_FK FOREIGN KEY 
--  (STATION_ID) REFERENCES &&tbl_owner..hdb_site(site_name) );
  
-- PROMPT Creating Check Constraints on 'XC_EXT_SITES'
ALTER TABLE &&tbl_owner..XC_EXT_SITES
 ADD CONSTRAINT AVCON_5863_ENABL_000 CHECK (ENABLED IN ('Y', 'N'))
 ADD CONSTRAINT AVCON_5863_TIME__000 CHECK (GMT_OFFSET 
IN (0, -12, 11, -11, 12, 1, -10, 2, -9, 3, -8, 4, -7, 5, -6, 6, -5, 
7, -4, 8, -3, 9, -2, 10, -1));

-- PROMPT Creating Primary Key on 'XC_EXT_SITESENSORS'
ALTER TABLE &&tbl_owner..XC_EXT_SITESENSORS
 ADD ( CONSTRAINT SITSEN_PK PRIMARY KEY 
  (STE_STATION_ID, SENSORNAME)
  using index tablespace hdb_idx storage (initial 50k next 50k pctincrease 0) );

-- PROMPT Creating Foreign Keys on 'XC_EXT_SITESENSORS'
ALTER TABLE &&tbl_owner..XC_EXT_SITESENSORS ADD CONSTRAINT
 SITSEN_STE_FK FOREIGN KEY 
  (STE_STATION_ID) REFERENCES &&tbl_owner..XC_EXT_SITES
  (STATION_ID);

-- PROMPT Make XC_EXT_SITESENSORS.site_datatype_id unique
alter table &&tbl_owner..XC_EXT_SITESENSORS
    ADD ( CONSTRAINT UNIQUE_SDI_ID UNIQUE(site_datatype_id)
      using index tablespace hdb_idx storage (initial 50k next 50k pctincrease 0) ); 

-- PROMPT Creating Foreign Keys on 'XC_EXT_SITESENSORS'
ALTER TABLE &&tbl_owner..XC_EXT_SITESENSORS ADD CONSTRAINT
 SITE_ID_FK FOREIGN KEY 
  (site_datatype_id) REFERENCES &&tbl_hdb..hdb_site_datatype
  (site_datatype_id);

-- PROMPT Creating Check Constraints on 'XC_EXT_SITESENSORS'
ALTER TABLE &&tbl_owner..XC_EXT_SITESENSORS
 ADD CONSTRAINT CON_DATA_TABLE CHECK (DATA_TABLE 
IN ('XC_DATA7', 'XC_DATA8', 'XC_DATA9', 'XC_DATA10', 'XC_DATA11', 
'XC_DATA1', 'XC_DATA2', 'XC_DATA12', 'XC_DATA3', 'XC_DATA4', 
'XC_DATA5', 'XC_DATA6', 'XC_TXTDATA1'))
 ADD CONSTRAINT CON_ALERT_TYPE CHECK (ALERT_TYPE IN ('Normal', 'Wind Run', 'Wind Dir'));
                  

-- PROMPT Creating Primary Key on 'XC_EXT_DATA1'
ALTER TABLE &&tbl_owner..XC_EXT_DATA1
 ADD (CONSTRAINT DAT1_PK PRIMARY KEY 
  (site_datatype_id,
   interval,
   start_date_time,
   end_date_time,
   SOURCE
   )
	using index tablespace hdb_idx storage (initial 50k next 50k pctincrease 0) ); 

-- PROMPT Creating Foreign Keys on 'XC_EXT_DATA1'
ALTER TABLE &&tbl_owner..XC_EXT_DATA1 ADD CONSTRAINT
 DAT1_HDB_FK FOREIGN KEY 
  (site_datatype_id) REFERENCES &&tbl_hdb..hdb_site_datatype
  (site_datatype_id);


-- PROMPT Creating Primary Key on 'XC_GOESQC'
ALTER TABLE &&tbl_owner..XC_GOESQC
 ADD ( CONSTRAINT GOESQC_PK PRIMARY KEY 
  (NESDIS_ID
  ,TIME_TAG
  ,SOURCE)
	using index tablespace hdb_idx storage (initial 50k next 50k pctincrease 0) ); 


-- PROMPT Creating Primary Key on 'XC_RTUQC'
ALTER TABLE &&tbl_owner..XC_RTUQC
 ADD ( CONSTRAINT RTUQC_PK PRIMARY KEY 
 (
    STATION_ID,
    TIME_TAG,
    RX_TX
 ) using index tablespace hdb_idx storage (initial 50k next 50k pctincrease 0) ); 

-- PROMPT Creating Primary Key on 'XC_TXTDATA1'
ALTER TABLE &&tbl_owner..XC_TXTDATA1
 ADD ( CONSTRAINT TXD1_PK PRIMARY KEY 
  (  
    STATION_ID,
    SENSORNAME,
    SOURCE,
    TIME_TAG
    )
	using index tablespace hdb_idx storage (initial 50k next 50k pctincrease 0) ); 

-- PROMPT Creating Primary Key on 'XC_PPLIST'
ALTER TABLE &&tbl_owner..XC_PPLIST
 ADD ( CONSTRAINT PPLIST_PK PRIMARY KEY 
  (STATION_ID
  ,SENSORNAME
  ,DATA_LABEL
  ,FUNCTION_NAME
 )
	using index tablespace hdb_idx storage (initial 50k next 50k pctincrease 0) ); 

-- PROMPT Creating Primary Key on 'XC_PPDATA1'
ALTER TABLE &&tbl_owner..XC_PPDATA1
 ADD ( CONSTRAINT PPDAT1_PK PRIMARY KEY 
  (STATION_ID
  ,SENSORNAME
  ,DATA_LABEL
  ,FUNCTION_NAME
  ,TIME_TAG
  ,TIME_PERIOD)
	using index tablespace hdb_idx storage (initial 50k next 50k pctincrease 0) ); 

SPOOL OFF
