Commits

Grace Batumbya committed 2144734 Merge

Cleaned up date storage patch.
Merge with default (bf677755b00908b1b70fa4ada8210bd924ef8e1b).
Ref #23: getDate() returns wrong values

  • Participants
  • Parent commits 6e43a90, bf67775

Comments (0)

Files changed (7)

 Work in progress:
 
+* Added support for specifying a date storage class. For more info <https://bitbucket.org/xerial/sqlite-jdbc/issue/23/getdate-returns-wrong-values>
+* Issue 23 - getDate() returns wrong values
 * Added support for InputStreams on PreparedStatement
     1. setBinaryStream()
     2. setAsciiStream()

File src/main/java/org/sqlite/Conn.java

 import java.sql.Savepoint;
 import java.sql.Statement;
 import java.sql.Struct;
+import java.text.DateFormat;
+import java.text.SimpleDateFormat;
 import java.util.HashMap;
 import java.util.Map;
 import java.util.Properties;
 
+import org.sqlite.SQLiteConfig.DateClass;
+import org.sqlite.SQLiteConfig.DatePrecision;
 import org.sqlite.SQLiteConfig.TransactionMode;
 
 class Conn implements Connection
         beginCommandMap.put(TransactionMode.EXCLUSIVE, "begin exclusive;");
     }
 
+    /* Date storage configuration */
+    public final DateClass dateClass;
+    public final DatePrecision datePrecision; //Calendar.SECOND or Calendar.MILLISECOND
+    public final long dateMultiplier;
+    public final DateFormat dateFormat;
+
     /**
      * Constructor to create a connection to a database at the given location.
      * @param url The location of the database.
         this.fileName = fileName;
 
         SQLiteConfig config = new SQLiteConfig(prop);
+        this.dateClass = config.dateClass;
+        this.dateMultiplier = config.dateMultiplier;
+        this.dateFormat = new SimpleDateFormat(config.dateStringFormat);
+        this.datePrecision = config.datePrecision;
+        this.transactionMode = config.getTransactionMode();
         this.openModeFlags = config.getOpenModeFlags();
+
         open(openModeFlags);
 
         if (fileName.startsWith("file:") && !fileName.contains("cache="))
         }
         db.enable_load_extension(config.isEnabledLoadExtension());
 
-        this.transactionMode = config.getTransactionMode();
-
         // set pragmas
         config.apply(this);
     }

File src/main/java/org/sqlite/PrepStmt.java

 
 package org.sqlite;
 
-import java.io.ByteArrayOutputStream;
 import java.io.IOException;
 import java.io.InputStream;
-import java.io.OutputStream;
 import java.io.Reader;
 import java.math.BigDecimal;
 import java.sql.Date;
             batch(pos, null);
         }
         else if (value instanceof java.util.Date) {
-            batch(pos, new Long(((java.util.Date) value).getTime()));
+            setDateByMilliseconds(pos, ((java.util.Date) value).getTime());
         }
         else if (value instanceof Date) {
-            batch(pos, new Long(((Date) value).getTime()));
+            setDateByMilliseconds(pos, new Long(((Date) value).getTime()));
         }
         else if (value instanceof Time) {
-            batch(pos, new Long(((Time) value).getTime()));
+            setDateByMilliseconds(pos, new Long(((Time) value).getTime()));
         }
         else if (value instanceof Timestamp) {
-            batch(pos, new Long(((Timestamp) value).getTime()));
+            setDateByMilliseconds(pos, new Long(((Timestamp) value).getTime()));
         }
         else if (value instanceof Long) {
             batch(pos, value);
     }
 
     /**
+    * Store the date in the user's preferred format (text, int, or real)
+    */
+   private void setDateByMilliseconds(int pos, Long value) throws SQLException {
+       switch(conn.dateClass) {
+           case TEXT:
+               batch(pos, conn.dateFormat.format(new Date(value)));
+               break;
+
+           case REAL:
+               // long to Julian date
+               batch(pos, new Double((value/86400000.0) + 2440587.5));
+               break;
+
+           default: //INTEGER:
+               batch(pos, new Long(value / conn.dateMultiplier));
+       }
+   }
+
+   /**
      * @see java.sql.PreparedStatement#setTime(int, java.sql.Time)
      */
     public void setTime(int pos, Time x) throws SQLException {

File src/main/java/org/sqlite/RS.java

 import java.sql.Time;
 import java.sql.Timestamp;
 import java.sql.Types;
+import java.text.DateFormat;
 import java.util.Calendar;
+import java.util.GregorianCalendar;
 import java.util.Locale;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
      */
     public BigDecimal getBigDecimal(int col) throws SQLException {
         final String stringValue = getString(col);
-		if (stringValue == null) {
-			return null;
-		}
-		else {
-			try {
-				return new BigDecimal(stringValue);
-			}
-			catch (NumberFormatException e) {
-				throw new SQLException("Bad value for type BigDecimal : " + stringValue);
-			}
-		}
-	}
+        if (stringValue == null) {
+            return null;
+        }
+        else {
+            try {
+                return new BigDecimal(stringValue);
+            }
+            catch (NumberFormatException e) {
+                throw new SQLException("Bad value for type BigDecimal : " + stringValue);
+            }
+        }
+    }
 
     /**
      * @see java.sql.ResultSet#getBigDecimal(java.lang.String)
      * @see java.sql.ResultSet#getDate(int)
      */
     public Date getDate(int col) throws SQLException {
-        if (db.column_type(stmt.pointer, markCol(col)) == SQLITE_NULL) {
-            return null;
+        switch(db.column_type(stmt.pointer, markCol(col))) {
+            case SQLITE_NULL:
+                return null;
+    
+            case SQLITE_TEXT:
+                try {
+                    return new Date(stmt.conn.dateFormat.parse(db.column_text(stmt.pointer, markCol(col))).getTime());
+                }
+                catch (Exception e) {
+                    SQLException error = new SQLException("Error parsing date");
+                    error.initCause(e);
+
+                    throw error;
+                }
+    
+            case SQLITE_FLOAT:
+                return new Date(julianDateToCalendar(db.column_double(stmt.pointer, markCol(col))).getTimeInMillis());
+    
+            default: //SQLITE_INTEGER:
+                return new Date(db.column_long(stmt.pointer, markCol(col)) * stmt.conn.dateMultiplier);
         }
-        return new Date(db.column_long(stmt.pointer, markCol(col)));
     }
 
     /**
      * @see java.sql.ResultSet#getDate(int, java.util.Calendar)
      */
     public Date getDate(int col, Calendar cal) throws SQLException {
-        if (db.column_type(stmt.pointer, markCol(col)) == SQLITE_NULL) {
-            return null;
+        checkCalendar(cal);
+
+        switch (db.column_type(stmt.pointer, markCol(col))) {
+            case SQLITE_NULL:
+                return null;
+    
+            case SQLITE_TEXT:
+                try {
+                    DateFormat dateFormat = (DateFormat) stmt.conn.dateFormat.clone();
+                    dateFormat.setCalendar(cal);
+
+                    return new java.sql.Date(dateFormat.parse(db.column_text(stmt.pointer, markCol(col))).getTime());
+                }
+                catch (Exception e) {
+                    SQLException error = new SQLException("Error parsing time stamp");
+                    error.initCause(e);
+
+                    throw error;
+                }
+    
+            case SQLITE_FLOAT:
+                return new Date(julianDateToCalendar(db.column_double(stmt.pointer, markCol(col)), cal).getTimeInMillis());
+    
+            default: // SQLITE_INTEGER: 
+                cal.setTimeInMillis(db.column_long(stmt.pointer, markCol(col)) * stmt.conn.dateMultiplier);
+                return new Date(cal.getTime().getTime());
         }
-        if (cal == null) {
-            return getDate(col);
-        }
-        cal.setTimeInMillis(db.column_long(stmt.pointer, markCol(col)));
-        return new Date(cal.getTime().getTime());
     }
 
     /**
      * @see java.sql.ResultSet#getTime(int)
      */
     public Time getTime(int col) throws SQLException {
-        if (db.column_type(stmt.pointer, markCol(col)) == SQLITE_NULL) {
-            return null;
+        switch (db.column_type(stmt.pointer, markCol(col))) {
+            case SQLITE_NULL:
+                return null;
+    
+            case SQLITE_TEXT:
+                try {
+                    return new Time(stmt.conn.dateFormat.parse(db.column_text(stmt.pointer, markCol(col))).getTime());
+                }
+                catch (Exception e) {
+                    SQLException error = new SQLException("Error parsing time");
+                    error.initCause(e);
+
+                    throw error;
+                }
+    
+            case SQLITE_FLOAT:
+                return new Time(julianDateToCalendar(db.column_double(stmt.pointer, markCol(col))).getTimeInMillis());
+    
+            default:// SQLITE_INTEGER
+                return new Time(db.column_long(stmt.pointer, markCol(col)) * stmt.conn.dateMultiplier);
         }
-        return new Time(db.column_long(stmt.pointer, markCol(col)));
     }
 
     /**
      * @see java.sql.ResultSet#getTime(int, java.util.Calendar)
      */
     public Time getTime(int col, Calendar cal) throws SQLException {
-        if (cal == null) {
-            return getTime(col);
+        checkCalendar(cal);
+
+        switch (db.column_type(stmt.pointer, markCol(col))) {
+            case SQLITE_NULL:
+                return null;
+
+            case SQLITE_TEXT:
+                try {
+                    DateFormat dateFormat = (DateFormat) stmt.conn.dateFormat.clone();
+                    dateFormat.setCalendar(cal);
+
+                    return new Time(dateFormat.parse(db.column_text(stmt.pointer, markCol(col))).getTime());
+                }
+                catch (Exception e) {
+                    SQLException error = new SQLException("Error parsing time");
+                    error.initCause(e);
+
+                    throw error;
+                }
+    
+            case SQLITE_FLOAT:
+                return new Time(julianDateToCalendar(db.column_double(stmt.pointer, markCol(col)), cal).getTimeInMillis());
+
+            default: //SQLITE_INTEGER
+                cal.setTimeInMillis(db.column_long(stmt.pointer, markCol(col)) * stmt.conn.dateMultiplier);
+                return new Time(cal.getTime().getTime());
         }
-        if (db.column_type(stmt.pointer, markCol(col)) == SQLITE_NULL) {
-            return null;
-        }
-        cal.setTimeInMillis(db.column_long(stmt.pointer, markCol(col)));
-        return new Time(cal.getTime().getTime());
     }
 
     /**
      * @see java.sql.ResultSet#getTimestamp(int)
      */
     public Timestamp getTimestamp(int col) throws SQLException {
-        if (db.column_type(stmt.pointer, markCol(col)) == SQLITE_NULL) {
-            return null;
+        switch (db.column_type(stmt.pointer, markCol(col))) {
+            case SQLITE_NULL:
+                return null;
+    
+            case SQLITE_TEXT:
+                try {
+                    return new Timestamp(stmt.conn.dateFormat.parse(db.column_text(stmt.pointer, markCol(col))).getTime());
+                }
+                catch (Exception e) {
+                    SQLException error = new SQLException("Error parsing time stamp");
+                    error.initCause(e);
+
+                    throw error;
+                }
+    
+            case SQLITE_FLOAT:
+                return new Timestamp(julianDateToCalendar(db.column_double(stmt.pointer, markCol(col))).getTimeInMillis());
+    
+            default: //SQLITE_INTEGER:
+                return new Timestamp(db.column_long(stmt.pointer, markCol(col)) * stmt.conn.dateMultiplier);
         }
-        return new Timestamp(db.column_long(stmt.pointer, markCol(col)));
     }
 
     /**
         if (cal == null) {
             return getTimestamp(col);
         }
-        if (db.column_type(stmt.pointer, markCol(col)) == SQLITE_NULL) {
-            return null;
+
+        switch (db.column_type(stmt.pointer, markCol(col))) {
+            case SQLITE_NULL:
+                return null;
+    
+            case SQLITE_TEXT:
+                try {
+                    DateFormat dateFormat = (DateFormat)stmt.conn.dateFormat.clone();
+                    dateFormat.setCalendar(cal);
+
+                    return new Timestamp(dateFormat.parse(db.column_text(stmt.pointer, markCol(col))).getTime());
+                }
+                catch (Exception e) {
+                    SQLException error = new SQLException("Error parsing time stamp");
+                    error.initCause(e);
+
+                    throw error;
+                }
+            
+            case SQLITE_FLOAT:
+                return new Timestamp(julianDateToCalendar(db.column_double(stmt.pointer, markCol(col)), cal).getTimeInMillis());
+    
+            default: //SQLITE_INTEGER
+                cal.setTimeInMillis(db.column_long(stmt.pointer, markCol(col)) * stmt.conn.dateMultiplier);
+
+                return new Timestamp(cal.getTime().getTime());
         }
-        cal.setTimeInMillis(db.column_long(stmt.pointer, markCol(col)));
-        return new Timestamp(cal.getTime().getTime());
     }
 
     /**
     public boolean rowUpdated() throws SQLException {
         return false;
     }
+
+    /**
+     * Transforms a Julian Date to java.util.Calendar object.
+     */
+    private Calendar julianDateToCalendar(Double jd) {
+        return julianDateToCalendar(jd, Calendar.getInstance());
+    }
+
+    /**
+     * Transforms a Julian Date to java.util.Calendar object.
+     * Based on Guine Christian's function found here:
+     * http://java.ittoolbox.com/groups/technical-functional/java-l/java-function-to-convert-julian-date-to-calendar-date-1947446
+     */
+    private Calendar julianDateToCalendar(Double jd, Calendar cal) {
+        if (jd == null) {
+            return null;
+        }
+
+        int yyyy, dd, mm, hh, mn, ss, ms , A;
+
+        double w = jd + 0.5;
+        int Z = (int)w;
+        double F = w - Z;
+
+        if (Z < 2299161) {
+            A = Z;
+        }
+        else {
+            int alpha = (int)((Z - 1867216.25) / 36524.25);
+            A = Z + 1 + alpha - (int)(alpha / 4.0);
+        }
+
+        int B = A + 1524;
+        int C = (int)((B - 122.1) / 365.25);
+        int D = (int)(365.25 * C);
+        int E = (int)((B - D) / 30.6001);
+
+        //  month
+        mm = E - ((E < 13.5) ? 1 : 13);
+
+        // year
+        yyyy = C - ((mm > 2.5) ? 4716 : 4715);
+
+        // Day
+        double jjd = B - D - (int)(30.6001 * E) + F;
+        dd = (int)jjd;
+
+        // Hour
+        double hhd = jjd - dd;
+        hh = (int)(24 * hhd);
+
+        // Minutes
+        double mnd = (24 * hhd) - hh;
+        mn = (int)(60 * mnd);
+
+        // Seconds
+        double ssd = (60 * mnd) - mn;
+        ss = (int)(60 * ssd);
+
+        // Milliseconds
+        double msd = (60 * ssd) - ss;
+        ms = (int)(1000 * msd);
+
+        cal.set(yyyy, mm-1, dd, hh, mn, ss);
+        cal.set(Calendar.MILLISECOND, ms);
+
+        if (yyyy<1) {
+            cal.set(Calendar.ERA, GregorianCalendar.BC);
+            cal.set(Calendar.YEAR, -(yyyy-1));
+        }
+
+        return cal;
+    }
+
+    public void checkCalendar(Calendar cal) throws SQLException {
+        if (cal != null)
+            return;
+
+        SQLException e = new SQLException("Expected a calendar instance.");
+        e.initCause(new NullPointerException());
+
+        throw e;
+    }
 }

File src/main/java/org/sqlite/SQLiteConfig.java

  */
 public class SQLiteConfig
 {
+    public final static String DEFAULT_DATE_STRING_FORMAT = "yyyy-MM-dd HH:mm:ss.SSS";
+
     private final Properties pragmaTable;
     private int              openModeFlag = 0x00;
     private TransactionMode  transactionMode; 
 
+    /* Date storage class defaults*/
+    public final DateClass dateClass;
+    public final DatePrecision datePrecision;
+    public final long dateMultiplier;
+    public final String dateStringFormat;
+
     /**
      * Default constructor.
      */
      */
     public SQLiteConfig(Properties prop) {
         this.pragmaTable = prop;
+
         String openMode = pragmaTable.getProperty(Pragma.OPEN_MODE.pragmaName);
         if (openMode != null) {
             openModeFlag = Integer.parseInt(openMode);
         setOpenMode(SQLiteOpenMode.OPEN_URI); // Enable URI filenames
 
         transactionMode = TransactionMode.getMode(
-            prop.getProperty(Pragma.TRANSACTION_MODE.pragmaName, TransactionMode.DEFFERED.name()));
+                pragmaTable.getProperty(Pragma.TRANSACTION_MODE.pragmaName, TransactionMode.DEFFERED.name()));
+
+        dateClass = DateClass.getDateClass(pragmaTable.getProperty(Pragma.DATE_CLASS.pragmaName, DateClass.INTEGER.name()));
+        datePrecision = DatePrecision.getPrecision(pragmaTable.getProperty(Pragma.DATE_PRECISION.pragmaName, DatePrecision.MILLISECONDS.name()));
+        dateMultiplier = (datePrecision == DatePrecision.MILLISECONDS) ? 1L : 1000L;
+        dateStringFormat = pragmaTable.getProperty(Pragma.DATE_STRING_FORMAT.pragmaName, DEFAULT_DATE_STRING_FORMAT);
     }
 
     /**
         pragmaParams.remove(Pragma.OPEN_MODE.pragmaName);
         pragmaParams.remove(Pragma.SHARED_CACHE.pragmaName);
         pragmaParams.remove(Pragma.LOAD_EXTENSION.pragmaName);
+        pragmaParams.remove(Pragma.DATE_PRECISION.pragmaName);
+        pragmaParams.remove(Pragma.DATE_CLASS.pragmaName);
+        pragmaParams.remove(Pragma.DATE_STRING_FORMAT.pragmaName);
 
         Statement stat = conn.createStatement();
         try {
     public Properties toProperties() {
         pragmaTable.setProperty(Pragma.OPEN_MODE.pragmaName, Integer.toString(openModeFlag));
         pragmaTable.setProperty(Pragma.TRANSACTION_MODE.pragmaName, transactionMode.getValue());
+        pragmaTable.setProperty(Pragma.DATE_CLASS.pragmaName, dateClass.getValue());
+        pragmaTable.setProperty(Pragma.DATE_PRECISION.pragmaName, datePrecision.getValue());
+        pragmaTable.setProperty(Pragma.DATE_STRING_FORMAT.pragmaName, dateStringFormat);
 
         return pragmaTable;
     }
 
         // Parameters requiring SQLite3 API invocation
         OPEN_MODE("open_mode", "Database open-mode flag", null),
-        SHARED_CACHE("shared_cache", "Enablse SQLite Shared-Cache mode, native driver only", OnOff),
+        SHARED_CACHE("shared_cache", "Enable SQLite Shared-Cache mode, native driver only", OnOff),
         LOAD_EXTENSION("enable_load_extension", "Enable SQLite load_extention() function, native driver only", OnOff),
 
         // Pragmas that can be set after opening the database
         TEMP_STORE_DIRECTORY("temp_store_directory"),
         USER_VERSION("user_version"),
 
-        // transaction mode
-        TRANSACTION_MODE("transaction_mode", toStringArray(TransactionMode.values()));
+        // Others
+        TRANSACTION_MODE("transaction_mode", toStringArray(TransactionMode.values())),
+        DATE_PRECISION("date_precision", "\"seconds\": Read and store integer dates as seconds from the Unix Epoch (SQLite standard).\n\"milliseconds\": (DEFAULT) Read and store integer dates as milliseconds from the Unix Epoch (Java standard).", toStringArray(DatePrecision.values())),
+        DATE_CLASS("date_class", "\"integer\": (Default) store dates as number of seconds or milliseconds from the Unix Epoch\n\"text\": store dates as a string of text\n\"real\": store dates as Julian Dates", toStringArray(DateClass.values())),
+        DATE_STRING_FORMAT("date_string_format", "Format to store and retrieve dates stored as text. Defaults to \"yyyy-MM-dd HH:mm:ss.SSS\"", null);
 
         public final String   pragmaName;
         public final String[] choices;
     public TransactionMode getTransactionMode() {
         return transactionMode;
     }
+
+    public static enum DatePrecision implements PragmaValue {
+        SECONDS, MILLISECONDS;
+
+        public String getValue() {
+            return name();
+        }
+
+        public static DatePrecision getPrecision(String precision) {
+            return DatePrecision.valueOf(precision.toUpperCase());
+        }
+    }
+ 
+    /**
+     * @param datePrecision One of SECONDS or MILLISECONDS
+     * @throws SQLException 
+     */
+    public void setDatePrecision(String datePrecision) throws SQLException {
+        setPragma(Pragma.DATE_PRECISION, DatePrecision.getPrecision(datePrecision).getValue());
+    }
+
+    public static enum DateClass implements PragmaValue {
+        INTEGER, TEXT, REAL;
+
+        public String getValue() {
+            return name();
+        }
+
+        public static DateClass getDateClass(String dateClass) {
+            return DateClass.valueOf(dateClass.toUpperCase());
+        }
+    }
+
+    /**
+     * @param dateClass One of INTEGER, TEXT or REAL
+     */
+    public void setDateClass(String dateClass) {
+        setPragma(Pragma.DATE_CLASS, DateClass.getDateClass(dateClass).getValue());
+    }
+
+    /**
+     * @param format Format of date string
+     */
+    public void setDateStringFormat(String dateStringFormat) {
+        setPragma(Pragma.DATE_STRING_FORMAT, dateStringFormat);
+    }
 }

File src/test/java/org/sqlite/PrepStmtTest.java

 import java.sql.Statement;
 import java.util.StringTokenizer;
 
-import javax.naming.BinaryRefAddr;
-
 import org.junit.After;
 import org.junit.Before;
 import org.junit.BeforeClass;
-import org.junit.Ignore;
 import org.junit.Test;
 
 /** These tests are designed to stress PreparedStatements on memory dbs. */

File src/test/java/org/sqlite/QueryTest.java

 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.text.SimpleDateFormat;
 import java.util.Date;
 
 import org.junit.BeforeClass;
         conn.createStatement().execute("create table sample (start_time datetime)");
 
         Date now = new Date();
-        //String date = "2000-01-01 16:45:00";
-        conn.createStatement().execute(String.format("insert into sample values(%s)", now.getTime()));
+        String date = new SimpleDateFormat(SQLiteConfig.DEFAULT_DATE_STRING_FORMAT).format(now);
+
+        conn.createStatement().execute("insert into sample values(" + now.getTime() + ")");
+        conn.createStatement().execute("insert into sample values('" + date + "')");
 
         ResultSet rs = conn.createStatement().executeQuery("select * from sample");
         assertTrue(rs.next());
         assertEquals(now, rs.getDate(1));
+        assertTrue(rs.next());
+        assertEquals(now, rs.getDate(1));
 
+        PreparedStatement stmt = conn.prepareStatement("insert into sample values(?)");
+        stmt.setDate(1, new java.sql.Date(now.getTime()));
     }
 
     @Test