DatabaseTable Connector

Last modified 30 Sep 2021 09:11 +02:00

Identity connector for generic relational database table.

Functionalitystable
Development statusactive (actively developed and maintained)
Support statussupported
OriginICF
Support provided byEvolveum
Target systemsGeneric database table (JDBC)
ProtocolDatabase-specific. Connector is using JDBC.
Source codehttps://github.com/Evolveum/openicf/tree/master/connectors/java/databasetable

Capabilities and Features

Schema YES

Provisioning

YES

Live Synchronization

YES

Using last modification timestamps

Password

YES

Activation

SIMULATED

Configured capability

Scripting

NO

History

This is almost entirely the original version from Sun ICF. However, there are some Evolveum improvements.

Version Origin Binary Sources Build Date Framework version Description

1.1.0.e6296

OpenICF trunk version, built and tested by Evolveum

download jar

OpenICF: trunk, revision 6296

Jan 2013

Well tested (now obsolete)

1.4.0.49

Evolveum

download jar

Evolveum OpenICF git repository (tag connector-databasetable-v1.4.0.49)

16 Jan 2015

Bundled with midPoint.

1.4.2.0

Evolveum

download jar

Evolveum OpenICF git repository (tag connector-databasetable-v1.4.2.0)

June 2016

1.4.2.0

Current version. Bundled with midPoint 3.4.

1.4.3.0

Evolveum

download jar

Evolveum OpenICF git repository (tag connector-databasetable-v1.4.3.0)

October 2018

1.4.2.0

Sync Order Column (contributed by Martin Lizner)
Current version. Bundled with midPoint 3.9.

1.4.4.0

Evolveum

download jar

Evolveum OpenICF git repository (tag connector-databasetable-v1.4.4.0)

July 2020

1.4.2.0

Exception handling for creating new connection

1.4.5.0

Evolveum

download jar

Evolveum OpenICF git repository (tag connector-databasetable-v1.4.5.0)

March 2021

1.4.2.0

Using driver’s native method for connection validation

1.4.6.0

Evolveum

download jar

Evolveum OpenICF git repository (tag connector-databasetable-v1.4.6.0)

March 2021

1.4.2.0

Logging verbosity improvement

1.4.7.0

Evolveum

download jar

Evolveum OpenICF git repository (tag connector-databasetable-v1.4.7.0)

August 2021

1.4.2.0

UID type translation improvement

1.4.8.0

Evolveum

download jar

Evolveum OpenICF git repository (tag connector-databasetable-v1.4.8.0)

August 2021

1.4.2.0

Exception handling improvements with sqlState

1.4.8.1

Evolveum

download jar

Evolveum OpenICF git repository (tag connector-databasetable-v1.4.8.1)

September 2021

1.4.2.0

Patch regarding sqlState switch not being available in the IAM GUI

1.4.9.0

Evolveum

download jar

Evolveum OpenICF git repository (tag connector-databasetable-v1.4.9.0)

September 2021

1.4.2.0

Extending sqlState handling and adding more generic options regarding sqlState error codes to connector configuration

Interoperability

This connector is supposed to work with all relational databases that follow the SQL standard and that provide a JDBC driver. The driver is not part of the connector distribution (except for PostgreSQL driver, which is distributed with midPoint). The deployer is responsible for using correct JDBC driver.

Limitations

The connector supports only a single database table.

Requires correct JDBC driver to be present on classpath

Support

Only standard SQL behavior is supported. This means standard SQL operations and standard database schemas. Non-standard database operations and vendor-specific behavior is not supported.

Problems originating in JDBC driver, problem that are caused by non-compliance to SQL standard or vendor-specific behavior are not covered by the support.

The bundled support does not include support for any specific database server. Support for specific database servers needs to be explicitly negotiated in the support contract.

Documentation

Unfortunately, there is no comprehensive documentation for this connector.

Resource Examples

Notes

DatabaseTable connector now assumes that UID is the same as NAME. See https://jira.evolveum.com/browse/MID-1127.

The following two configuration properties were reported to resolve problems with Oracle DATE attribute

<icfsdbt:nativeTimestamps>true</icfsdbt:nativeTimestamps>
<icfsdbt:allNative>true</icfsdbt:allNative>

For PostgreSQL TIMESTAMP or TIMESTAMPTZ attribute only allNative configuration property is needed

<icfsdbt:allNative>true</icfsdbt:allNative>

In addition, the DATE, TIMESTAMP or TIMESTAMPTZ column must be provisioned as string with correct formatting, e.g.: return new Date().format("yyyy-MM-dd HH:mm:ss"), otherwise "ORA-01861: literal does not match format string" occurs (or similar exception in PostreSQL).

TIMESTAMPTZ is stored including the timezone. DatabaseTable connector doesn’t work with timezone directly, but the value is automatically converted to midPoint local timezone. Therefore, you always work with local timezone in mappings and it will be automatically stored correctly in the database.

PostgreSQL server might be configured to close the idle connection after several minutes. That will raise following exception in the DatabaseTable connector "DB error: org.postgresql.util.PSQLException(An I/O error occurred while sending to the backend.)→;java.net.SocketException(Connection reset)". Solution for this problem is configure jdbc driver to send keepalive packets by adding keepalives=1 and keepalives_idle=XXX (XXX is number of seconds after which keepalive packet will be sent) to jdbcUrlTemplate. For example:

<icfsdbt:jdbcUrlTemplate>jdbc:postgresql://localhost:5432/databaseName?keepalives=1&keepalives_idle=120</icfsdbt:jdbcUrlTemplate>

SQLStateExceptionHandling configuration parameter was added. This acts as a switch if SqlState flag based exception evaluation should be turned on. Currently the evaluation helps to translate SQL error messages to the "AlreadyExists" exception for further handling on the IAM. By default the flag is set to "false". In case that the default behavior is active then other mechanisms of exception interpretation are active (i.e. see "Already exists messages" configuration property).

As from the connector version 1.4.9.0 the SQLStateExceptionHandling is extended by the capability of specifying concrete sqlState codes for some of the most frequent exceptions. The properties used for this are mutlivalued so you are capable to interpret multiple sqlState codes in case of an error. SQLState codes are an SQL standard and should be present in the documentation of most of the relational databases. The debug log also dumps the SQL state codes in case of an SQLException occurrence. Additionally in case the SQLStateExceptionHandling feature is turned on, the handling via "alreadyExistMessages" is inactive even if the properties for it are specified.

Example of the sqlState handling configuration property and also the SQLstate code properties.

<!--Switch to start handling of some of the sql exceptions via the SQL state error codes.-->
<icscdbtable:SQLStateExceptionHandling>false</icscdbtable:SQLStateExceptionHandling>
<!--Collection of values representing SQL state codes which can be interpreted to create an AlreadyExists exception-->
<icscdbtable:SQLStateAlreadyExists>23000</icscdbtable:SQLStateAlreadyExists>
<!--Collection of values representing SQL state codes which can be interpreted to create an ConnectionFailed exception-->
<icscdbtable:SQLStateConnectionFailed>08000</icscdbtable:SQLStateConnectionFailed>
<!--Collection of values representing SQL state codes which can be interpreted to create an InvalidAttributeValue exception-->
<icscdbtable:SQLStateInvalidAttributeValue>22000</icscdbtable:SQLStateInvalidAttributeValue>
<!--Collection of values representing SQL state codes which can be interpreted to create an Configuration exception.-->
<icscdbtable:SQLStateConfigurationException>0A000</icscdbtable:SQLStateConfigurationException

Example of the "Already exists messages" configuration property

<icscdbtable:alreadyExistMessages>Here insert the error message which is produced in case of an AlreadyExists situation (conflict)</icscdbtable:alreadyExistMessages>