Java > Open Source Codes > org > compiere > model > 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

---

Free Books   Free Magazines  

Popular Tags