Overview

Overview

In the following, ‘Configuration Manager’ will be abbreviated as ‘CM’. The CM allows to

  • select the target SICS schema (provided it is defined in the \runtime\conf\sics-database-sources.xml file)
  • select the entity / table [data provider] to be compared
  • view a short description of the selected entity / table
  • select to show all rows or just those with differences or just those selected
  • display the result of the comparison as pairs of source and target rows
  • display in a different color all dependencies (forward and reverse) between data providers
  • display in a different color all dependencies (forward only) between a data provider and a non-data provider like e.g. Base Companies
  • navigate to any data provider dependency by double-clicking a cell containing [a part of] the dependency key
  • return to source (navigate back) after navigating a dependency
  • select all or some of the displayed difference pairs (subject to dependencies)
  • recalculate dependencies when changing data provider after having (de)selected one or more differences
  • remember selections made, if entity is changed (this means it will be possible to accumulate selections for several entities)
  • generate insert/update/delete SQL statements for the selections made
  • clear all selections on the currently viewed entity
  • clear all selections on all entities
  • reload data from database for current/all entities; note that this will also clear all selections on current/all entities.

A number of colors are used to indicate keys (used for matching source/target rows into pairs) and differences.

A screen called “Configuration Manager SQL Viewer” is used to present the generated SQL - This form allows to,

  • Copy the SQL to the clipboard
  • Write the SQL to a file
  • Apply the SQL to the target schema (subject to security usecase authorization).

Data Providers #

Each entity / table will have a bespoke data provider.

The data provider basically covers one database table, with an associated textual description.

The data provider is responsible for reading source and target data, and providing these data in a uniform way to the visual presentation layer.

The data provider has knowledge about dependencies that one entity may have upon other entities. For instance, the REF_TO_REF entity will be aware that the FRK/FSK_PARENT and FRK/FSK_CHILD values require that there exists a corresponding row in the REFERENCE_DATA entity.

The data provider has knowledge about which entity fields (database table columns) constitute the ’extended identifier’, a key which uniquely identifies the entity in a business context.

As part of comparing database rows from the source and target schema, the rows will be paired/matched on the extended key. This will divide the data into four categories:

  1. Source/target row pairs with no differences
  2. Source/target row pairs with differences
  3. Source rows with no matching target row (i.e. target data is missing)
  4. Target rows with no matching source row (i.e. source row has been deleted).

With some limitations, items from the three last categories can be selected by the user, with the intention to generate SQL statements that will make the target schema reflect the data in the source schema.

When dependencies exist, it will be checked that the necessary referenced data is present (physically on the database, or through selection of differences on the referenced data entity).

Generating SQL for category 3 items means creating INSERT statements. However, the system will not allow selection of category 3 items with an unsatisfied dependency. For example, assume the source schema for the REF_TO_REF entity contains a row with FRK_PARENT='A', FSK_PARENT=1, FRK_CHILD='B', FSK_CHILD=2. This means that the source schema has a row in REFERENCE_DATA with CODE='A', SUBCLASS=1 and another row with CODE='B', SUBCLASS=2. Now, for the dependency to be satisfied, the target schema must also have two such rows in its REFERENCE_DATA table - or - the user must already have selected the relevant difference on the data entity for REFERENCE_DATA.

Generating SQL for category 4 means creating DELETE statements. However, the system will not allow selection of category 4 items which are referenced from other data providers (reverse dependency) unless those referring items are all selected for deletion as well.

NOTE: Item deletion should be done with care. Other parts of the SICS database, not covered by the configuration manager, may contain references to the items selected for deletion. Item deletion may lead to ‘dangling pointers’ (if the deleted row was actually referenced from some other table) and/or constraint errors.