KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > mckoi > database > TableFunctions


1 /**
2  * com.mckoi.database.TableFunctions 08 Nov 2001
3  *
4  * Mckoi SQL Database ( http://www.mckoi.com/database )
5  * Copyright (C) 2000, 2001, 2002 Diehl and Associates, Inc.
6  *
7  * This program is free software; you can redistribute it and/or
8  * modify it under the terms of the GNU General Public License
9  * Version 2 as published by the Free Software Foundation.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License Version 2 for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * Version 2 along with this program; if not, write to the Free Software
18  * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
19  *
20  * Change Log:
21  *
22  *
23  */
24
25 package com.mckoi.database;
26
27 import com.mckoi.util.IntegerVector;
28
29 /**
30  * A number of functions that are table set functions such as simple select
31  * operations, joins, unions, sub-query operations, etc.
32  *
33  * @author Tobias Downer
34  */
35
36 public class TableFunctions {
37
38
39   /**
40    * The function for a non-correlated ANY or ALL sub-query operation between a
41    * left and right branch. An example of an SQL query that generates such a
42    * query is;
43    * <p><pre>
44    * Table.col > ANY ( SELECT .... )
45    * </pre><p>
46    * This function only works non-correlated sub-queries.
47    * <p>
48    * A non-correlated sub-query, or a correlated sub-query where the correlated
49    * variables are references to a parent plan branch, the plan only need be
50    * evaluated once and optimizations on the query present themselves.
51    */
52   static Table anyAllNonCorrelated(Table left_table, Variable[] left_vars,
53                                    Operator op, Table right_table) {
54     // Check the right table and the correct number of columns,
55 if (right_table.getColumnCount() != left_vars.length) {
56       throw new RuntimeException JavaDoc(
57                         "Input table <> " + left_vars.length + " columns.");
58     }
59
60     // Handle trivial case of no entries to select from
61 if (left_table.getRowCount() == 0) {
62       return left_table;
63     }
64
65     // Resolve the vars in the left table and check the references are
66 // compatible.
67 int sz = left_vars.length;
68     int[] left_col_map = new int[sz];
69     int[] right_col_map = new int[sz];
70     for (int i = 0; i < sz; ++i) {
71       left_col_map[i] = left_table.findFieldName(left_vars[i]);
72       right_col_map[i] = i;
73
74 // System.out.println("Finding: " + left_vars[i]);
75 // System.out.println("left_col_map: " + left_col_map[i]);
76 // System.out.println("right_col_map: " + right_col_map[i]);
77
78       if (left_col_map[i] == -1) {
79         throw new RuntimeException JavaDoc("Invalid reference: " + left_vars[i]);
80       }
81       DataTableColumnDef left_type =
82                                  left_table.getColumnDefAt(left_col_map[i]);
83       DataTableColumnDef right_type = right_table.getColumnDefAt(i);
84       if (!left_type.getTType().comparableTypes(right_type.getTType())) {
85         throw new Error JavaDoc(
86             "The type of the sub-query expression " + left_vars[i] + "(" +
87             left_type.getSQLTypeString() + ") is incompatible with " +
88             "the sub-query type " + right_type.getSQLTypeString() + ".");
89       }
90     }
91
92     // We now have all the information to solve this query.
93
94     IntegerVector select_vec;
95
96     if (op.isSubQueryForm(Operator.ALL)) {
97       // ----- ALL operation -----
98 // We work out as follows:
99 // For >, >= type ALL we find the highest value in 'table' and
100 // select from 'source' all the rows that are >, >= than the
101 // highest value.
102 // For <, <= type ALL we find the lowest value in 'table' and
103 // select from 'source' all the rows that are <, <= than the
104 // lowest value.
105 // For = type ALL we see if 'table' contains a single value. If it
106 // does we select all from 'source' that equals the value, otherwise an
107 // empty table.
108 // For <> type ALL we use the 'not in' algorithm.
109
110       if (op.is(">") || op.is(">=")) {
111         // Select the last from the set (the highest value),
112 TObject[] highest_cells =
113                                 right_table.getLastCellContent(right_col_map);
114         // Select from the source table all rows that are > or >= to the
115 // highest cell,
116 select_vec = left_table.selectRows(left_col_map, op, highest_cells);
117       }
118       else if (op.is("<") || op.is("<=")) {
119         // Select the first from the set (the lowest value),
120 TObject[] lowest_cells =
121                                right_table.getFirstCellContent(right_col_map);
122         // Select from the source table all rows that are < or <= to the
123 // lowest cell,
124 select_vec = left_table.selectRows(left_col_map, op, lowest_cells);
125       }
126       else if (op.is("=")) {
127         // Select the single value from the set (if there is one).
128 TObject[] single_cell =
129                              right_table.getSingleCellContent(right_col_map);
130         if (single_cell != null) {
131           // Select all from source_table all values that = this cell
132 select_vec = left_table.selectRows(left_col_map, op, single_cell);
133         }
134         else {
135           // No single value so return empty set (no value in LHS will equal
136 // a value in RHS).
137 return left_table.emptySelect();
138         }
139       }
140       else if (op.is("<>")) {
141         // Equiv. to NOT IN
142 select_vec = INHelper.notIn(left_table, right_table,
143                                     left_col_map, right_col_map);
144       }
145       else {
146         throw new RuntimeException JavaDoc(
147                            "Don't understand operator '" + op + "' in ALL.");
148       }
149     }
150     else if (op.isSubQueryForm(Operator.ANY)) {
151
152       // ----- ANY operation -----
153 // We work out as follows:
154 // For >, >= type ANY we find the lowest value in 'table' and
155 // select from 'source' all the rows that are >, >= than the
156 // lowest value.
157 // For <, <= type ANY we find the highest value in 'table' and
158 // select from 'source' all the rows that are <, <= than the
159 // highest value.
160 // For = type ANY we use same method from INHelper.
161 // For <> type ANY we iterate through 'source' only including those
162 // rows that a <> query on 'table' returns size() != 0.
163
164       if (op.is(">") || op.is(">=")) {
165         // Select the first from the set (the lowest value),
166 TObject[] lowest_cells =
167                                right_table.getFirstCellContent(right_col_map);
168         // Select from the source table all rows that are > or >= to the
169 // lowest cell,
170 select_vec = left_table.selectRows(left_col_map, op, lowest_cells);
171       }
172       else if (op.is("<") || op.is("<=")) {
173         // Select the last from the set (the highest value),
174 TObject[] highest_cells =
175                                 right_table.getLastCellContent(right_col_map);
176         // Select from the source table all rows that are < or <= to the
177 // highest cell,
178 select_vec = left_table.selectRows(left_col_map, op, highest_cells);
179       }
180       else if (op.is("=")) {
181         // Equiv. to IN
182 select_vec = INHelper.in(left_table, right_table,
183                                  left_col_map, right_col_map);
184       }
185       else if (op.is("<>")) {
186         // Select the value that is the same of the entire column
187 TObject[] cells = right_table.getSingleCellContent(right_col_map);
188         if (cells != null) {
189           // All values from 'source_table' that are <> than the given cell.
190 select_vec = left_table.selectRows(left_col_map, op, cells);
191         }
192         else {
193           // No, this means there are different values in the given set so the
194 // query evaluates to the entire table.
195 return left_table;
196         }
197       }
198       else {
199         throw new RuntimeException JavaDoc(
200                            "Don't understand operator '" + op + "' in ANY.");
201       }
202     }
203     else {
204       throw new RuntimeException JavaDoc("Unrecognised sub-query operator.");
205     }
206
207     // Make into a table to return.
208 VirtualTable rtable = new VirtualTable(left_table);
209     rtable.set(left_table, select_vec);
210
211     return rtable;
212   }
213
214
215
216
217
218
219
220
221
222 }
223
Popular Tags