path: root/projects/net.wotonomy.persistence/src/main/java/net/wotonomy/access/EOSQLExpression.java

/*
Wotonomy: OpenStep design patterns for pure Java applications.
Copyright (C) 2000 Michael Powers

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, see http://www.gnu.org
*/
package net.wotonomy.access;

import java.util.Enumeration;

import net.wotonomy.control.EOAndQualifier;
import net.wotonomy.control.EOFetchSpecification;
import net.wotonomy.control.EOKeyComparisonQualifier;
import net.wotonomy.control.EOKeyValueQualifier;
import net.wotonomy.control.EOOrQualifier;
import net.wotonomy.control.EOQualifier;
import net.wotonomy.control.EOSortOrdering;
import net.wotonomy.foundation.NSArray;
import net.wotonomy.foundation.NSData;
import net.wotonomy.foundation.NSDictionary;
import net.wotonomy.foundation.NSKeyValueCoding;
import net.wotonomy.foundation.NSMutableArray;
import net.wotonomy.foundation.NSMutableDictionary;
import net.wotonomy.foundation.NSSelector;
import net.wotonomy.foundation.NSTimestamp;
import net.wotonomy.foundation.NSTimestampFormatter;

/**
 * @author ezamudio@nasoft.com
 * @author $Author: cgruber $
 * @version $Revision: 894 $
 */
public abstract class EOSQLExpression {

	public static final String BindVariableAttributeKey = "BindVariableAttribute";
	public static final String BindVariableColumnKey = "BindVariableColumn";
	public static final String BindVariableNameKey = "BindVariableName";
	public static final String BindVariablePlaceHolderKey = "BindVariablePlaceholder";
	public static final String BindVariableValueKey = "BindVariableValue";
	private static int UseBindings;
	private static final int _DefaultFormatSQLStringLength = 64;
	private static final int _DefaultListStringLength = 256;
	private static final int _DefaultOrderByStringLength = 128;
	private static final int _DefaultPathLength = 128;
	private static final int _DefaultTableListLength = 128;
	protected static final char[] _hexChars = new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B',
			'C', 'D', 'E', 'F' };
	private static final int _ValueLengthLimit = 40;
	protected NSMutableDictionary _aliasesByRelationshipPath;
	protected NSMutableDictionary _aliasesByEntityName;
	protected NSMutableArray _bindings;
	protected NSMutableArray _contextStack;
	protected static NSTimestampFormatter _defaultDateFormatter;
	protected EOEntity _entity;
	protected StringBuffer _joinClauseString;
	protected StringBuffer _listString;
	protected StringBuffer _orderByString;
	protected String _statement;
	protected String _upperFunctionName;
	protected boolean _useAliases = true;
	protected static boolean _quoteExternalNames;
	protected StringBuffer _valueListString;
	protected String _whereClauseString;

	private EOSQLExpression() {
		super();
	}

	public EOSQLExpression(EOEntity entity) {
		super();
		_entity = entity;
	}

	public String _aliasForRelatedAttributeRelationshipPath(EOAttribute a, String path) {
		return null;
	}

	public String _aliasForRelationshipPath(String path) {
		return (String) _aliasesByRelationshipPath.objectForKey(path);
	}

	protected NSTimestampFormatter _defaultDateFormatter() {
		return _defaultDateFormatter;
	}

	protected StringBuffer _listString() {
		if (_listString == null)
			_listString = new StringBuffer();
		return _listString;
	}

	protected StringBuffer _orderByString() {
		if (_orderByString == null)
			_orderByString = new StringBuffer();
		return _orderByString;
	}

	public EOEntity _rootEntityForExpression() {
		return _entity;
	}

	public void _setEntity(EOEntity value) {
		_entity = value;
	}

	public String _sqlStringForJoinSemanticMatchSemantic(int semantic, int match) {
		return null;
	}

	protected String _stringForDate(NSTimestamp timestamp) {
		return null;
	}

	protected StringBuffer _valueList() {
		if (_valueListString == null)
			_valueListString = new StringBuffer();
		return _valueListString;
	}

	public void addBindVariableDictionary(NSDictionary dict) {
	}

	/**
	 * Adds the SQL to create the attribute to the attribute list. The appended text
	 * is of the form attr_name attr_type allow_null
	 * 
	 * @param attr The attribute to create the SQL for.
	 */
	public void addCreateClauseForAttribute(EOAttribute attr) {
		StringBuffer buf = new StringBuffer(attr.columnName());
		buf.append(' ');
		buf.append(columnTypeStringForAttribute(attr));
		buf.append(allowsNullClauseForConstraint(attr.allowsNull()));
		appendItemToListString(buf.toString(), _listString());
	}

	public void addInsertListAttribute(EOAttribute attr, Object o) {
	}

	public void addJoinClause(String left, String right, int semantic) {
		String s = assembleJoinClause(left, right, semantic);
		if (_joinClauseString == null)
			_joinClauseString = new StringBuffer();
		if (_joinClauseString.length() > 0)
			_joinClauseString.append(" AND ");
		_joinClauseString.append(s);
	}

	public void addOrderByAttributeOrdering(EOSortOrdering order) {
		String sql = sqlStringForAttributeNamed(order.key());
		if (order.selector().equals(EOSortOrdering.CompareCaseInsensitiveAscending)
				|| order.selector().equals(EOSortOrdering.CompareCaseInsensitiveDescending))
			sql = "UPPER(" + sql + ")";
		if (order.selector().equals(EOSortOrdering.CompareCaseInsensitiveAscending)
				|| order.selector().equals(EOSortOrdering.CompareAscending))
			sql += " ASC";
		else
			sql += " DESC";
		appendItemToListString(sql, _orderByString());
	}

	public void addSelectListAttribute(EOAttribute attr) {
		appendItemToListString(formatSQLString(sqlStringForAttribute(attr), attr.readFormat()), _listString());
	}

	public void addUpdateListAttribute(EOAttribute attr, Object o) {
		StringBuffer buf = new StringBuffer(attr.columnName());
		buf.append('=');
		buf.append(formatSQLString(formatValueForAttribute(o, attr), attr.writeFormat()));
		appendItemToListString(buf.toString(), _listString());
	}

	public NSMutableDictionary aliasesByRelationshipPath() {
		if (_aliasesByRelationshipPath == null) {
			_aliasesByRelationshipPath = new NSMutableDictionary();
			if (!_useAliases)
				return _aliasesByRelationshipPath;
			_aliasesByRelationshipPath.setObjectForKey("t0", "");
		}
		return _aliasesByRelationshipPath;
	}

	public String allowsNullClauseForConstraint(boolean flag) {
		return flag ? "" : " NOT NULL";
	}

	public void appendItemToListString(String item, StringBuffer list) {
		if (list.length() > 0)
			list.append(", ");
		list.append(item);
	}

	public String assembleDeleteStatementWithQualifier(EOQualifier q, String tableList, String whereClause) {
		String s = "DELETE FROM " + tableList;
		if (whereClause != null && whereClause.length() > 0)
			s += " WHERE " + whereClause;
		return s;
	}

	public String assembleInsertStatementWithRow(NSDictionary row, String tableList, String columnList,
			String valueList) {
		String sql = "INSERT INTO " + tableList;
		if (columnList != null)
			sql += " (" + columnList + ")";
		sql += " VALUES " + valueList;
		return sql;
	}

	public String assembleJoinClause(String leftName, String rightName, int semantic) {
		String op = "=";
		if (semantic == EORelationship.LeftOuterJoin)
			op = "*=";
		else if (semantic == EORelationship.RightOuterJoin)
			op = "=*";
		return leftName + op + rightName;
	}

	public String assembleSelectStatementWithAttributes(NSArray attributes, boolean lock, EOQualifier q,
			NSArray fetchOrder, String selectString, String columnList, String tableList, String whereClause,
			String joinClause, String orderByClause, String lockClause) {
		String sql = selectString + " " + columnList + " FROM " + tableList;
		if (lockClause != null)
			sql += " " + lockClause;
		if (whereClause != null || joinClause != null)
			sql += " WHERE ";
		if (whereClause != null)
			sql += whereClause;
		if (whereClause != null && joinClause != null)
			sql += " AND ";
		if (joinClause != null)
			sql += joinClause;
		if (orderByClause != null)
			sql += " ORDER BY " + orderByClause;
		return sql;
	}

	public String assembleUpdateStatementWithRow(NSDictionary row, EOQualifier q, String tableList, String updateList,
			String whereClause) {
		String s = "UPDATE " + tableList + " SET " + updateList;
		if (whereClause != null && whereClause.length() > 0)
			s += " WHERE " + whereClause;
		return s;
	}

	public NSArray bindVariableDictionaries() {
		return null;
	}

	public abstract NSMutableDictionary bindVariableDictionaryForAttribute(EOAttribute attr, Object o);

	public String columnTypeStringForAttribute(EOAttribute attr) {
		String x = attr.externalType();
		if (attr.precision() > 0) {
			x += " (" + attr.precision() + "," + attr.scale() + ")";
		} else if (attr.width() > 0) {
			x += " (" + attr.width() + ")";
		}
		return x;
	}

	public EOEntity entity() {
		return _entity;
	}

	public String externalNameQuoteCharacter() {
		return "\"";
	}

	public String formatSQLString(String value, String format) {
		if (format == null)
			return value;
		return value;
	}

	/**
	 * Returns the received string wrapped in single quotes, with any quotes or
	 * escape chars found inside it properly escaped.
	 * 
	 * @param s The string to format.
	 */
	public String formatStringValue(String s) {
		StringBuffer buf = new StringBuffer(s);
		for (int i = buf.length() - 1; i >= 0; i--) {
			if (buf.charAt(i) == sqlEscapeChar()) {
				buf.insert(i, sqlEscapeChar());
				i++;
			}
			if (buf.charAt(i) == '\'') {
				buf.insert(i, sqlEscapeChar());
				i++;
			}
		}
		buf.append('\'');
		buf.insert(0, '\'');
		return buf.toString();
	}

	public String formatValueForAttribute(Object value, EOAttribute attr) {
		if (value == null || value == NSKeyValueCoding.NullValue)
			return "NULL";
		if (value instanceof String)
			return formatStringValue((String) value);
		if (value instanceof Number)
			return sqlStringForNumber((Number) value);
		// TODO: format timestamps
		return value.toString();
	}

	public String joinClauseString() {
		if (_joinClauseString == null)
			return null;
		return _joinClauseString.toString();
	}

	public void joinExpression() {
		_joinClauseString = null;
		if (_aliasesByEntityName.count() > 1) {
		}
	}

	public String listString() {
		return _listString().toString();
	}

	public String lockClause() {
		return "";
	}

	public boolean mustUseBindVariableForAttribute(EOAttribute attr) {
		return false;
	}

	public String orderByString() {
		if (_orderByString == null)
			return null;
		return _orderByString.toString();
	}

	public void prepareConstraintStatementForRelationship(EORelationship rel, NSArray arr1, NSArray arr2) {
	}

	public void prepareDeleteExpressionForQualifier(EOQualifier q) {
		String where = null;
		setStatement(assembleDeleteStatementWithQualifier(q, _entity.externalName(), where));
	}

	public void prepareInsertExpressionWithRow(NSDictionary row) {
		StringBuffer cols = new StringBuffer("(");
		StringBuffer values = new StringBuffer("(");
		Enumeration enumeration = row.keyEnumerator();
		while (enumeration.hasMoreElements()) {
			String key = (String) enumeration.nextElement();
			EOAttribute a = _entity.attributeNamed(key);
			cols.append(a.columnName());
			values.append(formatValueForAttribute(row.objectForKey(key), a));
			if (enumeration.hasMoreElements()) {
				cols.append(", ");
				values.append(", ");
			}
		}
		cols.append(")");
		cols.append(")");
		setStatement(assembleInsertStatementWithRow(row, _entity.externalName(), cols.toString(), values.toString()));
	}

	public void prepareSelectExpressionWithAttributes(NSArray atts, boolean lock, EOFetchSpecification fspec) {
		_aliasesByRelationshipPath = new NSMutableDictionary();
		_aliasesByEntityName = new NSMutableDictionary();
		EOQualifier q = null;
		NSArray order = null;
		if (fspec != null) {
			q = fspec.qualifier();
			order = fspec.sortOrderings();
		}
		// Assemble the column list (this yields the alias list)
		for (int i = 0; i < atts.count(); i++)
			addSelectListAttribute((EOAttribute) atts.objectAtIndex(i));
		// assemble the where string
		if (q != null) {
			if (q instanceof EOQualifierSQLGeneration)
				setWhereClauseString(sqlStringForQualifier((EOQualifierSQLGeneration) q));
			else {
				EOQualifierSQLGeneration.Support sup = EOQualifierSQLGeneration.Support.supportForClass(q.getClass());
				setWhereClauseString(sup.sqlStringForSQLExpression(q, this));
			}
		}
		// assemble the join string
		joinExpression();
		// assemble the order by string
		if (order != null && order.count() > 0) {
			for (int i = 0; i < order.count(); i++) {
				EOSortOrdering so = (EOSortOrdering) order.objectAtIndex(i);
				addOrderByAttributeOrdering(so);
			}
		}
		// create the statement
		setStatement(assembleSelectStatementWithAttributes(atts, lock, q, order, "SELECT", listString(),
				tableListWithRootEntity(_entity), whereClauseString(), joinClauseString(), orderByString(),
				lockClause()));
	}

	/** Build an UPDATE statement with the given information. */
	public void prepareUpdateExpressionWithRow(NSDictionary row, EOQualifier q) {
		StringBuffer buf = new StringBuffer();
		Enumeration enumeration = row.keyEnumerator();
		while (enumeration.hasMoreElements()) {
			String key = (String) enumeration.nextElement();
			EOAttribute a = _entity.attributeNamed(key);
			if (a == null)
				throw new EOGeneralAdaptorException(
						"Cannot find attribute named " + key + " in entity " + _entity.name());
			buf.append(a.columnName());
			buf.append('=');
			buf.append(formatValueForAttribute(row.objectForKey(key), a));
			if (enumeration.hasMoreElements())
				buf.append(", ");
		}
		if (q != null) {
			setWhereClauseString(sqlStringForQualifier(null));
		}
		setStatement(
				assembleUpdateStatementWithRow(row, q, _entity.externalName(), buf.toString(), whereClauseString()));
	}

	public void setStatement(String statement) {
		_statement = statement;
	}

	public String statement() {
		return _statement;
	}

	public void setUseAliases(boolean flag) {
		_useAliases = flag;
	}

	public boolean useAliases() {
		return _useAliases;
	}

	public void setUseBindVariables(boolean flag) {
	}

	public boolean useBindVariables() {
		return System.getProperty("EOAdaptorUseBindVariables", "false").equals("true");
	}

	/** @deprecated Check externalNameQuoteCharacter instead. */
	public static void setUseQuotedExternalNames(boolean flag) {
		_quoteExternalNames = flag;
	}

	/** @deprecated Use the instance method externalNameQuoteCharacter instead. */
	public static boolean useQuotedExternalNames() {
		return _quoteExternalNames;
	}

	public void setWhereClauseString(String clause) {
		_whereClauseString = clause;
	}

	public String whereClauseString() {
		return _whereClauseString;
	}

	public boolean shouldUseBindVariableForAttribute(EOAttribute attr) {
		return false;
	}

	public char sqlEscapeChar() {
		return '\\';
	}

	public String sqlPatternFromShellPattern(String pattern) {
		return sqlPatternFromShellPatternWithEscapeCharacter(pattern, sqlEscapeChar());
	}

	public String sqlPatternFromShellPatternWithEscapeCharacter(String pattern, char escape) {
		StringBuffer buf = new StringBuffer(pattern);
		int idx = 0;
		// escape all '%'
		do {
			idx = buf.indexOf("%");
			if (idx == 0)
				buf.insert(escape, 0);
			else if (idx > 0 && buf.charAt(idx - 1) != escape)
				buf.insert(escape, idx);
		} while (idx >= 0);
		// escape all '_'
		do {
			idx = buf.indexOf("_");
			if (idx == 0)
				buf.insert(escape, 0);
			else if (idx > 0 && buf.charAt(idx - 1) != escape)
				buf.insert(escape, idx);
		} while (idx >= 0);
		// substitute all '*'
		do {
			idx = buf.indexOf("*");
			if (idx >= 0)
				buf.replace(idx, idx + 1, "%");
		} while (idx >= 0);
		// substitute all '?'
		do {
			idx = buf.indexOf("?");
			if (idx >= 0)
				buf.replace(idx, idx + 1, "_");
		} while (idx >= 0);
		return buf.toString();
	}

	public String sqlStringForAttribute(EOAttribute attr) {
		if (_aliasesByEntityName == null)
			_aliasesByEntityName = new NSMutableDictionary();
		String alias = (String) _aliasesByEntityName.objectForKey(attr.entity().name());
		if (alias == null) {
			alias = "t" + (_aliasesByEntityName.count() + 1);
			_aliasesByEntityName.setObjectForKey(alias, attr.entity().name());
		}
		if (useAliases())
			return alias + "." + attr.columnName();
		else
			return attr.entity().externalName() + "." + attr.columnName();
	}

	public String sqlStringForAttributeNamed(String name) {
		if (name.indexOf('.') > 0) {
			return sqlStringForAttribute(_entity._attributeForPath(name));
		}
		return sqlStringForAttribute(_entity.attributeNamed(name));

	}

	/**
	 * Returns a string representing the path from the first relationship in the
	 * array to the last one.
	 * 
	 * @param path An array of EORelationship objects.
	 * @return A string consisting of the names of the relationships separated by
	 *         dots.
	 */
	public String sqlStringForAttributePath(NSArray path) {
		StringBuffer buf = new StringBuffer();
		for (int i = 0; i < path.count(); i++) {
			EORelationship rel = (EORelationship) path.objectAtIndex(i);
			if (i > 0)
				buf.append('.');
			buf.append(rel.name());
		}
		return buf.toString();
	}

	public String sqlStringForCaseInsensitiveLike(String key, String value) {
		return "LOWER(" + key + ") LIKE LOWER(" + sqlPatternFromShellPattern(value) + ")";
	}

	public String sqlStringForConjoinedQualifiers(NSArray qualifiers) {
		EOAndQualifier q = new EOAndQualifier(qualifiers);
		return EOQualifierSQLGeneration.Support.supportForClass(EOAndQualifier.class).sqlStringForSQLExpression(q,
				this);
	}

	public String sqlStringForData(NSData data) {
		byte[] b = data.bytes();
		char[] c = new char[b.length * 2];
		int pos = 0;
		for (int i = 0; i < b.length; i++) {
			int x = (b[i] & 0xf0) >> 4;
			c[pos++] = _hexChars[x];
			x = (b[i] & 0x0f);
			c[pos++] = _hexChars[x];
		}
		return new String(c);
	}

	public String sqlStringForDisjoinedQualifiers(NSArray qualifiers) {
		EOOrQualifier q = new EOOrQualifier(qualifiers);
		return EOQualifierSQLGeneration.Support.supportForClass(EOOrQualifier.class).sqlStringForSQLExpression(q, this);
	}

	public String sqlStringForKeyComparisonQualifier(EOKeyComparisonQualifier q) {
		return EOQualifierSQLGeneration.Support.supportForClass(EOKeyComparisonQualifier.class)
				.sqlStringForSQLExpression(q, this);
	}

	public String sqlStringForKeyValueQualifier(EOKeyValueQualifier q) {
		return EOQualifierSQLGeneration.Support.supportForClass(EOKeyValueQualifier.class).sqlStringForSQLExpression(q,
				this);
	}

	public String sqlStringForNegatedQualifier(EOQualifier q) {
		EOQualifierSQLGeneration.Support sup = EOQualifierSQLGeneration.Support.supportForClass(q.getClass());
		String sql = sup.sqlStringForSQLExpression(q, this);
		return "NOT (" + sql + ")";
	}

	public static String sqlStringForNumber(Number number) {
		return number.toString();
	}

	public String sqlStringForQualifier(EOQualifierSQLGeneration sql) {
		return sql.sqlStringForSQLExpression(this);
	}

	public String sqlStringForSchemaObjectName(String name) {
		return name;
	}

	public String sqlStringForSelector(NSSelector sel, Object value) {
		if (sel == EOQualifier.QualifierOperatorEqual) {
			if (value == NSKeyValueCoding.NullValue)
				return " is ";
			return "=";
		} else if (sel == EOQualifier.QualifierOperatorNotEqual) {
			if (value == NSKeyValueCoding.NullValue)
				return " is not ";
			return "<>";
		} else if (sel == EOQualifier.QualifierOperatorLessThan) {
			return "<";
		} else if (sel == EOQualifier.QualifierOperatorGreaterThan) {
			return ">";
		} else if (sel == EOQualifier.QualifierOperatorLessThanOrEqualTo) {
			return "<=";
		} else if (sel == EOQualifier.QualifierOperatorGreaterThanOrEqualTo) {
			return ">=";
		} else if (sel == EOQualifier.QualifierOperatorLike
				|| sel == EOQualifier.QualifierOperatorCaseInsensitiveLike) {
			return " like ";
		}
		return sel.name();
	}

	public static String sqlStringForString(String s) {
		return s;
	}

	public String sqlStringForValue(Object value, String keyPath) {
		EOAttribute a = _entity._attributeForPath(keyPath);
		return formatValueForAttribute(value, a);
	}

	public String tableListWithRootEntity(EOEntity root) {
		StringBuffer buf = new StringBuffer(root.externalName());
		if (useAliases()) {
			buf.append(" ");
			buf.append(_aliasesByEntityName.objectForKey(root.name()));
		}
		if (_aliasesByEntityName.count() > 0) {
			Enumeration enumeration = _aliasesByEntityName.keyEnumerator();
			while (enumeration.hasMoreElements()) {
				String key = (String) enumeration.nextElement();
				if (!key.equals(root.name())) {
					buf.append(", ");
					buf.append(key);
					if (useAliases())
						buf.append(_aliasesByEntityName.objectForKey(key));
				}
			}
		}
		return buf.toString();
	}

	public String toString() {
		return "<" + getClass().getName() + "> " + statement();
	}

	public String valueList() {
		return _valueList().toString();
	}

}
/*
 * $Log$ Revision 1.2 2006/02/16 16:47:14 cgruber Move some classes in to
 * "internal" packages and re-work imports, etc.
 *
 * Also use UnsupportedOperationExceptions where appropriate, instead of
 * WotonomyExceptions.
 *
 * Revision 1.1 2006/02/16 13:19:57 cgruber Check in all sources in
 * eclipse-friendly maven-enabled packages.
 *
 * Revision 1.5 2005/05/11 15:21:53 cgruber Change enum to enumeration, since
 * enum is now a keyword as of Java 5.0
 *
 * A few other comments in the code.
 *
 * Revision 1.4 2003/08/29 21:14:18 chochos fix the algorithm in
 * formatStringValue.
 *
 * Revision 1.3 2003/08/14 02:12:32 chochos implemented use of (simple)
 * qualifiers
 *
 * Revision 1.2 2003/08/13 22:59:39 chochos Can now generate simple one-entity
 * select statements.
 *
 * Revision 1.1 2003/08/13 01:04:32 chochos the SQL generation classes.
 * EOSQLExpression still needs a lot of work, but the factory is pretty much
 * done.
 *
 */