You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by dj...@apache.org on 2006/09/21 06:28:15 UTC
svn commit: r448456 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/types/SQLTime.java
engine/org/apache/derby/iapi/types/SQLTimestamp.java
testing/org/apache/derbyTesting/functionTests/tests/lang/TimeHandlingTest.java
Author: djd
Date: Wed Sep 20 21:28:14 2006
New Revision: 448456
URL: http://svn.apache.org/viewvc?view=rev&rev=448456
Log:
DERBY-1811 Ensure embedded ResultSet.getTimestamp on a TIME column returns a java.sql.Timestamp with a date portion
equal to the current date at the time the getTimestamp method is called.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TimeHandlingTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java?view=diff&rev=448456&r1=448455&r2=448456
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java Wed Sep 20 21:28:14 2006
@@ -139,30 +139,42 @@
}
/**
- @exception StandardException thrown on failure
+ * Convert a SQL TIME to a JDBC java.sql.Timestamp.
+ *
+ * Behaviour is to set the date portion of the Timestamp
+ * to the actual current date, which may not match the
+ * SQL CURRENT DATE, which remains fixed for the lifetime
+ * of a SQL statement. JDBC drivers (especially network client drivers)
+ * could not be expected to fetch the CURRENT_DATE SQL value
+ * on every query that involved a TIME value, so the current
+ * date as seen by the JDBC client was picked as the logical behaviour.
+ * See DERBY-1811.
*/
- public Timestamp getTimestamp( Calendar cal) throws StandardException
+ public Timestamp getTimestamp( Calendar cal)
{
if (isNull())
return null;
else
{
if( cal == null)
- cal = new GregorianCalendar();
- /*
- ** HACK FOR SYMANTEC: in symantec 1.8, the call
- ** to today.getTime().getTime() will blow up
- ** in GregorianCalendar because year <= 0.
- ** This is a bug in some sort of optimization that
- ** symantic is doing (not related to the JIT). If
- ** we do a reference to that field everythings works
- ** fine, hence this extraneous get(Calendar.YEAR).
- */
- cal.get(Calendar.YEAR);
+ {
+ // Calendar initialized to current date and time.
+ cal = new GregorianCalendar();
+ }
+ else
+ {
+ cal.clear();
+ // Set Calendar to current date and time.
+ cal.setTime(new Date(System.currentTimeMillis()));
+ }
+
cal.set(Calendar.HOUR_OF_DAY, getHour(encodedTime));
cal.set(Calendar.MINUTE, getMinute(encodedTime));
cal.set(Calendar.SECOND, getSecond(encodedTime));
+
+ // Derby's resolution for the TIME type is only seconds.
cal.set(Calendar.MILLISECOND, 0);
+
return new Timestamp(cal.getTime().getTime());
}
}
@@ -797,13 +809,10 @@
if (isNull())
return null;
- return newTime(cal);
- }
-
- protected Time newTime(java.util.Calendar cal)
- {
if( cal == null)
cal = new GregorianCalendar();
+
+ cal.clear();
cal.set(Calendar.YEAR, 1970);
cal.set(Calendar.MONTH, Calendar.JANUARY);
cal.set(Calendar.DATE, 1);
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java?view=diff&rev=448456&r1=448455&r2=448456
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java Wed Sep 20 21:28:14 2006
@@ -169,6 +169,7 @@
{
if( cal == null)
cal = new GregorianCalendar();
+ cal.clear();
cal.set(Calendar.YEAR, SQLDate.getYear(encodedDate) );
cal.set(Calendar.MONTH, SQLDate.getMonth(encodedDate)-1);
cal.set(Calendar.DATE, SQLDate.getDay(encodedDate) );
@@ -197,13 +198,14 @@
{
if( cal == null)
cal = new GregorianCalendar();
+ cal.clear();
cal.set(Calendar.YEAR, 1970);
cal.set(Calendar.MONTH, Calendar.JANUARY);
cal.set(Calendar.DATE, 1);
cal.set(Calendar.HOUR_OF_DAY, SQLTime.getHour(encodedTime));
cal.set(Calendar.MINUTE, SQLTime.getMinute(encodedTime));
cal.set(Calendar.SECOND, SQLTime.getSecond(encodedTime));
- cal.set(Calendar.MILLISECOND, (int)(nanos/1E06));
+ cal.set(Calendar.MILLISECOND, (int)(nanos/1000000));
return new Time(cal.getTime().getTime());
}
@@ -889,7 +891,7 @@
return newTimestamp(cal);
}
- protected Timestamp newTimestamp(Calendar currentCal)
+ private Timestamp newTimestamp(Calendar currentCal)
{
if( currentCal == null)
currentCal = new GregorianCalendar();
@@ -901,6 +903,7 @@
private void setCalendar( Calendar cal)
{
+ cal.clear();
cal.set(Calendar.YEAR, SQLDate.getYear(encodedDate));
/* Note calendar month is zero based so we subtract 1*/
cal.set(Calendar.MONTH, (SQLDate.getMonth(encodedDate)-1));
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TimeHandlingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TimeHandlingTest.java?view=diff&rev=448456&r1=448455&r2=448456
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TimeHandlingTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TimeHandlingTest.java Wed Sep 20 21:28:14 2006
@@ -20,6 +20,7 @@
*/
package org.apache.derbyTesting.functionTests.tests.lang;
+import java.io.UnsupportedEncodingException;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
@@ -29,6 +30,7 @@
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Calendar;
+import java.util.Random;
import junit.framework.Test;
import junit.framework.TestSuite;
@@ -115,14 +117,118 @@
}
/**
- * Simple set up, just get a Calendar.
+ * Simple set up, just get a Calendar
+ * and ensure the table T_ALL is empty.
+ * @throws SQLException
+ * @throws UnsupportedEncodingException
*/
- protected void setUp()
+ protected void setUp() throws UnsupportedEncodingException, SQLException
{
cal = Calendar.getInstance();
+ runSQLCommands("DELETE FROM TIME_ALL;");
}
/**
+ * Test inserting and selecting of TIME values.
+ * A set of random TIME values are inserted along with an
+ * identifer that encodes the time value. The values are then
+ * fetched and compared to a value calculated from the identifier.
+ * The returned values are fetched using checkTimeValue thus inheriting
+ * all the checks within that method.
+ * @throws SQLException
+ * @throws UnsupportedEncodingException
+ */
+ public void testInertTime() throws SQLException, UnsupportedEncodingException
+ {
+ getConnection().setAutoCommit(false);
+ // Insert a set of time values,
+
+
+ Random r = new Random();
+
+ // Insert 500 TIME values using a PreparedStatement,
+ // but randomly selecting the way the value is inserted
+ // between:
+ // java.sql.Time object
+ // String representation hh:mm:ss from Time.toString()
+ // String representation hh.mm.ss
+
+ // prime number used to select the way the
+ // selected value is inserted.
+ final int itk = 71;
+
+ PreparedStatement ps = prepareStatement(
+ "INSERT INTO TIME_ALL(ID, C_T) VALUES (?, ?)");
+
+ for (int i = 0; i < 500; i++) {
+
+ // Just some big range from zero upwards
+ int id = r.nextInt(1000000);
+ ps.setInt(1, id);
+
+ Time ct = getCodedTime(id);
+
+ switch ((id % itk) % 3)
+ {
+ case 0: // Insert using Time object
+ ps.setTime(2, ct);
+ break;
+ case 1: // Insert using String provided by Time.toString() (hh:mm:ss)
+ ps.setString(2, ct.toString());
+ break;
+ case 2: // Insert using String format (hh.mm.ss)
+ ps.setString(2, ct.toString().replace(':', '.'));
+ break;
+ default:
+ fail("not reached");
+
+ }
+ ps.executeUpdate();
+ }
+ ps.close();
+ commit();
+
+ Statement s = createStatement();
+
+ ResultSet rs = s.executeQuery("SELECT ID, C_T FROM TIME_ALL");
+ int rowCount = 0;
+ while (rs.next())
+ {
+ int id = rs.getInt(1);
+ Time t = checkTimeValue(rs, 2);
+ assertTimeEqual(getCodedTime(id), t);
+ rowCount++;
+ }
+ rs.close();
+ s.close();
+ commit();
+
+ assertEquals(rowCount, 500);
+ }
+
+ /**
+ * Return a time simply encoded from an integer identifier
+ * and a set of fixed encoding keys, each a prime number.
+ * This allows a random value to be inserted into a table
+ * as a TIME and an INTEGER and thus checked for consistency
+ * on a SELECT.
+ * @param id
+ * @return
+ */
+ private Time getCodedTime(int id)
+ {
+ final int hk = 17;
+ final int mk = 41;
+ final int sk = 67;
+
+ int hour = (id % hk) % 24;
+ int min = (id % mk) % 60;
+ int sec = (id % sk) % 60;
+
+ return getTime19700101(hour, min ,sec);
+ }
+
+ /**
* Tests for CURRENT TIME and CURRENT_TIME.
* A set of tests that ensure the CURRENT TIME maintains
* a single value for the life time of a statement and
@@ -523,6 +629,7 @@
long now = System.currentTimeMillis();
Timestamp tsv = rs.getTimestamp(column);
+ long now2 = System.currentTimeMillis();
assertNotNull(tsv);
assertFalse(rs.wasNull());
@@ -530,9 +637,13 @@
assertTimeEqual(tv, tsv);
// DERBY-1811, DERBY-889 being fixed could add tests
- // here to check the returned date portion is the current date
- // using the value from 'now'.
-
+ // Check the returned date portion is the current date
+ // using the value from 'now' and 'now2'. Double check
+ // just in case this test runs at midnight.
+ if (!(isDateEqual(now, tsv) || isDateEqual(now2, tsv)))
+ {
+ fail("TIME to java.sql.Timestamp does not contain current date " + tsv);
+ }
String sv = rs.getString(column);
assertNotNull(sv);
@@ -687,7 +798,33 @@
}
return tsv;
- }
+ }
+
+ /**
+ * Create a Time object that has its date components
+ * set to 1970/01/01 and its time to match the time
+ * represented by h, m and s. This matches Derby by
+ * setting the milli-second component to zero.
+ * <BR>
+ * Note that the Time(long) constructor for java.sql.Time
+ * does *not* set the date component to 1970/01/01.
+ * This is a requirement for JDBC java.sql.Time values though
+ */
+ private Time getTime19700101(int hour, int min, int sec)
+ {
+ cal.clear();
+ cal.set(1970, Calendar.JANUARY, 1);
+ cal.set(Calendar.MILLISECOND, 0);
+
+ cal.set(Calendar.HOUR_OF_DAY, hour);
+ cal.set(Calendar.MINUTE, min);
+ cal.set(Calendar.SECOND, sec);
+
+ Time to = new Time(cal.getTime().getTime());
+ assertTime1970(to);
+ return to;
+ }
+
/**
* Create a Time object that has its date components
* set to 1970/01/01 and its time to match the time
@@ -762,15 +899,15 @@
}
/**
- * Assert the time portion of a java.sql.Timestamp
- * is equal to the value of a java.sql.Time.
+ * Assert the SQL time portion of two SQL JDBC type
+ * types are equal.
* @param tv
* @param tsv
*/
- private void assertTimeEqual(Time tv, Timestamp tsv)
+ private void assertTimeEqual(java.util.Date tv1, java.util.Date tv2)
{
cal.clear();
- cal.setTime(tv);
+ cal.setTime(tv1);
int hour = cal.get(Calendar.HOUR_OF_DAY);
int min = cal.get(Calendar.MINUTE);
@@ -779,10 +916,31 @@
// Check the time portion is set to the same as tv
cal.clear();
- cal.setTime(tsv);
+ cal.setTime(tv2);
assertEquals(hour, cal.get(Calendar.HOUR_OF_DAY));
assertEquals(min, cal.get(Calendar.MINUTE));
assertEquals(sec, cal.get(Calendar.SECOND));
assertEquals(ms, cal.get(Calendar.MILLISECOND));
+ }
+
+ /**
+ * Check if the date portion of a Timestamp value
+ * is equal to the date portion of a time value
+ * represented in milli-seconds since 1970.
+ */
+ private boolean isDateEqual(long d, Timestamp tsv)
+ {
+ cal.clear();
+ cal.setTime(new java.util.Date(d));
+ int day = cal.get(Calendar.DAY_OF_MONTH);
+ int month = cal.get(Calendar.MONTH);
+ int year = cal.get(Calendar.YEAR);
+
+ cal.clear();
+ cal.setTime(tsv);
+
+ return day == cal.get(Calendar.DAY_OF_MONTH)
+ && month == cal.get(Calendar.MONTH)
+ && year == cal.get(Calendar.YEAR);
}
}