/*

 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.

  *

  */