Global Database Updater

The purpose of this feature is to be able to update/patch every OpenPetra database around the world using files hosted on Caleb. In the future this feature will be run automatically but for now it is manually implemented using a button in System Manager/Database.

The Hosted Directory

This is a folder that contains everything needed for the database update. This folder will be hosted on Caleb (not yet implemented, currently using a local folder for testing). The Database Updater will read this directory for SQL files. Every SQL file will then be read by OpenPetra and each query will be run against the database.

The SQL Files

SQL files will be made up of one or more queries (separated by semicolons). It is important that each query is preceded by a comment. The comment must begin with two hyphens (--). This comment will be used by OpenPetra to produce a report. If a query fails this comment will be used to show where the failure was.

A different database transaction is used for each file. If one query fails then all changes made from that file will be rolled back. Other SQL files will not be affected.

A CSV file (placed in the same directory) can be linked to a SQL file. This is done by writing the CSV file name in a comment. CSV column names can then be used as parameter names. E.g.

-- Partners.csv
-- Modify Partner names.
UPDATE p_partner SET p_partner_short_name_c = @PartnerName
WHERE p_partner.p_partner_key_n = @PartnerKey
AND p_partner.p_partner_short_name_c <> @PartnerName

The CSV Files

The following are requirements for the CSV file:

• The CSV file must begin with a header containing the field names that will be used for the parameters in the SQL file.
• Each line must contain the same number of comma separated field.
• All string fields must be enclosed with double quotes. Numbers should not be enclosed with quotes.
• A double quote character in a field must be represented by two double quote characters.

E.g.

PartnerKey, PartnerName, PartnerAddress
0123456789, "Joe", """The House,"" 1 Street, Town"

The Report

A report of the update will be written to a date stamped text file in /log/DatabaseUpdate. This will report how many changes were made to the database by each query and whether or not the transaction was successful. More detailed information on why a query failed will be included in the Server log file.

TODO - once this feature is set up to run automatically, if a query fails then a designated user should be emailed to alert them to the failure.