AccessSqlParser


1   /******************************************************************************
2    * The contents of this file are subject to the   Compiere License  Version 1.1
3    * ("License"); You may not use this file except in compliance with the License
4    * You may obtain a copy of the License at http://www.compiere.org/license.html
5    * Software distributed under the License is distributed on an  "AS IS"  basis,
6    * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
7    * the specific language governing rights and limitations under the License.
8    * The Original Code is                  Compiere  ERP & CRM  Business Solution
9    * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
10   * Portions created by Jorg Janke are Copyright (C) 1999-2003 Jorg Janke, parts
11   * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
12   * Contributor(s): ______________________________________.
13   *****************************************************************************/
14  package org.compiere.model;
15  
16  import java.util.*;
17  
18  import org.compiere.util.*;
19  
20  /**
21   *  Parse FROM in SQL WHERE clause
22   *  
23   *  @author Jorg Janke
24   *  @version $Id: AccessSqlParser.java,v 1.7 2003/11/07 06:37:56 jjanke Exp $
25   */
26  public class AccessSqlParser
27  {
28      /**
29       *  Base Constructor.
30       *  You need to set the SQL and start the parsing manually.
31       */
32      public AccessSqlParser ()
33      {
34      }   //  AccessSqlParser
35  
36      /**
37       *  Full Constructor 
38       *  @param sql sql command
39       */
40      public AccessSqlParser (String   sql)
41      {
42          setSql(sql);
43      }   //  AccessSqlParser
44  
45      /** FROM String         */
46      private static final String       FROM = " FROM ";
47      private static final int        FROM_LENGTH = FROM.length();
48      private static final String       WHERE = " WHERE ";
49      private static final String       ON = " ON ";
50  
51      /** Logger              */
52      private Logger  log = Logger.getCLogger(getClass());
53      /** Original SQL            */
54      private String        m_sqlOriginal;
55      /** SQL Selects         */
56      private String  []    m_sql;
57      /** List of Arrays      */
58      private ArrayList   m_tableInfo = new ArrayList(); 
59  
60      /**
61       *  Set Sql and parse it
62       *  @param sql sql
63       */
64      public void setSql (String   sql)
65      {
66          if (sql == null)
67              throw new IllegalArgumentException  ("AccessSqlParser - no SQL");
68          m_sqlOriginal = sql;
69          //
70          parse();
71      }   //  setSQL
72  
73      /**
74       *  Get (original) Sql
75       *  @return sql
76       */
77      public String   getSql()
78      {
79          return m_sqlOriginal;
80      }   //  getSql
81  
82      /**
83       *  Parse Original SQL.
84       *  Called from setSql or Constructor.
85       */
86      public boolean parse()
87      {
88          if (m_sqlOriginal == null || m_sqlOriginal.length() == 0)
89              throw new IllegalArgumentException  ("AccessSqlParser - no SQL");
90          //
91      //  if (Log.isTraceLevel(10))
92      //      log.debug("parse - " + m_sqlOriginal);
93          getSelectStatements();
94          //  analyse each select 
95          for (int i = 0; i < m_sql.length; i++)
96          {   
97              TableInfo[] info = getTableInfo(m_sql[i].trim());
98              m_tableInfo.add(info);
99          }
100         //
101         if (Log.isTraceLevel(10))
102             log.debug(toString());
103         return m_tableInfo.size() > 0;
104     }   //  parse
105 
106     /**
107      *  Parses  m_sqlOriginal and creates Array of m_sql statements
108      */
109     private void getSelectStatements()
110     {
111         String  [] sqlIn = new String  [] {m_sqlOriginal};
112         String  [] sqlOut = getSubSQL (sqlIn);
113         //  a sub-query was found
114         while (sqlIn.length != sqlOut.length)
115         {
116             sqlIn = sqlOut;
117             sqlOut = getSubSQL (sqlIn);
118         }
119         m_sql = sqlOut;
120         /** List & check **
121         for (int i = 0; i < m_sql.length; i++)
122         {
123             if (m_sql[i].indexOf("SELECT ",2) != -1)
124                 log.error("getSelectStatements #" + i + " Has embedded SQL - " + m_sql[i]);
125             else
126                 log.debug("getSelectStatements #" + i + " - " + m_sql[i]);
127         }
128         /** **/
129     }   //  getSelectStatements
130 
131     /**
132      *  Get Sub SQL of sql statements
133      *  @param sqlIn array of input sql
134      *  @return array of resulting sql
135      */
136     private String  [] getSubSQL (String  [] sqlIn)
137     {
138         ArrayList list = new ArrayList();
139         for (int sqlIndex = 0; sqlIndex < sqlIn.length; sqlIndex++)
140         {
141             String   sql = sqlIn[sqlIndex];
142             int index = sql.indexOf("(SELECT ", 7);
143             while (index != -1)
144             {
145                 int endIndex = index+1;
146                 int parenthesisLevel = 0;
147                 //  search for the end of the sql
148                 while (endIndex++ < sql.length())
149                 {
150                     char c = sql.charAt(endIndex); 
151                     if (c == ')')
152                     {
153                         if (parenthesisLevel == 0)
154                             break;
155                         else
156                             parenthesisLevel--;
157                     }
158                     else if (c == '(')
159                         parenthesisLevel++;
160                 }
161                 String   subSQL = sql.substring(index, endIndex+1);
162                 list.add(subSQL);
163                 //  remove inner SQL (##)
164                 sql = sql.substring(0,index+1) + "##" 
165                     + sql.substring(endIndex);
166                 index = sql.indexOf("(SELECT ", 7);
167             }           
168             list.add(sql);  //  last SQL
169         }
170         String  [] retValue = new String  [list.size()];
171         list.toArray(retValue);
172         return retValue;
173     }   //  getSubSQL
174 
175     /**
176      *  Get Table Info for SQL
177      *  @param sql sql
178      *  @return array of table info for sql
179      */
180     private TableInfo[] getTableInfo (String   sql)
181     {
182         ArrayList list = new ArrayList();
183         //  remove ()
184         if (sql.startsWith("(") && sql.endsWith(")"))
185             sql = sql.substring(1,sql.length()-1);
186             
187         int fromIndex = sql.indexOf(FROM);
188         if (fromIndex != sql.lastIndexOf(FROM))
189             log.error("getTableInfo - More than one FROM clause - " + sql);
190         while (fromIndex != -1)
191         {
192             String   from = sql.substring(fromIndex+FROM_LENGTH);
193             int index = from.lastIndexOf(WHERE);    //  end at where
194             if (index != -1)
195                 from = from.substring(0, index);
196             from = Util.replace(from, " AS ", " ");
197             from = Util.replace(from, " as ", " ");
198             from = Util.replace(from, " INNER JOIN ", ", ");
199             from = Util.replace(from, " LEFT OUTER JOIN ", ", ");
200             from = Util.replace(from, " RIGHT OUTER JOIN ", ", ");
201             //  Remove ON clause
202             index = from.indexOf(ON);
203             while (index != -1)
204             {
205                 int indexClose = from.indexOf(')');
206                 if (indexClose != -1)
207                     from = from.substring(0, index) + from.substring(indexClose+1);
208                 else
209                 {
210                     log.error("parse - could not remove ON " + from);
211                     break;
212                 }           
213                 index = from.indexOf(ON);
214             }
215             
216 //          log.debug("getTableInfo - " + from);
217             StringTokenizer tableST = new StringTokenizer (from, ",");
218             while (tableST.hasMoreTokens())
219             {
220                 String   tableString = tableST.nextToken().trim();
221                 StringTokenizer synST = new StringTokenizer (tableString, " ");
222                 TableInfo tableInfo = null;
223                 if (synST.countTokens() > 1)
224                     tableInfo = new TableInfo(synST.nextToken(), synST.nextToken());
225                 else
226                     tableInfo = new TableInfo(tableString);
227 //              log.debug("getTableInfo -- " + tableInfo);
228                 list.add(tableInfo);
229             }
230             //
231             sql = sql.substring(0, fromIndex);
232             fromIndex = sql.lastIndexOf(FROM);
233         }
234         TableInfo[] retValue = new TableInfo[list.size()];
235         list.toArray(retValue);
236         return retValue;
237     }   //  getTableInfo
238 
239 
240     /**
241      *  String Representation
242      *  @return info
243      */
244     public String   toString()
245     {
246         StringBuffer   sb = new StringBuffer  ("AccessSqlParser[");
247         if (m_tableInfo == null)
248             sb.append(m_sqlOriginal);
249         else
250         {
251             for (int i = 0; i < m_tableInfo.size(); i++)
252             {
253                 if (i > 0)
254                     sb.append("|");
255                 TableInfo[] info = (TableInfo[])m_tableInfo.get(i);
256                 for (int ii = 0; ii < info.length; ii++)
257                 {
258                     if (ii > 0)
259                         sb.append(",");
260                     sb.append(info[ii].toString());
261                 }
262             }
263         }
264         sb.append("|").append(getMainSqlIndex());
265         sb.append("]");
266         return sb.toString();
267     } //    toString
268 
269     /**
270      *  Get Table Info
271      *  @return table info
272      */
273     public TableInfo[] getTableInfo (int index)
274     {
275         if (index < 0 || index > m_tableInfo.size())
276             return null;
277         TableInfo[] retValue = (TableInfo[])m_tableInfo.get(index);
278         return retValue;
279     }   //  getTableInfo
280 
281     /**
282      *  Get Sql Statements
283      *  @return index index of query
284      */
285     public String   getSqlStatement (int index)
286     {
287         if (index < 0 || index > m_sql.length)
288             return null;
289         return m_sql[index];
290     }   //  getSqlStatement
291 
292     /**
293      *  Get No of SQL Statements
294      *  @return FROM clause count
295      */
296     public int getNoSqlStatments()
297     {
298         if (m_sql == null)
299             return 0;
300         return m_sql.length;
301     }   //  getNoSqlStatments
302 
303     /**
304      *  Get index of main Statements
305      *  @return index of main statement or -1 if not found
306      */
307     public int getMainSqlIndex()
308     {
309         if (m_sql == null)
310             return -1;
311         else if (m_sql.length == 1)
312             return 0;
313         for (int i = m_sql.length-1; i >= 0; i--)
314         {
315             if (m_sql[i].charAt(0) != '(')
316                 return i;
317         }
318         return -1;
319     }   //  getMainSqlIndex
320 
321     /**
322      *  Get main sql Statement
323      *  @return main statement
324      */
325     public String   getMainSql()
326     {
327         if (m_sql == null)
328             return m_sqlOriginal;
329             
330         if (m_sql.length == 1)
331             return m_sql[0];
332         for (int i = m_sql.length-1; i >= 0; i--)
333         {
334             if (m_sql[i].charAt(0) != '(')
335                 return m_sql[i];
336         }
337         return "";
338     }   //  getMainSql
339 
340     /**
341      *  Table Info VO
342      */
343     public class TableInfo
344     {
345         /**
346          *  Constructor
347          *  @param tableName table
348          *  @param synonym synonym
349          */
350         public TableInfo (String   tableName, String   synonym)
351         {
352             m_tableName = tableName;
353             m_synonym = synonym;
354         }   //  TableInfo
355         
356         /**
357          *  Short Constuctor - no syn
358          *  @param tableName table
359          */
360         public TableInfo (String   tableName)
361         {
362             this (tableName, null);
363         }   //  TableInfo
364         
365         private String   m_tableName;
366         private String   m_synonym;
367         
368         /**
369          *  Get Table Synonym
370          *  @return synonym
371          */
372         public String   getSynonym()
373         {
374             if (m_synonym == null)
375                 return "";
376             return m_synonym;
377         }   //  getSynonym
378 
379         /**
380          *  Get TableName
381          *  @return table name
382          */
383         public String   getTableName()
384         {
385             return m_tableName;
386         }   //  getTableName
387 
388         /**
389          *  String Representation
390          *  @return info
391          */
392         public String   toString()
393         {
394             StringBuffer   sb = new StringBuffer  (m_tableName);
395             if (getSynonym().length() > 0)
396                 sb.append("=").append(m_synonym);
397             return sb.toString();
398         }   //  toString
399 
400     }   //  TableInfo
401 
402 }   //  AccessSqlParser
403
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags