Database Replication

Introduction

Use the Database Replication module to import data from existing databases into your Mendix app. You can configure how each table, column, and relation maps to your Mendix domain model, including complex mappings across multiple joined tables. Configuration is available in the client or from Java.

Typical Use Cases

• Convert an existing database to a Mendix domain model.
• Synchronize your app with a database used by another app.
• Create mappings between database columns and object attributes.
• Map database references to Mendix object references.
• Map object attributes based on multiple joined tables.

Features

• Support for custom queries for object attributes

• Support for the following databases:

  • SQL Server 2005 and above
  • Oracle
  • PostgreSQL
  • AS400
  • DB2
  • DMS2
  • Informix

• Support for non-persistable objects

• Automatic query generation for object attribute values

• Object event execution at import time

Dependencies

• Mx Model Reflection module – This module is required in order to configure the mapping between Excel columns and the domain model.
• The replication.jar file – This is included in the .mpk file. Make sure you remove any older versions of replication.jar before installing this module. If you update, you do not need to remove the library anymore.
  If you have an old replication.jar file and are using the Excel Importer, update this module as well, since you need Excel Importer 3.0 or above when using this module.

Configuration

After downloading the module, place the DatabaseMapping page in the menu. With this page, you can create database connections and table mappings.

To use this module, you need a database connection definition and a table mapping. The connection definition points to the location of the database you want to import. The table mapping determines how this database is mapped to your domain model.

Once a database and mapping are defined, you can use the importByMapping Java action to run an import. You can also use the Import by table mapping button on the mapping overview.

Database Connection Properties

You can create new database connections from the Databases page. Each connection has a few basic properties. After creating a database connection, synchronize the table information using the button on the overview. This makes sure the connection information is correct and that the replication module has all the table information from your database.

On this page, you must select the database type before filling in the connection details. Available database parameters vary depending on database type.

Common Parameters

The following parameters are available to most database types:

• Host
• Port
• Username
• Password
• Database time zone

Depending on the database, some of the following parameters may be available:

• Database name
• Instance name
• Service name (exclusive to Oracle)
• SID (exclusive to Oracle)
• Use Integrated Security (for example, to connect to SQL Server using your Windows user credentials)

Database Time Zone

By default, the module assumes that the time zone of the source database is the same as the Mendix app's server time zone. Starting with module version 8.2.0, you can configure the source database time zone. If the setting is defined, the module converts imported datetime values from the specified database time zone to UTC. If the setting is not defined, the module assumes the database time zone is the same as the app's server time zone, and converts imported datetime values from server time zone to UTC.

Custom Connection Parameters

If your database type is not listed, it may still be possible to connect using a JDBC compatible driver and the Custom connection option. You need to obtain the drivers and place them inside the userlib directory, then configure the connection parameters:

• Username and Password – The credentials given to the driver to connect to the database server.
• Connection string – A fully formed JDBC URL string (for example, jdbc:postgresql://localhost:5432/database for a PostgreSQL database).
• Open/Close escape character – Characters used to quote table and column names (for example, " in PostgreSQL or [ and ] in SQL Server).
• Driver class – The full name of the driver to be loaded (for example, org.postgresql.Driver for PostgreSQL).
• Escape column names – Checkbox indicating if column names must be quoted using the escape characters when querying the database.
• Escape table names – Checkbox indicating if table names must be quoted using the escape characters when querying the database.
• Escape the schema and table name separately – Checkbox indicating schema and table names should be quoted together (for example, "public_schema.table_name") or separately (for example, "public_schema"."table_name").
• Allow AS token for tables – Checkbox indicating if AS is necessary when aliasing table names (for example, SELECT * FROM table AS A vs SELECT * FROM table A).
• Close connection after query – Checkbox indicating if the connection to the database must be closed after every query.

Mapping Properties

Table mappings are created from the Mappings page. Each mapping is associated with a single database. A mapping is always defined for one main Mendix object type and one corresponding database table. Information from other tables can be joined in later. The New mapping dialog box enables setting these properties.

Description

A descriptive name for your mapping.

Database Name and Table

This is the database and table from which you want to retrieve data. If you need other tables to create your Mendix object, you can select these later.

Object Type

The Mendix object type to which this table should be mapped.

Defining the Mappings

Once the mapping is created, you can use the mapping editor to define various aspects of the mapping. The mapping editor tabs are described in the following sections.

At the top of the page, you can set the Import action. This setting controls how mapped objects are added to your application. This option only applies to the object selected on the template level. All associations need to be configured separately.

• Synchronize and create objects – updates objects that already exist with the values from the database; objects that cannot be found are created
• Synchronize only existing objects – only updates objects that already exist, but does not create new objects
• Create an object for each row – does not check whether objects already exist; for each row in the sheet, a new object is created
• Only create new objects – searches for any objects with the defined key; if an object is not found, a new object is created; if the object is found, it is skipped

Tab Page Column Mappings

For each attribute or reference on the target Mendix object, you must define a column mapping. This indicates which column will be used to get the attribute value. A column mapping has the properties described in the following sections.

Mapping Type

Use this field to indicate which type of object the column should be processed as. You can choose an object attribute or an object reference. Depending on your choice, different subsequent options are available.

Query Type

This field determines whether you want to provide a custom query or use the automatic querying for this field. Usually, you will want the automatic querying, which is the default. Custom queries can be used to merge database fields, evaluate fields, or alter their notation. When you choose custom, you need to use escape characters or aliases exactly as the database expects.

Table Alias

This indicates the table that has the column you want to map. The tables in this drop-down menu are the ones you joined in the Include additional tables tab. The Start object entry represents the table you selected when creating the table mapping.

Column Name

This is the name of the column you want to map.

Format with Microflow

You can use a custom microflow to parse data for an attribute. Such a microflow should have one string input parameter. This will receive the data entry from the database. The microflow should return the value to put in the field you want to map. For example, you can input a DateTime if mapping to a DateTime attribute.

Attribute Name

This field is the name of the attribute you map this column to. This field also has quick search functionality. Type in the box to the left of the reference selector and press “tab” to search for the value you typed.

Reference and Object Type

These fields are used to configure referenced objects. You can specify what reference should be followed and what type of object is used. Both fields have a quick search box.

Is Key

This field determines whether the attribute can uniquely identify its object.

The following options are available:

• No – No special handling for this column
• Yes or Yes, only for the main object – A unique key value for the object to which it is mapped
• Yes, only for the associated object – A key for the object to which it is a reference
• Yes, for both – A key for both the mapped object and the referenced object

Case Sensitive

This determines whether values in this column are interpreted as case-sensitive. This is only possible if the database you are using is also case-sensitive. For example, MS SQL does not search case-sensitively by default.

Custom Query

If you selected Custom as the query type, you can enter a custom select value for the field. This enables constructing a complex value for the mapped field. For example, you can concatenate multiple database columns together. The Join tokens table below the query field shows the tokens representing the tables you joined using the Include additional tables tab. You can insert these into your query and they will be replaced by the proper table names.

For example, to map a first name and last name column to a full name object attribute, use a query like this: [%startobject%].firstname || ' ' || [%startobject%].lastname.

Tab Page References

For each reference used in the mapping, you can configure what should happen if a referenced object is not found.

The following options are available:

• Create if not found – If the referenced object is not found, a new one is created with the values found in the database.
• Ignore if not found – If the referenced object is not found, the reference is not set and no new object is created.
• Create an object for each row – Does not check whether objects already exist. For each row in the dataset, a new object is created. References between objects are not set.
• Only create new objects – Searches for any objects with the defined key. If those objects are not found, a new object is created. If the object is found, it is skipped.

Data Handling

As in Studio Pro, the value of a reference (set) can be added to the current values or overwritten. This property specifies which of those actions is used. Versions below 3.0 use Overwrite by default.

• Overwrite – Always uses the contents of the sheet to overwrite the current reference value. If the cell is empty, the reference is set to empty as well.
• Append – Adds the newly found associated objects to the reference (set). If the cell is empty, the reference remains unchanged.

Print Message When Reference Is Not Found

This keeps track of all the object keys that could not be found in this association.

Commit Unchanged Objects

Even if there are no changes to the object, this still commits the objects in order to execute the events.

Including Additional Tables

You can define which additional tables are necessary to get the data for the Mendix object you are mapping. Each additional joined table has the properties described in the following sections.

Short Name

This is a descriptive name for this table. This name is displayed in the Table alias selector for the column mapping configuration.

Join Type

This is the type of join to make. Most standard database join types are supported.

Left and Right Table

These are the tables to join together. The left table is always the one that is already included in this table mapping. The right table can be any other table.

Join Constraints

You can specify join constraints. Each constraint specifies that two fields, one from each table, should be equal to one other. Usually, you will want at least one constraint. If you do not specify any constraints, the join is a cross join.

Tab Page Settings

There are a number of settings that control the behavior of the table mapping as a whole. These are described in the following sections.

⚠ Custom Constraints

You can put a constraint on the rows that are imported from the database. Here, you can insert a custom SQL constraint that will be appended as a WHERE clause to all queries. This means that any database rows that do not satisfy this constraint will be ignored by the replication module.

Print Statistics and Not Found Messages

These control which kinds of messages are printed in the app console.

Remove Unsynced Objects

This option defines what to do with objects in your Mendix database which have no corresponding entry in the imported database. There are three options:

• Nothing (default) – Nothing is done to Mendix objects, whether they have corresponding entries or not.
• Track changes – Keep track of which objects were synchronized, but do not remove them if they have no corresponding entries in the imported database.
• Remove unchanged objects – Keep track of which objects were synchronized and remove those with no corresponding entries in the imported database. You can use this option if, for example, you want to mirror any changes to a target database, and not just import new data.

This functionality requires your Mendix objects to have an integer attribute that is used to keep track of which objects to remove. If you select Track changes or Remove unchanged objects, an extra field to select this integer attribute is displayed. This tracking attribute must not be used by your app in any other way, as this may cause unexpected results. Also, if you select Remove unchanged objects, objects that were created by your app and that were not imported will be removed at the next import.

Mode

Setting this to advanced reveals more options. Only modify these if you know what you are doing.

• Import in a new context – Creates a new context for each mapping import. If you clear this, you can control the contexts in which imports are done.
• Use transactions – Controls the use of the database transaction functionality by the replication module. Normally, each mapping is imported in its own transaction. If you clear this box, the app controls the transaction.
• Ignore objects with empty keys – This means that empty values for key fields are not considered valid. Clearing this allows objects with empty key values to exist.
• Reset empty associations – This means that, if an empty value is found for the associations, the reference should be reset.
• Commit unchanged objects – Even if there are no changes to the object, this still commits the objects in order to execute the events.
• Print not found messages for main object – Keeps track of all the object keys that could not be found. This consumes a lot of memory, since all the values need to be remembered. This only works in combination with the "find ignore" sync option.

Final Microflow

This setting specifies a microflow that runs after the import is completed. The microflow can have two optional parameters: TableMapping and ReplicationStatus. TableMapping is the instance that was just executed. ReplicationStatus (not committed) contains the statistics that are also printed in the log.

Time Zone of Datetime Values in the Database

This setting specifies the time zone in which datetime values are stored in the source database. If the setting is defined, all imported datetime values are converted from database time zone to UTC and stored in UTC in resulting Mendix objects. If the setting is not defined, it defaults to database time zone.

In most cases, there is no need to change this setting unless you know that datetime values in this particular table are stored in a different time zone from the rest of the database.

Configuring Import Calls

An import call is a table mapping configured to be run with constraints. This constraint may be of the following types:

• Static, where all calls are constrained by the same values
• Dynamic, where the constraint relies on the value of attributes in the table

Constraint

This field defines how the custom constraints will be combined to form the import call constraint:

• Normal – All constraints are combined with the AND operator following their constraint number.
• Advanced – You need to explicitly define the import call constraint in the Constraint dependency field. You can use braces and all the operators supported by the database (AND, OR, IN, NOT, etc.). For example: [&7&] OR ([&1&] AND [&2&]).

Data Objects

When executing the import using an import call, you can optionally provide two Mendix objects to use as constraint parameters. All object types are supported (including non-persistable).

These objects must be provided as input explicitly in a microflow. Using these objects in an import call prevents it from being executed directly by a user action. Import on the Import call tab and Run scheduled events on the Planned import action tab will be affected, as they perform an import using import calls.

Custom Constraints

If you click any constraint in the Custom constraints table, the Constraint details dialog box opens. The fields are as follows:

• Constraint Type:

  • Dynamic – Use the value from an attribute as a constraint. This can be an attribute from data object 1 or from data object 2.
  • Static – Choose any of the columns and set a static value as the constraint.

• Ignore When Empty – This is only applicable when selecting the Normal constraint mode. When a dynamic value is empty (null), the constraint is ignored, and only the other constraints are applied to the import action.

• Table – This determines the tables from the mapping on which the constraint applies. You can select all the tables you have configured in the mapping, so both additional joined tables, as well as the main table.

• Constraint – The first value to select is any of the columns from the selected table. After that, you can select any operator followed by the value you want to constrain. If you select the Is (not) null operator, the value is no longer useful and will be hidden.

Planned Import Actions

The module supports configuring scheduled import actions. When importing multiple tables from a database, placing all the import activities in one or more microflows can cause problems. The module provides an option to configure your complete import scheduled event.

Each import action can consist of one or multiple import calls. The default scheduled event flow processes all enabled import actions and automatically calls the import call configuration for each action. This allows you to configure the complete order and dependencies between import actions.

You can use the DateTime from the last successful import as a constraint. This DateTime is the start time of the last successful run.

Each import action runs in a single transaction, which means all changes are reverted if an exception occurs. Configure the mapping not to create a new transaction or a new context.

Importing and Exporting a File

You can import and export table mappings to an XML file using the Table mapping tab.

Data Types

Database-specific data types are converted to a Java type with the JDBC driver. The module then converts these Java types into a Mendix primitive type. This step also performs implicit conversions between types.

Mendix primitive types and supported mappings from Java objects are listed in the following table. Supported conversions are marked with X.

Mendix Type / Java Type	Boolean	DateTime	Integer	AutoNumber / Long	Decimal	HashString / String / Enum	Binary
Boolean	X
util.Date		X		X		X
Integer	X	X	X		X
Long	X	X	X	X	X
Double	X		X	X	X
BigDecimal	X		X	X	X
String	X	X	X	X	X	X

Troubleshooting

A Table Alias Is Selected but the Column Name Field Does Not Show Anything

This is a limitation. The workaround is to select the blank option in the table alias selector, then select your desired table again. This should repopulate the column selector.

No Automatic Mapping Inheritance

An example of mapping inheritance would be creating a different subtype of a generalization based on a value in a column.

The database importer cannot do this automatically. To work around this, map your table to temporary objects containing the fields for which you need to determine the subtype. Then use a microflow to convert each of these to the correct type.