Query precompQuery = precomputedTable.getQuery(); Set<Query> retval = new LinkedHashSet<Query>(); if (precompQuery.getGroupBy().size() != query.getGroupBy().size()) { if (precompQuery.getFrom().size() != query.getFrom().size()) { .getFrom(), query.getFrom(), new AbstractTableComparator()); remapAliases(mapping, originalQuery.getFrom()); if (!precompQuery.getWhere().equals(query.getWhere())) { continue; if (!precompQuery.getGroupBy().equals(query.getGroupBy())) { continue; if (!compareConstraints(precompQuery.getHaving(), query.getHaving(), constraintEqualsSet)) { continue; if (precompQuery.isDistinct() && (!query.isDistinct())) { continue; Query newQuery = new Query(); reconstructSelectValues(query.getSelect(), precomputedSqlTable, valueMap, precompQuery.getFrom(), true, newQuery); newQuery.addFrom(precomputedSqlTable);
/** * Processes an AST node that describes a GROUP clause. * * @param node an AST node to process */ public void processGroupClause(AST node) { AST ast = node; do { addGroupBy(processNewAbstractValue(ast)); ast = ast.getNextSibling(); } while (ast != null); }
/** * Processes an AST node that describes a ORDER clause. * * @param node an AST node to process */ public void processOrderClause(AST node) { AST ast = node; do { addOrderBy(processNewAbstractValue(ast)); ast = ast.getNextSibling(); } while (ast != null); }
/** * Returns a String representation of this SubQueryConstraint object, suitable for forming part * of an SQL Query. * * @return the String representation */ @Override public String getSQLString() { if (right.getSelect().size() != 1) { throw (new IllegalStateException("Right must have one result column only")); } return left.getSQLString() + " IN (" + right.getSQLString() + ")"; }
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 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 testQuiteComplexQuery() throws Exception { q1 = new Query("select t1.field1 as first, table2.field1, count(*) as c, max(t1.field2) as mx from table1 t1, table2, (select table3.field2 as f1 from table3) as t3 where t1.field3 = table2.field1 and t3.f1 = table2.field3 and (t1.field4 = table2.field2 or t1.field4 = table2.field3) group by t1.field1, table2.field1 having (t1.field1 = table2.field2 or t1.field1 = table2.field3) order by t1.field1, table2.field1 limit 100 offset 10"); q2 = new Query(); Table t1 = new Table("table1", "t1"); Table t2 = new Table("table2"); q3 = new Query(); q3.addSelect(new SelectValue(t3f2, "f1")); q3.addFrom(t3); q2.addSelect(sv1); q2.addSelect(sv2); q2.addSelect(sv3); q2.addSelect(sv4); q2.addFrom(t1); q2.addFrom(t2); q2.addFrom(sq1); q2.addWhere(new Constraint(t1f3, Constraint.EQ, t2f1)); q2.addWhere(new Constraint(t2f3, Constraint.EQ, t3f1)); q2.addWhere(cs1); q2.addGroupBy(t1f1); q2.addGroupBy(t2f1); q2.addHaving(cs2); q2.addOrderBy(t1f1); q2.addOrderBy(t2f1);
public void testUnion() throws Exception { q1 = new Query("select table.field from table union 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.addToUnion(q3); Query q4 = new Query("select table2.field2 from table2 union select table.field from table"); Query q5 = new Query("select table.field from table union select table.field from table"); Query q6 = new Query("select table.field from table"); Query q7 = new Query("select table.field from table union select table.field from table union select table2.field2 from table2"); assertEquals(q2, q1); assertEquals(q2.hashCode(), q1.hashCode()); assertEquals("SELECT table.field FROM table UNION SELECT table2.field2 FROM table2", q1.getSQLString()); assertEquals(q1, q4); assertEquals(q1.hashCode(), q4.hashCode()); assertTrue("Expected q1 and q5 to be not equal.", !q1.equals(q5)); assertTrue("Expected q1.hashCode to not equal q5.hashCode.", q1.hashCode() != q5.hashCode()); assertTrue("Expected q1 and q6 to be not equal.", !q1.equals(q6)); assertTrue("Expected q1.hashCode to not equal q6.hashCode.", q1.hashCode() != q6.hashCode()); assertTrue("Expected q1 and q7 to be not equal.", !q1.equals(q7)); assertTrue("Expected q1.hashCode to not equal q7.hashCode.", q1.hashCode() != q7.hashCode()); }
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 testMergeGroupByFits() throws Exception { Query q1 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b, count(*) as stuff from table as table1, somethingelse as table2 WHERE table1.c = table2.a GROUP BY table1.a, table1.b, table1.d HAVING table1.d = 'five' ORDER BY table1.a LIMIT 100 OFFSET 0"); Query pq1 = new Query("SELECT table3.a AS sahjg, table3.b AS aytq, count(*) AS hksf, table3.d AS fdjsa FROM table AS table3, somethingelse AS table4 WHERE table3.c = table4.a GROUP BY table3.a, table3.b, table3.d"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); Query eq1 = new Query("SELECT P42.sahjg AS t1_a, P42.aytq AS t1_b, P42.hksf AS stuff from precomp1 AS P42 WHERE P42.fdjsa = 'five' ORDER BY P42.sahjg LIMIT 100 OFFSET 0"); Set eSet = new HashSet(); eSet.add(eq1); StringUtil.setNextUniqueNumber(42); Set newSet = QueryOptimiser.mergeGroupBy(pt1, q1, q1); assertEquals(eSet, newSet); }
Field orderByField = null; List<AbstractValue> tempOrderBy = new ArrayList<AbstractValue>(); reconstructAbstractValues(currentQuery.getOrderBy(), precomputedSqlTable, valueMap, precompQuery.getFrom(), false, tempOrderBy); List<AbstractValue> newOrderBy = newQuery.getOrderBy(); Iterator<AbstractValue> orderByIter = tempOrderBy.iterator(); if (orderByIter.hasNext()) { if ((matches || matchesDesc) && currentQuery.isDistinct()) { Set<AbstractValue> newS = new LinkedHashSet<AbstractValue>(); for (SelectValue newSelectSV : newQuery.getSelect()) { newS.add(newSelectSV.getValue()); if ((orderByField != null) && currentQuery.isDistinct()) { newQuery.addSelect(new SelectValue(orderByField, "orderby_field_from_pt"));
for (SelectValue value : q.getSelect()) { valueMap.put(value.getValue(), value); boolean useOrderByField = (q.getOrderBy().size() > 1) && (q.getUnion().size() == 1); try { if (useOrderByField) { for (AbstractValue column : q.getOrderBy()) { if (column instanceof OrderDescending) { column = ((OrderDescending) column).getValue(); for (AbstractValue column : q.getOrderBy()) { if (column instanceof OrderDescending) { column = ((OrderDescending) column).getValue(); List<AbstractValue> orderBy = q.getOrderBy(); StringBuffer extraBuffer = new StringBuffer(); for (int i = 0; i < orderBy.size(); i++) { generationSqlString = q.getSQLStringForPrecomputedTable(extraBuffer.toString()); } else { orderByField = null; generationSqlString = q.getSQLString();
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 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); }
/** * Processes an AST node that describes a HAVING condition. * * @param node an AST node to process */ private void processHavingClause(AST node) { AST ast = node; do { switch (ast.getType()) { case SqlTokenTypes.AND_CONSTRAINT_SET: // Recurse - it's an AND set, equivalent to a HAVING_CLAUSE processHavingClause(ast.getFirstChild()); break; case SqlTokenTypes.OR_CONSTRAINT_SET: case SqlTokenTypes.CONSTRAINT: case SqlTokenTypes.NOT_CONSTRAINT: case SqlTokenTypes.SUBQUERY_CONSTRAINT: case SqlTokenTypes.INLIST_CONSTRAINT: case SqlTokenTypes.NULL_CONSTRAINT: addHaving(processNewAbstractConstraint(ast)); break; default: throw (new IllegalArgumentException("Unknown AST node: " + ast.getText() + " [" + ast.getType() + "]")); } ast = ast.getNextSibling(); } while (ast != null); }
/** * Compare this SubQuery to another AbstractTable, ignoring alias. * * @param obj an AbstractTable to compare to * @return true if the object is of the same class, and with an equal query */ @Override public boolean equalsIgnoreAlias(AbstractTable obj) { if (obj instanceof SubQuery) { return query.equals(((SubQuery) obj).query); } return false; } }
public void testTreeParserRulesForConstraint() throws Exception { // (aleft != aleft) becomes NOT (aleft = aright) Query lq1 = new Query("select t.a from t where t.a != t.b"); Query lq2 = new Query("select t.a from t where not t.a = t.b"); assertEquals(lq1, lq2); Query lq3 = new Query("select t.a from t where t.a = t.b"); assertTrue("Expected lq1 to not equal lq3", !lq1.equals(lq3)); // (bleft >= bright) becomes NOT (bleft < bright) lq1 = new Query("select t.a from t where t.a >= t.b"); lq2 = new Query("select t.a from t where not t.a < t.b"); assertEquals(lq1, lq2); // (cleft <= cright) becomes NOT (cright < cleft) lq1 = new Query("select t.a from t where t.a <= t.b"); lq2 = new Query("select t.a from t where not t.a > t.b"); assertEquals(lq1, lq2); // (dleft > dright) becomes (dright < dleft) lq1 = new Query("select t.a from t where t.a < t.b"); lq2 = new Query("select t.a from t where t.b > t.a"); assertEquals(lq1, lq2); }
/** * Constructs an instance of PostgresExplainResult for a given Query and * database Connection. * * @param query the org.intermine.sql.query.Query to be explained * @param database a java.sql.Connection by which to access the database * @throws SQLException if a database error occurs * @throws NullPointerException if either query or database are null */ public PostgresExplainResult(Query query, Connection database) throws SQLException { this(query.getSQLString(), database); }