Auto Linking to Databases

Paul Campbell  23 February 2010 08:12:32 AM

This post describes the Auto Link feature, which allows the IDIOM Decision Server to automatically link to database tables. The focus of this post is linking to the Sql Server database, although Idiom  also supports Oracle and MySql (DB2 is currently under development).

Table Definition

The Table Definition now has some extra attributes added that allow mapping of table data to the external data source. A read only check box indicates whether a table is auto-linked or not, while the Column Descriptors will show the Mapping Names within the Column Descriptor Grid if the table is auto-linked. These are read-only fields.

Associated Table Contents

Note that once tables are auto-linked, users are no longer able to create new or to modify 'Table Contents'. Any existing table contents will be left in the repository, however, the only action that is available for the user is to delete these table contents.

Any attempt to create new, or modify table contents, either via double clicking on the associated table contents grid, or right clicking an entry on the grid, will be presented with a dialog or menu item to inform the user that they are not allowed to do so for an auto-linked table.

When a user initially turns auto-linking on and attempts to save the table definition, they will be presented with a dialogue that tells them that their associated table contents will no longer be used.

This message shows when the user saves from using the save button on the toolbar, using the Ctrl-S keyboard shortcut, or when using the ‘Save All’ feature.

Table Wizard

The existing table wizard has an option (check box) for users to indicate that they wish to automatically link the contexts of their tables directly to a database. If checked then the relevant information will be then stored in the release model.

Linking Existing Tables

Users are able to link their existing tables to make use of the auto-link feature so that they do not have to recreate their tables. The process of linking an existing table is done by using a wizard called "Table Data Source Wizard"

The following steps outline the process in which the Table Data Source Wizard will work.

1.        Tools  Table Data Source Wizard

2.        The user is presented with the welcome screen  Next

3.        Table Selection screen, user selects the appropriate table to auto-link then clicks Next.

4.        The user is presented with the same “Select Table Source” step from the “Import Table Wizard”.

5.        The same “Enter Credentials” step from the “Import Table Wizard”.

6.        The same “Select Database” step from the “Import Table Wizard”.

7.        A table listing is provided to the user to select the target table to use.

8.        Now the user maps database table columns across to Idiom table columns.  The user is not allowed to proceed to the next step until all columns in the Idiom table have a valid mapping.

Mapping a column is performed by dragging and dropping a column from the ‘Database Table’ List View onto the ‘Idiom Table’ List View.

9.        Confirmation step.

Table Auto-link Properties Dialogue

There is also a standard dialogue method for editing a table’s auto-link properties (manual entry), which can be accessed by clicking on a new “Edit Auto-link Properties” button on the Table form’s main tool bar. The user will be presented with a dialogue as follows:

Deployment / decisionServer.config

The Generated runtime code will contain user credentials provided during design time inside the Decision Manager, however, since these may well be different to those that are required on the production server, specific credentials can be provided via the decisionServer.config file itself. This is a further addition to the config file itself:

Each decisionModel element can have DbConnection elements, similar to what is stored in the Table element, one for each Table database connection setting that needs to be overridden. In addition to this, the root decisionServer element itself can contain DbConnection elements, to act as a default setting for any decisionModel elements that do not define their own DbConnection element. When the DbConnection element is used as a child of the decisionServer element, the TableIDREF attribute is redundant and will be ignored.

Connection precedence works as follows:

1.        decisionModel element DBConnection with TableIDREF.

2.        decisionServer element DBConnection

3.        decisionModel element DBConnection without TableIDREF.

4.        Embedded DBConnection settings from Table Import Wizard.

The presence of the DbConnection element in the configuration file will override any hard-coded setting within generated runtime code based on the TableIDRef.

Settings DbConnection via API method

Developers can also be provide their own database connection settings via the ‘DefineDatabaseSettings’ method in the DatabaseConnectionManager (which can be obtained from the LocalDecisionServer). This provides the same functionality as specified in the ‘Settings via modified decisionServer.config’ section above.

Auto Link Data type conversions.

IDM currently supports 5 different data types, (string, int, decimal, dateTime and Boolean).
The table below outlines what conversions that it currently handles between the different database engines.

• Comments [0]