The schema compare/modify functionality exists in Torque's sibling project, ddlutils, http://db.apache.org/ddlutils. I use the API change database methods to compare production DB's to the Torque Schema of new versions and generate the scripts needed to modify the DB.
It's not a Maven or Ant task, but the code to do this using DDLUtils is only 100 lines in a single class (including doc and format spacing). I could have integrated it in my project, but instead it's a quick and dirty utility web app that lets me specify the new and old stuff and then spits out the SQL script. I don't execute this directly because one minor issue with the code is that it assumes that the WHOLE db schema/catalog/db space is defined in the schema and it generates drop statements for tables not in it... and I need these tables for other things. But it's easy to quickly edit the SQL script and remove them. A big plus for having a manual review of the SQL script prior to execution is that it also helps identify those nasty little... I'll patch the production DB to fix an immediate problem then forgot to update the dev code later that we all know are big no-no's but do anyway. Also, it allows the script to be produced prior to the upgrade applications. This lets upgrades be done quickly with minimum user downtime. As to the data issues, this is really an application by application specific function. There is no way for Torque to know what is application data and what is user data. Plus there are a lot of issues around schema design that need to be considered. E.g., application data id's have to stay the same because they are referenced in user data and the like. That said, the needs you state are exactly why I developed a couple of Torque add-ons to help with importing and exporting data as XML. I published this as a SourceForge projects a while back. See: http://torque-addons.sourceforge.net/ Basically, this works by having a Generator add-on that creates Apache commons Betwixt maps for the Torque record objects and a framework for using this to import / export data. I use this in my code to have "export/import system properties" admin functions. Part of my upgrade process is to export the latest system properties on both the production and new release. Do a comparison to find problems (stuff in production not in dev, etc.). Then during the upgrade, import the system properties to the production DB. > -----Original Message----- > From: Ludwig Magnusson [mailto:[email protected]] > Sent: Tuesday, December 01, 2009 4:07 AM > To: 'Apache Torque Developers List' > Subject: [PROPOSAL] Migrate > > Hi! > > I'm working in a project where we are releasing new versions of our > webapp > regularly and often. In every release there may or may not be changes to > the > database. As far as I know there is no really smooth way of doing a > release > like this. I need to create an sql-file that contains the changes of the > database and it needs to be correct in every way. > > Or I could extract the entire production database, make the changes in > the > extracted file, then drop the production database and insert it again > with > the new file. > > Besides this I need to deploy the webapp. No solution is great but I have > a > proposal for solving this. > > > > The premises are these: > > . You have a live running webapp at a public sever which people > are > using. > > . The public app has its database structure that may or may not > change between releases. > > . The public app has data in the database that you wish to keep > between releases (unless a certain table or column in a table were to be > removed. This would of course also remove the data of that table/column). > This is data like user accounts, products or whatever your database > consists > of. > > > > . In your development environment you have a schema-xml file > describing your database. > > . You have some data that is essential to your database, such as > permissions etc. > > . You have some data that is not essential but used for e.g. > functional testing, such as mock users, mock products etc. > > > > . When you release a new version you want to merge the structure > in > your development environment with the existing one in your production > environment (add/remove tables/columns/constraints). > > . You also want to add new essential data. > > > > My proposal is to create a new torque task called migrate that solves > this > (if possible). > > The execution would work like this: > > > > 1. Validate the development version of schema-xml. > > 2. Extract the database structure from the production environment. > > 3. Compare it to the schema-xml from development. > > 4. Create an sql file (or equivalent) from the comparison, only > containing statements like create/alter/drop table/constraint for the > tables > that need changing. > > 5. Execute the created file. > > 6. Insert essential data from the data.xml > > > > This feature would make it possible to release a new version of the > webapp, > containing database changes, with one single maven command and I would > like > some feedback on the idea. If this is an interesting feature, me and my > team > are willing to put a lot of effort into developing it, but we would also > appreciate help from experienced torque developers on how basic torque > functions work since we are not that familiar with the torque code. > > > > Open for feedback > > /Ludwig DukeCE Privacy Statement: Please be advised that this e-mail and any files transmitted with it are confidential communication or may otherwise be privileged or confidential and are intended solely for the individual or entity to whom they are addressed. If you are not the intended recipient you may not rely on the contents of this email or any attachments, and we ask that you please not read, copy or retransmit this communication, but reply to the sender and destroy the email, its contents, and all copies thereof immediately. Any unauthorized dissemination, distribution or copying of this communication is strictly prohibited. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
