Alter Scripts

A generated alter script contains the SQL DDL commands required to modify existing tables, columns, and stored procedures that bring into effect the changes that were made in the RPS mapping or mapped classes in the primary database.

The alter scripts generated by an RPS node for instantiation are created with the following format.

extract_mapping-OID/tableAlter_RPS-mapping-name_mapping-OID.sql

The mapping-OID value contains the object identifier of the RPS mapping entity and it is appended to the file name to ensure uniqueness.

The alter scripts are written to a subdirectory of the path specified in the Alter Script Path text box on the Configure RPS Node dialog in the RPS Manager application. (For details, see "Configuring your RPS Node", later in this chapter.)

Two types of alter script can be generated. The first is an auto initiate script that makes use of SQL ALTER TABLE commands to modify tables in the target. The second is a Drop/Reload script that renames any affected tables and creates new empty tables for reloading of the data.

Comments included at the top of the script describe what has changed and what actions the script will take, as shown in the following example.

-- The following change(s) to the table 'Agent'
-- new column 'webSite'
-- could cause an incompatibility between your Jade and
-- SQL databases.  Therefore this table will be renamed
-- and a new one created.  You will also need to extract
-- table data from the Jade database and load it into
-- this new table.  Table renamed rather than dropped,
-- it can be dropped manually when no longer required.

The following Jade schema changes are achieved using ALTER TABLE commands and result in an auto initiate script.

• Changes to the Jade schema, as follows.

  • Adding a property to a class mapped to a table and adding the new property to the table.

  • Changing a Jade primitive type from Byte to Integer or Integer64.

  • Changing a Jade primitive type from Integer to Integer64.

  • Deleting a property or column-mapping method used in a table if the table is in Overwrite mode.

  • Increasing the length of a String, StringUtf8, or Binary property used in a table.

• Changes to the RPS mapping, as follows.

  • For historical tables only, adding an existing property or method to a table if the DropHistoricalTableOnAddExisting parameter in the [JadeRps] section of the Jade initialization file is set to false.

    Any existing rows will have a column value of NULL.

  • Removing a property or column-mapping method used in a table from a table, if the table is in Overwrite mode.

  • Changing the mapping type of String, StringUtf8, or Binary columns.

The following Jade schema changes result in a Drop/Reload script using a renaming of the old table and a creation of the new table to achieve the table redefinition.

• Changes to the Jade schema, as follows.

  • Deleting a property or column-mapping method used in a table, if the table is in Historical mode.

  • Changing the Jade primitive type of a property or column mapping method that is used in a table, except for the cases documented earlier in this section.

• Changes to the RPS mapping, as follows.

  • Adding a property or column mapping method, except for historical tables with the DropHistoricalTableOnAddExisting parameter in the [JadeRps] section of the Jade initialization file set to false.

  • Adding an existing property or column-mapping method to a table.

  • Adding a filter constraint to or removing a filter constraint from a table.

  • Changing the mapping type of a column (except for length changes to a String, StringUtf8, or Binary property used in a table).

• Changes to the database type, as follows.

  • For DATETIME to DATETIME2 conversion, data is not reloaded on change. The millisecond values in the data at the time of the upgrade therefore may not be exact matches to the Jade data.

    If you require the data to match, you must manually extract and reload the required tables. For details, see "Extracting Data from the Jade Database" or "Extracting Data and Loading it into the Relational Database", later in this chapter.

  • ALTER COLUMN for VARCHAR(max) and VARBINARY(max).

  • ALTER COLUMN for date, time, or timestamp conversion.