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 CSV file (places 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 the 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