Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision Last revision Both sides next revision | ||
devel:documentation:systems:dev:scripted-jdbc-connector [2018/03/22 12:46] stloukalp created |
devel:documentation:systems:dev:scripted-jdbc-connector [2022/08/23 15:49] apeterova Info about pooling configuration due to memory leak |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== Using Scripted JDBC connector ====== | ||
+ | |||
+ | {{tag> jdbc connector}} | ||
+ | |||
+ | ===== Configuration ===== | ||
+ | |||
+ | The configuration is done using standard JDBC setting, i.e. setting a proper URL template, JDBC driver etc. We are mainly interested in configuring the CRUD operation scripts. You can either: | ||
+ | |||
+ | * set script path on filesystem (recommended) | ||
+ | * set an inline script (recommended only for deployments without FS) | ||
+ | |||
+ | Another useful setting is " | ||
+ | |||
+ | ==== Pooling configuration ==== | ||
+ | |||
+ | We recommed using the [[.: | ||
+ | |||
+ | ===== Schema attributes ===== | ||
+ | |||
+ | While retrieving account data from target system (the SEARCH operation), the connector requires us to set both < | ||
+ | |||
+ | <code groovy> | ||
+ | import groovy.sql.Sql | ||
+ | import groovy.transform.Field | ||
+ | |||
+ | @Field UID_ATTR = " | ||
+ | @Field TABLE_NAME = " | ||
+ | |||
+ | def sql = new Sql(connection) | ||
+ | def result = [] | ||
+ | String select = " | ||
+ | sql.eachRow(select, | ||
+ | def res = [:] | ||
+ | res.put(" | ||
+ | res.put(" | ||
+ | row.getMetaData().collect({ m -> m.columnName }) | ||
+ | .each({ column -> res.put(column, | ||
+ | result.add(res) | ||
+ | }) | ||
+ | return result | ||
+ | |||
+ | |||
+ | </ | ||
+ | |||
+ | All retrieved attributes must be **a list of maps** | ||
+ | |||
+ | Another ' | ||
+ | |||
+ | - ignore the attribute in your script | ||
+ | - always rename the attribute in the script | ||
+ | |||
+ | The first option expects that you have an additional attribute < | ||
+ | |||
+ | * LOGIN | ||
+ | * < | ||
+ | |||
+ | Then in mapping we check the LOGIN attribute as identifier and choose however we want to fill its value. The only difference with < | ||
+ | |||
+ | The second option requires that all of your CRUD scripts handle the < | ||
+ | |||
+ | ===== Using groovy SQL ===== | ||
+ | |||
+ | Groovy has a powerful yet simple mechanism of querying databases through JDBC implemented in the ''< | ||
+ | |||
+ | 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. | ||
+ | |||
+ | For example lets say we have a table USERS with columns ID (int) and LOGIN (varchar) and we want to select a row where ID = 123. So we will do something like this: | ||
+ | |||
+ | <code groovy> | ||
+ | def id = 123 | ||
+ | def retrieved = sql.firstRow(" | ||
+ | println retrieved | ||
+ | |||
+ | |||
+ | </ | ||
+ | |||
+ | Now everything should work just fine and we get our desired row printed out. But what if ''< | ||
+ | |||
+ | The danger with ICF Attributes is that these contain a // | ||
+ | |||
+ | ===== List of input variables ===== | ||
+ | |||
+ | Following is a list of input variables you can expect in JDBC scripts. | ||
+ | |||
+ | ==== CREATE script ==== | ||
+ | |||
+ | * connection: SQL connection | ||
+ | * action: String correponding to the action (" | ||
+ | * log: a handler to the Log facility | ||
+ | * objectClass: | ||
+ | * id: The entry identifier (OpenICF " | ||
+ | * attributes: an Attribute Map, containg the ''< | ||
+ | * password: password string, clear text | ||
+ | * options: a handler to the OperationOptions Map | ||
+ | |||
+ | ==== SEARCH script (GET) ==== | ||
+ | |||
+ | * connection: handler to the SQL connection | ||
+ | * objectClass: | ||
+ | * action: a string describing the action (" | ||
+ | * log: a handler to the Log facility | ||
+ | * options: a handler to the OperationOptions Map | ||
+ | * query: a handler to the Query Map | ||
+ | |||
+ | ==== UPDATE script ==== | ||
+ | |||
+ | * The connector sends us the following: | ||
+ | * connection : SQL connection | ||
+ | * action: String correponding to the action (UPDATE / ADD_ATTRIBUTE_VALUES / REMOVE_ATTRIBUTE_VALUES) | ||
+ | * UPDATE: For each input attribute, replace all of the current values of that attribute in the target object with the values of that attribute | ||
+ | * ADD_ATTRIBUTE_VALUES: | ||
+ | * REMOVE_ATTRIBUTE_VALUES: | ||
+ | * log: a handler to the Log facility | ||
+ | * objectClass: | ||
+ | * uid: a String representing the entry uid | ||
+ | * attributes: an Attribute Map, containg the ''< | ||
+ | * password: password string, clear text (only for UPDATE) | ||
+ | * options: a handler to the OperationOptions Map | ||
+ | |||
+ | ==== DELETE script ==== | ||
+ | |||
+ | * connection: handler to the SQL connection | ||
+ | * action: a string describing the action (" | ||
+ | * log: a handler to the Log facility | ||
+ | * objectClass: | ||
+ | * options: a handler to the OperationOptions Map | ||
+ | * uid: String for the unique id that specifies the object to delete | ||
+ | |||