"Seegrid will be due for a migration to confluence on the 1st of August. Any update on or after the 1st of August will NOT be migrated"

Database to UML importer

Contents

Description

This tool provides a means to load an existing Platform Specific Model (PSM) into a UML model, performing the basic generalisations required to create a Platform Independent Model.

This involves mapping to PI data types, and identifying implementation patterns such as foreign key constraints as relationships.

There is no intention to support full object-relational mapping paradigms here, but to allow an existing implementation to be faithfully transcribed into a UML model to support:

  • Creating a more powerful model through refactoring and reconciling one or more imported schemas
  • Mapping the implementation to a more general model.

The refactoring process can potentially create the mapping as a side effect. Automating the capture of this mapping is the subject of additional experimentation.

Requirements

  • Access an existing database schema (particularly SQL Server, Oracle and PostgresSQL) into a
  • Transform database schema into UML model, using a platform-independent idiom. (Platform independence is necessary since we want to reference the content, not interact with the original database). The target idiom is currently ISO19100, however this could be made configurable if required.
  • Keep all the database schema information in the UML model, including database name, schema name, and foreign key name.
  • Convert foreign key relationships into UML class relationships: A foreign key column should be transform into an association owner end ( Target Role ), but not a class attribute. The association Target Role name is the foreign key column name.
  • Automatically apply ISO19100 profile to the newly generated UML model.
  • Convert database data type to the ISO19100 data type ( ISO19103 Conceptual Schema Language, ISO19107 Spatial Schema )

Options considered

  • Option 1: Use Enterprise Architect built in “Import DB schema from ODBC source” function.
  • Option 2: Develop a new Enterprise Architect Add-in function.
  • Option 3: Use a third party tool

Option 1 has some limitations:
  1. The EA ( Enterprise Architect) will apply its own database profile to the imported model. This profile doesn’t meet our requirement. For example: It stores the primary/foreign key information in an UML function which has “PK/FK” stereotype applied, and the name of the primary key is a random generated name. Figure 1 is a class generated by EA based on a imported database schema.

image001.png

Figure 1: A class generated by EA’s “Import DB Schema” function

  1. We cannot configure EA to apply an ISO 19100 profile (or any other chosen target profile) to the imported model automatically.
  2. The EA’s “Import DB Schema” function simply uses the original database data type as the UML attribute data type. It generates a Platform Specific Model in UML notation.
  3. The EA’s “Import DB Schema” function requires an ODBC connection.

Option 3, using a third party tool, leads us to similar issues over lack of an obvious PIM idiom, and no tools to support the ISO19100 idiom required for key spatio-temporal data application domains.

We took the second option. A new function has been created in the AHGF Modelling toolset to achieve our requirements. Currently, this function supports SQL Server, Oracle and PostgresSQL databases.

How to use this function

  1. Select the “Database to UML” item from the “AHGF Modelling Toolset” Add-in.

image002.png

  1. Select the database type you want to connect to and provide required connection details. Click on the “Connect” button, after you provide all the required details.

image005.png

  1. After you clicked on the “Connect” button, the application will connect to the selected database and retrieve all the user tables. It displays all the tables in the “Working Table Selection” window.

image007.png

  1. Select the tables to import

  1. Specify the data type mapping file.

image009.png

This data type mapping file is a user-definable XML file, however a library of options is pre-loaded. It tells the application how to map a database data type to an UML data type. For example, To map the Oracle data type “varchar2” to ISO “CharacterString”, the following xml element need to be added in the mapping file.

  <DataType>
      <Name>varchar2</Name>
      <MapTo>CharacterString</MapTo> 
  </DataType>

The following predefined data type mapping files are available:

  1. Click on “Run” button to execute the process. A new model will be generated and become visible in the Project Browser.

image011.png

A class diagram will be generated automatically:

Class_Diagram.png

Outputs

(show reports generated and explain line be line here..)

Development status:

Please see JIRA task tracker for this component:

[[JIRA component overview][http://jira.toolkit.net.au/browse/AHGF/component/10405]

Future research

Further work needs to be undertaken to understand the limits of the approach - it is not expected that this tool can automate all possible model patterns - in particular it may be desirable to identify extension tables as specialisations, sql views may need to be imported into different packages, recursive data patterns may be hard to detect.

We need to collate a suite of test cases to ensure regression stability as we try to cope with more complex database patterns.

Troubleshooting problems

What might go wrong and how do you determine and fix?

 
Topic revision: r5 - 15 Oct 2010, UnknownUser
 

Current license: All material on this collaboration platform is licensed under a Creative Commons Attribution 3.0 Australia Licence (CC BY 3.0).