/** * Create temporary tables for the bag in the BagConstraints of the given Query, then call * SqlGenerator.generate(). * * @param c a Connection to use * @param q the Query * @param start the start row number (inclusive, from zero) * @param limit maximum number of rows to return * @return the SQL for the Query * @throws ObjectStoreException if an error occurs */ protected String generateSql(Connection c, Query q, int start, int limit) throws ObjectStoreException { createTempBagTablesForQuery(c, q); return SqlGenerator.generate(q, start, limit, schema, db, bagConstraintTables); }
/** * Generate SQL only for counting; no need to order by and no need to calculate LIMIT and OFFSET * Create temporary tables for the bag in the BagConstraints of the given Query, then call * directly SqlGenerator.generate() using as Query type the value QUERY_FOR_COUNTING * * @param c a Connection to use * @param q the Query * @return the SQL for the Query * @throws ObjectStoreException if an error occurs */ protected String generateSqlForCount(Connection c, Query q) throws ObjectStoreException { createTempBagTablesForQuery(c, q); return SqlGenerator.generate(q, schema, db, null, SqlGenerator.QUERY_FOR_COUNTING, bagConstraintTables); }
/** * Create temporary tables for the bag in the BagConstraints of the given Query, then call * SqlGenerator.generate(). * * @param c a Connection to use * @param q the Query * @param start the start row number (inclusive, from zero) * @param limit maximum number of rows to return * @return the SQL for the Query * @throws ObjectStoreException if an error occurs */ protected String generateSql(Connection c, Query q, int start, int limit) throws ObjectStoreException { createTempBagTablesForQuery(c, q); return SqlGenerator.generate(q, start, limit, schema, db, bagConstraintTables); }
/** * Generate SQL only for counting; no need to order by and no need to calculate LIMIT and OFFSET * Create temporary tables for the bag in the BagConstraints of the given Query, then call * directly SqlGenerator.generate() using as Query type the value QUERY_FOR_COUNTING * * @param c a Connection to use * @param q the Query * @return the SQL for the Query * @throws ObjectStoreException if an error occurs */ protected String generateSqlForCount(Connection c, Query q) throws ObjectStoreException { createTempBagTablesForQuery(c, q); return SqlGenerator.generate(q, schema, db, null, SqlGenerator.QUERY_FOR_COUNTING, bagConstraintTables); }
Query q = MainHelper.makeQuery(templateQuery, new HashMap<String, InterMineBag>(), new HashMap<String, QuerySelectable>(), bqr, null); String sqlString = SqlGenerator.generate(q, 0, Integer.MAX_VALUE, ((ObjectStoreInterMineImpl) productionOs).getSchema(), ((ObjectStoreInterMineImpl) productionOs).getDatabase(),
/** * Converts a SubqueryExistsConstraint object into a String suitable for putting in an SQL * query. * * @param state the current SqlGenerator state * @param buffer the StringBuffer to place text into * @param c the SubqueryExistsConstraint object * @param schema the DatabaseSchema in which to look up metadata * @throws ObjectStoreException if something goes wrong */ protected static void subqueryExistsConstraintToString(State state, StringBuffer buffer, SubqueryExistsConstraint c, DatabaseSchema schema) throws ObjectStoreException { Query subQ = c.getQuery(); buffer.append((c.getOp() == ConstraintOp.EXISTS ? "EXISTS(" : "(NOT EXISTS(") + generate(subQ, schema, state.getDb(), null, QUERY_SUBQUERY_EXISTS, state.getBagTableNames()) + (c.getOp() == ConstraintOp.EXISTS ? ")" : "))")); }
/** * Converts a SubqueryExistsConstraint object into a String suitable for putting in an SQL * query. * * @param state the current SqlGenerator state * @param buffer the StringBuffer to place text into * @param c the SubqueryExistsConstraint object * @param schema the DatabaseSchema in which to look up metadata * @throws ObjectStoreException if something goes wrong */ protected static void subqueryExistsConstraintToString(State state, StringBuffer buffer, SubqueryExistsConstraint c, DatabaseSchema schema) throws ObjectStoreException { Query subQ = c.getQuery(); buffer.append((c.getOp() == ConstraintOp.EXISTS ? "EXISTS(" : "(NOT EXISTS(") + generate(subQ, schema, state.getDb(), null, QUERY_SUBQUERY_EXISTS, state.getBagTableNames()) + (c.getOp() == ConstraintOp.EXISTS ? ")" : "))")); }
public void testForPrecomp() throws Exception { DatabaseSchema schema = getSchema(); Query q = (Query) queries.get("SelectSimpleObject"); assertEquals("SQL incorrect.", precompTableString(), SqlGenerator.generate(q, schema, db, null, SqlGenerator.QUERY_FOR_PRECOMP, Collections.EMPTY_MAP)); }
public void testOverlapQueries() throws Exception { DatabaseSchema schema = getSchema(); Query q1 = rangeDoesNotOverlap(); String generated1 = SqlGenerator.generate(q1, 0, Integer.MAX_VALUE, getSchema(), db, new HashMap()); // 1. We can use Postgres built in range types Query q = rangeOverlaps(); String generated = SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, new HashMap()); String expected = getOverlapQuery("int4range", "RangeOverlaps"); assertEquals(expected, generated); q = rangeDoesNotOverlap(); generated = SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, new HashMap()); expected = getOverlapQuery("int4range", "RangeDoesNotOverlap"); assertEquals(expected, generated); q = rangeOverlapsValues(); generated = SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, new HashMap()); expected = getOverlapQuery("int4range", "RangeOverlapsValues"); assertEquals(expected, generated); }
q.addFrom(c1); q.addToSelect(c1); assertEquals("SQL incorrect.", getRegisterOffset1(), SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, new HashMap())); SqlGenerator.registerOffset(q, 5, schema, db, new Integer(10), new HashMap()); assertEquals(getRegisterOffset1(), SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 10 ORDER BY a1_.id OFFSET 5", SqlGenerator.generate(q, 10, Integer.MAX_VALUE, schema, db, new HashMap())); SqlGenerator.registerOffset(q, 11000, schema, db, new Integer(20), new HashMap()); assertEquals(getRegisterOffset1(), SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 10 ORDER BY a1_.id OFFSET 5", SqlGenerator.generate(q, 10, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 20 ORDER BY a1_.id OFFSET 5", SqlGenerator.generate(q, 11005, Integer.MAX_VALUE, schema, db, new HashMap())); SqlGenerator.registerOffset(q, 21000, schema, db, new Integer(30), new HashMap()); assertEquals(getRegisterOffset1(), SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 10 ORDER BY a1_.id OFFSET 5", SqlGenerator.generate(q, 10, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 10 ORDER BY a1_.id OFFSET 11000", SqlGenerator.generate(q, 11005, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 30 ORDER BY a1_.id OFFSET 5", SqlGenerator.generate(q, 21005, Integer.MAX_VALUE, schema, db, new HashMap())); SqlGenerator.registerOffset(q, 21005, schema, db, new Integer(31), new HashMap()); assertEquals(getRegisterOffset1(), SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 10 ORDER BY a1_.id OFFSET 5", SqlGenerator.generate(q, 10, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 10 ORDER BY a1_.id OFFSET 11000", SqlGenerator.generate(q, 11005, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 30 ORDER BY a1_.id OFFSET 5", SqlGenerator.generate(q, 21005, Integer.MAX_VALUE, schema, db, new HashMap())); SqlGenerator.registerOffset(q, 11002, schema, db, new Integer(29), new HashMap()); assertEquals(getRegisterOffset1(), SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 10 ORDER BY a1_.id OFFSET 5", SqlGenerator.generate(q, 10, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 10 ORDER BY a1_.id OFFSET 11000", SqlGenerator.generate(q, 11005, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 30 ORDER BY a1_.id OFFSET 5", SqlGenerator.generate(q, 21005, Integer.MAX_VALUE, schema, db, new HashMap())); SqlGenerator.registerOffset(q, 101000, schema, db, new Integer(40), new HashMap()); assertEquals(getRegisterOffset1(), SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 10 ORDER BY a1_.id OFFSET 5", SqlGenerator.generate(q, 10, Integer.MAX_VALUE, schema, db, new HashMap())); assertEquals(getRegisterOffset2() + "a1_.id > 10 ORDER BY a1_.id OFFSET 11000", SqlGenerator.generate(q, 11005, Integer.MAX_VALUE, schema, db, new HashMap()));
/** * Converts a SubqueryConstraint object into a String suitable for putting in an SQL query. * * @param state the current SqlGenerator state * @param buffer the StringBuffer to place text into * @param c the SubqueryConstraint object * @param q the Query * @param schema the DatabaseSchema in which to look up metadata * @throws ObjectStoreException if something goes wrong */ protected static void subqueryConstraintToString(State state, StringBuffer buffer, SubqueryConstraint c, Query q, DatabaseSchema schema) throws ObjectStoreException { Query subQ = c.getQuery(); QueryEvaluable qe = c.getQueryEvaluable(); QueryClass cls = c.getQueryClass(); if (qe != null) { queryEvaluableToString(buffer, qe, q, state); } else { queryClassToString(buffer, cls, q, schema, QUERY_SUBQUERY_CONSTRAINT, state); } buffer.append(" " + c.getOp().toString() + " (" + generate(subQ, schema, state.getDb(), null, QUERY_SUBQUERY_CONSTRAINT, state.getBagTableNames()) + ")"); }
/** * Converts a SubqueryConstraint object into a String suitable for putting in an SQL query. * * @param state the current SqlGenerator state * @param buffer the StringBuffer to place text into * @param c the SubqueryConstraint object * @param q the Query * @param schema the DatabaseSchema in which to look up metadata * @throws ObjectStoreException if something goes wrong */ protected static void subqueryConstraintToString(State state, StringBuffer buffer, SubqueryConstraint c, Query q, DatabaseSchema schema) throws ObjectStoreException { Query subQ = c.getQuery(); QueryEvaluable qe = c.getQueryEvaluable(); QueryClass cls = c.getQueryClass(); if (qe != null) { queryEvaluableToString(buffer, qe, q, state); } else { queryClassToString(buffer, cls, q, schema, QUERY_SUBQUERY_CONSTRAINT, state); } buffer.append(" " + c.getOp().toString() + " (" + generate(subQ, schema, state.getDb(), null, QUERY_SUBQUERY_CONSTRAINT, state.getBagTableNames()) + ")"); }
public void testInvalidClassConstraint() throws Exception { Query q = new Query(); QueryClass qc = new QueryClass(Company.class); q.addFrom(qc); q.addToSelect(qc); Company c = (Company) DynamicUtil.createObject(Collections.singleton(Company.class)); q.setConstraint(new ClassConstraint(qc, ConstraintOp.EQUALS, c)); try { SqlGenerator.generate(q, 0, Integer.MAX_VALUE, getSchema(), db, new HashMap()); fail("Expected: ObjectStoreException"); } catch (ObjectStoreException e) { assertEquals("ClassConstraint cannot contain an InterMineObject without an ID set", e.getMessage()); } }
public void testInvalidClass() throws Exception { Query q = new Query(); QueryClass c1 = new QueryClass(Company.class); q.addFrom(c1); q.addToSelect(c1); DatabaseSchema s = new DatabaseSchema(new Model("nothing", "", new HashSet()), Collections.EMPTY_LIST, false, Collections.EMPTY_SET, 1); try { SqlGenerator.generate(q, 0, Integer.MAX_VALUE, s, db, new HashMap()); fail("Expected: ObjectStoreException"); } catch (ObjectStoreException e) { assertEquals("interface org.intermine.model.testmodel.Company is not in the model", e.getMessage()); } try { SqlGenerator.findTableNames(q, s, false); fail("Expected: ObjectStoreException"); } catch (ObjectStoreException e) { assertEquals("interface org.intermine.model.testmodel.Company is not in the model", e.getMessage()); } }
public void testRegisterOffset4() throws Exception { DatabaseSchema schema = getSchema(); Query q = new Query(); QueryClass qc = new QueryClass(Employee.class); q.addFrom(qc); QueryField f = new QueryField(qc, "name"); q.addToSelect(f); q.addToOrderBy(new OrderDescending(f)); assertEquals("SELECT DISTINCT a1_.name AS a2_ FROM " + getRegisterOffset3() + " ORDER BY a1_.name DESC", SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, Collections.EMPTY_MAP)); SqlGenerator.registerOffset(q, 5, schema, db, "flibble", Collections.EMPTY_MAP); assertEquals("SELECT DISTINCT a1_.name AS a2_ FROM " + getRegisterOffset3() + " " + getRegisterOffset4() + " a1_.name < 'flibble' ORDER BY a1_.name DESC OFFSET 5", SqlGenerator.generate(q, 10, Integer.MAX_VALUE, schema, db, Collections.EMPTY_MAP)); }
public void testRegisterOffset2() throws Exception { DatabaseSchema schema = getSchema(); Query q = new Query(); QueryClass qc = new QueryClass(Employee.class); q.addFrom(qc); QueryField f = new QueryField(qc, "name"); q.addToSelect(f); assertEquals("SELECT DISTINCT a1_.name AS a2_ FROM " + getRegisterOffset3() + " ORDER BY a1_.name", SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, Collections.EMPTY_MAP)); SqlGenerator.registerOffset(q, 5, schema, db, "flibble", Collections.EMPTY_MAP); String expected = "SELECT DISTINCT a1_.name AS a2_ FROM " + getRegisterOffset3() + " " + getRegisterOffset4() + " (a1_.name > 'flibble' OR a1_.name IS NULL) ORDER BY a1_.name OFFSET 5"; assertEquals(expected, SqlGenerator.generate(q, 10, Integer.MAX_VALUE, schema, db, Collections.EMPTY_MAP)); q = new Query(); qc = new QueryClass(Employee.class); q.addFrom(qc); f = new QueryField(qc, "age"); q.addToSelect(f); assertEquals("SELECT DISTINCT a1_.age AS a2_ FROM " + getRegisterOffset3() + " ORDER BY a1_.age", SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, Collections.EMPTY_MAP)); SqlGenerator.registerOffset(q, 5, schema, db, new Integer(34), Collections.EMPTY_MAP); assertEquals("SELECT DISTINCT a1_.age AS a2_ FROM " + getRegisterOffset3() + " " + getRegisterOffset4() + " a1_.age > 34 ORDER BY a1_.age OFFSET 5", SqlGenerator.generate(q, 10, Integer.MAX_VALUE, schema, db, Collections.EMPTY_MAP)); }
public void testRegisterOffset3() throws Exception { DatabaseSchema schema = getSchema(); Query q = new Query(); QueryClass qc = new QueryClass(Employee.class); q.addFrom(qc); QueryField f = new QueryField(qc, "name"); q.addToSelect(f); SimpleConstraint sc = new SimpleConstraint(f, ConstraintOp.IS_NOT_NULL); q.setConstraint(sc); assertEquals("SELECT DISTINCT a1_.name AS a2_ FROM " + getRegisterOffset3() + " " + getRegisterOffset4() + " a1_.name IS NOT NULL ORDER BY a1_.name", SqlGenerator.generate(q, 0, Integer.MAX_VALUE, schema, db, Collections.EMPTY_MAP)); SqlGenerator.registerOffset(q, 5, schema, db, "flibble", Collections.EMPTY_MAP); assertEquals("SELECT DISTINCT a1_.name AS a2_ FROM " + getRegisterOffset3() + " " + getRegisterOffset4() + " a1_.name IS NOT NULL AND a1_.name > 'flibble' ORDER BY a1_.name OFFSET 5", SqlGenerator.generate(q, 10, Integer.MAX_VALUE, schema, db, Collections.EMPTY_MAP)); }
public void testInvalidFromElement() throws Exception { Query q = new Query(); FromElement fe = new FromElement() {}; q.addFrom(fe); QueryClass qc = new QueryClass(Company.class); q.addFrom(qc); q.addToSelect(qc); try { SqlGenerator.generate(q, 0, Integer.MAX_VALUE, getSchema(), db, new HashMap()); fail("Expected: ObjectStoreException"); } catch (ObjectStoreException e) { assertTrue(e.getMessage().startsWith("Unknown FromElement: ")); } try { SqlGenerator.findTableNames(q, getSchema(), false); fail("Expected: ObjectStoreException"); } catch (ObjectStoreException e) { assertTrue(e.getMessage().startsWith("Unknown FromElement: ")); } }
public void testInvalidConstraintType() throws Exception { Query q = new Query(); QueryClass qc = new QueryClass(Company.class); q.addFrom(qc); q.addToSelect(qc); q.setConstraint(new Constraint() {}); try { SqlGenerator.generate(q, 0, Integer.MAX_VALUE, getSchema(), db, new HashMap()); fail("Expected: ObjectStoreException"); } catch (ObjectStoreException e) { assertTrue(e.getMessage(), e.getMessage().startsWith("Unrecognised object ")); } try { SqlGenerator.findTableNames(q, getSchema(), false); fail("Expected: ObjectStoreException"); } catch (ObjectStoreException e) { assertTrue(e.getMessage(), e.getMessage().startsWith("Unknown constraint ")); } }
public void testInvalidClassInContainsConstraint() throws Exception { Employee emp = new Employee() { private Set extras; public Set getExtras() { return extras; } public void setExtras(Set extras) { this.extras = extras; } public void addExtras(Employee e) { extras.add(e); } }; Query q = new Query(); QueryClass qc = new QueryClass(Employee.class); q.addFrom(qc); q.addToSelect(qc); q.setConstraint(new ContainsConstraint(new QueryCollectionReference(emp, "extras"), ConstraintOp.CONTAINS, qc)); try { SqlGenerator.generate(q, 0, Integer.MAX_VALUE, getSchema(), db, new HashMap()); fail("Expected: ObjectStoreException"); } catch (ObjectStoreException e) { assertEquals("Reference ?.extras is not in the model - fields available in class org.intermine.objectstore.intermine.SqlGeneratorTest$3 are [fullTime, age, end, department, departmentThatRejectedMe, employmentPeriod, simpleObjects, address, id, name]", e.getMessage()); } }