Sunday, December 5, 2010

Reverse engineer a database - again

Green field development is a one thing – mastering projects that already have a lot of data is another. This is where the Reverse tools come into play.

I am doing work for an Insurance company – they are challenged by the soft wind of change that never seems to stop.  New products, new regulations, new technology, new clients, new business areas, new employees – in short everything is challenged by a relentless “let us move on” attitude.

Of course  this is common place for all business – competition will force you to evolve or die.

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.

I applied AppComplete on their 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:


This is good news because I can now draw diagrams that can help me understand what the information is all about.


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


Since the information in the database now is explained in a the 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 AppComplete use the Schema of the database to draw conclusions about the model – the Schema might not be the 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 has no primary key according to the Schema etc… In short the Reverse mechanism is hampered by crappy input.

Defining Persistence mapping manually

I suspect that the “Crappy input” fact is common place and AppComplete 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:


So that is the way it actually works – Fine. Hard work to look up missing details by browsing the database schema and comparing with the model.

It will not happen – too much work – is there another way?

Aided definition of Persistence Mapping

The latest versions of AppComplete 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 and Clicking and editing Persistence mapping attributes of the model.


Anonymous Anonymous said...

This looks great. Will this feature be in the modeler that comes with ECO?

Also, have you given any thought to a feature that has been requested more than once on the forum. Basically the request is to add a property in the modeler for a class as to whether the modeled class will be evolved to the database or whether the database will maintain the class information and the model picks up the changes (reverse engineers) from the database.

Initially, the property could just prevent the evolve to database for the class and I could manually manage the reverse part. This should be a very easy and straight forward feature to add.


December 7, 2010 at 6:17 PM  
Blogger Hans Karlsen said...

Yes this is also available in Modlr from within VisualStudio.

And yes I see your point with having a property to tell if a class can be changed by evolve or not. But you should consider having the classes from Reverse in one package and your new classes in another package.

If you then create a new model and import the package with the reversed - the reversed classes will be treated as readonly in the model.

If you create a link to such a class the codegen adds extension methods to the readonly classes for navigation.

January 3, 2011 at 9:38 AM  
Anonymous Anonymous said...

Thanks for the answer Hans. Glad to hear the changes are getting added to ECO in VS as well. As far as putting the reverse engineered classes in a separate package, I don't think that would work very well. I would not want to be restricted to extension methods and not being able to add references to my new classes from the rev. eng. ones. It's not that the rev. eng. classes are fixed and not changing. Just that the changes made to them will be made in the database manually.


January 6, 2011 at 12:14 AM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home

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