METAMODEL-1171: Fixed.
authorJakub Neubauer <jakub.neubauer@gmail.com>
Thu, 7 Dec 2017 04:35:12 +0000 (20:35 -0800)
committerKasper Sørensen <i.am.kasper.sorensen@gmail.com>
Thu, 7 Dec 2017 04:35:12 +0000 (20:35 -0800)
Closes #170

CHANGES.md
core/src/main/java/org/apache/metamodel/util/FormatHelper.java
core/src/test/java/org/apache/metamodel/util/FormatHelperTest.java

index cbf22b9..3fd8879 100644 (file)
@@ -1,4 +1,5 @@
  * [METAMODEL-1169] - Fixed issue with SQL Server milliseconds precision in WHERE
+ * [METAMODEL-1171] - Fixed parsing of query operators with DATE, TIME, TIMESTAMP prefix to operand date/time values.
 
 ### Apache MetaModel 5.0
 
index 6eff30f..22efc8e 100644 (file)
@@ -24,6 +24,7 @@ import java.text.DecimalFormatSymbols;
 import java.text.NumberFormat;
 import java.text.ParseException;
 import java.util.Date;
+import java.util.regex.Pattern;
 
 import org.apache.metamodel.query.QueryParameter;
 import org.apache.metamodel.schema.ColumnType;
@@ -32,6 +33,10 @@ import org.apache.metamodel.schema.ColumnType;
  * Helper class for formatting
  */
 public final class FormatHelper {
+    
+    private static Pattern DATE_PATTERN = Pattern.compile("(?:(?:DATE *['(\"])|(?:['(\"]))?([^')\"]*)[')\"]?");
+    private static Pattern TIME_PATTERN = Pattern.compile("(?:(?:TIME *['(\"])|(?:['(\"]))?([^')\"]*)[')\"]?");
+    private static Pattern TIMESTAMP_PATTERN = Pattern.compile("(?:(?:TIMESTAMP *['(\"])|(?:['(\"]))?([^')\"]*)[')\"]?");
 
     /**
      * Creates a uniform number format which is similar to that of eg. Java
@@ -158,11 +163,14 @@ public final class FormatHelper {
         final String[] formats;
         if (columnType.isTimeBased()) {
             if (columnType == ColumnType.DATE) {
+                value = DATE_PATTERN.matcher(value).replaceFirst("$1");
                 formats = new String[] { "yyyy-MM-dd" };
             } else if (columnType == ColumnType.TIME) {
-                formats = new String[] { "HH:mm:ss", "HH:mm" };
+                value = TIME_PATTERN.matcher(value).replaceFirst("$1");
+                formats = new String[] { "HH:mm:ss.SSS", "HH:mm:ss", "HH:mm" };
             } else {
-                formats = new String[] { "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm", "yyyy-MM-dd" };
+                value = TIMESTAMP_PATTERN.matcher(value).replaceFirst("$1");
+                formats = new String[] { "yyyy-MM-dd HH:mm:ss.SSS", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm", "yyyy-MM-dd" };
             }
         } else {
             throw new IllegalArgumentException("Cannot parse time value of type: " + columnType);
index 80cb23e..469f42b 100644 (file)
@@ -20,6 +20,10 @@ package org.apache.metamodel.util;
 
 import java.text.NumberFormat;
 import java.util.Arrays;
+import java.util.Calendar;
+import java.util.Date;
+import java.util.Locale;
+import java.util.TimeZone;
 
 import org.apache.metamodel.schema.ColumnType;
 
@@ -59,4 +63,113 @@ public class FormatHelperTest extends TestCase {
                                FormatHelper.formatSqlValue(null, new Object[] { "foo", 1,
                                                "bar", 0.1234 }));
        }
+
+    public void testParseTimeSqlValue() throws Exception {
+        final Calendar c = Calendar.getInstance(TimeZone.getDefault(), Locale.getDefault());
+
+        c.setTimeInMillis(0);
+        c.set(Calendar.YEAR, 2011);
+        c.set(Calendar.MONTH, Month.JULY.getCalendarConstant());
+        c.set(Calendar.DAY_OF_MONTH, 24);
+        c.set(Calendar.HOUR_OF_DAY, 17);
+        c.set(Calendar.MINUTE, 34);
+        c.set(Calendar.SECOND, 56);
+        final Date timestampFullSeconds = c.getTime();
+        c.set(Calendar.MILLISECOND, 413);
+        final Date timestampFullMillis = c.getTime();
+
+        c.setTimeInMillis(0);
+        c.set(Calendar.YEAR, 2011);
+        c.set(Calendar.MONTH, Month.JULY.getCalendarConstant());
+        c.set(Calendar.DAY_OF_MONTH, 24);
+        c.set(Calendar.HOUR_OF_DAY, 0);
+        c.set(Calendar.MINUTE, 0);
+        c.set(Calendar.SECOND, 0);
+        final Date dateOnly = c.getTime();
+
+        c.setTimeInMillis(0);
+        c.set(Calendar.YEAR, 1970);
+        c.set(Calendar.MONTH, Calendar.JANUARY);
+        c.set(Calendar.DAY_OF_MONTH, 1);
+        c.set(Calendar.HOUR_OF_DAY, 17);
+        c.set(Calendar.MINUTE, 34);
+        c.set(Calendar.SECOND, 56);
+        final Date timeOnlySeconds = c.getTime();
+        c.set(Calendar.MILLISECOND, 413);
+        final Date timeOnlyMillis = c.getTime();
+
+        // Test parsing of formatted date/time values
+        final String dateStr = FormatHelper.formatSqlValue(ColumnType.DATE, timestampFullSeconds);
+        final Date parsedOnlyDate = FormatHelper.parseSqlTime(ColumnType.DATE, dateStr);
+        assertEquals(dateOnly, parsedOnlyDate);
+
+        final String timeStr = FormatHelper.formatSqlValue(ColumnType.TIME, timestampFullSeconds);
+        assertEquals(timeOnlySeconds, FormatHelper.parseSqlTime(ColumnType.TIME, timeStr));
+
+        final String timestampStr = FormatHelper.formatSqlValue(ColumnType.TIMESTAMP, timestampFullSeconds);
+        assertEquals(timestampFullSeconds, FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, timestampStr));
+
+        // Now tests some specific cases
+        assertEquals(dateOnly, FormatHelper.parseSqlTime(ColumnType.DATE, "DATE '2011-07-24'"));
+        assertEquals(dateOnly, FormatHelper.parseSqlTime(ColumnType.DATE, "DATE'2011-07-24'"));
+        assertEquals(dateOnly, FormatHelper.parseSqlTime(ColumnType.DATE, "DATE \"2011-07-24\""));
+        assertEquals(dateOnly, FormatHelper.parseSqlTime(ColumnType.DATE, "DATE\"2011-07-24\""));
+        assertEquals(dateOnly, FormatHelper.parseSqlTime(ColumnType.DATE, "DATE (2011-07-24)"));
+        assertEquals(dateOnly, FormatHelper.parseSqlTime(ColumnType.DATE, "DATE(2011-07-24)"));
+        assertEquals(dateOnly, FormatHelper.parseSqlTime(ColumnType.DATE, "2011-07-24"));
+        assertEquals(dateOnly, FormatHelper.parseSqlTime(ColumnType.DATE, "'2011-07-24'"));
+        assertEquals(dateOnly, FormatHelper.parseSqlTime(ColumnType.DATE, "\"2011-07-24\""));
+
+        assertEquals(timeOnlySeconds, FormatHelper.parseSqlTime(ColumnType.TIME, "TIME '17:34:56'"));
+        assertEquals(timeOnlySeconds, FormatHelper.parseSqlTime(ColumnType.TIME, "TIME'17:34:56'"));
+        assertEquals(timeOnlySeconds, FormatHelper.parseSqlTime(ColumnType.TIME, "TIME \"17:34:56\""));
+        assertEquals(timeOnlySeconds, FormatHelper.parseSqlTime(ColumnType.TIME, "TIME\"17:34:56\""));
+        assertEquals(timeOnlySeconds, FormatHelper.parseSqlTime(ColumnType.TIME, "TIME (17:34:56)"));
+        assertEquals(timeOnlySeconds, FormatHelper.parseSqlTime(ColumnType.TIME, "TIME(17:34:56)"));
+        assertEquals(timeOnlySeconds, FormatHelper.parseSqlTime(ColumnType.TIME, "'17:34:56'"));
+        assertEquals(timeOnlySeconds, FormatHelper.parseSqlTime(ColumnType.TIME, "\"17:34:56\""));
+        assertEquals(timeOnlySeconds, FormatHelper.parseSqlTime(ColumnType.TIME, "17:34:56"));
+        assertEquals(timeOnlyMillis, FormatHelper.parseSqlTime(ColumnType.TIME, "TIME '17:34:56.413'"));
+
+        assertEquals(timestampFullSeconds,
+                FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, "TIMESTAMP '2011-07-24 17:34:56'"));
+        assertEquals(timestampFullSeconds,
+                FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, "TIMESTAMP'2011-07-24 17:34:56'"));
+        assertEquals(timestampFullSeconds,
+                FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, "TIMESTAMP \"2011-07-24 17:34:56\""));
+        assertEquals(timestampFullSeconds,
+                FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, "TIMESTAMP\"2011-07-24 17:34:56\""));
+        assertEquals(timestampFullSeconds,
+                FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, "TIMESTAMP (2011-07-24 17:34:56)"));
+        assertEquals(timestampFullSeconds,
+                FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, "TIMESTAMP(2011-07-24 17:34:56)"));
+        assertEquals(timestampFullSeconds, FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, "'2011-07-24 17:34:56'"));
+        assertEquals(timestampFullSeconds, FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, "\"2011-07-24 17:34:56\""));
+        assertEquals(timestampFullSeconds, FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, "2011-07-24 17:34:56"));
+        assertEquals(timestampFullMillis,
+                FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, "TIMESTAMP '2011-07-24 17:34:56.413'"));
+        assertEquals(dateOnly, FormatHelper.parseSqlTime(ColumnType.TIMESTAMP, "2011-07-24"));
+
+        try {
+            FormatHelper.parseSqlTime(ColumnType.DATE, "XXX '2011-07-24'");
+            fail("should fail");
+        } catch (IllegalArgumentException e) {
+            // OK
+        }
+
+        try {
+            assertEquals(dateOnly, FormatHelper.parseSqlTime(ColumnType.DATE, "TIME '2011-07-24'"));
+            fail("should fail");
+        } catch (IllegalArgumentException e) {
+            // OK
+        }
+
+        try {
+            assertEquals(timestampFullSeconds,
+                    FormatHelper.parseSqlTime(ColumnType.TIME, "TIMESTAMP '2011-07-24 17:34:56'"));
+            fail("should fail");
+        } catch (IllegalArgumentException e) {
+            // OK
+        }
+    }
 }
\ No newline at end of file