To update from an R4 db to an R5 db.  Conversion time: about 1 hour

-3. Consider doing all this to a COPY of the working r4 database

-2. In sqlplus, do this to see what's being done
    set echo on

-1. Drop old triggers (note that we do really_drop twice to make sure
    we hit everything)
    NOTE: really_drop will throw errors.  ignore them.
    - drop_old.sql -- this will create file called really_drop.sql
    - really_drop.sql
    - really_drop.sql

0.  Create the DDLState table
    - create_ddl.sql


1. Drop data from certain tables (if you can afford to lose this info)
    - truncate table log
    - truncate table event
    - truncate table alert
    - truncate table error

2.  Add syncdeleted field; ignore any errors
    - add_delete.sql

2a.  Populate syncdeleted field based on activationtime and deactivationtime
    - populate_delete.sql
    - commit

2b.
    - delete from component where syncdeleted!=0;
    - commit

3. Cleanup broken stuff:  Ignore "already has column" error.
    - cleanup.sql
    - cleanup2.sql
    - commit

4.  Run upgradedb and run the generated file
    - upgrade.sql    

5.  Run cleanupdb and run the generated file -- this will remove bad data
    - cleanupdb.sql    

6.  Touch every record to cause validity triggers to fire.
    - touch.sql

7.  Fix errors shown by triggers; goto 6.

8.  commit

----- data model upgrade
9. fix CK personnel
	- updatePersonnelHistoric.sql ** run ONLY ONCE - does id swapping **
	- commit

10. separate public and private problems into separate tables 
	-run com.patientkeeper.repositoryupkeep.r4r5.UpgradeProblemData (can be run more than once)
------

11. delete and reloads data into preference, headerdefinition, nomen, and standardcategory
	- run com.patientkeeper.repositoryupkeep.r4r5.ReloadData (can be run more than once)

12. fix hhmappable fields
	- remap.sql



DONE

Example of fixing errors found through touch:

SQL> @touch
update HPICKER set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find POSITION.SYNCID matching HPICKER.POSITIONID=601439,
HPICKER.SYNCREPOSITORYID=46606.
ORA-06512: at "SANJ.THPICKER", line 31
ORA-04088: error during execution of trigger 'SANJ.THPICKER'


update CHARGETRANSACTION set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find BILLINGBATCH.SYNCID matching
CHARGETRANSACTION.BATCHID=0, CHARGETRANSACTION.SYNCREPOSITORYID=248.
ORA-06512: at "SANJ.TCHARGETRANSACTION", line 38
ORA-04088: error during execution of trigger 'SANJ.TCHARGETRANSACTION'


update CTHEADERVALUE set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find HEADERDEFINITION.SYNCID matching
CTHEADERVALUE.FIELDID=2, CTHEADERVALUE.SYNCREPOSITORYID=638.
ORA-06512: at "SANJ.TCTHEADERVALUE", line 25
ORA-04088: error during execution of trigger 'SANJ.TCTHEADERVALUE'


update EVENT set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find MODULE.SYNCID matching EVENT.MODULEID=0,
EVENT.SYNCREPOSITORYID=308135.
ORA-06512: at "SANJ.TEVENT", line 31
ORA-04088: error during execution of trigger 'SANJ.TEVENT'


update ALERT set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find MODULE.SYNCID matching ALERT.MODULEID=0,
ALERT.SYNCREPOSITORYID=6517.
ORA-06512: at "SANJ.TALERT", line 31
ORA-04088: error during execution of trigger 'SANJ.TALERT'


update STOCKPHRASE set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find MODULE.SYNCID matching STOCKPHRASE.MODULEID=19531,
STOCKPHRASE.SYNCREPOSITORYID=101.
ORA-06512: at "SANJ.TSTOCKPHRASE", line 43
ORA-04088: error during execution of trigger 'SANJ.TSTOCKPHRASE'


update CUSTOMCOMPDEF set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find CUSTOMPANELDEF.SYNCID matching
CUSTOMCOMPDEF.COMPONENTDEFINITIONID=680948, CUSTOMCOMPDEF.SYNCREPOSITORYID=12.
ORA-06512: at "SANJ.TCUSTOMCOMPDEF", line 25
ORA-04088: error during execution of trigger 'SANJ.TCUSTOMCOMPDEF'


update COMPONENT set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find PANEL.SYNCID matching COMPONENT.PANELID=93,
COMPONENT.SYNCREPOSITORYID=49838.
ORA-06512: at "SANJ.TCOMPONENT", line 25
ORA-04088: error during execution of trigger 'SANJ.TCOMPONENT'


update PRIVATEPROBLEM set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find PROBLEM.SYNCID matching
PRIVATEPROBLEM.SYNCID=2147483650, PRIVATEPROBLEM.SYNCREPOSITORYID=102.
ORA-06512: at "SANJ.TPRIVATEPROBLEM", line 23
ORA-04088: error during execution of trigger 'SANJ.TPRIVATEPROBLEM'


update PROBLEMRELTN set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find PRIVATEPROBLEM.SYNCID matching
PROBLEMRELTN.SYNCPARENTID=2147483650, PROBLEMRELTN.SYNCREPOSITORYID=62.
ORA-06512: at "SANJ.TPROBLEMRELTN", line 19
ORA-04088: error during execution of trigger 'SANJ.TPROBLEMRELTN'


update POSITION set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find INSTITUTION.SYNCID matching
POSITION.INSTITUTIONID=601251, POSITION.SYNCREPOSITORYID=81.
ORA-06512: at "SANJ.TPOSITION", line 29
ORA-04088: error during execution of trigger 'SANJ.TPOSITION'


update PREFERENCE set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find POSITION.SYNCID matching PREFERENCE.POSITIONID=3451,
PREFERENCE.SYNCREPOSITORYID=45.
ORA-06512: at "SANJ.TPREFERENCE", line 31
ORA-04088: error during execution of trigger 'SANJ.TPREFERENCE'


update SYNCMARK set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find PATIENT.SYNCID matching SYNCMARK.SYNCID=628730,
SYNCMARK.SYNCREPOSITORYID=25.
ORA-06512: at "SANJ.TSYNCMARK", line 19
ORA-04088: error during execution of trigger 'SANJ.TSYNCMARK'


Input truncated to 54 characters
update FILTERREFDATAVALUE set syncversion=syncversion
       *
ERROR at line 1:
ORA-20002: Could not find REFERENCEDATAVALUE.SYNCID matching
FILTERREFDATAVALUE.SYNCID=634772, FILTERREFDATAVALUE.SYNCREPOSITORYID=681.
ORA-06512: at "SANJ.TFILTERREFDATAVALUE", line 19
ORA-04088: error during execution of trigger 'SANJ.TFILTERREFDATAVALUE'


SQL> desc snapshotdef;
ERROR:
ORA-04043: object snapshotdef does not exist


SQL> update hpicker set syncdeleted=syncrepositoryid where positionid=601439;
SQL> update chargetransaction set syncdeleted=syncrepositoryid where batchid=0;
SQL> update ctheadervalue set syncdeleted=syncrepositoryid where fieldid=2;
SQL> update event set syncdeleted=syncrepositoryid where moduleid=0;
SQL> update alert set syncdeleted=syncrepositoryid where moduleid=0;
SQL> update stockphrase set syncdeleted=syncrepositoryid where moduleid=19531;
SQL> update customcompdef set syncdeleted=syncrepositoryid where componentdefinitionid=680948;
SQL> update component set syncdeleted=syncrepositoryid where panelid=93;
SQL> update privateproblem set syncdeleted=syncrepositoryid where syncid=2147483650;
SQL> update problemreltn set syncdeleted=syncrepositoryid where syncparentid=2147483650;

SQL> update position set syncdeleted=syncrepositoryid where institutionid=601251;
SQL> update preference set syncdeleted=syncrepositoryid where positionid=3451;
SQL> update syncmark set syncdeleted=syncrepositoryid where syncid=628730;
SQL> update FILTERREFDATAVALUE set syncdeleted=syncrepositoryid where syncid=634772;
SQL> commit;

































































