/*
* #!
* Ontopia Engine
* #-
* Copyright (C) 2001 - 2013 The Ontopia Project
* #-
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
* !#
*/
package net.ontopia.persistence.query.sql;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import net.ontopia.persistence.proxy.DefaultFieldHandler;
import net.ontopia.persistence.proxy.FieldHandlerIF;
import net.ontopia.persistence.proxy.SQLTypes;
import net.ontopia.utils.OntopiaRuntimeException;
import net.ontopia.utils.PropertyUtils;
import net.ontopia.utils.StringUtils;
/**
* INTERNAL: Generic SQL statement generator.
*/
public class GenericSQLGenerator implements SQLGeneratorIF {
// FIXME: May have to tweak these based on empirical values
protected static final int INIT_WIDTH_SELECT = 64;
protected static final int INIT_WIDTH_FROM = 64;
protected static final int INIT_WIDTH_WHERE = 256;
protected static final int INIT_WIDTH_GROUP_BY = 16;
protected static final int INIT_WIDTH_ORDER_BY = 32;
protected static final int INIT_WIDTH_SQL =
INIT_WIDTH_SELECT +
INIT_WIDTH_FROM +
INIT_WIDTH_WHERE +
INIT_WIDTH_GROUP_BY +
INIT_WIDTH_ORDER_BY;
protected int MAX_ELEMENTS_IN = 0;
GenericSQLGenerator(Map properties) {
if (properties != null) {
this.MAX_ELEMENTS_IN = PropertyUtils.getInt((String)properties.get("net.ontopia.persistence.query.sql.InMaxElements"), MAX_ELEMENTS_IN);
}
}
/**
* INTERNAL: Class used to hold information collected after having
* analyzed the SQL filter.
*/
class BuildInfo {
protected BuildInfo parent;
// FIXME: Replace Collection instances with arrays.
protected boolean is_setop_query = false;
protected int tlevel = 1;
protected Map tlevels = new HashMap();
// selects
protected List stypes = new ArrayList();
protected List sfhandlers = new ArrayList();
// parameters
protected List pnames = new ArrayList();
protected List ptypes = new ArrayList();
protected List pfhandlers = new ArrayList();
protected int where_offset;
protected List poffsets = new ArrayList();
// aggregate selects
protected boolean hasaggs = false;
protected List nonaggregate = new ArrayList();
// Note that these iterators are intended to be reused
protected ColumnValueIterator viter1 = new ColumnValueIterator();
protected ColumnValueIterator viter2 = new ColumnValueIterator();
BuildInfo() {
}
BuildInfo(BuildInfo parent) {
this.parent = parent;
}
protected void addSelect(Class stype, FieldHandlerIF sfhandler) {
// FIXME: 1. Result column(s) -> Object value
// FIXME: 2. Object value -> Instance field value
//! if (!sfhandlers.contains(sfhandler)) { // this to avoid duplicate selects(!)
sfhandlers.add(sfhandler);
stypes.add(stype);
//! } else new RuntimeException().printStackTrace();
}
protected void addNonAggregateSelect(StringBuilder select) {
nonaggregate.add(select);
}
protected void addParameter(SQLParameter param, int offset) {
// FIXME: Should perhaps keep track of SQLParameter instead each
// of its components individually?
pnames.add(param.getName());
ptypes.add(param.getValueType());
pfhandlers.add(param.getFieldHandler());
poffsets.add(new Integer(offset));
// FIXME: If ptype is instance of Collection, we know it is of
// variable length.
}
protected void embedInfoFrom(BuildInfo info, int sql_offset) {
// copy parameter names
pnames.addAll(info.pnames);
// copy parameter types
ptypes.addAll(info.ptypes);
// copy parameter field handlers
pfhandlers.addAll(info.pfhandlers);
// adjust and copy parameter offsets
if (poffsets.size() > 0) {
Iterator iter = info.poffsets.iterator();
while (iter.hasNext()) {
Integer old_po = (Integer)iter.next();
Integer new_po = new Integer(old_po.intValue() + sql_offset);
poffsets.add(new_po);
}
}
}
protected Class[] getSelectTypes() {
Class[] _types = new Class[stypes.size()];
stypes.toArray(_types);
return _types;
}
protected Class[] getParameterTypes() {
Class[] _types = new Class[ptypes.size()];
ptypes.toArray(_types);
return _types;
}
protected FieldHandlerIF[] getSelectHandlers() {
FieldHandlerIF[] _sfhandlers = new FieldHandlerIF[sfhandlers.size()];
sfhandlers.toArray(_sfhandlers);
return _sfhandlers;
}
protected String[] getParameterNames() {
String[] _pnames = new String[pnames.size()];
pnames.toArray(_pnames);
return _pnames;
}
protected FieldHandlerIF[] getParameterHandlers() {
FieldHandlerIF[] _pfhandlers = new FieldHandlerIF[pfhandlers.size()];
pfhandlers.toArray(_pfhandlers);
return _pfhandlers;
}
protected int[] getParameterOffsets() {
int length = poffsets.size();
int[] _poffsets = new int[length];
for (int i=0; i < length; i++) {
_poffsets[i] = ((Integer)poffsets.get(i)).intValue() + where_offset;
//! System.out.println("P: " + _poffsets[i]);
}
return _poffsets;
}
protected int[] getCollectionIndexes() {
int length = ptypes.size();
int[] indexes = null;
int coll_count = 0;
for (int i=0; i < length; i++) {
if (Collection.class.isAssignableFrom((Class)ptypes.get(i))) {
if (indexes == null ||
indexes.length <= coll_count)
indexes = new int[coll_count + 1];
indexes[coll_count] = i;
coll_count++;
}
}
return indexes;
}
// Flag used to indicate whether SQLColumns tables should be registered.
protected boolean register_tables = false;
// Tables referenced
protected Set rtables = new HashSet();
// Tables joined
protected Set jtables = new HashSet();
// Tables selected
protected Set stables = new HashSet();
// Table joins
protected Set joins = new HashSet();
// FROM fragments (strings that later get joined)
protected List fg_from = new ArrayList();
}
class ColumnValueIterator {
protected SQLValueIF current;
protected SQLValueIF[] list;
protected int pindex;
protected int cindex;
void resetValue(SQLValueIF value) {
pindex = 0;
cindex = 0;
if (value.getType() == SQLValueIF.TUPLE) {
SQLTuple tuple = (SQLTuple)value;
list = tuple.getValues();
// If it's a nested tuple we need to flatten it.
int varity = tuple.getValueArity();
if (varity > list.length) {
list = new SQLValueIF[varity];
flattenSQLValueIF(tuple.getValues(), list, 0);
}
current = list[0];
} else {
list = null;
current = value;
}
}
void nextReference(StringBuilder sql, BuildInfo info) {
// FIXME: why don't we just delegate to atomicSQLValueIF?
switch (current.getType()) {
case SQLValueIF.COLUMNS: {
SQLColumns columns = (SQLColumns)current;
SQLTable table = columns.getTable();
// If table registration flag is set and this is the first column,
// register table with build info
if (cindex == 0 && info.register_tables)
info.rtables.add(table);
referenceSQLColumnsColumn(table, columns.getColumns()[cindex], sql, info);
break;
}
case SQLValueIF.PARAMETER: {
// FIXME: Make sure that this method is not called outside the
// where clause and only once per parameter - and in the right
// order.
// Register parameter type+info
SQLParameter param = (SQLParameter)current;
//! System.out.println("===1> Parameter " + param.getName() + " (index: " + sql.length() + ")");
if (cindex == 0)
info.addParameter(param, sql.length());
sql.append('?');
break;
}
case SQLValueIF.PRIMITIVE: {
// Note: arity is always 1
referenceSQLPrimitive((SQLPrimitive)current, sql, info);
break;
}
case SQLValueIF.NULL:
// Note: arity is always 1
sql.append("null");
break;
case SQLValueIF.VERBATIM:
sql.append(((SQLVerbatim)current).getValue());
break;
case SQLValueIF.FUNCTION:
referenceSQLFunction((SQLFunction)current, sql, info);
return;
default:
throw new OntopiaRuntimeException("Unsupported SELECT SQLValueIF: '" +
current + "' type: " + current.getType());
}
// Skip to next value
cindex++;
if (cindex >= current.getArity()) {
if (list != null) {
pindex++;
if (pindex < list.length) {
current = list[pindex];
cindex = 0;
} else {
// we're done
}
} else {
// we're done
}
}
}
}
public SQLStatementIF createSQLStatement(SQLQuery query) {
// Create SQL query from query components
BuildInfo info = new BuildInfo();
String sql = createStatement(query, info);
//! // Debug: output parameter offsets
//! System.out.println("===1> Parameters: " + info.poffsets);
//! int[] offs = info.getParameterOffsets();
//! List offs_ = new ArrayList();
//! for (int i=0; i < offs.length; i++)
//! offs_.add(new Integer(offs[i]));
//! System.out.println("===x> Parameters: " + offs_);
// Wrap it all in a SQL statement object
ParameterProcessorIF proc = null;
int[] coll_indexes = info.getCollectionIndexes();
if (coll_indexes != null)
proc = new CollectionParameterProcessor(info.getParameterHandlers(), info.getParameterNames(),
coll_indexes, info.getParameterOffsets());
else
proc = new DefaultParameterProcessor(info.getParameterHandlers(), info.getParameterNames());
return new SQLStatement(sql, info.getSelectHandlers(), proc);
}
protected String createStatement(SQLQuery query, BuildInfo info) {
// Analyze query by tracking the topmost level at which a table is
// referenced. This information is later being used to figure out
// in which from clause the table should be referenced.
analyzeQuery(query, info.tlevels, new Integer(1));
//! System.out.println("MAP: " + info.tlevels);
return createStatement(query.getFilter(), query.getSelect(),
query.getDistinct(), query.getOffset(), query.getLimit(),
query.getOrderBy(), query.isSetQuery(), info);
}
protected String createStatement(SQLExpressionIF filter, List selects, boolean distinct, int offset, int limit,
List orderby, boolean issetquery, BuildInfo info) {
// Analyze query
// WHERE and SELECT clauses must be created first.
StringBuilder sql_select = createSelectClause(selects, distinct, info);
if (issetquery)
info.rtables.clear();
StringBuilder sql_where = createWhereClause(filter, info);
StringBuilder sql_group_by = createGroupByClause(info);
StringBuilder sql_order_by = createOrderByClause(orderby, info);
// FROM clause must be created at last.
StringBuilder sql_from;
if (issetquery) {
sql_from = sql_where;
sql_from.insert(0, '(');
sql_from.append(')');
fromSubSelectAlias(sql_from, info);
sql_where = null;
} else
sql_from = createFromClause(filter, info);
StringBuilder sql_offset_limit = createOffsetLimitClause(offset, limit, info);
// Construct the full statement.
return createStatement(sql_select, sql_where, sql_from, sql_group_by, sql_order_by, sql_offset_limit, info);
}
protected StringBuilder createSelectClause(List selects, boolean distinct, BuildInfo info) {
// SELECT clause
info.register_tables = true;
StringBuilder sql_select = new StringBuilder(INIT_WIDTH_SELECT);
produceSelect(selects, distinct, sql_select, info);
info.register_tables = false;
return sql_select;
}
protected StringBuilder createWhereClause(SQLExpressionIF filter, BuildInfo info) {
// WHERE clause
info.register_tables = true;
StringBuilder sql_where = new StringBuilder(INIT_WIDTH_WHERE);
produceWhere(filter, sql_where, info);
info.register_tables = false;
return sql_where;
}
protected StringBuilder createFromClause(SQLExpressionIF filter, BuildInfo info) {
// FROM clause
StringBuilder sql_from = new StringBuilder(INIT_WIDTH_FROM);
produceFrom(sql_from, info);
return sql_from;
}
protected StringBuilder createGroupByClause(BuildInfo info) {
// GROUP BY clause
StringBuilder sql_group_by = new StringBuilder(INIT_WIDTH_GROUP_BY);
produceGroupBy(sql_group_by, info);
return sql_group_by;
}
protected StringBuilder createOrderByClause(List orderby, BuildInfo info) {
// ORDER BY clause
info.register_tables = true;
StringBuilder sql_order_by = new StringBuilder(INIT_WIDTH_ORDER_BY);
produceOrderBy(orderby, sql_order_by, info);
info.register_tables = false;
return sql_order_by;
}
protected StringBuilder createOffsetLimitClause(int offset, int limit, BuildInfo info) {
// ISSUE: does not work with Oracle
// LIMIT x OFFSET y clause
if (limit > 0 && offset > 0) {
StringBuilder sb = new StringBuilder();
sb.append(" limit ").append(limit).append(" offset ").append(offset);
return sb;
} else if (limit > 0) {
StringBuilder sb = new StringBuilder();
sb.append(" limit ").append(limit);
return sb;
} else if (offset > 0) {
// ISSUE: does not work with MySQL
StringBuilder sb = new StringBuilder();
sb.append(" offset ").append(offset);
return sb;
} else {
return null;
}
}
protected String createStatement(StringBuilder sql_select, StringBuilder sql_where,
StringBuilder sql_from, StringBuilder sql_group_by,
StringBuilder sql_order_by, StringBuilder sql_offset_limit, BuildInfo info) {
// FIXME: Return StringBuilder instead?
// NOTE: WHERE clause and FROM clause has already been created at this point.
// Append clauses
StringBuilder sql = new StringBuilder(INIT_WIDTH_SQL);
// SELECT clause
sql.append("select ");
sql.append(sql_select);
// FROM clause
sql.append(" from ");
sql.append(sql_from);
// WHERE clause
if (sql_where != null && sql_where.length() != 0) {
sql.append(" where ");
info.where_offset = sql.length(); // set where-offset member
sql.append(sql_where);
}
// GROUP BY clause
if (sql_group_by != null && sql_group_by.length() != 0) {
sql.append(" group by ");
sql.append(sql_group_by);
}
// ORDER BY clause
if (sql_order_by != null && sql_order_by.length() != 0) {
sql.append(" order by ");
sql.append(sql_order_by);
}
// LIMIT x OFFSET y clause
if (sql_offset_limit != null && sql_offset_limit.length() != 0) {
sql.append(sql_offset_limit);
}
// Create statement object
return sql.toString();
}
// -----------------------------------------------------------------------------
// Pre-build SQL query analysis
// -----------------------------------------------------------------------------
protected void analyzeQuery(SQLQuery sqlquery, Map tlevels, Integer level) {
analyzeSelect(sqlquery.getSelect(), tlevels, level);
analyzeOrderBy(sqlquery.getOrderBy(), tlevels, level);
analyzeExpression(sqlquery.getFilter(), tlevels, level);
}
protected void analyzeSelect(List selects, Map tlevels, Integer level) {
Iterator iter = selects.iterator();
while (iter.hasNext()) {
Object selected = iter.next();
if (selected instanceof SQLAggregateIF)
analyzeValue(((SQLAggregateIF)selected).getValue(), tlevels, level);
else
analyzeValue((SQLValueIF)selected, tlevels, level);
}
}
protected void analyzeOrderBy(List orderby, Map tlevels, Integer level) {
Iterator iter = orderby.iterator();
while (iter.hasNext()) {
SQLOrderBy order = (SQLOrderBy)iter.next();
if (order.isAggregate())
analyzeValue(order.getAggregate().getValue(), tlevels, level);
else
analyzeValue(order.getValue(), tlevels, level);
}
}
protected void analyzeExpression(SQLExpressionIF expr, Map tlevels, Integer level) {
// Skip if expression is null
if (expr == null) return;
// Check expression type
switch (expr.getType()) {
// LOGICAL EXPRESSIONS
case SQLExpressionIF.AND: {
analyzeExpressions(((SQLAnd)expr).getExpressions(), tlevels, level);
return;
}
case SQLExpressionIF.OR: {
analyzeExpressions(((SQLOr)expr).getExpressions(), tlevels, level);
return;
}
case SQLExpressionIF.NOT: {
analyzeExpression(((SQLNot)expr).getExpression(), tlevels, level);
return;
}
// SIMPLE EXPRESSIONS
case SQLExpressionIF.FALSE: {
// Nothing to analyze
return;
}
case SQLExpressionIF.EQUALS: {
SQLEquals exp = (SQLEquals)expr;
analyzeValue(exp.getLeft(), tlevels, level);
analyzeValue(exp.getRight(), tlevels, level);
return;
}
case SQLExpressionIF.NOT_EQUALS: {
SQLNotEquals exp = (SQLNotEquals)expr;
analyzeValue(exp.getLeft(), tlevels, level);
analyzeValue(exp.getRight(), tlevels, level);
return;
}
case SQLExpressionIF.IS_NULL: {
analyzeValue(((SQLIsNull)expr).getValue(), tlevels, level);
return;
}
case SQLExpressionIF.LIKE: {
SQLLike exp = (SQLLike)expr;
analyzeValue(exp.getLeft(), tlevels, level);
analyzeValue(exp.getRight(), tlevels, level);
return;
}
case SQLExpressionIF.VERBATIM: {
return;
}
case SQLExpressionIF.VALUE_EXPRESSION:
analyzeValue(((SQLValueExpression)expr).getValue(), tlevels, level);
return;
case SQLExpressionIF.EXISTS:
analyzeExpression(((SQLExists)expr).getExpression(), tlevels, new Integer(level.intValue() + 1));
return;
case SQLExpressionIF.IN: {
SQLIn exp = (SQLIn)expr;
analyzeValue(exp.getLeft(), tlevels, level);
analyzeValue(exp.getRight(), tlevels, level);
return;
}
case SQLExpressionIF.JOIN: {
SQLJoin exp = (SQLJoin)expr;
analyzeValue(exp.getLeft(), tlevels, level);
analyzeValue(exp.getRight(), tlevels, level);
return;
}
// SET OPERATIONS
case SQLExpressionIF.SET_OPERATION: {
//! whereSQLSetOperation((SQLSetOperation)expr, sql, info);
return;
}
default:
throw new OntopiaRuntimeException("Unsupported WHERE SQLExpressionIF: '" + expr + "' (type: + " + expr.getType() + ")");
}
}
protected void analyzeExpressions(SQLExpressionIF[] exprs, Map tlevels, Integer level) {
// Loop over SQL expressions and analyze them individually
for (int i=0; i < exprs.length; i++) {
analyzeExpression(exprs[i], tlevels, level);
}
}
protected void analyzeValue(SQLValueIF value, Map tlevels, Integer level) {
switch (value.getType()) {
case SQLValueIF.COLUMNS: {
//! System.out.println("=> " + level + ":" + value);
SQLTable tbl = ((SQLColumns)value).getTable();
Integer _level = (Integer)tlevels.get(tbl);
if (_level == null || _level.intValue() > level.intValue())
tlevels.put(tbl, level);
return;
}
case SQLValueIF.TUPLE: {
SQLValueIF[] values = ((SQLTuple)value).getValues();
for (int i=0; i < values.length; i++) {
analyzeValue(values[i], tlevels, level);
}
return;
}
case SQLValueIF.FUNCTION: {
SQLFunction func = (SQLFunction)value;
SQLValueIF[] args = func.getArguments();
for (int i=0; i < args.length; i++) {
analyzeValue(args[i], tlevels, level);
}
return;
}
}
}
// -----------------------------------------------------------------------------
// SELECT clause
// -----------------------------------------------------------------------------
protected void produceSelect(List selects, boolean distinct, StringBuilder sql, BuildInfo info) {
// Output distinct if specified
if (distinct) sql.append("distinct ");
// FIXME: What if select list is empty? Use '*'?
// Make sure selected fields are registered (field handlers and aggregate functions)
boolean register = true;
// Loop over selected values and produce fragment
if (selects.isEmpty()) {
// 'select *'
sql.append('*');
} else {
Iterator iter = selects.iterator();
while (iter.hasNext()) {
Object selected = iter.next();
if (selected instanceof SQLAggregateIF)
// aggregate
selectSQLAggregateIF((SQLAggregateIF)selected, register, sql, info);
else
// selected value
selectSQLValueIF((SQLValueIF)selected, register, sql, info);
if (iter.hasNext()) sql.append(", ");
}
}
}
// -----------------------------------------------------------------------------
// GROUP BY clause
// -----------------------------------------------------------------------------
protected void produceGroupBy(StringBuilder sql, BuildInfo info) {
if (!info.hasaggs) return;
// Ignore if no aggregate values are selected
List nonagg = info.nonaggregate;
if (nonagg.isEmpty()) return;
// Loop over non aggregate columns
Iterator iter = nonagg.iterator();
while (iter.hasNext()) {
sql.append(iter.next());
if (iter.hasNext()) sql.append(", ");
}
}
// -----------------------------------------------------------------------------
// ORDER BY clause
// -----------------------------------------------------------------------------
protected void produceOrderBy(List orderby, StringBuilder sql, BuildInfo info) {
if (orderby == null || orderby.isEmpty()) return;
// Do not register field handlers and aggregate functions in order by clause
boolean register = false;
// Loop over order by columns
Iterator iter = orderby.iterator();
while (iter.hasNext()) {
SQLOrderBy ob = (SQLOrderBy)iter.next();
// Same output as SELECT fragments, so we delegate to the select method.
if (ob.isAggregate())
selectSQLAggregateIF(ob.getAggregate(), register, sql, info);
else
selectSQLValueIF(ob.getValue(), register, sql, info);
if (ob.getOrder() == SQLOrderBy.ASCENDING)
sql.append(" ASC");
else
sql.append(" DESC");
if (iter.hasNext()) sql.append(", ");
}
}
// -----------------------------------------------------------------------------
// FROM clause
// -----------------------------------------------------------------------------
protected boolean isFromLevel(SQLTable tbl, BuildInfo info) {
Integer tlevel = (Integer)info.tlevels.get(tbl);
//! System.out.println("TBL: " + tbl + ":" + info.tlevel + " " + (tlevel.intValue() == info.tlevel));
if (tlevel.intValue() == info.tlevel)
return true;
else
return false;
}
protected void produceFrom(StringBuilder sql, BuildInfo info) {
// Loop over referenced tables and FROM those that aren't joined.
Iterator iter = info.rtables.iterator();
while (iter.hasNext()) {
// FROM referenced table unless joined
SQLTable rtable = (SQLTable)iter.next();
if (!info.jtables.contains(rtable)) {
// Ignore if parent statement references table
if (info.parent == null || isFromLevel(rtable, info)) {
// FROM referenced table
fromSQLTable(rtable, sql, info);
}
}
}
// Register table joins with FROM clause (e.g. Oracle 8i)
iter = info.joins.iterator();
while (iter.hasNext()) {
// Register join with FROM clause
SQLJoin join = (SQLJoin)iter.next();
// Join directly if no parent statement
if (info.parent == null) {
fromSQLJoin(join, sql, info);
}
// Otherwise filter out tables referenced from parent.
else {
SQLTable ltable = join.getLeft().getTable();
SQLTable rtable = join.getRight().getTable();
boolean llevel = isFromLevel(ltable, info);
boolean rlevel = isFromLevel(rtable, info);
if (llevel) {
if (rlevel) {
// Join if both tables are not referenced by the parent.
fromSQLJoin(join, sql, info);
} else {
// FROM left table
fromSQLTable(ltable, sql, info);
}
} else {
if (rlevel) {
// FROM right table
fromSQLTable(rtable, sql, info);
} else {
// Do nothing since both tables are referenced by parent.
}
}
}
}
// Join tables using a comma separator
StringUtils.join(info.fg_from, ", ", sql);
}
//! protected void produceFrom(StringBuilder sql, BuildInfo info) {
//! Set ptables = null;
//! if (info.parent != null) {
//! ptables = new HashSet();
//! accumulateParentTables(info.parent, ptables);
//! }
//!
//! // Loop over referenced tables and FROM those that aren't joined.
//! Iterator iter = info.rtables.iterator();
//! while (iter.hasNext()) {
//! // FROM referenced table unless joined
//! SQLTable rtable = (SQLTable)iter.next();
//! if (!info.jtables.contains(rtable)) {
//! // Ignore if parent statement references table
//! if (info.parent == null || !ptables.contains(rtable)) {
//! // FROM referenced table
//! fromSQLTable(rtable, sql, info);
//! }
//! }
//! }
//!
//! // Register table joins with FROM clause (e.g. Oracle 8i)
//! iter = info.joins.iterator();
//! while (iter.hasNext()) {
//! // Register join with FROM clause
//! SQLJoin join = (SQLJoin)iter.next();
//!
//! // Join directly if no parent statement
//! if (info.parent == null) {
//! fromSQLJoin(join, sql, info);
//! }
//! // Otherwise filter out tables referenced from parent.
//! else {
//! SQLTable ltable = join.getLeft().getTable();
//! SQLTable rtable = join.getRight().getTable();
//! boolean lparent = ptables.contains(ltable);
//! boolean rparent = ptables.contains(rtable);
//!
//! if (lparent) {
//! if (rparent) {
//! // Do nothing since both tables are referenced by parent.
//! } else {
//! // FROM right table
//! fromSQLTable(rtable, sql, info);
//! }
//! } else {
//! if (rparent) {
//! // FROM left table
//! fromSQLTable(ltable, sql, info);
//! } else {
//! // Join if both tables are not referenced by the parent.
//! fromSQLJoin(join, sql, info);
//! }
//! }
//! }
//! }
//!
//! // Join tables using a comma separator
//! StringUtils.join(info.fg_from, ", ", sql);
//! }
//!
//! protected void accumulateParentTables(BuildInfo info, Set tables) {
//! tables.addAll(info.rtables);
//! tables.addAll(info.jtables);
//! if (info.parent != null)
//! accumulateParentTables(info.parent, tables);
//! }
protected void fromSubSelectAlias(StringBuilder sql, BuildInfo info) {
// No need for an alias.
}
// -----------------------------------------------------------------------------
// WHERE clause
// -----------------------------------------------------------------------------
protected void produceWhere(SQLExpressionIF filter, StringBuilder sql, BuildInfo info) {
whereSQLExpressionIF(filter, sql, info);
//! System.out.println("SQL: " + expression);
//! System.out.println("RTABLES: " + StringUtils.join(info.rtables, ", "));
//! System.out.println("JTABLES: " + StringUtils.join(info.jtables, ", "));
//! System.out.println("JOINS: " + info.joins);
}
// -----------------------------------------------------------------------------
// SELECT - fragments
// -----------------------------------------------------------------------------
protected void selectSQLAggregateIF(SQLAggregateIF aggregate, boolean register, StringBuilder sql, BuildInfo info) {
switch (aggregate.getType()) {
case SQLAggregateIF.COUNT:
// Note: aggregate value field will not be registered, but
// instead a field of the aggregate result type is used.
if (register) {
// Register select type+info
info.addSelect(java.lang.Integer.class, new DefaultFieldHandler(SQLTypes.getType(java.lang.Integer.class)));
info.hasaggs = true;
}
sql.append("count(");
if (aggregate.isReference())
sql.append(aggregate.getReference().getValue().getAlias());
else
selectSQLValueIF(aggregate.getValue(), false, sql, info); // Note: register is false
sql.append(')');
if (register) selectColumnAlias(aggregate, sql);
return;
default:
throw new OntopiaRuntimeException("Invalid aggregate function: '" + aggregate + "'");
}
}
protected void selectColumnAlias(SQLAggregateIF aggregate, StringBuilder sql) {
if (aggregate == null) return;
String alias = aggregate.getAlias();
if (alias != null) {
sql.append(" as ");
sql.append(alias);
}
}
protected void selectColumnAlias(SQLValueIF value, StringBuilder sql) {
if (value == null) return;
String alias = value.getAlias();
if (alias != null) {
sql.append(" as ");
sql.append(alias);
}
}
protected void selectSQLValueIF(SQLValueIF value, boolean register, StringBuilder sql, BuildInfo info) {
SQLValueIF sqlvalue;
SQLValueIF refvalue;
if (value.isReference()) {
sqlvalue = value.getReference();
refvalue = value;
} else {
sqlvalue = value;
refvalue = null;
}
// FIXME: Only columns can be selected at this time.
switch (sqlvalue.getType()) {
case SQLValueIF.COLUMNS:
if (register) {
StringBuilder sb = new StringBuilder();
selectSQLColumns((SQLColumns)sqlvalue, refvalue, register, sb, info, false);
// WARNING: last boolean used to prevent [column] aliases to be included in group by clause
StringBuilder sb_groupby = new StringBuilder();
selectSQLColumns((SQLColumns)sqlvalue, refvalue, false, sb_groupby, info, true);
info.addNonAggregateSelect(sb_groupby);
// ISSUE: the next line is problematic if compiled with java 1.4
sql.append(sb);
} else {
selectSQLColumns((SQLColumns)sqlvalue, refvalue, register, sql, info, false);
}
return;
case SQLValueIF.PRIMITIVE:
selectSQLPrimitive((SQLPrimitive)sqlvalue, refvalue, register, sql, info);
return;
//! // FIXME: Selects of parameters not currently allowed.
//! case SQLValueIF.PARAMETER:
//! return selectSQLParameter((SQLParameter)sqlvalue, info);
case SQLValueIF.NULL:
selectSQLNull((SQLNull)sqlvalue, refvalue, register, sql, info);
return;
case SQLValueIF.VERBATIM:
if (register) {
StringBuilder sb = new StringBuilder();
selectSQLVerbatim((SQLVerbatim)sqlvalue, refvalue, register, sql, info);
info.addNonAggregateSelect(sb);
sql.append(sb);
} else {
selectSQLVerbatim((SQLVerbatim)sqlvalue, refvalue, register, sql, info);
}
return;
case SQLValueIF.FUNCTION:
if (register) {
StringBuilder sb = new StringBuilder();
selectSQLFunction((SQLFunction)sqlvalue, refvalue, register, sql, info);
info.addNonAggregateSelect(sb);
sql.append(sb);
} else {
selectSQLFunction((SQLFunction)sqlvalue, refvalue, register, sql, info);
}
return;
default:
throw new OntopiaRuntimeException("Unsupported SELECT SQLValueIF: '" + sqlvalue + "'");
}
}
protected void selectSQLColumns(SQLColumns columns, SQLValueIF refvalue, boolean register,
StringBuilder sql, BuildInfo info, boolean nonagg) {
// Register select type+info
if (register) {
// NOTE:Value type and field handler defaults to java.lang.String type
info.addSelect(columns.getValueType(), columns.getFieldHandler());
}
// Extract columns info
SQLTable table = columns.getTable();
String[] cols = columns.getColumns();
// If table registration flag is set, register table with build info
if (info.register_tables) info.rtables.add(table);
// Loop over columns and insert references
if (refvalue == null) {
for (int i=0; i < cols.length; i++) {
if (i > 0) sql.append(", ");
referenceSQLColumnsColumn(table, cols[i], sql, info);
// TODO: support for " as <calias>"
if (register && !nonagg) selectColumnAlias(columns, sql);
}
} else {
for (int i=0; i < cols.length; i++) {
if (i > 0) sql.append(", ");
sql.append(columns.getAlias());
// TODO: Add suffix if width > 1.
//! if (i > 0) sql.append(i);
// TODO: support for " as <calias>"
if (register) selectColumnAlias(refvalue, sql);
}
}
}
protected void selectSQLPrimitive(SQLPrimitive value, SQLValueIF refvalue,
boolean register, StringBuilder sql, BuildInfo info) {
// Register select type+info
if (register)
info.addSelect(value.getValueType(), value.getFieldHandler());
if (refvalue == null) {
referenceSQLPrimitive(value, sql, info);
if (register) selectColumnAlias(value, sql);
} else {
// NOTE: This may not really be neccesary for NULLs
sql.append(value.getAlias());
if (register) selectColumnAlias(refvalue, sql);
}
}
//! protected String selectSQLParameter(SQLParameter parameter, boolean register, BuildInfo info) {
//! return StringUtils.join(referenceSQLParameter(parameter, info), ", ");
//! }
protected void selectSQLNull(SQLNull value, SQLValueIF refvalue,
boolean register, StringBuilder sql, BuildInfo info) {
// Register select type+info
if (register)
info.addSelect(value.getValueType(), value.getFieldHandler());
if (refvalue == null) {
sql.append("null");
if (register) selectColumnAlias(value, sql);
} else {
// NOTE: This may not really be neccesary for NULLs
sql.append(value.getAlias());
if (register) selectColumnAlias(refvalue, sql);
}
}
protected void selectSQLVerbatim(SQLVerbatim value, SQLValueIF refvalue,
boolean register, StringBuilder sql, BuildInfo info) {
// Register select type+info
if (register)
info.addSelect(value.getValueType(), value.getFieldHandler());
if (refvalue == null) {
// Output verbatime value directly
sql.append(value.getValue());
if (register) selectColumnAlias(value, sql);
} else {
sql.append(value.getAlias());
if (register) selectColumnAlias(refvalue, sql);
}
}
protected void selectSQLFunction(SQLFunction value, SQLValueIF refvalue,
boolean register, StringBuilder sql, BuildInfo info) {
// Register select type+info
if (register)
info.addSelect(value.getValueType(), value.getFieldHandler());
if (refvalue == null) {
// Output verbatime value directly
referenceSQLFunction(value, sql, info);
if (register) selectColumnAlias(value, sql);
} else {
sql.append(value.getAlias());
if (register) selectColumnAlias(refvalue, sql);
}
}
// -----------------------------------------------------------------------------
// FROM - fragments
// -----------------------------------------------------------------------------
protected void fromSQLTable(SQLTable table, StringBuilder sql, BuildInfo info) {
// Ignore if already FROMed
if (!info.stables.contains(table)) {
// Register FROM fragment
StringBuilder sb = new StringBuilder();
referenceSQLTableAndAlias(table, sb, info);
info.fg_from.add(sb);
// Register right table as selected
info.stables.add(table);
}
}
protected void fromSQLJoin(SQLJoin join, StringBuilder sql, BuildInfo info) {
// Check join type
switch (join.getJoinType()) {
case SQLJoin.CROSS:
// Register cross join with FROM clause
fromSQLCrossJoin(join, sql, info);
break;
case SQLJoin.LEFT_OUTER:
// Register left outer join with FROM clause
fromSQLLeftOuterJoin(join, sql, info);
break;
case SQLJoin.RIGHT_OUTER:
// Register left outer join with FROM clause
fromSQLRightOuterJoin(join, sql, info);
break;
default:
throw new OntopiaRuntimeException("Unsupported FROM SQLJoin join type: '" + join.getJoinType() + "'");
}
}
protected void fromSQLCrossJoin(SQLJoin join, StringBuilder sql, BuildInfo info) {
// FROM left table
fromSQLTable(join.getLeft().getTable(), sql, info);
// FROM right table
fromSQLTable(join.getRight().getTable(), sql, info);
}
protected void fromSQLLeftOuterJoin(SQLJoin join, StringBuilder sql, BuildInfo info) {
fromSQLJoin_GENERIC(join, " LEFT OUTER JOIN ", sql, info);
}
protected void fromSQLRightOuterJoin(SQLJoin join, StringBuilder sql, BuildInfo info) {
fromSQLJoin_GENERIC(join, " RIGHT OUTER JOIN ", sql, info);
}
protected void fromSQLJoin_GENERIC(SQLJoin join, String jointype, StringBuilder sql, BuildInfo info) {
SQLColumns lcols = join.getLeft();
SQLColumns rcols = join.getRight();
StringBuilder sb = new StringBuilder();
sb.append('(');
// FROM left table
referenceSQLTableAndAlias(lcols.getTable(), sb, info);
// join type, e.g. " LEFT OUTER JOIN "
sb.append(jointype);
// FROM right table
referenceSQLTableAndAlias(rcols.getTable(), sb, info);
// ON expression
sb.append(" on ");
whereSQLCrossJoin_GENERIC(join, sb, info);
sb.append(')');
// Register FROM fragment
info.fg_from.add(sb);
// Register tables as selected
info.stables.add(lcols.getTable());
info.stables.add(rcols.getTable());
}
// -----------------------------------------------------------------------------
// SQLExpressionIF
// -----------------------------------------------------------------------------
protected void whereSQLExpressionIF(SQLExpressionIF expr, StringBuilder sql, BuildInfo info) {
// Skip if expression is null
if (expr == null) return;
// Check expression type
switch (expr.getType()) {
// LOGICAL EXPRESSIONS
case SQLExpressionIF.AND:
whereSQLAnd((SQLAnd)expr, sql, info);
return;
case SQLExpressionIF.OR:
whereSQLOr((SQLOr)expr, sql, info);
return;
case SQLExpressionIF.NOT:
whereSQLNot((SQLNot)expr, sql, info);
return;
// SIMPLE EXPRESSIONS
case SQLExpressionIF.FALSE: {
whereSQLFalse((SQLFalse)expr, sql, info);
return;
}
case SQLExpressionIF.EQUALS:
whereSQLEquals((SQLEquals)expr, sql, info);
return;
case SQLExpressionIF.NOT_EQUALS:
whereSQLNotEquals((SQLNotEquals)expr, sql, info);
return;
case SQLExpressionIF.IS_NULL:
whereSQLIsNull((SQLIsNull)expr, sql, info);
return;
case SQLExpressionIF.LIKE:
whereSQLLike((SQLLike)expr, sql, info);
return;
case SQLExpressionIF.VERBATIM:
whereSQLVerbatimExpression((SQLVerbatimExpression)expr, sql, info);
return;
case SQLExpressionIF.VALUE_EXPRESSION:
whereSQLValueExpression((SQLValueExpression)expr, sql, info);
return;
case SQLExpressionIF.EXISTS:
whereSQLExists((SQLExists)expr, sql, info);
return;
case SQLExpressionIF.IN:
whereSQLIn((SQLIn)expr, sql, info);
return;
case SQLExpressionIF.JOIN:
whereSQLJoin((SQLJoin)expr, sql, info);
return;
// SET OPERATIONS
case SQLExpressionIF.SET_OPERATION:
whereSQLSetOperation((SQLSetOperation)expr, sql, info);
return;
default:
throw new OntopiaRuntimeException("Unsupported WHERE SQLExpressionIF: '" + expr + "' (type: + " + expr.getType() + ")");
}
}
protected void whereSQLExpressionIF(SQLExpressionIF[] nexprs, String separator, StringBuilder sql, BuildInfo info) {
// Returns true if any nested expression contributed to the where clause
if (nexprs == null || nexprs.length == 0) return;
// Process expression
//! whereSQLExpressionIF(nexprs[0], sql, info);
int length = nexprs.length;
int c = 0;
for (int i=0; i < length; i++) {
if (nexprs[i] == null) continue;
if (c > 0)
sql.append(separator);
whereSQLExpressionIF(nexprs[i], sql, info);
c++;
}
}
// -----------------------------------------------------------------------------
// SQLLogicalIF
// -----------------------------------------------------------------------------
protected void whereSQLAnd(SQLAnd and, StringBuilder sql, BuildInfo info) {
sql.append('(');
whereSQLExpressionIF(and.getExpressions(), " and ", sql, info);
sql.append(')');
}
protected void whereSQLOr(SQLOr or, StringBuilder sql, BuildInfo info) {
sql.append('(');
whereSQLExpressionIF(or.getExpressions(), " or ", sql, info);
sql.append(')');
}
protected void whereSQLNot(SQLNot not, StringBuilder sql, BuildInfo info) {
// FIXME: May have to special-case if nested expression is empty.
sql.append("not (");
whereSQLExpressionIF(not.getExpression(), sql, info);
sql.append(')');
}
// -----------------------------------------------------------------------------
// Operators
// -----------------------------------------------------------------------------
protected void whereSQLFalse(SQLFalse expr, StringBuilder sql, BuildInfo info) {
sql.append("false");
}
protected void whereSQLEquals(SQLEquals equals, StringBuilder sql, BuildInfo info) {
// Rewrite null values to an "is null" expression
if (equals.getLeft().getType() == SQLValueIF.NULL)
whereSQLValueEqualsNull(equals.getRight(), sql, info);
else if (equals.getRight().getType() == SQLValueIF.NULL)
whereSQLValueEqualsNull(equals.getLeft(), sql, info);
else
referenceSQLValueIFOpBinary(equals.getLeft(), "=", equals.getRight(), sql, info);
}
protected void whereSQLNotEquals(SQLNotEquals nequals, StringBuilder sql, BuildInfo info) {
// Rewrite null values to an "is not null" expression
if (nequals.getLeft().getType() == SQLValueIF.NULL)
whereSQLValueNotEqualsNull(nequals.getRight(), sql, info);
else if (nequals.getRight().getType() == SQLValueIF.NULL)
whereSQLValueNotEqualsNull(nequals.getLeft(), sql, info);
else
referenceSQLValueIFOpBinary(nequals.getLeft(), "!=", nequals.getRight(), sql, info);
}
protected void whereSQLValueEqualsNull(SQLValueIF value, StringBuilder sql, BuildInfo info) {
referenceSQLValueIFOpUnary(value, "is null", sql, info);
}
protected void whereSQLValueNotEqualsNull(SQLValueIF value, StringBuilder sql, BuildInfo info) {
referenceSQLValueIFOpUnary(value, "is not null", sql, info);
}
protected void whereSQLIsNull(SQLIsNull is_null, StringBuilder sql, BuildInfo info) {
referenceSQLValueIFOpUnary(is_null.getValue(), "is null", sql, info);
}
protected void whereSQLLike(SQLLike like, StringBuilder sql, BuildInfo info) {
//! referenceSQLValueIFOpBinary(like.getLeft(), "like", like.getRight(), sql, info);
if (like.getCaseSensitive()) {
// SQL: A like B
atomicSQLValueIF(like.getLeft(), sql, info);
sql.append(" like ");
atomicSQLValueIF(like.getRight(), sql, info);
} else {
// SQL: lower(A) like lower(B)
sql.append("lower(");
atomicSQLValueIF(like.getLeft(), sql, info);
sql.append(") like lower(");
atomicSQLValueIF(like.getRight(), sql, info);
sql.append(')');
}
}
protected boolean isPatternFunction(SQLFunction func) {
// SQL function is a pattern function if it contains at least one $ character
return func.getName().indexOf('$') != -1;
}
protected void referenceSQLFunction(SQLFunction func, StringBuilder sql, BuildInfo info) {
SQLValueIF[] args = func.getArguments();
String fname = func.getName();
if (isPatternFunction(func)) {
int pix = 0;
while (true) {
int ix = fname.indexOf('$', pix);
if (ix == -1 || ix >= fname.length() - 1) {
sql.append(fname.substring(pix));
break;
} else {
// FIXME: only 10 arguments supported
char c = fname.charAt(ix+1);
if (Character.isDigit(c)) {
int cix = Character.digit(c, 10) - 1;
sql.append(fname.substring(pix, ix));
atomicSQLValueIF(args[cix], sql, info);
ix = ix + 2;
} else {
sql.append(fname.substring(pix, ix));
ix = ix + 1;
}
pix = ix;
}
}
} else {
if (fname.equals(">") || fname.equals(">=") || fname.equals("<=") || fname.equals("<")) {
referenceSQLValueIFOpBinary(args[0], fname, args[1], sql, info);
} else {
sql.append(func.getName()).append('(');
for (int i=0; i < args.length; i++) {
if (i > 0) sql.append(", ");
atomicSQLValueIF(args[i], sql, info);
}
sql.append(')');
}
}
}
protected void whereSQLVerbatimExpression(SQLVerbatimExpression expr, StringBuilder sql, BuildInfo info) {
sql.append(expr.getValue());
}
protected void whereSQLValueExpression(SQLValueExpression expr, StringBuilder sql, BuildInfo info) {
atomicSQLValueIF(expr.getValue(), sql, info);
}
protected void whereSQLExists(SQLExists exists, StringBuilder sql, BuildInfo pinfo) {
// FIXME: Only use exists expression when new variables are
// introduced, since the from-clause will be empty otherwise.
BuildInfo info = new BuildInfo(pinfo);
info.tlevel = pinfo.tlevel + 1;
info.tlevels = pinfo.tlevels;
SQLExpressionIF filter = exists.getExpression();
StringBuilder sql_where = createWhereClause(filter, info);
StringBuilder sql_from = createFromClause(filter, info);
if (sql_from.length() > 0) {
// Produce full subquery if FROM clause is not empty
// WARN: Selecting a null might not always work?
List selects = Collections.singletonList(new SQLNull());
boolean distinct = false;
StringBuilder sql_select = createSelectClause(selects, distinct, info);
StringBuilder sql_group_by = null; // createGroupByClause(info);
StringBuilder sql_order_by = null; // createOrderByClause(orderby, info);
StringBuilder sql_offset_limit = null; // createOffsetLimitClause(offset, limit, info);
// Embed sub query
sql.append("exists (");
String subsql = createStatement(sql_select, sql_where, sql_from,
sql_group_by, sql_order_by, sql_offset_limit, info);
// Make sure parameters etc. are registered with parent build info.
pinfo.embedInfoFrom(info, sql.length());
sql.append(subsql);
sql.append(')');
} else {
// Make sure parameters etc. are registered with parent build info.
pinfo.embedInfoFrom(info, sql.length());
// Embed WHERE clause when FROM clause is empty.
sql.append(sql_where);
}
//! System.out.println("===2> Parameters: " + info.poffsets);
// FIXME: Should copy parameter types, parameter field handlers
// and parameter offsets to parent build info when done.
}
protected void whereSQLIn(SQLIn in, StringBuilder sql, BuildInfo info) {
SQLValueIF left = in.getLeft();
SQLValueIF right = in.getRight();
if (left.getArity() != 1)
throw new OntopiaRuntimeException("Arity of left value is not 1: " + left);
// FIXME: Some databases has a limit on the number of elements in
// the in clause, e.g. 256.
int rarity = right.getArity();
if (rarity > 1) {
//! joinSQLValueIF(right, ", ", sql, info);
// Use first value iterator
ColumnValueIterator viter = info.viter1;
viter.resetValue(right);
// Loop over all elements and split into appropriate number of
int max_elements_in = (MAX_ELEMENTS_IN > 0 ? MAX_ELEMENTS_IN : Integer.MAX_VALUE);
for (int i=0; i < rarity; i++) {
if (i % max_elements_in == 0) {
if (i > 0) sql.append(") or ");
atomicSQLValueIF(left, sql, info);
sql.append(" in (");
}
else {
sql.append(", ");
}
viter.nextReference(sql, info);
}
sql.append(')');
}
else if (rarity == 1) {
atomicSQLValueIF(left, sql, info);
sql.append(" in (");
atomicSQLValueIF(right, sql, info);
sql.append(')');
} else
throw new OntopiaRuntimeException("Arity of right value is less than 1 (it is " + rarity + ").");
}
// -----------------------------------------------------------------------------
// WHERE - set operations
// -----------------------------------------------------------------------------
protected String getSetOperator(int operator) {
switch (operator) {
case SQLSetOperation.UNION:
return "union";
case SQLSetOperation.UNION_ALL:
return "union all";
case SQLSetOperation.INTERSECT:
return "intersect";
case SQLSetOperation.INTERSECT_ALL:
return "intersect all";
case SQLSetOperation.EXCEPT:
return "except";
case SQLSetOperation.EXCEPT_ALL:
return "except all";
default:
throw new OntopiaRuntimeException("Unsupported set operator: '" + operator + "'");
}
}
protected void whereSQLSetOperation(SQLSetOperation setop, StringBuilder sql, BuildInfo info) {
String op = getSetOperator(setop.getOperator());;
// TODO: Verify "embed build info" code!
List sets = setop.getSets();
int length = sets.size();
for (int i=0; i < length; i++) {
// WARN: sapdb and mckoi does not accept the additional
// parenthesis they way they are currently constructed.
//! sql.append('(');
BuildInfo cinfo = new BuildInfo(info);
Object set = sets.get(i);
if (set instanceof SQLQuery) {
String subsql = createStatement((SQLQuery)set, cinfo);
// Make sure parameters etc. are registered with parent build info.
info.embedInfoFrom(cinfo, sql.length());
sql.append(subsql);
} else {
whereSQLSetOperation((SQLSetOperation)set, sql, info);
}
//! sql.append(')');
if (i < length - 1) {
sql.append(' ');
sql.append(op);
sql.append(' ');
}
}
info.is_setop_query = true;
}
// -----------------------------------------------------------------------------
// WHERE - joins
// -----------------------------------------------------------------------------
protected void whereSQLJoin(SQLJoin join, StringBuilder sql, BuildInfo info) {
// Register join with build info
info.joins.add(join);
// Register join tables with build info
info.jtables.add(join.getLeft().getTable());
info.jtables.add(join.getRight().getTable());
// Check join type
switch (join.getJoinType()) {
case SQLJoin.CROSS:
whereSQLCrossJoin(join, sql, info);
return;
case SQLJoin.LEFT_OUTER:
whereSQLLeftOuterJoin(join, sql, info);
return;
case SQLJoin.RIGHT_OUTER:
whereSQLRightOuterJoin(join, sql,info);
return;
default:
throw new OntopiaRuntimeException("Unsupported WHERE SQLJoin join type: '" + join.getJoinType() + "'");
}
}
protected void whereSQLCrossJoin(SQLJoin join, StringBuilder sql, BuildInfo info) {
whereSQLCrossJoin_GENERIC(join, sql, info);
}
protected void whereSQLCrossJoin_GENERIC(SQLJoin join, StringBuilder sql, BuildInfo info) {
// TASK: Cross joins inlined
// : T1.col1 = T2.col2
// Get join tables
String lalias = join.getLeft().getTable().getAlias();
String ralias = join.getRight().getTable().getAlias();
// Get join columns
String[] lcols = join.getLeft().getColumns();
String[] rcols = join.getRight().getColumns();
int length = lcols.length;
if (length > 1) sql.append('(');
for (int i=0; i < length; i++) {
sql.append(lalias);
sql.append('.');
sql.append(lcols[i]);
sql.append(" = ");
sql.append(ralias);
sql.append('.');
sql.append(rcols[i]);
if (i != length - 1)
sql.append(" and ");
}
if (length > 1) sql.append(')');
}
protected void whereSQLLeftOuterJoin(SQLJoin join, StringBuilder sql, BuildInfo info) {
whereSQLLeftOuterJoin_GENERIC(join, sql, info);
}
protected void whereSQLLeftOuterJoin_GENERIC(SQLJoin join, StringBuilder sql, BuildInfo info) {
}
protected void whereSQLLeftOuterJoin_ORACLE(SQLJoin join, StringBuilder sql, BuildInfo info) {
// TASK: Left outer joins inlined
// : T1.col1 = T2.col2(+)
// Get join tables
String lalias = join.getLeft().getTable().getAlias();
String ralias = join.getRight().getTable().getAlias();
// Get join columns
String[] lcols = join.getLeft().getColumns();
String[] rcols = join.getRight().getColumns();
int length = lcols.length;
if (length > 1) sql.append('(');
for (int i=0; i < length; i++) {
sql.append(lalias);
sql.append('.');
sql.append(lcols[i]);
sql.append(" = ");
sql.append(ralias);
sql.append('.');
sql.append(rcols[i]);
sql.append("(+)");
if (i != length - 1)
sql.append(" and ");
}
if (length > 1) sql.append(')');
}
protected void whereSQLRightOuterJoin(SQLJoin join, StringBuilder sql, BuildInfo info) {
whereSQLRightOuterJoin_GENERIC(join, sql, info);
}
protected void whereSQLRightOuterJoin_GENERIC(SQLJoin join, StringBuilder sql, BuildInfo info) {
}
protected void whereSQLRightOuterJoin_ORACLE(SQLJoin join, StringBuilder sql, BuildInfo info) {
// TASK: Right outer joins inlined
// : T1.col1(+) = T2.col2
// Get join tables
String lalias = join.getLeft().getTable().getAlias();
String ralias = join.getRight().getTable().getAlias();
// Get join columns
String[] lcols = join.getLeft().getColumns();
String[] rcols = join.getRight().getColumns();
int length = lcols.length;
if (length > 1) sql.append('(');
for (int i=0; i < length; i++) {
sql.append(lalias);
sql.append('.');
sql.append(lcols[i]);
sql.append("(+)");
sql.append(" = ");
sql.append(ralias);
sql.append('.');
sql.append(rcols[i]);
if (i != length - 1)
sql.append(" and ");
}
if (length > 1) sql.append(')');
}
// -----------------------------------------------------------------------------
// WHERE - fragments
// -----------------------------------------------------------------------------
//! protected String whereSQLValueIF(SQLValueIF value, BuildInfo info) {
//! // FIXME: nary SQLValueIFs not supported. See referenceSQLValueIFOperations!
//! // FIXME: This method might be superflous. See referenceSQLValueIF!
//! switch (value.getType()) {
//! case SQLValueIF.COLUMNS:
//! return whereSQLColumns((SQLColumns)value, info);
//! case SQLValueIF.PRIMITIVE:
//! return whereSQLPrimitive((SQLPrimitive)value, info);
//! case SQLValueIF.PARAMETER:
//! return whereSQLParameter((SQLParameter)value, info);
//! case SQLValueIF.NULL:
//! return null;
//! default:
//! throw new OntopiaRuntimeException("Unsupported WHERE SQLValueIF: '" + value + "'");
//! }
//! }
//!
//! protected String whereSQLColumns(SQLColumns columns, BuildInfo info) {
//! SQLTable table = columns.getTable();
//! info.rtables.add(table);
//! String[] columns = columns.getColumns();
//! if (columns.length != 1)
//! throw new OntopiaRuntimeException("Unsupported WHERE SQLColumns - arity not 1: '" + columns.length + "'");
//! return referenceSQLColumnsColumn(table, columns[0], info);
//! }
//!
//! protected String whereSQLPrimitive(SQLPrimitive primitive, BuildInfo info) {
//! return referenceSQLPrimitive(primitive, info);
//! }
//!
//! protected String whereSQLParameter(SQLParameter parameter, BuildInfo info) {
//! return StringUtils.join(referenceSQLParameter(parameter, info), ", ");
//! }
//! // -----------------------------------------------------------------------------
//! // ORDER BY - fragments
//! // -----------------------------------------------------------------------------
//!
//! protected void orderBySQLValueIF(SQLValueIF value, StringBuilder sql, BuildInfo info) {
//! // Do not register tables in order by clause
//! boolean register = false;
//! // Same output as SELECT fragments, so we delegate to the select method.
//! selectSQLValueIF(value, register, sql, info);
//! }
// -----------------------------------------------------------------------------
// SQLValueIF references
// -----------------------------------------------------------------------------
protected void referenceSQLValueIFOpUnary(SQLValueIF value, String operator,
StringBuilder sql, BuildInfo info) {
// Unary operations: <value> <operator>
// If arity is 1 there is no need to create an array.
int arity = value.getArity();
if (arity == 1) {
atomicSQLValueIF(value, sql, info);
sql.append(' ').append(operator);
} else {
// Use first value iterator
ColumnValueIterator viter = info.viter1;
viter.resetValue(value);
// Loop over value columns and output result
viter.nextReference(sql, info);
sql.append(' ').append(operator);
for (int i=1; i < arity; i++) {
sql.append(" and ");
viter.nextReference(sql, info);
sql.append(operator);
}
//! String[] refs = arraySQLValueIF(value, info);
//!
//! sql.append(refs[0]);
//! sql.append(operator);
//! // Loop over remaining values
//! int arity = value.getArity();
//! for (int i=1; i < arity; i++) {
//! sql.append(" and ");
//! sql.append(refs[i]);
//! sql.append(operator);
//! }
}
}
protected void referenceSQLValueIFOpBinary(SQLValueIF value1, String operator, SQLValueIF value2,
StringBuilder sql, BuildInfo info) {
// Binary operations: <value1> <operator> <value2>
int arity1 = value1.getArity();
int arity2 = value2.getArity();
if (arity1 != arity2)
throw new OntopiaRuntimeException("Arity of values is not compatible: First: " + value1 + " (arity: " + arity1 + ") Second: " + value2 + " (arity: " + arity2 + ")");
// If arity is 1 there is no need to create an array.
if (arity1 == 1) {
atomicSQLValueIF(value1, sql, info);
sql.append(' ').append(operator).append(' ');
atomicSQLValueIF(value2, sql, info);
} else {
// Use first and second value iterators
ColumnValueIterator viter1 = info.viter1;
viter1.resetValue(value1);
ColumnValueIterator viter2 = info.viter2;
viter2.resetValue(value2);
// Loop over value columns and output result
viter1.nextReference(sql, info);
sql.append(' ').append(operator).append(' ');
viter2.nextReference(sql, info);
for (int i=1; i < arity1; i++) {
sql.append(" and ");
viter1.nextReference(sql, info);
sql.append(' ').append(operator).append(' ');
viter2.nextReference(sql, info);
}
}
}
protected void atomicSQLValueIF(SQLValueIF value, StringBuilder sql, BuildInfo info) {
// Note: SQLValueIF must have an arity of 1.
switch (value.getType()) {
case SQLValueIF.COLUMNS: {
SQLColumns cols = (SQLColumns)value;
SQLTable table = cols.getTable();
// If table registration flag is set, register table with build info
if (info.register_tables)
info.rtables.add(table);
referenceSQLColumnsColumn(table, cols.getColumns()[0], sql, info);
return;
}
case SQLValueIF.TUPLE:
atomicSQLValueIF(((SQLTuple)value).getValues()[0], sql, info);
return;
case SQLValueIF.PARAMETER: {
SQLParameter param = (SQLParameter)value;
// Register parameter type+info
//! System.out.println("===2> Parameter " + param.getName() + " (index: " + sql.length() + ")");
info.addParameter(param, sql.length());
sql.append('?');
return;
}
case SQLValueIF.PRIMITIVE:
referenceSQLPrimitive((SQLPrimitive)value, sql, info);
return;
case SQLValueIF.NULL:
sql.append("null");
return;
case SQLValueIF.VERBATIM:
sql.append(((SQLVerbatim)value).getValue());
return;
case SQLValueIF.FUNCTION:
referenceSQLFunction((SQLFunction)value, sql, info);
return;
default:
throw new OntopiaRuntimeException("Unsupported SELECT SQLValueIF: '" + value + "' type: " + value.getType());
}
}
protected void escapeString(String value, StringBuilder sql) {
// TODO: optimize this code
char[] chars = value.toCharArray();
for (int i=0; i < chars.length; i++) {
// escape ' and \ characters
if (chars[i] == '\'') sql.append('\'');
if (chars[i] == '\\') sql.append('\\');
sql.append(chars[i]);
}
}
protected void joinSQLValueIF(SQLValueIF value, String separator, StringBuilder sql, BuildInfo info) {
int arity = value.getArity();
// Use first value iterator
ColumnValueIterator viter = info.viter1;
viter.resetValue(value);
viter.nextReference(sql, info);
for (int i=1; i < arity; i++) {
sql.append(separator);
viter.nextReference(sql, info);
}
}
protected int flattenSQLValueIF(SQLValueIF[] values, SQLValueIF[] flatlist, int pos) {
for (int i=0; i < values.length; i++) {
pos = flattenSQLValueIF(values[i], flatlist, pos);
}
return pos;
}
protected int flattenSQLValueIF(SQLValueIF value, SQLValueIF[] flatlist, int pos) {
if (value.getType() == SQLValueIF.TUPLE) {
SQLValueIF[] values = ((SQLTuple)value).getValues();
for (int i=0; i < values.length; i++) {
pos = flattenSQLValueIF(values[i], flatlist, pos);
}
} else {
flatlist[pos] = value;
pos++;
}
return pos;
}
//! protected String[] arraySQLValueIF(SQLValueIF value, BuildInfo info) {
//! switch (value.getType()) {
//! case SQLValueIF.COLUMNS:
//! return arraySQLColumns((SQLColumns)value, info);
//! case SQLValueIF.TUPLE:
//! String[] allrefs = new String[value.getArity()];
//! int pos = 0;
//! SQLValueIF[] values = ((SQLTuple)value).getValues();
//! for (int i=0; i < values.length; i++) {
//! SQLValueIF _val = values[i];
//! //! if (_val.getArity() == 1) {
//! //! allrefs[pos] = atomicSQLValueIF(_val, info);
//! //! pos++;
//! //! } else {
//! String[] refs = arraySQLValueIF(values[i], info);
//! System.arraycopy(refs, 0, allrefs, pos, refs.length);
//! pos += refs.length;
//! //!}
//! }
//! return allrefs;
//! case SQLValueIF.PARAMETER:
//! return arraySQLParameter((SQLParameter)value, info);
//! case SQLValueIF.PRIMITIVE:
//! return new String[] { stringSQLPrimitive((SQLPrimitive)value, info) };
//! case SQLValueIF.NULL:
//! return new String[] { null };
//! default:
//! throw new OntopiaRuntimeException("Unsupported SELECT SQLValueIF: '" + value + "' type: " + value.getType());
//! }
//! }
//! protected String[] arraySQLColumns(SQLColumns columns, BuildInfo info) {
//! SQLTable table = columns.getTable();
//! String[] cols = columns.getColumns();
//!
//! String[] strings = new String[cols.length];
//! for (int i=0; i < cols.length ; i++)
//! strings[i] = stringSQLColumnsColumn(table, cols[i], info);
//!
//! // If table registration flag is set, register table with build info
//! if (info.register_tables)
//! info.rtables.add(table);
//!
//! return strings;
//! }
//! protected void referenceSQLColumns(SQLColumns columns, StringBuilder sql, BuildInfo info) {
//! SQLTable table = columns.getTable();
//! String[] cols = columns.getColumns();
//!
//! String[] strings = new String[cols.length];
//! for (int i=0; i < cols.length ; i++)
//! referenceSQLColumnsColumn(table, cols[i], sql, info);
//!
//! // If table registration flag is set, register table with build info
//! if (info.register_tables)
//! info.rtables.add(table);
//! }
//! protected String[] arraySQLParameter(SQLParameter param, BuildInfo info) {
//! // FIXME: Make sure that this method is not called outside the
//! // where clause and only once per parameter - and in the right
//! // order.
//!
//! // Register parameter type+info
//! info.addParameter(param);
//!
//! int arity = param.getArity();
//! String[] strings = new String[arity];
//! for (int i=0; i < arity ; i++)
//! strings[i] = "?";
//! return strings;
//! }
//! protected String stringSQLPrimitive(SQLPrimitive primitive, BuildInfo info) {
//! switch (primitive.getSQLType()) {
//! case Types.VARCHAR:
//! return "'" + primitive.getValue() + "'";
//! default:
//! Object value = primitive.getValue();
//! return (value == null) ? null : value.toString();
//! }
//! }
// -----------------------------------------------------------------------------
// Primitive values
// -----------------------------------------------------------------------------
protected void referenceSQLPrimitive(SQLPrimitive primitive, StringBuilder sql, BuildInfo info) {
switch (primitive.getSQLType()) {
case Types.VARCHAR:
case Types.LONGVARCHAR:
case Types.CLOB:
sql.append('\'');
escapeString(primitive.getValue().toString(), sql);
sql.append('\'');
return;
default:
sql.append(primitive.getValue());
//! Object value = primitive.getValue();
//! return (value == null) ? null : value.toString();
}
}
// -----------------------------------------------------------------------------
// Tables and columns
// -----------------------------------------------------------------------------
protected void referenceSQLTableAndAlias(SQLTable table, StringBuilder sql, BuildInfo info) {
sql.append(table.getName());
sql.append(' ');
sql.append(table.getAlias());
}
protected void referenceSQLColumnsColumn(SQLTable table, String column, StringBuilder sql, BuildInfo info) {
sql.append(table.getAlias());
sql.append('.');
sql.append(column);
}
//! protected String referenceSQLTable(SQLTable table, BuildInfo info) {
//! return table.getName();
//! }
//! protected String stringSQLColumnsColumn(SQLTable table, String column, BuildInfo info) {
//! return table.getAlias() + "." +column;
//! }
// -----------------------------------------------------------------------------
// Features supported
// -----------------------------------------------------------------------------
public boolean supportsLimitOffset() {
return true;
}
// -----------------------------------------------------------------------------
// Utility methods
// -----------------------------------------------------------------------------
public static SQLGeneratorIF getSQLGenerator(String[] platforms, Map properties) {
// Get the first SQL generator that matches a platform in the list.
for (int i=0; i < platforms.length; i++) {
SQLGeneratorIF sqlgen = getSQLGenerator(platforms[i], properties);
if (sqlgen != null) return sqlgen;
}
throw new OntopiaRuntimeException("No SQL generator could be found for the platforms: " +
Arrays.asList(platforms));
}
public static SQLGeneratorIF getSQLGenerator(String platform, Map properties) {
// ADD: sapdb, firebird
if (platform.equals("generic"))
return new GenericSQLGenerator(properties);
else if (platform.startsWith("oracle"))
return new OracleSQLGenerator(properties);
else if (platform.equals("postgresql"))
return new PostgreSQLGenerator(properties);
else if (platform.equals("sqlserver"))
return new SQLServerSQLGenerator(properties);
else if (platform.equals("mysql"))
return new MySQLGenerator(properties);
else
return null;
}
}