Define a Database Model

This page describes how to define a database metadata and reverse-engineer a database.

Create and Reverse-Engineer a Database Model

This process uses a wizard composed of three steps:

  1. Create a new data server

  2. Create a new schema

  3. Reverse-engineer the datastores

Step 1: Create A New Data Server

To create a new data server:

  1. Click on the metadata New Metadata button in the Project Explorer toolbar. The New Model wizard opens.

  2. In the Choose the type of Metadata tree, select RDBMS > <DBMS Technology> where DBMS Technology> is the name of the DBMS technology you want to connect to.

  3. Click Next.

  4. Select the parent folder or project for your new resource.

  5. Enter a File Name and then click Next.

  6. Choose a Module and then click on Finish.

    To manage modules, refer to Install and Manage Modules

  7. The Server wizard opens.

  8. On the Server Connection page, enter the following details:

    • Name: Name of the data server.

    • Driver: Select a JDBC Driver suitable for your data server.

    • URL: Enter the JDBC URL to connect this data server.

    • User: The database username, if any.

    • Password: This user’s password, if any.

    • (Optional) AutoCommit: When this option is selected, Semarchy xDI Designer connects to the data server using auto-commit connections. This setting only affects the connections from the Designer (used for instance for reverse-engineering) and does not affect the connections used by Runtimes.

  9. Click on the Connect button to validate this connection and then click Next. The schema properties page opens.

Step 2: Create A New Schema

To declare a new schema:

  1. On the Schema Properties page, enter the following details:

    • Name: Select the checkbox next to this field and enter a user-friendly name for this schema.

    • Catalog Name (when applicable): Click on the Refresh Values to retrieve the list of catalogs and then select one.

    • Schema Name: Click on the Refresh Values button to retrieve the list of schemas from the database and then select the expected schema.

    • Reject Mask: Set the table name mask for the table containing the load rejects (error tables). See the Table Name Masks section below for more information.

    • Load Mask: Set the table name mask for the temporary load tables.

    • Integration Mask: Set the table name mask for the temporary integration tables.

    • Work Schema: Select a schema for storing the load and integration temporary tables for this data server. This schema is also referred to as the Staging Area. See the #_work_and_reject_schemas_selection section for more information. Click the …​ button to create a new schema definition and set it as the work schema. If no schema is selected for Work Schema, the Schema Name schema will be used.

    • Reject Schema: Select a schema for storing the errors (rejects) tables for this data server. Click the …​ button to create a new schema and set it as the reject schema. If no schema is selected for Reject Schema, the Schema Name schema will be used.

      It is recommended to configure two separate temporary (for example, SEMARCHY_TEMP) and error (for example SEMARCHY_REJECTS) schemas for each database server and set them as the Work Schema and the Reject Schema for all the data schemas. This avoids mixing application data (data schemas) and xDI tables in the same schema.

  2. Click Next. The Reverse Datastore page opens.

Step 3: Reverse-Engineer The Datastores

  1. In the Reverse Datastore page, optionally type a filter for the objects you want to retrieve.

    Use the _ and % wildcards to represent one or any number of characters.

  2. Optionally filter the type of objects that you want to return:

    • When All Datastore is selected (default), all the objects from the server will be retrieved.

    • If you unselect All Datastore, checkboxes that correspond to the different object types available for this technology are enabled (VIEW, TABLE, …​). Select the types you want to retrieve.

  3. Click the Refresh button to refresh the list of datastores.

  4. Select the datastores that you want to reverse engineer in the list.

  5. Set the following reverse-engineering parameters:

    • Reverse FK: When selected (default), foreign keys will be included in reverse-engineering.

    • Delete No Longer Existing Column: If this option is selected (default), the reverse-engineering process ran on an existing metadata deletes the columns that cannot be found anymore.

  6. Click Finish. The reverse-engineering process retrieves the structure of the selected datastores.

  7. Press Ctrl+S to save the editor.

Reverse-Engineer A New Schema

To reverse-engineer a new schema on an existing metadata:

  1. In the metadata file editor, select the root node.

  2. Right-click and select Action > Launch DataSchema Wizard.

  3. Follow the steps described in Step 2: Create A New Schema.

Reverse-Engineer An Existing Schema

To retrieve metadata changes from an existing schema, or to retrieve new table definitions, you must perform a new reverse-engineering.

To reverse-engineer an existing schema:

  1. In the metadata file editor, select the node corresponding to the schema.

  2. Right-click and select Action > Launch DataSchema Wizard.

  3. Click Next on the first page of the wizard.

  4. On the second page follow the steps described in Step 2: Create A New Schema.

Set the Delete No Longer Existing Column property to define whether you want to keep or delete the columns that cannot be found anymore.

Table Name Masks

Table Name Masks define name patterns for the temporary objects created at run-time.

A Table Name Masks is a string that corresponds to a table name. It can be parameterized using the following variables:

  • [number]: Automatically generated increment for the load tables, starting with 1.

  • [targetName]: Name of the target table of a mapping.

  • ${variable}$ or %{variable}%: A session variable that is set at run-time.

Note that the resulting string must be a valid table name.

Example: L_[targetName]_[number] would create Load tables named L_CUSTOMER_1, L_CUSTOMER_2, etc for a mapping loading the CUSTOMER table.

Creating and using a metadata Query

A SQL Query can be reverse-engineered and used in a database metadata.

To create a Query:

  1. Start by creating a folder to store queries:

    1. Right-click the database node in the metadata editor and select New > Query Folder.

    2. Enter a Name for the folder in the Properties view.

  2. Right-click the folder in the tree view and select New > Query.

  3. Enter a Name for the query.

  4. Enter an Expression that takes the form of a SQL SELECT query.

  5. Save the metadata.

  6. Right-click the query and select Actions > Reverse.

The reversed query can be used in Mappings as Source like any other datastore. However, it is not meant to be used as a target datastore.
It is possible to parameterize the query using XPath syntax.