Please enable JavaScript to view this site.

Altova UModel 2020 Enterprise Edition

Transforming UML Models

Example: Transform Access Database to SQLite

Scroll Home Prev Top Next More

This example illustrates how to convert a database model from one database kind to another. Specifically, it illustrates how to read the structure of a Microsoft Access database into a UML model, and then merge it into an existing SQLite database. After completing this example, the structure of the source Access database will be recreated in the target SQLite database. Note that the Microsoft Access and SQLite databases are provided here only as an example; the same mechanism described here applies when converting other database kinds supported by UModel (see UModel and Databases).

 

This example uses the following files available in the C:\Users\...\Documents\Altova\UModel2020 \UModelExamples\Tutorial directory:

 

Nanonull.mdb - The source Microsoft Access database

Nanonull.sqlite - The target SQLite database

 

Note:        Before proceeding, it is recommended to create a backup of the sample Nanonull.sqlite database file, because its contents will be modified by the instructions below.

 

Step 1: Import the source database into UModel

1.On the Project menu, click Import SQL Database, and follow the wizard steps to connect to the source Microsoft Access database (Nanonull.mdb) . For more information, see Connecting to a Database.

2.When prompted to create a name for the data source, give it a descriptive name (for example, "SourceDatabase").

um_transform_db_01

3.Select the database objects to be imported into the model, and click Finish.

um_transform_db_02

Notice that a "SourceDatabase" package becomes available in the Model Tree window under the "Root" package.

um_transform_db_03

 

Step 2: Import the target database into UModel

1.On the Project menu, click Import SQL Database, and follow the wizard steps to connect to the target SQLite database (Nanonull.sqlite).

2.When prompted to create a name for the data source, give it a descriptive name (in this example, "TargetDatabase").

um_transform_db_04

3.Select the database objects to be imported into the model, and click Next.

um_transform_db_05

4.When prompted to select a target package, select the Import in new Package check box, and click Finish.

um_transform_db_06

At this stage, a new "TargetDatabase" package is added in the Model Tree window under the "Root" package.

um_transform_db_07

 

Step 3: Run the model transformation from source to target database

1.On the Project menu, click Model Transformation.

2.On the "Select Source Package" dialog box, select "SourceDatabase / Nanonull" as package, and click Next.

um_transform_db_08

3.On the "Select Target Package" dialog box, select "TargetDatabase / main" as package, and click Next.

um_transform_db_09

4.On the "Model Transformation Details" dialog box, select DB to DB as transformation type, and click Next.

um_transform_db_10

5.On the "Type Mapping" dialog box, review the data types and change them as required. For this example, we chose to map only some Microsoft Access-specific data types that do not exist in SQLite, as shown below:

um_transform_db_11

As a rule of thumb, ensure that the left column contains a data type compatible with the source database, and the right column contains a data type compatible with the target database. To add or delete new mappings, use the Append, Insert, and Delete buttons.

6.Click Finish. On the message box which opens, click OK.

um_transform_db_12

A dependency diagram is generated, where you can review (and modify if required) any of the previously defined settings, including the data type mappings. For the purpose of this example, leave the default settings as is.

um_transform_db_13

 

Step 4: Merge program code from UModel project

1.On the Project menu, click Merge Program Code from UModel Project.

2.Leave the default settings as is, and click OK.

um_transform_db_14

An update database script is generated and displayed in a dialog box as shown below. You can now execute the script directly in UModel, or save it to a file. If you have installed Altova DatabaseSpy, you can also open and execute the script in DatabaseSpy, which provides a more dedicated database administration interface.

um_transform_db_15

It is strongly recommended to review and, if necessary, modify the generated script before running it against the target database.

 

If a source database contains object names (for example, indexes or foreign keys) that are not unique at database level, the database update script will fail to execute successfully. For example, a Microsoft Access database could contain multiple indexes with the same name. Unless the target database accepts duplicate names for indexes, you will need to edit the update script so that all required object names are unique.

 

You may also need to update the script to modify the size of columns according to the requirements of the target database.

 

After you execute the script (either directly in UModel or externally in a tool such as DatabaseSpy), the required tables, columns, as well as indexes and key constraints will be recreated in the target SQLite database. Note that SQLite (version 3.6.19) accepts the names of the foreign key constraints supplied by the SQL statement but does not provide a way to retrieve them from the database (in particular, foreign key constraints are retrieved with some arbitrary name, not their actual name). To ensure that your database model displays the actual object names as they are provided by the database, perform a reverse update of the model from the database. To do this, run the menu command Project | Merge UModel Project from Program Code. The model will then be updated to show object names as they are provided by the database.

© 2019 Altova GmbH