001: /*
002: * $Id: TestAxionQueryPlanner.java,v 1.12 2005/12/20 23:52:09 ahimanikya Exp $
003: * =======================================================================
004: * Copyright (c) 2004 Axion Development Team. All rights reserved.
005: *
006: * Redistribution and use in source and binary forms, with or without
007: * modification, are permitted provided that the following conditions
008: * are met:
009: *
010: * 1. Redistributions of source code must retain the above
011: * copyright notice, this list of conditions and the following
012: * disclaimer.
013: *
014: * 2. Redistributions in binary form must reproduce the above copyright
015: * notice, this list of conditions and the following disclaimer in
016: * the documentation and/or other materials provided with the
017: * distribution.
018: *
019: * 3. The names "Tigris", "Axion", nor the names of its contributors may
020: * not be used to endorse or promote products derived from this
021: * software without specific prior written permission.
022: *
023: * 4. Products derived from this software may not be called "Axion", nor
024: * may "Tigris" or "Axion" appear in their names without specific prior
025: * written permission.
026: *
027: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
028: * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
029: * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
030: * PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
031: * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
032: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
033: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
034: * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
035: * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
036: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
037: * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
038: * =======================================================================
039: */
040:
041: package org.axiondb.engine.commands;
042:
043: import java.util.HashSet;
044: import java.util.Set;
045:
046: import junit.framework.Test;
047: import junit.framework.TestSuite;
048:
049: import org.axiondb.AbstractDbdirTest;
050: import org.axiondb.Column;
051: import org.axiondb.ColumnIdentifier;
052: import org.axiondb.Database;
053: import org.axiondb.FromNode;
054: import org.axiondb.Row;
055: import org.axiondb.RowIterator;
056: import org.axiondb.Table;
057: import org.axiondb.TableIdentifier;
058: import org.axiondb.constraints.PrimaryKeyConstraint;
059: import org.axiondb.engine.DiskDatabase;
060: import org.axiondb.engine.rows.SimpleRow;
061: import org.axiondb.engine.tables.DiskTable;
062: import org.axiondb.functions.EqualFunction;
063: import org.axiondb.types.IntegerType;
064:
065: /**
066: * <p>
067: * TODO: Add Test for the following
068: * <li>If we use a composite condition in join (i.e. join condition which involves "and"
069: * or "or" function rather than single comparison function) then index information is not
070: * used to carry out join. We should find out comparison functions which involves column
071: * from both left and right join table and still should find the index iterator and use it
072: * if available.
073: * <li>If ansi join uses a composite condition then the comparison function which
074: * involves only one table column is applied at table level.
075: * <li>If we do not use new ansi join syntax and join condition is composite join
076: * condition, then Comparison function involving only one table column in that join
077: * condition are being applied twice. Once at each table level and once after creation of
078: * join iterator.
079: * <li>column literal functions are applied at indexed level when possible.
080: * <li>Test in two table right outer join, if only left table of join has an index then
081: * IndexedNestedLoopJoinedRowIterator should be used.
082: * <li>Test three table join (two table have index)results in two indexed joined iterator
083: * nesting.
084: * <li>Test in two table inner join, use of composite condition (condition using and
085: * function) in ansi join on clause still results in use of
086: * IndexedNestedLoopJoinedRowIterator, if only one table is indexed.
087: * <li>Test in non ansi join syntax (where join is specified in where clause), if implied
088: * column literal conditions are applied at table level.
089: * <li>Add Test for dynamic index.
090: * <li>Test dynamic Index : if created then, should use
091: * IndexedNestedLoopJoinedRowIterator if there is no Index available on the join columns
092: *
093: * @version $Revision: 1.12 $ $Date: 2005/12/20 23:52:09 $
094: * @author Ahimanikya Satapathy
095: */
096: public class TestAxionQueryPlanner extends AbstractDbdirTest {
097:
098: //------------------------------------------------------------ Conventional
099:
100: public TestAxionQueryPlanner(String testName) {
101: super (testName);
102: }
103:
104: public static void main(String args[]) {
105: String[] testCaseName = { TestAxionQueryPlanner.class.getName() };
106: junit.textui.TestRunner.main(testCaseName);
107: }
108:
109: public static Test suite() {
110: return new TestSuite(TestAxionQueryPlanner.class);
111: }
112:
113: //--------------------------------------------------------------- Lifecycle
114: Database _db = null;
115:
116: public void setUp() throws Exception {
117: getDbdir().mkdirs();
118: _db = new DiskDatabase(getDbdir());
119: super .setUp();
120: }
121:
122: public void tearDown() throws Exception {
123: _db.shutdown();
124: super .tearDown();
125: }
126:
127: //------------------------------------------------------------------- Tests
128: protected void addRows(Table table, boolean addpk) throws Exception {
129: Row row = new SimpleRow(2);
130: for (int i = 0; i < 5; i++) {
131: row.set(0, new Integer(1 + i));
132: row.set(1, new Integer(51 + i));
133: table.addRow(row);
134: }
135: assertEquals("Should have 5 rows", 5, table.getRowCount());
136:
137: if (addpk) {
138: addPK(table);
139: }
140: }
141:
142: protected void addColumns(Table table) throws Exception {
143: table.addColumn(new Column("ID", new IntegerType()));
144: table.addColumn(new Column("SID", new IntegerType()));
145: }
146:
147: protected void addColumns(CreateTableCommand create)
148: throws Exception {
149: create.addColumn("ID", "int");
150: create.addColumn("SID", "integer");
151: }
152:
153: protected void addPK(Table table) throws Exception {
154: PrimaryKeyConstraint pk = new PrimaryKeyConstraint("pk_"
155: + table.getName());
156: ColumnIdentifier colId = new ColumnIdentifier(
157: new TableIdentifier(table.getName()), "ID");
158: pk.addSelectable(colId);
159: table.addConstraint(pk);
160: }
161:
162: protected Table createTable(String name) throws Exception {
163: return new DiskTable(name, _db);
164: }
165:
166: private void createTables1() throws Exception {
167:
168: Table table1 = createTable("A");
169: addColumns(table1);
170: addRows(table1, true);
171: _db.addTable(table1);
172:
173: Table table2 = createTable("B");
174: addColumns(table2);
175: addRows(table2, true);
176: _db.addTable(table2);
177:
178: Table table3 = createTable("C");
179: addColumns(table3);
180: _db.addTable(table3);
181:
182: Table table4 = createTable("A2");
183: addColumns(table4);
184: addRows(table4, true);
185: _db.addTable(table4);
186: }
187:
188: public void testNestedLoopJoinWithIndexedJoin1() throws Exception {
189: createTables1();
190: doTestNestedLoopJoinWithIndexedJoin();
191: }
192:
193: private void createTables2() throws Exception {
194: CreateTableCommand create = new CreateTableCommand("A");
195: addColumns(create);
196: create.execute(_db);
197: Table table1 = _db.getTable("A");
198: addRows(table1, true);
199:
200: create = new CreateTableCommand("B");
201: addColumns(create);
202: create.execute(_db);
203: Table table2 = _db.getTable("B");
204: addRows(table2, true);
205:
206: create = new CreateTableCommand("C");
207: addColumns(create);
208: create.execute(_db);
209:
210: create = new CreateTableCommand("A2");
211: addColumns(create);
212: create.execute(_db);
213: Table table4 = _db.getTable("A2");
214: addRows(table4, true);
215: }
216:
217: public void testNestedLoopJoinWithIndexedJoin2() throws Exception {
218: createTables2();
219: doTestNestedLoopJoinWithIndexedJoin();
220: }
221:
222: private void doTestNestedLoopJoinWithIndexedJoin() throws Exception {
223: CreateViewCommand cmd = new CreateViewCommand();
224: cmd.setObjectName("threetablejoinview");
225: cmd.setIfNotExists(true);
226: cmd.setSubQuery("select a.* from a inner join b on "
227: + " a.id = b.id left outer join c on c.id = a.id");
228: cmd.execute(_db);
229: Table view = _db.getTable("threetablejoinview");
230: assertNotNull(view);
231: assertNotNull(view.getRowIterator(false));
232: assertEquals(5, view.getRowCount());
233:
234: AxionQueryContext ctx = new AxionQueryContext();
235:
236: TableIdentifier taid = new TableIdentifier("A");
237: TableIdentifier tbid = new TableIdentifier("B");
238: TableIdentifier tcid = new TableIdentifier("C");
239: TableIdentifier ta2id = new TableIdentifier("A2");
240:
241: ColumnIdentifier aid = new ColumnIdentifier(taid, "ID", "AID");
242: ColumnIdentifier bid = new ColumnIdentifier(tbid, "ID", "BID");
243: ColumnIdentifier bsid = new ColumnIdentifier(tbid, "SID",
244: "BSID");
245: ColumnIdentifier cid = new ColumnIdentifier(tcid, "ID", "CID");
246: ColumnIdentifier a2id = new ColumnIdentifier(ta2id, "ID");
247:
248: ctx.addSelect(aid);
249: ctx.addSelect(bsid);
250: ctx.addSelect(cid);
251:
252: FromNode from1 = new FromNode();
253: from1.setLeft(taid);
254: assertTrue(from1.hasLeft());
255: from1.setRight(tbid);
256: EqualFunction eq1 = new EqualFunction();
257: eq1.addArgument(aid);
258: eq1.addArgument(bid);
259: from1.setCondition(eq1);
260: assertNotNull(from1.toString());
261: from1.setType(FromNode.TYPE_INNER);
262: assertNotNull(from1.toString());
263: assertEquals("unknown?", FromNode.typeToString(999));
264:
265: FromNode from2 = new FromNode();
266: from2.setLeft(from1);
267: assertTrue(from2.hasLeft());
268: from2.setRight(tcid);
269: EqualFunction eq2 = new EqualFunction();
270: eq2.addArgument(aid);
271: eq2.addArgument(cid);
272: assertFalse(from2.hasCondition());
273: from2.setCondition(eq2);
274: assertTrue(from2.hasCondition());
275: from2.setType(FromNode.TYPE_LEFT);
276: assertNotNull(from2.toString());
277:
278: // Nested Right join
279: FromNode from3 = new FromNode();
280: from3.setLeft(ta2id);
281: assertTrue(from3.hasLeft());
282: from3.setRight(from2);
283: EqualFunction eq3 = new EqualFunction();
284: eq3.addArgument(a2id);
285: eq3.addArgument(aid);
286: assertFalse(from3.hasCondition());
287: from3.setCondition(eq3);
288: assertTrue(from3.hasCondition());
289: from3.setType(FromNode.TYPE_RIGHT);
290: assertNotNull(from3.toString());
291:
292: ctx.setFrom(from3);
293: SubSelectCommand selcmd = new SubSelectCommand(ctx);
294: RowIterator iter = selcmd.getRowIterator(_db);
295:
296: assertNotNull(iter);
297: for (int i = 0; i < 5; i++) {
298: assertTrue(iter.hasNext());
299: Row row = iter.next();
300: assertNotNull(row);
301: assertEquals(new Integer(1 + i), row.get(0));
302: assertEquals(new Integer(51 + i), row.get(1));
303: assertEquals(null, row.get(2));
304: }
305: assertTrue(!iter.hasNext());
306:
307: iter.reset();
308: for (int i = 0; i < 5; i++) {
309: assertTrue(iter.hasNext());
310: Row row = iter.next();
311: assertNotNull(row);
312: assertEquals(new Integer(1 + i), row.get(0));
313: assertEquals(new Integer(51 + i), row.get(1));
314: assertEquals(null, row.get(2));
315: }
316: assertTrue(!iter.hasNext());
317: }
318:
319: public void testPlannerToString() {
320: AxionQueryPlanner planner = new AxionQueryPlanner(
321: new AxionQueryContext());
322: assertNotNull(planner.toString());
323: AxionQueryPlanner.JoinCondition jcondition = planner.new JoinCondition(
324: new HashSet());
325: assertNotNull(jcondition.toString());
326:
327: AxionQueryPlanner.QueryPlannerJoinContext jcontext = planner.new QueryPlannerJoinContext();
328: assertNotNull(jcontext.toString());
329: }
330:
331: public void testDecomposeWhereConditionNodes() {
332: Set set = new HashSet();
333: String str = "BOGUS";
334: set.add(str);
335: Set newSet[] = AxionQueryOptimizer
336: .decomposeWhereConditionNodes(set, false);
337: assertTrue(newSet[0].isEmpty());
338: assertTrue(newSet[1].isEmpty());
339: assertTrue(!newSet[2].isEmpty());
340:
341: }
342:
343: public void testcreateOneRootFunctionForNullSet() {
344: assertNull(AxionQueryOptimizer
345: .createOneRootFunction(new HashSet()));
346: assertNull(AxionQueryOptimizer.createOneRootFunction(null));
347: }
348:
349: }
|