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