Any Database (ScriptedSQL)

Last modified 22 Mar 2022 08:31 +01:00
Table of Contents

Status

Provisioning works well.
Synchronization works well.

Recommended connector

ScriptedSQL Connector

The ScriptedSQL connector can be used for any JDBC-supported database. This connector can (need to) be scripted, so it has no restriction on number of tables, joins, remote procedure calls etc. The following steps describe setup for PostgreSQL database.

Resource Configuration

PostgreSQL Installation

Standard PostgreSQL installation is expected.

Example Database/Tables Definition

The database needs to be created. The following example is available in samples/resources/scriptedsql/create-scripted-idm-db-sync-postgresql.sql:

ScriptedSQL Database Definition
CREATE USER mdp_scriptedsql WITH PASSWORD 'password' LOGIN;

CREATE DATABASE mdp_scriptedsql WITH OWNER = mdp_scriptedsql ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;

The tables need to be created. The following example is available in samples/resources/scriptedsql/create-scripted-idm-tables-sync-postgresql.sql:

ScriptedSQL Tables Definition
CREATE TABLE Users (

    id        SERIAL PRIMARY KEY,

    login        VARCHAR(32) NOT NULL,

    firstname    VARCHAR(255),

    lastname    VARCHAR(255),

    fullname    VARCHAR(255),

    email        VARCHAR(255),

    organization    VARCHAR(255),

    password    VARCHAR(255),

    disabled    BOOLEAN DEFAULT false,

    timestamp    TIMESTAMP WITH TIME ZONE DEFAULT now()

);

CREATE TABLE Groups (

    id        SERIAL PRIMARY KEY,

    name        VARCHAR(255) NOT NULL,

    description    VARCHAR(255)

);

CREATE TABLE Organizations (

    id        SERIAL PRIMARY KEY,

    name        VARCHAR(255) NOT NULL,

    description    VARCHAR(255)

);

CREATE OR REPLACE FUNCTION update_timestamp_column()

RETURNS TRIGGER AS $$

BEGIN

    NEW.timestamp = now();

    RETURN NEW;

END;

$$ language 'plpgsql';

CREATE TRIGGER update_account_timestamp BEFORE UPDATE ON Users FOR EACH ROW EXECUTE PROCEDURE  update_timestamp_column();

The Groovy scripts implementing the operations are stored in samples/resources/scriptedsql/*.groovy. The scripts need to be referenced from the resource:

If you play with the samples, you will need to make changes in database (e.g. new/different columns), in the Groovy scripts (e.g. the new/different columns) as well as in the resource sample (new/modified mappings). You don’t need to use all of the scripts; e.g. if you don’t need synchronization, you don’t need the "SyncScript.groovy" to be referenced from the resource configuration. Omitting script from resource configurationProperties will cause the connector to not support that operation.

The configuration above and in the samples provides the following features to be as much as production-like as possible:

  • full account support (but Groovy scripts are prepared also for groups and organizations, so the samples may be extended soon)

  • create, update, read and delete operations

  • rename operation

  • activation (enable/disable) and password support for accounts

  • generated unique identifier for accounts, groups and organizations in database (by PostgreSQL)

  • synchronization support (for accounts)

Sample resource can be imported from samples/resources/scriptedsql/*.xml.

If you wish to set up login, in case of the ScriptedSQL connector use the following:

Logger Name Recommended log level

org.forgerock.openicf.misc.scriptedcommon.ScriptedConnector

TRACE

The Groovy scripts may need changes to work on other databases. They should be relatively simple to modify; the SyncScript.groovy for example, has been patched for PostgreSQL "timestamp" format.

Sometimes the proper syntax in the Groovy files might be tricky. For example, this is real-life problem and its solution:

  1. Originally, we were attempting to perform the call with an incorrect syntax:

sql.call("{? = call STORED_PROCEDURE(?, ?)}",[Sql.VARCHAR, attributes?.get("pidm")?.get(0), Sql.VARCHAR])

Proper syntax is: sql.call("? = call STORED_PROCEDURE(?, ?)",[Sql.VARCHAR, attributes?.get("pidm")?.get(0), Sql.VARCHAR])

Note the absence of the enclosures {} in the second line.
As this might be obvious to most Groovy devs, this gave us quite the headache since there’s confusing information published regarding this method.

Shared by Rodrigo Yanis, thank you!

Connector Configuration

JDBC Driver

The connector requires appropriate JDBC driver. The driver needs to be available to the web server. It usually has to be placed on on web server classpath. E.g. this means copying the driver to $TOMCAT_HOME/lib directory if tomcat server is used and restarting the server.

We are aware of problems with ScriptedSQL connector and PostgreSQL database using the JDBC driver bundled with midPoint (version 9.1). Upgrading the JDBC driver to 9.3 seems to fix the issue.

Connector Configuration Example

<c:connectorConfiguration>
    <icfc:resultsHandlerConfiguration>
        <icfc:enableNormalizingResultsHandler>false</icfc:enableNormalizingResultsHandler>
        <icfc:enableFilteredResultsHandler>false</icfc:enableFilteredResultsHandler>
        <icfc:filteredResultsHandlerInValidationMode>true</icfc:filteredResultsHandlerInValidationMode>
        <icfc:enableAttributesToGetSearchResultsHandler>false</icfc:enableAttributesToGetSearchResultsHandler>
    </icfc:resultsHandlerConfiguration>

    <!-- Configuration specific for the ScriptedSQL connector -->
    <icfc:configurationProperties xmlns:icscscriptedsql="http://midpoint.evolveum.com/xml/ns/public/connector/icf-1/bundle/com.evolveum.polygon.connector-scripted-sql/com.evolveum.polygon.connector.scripted.sql.ScriptedSQLConnector">
        <icscscriptedsql:createScriptFileName>CreateScript.groovy</icscscriptedsql:createScriptFileName>
        <icscscriptedsql:updateScriptFileName>UpdateScript.groovy</icscscriptedsql:updateScriptFileName>
        <icscscriptedsql:deleteScriptFileName>DeleteScript.groovy</icscscriptedsql:deleteScriptFileName>
        <icscscriptedsql:schemaScriptFileName>SchemaScript.groovy</icscscriptedsql:schemaScriptFileName>
        <icscscriptedsql:searchScriptFileName>SearchScript.groovy</icscscriptedsql:searchScriptFileName>
        <icscscriptedsql:testScriptFileName>TestScript.groovy</icscscriptedsql:testScriptFileName>
        <icscscriptedsql:syncScriptFileName>SyncScript.groovy</icscscriptedsql:syncScriptFileName>
        <icscscriptedsql:scriptRoots>/path/to/all/scripts/</icscscriptedsql:scriptRoots>
        <icscscriptedsql:classpath>.</icscscriptedsql:classpath>
        <icscscriptedsql:scriptBaseClass>BaseScript</icscscriptedsql:scriptBaseClass>

        <icscscriptedsql:user>postgres</icscscriptedsql:user>
        <icscscriptedsql:password><clearValue>postgres</clearValue></icscscriptedsql:password>
        <icscscriptedsql:jdbcDriver>org.postgresql.Driver</icscscriptedsql:jdbcDriver>
        <icscscriptedsql:jdbcUrlTemplate>jdbc:postgresql://localhost:5432/postgres</icscscriptedsql:jdbcUrlTemplate>
    </icfc:configurationProperties>
</c:connectorConfiguration>

<capabilities xmlns:cap="http://midpoint.evolveum.com/xml/ns/public/resource/capabilities-3">
    <configured>
        <cap:pagedSearch/>
        <cap:countObjects/>
        <cap:create>
            <cap:enabled>true</cap:enabled>
        </cap:create>
        <cap:update>
            <cap:enabled>true</cap:enabled>
        </cap:update>
        <cap:delete>
            <cap:enabled>true</cap:enabled>
        </cap:delete>
        <cap:liveSync>
            <cap:preciseTokenValue>true</cap:preciseTokenValue>
        </cap:liveSync>
    </configured>
</capabilities>

Resource Sample

See resource samples and Groovy implementation scripts in Git samples directory for ScriptedSQL connector (master).

Was this page helpful?
YES NO
Thanks for your feedback