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”.
You just need to give a valid connection string to it; in this case a 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 data and change it if we want:
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:
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) :
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.
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” :
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:
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:
/* Add column [Album].ReleaseDate */
ALTER TABLE [Album] ADD ReleaseDate DATETIME DEFAULT '19000101' NOT NULL;
That wraps up this presentation of the Reverse (engineer) database function in ECO and Gaffr/AppComplete.