Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
devel:documentation:systems:dev:scripted-jdbc-connector [2019/02/26 09:14]
kotisovam [Using Scripted JDBC connector] snippet moved to admin guide 		devel:documentation:systems:dev:scripted-jdbc-connector [2022/08/23 16:10] (current)
apeterova path to the scripts for appliance
Line 1: Line 1:
 ====== Using Scripted JDBC connector ====== ====== Using Scripted JDBC connector ======
 +
 {{tag> jdbc connector}} {{tag> jdbc connector}}
- 
  
 ===== Configuration ===== ===== Configuration =====
Line 10: Line 10:
   * set an inline script (recommended only for deployments without FS)   * set an inline script (recommended only for deployments without FS)
  
-Another useful setting is "Reload scripts on every execution", which mainly helps during the development stage. This option should be turned off in production.+If you run CzechIdM on appliance, you can place the scripts inside /data/volumes/czechidm/data/ folder, create e.g. a folder /data/volumes/czechidm/data/ourSystemScripts. Then the script path configured in CzechIdM will be e.g. /opt/czechidm/data/ourSystemScripts/search.groovy. 
 + 
 +Another useful setting is "Reload script on execution" (reloadScriptOnExecution), which mainly helps during the development stage. This option should be turned off in production, because reloading (recompiling) the Groovy scripts takes time and memory. However, when you need to upgrade the script and this option is turned off, then you have to restart IdM, otherwise the new version of the script wouldn't be loaded. Don't turn on the option "Reload script on execution" only temporarily, because it may not preserve the loaded newer version of the script, after you turn the option off again! 
 + 
 +==== Pooling configuration ==== 
 + 
 +We recommed using the [[.:system-mapping#connector_pool_configuration|connector pool]] when connectiong systems with this connector. This will make connecting to the datasource more effective. Also, this connector has a suspected memory leak when compiling groovy scripts repeatedly, which is avoided if connector pooling is enabled and "Reload script on execution" is disabled (see above)
  
 ===== Schema attributes ===== ===== Schema attributes =====
  
-While retrieving account data from target system (the SEARCH operation), the connector requires us to set both %%__%%NAME%%__%% and %%__%%UID%%__%% attribute unconditionally. Both **must** be Strings. Therefore you are required to do this manually in your SEARCH script. Following is an example of retrieving user data:+While retrieving account data from target system (the SEARCH operation), the connector requires us to set both <nowiki>__</nowiki>NAME<nowiki>__</nowiki> and <nowiki>__</nowiki>UID<nowiki>__</nowiki> attribute unconditionally. Both **must**  be Strings. Therefore you are required to do this manually in your SEARCH script. Following is an example of retrieving user data:
  
 <code groovy> <code groovy>
 +import groovy.sql.Sql
 +import groovy.transform.Field
 +
 @Field UID_ATTR = "id_column" @Field UID_ATTR = "id_column"
 @Field TABLE_NAME = "abc_users_table" @Field TABLE_NAME = "abc_users_table"
Line 22: Line 31:
 def sql = new Sql(connection) def sql = new Sql(connection)
 def result = [] def result = []
-def select = "SELECT * FROM $TABLE_NAME ${getWhere(query)}"+String select = "SELECT * FROM $TABLE_NAME ${getWhere(query)}"
 sql.eachRow(select, { row -> sql.eachRow(select, { row ->
     def res = [:]     def res = [:]
Line 32: Line 41:
 }) })
 return result return result
 +
 +
 </code> </code>
-All retrieved attributes must be **a list of maps** (look for ''%%result%%'' and ''%%res%%'' objects in the example above). This is crucial for SEARCH connector method to work properly. 
  
-Another 'catch' the connector has is that the %%__%%NAME%%__%% is a required input of the CREATE operation. Since it is not expected that you have any column called "%%__%%NAME%%__%%" in your database schema, this leaves you pretty much two options how to handle the attribute in your installation:+All retrieved attributes must be **a list of maps**  (look for ''<nowiki>result</nowiki>''  and ''<nowiki>res</nowiki>''  objects in the example above). This is crucial for SEARCH connector method to work properly. 
 + 
 +Another 'catch' the connector has is that the <nowiki>__</nowiki>NAME<nowiki>__</nowiki> is a required input of the CREATE operation. Since it is not expected that you have any column called "<nowiki>__</nowiki>NAME<nowiki>__</nowiki>" in your database schema, this leaves you pretty much two options how to handle the attribute in your installation:
  
   - ignore the attribute in your script   - ignore the attribute in your script
   - always rename the attribute in the script   - always rename the attribute in the script
  
-The first option expects that you have an additional attribute %%__%%NAME%%__%% in your system mapping in CzechIdM, which maps to the exactly same value as your mapping system identifier. Lets say we have a user's table USERS with 1 columns: LOGIN - identifier. In such case we create a new system in CzechIdM and while configuring the system schema, we create **two** attributes:+The first option expects that you have an additional attribute <nowiki>__</nowiki>NAME<nowiki>__</nowiki> in your system mapping in CzechIdM, which maps to the exactly same value as your mapping system identifier. Lets say we have a user's table USERS with 1 columns: LOGIN - identifier. In such case we create a new system in CzechIdM and while configuring the system schema, we create **two**  attributes:
  
   * LOGIN   * LOGIN
-  * %%__%%NAME%%__%%+  * <nowiki>__</nowiki>NAME<nowiki>__</nowiki>
  
-Then in mapping we check the LOGIN attribute as identifier and choose however we want to fill its value. The only difference with %%__%%NAME%%__%% attributes is that we will not check the identifier checkbox, otherwise it stays the same.+Then in mapping we check the LOGIN attribute as identifier and choose however we want to fill its value. The only difference with <nowiki>__</nowiki>NAME<nowiki>__</nowiki> attributes is that we will not check the identifier checkbox, otherwise it stays the same.
  
-The second option requires that all of your CRUD scripts handle the %%__%%NAME%%__%% attribute in a special way. For example you can rename it to your ID column name. This way of handling the identifier is rather straightforward, but causes high pollution of your JDBC scripts with non-reusable code.+The second option requires that all of your CRUD scripts handle the <nowiki>__</nowiki>NAME<nowiki>__</nowiki> attribute in a special way. For example you can rename it to your ID column name. This way of handling the identifier is rather straightforward, but causes high pollution of your JDBC scripts with non-reusable code.
  
 ===== Using groovy SQL ===== ===== Using groovy SQL =====
  
-Groovy has a powerful yet simple mechanism of querying databases through JDBC implemented in the ''%%groovy.sql.Sql%%'' class. However one has to be really careful and precise while using groovy's Sql with identity connector framework's (ICF) Attribute objects, because of its loose typing system.+Groovy has a powerful yet simple mechanism of querying databases through JDBC implemented in the ''<nowiki>groovy.sql.Sql</nowiki>''  class. However one has to be really careful and precise while using groovy's Sql with identity connector framework's (ICF) Attribute objects, because of its loose typing system.
  
 All values of ICF Attribute are of type java.lang.Object. To put it simply, typing does not exist here and the developer must handle it manually. If you only send simple attributes such as Strings, there is probably no need to worry about your queries. However mixing types together may cause unpredictable results. All values of ICF Attribute are of type java.lang.Object. To put it simply, typing does not exist here and the developer must handle it manually. If you only send simple attributes such as Strings, there is probably no need to worry about your queries. However mixing types together may cause unpredictable results.
Line 61: Line 73:
 def retrieved = sql.firstRow("SELECT * FROM USERS WHERE ID = ? LIMIT 1", [id]) def retrieved = sql.firstRow("SELECT * FROM USERS WHERE ID = ? LIMIT 1", [id])
 println retrieved println retrieved
 +
 +
 </code> </code>
-Now everything should work just fine and we get our desired row printed out. But what if ''%%id%%'' is a String? Meaning what if we pass ''%%def id = '123'%%''? Here we get some unpredictable behaviour. When using PostgreSQL, the query will deliberately fail with an exception. But on MySQL everything **works just fine**. 
  
-The danger with ICF Attributes is that these contain a //value// field - a list of values the Attribute carries. But if you accidentally call ''%%myString.value%%'' on a String parameter, you get an object of type ''%%class [C%%''. The funny thing is that your query will still pass on MySQL, but will not return anything (most probably). Therefore always check your types while using groovy Sql!+Now everything should work just fine and we get our desired row printed out. But what if ''<nowiki>id</nowiki>''  is a String? Meaning what if we pass ''<nowiki>def id = '123'</nowiki>''? Here we get some unpredictable behaviour. When using PostgreSQL, the query will deliberately fail with an exception. But on MySQL everything **works just fine**. 
 + 
 +The danger with ICF Attributes is that these contain a //value//  field - a list of values the Attribute carries. But if you accidentally call ''<nowiki>myString.value</nowiki>''  on a String parameter, you get an object of type ''<nowiki>class [C</nowiki>''. The funny thing is that your query will still pass on MySQL, but will not return anything (most probably). Therefore always check your types while using groovy Sql!
  
 ===== List of input variables ===== ===== List of input variables =====
Line 77: Line 92:
   * objectClass: a String describing the Object class   * objectClass: a String describing the Object class
   * id: The entry identifier (OpenICF "Name" atribute. (most often matches the uid)   * id: The entry identifier (OpenICF "Name" atribute. (most often matches the uid)
-  * attributes: an Attribute Map, containg the ''%%String%%'' attribute name as a key and the ''%%List%%'' attribute value(s) as value.+  * attributes: an Attribute Map, containg the ''<nowiki>String</nowiki>''  attribute name as a key and the ''<nowiki>List</nowiki>''  attribute value(s) as value.
   * password: password string, clear text   * password: password string, clear text
   * options: a handler to the OperationOptions Map   * options: a handler to the OperationOptions Map
Line 101: Line 116:
   * objectClass: a String describing the Object class   * objectClass: a String describing the Object class
   * uid: a String representing the entry uid   * uid: a String representing the entry uid
-  * attributes: an Attribute Map, containg the ''%%String%%'' attribute name as a key and the ''%%List%%'' attribute value(s) as value.+  * attributes: an Attribute Map, containg the ''<nowiki>String</nowiki>''  attribute name as a key and the ''<nowiki>List</nowiki>''  attribute value(s) as value.
   * password: password string, clear text (only for UPDATE)   * password: password string, clear text (only for UPDATE)
   * options: a handler to the OperationOptions Map   * options: a handler to the OperationOptions Map
Line 113: Line 128:
   * options: a handler to the OperationOptions Map   * options: a handler to the OperationOptions Map
   * uid: String for the unique id that specifies the object to delete   * uid: String for the unique id that specifies the object to delete
 +
 +
  • by kotisovam