Training:Reverse engineer a database

Reverse engineering is the process of taking a given database and creating a UML model from it.

Videos showing the process of reverse engineering a database to model: Documentation:Making sense of legacy data–DB Reverse

You just need to give a valid connection string to it.

In this case, we use the MusicStoreDb from a MicrosoftSample:

Start by analyzing the database:

And then you “Go!”. Be aware that this process will empty the package you started from and fill it with the stuff from the reverse engineer process:

Giving us this in the modlr tree:

I will drag these classes out on a diagram to get this:

That is enough information to run the model against the same database as we reversed it from:

Giving us a way to use the trusty AutoForms and EcoSpaceDebugger to look at the changeable data:

In this particular database, the RecordId, ArtistId, and so forth are of type int in the database and are designed with “Is Identity==true” in SqlExpress. Also the “Identity Increment” is set in SqlExpress.

The Reverse algorithm gave us a model with int Attributes for the ID – we want ECO to be able to use that Key scheme instead of the standard ECO_ID:

The PrimaryKey setting we got from the reversing process and the PrimaryKeyMapper I set to ”AutoInc” – an Eco-supplied primary key mapper with that name that handles the normal AutoIncrease behaviour of databases. (You can create your own named mappers in ECO for VisualStudio).

For the Attribute, we must also say that it is DbAssigned; that forces ECO to omit setting the primary key on the first save and to read the saved row back from the database to get the actual assigned key (which we need to use as a foreign key in related objects):

This is enough to enable you to create new objects from the ecospace debugger and have them inserted in the database (or from code or WECPOF or what have you). If you follow along in this brief and try to save a new Album, you will notice 2 SQLServer exceptions. Both Genre and Artist foreign keys are marked NOT NULL in the DB – handle this by assigning an Artist and a Genre to the new Album with AutoForms drag and drop.

Database Evolve

To use the Model Evolve mechanism – which calculates what needs to change in the database by comparing a new and old DB script – we have to allow ECO to write the (current) script to the database. If we do not do this, ECO will not have any “old” script to compare the new script with that ECO derives from your current model.

In ECO for VisualStudio, you can choose to store the script some other way by using the ORMappingProvider components in your EcoSpace – but in MDriven, you do not have any code, so you really must allow us to store it in the database. Note: this is only if you want to use the Database Evolve mechanism.

To write the Script to the Database, open up the prototyper once more by hitting “Run”:

Once you have done this one-time operation, which is only necessary for reversed databases (since standard eco databases have these from the start), we can do some model changes:

And “Run” and “Evolve”:

Looking in the SQL Script tab in the dialog above, we can see the expected script that ECO will send to the database:

BEGIN TRANSACTION;

/* Add column [Album].ReleaseDate */

ALTER TABLE [Album] ADD ReleaseDate DATETIME DEFAULT ’19000101′ NOT NULL;

COMMIT;

That wraps up this presentation of the Reverse (engineer) database function in MDriven.

MDriven for Massive Projects

New products, new regulations, new technology, new clients, new business areas, new employees – in short, everything is challenged by a relentless progressive attitude. The IT department needs to be agile to avoid becoming the heavy-set we-cannot-do-it-that-way part of the company that limits and slows evolution down.

Apply MDriven on the example database to see what it can do.

The reversing mechanism runs through the Schema of the database and creates Classes of the Tables, Attributes of Fields, and Associations of primary and foreign key pairs. We can now draw diagrams that help us understand what the information is all about.

And still have the overview that MDriven’s AutoDiagrams offer:

Since the information in the database is now explained in a model, I can create ViewModels that run and show the real data with WECPOF as explained here.

Real-life Experiences

Even if everything is just dandy as far as I can see, when comparing the database schema and Model, I notice that some things are missing. I find some missing associations, classes, and attributes.

Why is that? While the Reverse mechanism of MDriven uses the Schema of the database to conclude about the model – the Schema might not be a complete truth.

After all, developers can always join things in SQL even if those things do not have an explicit primary/foreign key definition.

Developers can use tables that have primary keys according to the Schema etc… In short, the Reverse mechanism is hampered by a crappy input.

Defining Persistence Mapping Manually

I suspect that the “Crappy input” fact is commonplace and MDriven must be able to adapt even in these situations.

Luckily it does; you can add missing classes manually, explaining what the Primary Key is in the properties:

You can also add Associations this way:

It is hard work to look up missing details by browsing the database schema and comparing it with the model.

Aided Definition of Persistence Mapping

The latest versions of MDriven have a way of browsing the database Schema so you do not need to switch tools. Again, right-click the Package, choose Functions, and Reverse Database:

Click Analyze db:

The schema shows up – if you click “Go!”, the process will start to clear your existing package content and fill it up with the stuff found in the Schema. You do not want to do that if you are largely happy with your model – just missing some details.

Instead, you can click the new tool “Reverse worker”:

This UI shows all the Tables in the DB. Select one Table and see all the columns in that Table. You can use the buttons “Use as PK(primary key) or Attribute” and “Use as FK(foreign key)” to pick columns:

If you then move on to step 4 “Find possible actions in the Model”, the tool suggests what you can do with the picked columns:

In this case, we can add an Association.

Depending on whether the Table is used by any of your Model classes or not, you can also get the option to add the Class:

This tool does the same thing you can do manually as explained earlier in this article – but it saves you the trouble of using some SQL-Browser, copying and pasting SQL-Names, or clicking and editing Persistence mapping attributes of the model.

Continued work on your model

When you are up and running with your reversed model it will probably not take long until you want to add new classes/tables to your database. To fully support standard MDriven models you will need to add a few administrative tables to your database as described here: The MDriven administrative database tables