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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
