Database Adapter
Intoduction
ConnectALL adds the capability to sync records to and from ALM applications to a database with the new Database Adapter. Whereas the traditional adapters use HTTP connections to fetch the content, the Database Adapter uses JDBC connections to perform the DML operations on the database tables.
Capabilities
- One adapter is provided for multiple database vendors (oracle, mysql, sql server, postgres)
- Ability to map the columns of the table to the ALM fields
- Create, Update new records in the databases
- Supports filtering records using traditional SQL
- Supports Dynamic Record Linking
Configuration
- Create an application link between your application to the database.
Enter the Connection details for the database adapter
URL: JDBC Connection string
Username: Login user
Password: Password
Database type: Database engine classification valid values are (mysql, oracle, postgres, mssql)Enter the credentials for connecting to the other application, select the conflict management strategy and click configure.
Entity Mapping
The Database adapter lists all the catalogs and schemas defined on the database for mapping. Based on the catalog and schema selected, the tables will be listed.
Field Mapping
- The Database adapter will list all the columns defined on the table selected, so they can be mapped to the ALM fields as required.
- In order to save the field mapping, you will need to select the ID and Change Date fields as shown below.
Field Mapping
Datatype for Change Date field
Datatype for ID field
Advanced Configuration
Poll Query
ConnectALL will use the poll query as an additional "AND clause" when trying to sync the records from database tables. The syntax is governed by the SQL Where Clause of the database. Query results are limited to 1000 by default.
Sync Type
ConnectALL does support both POLL and PUSH flows, while Poll is the most commonly used option for databases. "Change Date" field should be mapped as defined in the "Field Mapping" section; when using POLL sync as ConnectALL will query the modified records based on this field.
Connection Pooling
The first thing that comes to mind when using the Database adapter is how can we manage the JDBC Connections that are being created. ConnectALL manages these connections by pooling the connection objects, and the same can be configured per application link in the "DatabaseAdapter.Properties" file under the "$MULE_HOME/conf" directory.
Example:
# Connection Pooling configuration for MySQLJiraBug application link # ################################################################### # Maximum active connections database.MySQLJiraBug.maxActive=4 # Maximum idle connections database.MySQLJiraBug.maxIdle=1 # Block when exhausted flag database.MySQLJiraBug.blockWhenExhausted=true
Database specific customizations
The Database Adapter provides a bundle of customization's for configuring based on the requirements. All of these properties are set in the "DatabaseAdapter.properties" file in the "$MULE_HOME/conf" directory.
Usecases
- Dump the data from multiple ALM applications on to a database table for generating customized reports and dashboards.
- Import the aggregated data in to an Application, using back end processing techniques from other systems.
Assumptions
- A table is already created based on the requirements in the preferred database (ConnectALL will not be performing any DDL operations on the tables)
- The table should have an auto-incrementing column that will be refer to as an "ID" field in the database; this helps to identify the records based on this unique ID while updating the records
- The table should have a column to store the modification date of the record, and also this needs to be set on every modification using triggers or any similar feature of the database when using the sync option as poll.
- The ID field and the Change Date field will not be updated by ConnectALL.
- Use the special datatypes "LastUpdatedDt" and "ID" for configuring the ID and Change Date fields in the application link.
Limitations
- ConnectALL can read from a database and write to it. However, it cannot do the same in CLOB, BLOB datatypes.
- Table relationships cannot be read by ConnectALL
- Comments, Attachments and Issue Linking features of other adapters is not supported by Database Adapter.