INHelper


1   /**
2    * com.mckoi.database.INHelper  17 Sep 1998
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.BlockIntegerList;
28  import com.mckoi.util.IntegerVector;
29  
30  /**
31   * This is a static class that provides methods for performing the Query
32   * table command 'in' and 'not in'.  This command finds a match between one
33   * of the columns in two tables.  If match between a cell in one column is also
34   * found in the column of the other table, the row is included in the resultant
35   * table (or discluded (is that a word?) for 'not in').
36   * <p>
37   * @author Tobias Downer
38   */
39  
40  final class INHelper {
41  
42    /**
43     * This implements the 'in' command.  Returns the rows selected from table1.
44     * <p>
45     * <strong>NOTE:</strong> This is actually an incorrect implementation.  We
46     *   only keep for compatibility with DQL system.  The may return multiple
47     *   values from 'table1'
48     */
49    final static IntegerVector origIn(Table table1, Table table2,
50                                      int column1, int column2) {
51  
52      // First pick the the smallest and largest table.  We only want to iterate
53      // through the smallest table.
54      // NOTE: This optimisation can't be performed for the 'not_in' command.
55  
56      Table small_table;
57      Table large_table;
58      int small_column;
59      int large_column;
60  
61      if (table1.getRowCount() < table2.getRowCount()) {
62        small_table = table1;
63        large_table = table2;
64  
65        small_column = column1;
66        large_column = column2;
67  
68      }
69      else {
70        small_table = table2;
71        large_table = table1;
72  
73        small_column = column2;
74        large_column = column1;
75      }
76  
77      // Iterate through the small table's column.  If we can find identical
78      // cells in the large table's column, then we should include the row in our
79      // final result.
80  
81      IntegerVector result_rows = new IntegerVector();
82  
83      RowEnumeration e = small_table.rowEnumeration();
84  
85      Operator EQUALSOP = Operator.get("=");
86  
87      while (e.hasMoreRows()) {
88  
89        int small_row_index = e.nextRowIndex();
90        TObject cell =
91                    small_table.getCellContents(small_column, small_row_index);
92  
93        IntegerVector selected_set =
94                    large_table.selectRows(large_column, EQUALSOP, cell);
95  
96        // We've found cells that are IN both columns,
97  
98        if (selected_set.size() > 0) {
99  
100         // If the large table is what our result table will be based on, append
101         // the rows selected to our result set.  Otherwise add the index of
102         // our small table.  This only works because we are performing an
103         // EQUALS operation.
104 
105         if (large_table == table1) {
106           result_rows.append(selected_set);
107         }
108         else {
109           result_rows.addInt(small_row_index);
110         }
111       }
112 
113     }
114 
115     return result_rows;
116 
117   }
118 
119   /**
120    * This implements the 'in' command.  Returns the rows selected from table1.
121    * This correctly implements the 'in' relation.  The 'origIn' implementation
122    * may return multiple rows from the largest table.
123    */
124   final static IntegerVector in(Table table1, Table table2,
125                                 int column1, int column2) {
126 
127     // First pick the the smallest and largest table.  We only want to iterate
128     // through the smallest table.
129     // NOTE: This optimisation can't be performed for the 'not_in' command.
130 
131     Table small_table;
132     Table large_table;
133     int small_column;
134     int large_column;
135 
136     if (table1.getRowCount() < table2.getRowCount()) {
137       small_table = table1;
138       large_table = table2;
139 
140       small_column = column1;
141       large_column = column2;
142 
143     }
144     else {
145       small_table = table2;
146       large_table = table1;
147 
148       small_column = column2;
149       large_column = column1;
150     }
151 
152     // Iterate through the small table's column.  If we can find identical
153     // cells in the large table's column, then we should include the row in our
154     // final result.
155 
156     BlockIntegerList result_rows = new BlockIntegerList();
157 
158     RowEnumeration e = small_table.rowEnumeration();
159 
160     Operator EQUALSOP = Operator.get("=");
161 
162     while (e.hasMoreRows()) {
163 
164       int small_row_index = e.nextRowIndex();
165       TObject cell =
166                  small_table.getCellContents(small_column, small_row_index);
167 
168       IntegerVector selected_set =
169                  large_table.selectRows(large_column, EQUALSOP, cell);
170 
171       // We've found cells that are IN both columns,
172 
173       if (selected_set.size() > 0) {
174 
175         // If the large table is what our result table will be based on, append
176         // the rows selected to our result set.  Otherwise add the index of
177         // our small table.  This only works because we are performing an
178         // EQUALS operation.
179 
180         if (large_table == table1) {
181           // Only allow unique rows into the table set.
182           int sz = selected_set.size();
183           boolean rs = true;
184           for (int i = 0; rs == true && i < sz; ++i) {
185             rs = result_rows.uniqueInsertSort(selected_set.intAt(i));
186           }
187         }
188         else {
189           // Don't bother adding in sorted order because it's not important.
190           result_rows.add(small_row_index);
191         }
192       }
193 
194     }
195 
196     return new IntegerVector(result_rows);
197 
198   }
199 
200   /**
201    * A multi-column version of IN.
202    */
203   final static IntegerVector in(Table table1, Table table2,
204                                 int[] t1_cols, int[] t2_cols) {
205     if (t1_cols.length > 1) {
206       throw new Error  ("Multi-column 'in' not supported.");
207     }
208     return in(table1, table2, t1_cols[0], t2_cols[0]);
209   }
210 
211   /**
212    * This implements the 'not_in' command.
213    * ISSUE: This will be less efficient than 'in' if table1 has many rows and
214    *   table2 has few rows.
215    */
216   final static IntegerVector notIn(Table table1, Table table2,
217                                    int col1, int col2) {
218 
219     // Handle trivial cases
220     int t2_row_count = table2.getRowCount();
221     if (t2_row_count == 0) {
222       // No rows so include all rows.
223       return table1.selectAll(col1);
224     }
225     else if (t2_row_count == 1) {
226       // 1 row so select all from table1 that doesn't equal the value.
227       RowEnumeration e = table2.rowEnumeration();
228       TObject cell = table2.getCellContents(col2, e.nextRowIndex());
229       return table1.selectRows(col1, Operator.get("<>"), cell);
230     }
231 
232     // Iterate through table1's column.  If we can find identical cell in the
233     // tables's column, then we should not include the row in our final
234     // result.
235     IntegerVector result_rows = new IntegerVector();
236 
237     RowEnumeration e = table1.rowEnumeration();
238 
239     Operator EQUALSOP = Operator.get("=");
240 
241     while (e.hasMoreRows()) {
242 
243       int row_index = e.nextRowIndex();
244       TObject cell = table1.getCellContents(col1, row_index);
245 
246       IntegerVector selected_set =
247                             table2.selectRows(col2, Operator.get("="), cell);
248 
249       // We've found a row in table1 that doesn't have an identical cell in
250       // table2, so we should include it in the result.
251 
252       if (selected_set.size() <= 0) {
253         result_rows.addInt(row_index);
254       }
255 
256     }
257 
258     return result_rows;
259   }
260 
261   /**
262    * A multi-column version of NOT IN.
263    */
264   final static IntegerVector notIn(Table table1, Table table2,
265                                    int[] t1_cols, int[] t2_cols) {
266     if (t1_cols.length > 1) {
267       throw new Error  ("Multi-column 'not in' not supported.");
268     }
269     return notIn(table1, table2, t1_cols[0], t2_cols[0]);
270   }
271 
272 
273 }
274
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags