/* Copyright (C) 2006 Helge Hess This file is part of JOPE. JOPE 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, or (at your option) any later version. JOPE 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 JOPE; see the file COPYING. If not, write to the Free Software Foundation, 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. */ package org.opengroupware.jope.eoaccess; import java.text.CharacterIterator; import java.text.StringCharacterIterator; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.opengroupware.jope.eocontrol.EOAndQualifier; import org.opengroupware.jope.eocontrol.EOCompoundQualifier; import org.opengroupware.jope.eocontrol.EOFetchSpecification; import org.opengroupware.jope.eocontrol.EOKeyComparisonQualifier; import org.opengroupware.jope.eocontrol.EOKeyValueQualifier; import org.opengroupware.jope.eocontrol.EONotQualifier; import org.opengroupware.jope.eocontrol.EOOrQualifier; import org.opengroupware.jope.eocontrol.EOQualifier; import org.opengroupware.jope.eocontrol.EOQualifierVariable; import org.opengroupware.jope.eocontrol.EOSQLQualifier; import org.opengroupware.jope.eocontrol.EOSortOrdering; import org.opengroupware.jope.foundation.NSKeyValueStringFormatter; import org.opengroupware.jope.foundation.NSObject; /* * EOSQLExpression * * This class is used to generate SQL. * * TODO: document * TODO: document raw SQL pattern replacement * TODO: document that adaptors SUBCLASS this to achive proper quoting etc * TODO: document handling of EOSQLQualifier * * The final generation (prepare...) is triggered by the EOAdaptorChannel when * it is asked to perform an adaptor operation. It turns the adaptor operation * into a primary method call (like selectAttributes) which in turn uses the * SQLExpressionFactory to creates and prepares an expression. * * Raw SQL Patterns * ================ * * If the 'EOCustomQueryExpressionHintKey' is set, the value of this key is * processed as a keyvalue-format pattern to produce the SQL. EOSQLExpression * will still prepare and provide the parts of the SQL (eg qualifiers, sorts) * but the assembly will be done using the SQL pattern. * * Example: * SELECT COUNT(*) FROM %(tables)s WHERE %(where)s %(limit)s * * Keys: * select eg SELECT or SELECT DISTINCT * columns eg BASE.lastname, BASE.firstname * tables eg BASE.customer * basetable eg customer * qualifier eg lastname LIKE 'Duck%' * orderings eg lastname ASC, firstname DESC * limit eg OFFSET 0 LIMIT 1 * lock eg FOR UPDATE * joins * Compound: * where eg WHERE lastname LIKE 'Duck%' * andQualifier eg AND lastname LIKE 'Duck%' (nothing w/o qualifier) * orQualifier eg OR lastname LIKE 'Duck%' (nothing w/o qualifier) * * TODO: document * * THREAD: this object is not synchronized. * * Note: its quite inefficient from a String concat perspective. Should use * a StringBuffer, but well, as long as it doesn't hurt ;-) */ public class EOSQLExpression extends NSObject { protected static final Log log = LogFactory.getLog("EOSQLExpression"); protected static boolean isDebugOn = log.isDebugEnabled(); protected EOEntity entity; protected String statement; /* direct SQL */ protected StringBuffer listString; protected StringBuffer valueList; protected List> bindVariableDictionaries; protected boolean useAliases; protected boolean useBindVariables; protected Map aliasesByRelationshipPath; protected Map relationshipsByRelationshipPath; protected String joinClauseString; /* transient state */ protected EOQualifier qualifier; /* constructor */ public EOSQLExpression(EOEntity _entity) { this.entity = _entity; this.useAliases = false; this.useBindVariables = false; if (this.entity != null) { this.aliasesByRelationshipPath = new HashMap(4); this.aliasesByRelationshipPath.put("", "BASE"); } } /* accessors */ public EOEntity entity() { return this.entity; } public void setStatement(String _sql) { this.statement = _sql; } public String statement() { return this.statement; } public String listString() { return this.listString != null ? this.listString.toString() : null; } public String valueList() { return this.valueList != null ? this.valueList.toString() : null; } public boolean useAliases() { return this.useAliases; } public boolean useBindVariables() { return this.useBindVariables; } /* preparation and assembly */ public void prepareDeleteExpressionForQualifier(EOQualifier _q) { this.useAliases = false; this.qualifier = _q; /* where */ String whereClause = this.whereClauseString(); /* table list */ String tables = this.tableListWithRootEntity(this.entity); if (isDebugOn) log.debug(" tables: " + tables); /* assemble */ this.setStatement(this.assembleDeleteStatementWithQualifier (_q, tables, whereClause)); /* tear down */ this.qualifier = null; } public String assembleDeleteStatementWithQualifier (EOQualifier _q, String _tableList, String _whereClause) { return "DELETE FROM " + _tableList + " WHERE " + _whereClause; } public void prepareInsertExpressionWithRow(Map _row) { this.useAliases = false; /* fields and values */ if (_row != null) { for (String key: _row.keySet()) { this.addInsertListAttribute (this.entity.attributeNamed(key), _row.get(key)); } } /* table list */ String tables = this.tableListWithRootEntity(this.entity); if (isDebugOn) log.debug(" tables: " + tables); /* assemble */ this.statement = this.assembleInsertStatementWithRow (_row, tables, this.listString(), this.valueList()); } public String assembleInsertStatementWithRow (Map _row, String _tableList, String _columnList, String _valueList) { StringBuffer sb = new StringBuffer(128); sb.append("INSERT INTO "); sb.append(_tableList); if (_columnList != null) { sb.append(" ( "); sb.append(_columnList); sb.append(" )"); } sb.append(" VALUES ( "); sb.append(_valueList); sb.append(")"); return sb.toString(); } public void prepareUpdateExpressionWithRow (Map _row, EOQualifier _q) { if (_row == null || _row.size() == 0) { log.error("missing row for update ..."); this.statement = null; return; } this.useAliases = false; this.qualifier = _q; /* fields and values */ /* Note: needs to be done _before_ the whereClause, so that the ordering of * the bindings is correct. */ if (_row != null) { for (String key: _row.keySet()) { this.addUpdateListAttribute (this.entity.attributeNamed(key), _row.get(key)); } } /* where */ String whereClause = this.whereClauseString(); /* table list */ String tables = this.tableListWithRootEntity(this.entity); if (isDebugOn) log.debug(" tables: " + tables); /* assemble */ this.statement = this.assembleUpdateStatementWithRow (_row, _q, tables, this.listString(), whereClause); /* tear down */ this.qualifier = null; } public String assembleUpdateStatementWithRow (Map _row, EOQualifier _q, String _tables, String _vals, String _where) { if (_tables == null || _vals == null) return null; StringBuffer sb = new StringBuffer(128); sb.append("UPDATE "); sb.append(_tables); sb.append(" SET "); sb.append(_vals); if (_where != null && _where.length() > 0) { sb.append(" WHERE "); sb.append(_where); } return sb.toString(); } public void prepareSelectExpressionWithAttributes (EOAttribute[] _attrs, boolean _lock, EOFetchSpecification _fspec) { /* check for custom statements */ String customSQL = null; if (_fspec.hints() != null) customSQL = (String)_fspec.hints().get("EOCustomQueryExpressionHintKey"); if (isDebugOn) log.debug("generating SELECT expression .."); this.useAliases = true; this.qualifier = _fspec.qualifier(); /* apply restricting qualifier */ EOQualifier q = this.entity != null ? this.entity.restrictingQualifier() : null; if (q != null) { if (this.qualifier != null) { this.qualifier = new EOAndQualifier(new EOQualifier[] { this.qualifier, q }); } else this.qualifier = q; } if (isDebugOn) log.debug(" qualifier: " + this.qualifier); /* check for distinct */ String select = _fspec.usesDistinct() ? "SELECT DISTINCT" : "SELECT"; /* prepare columns to select */ String columns; if (_attrs != null && _attrs.length > 0) { this.listString = new StringBuffer(128); for (int i = 0; i < _attrs.length; i++) this.addSelectListAttribute(_attrs[i]); columns = this.listString.toString(); this.listString.setLength(0); } else columns = "*"; if (isDebugOn) log.debug(" columns: " + columns); /* prepare where clause (has side effects for joins etc) */ String where = this.whereClauseString(); if (isDebugOn) log.debug(" where: " + where); /* prepare order bys */ EOSortOrdering[] fetchOrder = _fspec.sortOrderings(); String orderBy = null; if (fetchOrder != null && fetchOrder.length > 0) { if (this.listString == null) this.listString = new StringBuffer(64); else this.listString.setLength(0); for (int i = 0; i < fetchOrder.length; i++) this.addOrderByAttributeOrdering(fetchOrder[0]); orderBy = this.listString.toString(); } if (isDebugOn) log.debug(" order: " + orderBy); /* joins, must be done before the tablelist is generated! */ this.joinExpression(); String joinClause = this.joinClauseString(); if (isDebugOn) log.debug(" join: " + joinClause); /* table list */ String tables = this.tableListWithRootEntity(this.entity); if (isDebugOn) log.debug(" tables: " + tables); /* lock */ String lockClause = _lock ? this.lockClause() : null; /* limits */ String limitClause = null; if (_fspec != null) limitClause = this.limitClause(_fspec.fetchOffset(), _fspec.fetchLimit()); // TODO: GROUP BY expression [, ...] // TODO: HAVING condition [, ...] /* we are done, assemble */ if (customSQL != null) { this.statement = assembleCustomSelectStatementWithAttributes (_attrs, _lock, q, fetchOrder, customSQL, select, columns, tables, where, joinClause, orderBy, limitClause, lockClause); } else { this.statement = assembleSelectStatementWithAttributes (_attrs, _lock, q, fetchOrder, select, columns, tables, where, joinClause, orderBy, limitClause, lockClause); } if (isDebugOn) log.debug("result: " + this.statement); } public String assembleSelectStatementWithAttributes (EOAttribute[] _attrs, boolean _lock, EOQualifier _qualifier, EOSortOrdering[] _fetchOrder, String _select, String _cols, String _tables, String _where, String _joinClause, String _orderBy, String _limit, String _lockClause) { StringBuffer sb = new StringBuffer(128); sb.append(_select == null ? "SELECT " : _select); sb.append(' '); sb.append(_cols); if (_tables != null) { sb.append(" FROM "); sb.append(_tables); } if (_where != null && _where.length() == 0) _where = null; if (_joinClause != null && _joinClause.length() == 0) _joinClause = null; if (_where != null || _joinClause != null) { sb.append(" WHERE "); if (_where != null) sb.append(_where); if (_where != null && _joinClause != null) sb.append(" AND "); if (_joinClause != null) sb.append(_joinClause); } if (_orderBy != null && _orderBy.length() > 0) { sb.append(" ORDER BY "); sb.append(_orderBy); } if (_limit != null) { sb.append(' '); sb.append(_limit); } if (_lockClause != null) { sb.append(' '); sb.append(_lockClause); } return sb.toString(); } public String assembleCustomSelectStatementWithAttributes (EOAttribute[] _attrs, boolean _lock, EOQualifier _qualifier, EOSortOrdering[] _fetchOrder, String _sqlPattern, String _select, String _cols, String _tables, String _where, String _joinClause, String _orderBy, String _limit, String _lockClause) { /* * Example: * SELECT COUNT(*) FROM %(tables)s WHERE %(where)s %(limit)s * * Keys: * select eg SELECT or SELECT DISTINCT * columns eg BASE.lastname, BASE.firstname * tables eg BASE.customer * basetable eg customer * qualifier eg lastname LIKE 'Duck%' * orderings eg lastname ASC, firstname DESC * limit eg OFFSET 0 LIMIT 1 * lock eg FOR UPDATE * joins * Compound: * where eg WHERE lastname LIKE 'Duck%' * andQualifier eg AND lastname LIKE 'Duck%' (nothing w/o qualifier) * orQualifier eg OR lastname LIKE 'Duck%' (nothing w/o qualifier) */ if (_sqlPattern == null || _sqlPattern.length() == 0) return null; if (_sqlPattern.indexOf("%") == -1) return _sqlPattern; /* contains no placeholders */ /* consolidate arguments */ if (_select != null && _select.length() == 0) _select = null; if (_cols != null && _cols.length() == 0) _cols = null; if (_tables != null && _tables.length() == 0) _tables = null; if (_where != null && _where.length() == 0) _where = null; if (_joinClause != null && _joinClause.length() == 0) _joinClause = null; if (_orderBy != null && _orderBy.length() == 0) _orderBy = null; if (_limit != null && _limit.length() == 0) _limit = null; if (_lockClause != null && _lockClause.length() == 0) _lockClause = null; /* prepare bindings */ Map bindings = new HashMap(8); if (_select != null) bindings.put("select", _select); if (_cols != null) bindings.put("columns", _cols); if (_tables != null) bindings.put("tables", _tables); if (_where != null) bindings.put("qualifier", _where); if (_joinClause != null) bindings.put("joins", _joinClause); if (_limit != null) bindings.put("limit", _limit); if (_lockClause != null) bindings.put("lock", _lockClause); if (_orderBy != null) bindings.put("orderings", _orderBy); /* adding compounds */ if (_where != null && _joinClause != null) bindings.put("where", " WHERE " + _where + " AND " + _joinClause); else if (_where != null) bindings.put("where", " WHERE " + _where); else if (_joinClause != null) bindings.put("where", " WHERE " + _joinClause); if (_where != null) { bindings.put("andQualifier", " AND " + _where); bindings.put("orQualifier", " OR " + _where); } if (_orderBy != null) bindings.put("orderby", " ORDER BY " + _orderBy); /* some base entity information */ if (this.entity != null) { String s = this.entity.externalName(); if (s != null && s.length() > 0) bindings.put("basetable", s); } /* format */ return NSKeyValueStringFormatter.format(_sqlPattern, bindings, true); } /* column lists */ public void addSelectListAttribute(EOAttribute _attribute) { if (_attribute == null) return; String s = this.sqlStringForAttribute(_attribute); s = this.formatSQLString(s, _attribute.readFormat()); this.appendItemToListString(s, this.listString); } public void addUpdateListAttribute(EOAttribute _attribute, Object _value) { if (_attribute == null) return; /* key */ String a = this.sqlStringForAttribute(_attribute); /* value */ // TODO: why not call sqlStringForValue()? boolean useBind; if (_value != null) { if (_value instanceof EOQualifierVariable) useBind = true; else if (_value instanceof EORawSQLValue) useBind = false; else useBind = this.shouldUseBindVariableForAttribute(_attribute); } else useBind = this.shouldUseBindVariableForAttribute(_attribute); String v; if (useBind) { Map bind = bindVariableDictionaryForAttribute(_attribute, _value); v = bind.get(BindVariablePlaceHolderKey).toString(); this.addBindVariableDictionary(bind); } else if (_value instanceof EORawSQLValue) v = _value.toString(); else v = this.formatValueForAttribute(_value, _attribute); if (_attribute.writeFormat() != null) v = this.formatSQLString(v, _attribute.writeFormat()); /* add to list */ if (this.listString == null) this.listString = new StringBuffer(255); this.appendItemToListString(a + " = " + v, this.listString); } public void addInsertListAttribute(EOAttribute _attribute, Object _value) { if (_attribute == null) return; /* key */ if (this.listString == null) this.listString = new StringBuffer(255); this.appendItemToListString (this.sqlStringForAttribute(_attribute), this.listString); /* value */ // TODO: why not call sqlStringForValue()? boolean useBind; if (_value != null) { if (_value instanceof EOQualifierVariable) useBind = true; else if (_value instanceof EORawSQLValue) useBind = false; else useBind = this.shouldUseBindVariableForAttribute(_attribute); } else useBind = this.shouldUseBindVariableForAttribute(_attribute); String v; if (useBind) { Map bind = bindVariableDictionaryForAttribute(_attribute, _value); v = bind.get(BindVariablePlaceHolderKey).toString(); this.addBindVariableDictionary(bind); } else if (_value instanceof EORawSQLValue) v = _value.toString(); else v = this.formatValueForAttribute(_value, _attribute); if (_attribute.writeFormat() != null) v = this.formatSQLString(v, _attribute.writeFormat()); if (this.valueList == null) this.valueList = new StringBuffer(255); this.appendItemToListString(v, this.valueList); } /* limits */ public String limitClause(int offset, int limit) { if (offset < 1 && limit < 1) return null; if (offset > 0 && limit > 0) return "LIMIT " + limit + " OFFSET " + offset; if (offset > 0) return "OFFSET " + offset; return "LIMIT " + limit; } /* orderings */ public void addOrderByAttributeOrdering(EOSortOrdering _ordering) { if (_ordering == null) return; Object sel = _ordering.selector(); String s = null; if (this.entity != null) { EOAttribute attribute = this.entity.attributeNamed(_ordering.key()); s = this.sqlStringForAttribute(attribute); } else /* raw fetch, just use the key as the SQL name */ s = _ordering.key(); if (sel == EOSortOrdering.EOCompareCaseInsensitiveAscending || sel == EOSortOrdering.EOCompareCaseInsensitiveDescending) { s = this.formatSQLString(s, "UPPER(%P)"); } if (sel == EOSortOrdering.EOCompareCaseInsensitiveAscending || sel == EOSortOrdering.EOCompareAscending) s += " ASC"; else if (sel == EOSortOrdering.EOCompareCaseInsensitiveDescending || sel == EOSortOrdering.EOCompareDescending) s += " DESC"; /* add to list */ this.appendItemToListString(s, this.listString); } /* where clause */ public String whereClauseString() { return this.sqlStringForQualifier(this.qualifier); } /* join clause */ public String joinClauseString() { /* this is set by calling joinExpression */ return this.joinClauseString; } public String tableListWithRootEntity(EOEntity _entity) { if (_entity == null) return null; StringBuffer sb = new StringBuffer(128); if (this.useAliases) { sb.append(this.sqlStringForSchemaObjectName(_entity.externalName())); sb.append(" AS "); sb.append(this.aliasesByRelationshipPath.get("")); for (String relPath: this.aliasesByRelationshipPath.keySet()) { if ("".equals(relPath)) continue; sb.append(", "); EORelationship rel = this.relationshipsByRelationshipPath.get(relPath); String tableName = rel.destinationEntity().externalName(); sb.append(this.sqlStringForSchemaObjectName(tableName)); sb.append(" AS "); sb.append(this.aliasesByRelationshipPath.get(relPath)); } } else { // TODO: just add all table names ... sb.append(this.sqlStringForSchemaObjectName(_entity.externalName())); } return sb.toString(); } public void joinExpression() { Map aliases = this.aliasesByRelationshipPath(); if (aliases == null) return; if (aliases.size() < 2) return; /* only the base entity */ boolean debugOn = log.isDebugEnabled(); for (String relPath: aliases.keySet()) { if ("".equals(relPath)) continue; /* root entity */ EORelationship rel = this.relationshipsByRelationshipPath.get(relPath); EOJoin[] joins = rel.joins(); if (joins == null) continue; /* nothing to do */ int idx = relPath.lastIndexOf('.'); String lastRelPath = idx == -1 ? "" : relPath.substring(0, idx); /* calculate prefixes */ String leftAlias, rightAlias; if (this.useAliases) { leftAlias = aliases.get(lastRelPath); rightAlias = aliases.get(relPath); if (debugOn) { log.debug("process join, relPath: " + relPath + " left " + leftAlias + " right " + rightAlias); } } else { leftAlias = rel.entity().externalName(); leftAlias = this.sqlStringForSchemaObjectName(leftAlias); rightAlias = rel.destinationEntity().externalName(); rightAlias = this.sqlStringForSchemaObjectName(leftAlias); } /* add joins */ for (EOJoin join: joins) { String left, right; //left = join.sourceAttribute().name(); //right = join.destinationAttribute().name(); left = this.sqlStringForAttribute(join.sourceAttribute(), lastRelPath); right = this.sqlStringForAttribute(join.destinationAttribute(),relPath); this.addJoinClause(left, right, rel.joinSemantic()); } } } public void addJoinClause(String _left, String _right, int _semantic) { String jc = assembleJoinClause(_left, _right, _semantic);; if (this.joinClauseString == null || this.joinClauseString.length() == 0) this.joinClauseString = jc; else this.joinClauseString += " AND " + jc; } public String assembleJoinClause(String _left, String _right, int _semantic) { // TODO: semantic String op = " = "; switch (_semantic) { case EORelationship.InnerJoin: op = " = "; break; case EORelationship.LeftOuterJoin: op = " *= "; break; case EORelationship.RightOuterJoin: op = " =* "; break; case EORelationship.FullOuterJoin: op = " *=* "; break; } return _left + op + _right; } /* basic construction */ public void appendItemToListString(String _item, StringBuffer _sb) { if (_sb.length() > 0) _sb.append(", "); _sb.append(_item); } /* formatting */ public String formatSQLString(String _sql, String _format) { if (_format == null) return _sql; if (_format.indexOf('%') == -1) /* contains now formats */ return _sql; // TODO: any other formats? what about %%P (escaped %) return _format.replace("%P", _sql); } public String formatStringValue(String _v) { // TODO: whats the difference to sqlStringForString? if (_v == null) return "NULL"; return "'" + escape(_v) + "'"; } public String sqlStringForString(String _v) { if (_v == null) return "NULL"; return "'" + escape(_v) + "'"; } public String sqlStringForNumber(Number _v) { if (_v == null) return "NULL"; return _v.toString(); } public String formatDateValue(Date _v, EOAttribute _attr) { // TODO: fixme return _v.toString(); } protected String formatValue(Object _v) { // own method for basic stuff if (_v == null) return "NULL"; if (_v instanceof String) return this.formatStringValue((String)_v); if (_v instanceof Number) return this.sqlStringForNumber((Number)_v); if (_v instanceof Date) return this.formatDateValue((Date)_v, null); if (_v instanceof EORawSQLValue) return _v.toString(); return this.formatStringValue(_v.toString()); } public String formatValueForAttribute(Object _value, EOAttribute _attr) { if (_attr == null) return this.formatValue(_value); // TODO: do something with the attribute ... // Note: read formats are applied later on return this.formatValue(_value); } public String sqlStringForValue(Object _value, String _keyPath) { if (_value != null && _value instanceof EORawSQLValue) return _value.toString(); EOAttribute attribute = (this.entity != null) ? this.entity.attributeNamed(_keyPath) : null; boolean useBind = (_value != null && _value instanceof EOQualifierVariable) ? true : this.shouldUseBindVariableForAttribute(attribute); if (useBind) { Map bind = bindVariableDictionaryForAttribute(attribute, _value); if (bind == null) { log.error("could not create bind for keypath: " + _keyPath + " (entity=" + this.entity + ", attribute=" + attribute + ", value=" + _value + ")"); return null; } this.addBindVariableDictionary(bind); return bind.get(BindVariablePlaceHolderKey).toString(); } return this.formatValueForAttribute(_value, attribute); } /* bind variables */ public boolean mustUseBindVariableForAttribute(EOAttribute _attr) { return false; } public boolean shouldUseBindVariableForAttribute(EOAttribute _attr) { if (this.mustUseBindVariableForAttribute(_attr)) return true; /* Hm, any reasons NOT to use binds? Actually yes, prepared statements are * slower if the query is used just once. However, its quite likely that * model based fetches reuse the same kind of query a LOT. So using binds * and caching the prepared statements makes quite some sense. * * Further, for JDBC this ensures that our basetypes are properly escaped, * we don't need to take care of that (eg handling the various Date types). */ return true; } public Map bindVariableDictionaryForAttribute (EOAttribute _attribute, Object _value) { Map bind = new HashMap(4); if (_attribute != null) bind.put(BindVariableAttributeKey, _attribute); if (_value != null) bind.put(BindVariableValueKey, _value); // TODO: check whether this '?' key is correct, I think so (might be JDBC // specific) bind.put(BindVariablePlaceHolderKey, "?"); /* generate and add a variable name */ String name; if (_value != null && _value instanceof EOQualifierVariable) { name = ((EOQualifierVariable)_value).key(); } else { name = _attribute != null ? _attribute.columnName() : "RAW"; if (this.bindVariableDictionaries != null) name += this.bindVariableDictionaries.size(); else name += "1"; } bind.put(BindVariableNameKey, name); return bind; } public void addBindVariableDictionary(Map _dict) { if (this.bindVariableDictionaries == null) this.bindVariableDictionaries = new ArrayList>(4); this.bindVariableDictionaries.add(_dict); } public List> bindVariableDictionaries() { return this.bindVariableDictionaries; } /* attributes */ public String sqlStringForAttributeNamed(String _name) { if (_name == null) return null; /* Note: this implies that attribute names may not contain dots, which * might be an issue with user generated tables. */ if (_name.indexOf('.') != -1) { /* its a keypath */ if (log.isDebugEnabled()) log.debug("gen SQL for keypath: " + _name); return this.sqlStringForAttributePath(_name.split("\\.")); } if (this.entity == null) return _name; /* just reuse the name for model-less operation */ EOAttribute attribute = this.entity.attributeNamed(_name); if (attribute == null) { if (log.isInfoEnabled()) log.info("could not lookup attribute in model: " + _name); return _name; } /* Note: this already adds the table alias */ return this.sqlStringForAttribute(attribute); } public String sqlStringForAttribute(EOAttribute _attr, String _relPath) { // TODO: this does not exist in WO, not sure how its supposed to work, // maybe we should also maintain an _attr=>relPath map? (probably // doesn't work because it can be used in many pathes) if (_attr == null) return null; // TODO: We need to support aliases. In this case the one for the // root entity? String s = _attr.valueForSQLExpression(this); if (this.useAliases) s = this.aliasesByRelationshipPath.get(_relPath) + "." + s; return s; } public String sqlStringForAttribute(EOAttribute _attr) { return this.sqlStringForAttribute(_attr, "" /* relship path, BASE */); } public String sqlStringForAttributePath(String[] _path) { if (_path == null || _path.length == 0) return null; if (_path.length == 1) return this.sqlStringForAttributeNamed(_path[0]); if (this.entity == null) { /* can't process relationships w/o entity */ log.warn("cannot process attribute pathes w/o an entity: " + Arrays.asList(_path)); return null; } boolean debugOn = log.isDebugEnabled(); if (debugOn) log.debug("gen SQL for attr path: " + Arrays.asList(_path)); /* setup relationship cache */ if (this.relationshipsByRelationshipPath == null) { this.relationshipsByRelationshipPath = new HashMap(_path.length); } /* sideeffect: fill aliasesByRelationshipPath */ String relPath = null; String alias = null; EORelationship rel = this.entity.relationshipNamed(_path[0]); if (rel == null) { log.warn("did not find relationship " + _path[0] + " in entity: " + this.entity); } for (int i = 0; i < _path.length - 1; i++) { if (debugOn) log.debug(" path component: " + _path[i]); if (_path[i].length() == 0) { /* invalid path segment */ log.warn("pathes contains an invalid path segment (..): " + Arrays.asList(_path)); continue; } if (relPath == null) relPath = _path[0]; else relPath += "." + _path[i]; if (debugOn) log.debug(" rel path: " + relPath); /* find EORelationship */ EORelationship nextRel = this.relationshipsByRelationshipPath.get(relPath); if (nextRel == null) { if (debugOn) log.debug(" rel not yet cached: " + relPath); /* not yet cached */ EOEntity de = (i == 0) ? this.entity : rel.destinationEntity(); if (de == null) { log.error("did not find entity of relationship " + relPath + ": " + rel); nextRel = rel = null; break; } nextRel = rel = de.relationshipNamed(_path[i]); if (rel == null) log.error("did not find relationship " + _path[i] + " in: " + de); this.relationshipsByRelationshipPath.put(relPath, rel); } /* find alias */ alias = this.aliasesByRelationshipPath.get(relPath); if (alias != null) { /* we already have an alias */ if (debugOn) log.debug(" existing alias: " + alias + " => "+relPath); continue; } if (this.useAliases) { /* produce an alias */ if (_path[i].startsWith("to") && _path[i].length() > 2) { /* eg: toCustomer => Cu" */ alias = _path[i].substring(2); } else alias = "T"; if (this.aliasesByRelationshipPath.containsValue(alias)) { /* search for next ID */ String balias = alias; for (int cnt = 1; cnt < 100 /* limit */; cnt++) { alias = balias + i; if (!this.aliasesByRelationshipPath.containsValue(alias)) break; alias = balias; } } } else alias = rel.destinationEntity().name; // TODO: check whether its correct if (debugOn) log.debug(" new alias: '" + alias + "' => " + relPath); this.aliasesByRelationshipPath.put(relPath, alias); } /* lookup attribute in last relationship */ EOEntity ae = rel != null ? rel.destinationEntity() : null; EOAttribute attribute = ae != null ? ae.attributeNamed(_path[_path.length - 1]) : null; if (attribute == null) { log.warn("did not find attribute " + _path[_path.length - 1] + " in relationship " + rel + " entity: " + ae); } /* OK, we should now have an alias */ return this.sqlStringForAttribute(attribute, relPath); } /* database SQL */ public String externalNameQuoteCharacter() { /* char used to quote identifiers, eg backtick for MySQL! */ return "\""; } public String sqlStringForSchemaObjectName(String _name) { if (_name == null) return null; if ("*".equals(_name)) return "*"; /* maye not be quoted, not an ID */ String q = this.externalNameQuoteCharacter(); if (q == null) return _name; // TODO: escape quote-char return q + _name + q; } public String lockClause() { return "FOR UPDATE"; /* this is PostgreSQL 8.1 */ } /* qualifiers */ public String sqlStringForSelector (EOQualifier.ComparisonOperation _op, Object _value, boolean _allowNull) { /* Note: when used with key-comparison, the value is null! */ // TODO: fix equal to for that case! boolean useNullVariant = _value == null && _allowNull; switch (_op) { case EQUAL_TO: return !useNullVariant ? "=" : "IS"; case NOT_EQUAL_TO: return !useNullVariant ? "<>" : "IS NOT"; case GREATER_THAN: return ">"; case GREATER_THAN_OR_EQUAL: return ">="; case LESS_THAN: return "<"; case LESS_THAN_OR_EQUAL: return "<="; case CONTAINS: return "IN"; case LIKE: return "LIKE"; case CASE_INSENSITIVE_LIKE: return "LIKE"; /* overridden by PostgreSQL */ default: return null; } } public String sqlStringForQualifier(EOQualifier _q) { // TODO: do we need EOQualifierSQLGeneration? if (_q == null) return null; if (_q instanceof EONotQualifier) { return this.sqlStringForNegatedQualifier (((EONotQualifier)_q).qualifier()); } if (_q instanceof EOKeyValueQualifier) return this.sqlStringForKeyValueQualifier((EOKeyValueQualifier)_q); if (_q instanceof EOKeyComparisonQualifier) { return this.sqlStringForKeyComparisonQualifier ((EOKeyComparisonQualifier)_q); } if (_q instanceof EOAndQualifier) { return this.sqlStringForConjoinedQualifiers (((EOCompoundQualifier)_q).qualifiers()); } if (_q instanceof EOOrQualifier) { return this.sqlStringForDisjoinedQualifiers (((EOCompoundQualifier)_q).qualifiers()); } if (_q instanceof EOSQLQualifier) return this.sqlStringForRawQualifier((EOSQLQualifier)_q); // TODO: improve System.err.println("unsupported qualifier: " + _q); return null; } public String sqlStringForRawQualifier(EOSQLQualifier _q) { if (_q == null) return null; // TODO: Do something inside the parts? Pattern replacement? Object[] parts = _q.parts(); if (parts == null) return null; if (parts.length == 0) return ""; StringBuffer sb = new StringBuffer(256); for (int i = 0; i < parts.length; i++) { if (parts[i] == null) { log.warn("SQL qualifier contains a null part ..."); continue; } if (parts[i] instanceof EOQualifierVariable) log.warn("SQL qualifier contains a variable: " + parts[i]); sb.append(parts[i]); } return sb.toString(); } public String sqlStringForNegatedQualifier(EOQualifier _q) { String qs = this.sqlStringForQualifier(_q); if (qs == null || qs.length() == 0) return null; return "NOT ( " + qs + " )"; } public String sqlStringForKeyValueQualifier(EOKeyValueQualifier _q) { if (_q == null) return null; String k = _q.key(); EOAttribute a = this.entity != null ? this.entity.attributeNamed(k) :null; Object v = _q.value(); StringBuffer sb = new StringBuffer(64); String s; /* generate lhs */ if ((s = this.sqlStringForAttributeNamed(k)) == null) { log.warn("got no SQL string for attribute of LHS " + k + ": " + _q); return null; } if (a != null) s = this.formatSQLString(s, a.readFormat()); if (s == null) { log.warn("formatting attribute with read format returned null: "+a); return null; } // TODO: unless the DB supports a specific case-search LIKE, we need // to perform an upper sb.append(s); sb.append(" "); /* generate operator */ // TODO: do something about caseInsensitiveLike (TO_UPPER?), though in // PostgreSQL and MySQL this is already covered by special operators EOQualifier.ComparisonOperation opsel = _q.operation(); String op = this.sqlStringForSelector(opsel, v, true); sb.append(op); /* generate value */ sb.append(" "); if (op.startsWith("IS") && v == null) { /* IS NULL or IS NOT NULL */ sb.append("NULL"); } else { if (v != null) { if (opsel == EOQualifier.ComparisonOperation.LIKE || opsel == EOQualifier.ComparisonOperation.CASE_INSENSITIVE_LIKE) { // TODO: unless the DB supports a specific case-search LIKE, we need // to perform an upper v = this.sqlPatternFromShellPattern(v.toString()); } } sb.append(this.sqlStringForValue(v, k)); } return sb.toString(); } public String sqlStringForKeyComparisonQualifier(EOKeyComparisonQualifier _q){ if (_q == null) return null; EOAttribute a; String lhs = _q.leftKey(); String rhs = _q.rightKey(); StringBuffer sb = new StringBuffer(64); String s; /* generate lhs */ s = this.sqlStringForAttributeNamed(lhs); a = this.entity != null ? this.entity.attributeNamed(lhs) : null; if (a != null) s = this.formatSQLString(s, a.readFormat()); sb.append(s); sb.append(" "); /* generate operator */ // TODO: do something about caseInsensitiveLike (TO_UPPER?) sb.append(this.sqlStringForSelector(_q.operation(), null, false)); /* generate rhs */ sb.append(" "); s = this.sqlStringForAttributeNamed(rhs); a = this.entity != null ? this.entity.attributeNamed(rhs) : null; if (a != null) s = this.formatSQLString(s, a.readFormat()); sb.append(s); return sb.toString(); } public String sqlStringForJoinedQualifiers(EOQualifier[] _qs, String _op) { if (_qs == null || _qs.length == 0) return null; if (_qs.length == 1) return this.sqlStringForQualifier(_qs[0]); StringBuffer sb = new StringBuffer(256); for (int i = 0; i < _qs.length; i++) { String s = this.sqlStringForQualifier(_qs[i]); if (s == null || s.length() == 0) continue; /* do not add empty qualifiers as per doc */ if (sb.length() > 0) sb.append(_op); sb.append("( "); sb.append(s); sb.append(" )"); } return sb.toString(); } public String sqlStringForConjoinedQualifiers(EOQualifier[] _qs) { return this.sqlStringForJoinedQualifiers(_qs, " AND "); } public String sqlStringForDisjoinedQualifiers(EOQualifier[] _qs) { return this.sqlStringForJoinedQualifiers(_qs, " OR "); } public String sqlPatternFromShellPattern(String _pattern) { if (_pattern == null) return null; // System.err.println("FIXUP PATTERN: " + _pattern); String s = _pattern.replace("%", "\\%"); s = s.replace("*", "%"); s = s.replace("_", "\\_"); s = s.replace("?", "_"); // System.err.println("DID: " + s); return s; } /* aliases */ public Map aliasesByRelationshipPath() { return this.aliasesByRelationshipPath; } /* DDL */ public void addCreateClauseForAttribute(EOAttribute _attribute) { if (_attribute == null) return; if (this.listString == null) this.listString = new StringBuffer(1024); /* separator */ if (this.listString.length() > 0) this.listString.append(",\n"); /* column name */ String s = _attribute.columnName(); if (s == null) s = _attribute.name(); this.listString.append(this.sqlStringForSchemaObjectName(s)); this.listString.append(" "); /* column type */ this.listString.append(this.columnTypeStringForAttribute(_attribute)); /* constraints */ /* Note: we do not add primary keys, done in a separate step */ s = this.allowsNullClauseForConstraint(_attribute.allowsNull()); if (s != null && s.length() > 0) this.listString.append(s); } public String columnTypeStringForAttribute(EOAttribute _attr) { if (_attr == null) return null; String extType = _attr.externalType(); if (extType == null) { // TODO: derive ext-type automagically log.warn("attribute has no column type"); } if (_attr.hasPrecision()) return extType + "(" + _attr.precision() + "," + _attr.width() + ")"; if (_attr.hasWidth()) return extType + "(" + _attr.width() + ")"; return extType; } public String allowsNullClauseForConstraint(boolean _allowNull) { return _allowNull ? " NULL" : " NOT NULL"; } /* values */ public static interface SQLValue { public String valueForSQLExpression(EOSQLExpression _context); } /* constants */ public static final String BindVariableAttributeKey = "BindVariableAttributeKey"; public static final String BindVariablePlaceHolderKey = "BindVariablePlaceHolderKey"; public static final String BindVariableColumnKey = "BindVariableColumnKey"; public static final String BindVariableNameKey = "BindVariableNameKey"; public static final String BindVariableValueKey = "BindVariableValueKey"; /* description */ public void appendAttributesToDescription(StringBuffer _d) { super.appendAttributesToDescription(_d); if (this.entity != null) _d.append(" entity=" + this.entity); } /* escaping */ public static String escape(String _value) { if (_value == null) return null; if (_value.length() == 0) return ""; StringCharacterIterator localParser = new StringCharacterIterator(_value); // TODO: better use some FastStringBuffer (unsynchronized) StringBuffer buffer = new StringBuffer(_value.length()); for (char c = localParser.current(); c != CharacterIterator.DONE; c = localParser.next()) { if (c == '\'') { buffer.append('\''); buffer.append('\''); } else if (c == '\\') { buffer.append('\\'); buffer.append('\\'); } else buffer.append(c); } return buffer.toString (); } }