/*
$Header: /cvsroot/xorm/xorm/src/org/xorm/datastore/sql/SQLQuery.java,v 1.44 2004/05/19 20:00:32 wbiggs Exp $
This file is part of XORM.
XORM is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
XORM 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 General Public License for more details.
You should have received a copy of the GNU General Public License
along with XORM; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
package org.xorm.datastore.sql;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
import org.xorm.datastore.Table;
import org.xorm.datastore.Column;
import org.xorm.query.Operator;
import org.xorm.query.Condition;
import org.xorm.query.SimpleCondition;
import org.xorm.query.CompoundCondition;
import org.xorm.query.AndCondition;
import org.xorm.query.OrCondition;
import org.xorm.query.RawCondition;
import org.xorm.query.Selector;
import org.xorm.ObjectId;
/**
* Converts a selector tree to a SQL statement.
*/
public class SQLQuery {
public static class Alias {
private String name;
private Table table;
private Column joinColumn;
private Set fetchColumns;
public Alias(String name, Table table, Set fetchColumns, Column joinColumn) {
this.name = name;
this.table = table;
this.fetchColumns = fetchColumns;
this.joinColumn = joinColumn;
}
public String getName() { return name; }
public Table getTable() { return table; }
public Column getJoinColumn() { return joinColumn; }
public Set getFetchColumns() {
return (fetchColumns == null) ? Collections.EMPTY_SET : fetchColumns;
}
}
private Selector selector;
private int aliasIndex = 0;
private Set aliases = new LinkedHashSet();
private StringBuffer fromClause = new StringBuffer();
private StringBuffer whereClause = new StringBuffer();
private StringBuffer orderingClause = new StringBuffer();
private boolean distinct;
private List parameters = new ArrayList();
public SQLQuery(Selector selector) {
this.selector = selector;
Table table = selector.getTable();
Alias alias = makeAlias(selector);
parse(alias, selector);
}
public Table getTargetTable() {
return selector.getTable();
}
public Set getAliases() {
return aliases;
}
public List getParameters() {
return parameters;
}
private Alias makeAlias(Selector selector) {
String aliasName;
if (selector.getCondition() instanceof RawCondition) {
aliasName = selector.getTable().getName();
} else {
aliasName = "T" + aliasIndex++;
}
Alias alias = new Alias(aliasName, selector.getTable(), selector.getFetchColumns(), selector.getJoinColumn());
aliases.add(alias);
return alias;
}
private void parse(Alias alias, Selector selector) {
if (!selector.isOuterJoin()) {
if (fromClause.length() > 0) {
fromClause.append(", ");
}
fromClause.append(alias.getTable().getName())
.append(" ")
.append(alias.getName());
Condition condition = selector.getCondition();
if (condition != null) {
// Having to explicitly check this here is a bit kludgy
boolean outerJoin = false;
if (condition instanceof SimpleCondition) {
Object operand = ((SimpleCondition) condition).getValue();
if (operand instanceof Selector) {
Selector next = (Selector) operand;
outerJoin = next.isOuterJoin();
}
}
if (!outerJoin && (whereClause.length() > 0)) {
whereClause.append(" AND ");
}
parseBranch(alias, condition);
}
}
parseOrdering(selector);
}
/**
* Parses the inverted status of the condition and then passes
* control to parse(CompoundCondition) or
* parseSimple(Condition).
*
* @return true if the last clause parsed required an outer join
*/
private boolean parseBranch(Alias alias, Condition condition) {
/*
The situation where condition == null arises when the
ordering clause attempts to traverse relationships that
are not otherwise part of the query. In this case we treat
this as on outer join. There may be an optimization possible
whereby if the foreign key column is not nullable, we can
use inner joins instead.
*/
if (condition == null) return true;
if (condition.isInverted()) {
whereClause.append("NOT (");
}
try {
if (condition instanceof CompoundCondition) {
parseCompound(alias, (CompoundCondition) condition);
return false;
} else if (condition instanceof SimpleCondition) {
return parseSimple(alias, (SimpleCondition) condition);
} else if (condition instanceof RawCondition) {
parseRaw((RawCondition) condition);
return false;
}
} finally {
if (condition.isInverted()) {
whereClause.append(")");
}
}
return false;
}
/**
* Parses a compound condition by inserting appropriate
* parentheses and the "OR"/"AND" keyword between left hand
* and right hand sides of the compound condition.
* Recursively parses the left and right hand sides by calling
* parseBranch().
*/
private void parseCompound(Alias alias, CompoundCondition condition) {
whereClause.append("(");
if (parseBranch(alias, condition.getLHS())) {
// LHS expression was an outer join with no where clause
// ugly.. remove the '(' we just added
whereClause.deleteCharAt(whereClause.length()-1);
parseBranch(alias, condition.getRHS());
return;
} else {
if (condition instanceof OrCondition) {
whereClause.append(" OR ");
} else if (condition instanceof AndCondition) {
whereClause.append(" AND ");
}
}
parseBranch(alias, condition.getRHS());
whereClause.append(")");
}
/** Parses the non-compound Condition. */
private boolean parseSimple(Alias alias, SimpleCondition condition) {
Column column = condition.getColumn();
Object operand = condition.getValue();
boolean wasOuter = false;
StringBuffer buffer = whereClause;
if (operand instanceof Selector) {
Selector next = (Selector) operand;
Alias nextAlias = makeAlias(next);
if (next.isOuterJoin()) {
if (next.getCondition() instanceof SimpleCondition) {
parseSimple(nextAlias, (SimpleCondition) next.getCondition());
} else {
wasOuter = true;
}
fromClause.append(" LEFT OUTER JOIN ")
.append(nextAlias.getTable().getName())
.append(" ")
.append(nextAlias.getName())
.append(" ON ");
buffer = fromClause;
} else {
buffer.append("(");
}
buffer.append(alias.getName())
.append(".")
.append(column.getName())
.append(" = ");
// In the usual case this should be nextAlias.primaryKey,
// but in the case of CONTAINS it should be nextAlias.foreignKey.
buffer.append(nextAlias.getName())
.append(".")
.append(nextAlias.getJoinColumn().getName());
// Because Many-to-Many operations can cause multiple
// copies, set the distinct flag.
if (condition.getOperator() == Operator.CONTAINS) {
distinct = true;
}
if (!next.isOuterJoin()) {
parse(nextAlias, next);
buffer.append(")");
}
} else {
whereClause.append("(");
whereClause.append(alias.getName())
.append(".")
.append(column.getName());
parseOperand(condition.getOperator(), operand);
whereClause.append(")");
}
return wasOuter;
}
private void parseRaw(RawCondition condition) {
distinct = true;
aliases.clear(); // Raw condition won't use aliases
fromClause = new StringBuffer();
Iterator i = condition.getTables().iterator();
boolean first = true;
while (i.hasNext()) {
Table t = (Table) i.next();
Alias alias;
if (first) {
alias = new Alias(t.getName(), t, selector.getFetchColumns(), t.getPrimaryKey());
first = false;
} else {
alias = new Alias(t.getName(), t, null, t.getPrimaryKey());
fromClause.append(",");
}
aliases.add(alias);
fromClause.append(t.getName());
}
whereClause.append(condition.getRawQuery());
}
/**
* Parses an operator/operand pair and appends them to the whereClause.
*/
private void parseOperand(Operator operator, Object operand) {
if (operand == null) {
whereClause.append(" IS ");
if (Operator.NOT_EQUAL.equals(operator)) {
whereClause.append("NOT ");
}
} else {
appendOperator(operator, whereClause);
if (operand instanceof String) {
String strOp = operand.toString();
if (Operator.ENDS_WITH.equals(operator)) {
strOp = "%" + escapeLike(strOp);
} else if (Operator.STARTS_WITH.equals(operator)) {
strOp = escapeLike(strOp) + "%";
} else if (Operator.STR_CONTAINS.equals(operator)) {
strOp = "%" + escapeLike(strOp) + "%";
}
parameters.add(strOp);
operand = "?";
} else if (operand instanceof Date) {
operand = "{ts '" + new Timestamp(((Date) operand).getTime()) + "'}";
} else if (operand instanceof Collection) {
StringBuffer sb = new StringBuffer("(");
// Iterate through collection
Iterator it = ((Collection) operand).iterator();
boolean seenOne = false;
while (it.hasNext()) {
Object obj = it.next();
if (seenOne) {
sb.append(", ");
} else {
seenOne = true;
}
if (obj instanceof String) {
obj = escapeSQLString((String) obj);
}
sb.append(obj);
}
operand = sb.append(")").toString();
} else {
parameters.add(operand);
operand = "?";
}
}
whereClause.append(operand);
}
/**
* Appends the correct SQL for the given operator to the buffer.
* Does not deal with the operand itself. Therefore all of
* STARTS_WITH, ENDS_WITH and STR_CONTAINS append the value " LIKE ".
*
* Returns the same buffer as passed in.
*/
public static StringBuffer appendOperator(Operator operator, StringBuffer buffer) {
if (Operator.EQUAL.equals(operator)) {
buffer.append(" = ");
} else if (Operator.NOT_EQUAL.equals(operator)) {
buffer.append(" != ");
} else if (Operator.LT.equals(operator)) {
buffer.append(" < ");
} else if (Operator.GT.equals(operator)) {
buffer.append(" > ");
} else if (Operator.LTE.equals(operator)) {
buffer.append(" <= ");
} else if (Operator.GTE.equals(operator)) {
buffer.append(" >= ");
} else if (Operator.STARTS_WITH.equals(operator)
|| Operator.ENDS_WITH.equals(operator)
|| Operator.STR_CONTAINS.equals(operator)) {
buffer.append(" LIKE ");
} else if (Operator.IN.equals(operator)) {
buffer.append(" IN ");
}
return buffer;
}
private static StringBuffer escapeImpl(StringBuffer b, char toEscape, char escapeChar) {
for (int i = b.length() - 1; i >= 0; i--) {
if (b.charAt(i) == toEscape)
b.insert(i, escapeChar);
}
return b;
}
/**
* Escapes an SQL String by quoting all single-quotes as
* two single-quotes. Also appends leading and trailing single
* quotes. For example, "Bob" becomes "'Bob'" and "Bob's" becomes
* "'Bob''s'".
*/
public static String escapeSQLString(String operand) {
if (operand == null) return null;
return "'" +
escapeImpl(new StringBuffer(operand), '\'', '\'')
.append('\'').toString();
}
/**
* Escapes a SQL string by replacing "%" literals with "~%".
*/
public static String escapeLike(String operand) {
if (operand == null) return null;
return escapeImpl(new StringBuffer(operand), '%', '~').toString();
}
private void parseOrdering(Selector selector) {
Selector.Ordering[] ordering = selector.getOrdering();
if (ordering == null) {
return;
}
if (orderingClause.length() > 0) {
orderingClause.append(",");
}
for (int i = 0; i < ordering.length; i++) {
if (i > 0) {
orderingClause.append(", ");
}
Column column = ordering[i].getColumn();
Alias alias = null;
Iterator it = aliases.iterator();
while (it.hasNext()) {
alias = (Alias) it.next();
if (alias.getTable() == column.getTable()) {
break;
}
}
orderingClause.append(alias.getName())
.append('.')
.append(column.getName())
.append(' ');
int order = ordering[i].getOrder();
switch (order) {
case Selector.Ordering.ASCENDING:
orderingClause.append("ASC");
break;
case Selector.Ordering.DESCENDING:
orderingClause.append("DESC");
break;
}
}
}
/** SQL needed to run the count() query. */
public String toCountSQL() {
Alias first = (Alias) aliases.iterator().next();
StringBuffer sql = new StringBuffer()
.append("SELECT COUNT(");
if (distinct) {
sql.append("DISTINCT ");
sql.append(first.getName())
.append('.')
.append(first.getTable().getPrimaryKey().getName());
} else {
sql.append('*');
}
sql.append(") FROM ")
.append(fromClause.toString());
if (whereClause.length() > 0) {
sql.append(" WHERE ")
.append(whereClause.toString());
}
return sql.toString();
}
/**
* Returns the SQL generated by this query.
*/
public String toSQL() {
StringBuffer sql = new StringBuffer()
.append("SELECT ");
if (distinct) {
sql.append("DISTINCT ");
}
// Examine all selectors for fetchColumns
Iterator i = aliases.iterator();
boolean seenAny = false;
while (i.hasNext()) {
Alias alias = (Alias) i.next();
Table t = alias.getTable();
Set s = alias.getFetchColumns();
Iterator j = s.iterator();
while (j.hasNext()) {
if (seenAny) {
sql.append(", ");
} else {
seenAny = true;
}
Column c = (Column) j.next();
sql.append(alias.getName())
.append(".")
.append(c.getName());
}
}
sql.append(" FROM ")
.append(fromClause.toString());
if (whereClause.length() > 0) {
sql.append(" WHERE ")
.append(whereClause.toString());
}
if (orderingClause.length() > 0) {
sql.append(" ORDER BY ")
.append(orderingClause.toString());
}
return sql.toString();
}
}