Transcript Integrating database applications with the JDBC driver for IDM

Integrating database applications
with the JDBC driver for IDM
E. Axel Larsson
Drew University
20 July 2005
The JDBC driver for IDM vs.
other IDM drivers.




Generic driver
Works at the database level rather
than the app level.
You will be doing development in the
application space.
Varying comfort levels amongst
application vendors.
Supported databases.

What we’ve done at Drew


Others


Microsoft SQL Server, Oracle, MySQL
DB2, Sybase ASE, Postgres, others.
Database requirements


Triggers, views, foreign key constraints
What about MySQL?
Types of applications

Extend your IDM system beyond user
account provisioning in different
directory services.

Customer/patron databases



Helpdesk (at Drew – Supportworks)
Campus card systems (at Drew – CS Gold)
LAMP apps that don’t integrate with a
directory.

Discussion forums, online communities, open
source portals (at Drew – vBulletin forums)
How it works.

Subscriber

eDir events translated into INSERT,
UPDATE, DELETE SQL statements.


Not an arbitrary collection of tables provided
by the app, but a set of tables or views
specifically created for IDM.
Publisher


JDBC shim 1.x & 2.x – Triggers, event log
JDBC shim 2.x – Triggerless publication
Database schema
requirements

Two models

“Direct” synchronization



Views created in between the app database
tables and IDM.
Views must be updateable if the subscriber
channel is to be used (INSTEAD OF triggers)
“Indirect” synchronization


Database tables created in between the app
tables and IDM.
Duplication of data from IDM tables to app
tables (triggers)
“Direct” method




1 view for each object class.
View exposes all attributes included in the
driver filter.
Primary/foreign key hints (pk_ prefixes, etc.)
Subscriber channel:


View must be updateable (INSTEAD OF triggers)
Publisher channel:


Event log (JDBC 1.x, 2.x)
Triggerless (JDBC 2.x only)
“Indirect” method


Intermediate staging tables in between
IDM and the application.
1 “parent” table for each object class.



Parent table contains all single valued
attributes.
Must have a primary key.
1 “child” table for each MV attribute.


Two columns, foreign key to parent table,
and unconstrained value column.
Foreign key constraint must be explicitly
defined.
“Indirect” method (cont’d)

Related attributes



Map to DN type attributes in eDir.
Use foreign key references.
Data synchronization.


Triggers to move data between app
tables and IDM tables.
Event log triggers for publication.
Publisher event log

Event types








1 – insert field
2 – update field
3 – update field, removing current values.
4 – delete row
5 – insert row (query for values)
6 – update row (query for values)
7 – insert field (query for values)
8 – update field (query for values)
Which should I use?

App is primarily a publisher of data.



App is primarily a subscriber.


Indirect may be easier, because INSTEAD OF
triggers can be a hassle.
Other considerations




Direct sync is less invasive.
Triggerless publication in JDBC 2.x.
Isolation
Delegation of development responsibilities.
App vendor comfort levels / support.
I use the indirect method.
What about MySQL (4.x)?

No triggers, no views



Syncing directly to app tables.
App database schema must conform to
IDM needs.
MyISAM type tables, no foreign key
support.

No MV attributes.
IDM driver configuration

Unique IDs.


Correspond to the primary key in the parent
tables (indirect method) or the pk_ column in
views (direct method)
Primary key generation




Provided in operation (eDir attribute)
Provided by driver (SELECT MAX…)
Stored procedure / function in the DB.
Associations


KEY=pkvalue,TABLE=table,SCHEMA=schema
i.e. PIK=0779204,TABLE=idm_patrons,
SCHEMA=drewadmin
IDM driver configuration

Driver filters.



Object class = parent table or view
Attribute = view column, parent table
column, or child table column.
Example:


Single-value, parent-table column:
(eDir) Surname = (database) NAME_LAST
Multi-value, child table column:
(eDir) drewPersonRegisteredTerms =
(database) IDM_REG_TERMS.TERM
IDM driver configuration

Executing arbitrary SQL from policies.

Uses



Manage database accounts. (This is what
Novell’s preconfigured drivers do)
Work around some MySQL problems.
How


Add jdbc namespace to policy.
Append jdbc:statement elements to
documents being sent to the app.