db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From T E Schmitz <mail...@numerixtechnology.de>
Subject Re: 1. onDelete 2. 1:n association
Date Thu, 29 Jul 2004 08:57:49 GMT
Hello Artur,

Artur Z wrote:

> torque allows setting onDelete attribute in foreign keys relations
> but does not generate any java code to perfrorm deleting (it delegates this to db)

That's correct: this setting simply generates the SQL which determenis 
whether the DELETE [or UPDATE] are cascaded.

> I decided to try to modify Object.vm template, so it would generate such code
> but first I'd like to ask if somebody have not done that before/already ?

As a matter of curiosity: what kind of Java code would you want to generate?

> and second problem is that torque does not generate code for 1:n association, when both
(master and detail) tables are the same
> I also consider making such change to templates, but maybe there is a patch or fix which
makes this change ?

You are right - the doSelectJoinxxxx methods don't handle 
self-references. I have an idea how this could be done, in fact I have 
partly implemented it.

> if so could you share it ?

I would love to but I have made such vast changes to the templates that 
it is now impossible for me to share parts of it. But I am happy to 
share the idea:

I am using aliases to achieve n relationships to the same FK table. This 
also solves the self-referencing problem. Rather than addressing the 
FK-related tables with their fully qualified name I use an alias.
This has one disadvantage, which I have to solve yet: As you probably 
know already, the doSelectJoinXXXX method adds to the passed Criteria 
object. But using aliases requires the caller to use the same aliases 
for other conditions rather than the fully qualified table names. What 
needs to be added is a method that returns the alias name so the caller 
can query it and prepend it to an unqualified column name.

I am attaching the relevant code snippets to this posting. But please 
bear in mind that I have made the following changes and/or assumptions:
a) I have written a doSelectJoinAll, which joins all tables.
b) I am using the LEFT/RIGHT/INNER Join patch by Thomas Fischer, which 
is why I pass an additional parameter to the doSelectJoinXXXX method 
specifying the join type.
c) I use only explicit connections.
d) I don't use multi-column keys (I can't remember whether my Velocity 
code would cope with that.)

To make this easier reading, I am complementing the .vm code by the 
corresponding Java output and the resulting SQL.

If you have any template patches to share it would be very helpful if 
you could do the same.

I presume file attachments won't work for the mailing list. But I'm 
afraid the following code will look a mess in an email client.

//=========================== Peer.vm snippets ======================
## Column names
     #foreach ($col in $table.Columns)
         #set ( $tfc=$table.JavaName )
         #set ( $cfc=$col.JavaName )
         #set ( $cup=$col.Name.toUpperCase() )
         /** Unqualified database column name of the <I>$cup</I> column. */
         public static final String ${cup}_UQ = "$cup";
         /** Fully qualified database column name of the <I>$cup</I> 
column. */
         public static final String ${cup} = "${table.Name}.$cup";
     #end

## addSelectColumns
     protected static void addSelectColumns(Criteria criteria, String 
tableName) throws TorqueException
     {
         String prefix = tableName + ".";
     #foreach ($col in $table.Columns)
         #set ( $cup=$col.Name.toUpperCase() )
         criteria.addSelectColumn(prefix+${cup}_UQ);
     #end
     }

## row2Object
     static $table.JavaName row2Object(Record row,
                         int offset,
                         Class cls)
     throws TorqueException
     {
     try
     {
         $table.JavaName obj = ($table.JavaName) cls.newInstance();
         ${table.JavaName}${PeerSuffix}.populateObject(row, offset, obj);
         if (
     #foreach ($col in $table.Columns) ## this can only be done for 
non-primitive columns
         #set ( $cfc=$col.JavaName )
         (obj.get${cfc}() == null) &&
     #end
         true ) ## out of sheer laziness
         {
             return (null);
         }
         else
         {
     #if ($addSaveMethod)
             obj.setModified(false);
     #end
             obj.setNew(false);
             return (obj);
         }
     }
     catch (InstantiationException e)
     {
         throw new TorqueException(e);
     }
     catch (IllegalAccessException e)
     {
         throw new TorqueException(e);
     }
     }


##---------------------JOINALL------------

     #if ($countFK > 1)
         #set ($includeJoinAll = true)
         #foreach ($fk in $table.ForeignKeys)
             #set ( $tblFK = 
$table.Database.getTable($fk.ForeignTableName) )
             #if ($tblFK.isForReferenceOnly())
                 #set ($includeJoinAll = false)
             #end
         #end

         #if ($includeJoinAll)
             #set ( $relatedByCol = "" )
             #foreach ($columnName in $fk.LocalColumns)
                 #set ( $column = $table.getColumn($columnName) )
                 #if ($column.isMultipleFK())
                      #set ($relatedByCol = "$relatedByCol$column.JavaName")
                 #end
             #end

             #if ($relatedByCol == "")
                 #set ( $collThisTable = "${className}s" )
                 #set ( $collThisTableMs = $className )
             #else
                 #set ( 
$collThisTable="${className}s${JoinPhrase}$relatedByCol" )
                 #set ( 
$collThisTableMs="${className}${JoinPhrase}$relatedByCol" )
             #end

             /**
             * Selects a collection of <code>$className</code> objects 
populating all their
             * related objects.
             * <P>The join type is determined by the 
<code>joinType</code> argument. It determines whether
             * an inner or left outer join is performed. If the 
parameter is <code>null</code>, an inner join via the WHERE clause
             * is produced.
             * <P>Note that a right outer join makes no sense here as 
this might return a <code>null</code>
             * <code>$className</code> object.
             *
             * @param criteria <CODE>Criteria</code>.
             * @param connection database connection.
             * @param joinType join type - one of <code>null</code>, 
<code>Criteria.LEFT_JOIN</code>, <code>Criteria.INNER_JOIN</code>.
             *
             * @return Result set as <code>List</code>.
             *
             * @throws TorqueException Any exceptions caught during 
processing will be rethrown wrapped into a TorqueException.
             */
             protected static List doSelectJoinAll(Criteria criteria, 
Connection connection, Object joinType)
             throws TorqueException
             {
             #set ($index = 1)
                 addSelectColumns(criteria);
                 int offset$index = numColumns + 1;
             #foreach ($fk in $table.ForeignKeys)
                 ##if ( !($fk.ForeignTableName.equals($table.Name)) ) ## 
self-join - should work now because of ALIASes
                      #set ( $joinTable = 
$table.Database.getTable($fk.ForeignTableName) )
                      #set ( $joinClassName = $joinTable.JavaName )
                     #set ( $new_index = $index + 1 )
                     #set ( $alias = "A${index}" )

                     criteria.addAlias("${alias}","$fk.ForeignTableName");
                     #set ( $lfMap = $fk.LocalForeignMapping )
                     #foreach ($columnName in $fk.LocalColumns)
                         #set ( $column = $table.getColumn($columnName) )
                         #set ( $columnFk = $joinTable.getColumn( 
$lfMap.get($columnName) ) )
                         #set ($fkCup = "$columnFk.Name.toUpperCase()" )
                         criteria = addJoin(criteria, 
${table.JavaName}${PeerSuffix}.$column.Name.toUpperCase(),
                                 "${alias}.${fkCup}",joinType);
                     #end
 
${joinClassName}${PeerSuffix}.addSelectColumns(criteria, "${alias}");
                     int offset$new_index = offset$index + 
${joinClassName}${PeerSuffix}.numColumns;
                     Map map${index} = new HashMap();
                     #set ( $index = $new_index )
                 ##end
             #end

                 criteria = completeCriteria (criteria);

                 List rows = BasePeer.doSelect(criteria,connection);
                 List results = new ArrayList();

                 for (int i = 0; i < rows.size(); i++)
                 {
                     Record row = (Record)rows.get(i);
             #set ($classDecl = "Class")
             #if ($table.ChildrenColumn)
                 $classDecl omClass = 
${table.JavaName}${PeerSuffix}.getBaseClass(row, 1);
             #else
                 $classDecl omClass = 
${table.JavaName}${PeerSuffix}.getBaseClass();
             #end
             #set ($classDecl = "")
                 $className mainObj = 
($className)${table.JavaName}${PeerSuffix}.row2Object(row, 1, omClass);
             #set ( $index = 0 )
             #foreach ($fk in $table.ForeignKeys)

                 ##if ( !($fk.ForeignTableName.equals($table.Name)) ) ## 
self-join - should work now because of ALIASes
                      #set ( $joinTable = 
$table.Database.getTable($fk.ForeignTableName) )
                      #set ( $joinClassName = $joinTable.JavaName )
                      #set ($interfaceName = $joinTable.JavaName)
                     #if ($joinTable.Interface)
                         #set ($interfaceName = $joinTable.Interface)
                     #end

                     #set ( $partJoinName = "" )
                     #foreach ($columnName in $fk.LocalColumns)
                         #set ( $column = $table.getColumn($columnName) )
                         #if ($column.isMultipleFK())
                             #set ( $partJoinName = 
"$partJoinName$column.JavaName" )
                         #end
                     #end

                     #if ($partJoinName == "")
                         #set ( $joinString = $interfaceName )
                         #set ( $collThisTable = "${className}s" )
                         #set ( $collThisTableMs = $className )
                     #else
                         #set ( 
$joinString="${interfaceName}${JoinPhrase}$partJoinName" )
                         #set ( 
$collThisTable="${className}s${JoinPhrase}$partJoinName" )
                         #set ( 
$collThisTableMs="${className}${JoinPhrase}$partJoinName" )
                     #end

                     #set ( $index = $index + 1 )

                     #if ($joinTable.ChildrenColumn)
                         $classDecl omClass = 
${joinClassName}${PeerSuffix}.getBaseClass(row, offset$index);
                     #else
                         $classDecl omClass = 
${joinClassName}${PeerSuffix}.getBaseClass();
                     #end
                     #set ($classDecl = "")
                     $joinClassName obj$index = 
($joinClassName)${joinClassName}${PeerSuffix}.row2Object(row, 
offset$index, omClass);
                     if (obj$index != null)
                     {
                         ObjectKey obj${index}_PK = 
obj${index}.getPrimaryKey();
                         if (! (map${index}.containsKey(obj${index}_PK)))
                         {
 
obj${index}.add${collThisTableMs}(mainObj,connection);
                             map${index}.put(obj${index}_PK,obj${index});
                         }
                         else
                         {
                         $joinClassName temp = 
($joinClassName)map${index}.get(obj${index}_PK);
                         temp.add${collThisTableMs}(mainObj,connection);
                     }
                 }
                 ##end ## #if ( 
!($fk.ForeignTableName.equals($table.Name)) )
             #end ## #foreach ($fk in $table.ForeignKeys)
                 results.add(mainObj);
             }
             return results;
             }
         #end ## #if ($includeJoinAll)
     #end ## #if ($countFK > 1)

//================ SectionPeerBase.java snippets =====================

  /** Unqualified database column name of the POSITION column. */
   public static final String POSITION_UQ = "POSITION";

   /** Fully qualified database column name of the POSITION column. */
   public static final String POSITION = "SECTION.POSITION";

   /**
    * Selects a collection of <code>_Section</code> objects populating 
all their related
    * objects.
    *
    * <P>
    * The join type is determined by the <code>joinType</code> argument. 
It determines
    * whether an inner or left outer join is performed. If the parameter is
    * <code>null</code>, an inner join via the WHERE clause is produced.
    * </p>
    *
    * <P>
    * Note that a right outer join makes no sense here as this might 
return a
    * <code>null</code><code>_Section</code> object.
    * </p>
    *
    * @param criteria <CODE>Criteria</code>.
    * @param connection database connection.
    * @param joinType join type - one of <code>null</code>, 
<code>Criteria.LEFT_JOIN</code>,
    *       <code>Criteria.INNER_JOIN</code>.
    *
    * @return Result set as <code>List</code>.
    *
    * @throws TorqueException Any exceptions caught during processing 
will be rethrown
    *        wrapped into a TorqueException.
    */
   protected static List doSelectJoinAll (
     Criteria   criteria,
     Connection connection,
     Object     joinType) throws TorqueException
   {
     addSelectColumns(criteria);
     int offset1 = numColumns + 1;

     criteria.addAlias("A1","BRAND");
     criteria = 
addJoin(criteria,_SectionManager.BRAND_1_SKIPPED_FK,"A1.BRAND_PK",joinType);
     _BrandManager.addSelectColumns(criteria,"A1");
     int offset2 = offset1 + _BrandManager.numColumns;
     Map map1 = new HashMap();

     criteria.addAlias("A2","BRAND");
     criteria = 
addJoin(criteria,_SectionManager.BRAND_2_SKIPPED_FK,"A2.BRAND_PK",joinType);
     _BrandManager.addSelectColumns(criteria,"A2");
     int offset3 = offset2 + _BrandManager.numColumns;
     Map map2 = new HashMap();

     criteria = completeCriteria(criteria);

     List rows    = BasePeer.doSelect(criteria,connection);
     List results = new ArrayList();

     for (int i = 0; i < rows.size(); i++)
     {
       Record   row     = (Record)rows.get(i);
       Class    omClass = _SectionManager.getBaseClass();
       _Section mainObj = 
(_Section)_SectionManager.row2Object(row,1,omClass);

       omClass = _BrandManager.getBaseClass();
       _Brand obj1 = (_Brand)_BrandManager.row2Object(row,offset1,omClass);
       if (obj1 != null)
       {
         ObjectKey obj1_PK = obj1.getPrimaryKey();
         if (! (map1.containsKey(obj1_PK)))
         {
           obj1.add_SectionViaBrand1SkippedID(mainObj,connection);
           map1.put(obj1_PK,obj1);
         }
         else
         {
           _Brand temp = (_Brand)map1.get(obj1_PK);
           temp.add_SectionViaBrand1SkippedID(mainObj,connection);
         }
       }

       omClass = _BrandManager.getBaseClass();
       _Brand obj2 = (_Brand)_BrandManager.row2Object(row,offset2,omClass);
       if (obj2 != null)
       {
         ObjectKey obj2_PK = obj2.getPrimaryKey();
         if (! (map2.containsKey(obj2_PK)))
         {
           obj2.add_SectionViaBrand2SkippedID(mainObj,connection);
           map2.put(obj2_PK,obj2);
         }
         else
         {
           _Brand temp = (_Brand)map2.get(obj2_PK);
           temp.add_SectionViaBrand2SkippedID(mainObj,connection);
         }
       }
       results.add(mainObj);
     }
     return results;
   }

   /**
    * Create a new object of type cls from a resultset row starting from 
a specified offset.
    * This is done so that you can select other rows than just those 
needed for this object.
    * You may for example want to create two objects from the same row.
    *
    * @param row
    * @param offset
    * @param cls
    *
    * @return
    *
    * @throws TorqueException Any exceptions caught during processing 
will be     rethrown
    *        wrapped into a TorqueException.
    */
   static _Section row2Object (
     Record row,
     int    offset,
     Class  cls) throws TorqueException
   {
     try
     {
       _Section obj = (_Section)cls.newInstance();
       _SectionManager.populateObject(row,offset,obj);
       if ((obj.getSectionID() == null) && (obj.getSectionName() == null) &&
           (obj.getIsNew() == null) && (obj.getPosition() == null) &&
           (obj.getBrand1SkippedID() == null) && 
(obj.getBrand2SkippedID() == null) &&
           (obj.getLastUsed() == null) && true)
       {
         return (null);
       }
       else
       {
         obj.setModified(false);
         obj.setNew(false);
         return (obj);
       }
     }
     catch (InstantiationException e)
     {
       throw new TorqueException(e);
     }
     catch (IllegalAccessException e)
     {
       throw new TorqueException(e);
     }
   }

   /**
    * Add all columns to criteria object - fully qualified with by their 
table name.
    *
    * @param criteria criteria object to add columns to.
    *
    * @throws TorqueException Any exceptions caught during processing 
will be rethrown
    *        wrapped into a TorqueException.
    */
   protected static void addSelectColumns (Criteria criteria)
   throws TorqueException
   {
     addSelectColumns(criteria,TABLE_NAME);
   }

   /**
    * Add all columns to criteria object.
    *
    * @param criteria criteria object to add columns to.
    * @param tableName table name or alias to qualify column name with.
    *
    * @throws TorqueException Any exceptions caught during processing 
will be rethrown
    *        wrapped into a TorqueException.
    */
   protected static void addSelectColumns (
     Criteria criteria,
     String   tableName) throws TorqueException
   {
     String prefix = tableName + ".";
     criteria.addSelectColumn(prefix + SECTION_PK_UQ);
     criteria.addSelectColumn(prefix + SECTION_NAME_UQ);
     criteria.addSelectColumn(prefix + IS_NEW_UQ);
     criteria.addSelectColumn(prefix + POSITION_UQ);
     criteria.addSelectColumn(prefix + BRAND_1_SKIPPED_FK_UQ);
     criteria.addSelectColumn(prefix + BRAND_2_SKIPPED_FK_UQ);
     criteria.addSelectColumn(prefix + LAST_USED_UQ);
   }

   /**
    * The only reason why this method is needed is because
    * <code>org.apache.torque.util.SqlEnum</code> has no public access. 
We need to
    * parameterize the join methods in order to avoid duplicating code 
for inner and outer
    * joins.
    *
    * @param criteria <code>Criteria</code> object to add join criterion to.
    * @param left left side of the join.
    * @param right right side of the join.
    * @param joinType join type - one of <code>null</code>, 
<code>Criteria.LEFT_JOIN</code>,
    *       <code>Criteria.INNER_JOIN</code>.
    *
    * @return modified <code>Criteria</code> object.
    *
    * @throws TorqueException if the <code>joinType</code> is not one of 
<code>null</code>,
    *        <code>Criteria.LEFT_JOIN</code>, 
<code>Criteria.INNER_JOIN</code>.
    */
   private static Criteria addJoin (
     Criteria criteria,
     String   left,
     String   right,
     Object   joinType) throws TorqueException
   {
     if (joinType == null)
     {
       criteria.addJoin(left,right,null);
     }
     else if (joinType.equals(Criteria.INNER_JOIN))
     {
       criteria.addJoin(left,right,Criteria.INNER_JOIN);
     }
     else if (joinType.equals(Criteria.LEFT_JOIN))
     {
       criteria.addJoin(left,right,Criteria.LEFT_JOIN);
     }
     else
     {
       throw (new TorqueException("incorrect join type : " + joinType));
     }
     return (criteria);
   }

//================ SQL =====================

SELECT
SECTION.SECTION_PK,
SECTION.SECTION_NAME,
SECTION.IS_NEW,
SECTION.POSITION,
SECTION.BRAND_1_SKIPPED_FK,
SECTION.BRAND_2_SKIPPED_FK,
SECTION.LAST_USED,
A1.BRAND_PK,
A1.BRAND_NAME,
A1.LAST_USED,
A2.BRAND_PK,
A2.BRAND_NAME,
A2.LAST_USED

FROM SECTION

LEFT JOIN BRAND A1 ON SECTION.BRAND_1_SKIPPED_FK=A1.BRAND_PK
LEFT JOIN BRAND A2 ON SECTION.BRAND_2_SKIPPED_FK=A2.BRAND_PK

Of course, this could also be an inner join.

//=============================================================

-- 


Regards/Gruß,

Tarlika Elisabeth Schmitz

---------------------------------------------------------------------
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