Browser Compatibility

OpenVPMS is designed to be used with Firefox, Chrome, Safari or Microsoft Edge.

The Context Sensitive Help facility provides help when you press Alt-F1 on most screens.

By default, the help is displayed in a separate browser window. If you want it displayed in a tab rather than a new window, try the following:

• Firefox: install the 'Open Link in New Tab' add-on
• Chrome: install the 'One Window' extension
• Safari: select Preferences|Tabs|Open pages in tabs instead of windows: Always

Note that both Context Sensitive Help and Print Previews are treated as pop-ups by browsers and may be blocked. You will need to enable pop-ups for your OpenVPMS site.

Data Migration

OpenVPMS doesn't directly support data migration from other veterinary practice systems.

It does however provide a plugin for Pentaho Data Integration (PDI) that can be used to get data into OpenVPMS.

The plugin works with PDI 9. This can be obtained from here.

Extract this zip file to a directory. This will be referred to as <PDI_HOME>.

To install the OpenVPMSLoader plugin:

1. Remove any existing openvpms-pentaho-plugin directory from <PDI_HOME>/plugin
2. Extract <OPENVPMS_HOME>/import/plugin/openvpms-pentaho-plugin.zip to     <PDI_HOME>/plugins
   This should automatically create a subdirectory under plugins named openvpms-pentaho-plugin
3. Copy the MySQL JDBC connector from <OPENVPMS_HOME>/lib to <PDI_HOME>lib

See also Implementors Forum|Data Migration

Locale and Fonts - Unix

Locale

The reports and documents in the release package make use of the system's locale to get the appropriate date and currency formats and to select the appropriate resource bundle. Many unix systems use the standard en_US.UTF-8 locale. To change the locale on a ubuntu system you can use:

sudo apt-get install language-pack-en-base
sudo nano /etc/default/locale   

to set the required locale, then log out and in again, and restart Tomcat.

The locale being used is shown on the Help screen.

Document Fonts

OpenVPMS can generate documents using OpenOffice or Microsoft Word, or JasperReports templates. These have different requirements when it comes to fonts.

OpenOffice and Microsoft Word

OpenOffice (.odt) or Microsoft Word (.doc) templates can use any font installed on the system.
Fonts may get remapped if the template was created on a Windows system but the OpenVPMS server is running on a unix system and the named font isn't available.
For example 'Bradley Hand ITC' will be mapped to 'URW Chancery L'. To avoid this, install the required fonts.
To install specific fonts on an Ubuntu server, see here.

JasperReports

JasperReports templates (.jrxml) included in the distribution use the DejaVu Sans font. Prior to OpenVPMS 2.3, this was specified within each template. From OpenVPMS 2.3, it is specified in the file
<TOMCAT_HOME>/webapps/openvpms/WEB-INF/classes/jasperreports.properties:

net.sf.jasperreports.default.font.name=DejaVu Sans

DejaVu Sans is used as it is widely available, but doesn't support all languages.

To use an alternative font:

1. package a JasperReports font extension, as per:
   • https://community.jaspersoft.com/wiki/custom-font-font-extension
   • https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-u...
2. copy the font extension .jar to <TOMCAT_HOME>/webapps/openvpms/WEB-INF/lib/
3. change net.sf.jasperreports.default.font.name property in the <TOMCAT_HOME>/webapps/openvpms/WEB-INF/classes/jasperreports.properties to use the name of the new font extension
4. restart Tomcat

Locale and Fonts - Windows

Locale

The reports and documents in the release package make use of the system's locale to get the appropriate date and currency formats and to select the appropriate resource bundle. 

Note that the locale used by OpenVPMS is that seen by the Java environment used by Tomcat and that this can be different from that you see when you log on as a Windows user. The locale being used is displayed on the Help screen. For how to set/check the Java locale see here.

If you run Tomcat under a user account, then you need to check the locale set for that account.

However, if you run Tomcat under the system account (the normal setup) then you need to ensure that the locale set for the 'Welcome Screen and System accounts' is as required. If not, on the Copy Settings window, click the 'Welcome screen and system account' box and press OK. Note also that it is the 'Format:' setting that determines the date and currency formats.

If you change the region settings, then you MUST restart Tomcat for the change to take affect.

Document Fonts

OpenVPMS can generate documents using OpenOffice or Microsoft Word, or JasperReports templates. These have different requirements when it comes to fonts.

OpenOffice and Microsoft Word

OpenOffice (.odt) or Microsoft Word (.doc) templates can use any font installed on the system.
Fonts may get remapped if a specified font is not available on the Windows system where OpenVPMS is deployed. To avoid this, install the required fonts as per https://support.microsoft.com/en-us/office/add-a-font-b7c5f17c-4426-4b53...

JasperReports

JasperReports templates (.jrxml) included in the distribution use the DejaVu Sans font. Prior to OpenVPMS 2.3, this was specified within each template. From OpenVPMS 2.3, it is specified in the file
<TOMCAT_HOME>\webapps\openvpms\WEB-INF\classes\jasperreports.properties:

net.sf.jasperreports.default.font.name=DejaVu Sans

DejaVu Sans is used as it is widely available, but doesn't support all languages.

To use an alternative font:

1. package a JasperReports font extension, as per:
   • https://community.jaspersoft.com/wiki/custom-font-font-extension
   • https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-u...
2. copy the font extension .jar to <TOMCAT_HOME>\webapps\openvpms\WEB-INF\lib\
3. change net.sf.jasperreports.default.font.name property in the <TOMCAT_HOME>\webapps\openvpms\WEB-INF\classes\jasperreports.properties to use the name of the new font extension
4. restart Tomcat

New Installation

The following describes how to install OpenVPMS from scratch and assumes that you have downloaded it and unpacked its zip file.

If you have not installed the other required software, see Requirements.

The headings below are:

• Directory structure
• MySQL connector
• Database setup
• Document Templates
• Web application installation
• OpenOffice installation
• Testing the installation
• Optional steps
  • VeNom codes
  • Australian states and suburbs
  • External Edit support
• Adding users

Note that in the following the directory or folder separator character is shown as /, following unix conventions. On Windows, replace / with \. e.g. given:
  <OPENVPMS_HOME>/bin
change to:
  <OPENVPMS_HOME>\bin

Directory structure

The OpenVPMS installation has a single top-level directory named:
   openvpms-release-XXX
where XXX indicates the version.

This will be referred to as <OPENVPMS_HOME> in the remainder of this document. This directory has the following sub-directories:

MySQL connector

The MySQL Connector/J JDBC driver needs to be downloaded from:
    https://dev.mysql.com/downloads/connector/j/
   
It is typically named mysql-connector-java-8.<x>.zip or mysql-connector-java-<x>.tar.gz where <x> represents the minor version number.

The JDBC driver in the archive is named:
    mysql-connector-java-8.<x>.jar.

This needs to be copied to:

• the Apache Tomcat library directory: <TOMCAT_HOME>/lib
• the OpenVPMS library directory:  <OPENVPMS_HOME>/lib

In the above, <TOMCAT_HOME> refers to the directory where Apache Tomcat is  installed. On Windows, this will be something like:
    C:\Program Files\Apache Software Foundation\Tomcat 8.0

Database setup

To create the OpenVPMS MySQL database:

1. run toolbox configure to configure the database connection properties
2. run toolbox database --create to create the database
3. run dataload to import data into the database

1. Run 'toolbox configure'
 

The toolbox configure command:

• is run from the <OPENVPMS_HOME>/bin directory
• prompts for database connection properties:
  • If there is a default value, it will be surround by square brackets.
  • To keep the default value, press enter.
• updates the openvpms.properties configuration file in <OPENVPMS_HOME>/conf

E.g.:

  > cd <OPENVPMS_HOME>/bin
  > toolbox configure

  Database URL [jdbc:mysql://localhost:3306/openvpms?useSSL=false]:
  Database user [openvpms]:
  Database password [arandompassword]:
  Reporting database URL [jdbc:mysql://localhost:3306/openvpms?useSSL=false]:
  Reporting database user [openvpmsreport]:
  Reporting database password [anotherrandompassword]:

  The file ../conf/openvpms.properties should have restrictive permissions to protect passwords

  Take a secure backup of the file ../conf/openvpms.properties
  This needs to be kept for subsequent updates.

If the MySQL database is on a different host to that running Tomcat, replace localhost in the Database URL with the correct host name or IP address.

2. Run 'toolbox database --create'

The toolbox database --create command:

• is run from the <OPENVPMS_HOME>/bin directory
• creates the database corresponding to the Database URL specified by toolbox configure
• creates the user corresponding to the Database user and password specified by toolbox configure
• loads archetypes

  > cd <OPENVPMS_HOME>/bin
  > toolbox database --create -u <admin-user> -p <admin-password>

    Created openvpms
    Archetypes successfully loaded

NOTES:

• replace <admin-user> with a user that has administrator privileges in MySQL and <admin-password> with their password
• If the MySQL database is a different host to that running Tomcat, use the --host argument to specify the Tomcat host:

toolbox database --create -u <admin-user> -p <admin-password> --host <tomcat-host>

3. Run dataload

The dataload command provides two options:

• base - loads a base database in preparation for data migration
• setup - contains a default setup suitable for a new installation

  > cd <OPENVPMS_HOME>/bin
  > dataload setup

4. Set administrator password

The dataload command creates a default administration user named admin with password admin.

This should be secured by providing a new password e.g.:

  > cd <OPENVPMS_HOME>/bin
  > toolbox user --setpassword admin -p somestrongpassword

Document and Report Templates

The templates used for document (ie invoices, payments etc.) and reports, are located in:
      <OPENVPMS_HOME>/reports

These need to be loaded prior to use. This can be done using the toolbox template --load command.
There are 3 sizes of templates supported: A4, A5 and Letter (i.e. US-Letter).

To load all A4 templates:

> cd <OPENVPMS_HOME>/bin
> toolbox template --load --size A4 --all
Loaded 'Counter Sale'
Loaded 'Credit'
Loaded 'Credit Adjustment'
Loaded 'Debit Adjustment'
Loaded 'Estimate'
Loaded 'Estimate Items'
Loaded 'Estimate Items-PT'
Loaded 'Invoice'
Loaded 'Invoice Items'
Loaded 'Invoice Items-P
...
Loaded 'Stock Movement Report'
Loaded 'Stock Reorder Report'
Loaded 'Stock Valuation Report'
Loaded 'Stocktake Export Report'
Loaded 'Stocktake List Report'
Loaded 'HL7 Messages Report'
Loaded 'Insurance Claims Report'

NOTES:

• If a template with the same name has been already loaded, it will be replaced. More precisely, templates with the same ORIGINAL name AND same content name will be replaced. Hence, if you have a template named Invoice, with content invoice-BE.jrxml, this will not be replaced but a new template named Invoice with content Invoice.jrxml will be created. However, a template named Invoice with content Invoice.jrxml which has been renamed to 'My Invoice' will be overwritten with the original name and new content.
• Not all templates are available in all formats, however a complete set is loaded. Thus if you load the A5 document set you will find that a few templates are in A4 format.
• If you plan to use A5 for only a restricted set of documents (eg only invoices and receipts) then you should load the A4 set, and then update the content of those templates that you want to use A5 stationary. See here.
• Three drug label sizes are available, Diskette, Shipping, and Video Top. All the document template sets (A4, A5, Letter) load the Diskette sized label. If you need one of the others, then you can load it from <OPENVPMS-HOME>\reports\Patient\Labels\Shipping or ...\Video Top.  These folders contain a readme.txt file specifying  the label dimensions.
• Not everything is loaded. For example the Samples and the Patient Reminder Post Cards are not.

After installation, templates can be updated using via Administration|Templates.

Templates may be customised if necessary - those that have content with a:

• .doc or .odt extension can be customised in OpenOffice Writer or Microsoft Word
• .jrxml extension can be customised in Jaspersoft Studio

Customising of the .odt template content will be required to:

1. change the paper size from A4 to Letter if you loaded the Letter template set
2. insert your logo etc

Customising of the .jrxml template content is not generally required - the localisation is done using the Letterhead facility.

See also Introduction|Reporting, Reference|Reports and Forms, and Administration|Templates.

Web application installation

To install the OpenVPMS web application:

1. Generate the web archive (war) using:
   

   > cd <OPENVPMS_HOME>/bin
   > ./toolbox war
   
   Created ../webapps/openvpms.war
   
   The file ../webapps/openvpms.war should have restrictive permissions to protect passwords
   
   

2. Copy <OPENVPMS_HOME>/webapps/openvpms.war to the <TOMCAT_HOME>/webapps  directory.
3. Start Apache Tomcat if it is not running

Testing the installation

To test the installation, open up a web browser and enter the address:

      http://localhost:8080/openvpms/login

Login to OpenVPMS using user admin and the password set above.

OpenOffice installation

OpenVPMS uses OpenOffice to perform reporting, printing and document conversion.
Install it as per your platform's requirements and then:

1. Go to Administration - System - Sessions
2. Click the OpenOffice button
3. In the OpenOffice window, click the Configure button
4. In the Configure OpenOffice window, ensure that the Home Directory matches that where OpenOffice is installed.
5. Click OK

Optional steps

The following installations steps are optional:

1. Load VeNom codes
2. Load Australian states and suburbs
3. Add support for External Edit

Note that after loading any data (as with any external changes to the database) you should restart Tomcat to ensure that its caches do not hold obsolete information.

VeNom codes

The VeNom codes are standardised codes for classifying veterinary data. OpenVPMS can use VeNom:

• presenting complaint and diagnosis codes to classify patient Problems.
• visit reason codes for Appointments and Visits.

VeNom codes can all be loaded using:

> cd <OPENVPMS_HOME>/bin
> dataload -d ../import/data/VeNom

To load specific codes, see below.

Loading VeNom presenting complaints

> cd <OPENVPMS_HOME>/bin
> dataload -f ../import/data/VeNom/presentingComplaint.xml

Loading VeNom diagnoses

> cd <OPENVPMS_HOME>/bin
> dataload -f ../import/data/VeNom/diagnosis.xml

Loading VeNom visit reasons

> cd <OPENVPMS_HOME>/bin
> dataload -f ../import/data/VeNom/visitReason.xml

Australian states and suburbs

These can be loaded using:

> cd <OPENVPMS_HOME>/bin
> dataload -f ../import/data/postcodesAU.xml

External Edit support

See Concepts - External Edit.

Adding users

The installation includes a single administrator user named 'admin' that has authorities to perform all operations.

New users can be added in the web application at Administration - Users by clicking the New button.

Users have categories and roles that determine what they can do.
E.g., clinicians should be assigned:

• the Clinician category
• the Clinician, Insurance Claims and Base Role roles

See Concepts - Users for more details.

Requirements

OpenVPMS can be installed on either a unix system (most commonly a Ubuntu* server), or a Windows system.  For Windows, for small practices you can install on a desktop or laptop running Windows 7, 8 or 10; for larger practices it may be better to use a Windows Server operating system.

Both Java and MySQL are available in 32 and 64 bit versions.
If your system has more than 4GB of memory, use the 64 bit version.

See the following sections for the software requirements for OpenVPMS:

• Server Software
• Workstation Software
• Optional Software

Server Software

• Java Platform, Standard Edition 8 or 11
• Tomcat 9.x
• MySQL 5.7
• MySQL Connector/J JDBC driver 8.0.x

• OpenOffice 4.0.x or higher

Java

OpenVPMS may be run using Java 8 or Java 11. Java 11 is preferred.

There are multiple providers:
 

• Oracle
  http://www.oracle.com/technetwork/java/javase/downloads/index.html

   

• AdoptOpenJDK
  https://adoptopenjdk.net/

Java 8

The mimum supported version of Java 8 is Java 8 update 161.
Earlier versions may not include the Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files. These are required for strong password encryption. If Java is already installed, its version can be determined by running java -version  e.g.:

> java -version
java version "1.8.0_191"
Java(TM) SE Runtime Environment (build 1.8.0_191-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.191-b12, mixed mode)

Java 11

There are a number of locale changes in Java 11, compared to Java 8.
This causes differences in formatting for date functions that use the LONG, MEDIUM and SHORT date formats, or include MMM (e.g., MMM yyyy). This will affect JasperReports in particular.
E.g. in Java 8, a MEDIUM date format in the en_AU locale would display:

20/09/2006

whereas in Java 11 displays:

20 Sep. 2006

The Java 8 functionality can be retained by passing the following argument to java:
 

-Djava.locale.providers=COMPAT,CLDR

When using the standard Tomcat scripts, this can be done by setting the CATALINA_OPTS environment variable:

Windows
In a setenv.bat script in the same directory as catalina.bat, add:

set CATALINA_OPTS=-Djava.locale.providers=COMPAT,CLDR

Unix
In a setenv.sh script in the same directory as catalina.sh, add:

export CATALINA_OPTS=-Djava.locale.providers=COMPAT,CLDR

Tomcat

Tomcat 9.x is supported.

See https://tomcat.apache.org/download-90.cgi
On Windows, select the 32-bit/64-bit Windows Service Installer

If OpenVPMS is hosted behind a proxy or load balancer, Tomcat needs to be configured to pass the correct client IP addresses. See Tomcat Configuration for details.

MySQL

• MySQL 5.7.
  See https://dev.mysql.com/downloads/mysql/5.7.html#downloads

NOTE: when upgrading from an earlier MySQL release, see the relevant MySQL manual for migrating the MySQL data e.g.:

• MySQL 5.5 Reference Manual - Updating MySQL

• MySQL 5.7 Reference Manual - Updating MySQL
   

MySQL:

• should be on the same host as Tomcat
• should accept connections on port 3306
• include the following lines in my.ini
  max_allowed_packet=16M
  innodb_file_per_table
  character-set-server = utf8
  innodb_buffer_pool_size = 512M

NOTES:

• If character-set-server is not set to utf8, some characters may not be stored correctly. E.g, μ may be replaced with ?
• The innodb_buffer_pool_size value should be seen as a minimum only.  See Memory below.

MySQL Connector/J JDBC driver 8.0.x

See https://dev.mysql.com/downloads/connector/j/

This may already be included in the MySQL server installation.

OpenOffice

OpenOffice 4.0.x or higher
See http://www.openoffice.org/download/

NOTE: several users have indicated that LibreOffice 5 is not 100% compatible with OpenOffice. Fields may not merge correctly, and documents created in LibreOffice may look different when printed via OpenVPMS.
 

Memory

A host with 4GB memory is the minimum recommended size for a server running OpenVPMS, with at least:

• 1024M allocated to Tomcat
• 1024M allocated to MySQL via the innodb_buffer_pool_size property

To set Tomcat's memory allocation, see How do I adjust memory settings?

Also see:

• Tuning

Locale and Fonts

For unix systems see Unix Locale and Fonts
For windows systems see Windows Locale and Fonts

Workstation Software

If you wish to use the External Edit facility, the following software is required on all workstations that will edit documents in OpenVPMS:

• OpenOffice 4.1.2 or higher
  See http://www.openoffice.org/download/
   
• OpenWebStart
  See https://openwebstart.com/download/

Optional Software

• Jaspersoft Studio 6.16.0 or higher
  This is only required to customise document templates.
  See https://community.jaspersoft.com/project/jaspersoft-studio/releases

   

• MySQL Workbench
  This is not required by OpenVPMS itself, but if you are going to do any report development, then you will find that the Workbench’s ability to build and test SQL queries is very useful. You can download it from http://dev.mysql.com/downloads/workbench.
   

Security

This page addresses various security related matters.

• Database passwords
• Administrator password
• User passwords
• File permissions
• Tomcat and HTTPS
• MySQL and SSL

Database passwords

The database user name and password is configured via the toolbox configure command which stores the configuration in:

<OPENVPMS_HOME>/conf/openvpms.properties
 

When the database is created, these are used to create a corresponding MySQL database user.

If the database user name or password is changed*:

• toolbox configure needs to be re-run to update the openvpms.properties file
• toolbox war needs to be re-run to package the web application with the updated openvpms.properties
• the openvpms.war file needs to be redeployed

* For information on setting the MSQL password see:

  https://dev.mysql.com/doc/refman/5.7/en/set-password.html

Administrator password

The default installation creates an OpenVPMS user named 'admin', with password 'admin'. This should be changed using either:

• toolbox user --setpassword admin -p somestrongpassword
  

• Administration - Users in the web application

User passwords

User passwords can be configured using:

• toolbox user --setpassword admin -p somestrongpassword
  

• Administration - Users in the web application

There is little restriction on what passwords may be entered, but it is recommended that strong passwords are used.

File permissions

The OpenVPMS and Tomcat installation directories should only be accessible to a single user with a strong password.

These directories contain files that could enable an attacker to gain access to the OpenVPMS web application, or the MySQL database.

Tomcat and HTTPS

For security, Tomcat should be configured to use HTTPS connections. These encrypt data travelling between the browser and web server.

See SSL/TLS Configuration HOW-TO in the Apache Tomcat documentation.

MySQL and SSL

The default openvpms.properties configuration disables SSL access to the MySQL database server by specifying useSSL=false in the JDBC connection string i.e.:

db.url = jdbc:mysql://localhost:3306/openvpms_dev?useSSL=false

To connect to a MySQL server securely, see Connecting Securely Using SSL in the MySQL documentation.

Subscription

OpenVPMS relies on user subscriptions to fund development.

Your subscription status is displayed on the OpenVPMS login screen. If you have not paid, a link to a payment page is displayed. On payment, a subscription key will be mailed to you.

If you have a current subscription, you can request a subscription key by emailing a copy of your receipt to subscription[at]openvpms[dot]org.

To update your subscription status, edit the Practice in the Administration|Organisation workspace and upload the new subscription key.

Upgrade an existing system

This section details the procedure to be used when upgrading to a new release of OpenVPMS.

The headings below are:

• Release Notes
• Requirements
• Directories
• Configuration
• Preparation
  • Backup
  • Upgrade Time
  • MySQL connector
  • Disk space
  • Replication
• Database Upgrade
• Web application
• Customisation
• Document Templates

Having installed the new release, you may want to look at the Implementation Checklist page.

Note that in the following the directory or folder separator character is shown as /, following unix conventions. On Windows, replace / with \. e.g. given:
  <OPENVPMS_HOME>/bin
change to:
  <OPENVPMS_HOME>\bin

Release Notes

Release Notes are provided for sub-releases (e.g. 2.2.1, 2.2.2 etc). If you are upgrading to a sub-release then you should consult the release notes on the download page at https://www.openvpms.org/download

Requirements

The software required to run OpenVPMS may change between releases.

Check Requirements to ensure you have the necessary software.

Directories

These instructions assume that:

  1. The previous OpenVPMS installation is available in <OPENVPMS_PREV>.
     e.g. on Windows:
        c:\OpenVPMS\openvpms-release-2.3

  2. The new installation will be located in <OPENVPMS_HOME>.
     e.g. on Windows:
        c:\OpenVPMS\openvpms-release-2.4

NOTE: the OpenVPMS version can be excluded from the path name, for example c:\OpenVPMS-Current Release - when upgrading you can rename this to say 'Current Release prev' . This can simplify upgrades by removing the need to change custom scripts that contain the installation path.

The previous installation should be retained until:

•   settings have been migrated to the new installation
•   the migration has been verified to be successful

Configuration

Starting with OpenVPMS 2.2, there is an openvpms.properties configuration file located in the <OPENVPMS_HOME>/conf directory.

This contains:

• database connection details
• password decryption information

This is created or updated using:

> cd <OPENVPMS_HOME>/bin
> toolbox configure

Preparation

Backup

Backup be done using the mysqldump tool. e.g.:

> mysqldump -u <admin-user> -p openvpms --result-file openvpms.sql

Replace <admin-user> with a user that has administrator privileges in MySQL.

NOTE: It is good practice to ensure that the backup can be restored to a different server, prior to performing any upgrade.

See also How To - Administration - Backup.

Upgrade Time

Allow time for the database upgrade to take place. On large databases, this may take several hours.

In practices with limited windows for downtime, do a trial upgrade on a different host to estimate how long will be needed to perform the real migration.

The upgrade time may be reduced by making innodb_buffer_pool_size as large as possible for the duration of the upgrade.

MySQL connector

Copy the MySQL JDBC driver mysql-connector-java-<x>.jar from <OPENVPMS_PREV>/lib to <OPENVPMS_HOME>/lib

Disk Space

Database migration can require as much disk space as the largest table in the database. As a rule of thumb, ensure there is at least as much disk free as the database currently occupies, on the disk where the database resides.

The current database size can be determined by running:

> cd <OPENVPMS_HOME>/bin
> toolbox database --size
openvpms 100.05 GB

Replication

If you are replicating your OpenVPMS database, you must ensure that row-based replication is used. The upgrade scripts are not compatible with statement-based replication.

Database Upgrade

Upgrading from 1.9 or later

If you are upgrading from OpenVPMS 1.9 or a later release, then database migration is accomplished using the toolbox utility:

> cd <OPENVPMS_HOME>/bin
> toolbox database --update -u <admin-user> -p
Enter value for -p (Database administrator password):

37 changes need to be applied to update the database to the latest release.

WARNING: Updating the database can take a long time.
See https://openvpms.org/documentation/csh/2.4/topics/installing-openvpms/up...
for instructions on:
. backing up the database
. determining disk space requirements
. speeding up the update

DO NOT continue if the database is not backed up.

Proceed with the update? [Y/n] Y

Replace <admin-user> with a user that has administrator privileges in MySQL.

If you are upgrading from OpenVPMS 2.3, two MySQL users are required, a read/write user (default 'openvpms'), and a read-only user (default 'openvpmsreport').
These can be configured by running:

> cd <OPENVPMS_HOME>/bin
> toolbox configure
> toolbox database --setup-users -u <admin-user> -p

Upgrading from 1.8 or earlier

Upgrading from a version of OpenVPMS earlier than 1.9 requires that migration scripts first be run to bring the database up to 1.9, and then you can use the toolbox utility as above. 

The scripts are located in the <OPENVPMS_HOME>/legacy-migration directory. With the exception of the 1.5 to 1.6 release (where there was no change to the database structure), there is one sql script per release.

The sql scripts are:
     migrate-1.0-to-1.1.sql
     migrate-1.1-to-1.2.sql
     migrate-1.2-to-1.3.sql
     migrate-1.3-to-1.4.sql
     migrate-1.4-to-1.5.sql
     migrate-1.6-to-1.7.sql
     migrate-1.7-to-1.8.sql
     migrate-1.8-to-1.9.sql

You need to run each relevant one in turn using the mysql utility.

Hence if you are upgrading from OpenVPMS 1.8, run:
     > mysql -u openvpms -p openvpms < migrate-1.8-to-1.9.sql

If you are upgrading from OpenVPMS 1.7, run:
     > mysql -u openvpms -p openvpms < migrate-1.7-to-1.8.sql
     > mysql -u openvpms -p openvpms < migrate-1.7-to-1.9.sql

If you are upgrading from OpenVPMS 1.5 or 1.6, run:
I    > mysql -u openvpms -p openvpms < migrate-1.6-to-1.7.sql
     > mysql -u openvpms -p openvpms < migrate-1.7-to-1.8.sql
     > mysql -u openvpms -p openvpms < migrate-1.8-to-1.9.sql

If you are upgrading from OpenVPMS 1.0 - you need the lot, so run:
     > mysql -u openvpms -p openvpms < migrate-1.0-to-1.1.sql
     > mysql -u openvpms -p openvpms < migrate-1.1-to-1.2.sql
     > mysql -u openvpms -p openvpms < migrate-1.2-to-1.3.sql
     > mysql -u openvpms -p openvpms < migrate-1.3-to-1.4.sql
     > mysql -u openvpms -p openvpms < migrate-1.4-to-1.5.sql
     > mysql -u openvpms -p openvpms < migrate-1.6-to-1.7.sql
     > mysql -u openvpms -p openvpms < migrate-1.7-to-1.8.sql
     > mysql -u openvpms -p openvpms < migrate-1.8-to-1.9.sql

Migration Notes

1. The mysql command will prompt for a password. You can enter the password by including it directly following the '-p' - so if the password is say 'Opv1234' then you can use:
     > mysql -u openvpms -pOpv1234 openvpms < migrate-1.8-to-1.9.sql
2. If you want re-assurance that something is happening you can add the -v (verbose) option, ie:
     > mysql -v -u openvpms -pOpv1234 openvpms < migrate-1.8-to-1.9.sql
   If you want all the details, use -v -v -v, ie:
     > mysql -v -v -v -u openvpms -pOpv1234 openvpms < migrate-1.8-to-1.9.sql
3. If you are 're-migrating', i.e. you have an existing database that you are restoring a backup into, see Upgrade to a new machine for instructions to recreate and restore the database. This is necessary to avoid data corruption.

NOTE: With a large database, the 1.8 to 1.9 migration takes some time and using the -v or -v -v -v option is useful to reassure yourself that something is happening.

Web application

The web application needs to be created with the properties from <OPENVPMS_HOME>/conf/openvpms.properties.

This is done using the toolbox war command i.e.:

> cd <OPENVPMS_HOME>/bin
> toolbox war

Created ../webapps/openvpms.war

The file ../webapps/openvpms.war should have restrictive permissions to protect passwords

The existing web application should be removed before installing the new version.

To do this:

1. Shut down Apache Tomcat if it is already running.
2. Delete or move directory: <TOMCAT_HOME>/webapps/openvpms
   Do not move it to another directory under <TOMCAT_HOME>/webapps/ as Tomcat will continue to launch it.
3. Delete the file:      <TOMCAT_HOME>/webapps/openvpms.war
4. Delete the directory: <TOMCAT_HOME>/work/Catalina/localhost/openvpms
5. Copy <OPENVPMS_HOME>/webapps/openvpms.war to the directory <TOMCAT_HOME>/webapps
6. Start Apache Tomcat - this will extract <TOMCAT_HOME>/webapps/openvpms.war and build <TOMCAT_HOME>/webapps/openvpms

Customisation

If you use customised versions of the standard archetypes, or have added archetypes, these will need to be loaded.

For modified versions of the standard archetypes, be sure to incorporate any changes that have been made.

You should then use toolbox archetype --load to load these archetypes - or if you have only a few, use Administration|Archetypes|Import.

If you have customised versions of propercase.properties, help.properties, or messages.properties you need to install these in
  <TOMCAT_HOME>/webapps/openvpms/WEB-INF/classes/localisation

You can simply overwrite the default propercase.properties with your own version.

However, help.properties and messages.properties will need to be edited to bring your adjustments into the current versions.
 
If you have a customised default.stylesheet, then the version in
  <TOMCAT_HOME>/webapps/openvpms/WEB-INF/classes/style
will need to be edited to incorporate your changes.
 
Now restart Apache Tomcat so the above customisations get picked up and login and see that things are as they should be.

Document & Report Templates

To take advantage of the new and revised templates, they need to be loaded.

Templates are divided into two types:
•    document templates - Invoices, Receipts etc
•    report templates -  reports run from Reporting - Reports

It is strongly recommended that all sites run the latest versions of the standard reports.

If a site is using:

• standard document and report templates, then the latest versions can be loaded as per a new installation. This will replace existing templates, and add any new templates.
• custom document or report templates, then templates can be selectively loaded to avoid replacing customised ones

After loading, obsolete templates may need to be manually removed.

Selectively loading templates

Templates can be selectively loaded:

1. by type
2. by name
3. by creating a templates file that only loads the desired files
4. manually, by editing the appropriate Document Template (via Administration|Templates|Document Templates), and uploading the appropriate content file from <OPENVPMS_HOME>/reports

Loading templates by type

Document and report templates can be loaded separately. This can be useful if a site has customised one and not the other.

Document templates can be loaded using:

  > cd <OPENVPMS_HOME>/bin
  > toolbox template --load --size <size> documents

where size is one of A4, A5, or Letter.

Report templates can be loaded using:

  > cd <OPENVPMS_HOME>/bin
  > toolbox template --load --size <size> reports

where size is one of A4, or Letter.

In both cases, these will:

• replace existing Document Templates with the same Content name
• create new Document Templates where there is no existing template E.g. an existing Document Template named:

1. My Invoice with Content named Invoice.jrxml will be replaced by the standard Invoice with Content named Invoice.jrxml
2. Invoice with Content named Custom Invoice.jrxml, will not be replaced.The standard Invoice with Content named Invoice.jrxml will be loaded, and one must be manually deleted using Administration|Templates|Document Templates|Delete.

Loading templates by name

To load a subset of the available templates, specify them by name e.g.:

  > cd <OPENVPMS_HOME>/bin
  > toolbox template --load --size A4 'Invoice' 'Invoice Items' 'Invoice Items-PT'
  Loaded 'Invoice'
  Loaded 'Invoice Items'
  Loaded 'Invoice Items-PT'

Creating a templates file to load specific templates

To create a templates file that loads only the desired files, copy an existing templates xml file, and remove the lines that do not apply.

This will prevent standard templates replacing existing custom templates.
E.g., to exclude loading A4 invoices:

1. copy <OPENVPMS_HOME>/reports/documents-A4.xml to mydocuments.xml
2. remove or comment out the lines for the invoice templates that have been customised. (To aid this process you will find that the templates are grouped by sub-type in the xml file.) To comment them out, enclose the appropriate template lines by <!-- and --> - for example:

    <!--
    <template name="Invoice" archetype="act.customerAccountChargesInvoice" reportType="CUSTOMER"
              description="Invoice " path="Customer/Invoice/A5/Invoice.jrxml" mimeType="text/xml"
              docType="document.other"/>
    <template name="Invoice Items" archetype="SUBREPORT" reportType="CUSTOMER" description="Invoice Items "
              path="Customer/Invoice/A5/Invoice Items.jrxml" mimeType="text/xml" docType="document.other"/>
    -->

  3. load the templates

> cd <OPENVPMS_HOME>/bin
> toolbox template --load --file  ../reports/mydocuments.xml --all

Restart Tomcat after loading templates to ensure that its caches do not contain obsolete information.

See also Obsolete Document Templates.

Upgrade to a new machine

If you have an existing system but you are installing OpenVPMS on a new machine, you essentially go through the steps for a new install, but instead of creating a new openpms database, you restore your existing one, and then migrate that to the new release.

The steps are as follows:

1. Backup the existing database. See Preparation.
2. Perform the New Installation steps, stopping at the Database Setup step
3. Set up openvpms.properties
4. Create an empty OpenVPMS database
5. Restore the backup
6. Upgrade the database
7. Perform the New Installation step: OpenOffice installation
8. Perform the remaining Upgrade steps, starting at Web Application

Set up openvpms.properties

If the existing database is from OpenVPMS 2.2 or higher, the openvpms.properties file needs to be copied from the existing installation to the new installation.

This is located in <OPENVPMS_HOME>/conf.

If the existing database is from an earlier release, openvpms.properties needs to be configured. See Run 'toolbox configure'.

Creating an empty OpenVPMS database

To create an OpenVPMS database, run the following in a shell prompt:

> cd <OPENVPMS_HOME>/bin
> toolbox database --create --empty -u <admin-user> -p <admin-password>
Created openvpms

Replace <admin-user> with a user that has administrator privileges in MySQL and <admin-password> with their password.

This:

• creates the database corresponding to the Database URL specified by the toolbox configure command
• creates the user corresponding to the Database user and password specified by the toolbox configure command

The user only has privileges to connect from localhost. If the MySQL database is a different host to that running Tomcat, use the --host argument to specify the Tomcat host:

> toolbox database --create -u <admin-user> -p <admin-password> --host <tomcat-host>

Restoring the backup

Once the database is created, the mysql utility can be used to restore the backup onto the new machine.  This is done using the command:

  > mysql -u <admin-user> -p openvpms < openvpms.sql

where:

• <admin-user> is the name of the MySQL administrator user
• openvpms.sql is the dump from the old machine.

Dropping the database
 

If the database to restore a backup to already exists, it should be dropped first.

> mysql -u <admin-user> -p
> drop schema openvpms;
    Query OK, 46 rows affected (1.04 sec)
> quit