SelectRequest


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