Example: Transform Access Database to SQLite
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\UModel2024 \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").
3.Select the database objects to be imported into the model, and click Finish.
Notice that a "SourceDatabase" package becomes available in the Model Tree window under the "Root" package.
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").
3.Select the database objects to be imported into the model, and click Next.
4.When prompted to select a target package, select the Import in new Package check box, and click Finish.
At this stage, a new "TargetDatabase" package is added in the Model Tree window under the "Root" package.
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.
3.On the "Select Target Package" dialog box, select "TargetDatabase / main" as package, and click Next.
4.On the "Model Transformation Details" dialog box, select DB to DB as transformation type, and click Next.
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:
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.
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.
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.
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.
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.