[Oscarmcmaster-devel] Upgrading database schema, am I doing it right?

[ian p. <ia...@ro...>]

UPDATE 9.06 TO 9.12

I'm trying to update the OSCAR 9.06 at least to 9.12

I do this in the Mac Terminal ssh'ed into the Ubuntu Linux server  
rather than using the Linux desktop because of the inconsistencies of  
the COPY/PASTE key commands eg. Paste in Linux Terminal is SHIFT- 
CONTROL-V and CONTROL-V in Firefox

In the old OSCAR system 9.06
transfer the mysql dump to new OSCAR via the mysql dump command
mysqldump -uroot -pMySQL_PASSWORD   --add-drop-table  oscar_mcmaster  
 > ~/Documents/mysqlbackupDUMP.sql

use the scp command to transfer to new system.

In the new OSCAR system 9.12, I upload the entire old database into  
new OSCAR

mysql -uroot -pMySQL_PASSWORD  oscar_mcmaster < ~/Documents/ 
mysqlbackupDUMP.sql

cd $HOME/src/oscar_source/oscar_mcmaster/database/mysql/updates

ls -1
Don't type the updates manually, copy the directory listing and make  
mysql commands out of them
do a ls -1   — thats a one numeral not letter "L"
list what updates there are, copy the listing to a text editor and  
put the  "mysql -u root -pMySQL_PASSWORD oscar_mcmaster < " in  
front.  search and replace function is handy.  COpy it back to the  
terminal.

mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2009-07-02.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2009-07-06.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2009-07-31.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2009-08-20b.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2009-08-20.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2009-10-02.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2009-10-22.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2009-11-11.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2009-12-08.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2010-02-01.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2010-03-26.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2010-03-30.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2010-04-13.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2010-04-29.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster < update-2010-06-02.sql

cd $HOME/src/oscar_source/oscar_mcmaster/database/mysql/caisi/ 
updates/  -- there was a typo in a OSCAR PEI updating blog;  his own  
directory name not $HOME

ls -1

mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-07-17 
(2).sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-07-17.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-08-20.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-08-25b.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-08-25.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-08-27.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-08-29.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-09-02.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-09-10.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-09-16.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-09-17.sql
mysql -u root -pMySQL_PASSWORD oscar_mcmaster <   patch-2009-09-22.sql


Some updating commands don't work (says some tables not found and  
don't exist

  update-2009-07-06.sql
ERROR 1050 (42S01) at line 1: Table 'dsGuidelines' already exists

  update-2009-07-31.sql
ERROR 1146 (42S02) at line 3: Table 'oscar_mcmaster.billingmaster'  
doesn't exist

update-2009-08-20.sql
ERROR 1050 (42S01) at line 1: Table 'dx_associations' already exists

update-2010-03-26.sql
ERROR 1050 (42S01) at line 1: Table 'IntegratorControl' already exists

  patch-2009-08-20.sql
ERROR 1050 (42S01) at line 1: Table 'intake_node_js' already exists

  patch-2009-09-16.sql
ERROR 1050 (42S01) at line 1: Table 'CdsFormOption' already exists


It seems to work but I'm afraid I'm missing something.
I wish there was some documentation of what each table does .  The   
java source code and even the oscar.properties  don't have enough  
comments.
Such I manually create a billingmaster table in the mysql shell.

Since OSCAR is written in a relatively low level language, I'm trying  
to figure things by reserve engineering.  We need MORE comments in  
the source code:  Steve Wozniak wrote the Apple II monitor in 6502  
assembly language and every line was commented.  There are also  
inconsistencies in the field names between tables  e.g. demographicNo  
demographic_no etc

No ease of programming compares to Apple Hypercard :)

Also, I'm trying to data mine the lab values for a hepatitis B  
antigen  to get all my Hepatitis B carriers.      GDML types it as
HEPATITIS B SURFACE ANTIGEN (HBsAg)                      POS

it as  I know it's stored somewhere inside  but it seems that it's  
encoded in the hl7TextMessage table

select * from hl7TextMessage where lab_id = 233;

|    233 |                 30 | TVNIfF5 
+XCZ8R0RNTHxHRE1MfHx8MjAxMDEyMjAyMDQyMDl8fE9SVV5SMDF8TE9QVU4gICAuMTkyOTY 
4MTN8UHwyLjMNClBJRHwxfDY5MjE0ODI0NjZeR05eT058QkItMzk4NjIxNjJ8fENIT1deTUF 
ZXkt8fDE5NTIxMDA0fEZ8fHwyOSBST1NFQkFOSyBEUiA1MDheU0NBUkJPUk9VR0heT05UQVJ 
JT15PTl5NMUI1WTd8fCg0MTYpMzIxLTEyOTMNCk5URXwxfEZBU1RJTkcNCk9CUnwxfHx8MTE 
xR15HTFVDT1NFIFNFUlVNIEZBU1RJTkdeTDExMUFeQ0hFTUlTVFJZfFJ8MjAxMDEyMjAxMTI 
1fDIwMTAx     etc etec

the measurements table doesn't tag it!!!

Ian PUN MD