public void testSelectNoAlias() throws Exception { q1 = new Query("select table1.field1 from table1"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); assertEquals(q2, q1); }
public void testSelectDistinct() throws Exception { q1 = new Query("select distinct table1.field1 from table1"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.setDistinct(true); assertEquals(q2, q1); }
public void testLimitOffset() throws Exception { q1 = new Query("select table1.field1 from table1 limit 100 offset 10"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.setLimitOffset(100,10); assertEquals(q2, q1); }
public void testOrderByUnaliasedFieldSingleTable() throws Exception { // if a field (not an alias) appears in order by without a table name and there's only one // table then we can set a table name q1 = new Query("SELECT field1 FROM table1 ORDER BY field1"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); q2.addSelect(new SelectValue(f1, null)); q2.addFrom(t1); q2.addOrderBy(f1); assertEquals(q2, q1); }
public void testGroupBySingleField() throws Exception { q1 = new Query("select table1.field1 from table1 group by table1.field1"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.addGroupBy(f1); assertEquals(q2, q1); }
public void testOrderByAlias() throws Exception { // referring to an alias in the order by should sort by the field q1 = new Query("SELECT field1 AS alias1 FROM table1 ORDER BY alias1"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); SelectValue sv1 = new SelectValue(f1, "alias1"); q2.addSelect(sv1); q2.addFrom(t1); q2.addOrderBy(f1); 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 testExplain() throws Exception { q1 = new Query("explain select table1.field1 from table1"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.setExplain(true); assertEquals(q2, q1); }
public void testSelectTableAlias() throws Exception { q1 = new Query("select t1.field1 from table1 as t1"); Query q1_alt = new Query("select t1.field1 from table1 t1"); q2 = new Query(); Table t1 = new Table("table1", "t1"); Field f1 = new Field("field1", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); assertEquals(q2, q1); assertEquals(q2, q1_alt); }
public void testSelectTwoSameTables() throws Exception { q1 = new Query("select t1.field1 from table1 t1, table1 t2"); q2 = new Query(); Table t1 = new Table("table1", "t1"); Table t2 = new Table("table1", "t2"); Field f1 = new Field("field1", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.addFrom(t2); assertEquals(q2, q1); }
public void testAnonymousTableFields() throws Exception { q1 = new Query("select field from table"); q2 = new Query(); Table t1 = new Table("table"); Field f1 = new Field("field", t1); q2.addSelect(new SelectValue(f1, null)); q2.addFrom(t1); assertEquals(q2, q1); assertEquals("SELECT table.field FROM table", q1.getSQLString()); }
public void testSelectFunctionAlias() throws Exception { q1 = new Query("select max(table1.field1) as alias1 from table1"); q2 = new Query(); Table t1 = new Table("table1"); Field f1 = new Field("field1", t1); Function func1 = new Function(Function.MAX); func1.add(f1); SelectValue sv1 = new SelectValue(func1, "alias1"); q2.addSelect(sv1); q2.addFrom(t1); assertEquals(q2, q1); }
public void testSelectTwoDifferentTables() throws Exception { q1 = new Query("select table1.field1 from table1, table2"); q2 = new Query(); Table t1 = new Table("table1"); Table t2 = new Table("table2"); Field f1 = new Field("field1", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.addFrom(t2); assertEquals(q2, q1); }
public void testSelectTwoDifferentTablesWithAliases() throws Exception { q1 = new Query("select t1.field1 from table1 t1, table2 t2"); q2 = new Query(); Table t1 = new Table("table1", "t1"); Table t2 = new Table("table2", "t2"); Field f1 = new Field("field1", t1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(t1); q2.addFrom(t2); assertEquals(q2, q1); }
public void testSelectFromSubQuery() throws Exception { q1 = new Query("select t1.field1 from (select table2.field2 from table2) as t1"); q2 = new Query(); SubQuery sq1 = new SubQuery(new Query("select table2.field2 from table2"), "t1"); Field f1 = new Field("field1", sq1); SelectValue sv1 = new SelectValue(f1, null); q2.addSelect(sv1); q2.addFrom(sq1); //throw new Exception(q1.getSQLString() + " " + q2.getSQLString()); 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 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 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 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); }