Source Code Cross Referenced for FunctionValueConstraint.java in  » Database-ORM » MMBase » org » mmbase » storage » search » Java Source Code / Java DocumentationJava Source Code and Java Documentation

001:        /*
002:
003:        This software is OSI Certified Open Source Software.
004:        OSI Certified is a certification mark of the Open Source Initiative.
005:
006:        The license (Mozilla version 1.0) can be read at the MMBase site.
007:        See http://www.MMBase.org/license
008:
009:         */
010:        package org.mmbase.storage.search;
011:
012:        import org.mmbase.storage.search.FieldCompareConstraint;
013:
014:        /**
015:         * This class can solve the following.
016:
017:         = PROBLEM ==
018:
019:         The following query will be fired upon the database when somebody
020:         tries to login:
021:
022:         8000ms:
023:         SELECT otype,owner,number,firstname,account,lastname,email,description,password
024:         FROM vpro4_users users WHERE lowerEmail(email)='<USER>' AND lowerEmail(password)='<PASSWORD>'
025:
026:         The lower-function is slowing down the login-procedure, because the lower-function
027:         will force a sequential-scan.
028:
029:         So an functional index should be used to query the table, but informix can't put an index
030:         on a table with a function which is not variant;
031:
032:         -= SOLUTION =-
033:
034:         Use a wrapper to facilitate the variant version of lower and use this to query the database.
035:         Squirrel-the-database-client seems to have a problem with these kinds of queries; use the
036:         utility classes in cinema-importers -> importer -> CreateProcedure
037:
038:         - create an notvariant function of lower:
039:         javac CreateProcedure.java && java -cp /usr/local/SQuirreL\ SQL\ Client/lib/ifxjdbc.jar:. CreateProcedure
040:
041:         CREATE FUNCTION lowerNotVariant(field VARCHAR(255))
042:         RETURNING VARCHAR(255) WITH (NOT VARIANT);
043:         RETURN LOWER(field);
044:         END FUNCTION;
045:
046:         - set an index on the field to be queried:
047:         CREATE INDEX vpro4_users_email_lower on vpro4_users(lowerNotVaraint(email));
048:
049:         - now query the table with full-speed:
050:
051:         33ms: SELECT otype,owner,number,firstname,account,lastname,email,description,password
052:         FROM vpro4_users users WHERE lowerNotVariant(email)='<USER>' AND lowerNotVariant(password)='<PASSWORD>'
053:         README.txt (END)
054:         *
055:         * @author Marcel Maatkamp
056:         * @version $Id: FunctionValueConstraint.java,v 1.5 2007/12/06 08:13:36 michiel Exp $
057:         * @since MMBase-1.8.5
058:         */
059:        public interface FunctionValueConstraint extends FieldCompareConstraint {
060:
061:            public String getFunction();
062:
063:            /**
064:             * Gets the value to compare with.
065:             * Depending on the field type, the value is of type
066:             * <code>String</code> or <code>Number</code>.
067:             * <p>
068:             * If the associated field type is of string type, when used in
069:             * combination with the operator <code>LIKE</code>, this may contain the
070:             * following wildcard characters as well:
071:             * <ul>
072:             * <li>% for any string
073:             * <li>_ for a single character
074:             * </ul>
075:             */
076:            Object getValue();
077:
078:            /**
079:             * Returns a string representation of this FunctionValueConstraint.
080:             * The string representation has the form
081:             * "FunctionValueConstraint(inverse:&lt:inverse&gt;, field:&lt;field&gt;,
082:             *  casesensitive:&lt;casesensitive&gt;, operator:&lt;operator&gt;,
083:             *  value:&lt;value&gt;)"
084:             * where
085:             * <ul>
086:             * <li><em>&lt;inverse&gt;</em>is the value returned by
087:             *      {@link #isInverse isInverse()}
088:             * <li><em>&lt;field&gt;</em> is the field alias returned by
089:             *     <code>FieldConstraint#getField().getAlias()</code>, or
090:             *     <code>FieldConstraint#getField().getFieldName()</code>
091:             *      when the former is <code>null</code>.
092:             * <li><em>&lt;casesensitive&gt;</em> is the value returned by
093:             *     {@link FieldConstraint#isCaseSensitive isCaseSensitive()}
094:             * <li><em>&lt;operator&gt;</em> is the value returned by
095:             *     (@link FieldCompareConstraint#getOperator getOperator()}
096:             * <li><em>&lt;value&gt;</em> is the value returned by
097:             *     {@link #getValue getValue()}
098:             * </ul>
099:             *
100:             * @return A string representation of this FunctionValueConstraint.
101:             */
102:            public String toString();
103:
104:        }