A common application for MapForce is migrating data between MySQL and PostgreSQL databases. MapForce makes this easy with graphical, drag-and-drop data mapping tools including a rich library of filters and data processing functions to transform source data.
The Database Connection Wizard in MapForce makes it easy to connect. SQLite connections are supported as native, direct connections to the SQLite database file. No separate drivers are required. PostgreSQL connections are supported both as native connections and driver-based connections through interfaces (drivers) such as ODBC or JDBC. Native connections do not require any drivers.
When you load the MySQL and PostgreSQL database structures in the mapping pane, MapForce automatically interprets the database schemas, lets you pick available database tables and views, and recognizes table relationships.
Once you have loaded all of the content models required for your database mapping, complete the mapping by simply dragging connecting lines between the source and target structures.
Using the no-code MapForce approach, it's easy to meet common data transformation requirements, for example:
Many database mappings require tran sformation of data between the source and target based on Boolean conditions or SQL and SQL/XML statements. You may need to perform logical comparisons, mathematical computations, or string operations, check for database data of a particular value, and make other modifications to the data. In screenshot above, data processing functions appear as the boxes between the lines joining the source and the target data model.
Data processing functions enable you perform advanced database mappings on-the-fly for a multitude of real-world transformation requirements.
Once your mapping is defined, the built-in MapForce Engine allows you to view and save the results with one click.
Your MySQL to PostgreSQL mapping will produce output in the form of SQL scripts (e.g., SELECT, INSERT, UPDATE, and DELETE statements) that are run against your target database directly from within MapForce.
After previewing the output, you'll have the option to automate the data transformation process via MapForce Server.
Database output tracing makes it easy to log all the changes made by a mapping project to the PostgreSQL or MySQL database when the mapping runs.
When tracing is on, events such as database insert or update actions, or errors, are logged in an XML file that you can later analyze or process further in an automated way.
Database tracing can be enabled at the database component, table, stored procedure, or database field level. You can choose to trace all messages or only errors, or you can disable tracing completely.
Once you have selected the desired tracing level, the affected database component in the mapping project is extended with an XML tree showing the log structure. If you prefer the log file to be in a format other than XML, you can map data from it to some other data format supported by MapForce (for example, a text file or even another database).
When clicking the Output button to preview the result of the mapping, you can also see an example of what the trace log will look like when the mapping is executed.
MapForce also offers database transaction handling to roll back the affected part of the database conversion when an error is thrown and optionally proceed with the rest of the mapping.
Sometimes an error occurs that doesn’t prevent the rest of a database mapping from continuing, such as when certain database constraints prevent the mapping from inserting or updating invalid data.
Enabling database transaction handling will enclose all changes done by the database component inside a transaction that will be rolled back in case of error, and you can opt to either proceed with rest of the mapping or stop further execution of the mapping. You can enable transactions at database component level and at table action level.
This is very useful when the mapping designer wants to capture valid incoming data and simply ignore invalid data.
To complement its database mapping and integration capabilities, MapForce includes a Database Query tab for performing direct database queries. When you connect to a database using the Database Query tab, MapForce displays its tables in a hierarchical tree in the browser pane. MapForce does not just migrate MySQL and PostgreSQL. Specialized support is available for:
You can then use the SQL editor tab to display, edit, and execute SQL statements, either by opening existing SQL files or creating SQL statements from scratch using drag and drop and auto-complete functionality.
You can execute your SQL script and view the results in tabular form and save both the retrieved data and the SQL script individually to separate files.
The MapForce database key settings allow you to customize how primary and foreign key values will be added to a database that is a data mapping target. You can either provide values for keys from within MapForce, or you can let the database system handle the generation of auto-values.
In situations where primary and/or foreign key relationships are not explicitly defined in your database tables, MapForce allows you to define these relationships inline, without any effect on the source data.
When you are mapping to a database, MapForce allows you to select database table actions to control how data is written to the MySQL or PostgreSQL database. This gives you full flexibility to automate the most advanced data management tasks.
The easy-to-use Database Table Actions dialog box allows you to define the columns within the selected table to be used to determine what action (INSERT, UPDATE, DELETE) should be executed in the database.
This provides unprecedented flexibility in manipulating database rows in response to XML, database, EDI, XBRL, flat file, Excel, JSON, JSON5, Web services, or other database data through MapForce.
MapForce includes robust support for stored procedures as input components (procedures that provide results) or as output components (procedures that insert or update data). Or, stored procedures may be inserted as a function-like call allowing users to provide input data, execute the stored procedure, and read/map the output data to other components.
This screenshot shows the mapping of a stored procedure in SQL Server to create an XML file. The procedure returns a table of data showing all the managers in the chain of command above the specified employee ID, supplied as an input parameter – in this example the constant 67.
The parameter could also be supplied as a calculated value or a data element retrieved from elsewhere in the database.
MapForce provides a context menu that lets users run the stored procedure to reveal the data structure for mapping. Executing the illustrated mapping generates the XML output.
SQL queries that operate inside the database are not always sufficient for complex data mapping tasks. MapForce provides additional database sorting functionality through the SQL-WHERE/ORDER component for database input that requires additional processing, or when other data or conditions in the mapping impact the sorted order of data rows.
The Database Table Actions dialog supports NULL value comparison. NULL-aware comparisons provide an improved way to handle databases that contains null values. MapForce users can configure a database mapping so that data comparison is done in a NULL-aware manner, according to rules applicable to the database kind involved in the mapping.
The data mapping shown at right is intended to update the target table without inserting duplicate entries. Both tables are defined allowing the email field to be NULL, so matching name entries with NULL email fields could exist in each table.
Clicking the Actions icon next to the TARGET database table opens the Database Actions Dialog. The NULL equal checkbox next to the email field allows MapForce to treat NULL values in the source and target as equal for data mapping purposes, even though they are not considered equal by database rules.
Altova MapForce Server includes the built-in data transformation engine developed for MapForce and is greatly enhanced to operate in server environments. MapForce Server performs data transformations for PostgreSQL to MySQL or any combination of XML, database, EDI, XBRL, flat file, Excel, JSON, and/or Web service using preprocessed and optimized data mappings stored in execution files based on data mappings defined in MapForce. MapForce Server takes data transformation to the next level with server capabilities including parallel processing and multi-threading, bulk SQL merge, cross-platform support, and more.
Preprocessing enables faster performance and reduced memory footprint for most data mappings. MapForce Server operates under the management of FlowForce Server, in a standalone configuration executed from a command line, or programmatically via an API.
After a MapForce mapping is designed and tested, it can be executed by MapForce Server to automate business processes that require repetitive data transformations.
MapForce pre-processes and optimizes data mappings, stores them in MapForce Server Execution files for command-line execution by MapForce Server, and uploads them for use in FlowForce Server jobs.
When MapForce Server operates under the management of FlowForce Server, data mappings are executed as FlowForce Server job steps, based on triggers defined as part of the FlowForce Server job. For example, a new XBRL instance document lands in a directory, which triggers a multi-step FlowForce Server job to first validate the file using RaptorXML+XBRL Server, then execute MapForce Server to extract certain data from the XBRL and insert it into a database.