Reverse engineer a database

Reverse engineering is what we call the process that takes a given database and turns it into a model driven “thing”.

image

 

You just need to give a valid connection string to it; in this case a use the MusicStoreDb from a MicrosoftSample:

image

You start by Analyzing the database:

image

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:

image

 

Giving us this in the modlr tree:

image

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

image

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

image

Giving us a way to use the trusty AutoForms and EcoSpaceDebugger to look at the data and change it if we want:

image

In this particular database the RecordId, ArtistId and sofort 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 – how is that done? Like this:

image 

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 behavior 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) :

image

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

So when you have reached this far you may have the need for changing the model – just like any developer you might get an uncontrollable urge to make the model look more like reality from time to time.

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 Gaffr/AppComplete 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” :

image

And once we have done this one time operation, that is only necessary for reversed databases (since standard eco databases has these from the start), we can do some model changes:

image

And “Run” and “Evolve”:

image

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 ECO and Gaffr/AppComplete.

This entry was posted in Reverse. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*