001 package org.apache.ojb.broker.accesslayer.sql;
002
003 /* Copyright 2002-2005 The Apache Software Foundation
004 *
005 * Licensed under the Apache License, Version 2.0 (the "License");
006 * you may not use this file except in compliance with the License.
007 * You may obtain a copy of the License at
008 *
009 * http://www.apache.org/licenses/LICENSE-2.0
010 *
011 * Unless required by applicable law or agreed to in writing, software
012 * distributed under the License is distributed on an "AS IS" BASIS,
013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014 * See the License for the specific language governing permissions and
015 * limitations under the License.
016 */
017
018 import java.util.ArrayList;
019 import java.util.Collection;
020 import java.util.Enumeration;
021 import java.util.HashMap;
022 import java.util.Iterator;
023 import java.util.List;
024 import java.util.Map;
025
026 import org.apache.ojb.broker.PersistenceBrokerSQLException;
027 import org.apache.ojb.broker.accesslayer.JoinSyntaxTypes;
028 import org.apache.ojb.broker.metadata.ClassDescriptor;
029 import org.apache.ojb.broker.metadata.CollectionDescriptor;
030 import org.apache.ojb.broker.metadata.DescriptorRepository;
031 import org.apache.ojb.broker.metadata.FieldDescriptor;
032 import org.apache.ojb.broker.metadata.FieldHelper;
033 import org.apache.ojb.broker.metadata.ObjectReferenceDescriptor;
034 import org.apache.ojb.broker.metadata.SuperReferenceDescriptor;
035 import org.apache.ojb.broker.platforms.Platform;
036 import org.apache.ojb.broker.query.BetweenCriteria;
037 import org.apache.ojb.broker.query.Criteria;
038 import org.apache.ojb.broker.query.ExistsCriteria;
039 import org.apache.ojb.broker.query.FieldCriteria;
040 import org.apache.ojb.broker.query.InCriteria;
041 import org.apache.ojb.broker.query.LikeCriteria;
042 import org.apache.ojb.broker.query.MtoNQuery;
043 import org.apache.ojb.broker.query.NullCriteria;
044 import org.apache.ojb.broker.query.Query;
045 import org.apache.ojb.broker.query.QueryByCriteria;
046 import org.apache.ojb.broker.query.QueryBySQL;
047 import org.apache.ojb.broker.query.SelectionCriteria;
048 import org.apache.ojb.broker.query.SqlCriteria;
049 import org.apache.ojb.broker.query.UserAlias;
050 import org.apache.ojb.broker.util.SqlHelper;
051 import org.apache.ojb.broker.util.SqlHelper.PathInfo;
052 import org.apache.ojb.broker.util.logging.Logger;
053 import org.apache.ojb.broker.util.logging.LoggerFactory;
054
055 /**
056 * Model a Statement based on Query.
057 *
058 * @author <a href="mailto:jbraeuchi@gmx.ch">Jakob Braeuchi</a>
059 * @version $Id: SqlQueryStatement.java,v 1.1 2007-08-24 22:17:39 ewestfal Exp $
060 */
061 public abstract class SqlQueryStatement implements SqlStatement, JoinSyntaxTypes
062 {
063 private static final String ALIAS_SEPARATOR = ".";
064 private static final String M_N_ALIAS = "M_N";
065 private String sql;
066
067 private SqlQueryStatement m_parentStatement;
068 /** the logger */
069 private Logger m_logger;
070 /** the target table of the query */
071 private TableAlias m_root;
072 /** the search table of the query */
073 private TableAlias m_search;
074 /** the query */
075 private QueryByCriteria m_query;
076 /** the mapping of paths to TableAliases. the key is built using the path and the path class hints. */
077 private HashMap m_pathToAlias = new HashMap();
078 /** the mapping of ClassDescriptor to TableAliases */
079 private HashMap m_cldToAlias = new HashMap();
080 /** maps trees of joins to criteria */
081 private HashMap m_joinTreeToCriteria = new HashMap();
082
083 private Platform m_platform;
084 private ClassDescriptor m_baseCld;
085 private ClassDescriptor m_searchCld;
086
087 private int m_aliasCount = 0;
088 protected HashMap m_attrToFld = new HashMap(); //attribute -> FieldDescriptor
089
090 /**
091 * Constructor for SqlCriteriaStatement.
092 *
093 * @param pf the Platform
094 * @param cld the ClassDescriptor
095 * @param query the Query
096 * @param logger the Logger
097 */
098 public SqlQueryStatement(Platform pf, ClassDescriptor cld, Query query, Logger logger)
099 {
100 this(null, pf, cld, query, logger);
101 }
102
103 /**
104 * Constructor for SqlCriteriaStatement.
105 *
106 * @param parent the Parent Query
107 * @param pf the Platform
108 * @param cld the ClassDescriptor
109 * @param query the Query
110 * @param logger the Logger
111 */
112 public SqlQueryStatement(SqlQueryStatement parent, Platform pf, ClassDescriptor cld, Query query, Logger logger)
113 {
114 m_logger = logger != null ? logger : LoggerFactory.getLogger(SqlQueryStatement.class);
115 m_parentStatement = parent;
116 m_query = (QueryByCriteria) query;
117 m_platform = pf;
118 m_searchCld = cld;
119
120 if ((m_query == null) || (m_query.getBaseClass() == m_query.getSearchClass()))
121 {
122 m_baseCld = m_searchCld;
123 }
124 else
125 {
126 m_baseCld = cld.getRepository().getDescriptorFor(query.getBaseClass());
127 }
128
129 m_root = createTableAlias(m_baseCld, null, "");
130
131 // BRJ: create a special alias for the indirection table
132 if (m_query instanceof MtoNQuery)
133 {
134 MtoNQuery mnQuery = (MtoNQuery)m_query;
135 TableAlias mnAlias = new TableAlias(mnQuery.getIndirectionTable(), M_N_ALIAS);
136 setTableAliasForPath(mnQuery.getIndirectionTable(), null, mnAlias);
137 }
138
139 if (m_searchCld == m_baseCld)
140 {
141 m_search = m_root;
142 }
143 else
144 {
145 m_search = getTableAlias(m_query.getObjectProjectionAttribute(), false, null, null, m_query.getPathClasses());
146 }
147
148 // Walk the super reference-descriptor
149 buildSuperJoinTree(m_root, m_baseCld, "" ,false);
150
151 buildMultiJoinTree(m_root, m_baseCld, "", true);
152
153 // In some cases it is necessary to split the query criteria
154 // and then to generate UNION of several SELECTs
155 // We build the joinTreeToCriteria mapping,
156 if (query != null)
157 {
158 splitCriteria();
159 }
160 }
161
162 protected ClassDescriptor getBaseClassDescriptor()
163 {
164 return m_baseCld;
165 }
166
167 protected ClassDescriptor getSearchClassDescriptor()
168 {
169 return m_searchCld;
170 }
171
172 /**
173 * Return the TableAlias and the PathInfo for an Attribute name<br>
174 * field names in functions (ie: sum(name) ) are tried to resolve ie: name
175 * from FIELDDESCRIPTOR , UPPER(name_test) from Criteria<br>
176 * also resolve pathExpression adress.city or owner.konti.saldo
177 * @param attr
178 * @param useOuterJoins
179 * @param aUserAlias
180 * @param pathClasses
181 * @return ColumnInfo
182 */
183 protected AttributeInfo getAttributeInfo(String attr, boolean useOuterJoins, UserAlias aUserAlias, Map pathClasses)
184 {
185 AttributeInfo result = new AttributeInfo();
186 TableAlias tableAlias;
187 SqlHelper.PathInfo pathInfo = SqlHelper.splitPath(attr);
188 String colName = pathInfo.column;
189 int sp;
190
191 // BRJ:
192 // check if we refer to an attribute in the parent query
193 // this prefix is temporary !
194 if (colName.startsWith(Criteria.PARENT_QUERY_PREFIX) && m_parentStatement != null)
195 {
196 String[] fieldNameRef = {colName.substring(Criteria.PARENT_QUERY_PREFIX.length())};
197 return m_parentStatement.getAttributeInfo(fieldNameRef[0], useOuterJoins, aUserAlias, pathClasses);
198 }
199
200 sp = colName.lastIndexOf(".");
201 if (sp == -1)
202 {
203 tableAlias = getRoot();
204 }
205 else
206 {
207 String pathName = colName.substring(0, sp);
208 String[] fieldNameRef = {colName.substring(sp + 1)};
209
210 tableAlias = getTableAlias(pathName, useOuterJoins, aUserAlias, fieldNameRef, pathClasses);
211 /**
212 * if we have not found an alias by the pathName or
213 * aliasName (if given), try again because pathName
214 * may be an aliasname. it can be only and only if it is not
215 * a path, which means there may be no path separators (,)
216 * in the pathName.
217 */
218 if ((tableAlias == null) && (colName.lastIndexOf(".") == -1))
219 {
220 /**
221 * pathName might be an alias, so check this first
222 */
223 tableAlias = getTableAlias(pathName, useOuterJoins, new UserAlias(pathName, pathName, pathName), null, pathClasses);
224 }
225
226 if (tableAlias != null)
227 {
228 // correct column name to match the alias
229 // productGroup.groupName -> groupName
230 pathInfo.column = fieldNameRef[0];
231 }
232 }
233
234 result.tableAlias = tableAlias;
235 result.pathInfo = pathInfo;
236 return result;
237 }
238
239 /**
240 * Answer the column name for alias and path info<br>
241 * if translate try to convert attribute name into column name otherwise use attribute name<br>
242 * if a FieldDescriptor is found for the attribute name the column name is taken from
243 * there prefixed with the alias (firstname -> A0.F_NAME).
244 */
245 protected String getColName(TableAlias aTableAlias, PathInfo aPathInfo, boolean translate)
246 {
247 String result = null;
248
249 // no translation required, use attribute name
250 if (!translate)
251 {
252 return aPathInfo.column;
253 }
254
255 // BRJ: special alias for the indirection table has no ClassDescriptor
256 if (aTableAlias.cld == null && M_N_ALIAS.equals(aTableAlias.alias))
257 {
258 return getIndirectionTableColName(aTableAlias, aPathInfo.path);
259 }
260
261 // translate attribute name into column name
262 FieldDescriptor fld = getFieldDescriptor(aTableAlias, aPathInfo);
263
264 if (fld != null)
265 {
266 m_attrToFld.put(aPathInfo.path, fld);
267
268 // added to suport the super reference descriptor
269 if (!fld.getClassDescriptor().getFullTableName().equals(aTableAlias.table) && aTableAlias.hasJoins())
270 {
271 Iterator itr = aTableAlias.joins.iterator();
272 while (itr.hasNext())
273 {
274 Join join = (Join) itr.next();
275 if (join.right.table.equals(fld.getClassDescriptor().getFullTableName()))
276 {
277 result = join.right.alias + "." + fld.getColumnName();
278 break;
279 }
280 }
281
282 if (result == null)
283 {
284 result = aPathInfo.column;
285 }
286 }
287 else
288 {
289 result = aTableAlias.alias + "." + fld.getColumnName();
290 }
291 }
292 else if ("*".equals(aPathInfo.column))
293 {
294 result = aPathInfo.column;
295 }
296 else
297 {
298 // throw new IllegalArgumentException("No Field found for : " + aPathInfo.column);
299 result = aPathInfo.column;
300 }
301
302 return result;
303 }
304
305 /**
306 * Add the Column to the StringBuffer <br>
307 *
308 * @param aTableAlias
309 * @param aPathInfo
310 * @param translate flag to indicate translation of pathInfo
311 * @param buf
312 * @return true if appended
313 */
314 protected boolean appendColName(TableAlias aTableAlias, PathInfo aPathInfo, boolean translate, StringBuffer buf)
315 {
316 String prefix = aPathInfo.prefix;
317 String suffix = aPathInfo.suffix;
318 String colName = getColName(aTableAlias, aPathInfo, translate);
319
320 if (prefix != null) // rebuild function contains (
321 {
322 buf.append(prefix);
323 }
324
325 buf.append(colName);
326
327 if (suffix != null) // rebuild function
328 {
329 buf.append(suffix);
330 }
331
332 return true;
333 }
334
335 /**
336 * Get the FieldDescriptor for the PathInfo
337 *
338 * @param aTableAlias
339 * @param aPathInfo
340 * @return FieldDescriptor
341 */
342 protected FieldDescriptor getFieldDescriptor(TableAlias aTableAlias, PathInfo aPathInfo)
343 {
344 FieldDescriptor fld = null;
345 String colName = aPathInfo.column;
346
347 if (aTableAlias != null)
348 {
349 fld = aTableAlias.cld.getFieldDescriptorByName(colName);
350 if (fld == null)
351 {
352 ObjectReferenceDescriptor ord = aTableAlias.cld.getObjectReferenceDescriptorByName(colName);
353 if (ord != null)
354 {
355 fld = getFldFromReference(aTableAlias, ord);
356 }
357 else
358 {
359 fld = getFldFromJoin(aTableAlias, colName);
360 }
361 }
362 }
363
364 return fld;
365 }
366
367 /**
368 * Get FieldDescriptor from joined superclass.
369 */
370 private FieldDescriptor getFldFromJoin(TableAlias aTableAlias, String aColName)
371 {
372 FieldDescriptor fld = null;
373
374 // Search Join Structure for attribute
375 if (aTableAlias.joins != null)
376 {
377 Iterator itr = aTableAlias.joins.iterator();
378 while (itr.hasNext())
379 {
380 Join join = (Join) itr.next();
381 ClassDescriptor cld = join.right.cld;
382
383 if (cld != null)
384 {
385 fld = cld.getFieldDescriptorByName(aColName);
386 if (fld != null)
387 {
388 break;
389 }
390
391 }
392 }
393 }
394 return fld;
395 }
396
397 /**
398 * Get FieldDescriptor from Reference
399 */
400 private FieldDescriptor getFldFromReference(TableAlias aTableAlias, ObjectReferenceDescriptor anOrd)
401 {
402 FieldDescriptor fld = null;
403
404 if (aTableAlias == getRoot())
405 {
406 // no path expression
407 FieldDescriptor[] fk = anOrd.getForeignKeyFieldDescriptors(aTableAlias.cld);
408 if (fk.length > 0)
409 {
410 fld = fk[0];
411 }
412 }
413 else
414 {
415 // attribute with path expression
416 /**
417 * MBAIRD
418 * potentially people are referring to objects, not to the object's primary key,
419 * and then we need to take the primary key attribute of the referenced object
420 * to help them out.
421 */
422 ClassDescriptor cld = aTableAlias.cld.getRepository().getDescriptorFor(anOrd.getItemClass());
423 if (cld != null)
424 {
425 fld = aTableAlias.cld.getFieldDescriptorByName(cld.getPkFields()[0].getPersistentField().getName());
426 }
427 }
428
429 return fld;
430 }
431
432 /**
433 * Append the appropriate ColumnName to the buffer<br>
434 * if a FIELDDESCRIPTOR is found for the Criteria the colName is taken from
435 * there otherwise its taken from Criteria. <br>
436 * field names in functions (ie: sum(name) ) are tried to resolve
437 * ie: name from FIELDDESCRIPTOR , UPPER(name_test) from Criteria<br>
438 * also resolve pathExpression adress.city or owner.konti.saldo
439 */
440 protected boolean appendColName(String attr, boolean useOuterJoins, UserAlias aUserAlias, StringBuffer buf)
441 {
442 AttributeInfo attrInfo = getAttributeInfo(attr, useOuterJoins, aUserAlias, getQuery().getPathClasses());
443 TableAlias tableAlias = attrInfo.tableAlias;
444
445 return appendColName(tableAlias, attrInfo.pathInfo, (tableAlias != null), buf);
446 }
447
448 /**
449 * Append the appropriate ColumnName to the buffer<br>
450 * if a FIELDDESCRIPTOR is found for the Criteria the colName is taken from
451 * there otherwise its taken from Criteria. <br>
452 * field names in functions (ie: sum(name) ) are tried to resolve
453 * ie: name from FIELDDESCRIPTOR , UPPER(name_test) from Criteria<br>
454 * also resolve pathExpression adress.city or owner.konti.saldo
455 */
456 protected boolean appendColName(String attr, String attrAlias, boolean useOuterJoins, UserAlias aUserAlias,
457 StringBuffer buf)
458 {
459 AttributeInfo attrInfo = getAttributeInfo(attr, useOuterJoins, aUserAlias, getQuery().getPathClasses());
460 TableAlias tableAlias = attrInfo.tableAlias;
461 PathInfo pi = attrInfo.pathInfo;
462
463 if (pi.suffix != null)
464 {
465 pi.suffix = pi.suffix + " as " + attrAlias;
466 }
467 else
468 {
469 pi.suffix = " as " + attrAlias;
470 }
471
472 return appendColName(tableAlias, pi, true, buf);
473 }
474
475 /**
476 * Builds the Join for columns if they are not found among the existingColumns.
477 * @param columns the list of columns represented by Criteria.Field to ensure
478 * @param existingColumns the list of column names (String) that are already appended
479 */
480 protected void ensureColumns(List columns, List existingColumns)
481 {
482 if (columns == null || columns.isEmpty())
483 {
484 return;
485 }
486
487 Iterator iter = columns.iterator();
488
489 while (iter.hasNext())
490 {
491 FieldHelper cf = (FieldHelper) iter.next();
492 if (!existingColumns.contains(cf.name))
493 {
494 getAttributeInfo(cf.name, false, null, getQuery().getPathClasses());
495 }
496 }
497 }
498
499 /**
500 * Builds the Join for columns if they are not found among the existingColumns.
501 * These <b>columns are added to the statement</b> using a column-alias "ojb_col_x",
502 * x being the number of existing columns
503 * @param columns the list of columns represented by Criteria.Field to ensure
504 * @param existingColumns the list of column names (String) that are already appended
505 * @param buf the statement
506 * @return List of existingColumns including ojb_col_x
507 */
508 protected List ensureColumns(List columns, List existingColumns, StringBuffer buf)
509 {
510 if (columns == null || columns.isEmpty())
511 {
512 return existingColumns;
513 }
514
515 Iterator iter = columns.iterator();
516 int ojb_col = existingColumns.size() + 1;
517
518 while (iter.hasNext())
519 {
520 FieldHelper cf = (FieldHelper) iter.next();
521 if (!existingColumns.contains(cf.name))
522 {
523 existingColumns.add(cf.name);
524
525 buf.append(",");
526 appendColName(cf.name, "ojb_col_" + ojb_col, false, null, buf);
527 ojb_col++;
528 }
529 }
530
531 return existingColumns;
532 }
533
534
535 /**
536 * appends a WHERE-clause to the Statement
537 * @param where
538 * @param crit
539 * @param stmt
540 */
541 protected void appendWhereClause(StringBuffer where, Criteria crit, StringBuffer stmt)
542 {
543 if (where.length() == 0)
544 {
545 where = null;
546 }
547
548 if (where != null || (crit != null && !crit.isEmpty()))
549 {
550 stmt.append(" WHERE ");
551 appendClause(where, crit, stmt);
552 }
553 }
554
555 /**
556 * appends a HAVING-clause to the Statement
557 * @param having
558 * @param crit
559 * @param stmt
560 */
561 protected void appendHavingClause(StringBuffer having, Criteria crit, StringBuffer stmt)
562 {
563 if (having.length() == 0)
564 {
565 having = null;
566 }
567
568 if (having != null || crit != null)
569 {
570 stmt.append(" HAVING ");
571 appendClause(having, crit, stmt);
572 }
573 }
574
575 /**
576 * appends a WHERE/HAVING-clause to the Statement
577 * @param clause
578 * @param crit
579 * @param stmt
580 */
581 protected void appendClause(StringBuffer clause, Criteria crit, StringBuffer stmt)
582 {
583 /**
584 * MBAIRD
585 * when generating the "WHERE/HAVING" clause we need to append the criteria for multi-mapped
586 * tables. We only need to do this for the root classdescriptor and not for joined tables
587 * because we assume you cannot make a relation of the wrong type upon insertion. Of course,
588 * you COULD mess the data up manually and this would cause a problem.
589 */
590
591 if (clause != null)
592 {
593 stmt.append(clause.toString());
594 }
595 if (crit != null)
596 {
597 if (clause == null)
598 {
599 stmt.append(asSQLStatement(crit));
600 }
601 else
602 {
603 stmt.append(" AND (");
604 stmt.append(asSQLStatement(crit));
605 stmt.append(")");
606 }
607
608 }
609 }
610
611 /**
612 * Create SQL-String based on Criteria
613 */
614 private String asSQLStatement(Criteria crit)
615 {
616 Enumeration e = crit.getElements();
617 StringBuffer statement = new StringBuffer();
618
619 while (e.hasMoreElements())
620 {
621 Object o = e.nextElement();
622 if (o instanceof Criteria)
623 {
624 Criteria pc = (Criteria) o;
625
626 if (pc.isEmpty())
627 {
628 continue; //skip empty criteria
629 }
630
631 String addAtStart = "";
632 String addAtEnd = "";
633
634 // need to add parenthesises?
635 if (pc.isEmbraced())
636 {
637 addAtStart = " (";
638 addAtEnd = ")";
639 }
640
641 switch (pc.getType())
642 {
643 case (Criteria.OR) :
644 {
645 if (statement.length() > 0)
646 {
647 statement.append(" OR ");
648 }
649 statement.append(addAtStart);
650 statement.append(asSQLStatement(pc));
651 statement.append(addAtEnd);
652 break;
653 }
654 case (Criteria.AND) :
655 {
656 if (statement.length() > 0)
657 {
658 statement.insert(0, "( ");
659 statement.append(") AND ");
660 }
661 statement.append(addAtStart);
662 statement.append(asSQLStatement(pc));
663 statement.append(addAtEnd);
664 break;
665 }
666 }
667 }
668 else
669 {
670 SelectionCriteria c = (SelectionCriteria) o;
671 if (statement.length() > 0)
672 {
673 statement.insert(0, "(");
674 statement.append(") AND ");
675 }
676 appendSQLClause(c, statement);
677 }
678 } // while
679
680 // BRJ : negative Criteria surrounded by NOT (...)
681 if (crit.isNegative())
682 {
683 statement.insert(0, " NOT (");
684 statement.append(")");
685 }
686
687 return (statement.length() == 0 ? null : statement.toString());
688 }
689
690 /**
691 * Answer the SQL-Clause for a BetweenCriteria
692 *
693 * @param alias
694 * @param pathInfo
695 * @param c BetweenCriteria
696 * @param buf
697 */
698 private void appendBetweenCriteria(TableAlias alias, PathInfo pathInfo, BetweenCriteria c, StringBuffer buf)
699 {
700 appendColName(alias, pathInfo, c.isTranslateAttribute(), buf);
701 buf.append(c.getClause());
702 appendParameter(c.getValue(), buf);
703 buf.append(" AND ");
704 appendParameter(c.getValue2(), buf);
705 }
706
707 /**
708 * Answer the SQL-Clause for an ExistsCriteria
709 * @param c ExistsCriteria
710 */
711 private void appendExistsCriteria(ExistsCriteria c, StringBuffer buf)
712 {
713 Query subQuery = (Query) c.getValue();
714
715 buf.append(c.getClause());
716 appendSubQuery(subQuery, buf);
717 }
718
719 /**
720 * Answer the SQL-Clause for a FieldCriteria<br>
721 * The value of the FieldCriteria will be translated
722 *
723 * @param alias
724 * @param pathInfo
725 * @param c ColumnCriteria
726 * @param buf
727 */
728 private void appendFieldCriteria(TableAlias alias, PathInfo pathInfo, FieldCriteria c, StringBuffer buf)
729 {
730 appendColName(alias, pathInfo, c.isTranslateAttribute(), buf);
731 buf.append(c.getClause());
732
733 if (c.isTranslateField())
734 {
735 appendColName((String) c.getValue(), false, c.getUserAlias(), buf);
736 }
737 else
738 {
739 buf.append(c.getValue());
740 }
741 }
742
743 /**
744 * Get the column name from the indirection table.
745 * @param mnAlias
746 * @param path
747 */
748 private String getIndirectionTableColName(TableAlias mnAlias, String path)
749 {
750 int dotIdx = path.lastIndexOf(".");
751 String column = path.substring(dotIdx);
752 return mnAlias.alias + column;
753 }
754
755 /**
756 * Answer the SQL-Clause for an InCriteria
757 *
758 * @param alias
759 * @param pathInfo
760 * @param c InCriteria
761 * @param buf
762 */
763 private void appendInCriteria(TableAlias alias, PathInfo pathInfo, InCriteria c, StringBuffer buf)
764 {
765 appendColName(alias, pathInfo, c.isTranslateAttribute(), buf);
766 buf.append(c.getClause());
767
768 if (c.getValue() instanceof Collection)
769 {
770 Object[] values = ((Collection) c.getValue()).toArray();
771 int size = ((Collection) c.getValue()).size();
772
773 buf.append("(");
774 if (size > 0)
775 {
776 for (int i = 0; i < size - 1; i++)
777 {
778 appendParameter(values[i], buf);
779 buf.append(",");
780 }
781 appendParameter(values[size - 1], buf);
782 }
783 buf.append(")");
784 }
785 else
786 {
787 appendParameter(c.getValue(), buf);
788 }
789 }
790
791 /**
792 * Answer the SQL-Clause for a NullCriteria
793 *
794 * @param alias
795 * @param pathInfo
796 * @param c NullCriteria
797 * @param buf
798 */
799 private void appendNullCriteria(TableAlias alias, PathInfo pathInfo, NullCriteria c, StringBuffer buf)
800 {
801 appendColName(alias, pathInfo, c.isTranslateAttribute(), buf);
802 buf.append(c.getClause());
803 }
804
805 /**
806 * Answer the SQL-Clause for a SqlCriteria
807 *
808 */
809 private void appendSQLCriteria(SqlCriteria c, StringBuffer buf)
810 {
811 buf.append(c.getClause());
812 }
813
814 /**
815 * Answer the SQL-Clause for a SelectionCriteria
816 *
817 * @param c
818 * @param buf
819 */
820 private void appendSelectionCriteria(TableAlias alias, PathInfo pathInfo, SelectionCriteria c, StringBuffer buf)
821 {
822 appendColName(alias, pathInfo, c.isTranslateAttribute(), buf);
823 buf.append(c.getClause());
824 appendParameter(c.getValue(), buf);
825 }
826
827 /**
828 * Answer the SQL-Clause for a LikeCriteria
829 *
830 * @param c
831 * @param buf
832 */
833 private void appendLikeCriteria(TableAlias alias, PathInfo pathInfo, LikeCriteria c, StringBuffer buf)
834 {
835 appendColName(alias, pathInfo, c.isTranslateAttribute(), buf);
836 buf.append(c.getClause());
837 appendParameter(c.getValue(), buf);
838
839 buf.append(m_platform.getEscapeClause(c));
840 }
841
842 /**
843 * Answer the SQL-Clause for a SelectionCriteria
844 *
845 * @param alias
846 * @param pathInfo
847 * @param c SelectionCriteria
848 * @param buf
849 */
850 protected void appendCriteria(TableAlias alias, PathInfo pathInfo, SelectionCriteria c, StringBuffer buf)
851 {
852 if (c instanceof FieldCriteria)
853 {
854 appendFieldCriteria(alias, pathInfo, (FieldCriteria) c, buf);
855 }
856 else if (c instanceof NullCriteria)
857 {
858 appendNullCriteria(alias, pathInfo, (NullCriteria) c, buf);
859 }
860 else if (c instanceof BetweenCriteria)
861 {
862 appendBetweenCriteria(alias, pathInfo, (BetweenCriteria) c, buf);
863 }
864 else if (c instanceof InCriteria)
865 {
866 appendInCriteria(alias, pathInfo, (InCriteria) c, buf);
867 }
868 else if (c instanceof SqlCriteria)
869 {
870 appendSQLCriteria((SqlCriteria) c, buf);
871 }
872 else if (c instanceof ExistsCriteria)
873 {
874 appendExistsCriteria((ExistsCriteria) c, buf);
875 }
876 else if (c instanceof LikeCriteria)
877 {
878 appendLikeCriteria(alias, pathInfo, (LikeCriteria) c, buf);
879 }
880 else
881 {
882 appendSelectionCriteria(alias, pathInfo, c, buf);
883 }
884 }
885
886 /**
887 * Answer the SQL-Clause for a SelectionCriteria
888 * If the Criteria references a class with extents an OR-Clause is
889 * added for each extent
890 * @param c SelectionCriteria
891 */
892 protected void appendSQLClause(SelectionCriteria c, StringBuffer buf)
893 {
894 // BRJ : handle SqlCriteria
895 if (c instanceof SqlCriteria)
896 {
897 buf.append(c.getAttribute());
898 return;
899 }
900
901 // BRJ : criteria attribute is a query
902 if (c.getAttribute() instanceof Query)
903 {
904 Query q = (Query) c.getAttribute();
905 buf.append("(");
906 buf.append(getSubQuerySQL(q));
907 buf.append(")");
908 buf.append(c.getClause());
909 appendParameter(c.getValue(), buf);
910 return;
911 }
912
913 AttributeInfo attrInfo = getAttributeInfo((String) c.getAttribute(), false, c.getUserAlias(), c.getPathClasses());
914 TableAlias alias = attrInfo.tableAlias;
915
916 if (alias != null)
917 {
918 boolean hasExtents = alias.hasExtents();
919
920 if (hasExtents)
921 {
922 // BRJ : surround with braces if alias has extents
923 buf.append("(");
924 appendCriteria(alias, attrInfo.pathInfo, c, buf);
925
926 c.setNumberOfExtentsToBind(alias.extents.size());
927 Iterator iter = alias.iterateExtents();
928 while (iter.hasNext())
929 {
930 TableAlias tableAlias = (TableAlias) iter.next();
931 buf.append(" OR ");
932 appendCriteria(tableAlias, attrInfo.pathInfo, c, buf);
933 }
934 buf.append(")");
935 }
936 else
937 {
938 // no extents
939 appendCriteria(alias, attrInfo.pathInfo, c, buf);
940 }
941 }
942 else
943 {
944 // alias null
945 appendCriteria(alias, attrInfo.pathInfo, c, buf);
946 }
947
948 }
949
950 /**
951 * Append the Parameter
952 * Add the place holder ? or the SubQuery
953 * @param value the value of the criteria
954 */
955 private void appendParameter(Object value, StringBuffer buf)
956 {
957 if (value instanceof Query)
958 {
959 appendSubQuery((Query) value, buf);
960 }
961 else
962 {
963 buf.append("?");
964 }
965 }
966
967 /**
968 * Append a SubQuery the SQL-Clause
969 * @param subQuery the subQuery value of SelectionCriteria
970 */
971 private void appendSubQuery(Query subQuery, StringBuffer buf)
972 {
973 buf.append(" (");
974 buf.append(getSubQuerySQL(subQuery));
975 buf.append(") ");
976 }
977
978 /**
979 * Convert subQuery to SQL
980 * @param subQuery the subQuery value of SelectionCriteria
981 */
982 private String getSubQuerySQL(Query subQuery)
983 {
984 ClassDescriptor cld = getRoot().cld.getRepository().getDescriptorFor(subQuery.getSearchClass());
985 String sql;
986
987 if (subQuery instanceof QueryBySQL)
988 {
989 sql = ((QueryBySQL) subQuery).getSql();
990 }
991 else
992 {
993 sql = new SqlSelectStatement(this, m_platform, cld, subQuery, m_logger).getStatement();
994 }
995
996 return sql;
997 }
998
999 /**
1000 * Get TableAlias by the path from the target table of the query.
1001 * @param aPath the path from the target table of the query to this TableAlias.
1002 * @param useOuterJoins use outer join to join this table with the previous
1003 * table in the path.
1004 * @param aUserAlias if specified, overrides alias in crit
1005 * @param fieldRef String[1] contains the field name.
1006 * In the case of related table's primary key the "related.pk" attribute
1007 * must not add new join, but use the value of foreign key
1008 * @param pathClasses the hints
1009 */
1010 private TableAlias getTableAlias(String aPath, boolean useOuterJoins, UserAlias aUserAlias, String[] fieldRef, Map pathClasses)
1011 {
1012 TableAlias curr, prev, indirect;
1013 String attr, attrPath = null;
1014 ObjectReferenceDescriptor ord;
1015 CollectionDescriptor cod;
1016 ClassDescriptor cld;
1017 Object[] prevKeys;
1018 Object[] keys;
1019 ArrayList descriptors;
1020 boolean outer = useOuterJoins;
1021 int pathLength;
1022 List hintClasses = null;
1023 String pathAlias = aUserAlias == null ? null : aUserAlias.getAlias(aPath);
1024
1025 if (pathClasses != null)
1026 {
1027 hintClasses = (List) pathClasses.get(aPath);
1028 }
1029
1030 curr = getTableAliasForPath(aPath, pathAlias, hintClasses);
1031 if (curr != null)
1032 {
1033 return curr;
1034 }
1035
1036 descriptors = getRoot().cld.getAttributeDescriptorsForPath(aPath, pathClasses);
1037 prev = getRoot();
1038
1039 if (descriptors == null || descriptors.size() == 0)
1040 {
1041 if (prev.hasJoins())
1042 {
1043 for (Iterator itr = prev.iterateJoins(); itr.hasNext();)
1044 {
1045 prev = ((Join) itr.next()).left;
1046 descriptors = prev.cld.getAttributeDescriptorsForPath(aPath, pathClasses);
1047 if (descriptors.size() > 0)
1048 {
1049 break;
1050 }
1051 }
1052 }
1053 }
1054
1055 pathLength = descriptors.size();
1056 for (int i = 0; i < pathLength; i++)
1057 {
1058 if (!(descriptors.get(i) instanceof ObjectReferenceDescriptor))
1059 {
1060 // only use Collection- and ObjectReferenceDescriptor
1061 continue;
1062 }
1063
1064 ord = (ObjectReferenceDescriptor) descriptors.get(i);
1065 attr = ord.getAttributeName();
1066 if (attrPath == null)
1067 {
1068 attrPath = attr;
1069 }
1070 else
1071 {
1072 attrPath = attrPath + "." + attr;
1073 }
1074
1075 // use clas hints for path
1076 if (pathClasses != null)
1077 {
1078 hintClasses = (List) pathClasses.get(attrPath);
1079 }
1080
1081 // look for outer join hint
1082 outer = outer || getQuery().isPathOuterJoin(attrPath);
1083
1084 // look for 1:n or m:n
1085 if (ord instanceof CollectionDescriptor)
1086 {
1087 cod = (CollectionDescriptor) ord;
1088 cld = getItemClassDescriptor(cod, hintClasses);
1089
1090 if (!cod.isMtoNRelation())
1091 {
1092 prevKeys = prev.cld.getPkFields();
1093 keys = cod.getForeignKeyFieldDescriptors(cld);
1094 }
1095 else
1096 {
1097 String mnAttrPath = attrPath + "*";
1098 String mnUserAlias = (aUserAlias == null ? null : aUserAlias + "*");
1099 indirect = getTableAliasForPath(mnAttrPath, mnUserAlias, null);
1100 if (indirect == null)
1101 {
1102 indirect = createTableAlias(cod.getIndirectionTable(), mnAttrPath, mnUserAlias);
1103
1104 // we need two Joins for m:n
1105 // 1.) prev class to indirectionTable
1106 prevKeys = prev.cld.getPkFields();
1107 keys = cod.getFksToThisClass();
1108 addJoin(prev, prevKeys, indirect, keys, outer, attr + "*");
1109 }
1110 // 2.) indirectionTable to the current Class
1111 prev = indirect;
1112 prevKeys = cod.getFksToItemClass();
1113 keys = cld.getPkFields();
1114 }
1115 }
1116 else
1117 {
1118 // must be n:1 or 1:1
1119 cld = getItemClassDescriptor(ord, hintClasses);
1120
1121 // BRJ : if ord is taken from 'super' we have to change prev accordingly
1122 if (!prev.cld.equals(ord.getClassDescriptor()))
1123 {
1124 TableAlias ordAlias = getTableAliasForClassDescriptor(ord.getClassDescriptor());
1125 Join join = prev.getJoin(ordAlias);
1126 if (join != null)
1127 {
1128 join.isOuter = join.isOuter || outer;
1129 }
1130 prev = ordAlias;
1131 }
1132
1133 prevKeys = ord.getForeignKeyFieldDescriptors(prev.cld);
1134 keys = cld.getPkFields();
1135
1136 // [olegnitz]
1137 // a special case: the last element of the path is
1138 // reference and the field is one of PK fields =>
1139 // use the correspondent foreign key field, don't add the join
1140 if ((fieldRef != null) && (i == (pathLength - 1)))
1141 {
1142 FieldDescriptor[] pk = cld.getPkFields();
1143
1144 for (int j = 0; j < pk.length; j++)
1145 {
1146 if (pk[j].getAttributeName().equals(fieldRef[0]))
1147 {
1148 fieldRef[0] = ((FieldDescriptor) prevKeys[j]).getAttributeName();
1149 return prev;
1150 }
1151 }
1152 }
1153 }
1154
1155 pathAlias = aUserAlias == null ? null : aUserAlias.getAlias(attrPath);
1156 curr = getTableAliasForPath(attrPath, pathAlias, hintClasses);
1157
1158 if (curr == null)
1159 {
1160 curr = createTableAlias(cld, attrPath, pathAlias, hintClasses);
1161
1162 outer = outer || (curr.cld == prev.cld) || curr.hasExtents() || useOuterJoins;
1163 addJoin(prev, prevKeys, curr, keys, outer, attr);
1164
1165 buildSuperJoinTree(curr, cld, aPath, outer);
1166 }
1167
1168 prev = curr;
1169 }
1170
1171 m_logger.debug("Result of getTableAlias(): " + curr);
1172 return curr;
1173 }
1174
1175 /**
1176 * add a join between two aliases
1177 *
1178 * TODO BRJ : This needs refactoring, it looks kind of weird
1179 *
1180 * no extents
1181 * A1 -> A2
1182 *
1183 * extents on the right
1184 * A1 -> A2
1185 * A1 -> A2E0
1186 *
1187 * extents on the left : copy alias on right, extents point to copies
1188 * A1 -> A2
1189 * A1E0 -> A2C0
1190 *
1191 * extents on the left and right
1192 * A1 -> A2
1193 * A1 -> A2E0
1194 * A1E0 -> A2C0
1195 * A1E0 -> A2E0C0
1196 *
1197 * @param left
1198 * @param leftKeys
1199 * @param right
1200 * @param rightKeys
1201 * @param outer
1202 * @param name
1203 */
1204 private void addJoin(TableAlias left, Object[] leftKeys, TableAlias right, Object[] rightKeys, boolean outer,
1205 String name)
1206 {
1207 TableAlias extAlias, rightCopy;
1208
1209 left.addJoin(new Join(left, leftKeys, right, rightKeys, outer, name));
1210
1211 // build join between left and extents of right
1212 if (right.hasExtents())
1213 {
1214 for (int i = 0; i < right.extents.size(); i++)
1215 {
1216 extAlias = (TableAlias) right.extents.get(i);
1217 FieldDescriptor[] extKeys = getExtentFieldDescriptors(extAlias, (FieldDescriptor[]) rightKeys);
1218
1219 left.addJoin(new Join(left, leftKeys, extAlias, extKeys, true, name));
1220 }
1221 }
1222
1223 // we need to copy the alias on the right for each extent on the left
1224 if (left.hasExtents())
1225 {
1226 for (int i = 0; i < left.extents.size(); i++)
1227 {
1228 extAlias = (TableAlias) left.extents.get(i);
1229 FieldDescriptor[] extKeys = getExtentFieldDescriptors(extAlias, (FieldDescriptor[]) leftKeys);
1230 rightCopy = right.copy("C" + i);
1231
1232 // copies are treated like normal extents
1233 right.extents.add(rightCopy);
1234 right.extents.addAll(rightCopy.extents);
1235
1236 addJoin(extAlias, extKeys, rightCopy, rightKeys, true, name);
1237 }
1238 }
1239 }
1240
1241 /**
1242 * Get the FieldDescriptors of the extent based on the FieldDescriptors of the parent.
1243 */
1244 private FieldDescriptor[] getExtentFieldDescriptors(TableAlias extAlias, FieldDescriptor[] fds)
1245 {
1246 FieldDescriptor[] result = new FieldDescriptor[fds.length];
1247
1248 for (int i = 0; i < fds.length; i++)
1249 {
1250 result[i] = extAlias.cld.getFieldDescriptorByName(fds[i].getAttributeName());
1251 }
1252
1253 return result;
1254 }
1255
1256 private char getAliasChar()
1257 {
1258 char result = 'A';
1259
1260 if (m_parentStatement != null)
1261 {
1262 result = (char) (m_parentStatement.getAliasChar() + 1);
1263 }
1264
1265 return result;
1266 }
1267
1268 /**
1269 * Create a TableAlias for path or userAlias
1270 * @param aCld
1271 * @param aPath
1272 * @param aUserAlias
1273 * @param hints a List os Class objects to be used as hints for path expressions
1274 * @return TableAlias
1275 *
1276 */
1277 private TableAlias createTableAlias(ClassDescriptor aCld, String aPath, String aUserAlias, List hints)
1278 {
1279 if (aUserAlias == null)
1280 {
1281 return createTableAlias(aCld, hints, aPath);
1282 }
1283 else
1284 {
1285 return createTableAlias(aCld, hints, aUserAlias + ALIAS_SEPARATOR + aPath);
1286 }
1287 }
1288
1289 /**
1290 * Create new TableAlias for path
1291 * @param cld the class descriptor for the TableAlias
1292 * @param path the path from the target table of the query to this TableAlias.
1293 * @param hints a List of Class objects to be used on path expressions
1294 */
1295 private TableAlias createTableAlias(ClassDescriptor cld, List hints, String path)
1296 {
1297 TableAlias alias;
1298 boolean lookForExtents = false;
1299
1300 if (!cld.getExtentClasses().isEmpty() && path.length() > 0)
1301 {
1302 lookForExtents = true;
1303 }
1304
1305 String aliasName = String.valueOf(getAliasChar()) + m_aliasCount++; // m_pathToAlias.size();
1306 alias = new TableAlias(cld, aliasName, lookForExtents, hints);
1307
1308 setTableAliasForPath(path, hints, alias);
1309 return alias;
1310 }
1311
1312 /**
1313 * Create a TableAlias for path or userAlias
1314 * @param aTable
1315 * @param aPath
1316 * @param aUserAlias
1317 * @return TableAlias
1318 */
1319 private TableAlias createTableAlias(String aTable, String aPath, String aUserAlias)
1320 {
1321 if (aUserAlias == null)
1322 {
1323 return createTableAlias(aTable, aPath);
1324 }
1325 else
1326 {
1327 return createTableAlias(aTable, aUserAlias + ALIAS_SEPARATOR + aPath);
1328 }
1329 }
1330
1331 /**
1332 * Create new TableAlias for path
1333 * @param table the table name
1334 * @param path the path from the target table of the query to this TableAlias.
1335 */
1336 private TableAlias createTableAlias(String table, String path)
1337 {
1338 TableAlias alias;
1339
1340 if (table == null)
1341 {
1342 getLogger().warn("Creating TableAlias without table for path: " + path);
1343 }
1344
1345 String aliasName = String.valueOf(getAliasChar()) + m_aliasCount++; // + m_pathToAlias.size();
1346 alias = new TableAlias(table, aliasName);
1347 setTableAliasForPath(path, null, alias);
1348 m_logger.debug("createTableAlias2: path: " + path + " tableAlias: " + alias);
1349
1350 return alias;
1351 }
1352
1353 /**
1354 * Answer the TableAlias for aPath
1355 * @param aPath
1356 * @param hintClasses
1357 * @return TableAlias, null if none
1358 */
1359 private TableAlias getTableAliasForPath(String aPath, List hintClasses)
1360 {
1361 return (TableAlias) m_pathToAlias.get(buildAliasKey(aPath, hintClasses));
1362 }
1363
1364 /**
1365 * Set the TableAlias for aPath
1366 * @param aPath
1367 * @param hintClasses
1368 * @param TableAlias
1369 */
1370 private void setTableAliasForPath(String aPath, List hintClasses, TableAlias anAlias)
1371 {
1372 m_pathToAlias.put(buildAliasKey(aPath, hintClasses), anAlias);
1373 }
1374
1375 /**
1376 * Build the key for the TableAlias based on the path and the hints
1377 * @param aPath
1378 * @param hintClasses
1379 * @return the key for the TableAlias
1380 */
1381 private String buildAliasKey(String aPath, List hintClasses)
1382 {
1383 if (hintClasses == null || hintClasses.isEmpty())
1384 {
1385 return aPath;
1386 }
1387
1388 StringBuffer buf = new StringBuffer(aPath);
1389 for (Iterator iter = hintClasses.iterator(); iter.hasNext();)
1390 {
1391 Class hint = (Class) iter.next();
1392 buf.append(" ");
1393 buf.append(hint.getName());
1394 }
1395 return buf.toString();
1396 }
1397
1398 /**
1399 * Answer the TableAlias for ClassDescriptor.
1400 */
1401 protected TableAlias getTableAliasForClassDescriptor(ClassDescriptor aCld)
1402 {
1403 return (TableAlias) m_cldToAlias.get(aCld);
1404 }
1405
1406 /**
1407 * Set the TableAlias for ClassDescriptor
1408 */
1409 private void setTableAliasForClassDescriptor(ClassDescriptor aCld, TableAlias anAlias)
1410 {
1411 if (m_cldToAlias.get(aCld) == null)
1412 {
1413 m_cldToAlias.put(aCld, anAlias);
1414 }
1415 }
1416
1417 /**
1418 * Answer the TableAlias for aPath or aUserAlias
1419 * @param aPath
1420 * @param aUserAlias
1421 * @param hintClasses
1422 * @return TableAlias, null if none
1423 */
1424 private TableAlias getTableAliasForPath(String aPath, String aUserAlias, List hintClasses)
1425 {
1426 if (aUserAlias == null)
1427 {
1428 return getTableAliasForPath(aPath, hintClasses);
1429 }
1430 else
1431 {
1432 return getTableAliasForPath(aUserAlias + ALIAS_SEPARATOR + aPath, hintClasses);
1433 }
1434 }
1435
1436 /**
1437 * Answer the ClassDescriptor for itemClass for an ObjectReferenceDescriptor
1438 * check optional hint. The returned Class is to highest superclass contained in the hint list.
1439 * TODO: add super ClassDescriptor
1440 */
1441 private ClassDescriptor getItemClassDescriptor(ObjectReferenceDescriptor ord, List hintClasses)
1442 {
1443 DescriptorRepository repo = ord.getClassDescriptor().getRepository();
1444
1445 if (hintClasses == null || hintClasses.isEmpty())
1446 {
1447 return repo.getDescriptorFor(ord.getItemClass());
1448 }
1449
1450 Class resultClass = (Class) hintClasses.get(0);
1451
1452 for (Iterator iter = hintClasses.iterator(); iter.hasNext();)
1453 {
1454 Class clazz = (Class) iter.next();
1455 Class superClazz = clazz.getSuperclass();
1456
1457 if (superClazz != null && resultClass.equals(superClazz.getSuperclass()))
1458 {
1459 continue; // skip if we already have a super superclass
1460 }
1461
1462 if (hintClasses.contains(superClazz))
1463 {
1464 resultClass = superClazz; // use superclass if it's in the hints
1465 }
1466 }
1467
1468 return repo.getDescriptorFor(resultClass);
1469 }
1470
1471 /**
1472 * Appends the ORDER BY clause for the Query.
1473 * <br>
1474 * If the orderByField is found in the list of selected fields it's index is added.
1475 * Otherwise it's name is added.
1476 * @param orderByFields
1477 * @param selectedFields the names of the fields in the SELECT clause
1478 * @param buf
1479 */
1480 protected void appendOrderByClause(List orderByFields, List selectedFields, StringBuffer buf)
1481 {
1482
1483 if (orderByFields == null || orderByFields.size() == 0)
1484 {
1485 return;
1486 }
1487
1488 buf.append(" ORDER BY ");
1489 for (int i = 0; i < orderByFields.size(); i++)
1490 {
1491 FieldHelper cf = (FieldHelper) orderByFields.get(i);
1492 int colNumber = selectedFields.indexOf(cf.name);
1493
1494 if (i > 0)
1495 {
1496 buf.append(",");
1497 }
1498
1499 if (colNumber >= 0)
1500 {
1501 buf.append(colNumber + 1);
1502 }
1503 else
1504 {
1505 appendColName(cf.name, false, null, buf);
1506 }
1507
1508 if (!cf.isAscending)
1509 {
1510 buf.append(" DESC");
1511 }
1512 }
1513 }
1514
1515 /**
1516 * Appends the GROUP BY clause for the Query
1517 * @param groupByFields
1518 * @param buf
1519 */
1520 protected void appendGroupByClause(List groupByFields, StringBuffer buf)
1521 {
1522 if (groupByFields == null || groupByFields.size() == 0)
1523 {
1524 return;
1525 }
1526
1527 buf.append(" GROUP BY ");
1528 for (int i = 0; i < groupByFields.size(); i++)
1529 {
1530 FieldHelper cf = (FieldHelper) groupByFields.get(i);
1531
1532 if (i > 0)
1533 {
1534 buf.append(",");
1535 }
1536
1537 appendColName(cf.name, false, null, buf);
1538 }
1539 }
1540
1541 /**
1542 * Appends to the statement table and all tables joined to it.
1543 * @param alias the table alias
1544 * @param where append conditions for WHERE clause here
1545 */
1546 protected void appendTableWithJoins(TableAlias alias, StringBuffer where, StringBuffer buf)
1547 {
1548 int stmtFromPos = 0;
1549 byte joinSyntax = getJoinSyntaxType();
1550
1551 if (joinSyntax == SQL92_JOIN_SYNTAX)
1552 {
1553 stmtFromPos = buf.length(); // store position of join (by: Terry Dexter)
1554 }
1555
1556 if (alias == getRoot())
1557 {
1558 // BRJ: also add indirection table to FROM-clause for MtoNQuery
1559 if (getQuery() instanceof MtoNQuery)
1560 {
1561 MtoNQuery mnQuery = (MtoNQuery)m_query;
1562 buf.append(getTableAliasForPath(mnQuery.getIndirectionTable(), null).getTableAndAlias());
1563 buf.append(", ");
1564 }
1565 buf.append(alias.getTableAndAlias());
1566 }
1567 else if (joinSyntax != SQL92_NOPAREN_JOIN_SYNTAX)
1568 {
1569 buf.append(alias.getTableAndAlias());
1570 }
1571
1572 if (!alias.hasJoins())
1573 {
1574 return;
1575 }
1576
1577 for (Iterator it = alias.iterateJoins(); it.hasNext();)
1578 {
1579 Join join = (Join) it.next();
1580
1581 if (joinSyntax == SQL92_JOIN_SYNTAX)
1582 {
1583 appendJoinSQL92(join, where, buf);
1584 if (it.hasNext())
1585 {
1586 buf.insert(stmtFromPos, "(");
1587 buf.append(")");
1588 }
1589 }
1590 else if (joinSyntax == SQL92_NOPAREN_JOIN_SYNTAX)
1591 {
1592 appendJoinSQL92NoParen(join, where, buf);
1593 }
1594 else
1595 {
1596 appendJoin(where, buf, join);
1597 }
1598
1599 }
1600 }
1601
1602 /**
1603 * Append Join for non SQL92 Syntax
1604 */
1605 private void appendJoin(StringBuffer where, StringBuffer buf, Join join)
1606 {
1607 buf.append(",");
1608 appendTableWithJoins(join.right, where, buf);
1609 if (where.length() > 0)
1610 {
1611 where.append(" AND ");
1612 }
1613 join.appendJoinEqualities(where);
1614 }
1615
1616 /**
1617 * Append Join for SQL92 Syntax
1618 */
1619 private void appendJoinSQL92(Join join, StringBuffer where, StringBuffer buf)
1620 {
1621 if (join.isOuter)
1622 {
1623 buf.append(" LEFT OUTER JOIN ");
1624 }
1625 else
1626 {
1627 buf.append(" INNER JOIN ");
1628 }
1629 if (join.right.hasJoins())
1630 {
1631 buf.append("(");
1632 appendTableWithJoins(join.right, where, buf);
1633 buf.append(")");
1634 }
1635 else
1636 {
1637 appendTableWithJoins(join.right, where, buf);
1638 }
1639 buf.append(" ON ");
1640 join.appendJoinEqualities(buf);
1641 }
1642
1643 /**
1644 * Append Join for SQL92 Syntax without parentheses
1645 */
1646 private void appendJoinSQL92NoParen(Join join, StringBuffer where, StringBuffer buf)
1647 {
1648 if (join.isOuter)
1649 {
1650 buf.append(" LEFT OUTER JOIN ");
1651 }
1652 else
1653 {
1654 buf.append(" INNER JOIN ");
1655 }
1656
1657 buf.append(join.right.getTableAndAlias());
1658 buf.append(" ON ");
1659 join.appendJoinEqualities(buf);
1660
1661 appendTableWithJoins(join.right, where, buf);
1662 }
1663
1664 /**
1665 * Build the tree of joins for the given criteria
1666 */
1667 private void buildJoinTree(Criteria crit)
1668 {
1669 Enumeration e = crit.getElements();
1670
1671 while (e.hasMoreElements())
1672 {
1673 Object o = e.nextElement();
1674 if (o instanceof Criteria)
1675 {
1676 buildJoinTree((Criteria) o);
1677 }
1678 else
1679 {
1680 SelectionCriteria c = (SelectionCriteria) o;
1681
1682 // BRJ skip SqlCriteria
1683 if (c instanceof SqlCriteria)
1684 {
1685 continue;
1686 }
1687
1688 // BRJ: Outer join for OR
1689 boolean useOuterJoin = (crit.getType() == Criteria.OR);
1690
1691 // BRJ: do not build join tree for subQuery attribute
1692 if (c.getAttribute() != null && c.getAttribute() instanceof String)
1693 {
1694 //buildJoinTreeForColumn((String) c.getAttribute(), useOuterJoin, c.getAlias(), c.getPathClasses());
1695 buildJoinTreeForColumn((String) c.getAttribute(), useOuterJoin, c.getUserAlias(), c.getPathClasses());
1696 }
1697 if (c instanceof FieldCriteria)
1698 {
1699 FieldCriteria cc = (FieldCriteria) c;
1700 buildJoinTreeForColumn((String) cc.getValue(), useOuterJoin, c.getUserAlias(), c.getPathClasses());
1701 }
1702 }
1703 }
1704 }
1705
1706 /**
1707 * build the Join-Information for name
1708 * functions and the last segment are removed
1709 * ie: avg(accounts.amount) -> accounts
1710 */
1711 private void buildJoinTreeForColumn(String aColName, boolean useOuterJoin, UserAlias aUserAlias, Map pathClasses)
1712 {
1713 String pathName = SqlHelper.cleanPath(aColName);
1714 int sepPos = pathName.lastIndexOf(".");
1715
1716 if (sepPos >= 0)
1717 {
1718 getTableAlias(pathName.substring(0, sepPos), useOuterJoin, aUserAlias,
1719 new String[]{pathName.substring(sepPos + 1)}, pathClasses);
1720 }
1721 }
1722
1723 /**
1724 * build the Join-Information if a super reference exists
1725 *
1726 * @param left
1727 * @param cld
1728 * @param name
1729 */
1730 protected void buildSuperJoinTree(TableAlias left, ClassDescriptor cld, String name, boolean useOuterJoin)
1731 {
1732 ClassDescriptor superCld = cld.getSuperClassDescriptor();
1733 if (superCld != null)
1734 {
1735 SuperReferenceDescriptor superRef = cld.getSuperReference();
1736 FieldDescriptor[] leftFields = superRef.getForeignKeyFieldDescriptors(cld);
1737 TableAlias base_alias = getTableAliasForPath(name, null, null);
1738 String aliasName = String.valueOf(getAliasChar()) + m_aliasCount++;
1739 TableAlias right = new TableAlias(superCld, aliasName, useOuterJoin, null);
1740
1741 Join join1to1 = new Join(left, leftFields, right, superCld.getPkFields(), useOuterJoin, "superClass");
1742 base_alias.addJoin(join1to1);
1743
1744 buildSuperJoinTree(right, superCld, name, useOuterJoin);
1745 }
1746 }
1747
1748 /**
1749 * build the Join-Information for Subclasses having a super reference to this class
1750 *
1751 * @param left
1752 * @param cld
1753 * @param name
1754 */
1755 private void buildMultiJoinTree(TableAlias left, ClassDescriptor cld, String name, boolean useOuterJoin)
1756 {
1757 DescriptorRepository repository = cld.getRepository();
1758 Class[] multiJoinedClasses = repository.getSubClassesMultipleJoinedTables(cld, false);
1759
1760 for (int i = 0; i < multiJoinedClasses.length; i++)
1761 {
1762 ClassDescriptor subCld = repository.getDescriptorFor(multiJoinedClasses[i]);
1763 SuperReferenceDescriptor srd = subCld.getSuperReference();
1764 if (srd != null)
1765 {
1766 FieldDescriptor[] leftFields = subCld.getPkFields();
1767 FieldDescriptor[] rightFields = srd.getForeignKeyFieldDescriptors(subCld);
1768 TableAlias base_alias = getTableAliasForPath(name, null, null);
1769
1770 String aliasName = String.valueOf(getAliasChar()) + m_aliasCount++;
1771 TableAlias right = new TableAlias(subCld, aliasName, false, null);
1772
1773 Join join1to1 = new Join(left, leftFields, right, rightFields, useOuterJoin, "subClass");
1774 base_alias.addJoin(join1to1);
1775
1776 buildMultiJoinTree(right, subCld, name, useOuterJoin);
1777 }
1778 }
1779 }
1780
1781 /**
1782 * First reduce the Criteria to the normal disjunctive form, then
1783 * calculate the necessary tree of joined tables for each item, then group
1784 * items with the same tree of joined tables.
1785 */
1786 protected void splitCriteria()
1787 {
1788 Criteria whereCrit = getQuery().getCriteria();
1789 Criteria havingCrit = getQuery().getHavingCriteria();
1790
1791 if (whereCrit == null || whereCrit.isEmpty())
1792 {
1793 getJoinTreeToCriteria().put(getRoot(), null);
1794 }
1795 else
1796 {
1797 // TODO: parameters list shold be modified when the form is reduced to DNF.
1798 getJoinTreeToCriteria().put(getRoot(), whereCrit);
1799 buildJoinTree(whereCrit);
1800 }
1801
1802 if (havingCrit != null && !havingCrit.isEmpty())
1803 {
1804 buildJoinTree(havingCrit);
1805 }
1806
1807 }
1808
1809
1810 /**
1811 * Gets the query.
1812 * @return Returns a Query
1813 */
1814 protected QueryByCriteria getQuery()
1815 {
1816 return m_query;
1817 }
1818
1819 /**
1820 * Gets the root.
1821 * @return Returns a TableAlias
1822 */
1823 protected TableAlias getRoot()
1824 {
1825 return m_root;
1826 }
1827
1828 /**
1829 * Sets the root.
1830 * @param root The root to set
1831 */
1832 protected void setRoot(TableAlias root)
1833 {
1834 this.m_root = root;
1835 }
1836
1837 /**
1838 * Gets the search table of this query.
1839 * @return Returns a TableAlias
1840 */
1841 protected TableAlias getSearchTable()
1842 {
1843 return m_search;
1844 }
1845
1846 /**
1847 * Gets the joinTreeToCriteria.
1848 * @return Returns a HashMap
1849 */
1850 protected HashMap getJoinTreeToCriteria()
1851 {
1852 return m_joinTreeToCriteria;
1853 }
1854
1855 /**
1856 * Returns the joinSyntaxType.
1857 * @return byte
1858 */
1859 protected byte getJoinSyntaxType()
1860 {
1861 return m_platform.getJoinSyntaxType();
1862 }
1863
1864 /**
1865 * Returns the logger.
1866 * @return Logger
1867 */
1868 protected Logger getLogger()
1869 {
1870 return m_logger;
1871 }
1872
1873 public String getStatement()
1874 {
1875 if(sql == null)
1876 {
1877 sql = buildStatement();
1878 }
1879 return sql;
1880 }
1881
1882 /**
1883 * Build the SQL String.
1884 * @return SQL String
1885 */
1886 protected abstract String buildStatement();
1887
1888
1889 //-----------------------------------------------------------------
1890 // ------------------- Inner classes ------------------------------
1891 //-----------------------------------------------------------------
1892
1893 /**
1894 * This class is a helper to return TableAlias and PathInfo
1895 */
1896 static final class AttributeInfo
1897 {
1898 TableAlias tableAlias;
1899 PathInfo pathInfo;
1900 }
1901
1902 /**
1903 * This class represents one table (possibly with alias) in the SQL query
1904 */
1905 final class TableAlias
1906 {
1907 Logger logger = LoggerFactory.getLogger(TableAlias.class);
1908 ClassDescriptor cld; // Is null for indirection table of M:N relation
1909 String table;
1910 final String alias;
1911 List extents = new ArrayList();
1912 List hints = new ArrayList();
1913 List joins;
1914
1915 TableAlias(String aTable, String anAlias)
1916 {
1917 this.cld = null;
1918 this.table = aTable;
1919 this.alias = anAlias;
1920 }
1921
1922 TableAlias(ClassDescriptor aCld, String anAlias)
1923 {
1924 this(aCld, anAlias, false, null);
1925 }
1926
1927 TableAlias(ClassDescriptor aCld, String anAlias, boolean lookForExtents, List hints)
1928 {
1929 this.cld = aCld;
1930 this.table = aCld.getFullTableName();
1931 this.alias = anAlias;
1932 boolean useHintsOnExtents = false;
1933
1934 // BRJ: store alias map of in enclosing class
1935 setTableAliasForClassDescriptor(aCld, this);
1936
1937 //LEANDRO: use hints
1938 if (hints != null && hints.size() > 0)
1939 {
1940 useHintsOnExtents = true;
1941 }
1942
1943 logger.debug("TableAlias(): using hints ? " + useHintsOnExtents);
1944
1945 // BRJ : build alias for extents, only one per Table
1946 if (lookForExtents)
1947 {
1948 ClassDescriptor[] extCLDs = (ClassDescriptor[]) aCld.getRepository().getAllConcreteSubclassDescriptors(
1949 aCld).toArray(new ClassDescriptor[0]);
1950
1951 ClassDescriptor extCd;
1952 Class extClass;
1953 String extTable;
1954 Map extMap = new HashMap(); // only one Alias per Table
1955 int firstNonAbstractExtentIndex = 0;
1956
1957 for (int i = 0; i < extCLDs.length; i++)
1958 {
1959 extCd = extCLDs[i];
1960 extClass = extCd.getClassOfObject();
1961 if (useHintsOnExtents && (!hints.contains(extClass)))
1962 {
1963 //LEANDRO: don't include this class
1964 logger.debug("Skipping class [" + extClass + "] from extents List");
1965 firstNonAbstractExtentIndex++;
1966 continue;
1967 }
1968 extTable = extCd.getFullTableName();
1969
1970 // BRJ : Use the first non abstract extent
1971 // if the main cld is abstract
1972 //logger.debug("cld abstract["+aCld.isAbstract()+"] i["+i+"] index ["+firtsNonAbstractExtentIndex+"]");
1973 if (aCld.isAbstract() && i == firstNonAbstractExtentIndex)
1974 {
1975 this.cld = extCd;
1976 this.table = extTable;
1977 }
1978 else
1979 {
1980 // Add a new extent entry only if the table of the extent
1981 // does not match the table of the 'base' class.
1982 if (extMap.get(extTable) == null && !extTable.equals(table))
1983 {
1984 extMap.put(extTable, new TableAlias(extCd, anAlias + "E" + i, false, hints));
1985 }
1986 }
1987 }
1988 extents.addAll(extMap.values());
1989 }
1990
1991 if (cld == null)
1992 {
1993 throw new PersistenceBrokerSQLException("Table is NULL for alias: " + alias);
1994 }
1995 }
1996
1997 ClassDescriptor getClassDescriptor()
1998 {
1999 return cld;
2000 }
2001
2002 String getTableAndAlias()
2003 {
2004 return table + " " + alias;
2005 }
2006
2007 boolean hasExtents()
2008 {
2009 return (!extents.isEmpty());
2010 }
2011
2012 Iterator iterateExtents()
2013 {
2014 return extents.iterator();
2015 }
2016
2017 /**
2018 * Copy the Alias and all it's extents adding a Postfix
2019 * Joins are not copied.
2020 */
2021 TableAlias copy(String aPostfix)
2022 {
2023 TableAlias result, temp;
2024 Iterator iter = iterateExtents();
2025
2026 if (cld == null)
2027 {
2028 result = new TableAlias(table, alias + aPostfix);
2029 }
2030 else
2031 {
2032 result = new TableAlias(cld, alias + aPostfix);
2033 }
2034
2035 while (iter.hasNext())
2036 {
2037 temp = (TableAlias) iter.next();
2038 result.extents.add(temp.copy(aPostfix));
2039 }
2040
2041 return result;
2042 }
2043
2044 void addJoin(Join join)
2045 {
2046 if (joins == null)
2047 {
2048 joins = new ArrayList();
2049 }
2050 joins.add(join);
2051 }
2052
2053 Iterator iterateJoins()
2054 {
2055 return joins.iterator();
2056 }
2057
2058 boolean hasJoins()
2059 {
2060 return (joins != null);
2061 }
2062
2063 /**
2064 * Get the Join ponting to anAlias.
2065 */
2066 Join getJoin(TableAlias anAlias)
2067 {
2068 Join result = null;
2069
2070 if (joins != null)
2071 {
2072 Iterator iter = joins.iterator();
2073 while (iter.hasNext())
2074 {
2075 Join join = (Join) iter.next();
2076 if (join.right.equals(anAlias))
2077 {
2078 result = join;
2079 break;
2080 }
2081 }
2082 }
2083 return result;
2084 }
2085
2086 public String toString()
2087 {
2088 StringBuffer sb = new StringBuffer(1024);
2089 boolean first = true;
2090
2091 sb.append(getTableAndAlias());
2092 if (joins != null)
2093 {
2094 sb.append(" [");
2095 for (Iterator it = joins.iterator(); it.hasNext();)
2096 {
2097 Join join = (Join) it.next();
2098
2099 if (first)
2100 {
2101 first = false;
2102 }
2103 else
2104 {
2105 sb.append(", ");
2106 }
2107 sb.append("-(");
2108 sb.append(join.name);
2109 sb.append(")->");
2110 sb.append(join.right);
2111 }
2112 sb.append("]");
2113 }
2114 return sb.toString();
2115 }
2116
2117 public boolean equals(Object obj)
2118 {
2119 TableAlias t = (TableAlias) obj;
2120
2121 return table.equals(t.table); // BRJ: check table only
2122 }
2123
2124 public int hashCode()
2125 {
2126 return table.hashCode();
2127 }
2128
2129 }
2130
2131 /**
2132 * This class represents join between two TableAliases
2133 */
2134 final class Join
2135 {
2136 final TableAlias left;
2137 final String[] leftKeys;
2138 final TableAlias right;
2139 final String[] rightKeys;
2140 boolean isOuter;
2141 /** This is the name of the field corresponding to this join */
2142 final String name;
2143
2144 /**
2145 * leftKeys and rightKeys should be either FieldDescriptor[] or String[]
2146 */
2147 Join(TableAlias left, Object[] leftKeys, TableAlias right, Object[] rightKeys, boolean isOuter, String name)
2148 {
2149 this.left = left;
2150 this.leftKeys = getColumns(leftKeys);
2151 this.right = right;
2152 this.rightKeys = getColumns(rightKeys);
2153 this.isOuter = isOuter;
2154 this.name = name;
2155 }
2156
2157 private String[] getColumns(Object[] keys)
2158 {
2159 String[] columns = new String[keys.length];
2160
2161 if (keys instanceof FieldDescriptor[])
2162 {
2163 FieldDescriptor[] kd = (FieldDescriptor[]) keys;
2164 for (int i = 0; i < columns.length; i++)
2165 {
2166 columns[i] = kd[i].getColumnName();
2167 }
2168 }
2169 else
2170 {
2171 for (int i = 0; i < columns.length; i++)
2172 {
2173 columns[i] = keys[i].toString();
2174 }
2175 }
2176 return columns;
2177 }
2178
2179 void appendJoinEqualities(StringBuffer buf)
2180 {
2181 byte joinSyntax = getJoinSyntaxType();
2182
2183 for (int i = 0; i < leftKeys.length; i++)
2184 {
2185 if (i > 0)
2186 {
2187 buf.append(" AND ");
2188 }
2189 buf.append(left.alias);
2190 buf.append(".");
2191 buf.append(leftKeys[i]);
2192
2193 if (isOuter && joinSyntax == SYBASE_JOIN_SYNTAX)
2194 {
2195 buf.append("*=");
2196 }
2197 else
2198 {
2199 buf.append("=");
2200 }
2201
2202 buf.append(right.alias);
2203 buf.append(".");
2204 buf.append(rightKeys[i]);
2205
2206 if (isOuter && joinSyntax == ORACLE_JOIN_SYNTAX)
2207 {
2208 buf.append("(+)");
2209 }
2210 }
2211 }
2212
2213 public boolean equals(Object obj)
2214 {
2215 Join j = (Join) obj;
2216 return name.equals(j.name) && (isOuter == j.isOuter) && right.equals(j.right);
2217 }
2218
2219 public int hashCode()
2220 {
2221 return name.hashCode();
2222 }
2223
2224 public String toString()
2225 {
2226 return left.alias + " -> " + right.alias;
2227 }
2228 }
2229 }