SalesTestCase.java #3

/*
// $Id: //guest/julian_hyde/saffron/src/examples/sales/SalesTestCase.java#3 $
// Saffron preprocessor and data engine
// Copyright (C) 2002 Julian Hyde <julian.hyde@mail.com>
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Library General Public
// License as published by the Free Software Foundation; either
// version 2 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
// Library General Public License for more details.
//
// You should have received a copy of the GNU Library General Public
// License along with this library; if not, write to the
// Free Software Foundation, Inc., 59 Temple Place - Suite 330,
// Boston, MA  02111-1307, USA.
//
// See the COPYING file located in the top-level-directory of
// the archive of this library for complete text of license.
//
// jhyde, 26 April, 2002
*/
package sales;

import junit.framework.Test;
import junit.framework.TestSuite;
import saffron.Statement;
import saffron.runtime.Iterable;
import saffron.runtime.SyntheticObject;
import saffron.test.SaffronTestCase;

import java.util.*;

/**
 * <code>SalesTestCase</code> creates a suite of tests which use the sales
 * schema.
 *
 * @author jhyde
 * @since 26 April, 2002
 * @version $Id: //guest/julian_hyde/saffron/src/examples/sales/SalesTestCase.java#3 $
 **/
public class SalesTestCase extends SaffronTestCase {
	public SalesTestCase(String s) throws Exception {
		super(s);
	}

	public static Test suite() throws Exception {
		TestSuite suite = new TestSuite();
		if (false) {
			suite.addTest(salesJdbcSuite());
		}
		suite.addTest(salesMemorySuite());
		return suite;
	}

	/**
	 * Creates a test suite which queries a database via jdbc
	 */
	public static Test salesJdbcSuite() throws Exception {
		return new TestSuite(JdbcSalesTestCase.class);
	}

	/**
	 * Creates the same suite as {@link #salesJdbcSuite}, but based upon a
	 * sales object which holds things in-memory.
	 */
	public static Test salesMemorySuite() throws Exception {
		return new TestSuite(sales.InMemorySalesTestCase.class);
	}

	private static final int empCount = 4;
	private static final int emp15Count = 3;

	// -----------------------------------------------------------------------
	// the tests

	public void testIntArray() {
		Object o = runQuery(
			"select i from new int[] {1,2,3} as i");
		assertTrue(o instanceof int[]);
		int[] a = (int[]) o;
		assertTrue(a.length == 3);
		assertTrue(a[0] == 1 && a[1] == 2 && a[2] == 3);
	}
	public void testZeroColumns() {
		// 4 rows of zero columns
		Object o = runQuery(
			"select {} from sales.emps as emp");
		assertSynthetic(o, empCount, emptyStringArray);
	}
	public void testEmptySelectList() {
		// empty select list means select the whole object
		Object o = runQuery(
			"select from sales.emps as emp");
		assertTrue(o instanceof Emp[]);
		Emp[] emps = (Emp[]) o;
		assertEquals(emps.length, empCount);
	}
	public void testInnerJoin() {
		Object o = runQuery(
			"select {} from sales.emps as emp " +
			"join sales.depts as dept on emp.deptno == dept.deptno");
		assertSynthetic(o, 3, emptyStringArray);
	}
	public void testLeftJoin() {
		Object o = runQuery(
			"select {} from sales.emps as emp " +
			"left join sales.depts as dept on emp.deptno == dept.deptno");
		assertSynthetic(o, 3, emptyStringArray);
	}
	public void testCartesianProduct() {
		Object o = runQuery(
			"select " +
			"from sales.depts as dept " +
			"join sales.emps as emp");
		assertSynthetic(o, 12, new String[] {"dept","emp"});
	}
	public void testCorrelatedFrom() {
		Object o = runQuery(
			"select " +
			"from (select from sales.depts as d " +
			"      where d.deptno == emp.deptno) as dept " +
			"join sales.emps as emp");
		assertSynthetic(o, 3, new String[] {"dept","emp"});
	}
	public void _testCorrelatedFromTwice() {
		Object o = runQuery(
			"select " +
			"from sales.emps as emp " +
			"join (select from sales.depts as d " +
			"      where d.deptno == emp.empno) as dept " +
			"join (select from sales.depts as d " +
			"      where d.deptno == emp.empno) as dept2");
		assertSynthetic(o, 12, new String[] {"emp","dept","dept2"});
	}
	public void testEmptyFrom() {
		// empty from list means 'zero relations' -- hence, one row with no
		// columns -- but the type of the output is determined by the
		// select list, hence 'int' (not '{int}')
		Object o = runQuery(
			"select 5 where 2 < 3");
		assertEqualsDeep(o, new int[] {5});
	}
	public void testEmptyFrom2() {
		Object o = runQuery(
			"select false where 2 > 3");
		// 0 rows of 0 columns
		assertTrue(o instanceof boolean[]);
		assertEqualsDeep(o, new boolean[] {});
	}
	public void testEmptyFrom3() {
		Object o = runQuery(
			"select \"hello\"");
		assertEqualsDeep(o, new String[] {"hello"});
	}
	public void testUnboxedInt() {
		Object o = runQuery(
			"select 1 from sales.emps as emp where emp.deptno > 30");
		assertTrue(o instanceof int[]);
		int[] a = (int[]) o;
		assertTrue(a.length == 1);
		assertTrue(a[0] == 1);
	}
	public void testBoxedInt() {
		Object o = runQuery(
			"select {1 as tt} from sales.emps");
		assertSynthetic(o, empCount, new String[] {"tt"});
	}
	public void _testBoxedIntSyntaxError() {
		Throwable throwable = runQueryCatch(
			"select 1 as tt from sales.emps");
		assertTrue(throwable != null);
	}
	public void testObjectPair() {
		Object o = runQuery(
			"select {emp, dept} from sales.emps as emp " +
			"join sales.depts as dept " +
			"on emp.deptno == dept.deptno " +
			"where emp.deptno > 15");
		assertSynthetic(o, 2, new String[] {"emp", "dept"});
	}
	public void testQueryInFrom() {
		Object o = runQuery(
			"select {emp, dept} from (" +
			" select emps from sales.emps " +
			" where emps.deptno == 10) as emp " +
			"join sales.depts as dept " +
			" on emp.deptno == dept.deptno");
		assertSynthetic(o, 1, new String[] {"emp", "dept"});
	}
	public void testQueryInFromWithSelectList() {
		Object o = runQuery(
			"select {emp, dept} from (" +
			" select {emp.name.substring(0,1) as initial," +
			"  emp.deptno * 2 as twiceDeptno} " +
			" from sales.emps as emp " +
			" where emp.deptno == 10) as emp " +
			"join sales.depts as dept " +
			" on emp.twiceDeptno == dept.deptno");
		assertSynthetic(o, 1, new String[] {"emp", "dept"});
	}
	public void _testExpressionWithOrderBy() {
		// todo:
	}
	public void testIn() {
		Object o = runQuery(
			"select emp from sales.emps as emp " +
			"where emp.deptno in (" +
			" select dept.deptno from sales.depts as dept " +
			" where dept.deptno > 15)");
		assertTrue(o instanceof Emp[]);
		assertEquals(((Emp[]) o).length, 2);
	}
	public void testInAgainstNullable() {
		// if we implement 'in' using an outer join and a 'null' test, we'll
		// get problems if one of the rows is actually null
		Object o = runQuery(
			"select " +
			"from new String[] {\"john\", \"paul\", null, \"jones\"} as s " +
			"where s in (" +
			" select " +
			" from new String[] {" +
			"  \"john\", \"paul\", null, \"george\", \"ringo\"})");
		assertEqualsDeep(new String[] {"john", "paul", null}, o);
	}
	public void testInAsExpression() {
		Object o = runQuery(
			"\"fred\" in new String[] {\"bill\",\"fred\"}");
		assertEquals(o, Boolean.TRUE);
	}
	public void testExistsAsExpression() {
		Object o = runQuery(
			"\"Exists: \" + (exists (" +
			"select {} from sales.emps where emps.deptno == 10" +
			") ? \"yes\" : \"no\")");
		assertEquals(o, "Exists: yes");
	}
	public void _testExists() {
		Object o = runQuery(
			"select dept from sales.depts as dept where exists (" +
			" select {} from sales.emps as emp " +
			" where emp.deptno == dept.deptno && " +
			"  emp.gender.equals(\"F\"))");
		assertTrue(o instanceof Dept[]);
		Dept[] depts = (Dept[]) o;
		assertEquals(depts.length, 1);
		assertTrue(depts[0].deptno == 20);
	}
	public void testArrayInExpr() {
		Object o = runQuery(
			"\"There are \" + (" +
			"select {} from sales.emps as emp " +
			" where emp.gender.equals(\"F\")" +
			").length + \" female employees.\"");
		assertEquals(o, "There are 1 female employees.");
	}
	public void testUnion() {
		Object o = runQuery(
			"(select emp from sales.emps as emp where emp.deptno == 10) " +
			"union " +
			"(select emp from sales.emps as emp where emp.deptno == 20)");
		assertTrue(o instanceof Emp[]);
		Emp[] emps = (Emp[]) o;
		assertTrue(emps.length == 3);
	}
	public void testIntUnion() {
		Object o = runQuery(
			"new int[] {1,3,5} union new int[] {3,2,1}");
		assertTrue(o instanceof int[]);
		int[] a = (int[]) o;
		Arrays.sort(a);
		assertEqualsDeep(a, new int[] {1,2,3,5});
	}
	public void testNoSelectListOrFrom() {
		Object o = runQuery(
			"select");
		assertSynthetic(o, 1, emptyStringArray);
	}
	public void testEmptySelectNoAlias() {
		// if there's only from item, it doesn't need an alias
		Object o = runQuery(
				"select from new String[] {\"foo\",null}");
		assertEqualsDeep(new String[] {"foo",null}, o);
	}
	public void testJoinEmptySelect() {
		// 21; empty select, 2 tables -- yields several rows
		Object o = runQuery(
			"select from sales.emps join sales.depts " +
			" on emps.deptno == depts.deptno");
		assertSynthetic(o, 3, new String[] {"emps","depts"});
	}
	public void testEmptySelectInFromQueryFails() {
		// empty select, 1 table, results in unboxed select list, so this
		// FAILS
		Throwable throwable = runQueryCatch(
			"select t.emps from (select from sales.emps) as t");
		assertThrowableContains(
			throwable, "no field emps is accessible");
	}
	public void testUnboxedSelectOnJoinFromQuery() {
		// empty select, 2 tables, results in boxed select list, so this
		// SUCCEEDS
		Object o = runQuery(
			"select t.emps from (" +
			" select from sales.emps" +
			" join sales.depts on emps.deptno == depts.deptno) as t");
		assertTrue(o instanceof Emp[]);
		assertTrue(((Emp[]) o).length == 3);
	}
	public void testUnboxedSelectOnZeroTableFromQuery() {
		// empty select, 0 tables, results in boxed select list
		Object o = runQuery(
			"select t.getClass() from (select) as t");
		assertTrue(o instanceof Class[]);
		assertTrue(((Class[]) o).length == 1);
	}
	public void _testUnaliasedBoxedSelectListFails() {
		// expression in boxed list must be aliasable, so this FAILS
		Throwable throwable = runQueryCatch(
			"select {1} from sales.emps");
		assertThrowableContains(throwable, "foo");
	}
	public void testUnaliasableUnboxed() {
		// expression in unboxed list does not need to be aliasable, so
		// this SUCCEEDS
		Object o = runQuery(
			"select 1 from sales.emps");
		assertTrue(o instanceof int[]);
		assertTrue(((int[]) o).length == empCount);
	}
	public void testEmptyFromSingletonSelect() {
		// empty from, singleton select -- yields one row {1}
		Object o = runQuery(
			"select 1 as one");
		assertEqualsDeep(o, new int[] {1});
	}
	public void testNullSelect() {
		Object o = runQuery(
			"select null from sales.depts");
		assertEqualsDeep(o, new Object[] {null, null, null});
	}
	public void testEmptyFromPairSelect() {
		// 28; empty from, pair select -- yields one row {1, 2}
		Object o = runQuery(
			"select {1 as x, null as y}");
		assertSynthetic(o, 1, new String[] {"x","y"}, new Class[] {
			int.class, Object.class});
		SyntheticObject[] a = (SyntheticObject[]) o;
		assertEquals(a.length, 1);
		assertEquals(a[0].getFieldValue(0), new Integer(1));
		assertNull(a[0].getFieldValue(1));
	}
	public void testExistsEmptyFrom() {
		// 29; empty from, with subquery
		Object o = runQuery(
			"select 1 as one where exists (select)");
		assertEqualsDeep(o, new int[] {1});
	}
	public void testExistsEmptyFromFalse() {
		// 30; empty from, with subquery
		Object o = runQuery(
			"select 1 as one where exists (select where false)");
		assertEqualsDeep(o, new int[0]);
	}
	public void testCorrelatedExistsMissingFrom() {
		// 31; empty from, with correlated subquery -- yields one row {1}
		Object o = runQuery(
			"select i from new int[] {0,3,1,2} as i " +
			"where exists (select where i > 1)");
		assertEqualsDeep(o, new int[] {3,2});
	}
	public void testReferenceEnclosingSelectItemFails() {
		// illegal reference of select item from subquery
		Throwable throwable = runQueryCatch(
			"select {1 as one, 2 as two} where exists (" +
			" select where one > 0)");
		assertThrowableContains(
			throwable, "unknown field or variable: one");
	}
	public void testCorrelatedReferToVariables2LevelsOut() {
		// 33; refer to correlating variables 2 levels out
		Object o = runQuery(
			"select from sales.emps as emp where emp.deptno in (" +
			" select dept.deptno from sales.depts as dept where exists (" +
			"  select \"xyz\" from sales.emps as emp2" +
			"  where emp2.deptno == dept.deptno &&" +
			"  !emp2.gender.equals(emp.gender)))");
		assertTrue(o instanceof Emp[]);
		Emp[] emps = (Emp[]) o;
		assertTrue(emps.length == 2);
		assertTrue(emps[0].deptno == 20);
		assertTrue(emps[1].deptno == 20);
	}

	// todo: test compatibility of types in set operations

	// todo: Emp[][] empss; select * from empss as emps where (select 1
	// from emps as emp)

	// todo: Emp[][] empss; select * from empss as emps where exists
	// (select {} from emps as emp)
	//  -- should return all arrays which have at least one element

	public void _testOnly() {
		// only, one row -- yields Emp {100, "Fred", 10}
		Object o = runQuery(
			"only (select from sales.emps as emp where emp.empno == 100)");
		assertTrue(o instanceof Emp);
	}
	public void _testOnlyZeroRows() {
		// only, no rows -- yields null
		Object o = runQuery(
			"only (select from sales.emps as emp where false)");
		assertNull(o);
	}
	public void _testOnlySeveralRowsFails() {
		// only, several rows -- throws exception
		Throwable throwable = runQueryCatch(
			"only (select from sales.emps)");
		assertThrowableContains(throwable, "foo");
	}
	public void _testOnlyInSubquery() {
		// only, in subquery -- yields Emp {110, "Eric", 20}
		Object o = runQuery(
			"select from sales.emps as emp " +
			"where emp.empno == 10 + only (" +
			" select emps.empno from sales.emps " +
			" where emps.name.equals(\"Fred\"))");
		assertTrue(o instanceof Emp);
		Emp emp = (Emp) o;
		assertEquals(emp.name, "Eric");
	}
	/**
	 * Runs a query where one of the 'scalar expressions' is a relational
	 * expression which yields an array.
	 *
	 * <p> Disabled for now. Should we translate the inner expression
	 * before or after the outer? If before, the Rel tree can get huge. If
	 * afterwards, the correlating variables the inner refers to ('dept'
	 * in this case) will no longer exist. How about <i>during</i> (in
	 * other words, {@link QueryInfo#convertExpToInternal} does it)?
	 */
	public void _testNested() {
		// column is relation
		Object o = runQuery(
			"select {" +
			" dept.deptno as deptno, " +
			" (select from sales.emps as emp " +
			"  where emp.deptno == dept.deptno) as emps} " +
			"from sales.depts as dept");
		assertTrue(o instanceof SyntheticObject[]);
		SyntheticObject[] a = (SyntheticObject[]) o;
		Object v0 = a[1].getFieldValue(0);
		assertTrue(v0 instanceof Integer);
		assertTrue(((Integer) v0).intValue() == 10);
		Object v1 = a[1].getFieldValue(1);
		assertTrue(v1 instanceof Emp[]);
		assertTrue(((Emp[]) v1).length == 2);
	}
	public void testIterator() {
		Iterator evens = new Iterator() {
				int i;
				public boolean hasNext() { return i < 20; }
				public void remove() {}
				public Object next() { return new Integer(i += 2); }
			};
		Object o = runQuery(
			"select tt " +
			"from (int[]) evens as tt " +
			"where tt < 10",
			new Statement.Argument[] {
				new Statement.Argument("evens", Iterator.class, evens)});
		assertTrue(o instanceof int[]);
		int[] a = (int[]) o;
		assertEquals(a.length, 4);
		assertEquals(a[0], 2);
		assertEquals(a[3], 8);
	}
	public void testIterable() {
		Iterable iterable = new Iterable() {
				public Iterator iterator() {
					return makeIterator(new String[] {"foo", "bar"});
				}
			};
		Object o = runQuery(
			"select string " +
			"from (String[]) iterable as string " +
			"where string.startsWith(\"b\")",
			new Statement.Argument[] {
				new Statement.Argument("iterable", Iterable.class, iterable)});
		assertTrue(o instanceof String[]);
		String[] a = (String[]) o;
		assertEquals(a.length, 1);
		assertEquals(a[0], "bar");
	}
	public void testIteratorJoin() {
		Iterator twos = new Iterator() {
				int i;
				public boolean hasNext() { return i < 100; }
				public void remove() {}
				public Object next() { return new Integer(i += 2); }
			};
		Iterator threes = new Iterator() {
				int i;
				public boolean hasNext() { return i < 100; }
				public void remove() {}
				public Object next() { return new Integer(i += 3); }
			};
		// if we put twos before threes in the from list, this wouldn't finish,
		// because twos is infinite, and it would end up as the outer loop
		Object o = runQuery(
			"select tt " +
			"from (int[]) threes as ttt " +
			"join (int[]) twos as tt " +
			"on tt == ttt",
			new Statement.Argument[] {
				new Statement.Argument("twos", Iterator.class, twos),
				new Statement.Argument("threes", Iterator.class, threes)});
		assertTrue(o instanceof int[]);
		int[] a = (int[]) o;
		assertEquals(a.length, 16);
		assertEquals(a[0], 6);
	}
	public void testIteratorJoinedToArray() {
		Iterator fours = new Iterator() {
				int i;
				public boolean hasNext() { return i < 100; }
				public void remove() {}
				public Object next() { return new Integer(i += 4); }
			};
		Object o = runQuery(
			"select {four, emp}" +
			"from (int[]) fours as four " +
			"join sales.emps as emp " +
			"on four == emp.deptno",
			new Statement.Argument[] {
				new Statement.Argument("fours", Iterator.class, fours),
				this.arguments[0]});
		assertSynthetic(o, 3, new String[] {"four", "emp"},
				new Class[] {int.class, Emp.class});
	}
	/**
	 * Disabled, because openjava can't seem to handle this anonymous class
	 * syntax.
	 */
	public void _testAnonymousIterator() {
		Iterator j = new Iterator() {
				int i;
				public boolean hasNext() { return true; }
				public void remove() {}
				public Object next() { return new Integer(i += 2); }
			};
		Object o = runQuery(
			"select twos " +
			"from (int[]) new java.util.Iterator() { " +
			"		 int i; " +
			"		 public boolean hasNext() { return true; } " +
			"		 public void remove() { } " +
			"		 public Object next() { return new Integer(i += 2); } " +
			"	 } as twos " +
			"join (int[]) new java.util.Iterator() { " +
			"		 int i; " +
			"		 public boolean hasNext() { return i < 100; } " +
			"		 public void remove() { } " +
			"		 public Object next() { return new Integer(i += 3); } " +
			"	 } as threes " +
			"on twos == threes");
		assertTrue(o instanceof int[]);
		int[] a = (int[]) o;
		assertEquals(a.length, 16);
		assertEquals(a[0], 6);
	}

	public void testOutputAsIterator() {
		Object o = runQuery(
			"(java.util.Iterator) (select from sales.emps)");
		assertTrue(o instanceof Iterator);
		assertEquals(4, toList((Iterator) o).size());
	}
	public void testOutputAsIterable() {
		Object o = runQuery(
			"(saffron.runtime.Iterable) (select from sales.emps)");
		assertTrue(o instanceof Iterable);
		assertEquals(4, toList(((Iterable) o).iterator()).size());
	}
	public void testOutputAsVector() {
		Object o = runQuery(
			"(java.util.Vector) (select from sales.emps)");
		assertTrue(o instanceof Vector);
		assertEquals(4, ((Vector) o).size());
	}
	public void testOutputAsCollection() {
		Object o = runQuery(
			"(java.util.Collection) (select from sales.emps)");
		assertTrue(o instanceof Collection);
		assertEquals(4, ((Collection) o).size());
	}
	public void testOutputAsEnumeration() {
		Object o = runQuery(
			"(java.util.Enumeration) (select from sales.emps)");
		assertTrue(o instanceof Enumeration);
		assertEquals(4, toList((Enumeration) o).size());
	}
	public void testOutputAsArray() {
		Object o = runQuery(
			"(sales.Emp[]) (select from sales.emps)");
		assertTrue(o instanceof Emp[]);
		assertEquals(4, ((Emp []) o).length);
	}
	public void _testOutputAsResultSet() {
		Object o = runQuery(
			"(java.sql.ResultSet) (select from sales.emps)");
		assertTrue(o instanceof java.sql.ResultSet);
	}
	/**
	 * Runs several test cases. Edit it for debugging purposes.
	 **/
	public void _testSeveral() {
		testCorrelatedFrom();
		testObjectPair();
		testIn();
	}

	// todo: connection which comes from rel exp, e.g. "select from (select
	// from new SalesSchema[] {sales}).emps"

	// todo: "Emp[] emps = sales.emps" (check that optimizer kicks in and
	// removes contentsAsArray)

	// todo: "for (i in sales.emps) ..." (table usage not inside a select)
}

// End SalesTestCase.java