Database schema updates

Schema changes to an operational database on a server are typically made and tested in a temporary copy of the database before application to the operational database itself. You can use the Update Database Schema command in iBase Designer to manage this process, making the changes and then applying them to the other databases by applying a new database template.

This process is only suitable for compatible databases. A compatible database is any database that is created from the same database template or any copy of a database. These databases are compatible because their entity types, link types, fields, and standard fields share underlying table names, column names, and identifiers. For example, you cannot make a database 'compatible' by adding an apparently identical entity type because the entity type might not have the same table ID as the other databases.

A source database becomes incompatible with the other databases if you turn on case control - any action that you take must be repeated in all the related databases. Adding, modifying, or deleting entity types, link types, fields or standard fields does not make it incompatible because these changes can be updated to the target databases by saving a template.

A target database becomes incompatible if there is a conflict between the identifiers in the source and target databases. For example, if you manually add an entity type to the target database that has the same identifier as a different entity type in the source database. It also becomes incompatible with the source database if you turn on case control when the source database is not case-controlled.

Updating the original schema

Elements of a database schema that can be updated:
  • Entity types, link types, fields, and standard fields
  • Datasheets
  • Pick lists, icon lists, and SCC lists
  • Common folder objects, such as import specifications, report definitions, queries, charting schemes and so on (but not labeling schemes).
You can add to and edit these items as required.
Attention: Removing entity types, link types, fields or standard fields from the schema of an operational database deletes the data held for those database objects.

Creating a template for a schema update

To create a template that captures the updates to a database schema, including any changes to the common folder objects, create a template from the database that contains the required updates.

You should always test the new template before you apply it to the operational database or any copy databases. To do this, create a copy of the operational database and apply the update template to it (using the steps in the following section). Only when you verify that the database was updated correctly, should you apply these steps to your operational database.
Note: You can also create new databases from this template if required. Any database created from the template contains both the ordinary folder objects and the common folder objects.

Updating the schema of a database from a template

After you create a suitable template, you can apply the new schema to the operational database and to any copies of it. Before you start, make sure that you have:
  • A backup of the databases
  • Permissions to create and delete files in the same folder as the main database .idb file
To apply the schema change:
  1. In iBase Designer, log on as a database administrator and open the database.
  2. From the Tools menu, select Database Design Update Database Schema. An empty Update Database Schema dialog is displayed.
    Note: You cannot display this dialog if you are a member of a Data Access Control group that denies access to any tables or fields in the database.
  3. Select the template that contains the schema changes.

    After you select a template, you can review the entity types, link types, and fields in the template by clicking .

  4. On the Additions and Modifications page, and the Deletions page, review the changes that are listed. For example, the Additions and Modifications page summarizes the changes made to:

    • Entity types and their fields
    • Link types and their fields
    • Standard fields
    • Datasheets
    • Pick lists, icon lists, and SCC lists
    • Common folder objects (listed separately for each type of folder object)
    • Semantic Type Library (but specific changes are not listed)
  5. If required, click write to save a list of the schema changes in a file that you can print later.
  6. Click Update when you are ready to apply the changes. When this is finished, you are warned if any folder objects were renamed because they have the same name as a common folder object in the template.