Installing OpenVPMS

Complete

This section describes how to install OpenVPMS.

The required software is documented in Requirements.

There are three installation procedures:

  1. New Installation - to install for the first time
  2. Upgrading an existing system
  3. You have an existing system, but want to upgrade to a new machine

If you are doing a new install, you should also read:

Other useful information can be found in the following:

Browser Compatibility

Complete

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

Complete

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

Complete

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 if necessary using sudo service tomcat7 restart).

The locale being used is shown on the Help screen.

Fonts

NOTE: the following discussion applies ONLY to reports and documents that use jrxml content. For Forms and Letters that use Open Office odt (and Microsoft Word .doc) based content, you can happily use any font installed on the system. However, if you are creating odt/doc content on a Windows system but your OpenVPMS server is running on a unix system, then the fonts you use may get remapped. For example 'Bradley Hand ITC' will be mapped to 'URW Chancery L'. If you need specific fonts installed on your unbuntu server, see here.

***UPDATE for 1.9.1***

In the 1.9.1 release all standard reports have been converted to use the DejaVu fonts. Because these are built into JasperSoft Reports, all will work with no problems. If you build or use custom reports you should ensure that these use the DejaVu fonts rather than the default San Serif. (Look at the Style|Base section with JaspersoftStudio in any standard report to see how this is done.)

***End 1.9.1 - keep reading for 1.9 and prior ***

The reports and documents in the release package have all been developed in a Windows system. The fonts are almost without exception set to Sans Serif.  In a Windows system this maps to the Arial font.

However on unix systems it normally maps to the DejaVu fonts. Although these look like Arial, they have slightly bigger font width metrics, and as a result sometimes the text does not fit in the allowed space.

That is, you get effects like the following where the words 'From Date' and 'Method' have been truncated - and also 'Report' is missing from the title.

instead of:

The fix is to replace the DejaVu fonts with the Liberation fonts. These are a set of Sans Serif, Serif and Monospaced fonts designed to have the same font metrics as the Microsoft Arial, Time Roman and Courier New fonts.

Since JasperReports uses the Java fonts we need to do two things:

  1. install the Liberation fonts
  2. change Java's fontconfig to map the Sans Serif, Serif and Monospaced fonts to the Liberation ones rather than the DejaVu ones

On a Ubuntu systems you can install the Liberation fonts using:
  sudo apt-get install fonts-liberation

To change the font mapping, you need to edit the appropriate fontconfig file. In the <JAVA_HOME>/jre/lib directory you will find the following fontconfig files:

fontconfig.RedHat.5.bfc
fontconfig.RedHat.5.properties.src
fontconfig.RedHat.6.bfc
fontconfig.RedHat.6.properties.src
fontconfig.SuSE.10.bfc
fontconfig.SuSE.10.properties.src
fontconfig.SuSE.11.bfc
fontconfig.SuSE.11.properties.src
fontconfig.Turbo.bfc
fontconfig.Turbo.properties.src
fontconfig.Ubuntu.properties
fontconfig.bfc
fontconfig.properties.src

For a Ubuntu system, the one to edit is fontconfig.Ubuntu.properties.

To make things easier, the modified file is attached to this post named fontconfig.Ubuntu.properties.txt

After you modify the file, you will need to restart Tomcat to have it take effect.

Locale and Fonts - Windows

Complete

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.

Fonts

***UPDATE for 1.9.1***

In the 1.9.1 release all standard reports have been converted to use the DejaVu fonts. Because these are built into JasperSoft Reports, all will work with no problems. If you build or use custom reports you should ensure that these use the DejaVu fonts rather than the default San Serif. (Look at the Style|Base section with JaspersoftStudio in any standard report to see how this is done.)

***End 1.9.1 update ***

The reports and documents in the release package have all been developed in a Windows system, and thus in general you do not have to worry about fonts. The Sans Serif font set in all the standard reports and documents will map to Arial.

NOTE: the following discussion applies ONLY to reports and documents that use jrxml content. For Forms and Letters that use Open Office odt (and Microsoft Word .doc) based content, you can happily use any font installed on the system.

However, if you do want to use another font (for example if you need to support Chinese characters when printing drug labels) then you need to understand that the fonts available to Jaspersoft Studio that you can choose from when creating or editing jrxml content are NOT necessarily available to OpenVPMS when it is generating the report/document. This is because when generating the jrxml based report/document, OpenVPMS only has access to a) the fonts mapped by <JAVA-HOME>\lib\fontconfig.properties; and b) those held in jar files in the <TOMCAT-HOME>\lib folder.

Hence if you need a non-standard font you will need to create a jar file containing it and place this in <TOMCAT-HOME>\lib.

To do this, first using Windows Explorer, copy the fonts you want to a new folder, say c:\temp\fonts - in the following I copied Comic Sans MS (which includes 4 files) and Arial Unicode MS Regular and you should have:  

Now start JasperSoft Studio and use Window|Preferences|Jaspersoft Studio|Fonts to get:

Click the 'Add From Path' button and specify the folder in which you saved the fonts, then click Finish to get:

Now click on both the fonts to select them and then press the Export button. Specify the file name you want (say MyOpenVPMSfonts) and press Save. You can then Cancel the above window to dismiss it.

In your c:\temp\fonts folder there will now be MyOpenVPMSfonts.jar which you can copy to <TOMCAT-HOME>\lib  (eg C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib). After you restart Tomcat, your new fonts will be used when you generate reports and documents that use them.

New Installation

Complete

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:

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:

Name Contents
archetypes archetype definitions
bin tools used to configure and load data into OpenVPMS
conf configuration files for the tools in ../bin
import data to import into OpenVPMS
legacy-migration scripts used to migrate from releases prior to OpenVPMS 1.9.
lib jars used by the tools in ../bin
reports document templates for reporting
plugins plugin support
webapps  the OpenVPMS web applications

 

 

 

 

 

 

 

 

NOTE: the OpenVPMS installation directory should be secured to prevent unauthorised user access.

MySQL connector

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

The JDBC driver in the archive is named:
    mysql-connector-java-5.1.<x>-bin.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 7.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: apasswd
  Reporting database URL [jdbc:mysql://localhost:3306/openvpms?useSSL=false]:
  Reporting database user [openvpms]:
  Reporting database password [apasswd]:

  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
  • creates the user corresponding to the Database user and password specified
  • 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 mysql utility to grant access using the command:
GRANT ALL PRIVILEGES ON <database-name>.* TO '<user-name>'@'%' IDENTIFIED BY '<user-password>' WITH GRANT OPTION;

Replace <database-name>, <user-name> and <user-password> with the corresponding values entered during toolbox configure.

To improve security, the '%' should be replaced with the host that Tomcat will connect from.

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

OpenOffice installation

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

  1. Add it to the PATH of the user that runs Apache Tomcat.
    The installation path differs by platform and OpenOffice version. For OpenOffice 4 on Windows, the default installation path is:
     

           C:\Program Files (x86)\OpenOffice 4\program

    Windows users can find instructions for changing the PATH here.      

  2. Verify it can be run as that user. From a command prompt, enter:
          soffice
    This should start OpenOffice.

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.

 

Optional steps

OpenVPMS ships with optional data that can be loaded as required. This includes:

  • VeNom codes
  • Australian states and suburbs

Note that having loaded these (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:

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

 

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

Complete

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.

*NOTE: OpenVPMS requires MySQL 5.5, which is included in Ubuntu 14.04. Later releases include MySQL 5.6 or MySQL 5.7 which are only supported via configuration changes.

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.

Server Software

Java

OpenVPMS may be run using Java 8 or Java 11. Java 11 is preferred, as Java 8 is reaching end-of-life.

There are multiple providers:
 

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

Both Tomcat 8.x or 9.x are supported.

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

MySQL

MySQL 5.5 is supported natively.
MySQL 5.7 may be used with configuration changes.

MySQL 5.5

See http://dev.mysql.com/downloads/mysql/5.5.html#downloads

Download the MySQL Community Server 'Generally Available (GA) Release'.
 

MySQL 5.7

See https://dev.mysql.com/downloads/mysql/5.7.html#downloads
Download the MySQL Community Server 'Generally Available (GA) Release'.

The sql_mode must be changed to remove ONLY_FULL_GROUP_BY. This is done by configuring the server options file, or via MySQL Workbench.

On Windows, the MySQL installer may require that the Microsoft .Net Framework 4 be installed. This is available from:
http://www.microsoft.com/en-au/download/details.aspx?id=17851
 

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

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

See http://dev.mysql.com/downloads/connector/j/5.1.html

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:

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 then the following software is required on all workstations that will edit documents in OpenVPMS:

See http://www.openoffice.org/download/

Optional Software

Security

Complete

This page addresses various security related matters.

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.5/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.

 

Subscription

Complete

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

Complete

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

The headings below are:

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 (eg 1.8.1, 1.9.1 etc). If you are upgrading to a sub-release then you should consult the release notes on the download page at http://www.openvpms.org/download

Requirements

The software required to run OpenVPMS may change between releases.

Check Requirements to ensure you have the necessary software.

Preparation

Backup

Back up your database prior to performing the upgrade.

This can be done using the mysqldump tool. e.g.:

> mysqldump -u openvpms -p openvpms --result-file openvpms.sql

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.

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.

The current database size can be determined using the following SQL query:

SELECT table_schema 'database',
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) 'size (MB)'
FROM information_schema.tables
WHERE table_schema = 'openvpms';

This displays the size in megabytes. To display it in gigabytes, use:

SELECT table_schema 'database',
       ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) 'size (GB)'
FROM information_schema.tables
WHERE table_schema = 'openvpms';

(Replace 'openvpms' with the actual database name).

Time

Allow time for the migration to take place. On large databases, migration may take several hours.

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

Migration time may be reduced by making innodb_buffer_pool_size as large as possible for the duration of the migration.

 

Directories

These instructions assume that:

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

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

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

 

MySQL connector

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

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

 

This file needs to be preserved between releases in order to decrypt passwords.

When upgrading, copy the openvpms.properties from the prior installation to the new installation i.e. from <OPENVPMS_PREV>/conf to <OPENVPMS_HOME>/conf.

 

Database migration

Migrating 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. Run the following in a shell prompt:

> cd <OPENVPMS_HOME>/bin
> toolbox database --update

Migrating from 1.8 or earlier

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

This migration must be performed using MySQL 5.1 or 5.5.

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.

Replication

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

Load archetypes

Load the latest archetypes using:

> cd <OPENVPMS_HOME>/bin
> toolbox archetype --load

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 archload 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.

HL7

Sites using HL7 in OpenVPMS 1.8 need to manually edit their HL7 Connectors to include a mapping. A mapping for Cubex is provided.

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.

Obsolete Document Templates

Complete

Some document templates have been either renamed, had their content name changed, or are no longer used.

Changes from 1.9 to 2.0

If you have upgraded from 1.9 and you have used the templateload utility to load the 2.0 template set, then no action is required as no templates became obsolete.

Changes from 1.8 to 1.9

If you have upgraded from 1.8 and you have used the templateload utility to load the 1.9 template set, then the following templates can be deleted as they are no longer used:

Name Content
Counter Sale Items Counter Sale Items.jrxml
Credit Items Credit Items.jrxml
Debtors - Current - SubReport2 Debtors - Current - SubReport2.jrxml
Supplier Credit Items Supplier Credit Items.jrxml
Pharmacy Return Items Pharmacy Return Items.jrxml
Refund Items Refund Items.jrxml

Changes from 1.7 to 1.8

If you have upgraded from 1.7 and you used the templateload utility to load the 1.8 or 1.9 template set, then, unless you have manually deleted them, you will have obsolete 1.7 document templates in your system. The table below lists those templates that can be deleted.

NOTE:

  1. If you have customised versions of some templates and when customising them you did NOT change the content name (eg your Invoice template still uses content named "invoice A4.jrxml") then obviously you should not delete the template.
  2. The list below includes both the A5 and A4 template sets, and if you loaded the A4 set, then you will not have the A5 versions of some templates (eg, Counter Sale, Credit, Invoice, etc), and vice versa if you loaded the A5 set.


Name Content
Appointment Appointment A5.jrxml
Bank Deposit Bank Deposit A4.jrxml
Counter Sale countersale a4.jrxml
Counter Sale countersale a5.jrxml
Counter Sale Items countersaleItems.jrxml
Counter Sale Items countersaleItemsA5.jrxml
Credit credit a4.jrxml
Credit credit a5.jrxml
Credit Adjustment Credit Adjustment A5.jrxml
Credit Items creditItems.jrxml
Credit Items creditItemsA5.jrxml
Customer Account Balance Report customerAccountBalance.jrxml
Debit Adjustment Debit Adjustment A5.jrxml
Desexing Certificate desexing certificate a4.odt
Desexing Certificate desexing certificate a5.odt
Drug Label label Dymo.jrxml
Estimate Estimate A4.jrxml
Estimate Estimate A5.jrxml
Estimate Items Estimate Items A4.jrxml
Estimate Items Estimate Items A5.jrxml
Grouped Reminders Report grouped reminders a4.jrxml
Invoice invoice a4.jrxml
Invoice invoice a5.jrxml
Invoice Items invoiceitems.jrxml
Invoice Items invoiceitemsa5.jrxml
Invoice Reminders invoiceRemindersA4.jrxml
Invoice Reminders invoiceRemindersA5.jrxml
Message message a4.jrxml
Order Items orderItems.jrxml
Patient Clinical Event patientClinicalEvent a4.jrxml
Patient Clinical Event patientClinicalEvent a5.jrxml
Patient Image patientDocumentImage.jrxml
Patient Reminders Report patientReminders.jrxml
Receipt receipt a4.jrxml
Receipt receipt a5.jrxml
Receipt Items receiptItems.jrxml
Receipt Items receiptItemsA5.jrxml
Refund Refund A5.jrxml
Refund Items Refund Items A5.jrxml
Reminder Cartrophen First cartrophen first reminder.odt
Reminder Desexing First desex first reminder.odt
Reminder Vaccination First vaccination first reminder.odt
Reminder Vaccination Puppy and Kitten First vaccination puppy kitten first reminder.odt
Reminder Vaccination Second vaccination second reminder.odt
Statement statement A4.jrxml
Statement statement A5.jrxml
Statement Items statementItems A5.jrxml
Statement Items statementItems.jrxml
Stock Adjustment Stock Adjustment A4.jrxml
Stock Adjustment Items Stock Adjustment Items A4.jrxml
Stock Take List Report stock take list.jrxml
Stock Transfer Stock Transfer A4.jrxml
Stock Transfer Items Stock Transfer Items A4.jrxml
Supplier Credit Supplier Credit A5.jrxml
Supplier Credit Items Supplier Credit Items A5.jrxml
Supplier Invoice Supplier Invoice A5.jrxml
Supplier Invoice Items Supplier Invoice Items A5.jrxml
Supplier Refund Supplier Refund A5.jrxml
Supplier Refund Items Supplier Refund Items A5.jrxml
Supplier Remittance Supplier Remittance A5.jrxml
Supplier Remittance Items Supplier Remittance Items A5.jrxml
Task Task A5.jrxml
Till Balance TillBalance.jrxml
Vaccination Certificate vaccination certificate a4.odt
Vaccination Certificate vaccination certificate a5.odt
Work In Progress Report workInProgress.jrxml

 

Upgrade to a new machine

Complete

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 Upgrade|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. Perform the Upgrade Installation step: Migrate the database
  7. Perform the New Installation step: OpenOffice installation
  8. Perform the remaining Upgrade Installation steps, starting at Load Archetypes

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.

If the OpenVPMS database already exists on the new server, this step will fail with the error message:

Cannot create openvpms as it already exists

The database should dropped and the command run again. Failure to do so may result in the database containing different versions of OpenVPMS tables. This can affect upgrades, and cause data corruption.

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
    This user only has privileges to connect from localhost.

 

If the MySQL database is a different host to that running Tomcat, use the mysql utility to grant access using the command:

GRANT ALL PRIVILEGES ON <database-name>.* TO '<user-name>'@'%' IDENTIFIED BY '<user-password>' WITH GRANT OPTION;

Replace <database-name>, <user-name> and <user-password> with the corresponding values entered during toolbox configure.

To improve security, the '%' should be replaced with the host that Tomcat will connect from.

 

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.

Only perform this step if the Creating an empty OpenVPMS database step failed.
 

Dropping the database destroys any data present, so ensure there is a backup before proceeding.

 


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