From 1f7373e77b7e6487bb480508e32aef31e26e6ec2 Mon Sep 17 00:00:00 2001 From: James Ahlborn Date: Wed, 20 Sep 2017 04:42:59 +0000 Subject: [PATCH] split out function groups; implement numeric functions git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/branches/exprs@1117 f203690c-595d-4dc9-a70b-905162fa7fd2 --- .../jackcess/expr/EvalContext.java | 3 + .../jackcess/impl/expr/BuiltinOperators.java | 4 + .../jackcess/impl/expr/DefaultFunctions.java | 376 ++--------------- .../impl/expr/DefaultNumberFunctions.java | 182 +++++++++ .../impl/expr/DefaultTextFunctions.java | 379 ++++++++++++++++++ .../impl/expr/ExpressionatorTest.java | 18 + 6 files changed, 612 insertions(+), 350 deletions(-) create mode 100644 src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java create mode 100644 src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultTextFunctions.java diff --git a/src/main/java/com/healthmarketscience/jackcess/expr/EvalContext.java b/src/main/java/com/healthmarketscience/jackcess/expr/EvalContext.java index a4e21d1..9f442f1 100644 --- a/src/main/java/com/healthmarketscience/jackcess/expr/EvalContext.java +++ b/src/main/java/com/healthmarketscience/jackcess/expr/EvalContext.java @@ -17,6 +17,7 @@ limitations under the License. package com.healthmarketscience.jackcess.expr; import java.text.SimpleDateFormat; +import java.util.Random; /** * @@ -34,4 +35,6 @@ public interface EvalContext public Value getRowValue(String collectionName, String objName, String colName); + + public Random getRandom(Long seed); } diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java index d1829da..a87ad34 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java @@ -603,6 +603,10 @@ public class BuiltinOperators return new LongValue(s); } + public static Value toValue(float f) { + return new DoubleValue((double)f); + } + public static Value toValue(Double s) { return new DoubleValue(s); } diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java index 969c027..bcefa55 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java @@ -35,7 +35,14 @@ public class DefaultFunctions new HashMap(); private static final char NON_VAR_SUFFIX = '$'; - + static final RoundingMode DEFAULT_ROUND_MODE = RoundingMode.HALF_EVEN; + + static { + // load all default functions + DefaultTextFunctions.init(); + DefaultNumberFunctions.init(); + } + private DefaultFunctions() {} public static Function getFunction(String name) { @@ -48,6 +55,11 @@ public class DefaultFunctions private final int _minParams; private final int _maxParams; + protected BaseFunction(String name) + { + this(name, 0, Integer.MAX_VALUE); + } + protected BaseFunction(String name, int minParams, int maxParams) { _name = name; @@ -75,6 +87,8 @@ public class DefaultFunctions } } + // FIXME, provide context for exceptions thrown + @Override public String toString() { return getName() + "()"; @@ -179,58 +193,6 @@ public class DefaultFunctions } }); - public static final Function ASC = registerFunc(new Func1("Asc") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - String str = param1.getAsString(); - int len = str.length(); - if(len == 0) { - throw new IllegalStateException("No characters in string"); - } - long lv = str.charAt(0); - if((lv < 0) || (lv > 255)) { - throw new IllegalStateException("Character code '" + lv + - "' out of range "); - } - return BuiltinOperators.toValue(lv); - } - }); - - public static final Function ASCW = registerFunc(new Func1("AscW") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - String str = param1.getAsString(); - int len = str.length(); - if(len == 0) { - throw new IllegalStateException("No characters in string"); - } - long lv = str.charAt(0); - return BuiltinOperators.toValue(lv); - } - }); - - public static final Function CHR = registerStringFunc(new Func1("Chr") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - long lv = param1.getAsLong(); - if((lv < 0) || (lv > 255)) { - throw new IllegalStateException("Character code '" + lv + - "' out of range "); - } - char[] cs = Character.toChars((int)lv); - return BuiltinOperators.toValue(new String(cs)); - } - }); - - public static final Function CHRW = registerStringFunc(new Func1("ChrW") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - long lv = param1.getAsLong(); - char[] cs = Character.toChars((int)lv); - return BuiltinOperators.toValue(new String(cs)); - } - }); - public static final Function HEX = registerStringFunc(new Func1NullIsNull("Hex") { @Override protected Value eval1(EvalContext ctx, Value param1) { @@ -272,18 +234,6 @@ public class DefaultFunctions } }); - public static final Function STR = registerStringFunc(new Func1("Str") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - BigDecimal bd = param1.getAsBigDecimal(); - String str = bd.toPlainString(); - if(bd.compareTo(BigDecimal.ZERO) >= 0) { - str = " " + str; - } - return BuiltinOperators.toValue(str); - } - }); - public static final Function CBOOL = registerFunc(new Func1("CBool") { @Override protected Value eval1(EvalContext ctx, Value param1) { @@ -307,7 +257,7 @@ public class DefaultFunctions @Override protected Value eval1(EvalContext ctx, Value param1) { BigDecimal bd = param1.getAsBigDecimal(); - bd = bd.setScale(4, RoundingMode.HALF_EVEN); + bd = bd.setScale(4, DEFAULT_ROUND_MODE); return BuiltinOperators.toValue(bd); } }); @@ -317,7 +267,7 @@ public class DefaultFunctions // protected Value eval1(EvalContext ctx, Value param1) { // FIXME // BigDecimal bd = param1.getAsBigDecimal(); - // bd.setScale(4, RoundingMode.HALF_EVEN); + // bd.setScale(4, DEFAULT_ROUND_MODE); // return BuiltinOperators.toValue(bd); // } // }); @@ -325,8 +275,8 @@ public class DefaultFunctions public static final Function CDBL = registerFunc(new Func1("CDbl") { @Override protected Value eval1(EvalContext ctx, Value param1) { - Double db = param1.getAsDouble(); - return BuiltinOperators.toValue(db); + Double dv = param1.getAsDouble(); + return BuiltinOperators.toValue(dv); } }); @@ -363,11 +313,11 @@ public class DefaultFunctions public static final Function CSNG = registerFunc(new Func1("CSng") { @Override protected Value eval1(EvalContext ctx, Value param1) { - Double db = param1.getAsDouble(); - if((db < Float.MIN_VALUE) || (db > Float.MAX_VALUE)) { - throw new IllegalStateException("Single value '" + db + "' out of range "); + Double dv = param1.getAsDouble(); + if((dv < Float.MIN_VALUE) || (dv > Float.MAX_VALUE)) { + throw new IllegalStateException("Single value '" + dv + "' out of range "); } - return BuiltinOperators.toValue((double)db.floatValue()); + return BuiltinOperators.toValue(dv.floatValue()); } }); @@ -380,297 +330,23 @@ public class DefaultFunctions // FIXME, CVAR - public static final Function INSTR = registerFunc(new FuncVar("InStr", 2, 4) { - @Override - protected Value evalVar(EvalContext ctx, Value[] params) { - int idx = 0; - int start = 0; - if(params.length > 2) { - // 1 based offsets - start = params[0].getAsLong().intValue() - 1; - ++idx; - } - Value param1 = params[idx++]; - if(param1.isNull()) { - return param1; - } - String s1 = param1.getAsString(); - int s1Len = s1.length(); - if(s1Len == 0) { - return BuiltinOperators.ZERO_VAL; - } - Value param2 = params[idx++]; - if(param2.isNull()) { - return param2; - } - String s2 = param2.getAsString(); - int s2Len = s2.length(); - if(s2Len == 0) { - // 1 based offsets - return BuiltinOperators.toValue(start + 1); - } - boolean ignoreCase = true; - if(params.length > 3) { - ignoreCase = doIgnoreCase(params[3]); - } - int end = s1Len - s2Len; - while(start < end) { - if(s1.regionMatches(ignoreCase, start, s2, 0, s2Len)) { - // 1 based offsets - return BuiltinOperators.toValue(start + 1); - } - ++start; - } - return BuiltinOperators.ZERO_VAL; - } - }); - - public static final Function INSTRREV = registerFunc(new FuncVar("InStrRev", 2, 4) { - @Override - protected Value evalVar(EvalContext ctx, Value[] params) { - Value param1 = params[0]; - if(param1.isNull()) { - return param1; - } - String s1 = param1.getAsString(); - int s1Len = s1.length(); - if(s1Len == 0) { - return BuiltinOperators.ZERO_VAL; - } - Value param2 = params[1]; - if(param2.isNull()) { - return param2; - } - String s2 = param2.getAsString(); - int s2Len = s2.length(); - int start = s1Len - 1; - if(s2Len == 0) { - // 1 based offsets - return BuiltinOperators.toValue(start + 1); - } - if(params.length > 2) { - start = params[2].getAsLong().intValue(); - if(start == -1) { - start = s1Len; - } - // 1 based offsets - --start; - } - boolean ignoreCase = true; - if(params.length > 3) { - ignoreCase = doIgnoreCase(params[3]); - } - start = Math.min(s1Len - s2Len, start); - while(start >= 0) { - if(s1.regionMatches(ignoreCase, start, s2, 0, s2Len)) { - // 1 based offsets - return BuiltinOperators.toValue(start + 1); - } - --start; - } - return BuiltinOperators.ZERO_VAL; - } - }); - - public static final Function LCASE = registerStringFunc(new Func1NullIsNull("LCase") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - String str = param1.getAsString(); - return BuiltinOperators.toValue(str.toLowerCase()); - } - }); - - public static final Function UCASE = registerStringFunc(new Func1NullIsNull("UCase") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - String str = param1.getAsString(); - return BuiltinOperators.toValue(str.toUpperCase()); - } - }); - - public static final Function LEFT = registerStringFunc(new Func2("Left") { - @Override - protected Value eval2(EvalContext ctx, Value param1, Value param2) { - if(param1.isNull()) { - return param1; - } - String str = param1.getAsString(); - int len = (int)Math.min(str.length(), param2.getAsLong()); - return BuiltinOperators.toValue(str.substring(0, len)); - } - }); - - public static final Function RIGHT = registerStringFunc(new Func2("Right") { - @Override - protected Value eval2(EvalContext ctx, Value param1, Value param2) { - if(param1.isNull()) { - return param1; - } - String str = param1.getAsString(); - int strLen = str.length(); - int len = (int)Math.min(strLen, param2.getAsLong()); - return BuiltinOperators.toValue(str.substring(strLen - len, strLen)); - } - }); - - public static final Function MID = registerStringFunc(new FuncVar("Mid", 2, 3) { - @Override - protected Value evalVar(EvalContext ctx, Value[] params) { - Value param1 = params[0]; - if(param1.isNull()) { - return param1; - } - String str = param1.getAsString(); - int strLen = str.length(); - // 1 based offsets - int start = (int)Math.max(strLen, params[1].getAsLong() - 1); - int len = Math.max( - ((params.length > 2) ? params[2].getAsLong().intValue() : strLen), - (strLen - start)); - return BuiltinOperators.toValue(str.substring(start, start + len)); - } - }); - - public static final Function LEN = registerFunc(new Func1NullIsNull("Len") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - String str = param1.getAsString(); - return BuiltinOperators.toValue(str.length()); - } - }); - - public static final Function LTRIM = registerStringFunc(new Func1NullIsNull("LTrim") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - String str = param1.getAsString(); - return BuiltinOperators.toValue(trim(str, true, false)); - } - }); - - public static final Function RTRIM = registerStringFunc(new Func1NullIsNull("RTrim") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - String str = param1.getAsString(); - return BuiltinOperators.toValue(trim(str, false, true)); - } - }); - - public static final Function TRIM = registerStringFunc(new Func1NullIsNull("Trim") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - String str = param1.getAsString(); - return BuiltinOperators.toValue(trim(str, true, true)); - } - }); - - public static final Function SPACE = registerStringFunc(new Func1("Space") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - int lv = param1.getAsLong().intValue(); - return BuiltinOperators.toValue(nchars(lv, ' ')); - } - }); - - public static final Function STRCOMP = registerFunc(new FuncVar("StrComp", 2, 3) { - @Override - protected Value evalVar(EvalContext ctx, Value[] params) { - Value param1 = params[0]; - Value param2 = params[1]; - if(param1.isNull() || param2.isNull()) { - return BuiltinOperators.NULL_VAL; - } - String s1 = param1.getAsString(); - String s2 = param2.getAsString(); - boolean ignoreCase = true; - if(params.length > 2) { - ignoreCase = doIgnoreCase(params[2]); - } - int cmp = (ignoreCase ? - s1.compareToIgnoreCase(s2) : s1.compareTo(s2)); - return BuiltinOperators.toValue(cmp); - } - }); - - public static final Function STRING = registerStringFunc(new Func2("String") { - @Override - protected Value eval2(EvalContext ctx, Value param1, Value param2) { - if(param1.isNull() || param2.isNull()) { - return BuiltinOperators.NULL_VAL; - } - int lv = param1.getAsLong().intValue(); - char c = (char)(param2.getAsString().charAt(0) % 256); - return BuiltinOperators.toValue(nchars(lv, c)); - } - }); - - public static final Function STRREVERSE = registerFunc(new Func1("StrReverse") { - @Override - protected Value eval1(EvalContext ctx, Value param1) { - String str = param1.getAsString(); - return BuiltinOperators.toValue( - new StringBuilder(str).reverse().toString()); - } - }); - - - private static String nchars(int num, char c) { - StringBuilder sb = new StringBuilder(num); - for(int i = 0; i < num; ++i) { - sb.append(c); - } - return sb.toString(); - } - - private static String trim(String str, boolean doLeft, boolean doRight) { - int start = 0; - int end = str.length(); - - if(doLeft) { - while((start < end) && (str.charAt(start) == ' ')) { - ++start; - } - } - if(doRight) { - while((start < end) && (str.charAt(end - 1) == ' ')) { - --end; - } - } - return str.substring(start, end); - } - - private static boolean doIgnoreCase(Value paramCmp) { - int cmpType = paramCmp.getAsLong().intValue(); - switch(cmpType) { - case -1: - // vbUseCompareOption -> default is binary - case 0: - // vbBinaryCompare - return false; - case 1: - // vbTextCompare - return true; - default: - // vbDatabaseCompare -> unsupported - throw new IllegalStateException("Unsupported compare type " + cmpType); - } - } private static long roundToLong(Value param) { if(param.getType().isIntegral()) { return param.getAsLong(); } - return param.getAsBigDecimal().setScale(0, RoundingMode.HALF_EVEN) + return param.getAsBigDecimal().setScale(0, DEFAULT_ROUND_MODE) .longValue(); } // https://www.techonthenet.com/access/functions/ // https://support.office.com/en-us/article/Access-Functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83 - private static Function registerFunc(Function func) { + static Function registerFunc(Function func) { return registerFunc(false, func); } - private static Function registerStringFunc(Function func) { + static Function registerStringFunc(Function func) { return registerFunc(true, func); } diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java new file mode 100644 index 0000000..b4b2d26 --- /dev/null +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java @@ -0,0 +1,182 @@ +/* +Copyright (c) 2017 James Ahlborn + +Licensed under the Apache License, Version 2.0 (the "License"); +you may not use this file except in compliance with the License. +You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, software +distributed under the License is distributed on an "AS IS" BASIS, +WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +See the License for the specific language governing permissions and +limitations under the License. +*/ + +package com.healthmarketscience.jackcess.impl.expr; + +import java.math.BigDecimal; + +import com.healthmarketscience.jackcess.expr.EvalContext; +import com.healthmarketscience.jackcess.expr.Function; +import com.healthmarketscience.jackcess.expr.Value; +import static com.healthmarketscience.jackcess.impl.expr.DefaultFunctions.*; + +/** + * + * @author James Ahlborn + */ +public class DefaultNumberFunctions +{ + + private DefaultNumberFunctions() {} + + static void init() { + // dummy method to ensure this class is loaded + } + + public static final Function ABS = registerFunc(new Func1NullIsNull("Abs") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + Value.Type type = param1.getType(); + if(!type.isNumeric()) { + // FIXME how to handle text/date? + // FIXME, cast to number, date as date? + } + if(type.isIntegral()) { + return BuiltinOperators.toValue(Math.abs(param1.getAsLong())); + } + if(type.getPreferredFPType() == Value.Type.DOUBLE) { + return BuiltinOperators.toValue(Math.abs(param1.getAsDouble())); + } + return BuiltinOperators.toValue(param1.getAsBigDecimal().abs()); + } + }); + + public static final Function ATAN = registerFunc(new Func1("Atan") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + Value.Type type = param1.getType(); + if(!type.isNumeric()) { + // FIXME how to handle text/date? + } + return BuiltinOperators.toValue(Math.atan(param1.getAsDouble())); + } + }); + + public static final Function COS = registerFunc(new Func1("Cos") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + Value.Type type = param1.getType(); + if(!type.isNumeric()) { + // FIXME how to handle text/date? + } + return BuiltinOperators.toValue(Math.cos(param1.getAsDouble())); + } + }); + + public static final Function EXP = registerFunc(new Func1("Exp") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + Value.Type type = param1.getType(); + if(!type.isNumeric()) { + // FIXME how to handle text/date? + } + return BuiltinOperators.toValue(Math.exp(param1.getAsDouble())); + } + }); + + public static final Function FIX = registerFunc(new Func1NullIsNull("Fix") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + Value.Type type = param1.getType(); + if(!type.isNumeric()) { + // FIXME how to handle text/date? + } + if(type.isIntegral()) { + return param1; + } + return BuiltinOperators.toValue(param1.getAsDouble().longValue()); + } + }); + + public static final Function INT = registerFunc(new Func1NullIsNull("Int") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + Value.Type type = param1.getType(); + if(!type.isNumeric()) { + // FIXME how to handle text/date? + } + if(type.isIntegral()) { + return param1; + } + return BuiltinOperators.toValue((long)Math.floor(param1.getAsDouble())); + } + }); + + public static final Function RND = registerFunc(new FuncVar("Rnd", 0, 1) { + @Override + public boolean isPure() { + return false; + } + @Override + protected Value evalVar(EvalContext ctx, Value[] params) { + Long seed = ((params.length > 0) ? params[0].getAsLong() : null); + return BuiltinOperators.toValue(ctx.getRandom(seed).nextFloat()); + } + }); + + public static final Function ROUND = registerFunc(new FuncVar("Round", 1, 2) { + @Override + protected Value evalVar(EvalContext ctx, Value[] params) { + Value param1 = params[0]; + if(param1.isNull()) { + return null; + } + if(param1.getType().isIntegral()) { + return param1; + } + int scale = 0; + if(params.length > 1) { + scale = params[1].getAsLong().intValue(); + } + BigDecimal bd = param1.getAsBigDecimal().setScale(scale, DEFAULT_ROUND_MODE); + return BuiltinOperators.toValue(bd); + } + }); + + public static final Function SGN = registerFunc(new Func1NullIsNull("Sgn") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + int signum = 0; + if(param1.getType().isIntegral()) { + long lv = param1.getAsLong(); + signum = ((lv > 0) ? 1 : ((lv < 0) ? -1 : 0)); + } else { + signum = param1.getAsBigDecimal().signum(); + } + return BuiltinOperators.toValue(signum); + } + }); + + public static final Function SQR = registerFunc(new Func1("Sqr") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + double dv = param1.getAsDouble(); + if(dv < 0.0d) { + throw new IllegalStateException("Invalid value '" + dv + "'"); + } + return BuiltinOperators.toValue(Math.sqrt(dv)); + } + }); + + // public static final Function Val = registerFunc(new Func1("Val") { + // @Override + // protected Value eval1(EvalContext ctx, Value param1) { + // // FIXME, maybe leverage ExpressionTokenizer.maybeParseNumberLiteral (note, leading - or + is valid, exponent form is valid) + // } + // }); + + +} diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultTextFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultTextFunctions.java new file mode 100644 index 0000000..278ea4e --- /dev/null +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultTextFunctions.java @@ -0,0 +1,379 @@ +/* +Copyright (c) 2017 James Ahlborn + +Licensed under the Apache License, Version 2.0 (the "License"); +you may not use this file except in compliance with the License. +You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, software +distributed under the License is distributed on an "AS IS" BASIS, +WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +See the License for the specific language governing permissions and +limitations under the License. +*/ + +package com.healthmarketscience.jackcess.impl.expr; + +import java.math.BigDecimal; + +import com.healthmarketscience.jackcess.expr.EvalContext; +import com.healthmarketscience.jackcess.expr.Function; +import com.healthmarketscience.jackcess.expr.Value; +import static com.healthmarketscience.jackcess.impl.expr.DefaultFunctions.*; + +/** + * + * @author James Ahlborn + */ +public class DefaultTextFunctions +{ + + private DefaultTextFunctions() {} + + static void init() { + // dummy method to ensure this class is loaded + } + + public static final Function ASC = registerFunc(new Func1("Asc") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + String str = param1.getAsString(); + int len = str.length(); + if(len == 0) { + throw new IllegalStateException("No characters in string"); + } + long lv = str.charAt(0); + if((lv < 0) || (lv > 255)) { + throw new IllegalStateException("Character code '" + lv + + "' out of range "); + } + return BuiltinOperators.toValue(lv); + } + }); + + public static final Function ASCW = registerFunc(new Func1("AscW") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + String str = param1.getAsString(); + int len = str.length(); + if(len == 0) { + throw new IllegalStateException("No characters in string"); + } + long lv = str.charAt(0); + return BuiltinOperators.toValue(lv); + } + }); + + public static final Function CHR = registerStringFunc(new Func1("Chr") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + long lv = param1.getAsLong(); + if((lv < 0) || (lv > 255)) { + throw new IllegalStateException("Character code '" + lv + + "' out of range "); + } + char[] cs = Character.toChars((int)lv); + return BuiltinOperators.toValue(new String(cs)); + } + }); + + public static final Function CHRW = registerStringFunc(new Func1("ChrW") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + long lv = param1.getAsLong(); + char[] cs = Character.toChars((int)lv); + return BuiltinOperators.toValue(new String(cs)); + } + }); + + public static final Function STR = registerStringFunc(new Func1("Str") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + BigDecimal bd = param1.getAsBigDecimal(); + String str = bd.toPlainString(); + if(bd.compareTo(BigDecimal.ZERO) >= 0) { + str = " " + str; + } + return BuiltinOperators.toValue(str); + } + }); + + public static final Function INSTR = registerFunc(new FuncVar("InStr", 2, 4) { + @Override + protected Value evalVar(EvalContext ctx, Value[] params) { + int idx = 0; + int start = 0; + if(params.length > 2) { + // 1 based offsets + start = params[0].getAsLong().intValue() - 1; + ++idx; + } + Value param1 = params[idx++]; + if(param1.isNull()) { + return param1; + } + String s1 = param1.getAsString(); + int s1Len = s1.length(); + if(s1Len == 0) { + return BuiltinOperators.ZERO_VAL; + } + Value param2 = params[idx++]; + if(param2.isNull()) { + return param2; + } + String s2 = param2.getAsString(); + int s2Len = s2.length(); + if(s2Len == 0) { + // 1 based offsets + return BuiltinOperators.toValue(start + 1); + } + boolean ignoreCase = true; + if(params.length > 3) { + ignoreCase = doIgnoreCase(params[3]); + } + int end = s1Len - s2Len; + while(start < end) { + if(s1.regionMatches(ignoreCase, start, s2, 0, s2Len)) { + // 1 based offsets + return BuiltinOperators.toValue(start + 1); + } + ++start; + } + return BuiltinOperators.ZERO_VAL; + } + }); + + public static final Function INSTRREV = registerFunc(new FuncVar("InStrRev", 2, 4) { + @Override + protected Value evalVar(EvalContext ctx, Value[] params) { + Value param1 = params[0]; + if(param1.isNull()) { + return param1; + } + String s1 = param1.getAsString(); + int s1Len = s1.length(); + if(s1Len == 0) { + return BuiltinOperators.ZERO_VAL; + } + Value param2 = params[1]; + if(param2.isNull()) { + return param2; + } + String s2 = param2.getAsString(); + int s2Len = s2.length(); + int start = s1Len - 1; + if(s2Len == 0) { + // 1 based offsets + return BuiltinOperators.toValue(start + 1); + } + if(params.length > 2) { + start = params[2].getAsLong().intValue(); + if(start == -1) { + start = s1Len; + } + // 1 based offsets + --start; + } + boolean ignoreCase = true; + if(params.length > 3) { + ignoreCase = doIgnoreCase(params[3]); + } + start = Math.min(s1Len - s2Len, start); + while(start >= 0) { + if(s1.regionMatches(ignoreCase, start, s2, 0, s2Len)) { + // 1 based offsets + return BuiltinOperators.toValue(start + 1); + } + --start; + } + return BuiltinOperators.ZERO_VAL; + } + }); + + public static final Function LCASE = registerStringFunc(new Func1NullIsNull("LCase") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + String str = param1.getAsString(); + return BuiltinOperators.toValue(str.toLowerCase()); + } + }); + + public static final Function UCASE = registerStringFunc(new Func1NullIsNull("UCase") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + String str = param1.getAsString(); + return BuiltinOperators.toValue(str.toUpperCase()); + } + }); + + public static final Function LEFT = registerStringFunc(new Func2("Left") { + @Override + protected Value eval2(EvalContext ctx, Value param1, Value param2) { + if(param1.isNull()) { + return param1; + } + String str = param1.getAsString(); + int len = (int)Math.min(str.length(), param2.getAsLong()); + return BuiltinOperators.toValue(str.substring(0, len)); + } + }); + + public static final Function RIGHT = registerStringFunc(new Func2("Right") { + @Override + protected Value eval2(EvalContext ctx, Value param1, Value param2) { + if(param1.isNull()) { + return param1; + } + String str = param1.getAsString(); + int strLen = str.length(); + int len = (int)Math.min(strLen, param2.getAsLong()); + return BuiltinOperators.toValue(str.substring(strLen - len, strLen)); + } + }); + + public static final Function MID = registerStringFunc(new FuncVar("Mid", 2, 3) { + @Override + protected Value evalVar(EvalContext ctx, Value[] params) { + Value param1 = params[0]; + if(param1.isNull()) { + return param1; + } + String str = param1.getAsString(); + int strLen = str.length(); + // 1 based offsets + int start = (int)Math.max(strLen, params[1].getAsLong() - 1); + int len = Math.max( + ((params.length > 2) ? params[2].getAsLong().intValue() : strLen), + (strLen - start)); + return BuiltinOperators.toValue(str.substring(start, start + len)); + } + }); + + public static final Function LEN = registerFunc(new Func1NullIsNull("Len") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + String str = param1.getAsString(); + return BuiltinOperators.toValue(str.length()); + } + }); + + public static final Function LTRIM = registerStringFunc(new Func1NullIsNull("LTrim") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + String str = param1.getAsString(); + return BuiltinOperators.toValue(trim(str, true, false)); + } + }); + + public static final Function RTRIM = registerStringFunc(new Func1NullIsNull("RTrim") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + String str = param1.getAsString(); + return BuiltinOperators.toValue(trim(str, false, true)); + } + }); + + public static final Function TRIM = registerStringFunc(new Func1NullIsNull("Trim") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + String str = param1.getAsString(); + return BuiltinOperators.toValue(trim(str, true, true)); + } + }); + + public static final Function SPACE = registerStringFunc(new Func1("Space") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + int lv = param1.getAsLong().intValue(); + return BuiltinOperators.toValue(nchars(lv, ' ')); + } + }); + + public static final Function STRCOMP = registerFunc(new FuncVar("StrComp", 2, 3) { + @Override + protected Value evalVar(EvalContext ctx, Value[] params) { + Value param1 = params[0]; + Value param2 = params[1]; + if(param1.isNull() || param2.isNull()) { + return BuiltinOperators.NULL_VAL; + } + String s1 = param1.getAsString(); + String s2 = param2.getAsString(); + boolean ignoreCase = true; + if(params.length > 2) { + ignoreCase = doIgnoreCase(params[2]); + } + int cmp = (ignoreCase ? + s1.compareToIgnoreCase(s2) : s1.compareTo(s2)); + return BuiltinOperators.toValue(cmp); + } + }); + + public static final Function STRING = registerStringFunc(new Func2("String") { + @Override + protected Value eval2(EvalContext ctx, Value param1, Value param2) { + if(param1.isNull() || param2.isNull()) { + return BuiltinOperators.NULL_VAL; + } + int lv = param1.getAsLong().intValue(); + char c = (char)(param2.getAsString().charAt(0) % 256); + return BuiltinOperators.toValue(nchars(lv, c)); + } + }); + + public static final Function STRREVERSE = registerFunc(new Func1("StrReverse") { + @Override + protected Value eval1(EvalContext ctx, Value param1) { + String str = param1.getAsString(); + return BuiltinOperators.toValue( + new StringBuilder(str).reverse().toString()); + } + }); + + + private static String nchars(int num, char c) { + StringBuilder sb = new StringBuilder(num); + for(int i = 0; i < num; ++i) { + sb.append(c); + } + return sb.toString(); + } + + private static String trim(String str, boolean doLeft, boolean doRight) { + int start = 0; + int end = str.length(); + + if(doLeft) { + while((start < end) && (str.charAt(start) == ' ')) { + ++start; + } + } + if(doRight) { + while((start < end) && (str.charAt(end - 1) == ' ')) { + --end; + } + } + return str.substring(start, end); + } + + private static boolean doIgnoreCase(Value paramCmp) { + int cmpType = paramCmp.getAsLong().intValue(); + switch(cmpType) { + case -1: + // vbUseCompareOption -> default is binary + case 0: + // vbBinaryCompare + return false; + case 1: + // vbTextCompare + return true; + default: + // vbDatabaseCompare -> unsupported + throw new IllegalStateException("Unsupported compare type " + cmpType); + } + } + + +} diff --git a/src/test/java/com/healthmarketscience/jackcess/impl/expr/ExpressionatorTest.java b/src/test/java/com/healthmarketscience/jackcess/impl/expr/ExpressionatorTest.java index b9b9b22..6475c4e 100644 --- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/ExpressionatorTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/ExpressionatorTest.java @@ -18,6 +18,7 @@ package com.healthmarketscience.jackcess.impl.expr; import java.text.SimpleDateFormat; import java.util.Date; +import java.util.Random; import com.healthmarketscience.jackcess.DatabaseBuilder; import com.healthmarketscience.jackcess.TestUtil; @@ -349,6 +350,9 @@ public class ExpressionatorTest extends TestCase private static final class TestEvalContext implements EvalContext { private final Value _thisVal; + private Random _defRnd; + private Random _rnd; + private long _rndSeed; private TestEvalContext(Value thisVal) { _thisVal = thisVal; @@ -379,5 +383,19 @@ public class ExpressionatorTest extends TestCase String colName) { throw new UnsupportedOperationException(); } + + public Random getRandom(Long seed) { + if(seed == null) { + if(_defRnd == null) { + _defRnd = new Random(System.currentTimeMillis()); + } + return _defRnd; + } + if((_rnd == null) || (seed != _rndSeed)) { + _rndSeed = seed; + _rnd = new Random(_rndSeed); + } + return _rnd; + } } } -- 2.39.5