CS Utils Importer - JDBC driven import

JDBC driven import

JDBC driven import means import from a relational database, which is accessed via JDBC. The importer provides a generic possibility to import data.

Importing data from a JDBC source works in three steps:

In the first step, you'll be creating a so-called database description. The initial version of this file will be created automatically. However, manual editing is typically required: For example, the database description contains all tables and columns. In most cases, you'll be restricting import to a certain subset. Likewise, you'll notice that the initial database description contains table and column names all uppercased. (That's how the databases JDBC layer represents them.) Editing the database description allows to have names better readable for humans, like camel cased.
In the second step, you'll be creating a meta model file, suitable for the meta model importer. This step takes the manually edited database description from the first step as an additional input.
The final step will creating a model file, which can be used by the model importer. The manually edited database description from the first step will again be used as additional input.

Database description

The database description is best explained by an example. Suggest a database, which is created from the following statements:

CREATE TABLE TestDbOrg (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(64) NOT NULL,
    description VARCHAR(128),
    some_attribute VARCHAR(32)
);
CREATE TABLE TestDbUser (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(64),
    employeeOf INTEGER NOT NULL,
    managedBy INTEGER,
    CONSTRAINT FK_USER_EMPLOYEE_OF FOREIGN KEY (employeeOf) REFERENCES TestDbOrg (id),
    CONSTRAINT FK_USER_MANAGED_BY FOREIGN KEY (managedBy) REFERENCES TestDbUser (id)
);

The JDBC exporter will read this database, and create a file like the following:

<jdbcExport locale="de_DE"
  xmlns="http://namespaces.csutils.sf.net/importer/jdbcExporter/1.0.0">
  <table registryObjectType="{http://namespaces.example.com/model}TESTDBORG"
    name="TESTDBORG" identification="ID">
    <column name="ID" />
    <column name="NAME" />
    <column name="DESCRIPTION" />
    <column name="SOME_ATTRIBUTE"/>
  </table>
  <table registryObjectType="{http://namespaces.example.com/model}TESTDBUSER"
    name="TESTDBUSER" identification="ID">
    <column name="ID"/>
    <column name="NAME"/>
    <column targetType="TESTDBORG" name="EMPLOYEEOF"
      associationType="EMPLOYEEOF" />
    <column targetType="TESTDBUSER" name="MANAGEDBY"
      associationType="MANAGEDBY" />
  </table>
</jdbcExport>

As you can see, this file is basically a list of tables and columns. However, a little bit more has been done: The JDBC exporter has noticed the primary key columns and used them to create an attribute identification="ID". What's even more, the JDBC exporter has also detected the foreign keys and used them for a description of relationships: The columns EMPLOYEEOF and MANAGEDBY are containing attributes targetType and associationType. The target type is obviously referencing to the proper table.

On the other hand, there are a few things, which you would like to change.

The suggested registry object type is something like {http://namespaces.example.com/model}TESTDBORG. In other words, this is the asset type of objects, which are created as import from the given table. Even if you are ready to accept the TESTDBORG as the asset types name, most people would reject the proposed namespace URI. So, the first thing to change will be replacing the registry object type with something like {http://namespaces.mycompany.com/CentraSite}TestDbOrg.
The column names are ID, NAME, and so on. This is how JDBC returns them. Without any changes, these will become the attribute names. In other words, chances are you will replace these with id, name, and so on. This works fine, if the attribute name and the column name are the same, except for case. If you don't like attribute names like some_attribute and prefer this to be camel cases, you need to change the entire line from
```
    <column name="SOME_ATTRIBUTE"/>
```
to
```
    <column name="SOME_ATTRIBUTE" attributeName="someAttribute"/>
```
Assets should have proper names and descriptions. These are created by adding nameColumn and descriptionColumn attributes to the table element:
```
  <table registryObjectType="{http://namespaces.mycompany.com/CentraSite}TestDbOrg"
    name="TESTDBORG" identification="ID" nameColumn="NAME"
    descriptionColumn="DESCRIPTION">
```
This means, that the columns NAME and DESCRIPTION are not to be imported as slots and should rather be mapped to the asset types name, and description.

For relationships, you would likely see another association type. This is done by editing the respective associationType attribute.

To sum it all up, your database description will likely end to look like this:

<jdbcExport locale="de_DE"
  xmlns="http://namespaces.csutils.sf.net/importer/jdbcExporter/1.0.0">
  <table registryObjectType="{http://namespaces.mycompany.com/CentraSite}TestDbOrg"
    name="TESTDBORG" identification="id" nameColumn="name"
    descriptionColumn="description">
    <column name="id" />
    <column name="name" />
    <column name="description" />
    <column name="some_attribute" attributeName="someAttribute"/>
  </table>
  <table registryObjectType="{http://namespaces.mycompany.com/CentraSite}TestDbUser"
    name="TESTDBUSER" identification="id" nameColumn="name">
    <column name="id" />
    <column name="name" />
    <column targetType="TESTDBORG" name="employeeOf"
      associationType="employeeOf" />
    <column targetType="TESTDBUSER" name="managedBy"
      associationType="managedBy" />
  </table>
</jdbcExport>

Creating the meta model

As a second step, you need to create the meta model, which will then be used to create the necessary custom asset types in CentraSite. This is done by rerunning the importer in mode metaModel, using the database description as input. The result will look like this:

<roMetaModel xmlns="http://namespaces.csutils.sf.net/model">
  <roTypes>
    <roType qName="{http://namespaces.mycompany.com/CentraSite}TestDbOrg">
      <attributes>
        <attribute type="integer" name="id" />
        <attribute type="string" name="someAttribute"
          minOccurs="0" />
      </attributes>
    </roType>
    <roType qName="{http://namespaces.mycompany.com/CentraSite}TestDbUser">
      <attributes>
        <attribute type="integer" name="id" />
        <relation associationType="employeeOf" name="employeeOf">
          <targetTypes>
            <targetType qName="{http://namespaces.mycompany.com/CentraSite}TestDbOrg" />
          </targetTypes>
        </relation>
        <relation associationType="managedBy" name="managedBy"
          minOccurs="0">
          <targetTypes>
            <targetType
              qName="{http://namespaces.mycompany.com/CentraSite}TestDbUser" />
          </targetTypes>
        </relation>
      </attributes>
    </roType>
  </roTypes>
</roMetaModel>

Creating the model

Finally, after creating the asset types, you'll be creating the so-called model file, which will then be used to import the data into CentraSite. This is done by rerunning the JDBC exporter in mode model, using the database description as input. The result will look like this. The file can be imported into CentraSite using the model importer.

<model xmlns="http://namespaces.csutils.sf.net/importer/model/1.0.0">
  <identifications>
    <uniqueSlot attributeName="id"
      assetType="{http://namespaces.example.com/model}TestDbOrg" />
    <uniqueSlot attributeName="id"
      assetType="{http://namespaces.example.com/model}TestDbUser" />
  </identifications>
  <assets>
    <asset>
      <names>
        <name locale="de_DE">Org 1</name>
      </names>
      <descriptions>
        <description locale="de_DE">Description of Org 1
        </description>
      </descriptions>
      <attributes>
        <slot name="id">
          <value>1</value>
        </slot>
        <slot name="someAttribute">
          <value>Some Attr 1</value>
        </slot>
      </attributes>
    </asset>
    <asset>
      <names>
        <name locale="de_DE">Org 2</name>
      </names>
      <descriptions>
        <description locale="de_DE">Description of Org 2
        </description>
      </descriptions>
      <attributes>
        <slot name="id">
          <value>2</value>
        </slot>
        <slot name="someAttribute">
          <value>Some Attr 2</value>
        </slot>
      </attributes>
    </asset>
    <asset>
      <names>
        <name locale="de_DE">User 1</name>
      </names>
      <attributes>
        <slot name="id">
          <value>3</value>
        </slot>
      </attributes>
    </asset>
    <asset>
      <names>
        <name locale="de_DE">User 2</name>
      </names>
      <attributes>
        <slot name="id">
          <value>4</value>
        </slot>
      </attributes>
    </asset>
  </assets>
</model>

CentraSite Utilities Importer

Project Documentation

JDBC driven import

Database description

Creating the meta model

Creating the model