172N/ASample 3 - Scripted SQL

172N/A-----------------------

172N/ACopyright (c) 2012 ForgeRock AS

172N/AThis work is licensed under a Creative Commons Attribution-

172N/ANonCommercial-NoDerivs 3.0 Unported License. See

172N/A

172N/AThis sample demonstrates an example configuration for a scripted SQL

172N/Aconnector, a connector that communicates with a database through configurable

172N/ASQL scripts. This example requires a fresh installation of OpenIDM.

172N/A

172N/AFor documentation pertaining to this example see:

172N/Ahttp://openidm.forgerock.org/doc/install-guide/index.html#more-sample3

172N/A

172N/ATo try the example, follow these steps.

172N/A

172N/A 1. Copy the MySQL Connector/J .jar to the OpenIDM bundle/ directory.

172N/A

172N/A $ cp mysql-connector-java-5.1.18-bin.jar /path/to/openidm/bundle/

172N/A

172N/A 3. Set up MySQL to listen on localhost:3306, connecting as root:password.

172N/A

172N/A 4. Import the sample data definition language file into MySQL.

172N/A

172N/A $ ./bin/mysql -u root -p < /path/to/openidm/samples/sample3/data/sample_HR_DB.mysql

172N/A Enter password:

172N/A $ ./bin/mysql -u root -p

172N/A Enter password:

172N/A mysql> use HRDB;

172N/A mysql> select * from Users;

172N/A +----+--------+----------+-----------+----------+---------------+---------------------------+--------------+---------------------+

172N/A | id | uid | password | firstname | lastname | fullname | email | organization | timestamp |

172N/A +----+--------+----------+-----------+----------+---------------+---------------------------+--------------+---------------------+

172N/A | 1 | bob | password | Bob | Fleming | Bob Fleming | Bob.Fleming@example.com | HR | 2012-02-20 12:16:44 |

172N/A | 2 | rowley | password | Rowley | Birkin | Rowley Birkin | Rowley.Birkin@example.com | SALES | 2012-02-20 12:16:44 |

172N/A | 3 | louis | password | Louis | Balfour | Louis Balfour | Louis.Balfour@example.com | SALES | 2012-02-20 12:16:44 |

172N/A | 4 | john | password | John | Smith | John Smith | John.Smith@example.com | SUPPORT | 2012-02-20 12:16:44 |

172N/A | 5 | jdoe | password | John | Doe | John Doe | John.Does@example.com | ENG | 2012-02-20 12:16:44 |

172N/A +----+--------+----------+-----------+----------+---------------+---------------------------+--------------+---------------------+

172N/A 5 rows in set (0.01 sec)

172N/A

172N/A mysql> quit

172N/A Bye

172N/A

172N/A 5. Start OpenIDM with the configuration for sample 3.

172N/A

172N/A $ /path/to/openidm/startup.sh -p samples/sample3

172N/A ...

172N/A -> bob

172N/A rowley

172N/A louis

172N/A john

172N/A jdoe

172N/A

172N/A 6. Run reconciliation:

172N/A

172N/A $ curl --header "X-OpenIDM-Username: openidm-admin" --header "X-OpenIDM-Password: openidm-admin" --request POST "http://localhost:8080/openidm/recon?_action=recon&mapping=systemHrdb_managedUser"

172N/A

172N/A 7. Retrieve the list of users from OpenIDM's internal repository:

172N/A

172N/A $ curl --header "X-OpenIDM-Username: openidm-admin" --header "X-OpenIDM-Password: openidm-admin" --request GET "http://localhost:8080/openidm/managed/user/?_queryId=query-all-ids"

172N/A {

172N/A "query-time-ms": 0,

172N/A "result": [

172N/A {

172N/A "_id": "9d1a92bf-aa88-4b48-b607-ec6d95e7b178",

172N/A "_rev": "0"

172N/A },

172N/A {

172N/A "_id": "d781ba76-33ba-4581-8cda-8ed9c6d4903d",

172N/A "_rev": "0"

172N/A },

172N/A {

172N/A "_id": "40b7ca4f-f112-4878-ae6b-6aa97503fac6",

172N/A "_rev": "0"

172N/A },

172N/A {

172N/A "_id": "3afb15e5-8713-4ba4-a464-fa9c5f952bff",

172N/A "_rev": "0"

172N/A },

172N/A {

172N/A "_id": "5474ff66-31af-4097-b1fb-a394f9a60d95",

172N/A "_rev": "0"

172N/A }

172N/A ],

172N/A "conversion-time-ms": 0

172N/A }

172N/A

172N/A In your terminal the response appears all on one line.

172N/A

172N/A 8. Retrieve an individual user, using an ID value from the previous GET:

172N/A

172N/A $ curl --header "X-OpenIDM-Username: openidm-admin" --header "X-OpenIDM-Password: openidm-admin" --request GET "http://localhost:8080/openidm/managed/user/5474ff66-31af-4097-b1fb-a394f9a60d95"

172N/A {

172N/A "familyName": "Doe",

172N/A "userName": "jdoe",

172N/A "givenName": "John",

172N/A "_id": "5474ff66-31af-4097-b1fb-a394f9a60d95",

172N/A "_rev": "0",

172N/A "email": [

172N/A "John.Does@example.com"

172N/A ]

172N/A }

172N/A

172N/A Again, in your terminal the response appears all on one line.

172N/A