Updating the database design

After replication is configured, iBase provides tools to update the database design at all sites. Nevertheless, it can take time to make these changes, mainly because of the cooperation that is required between sites. You must also require the cooperation of the SQL Server administrators at each site as they must delete and then re-create the publication and subscriptions.

Items that must exist before the publisher site is configured for replication

The database schema must be set up correctly before replication is configured for the publisher site. For database replication, the items in the database schema are:

• Entity types
• Link types
• Fields
• Standard fields
• Pick lists, icon lists, and SCC lists (but not their contents, which can be changed by any authorized user at any time)
• Chart attributes
• Datasheets
• Common folder objects (folder objects that are marked as common folder objects in iBase but not dependent on any data)
• Semantic types and the way they are assigned

You might want to make certain changes to the database schema to support database replication. Specifically, adding a Merge Request entity to help manage the merging of records, and adding owner fields to reduce the risk that conflicts might occur.

The following objects are not replicated, and need to be added to the database template before you set up new databases at the subscriber sites:

• Labeling schemes

Public and private folder object control groups are not replicated but the objects they contain are copied for all users. (Folder object control groups are defined in the security file.)

Note: You might want to delete unwanted objects at this stage otherwise they are copied to the databases at the subscriber sites.

Items that can be added at any time

The items in pick lists, icon lists, and SCC lists can be updated at any time as the contents of these lists are replicated.

The following non-database files can also be replicated (by loading them in the File Manager) and therefore do not need to be considered when you prepare the database for replication. The exact list of files depend on how you use iBase:

• Command groups (CommandGroups.mdb)
• Word documents that are used for styles or as templates in reporting
• Lists of excluded words for search indexes (WSexclude.txt)
• Icon lists (Iconlist.txt) and, if customized, the associated graphic files (.ico)
• i2 Text Chart templates
• i2 Analyst's Notebook templates
• Mapping configuration files

If there are many files, for example Word documents, then consider archiving them in a .zip file before you load them in the File Manager.

Note: After replication, you will need to copy these files to the correct place in the database folder or to the correct folder in the iBase installation.

Items that are site-specific

The Word Search and Full-Text Search indexes must be built individually at each subscriber site.

Possible schema changes for replication

When you review the current database schema, you might want to consider making the following two changes to support iBase database replication.

Adding a Merge Request entity type

The Merge Request entity type is intended to support the procedure for merging entities. A procedure is required because the iBase Conflict Viewer does not handle conflicts that arise from merging entities or occurring in the records that are affected by a merge operation.

When you design a suitable entity type for Merge Requests, you might want to add fields for:

• The name and location of the analyst
• The reason for the merge
• Details of the records for the proposed merge, for example by listing the record IDs.
• A screen capture of the pre-merge data
• Room for an analyst from each site to enter comments
• Voting buttons, one for each site (you might use a checkbox or a list with Yes/No/Discuss options)

For more information, see Merging Entities.  

Adding owner hyperlink fields to entity and link types

To reduce the likelihood that conflicts occur, you can define a hyperlink field that automatically inserts the name of the current user whenever a new record is saved. Users can see this information when you show or edit the record. For more information, see Checking the Ownership of Records.

To define this type of field, use iBase Designer to create new fields or a new standard field with:

• The hyperlink field type
• A name such as Owner
• A default value of $ (the $ symbol automatically inserts the name of the current user when the record is saved)

You must also update your datasheets to show the owner field.

Process for changing the design of a live database

To make changes to the design of a live database that is not yet configured for replication in SQL Server, work on an isolated copy of the iBase database.

After you finalize the design, you can apply the changes to the live database using the Update Database Schema dialog (you can use this dialog even though replication is not yet configured) or you can make them by hand.

The steps are:

1. Create a template from your live database.
2. Create a temporary database from the template, in either Microsoft Access or SQL Server format.
3. Make the required changes to the design of your temporary database, considering all the issues described in this topic.
4. Test the new design in iBase.
5. Create a template to capture the new design. This template is needed by the subscriber sites.

You can then repeat these changes in the live database or use the Update Database Schema dialog to apply some of the changes for you. Changes to folder objects and datasheets must always be made manually.