Connect to a MySQL database with ScriptedSQL

This sample uses the Groovy Connector Toolkit bundled with IDM (openidm/connectors/groovy-connector-1.5.20.9.jar ) to implement a ScriptedSQL connector that interacts with an external MySQL database (HRDB), and also demonstrates the following functionality:

Complex data types.

Complex data types can be stored, retrieved and synchronized like any other object property. They are stored in the managed data as JSON objects, represented as a string, but can be mapped to external resources in any format required. You can customize the mapping to do additional work with or transformations on the complex data types.

This sample defines one complex data type, cars, discussed in more detail later in this section.
Event hooks to perform actions.

The mapping from the internal repository to the external hrdb database includes two script hooks. The first hook is for an onCreate event and the second is for an onUpdate event.
Custom scripted endpoints.

Custom scripted endpoints are configured in the provisioner configuration file and allow you to execute custom scripts over REST. This sample uses a custom scripted endpoint to reset the database and populate it with data.

Because MySQL cannot "un-hash" user passwords there is no way for a reconciliation operation to retrieve the password from MySQL and store it in the managed user object. This issue might impact configurations that support multiple external resources in that passwords might not be synchronized immediately after reconciliation from MySQL to the managed user repository. Users who are missing in the repository will be created by reconciliation but their passwords will be empty. When those users are synchronized to other external resources, they will have empty passwords in those resources. Additional scripting might be required to handle this situation, depending on the requirements of your deployment.

The Groovy scripts required for the sample are located in the samples/scripted-sql-with-mysql/tools directory. Note that the power of the Groovy connector is in the associated Groovy scripts, and their application in your particular deployment. The scripts provided with this sample are specific to the sample. You must customize these scripts to address the requirements of your specific deployment. The sample scripts are a good starting point on which to base your customization.

Configure the external MySQL database

This sample assumes a database running on the localhost.

Download MySQL Connector/J version 8.0 or later.

Unpack the downloaded file, and copy the .jar file to openidm/lib :

cp mysql-connector-java-version-bin.jar /path/to/openidm/lib/

If you are running this sample with an SQL database other than MySQL, download the corresponding driver and place it in the openidm/lib directory. It is not necessary to create an OSGi bundle for the driver.

Set up MySQL to listen on localhost, port 3306. IDM will connect to the hrdb database as user root with password password.

To use an existing MySQL instance that runs on a different host or port, or to change the database credentials, edit the configurationProperties in the connector configuration file (samples/scripted-sql-with-mysql/conf/provisioner.openicf-hrdb.json) before you start the sample. The default configuration is as follows:

"configurationProperties" : {
    "username" : "root",
    "password" : "password",
    "driverClassName" : "com.mysql.cj.jdbc.Driver",
    "url" : "jdbc:mysql://localhost:3306/hrdb?serverTimezone=UTC",
    ...

The default configuration expects SSL, which is strongly advised in a production environment. If you are running this in a test environment, you can bypass the SSL requirement:

Add &useSSL=false to the end of the url.
If you are running MySQL 8.0.11+, add &allowPublicKeyRetrieval=true to the end of the url.

Set up the hrdb database, with which IDM will synchronize its managed user repository:

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE hrdb CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)

Configure your GRANT permissions:

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON hrdb.* TO 'root'@'%' WITH GRANT OPTION;

Run the sample

The mapping configuration file (sync.json) for this sample includes the mapping systemHrdb_managedUser. You will use this mapping to synchronize users from the source hrdb database with the target IDM repository.

Start IDM with the configuration for the ScriptedSQL sample:

/path/to/openidm/startup.sh -p samples/scripted-sql-with-mysql

Run the custom script (samples/scripted-sql-with-mysql/tools/ResetDatabaseScript.groovy) to reset the database and populate it with sample data.

You can run the script again, at any point, to reset the database.

curl \
--header "X-OpenIDM-Username: openidm-admin" \
--header "X-OpenIDM-Password: openidm-admin" \
--header "Accept-API-Version: resource=1.0" \
--request POST \
"http://localhost:8080/openidm/system/hrdb?_action=script&scriptId=ResetDatabase"
{
  "actions": [
    {
      "result": "Database reset successful."
    }
  ]
}

The hrdb database should now be populated with sample data.

Review the contents of the database:

mysql -u root -p
Enter password:
mysql > use hrdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql > select * from users;
----------------------------------------------------------------------...
| id | uid    | password     | firstname | lastname | fullname      | email  ...
+------------------------------------------------------------------------...
|  1 | bob    | e38ad2149... | Bob       | Fleming  | Bob Fleming   | Bob.Fle...
|  2 | rowley | 2aa60a8ff... | Rowley    | Birkin   | Rowley Birkin | Rowley....
|  3 | louis  | 1119cfd37... | Louis     | Balfour  | Louis Balfour | Louis.B...
|  4 | john   | a1d7584da... | John      | Smith    | John Smith    | John.Sm...
|  5 | jdoe   | edba955d0... | John      | Doe      | John Doe      | John.Do...
+-----------------------------------------------------------------+-------...
5 rows in set (0.00 sec)

The passwords in the above output are SHA-1 hashed because they cannot be read into IDM as cleartext. The SHA-1 Hash function is used for compatibility reasons. Use a more secure algorithm in a production database.

Reconcile the hrdb database with the managed user repository.
1. To reconcile the repository by using the Administration UI:
  1. Log in to the admin UI at the URL https://localhost:8443/admin as the default administrative user (openidm-admin) with password openidm-admin.
  2. Select Configure > Mappings.
    
    The Mappings page shows two mappings, one from the hrdb database to the IDM managed user repository (managed/user), and one in the opposite direction.
  3. Click Reconcile on the first mapping (systemHrdb_managedUser).
2. To reconcile the repository by using the command-line, launch the reconciliation operation with the following command:
  curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request POST \ "http://localhost:8080/openidm/recon?_action=recon&mapping=systemHrdb_managedUser&waitForCompletion=true" { "state": "SUCCESS", "_id": "f3c618aa-cc3b-49ed-9a3a-00b012db2513" }
The reconciliation operation creates the five users from the MySQL database in the IDM repository.

Retrieve the list of users from the repository.

To retrieve the users in the repository from the admin UI:
1. Select Manage > User to display the User List.
  
  The five users from the hrdb database have been reconciled to the OpenIDM repository.
2. To retrieve the details of a specific user, click that user entry.

To retrieve the users from the repository by using the command-line, query the IDs in the repository as follows:

curl \
--header "X-OpenIDM-Username: openidm-admin" \
--header "X-OpenIDM-Password: openidm-admin" \
--header "Accept-API-Version: resource=1.0" \
--request GET \
"http://localhost:8080/openidm/managed/user?_queryId=query-all-ids"
{
  "result": [
    {
      "_id": "1b379e4d-3b8d-47e7-93d5-a72c4b483e39",
      "_rev": "000000002d93e471"
    },
    {
      "_id": "a658f751-d6e9-4b5d-af56-071a9b05c3af",
      "_rev": "000000003c83d48a"
    },
    {
      "_id": "5b31027b-09f8-4c7f-abfa-c6bc86ae3943",
      "_rev": "00000000b042e559"
    },
    {
      "_id": "1b3f6b06-1752-4c40-ba34-51d30b184b9d",
      "_rev": "0000000092bdda6d"
    },
    {
      "_id": "9c62f0d2-47e2-4fc5-89d1-b50b782b1022",
      "_rev": "0000000025cdd3c6"
    }
  ],
  "resultCount": 5,
  "pagedResultsCookie": null,
  "totalPagedResultsPolicy": "NONE",
  "totalPagedResults": -1,
  "remainingPagedResults": -1
}

To retrieve a complete user record, query the userName of the individual user entry. The following query returns the record for the user Rowley Birkin:

curl \
--header "X-OpenIDM-Username: openidm-admin" \
--header "X-OpenIDM-Password: openidm-admin" \
--header "Accept-API-Version: resource=1.0" \
--request GET \
"http://localhost:8080/openidm/managed/user/?_queryId=for-userName&uid=rowley"
"result": [
    {
      "_id": "1b379e4d-3b8d-47e7-93d5-a72c4b483e39",
      "_rev": "000000002d93e471",
      "mail": "Rowley.Birkin@example.com",
      "userName": "rowley",
      "sn": "Birkin",
      "organization": "SALES",
      "givenName": "Rowley",
      "cars": [
        {
          "year": "2013",
          "make": "BMW",
          "model": "328ci"
        },
        {
          "year": "2010",
          "make": "Lexus",
          "model": "ES300"
        }
      ],
      "accountStatus": "active",
      "effectiveRoles": [],
      "effectiveAssignments": []
    }
  ],
  ...
}

Regardless of how you have retrieved Rowley Birkin’s entry, note the cars property in this user’s entry. This property demonstrates a complex object, stored in JSON format in the user entry, as a list that contains multiple objects. In the MySQL database, the car table joins to the users table through a cars.users_id column. The Groovy scripts read this data from MySQL and repackage it in a way that IDM can understand. With support for complex objects, the data is passed through to IDM as a list of car objects. Data is synchronized from IDM to MySQL in the same way. Complex objects can also be nested to any depth.

Group membership (not demonstrated here) is maintained with a traditional "join table" in MySQL (groups_users). IDM does not maintain group membership in this way, so the Groovy scripts do the work to translate membership between the two resources.

Test the event hooks

This sample uses the onCreate and onUpdate hooks to log messages when a user is created or updated in the external database.

The sample’s conf/sync.json file defines these event hooks as follows:

...
    {
        "name" : "managedUser_systemHrdb",
        "source" : "managed/user",
        "target" : "system/hrdb/account",
        "links" : "systemHrdb_managedUser",
        "correlationQuery" : {
            "type" : "text/javascript",
            "source" : "({'_queryFilter': 'uid eq \"' + source.userName + '\"'});"
        },
        "onCreate" : {
            "type" : "text/javascript",
            "source" : "logger.info(\"Creating new user in external repo\")"
        },
        "onUpdate" : {
            "type" : "text/javascript",
            "source" : "logger.info(\"Updating existing user in external repo\")"
        },
...

Using these event hooks, IDM logs a message when a user is created or updated in the external database. In this sample, the script source is included in the mapping. However, a script can also be called from an external file. For more information about event hooks, see Script triggers.

To test the event hooks, create a new managed user as follows:

curl \
--header "X-OpenIDM-Username: openidm-admin" \
--header "X-OpenIDM-Password: openidm-admin" \
--header "Accept-API-Version: resource=1.0" \
--header "Content-Type: application/json" \
--request POST \
--data '{
    "mail":"fdoe@example.com",
    "sn":"Doe",
    "telephoneNumber":"555-1234",
    "userName":"fdoe",
    "givenName":"Felicitas",
    "description":"Felicitas Doe",
    "displayName":"fdoe"}' \
"http://localhost:8080/openidm/managed/user?_action=create"
{
  "_id": "2e5e9748-77e6-4019-90e1-abe9ab897343",
  "_rev": "0000000015b2d4ba",
  "mail": "fdoe@example.com",
  "sn": "Doe",
  "telephoneNumber": "555-1234",
  "userName": "fdoe",
  "givenName": "Felicitas",
  "description": "Felicitas Doe",
  "displayName": "fdoe",
  "accountStatus": "active",
  "effectiveRoles": [],
  "effectiveAssignments": []
}

The implicit synchronization between the managed user repository and the HRDB database creates that user in the database automatically.

Check the latest log file at path/to/openidm/logs/openidm0.log.0. You should see the following message at the end of the log:

INFO: Creating new user in external repo

Query the new user entry in the HRDB database:

curl \
--header "X-OpenIDM-Username: openidm-admin" \
--header "X-OpenIDM-Password: openidm-admin" \
--header "Accept-API-Version: resource=1.0" \
--request GET \
"http://localhost:8080/openidm/system/hrdb/account?_queryFilter=uid+eq+'fdoe'"
{
  "result": [
    {
      "_id": "6",
      "cars": [],
      "firstName": "Felicitas",
      "uid": "fdoe",
      "lastName": "Doe",
      "organization": "IDM",
      "fullName": "Felicitas Doe",
      "email": "fdoe@example.com"
    }
  ],
  …
}

Update fdoe’s entry in the HRDB database with a patch request. The following request updates the user’s organization field:

curl \
--header "X-OpenIDM-Username: openidm-admin" \
--header "X-OpenIDM-Password: openidm-admin" \
--header "Accept-API-Version: resource=1.0" \
--header "Content-Type: application/json" \
--request PATCH \
--data '[ {
   "operation" : "replace",
   "field" : "organization",
   "value" : "example.com"
} ]' \
"http://localhost:8080/openidm/system/hrdb/account/6"
{
  "_id": "6",
  "cars": [],
  "firstName": "Felicitas",
  "uid": "fdoe",
  "lastName": "Doe",
  "organization": "example.com",
  "fullName": "Felicitas Doe",
  "email": "fdoe@example.com"
}

Note that this update does not reference the onUpdate script hook so this change is not logged in openidm0.log.0.

Run the sample with paging

All ICF connectors from version 1.4 onwards support the use of paging parameters to restrict query results. The following command indicates that only two records should be returned (_pageSize=2) and that the records should be sorted according to their timestamp and _id (_sortKeys=timestamp,_id). Including the timestamp in the sort ensures that, as you page through the set, changes to records that have already been visited are not lost. Instead, those records are pushed onto the last page:

curl \
--header "X-OpenIDM-Username: openidm-admin" \
--header "X-OpenIDM-Password: openidm-admin" \
--header "Accept-API-Version: resource=1.0" \
--request GET \
"http://localhost:8080/openidm/system/hrdb/account?_queryFilter=true&_pageSize=2&_sortKeys=timestamp,_id"
{
  "result": [
    {
      "_id": "1",
      "firstName": "Bob",
      "cars": [
        {
          "year": "1979",
          "make": "Ford",
          "model": "Pinto"
        }
      ],
      "fullName": "Bob Fleming",
      "email": "Bob.Fleming@example.com",
      "uid": "bob",
      "lastName": "Fleming",
      "organization": "HR"
    },
    {
      "_id": "2",
      "firstName": "Rowley",
      "cars": [
        {
          "year": "2013",
          "make": "BMW",
          "model": "328ci"
        }
      ],
      "fullName": "Rowley Birkin",
      "email": "Rowley.Birkin@example.com",
      "uid": "rowley",
      "lastName": "Birkin",
      "organization": "SALES"
    }
  ],
  "resultCount": 2,
  "pagedResultsCookie": "2018-04-05+16%3A30%3A22.0%2C2",
  "totalPagedResultsPolicy": "NONE",
  "totalPagedResults": -1,
  "remainingPagedResults": -1
}

The pagedResultsCookie is used by the server to keep track of the position in the search results. You can ignore the "remainingPagedResults": -1 in the output. The real value of this property is not returned because the scripts that the connector uses do not do any counting of the records in the resource.

Using the pagedResultsCookie from the previous step, run a similar query, to retrieve the following set of records in the database:

curl \
--header "X-OpenIDM-Username: openidm-admin" \
--header "X-OpenIDM-Password: openidm-admin" \
--header "Accept-API-Version: resource=1.0" \
--request GET \
"http://localhost:8080/openidm/system/hrdb/account?_queryId=query-all-ids&_pageSize=2&_sortKeys=timestamp,_id&_pagedResultsCookie=2018-04-05+16%3A30%3A22.0%2C2"
{
  "result": [
    {
      "_id": "3",
    },
    {
      "_id": "4",
    }
  ],
  "resultCount": 2,
  "pagedResultsCookie": "2018-04-05+16%3A30%3A22.0%2C4",
  "totalPagedResultsPolicy": "NONE",
  "totalPagedResults": -1,
  "remainingPagedResults": -1
}

For more information about paging, see Page Query Results.

IDM 7.2.1

Connect to a MySQL database with ScriptedSQL

Configure the external MySQL database

Run the sample

Test the event hooks

Run the sample with paging