README revision 4b4c3a2028eea921136be5b4717c69c074b4982e
842ae4bd224140319ae7feec1872b93dfd491143fieldingSample 3 - Scripted SQL
842ae4bd224140319ae7feec1872b93dfd491143fielding-----------------------
842ae4bd224140319ae7feec1872b93dfd491143fieldingCopyright (c) 2012 ForgeRock AS
842ae4bd224140319ae7feec1872b93dfd491143fieldingThis work is licensed under a Creative Commons Attribution-
842ae4bd224140319ae7feec1872b93dfd491143fieldingNonCommercial-NoDerivs 3.0 Unported License. See
71da3cca78eea6010f89b139ecadb79e6d213c4fndThis sample demonstrates an example configuration for a scripted SQL
b0fb330a8581c8bfab5e523084f9f39264a52b12gsteinconnector, a connector that communicates with a database through configurable
71da3cca78eea6010f89b139ecadb79e6d213c4fndSQL scripts. This example requires a fresh installation of OpenIDM.
71da3cca78eea6010f89b139ecadb79e6d213c4fndFor documentation pertaining to this example see:
71da3cca78eea6010f89b139ecadb79e6d213c4fndhttp://openidm.forgerock.org/doc/install-guide/index.html#more-sample3
b0fb330a8581c8bfab5e523084f9f39264a52b12gsteinTo try the example, follow these steps.
f4c310fd2555c6faca1f980f00b161eadb089023gstein 1. Copy the MySQL Connector/J .jar to the OpenIDM bundle/ directory.
f4c310fd2555c6faca1f980f00b161eadb089023gstein $ cp mysql-connector-java-5.1.18-bin.jar /path/to/openidm/bundle/
f4c310fd2555c6faca1f980f00b161eadb089023gstein 3. Set up MySQL to listen on localhost:3306, connecting as root:password.
952023a04a2f9d51553babd9094fb857f1c97548trawick 4. Import the sample data definition language file into MySQL.
1b21d7b3d97def358b2e923655edeb16613a1c31gstein $ ./bin/mysql -u root -p < /path/to/openidm/samples/sample3/data/sample_HR_DB.mysql
1b21d7b3d97def358b2e923655edeb16613a1c31gstein Enter password:
1b21d7b3d97def358b2e923655edeb16613a1c31gstein $ ./bin/mysql -u root -p
f4c310fd2555c6faca1f980f00b161eadb089023gstein Enter password:
f4c310fd2555c6faca1f980f00b161eadb089023gstein mysql> use HRDB;
f4c310fd2555c6faca1f980f00b161eadb089023gstein mysql> select * from Users;
f4c310fd2555c6faca1f980f00b161eadb089023gstein +----+--------+----------+-----------+----------+---------------+---------------------------+--------------+---------------------+
f4c310fd2555c6faca1f980f00b161eadb089023gstein | id | uid | password | firstname | lastname | fullname | email | organization | timestamp |
f4c310fd2555c6faca1f980f00b161eadb089023gstein +----+--------+----------+-----------+----------+---------------+---------------------------+--------------+---------------------+
f4c310fd2555c6faca1f980f00b161eadb089023gstein | 1 | bob | password | Bob | Fleming | Bob Fleming | Bob.Fleming@example.com | HR | 2012-02-20 12:16:44 |
f4c310fd2555c6faca1f980f00b161eadb089023gstein | 2 | rowley | password | Rowley | Birkin | Rowley Birkin | Rowley.Birkin@example.com | SALES | 2012-02-20 12:16:44 |
99d46a23c6eac800f327b29f8009f7d7da986230trawick | 3 | louis | password | Louis | Balfour | Louis Balfour | Louis.Balfour@example.com | SALES | 2012-02-20 12:16:44 |
99d46a23c6eac800f327b29f8009f7d7da986230trawick | 4 | john | password | John | Smith | John Smith | John.Smith@example.com | SUPPORT | 2012-02-20 12:16:44 |
f4c310fd2555c6faca1f980f00b161eadb089023gstein | 5 | jdoe | password | John | Doe | John Doe | John.Does@example.com | ENG | 2012-02-20 12:16:44 |
1ccd992d37d62c8cb2056126f2234f64ec189bfddougm +----+--------+----------+-----------+----------+---------------+---------------------------+--------------+---------------------+
f4c310fd2555c6faca1f980f00b161eadb089023gstein 5 rows in set (0.01 sec)
f4c310fd2555c6faca1f980f00b161eadb089023gstein mysql> quit
f4c310fd2555c6faca1f980f00b161eadb089023gstein 5. Start OpenIDM with the configuration for sample 3.
5b03ba47ff7225cacb131f14b019332af27da960gstein 6. Run reconciliation:
99d46a23c6eac800f327b29f8009f7d7da986230trawick $ 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"
5b03ba47ff7225cacb131f14b019332af27da960gstein 7. Retrieve the list of users from OpenIDM's internal repository:
5b03ba47ff7225cacb131f14b019332af27da960gstein $ 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"
5b03ba47ff7225cacb131f14b019332af27da960gstein "query-time-ms": 0,
5b03ba47ff7225cacb131f14b019332af27da960gstein "result": [
e8f95a682820a599fe41b22977010636be5c2717jim "_id": "9d1a92bf-aa88-4b48-b607-ec6d95e7b178",
e8f95a682820a599fe41b22977010636be5c2717jim "_rev": "0"
1ccd992d37d62c8cb2056126f2234f64ec189bfddougm "_id": "d781ba76-33ba-4581-8cda-8ed9c6d4903d",
f4c310fd2555c6faca1f980f00b161eadb089023gstein "_rev": "0"
f4c310fd2555c6faca1f980f00b161eadb089023gstein "_id": "40b7ca4f-f112-4878-ae6b-6aa97503fac6",
f4c310fd2555c6faca1f980f00b161eadb089023gstein "_rev": "0"
f4c310fd2555c6faca1f980f00b161eadb089023gstein "_id": "3afb15e5-8713-4ba4-a464-fa9c5f952bff",
f4c310fd2555c6faca1f980f00b161eadb089023gstein "_rev": "0"
691db92094897494d6c31326108da20088bc175etrawick "_id": "5474ff66-31af-4097-b1fb-a394f9a60d95",
691db92094897494d6c31326108da20088bc175etrawick "_rev": "0"
691db92094897494d6c31326108da20088bc175etrawick "conversion-time-ms": 0
691db92094897494d6c31326108da20088bc175etrawick In your terminal the response appears all on one line.
691db92094897494d6c31326108da20088bc175etrawick 8. Retrieve an individual user, using an ID value from the previous GET:
691db92094897494d6c31326108da20088bc175etrawick $ 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"
691db92094897494d6c31326108da20088bc175etrawick "familyName": "Doe",
691db92094897494d6c31326108da20088bc175etrawick "userName": "jdoe",
691db92094897494d6c31326108da20088bc175etrawick "givenName": "John",
691db92094897494d6c31326108da20088bc175etrawick "_id": "5474ff66-31af-4097-b1fb-a394f9a60d95",
691db92094897494d6c31326108da20088bc175etrawick "_rev": "0",
691db92094897494d6c31326108da20088bc175etrawick "John.Does@example.com"
98e9c4a310bb623ff788680f88b6bd200ff36a24wrowe Again, in your terminal the response appears all on one line.