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.