Tuesday, August 24, 2010

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.

5 Comments:

Blogger Peter Buchmann said...

Which databases are possible to reverse? In ECO5 there is only:
SQL Server 2005, MySQL, Firebird and Oracle.
For me it would be very interesting to be able to reverse old Access Databases.

August 25, 2010 at 2:22 PM  
Blogger Hans Karlsen said...

The reverse functionality is implemented for the specific databases mentioned above. The specialization part of each implementation is thin so it is almost easy to add new databases.

The main thing that differs between database-implementations is how the meta information is retrieved.

- If you have a specific need you can talk to us and we will give you a fixed price on the extensions of the functionality.

August 27, 2010 at 9:04 AM  
Anonymous Anonymous said...

Hi Hans,
Could you please write the article how to create custom PM?
It would be very helpful for newcomers.
Thank you,
Best Regards,
Alexandr

August 29, 2010 at 12:51 PM  
Blogger Rich said...

I am trying to map to an old (unsuported) version of oracle. I have been able to get some uxisting tables mapped to classes, but only by modifying the XML mapping file.

What controls the appearance of the PrimaryKey and PrimaryKeyMapper properties, and what are the possible values?

I am looking for any existing way to set the model to match classes that are not eco placed classes (no eco_ID), but were not able to be successfully mapped by the reverse engineering functions.

Currently I have to resort to XML mapping files in order get any legacy table mapping to work

Is there a way to take a class that was placed in MODLR and change the properties so that it uses an alternate unique key and does not try to create an ECO_ID column in existing tables?

September 6, 2010 at 5:04 PM  
Blogger Hans Karlsen said...

You are correct that the PrimaryKey and PrimaryKeyMapper fields are hidden by default. To Show them you set the Property ORMappingConfigMode to All on the Package that owns the classes.

The PrimaryKey can then be set to a valid sql column, the primarykeymapper is a valid key mapper Id. Check your PersistenceMapper's SqlDatabaseConfigs's KeyMapper collection to find out the once installed and thus selectable values.

September 6, 2010 at 7:20 PM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home

 
Contact Us | Terms of Use | Privacy Statement © 2009 CapableObjects