Mittwoch, 10. Juli 2013

Abuse inheritance for embedding with SQL Developer

Creating relational models in SQL is often a very annoying job, because of the limited expressiveness of the relational model. Thanks to PostgreSQL it is possible to create inheritance relationships between different entities. But the object oriented world spurns more and more inheritance. Inheritance was invented to reuse code but after Barbara Liskov formulated her substitution principle most object oriented disciples bless embedding instead of inheritance. Evangelists like Google´s Go banish inheritance at all and allow only embedding.

Never the less Oracle´s SQL Developer does not know anything about embedding and provides only single inheritance. The following example shows how to use this for embedding. I would like to created objects in my database which timestamps. A "creatable" should have a create time stamp, a "deletable" should have an additional delete time stamp and a "modifyable" should add a third time stamp. This is the logical model:

The engineering strategy "one table per child" results in a relational model with only one table.

The attributes of all ancestors are merged into the table. Whenever I need the three time stamps I can specify the inheritance without the need to add them manually to the entity.

But one thing is still annoying. The columns are prefixed with the abstract table names, which leads to unnecessary redundancy in the name of the columns. I did not find a way to suppress this naming style but I found a way to fix the names by writing a custom transformation script. SQL Developer embeds Mozilla´s Rhino engine, which provides a way to manipulate the engineered model. The following script removes the table prefixes from the three columns.

function set_name (object, name)

tables = model.getTableSet().toArray();
for (var t = 0; t<tables.length;t++) {
    table = tables[t];
    tname = table.getName();
    columns = table.getElements();
    for (var c = 0; c < columns.length; c++) {
        column = columns[c];
        cname = column.getName();
        if (m = cname.match(/.+_((CREATE|DELETE|MODIFY)_TS)$/))
            set_name(column, m[1]);

Keine Kommentare: