So there you are having created a work of beauty in Sparx Enterprise Architect that models all of your databases, and then the fly lands in the ointment. There’s a new version of “stuff” coming down the pipeline and you need to update the model or things are going to be getting stale (and nobody likes stale database models).
So how are we going to do this? Well that’s the point of this post – there’s some nice semi-automated ways of doing this in Sparx EA – but there are some gotchas which I hope to provide resolutions for.
First lets assume for sanity’s sake that someone has kindly provided you with a version of the database with all the changes present and that you’re not a masochist who enjoys making a lot of manual changes.
Also a caveat – this post deals with Sparx EA v14 and SQL server databases so future changes may make some of work-arounds listed here redundant.
I’m assuming here that you (the lucky reader) have a reasonable understanding of Sparx EA and that you have an existing database design model with the relevant ODBC connections already present (or are quite capable of setting it up).
Going for the full enchilada
We can use the Import DB Schema from ODBC approach and choose to synchronise objects. Definitely works – but it will process all of the source tables – not ideal if you have a large database and only a small amount have changed.
To get started doing this first navigate to your desired <<Database>> model in the Project Broswer and select it. From there you have two ways to initiate the Import dialog: 1) Using the main menu bar choose Code > DDL > Import a DB Schema, or 2) Right-click
This can be started by using either of the following approaches:
- Project Browser – Navigate to your desired <<Database>> model and select it. Then from the main menu bar choose Code > DDL > Import a DB Schema, or
- Follow approach 1 but instead of navigating the main menu bar right-click on the <<Database>> model and choose to Open Package in Database Builder. From the Database Builder right-click on the highlighted database and choose Import DB schema from ODBC …
I’m not going to go over all of the different options for the spawned dialog, except to point out the Synchronization options. By default this will be set as Import as New Objects which we don’t want since this will produce copies of any existing tables in the model. Instead we want to select the other option Synchronize existing classes. Now when the import process runs it will still evaluate all of the possible source tables, but only create those that don’t already exist in your database model (it will still modify existing tables if they differ from that in your model). The downside to this approach is that it will take a while if you have a lot of tables in your source database.
Just some of the toppings thanks
Another option is to make use of the Database Builder tool and its Show Differences/Database Compare functionality to only bring through and work on the added/modified objects.
First navigate to your desired <<Database>> model and open it in the Database Builder by following approach 2 detailed in the previous section. Then right-click on your highlighted database and choose Show Differences (if you want to customise this you can choose Show Differences with Options). After some behind the scenes magic you should end up with the Database Compare tab being populated. From here you simply identify the objects you want to import (by choosing the appropriate Action) and click on the Import from ODBC button at the bottom.
This works nicely and only process the differences BUT (and its a big but) there’s an issue when the new database objects are created in the Sparx EA database model. Instead of the expected Element profile (Type = “Table” and Stereotype = “table”) the new Elements have the profile (Type = “Class” and Stereotype = “Table”).
Itself not a big deal – you get a slightly different icon in the package explorer and the object is still able to be used in an Entity Relationship Diagram. The subtle and importance difference is with the database schema that owns the table.
For the Element Type = “Class” the schema ends up being stored as the Element Owner – so what? you ask, well the rub comes when you want to generate the DDL for the table object. You won’t get any information about the table schema (even if it exists) and this can be quite irritating when you discover that all of your new tables are going straight into the default database schema thanks to the automagically generated sql script.
So how do we resolve this? According to Sparx EA support it’s simply a matter of changing the Stereotype property from “table” to “EAUML::table” for the Element. This can be done manually (if you’ve got a small number of tables), or via an automation script that loops through the table elements and corrects the value.
Alas I have no script example for this – you can work on that as a homework exercise.
Until de next time – bork, bork, bork. Brent.
