Generating tables: Manual SQL vs. Modeller

["Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de>]

On Wed, Oct 12, 2011 at 4:13 AM, Durchholz, Joachim <Jo...@hennig-fahrzeugteile.de> wrote:
>> Maintaining a set of scripts so that it remains consistent with the database would be just a waste of effort. It is unlikely that they'd ever need such scripts anyway: the ability to create an empty database would be worthless to the company, it needs the data.
> 
> While I have no opinion on the thread as a whole, I do want to state that the above statement isn't true for many of us.   When you are 
writing integration tests, the ability to create an empty database with an identical schema and populate it with your test data is extremely useful.

Heh, you're right. I should have known better, our own unit tests generate a test database (maybe they're running just too smoothly ;-) ).

However, we are generating the database not from SQLs but straight from the ORM model. Our use case includes these reasons:
1) ORM-generated tables are closer to what the Java side of things actually needs. In other words, a generated table may expose assumptions that are satisfied in the database, but not in the ORM model.
2) We assume that the ORM can check compatibility of its internal model with the database it is connected to. We further assume that this check is more reliable than manually comparing a set of creation SQL scripts with the actual production database. (This assumption need not be true for all circumstances, but it is definitely true for our team and Hibernate, which we're still tied to.)
3) For portability across databases, a set of manually maintained SQL scripts simply isn't going to work since table/view/whatever creation syntax varies wildly.

One use case that I don't have but others might is installable applications that are portable across databases.
For these, all aspects of the database schema (as far as they can be made portable) would best be placed inside modeller. This includes stuff like comments, views, triggers, sequences.
Not that I think Cayenne should change its scope on the spot, just that it might be something to consider in the future.

Just my 2 cents.

Regards,
Jo