Re: svn commit: r948440 - in /ofbiz/trunk/framework/entity: dtd/entitymodel.xsd src/org/ofbiz/entity/jdbc/DatabaseUtil.java src/org/ofbiz/entity/model/ModelIndex.java

[Adam Heath-2]

[hidden email] wrote:
> Author: doogie
> Date: Wed May 26 14:22:21 2010
> New Revision: 948440
>
> URL: http://svn.apache.org/viewvc?rev=948440&view=rev
> Log:
> Add support for UPPER and LOWER functions in index fields.

<extend-entity entity-name="Person">
 <index name="perLowerFirstName">
  <index-field name="firstName" function="lower"/>
 </index>
 <index name="perLowerMiddleName">
  <index-field name="middleName" function="lower"/>
 </index>
 <index name="perLowerLastName">
  <index-field name="lastName" function="lower"/>
 </index>
</extend-entity>

So that when an EntityCondition does a comparison against one of those
fields, but converts it to lowercase, the database can still do an
index lookup, instead of a sequential scan.

The sql is: CREATE INDEX perLowerFirstName ON public.PERSON
(LOWER(FIRST_NAME));

> Modified:
>     ofbiz/trunk/framework/entity/dtd/entitymodel.xsd
>     ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java
>     ofbiz/trunk/framework/entity/src/org/ofbiz/entity/model/ModelIndex.java
>
> Modified: ofbiz/trunk/framework/entity/dtd/entitymodel.xsd
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/dtd/entitymodel.xsd?rev=948440&r1=948439&r2=948440&view=diff
> ==============================================================================
> --- ofbiz/trunk/framework/entity/dtd/entitymodel.xsd (original)
> +++ ofbiz/trunk/framework/entity/dtd/entitymodel.xsd Wed May 26 14:22:21 2010
> @@ -202,6 +202,14 @@ under the License.
>      </xs:element>
>      <xs:attributeGroup name="attlist.index-field">
>          <xs:attribute name="name" type="xs:string" use="required"/>
> +        <xs:attribute name="function">
> +            <xs:simpleType>
> +                <xs:restriction base="xs:token">
> +                    <xs:enumeration value="lower"/>
> +                    <xs:enumeration value="upper"/>
> +                </xs:restriction>
> +            </xs:simpleType>
> +        </xs:attribute>
>      </xs:attributeGroup>
>      
>      <!-- ================== view-entity ===================== -->
>
> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java?rev=948440&r1=948439&r2=948440&view=diff
> ==============================================================================
> --- ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java (original)
> +++ ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/DatabaseUtil.java Wed May 26 14:22:21 2010
> @@ -2800,16 +2800,23 @@ public class DatabaseUtil {
>      }
>  
>      public String makeIndexClause(ModelEntity entity, ModelIndex modelIndex) {
> -        Iterator<String> fieldNamesIter = modelIndex.getIndexFieldsIterator();
> +        Iterator<ModelIndex.Field> fieldsIter = modelIndex.getFieldsIterator();
>          StringBuilder mainCols = new StringBuilder();
>  
> -        while (fieldNamesIter.hasNext()) {
> -            String fieldName = fieldNamesIter.next();
> -            ModelField mainField = entity.getField(fieldName);
> +        while (fieldsIter.hasNext()) {
> +            ModelIndex.Field field = fieldsIter.next();
> +            ModelIndex.Function function = field.getFunction();
>              if (mainCols.length() > 0) {
>                  mainCols.append(", ");
>              }
> +            if (function != null) {
> +                mainCols.append(function.toString()).append('(');
> +            }
> +            ModelField mainField = entity.getField(field.getFieldName());
>              mainCols.append(mainField.getColName());
> +            if (function != null) {
> +                mainCols.append(')');
> +            }
>          }
>  
>          StringBuilder indexSqlBuf = new StringBuilder("CREATE ");
>
> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/model/ModelIndex.java
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/model/ModelIndex.java?rev=948440&r1=948439&r2=948440&view=diff
> ==============================================================================
> --- ofbiz/trunk/framework/entity/src/org/ofbiz/entity/model/ModelIndex.java (original)
> +++ ofbiz/trunk/framework/entity/src/org/ofbiz/entity/model/ModelIndex.java Wed May 26 14:22:21 2010
> @@ -27,7 +27,9 @@ import org.w3c.dom.Element;
>  import org.w3c.dom.NodeList;
>  
>  import org.ofbiz.base.util.StringUtil;
> +import org.ofbiz.base.util.UtilValidate;
>  import org.ofbiz.base.util.UtilXml;
> +import org.ofbiz.base.util.collections.IteratorWrapper;
>  
>  /**
>   * Generic Entity - Relation model class
> @@ -42,7 +44,7 @@ public class ModelIndex extends ModelChi
>      protected boolean unique;
>  
>      /** list of the field names included in this index */
> -    protected List<String> fieldNames = new ArrayList<String>();
> +    protected List<Field> fields = new ArrayList<Field>();
>  
>      /** Default Constructor */
>      public ModelIndex() {
> @@ -71,7 +73,8 @@ public class ModelIndex extends ModelChi
>  
>              if (indexFieldElement.getParentNode() == indexElement) {
>                  String fieldName = indexFieldElement.getAttribute("name").intern();
> -                this.fieldNames.add(fieldName);
> +                String function = indexFieldElement.getAttribute("function");
> +                this.fields.add(new Field(fieldName, UtilValidate.isNotEmpty(function) ? Function.valueOf(function.toUpperCase()) : null));
>              }
>          }
>      }
> @@ -94,24 +97,45 @@ public class ModelIndex extends ModelChi
>          this.unique = unique;
>      }
>  
> +    /** @deprecated use getFieldsIterator() */
> +    @Deprecated
>      public Iterator<String> getIndexFieldsIterator() {
> -        return this.fieldNames.iterator();
> +        return new IteratorWrapper<String, Field>(this.fields.iterator()) {
> +            protected void noteRemoval(String dest, Field src) {
> +            }
> +
> +            protected String convert(Field src) {
> +                return src.getFieldName();
> +            }
> +        };
> +    }
> +
> +    public Iterator<Field> getFieldsIterator() {
> +        return this.fields.iterator();
>      }
>  
>      public int getIndexFieldsSize() {
> -        return this.fieldNames.size();
> +        return this.fields.size();
>      }
>  
>      public String getIndexField(int index) {
> -        return this.fieldNames.get(index);
> +        return this.fields.get(index).getFieldName();
>      }
>  
>      public void addIndexField(String fieldName) {
> -        this.fieldNames.add(fieldName);
> +        this.fields.add(new Field(fieldName, null));
> +    }
> +
> +    public void addIndexField(String fieldName, String functionName) {
> +        this.fields.add(new Field(fieldName, Function.valueOf(functionName)));
> +    }
> +
> +    public void addIndexField(String fieldName, Function function) {
> +        this.fields.add(new Field(fieldName, function));
>      }
>  
>      public String removeIndexField(int index) {
> -        return this.fieldNames.remove(index);
> +        return this.fields.remove(index).getFieldName();
>      }
>  
>      public Element toXmlElement(Document document) {
> @@ -121,12 +145,43 @@ public class ModelIndex extends ModelChi
>              root.setAttribute("unique", "true");
>          }
>  
> -        for (String fieldName: this.fieldNames) {
> +        for (Field field: this.fields) {
>              Element fn = document.createElement("index-field");
> -            fn.setAttribute("name", fieldName);
> +            fn.setAttribute("name", field.getFieldName());
> +            if (field.getFunction() != null) {
> +                fn.setAttribute("function", field.getFunction().toString());
> +            }
>              root.appendChild(fn);
>          }
>  
>          return root;
>      }
> +
> +    public static final class Field {
> +        private final String fieldName;
> +        private final Function function;
> +
> +        public Field(String fieldName, Function function) {
> +            this.fieldName = fieldName;
> +            this.function = function;
> +        }
> +
> +        public String getFieldName() {
> +            return this.fieldName;
> +        }
> +
> +        public Function getFunction() {
> +            return function;
> +        }
> +
> +        public String toString() {
> +            if (function == null) {
> +                return fieldName;
> +            } else {
> +                return function.toString() + '(' + fieldName + ')';
> +            }
> +        }
> +    }
> +
> +    public enum Function { LOWER, UPPER }
>  }
>
>