Upgrade script

[Ben Litchfield <be...@csh.rit.edu>]

Is it, or could it be possible for torque to generate an upgrade script.
For example, let's say I deploy version1.0 of my application to
production.


I am now happily working on version2.0 and add a few columns to support
features.  At some point I need to upgrade the production schema to the
version2.0 schema but I obviously can't run the script that is normally
generated by torque because it only drops/creates tables.

What would be nice is if I could feed schema1.0.xml and schema2.0.xml to a
torque task and it would create a script that would only run the commands
needed to bring the schema up to date.  Something like

ALTER TABLE blah ADD name VARCHAR(200) //or whatever


Has this been thought about?  Is it possible?  What are other's thoughts
on something like this?

Ben Litchfield

Re: Upgrade script

["Peter S. Hamlen" <ph...@mail.com>]

Ben,

This turns out to be a very complicated task.  The scenario you mention
(adding columns) is relatively simple - but much more complicated issues
arise:

  Example #1:  Dropping a column.  
  Depending on the database, this can be extremely difficult.  For
example, Postgresql doesn't support dropping a column.  you have to
rename the old table, create a new table without the column and then
"reinsert" all the rows into the new table.

  Example #2:  Renaming a column
  In this case, it becomes extremely tricky to determine whether you
have "renamed" a column or dropped one column and added a second
column.  In the first case, then you want to issue a rename command.  In
the second, you want a drop and an add command.  (Note that in the first
case, dropping and adding will cause you to lose all the data in the
column.)

Having given this a fair amount of thought, I think the simplest
approach to solving this problem is to create a new XML file that stores
"schema changes".  For example:
  <addTable>
    <table name="foo">
     ...
     </table>
   </addTable>

   <addColumn table="foo">
     <column name="fooId" autoincrement="true"/>
   </addColumn>

You could then write an XML processor that would apply these changes to
the Schema.xml file, and also generate the schema-change commands that
you refer to.

Probably additional discussions on this topic should be in the
torque-dev mailing list.

-Peter

On Fri, 2003-01-17 at 11:12, Ben Litchfield wrote:
> 
> Is it, or could it be possible for torque to generate an upgrade script.
> For example, let's say I deploy version1.0 of my application to
> production.
> 
> 
> I am now happily working on version2.0 and add a few columns to support
> features.  At some point I need to upgrade the production schema to the
> version2.0 schema but I obviously can't run the script that is normally
> generated by torque because it only drops/creates tables.
> 
> What would be nice is if I could feed schema1.0.xml and schema2.0.xml to a
> torque task and it would create a script that would only run the commands
> needed to bring the schema up to date.  Something like
> 
> ALTER TABLE blah ADD name VARCHAR(200) //or whatever
> 
> 
> Has this been thought about?  Is it possible?  What are other's thoughts
> on something like this?
> 
> Ben Litchfield
> 
> 
> --
> To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>