How to upgrade OSCAR to the latest version

Because OSCAR is open source software, all physicians are welcome (encouraged, even) to download OSCAR and set it up in their clinic. At any time. Free of charge. I still can't get used to that: the most useful EMR I have used in my 12 years of using electronic GP records is free.

Oscar guru David Daley has an excellent 'how to' for doctors that want to set up OSCAR EMR for their clinic.

However, OSCAR is constantly being updated and improved upon. New features are being added, and bugs fixed by a small army of volunteer and professional programmers around Canada (and the world). The fantastic thing about open source is that when a family doctor in Saskatoon adds a new feature, it can become available to everyone else who uses OSCAR. The code for OSCAR is stored in a repository which is constantly updated.

To take advantage of the latests stable version of OSCAR, you can upgrade your system for free. Here's how.

Notes:
i. This how to assumes that you have installed using the settings and locations in David Daley's how-to which I have cribbed from here to create the update how-to).

ii. I use vi ‘vi’ as my text editor, but it is not for everyone. If you want to use another editor, replace ‘vi’ in the commands that follow with, for example, nano.


Log into your server using terminal.

Backup your server (if the update goes horribly wrong, it is essential that you can roll back your server to what you had before).

Change to your backup folder.

cd /usr/local/backups


We're going to create a backup script to totally backup your oscar installation, so open a new file:

sudo vi extendedBackupOSCAR.sh


Enter the following text and save the file:

#!/bin/sh -e

#set the backup directory name
installation=oscar
backup=/usr/local/backups
mysql_root_password=*******

oscarname=extended.$installation.backup
backupdir=$backup/$oscarname-`date +%F`

#create the backup directory
mkdir $backupdir

#dump the mysql data into the backup
sqlname=$oscarname.sqldump.`date +%F`
/usr/bin/mysqldump --add-drop-table -uroot -p$mysql_root_password oscar_mcmaster > $backupdir/$sqlname.sql
gzip $backupdir/$sqlname.sql

#tar the oscar server
tarname=$oscarname.webapps.`date +%F`
tar -cf $backupdir/$tarname.tar /usr/share/tomcat5.5/webapps
gzip $backupdir/$tarname.tar


Run the script:

sudo ./extendedBackupOSCAR.sh


Now transfer the backup files you just created to somewhere safe (make sure that they are stored encrypted and/or securely as they contain all your patient information).


Update the source code on your system

We are going to update to OSCAR 9.05 in this example (by setting the date for 31 May 2009).

cd $HOME/src/oscar_source

cvs -d:pserver:anonymous@oscarmcmaster.cvs.sourceforge.net:/cvsroot/oscarmcmaster login


(you will be asked for a password, you can leave this blank).

cvs -z3 -r -d:pserver:anonymous@oscarmcmaster.cvs.sourceforge.net:/cvsroot/oscarmcmaster co -r RELEASE_9_06 oscar_mcmaster

cvs -z3 -d:pserver:anonymous@oscarmcmaster.cvs.sourceforge.net:/cvsroot/oscarmcmaster co -P -D "2009-07-20 23:59:59" drugref

cvs -d:pserver:anonymous@oscarmcmaster.cvs.sourceforge.net:/cvsroot/oscarmcmaster logout


Wait for the files to download, then compile the new version of the software.

cd $HOME/src/oscar_source/oscar_mcmaster/build

ant


Once the build is finished (you should get a 'Build Successful' confirmation message), transfer ONLY the updated oscar.war file to your tomcat server:
**WARNING! copying all the .war files over (ie: including the OscarDocument.war file) will delete all of you uploaded documents such as scanned results and old charts.**

sudo cp $HOME/src/oscar_source/oscar_mcmaster/build/tmp/oscar.war $CATALINA_HOME/webapps/


and restart the tomcat server

sudo /etc/init.d/tomcat5.5 restart


Update the MySQL database

Now you need to update your MySQL database with any changes that have taken place since the last update.

First, we move to the oscar mysql updates directory and list all the MySQL update files:

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

ls -l


We need to run all the files ending in .sql which have a file name with a date BETWEEN the last installation date and our chosen update date. Run them in date order (oldest first).

(Replace ****** in the following with your MySQL root password.)

mysql -u root -p******** oscar_mcmaster < update-YYYY-MM-DD.sql


Then, we do the same with the caisi mysql updates directory and list all the MySQL update files:

cd /home/robbie21/src/oscar_source/oscar_mcmaster/database/mysql/caisi/updates/

ls -l


Again, we need to run all the files ending in .sql which have a file name with a date BETWEEN the last installation date and our chosen update date. Run them in date order (oldest first).

(Replace ****** in the following with your MySQL root password.)

mysql -u root -p******** oscar_mcmaster < patch-YYYY-MM-DD.sql


Update the DrugRef database

Delete the old drug ref folders and replace them with the new ones:

sudo rm -R /usr/local/DPD

sudo rm -R /usr/local/drugref-ca_1_5

sudo mkdir /usr/local/DPD

sudo mkdir /usr/local/drugref-ca_1_5

sudo cp $HOME/src/oscar_source/drugref/drugref2/DPD/* /usr/local/DPD

sudo cp $HOME/src/oscar_source/drugref/drugref2/drugref-ca_1_5/* /usr/local/drugref-ca_1_5

sudo chown -R postgres:postgres /usr/local/DPD

sudo chown -R postgres:postgres /usr/local/drugref-ca_1_5


Now change users to the postgres user:

su - postgres


Run postgresql and elete the old drug ref database

psql

which will bring up the postgresql prompt 'postgres=# '

drop database drugref2;

and you should get back the confirmation 'DROP DATABASE'.

Quit out of postgresql:

\q

Once back at the normal command line, move to the new drug ref folder.

cd /usr/local/DPD


Make the scripts executable:

chmod +x create_database.sh

chmod +x import_dpd.sh


And run the scripts to create the new database. The second command will download the latest drug database from Health Canada. This import script will take some time, so be patient (a good time to get a fresh coffee). You can ignore the ERROR: table "*****" does not exist CREATE TABLE messages.

./create_database.sh

./import_dpd.sh


We need to add a new first line to the DrugRef calling program to compensate for a python issue.

First, edit the file:

vi /usr/local/drugref-ca_1_5/drugref_service.py


And now add as the very first line (including the hash # sign):

# coding: latin-1


Save that file, and then exit from the postgres user account.

exit


That's it!





Check everything is working by logging into OSCAR.

That's it!

How many patients do I have?

One of the great strengths of OSCAR is the incredible search tools built into it.

These tools, called Query by Example and Report by Template, allow users to search for any combination of information on the database.

One of the things that amazed me when I came to Canada was that many of the GPs I spoke to did not know exactly how many patient they had.

With OSCAR, we simply go to 'Query by Example' and enter the following search line:

select count(*) AS 'Number of Active Patients' from demographic where patient_status='AC';

Hit the QUERY button and up pops:

Number of Active Patients
305

Ok, that's pretty simple.

But what about patients who are on our system but may have moved away, moved doctors etc.. This is a perennial problem for doctors.

Let's try a report template for this one.

First we need to save our template to a blank text file. Open up a text editor (for example, notepad on Windows, TextEdit on the mac, gEdit on Ubuntu) and cut and paste the following:

<report title="Admin - Stragglers" description="Lists patients who have not booked an appointment for a user-defined period of time.">

<query>


SELECT hin AS 'Health#',
CONCAT(last_name,', ',first_name) AS 'Name',
CONCAT(year_of_birth,'-',month_of_birth,'-',date_of_birth,' ') AS 'DoB',
phone AS 'Phone #',
CONCAT(address,',',city,' ',province,' ',postal) AS 'Address'
FROM demographic
WHERE patient_status='AC'
&& (roster_status='' || roster_status='RO')
&& demographic_no NOT IN (
 select demographic_no
 FROM appointment
 WHERE appointment_date > DATE_SUB(NOW(), INTERVAL {qty} {unit})
 GROUP BY demographic_no
)
GROUP BY demographic_no
ORDER BY Name
;


</query>



<param id="qty" type="list" description="Not seen for QUANTITY:">
 <choice id="1">1</choice>
 <choice id="2">2</choice>
 <choice id="3">3</choice>
 <choice id="4">4</choice>
 <choice id="5">5</choice>
 <choice id="6">6</choice>
 <choice id="7">7</choice>
 <choice id="8">8</choice>
 <choice id="9">9</choice>
 <choice id="10">10</choice>
 <choice id="11">11</choice>
</param>



<param id="unit" type="list" description="Not seen for UNIT:">
 <choice id="YEAR">years</choice>
 <choice id="MONTH">months</choice>
</param>

</report>

In the main OSCAR screen, click on ADMIN, then REPORT BY TEMPLATE. Click on ADD A NEW TEMPLATE. Browse for the text file you just saved and click 'UPLOAD'.

Voila - you now have your saved report template.



Select how far back you want to go and hit run: you will then get a print out of the names, health numbers, phone numbers, and addresses of all your active patients who have not booked an appointment in the last x years.





The searches have endless uses, and allow you to take your patient care to a level that is simply not possible with paper charts (or is very, very, very, very, prohibitively, very time consuming with paper charts).

For example, how many of my diabetic patients have had their eGFR checked in the last year? How many of my diabetic patients are not on an ACE inhibitor? How many of my diabetic patients are not on a statin? How many patient are being prescribed diabetes medications but are not on our diabetes register for regular follow up? Etc. Etc.






There are lots of pre-written searches available on the OSCAR canada websites and on the user forums. However, if you are interested in writing your own (or paying someone to write them for you), the queries are all written in the widely-used SQL language which every professional programmer can understand. There is also a 'cookbook' which I would recommend to anyone (the MySQL Cookbook).