Connect to a MySQL Database With ScriptedSQL

Download MySQL Connector/J version 5.1 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/

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.jdbc.Driver",
    "url" : "jdbc:mysql://localhost:3306/hrdb?serverTimezone=UTC",
    ...

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;

Start IDM with the configuration for the ScriptedSQL sample:

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

Run the custom script described in the previous section 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)
     

Reconcile the hrdb database with the managed user repository.

• 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).

• 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.