public void testWhereTwoEqualConstraints() throws Exception { q1 = new Query("select table1.field1 from table1 where table1.field1 = 1 and table1.field2 = 2"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); Field f2 = new Field("field2", t1); Constant c1 = new Constant("1"); Constant c2 = new Constant("2"); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.addWhere(new Constraint(f1, Constraint.EQ, c1)); q2.addWhere(new Constraint(f2, Constraint.EQ, c2)); assertEquals(q2, q1); }
public void setUp() { q1 = new Query(); Table t = new Table("mytable"); Constant c = new Constant("1"); Field f = new Field("a", t); SelectValue sv = new SelectValue(f, null); q1.addFrom(t); q1.addSelect(sv); q1.addWhere(new Constraint(f, Constraint.EQ, c)); }
public void setUp() { q1 = new Query(); Table t = new Table("mytable"); Constant c = new Constant("1"); Field f = new Field("a", t); SelectValue sv = new SelectValue(f, null); q1.addFrom(t); q1.addSelect(sv); q1.addWhere(new Constraint(f, Constraint.EQ, c)); q2 = new Query(); Table t1 = new Table("mytable"); Table t2 = new Table("mytable"); Field f1 = new Field("a", t1); Field f2 = new Field("b", t2); sv = new SelectValue(f1, null); q2.addFrom(t1); q2.addFrom(t2); q2.addSelect(sv); q2.addWhere(new Constraint(f1, Constraint.EQ, c)); q2.addWhere(new Constraint(f2, Constraint.EQ, c)); }
public void setUp() { q1 = new Query(); Table t = new Table("mytable"); Constant c = new Constant("1"); Field f = new Field("a", t); SelectValue sv = new SelectValue(f, null); q1.addFrom(t); q1.addSelect(sv); q1.addWhere(new Constraint(f, Constraint.EQ, c)); q2 = new Query(); Table t1 = new Table("mytable"); Table t2 = new Table("mytable"); Field f1 = new Field("a", t1); Field f2 = new Field("b", t2); sv = new SelectValue(f1, null); q2.addFrom(t1); q2.addFrom(t2); q2.addSelect(sv); q2.addWhere(new Constraint(f1, Constraint.EQ, c)); q2.addWhere(new Constraint(f2, Constraint.EQ, c)); }
public void testWhereNottedConstraintSet() throws Exception { q1 = new Query("select table1.field1 from table1 where not (table1.field1 = table1.field2 or table1.field1 = table1.field3)"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); Field f2 = new Field("field2", t1); Field f3 = new Field("field3", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.addWhere(new NotConstraint(new Constraint(f1, Constraint.EQ, f2))); q2.addWhere(new NotConstraint(new Constraint(f1, Constraint.EQ, f3))); assertEquals(q2, q1); }
public void setUp() { q1 = new Query(); Table t = new Table("mytable"); Constant c = new Constant("1"); Field f = new Field("a", t); SelectValue sv = new SelectValue(f, null); q1.addFrom(t); q1.addSelect(sv); q1.addWhere(new Constraint(f, Constraint.EQ, c)); q2 = new Query(); Table t1 = new Table("mytable"); Table t2 = new Table("mytable2"); c = new Constant("1"); Field f1 = new Field("a", t1); Field f2 = new Field("b", t2); sv = new SelectValue(f, null); q2.addFrom(t1); q2.addFrom(t2); q2.addSelect(sv); q2.addWhere(new Constraint(f1, Constraint.EQ, c)); q2.addWhere(new Constraint(f2, Constraint.EQ, c)); }
public void testWhereFieldLessThanField() throws Exception { q1 = new Query("select table1.field1 from table1 where table1.field1 < table1.field2"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); Field f2 = new Field("field2", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.addWhere(new Constraint(f1, Constraint.LT, f2)); assertEquals(q2, q1); }
public void testWhereOneEqualConstraint() throws Exception { q1 = new Query("select table1.field1 from table1 where table1.field1 = 1"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); Constant c = new Constant("1"); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.addWhere(new Constraint(f1, Constraint.EQ, c)); assertEquals(q2, q1); }
public void testGetSQLString() throws Exception { Query q = new Query(); Table t = new Table("mytable"); Constant c = new Constant("1"); Field f = new Field("a", t); SelectValue sv = new SelectValue(f, null); q.addFrom(t); q.addSelect(sv); q.addWhere(new Constraint(f, Constraint.EQ, c)); assertEquals("SELECT mytable.a FROM mytable WHERE mytable.a = 1", q.getSQLString()); }
public void testScientificNumbers() throws Exception { Query q = new Query("SELECT table.field AS a FROM table WHERE table.field < 1.3432E-11"); Query eq = new Query(); Table t = new Table("table"); Field f = new Field("field", t); Constant c = new Constant("1.3432E-11"); SelectValue sv = new SelectValue(f, "a"); eq.addFrom(t); eq.addSelect(sv); eq.addWhere(new Constraint(f, Constraint.LT, c)); assertEquals(eq, q); }
public void setUp() throws Exception { database = DatabaseFactory.getDatabase("db.unittest"); Query q1 = new Query(); Table t = new Table("tabletest"); Constant c = new Constant("50"); Field f1 = new Field("col1", t); Field f2 = new Field("col2", t); SelectValue sv1 = new SelectValue(f1, null); SelectValue sv2 = new SelectValue(f2, null); q1.addFrom(t); q1.addSelect(sv1); q1.addSelect(sv2); q1.addWhere(new Constraint(f1, Constraint.LT, c)); q1.addOrderBy(f1); Connection con = database.getConnection(); con.setAutoCommit(false); pt1 = new PrecomputedTable(q1, q1.getSQLString(), "precompA", "test", con); con.close(); }
public void testSubQueryWhereScope() throws Exception { q1 = new Query("select t1.f1 as v1 from t1 where t1.f1 in (select t3.f1 as v3 from t3 where t3.f1 = t1.f2)"); Table t3 = new Table("t3"); Field t3f1 = new Field("f1", t3); q3 = new Query(); SelectValue sv3 = new SelectValue(t3f1, "v3"); q3.addSelect(sv3); q3.addFrom(t3); Table t1 = new Table("t1"); Field t1f2 = new Field("f2", t1); Constraint c1 = new Constraint(t3f1, Constraint.EQ, t1f2); q3.addWhere(c1); q2 = new Query(); Field t1f1 = new Field("f1", t1); SelectValue sv1 = new SelectValue(t1f1, "v1"); SubQueryConstraint c2 = new SubQueryConstraint(t1f1, q3); q2.addSelect(sv1); q2.addFrom(t1); q2.addWhere(c2); assertEquals(q2, q1); }
public void testWhereNottedFieldLessThanField() throws Exception { q1 = new Query("select table1.field1 from table1 where not table1.field1 < table1.field2"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); Field f2 = new Field("field2", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.addWhere(new NotConstraint(new Constraint(f1, Constraint.LT, f2))); assertEquals(q2, q1); }
public void testWhereFieldLessThanFunction() throws Exception { q1 = new Query("select table1.field1 from table1 where table1.field1 < avg(table1.field2)"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); Field f2 = new Field("field2", t1); Function func1 = new Function(Function.AVG); func1.add(f2); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.addWhere(new Constraint(f1, Constraint.LT, func1)); assertEquals(q2, q1); }
public void testWhereFieldLessThanPlusFunction() throws Exception { q1 = new Query("select table1.field1 from table1 where table1.field1 < table1.field2 + table1.field3"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); Field f2 = new Field("field2", t1); Field f3 = new Field("field3", t1); Function func1 = new Function(Function.PLUS); func1.add(f2); func1.add(f3); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.addWhere(new Constraint(f1, Constraint.LT, func1)); assertEquals(q2, q1); }
public void testWhereConstraintSet() throws Exception { q1 = new Query("select table1.field1 from table1 where (table1.field1 = table1.field2 or table1.field1 = table1.field3)"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); Field f2 = new Field("field2", t1); Field f3 = new Field("field3", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); ConstraintSet cs1 = new ConstraintSet(); cs1.add(new Constraint(f1, Constraint.EQ, f2)); cs1.add(new Constraint(f1, Constraint.EQ, f3)); q2.addWhere(cs1); assertEquals(q2, q1); }
public void testWhereFieldInSubQuery() throws Exception { q1 = new Query("select table1.field1 from table1 where table1.field1 in (select table2.field2 from table2)"); q2 = new Query(); q3 = new Query(); Table t1 = new Table("table1"); Table t2 = new Table("table2"); Field f1 = new Field("field1", t1); Field f2 = new Field("field2", t2); SelectValue sv1 = new SelectValue(f1, null); SelectValue sv2 = new SelectValue(f2, null); q2.addSelect(sv1); q2.addFrom(t1); q3.addSelect(sv2); q3.addFrom(t2); q2.addWhere(new SubQueryConstraint(f1, q3)); assertEquals(q2, q1); }
public void testNotSubqueryConstraint() throws Exception { q1 = new Query("select table.field from table where table.field not in (select table2.field2 from table2)"); q2 = new Query(); q3 = new Query(); Table t1 = new Table("table"); Table t2 = new Table("table2"); Field f1 = new Field("field", t1); Field f2 = new Field("field2", t2); q2.addSelect(new SelectValue(f1, null)); q2.addFrom(t1); q3.addSelect(new SelectValue(f2, null)); q3.addFrom(t2); q2.addWhere(new NotConstraint(new SubQueryConstraint(f1, q3))); assertEquals("SELECT table.field FROM table WHERE table.field NOT IN (SELECT table2.field2 FROM table2)", q1.getSQLString()); assertEquals(q2, q1); }
public void testNullStuff() throws Exception { q1 = new Query("select t.a from t where t.b is null"); q2 = new Query("select t.a from t where not t.b is not null"); q3 = new Query(); Table t1 = new Table("t"); Field f1 = new Field("a", t1); Field f2 = new Field("b", t1); q3.addSelect(new SelectValue(f1, null)); q3.addFrom(t1); q3.addWhere(new Constraint(f2, Constraint.EQ, new Constant("null"))); assertEquals("SELECT t.a FROM t WHERE t.b IS NULL", q1.getSQLString()); assertEquals("SELECT t.a FROM t WHERE t.b IS NULL", q2.getSQLString()); assertEquals("SELECT t.a FROM t WHERE t.b IS NULL", q3.getSQLString()); assertEquals(q2, q1); assertEquals(q3, q1); assertEquals(q3, q2); }
public void testNotNullStuff() throws Exception { q1 = new Query("select t.a from t where t.b is not null"); q2 = new Query("select t.a from t where not t.b is null"); q3 = new Query(); Table t1 = new Table("t"); Field f1 = new Field("a", t1); Field f2 = new Field("b", t1); q3.addSelect(new SelectValue(f1, null)); q3.addFrom(t1); q3.addWhere(new NotConstraint(new Constraint(f2, Constraint.EQ, new Constant("null")))); assertEquals("SELECT t.a FROM t WHERE t.b IS NOT NULL", q1.getSQLString()); assertEquals("SELECT t.a FROM t WHERE t.b IS NOT NULL", q2.getSQLString()); assertEquals("SELECT t.a FROM t WHERE t.b IS NOT NULL", q3.getSQLString()); assertEquals(q2, q1); assertEquals(q3, q1); assertEquals(q3, q2); }