Added support for date types (date, time, timestamp).
With this change, .getMetadata() reports the correct java.sql.Date
type for columns marked as date. The .getDate() on a row also returns a
java.sql.Date object. Since jdbc expects dates to be in the GMT
timezone, the dates are converted first.
diff --git a/pom.xml b/pom.xml
index 73c5b21..51a6e0c 100644
--- a/pom.xml
+++ b/pom.xml
@@ -111,6 +111,11 @@
<version>1.1.7</version>
<scope>test</scope>
</dependency>
+ <dependency>
+ <groupId>org.apache.commons</groupId>
+ <artifactId>commons-lang3</artifactId>
+ <version>3.2</version>
+ </dependency>
</dependencies>
<reporting>
diff --git a/src/main/java/net/hydromatic/optiq/impl/csv/CsvEnumerator.java b/src/main/java/net/hydromatic/optiq/impl/csv/CsvEnumerator.java
index b632b26..f1f3fac 100644
--- a/src/main/java/net/hydromatic/optiq/impl/csv/CsvEnumerator.java
+++ b/src/main/java/net/hydromatic/optiq/impl/csv/CsvEnumerator.java
@@ -21,7 +21,13 @@
import au.com.bytecode.opencsv.CSVReader;
+import org.apache.commons.lang3.time.FastDateFormat;
+
import java.io.*;
+import java.text.ParseException;
+import java.util.Date;
+import java.util.TimeZone;
+
/** Enumerator that reads from a CSV file. */
class CsvEnumerator implements Enumerator<Object> {
@@ -29,6 +35,19 @@
private final RowConverter rowConverter;
private Object current;
+ private static final FastDateFormat TIME_FORMAT_DATE;
+ private static final FastDateFormat TIME_FORMAT_TIME;
+ private static final FastDateFormat TIME_FORMAT_TIMESTAMP;
+
+ static {
+
+ TimeZone gmt = TimeZone.getTimeZone("GMT");
+ TIME_FORMAT_DATE = FastDateFormat.getInstance("yyyy-MM-dd", gmt);
+ TIME_FORMAT_TIME = FastDateFormat.getInstance("hh:mm:ss", gmt);
+ TIME_FORMAT_TIMESTAMP = FastDateFormat.getInstance(
+ "yyyy-MM-dd hh:mm:ss", gmt);
+ }
+
public CsvEnumerator(File file, CsvFieldType[] fieldTypes) {
this(file, fieldTypes, identityList(fieldTypes.length));
}
@@ -131,6 +150,40 @@
return null;
}
return Double.parseDouble(string);
+ case DATE:
+ if (string.length() == 0) {
+ return null;
+ }
+
+ try {
+ Date date = TIME_FORMAT_DATE.parse(string);
+ return new java.sql.Date(date.getTime());
+ } catch (ParseException e) {
+ return null;
+ }
+
+ case TIME:
+ if (string.length() == 0) {
+ return null;
+ }
+
+ try {
+ Date date = TIME_FORMAT_TIME.parse(string);
+ return new java.sql.Time(date.getTime());
+ } catch (ParseException e) {
+ return null;
+ }
+
+ case TIMESTAMP:
+ if (string.length() == 0) {
+ return null;
+ }
+ try {
+ Date date = TIME_FORMAT_TIMESTAMP.parse(string);
+ return new java.sql.Timestamp(date.getTime());
+ } catch (ParseException e) {
+ return null;
+ }
}
}
}
diff --git a/src/main/java/net/hydromatic/optiq/impl/csv/CsvFieldType.java b/src/main/java/net/hydromatic/optiq/impl/csv/CsvFieldType.java
index 1e4569b..12e94d2 100644
--- a/src/main/java/net/hydromatic/optiq/impl/csv/CsvFieldType.java
+++ b/src/main/java/net/hydromatic/optiq/impl/csv/CsvFieldType.java
@@ -34,7 +34,7 @@
* makes it easier to write SQL.</p>
*/
enum CsvFieldType {
- STRING(null, String.class),
+ STRING(null, String.class, "string"),
BOOLEAN(Primitive.BOOLEAN),
BYTE(Primitive.BYTE),
CHAR(Primitive.CHAR),
@@ -43,32 +43,32 @@
LONG(Primitive.LONG),
FLOAT(Primitive.FLOAT),
DOUBLE(Primitive.DOUBLE),
- DATE(null, java.sql.Date.class),
- TIME(null, java.sql.Time.class),
- TIMESTAMP(null, java.sql.Timestamp.class);
+ DATE(null, java.sql.Date.class, "date"),
+ TIME(null, java.sql.Time.class, "time"),
+ TIMESTAMP(null, java.sql.Timestamp.class, "timestamp");
private final Primitive primitive;
private final Class clazz;
+ private final String simpleName;
private static final Map<String, CsvFieldType> MAP =
new HashMap<String, CsvFieldType>();
static {
for (CsvFieldType value : values()) {
- MAP.put(value.clazz.getSimpleName(), value);
- if (value.primitive != null) {
- MAP.put(value.primitive.primitiveClass.getSimpleName(), value);
- }
+ MAP.put(value.simpleName, value);
}
}
CsvFieldType(Primitive primitive) {
- this(primitive, primitive.boxClass);
+ this(primitive, primitive.boxClass,
+ primitive.primitiveClass.getSimpleName());
}
- CsvFieldType(Primitive primitive, Class clazz) {
+ CsvFieldType(Primitive primitive, Class clazz, String simpleName) {
this.primitive = primitive;
this.clazz = clazz;
+ this.simpleName = simpleName;
}
public RelDataType toType(JavaTypeFactory typeFactory) {
diff --git a/src/test/java/net/hydromatic/optiq/test/CsvTest.java b/src/test/java/net/hydromatic/optiq/test/CsvTest.java
index a4749fd..5847c8b 100644
--- a/src/test/java/net/hydromatic/optiq/test/CsvTest.java
+++ b/src/test/java/net/hydromatic/optiq/test/CsvTest.java
@@ -267,6 +267,60 @@
"select empno, slacker from emps where slacker",
"EMPNO=100; SLACKER=true");
}
+
+ @Test
+ public void testDateType() throws SQLException {
+
+ Properties info = new Properties();
+ info.put("model", "target/test-classes/bug.json");
+
+ Connection connection = DriverManager
+ .getConnection("jdbc:optiq:", info);
+
+ try {
+
+ ResultSet res = connection.getMetaData().getColumns(null, null,
+ "DATE", "JOINEDAT");
+ res.next();
+ Assert.assertEquals(res.getInt("DATA_TYPE"), java.sql.Types.DATE);
+
+ res = connection.getMetaData().getColumns(null, null,
+ "DATE", "JOINTIME");
+ res.next();
+ Assert.assertEquals(res.getInt("DATA_TYPE"), java.sql.Types.TIME);
+
+ res = connection.getMetaData().getColumns(null, null,
+ "DATE", "JOINTIMES");
+ res.next();
+ Assert.assertEquals(res.getInt("DATA_TYPE"), java.sql.Types.TIMESTAMP);
+
+ Statement statement = connection.createStatement();
+ ResultSet resultSet = statement.executeQuery(
+ "select \"JOINEDAT\", \"JOINTIME\", \"JOINTIMES\" from \"DATE\" where EMPNO = 100");
+ resultSet.next();
+
+ // date
+ Assert.assertEquals(java.sql.Date.class, resultSet.getDate(1).getClass());
+ Assert.assertEquals(java.sql.Date.valueOf("1996-08-03"),
+ resultSet.getDate(1));
+
+ // time
+ Assert.assertEquals(java.sql.Time.class, resultSet.getTime(2).getClass());
+ Assert.assertEquals(java.sql.Time.valueOf("00:01:02"),
+ resultSet.getTime(2));
+
+ // timestamp
+ Assert.assertEquals(java.sql.Timestamp.class,
+ resultSet.getTimestamp(3).getClass());
+ Assert.assertEquals(java.sql.Timestamp.valueOf("1996-08-03 00:01:02"),
+ resultSet.getTimestamp(3));
+
+ } finally {
+ connection.close();
+ }
+
+
+ }
}
// End CsvTest.java
diff --git a/src/test/resources/bug/DATE.csv b/src/test/resources/bug/DATE.csv
new file mode 100644
index 0000000..ea5ef35
--- /dev/null
+++ b/src/test/resources/bug/DATE.csv
@@ -0,0 +1,6 @@
+EMPNO:int,JOINEDAT:date,JOINTIME:time,JOINTIMES:timestamp
+100,"1996-08-03","00:01:02","1996-08-03 00:01:02"
+110,"2001-01-01","00:00:00","2001-01-01 00:00:00"
+110,"2002-05-03","00:00:00","2002-05-03 00:00:00"
+120,"2005-09-07","00:00:00","2005-09-07 00:00:00"
+130,"2007-01-01","00:00:00","2007-01-01 00:00:00"
