EMF/Teneo/rdb2ecore

Introduction

The past years several times the requirement has been posted on the EMF newsgroup to be able to translate a rdb schema into ecore. This wiki page serves as the starting point for developing a new Teneo component to support translation of rdb schemas to ecore.

This page is mainly used as a place to put thoughts and ideas, it reflects an ongoing thought process.

Translating RDB concepts to Ecore

This section discusses how RDB concepts can be translated to ecore.

Table

The most simple case: a table translates to an EClass.

The following structures should be detected automatically:

• inheritance structures: as it is supported by hibernate for example, however for most rdb schemas one can make the statements that inheritance is not modeled in the rdb schema. Support for this seems to be an advanced feature.
• embedded components: can be identified by checking if foreign keys are one-to-one (foreign keys with unique constraints)
• join tables which reflect bidirectional many-to-many associations

Primary Key Columns

A primary key column can translate to an ID eattribute in ecore. Although the ID in ecore is stricter than in a rdb. In ecore the ID must be unique over all types, in a rdb a PK column value needs to be unique only in the table itself.

Composite Primary Key Column

TBD

Foreign Key Columns

The general statement is that a foreign key column translates to an EReference. The following other concepts are relevant:

• a foreign key with cascade delete translates to a containment relation (on the other side of the foreign key)
• a foreign key can be used to always create a corresponding EReference with on the other side (in the refered-to entity).
• a unique constraint can be used to identify if the ereference is a many or single ereference (on the other side).

Primitive Type Columns

Primitive typed columns (string, numeric, etc.) translate directly to an EAttribute. Next to naming is the the main thing to identify the correct EDataType (see next thing).

Datatypes

It can make sense to develop an EPackage with EDataTypes which match jdbc/sql primitive types. This EPackage can then be used when converting primitive typed columns to ecore EAttributes.

Also a research topic is if type definitions in the rdb are available and can be used to generate EDatatypes.

Naming

Naming is an important topic as in practically all cases the names of tables/columns are not useful/less meaningful in ecore and java. This means that the conversion logic should allow plugging in an own naming strategy. Different options could be useful:

• use an annotation/xml like approach, an xml file is supplied by the user which overrides specific naming
• allow a custom naming strategy implementation, this class is used during the conversion/translation process.
• support a wizard like approach were users can edit/change names (see next topic)

User interface/Wizard

It could be great to have a supporting UI for the tool, to support setting custom names for tables/columns, to flag bi-directional relations etc. The result of manual changes/settings should be persisted so that people can re-do the wizard with their previous settings maintained.

Technical Architecture/Approach

The flow to translate a rdb schema to an ecore will go through the following steps:

1. The database schema is read through JDBC metadata (to try to be database independent)
2. The database schema is converted into an ecore model (a new one to have a more or less simple model of a RDB Schema in ecore)
3. The RDB model is enriched with annotations to control how the rdb model is translated to ecore later
4. The rdb model together with its annotations are translated to ecore

Some of the steps above are so-called model-2-model transformations. The question is if we can use m2m transformation tools or that it is easier to just use 'plain-old' java.

A possible project we can use for reading the database model is the apache ddl utils project. The risk with this project is that it does not seem to be actively maintained.