-- Reloads existing 3 tables: EPICS_DEFINITIONS, DEFAULTS and MENUS of hdb
-- database, using  the ../epics/d0Epics.dbd definition file.
-----------------------------------------------------------------------------
--
-- This SQL script should be rewritten as Python script, but at least
-- it shows what is to be done.
-- Setups must be made before running this script:
--
-- > setup hdb -d
-- > source my_echo.cshrc
-- 
-- Next invoke SQL with username and password of the hdb database to be reloaded:
--
-- > sqlplus hdb@d0on/<hdb_pass>
-- 
-- Finally run this procedure with the hdb password, <hdb_pass>,
-- as input parameter 1:
--
-- SQL> @reload_epics_defs <hdb_pass>
--
-- Log file appears in ../logs/.  directory.
--
-----------------------------------------------------------------------------
--  
-- Created:
--    Jan 10, 2003  Stan K.
--
--
define db = 'hdb'
define passwd = &&1
--
--
spool ../logs/reload_epics_defs_1.log
--
PROMPT Droping Foreign Key 'TEMPL_FLD_DEF_FK' on 'TEMPL_FIELDS'
ALTER TABLE TEMPL_FIELDS
 DROP CONSTRAINT TEMPL_FLD_DEF_FK CASCADE
/

PROMPT Droping Foreign Key 'DFLTS_DEF_FK' on 'DEFAULTS'
ALTER TABLE DEFAULTS
 DROP CONSTRAINT DFLTS_DEF_FK CASCADE
/

PROMPT Deleting rows from 'MENUS'
TRUNCATE TABLE MENUS;

PROMPT Deleting rows from 'DEFAULTS'
TRUNCATE TABLE DEFAULTS;

PROMPT Deleting rows from 'EPICS_DEFINITIONS'
TRUNCATE TABLE EPICS_DEFINITIONS;

--
spool OFF
--
spool ../logs/reload_epics_defs_2.log
PROMPT Inserting into table EPICS_DEFINITIONS
spool OFF
host $my_echo &&db.\\n&&passwd | ../py/&&db._insert_epics_defs.py ../epics/d0Epics.dbd >> ../logs/reload_epics_defs_2.log
--
spool ../logs/reload_epics_defs_3.log
@update_epics_definitions.sql
PROMPT Using PROMPT to fill missing FIELD_SUMMARY in EPICS_DEFINITIONS
update epics_definitions set field_summary = ltrim(rtrim(prompt,'"'),'"')
where field_summary is NULL and prompt is not NULL;
commit;
--
PROMPT Inserting into table DEFAULTS, from EPICS_DEFINITIONS table
spool OFF
host $my_echo &&db.\\n&&passwd | ../py/&&db._insert_defaults.py >> ../logs/reload_epics_defs_3.log
--

spool ../logs/reload_epics_defs_4.log
PROMPT Recreating Foreign Key 'TEMPL_FLD_DEF_FK' on 'TEMPL_FIELDS'
ALTER TABLE TEMPL_FIELDS ADD CONSTRAINT
 TEMPL_FLD_DEF_FK FOREIGN KEY 
  (FIELD_NAME,RECORD_TYPE) REFERENCES EPICS_DEFINITIONS 
  (FIELD_NAME,RECORD_TYPE)
/

PROMPT Recreating Foreign Key 'DFLTS_DEF_FK' on 'DEFAULTS'
ALTER TABLE DEFAULTS ADD CONSTRAINT
 DFLTS_DEF_FK FOREIGN KEY 
  (FIELD_NAME,RECORD_TYPE) REFERENCES EPICS_DEFINITIONS 
  (FIELD_NAME,RECORD_TYPE)
/
commit
/

spool OFF
--
--
host cat ../logs/reload_epics_defs_1.log ../logs/reload_epics_defs_2.log ../logs/reload_epics_defs_3.log ../logs/reload_epics_defs_4.log > ../logs/reload_epics_defs.log
host rm ../logs/reload_epics_defs_*.log
