Java > Open Source Codes > com > sqlmagic > tinysql > tinySQL


1 /*
2  * tinySQL.java
3  *
4  * A trivial implementation of SQL in an abstract class.
5  * Plug it in to your favorite non-SQL data source, and
6  * QUERY AWAY!
7  *
8  * Copyright 1996, Brian C. Jepson
9  * (bjepson@ids.net)
10  *
11  * $Author: davis $
12  * $Date: 2004/12/18 21:23:50 $
13  * $Revision: 1.1 $
14  *
15  * This library is free software; you can redistribute it and/or
16  * modify it under the terms of the GNU Lesser General Public
17  * License as published by the Free Software Foundation; either
18  * version 2.1 of the License, or (at your option) any later version.
19  *
20  * This library is distributed in the hope that it will be useful,
21  * but WITHOUT ANY WARRANTY; without even the implied warranty of
22  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
23  * Lesser General Public License for more details.
24  *
25  * You should have received a copy of the GNU Lesser General Public
26  * License along with this library; if not, write to the Free Software
27  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
28  *
29  * Revision History:
30  *
31  * Major rewrite in 2003/4 by Davis Swan SQL*Magic Ltd.
32  */
33
34 package com.sqlmagic.tinysql;
35
36 import java.util.*;
37 import java.lang.*;
38 import java.io.*;
39 import java.sql.SQLException ;
40 import java.sql.Types ;
41
42 /**
43  * @author Thomas Morger <mgs@sherito.org> Changed tinySQL to reflect changes
44  * in tinySQLResultSet - an instance of the connection is passed to a new
45  * resultset at construction time.
46  *
47  * When fetching a resultset, the number of rows to fetch can be set in the
48  * statement. The builder will return when the given number of rows has been
49  * reached and will be restarted by tsResultSet when more rows are needed.
50  */
51 public abstract class tinySQL {
52
53   boolean debug=false,groupBreak=true,keepRecord=true;
54   boolean exDebug=false,performDebug=false;
55   Hashtable groupFunctions;
56   String newLine = System.getProperty("line.separator");
57   String zeroCount=(String )null;
58   static tinySQLTable insertTable=(tinySQLTable)null;
59   tinySQLWhere wc;
60   // This is the InputStream from which the parser reads.
61 //
62 private InputStream SQLStream;
63
64   // This is the last SQL Statement processed by sqlexec(String s).
65 // Note that sqlexec() does *not* support this.
66 //
67 private tinySQLStatement sqlstatement = null;
68 /*
69  *
70  * Constructs a new tinySQL object.
71  *
72  */
73   public tinySQL() {
74
75   }
76
77 /*
78  * Reads SQL statements from System.in() and returns a
79  * tsResultSet for the last statement executed. This is
80  * really only good for testing.
81  *
82  * @exception tinySQLException
83  */
84   public tsResultSet sqlexec() throws tinySQLException {
85
86     SQLStream = (InputStream) System.in;
87     System.err.println("Reading SQL Statements from STDIN...");
88     System.err.println("CRASHING AFTER THIS POINT IS SURE...");
89     System.err.println("Have no Statement, no connection and no clue how to continue ...");
90     return sql(null);
91
92   }
93
94 /*
95  * Processes the SQL Statement s and returns
96  * a tsResultSet.
97  *
98  * @param s SQL Statement to execute
99  * @exception tinySQLException
100  */
101   public tsResultSet sqlexec(tinySQLStatement s) throws tinySQLException
102   {
103      return sql(s);
104   }
105   public tsResultSet sqlexec(tinySQLPreparedStatement s)
106      throws tinySQLException
107   {
108      return sql(s);
109   }
110
111 /*
112  *
113  * Read SQL Statements from the SQLStream, and
114  * return a result set for the last SQL Statement
115  * executed.
116  *
117  * @returns the ResultSet or null, if no result set was created
118  * @exception tinySQLException
119  *
120  */
121    protected tsResultSet sql(Object s) throws tinySQLException
122    {
123 /*
124  * Build the ResultSet
125  */
126       tsResultSet rs = null;
127       tinySQLTable jtbl;
128       tinySQLPreparedStatement pstmt=(tinySQLPreparedStatement)null;
129       boolean useTinyParser = true;
130       Vector actions,columns,columnDefs,values,columnContexts,columnAliases,
131       tbls;
132       String actionType,orderType,tableName,statementType,byteString;
133       Hashtable h;
134       byte[] bStream;
135       ByteArrayInputStream st;
136       int i;
137       String actionString;
138       groupBreak=true;
139       keepRecord=true;
140       zeroCount = (String )null;
141       statementType = s.getClass().getName();
142       try
143       {
144 /*
145  * Instantiate a new parser object which reads from the SQLStream. This
146  * should probably be changed to a String at some point. Note that
147  * parsing is only done once for a PreparedStatement.
148  */
149          actions = (Vector)null;
150          if ( statementType.endsWith("tinySQLPreparedStatement") )
151          {
152             pstmt = (tinySQLPreparedStatement)s;
153             pstmt.updateActions(actions);
154             actions = pstmt.getActions();
155             byteString = pstmt.getSQLString();
156             bStream = (byte[])null;
157             if ( pstmt.getSQLString() != (String )null )
158                bStream = pstmt.getSQLString().getBytes();
159          } else if ( statementType.endsWith("tinySQLStatement") ) {
160             bStream = ((tinySQLStatement)s).getSQLString().getBytes();
161          } else {
162             throw new tinySQLException("Unknown statement type"
163             + statementType);
164          }
165          if ( actions == (Vector)null )
166          {
167             st = new ByteArrayInputStream(bStream);
168             SQLStream = (InputStream) st;
169             tinySQLParser tinyp = new tinySQLParser(SQLStream);
170             actions = tinyp.getActions();
171             if ( statementType.endsWith("tinySQLPreparedStatement") )
172                pstmt.updateActions(actions);
173          }
174 /*
175  * The actions Vector consists of a list of Hashtables. Each of these
176  * action Hashtables contains elements required for a particular SQL
177  * statement. The following elements are used for various actions;
178  *
179  * Type Name Description
180  *
181  * String tableName The name of the affected table for
182  * CREATE,INSERT,UPDATE,DELETE actions.
183  *
184  * Vector tbls List of table names in a SELECT action.
185  *
186  * Vector columns A list of column names used by the
187  * the action.
188  *
189  * Vector columnContexts A list of Strings indicating the context
190  * for the elements in the columns Vector.
191  * Values can be SELECT,ORDER,GROUP.
192  *
193  * Vector columnDefs A list of column objects used by the
194  * CREATE TABLE and ALTER TABLE ADD actions.
195  *
196  * Vector values A list of String values used in INSERT
197  * and UPDATE actions.
198  *
199  * String orderType Type or ORDER BY - ASC or DESC.
200  *
201  * String oldColumnName Old column name for the
202  * ALTER TABLE RENAME action.
203  *
204  * String newColumnName New column name for the
205  * ALTER TABLE RENAME action.
206  *
207  * String orderType Type or ORDER BY - ASC or DESC.
208  *
209  * tinySQLWhere whereClause An object containing the where clause
210  * which can be updated and queried.
211  */
212          for (i = 0; i < actions.size(); i++)
213          {
214             h = (Hashtable)actions.elementAt(i);
215             actionType = (String )h.get("TYPE");
216 /*
217  * Many actions have a table specification. If this one, build
218  * a table object to update the where clause if there is one.
219  */
220             tableName = (String ) h.get("TABLE");
221             wc = (tinySQLWhere) h.get("WHERE");
222             if ( tableName != (String )null & !actionType.equals("DROP_TABLE") &
223                !actionType.equals("CREATE_TABLE") & !actionType.equals("INSERT") )
224             {
225                jtbl = getTable(tableName);
226 /*
227  * For prepared statements, store any table objects that
228  * are created so that they can be explicitly closed when
229  * the statement processing is complete.
230  */
231                if ( statementType.endsWith("tinySQLPreparedStatement") )
232                   pstmt.addTable(jtbl);
233                if ( wc != (tinySQLWhere)null ) wc.setColumnTypes(jtbl);
234             }
235             actionString = UtilString.actionToString(h);
236             if ( debug ) System.out.println("ACTION: " + actionString);
237             if ( actionType.equals("UPDATE") )
238             {
239 /*
240  * SQL UPDATE
241  */
242                columns = (Vector) h.get("COLUMNS");
243                values = (Vector) h.get("VALUES");
244                UpdateStatement (tableName, columns, values, wc);
245             } else if ( actionType.equals("DELETE") ) {
246 /*
247  * SQL DELETE
248  */
249                DeleteStatement (tableName, wc);
250             } else if ( actionType.equals("SELECT") ) {
251 /*
252  * SQL SELECT
253  */
254                tbls = (Vector) h.get("TABLES");
255                columns = (Vector) h.get("COLUMNS");
256                columnContexts = (Vector) h.get("CONTEXT");
257                columnAliases = (Vector) h.get("COLUMN_ALIASES");
258                orderType = (String )h.get("ORDER_TYPE");
259                rs = SelectStatement(tbls,columns,columnContexts,
260                                     columnAliases,wc,orderType,s);
261             } else if ( actionType.equals("INSERT") ) {
262 /*
263  * SQL INSERT
264  */
265                columns = (Vector) h.get("COLUMNS");
266                values = (Vector) h.get("VALUES");
267                InsertStatement (statementType, tableName, columns, values);
268             } else if ( actionType.equals("CREATE_TABLE") ) {
269 /*
270  * SQL CREATE TABLE
271  *
272  * CREATE TABLE User(user_oid NUMBER(8) NOT NULL,
273  * userType VARCHAR(80) DEFAULT '-' NOT NULL,
274  * PRIMARY KEY (user_oid))
275  *
276  * -> DEFAULT / NOT NULL / PRIMARY KEY is not supported
277  *
278  */
279                columnDefs = (Vector) h.get("COLUMN_DEF");
280                CreateTable (tableName, columnDefs);
281             } else if ( actionType.equals("ALTER_ADD") ) {
282 /*
283  * SQL ALTER TABLE ADD
284  */
285                columnDefs = (Vector) h.get("COLUMN_DEF");
286                AlterTableAddCol (tableName, columnDefs);
287             } else if ( actionType.equals("ALTER_DROP") ) {
288 /*
289  * SQL ALTER TABLE DROP
290  */
291                columns = (Vector) h.get("COLUMNS");
292                AlterTableDropCol (tableName, columns);
293             } else if ( actionType.equals("ALTER_RENAME") ) {
294 /*
295  * SQL ALTER TABLE RENAME
296  */
297                String oldColname = (String ) h.get("OLD_COLUMN");
298                String newColname = (String ) h.get("NEW_COLUMN");
299                AlterTableRenameCol(tableName, oldColname, newColname);
300             } else if ( actionType.equals("DROP_TABLE") ) {
301 /*
302  * SQL DROP TABLE
303  */
304                DropTable( tableName );
305             } else {
306                System.out.println("Unrecognized action " + actionType);
307             }
308          }
309       } catch (Exception e) {
310          if ( exDebug ) e.printStackTrace(System.out);
311          throw new tinySQLException(e.getMessage());
312       }
313       return rs;
314    }
315 /*
316  * Execute an SQL Select Statement
317  */
318    protected tsResultSet SelectStatement (Vector t,Vector c,Vector x,
319       Vector a, tinySQLWhere w,String ot,Object stmt) throws tinySQLException
320    {
321       Hashtable tables,columns;
322       Vector groupByColumns,orderByColumns;
323       String tableName,tableAlias,columnList,selectColumn,
324       columnName,functionName,upperColumn,columnContext,functionArgs,
325       columnAlias;
326       StringBuffer functionBuffer;
327       FieldTokenizer ft,ftArgs;
328       tsResultSet jrs;
329       tsColumn columnObject,testCol;
330       Vector cols,groupFunction;
331       int i,j,insertAt,rowCount,foundDot,columnSize,columnType;
332       int[] rowCounts;
333       tinySQLTable jtbl;
334       Enumeration col_keys;
335 /*
336  * Instantiate a new, empty tsResultSet
337  */
338       jrs = new tsResultSet(t, c, w, this);
339       groupFunctions = new Hashtable();
340       groupByColumns = new Vector();
341       orderByColumns = new Vector();
342       try
343       {
344         jrs.setFetchSize(((tinySQLStatement)stmt).getFetchSize());
345         jrs.setType(((tinySQLStatement)stmt).getResultSetType());
346       } catch (SQLException sqle) {
347         Utils.log ("Caught SQLException while setting Fetchsize and ResultSetType");
348         Utils.log (" This event is (should be) impossible!");
349       }
350 /*
351  * Create a table object for each table used in the SELECT statement
352  * and store these objects in the tables Hashtable. Save the original
353  * list of table names to set the default selection order.
354  */
355       tables = new Hashtable();
356       tables.put("TABLE_SELECT_ORDER",t);
357       rowCounts = new int[t.size()];
358       for (i = 0; i < t.size(); i++)
359       {
360          tableName = (String )t.elementAt(i);
361          jtbl = getTable(tableName);
362          tables.put(tableName,jtbl);
363          jtbl.GoTop();
364 /*
365  * Sort the table selections from smallest to largest table to
366  * enhance the query performance.
367  */
368          rowCount = jtbl.GetRowCount();
369          rowCounts[i] = rowCount;
370          if ( i == 0 ) continue;
371          insertAt = 0;
372          for ( j = i - 1; j >= 0; j-- )
373          {
374             if ( rowCounts[j] <= rowCounts[i] )
375             {
376                insertAt = j + 1;
377                break;
378             }
379          }
380          if ( insertAt != i )
381          {
382 /*
383  * The table selection order must be changed.
384  */
385             rowCount = rowCounts[insertAt];
386             rowCounts[insertAt] = rowCounts[i];
387             rowCounts[i] = rowCount;
388             t.removeElementAt(i);
389             t.insertElementAt(tableName,insertAt);
390          }
391          if ( debug | performDebug )
392          {
393             System.out.println("Table selection order");
394             for ( j = 0; j <= i; j++ )
395                System.out.println(t.elementAt(j) + " " + rowCounts[j]);
396          }
397       }
398       if ( w != (tinySQLWhere)null ) w.setColumnTypes(tables);
399 /*
400  * Check for a column named *
401  */
402       for (i = 0; i < c.size(); i++)
403       {
404          columnName = (String )c.elementAt(i);
405          columnContext = (String )x.elementAt(i);
406          if ( !columnName.equals("*") ) continue;
407 /*
408  * A column * has been found. Delete the existing list of SELECT
409  * columns and replace by using an enumeration variable to cycle through
410  * the columns in the tables Hashtable.
411  */
412          for ( j = 0; j < c.size(); j++ )
413          {
414             columnContext = (String )x.elementAt(j);
415             if ( columnContext.equals("SELECT") )
416             {
417                c.removeElementAt(j);
418                x.removeElementAt(j);
419                a.removeElementAt(j);
420             }
421          }
422          for ( j = 0; j < t.size(); j++ )
423          {
424             jtbl = (tinySQLTable)tables.get((String )t.elementAt(j));
425             col_keys = jtbl.column_info.keys();
426 /*
427  * Expand to all columns.
428  */
429             while (col_keys.hasMoreElements())
430             {
431                columnName = (String )col_keys.nextElement();
432                c.addElement(jtbl.table + "->" + jtbl.tableAlias
433                         + "." + columnName);
434                a.addElement(columnName);
435                x.addElement("SELECT");
436             }
437          }
438          break;
439       }
440 /*
441  * Build a column object for each selected column.
442  */
443       for (i = 0; i < c.size(); i++)
444       {
445          columnName = (String )c.elementAt(i);
446          columnContext = (String )x.elementAt(i);
447          columnAlias = (String )null;
448          if ( i < a.size() )
449             columnAlias = (String )a.elementAt(i);
450          columnObject = new tsColumn(columnName,tables);
451          columnObject.alias = UtilString.removeQuotes(columnAlias);
452 /*
453  * The column object is now added to the ResultSet or the list of
454  * Group By or Order By columns.
455  */
456          if ( columnContext.equals("GROUP") )
457          {
458             groupByColumns.addElement(columnObject);
459          } else if ( columnContext.equals("ORDER") ) {
460             orderByColumns.addElement(columnObject);
461          } else {
462             jrs.addColumn (columnObject);
463          }
464          if ( debug ) System.out.println("Adding " + columnContext
465          + " column " + newLine + columnObject.toString() + newLine);
466       }
467       jrs.setState (1,tables,groupByColumns,orderByColumns,ot);
468       contSelectStatement (jrs);
469       return jrs;
470   }
471 /*
472  * Support function for restartable queries. Continue to
473  * read the query result. The current state is taken from
474  * tsResultSet. Proceed until maxFetchSize has reached.
475  */
476    protected void contSelectStatement (tsResultSet jrs)
477       throws tinySQLException
478    {
479 /*
480  * The table scan here is an iterative tree expansion, similar to
481  * the algorithm shown in the outline example in Chapter 5.
482  */
483       String columnName,columnString,whereStatus;
484       boolean addOK;
485       int i,rowCount;
486       int level = jrs.getLevel ();
487       tinySQLTable jtbl;
488       tsColumn updateColumn;
489       Hashtable tables = jrs.getTableState ();
490       Vector ob = jrs.getOrderByColumns();
491       tinySQLWhere wc = jrs.getWhereClause();
492       String orderType = jrs.getOrderType();
493 /*
494  * Create a hashtable to enumerate the tables to be scanned and initialize
495  * with the first table name.
496  */
497       Hashtable tbl_list = new Hashtable();
498       Vector groupFunction;
499       Vector t = (Vector)tables.get("TABLE_SELECT_ORDER");
500       String current = (String ) t.elementAt(0);
501       String firstColumn = "*";
502       tbl_list.put( current, new Integer (1) );
503 /*
504  * Create a row object; this is added to the result set
505  */
506       tsRow record = new tsRow();
507       if ( ob.size() > 0 ) record.setOrderBy(ob);
508       Vector resultSet = new Vector();
509 /*
510  * Keep retrieving rows until we run out of rows to process.
511  */
512       while ( level > 0 )
513       {
514          boolean levelFound = false;
515 /*
516  * Find an item within the tbl_list which has the same level as the
517  * one we're on.
518  */
519          Enumeration keys = tbl_list.keys();
520          while (keys.hasMoreElements())
521          {
522 /*
523  * Get the next element in the "to be processed"
524  * Hashtable, and find out its level, storing this
525  * value in currLevel.
526  */
527             String hashkey = (String ) keys.nextElement();
528             int currLevel = ((Integer ) tbl_list.get(hashkey)).intValue();
529 /*
530  * As soon as an element is found whose level is equal to the
531  * one currently being processed, grab it's primary key (the hashkey),
532  * flag levelfound, and break!
533  */
534             if (currLevel == level)
535             {
536                current = hashkey; levelFound = true; break;
537             }
538          }
539          boolean eof = false; // did we hit eof?
540 boolean haveRecord = false; // did we get a record or not?
541 /*
542  * If a table was found at the current level, then we should
543  * try to get another row from it.
544  */
545          if (levelFound)
546          {
547 /*
548  * Get the current table
549  */
550             jtbl = (tinySQLTable) tables.get(current);
551             if ( performDebug ) System.out.println("Selecting records from "
552             + jtbl.table);
553 /*
554  * Skip to the next undeleted record; at some point,
555  * this will run out of records, and found will be false.
556  */
557             boolean found = false;
558             while (jtbl.NextRecord())
559             {
560                if (!jtbl.isDeleted())
561                {
562 /*
563  * Evaluate the where clause for each column in the table. If
564  * it is false, skip to the next row. Otherwise, add the
565  * column value to the output record.
566  */
567                   Enumeration cols = jtbl.column_info.keys();
568                   found = true;
569                   whereStatus = "TRUE";
570                   while (cols.hasMoreElements())
571                   {
572                      columnName = jtbl.table + "->" + jtbl.tableAlias
573                      + "." + (String )cols.nextElement();
574                      columnString = jtbl.GetCol(columnName);
575                      if ( wc != (tinySQLWhere)null )
576                      {
577                         whereStatus = wc.evaluate(columnName,columnString);
578                         if ( whereStatus.equals("FALSE") )
579                         {
580 /*
581  * This column value caused the where clause to
582  * be FALSE. Go to the next row in the table.
583  */
584                            wc.clearValues(jtbl.table + "->" + jtbl.tableAlias);
585                            found = false;
586                            break;
587                         }
588                      }
589 /*
590  * Update the ResultSet tsColumn values
591  */
592                      jrs.updateColumns(columnName,columnString);
593                   }
594                   if ( found ) break;
595                }
596             }
597             if (found)
598             {
599                for ( i = 0; i < jrs.numcols(); i++ )
600                {
601                   updateColumn = jrs.columnAtIndex(i);
602                   columnString = updateColumn.getString();
603                   if ( updateColumn.isNotNull )
604                      record.put(updateColumn.name,columnString);
605                   else
606                      record.remove(updateColumn.name);
607                }
608                if ( performDebug )
609                   System.out.println("Record is " + record.toString());
610 /*
611  * Since we were just able to get a row, then
612  * we are not at the end of file
613  */
614                eof = false;
615 /*
616  * If the table we are processing is not the last in
617  * the list, then we should increment level and loop to the top.
618  */
619                if (level < t.size())
620                {
621 /*
622  * Increment level
623  */
624                   level++;
625 /*
626  * Add the next table in the list of tables to
627  * the tbl_list, the Hashtable of "to be processed" tables.
628  */
629                   String next_tbl = (String ) t.elementAt( level - 1);
630                   tbl_list.put( next_tbl, new Integer (level) );
631                } else {
632 /*
633  * If the table that was just processed is the last in
634  * the list, then we have drilled down to the bottom;
635  * all columns have values, and we can add it to the
636  * result set. The next time through, the program
637  * will try to read another row at this level; if it's
638  * found, only columns for the table being read will
639  * be overwritten in the tsRow.
640  *
641  * Columns for the other table will be left alone, and
642  * another row will be added to the result set. Here
643  * is the essence of the Cartesian Product which is
644  * being built here.
645  */
646                   haveRecord = true;
647                }
648             } else {
649 /*
650  * We didn't find any more records at this level.
651  * Reset the record pointer to the top of the table,
652  * and decrement level. We have hit end of file here.
653  */
654                if ( wc != (tinySQLWhere)null ) wc.clearValues(jtbl.table);
655                level--;
656                eof = true;
657                jtbl.GoTop();
658             }
659          } else {
660 /*
661  * No tables were found at this level; back up a level
662  * and see if there's any up there.
663  */
664             level--;
665          }
666 /*
667  * If we got a record, then add it to the result set.
668  */
669          if (haveRecord)
670          {
671 /*
672  * If group functions are involved, add records only after a break,
673  * which is defined as a change in all of the group columns.
674  * Otherwise, update the current record.
675  */
676             if ( jrs.isGrouped() )
677             {
678                if ( groupBreak )
679                {
680                   zeroCount = (String )null;
681                   addOK = jrs.addRow((tsRow)record.clone(),orderType);
682                   if (addOK == false)
683                   {
684                      jrs.setLevel (level);
685                      return;
686                   }
687                   groupBreak = false;
688                } else {
689                   jrs.updateRow( (tsRow) record.clone());
690                }
691             } else {
692 /*
693  * No group functions are involved. Just add the record.
694  */
695                addOK = jrs.addRow((tsRow)record.clone(),orderType);
696                if (addOK == false)
697                {
698                   jrs.setLevel (level);
699                   return;
700                }
701             }
702             firstColumn = "*";
703          }
704       }
705 /*
706  * Close all the tables
707  */
708       for ( i = 0; i < t.size(); i++ )
709       {
710          jtbl = (tinySQLTable)tables.get((String )t.elementAt(i));
711          jtbl.close();
712       }
713       if ( zeroCount != (String )null )
714       {
715          record.put(zeroCount,"0");
716          addOK = jrs.addRow((tsRow)record.clone());
717          zeroCount = (String )null;
718       }
719 /*
720  * return a result set
721  */
722       jrs.setLevel (0);
723    }
724 /*
725  * Delete rows which match a where clause.
726  */
727    private void DeleteStatement (String tableName, tinySQLWhere wc)
728       throws tinySQLException
729    {
730       tinySQLTable jtbl;
731       Hashtable tables;
732       String columnName,columnString,whereStatus;
733       Enumeration cols;
734 /*
735  * Create a table object and put it in the Hashtable.
736  */
737       jtbl = getTable(tableName);
738       tables = new Hashtable();
739       tables.put(tableName, jtbl);
740 /*
741  * Process each row in the table ignoring deleted rows.
742  */
743       jtbl.GoTop();
744       while (jtbl.NextRecord())
745       {
746          if (!jtbl.isDeleted())
747          {
748             cols = jtbl.column_info.keys();
749             whereStatus = "TRUE";
750             while (cols.hasMoreElements())
751             {
752                columnName = jtbl.table + "->" + jtbl.tableAlias + "."
753                + (String )cols.nextElement();
754                columnString = jtbl.GetCol(columnName);
755 /*
756  * Check the status of the where clause for each column value.
757  */
758                if ( wc != (tinySQLWhere)null )
759                   whereStatus = wc.evaluate(columnName,columnString);
760                if ( whereStatus.equals("FALSE") ) break;
761             }
762             if ( whereStatus.equals("TRUE") ) jtbl.DeleteRow();
763             if ( wc != (tinySQLWhere)null )
764                wc.clearValues(jtbl.table + "->" + jtbl.tableAlias);
765          }
766       }
767       jtbl.close();
768    }
769 /*
770  * Update rows which match a WHERE clause
771  */
772    private void UpdateStatement(String tableName, Vector c, Vector v, tinySQLWhere wc)
773        throws tinySQLException
774    {
775 /*
776  * Create a table object and put it in the Hashtable.
777  */
778       tinySQLTable jtbl = getTable(tableName);
779       Hashtable tables = new Hashtable();
780       tables.put(tableName, jtbl);
781       String columnName,columnString,whereStatus;
782 /*
783  * Process each row in the table ignoring deleted rows.
784  */
785       jtbl.GoTop();
786       while (jtbl.NextRecord())
787       {
788          if (!jtbl.isDeleted())
789          {
790             Enumeration cols = jtbl.column_info.keys();
791             whereStatus = "TRUE";
792             while (cols.hasMoreElements())
793             {
794 /*
795  * Use the table name for the table alias for updates.
796  */
797                columnName = jtbl.table + "->" + jtbl.table
798                + "." + (String ) cols.nextElement();
799                columnString = jtbl.GetCol(columnName);
800 /*
801  * Check the status of the where clause for each column value.
802  */
803                if ( wc != (tinySQLWhere)null )
804                   whereStatus = wc.evaluate(columnName,columnString);
805                if ( whereStatus.equals("FALSE") ) break;
806             }
807             if ( whereStatus.equals("TRUE") ) jtbl.UpdateCurrentRow(c, v);
808             if ( wc != (tinySQLWhere)null )
809                wc.clearValues(jtbl.table + "->" + jtbl.tableAlias);
810          }
811       }
812       jtbl.close();
813    }
814 /*
815  * Create the tinySQLTable object, then insert a row, and update
816  * it with the c and v Vectors
817  */
818    private void InsertStatement (String statementType, String tableName,
819                                  Vector c, Vector v)
820         throws tinySQLException
821    {
822       String columnName,valueString;
823       int i,columnType,columnSize;
824       tinySQLTable jtbl=(tinySQLTable)null;
825       double value;
826       insertTable = getTable(tableName);
827 /*
828  * Check that the values supplied are the correct type and length.
829  */
830       for ( i = 0; i < c.size(); i++ )
831       {
832          columnName = (String )c.elementAt(i);
833          valueString = (String )v.elementAt(i);
834          if ( valueString == (String )null ) continue;
835          valueString = UtilString.removeQuotes(valueString);
836          valueString = UtilString.replaceAll(valueString,"''","'");
837          columnType = insertTable.ColType(columnName);
838          if ( Utils.isNumberColumn(columnType) )
839          {
840             try
841             {
842                value = Double.parseDouble(valueString);
843             } catch (Exception e) {
844                throw new tinySQLException("Insert failed: column "
845                + columnName + " is numeric - found " + valueString);
846             }
847          }
848          columnSize = insertTable.ColSize(columnName);
849          if ( valueString.length() > columnSize )
850          {
851             throw new tinySQLException("Insert failed: string too long for "
852             + " column " + columnName + " "
853             + Integer.toString(valueString.length())
854             + " > " + Integer.toString(columnSize) + "<" + valueString +">");
855          }
856       }
857       insertTable.InsertRow(c, v);
858 /*
859  * Close the table file that has just been updated unless this is a
860  * PreparedStatement. In that case an explicit close must be done
861  * on the statement object to close any open files.
862  */
863       if ( !statementType.endsWith("tinySQLPreparedStatement") )
864          insertTable.close();
865    }
866 /*
867  * Creates a table given a tableName, and a Vector of column
868  * definitions.
869  *
870  * The column definitions are an array of tsColumn
871  */
872   abstract void CreateTable (String tableName, Vector v)
873     throws IOException, tinySQLException;
874 /*
875  * Creates new Columns given a tableName, and a Vector of
876  * column definition (tsColumn) arrays.<br>
877  *
878  * ALTER TABLE table [ * ] ADD [ COLUMN ] column type
879  */
880   abstract void AlterTableAddCol (String tableName, Vector v)
881     throws IOException, tinySQLException;
882
883 /*
884  * Deletes Columns given a tableName, and a Vector of
885  * column definition (tsColumn) arrays.<br>
886  *
887  * ALTER TABLE table DROP [ COLUMN ] column { RESTRICT | CASCADE }
888  */
889   abstract void AlterTableDropCol (String tableName, Vector v)
890     throws IOException, tinySQLException;
891 /*
892  * Rename columns
893  *
894  * ALTER TABLE table RENAME war TO peace
895  */
896   void AlterTableRenameCol (String tableName, String oldColname, String newColname)
897     throws IOException, tinySQLException
898   {
899       throw new tinySQLException("ALTER TABLE RENAME '" + oldColname + " TO " + newColname + "' is not supported");
900   }
901 /*
902  * Drops a table by name
903  */
904    abstract void DropTable (String tableName) throws tinySQLException;
905 /*
906  * Create a tinySQLTable object by table name
907  */
908    abstract tinySQLTable getTable(String tableName) throws tinySQLException;
909
910 }
911

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags