Fork me on GitHub

Unification

Melati is based upon holding rich metadata about database objects. This metadata is held in three locations and three different formats: the database metadata tables, the JDBC metadata and generated java files.

The unique feature of Melati is that it does a three way unification between these three potentially conflicting sets of metadata.

What is Unification

We are talking about Unification as in Prolog, ie a procedure for making sets of variables with attributes equivalent.

If you have two lists of assigned variables then all those that occur in both and are equivalent unify, any which only occur in one are copied to the other and so unify. If a variable occurs in both but does not have equivalent attributes then unification fails.

In a three way unification one needs in theory to unify A with B, unify the resultant (A or B) with C and then unify the resultant (B or C) with A again, though we will see below that this last step is not required in our case.

Metadata

A PoemDatabase has an Active online catalog based on the relational model that is to say it has system tables called tableInfo and columnInfo which are used to store metadata about the tables and fields in the database.

All JDBC databases contain and can supply metadata about their contents through the DatabaseMetaData class of the JDBC API.

A Poem Table can support all SQL datatypes as columns, but Poem holds more metadata about a column than SQL does, for instance how to display the field.

Unification Rules

In the abstract, Prolog, sense unification between two trees is bi-directional, with no precedence, but our case is limited:

  1. Metadata in the database tables takes precedence over metadata in the java code.
  2. The java code is compiled so cannot change, however see Iterating below.
  3. The existing jdbc metadata cannot be changed only added to.

The equivalence function between Poem's datatypes and SQL datatypes (called canRepresent) determines whether a JDBC column is allowed to unify with a columnn in the columnInfo table.

Example

If you point melati at an empty database it will unify with a PoemDatabase creating tables and populating the metadata tables with results shown below:

Unifying with empty database
Poem Info JDBC
TableInfoTable tableinfo TABLEINFO
ColumnInfoTable columninfo COLUMNINFO
TableCategoryTable tablecategory TABLECATEGORY

If you point it at your database, then the Poem metadata tables are created and the information about them and your table is stored in them (the metadata tables hold information about themselves as per Codd's rule).

Unifying with populated database
Poem Info JDBC
yourtable YOURTABLE
TableInfoTable tableinfo TABLEINFO
ColumnInfoTable columninfo COLUMNINFO
TableCategoryTable tablecategory TABLECATEGORY

Use case - Generating classes from a database

You have a database, which is normalised and has a numeric primary key called id in each table and you want to represent this in java.

The unification process takes the following steps:

  1. Look for database metadata tables (tableinfo, columninfo, tablecategory) - not found, so created
  2. Use the java definitions of a PoemDatabase to create tables the user management and settings table and populate tableinfo and columninfo with details of these tables.
  3. Read the JDBC metadata to populate tableinfo and columninfo with details of your tables.
You can now view your database in the Admin system. You can generate a Data Structure Definition file using the Admin system DSD Report from which you can generate your java files.

Use case - Generating a database from a data definition

You have defined the database structure you want in a DSD file.

Depending upon your DBMS you may need to create an empty database, then point Melati at that database and the unification process will create the tables themselves and populate the tableinfo and columninfo tables for you. If your application requires that some tables are populated with default values, type tables for example, then you can add an ensure method to your table initialisation method to create these values if absent.

Iterating

Once you have a functioning Poem database you can create a DSD using the Admin system DSD Report.

This can be fed back into the start of the process, enabling you to add meta data, such as verbose descriptions of fields and tables, using the Admin interface, and then recreate your data definition and hence your java source.

This feature is particularly powerful in a situation where you are capturing requirements with a customer, as you can add and delete tables and columns, reorder them and change their characteristics on the fly in real time, then export your DSD and capture the metadata in your java files.