implement a bunch of functions which really need unit tests; add support for calculated field expressions

git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/branches/exprs@1113 f203690c-595d-4dc9-a70b-905162fa7fd2

5 files changed, 334 insertions, 22 deletions

diff --git a/src/main/java/com/healthmarketscience/jackcess/expr/Value.java b/src/main/java/com/healthmarketscience/jackcess/expr/Value.java
index ad0e587..1fd4390 100644
--- a/src/main/java/com/healthmarketscience/jackcess/expr/Value.java
+++ b/src/main/java/com/healthmarketscience/jackcess/expr/Value.java
@@ -34,7 +34,6 @@ public interface Value
     }
 
     public boolean isIntegral() {
-      // note when BOOLEAN is converted to number, it is integral
       return (this == LONG);
     }
 
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 93b4bf2..d1829da 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java
@@ -595,6 +595,10 @@ public class BuiltinOperators
     return new StringValue(s);
   }
 
+  public static Value toValue(int i) {
+    return new LongValue((long)i);
+  }
+
   public static Value toValue(Long s) {
     return new LongValue(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 49fecb7..9abe334 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
@@ -84,7 +84,7 @@ public class DefaultFunctions
   public static abstract class Func0 extends BaseFunction
   {
     protected Func0(String name) {
-      super(name, 0, 9);
+      super(name, 0, 0);
     }
 
     public final Value eval(EvalContext ctx, Value... params) {
@@ -109,6 +109,24 @@ public class DefaultFunctions
     protected abstract Value eval1(EvalContext ctx, Value param);
   }
 
+  public static abstract class Func1NullIsNull extends BaseFunction
+  {
+    protected Func1NullIsNull(String name) {
+      super(name, 1, 1);
+    }
+
+    public final Value eval(EvalContext ctx, Value... params) {
+      validateNumParams(params);
+      Value param1 = params[0];
+      if(param1.isNull()) {
+        return param1;
+      }
+      return eval1(ctx, param1);
+    }
+
+    protected abstract Value eval1(EvalContext ctx, Value param);
+  }
+
   public static abstract class Func2 extends BaseFunction
   {
     protected Func2(String name) {
@@ -138,6 +156,20 @@ public class DefaultFunctions
                                    Value param1, Value param2, Value param3);
   }
 
+  public static abstract class FuncVar extends BaseFunction
+  {
+    protected FuncVar(String name, int minParams, int maxParams) {
+      super(name, minParams, maxParams);
+    }
+
+    public final Value eval(EvalContext ctx, Value... params) {
+      validateNumParams(params);
+      return evalVar(ctx, params);
+    }
+
+    protected abstract Value evalVar(EvalContext ctx, Value[] params);
+  }
+
   public static final Function IIF = registerFunc(new Func3("IIf") {
     @Override
     protected Value eval3(EvalContext ctx, 
@@ -199,12 +231,9 @@ public class DefaultFunctions
     }
   });
 
-  public static final Function HEX = registerStringFunc(new Func1("Hex") {
+  public static final Function HEX = registerStringFunc(new Func1NullIsNull("Hex") {
     @Override
     protected Value eval1(EvalContext ctx, Value param1) {
-      if(param1.isNull()) {
-        return param1;
-      }
       if((param1.getType() == Value.Type.STRING) &&
          (param1.getAsString().length() == 0)) {
         return BuiltinOperators.ZERO_VAL;
@@ -214,9 +243,9 @@ public class DefaultFunctions
     }
   });
 
-  public static final Function NZ = registerFunc(new BaseFunction("Nz", 1, 2) {
-    public Value eval(EvalContext ctx, Value... params) {
-      validateNumParams(params);
+  public static final Function NZ = registerFunc(new FuncVar("Nz", 1, 2) {
+    @Override
+    protected Value evalVar(EvalContext ctx, Value[] params) {
       Value param1 = params[0];
       if(!param1.isNull()) {
         return param1;
@@ -231,12 +260,9 @@ public class DefaultFunctions
     }
   });
 
-  public static final Function OCT = registerStringFunc(new Func1("Oct") {
+  public static final Function OCT = registerStringFunc(new Func1NullIsNull("Oct") {
     @Override
     protected Value eval1(EvalContext ctx, Value param1) {
-      if(param1.isNull()) {
-        return param1;
-      }
       if((param1.getType() == Value.Type.STRING) &&
          (param1.getAsString().length() == 0)) {
         return BuiltinOperators.ZERO_VAL;
@@ -347,7 +373,281 @@ public class DefaultFunctions
   });
 
   // FIXME, CSTR, 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.max(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.max(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 = registerFunc(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 = registerFunc(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 = registerFunc(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 = registerStringFunc(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);
+    } 
+  }
 
   // https://www.techonthenet.com/access/functions/
   // https://support.office.com/en-us/article/Access-Functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/Expressionator.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/Expressionator.java
index 7eaea7a..ec464ec 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/Expressionator.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/Expressionator.java
@@ -58,7 +58,7 @@ public class Expressionator
 
   
   public enum Type {
-    DEFAULT_VALUE, FIELD_VALIDATOR, RECORD_VALIDATOR;
+    DEFAULT_VALUE, EXPRESSION, FIELD_VALIDATOR, RECORD_VALIDATOR;
   }
 
   public interface ParseContext {
@@ -1908,7 +1908,8 @@ public class Expressionator
     public Object eval(EvalContext ctx) {
       switch(_type) {
       case DEFAULT_VALUE:
-        return evalDefault(ctx);
+      case EXPRESSION:
+        return evalValue(ctx);
       case FIELD_VALIDATOR:
       case RECORD_VALIDATOR:
         return evalCondition(ctx);
@@ -1930,7 +1931,7 @@ public class Expressionator
       return _expr.toString();
     }
 
-    private Object evalDefault(EvalContext ctx) {
+    private Object evalValue(EvalContext ctx) {
       Value val = _expr.eval(ctx);
 
       if(val.isNull()) {
diff --git a/src/test/java/com/healthmarketscience/jackcess/TestUtil.java b/src/test/java/com/healthmarketscience/jackcess/TestUtil.java
index 3317c7f..7033f7a 100644
--- a/src/test/java/com/healthmarketscience/jackcess/TestUtil.java
+++ b/src/test/java/com/healthmarketscience/jackcess/TestUtil.java
@@ -383,19 +383,27 @@ public class TestUtil
   {
     // FIXME should really be using commons io FileUtils here, but don't want
     // to add dep for one simple test method
-    byte[] buf = new byte[1024];
     OutputStream ostream = new FileOutputStream(dstFile);
     InputStream istream = new FileInputStream(srcFile);
     try {
-      int numBytes = 0;
-      while((numBytes = istream.read(buf)) >= 0) {
-        ostream.write(buf, 0, numBytes);
-      }
+      copyStream(istream, ostream);
     } finally {
       ostream.close();
     }
   }
 
+  static void copyStream(InputStream istream, OutputStream ostream)
+    throws IOException
+  {
+    // FIXME should really be using commons io FileUtils here, but don't want
+    // to add dep for one simple test method
+    byte[] buf = new byte[1024];
+    int numBytes = 0;
+    while((numBytes = istream.read(buf)) >= 0) {
+      ostream.write(buf, 0, numBytes);
+    }
+  }
+
   static File createTempFile(boolean keep) throws Exception {
     File tmp = File.createTempFile("databaseTest", ".mdb");
     if(keep) {