Using the DSB
Starting the DSB #
The DSB is part of the SICS System Administration Utility. The DSB is located in the ‘Database Setup’ Folder.
Initially, the DSB GUI looks like this:
There are two ‘alias’ entries in the ‘Data sources’ listbox. The first one represents the database one is logged in to. The second one represents the SICS application / runtime image.
Select either data source to populate the ‘Tables’ and ‘Views’ listbox. Population may take a minute or two.
Select any table or view name. The corresponding data will be filled into the ‘Columns’ tab of the notebook.
The ‘References’ tab shows all references to the selected table T. By ‘reference’ is meant a constraint on a column C in another table U, where the column C refers to the primary key of table T. In other words, C is a foreign key column with a constraint defined on it.
The ‘References’ tab shows a table with 3 columns:
| Description | |
|---|---|
| Table | The table that has an FK pointing to the selected table. |
| Columns | The FK Column(s) on that table that point to the selected table. |
| Comment | When there is only 1 column in the Columns cell, this will show the column comment for that column. For more than 1 column it is blank. |
The tab content is only visible when the selected table has a primary key.
Example:
Adding another Data Source #
In the ‘Data sources’ listbox, select’ Add …’ from the popup menu. This will allow you to make another connection to the database:
Database structure descriptions on file #
It is possible to save the complete description of one of your datasources for later comparisons. Writing the description to a file can be done by highlighting the datasource and click right mouse button. In the popup menu select “Write To Tab-Delimited file…”.
This file can later be loaded and compared against other datasources. To load a file as a datasource simply click right mouse button and select “Add From Tab-Delimited file…”.
Comparing two data sources #
Select any two data sources and select ‘Compare’ from the popup menu. Now three things will happen. First, all information from the database data source(s) will be read into memory and cached (if not previously done). This may take several minutes.
When all metadata are cached, the comparison takes place.
Finally, the comparison result is displayed
The tree-hierarchy can be expanded to reveal the details of the comparison:
Note that words like ‘ADDITIONAL’ and ‘MISSING’ means that in data source 1 this item has been added (or removed), compared to data source 2. In the example above, we see that the database structure (data source 1) is missing a constraint on the table ACCOUNTING_ORDER, and the table ACC_CLASSIFIC has an extra column and an extra index (in the database structure). Note: It is not absolutely necessary to use constraints on the database. If one has chosen not to use constraints, then the DSB will report a lot of differences.
One should note that indexes are compared based on their definition (column names & sequence), and not based on their index name.
On some RDBMSs it is possible to define the same index more than once, although with different index names. This is not a desired situation, but it could occur. This situation is treated by the DSB as follows. Assume source 1 is a database schema and source 2 is the SICS runtime image, and source 1 contains two identical indexes
F_1234567890 ON AC_LEDGER_DETAIL (FK_INS_PER)
IX_ABCDEF ON AC_LEDGER_DETAIL (FK_INS_PER)
whereas source 2 contains the index
F_1234567890 ON AC_LEDGER_DETAIL (FK_INS_PER)
then one of the duplicate indexes in source 1 will match the index in source 2, whereas the other duplicate index in source 1 will be reported as ADDED.
Constraints are compared by definition, too.
Tables, columns and views are compared based on their names.
Writing information to file #
The results of comparing two data sources can be written to a file. In the comparison results window, open the popup menu on the tree hierarchy and select ‘Write to file …’.
Correcting Discrepancies #
The DSB can be used to spot discrepancies between the structure of a database schema and the requirements of the SICS runtime image. In its current state, the DSB cannot correct such discrepancies. Any modification to the database structure must be done manually or by using the “Manage Tables” function which is also located in the ‘Database Setup’ Folder.