/* Generated by Together */
/*
Copyright (C) 2003 EBI, GRL
This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 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
Lesser General Public License for more details.
You should have received a copy of the GNU Lesser 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
*/
package org.ensembl.mart.lib;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.HashMap;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Pattern;
/**
* Compiles a Query object into SQL.
*
* @author <a href="mailto:craig@ebi.ac.uk">Craig Melsopp</a>
* @author <a href="mailto:dlondon@ebi.ac.uk">Darin London</a>
* @author <a href="mailto:damian@ebi.ac.uk">Damian Smedley</a>
*/
public class QueryCompiler {
private DetailedDataSource ds;
/**
* Constructs a CompiledSQLQuery object with a specified mySQL
* database Connection, and a Query object
*
* @param conn
* @param query
* @throws SQLException
* @see Query
*/
public QueryCompiler(Query query, DetailedDataSource dsource) throws SQLException {
this.query = query;
this.ds=dsource;
}
/**
*
* @return Query query
*/
public Query getQuery() {
return query;
}
/**
* creates a String SQL statement suitable for preparing in
* a PreparedStatement using a mySQL Connection.
*
* @return String SQL - SQL to be executed
* @throws InvalidQueryException
*/
public String toSQL() throws InvalidQueryException {
// select gene_stable_id from homo_sapiens_core_gene where
// chromosome_id="3" limit 3;
if (sql == null)
compileSQL();
return sql;
}
/**
* creates a String SQL statement suitable for preparing in
* a PreparedStatement using a mySQL Connection, whereby the
* last item in the select clause is the primaryKey for the
* entire SQL query (eg, used in any join fields, or determined from
* the single table beign queried).
*
* @return String SQL - SQL to be executed
* @throws InvalidQueryException
*/
public String toSQLWithKey() throws InvalidQueryException {
// select gene_stable_id from homo_sapiens_core_gene where
// chromosome_id="3" limit 3;
if (pksql == null)
compileSQL();
return pksql;
}
/**
* creates a String SQL statement suitable for preparing in
* a PreparedStatement using a JDBC Connection, whereby the
* select clause is select count(distinct highKey) where highKey
* is the first primary key listed in the DatasetConfig XML primary keys.
* This counts all distinct focus objects returned by the filters.
*
* @return String SQL - SQL to be executed
* @throws InvalidQueryException
*/
public String toFocusCountSQL() throws InvalidQueryException {
if (fcountSQL == null)
compileFocusCountSQL();
return fcountSQL;
}
/**
* Returns the lowest level key
* @return String lowest level key used in join clauses
* @throws InvalidQueryException same as getPrimaryKey
*/
public String getLowestLevelKey() throws InvalidQueryException {
if (sql == null)
compileSQL();
return lowestLevelKey;
}
/**
* Returns the lowest level key, qualified with the mainTable.
* @return String mainTable+"."+lowestLevelKey
* @throws InvalidQueryException same as getPrimaryKey
*/
public String getQualifiedLowestLevelKey() throws InvalidQueryException {
if (sql == null)
compileSQL();
return qualifiedLowestLevelKey;
}
private void compileSQL() throws InvalidQueryException {
boolean success = false;
StringBuffer buf = new StringBuffer();
mainTable = null;
success = selectClause(buf);
if (success) {
if (logger.isLoggable(Level.FINE))
logger.fine("select clause:" + buf.toString());
success = fromClause(buf);
}
if (success) {
if (logger.isLoggable(Level.FINE))
logger.fine("select + from clauses:" + buf.toString());
success = whereClause(buf);
}
if (success && query.hasSort()) {
if (logger.isLoggable(Level.FINE))
logger.fine("select + from + where clauses:" + buf.toString());
success = sortByClause(buf);
}
if (success && logger.isLoggable(Level.FINE))
logger.fine("select + from + where clauses:" + buf.toString());
//}
if (!success)
throw new InvalidQueryException("Failed to compile query :" + query);
sql = buf.toString();
//generate pksql
StringBuffer pkbuf = new StringBuffer(sql);
pkbuf.insert(sql.indexOf(FROM), ", " + qualifiedLowestLevelKey);
pksql = pkbuf.toString();
if (logger.isLoggable(Level.FINE)) {
logger.fine("SQL: " + sql + "\n");
logger.fine("PKSQL: " + pksql + "\n");
logger.fine("MainTable: " + mainTable + "\n");
logger.fine("LowestLevelKey: " + lowestLevelKey + "\n");
}
}
private void compileFocusCountSQL() throws InvalidQueryException {
StringBuffer buf = new StringBuffer();
if (query.getFilters().length < 1) {
buf.append(SELECT).append(" count(").append("main").append(".").append(query.getPrimaryKeys()[0]).append(")").
append(FROM).append(" ").append(ds.getSchema()).
append(".").append(query.getMainTables()[0]).append(" main");
fcountSQL = buf.toString();
} else {
boolean success = false;
mainTable = null;
success = fromClause(buf);
if (success) {
success = whereClause(buf);
}
if (success && logger.isLoggable(Level.FINE))
logger.fine("from + where clauses:" + buf.toString());
if (!success)
throw new InvalidQueryException("Failed to compile query :" + query);
StringBuffer sbuf = new StringBuffer(SELECT);
sbuf.append(" count(distinct ").append("main").append(".").append(query.getPrimaryKeys()[0]).append(")").append(buf);
fcountSQL = sbuf.toString();
}
if (logger.isLoggable(Level.INFO))
logger.info("fcountSQL: " + fcountSQL + "\n");
}
/**
* @return true if all attributes in the query could be mapped to tables by
* the mapper, otherwise false.
*/
private boolean selectClause(StringBuffer buf) throws InvalidQueryException {
final int nAttributes = query.getAttributes().length;
if (nAttributes == 0)
throw new InvalidQueryException("No attributes selected.");
buf.append(SELECT).append(" ");
for (int i = 0; i < nAttributes; ++i) {
Attribute a = query.getAttributes()[i];
// do not append for aliases, postgres does not like mixing schema and aliases
// safe in oracle and mysql
if (! a.getTableConstraint().equals("main")) buf.append(ds.getSchema()).append(".");
if ( query.getDataSource().getDatabaseType().equals("sqlserver") &&
java.util.regex.Pattern.matches("[0-9]", a.getField().substring(0,1)) ) {
buf.append(a.getTableConstraint()).append(".").append(
"[").append(a.getField()).append("]");
} else
buf.append(a.getTableConstraint()).append(".").append(a.getField());
if (i + 1 < nAttributes)
buf.append(", ");
}
return true;
}
/**
* Builds array of "from" tables by looking at all the columns mentioned in
* the queries attributes and filters.
*
* @return true if all attributes and filter 'columns' in the query could
* be mapped to tables by the mapper, otherwise false.
*/
private boolean fromClause(StringBuffer buf) throws InvalidQueryException {
HashSet relevantTables = new HashSet();
joinTables = new HashMap();
String[] mainTableNames = query.getMainTables();
String[] primaryKeys = query.getPrimaryKeys();
// reverse cycle through primaryKeys
for (int k = primaryKeys.length - 1; k > -1 && (lowestLevelKey == null); k--) {
// if an attribute or filter key = this primaryKey
// then store lowestLevelKey and mainTable and break out
for (int i = 0; i < query.getAttributes().length; ++i) {
Attribute attribute = query.getAttributes()[i];
if (attribute.getKey().equals(primaryKeys[k])) {
lowestLevelKey = primaryKeys[k];
mainTable = mainTableNames[k];
StringBuffer qualBuf = new StringBuffer("main");
qualBuf.append(".").append(lowestLevelKey);
qualifiedLowestLevelKey = qualBuf.toString();
}
}
for (int i = 0; i < query.getFilters().length; ++i) {
Filter filter = query.getFilters()[i];
if (filter.getKey().equals(primaryKeys[k])) {
lowestLevelKey = primaryKeys[k];
mainTable = mainTableNames[k];
StringBuffer qualBuf = new StringBuffer("main");
qualBuf.append(".").append(lowestLevelKey);
qualifiedLowestLevelKey = qualBuf.toString();
}
}
}
// add main.lowestLevelKey to SELECT clause
//buf.append(", main.").append(lowestLevelKey);
// cycle through all filter and atts adding tableConstraint
// to fromTables where != main or already in there
for (int i = 0; i < query.getAttributes().length; ++i) {
Attribute attribute = query.getAttributes()[i];
if (!attribute.getTableConstraint().equals("main")) {
//buf.append(ds.getSchema()).append(".");
StringBuffer bfa = new StringBuffer(ds.getSchema()).append(".");
relevantTables.add(bfa.append(attribute.getTableConstraint()).toString());
//relevantTables.add(attribute.getTableConstraint());
joinTables.put(attribute.getTableConstraint(), attribute.getKey());
}
}
for (int i = 0; i < query.getFilters().length; ++i) {
Filter filter = query.getFilters()[i];
if (!filter.getTableConstraint().equals("main")) {
StringBuffer bff = new StringBuffer(ds.getSchema()).append(".");
relevantTables.add(bff.append(filter.getTableConstraint()).toString());
//relevantTables.add(bf.append(filter.getTableConstraint()));
joinTables.put(filter.getTableConstraint(), filter.getKey());
}
}
fromTables = new String[relevantTables.size()];
relevantTables.toArray(fromTables);
buf.append(FROM);
boolean from = false;
for (int i = 0; i < fromTables.length; ++i) {
if (i > 0)
buf.append(" , ");
buf.append(fromTables[i]);
from = true;
}
if (from)
buf.append(" ,");
buf.append(ds.getSchema()).append(".").append(mainTable).append(" main");
return true;
}
/**
* @return true if all filter condition 'columns' in the query could be mapped to tables by
* the mapper, otherwise false.
*/
private boolean whereClause(StringBuffer buf) throws InvalidQueryException {
final int nFilters = query.getFilters().length;
if (nFilters > 0 || fromTables.length > 0)
buf.append(WHERE);
boolean and = false;
// Add user defined filters to where clause
if (nFilters != 0) {
for (int i = 0; i < nFilters; ++i) {
Filter f = query.getFilters()[i];
if (and)
buf.append(" AND ");
// postgres does not like mixing schemas and aliases
if (! f.getTableConstraint().equals("main")) buf.append(ds.getSchema()).append(".");
if ( query.getDataSource().getDatabaseType().equals("sqlserver") &&
java.util.regex.Pattern.matches("[0-9]", f.getField().substring(0,1)) ) {
buf.append(f.getTableConstraint()).append(".").append("[").append(f.getField()).append("]").append(" ").append(
f.getRightHandClause()).append(" ");
} else
buf.append(f.getTableConstraint()).append(".").append(f.getField()).append(" ").append(
f.getRightHandClause()).append(" ");
//System.out.println("RIGHT HAND CLAUSE "+f.getRightHandClause());
and = true;
}
}
// Add joins to where clause
Set tables = joinTables.entrySet();
Iterator iterator = tables.iterator();
while (iterator.hasNext()) {
Map.Entry mapentry = (Map.Entry) iterator.next();
if (and)
buf.append(" AND ");
and = true;
//do not mix schema and aliases, postgres does not like it
buf.append("main.").append(mapentry.getValue()).append("=").append(ds.getSchema()).append(".").append(mapentry.getKey()).append(".").append(
mapentry.getValue());
}
return true;
}
private boolean sortByClause(StringBuffer buf) throws InvalidQueryException {
final int nAttributes = query.getSortByAttributes().length;
buf.append(SORTBY).append(" ");
for (int i = 0; i < nAttributes; ++i) {
Attribute a = query.getSortByAttributes()[i];
//hack for AE
lowestLevelKey = a.getField();
qualifiedLowestLevelKey = a.getTableConstraint()+"."+lowestLevelKey;
if ( query.getDataSource().getDatabaseType().equals("sqlserver") &&
java.util.regex.Pattern.matches("[0-9]", a.getField().substring(0,1)) ) {
buf.append(a.getTableConstraint()).append(".").append("[").append(a.getField()).append("]");
} else
buf.append(a.getTableConstraint()).append(".").append(a.getField());
if (i + 1 < nAttributes)
buf.append(", ");
}
return true;
}
// private String[] toStringArray(List list) {
// return (String[]) list.toArray(new String[list.size()]);
// }
private String sql = null;
private String pksql = null;
private String fcountSQL = null;
private Query query = null;
private Logger logger = Logger.getLogger(QueryCompiler.class.getName());
private String mainTable = null; // either the _main table, or the single dimension table when that is chosen
//private String primaryKey = null; // whichever primary_key supplied by the query is used in the SQL
private String lowestLevelKey = null; //
private String qualifiedLowestLevelKey = null; //
//private String qualifiedPrimaryKey = null; // mainTable + "." + primaryKey
private String[] fromTables = null;
private HashMap joinTables = null;
//private FieldMapper[] mappers = null;
//SQL keywords
private final String SELECT = "SELECT ";
private final String FROM = " FROM ";
private final String WHERE = " WHERE ";
private final String SORTBY = " ORDER BY ";
}