Training:Reverse engineer a database

Reverse engineering is what we call the process that takes a given database and creates an UML model from it

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

in this case we use the MusicStoreDb from a MicrosoftSample:

You start by Analyzing the database:

And then you “Go!” – be aware of 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:

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

And that is enough information to run the model against the exact 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).

And for the Attribute we must also say that it is DbAssigned; that forces ECO to omit setting the primary key on first save, and also forces ECO 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 actually enough to be able 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 – that calculates what needs to be changed 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 that 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”:

4And once we have done this one-time operation, that 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 just to see what it could do.

The reversing mechanism runs thru 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 can help to understand what the information is all about.

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

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

Real life experiences

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

Why is that? While the Reverse mechanism of MDriven use the Schema of the database to draw conclusions about the model – the Schema might not be a complete truth

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

Developers can use tables that have primary key 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 common place and MDriven must be able to do adapt even in these situations.

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

You can also add Associations this way:

Hard work to look up missing details by browsing the database schema and comparing with the model.

Aided definition of Persistence Mapping

The latest versions of MDriven has a way to browse the database Schema so you do not need to switch tools. Again right-click the Package, choose Functions, 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 you 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 suggest what you can do with the picked columns:

In this case we can add an Association.

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

So this tool does the same thing that you can do manually explained earlier in this article – but it saves you the trouble of using some SQL-Browser, copy and paste SQL-Names; Clicking and editing Persistence mapping attributes of the model.