commons-sql is cool... I think... I guess?.
James says commons-sql is ready for use. Nifty. I like the idea, but from reading the site I still don't quite get it.
James, perhaps a very simple overview of what commons-sql does, and where it's useful is needed for the front page, rather than a paragraph loaded with other Jakarta project names and JavaDoc links?
Now commons-sql could save me massive headaches (I think) if it could handle updating of the datamodel. What I'm thinking is that you define your datamodel in an XML file, and commons-sql knows how to autocreate that given the DB type. Then when you ship an update, commons-sql could realise the DB already exists, and instead of creating, update it based on the current XML datamodel (ie add new tables and new columns).
This is one of the coolest OFBiz EE features that we use everytime we ship an update. To have it 'framework independent' would be very cool.
Can it do that? If not, could it?
I tried, in the first paragraph to describe exactly what commons-sql is (try hitting refresh on your browser just in case ;-) - looks like my 2nd attempt at describing it failed too :-)
Yes Mike, commons-sql could do that. The idea is that its just an XML format for what a logical database structure should look like, and a set of beans that represent the logical structure. So you can work with XML or beans and convert from one to the other as you wish. Then there's a bunch of tools for creating & applying the DDL or extracting the model from a real database, generating documentation etc.
What I'm hoping gets done soon which will be cool, is essentially what you just described. Take a database model from a new software release, compare it against the current database model via database Connection, do a delta, add any missing columns or indexes and report any issues (columns that should be deleted, columns that need migration) etc. So a DbDiff would really rock and shouldn't be too hard to do.
Migrating database schemas is a PITA and it doesn't really matter what persistence technology is used to read/write to your database - its still the same problem to solve. Hopefully commons-sql can solve all these messy problems for us. Though it obviously needs work!
Right now just being able to automatically create the DDL for lots of different databases from a single logical database model is a big win for me. Hopefully the feature set of useful tools will grow over time.