When you log onto the Impact database, you do so via a Database Connection. The connection stores information such as the location of the database on your network (specifically an ADO connection string or ODBC source through which the database is accessed).

To log on, you need a user name and password which will be supplied by your Impact system administrator.

The term database means different things to different people. An "Impact database" does include a true database, but it also might include drawings files (projects, symbols and templates), user files (working drawings plus settings), and configuration files. The real database part is a relational database (comprising tables, fields, indexes and so on) using a client-server database engine (Microsoft SQL Server, Oracle, MySQL). Optionally, the database can be "blobbed", which means that all the files (drawings, settings, configuration) are converted to database "BLOBS" (database jargon: binary large objects) and stored within the true database. In this case, the "Impact database" and the "true database" are synonymous.

Related topics: Database Administration.

Introduction

This section describes the implementation details for One to Many and Many to Many database support in Impact. It includes the following features:-

• Ability to define table relationships between both internal and custom tables.
• Configure a DBW to open one or more child DBWs for editing related records.
• Configure a DBW to include one or more embedded Child ListViews.
• Configure a DBW ListView, for displaying multiple records from a table as a list.
• Storing related child data for custom tables.
• Storing related child data for Users and Sites.
• Storing embedded related child data during working projects lifetime.
• Updating related child data at update.
• Deleting related child data when deleting items.

Configure Tables

The most important consideration is to determine what additional data you want to store with each Impact item, whether it be a Project, Layer (or ExtraLayer type), Template, Symbol, Customer, Contact, User or Site. You can also store additional data for a new or existing custom table.

Simply define a new custom table to store the related data and then attach that data to the parent table via a foreign key.

Once you have added the tables it is important to define the table relationships so Impact knows about the relationships. See Configure Relationships below.

One-to-Many

Create a custom table that will hold the data that you want to store for the parent table. This custom table will hold zero or more records that are related back to a single parent record.

For example, to create a table to store additional related data for each DRAWINGS record you might define a table as below.

The DRAWINFO custom table will store zero or more records for each DRAWINGS record.

No Database Menu properties are specified as this table shouldn’t appear in the Impact Database menu.

It has DI_DATE and DI_TIME columns specified for automatically storing record modification times.

When defining the table fields it is important to include a foreign key link back to the parent table. In this case DI_DRAWKEY will be used to relate each DRAWINFO record back to a specific DRAWING record. It is recommended that all foreign keys are indexed.

The remaining fields in the DRAWINFO table are used to store the specific data that you are interested in. The data that you store will be specific to the customisation/automation you are implementing.

You can specify columns to be required in which case Impact will enforce this for each table record that is added.

Many-to-Many

The above example contained a single foreign key which will be used to link the DRAWINFO table back to a single DRAWINGS record. This creates a one-to-many relationship; one DRAWINGS record to many DRAWINFO records.

To create many-to-many relationships you need to define additional foreign key relationships from the child table to other tables in the Impact database.

The example LAYINFO table below contains multiple foreign key columns.

The LI_LEYKEY column relates each LAYINFO record back to a single LAYERS record.

The LI_COLKEY column is a foreign key to a custom COLOURS tables primary key COL_KEY.

The LI_USERKEY column is a foreign key to the USERS table primary key U_KEY.

Due to these extra foreign key columns and table relationships they are collectively known as a many-to-many relationship

Many LAYERS records are related to many COLOURS records via the LAYINFO link table.

In Impact the smallest many-to-many link table contains three columns; the primary key column and two foreign key columns linking two tables.

CUSTOMER Contacts and Addresses

There are two internal tables that can also be used with the new ListView controls available in DBWs.

The CONTACTS table has a foreign key to link an address to a CUSTOMER, specifically CC_CUSTKEY.

The ADDRESS table has four foreign keys defined that can link an address to one of four parent tables, specifically A_USERKEY, A_CUSTKEY, A_CONTKEY and A_SITEKEY.

Modification Dates/Times

You can configure a date and time column to store modification times for a custom table.

Impact will then synchronise the modification date and time columns when each related child record is added or updated. For data related to projects (DRAWINGS, LAYERS, ONE_UP, MULTI_UP etc) the modification date and time is synchronised with the actual project update time.

These columns are also made read-only in any DBW.

Database Menu Items

Previously, all database tables that had a mapping in either the Database Installation->DBW Mappings or Database Operation->DBW Mappings had an associated menu item in the Impact Database menu.

Previously, if the table did not have a configured Menu Text in the Database Menu group of the Table Structure properties then a menu item caption was automatically generated from the Table Name.

As DBW Mappings can now be specified for child tables that may not require a menu item in the Impact Database menu this behaviour has changed.

DBW Mappings will ONLY be added to the Impact Database menu when they have Menu Text specified.

Existing databases are automatically updated so that the Menu Text is generated from the table name if it is missing. So no configuration changes to existing databases should be required.

New tables that are added will need to have a Menu Text configured if they need to appear on the Impact Database menu.

Table Prompts

A new Prompt property has been added for each table in the Table Structure form.

This is currently used to display friendly table names when prompting to delete tables. It cannot rely on the Menu Text as this may not be configured (see above).

Existing databases are automatically updated and the Prompt is copied from the Menu Text.

Configure Relationships

Once you have defined the tables to hold the extra data you must define the table relationships so that Impact knows about them.

This is done via Database Installation->Table Relationships (this used to be on the Database Installation->Advanced Queries options page).

Define each of the table relationships using the foreign keys you created while defining the tables. So for the DRAWINFO example above define the table relationship from DRAWINFO.DI_DRAWKEY to DRAWINGS.D_KEY as below.

For the LAYINFO example above you would define three table relationships:-

1. LAYINFO.LI_LAYKEY to LAYERS.L_KEY
2. LAYINFO.LI_COLKEY to COLOURS.COL_KEY
3. LAYINFO.LI_USERKEY to USERS.U_KEY

Relationships can be defined between both integer and character columns, although typically integer relationships using the primary keys should be used.

Structure Visualisation

Once you have defined your tables and table relationships you can visualise how your tables are related using an external application called Graphviz.

You are recommended to use the command line version rather than link in a specific library version as it facilitates easy updates.

Graphviz is available from http://www.graphviz.org/. Once you have installed it, simply click Visualise and Impact will prompt you to configure the location. The configuration is stored on the workstation in the registry rather than user settings as it is specific to a workstation installation.

Impact simply generates an .xml file containing the Graphviz ‘dot’ notation and calls the command line dot.exe to generate a .png file, which is then displayed using the associated Windows application.

There are a few options you can configure to control which tables and columns get included in the xml output. You can access these options after initial configuration by using the right arrow of the Visualise button.

Visualise Example

The example below was produced for the DRAWINFO and LAYINFO example tables and table relationships discussed in the previous sections.

The light brown coloured tables are internal Impact tables; the light blue tables are custom Impact tables.

Internal Impact table relationships are shown in black, while user-defined table relationships are shown in blue.

A diamond headed line represents a one-to-many relationship, with the diamond end showing the table that may contain many records to a single parent key. A filled diamond represents a non-null foreign key, while an open diamond represents a foreign key that may be null.

There is also a filled circle double headed line representing a one-to-one relationship such as LAYERS.L_EXTKEY to ONE_UP.OU_KEY. One-to-one table relationships cannot be user-defined.

Direct and Embedded DBWs

Once you have defined the tables and table relationships you then need to configure the appropriate DBWs for entering those values.

There are two types of DBW and each supports related data in a slightly different way.

Direct DBWs are used to enter data into custom tables and are available through the Impact Database menu.

Embedded DBWs are used to enter and display data related to other Impact objects such as Projects, Templates, Symbols, Layers, Users and Sites. Embedded DBWs do not store data immediately into their destination tables, instead they store the information internally and use it to add/update/delete rows as appropriate.

Embedded DBWs are also used within the Impact Browser to view project and layer database information. Embedded DBWs used within the Impact Browser are read-only.

Configure DBWs

The Field Attributes form for configuring DBW fields has been improved using a page control for the main properties of each control type.

The Control Type has been moved to above the page control to indicate it should be selected first.

Two new control types have been added: - Table ListView and Child Table ListView.

The Test feature when editing DBWs supports these new ListView controls and even allows you to add/edit/delete rows from the ListView controls. No data is updated when using Test mode.

DBW Menu/Button Configuration

The Delete All command has been moved from the Action menu to a button. This is hidden if not configured in the DBW.

Previously, only additional Action menu items could be configured in a DBW. Now you can also configure additional automation buttons.

A new Open Child DBW action allows a child DBW to be opened for the currently displayed parent record.

Select an appropriate relationship for a child table -> parent table.

Either select a specific DBW or select <None> to use the default DBW mapping for the child table.

Specify an optional DBW Title that will substitute values from the current parent record.

You can now specify the action automation item to be either a menu or button.

CUSTOMER DBW Update

Existing CUSTOMER DBW settings are automatically updated to include a Contacts and Addresses button on the DBW. This allows further customisation as required.

USERS DBW Update

In earlier versions of Impact, for customers using LDAP authentication for user access the User Properties form included an Address page  when a DBW mapping existed for the ADDRESS table. This allowed a single ADDRESS record to be edited for the user.

Now, as each user can have multiple ADDRESS records, you need to configure a Child Table ListView within the USERS DBW to display addresses.

This change is NOT performed automatically and any customers using LDAP will need to have their USERS DBW reconfigured as appropriate.

Address records created for each user from the LDAP directory will have an A_DESCRPT value of ‘LDAP’. When editing the ‘LDAP’ ADDRESS record within a DBW any fields that have an LDAP mapping will be disabled.

Databases are updated automatically to set the A_DESCRPT column for existing LDAP user addresses.

Impact Browser

The Impact Browser supports DBWs both embedded within the Properties pages and via ‘Database’ command on the context menu.

Any DBWs configured containing Child Table ListView controls will display related data read-only.

Saving Projects

When a project is first saved, any related data that has been added via embedded DBWs is automatically added to the database.

When using the Integrated DBW or individual modal DBWs containing Child Table ListView controls the validity of the table rows is checked before the modal dialogues are closed.

In addition, before a project is saved, Impact checks to ensure that all related data rows have any required non-null column values supplied. This is to ensure any rows entered via the Database floating toolbox are valid as these are not checked while using direct editing within a Child Table ListView on the toolbox.

Working Projects

During the lifetime of a working project any related data entered into embedded DBWs is stored within the working project itself.

Impact keeps track of which related data rows are added, changed or deleted during the lifetime of the working project.

No related data is written to the database until the project is updated. If the project or individual layers are discarded then no related data is written to the database.

Project Revisions

Each project revisions that is created containing geometry is also saved with all the current related data records from the database.

Selecting individual revisions within the Impact Browser and viewing their associated database information will show any related data from when the revision was created.

Reverting a project to a previous revision will replace all the current related data with the related data stored within the project revision being restored.

Cascading Deletes

When deleting items from within Impact any related child data is also automatically deleted.

This applies to:-

• Projects, including all layers and extra layer records
• Templates and Symbols
• Customers and Contacts
• Users
• Sites
• Custom table records