Java > Open Source Codes > smallsql > database > CommandSelect


1 /* =============================================================
2  * SmallSQL : a free Java DBMS library for the Java(tm) platform
3  * =============================================================
4  *
5  * (C) Copyright 2004-2006, by Volker Berlin.
6  *
7  * Project Info: http://www.smallsql.de/
8  *
9  * This library is free software; you can redistribute it and/or modify it
10  * under the terms of the GNU Lesser General Public License as published by
11  * the Free Software Foundation; either version 2.1 of the License, or
12  * (at your option) any later version.
13  *
14  * This library is distributed in the hope that it will be useful, but
15  * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
16  * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public
17  * License for more details.
18  *
19  * You should have received a copy of the GNU Lesser General Public
20  * License along with this library; if not, write to the Free Software
21  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301,
22  * USA.
23  *
24  * [Java is a trademark or registered trademark of Sun Microsystems, Inc.
25  * in the United States and other countries.]
26  *
27  * ---------------
28  * CommandSelect.java
29  * ---------------
30  * Author: Volker Berlin
31  *
32  */
33 package smallsql.database;
34
35 import java.sql.*;
36
37
38 class CommandSelect extends Command{
39
40     private DataSources from; // List of TableResult
41 private Expression where;
42     RowSource join;
43     private Expressions groupBy;
44     private Expression having;
45     private Expressions orderBy;
46     private boolean isAggregateFunction;
47     private int maxRows;
48     /** is set if the keyword DISTINCT is used */
49     private boolean isDistinct;
50
51     CommandSelect(Logger log){
52         super(log);
53     }
54     
55     CommandSelect(Logger log, Expressions columnExpressions){
56         super(log, columnExpressions);
57     }
58     
59     
60     boolean compile(SSConnection con) throws Exception {
61         boolean needCompile = false;
62         if(from != null){
63             for(int i=0; i<from.size(); i++){
64                 DataSource fromEntry = from.get(i);
65                 needCompile |= fromEntry.init( con );
66             }
67         }
68
69         if(join == null){
70             join = new NoFromResult();
71             from = new DataSources();
72             needCompile = true;
73         }
74         if(!needCompile) return false;
75
76         for(int i=0; i<columnExpressions.size(); i++){
77             Expression col = columnExpressions.get(i);
78             if(col.getAlias() == null){
79                 // für Ausdrücke automatische Namen vergeben
80 col.setAlias("col" + (i+1));
81             }
82
83             if(col.getType() != Expression.NAME){
84                 compileLinkExpressionParams(col);
85                 continue;
86             }
87
88             ExpressionName expr = (ExpressionName)col;
89
90             if("*".equals( expr.getName() )){
91                 String tableAlias = expr.getTableAlias();
92                 if(tableAlias != null){
93                     // Syntax: tableAlias.*
94 int t=0;
95                     for(; t<from.size(); t++){
96                         DataSource fromEntry = from.get(t);
97                         if(tableAlias.equalsIgnoreCase( fromEntry.getAlias() )){
98                             TableView table = fromEntry.getTableView();
99                             columnExpressions.remove(i);
100                             i = compileAdd_All_Table_Columns( fromEntry, table, i ) - 1;
101                             break;
102                         }
103                     }
104                     if(t==from.size()) throw Utils.createSQLException( "The column prefix '" + tableAlias + "' does not match with a table name or alias name used in this query" );
105                 }else{
106                     // Syntax *
107 columnExpressions.remove(i);
108                     for(int t=0; t<from.size(); t++){
109                         DataSource fromEntry = from.get(t);
110                         TableView table = fromEntry.getTableView();
111                         i = compileAdd_All_Table_Columns( fromEntry, table, i );
112                     }
113                     i--;
114                 }
115             }else{
116                 // not a * Syntax
117 compileLinkExpressionName( expr );
118             }
119
120         }
121         if(where != null) compileLinkExpression( where );
122         if(having != null) compileLinkExpression( having );
123         if(orderBy != null) {
124             for(int i=0; i<orderBy.size(); i++){
125                 compileLinkExpression( orderBy.get(i));
126             }
127         }
128         if(groupBy != null){
129             for(int i=0; i<groupBy.size(); i++){
130                 compileLinkExpression( groupBy.get(i) );
131             }
132         }
133
134         if(join instanceof Join){
135             compileJoin( (Join)join );
136         }
137         
138         if(where != null){
139             join = new Where( join, where );
140         }
141         
142         if(isGroupResult()) {
143             join = new GroupResult( this, join, groupBy, having, orderBy);
144             if(having != null){
145                 join = new Where( join, having );
146             }
147         }
148         
149         if(isDistinct){
150             join = new Distinct( join, columnExpressions );
151         }
152         
153         if(orderBy != null){
154             join = new SortedResult( join, orderBy );
155         }
156         
157         return true;
158     }
159
160     
161     /**
162      * If this ResultSet is use any type of grouping. This means that GroupResult need create and that
163      * the ResultSet is not updataable.
164      */
165     final boolean isGroupResult(){
166         return groupBy != null || having != null || isAggregateFunction;
167     }
168     
169     
170     /**
171      * Set the link between the Named Expression and the Table object
172      * in the condition.
173      * If there are cascade Joins then follow the tree with a recursion.
174      */
175     private void compileJoin( Join join ) throws Exception {
176         if(join.condition != null) compileLinkExpressionParams( join.condition );
177         if(join.left instanceof Join){
178             compileJoin( (Join)join.left );
179         }
180         if(join.right instanceof Join){
181             compileJoin( (Join)join.right );
182         }
183     }
184     
185     
186     private void compileLinkExpression( Expression expr) throws Exception {
187         if(expr.getType() == Expression.NAME)
188              compileLinkExpressionName( (ExpressionName)expr);
189         else compileLinkExpressionParams( expr );
190     }
191     
192     
193     /**
194      * setzt die Verbindung einer named Expresion zur Table und Spaltenindex
195      * This means a colun name in the SQL statement is link to it table source
196      */
197     private void compileLinkExpressionName( ExpressionName expr ) throws Exception {
198             String tableAlias = expr.getTableAlias();
199             if(tableAlias != null){
200                 int t=0;
201                 for(; t<from.size(); t++){
202                     DataSource fromEntry = from.get(t);
203                     if(tableAlias.equalsIgnoreCase( fromEntry.getAlias() )){
204                         TableView table = fromEntry.getTableView();
205                         int colIdx = table.findColumnIdx( expr.getName() );
206                         if(colIdx>=0){
207                             // Column gefunden und Table zur Expression setzen
208 expr.setFrom( fromEntry, colIdx, table );
209                             break;
210                         }else
211                             throw Utils.createSQLException("Invalid column name '" + expr.getName() + "'.");
212                     }
213                 }
214                 if(t==from.size()) throw Utils.createSQLException( "The column prefix '" + tableAlias + "' does not match with a table name or alias name used in this query" );
215             }else{
216                 // column name ohne table name
217 int t=0;
218                 for(; t<from.size(); t++){
219                     DataSource fromEntry = from.get(t);
220                     TableView table = fromEntry.getTableView();
221                     int colIdx = table.findColumnIdx( expr.getName() );
222                     if(colIdx>=0){
223                         // Column gefunden und Table zur Expression setzen
224 expr.setFrom( fromEntry, colIdx, table );
225                         break;
226                     }
227                 }
228                 if(t>=from.size()){
229                     throw Utils.createSQLException("Invalid column name '" + expr.getName() + "'.");
230                 }
231             }
232             compileLinkExpressionParams(expr);
233     }
234     
235
236     private void compileLinkExpressionParams(Expression expr) throws Exception {
237             // SubExpresion überprüfen
238 Expression[] params = expr.getParams();
239             isAggregateFunction = isAggregateFunction || expr.getType() >= Expression.GROUP_BEGIN;
240             if(params != null){
241                 for(int k=0; k<params.length; k++){
242                     Expression param = params[k];
243                     int type = param.getType();
244                     isAggregateFunction = isAggregateFunction || type >= Expression.GROUP_BEGIN;
245                     if(type == Expression.NAME)
246                          compileLinkExpressionName( (ExpressionName)param );
247                     else compileLinkExpressionParams( param );
248                 }
249             }
250     }
251     
252
253     private final int compileAdd_All_Table_Columns( DataSource fromEntry, TableView table, int position){
254         for(int k=0; k<table.columns.size(); k++){
255             ExpressionName expr = new ExpressionName( table.columns.get(k).getName() );
256             expr.setFrom( fromEntry, k, table );
257             columnExpressions.add( position++, expr );
258         }
259         return position;
260     }
261     
262
263     /**
264      * The main method to execute this Command and create a ResultSet.
265      */
266     void executeImpl(SSConnection con, SSStatement st) throws Exception {
267         compile(con);
268         if((st.rsType == ResultSet.TYPE_SCROLL_INSENSITIVE || st.rsType == ResultSet.TYPE_SCROLL_SENSITIVE) &&
269             !join.isScrollable()){
270             join = new Scrollable(join);
271         }
272         join.execute();
273         rs = new SSResultSet( st, this );
274     }
275     
276     
277     /**
278      * Is used from ResultSet.beforeFirst().
279      *
280      */
281     void beforeFirst() throws Exception {
282         join.beforeFirst();
283     }
284     
285     
286     /**
287      * Is used from ResultSet.isBeforeFirst().
288      */
289     boolean isBeforeFirst() throws SQLException{
290         return join.isBeforeFirst();
291     }
292     
293
294     /**
295      * Is used from ResultSet.isFirst().
296      */
297     boolean isFirst() throws SQLException{
298         return join.isFirst();
299     }
300     
301
302     /**
303      * Is used from ResultSet.first().
304      */
305     boolean first() throws Exception {
306         return join.first();
307     }
308
309
310     /**
311      * Is used from ResultSet.previous().
312      */
313     boolean previous() throws Exception {
314         return join.previous();
315     }
316
317
318     /**
319      * move to the next row.
320      * @return true if the next row valid
321      * @throws Exception
322      */
323     boolean next() throws Exception {
324         if(maxRows > 0 && join.getRow() >= maxRows){
325             join.afterLast();
326             return false;
327         }
328         return join.next();
329     }
330
331
332     /**
333      * Is used from ResultSet.last().
334      */
335     final boolean last() throws Exception {
336         if(maxRows > 0){
337             return join.absolute(maxRows);
338         }
339         return join.last();
340     }
341
342
343     /**
344      * Is used from ResultSet.afterLast().
345      */
346     final void afterLast() throws Exception {
347         join.afterLast();
348     }
349
350
351     /**
352      * Is used from ResultSet.isLast().
353      */
354     boolean isLast() throws Exception {
355         return join.isLast();
356     }
357     
358
359     /**
360      * Is used from ResultSet.isAfterLast().
361      */
362     boolean isAfterLast() throws Exception {
363         return join.isAfterLast();
364     }
365     
366
367     /**
368      * Is used from ResultSet.absolute().
369      */
370     final boolean absolute(int row) throws Exception {
371         return join.absolute(row);
372     }
373
374
375     /**
376      * Is used from ResultSet.relative().
377      */
378     final boolean relative(int rows) throws Exception {
379         return join.relative(rows);
380     }
381
382
383     /**
384      * Is used from ResultSet.afterLast().
385      */
386     final int getRow() throws Exception {
387         int row = join.getRow();
388         if(maxRows > 0 && row > maxRows) return 0;
389         return row;
390     }
391
392
393     final void updateRow(SSConnection con, Expression[] newRowSources) throws SQLException{
394         int savepoint = con.getSavepoint();
395         try{
396             //loop through all tables of this ResultSet
397 for(int t=0; t<from.size(); t++){
398                 TableViewResult result = TableViewResult.getTableViewResult( from.get(t) );
399                 TableView table = result.getTableView();
400                 Columns tableColumns = table.columns;
401                 int count = tableColumns.size();
402                 
403                 // order the new Values after it position in the table
404 Expression[] updateValues = new Expression[count];
405                 boolean isUpdateNeeded = false;
406                 for(int i=0; i<columnExpressions.size(); i++){
407                     Expression src = newRowSources[i];
408                     if(src != null && (!(src instanceof ExpressionValue) || !((ExpressionValue)src).isEmpty())){
409                         Expression col = columnExpressions.get(i);
410                         if(!col.isDefinitelyWritable())
411                             throw Utils.createSQLException("Column " + i + " is read only.");
412                         ExpressionName exp = (ExpressionName)col;
413                         if(table == exp.getTable()){
414                             updateValues[exp.getColumnIndex()] = src;
415                             isUpdateNeeded = true;
416                             continue;
417                         }
418                     }
419                 }
420                 
421                 // save the new values if there are new value for this table
422 if(isUpdateNeeded){
423                     result.updateRow(updateValues);
424                 }
425             }
426         }catch(Throwable e){
427             con.rollback(savepoint);
428             throw Utils.createSQLException(e);
429         }finally{
430             if(con.getAutoCommit()) con.commit();
431         }
432     }
433     
434     final void insertRow(SSConnection con, Expression[] newRowSources) throws SQLException{
435         if(from.size() > 1)
436             throw Utils.createSQLException("InsertRow not supported on joins.");
437         if(from.size() == 0)
438             throw Utils.createSQLException("InsertRow need a FROM expression.");
439         
440         int savepoint = con.getSavepoint();
441         try{
442             TableViewResult result = TableViewResult.getTableViewResult( from.get(0) );
443             TableView table = result.getTableView();
444             Columns tabColumns = table.columns;
445             int count = tabColumns.size();
446                     
447             // order the new Values after it position in the table
448 Expression[] updateValues = new Expression[count];
449             if(newRowSources != null){
450                 for(int i=0; i<columnExpressions.size(); i++){
451                     Expression src = newRowSources[i];
452                     if(src != null && (!(src instanceof ExpressionValue) || !((ExpressionValue)src).isEmpty())){
453                         Expression rsColumn = columnExpressions.get(i); // Column of the ResultSet
454 if(!rsColumn.isDefinitelyWritable())
455                             throw Utils.createSQLException("Column " + i + " is read only.");
456                         ExpressionName exp = (ExpressionName)rsColumn;
457                         if(table == exp.getTable()){
458                             updateValues[exp.getColumnIndex()] = src;
459                             continue;
460                         }
461                     }
462                     updateValues[i] = null;
463                 }
464             }
465                     
466             // save the new values if there are new value for this table
467 result.insertRow(updateValues);
468         }catch(Throwable e){
469             con.rollback(savepoint);
470             throw Utils.createSQLException(e);
471         }finally{
472             if(con.getAutoCommit()) con.commit();
473         }
474     }
475     
476     final void deleteRow(SSConnection con) throws SQLException{
477         int savepoint = con.getSavepoint();
478         try{
479             if(from.size() > 1)
480                 throw Utils.createSQLException("DeleteRow not supported on joins.");
481             if(from.size() == 0)
482                 throw Utils.createSQLException("DeleteRow need a FROM expression.");
483             TableViewResult.getTableViewResult( from.get(0) ).deleteRow();
484         }catch(Throwable e){
485             con.rollback(savepoint);
486             throw Utils.createSQLException(e);
487         }finally{
488             if(con.getAutoCommit()) con.commit();
489         }
490     }
491     
492     
493     /**
494      * The returning index start at 0.
495      */
496     public int findColumn(String columnName) throws SQLException {
497         Expressions columns = columnExpressions;
498         // FIXME performance
499 for(int i=0; i<columns.size(); i++){
500             if(columnName.equalsIgnoreCase(columns.get(i).getAlias()))
501                 return i;
502         }
503         throw Utils.createSQLException("Column '"+columnName+"' not found." );
504     }
505     
506     
507     /**
508      * Set if the keyword DISTINCT occur in the SELECT expession.
509      */
510     final void setDistinct(boolean distinct){
511         this.isDistinct = distinct;
512     }
513     
514     
515     /**
516      * Set the RowSource expression from the FROM clause.
517      * The Simples case is only a Table (TableResult)
518      */
519     final void setSource(RowSource join){
520         this.join = join;
521     }
522
523     /**
524      * List of all Tables and Views.
525      * This is needed to replace the table aliases in the columnExpressions with the real soures.
526      */
527     final void setFrom( DataSources from ){
528         this.from = from;
529     }
530
531     /**
532      * Is used from CommandSelect, CommandDelete and CommandUpdate
533      * @param where
534      */
535     final void setWhere( Expression where ){
536         this.where = where;
537     }
538
539     final void setGroup(Expressions group){
540         this.groupBy = group;
541     }
542
543     final void setHaving(Expression having){
544         this.having = having;
545     }
546
547     final void setOrder(Expressions order){
548         this.orderBy = order;
549     }
550     
551
552     final void setMaxRows(int max){
553         maxRows = max;
554     }
555     
556     
557     final int getMaxRows(){
558         return maxRows;
559     }
560 }

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags