db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Greg Monroe <Greg.Mon...@dukece.com>
Subject RE: [PROPOSAL] Migrate
Date Tue, 01 Dec 2009 14:43:21 GMT
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 

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:


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:ludwig@itcatapult.com]
> 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: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org

View raw message