db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kris Nuttycombe" <Kris.Nuttyco...@noaa.gov>
Subject Key problems - Torque 3.1.1
Date Mon, 15 Nov 2004 19:53:09 GMT
Hi, all,

Torque's driving me a bit batty this morning. Here is the problem: I'm 
trying to use Torque to facilitate transfers from one database to 
another, but it seems like I can't set the primary keys in the 
destination database correctly. Here is an example of code that is 
supposed to do this:

    /**
     * This method will transfer a template set from the source database to
     * {@link #getDestSetId() <i>destSetId</i>} in the destination database.
     */
    private void transferTemplateSet(Connection sourceConnection, 
Connection destConnection) throws TorqueException {
        Criteria dcrit = new Criteria();
        dcrit.add(DataInfoPeer.ID, this.destSetId);
        List destList = DataInfoPeer.doSelect(dcrit, destConnection);
       
        if (destList.isEmpty() || this.updateSet) {
            Criteria crit = new Criteria();
            crit.add(DataInfoPeer.ID, this.sourceSetId);
            List sourceList = DataInfoPeer.doSelect(crit, sourceConnection);
            log.info(sourceList.size() + " entries found in the source 
database for data set " + this.sourceSetId);

            if (!sourceList.isEmpty()) {
                DataInfo source = (DataInfo) sourceList.get(0);
                DataInfo dest =  source.copy();
                dest.setId(this.destSetId);
                if (this.dataSourceOverride > 0) 
dest.setDataSourceId(this.dataSourceOverride);

                log.debug("Saving record " + dest.toString());
                dest.save(destConnection);
            }
        }
    }

To facilitate this, I had to patch Object.vm and Peer.vm to run a query 
for isNew() instead of just using the boolean property, since I'm 
copying the source object and setting the primary keys and I don't know 
in advance whether or not the object exists in the destination database. 
Here are the patches I use:

Object.vm:

***************
*** 1143,1149 ****
              // If this object has been modified, then save it to the 
database.
              if (isModified())
              {
!                 if (isNew())
                  {
                      ${table.JavaName}Peer.doInsert(($table.JavaName) 
this, con);
                      setNew(false);
--- 1143,1149 ----
              // If this object has been modified, then save it to the 
database.
              if (isModified())
              {
!                 if (isNew(con))
                  {
                      ${table.JavaName}Peer.doInsert(($table.JavaName) 
this, con);
                      setNew(false);
***************
*** 1190,1195 ****
--- 1190,1207 ----
              alreadyInSave = false;
          }
    #end
+     }
+
+     /**
+      * Check whether the object already exists in the database.
+      * Used to determine whether to insert or update. This is more
+      * expensive than using the boolean isNew() flag, but allows
+      * much nicer handling for IDB applications. -Kris Nuttycombe
+      */
+     public boolean isNew(Connection con) throws TorqueException {
+         Criteria criteria = 
${table.JavaName}Peer.buildCriteria(this.getPrimaryKey());
+         List list =  ${table.JavaName}Peer.doSelect(criteria, con);
+         return list.isEmpty();
      }
  #end

Peer.vm:

***************
*** 53,62 ****
  #if (!$table.isAlias())

      /** the default database name for this class */
!     public static final String DATABASE_NAME = "$table.Database.Name";

       /** the table name for this class */
!     public static final String TABLE_NAME = "$table.Name";

      /**
       * @return the map builder for this peer
--- 53,62 ----
  #if (!$table.isAlias())

      /** the default database name for this class */
!     public static final String __DATABASE_NAME__ = "$table.Database.Name";

       /** the table name for this class */
!     public static final String __TABLE_NAME__ = "$table.Name";

      /**
       * @return the map builder for this peer
***************
*** 612,618 ****
      public static void doUpdate(Criteria criteria, Connection con)
          throws TorqueException
      {
!         Criteria selectCriteria = new Criteria(DATABASE_NAME, 2);
    #foreach ($col in $table.Columns)
      #set ( $cup=$col.Name.toUpperCase() )
      #if($col.isBooleanInt())
--- 612,618 ----
      public static void doUpdate(Criteria criteria, Connection con)
          throws TorqueException
      {
!         Criteria selectCriteria = new Criteria(__DATABASE_NAME__, 2);
    #foreach ($col in $table.Columns)
      #set ( $cup=$col.Name.toUpperCase() )
      #if($col.isBooleanInt())
***************
*** 748,753 ****
--- 748,764 ----
      }

      /**
+      * Method to do selects
+      *
+      * @throws TorqueException Any exceptions caught during processing 
will be
+      *         rethrown wrapped into a TorqueException.
+      */
+     public static List doSelect($table.JavaName obj, Connection con) 
throws TorqueException
+     {
+         return doSelect(buildCriteria(obj), con);
+     }
+
+     /**
       * Method to do inserts
       *
       * @throws TorqueException Any exceptions caught during processing 
will be
***************
*** 890,896 ****
      /** Build a Criteria object from the data object for this peer */
      public static Criteria buildCriteria( $table.JavaName obj )
      {
!         Criteria criteria = new Criteria(DATABASE_NAME);
    #foreach ($col in $table.Columns)
      #set ( $cfc=$col.JavaName )
      #set ( $cup=$col.Name.toUpperCase() )
--- 901,907 ----
      /** Build a Criteria object from the data object for this peer */
      public static Criteria buildCriteria( $table.JavaName obj )
      {
!         Criteria criteria = new Criteria(__DATABASE_NAME__);
    #foreach ($col in $table.Columns)
      #set ( $cfc=$col.JavaName )
      #set ( $cup=$col.Name.toUpperCase() )
***************
*** 906,912 ****
      /** Build a Criteria object from the data object for this peer, 
skipping all binary columns */
      public static Criteria buildSelectCriteria( $table.JavaName obj )
      {
!         Criteria criteria = new Criteria(DATABASE_NAME);
    #foreach ($col in $table.Columns)
      #set ( $cfc=$col.JavaName )
      #set ( $cup=$col.Name.toUpperCase() )
--- 917,923 ----
      /** Build a Criteria object from the data object for this peer, 
skipping all binary columns */
      public static Criteria buildSelectCriteria( $table.JavaName obj )
      {
!         Criteria criteria = new Criteria(__DATABASE_NAME__);
    #foreach ($col in $table.Columns)
      #set ( $cfc=$col.JavaName )
      #set ( $cup=$col.Name.toUpperCase() )
***************
*** 980,986 ****
          $table.JavaName retVal = null;
          try
          {
!             db = Torque.getConnection(DATABASE_NAME);
              retVal = ${retrieveMethod}(pk, db);
          }
          finally
--- 991,997 ----
          $table.JavaName retVal = null;
          try
          {
!             db = Torque.getConnection(__DATABASE_NAME__);
              retVal = ${retrieveMethod}(pk, db);
          }
          finally
***************
*** 1033,1039 ****
          List retVal = null;
          try
          {
!            db = Torque.getConnection(DATABASE_NAME);
             retVal = ${retrieveMethod}s(pks, db);
          }
          finally
--- 1044,1050 ----
          List retVal = null;
          try
          {
!            db = Torque.getConnection(__DATABASE_NAME__);
             retVal = ${retrieveMethod}s(pks, db);
          }
          finally
***************
*** 1115,1121 ****
          $table.JavaName retVal = null;
          try
          {
!            db = Torque.getConnection(DATABASE_NAME);
             retVal = retrieveByPK(
    #set ( $comma = false )
    #foreach ($col in $table.PrimaryKey)
--- 1126,1132 ----
          $table.JavaName retVal = null;
          try
          {
!            db = Torque.getConnection(__DATABASE_NAME__);
             retVal = retrieveByPK(
    #set ( $comma = false )
    #foreach ($col in $table.PrimaryKey)
***************
*** 1546,1552 ****
      protected static TableMap getTableMap()
          throws TorqueException
      {
!         return Torque.getDatabaseMap(DATABASE_NAME).getTable(TABLE_NAME);
      }
    #end ## ends if (!$table.isAlias())

--- 1557,1563 ----
      protected static TableMap getTableMap()
          throws TorqueException
      {
!         return 
Torque.getDatabaseMap(__DATABASE_NAME__).getTable(__TABLE_NAME__);
      }
    #end ## ends if (!$table.isAlias())

***************
*** 1557,1563 ****
          // another value so == check is okay and faster
          if (crit.getDbName() == Torque.getDefaultDB())
          {
!             crit.setDbName(DATABASE_NAME);
          }
      }
  }
--- 1568,1574 ----
          // another value so == check is okay and faster
          if (crit.getDbName() == Torque.getDefaultDB())
          {
!             crit.setDbName(__DATABASE_NAME__);
          }
      }
  }

The patch of Peer.vm may be more verbose than is really necessary, but I 
needed to change the DATABASE_NAME and TABLE_NAME members to avoid 
conflicts with column names in some of my tables. The important 
difference here is the addition of the metho public static List 
doSelect($table.JavaName obj, Connection con).

Now, here's the problematic part. I'll start with my log output from the 
first code segment above:

http8080-Processor24 INFO  template.TemplateSetTransfer - 1 entries 
found in the source database for data set 400133
http8080-Processor24 DEBUG template.TemplateSetTransfer - Saving record 
DataInfo:
Id = 400133
Title = Template Set Editing Tools
DataSourceId = 100005
FgdcNumber = null
LastUpdate = 2004-08-04 10:58:11.0

According to the log, the destination data_info entry looks right. 
Here's the schema.xml entry for the data_info table:

    <table name="data_info">
        <column name="id" type="INTEGER" primaryKey="true" required="true"/>
        <column name="title" size="100" type="VARCHAR"/>
        <column name="data_source_id" required="true" type="INTEGER"/>
        <column name="fgdc_number" size="24" type="VARCHAR"/>
        <column name="last_update" type="TIMESTAMP"/>
       
        <foreign-key foreignTable="data_source">
            <reference local="data_source_id" foreign="id"/>
        </foreign-key>
    </table>

However, here's what shows up when I query the destination database:

mysql> select * from data_info;
+----+----------------------------+----------------+-------------+----------------+
| id | title                      | data_source_id | fgdc_number | 
last_update    |
+----+----------------------------+----------------+-------------+----------------+
|  0 | Template Set Editing Tools |         100005 | NULL        | 
20040804105811 |
+----+----------------------------+----------------+-------------+----------------+
1 row in set (0.00 sec)

No matter what entry I attempt to transfer, the destination ID always 
ends up as 0. Of course, this causes an exception due to a key conflict 
if I attempt another transfer.

Can anyone please help me figure out why the correct primary key is not 
being set?

Thank you very much,

Kris Nuttycombe

-- 
=====================================================
Kris Nuttycombe
Associate Scientist
Geospatial Data Services Group
CIRES, National Geophysical Data Center/NOAA
(303) 497-6337
Kris.Nuttycombe@noaa.gov
=====================================================



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


Mime
View raw message