001: /*
002: * $Id: TestSelectCommand.java,v 1.16 2005/12/20 18:32:27 ahimanikya Exp $
003: * =======================================================================
004: * Copyright (c) 2002-2003 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.io.InputStream;
044: import java.sql.ResultSet;
045: import java.util.ArrayList;
046: import java.util.Arrays;
047: import java.util.List;
048: import java.util.Properties;
049:
050: import junit.framework.Test;
051: import junit.framework.TestCase;
052: import junit.framework.TestSuite;
053:
054: import org.axiondb.AxionCommand;
055: import org.axiondb.ColumnIdentifier;
056: import org.axiondb.Database;
057: import org.axiondb.Literal;
058: import org.axiondb.OrderNode;
059: import org.axiondb.Row;
060: import org.axiondb.RowIterator;
061: import org.axiondb.Selectable;
062: import org.axiondb.TableIdentifier;
063: import org.axiondb.engine.BaseDatabase;
064: import org.axiondb.engine.MemoryDatabase;
065: import org.axiondb.functions.EqualFunction;
066: import org.axiondb.functions.FunctionIdentifier;
067: import org.axiondb.types.CharacterVaryingType;
068: import org.axiondb.types.IntegerType;
069:
070: /**
071: * @version $Revision: 1.16 $ $Date: 2005/12/20 18:32:27 $
072: * @author Chuck Burdick
073: * @author Ahimanikya Satapathy
074: */
075: public class TestSelectCommand extends TestCase {
076:
077: //------------------------------------------------------------ Conventional
078:
079: public TestSelectCommand(String testName) {
080: super (testName);
081: }
082:
083: public static void main(String args[]) {
084: String[] testCaseName = { TestSelectCommand.class.getName() };
085: junit.textui.TestRunner.main(testCaseName);
086: }
087:
088: public static Test suite() {
089: return new TestSuite(TestSelectCommand.class);
090: }
091:
092: //--------------------------------------------------------------- Lifecycle
093: Database _db = null;
094:
095: public void setUp() throws Exception {
096: super .setUp();
097: _db = new MemoryDatabase();
098: {
099: CreateTableCommand cmd = new CreateTableCommand("FOO");
100: cmd.addColumn("A", "varchar", "10");
101: cmd.addColumn("B", "integer");
102: cmd.execute(_db);
103: }
104:
105: AxionCommand cmd = new InsertCommand(
106: new TableIdentifier("FOO"), Arrays
107: .asList(new ColumnIdentifier[] {
108: new ColumnIdentifier("A"),
109: new ColumnIdentifier("B") }),
110: Arrays.asList(new Literal[] {
111: new Literal("alabama",
112: new CharacterVaryingType(10)),
113: new Literal("100", new IntegerType()) }));
114: cmd.executeUpdate(_db);
115:
116: cmd = new InsertCommand(new TableIdentifier("FOO"), Arrays
117: .asList(new ColumnIdentifier[] {
118: new ColumnIdentifier("A"),
119: new ColumnIdentifier("B") }), Arrays
120: .asList(new Literal[] {
121: new Literal("alaska", new CharacterVaryingType(
122: 10)),
123: new Literal("200", new IntegerType()) }));
124: cmd.executeUpdate(_db);
125:
126: cmd = new InsertCommand(new TableIdentifier("FOO"), Arrays
127: .asList(new ColumnIdentifier[] {
128: new ColumnIdentifier("A"),
129: new ColumnIdentifier("B") }), Arrays
130: .asList(new Literal[] {
131: new Literal("alabama",
132: new CharacterVaryingType(10)),
133: new Literal("100", new IntegerType()) }));
134: cmd.executeUpdate(_db);
135: }
136:
137: //------------------------------------------------------------------- Tests
138:
139: public void testSelect() throws Exception {
140: assertEquals("Should have 3 rows", 3, _db.getTable("FOO")
141: .getRowCount());
142:
143: AxionQueryContext ctx = new AxionQueryContext();
144: TableIdentifier tid = new TableIdentifier("FOO");
145: ColumnIdentifier aid = new ColumnIdentifier(tid, "A");
146: ColumnIdentifier bid = new ColumnIdentifier(tid, "B");
147: ctx.addSelect(aid);
148: ctx.addSelect(bid);
149: assertNotNull(ctx.toString());
150:
151: ctx.addFrom(tid);
152: ctx.setDistinct(true);
153: ctx.setLimit(new Literal("1", new IntegerType()));
154: assertNotNull(ctx.toString());
155:
156: FunctionIdentifier fn = new FunctionIdentifier("=");
157: fn.addArgument(bid);
158: fn
159: .addArgument(new Literal("100",
160: new CharacterVaryingType(10)));
161: ctx.setWhere(fn);
162:
163: assertNotNull(ctx.toString());
164: List glist = new ArrayList();
165: glist.add(aid);
166: glist.add(bid);
167: ctx.setGroupBy(glist);
168: assertNotNull(ctx.toString());
169:
170: List olist = new ArrayList();
171: olist.add(new OrderNode(aid, true));
172: olist.add(new OrderNode(bid, false));
173: ctx.setOrderBy(olist);
174: assertNotNull(ctx.toString());
175:
176: SelectCommand cmd = new SelectCommand(ctx);
177:
178: assertNotNull(cmd.toString());
179:
180: ResultSet rset = cmd.executeQuery(_db);
181: assertTrue("Should have a row", rset.next());
182: assertEquals("Should get value", "alabama", rset.getString(1));
183: assertTrue("Should not have row", !rset.next());
184:
185: try {
186: ctx.setGroupBy(glist);
187: fail("Exception Expected, already resolved");
188: } catch (IllegalStateException e) {
189: // expected
190: }
191:
192: try {
193: ctx.addFrom(tid);
194: fail("Exception Expected, already resolved");
195: } catch (IllegalStateException e) {
196: // expected
197: }
198:
199: }
200:
201: public void testSelect2() throws Exception {
202: assertEquals("Should have 3 rows", 3, _db.getTable("FOO")
203: .getRowCount());
204:
205: AxionQueryContext ctx = new AxionQueryContext();
206: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
207: "A"));
208: ctx.addFrom(new TableIdentifier("FOO"));
209: SelectCommand cmd = new SelectCommand(ctx);
210:
211: assertNotNull(cmd.toString()); // prove toString doesn't do anything too weird
212:
213: ResultSet rset = cmd.executeQuery(_db);
214: assertTrue("Should have a row", rset.next());
215: assertEquals("Should get value", "alabama", rset.getString(1));
216: assertTrue("Should have a row", rset.next());
217: assertEquals("Should get value", "alaska", rset.getString(1));
218:
219: try {
220: ctx.setSelect(0, new ColumnIdentifier(new TableIdentifier(
221: "FOO"), "B"));
222: fail("Exception Expected, already resolved");
223: } catch (IllegalStateException e) {
224: // expected
225: }
226:
227: RowIterator rows = cmd.makeRowIterator(_db, true);
228: assertTrue("Should have a row", rows.hasNext());
229: Row row = rows.next();
230: assertEquals("Should get value", "alabama", row.get(0));
231: assertTrue("Should have a row", rows.hasNext());
232: row = rows.next();
233: assertEquals("Should get value", "alaska", row.get(0));
234:
235: }
236:
237: public void testSubSelect() throws Exception {
238: assertEquals("Should have 3 rows", 3, _db.getTable("FOO")
239: .getRowCount());
240:
241: AxionQueryContext ctx = new AxionQueryContext();
242: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
243: "A"));
244: ctx.addFrom(new TableIdentifier("FOO"));
245: ctx.setAliasName("MYFOO");
246: SubSelectCommand cmd = new SubSelectCommand(ctx);
247: cmd.setDB(_db);
248:
249: assertNotNull(cmd.toString()); // prove toString doesn't do anything too weird
250:
251: try {
252: cmd.executeQuery(_db);
253: fail("Expected Exception");
254: } catch (Exception e) {
255: // expected
256: }
257:
258: try {
259: cmd.execute(_db);
260: fail("Expected Exception");
261: } catch (Exception e) {
262: // expected
263: }
264:
265: try {
266: cmd.executeUpdate(_db);
267: fail("Expected Exception");
268: } catch (Exception e) {
269: // expected
270: }
271:
272: assertEquals("MYFOO", cmd.getAlias().toUpperCase());
273: assertEquals("MYFOO", cmd.getLabel().toUpperCase());
274:
275: RowIterator iter = (RowIterator) cmd.evaluate(null);
276:
277: assertNotNull(iter);
278: assertTrue(iter.hasNext());
279: assertNotNull(iter.next());
280: assertTrue(iter.hasNext());
281: assertNotNull(iter.next());
282: assertTrue(iter.hasNext());
283: assertNotNull(iter.next());
284: assertTrue(!iter.hasNext());
285: }
286:
287: public void testScalarSubSelect() throws Exception {
288: AxionQueryContext ctx = new AxionQueryContext();
289: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
290: "A"));
291: ctx.addFrom(new TableIdentifier("FOO"));
292: ctx.setAliasName("MYFOO");
293:
294: SubSelectCommand cmd = new SubSelectCommand(ctx);
295: cmd.setDB(_db);
296: cmd.setEvaluteAsScalarValue();
297:
298: EqualFunction eq = new EqualFunction();
299: eq.addArgument(new ColumnIdentifier(new TableIdentifier("FOO"),
300: "A"));
301: eq.addArgument(new Literal("alaska", new CharacterVaryingType(
302: 10)));
303: ctx.setWhere(eq);
304: assertEquals("alaska", cmd.evaluate(null));
305: }
306:
307: public void testReadonlyDBForSubSelect() throws Exception {
308: InputStream in = BaseDatabase.class.getClassLoader()
309: .getResourceAsStream("org/axiondb/axiondb.properties");
310: Properties prop = new Properties();
311: prop.load(in);
312: prop.setProperty("readonly", "yes");
313: MemoryDatabase db = new MemoryDatabase("readonlydb", prop);
314:
315: AxionQueryContext ctx = new AxionQueryContext();
316: ctx.addSelect(new ColumnIdentifier(new TableIdentifier(
317: "AXION_TABLES"), "TABLE_NAME"));
318: ctx.addFrom(new TableIdentifier("AXION_TABLES"));
319:
320: SubSelectCommand cmd = new SubSelectCommand(ctx);
321: cmd.setDB(db);
322: cmd.setEvaluteAsScalarValue();
323:
324: EqualFunction eq = new EqualFunction();
325: eq.addArgument(new ColumnIdentifier(new TableIdentifier(
326: "AXION_TABLES"), "TABLE_NAME"));
327: eq.addArgument(new Literal("AXION_COLUMNS",
328: new CharacterVaryingType(20)));
329: ctx.setWhere(eq);
330: assertEquals("AXION_COLUMNS", cmd.evaluate(null));
331: }
332:
333: public void testBadScalarSubSelect() throws Exception {
334: AxionQueryContext ctx = new AxionQueryContext();
335: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
336: "A"));
337: ctx.addFrom(new TableIdentifier("FOO"));
338: ctx.setAliasName("MYFOO");
339:
340: SubSelectCommand cmd = new SubSelectCommand(ctx);
341: cmd.setDB(_db);
342: cmd.setEvaluteAsScalarValue();
343:
344: try {
345: cmd.evaluate(null);
346: fail("Expected Exception");
347: } catch (Exception e) {
348: // expected
349: }
350: }
351:
352: public void testGroupBy() throws Exception {
353: AxionQueryContext ctx = new AxionQueryContext();
354:
355: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
356: "A"));
357: Selectable countfn = new FunctionIdentifier("COUNT", Arrays
358: .asList(new ColumnIdentifier[] { new ColumnIdentifier(
359: "B") }));
360: ctx.addSelect(countfn);
361: ctx.addFrom(new TableIdentifier("FOO"));
362: ctx.setGroupBy(Arrays
363: .asList(new ColumnIdentifier[] { new ColumnIdentifier(
364: new TableIdentifier("FOO"), "A") }));
365: ctx.setOrderBy(Arrays.asList(new OrderNode[] { new OrderNode(
366: new ColumnIdentifier(new TableIdentifier("FOO"), "A"),
367: true) }));
368: ctx.setHaving(new FunctionIdentifier(">", Arrays
369: .asList(new Selectable[] { countfn,
370: new Literal(new Integer(0)) })));
371: assertNotNull(ctx.toString());
372: SelectCommand cmd = new SelectCommand(ctx);
373:
374: ResultSet rset = cmd.executeQuery(_db);
375: assertTrue("Should have a row", rset.next());
376: // since resultset is ordered by dfault in descending order..
377: assertEquals("Should get value", "alaska", rset.getString(1));
378: assertEquals("Should get count 1 for alaska ", "1", rset
379: .getString(2));
380: assertTrue("Should have a row", rset.next());
381: assertEquals("Should get value", "alabama", rset.getString(1));
382: assertEquals("Should get count 2 for alabama ", "2", rset
383: .getString(2));
384:
385: // should fail
386: try {
387: ctx.setHaving(null);
388: fail("Expected Already resolved exception");
389: } catch (IllegalStateException e) {
390: // expected
391: }
392:
393: rset.close();
394:
395: }
396:
397: public void testCantChangeAfterResolved() throws Exception {
398: AxionQueryContext ctx = new AxionQueryContext();
399:
400: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
401: "A"));
402: ctx.addFrom(new TableIdentifier("FOO"));
403: SelectCommand cmd = new SelectCommand(ctx);
404: ResultSet rset = cmd.executeQuery(_db);
405: assertTrue("Should have a row", rset.next());
406: assertEquals("Should get value", "alabama", rset.getString(1));
407: assertTrue("Should have a row", rset.next());
408: assertEquals("Should get value", "alaska", rset.getString(1));
409: rset.close();
410:
411: try {
412: ctx.addSelect(null);
413: fail("Expected IllegalStateException");
414: } catch (IllegalStateException e) {
415: // expected
416: }
417:
418: try {
419: ctx.setSelect(null);
420: fail("Expected IllegalStateException");
421: } catch (IllegalStateException e) {
422: // expected
423: }
424:
425: try {
426: ctx.setFrom(null);
427: fail("Expected IllegalStateException");
428: } catch (IllegalStateException e) {
429: // expected
430: }
431:
432: try {
433: ctx.setWhere(null);
434: fail("Expected IllegalStateException");
435: } catch (IllegalStateException e) {
436: // expected
437: }
438:
439: try {
440: ctx.setOrderBy(null);
441: fail("Expected IllegalStateException");
442: } catch (IllegalStateException e) {
443: // expected
444: }
445:
446: try {
447: ctx.addOrderBy(null);
448: fail("Expected IllegalStateException");
449: } catch (IllegalStateException e) {
450: // expected
451: }
452:
453: try {
454: ctx.setDistinct(true);
455: fail("Expected IllegalStateException");
456: } catch (IllegalStateException e) {
457: // expected
458: }
459: }
460: }
|