ORM layer from database tables

Apr 8, 2009 at 1:36 PM
Hi,

Is it (or will it) possible to "reverse-engineer" the entity model as well as the data access layer from an existing database schema ?

Regards.
Coordinator
Apr 8, 2009 at 3:47 PM
Short answer : not in the foreseeable future.

Long answer : this is a quite legitimate goal for our factory, that would require a lot of obstacles to be overcome. From the top of my head :
  • When you have a legacy database, the chances for it to be badly designed (if somehow designed at all) are huge (by my experience). So you would certainly have to refactor the generated model a lot to make it acceptable. After what you would have to manually edit  the generated data mappers (or better yet : the stored procedures) to map the legacy database to your new model. In this (worst) case, the amount of manual work seems quite comparable to the amount of work now needed...
  • If you assume that the legacy database is perfectly designed according to well known patterns : how would you detect that a single table is in fact the implementation of a Single Inheritance Pattern (note that we currently currently use the Class Table Inheritance pattern to generate the database scripts) ?
  • Though we now only support SQL Server (2005, 2008), we plan to support other RDBMS vendors quite soon, starting with Oracle. Which introduces the problem of datatypes : how to consistently map back a database vendor specifc type to a SDML one (Guid will be stored as RAW(16) on Oracle, which is basically a binary type) ?
So our plan for now is to focus on other features (such as the multi database vendor thing).

Note that technically, I think that the best way to do that would be to directly generate the SDML model file from the database, which is a XML file. If I had to do it the quick way (which means not integrated into Salamanca), I would certainly use a tool like CodeSmith to do the job...

Thanks for your interest. Please keep me informed if you plan to go further into the model generation path.
Apr 9, 2009 at 9:40 AM
Thank you for your answers (short and long) !

I realize indeed that it would involve a lot of work and compromises.
On this subject, the ASP.NET team just released the "RetrofitModel" option for one of the Entity framework tool : EDMGen2.

The strategy I consider is to model the legacy objects in a way that will match the database schema, then refactor some of the generated code to comply to the existing tables.
I guess it is not far from your suggestion to generate the model from the database with the advantage that it integrates into Salamanca.

I didn't quiet understand how through the "quick way" and CodeSmith I would generate entities that integrate into my domain objects generated with Salamanca.
But that's another story...

I'll keep you inform of the result I came up with.