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
Any changes to the Connection Pooling Profile requires a restart to the tomcat and mule services

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.


The JDBC Driver used for connecting to database's is configured using the property <database type>.driver=<fully qualified class name>
Default values: 

mysql.driver=com.mysql.jdbc.Driver
oracle.driver=oracle.jdbc.OracleDriver
mssql.driver=net.sourceforge.jtds.jdbc.Driver
postgres.driver=org.postgresql.Driver

JDBC drivers for Oracle, Postgres and SQL Server are not bundled by ConnectALL. They need to be deployed manually.

To add the JDBC drivers that are going to be used by ConnectALL, copy the Jar file of your database driver into the $MULE_HOME/lib/user directory and the $CATALINA_HOME/lib/ directory and restart the Mule and Tomcat services.

Databases will have different namespaces called catalogs, schemas, instances, databases to locate a database table. ConnectALL uses the fully qualified name of the table based on the configurations defined in the Entity Mapping screen. The Fully qualified name of a table is computed by concatenating the "Catalog", "Schema" and "Table" names delimited a "." \[Dot\] separator by default. The separator can be configured if needed using the below property.

MySQLJiraBug.mysql.catalog.schema.separator=.

In order to insert new records in to a table, ConnectALL uses this template to create the insert statement. Where $TABLE is a placeholder for table name $INSERT_FIELDS is a placeholder for comma separated field names and $INSERT_VALUES is a placeholder for comma separated values. The default values is insert into $TABLE ($INSERT_FIELDS) values ($INSERT_VALUES), and can be customized using:

MySQLJiraBug.mysql.insert.template=insert into $TABLE ($INSERT_FIELDS) values ($INSERT_VALUES)

In order to update existing records in a table, ConnectALL uses this template to create the update statement. Where $TABLE is a placeholder for table name $UPDATE_FIELD_VALUES is a placeholder for comma separated field name and value pairs and $ID_FILTER is the placeholder for record id filter. The default values is update $TABLE set $UPDATE_FIELD_VALUES where $ID_FILTER and can be customized using:

MySQLJiraBug.mysql.update.template=update $TABLE set $UPDATE_FIELD_VALUES where $ID_FILTER

In order to retrieve existing records in a table, ConnectALL uses this template to create the select statement. Where $TABLE is a placeholder for table name $SELECT_FIELDS is a placeholder for comma separated field names. The default values is select $SELECT_FIELDS from $TABLE and can be customized using:

MySQLJiraBug.mysql.select.template=select $SELECT_FIELDS from $TABLE

The formatting of the values might be different for each datatype across databases. Datatype formatting templates help to customize these differences. The default template for an Integer value is $VALUE

MySQLJiraBug.mysql.integer.value.template=$VALUE

The formatting of the values might be different for each datatype across databases, datatype formatting templates helps to customize these differences. Default template for a String values is '$VALUE'

MySQLJiraBug.mysql.string.value.template='$VALUE'

The formatting of the values might be different for each datatype across databases. Datatype formatting templates help to customize these differences. The default template for String values are TO_DATE('$VALUE'\,'$FORMAT') where $VALUE is the placeholder for value and $FORMAT is placeholder for date format.

MySQLJiraBug.mysql.date.value.template='$VALUE'

For parsing the dates correctly, ConnectALL needs to know the correct format to be used to convert the Application date when inserting or updating table records. For this purpose, ConnectALL exposes 2 properties to convert the date to a given format, and to tell the database what is the format of the date.

# Format to which application date needs to be converted to
MySQLJiraBug.mysql.java.date.format=yyyy/MM/dd
# Format to be sent to the database during insert and update
MySQLJiraBug.mysql.sql.date.format=yyyy/mm/dd

Important

 If you are using Oracle Database, change the property for the date field from – **oracle.date.value.template=TO_DATE($VALUE,$FORMAT) to oracle.date.value.template=$VALUE  in the DatabaseAdapter.properties file.

Also, if you are using any other databases (excluding MYSQL and Oracle), if the date field is not parsed, change the date.value.template=TO_DATE($VALUE,$FORMAT) to *date.value.template=$VALU*E in the DatabaseAdapters.properties file.

The formatting of the values might be different for each datatype across databases, datatype formatting templates help to customize these differences. Default templates for String values are TO_DATE('$VALUE'\,'$FORMAT') where $VALUE is the placeholder for value and $FORMAT is placeholder for date format.

MySQLJiraBug.mysql.date.value.template='$VALUE'

For parsing the dates correctly, ConnectALL needs to know the correct format to be used to convert the Application date when inserting or updating table records. For this purpose, ConnectALL exposes 2 properties to convert the date to a given format, and to tell the database what is the format of the date.

# Format to which application date needs to be converted to
MySQLJiraBug.mysql.java.datetime.format=yyyy/MM/dd HH:mm:ss
# Format to be sent to the database during insert and update
MySQLJiraBug.mysql.sql.datetime.format=yyyy/mm/dd hh24:mi:ss

When this value is set, the Connection Pool statistics will be printed in the log when acquiring and release a connection for a given connection key. The Default value for this is false

print.pool.statistics=false

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.