db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Scott Eade <se...@backstagetech.com.au>
Subject Re: [PATCHES][Discussion wanted] My proposals from the last two weeks
Date Fri, 24 Oct 2003 11:39:23 GMT
Henning,

I leave it to those more informed than I to review your proposals, but I 
thought I would throw the following into the mix as it may be an 
alternative use case for something like what you are working on.

I am in the process of migrating a Turbine/Torque webapp from MySQL to 
PostgreSQL - I have my app working using PostgreSQL, but now need to 
convert the existing data across (handling things such as MySQL's 
AUTO_INCREMENT vs PostgreSQL's SEQUENCE, date/time/timestamp 
differences, etc.).  In the process of migrating the data I have 
actually run into a few of the issues you are addressing, or at least 
variations on them.

Under MySQL I use AUTO_INCREMENT for all of my tables that have IDs, 
including the Turbine* tables (thanks to your TorqueUser code in Turbine 
2.3 this works brilliantly).

I couldn't see any free data migration tools so I decided to use Torque 
to do it for me.  Here is a brief summary of what I have done:

1. I generate two sets of Torque classes - one for MySQL and another for 
PostgreSQL.  These have different project names and packages so I can 
access them simultaneously from the same purpose built conversion 
application.  The PostgreSQL schema has its idMethod set to "none" (in 
the real application it is "native") so that when the conversion runs 
the ids allocated by MySQL are retained and thus all of the foreign key 
references can be successfully transferred (i.e. referential integrity 
is maintained).

2. The sql that Torque generates to create the PostgreSQL database from 
the true application schema (the one with idMethod="native") has to be 
manipulated to remove the sql that creates and drops the sequences since 
PostgreSQL 7.3 does this automatically (of course for the "real" app you 
also have to add id-method-parameter elements to the schema to set the 
name of the sequences correctly since Torque gets it wrong).  To manage 
the RI, I have moved the sql that defines the RI constraints into a 
separate file that I will run after the data has been loaded.

3. I manually create a new method doMigrateData() in the PostgreSQL peer 
classes, and invoke this for all tables in my schema.  The new method 
looks like this (whole class shown for context):

package com.backstagetech.cmes.postgresql.om;

import java.util.Iterator;
import java.util.List;

import org.apache.torque.TorqueException;
import org.apache.torque.util.BasePeer;
import org.apache.torque.util.Criteria;

public class TurbineGroupPeer
    extends com.backstagetech.cmes.postgresql.om.BaseTurbineGroupPeer
{
    /**
     * Migrate the record from MySQL to PostgreSQL
     *
     * To update for a new class:
     * 1. Replace TurbineGroup with the new class name.
     * 2. Replace The primary key (GROUP_ID) with the one for the new 
class.
     */
    public static void doMigrateData() throws TorqueException
    {
        List mysqlRecords
                = com.backstagetech.cmes.mysql.om.TurbineGroupPeer
                        .doSelectVillageRecords(new Criteria());
           
        List postgresqlObjects
                = TurbineGroupPeer.populateObjects(mysqlRecords);
           
        for (Iterator iter = postgresqlObjects.iterator(); iter.hasNext();)
        {
            TurbineGroup postgresqlObject = (TurbineGroup) iter.next();
               
            postgresqlObject.setNew(true);
            postgresqlObject.setModified(true);
            postgresqlObject.save();
        }
           
        // The following is only necessary when the table has an 
autoIncrement primary key.
        String serialQuery = "SELECT setval('"
                + ((String) 
TurbineGroupPeer.getTableMap().getPrimaryKeyMethodInfo()).toLowerCase()
                + "', max("
            + TurbineGroupPeer.GROUP_ID
                + ")) FROM " + TurbineGroupPeer.TABLE_NAME;
        BasePeer.executeQuery(serialQuery);
    }
}
Using an eclipse template this is really easy to create - as this is 
only a one-off conversion I have not really considered if this is an 
optimal solution nor how it might be automated.

4. After execution I should be able to run the sql that enables the RI 
rules, switch back to my webapp and start using PostgreSQL.  I say 
"should" because I am in the middle of this now - so far the data looks 
good.

Torque actually seems to handle this task quite well and for the limited 
one-off conversion I need the above seems to work fine.

Scott

-- 
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au





---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message