tinySQLParser


1   /*
2    * tinySQLParser
3    * 
4    * $Author: davis $
5    * $Date: 2004/12/18 21:28:17 $
6    * $Revision: 1.1 $
7    *
8    * This simple token based parser replaces the CUP generated parser
9    * simplifying extensions and reducing the total amount of code in 
10   * tinySQL considerably.
11   *
12   * This library is free software; you can redistribute it and/or
13   * modify it under the terms of the GNU Lesser General Public
14   * License as published by the Free Software Foundation; either
15   * version 2.1 of the License, or (at your option) any later version.
16   *
17   * This library is distributed in the hope that it will be useful,
18   * but WITHOUT ANY WARRANTY; without even the implied warranty of
19   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
20   * Lesser General Public License for more details.
21   *
22   * You should have received a copy of the GNU Lesser General Public
23   * License along with this library; if not, write to the Free Software
24   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307 USA
25   *
26   * Revision History;
27   *
28   * Written by Davis Swan in April, 2004.
29   */
30  
31  package com.sqlmagic.tinysql;
32  
33  import java.io.*;
34  import java.util.*;
35  import java.text.*;
36  import java.sql.Types  ;
37  
38  public class tinySQLParser
39  {
40     Vector columnList,tableList,actionList,valueList,contextList,
41     columnAliasList;
42     tinySQLWhere whereClause;
43     String   statementType=(String  )null,tableName=(String  )null;
44     String   lastKeyWord=(String  )null,orderType=(String  )null;
45     String   oldColumnName=(String  )null,newColumnName=(String  )null;
46     String  [] colTypeNames = {"INT","FLOAT","CHAR","DATE"};
47     int[] colTypes = {Types.INTEGER,Types.FLOAT,Types.CHAR,Types.DATE};
48     int starAt = Integer.MIN_VALUE;
49     boolean debug=false;
50     public tinySQLParser(InputStream sqlInput) throws tinySQLException
51     {
52        StreamTokenizer st;
53        FieldTokenizer ft;
54        Reader r;
55        String   nextToken,upperField,nextField,keyWord=(String  )null;
56        StringBuffer   cmdBuffer,inputSQLBuffer;
57        int lastIndex,keyIndex;
58        r = new BufferedReader(new InputStreamReader(sqlInput));
59        actionList = new Vector();
60        columnList = new Vector();
61        columnAliasList = new Vector();
62        contextList = new Vector();
63        tableList = new Vector();
64        valueList = new Vector();
65        tableName = (String  )null;
66        whereClause = (tinySQLWhere)null;
67        try
68        {
69           st = new StreamTokenizer(r);
70           st.eolIsSignificant(false);
71           st.wordChars('\'','}');
72           st.wordChars('?','?');
73           st.wordChars('"','.');
74           st.ordinaryChars('0','9');
75           st.wordChars('0','9');
76           cmdBuffer = new StringBuffer  ();
77           inputSQLBuffer = new StringBuffer  ();
78           while ( st.nextToken() != StreamTokenizer.TT_EOF)
79           {
80              if ( st.ttype == StreamTokenizer.TT_WORD )
81                 nextToken = st.sval.trim();
82              else 
83                 continue;
84              if ( inputSQLBuffer.length() > 0 ) inputSQLBuffer.append(" ");
85              inputSQLBuffer.append(nextToken);
86           }
87           ft = new FieldTokenizer(inputSQLBuffer.toString(),' ',false);
88           while ( ft.hasMoreFields() )
89           {
90              nextField = ft.nextField();
91              upperField = nextField.toUpperCase();
92              if ( statementType == (String  )null ) 
93              {
94                 statementType = upperField;
95                 lastIndex = getKeywordIndex(statementType,statementType);
96                 if ( lastIndex != 0 ) throwException(9);
97                 keyWord = statementType;
98              } else {
99                 keyIndex = getKeywordIndex(statementType,upperField);
100                if ( keyIndex < 0 )
101                {
102                   if ( cmdBuffer.length() > 0 ) cmdBuffer.append(" ");
103                   cmdBuffer.append(nextField);
104                } else {
105                   setPhrase(keyWord,cmdBuffer.toString());
106                   cmdBuffer = new StringBuffer  ();
107                   keyWord = upperField;
108                   if ( debug ) System.out.println("Found keyword " + keyWord);
109                }
110             }  
111          }
112          if ( keyWord != (String  )null ) setPhrase(keyWord,cmdBuffer.toString());
113          addAction();
114          if ( debug ) System.out.println("SQL:"+inputSQLBuffer.toString());
115       } catch ( Exception   ex ) {
116          throw new tinySQLException(ex.getMessage());
117       }
118    }
119 /*
120  * This method sets up particular phrase elements for the SQL command.
121  * Examples would be a list of selected columns and tables for a SELECT
122  * statement, or a list of column definitions for a CREATE TABLE
123  * statement.  These phrase elements will be added to the action list
124  * once the entire statement has been parsed.
125  */
126    public void setPhrase(String   inputKeyWord,String   inputString)
127       throws tinySQLException
128    {
129       String   nextField,upperField,tableAlias,colTypeStr,colTypeSpec,
130       fieldString,syntaxErr,tempString,columnName,columnAlias;
131       StringBuffer   colTypeBuffer,concatBuffer;
132       FieldTokenizer ft1,ft2,ft3;
133       tsColumn createColumn;
134       int i,j,k,lenc,colType,countFields;
135 /*
136  *    Handle compound keywords.
137  */
138       if ( inputString == (String  )null ) 
139       {
140          lastKeyWord = inputKeyWord;
141          return;
142            
143       } else if ( inputString.trim().length() == 0 ) {
144          lastKeyWord = inputKeyWord;
145          return;
146       }
147       ft1 = new FieldTokenizer(inputString,',',false);
148       while ( ft1.hasMoreFields() )
149       {
150          nextField = ft1.nextField().trim();
151          if ( debug ) System.out.println("Next field is " + nextField);
152          upperField = nextField.toUpperCase();
153          if ( inputKeyWord.equals("SELECT") )
154          {
155 /*
156  *          Check for and set column alias.
157  */
158             ft2 = new FieldTokenizer(nextField,' ',false);
159             columnName = ft2.getField(0);
160             if ( columnName.equals("*") ) starAt = columnList.size();
161             columnAlias = (String  )null;
162 /*
163  *          A column alias can be preceded by the keyword AS which will
164  *          be ignored by tinySQL.
165  */
166             if ( ft2.countFields() == 2 ) columnAlias = ft2.getField(1);
167             else if ( ft2.countFields() == 3 ) columnAlias = ft2.getField(2);
168 /*
169  *          Check for column concatenation using the | symbol
170  */
171             ft2 = new FieldTokenizer(columnName,'|',false);
172             if ( ft2.countFields() > 1 ) 
173             {
174                concatBuffer = new StringBuffer  ("CONCAT(");
175                while ( ft2.hasMoreFields() )
176                {
177                   if ( concatBuffer.length() > 7 ) 
178                      concatBuffer.append(",");
179                   concatBuffer.append(ft2.nextField());
180                }
181                columnName = concatBuffer.toString() + ")";
182             }
183             columnList.addElement(columnName);
184             columnAliasList.addElement(columnAlias);
185             contextList.addElement(inputKeyWord);
186          } else if ( inputKeyWord.equals("TABLE") ) {
187 /*
188  *          If the input keyword is TABLE, update the statement type to be a 
189  *          compound type such as CREATE_TABLE, DROP_TABLE, or ALTER_TABLE.
190  */
191             if ( !statementType.equals("INSERT") )
192                statementType = statementType + "_TABLE";
193             if ( statementType.equals("CREATE_TABLE") ) 
194             {
195 /*
196  *             Parse out the column definition.
197  */
198                ft2 = new FieldTokenizer(nextField,'(',false);
199                if ( ft2.countFields() != 2 ) throwException(1);
200                tableName = ft2.getField(0);
201                fieldString = ft2.getField(1);
202                ft2 = new FieldTokenizer(fieldString,',',false);
203                while ( ft2.hasMoreFields() )
204                {
205                   tempString = ft2.nextField();
206                   createColumn = parseColumnDefn(tempString);
207                   if ( createColumn != (tsColumn)null )
208                      columnList.addElement(createColumn);
209                }
210             } else {
211                tableName = upperField;
212             } 
213          } else if ( inputKeyWord.equals("BY") ) {
214 /*
215  *          Set up Group by and Order by columns.
216  */
217             if ( lastKeyWord == (String  )null ) 
218             {
219                throwException(6);
220             } else {
221                ft3 = new FieldTokenizer(upperField,' ',false);
222                columnList.addElement(ft3.getField(0));
223                if ( ft3.countFields() == 2 )
224                {
225 /*
226  *                ASC or DESC are the only allowable directives after GROUP BY
227  */
228                   if ( ft3.getField(1).startsWith("ASC") |
229                        ft3.getField(1).startsWith("DESC") )
230                      orderType = ft3.getField(1);
231                   else
232                      throwException(7);
233                }
234                contextList.addElement(lastKeyWord);
235             }
236          } else if ( inputKeyWord.equals("DROP") ) {
237 /*
238  *          Parse list of columns to be dropped.
239  */
240             statementType = "ALTER_DROP";
241             ft2 = new FieldTokenizer(upperField,' ',false);
242             while ( ft2.hasMoreFields() )
243             {
244                columnList.addElement(UtilString.removeQuotes(ft2.nextField()));
245             }
246          } else if ( inputKeyWord.equals("RENAME") ) {
247 /*
248  *          Parse old and new column name.
249  */
250             statementType = "ALTER_RENAME";
251             ft2 = new FieldTokenizer(upperField,' ',false);
252             oldColumnName = ft2.getField(0);
253             newColumnName = ft2.getField(1);
254          } else if ( inputKeyWord.equals("ADD") ) {
255 /*
256  *          Parse definition of columns to be added.
257  */
258             statementType = "ALTER_ADD";
259             createColumn = parseColumnDefn(nextField);
260             if ( createColumn != (tsColumn)null )
261                columnList.addElement(createColumn);
262          } else if ( inputKeyWord.equals("FROM") ) {
263 /*
264  *          Check for and set table alias.
265  */
266             ft2 = new FieldTokenizer(upperField,' ',false);
267             tableName = ft2.getField(0);
268             tableAlias = (ft2.getField(1,tableName)).toUpperCase();
269             tableList.addElement(tableName + "->" + tableAlias);
270          } else if ( inputKeyWord.equals("INTO") ) {
271             ft2 = new FieldTokenizer(nextField,'(',false);
272             if ( ft2.countFields() != 2 ) throwException(3);
273             tableName = ft2.getField(0);
274             fieldString = ft2.getField(1).toUpperCase();
275             ft2 = new FieldTokenizer(fieldString,',',false);
276             while ( ft2.hasMoreFields() )
277             {
278                tempString = UtilString.removeQuotes(ft2.nextField());
279                columnList.addElement(tempString);
280                contextList.addElement(inputKeyWord);
281             }
282          } else if ( inputKeyWord.equals("VALUES") ) {
283             ft2 = new FieldTokenizer(nextField,'(',false);
284             fieldString = ft2.getField(0);
285             ft2 = new FieldTokenizer(fieldString,',',false);
286             while ( ft2.hasMoreFields() )
287             {
288                tempString = UtilString.removeQuotes(ft2.nextField());
289                tempString = UtilString.replaceAll(tempString,"''","'");
290                valueList.addElement(tempString);
291             }
292          } else if ( inputKeyWord.equals("UPDATE") ) {
293             tableName = nextField.toUpperCase();
294          } else if ( inputKeyWord.equals("SET") ) {
295 /*
296  *          Parse the update column name/value pairs
297  */
298             ft2 = new FieldTokenizer(nextField,'=',false);
299             if ( ft2.countFields() != 2 ) throwException(4);
300             columnList.addElement(ft2.getField(0));
301             contextList.addElement(inputKeyWord);
302             valueList.addElement(UtilString.removeQuotes(ft2.getField(1)));
303          } else if ( inputKeyWord.equals("WHERE") ) {
304             whereClause = new tinySQLWhere(nextField);
305          } else if ( !inputKeyWord.equals("TABLE") ) {
306             throwException(10);
307          }
308       }
309       lastKeyWord = inputKeyWord;
310    }
311    public tsColumn parseColumnDefn(String   columnDefn) throws tinySQLException
312    {
313 /*
314  *    Parse out the column definition.
315  */
316       tsColumn createColumn;
317       int i;
318       FieldTokenizer ft;
319       String   fieldString,tempString,colTypeStr,colTypeSpec;
320       ft = new FieldTokenizer(columnDefn.toUpperCase(),' ',false);
321 /*
322  *    A column definition must consist of a column name followed by a 
323  *    column specification.
324  */
325       if ( ft.countFields() < 2 ) throwException(2);
326       createColumn = new tsColumn(ft.getField(0));
327       colTypeStr = "";
328       for ( i = 1; i < ft.countFields(); i++ )
329          colTypeStr += ft.getField(1);
330       ft = new FieldTokenizer(colTypeStr,'(',false);
331       colTypeStr = ft.getField(0);
332       createColumn.size = 10;
333       createColumn.decimalPlaces = 0;
334       if ( colTypeStr.equals("FLOAT") ) 
335       {
336          createColumn.size = 12;
337          createColumn.decimalPlaces = 2;
338       }
339       colTypeSpec = ft.getField(1);
340       if ( !colTypeSpec.equals("NULL") )
341       {
342 /*
343  *       Parse out the scale and precision is supplied.
344  */
345          ft = new FieldTokenizer(colTypeSpec,',',false);
346          createColumn.size = ft.getInt(0,8);
347          createColumn.decimalPlaces = ft.getInt(1,0);
348       }
349       createColumn.type = Integer.MIN_VALUE;
350       for ( i = 0; i < colTypeNames.length; i++ )
351          if ( colTypeStr.equals(colTypeNames[i]) )
352              createColumn.type = colTypes[i]; 
353       if ( createColumn.type == Integer.MIN_VALUE ) throwException(8);
354       if ( debug ) System.out.println("Column " + createColumn.name 
355       + ", type is " + createColumn.type + ",size is " + createColumn.size 
356       + ",precision is " + createColumn.decimalPlaces);
357       return createColumn;
358    }
359 /*
360  * This method is used to idenify SQL key words, and the order in which they
361  * should appear in the SQL statement.
362  */
363    public int getKeywordIndex(String   inputContext,String   inputWord)
364    {
365       String  [][] sqlSyntax = {{"SELECT","FROM","WHERE","GROUP","ORDER","BY"},
366         {"INSERT","INTO","VALUES"},
367         {"DROP","TABLE"},
368         {"DELETE","FROM","WHERE"},
369         {"CREATE","TABLE"},
370         {"UPDATE","SET","WHERE"},
371         {"ALTER","TABLE","DROP","MODIFY","ADD","RENAME"}};
372       int i,j;
373       for ( i = 0; i < sqlSyntax.length; i++ )
374       {
375          for ( j = 0; j < sqlSyntax[i].length; j++ )
376          {
377             if ( sqlSyntax[i][0].equals(inputContext) &
378                  sqlSyntax[i][j].equals(inputWord) )
379                return j;
380          }
381       }
382       return Integer.MIN_VALUE;
383    }
384 /*
385  * Add an action Hashtable to the list of actions
386  */
387    public void addAction () throws tinySQLException
388    {
389       int i,j,foundDot;
390       String   columnName;
391       Hashtable newAction =  new Hashtable();
392       newAction.put("TYPE", statementType);
393       if ( statementType.equals("SELECT") )
394       {
395          newAction.put("TABLES",tableList);
396          if ( whereClause != (tinySQLWhere)null )
397             newAction.put("WHERE",whereClause);
398 /*
399  *       If this is a SELECT *, move the * to the bottom of the columnList
400  *       so that the column expansion will not impact GROUP BY or
401  *       ORDER BY columns.
402  */
403          if ( starAt != Integer.MIN_VALUE ) 
404          {
405             columnList.addElement(columnList.elementAt(starAt));
406             columnAliasList.addElement(columnList.elementAt(starAt));
407             contextList.addElement(contextList.elementAt(starAt));
408             columnList.removeElementAt(starAt);
409             columnAliasList.removeElementAt(starAt);
410             contextList.removeElementAt(starAt);
411          }
412          newAction.put("COLUMNS",columnList);
413          newAction.put("COLUMN_ALIASES",columnAliasList);
414          newAction.put("CONTEXT",contextList);
415          if ( orderType != (String  )null ) newAction.put("ORDER_TYPE",orderType);
416       } else if ( statementType.equals("DROP_TABLE") ) {
417          newAction.put("TABLE",tableName);
418       } else if ( statementType.equals("CREATE_TABLE") ) {
419          newAction.put("TABLE",tableName);
420          newAction.put("COLUMN_DEF",columnList);
421       } else if ( statementType.equals("ALTER_RENAME") ) {
422          newAction.put("OLD_COLUMN",oldColumnName);
423          newAction.put("NEW_COLUMN",newColumnName);
424       } else if ( statementType.equals("ALTER_ADD") ) {
425          newAction.put("TABLE",tableName);
426          newAction.put("COLUMN_DEF",columnList);
427       } else if ( statementType.equals("ALTER_DROP") ) {
428          newAction.put("TABLE",tableName);
429          newAction.put("COLUMNS",columnList);
430       } else if ( statementType.equals("DELETE") ) {
431          newAction.put("TABLE",tableName);
432          if ( whereClause != (tinySQLWhere)null )
433             newAction.put("WHERE",whereClause);
434       } else if ( statementType.equals("INSERT") |
435                   statementType.equals("UPDATE") ) {
436          newAction.put("TABLE",tableName);
437          if ( columnList.size() != valueList.size() ) throwException(5);
438          newAction.put("COLUMNS",columnList);
439          newAction.put("VALUES",valueList);
440          if ( whereClause != (tinySQLWhere)null )
441             newAction.put("WHERE",whereClause);
442       }
443       actionList.addElement(newAction);
444    }
445    public void throwException(int exceptionNumber) throws tinySQLException
446    {
447       String   exMsg = (String  )null;
448       if ( exceptionNumber == 1 )
449          exMsg = "CREATE TABLE must be followed by a table name and a list"
450          + " of column specifications enclosed in brackets.";
451       else if ( exceptionNumber == 2 )
452          exMsg = "A column specification must consist of a column name"
453          + " followed by a column type specification.";
454       else if ( exceptionNumber == 3 )
455          exMsg = "INTO should be followed by a table name and "
456          + "a list of columns enclosed in backets.";
457       else if ( exceptionNumber == 4 )
458          exMsg = "SET must be followed by assignments in the form"
459          + " <columnName>=<value>.";
460       else if ( exceptionNumber == 5 )
461          exMsg = "INSERT statement number of columns and values provided"
462          + " do not match.";
463       else if ( exceptionNumber == 6 )
464          exMsg = "BY cannot be the first keyword.";
465       else if ( exceptionNumber == 7 )
466          exMsg = "ORDER BY can only be followed by the ASC or DESC directives";
467       else if ( exceptionNumber == 8 )
468          exMsg = "Supported column types are INT,CHAR,FLOAT,DATE";
469       else if ( exceptionNumber == 9 )
470          exMsg = "Expecting SELECT, INSERT, ALTER, etc. in " + statementType;
471       else if ( exceptionNumber == 10 )
472          exMsg = "Unrecognized keyword ";
473       throw new tinySQLException(exMsg);
474    }
475    public Vector getActions()
476    {
477       return actionList;
478    }
479 }
480
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags