Create a JDBC Data View For Example.com's Payroll Database

1. Create a JDBC data source for the payroll database.

   $ dpconf create-jdbc-data-source -b payrollsqldb \ -B jdbc:payrollsqldb:payrollsql://localhost/ \ -J file://payrollsqldb.jar \ -S org.payrollsqldb.jdbcDriver payroll-src

2. Configure the JDBC data source with the properties of the SQL database.

   $ dpconf set-jdbc-data-source-prop payroll-src \ db-user:proxy db-pwd-file:password-file-location/myPasswordFile

3. Enable the JDBC data source.

   $ dpconf set-jdbc-data-source-prop payroll-src is-enabled:true

4. Create a JDBC data source pool for the payroll database.

   $ dpconf create-jdbc-data-source-pool payroll-pool

5. Attach the payroll data source to the data source pool.

   $ dpconf attach-jdbc-data-source payroll-pool payroll-src

6. Create a JDBC data view for the payroll database, with a virtual DN of o=payroll.

   $ dpconf create-jdbc-data-view payroll-view payroll-pool o=payroll

7. Create a JDBC table for each table in the SQL database.

   $ dpconf create-jdbc-table jdbc-employee employee $ dpconf create-jdbc-table jdbc-salary salary

8. Add a JDBC attribute for each column in the SQL tables.

   $ dpconf add-jdbc-attr jdbc-employee eid employee_id $ dpconf add-jdbc-attr jdbc-employee first firstname $ dpconf add-jdbc-attr jdbc-employee last lastname $ dpconf add-jdbc-attr jdbc-employee description description $ dpconf add-jdbc-attr jdbc-employee spouse spousename $ dpconf add-jdbc-attr jdbc-salary salary salary $ dpconf add-jdbc-attr jdbc-salary social ssn

9. Specify which attributes can be viewed and which can be written, through the JDBC data view.

   $ dpconf set-jdbc-data-view-prop payroll-view \ viewable-attr:eid \ viewable-attr:first \ viewable-attr:last \ viewable-attr:desc \ viewable-attr:spouse \ viewable-attr:salary \ viewable-attr:social $ dpconf set-jdbc-data-view-prop payroll-view \ writable-attr:eid \ writable-attr:first \ writable-attr:last \ writable-attr:description \ writable-attr:spouse \ writable-attr:salary \ writable-attr:social

10. Create a JDBC object class that maps to an LDAP object class.

    The following command creates an object class that maps to the LDAP person object class. The object class specifies that the employee table should be used as the primary table, and that the salary table should be used as the secondary table. The eid attribute should be used to construct the DN.

    $ dpconf create-jdbc-object-class payroll-view \ person jdbc-employee jdbc-salary eid

11. Create a filter join rule on the secondary table that specifies how data from the secondary table should be linked to data from the primary table.

    The following join rule specifies that data should be joined based on the employee_id attribute.

    $ dpconf set-jdbc-table-prop jdbc-salary \ filter-join-rule:employee_id=\${employee.employee_id}

12. Create a super class on the JDBC object class.

    $ dpconf set-jdbc-object-class-prop payroll-view person super-class:extensibleObject