Operations with Schema Mapping

Creating New Mapping

Before creation of mapping ECSchema should be opened in advance. To create new mapping perform below operations.

Select "Database Mapping > Add New..." menu item or click right mouse button on schema in Class Editor navigation tree and select "Add New Mapping..." item in context menu. After that New Mapping dialog is displayed

Specify the schema for which you plan to create the mapping. By default the recently selected schema is specified.
Next specify the application which the mapping belongs to. You can select one from list or create a new application by pressing "+" button

Define a mapping version (minor and major part). For the same application there can be several mappings with different versions.
Optionally it is possible to not create a mapping from scratch but it is advised to use an existing mapping as template instead. For this you can import the data of an existing mapping by checking the "Import Mapping" checkbox and selecting an existing mapping from the drop down menu.

Opening Mapping

To open an existing mapping for ECSchema do the following steps:

Select "Database Mapping > Open..." menu item or right click on a schema in the ClassEditor navigation tree and chose "Open Existing Mapping..." item in context menu. The Open Mapping dialog box is displayed

Specify the schema you want open mapping for. By default the last selected schema is specified.
Specify the application.
Select the mapping you want to open in the list and click ok

→ It is also possible to remove a mapping by selecting it in the list and clicking the "Remove" button

Note: A schema doesn't need to be opened in advance. The appropriate schema will be opened automatically while opening the mapping.

Saving a Mapping

To save a mapping select "Database Mapping > Save..." menu item or right click on the schema in tree and choose "Save Mapping" from the context menu. If a mapping file already exists it will be overridden, if not an new mapping file will be created

Closing a Mapping

To close an opened mapping select "Database Mapping > Close" or right click on the schema in the ClassEditor navigation tree and chose "Close Mapping" from the context menu.

Support of referenced schemas

In practice sometimes an APPlugin may work with two or more schemas. One schema may have base classes shared among several applications and the second can reference the first schema and contain derived classes with additional properties. The mapping file can contain mappings for all working schemas. The schema that references others will be considered as master. To allow the mapping of classes of any referenced schema it is sufficient to open it in the ClassEditor. After that it is possible to perform all operations with its classes like with classes of master schema.

Mapping storage

To locate mappings the Plant Poject Database Mapping Extension uses the ECSCHEMA_PATH environment variable. The variable contains a list of folders where ECSchemas are located. The mapping plug-in searches these folders for existing mapping files. All mappings for a schema are located in a subfolder of the schema folder. The subfolder has the following name pattern:

<FullSchemaName>_mapping

Example: PlantProjectSchema.08.11_mapping

The folder contains all mappings of all application for that schema. The name pattern for a mapping file is the following:

<FullSchemaName>_<AppName>.<MappingMajorVersion>.<MappingMinorVersion>.mapping.xml

Example: PlantProjectSchema.08.11_Autoplant_PIW.01.00.mapping.xml

Plant Project Database Mapping Add-in panel

After a mapping is opened or created the Plant Project Database Mapping Extension panel is displayed on the right of the ClassEditor. It consists of two tabs (see Fig.6). The first tab is "Summary". It contains overall information about the mapping and provides some statistics. The second tab is "Class Mapping" or "Relationship Mapping". The name depends on what has previously been selected: class or relationship. It provides overall information about the currently selected class mapping and property mapping.

Summary tab

The Summary tab contains overall information about the whole schema mapping and has the following sections:

Mapping Info. This is the information about the mapped schema, the mapping version, the application and some statistical information.
Classes. List of mapped classes per schema. The class names are hyperlinks. If you click a class name the corresponding class in the class tree is selected. The list contains two sections: Active and Inactive. Active contains mapped classes that are supported by Plant Project Database ECPlugin (they will be at least readable). Classes in the Inactive section are skipped.
Relationship. List of mapped relationships. It has the same structure as the class tab.
Unknown Classes. A list of classes that don't exist in the schema but exist in the mapping. This can be caused by classes that were removed while the mapping file was closed. Click on the "remove" hyperlink to remove those classes.
Unknown Relationships. List of relationships that don't exist in the mapping but exist in the schema.

Export Summary: When clicking the Export Summary button a summary is saved in a RichText file.

Edit Database Structure: Opens a tool that allows loading and editing the Plant Project Database structure. For more information about database structure and operations with it see Database Structure.

Class and Relationship Mapping tab

Under this tab the overall information about the selected class or relationships mapping (on the top) and the selected property (on the bottom) is displayed. The class related part contains the following sections:

Database. Name of the database the mapping is contained in. (The mapping will support several databases, once the application can store data in several databases.)
Description. Mapping Description.
Tables. List of tables where instances of the class is contained.
Instance. Database table fields that correspondent to ID, Name and Label of the class instance.
SELECT Statement. SQL statement for reading instances of the class generated based on mapping.
INSERT Statements. SQL statement or set of statements for inserting new instances of a class in the database.
UPDATE Statements. SQL statement or set of statements for updating existing instances in database.
DELETE Statements. SQL statement or set of statements for deleting existing instances from database.
All Properties. List of all properties in a class.
Non-mapped properties. List of properties that were not mapped.
Incorrectly mapped properties. List of properties that have some inconsistencies. (e.g. reference to non-existing table or column)

From the class mapping tab it is possible to activate or deactivate a mapping, add a new mapping, edit and remove a mapping. For more information see Class Mapping.

The property related panel contains more detailed information about the selected property. As for a class it is possible to activate or deactivate a mapping, add a new mapping, edit and delete a property mapping. For more detail see Property Mapping. The information of a property has the following sections:

Read Only. Is the property read-only or is it writable.
Tables and Fields. List of tables and fields the property value is contained in. Property value can be dissipated between several columns. In this case custom processes should be provided for that property (see Multi-Column Property Mapping).

Database Structure

In order to simplify the process of creating a mapping and provide the necessary validation a mapping file contains a model of the application database structure (only structure without data). If an application uses several databases then all of them should be included in the mapping file. A mapping doesn't contain the complete information about a database, but it contains the most significant information for generating the proper statements for reading and writing objects.

Database structure is stored in provider independent format. It is possible to load structure using several providers:

Microsoft Access Provider
Microsoft SQL Server .Net Provider
Oracle .Net Provider
Generic OLEDB Provider
Generic ODBC Provider

5.1 Loading database structure

The database structure is necessary to perform any editing on the mapping. After creation of a new mapping it should be loaded. The operation includes the following steps:

Open Database Structure Editor tool by clicking on "Edit Database Structure..." button on "Summary" tab of the Mapping Add-in panel or selecting "Database Mapping > Edit Database Structure...". The Database Editor tool is displayed.
Select the Databases node and press the "Load" button. The Open Database dialog box is displayed
Enter a name for database and optionally a description.
Select a database provider.
Define connection properties. You can test connection by pressing the "Test" button.
Click the "OK" button. The database structure is loaded and displayed in the editor.

Browsing the Database Structure

The loaded database is displayed in the Database Structure Editor dialog box. The dialog box has two parts: The Navigation pane (on the left) and the Details pane (on the right). For a selected database item the detailed information is displayed in the details pane.
The navigation pane allows to navigation over all main items of the database:

Tables
Columns
Primary Keys
Foreign Keys
Indeces

Editing the Database Structure

The Database Editor also supports the editing of a database structure (creation of new database, editing tables and columns, indeces and keys). By default this feature is disabled. To enable it you need to add an option in the Class Editor configuration file (ECClassEditor.exe.config):

<?xml version="1.0" encoding="utf-8" ?> <configuration> ... <appSettings> ... <add key="IsDBStructureEditable" value="True" /> ... </appSettings> </configuration>

Note: the ECClassEditor.exe.config file is located underneath the Program Files folder and you need administrator rights in order to make changes to it

Plant Project Database - Operations with Schema Mapping and Mapping Add-in panel