SelectRequest


1   /**
2    * Sequoia: Database clustering technology.
3    * Copyright (C) 2002-2004 French National Institute For Research In Computer
4    * Science And Control (INRIA).
5    * Copyright (C) 2005 AmicoSoft, Inc. dba Emic Networks
6    * Contact: sequoia@continuent.org
7    * 
8    * Licensed under the Apache License, Version 2.0 (the "License");
9    * you may not use this file except in compliance with the License.
10   * You may obtain a copy of the License at
11   * 
12   * http://www.apache.org/licenses/LICENSE-2.0
13   * 
14   * Unless required by applicable law or agreed to in writing, software
15   * distributed under the License is distributed on an "AS IS" BASIS,
16   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17   * See the License for the specific language governing permissions and
18   * limitations under the License. 
19   *
20   * Initial developer(s): Emmanuel Cecchet.
21   * Contributor(s): Julie Marguerite, Mathieu Peltier, Sara Bouchenak.
22   */
23  
24  package org.continuent.sequoia.controller.requests;
25  
26  import java.io.Serializable  ;
27  import java.sql.SQLException  ;
28  import java.util.ArrayList  ;
29  import java.util.Collection  ;
30  import java.util.ConcurrentModificationException  ;
31  import java.util.Hashtable  ;
32  import java.util.Iterator  ;
33  import java.util.SortedSet  ;
34  import java.util.StringTokenizer  ;
35  import java.util.TreeSet  ;
36  
37  import org.continuent.sequoia.common.i18n.Translate;
38  import org.continuent.sequoia.controller.semantic.SemanticBehavior;
39  import org.continuent.sequoia.controller.sql.schema.AliasedDatabaseTable;
40  import org.continuent.sequoia.controller.sql.schema.DatabaseColumn;
41  import org.continuent.sequoia.controller.sql.schema.DatabaseSchema;
42  import org.continuent.sequoia.controller.sql.schema.DatabaseTable;
43  import org.continuent.sequoia.controller.sql.schema.TableColumn;
44  
45  /**
46   * A <code>SelectRequest</code> is an SQL request returning a
47   * {@link java.sql.ResultSet}. It may also have database side-effects.
48   * <p>
49   * It has the following syntax:
50   * 
51   * <pre>
52   *  SELECT [ALL|DISTINCT] select-item[,select-item]* 
53   *  FROM table-specification[,table-specification]* 
54   *  [WHERE search-condition] 
55   *  [GROUP BY grouping-column[,grouping-column]] 
56   *  [HAVING search-condition] 
57   *  [ORDER BY sort-specification[,sort-specification]] 
58   *  [LIMIT ignored]
59   * </pre>
60   * 
61   * Note that table-specification in the <code>FROM</code> clause can be a
62   * sub-select. Everything after the end of the <code>WHERE</code> clause is
63   * ignored.
64   * 
65   * @author <a HREF="mailto:Emmanuel.Cecchet@inria.fr">Emmanuel Cecchet </a>
66   * @author <a HREF="mailto:Julie.Marguerite@inria.fr">Julie Marguerite </a>
67   * @author <a HREF="mailto:Mathieu.Peltier@inrialpes.fr">Mathieu Peltier </a>
68   * @author <a HREF="mailto:Sara.Bouchenak@epfl.ch">Sara Bouchenak </a>
69   * @version 1.0
70   */
71  public class SelectRequest extends AbstractRequest implements Serializable  
72  {
73    private static final long      serialVersionUID = 6498520472410320514L;
74  
75    /** <code>ArrayList</code> of <code>TableColumn</code> objects. */
76    protected transient ArrayList    select;
77  
78    /** <code>ArrayList</code> of <code>String</code> objects. */
79    protected transient Collection   from;
80  
81    /** <code>ArrayList</code> of <code>AliasedTable</code> objects */
82    protected transient Collection   aliasFrom;
83  
84    /** <code>ArrayList</code> of <code>TableColumn</code> objects. */
85    protected transient ArrayList    where;
86  
87    /** <code>ArrayList</code> of <code>OrderBy</code> objects */
88    protected transient ArrayList    order;
89  
90    /** Some values to keep track of function in the SELECT request */
91    public static final int        NO_FUNCTION      = 0;
92    /** Represents a SQL max() macro */
93    public static final int        MAX_FUNCTION     = 1;
94    /** Represents a SQL min() macro */
95    public static final int        MIN_FUNCTION     = 2;
96    /** Represents a SQL average() macro */
97    public static final int        AVERAGE_FUNCTION = 3;
98    /** Represents a SQL count() macro */
99    public static final int        COUNT_FUNCTION   = 4;
100   /** Represents a SQL sum() macro */
101   public static final int        SUM_FUNCTION     = 5;
102 
103   /** Need to keep track of type of query, e.g. MAX, COUNT, etc. */
104   public transient int           funcType         = 0;
105 
106   /** Primary key value in case of a unique selection */
107   protected transient String       pkValue          = null;
108 
109   /**
110    * <code>Hashtable</code> of String keys corresponding to column names and
111    * String values corresponding to the values associated with the UNIQUE
112    * columns of a UNIQUE SELECT.
113    * <p>
114    * Used with the <code>COLUMN_UNIQUE_DELETE</code> granularity.
115    * 
116    * @see org.continuent.sequoia.controller.cache.result.CachingGranularities
117    */
118   protected transient Hashtable    whereValues;
119 
120   /**
121    * Creates a new <code>SelectRequest</code> instance. The caller must give
122    * an SQL request, without any leading or trailing spaces and beginning with
123    * the 'select' keyword (it will not be checked).
124    * <p>
125    * The request is not parsed but it can be done later by a call to
126    * {@link #parse(DatabaseSchema, int, boolean)}.
127    * 
128    * @param sqlQuery the SQL query
129    * @param escapeProcessing should the driver to escape processing before
130    *          sending to the database ?
131    * @param timeout an <code>int</code> value
132    * @param lineSeparator the line separator used in the query
133    * @see #parse
134    */
135   public SelectRequest(String   sqlQuery, boolean escapeProcessing, int timeout,
136       String   lineSeparator)
137   {
138     super(sqlQuery, escapeProcessing, timeout, lineSeparator,
139         RequestType.SELECT);
140     isReadOnly = true;
141   }
142 
143   /**
144    * @see AbstractRequest#AbstractRequest(java.lang.String, boolean, int,
145    *      java.lang.String, int)
146    */
147   protected SelectRequest(String   sqlQuery, boolean escapeProcessing,
148       int timeout, String   lineSeparator, int type)
149   {
150     super(sqlQuery, escapeProcessing, timeout, lineSeparator, type);
151     isReadOnly = true;
152   }
153 
154   /**
155    * @see org.continuent.sequoia.controller.requests.AbstractRequest#altersAggregateList()
156    */
157   public boolean altersAggregateList()
158   {
159     return false;
160   }
161 
162   /**
163    * @see org.continuent.sequoia.controller.requests.AbstractRequest#altersDatabaseCatalog()
164    */
165   public boolean altersDatabaseCatalog()
166   {
167     return false;
168   }
169 
170   /**
171    * @see org.continuent.sequoia.controller.requests.AbstractRequest#altersDatabaseSchema()
172    */
173   public boolean altersDatabaseSchema()
174   {
175     return false;
176   }
177 
178   /**
179    * @see org.continuent.sequoia.controller.requests.AbstractRequest#altersMetadataCache()
180    */
181   public boolean altersMetadataCache()
182   {
183     return false;
184   }
185 
186   /**
187    * @see org.continuent.sequoia.controller.requests.AbstractRequest#altersQueryResultCache()
188    */
189   public boolean altersQueryResultCache()
190   {
191     return false;
192   }
193 
194   /**
195    * @see org.continuent.sequoia.controller.requests.AbstractRequest#altersSomething()
196    */
197   public boolean altersSomething()
198   {
199     return false;
200   }
201 
202   /**
203    * @see org.continuent.sequoia.controller.requests.AbstractRequest#altersStoredProcedureList()
204    */
205   public boolean altersStoredProcedureList()
206   {
207     return false;
208   }
209 
210   /**
211    * @see org.continuent.sequoia.controller.requests.AbstractRequest#altersUserDefinedTypes()
212    */
213   public boolean altersUserDefinedTypes()
214   {
215     return false;
216   }
217 
218   /**
219    * @see org.continuent.sequoia.controller.requests.AbstractRequest#altersUsers()
220    */
221   public boolean altersUsers()
222   {
223     return false;
224   }
225 
226   /**
227    * <p>
228    * The result of the parsing is accessible through the {@link #getSelect()},
229    * {@link #getFrom()}and {@link #getWhere()}functions.
230    * 
231    * @see org.continuent.sequoia.controller.requests.AbstractRequest#parse(org.continuent.sequoia.controller.sql.schema.DatabaseSchema,
232    *      int, boolean)
233    */
234   public void parse(DatabaseSchema schema, int granularity,
235       boolean isCaseSensitive) throws SQLException  
236   {
237     try
238     {
239       if ((granularity == ParsingGranularities.NO_PARSING) || (schema == null))
240       {
241         cacheable = RequestType.UNCACHEABLE;
242         isParsed = true;
243         return;
244       }
245 
246       String   originalSQL = this.trimCarriageReturnAndTabs();
247 
248       while (originalSQL.charAt(0) == '(')
249         originalSQL = originalSQL.substring(1);
250 
251       String   sql = originalSQL.toLowerCase();
252       if (!isCaseSensitive)
253         originalSQL = sql;
254 
255       // Strip 'select'
256       sql = sql.substring(6).trim();
257 
258       // Look for DISTINCT
259       if (sql.startsWith("distinct"))
260         sql = sql.substring(8).trim(); // Strip 'distinct '
261 
262       // Look for the begining of the FROM clause
263       int fromIndex = sql.indexOf("from ");
264       if (fromIndex == -1)
265         // No FROM keyword found, stop here
266         return;
267 
268       // Keep SELECT clause for later, we first have to check the
269       // tables involved in the FROM clause
270       int fshift = originalSQL.length() - sql.length();
271       String   selectClause = (isCaseSensitive) ? originalSQL.substring(fshift,
272           fshift + fromIndex) : sql.substring(0, fromIndex);
273 
274       // Get rid of FROM
275       sql = sql.substring(fromIndex + 5).trim();
276 
277       // Now find the boundaries of the FROM and WHERE clauses
278       int whereIndex = 0;
279       int parenthesis = 0;
280       int lastParenthesisIdx = 0;
281       boolean foundWhere = false;
282       do
283       {
284         switch (sql.charAt(whereIndex))
285         {
286           case '(' :
287             parenthesis++;
288             break;
289           case ')' :
290             parenthesis--;
291             lastParenthesisIdx = whereIndex;
292             break;
293           case 'w' :
294             if (parenthesis == 0)
295               try
296               {
297                 foundWhere = (sql.charAt(whereIndex + 1) == 'h')
298                     && (sql.charAt(whereIndex + 2) == 'e')
299                     && (sql.charAt(whereIndex + 3) == 'r')
300                     && (sql.charAt(whereIndex + 4) == 'e');
301               }
302               catch (StringIndexOutOfBoundsException   ignore)
303               {
304                 foundWhere = false;
305               }
306             break;
307           default :
308             break;
309         }
310         whereIndex++;
311       }
312       while ((!foundWhere) && (whereIndex < sql.length()));
313       if (foundWhere)
314         whereIndex--;
315       else
316         whereIndex = -1;
317 
318       // Warning! Here if whereIndex is -1 (no where clause)
319       // endWhere is used to find the end of the FROM clause.
320       // The variable name can be misleading but it's faster to do it this
321       // way.
322       int endWhere = sql.indexOf("group by ", lastParenthesisIdx);
323       if (endWhere == -1)
324       {
325         endWhere = sql.indexOf("having ", lastParenthesisIdx);
326         if (endWhere == -1)
327         {
328           endWhere = sql.indexOf("order by ", lastParenthesisIdx);
329           if (endWhere == -1)
330           {
331             endWhere = sql.indexOf("limit ", lastParenthesisIdx);
332             if (endWhere == -1)
333               endWhere = sql.length();
334           }
335         }
336       }
337       int endFrom;
338       if (whereIndex == -1)
339         endFrom = endWhere;
340       else
341         endFrom = whereIndex;
342 
343       try
344       {
345         switch (granularity)
346         {
347           case ParsingGranularities.NO_PARSING :
348             return;
349           case ParsingGranularities.TABLE :
350             int shift = originalSQL.length() - sql.length();
351             from = getFromTables(originalSQL.substring(shift, shift + endFrom)
352                 .trim(), schema, isCaseSensitive);
353             break;
354           case ParsingGranularities.COLUMN :
355           case ParsingGranularities.COLUMN_UNIQUE :
356             shift = originalSQL.length() - sql.length();
357             from = getFromTables(originalSQL.substring(shift, shift + endFrom)
358                 .trim(), schema, isCaseSensitive);
359             // Find columns selected in the SELECT clause
360             select = getSelectedColumns(selectClause, from, isCaseSensitive);
361             if (whereIndex > 1)
362               // Find columns involved in the WHERE clause (5="WHERE")
363               where = getWhereColumns(originalSQL.substring(
364                   shift + whereIndex + 5, shift + endWhere).trim(), from,
365                   granularity == ParsingGranularities.COLUMN_UNIQUE,
366                   isCaseSensitive);
367             break;
368           default :
369             throw new SQLException  ("Unsupported parsing granularity: '"
370                 + granularity + "'");
371         }
372       }
373       catch (SQLException   e)
374       {
375         from = null;
376         select = null;
377         where = null;
378         cacheable = RequestType.UNCACHEABLE;
379         throw e;
380       }
381 
382       // Gokul added this
383       // I need to have the aliases to determine if any of the OrderBy columns
384       // are referenced using their alias
385 
386       aliasFrom = from;
387 
388       if (from != null)
389       {
390         // Convert 'from' to an ArrayList of String objects instead of
391         // AliasedTables objects
392         int size = from.size();
393         ArrayList   unaliased = new ArrayList  (size);
394         for (Iterator   iter = from.iterator(); iter.hasNext();)
395           unaliased.add(((AliasedDatabaseTable) iter.next()).getTable()
396               .getName());
397         from = unaliased;
398       }
399 
400       isParsed = true;
401     }
402     finally
403     {
404       if (isParsed)
405       {
406         SortedSet   readSet = null;
407         if (from != null)
408           readSet = new TreeSet  (from);
409         setSemantic(new SemanticBehavior(readSet, writeLockedTables, null,
410             altersDatabaseSchema(), altersMetadataCache(),
411             altersQueryResultCache(), altersUsers(), isReadOnly,
412             needsMacroProcessing(), SemanticBehavior.SERIALIZABLE_ORDER,
413             requiresConnectionPoolFlush
414                 ? SemanticBehavior.FLUSH_ALL_USERS
415                 : SemanticBehavior.FLUSH_NONE));
416       }
417     }
418   }
419 
420   /**
421    * @see AbstractRequest#cloneParsing(AbstractRequest)
422    */
423   public void cloneParsing(AbstractRequest request)
424   {
425     if (!request.isParsed())
426       return;
427     cloneParsingCommons(request);
428     SelectRequest selectRequest = (SelectRequest) request;
429     select = selectRequest.getSelect();
430     from = selectRequest.getFrom();
431     where = selectRequest.getWhere();
432     pkValue = selectRequest.getPkValue();
433     isParsed = true;
434   }
435 
436   /**
437    * Extracts the tables from the given <code>FROM</code> clause and retrieves
438    * their alias if any.
439    * 
440    * @param fromClause the <code>FROM</code> clause of the request (without
441    *          the <code>FROM</code> keyword)
442    * @param schema the <code>DatabaseSchema</code> this request refers to
443    * @param isCaseSensitive true if table name parsing is case sensitive
444    * @return an <code>ArrayList</code> of <code>AliasedDatabaseTable</code>
445    *         objects
446    * @exception SQLException if an error occurs
447    */
448   private Collection   getFromTables(String   fromClause, DatabaseSchema schema,
449       boolean isCaseSensitive) throws SQLException  
450   {
451     ArrayList   result = new ArrayList  ();
452 
453     // Search for subselects in from clause
454     try
455     {
456       int subSelect = fromClause.toLowerCase().indexOf("select ");
457       while (subSelect != -1)
458       {
459         int subFromIndex = fromClause.indexOf("from", subSelect + 1) + 5;
460         int bracket = subFromIndex;
461         int parenthesis = 1;
462         do
463         {
464           char c = fromClause.charAt(bracket);
465           switch (c)
466           {
467             case '(' :
468               parenthesis++;
469               break;
470             case ')' :
471               parenthesis--;
472               break;
473             default :
474               break;
475           }
476           bracket++;
477         }
478         while ((parenthesis > 0) && (bracket < fromClause.length()));
479 
480         SelectRequest subQuery = new SelectRequest(fromClause.substring(
481             subSelect, bracket - 1).trim(), this.escapeProcessing, 0,
482             getLineSeparator());
483         subQuery.setSemanticManager(getSemanticManager());
484         subQuery.parse(schema, ParsingGranularities.TABLE, isCaseSensitive);
485         for (Iterator   iter = subQuery.getFrom().iterator(); iter.hasNext();)
486         {
487           result.add(new AliasedDatabaseTable(schema.getTable((String  ) iter
488               .next(), isCaseSensitive), null));
489         }
490 
491         if (subFromIndex + bracket > fromClause.length())
492         {
493           if (subSelect > 0)
494           {
495             fromClause = fromClause.substring(0, subSelect - 1).trim();
496             if ((fromClause.length() > 0)
497                 && (fromClause.charAt(fromClause.length() - 1) == '('))
498               fromClause = fromClause.substring(0, fromClause.length() - 1)
499                   .trim();
500           }
501           else
502             fromClause = "";
503           break; // Nothing more to process
504         }
505         fromClause = (subSelect > 0 ? fromClause.substring(0, subSelect - 1)
506             .trim() : "")
507             + fromClause.substring(subFromIndex + bracket).trim();
508         subSelect = fromClause.toLowerCase().indexOf("select");
509       }
510     }
511     catch (RuntimeException   e)
512     {
513       // Parsing failed, select everything
514       Collection   unaliasedTables = schema.getTables().values();
515       ArrayList   fromAliasedTables = new ArrayList  (unaliasedTables.size());
516       for (Iterator   iter = unaliasedTables.iterator(); iter.hasNext();)
517       {
518         DatabaseTable t = (DatabaseTable) iter.next();
519         fromAliasedTables.add(new AliasedDatabaseTable(t, null));
520       }
521       return fromAliasedTables;
522     }
523 
524     // Use a brutal force technique by matching schema table names in the from
525     // clause
526     Collection   tables = schema.getTables().values();
527     // Note that we use an iterator here since the tables might be modified
528     // concurrently by a write query that alters the database schema. In case
529     // of a concurrent modification, iter.next() will fail and we will restart
530     // the parsing and this will prevent the disgracious error message reported
531     // by BUG #303423.
532     for (Iterator   iter = tables.iterator(); iter.hasNext();)
533     {
534       // Check if this table is found in the FROM string
535       DatabaseTable t;
536       try
537       {
538         t = (DatabaseTable) iter.next();
539       }
540       catch (ConcurrentModificationException   race)
541       {
542         iter = tables.iterator();
543         continue;
544       }
545       String   tableName = t.getName();
546       if (!isCaseSensitive)
547         tableName = tableName.toLowerCase();
548 
549       // Check that we have a full match and not a partial match
550       int index;
551       int afterTableNameIndex = 0;
552       boolean left;
553       boolean right;
554       do
555       {
556         index = fromClause.indexOf(tableName, afterTableNameIndex);
557         if (index == -1)
558           break;
559         afterTableNameIndex = index + tableName.length();
560         left = (index == 0)
561             || ((index > 0) && ((fromClause.charAt(index - 1) == ' ')
562                 || (fromClause.charAt(index - 1) == '(')
563                 || (fromClause.charAt(index - 1) == ',') || (fromClause
564                 .charAt(index - 1) == getLineSeparator().charAt(
565                 getLineSeparator().length() - 1))));
566         right = (afterTableNameIndex >= fromClause.length())
567             || ((afterTableNameIndex < fromClause.length()) && ((fromClause
568                 .charAt(afterTableNameIndex) == ' ')
569                 || (fromClause.charAt(afterTableNameIndex) == ',')
570                 || (fromClause.charAt(afterTableNameIndex) == ')') || (fromClause
571                 .charAt(afterTableNameIndex) == getLineSeparator().charAt(0))));
572       }
573       while (!left || !right);
574       if (index != -1)
575       {
576         // Check if the table has an alias
577         // Example: SELECT x.price FROM item x
578         String   alias = null;
579         index += tableName.length();
580         if ((index < fromClause.length()) && (fromClause.charAt(index) == ' '))
581         {
582           char c;
583           // Skip spaces before alias
584           do
585           {
586             c = fromClause.charAt(index);
587             index++;
588           }
589           while ((index < fromClause.length()) && (c != ' ')
590               && (c != getLineSeparator().charAt(0)));
591           if (index < fromClause.length())
592           {
593             int start = index;
594             do
595             {
596               c = fromClause.charAt(index);
597               index++;
598             }
599             while ((index < fromClause.length()) && (c != ' ') && (c != ',')
600                 && (c != getLineSeparator().charAt(0)));
601             alias = fromClause.substring(start, index - 1);
602           }
603         }
604         result.add(new AliasedDatabaseTable(t, alias));
605       }
606     }
607 
608     return result;
609   }
610 
611   /**
612    * Gets all the columns selected in the given <code>SELECT</code> clause.
613    * <p>
614    * The selected columns or tables must be found in the given
615    * <code>ArrayList</code> of <code>AliasedDatabaseTable</code>
616    * representing the <code>FROM</code> clause of the same request.
617    * 
618    * @param selectClause <code>SELECT</code> clause of the request (without
619    *          the <code>SELECT</code> keyword)
620    * @param aliasedFrom a <code>Collection</code> of
621    *          <code>AliasedDatabaseTable</code>
622    * @param isCaseSensitive true if column name parsing is case sensitive
623    * @return an <code>ArrayList</code> of <code>TableColumn</code>
624    */
625   private ArrayList   getSelectedColumns(String   selectClause,
626       Collection   aliasedFrom, boolean isCaseSensitive)
627   {
628     StringTokenizer   selectTokens = new StringTokenizer  (selectClause, ",");
629     ArrayList   result = new ArrayList  ();
630     StringBuffer   unresolvedTokens = null;
631 
632     while (selectTokens.hasMoreTokens())
633     {
634       String   token = selectTokens.nextToken().trim();
635       // Check if it is a function, e.g., MAX, COUNT, etc.
636       if (isSqlFunction(token))
637       {
638         // token has the following form:
639         // max(...)
640         // or
641         // count(...)
642         int leftPar = token.indexOf("(");
643         token = token.substring(leftPar + 1, token.length() - 1);
644       }
645       // Is it using an aliased table name (x.price for example) ?
646       String   alias = null;
647       int aliasIdx = token.indexOf(".");
648       if (aliasIdx != -1)
649       {
650         alias = token.substring(0, aliasIdx);
651         token = token.substring(aliasIdx + 1); // Get rid of the '.'
652       }
653 
654       // Discard any AS clause
655       int as = token.indexOf(" as ");
656       if (as != -1)
657         token = token.substring(0, as).trim();
658 
659       // Now token only contains the column name
660 
661       // Deal with SELECT * or x.*
662       if (token.indexOf("*") != -1)
663       {
664         if (alias == null)
665         {
666           // We have to take all colums of all tables of the FROM
667           // clause
668           for (Iterator   iter = aliasedFrom.iterator(); iter.hasNext();)
669           {
670             DatabaseTable t = ((AliasedDatabaseTable) iter.next()).getTable();
671             ArrayList   cols = t.getColumns();
672             int colSize = cols.size();
673             for (int j = 0; j < colSize; j++)
674               result.add(new TableColumn(t.getName(), ((DatabaseColumn) cols
675                   .get(j)).getName()));
676           }
677           return result;
678         }
679         else
680         {
681           // Add all colums of the table corresponding to the alias
682           for (Iterator   iter = aliasedFrom.iterator(); iter.hasNext();)
683           {
684             AliasedDatabaseTable adt = (AliasedDatabaseTable) iter.next();
685             // The alias could be the full name of the table
686             // instead of a "real" alias
687             if (alias.equals(adt.getAlias())
688                 || alias.equals(adt.getTable().getName()))
689             {
690               DatabaseTable t = adt.getTable();
691               ArrayList   cols = t.getColumns();
692               int colSize = cols.size();
693               for (int j = 0; j < colSize; j++)
694                 result.add(new TableColumn(t.getName(), ((DatabaseColumn) cols
695                     .get(j)).getName()));
696               break;
697             }
698           }
699         }
700         continue;
701       }
702 
703       // First, we suppose that it's a simple column name.
704       // If it fails, we will consider it later.
705       DatabaseColumn col = null;
706 
707       if (alias == null)
708       {
709         for (Iterator   iter = aliasedFrom.iterator(); iter.hasNext();)
710         {
711           DatabaseTable t = ((AliasedDatabaseTable) iter.next()).getTable();
712           col = t.getColumn(token, isCaseSensitive);
713           if (col != null)
714           {
715             result.add(new TableColumn(t.getName(), col.getName()));
716             break;
717           }
718         }
719       }
720       else
721       // same with an alias
722       {
723         for (Iterator   iter = aliasedFrom.iterator(); iter.hasNext();)
724         {
725           AliasedDatabaseTable t = (AliasedDatabaseTable) iter.next();
726           // It can be either an alias or the fully qualified name of
727           // the table
728           if (alias.equals(t.getAlias())
729               || alias.equals(t.getTable().getName()))
730           {
731             col = t.getTable().getColumn(token, isCaseSensitive);
732             if (col != null)
733             {
734               result
735                   .add(new TableColumn(t.getTable().getName(), col.getName()));
736               break;
737             }
738           }
739         }
740       }
741 
742       if (col == null)
743       {
744         if (unresolvedTokens == null)
745           unresolvedTokens = new StringBuffer  ();
746         unresolvedTokens.append(token);
747         unresolvedTokens.append(" ");
748       }
749     }
750 
751     if (unresolvedTokens != null)
752     {
753       // Those tokens may be complex expressions, so instead of parsing
754       // them, we use a brutal force technique and we try to directly
755       // identify every column name of each table.
756       DatabaseColumn col;
757 
758       String   unresolvedTokensString = unresolvedTokens.toString();
759       if (!isCaseSensitive)
760         unresolvedTokensString = unresolvedTokensString.toLowerCase();
761 
762       for (Iterator   iter = aliasedFrom.iterator(); iter.hasNext();)
763       {
764         DatabaseTable t = ((AliasedDatabaseTable) iter.next()).getTable();
765         ArrayList   cols = t.getColumns();
766         int size = cols.size();
767         for (int j = 0; j < size; j++)
768         {
769           col = (DatabaseColumn) cols.get(j);
770           String   columnName = col.getName();
771           if (!isCaseSensitive)
772             columnName = columnName.toLowerCase();
773 
774           // if pattern found and column not already in result, it's a
775           // dependency !
776           int matchIdx = unresolvedTokensString.indexOf(columnName);
777           if (matchIdx != -1)
778             if ((matchIdx == 0)
779                 || (unresolvedTokens.charAt(matchIdx - 1) == ' ')
780                 || (unresolvedTokens.charAt(matchIdx - 1) == '(')
781                 || (unresolvedTokens.charAt(matchIdx - 1) == '.'))
782             {
783               TableColumn c = new TableColumn(t.getName(), col.getName());
784               if (!result.contains(c))
785                 result.add(c);
786             }
787         }
788       }
789     }
790     return result;
791   }
792 
793   /**
794    * Checks if the string parameter represents an SQL function, e. g., MAX,
795    * COUNT, SUM, etc.
796    * 
797    * @param str A lower-case string that may represent an SQL function
798    * @return boolean <code>true</code> if it is an SQL function and
799    *         <code>false</code> otherwise.
800    */
801   private boolean isSqlFunction(String   str)
802   {
803 
804     if (str != null)
805     {
806       if (str.startsWith("max(") && str.endsWith(")"))
807       {
808         funcType = SelectRequest.MAX_FUNCTION;
809         return true;
810       }
811       else if (str.startsWith("count(") && str.endsWith(")"))
812       {
813         funcType = SelectRequest.COUNT_FUNCTION;
814         return true;
815       }
816       else if (str.startsWith("avg(") && str.endsWith(")"))
817       {
818         funcType = SelectRequest.AVERAGE_FUNCTION;
819         return true;
820       }
821       else if (str.startsWith("min(") && str.endsWith(")"))
822       {
823         funcType = SelectRequest.MIN_FUNCTION;
824         return true;
825       }
826       else if (str.startsWith("sum(") && str.endsWith(")"))
827       {
828         funcType = SelectRequest.SUM_FUNCTION;
829         return true;
830       }
831       else
832       {
833         funcType = SelectRequest.NO_FUNCTION;
834         return false;
835       }
836     }
837     else
838       return false;
839   }
840 
841   /**
842    * Gets all the columns involved in the given <code>WHERE</code> clause.
843    * <p>
844    * The selected columns or tables must be found in the given
845    * <code>ArrayList</code> of <code>AliasedDatabaseTable</code>
846    * representing the <code>FROM</code> clause of the same request.
847    * 
848    * @param whereClause <code>WHERE</code> clause of the request (without the
849    *          <code>WHERE</code> keyword)
850    * @param aliasedFrom a <code>Collection</code> of
851    *          <code>AliasedDatabaseTable</code>
852    * @param setUniqueCacheable true if we have to check is this select is
853    *          <code>UNIQUE</code> or not
854    * @param isCaseSensitive true if column name parsing is case sensitive
855    * @return an <code>ArrayList</code> of <code>TableColumn</code>
856    */
857   private ArrayList   getWhereColumns(String   whereClause, Collection   aliasedFrom,
858       boolean setUniqueCacheable, boolean isCaseSensitive)
859   {
860     ArrayList   result = new ArrayList  (); // TableColumn
861     // objects
862 
863     if (!isCaseSensitive)
864       whereClause = whereClause.toLowerCase();
865 
866     // Instead of parsing the clause, we use a brutal force technique
867     // and we try to directly identify every column name of each table.
868     DatabaseColumn col;
869     for (Iterator   iter = aliasedFrom.iterator(); iter.hasNext();)
870     {
871       DatabaseTable t = ((AliasedDatabaseTable) iter.next()).getTable();
872       ArrayList   cols = t.getColumns();
873       int size = cols.size();
874       for (int j = 0; j < size; j++)
875       {
876         col = (DatabaseColumn) cols.get(j);
877         // if pattern found and column not already in result, it's a
878         // dependency !
879         String   columnName = col.getName();
880         if (!isCaseSensitive)
881           columnName = columnName.toLowerCase();
882 
883         int matchIdx = whereClause.indexOf(columnName);
884         while (matchIdx > 0)
885         {
886           // Try to check that we got the full pattern and not a
887           // sub-pattern
888           char beforePattern = whereClause.charAt(matchIdx - 1);
889           if (((beforePattern >= 'a') && (beforePattern <= 'z'))
890               || ((beforePattern >= 'A') && (beforePattern <= 'Z'))
891               || (beforePattern == '_'))
892             matchIdx = whereClause.indexOf(columnName, matchIdx + 1);
893           else
894           {
895             char afterPattern;
896             try
897             {
898               afterPattern = whereClause.charAt(matchIdx + columnName.length());
899               if (((afterPattern >= 'a') && (afterPattern <= 'z'))
900                   || ((afterPattern >= 'A') && (afterPattern <= 'Z'))
901                   || (afterPattern == '_'))
902               {
903                 // This is a subset of the full name of another
904                 // column,
905                 // let's jump to next mathing pattern
906                 matchIdx = whereClause.indexOf(columnName, matchIdx + 1);
907               }
908               else
909                 break;
910             }
911             catch (IndexOutOfBoundsException   e)
912             {
913               break;
914             }
915           }
916         }
917         if (matchIdx == -1)
918           continue;
919         result.add(new TableColumn(t.getName(), col.getName()));
920 
921         if (setUniqueCacheable)
922         { // Check if this request selects a
923           // unique row
924           if (!col.isUnique())
925           { // Column has no unicity constraint,
926             // we can select multiple rows
927             // with same value, give up.
928             setUniqueCacheable = false;
929             continue;
930           }
931 
932           // Check if the column is in the left side of an equality
933           // with a
934           // constant.
935           // e.g.: 'column_name1 = 10' is ok
936           // but '5=table_name.column_name2' will fail
937 
938           int lookingForEqual = matchIdx + columnName.length();
939           boolean searchReverse = false;
940           try
941           {
942             while (whereClause.charAt(lookingForEqual) == ' ')
943               lookingForEqual++;
944           }
945           catch (Exception   e)
946           {
947             searchReverse = true;
948           }
949 
950           String   rightSide;
951 
952           if (searchReverse || (whereClause.charAt(lookingForEqual) != '='))
953           {
954             try
955             {
956               // try reverse
957               StringBuffer   sb = new StringBuffer  (whereClause.substring(0,
958                   lookingForEqual));
959               String   reverse = sb.reverse().toString();
960               reverse = reverse.substring(reverse.indexOf('=') + 1);
961               sb = new StringBuffer  (reverse);
962               // Get back the original values
963               sb = sb.reverse();
964               rightSide = sb.toString();
965             }
966             catch (Exception   e)
967             {
968               // No equality, it is not unique cacheable
969               setUniqueCacheable = false;
970               continue;
971             }
972           }
973           else
974           {
975             // We found it let's move to next char
976             int nextSpace = lookingForEqual + 1;
977             try
978             {
979               while (whereClause.charAt(nextSpace) == ' ')
980                 nextSpace++;
981             }
982             catch (Exception   e1)
983             { // This should not happen
984               // unless we get a query like:
985               // 'select ... where id= '
986               setUniqueCacheable = false;
987               continue;
988             }
989 
990             rightSide = whereClause.substring(nextSpace);
991           }
992           char firstChar = rightSide.charAt(0);
993           if ((firstChar == '\'') || (firstChar == '"')
994               || ((firstChar >= '0') && (firstChar <= '9'))
995               || (firstChar == '?'))
996           { // Ok, the value is either
997             // '...' or "..." or starts
998             // with a
999             // number which is enough for us to consider that it is
1000            // an
1001            // acceptable constant.
1002            pkValue = rightSide;
1003          }
1004          else
1005          {
1006            setUniqueCacheable = false;
1007            continue;
1008          }
1009        }
1010      }
1011    }
1012
1013    if (setUniqueCacheable && !result.isEmpty())
1014      cacheable = RequestType.UNIQUE_CACHEABLE;
1015
1016    return result;
1017  }
1018
1019  /**
1020   * Returns a <code>Collection</code> of <code>AliasedDatabaseTable</code>
1021   * objects representing the table names found in the <code>FROM</code>
1022   * clause of this request.
1023   * 
1024   * @return a <code>Collection</code> of <code>AliasedDatabaseTable</code>
1025   */
1026  public Collection   getAliasedFrom()
1027  {
1028    return aliasFrom;
1029  }
1030
1031  /**
1032   * Returns a <code>Collection</code> of <code>String</code> objects
1033   * representing the table names found in the <code>FROM</code> clause of
1034   * this request.
1035   * 
1036   * @return a <code>Collection</code> of <code>String</code>
1037   */
1038  public Collection   getFrom()
1039  {
1040    return from;
1041  }
1042
1043  /**
1044   * Returns an <code>ArrayList</code> of <code>OrderBy</code> objects
1045   * representing the columns involved in the <code>ORDER BY</code> clause of
1046   * this request.
1047   * 
1048   * @return an <code>ArrayList</code> of <code>OrderBy</code>
1049   */
1050  public ArrayList   getOrderBy()
1051  {
1052    return order;
1053  }
1054
1055  /**
1056   * @return Returns the pkValue.
1057   */
1058  public String   getPkValue()
1059  {
1060    return pkValue;
1061  }
1062
1063  /**
1064   * Returns an <code>ArrayList</code> of <code>DatabaseColumn</code>
1065   * objects representing the columns selected in the <code>SELECT</code>
1066   * clause of this request.
1067   * 
1068   * @return an <code>ArrayList</code> of <code>TableColumn</code>
1069   */
1070  public ArrayList   getSelect()
1071  {
1072    return select;
1073  }
1074
1075  /**
1076   * Returns an <code>ArrayList</code> of <code>TableColumn</code> objects
1077   * representing the columns involved in the <code>WHERE</code> clause of
1078   * this request.
1079   * 
1080   * @return an <code>ArrayList</code> of <code>TableColumn</code>
1081   */
1082  public ArrayList   getWhere()
1083  {
1084    return where;
1085  }
1086
1087  /**
1088   * Returns an <code>Hashtable</code> of <code>String</code> keys
1089   * representing unique column names and <code>String</code> values
1090   * associated with the columns involved in this request.
1091   * 
1092   * @return an <code>Hashtable</code> value
1093   */
1094  public Hashtable   getWhereValues()
1095  {
1096    return whereValues;
1097  }
1098
1099  /**
1100   * Returns the mustBroadcast value (now a wrapper on isReadOnly).
1101   * 
1102   * @return Returns the mustBroadcast.
1103   */
1104  public boolean isMustBroadcast()
1105  {
1106    if (getSemantic() != null)
1107      return !getSemantic().isReadOnly();
1108    else
1109      return !isReadOnly;
1110  }
1111
1112  /**
1113   * @return <code>true</code>
1114   * @see org.continuent.sequoia.controller.requests.AbstractRequest#needsMacroProcessing()
1115   */
1116  public boolean needsMacroProcessing()
1117  {
1118    return true;
1119  }
1120
1121  /**
1122   * Does this request returns a ResultSet?
1123   * 
1124   * @return false
1125   */
1126  public boolean returnsResultSet()
1127  {
1128    return true;
1129  }
1130
1131  /**
1132   * Sets the mustBroadcast value (now a wrapper on isReadOnly).
1133   * 
1134   * @param mustBroadcast The mustBroadcast to set.
1135   * @deprecated use isReadOnly instead
1136   */
1137  public void setMustBroadcast(boolean mustBroadcast)
1138  {
1139    this.isReadOnly = !mustBroadcast;
1140  }
1141
1142  /**
1143   * @param pkValue The pkValue to set.
1144   */
1145  public void setPkValue(String   pkValue)
1146  {
1147    this.pkValue = pkValue;
1148  }
1149
1150  /**
1151   * @see org.continuent.sequoia.controller.requests.AbstractRequest#getParsingResultsAsString()
1152   */
1153  public String   getParsingResultsAsString()
1154  {
1155    StringBuffer   sb = new StringBuffer  (super.getParsingResultsAsString());
1156    if (select != null && select.size() > 0)
1157    {
1158      sb.append(Translate.get("request.select.selects"));
1159      for (int i = 0; i < select.size(); i++)
1160      {
1161        sb.append(Translate.get("request.select.select", select.get(i)));
1162      }
1163    }
1164    if (from != null && from.size() > 0)
1165    {
1166      sb.append(Translate.get("request.select.froms"));
1167      for (int i = 0; i < from.size(); i++)
1168      {
1169        sb.append(Translate.get("request.select.from", from.toArray()[i]));
1170      }
1171    }
1172    if (aliasFrom != null && aliasFrom.size() > 0)
1173    {
1174      sb.append(Translate.get("request.select.alias.froms"));
1175      for (int i = 0; i < aliasFrom.size(); i++)
1176      {
1177        sb.append(Translate.get("request.select.alias.from",
1178            ((AliasedDatabaseTable) aliasFrom.toArray()[i]).getAlias()));
1179      }
1180    }
1181    if (where != null && where.size() > 0)
1182    {
1183      sb.append(Translate.get("request.select.wheres"));
1184      for (int i = 0; i < where.size(); i++)
1185      {
1186        sb.append(Translate.get("request.select.where", where.toArray()[i]));
1187      }
1188    }
1189    if (order != null && order.size() > 0)
1190    {
1191      sb.append(Translate.get("request.select.orders"));
1192      for (int i = 0; i < order.size(); i++)
1193      {
1194        sb.append(Translate.get("request.select.order", where.toArray()[i]));
1195      }
1196    }
1197    return sb.toString();
1198  }
1199
1200  /**
1201   * Displays some debugging information about this request.
1202   */
1203  public void debug()
1204  {
1205    super.debug();
1206    if (select != null)
1207    {
1208      System.out.println("Selected columns:");
1209      for (int i = 0; i < select.size(); i++)
1210        System.out
1211            .println("  " + ((TableColumn) select.get(i)).getColumnName());
1212    }
1213    else
1214      System.out.println("No information on selected columns");
1215
1216    if (select != null)
1217    {
1218      System.out.println("");
1219      System.out.println("From tables:");
1220      for (Iterator   iter = from.iterator(); iter.hasNext();)
1221        for (int i = 0; i < from.size(); i++)
1222          System.out.println("  " + iter.next());
1223    }
1224    else
1225      System.out.println("No information on from tables");
1226
1227    System.out.println("");
1228    System.out.println("Where columns:");
1229    if (where == null)
1230      System.out.println("  No Where clause");
1231    else
1232      for (int i = 0; i < where.size(); i++)
1233        System.out.print("  " + ((TableColumn) where.get(i)).getColumnName());
1234
1235    System.out.println("");
1236    System.out.println("PK value: " + pkValue);
1237  }
1238
1239}
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags