Database Upgrades Required for Petro-SIM 6

Petro-SIM version 6 imposes a few changes to its database structure that are fully backwards-compatible and easy to apply. This note outlines the changes and how the changes can be applied.

Structure Changes

The changes specific to version 6 affect the PetroSIM_Views tables used to hold report data views (these should not be confused with conventional SQL views):

  • Alters the PetroSIM_Views table to insert a new Parent column to allow view hierarchies
  • Creates tables for holding view attributes

The changes also create a View of the PetroSIM_ObjectData table joined against tables holding unit of measure information. The resulting PetroSIM_ObjectDataDisplay view is used by the new reporting functionality in version 6 to support reporting in alternative unit sets, making it easy for example to switch temperature reporting from C to F.

Please note that the changes recommended for your own database may be more or less extensive than these depending on your current schema revision.

Applying the changes

Petro-SIM inspects database structure and the need if any for updates every time you connect. It produces a dialog like

if it detects change is needed. Changes can then be applied in two ways:

  1. You can have Petro-SIM apply the changes for you so long as you have sufficient privilege to alter tables and create new tables and views. For SQL Server this generally means you have dbo or db_owner privilege. Click the Upgrade to use this method.
  2. You can have Petro-SIM generate SQL statements that you can then execute through a SQL management tool. Check the Show Upgrade SQL Commands to view the SQL. You can save this to file by right-clicking inside the text window.

If you are using the Petro-SIM database in a managed multi-user environment where database schema changes are locked down and notwithstanding that we believe the changes to be safe and to have no impact on production data, we strongly recommend that you work with your organization's database administrator team using the second method above. We also recommend that you consider first testing the changes against a test mirror of your production database.