Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > lang > TimeHandlingTest


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.lang.TimeHandlingTest
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */
21 package org.apache.derbyTesting.functionTests.tests.lang;
22
23 import java.io.UnsupportedEncodingException ;
24 import java.sql.Date ;
25 import java.sql.PreparedStatement ;
26 import java.sql.ResultSet ;
27 import java.sql.SQLException ;
28 import java.sql.Statement ;
29 import java.sql.Time ;
30 import java.sql.Timestamp ;
31 import java.sql.Types ;
32 import java.util.Calendar ;
33 import java.util.Random ;
34
35 import junit.framework.Test;
36 import junit.framework.TestSuite;
37
38 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
39 import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
40
41
42 public class TimeHandlingTest extends BaseJDBCTestCase {
43     
44     /**
45      * All the functions or expressions that result in
46      * a TIME value with the same value as CURRENT_TIME.
47      */
48     private static final String [] CURRENT_TIME_FUNCTIONS =
49     {"CURRENT TIME", "CURRENT_TIME"};
50     
51     /**
52      * All the functions or expressions that result in
53      * a TIMESTAMP value with the same value as CURRENT_TIMESTAMP.
54      */
55     private static final String [] CURRENT_TIMESTAMP_FUNCTIONS =
56     {"CURRENT TIMESTAMP", "CURRENT_TIMESTAMP"};
57
58     /**
59      * Time to sleep that will result in different TIME values.
60      */
61     private static final long SLEEP_TIME = 2000;
62     
63     /**
64      * Calendar for testing returned values.
65      */
66     private Calendar cal;
67
68
69     public static Test suite()
70     {
71         TestSuite suite = new TestSuite(TimeHandlingTest.class);
72         
73         return new CleanDatabaseTestSetup(suite) {
74             protected void decorateSQL(Statement s) throws SQLException {
75                
76                 s.execute("CREATE FUNCTION SLEEP() RETURNS INTEGER" +
77                         " LANGUAGE JAVA PARAMETER STYLE JAVA" +
78                         " NO SQL " +
79                         " EXTERNAL NAME '"+
80                         TimeHandlingTest.class.getName().concat(".sleep'"));
81                 
82                 s.execute("CREATE TABLE TIME_ALL (ID INT," +
83                         " C_T TIME," +
84                         " C_D DATE," +
85                         " C_TS TIMESTAMP)");
86                 
87                 for (int f = 0; f < CURRENT_TIME_FUNCTIONS.length; f++)
88                 {
89                     s.execute("ALTER TABLE TIME_ALL ADD COLUMN" +
90                             " D_T" + f + " TIME WITH DEFAULT " +
91                             CURRENT_TIME_FUNCTIONS[f]);
92                 }
93                 for (int f = 0; f < CURRENT_TIMESTAMP_FUNCTIONS.length; f++)
94                 {
95                     s.execute("ALTER TABLE TIME_ALL ADD COLUMN" +
96                             " D_TS" + f + " TIMESTAMP WITH DEFAULT " +
97                             CURRENT_TIMESTAMP_FUNCTIONS[f]);
98                 }
99            }
100         };
101     }
102     
103     /**
104      * Method for SQL SLEEP function. Sleeps for the time
105      * that will result in a change in
106      * System.currentTimeMillis and a Derby TIME value.
107      * @return
108      * @throws InterruptedException
109      */
110     public static int sleep() throws InterruptedException {
111         Thread.sleep(SLEEP_TIME);
112         return 0;
113     }
114
115     public TimeHandlingTest(String name) {
116         super(name);
117     }
118     
119     /**
120      * Simple set up, just get a Calendar
121      * and ensure the table T_ALL is empty.
122      * @throws SQLException
123      * @throws UnsupportedEncodingException
124      */
125     protected void setUp() throws UnsupportedEncodingException , SQLException
126     {
127         cal = Calendar.getInstance();
128         runSQLCommands("DELETE FROM TIME_ALL;");
129     }
130     
131     /**
132      * Test inserting and selecting of TIME values.
133      * A set of random TIME values are inserted along with an
134      * identifer that encodes the time value. The values are then
135      * fetched and compared to a value calculated from the identifier.
136      * The returned values are fetched using checkTimeValue thus inheriting
137      * all the checks within that method.
138      * @throws SQLException
139      * @throws UnsupportedEncodingException
140      */
141     public void testInertTime() throws SQLException , UnsupportedEncodingException
142     {
143         getConnection().setAutoCommit(false);
144         // Insert a set of time values,
145
146
147         Random r = new Random ();
148
149         // Insert 500 TIME values using a PreparedStatement,
150 // but randomly selecting the way the value is inserted
151 // between:
152 // java.sql.Time object
153 // String representation hh:mm:ss from Time.toString()
154 // String representation hh.mm.ss
155
156         // prime number used to select the way the
157 // selected value is inserted.
158 final int itk = 71;
159
160         PreparedStatement ps = prepareStatement(
161            "INSERT INTO TIME_ALL(ID, C_T) VALUES (?, ?)");
162  
163         for (int i = 0; i < 500; i++) {
164             
165             // Just some big range from zero upwards
166 int id = r.nextInt(1000000);
167             ps.setInt(1, id);
168             
169             Time ct = getCodedTime(id);
170            
171             switch ((id % itk) % 3)
172             {
173             case 0: // Insert using Time object
174 ps.setTime(2, ct);
175                 break;
176             case 1: // Insert using String provided by Time.toString() (hh:mm:ss)
177 ps.setString(2, ct.toString());
178                 break;
179             case 2: // Insert using String format (hh.mm.ss)
180 ps.setString(2, ct.toString().replace(':', '.'));
181                 break;
182             default:
183                fail("not reached");
184                
185              }
186             ps.executeUpdate();
187         }
188         ps.close();
189         commit();
190         
191         Statement s = createStatement();
192         
193         ResultSet rs = s.executeQuery("SELECT ID, C_T FROM TIME_ALL");
194         int rowCount = 0;
195         while (rs.next())
196         {
197             int id = rs.getInt(1);
198             Time t = checkTimeValue(rs, 2);
199             assertTimeEqual(getCodedTime(id), t);
200             rowCount++;
201         }
202         rs.close();
203         s.close();
204         commit();
205         
206         assertEquals(rowCount, 500);
207     }
208
209     /**
210      * Return a time simply encoded from an integer identifier
211      * and a set of fixed encoding keys, each a prime number.
212      * This allows a random value to be inserted into a table
213      * as a TIME and an INTEGER and thus checked for consistency
214      * on a SELECT.
215      * @param id
216      * @return
217      */
218     private Time getCodedTime(int id)
219     {
220         final int hk = 17;
221         final int mk = 41;
222         final int sk = 67;
223
224         int hour = (id % hk) % 24;
225         int min = (id % mk) % 60;
226         int sec = (id % sk) % 60;
227         
228         return getTime19700101(hour, min ,sec);
229     }
230
231     /**
232      * Tests for CURRENT TIME and CURRENT_TIME.
233      * A set of tests that ensure the CURRENT TIME maintains
234      * a single value for the life time of a statement and
235      * that (subject to the resolution) the returned value
236      * is correctly between the start time of the statement
237      * execution and the first fetch or completion.
238      * @throws SQLException
239      * @throws InterruptedException
240      */
241     public void testCurrentTime() throws SQLException , InterruptedException
242     {
243         currentFunctionTests(Types.TIME, CURRENT_TIME_FUNCTIONS);
244     }
245     
246     /**
247      * Tests for CURRENT TIMESTAMP functions.
248      * A set of tests that ensure the CURRENT TIMESTAMP maintains
249      * a single value for the life time of a statement and
250      * that (subject to the resolution) the returned value
251      * is correctly between the start time of the statement
252      * execution and the first fetch or completion.
253      * @throws SQLException
254      * @throws InterruptedException
255      */
256     public void testCurrentTimestamp() throws SQLException , InterruptedException
257     {
258         currentFunctionTests(Types.TIMESTAMP, CURRENT_TIMESTAMP_FUNCTIONS);
259     }
260     /**
261      * Test all the current timedate functions passed in that
262      * return the specified type. Generic function that checks
263      * the functions' are all identical in various situations
264      * and that the have the correct value, and change across
265      * executions.
266      *
267      * @param jdbcType JDBC type, Types.TIME, DATE or TIMESTAMP.
268      * @param functions List of functions or expressions that map to the
269      * current time date value and return the specified type.
270      * @throws SQLException
271      * @throws InterruptedException
272      */
273     private void currentFunctionTests(int jdbcType, String [] functions)
274     throws SQLException , InterruptedException
275     {
276         Statement s = createStatement();
277
278         // Single value returned by each function.
279 for (int f = 0; f < functions.length; f++) {
280             checkCurrentQuery(jdbcType, s, "VALUES " + functions[f],
281                     new int[] {1}, 1);
282         }
283         
284         // Create text for a single row in a VALUES clause,
285 // each function represented once.
286 StringBuffer rb = new StringBuffer ("(");
287         for (int f = 0; f < functions.length; f++) {
288             if (f != 0)
289                 rb.append(", ");
290             rb.append(functions[f]);
291         }
292         rb.append(")");
293         String row = rb.toString();
294         
295         int[] columns = new int[functions.length];
296         for (int f = 0; f < columns.length; f++)
297             columns[f] = f + 1;
298         
299         // All the functions as a single row, all return the same value
300 String sql = "VALUES " + row;
301         checkCurrentQuery(jdbcType, s, sql, columns, functions.length);
302
303         
304         // Check they produce the same value across multiple rows
305 sql = "VALUES " + row + "," + row + "," + row;
306         checkCurrentQuery(jdbcType, s, sql, columns, 3 * functions.length);
307
308         // Check they produce the same value across multiple rows
309 // with a forced sleep within row creaton
310 String sleepRow = row.substring(0, row.length() - 1)
311              + ", SLEEP())";
312         
313         sql = "VALUES " + sleepRow + "," + sleepRow + "," + sleepRow;
314         checkCurrentQuery(jdbcType, s, sql, columns, 3 * functions.length);
315
316         
317         // Check behaviour in non-queries.
318 String ccol = null;
319         String dcol = null;
320         switch (jdbcType)
321         {
322         case Types.TIME:
323             dcol = "D_T";
324             ccol = "C_T";
325             break;
326         case Types.TIMESTAMP:
327             dcol = "D_TS";
328             ccol = "C_TS";
329             break;
330         case Types.DATE:
331             dcol = "D_D";
332             ccol = "C_D";
333             break;
334         default:
335             fail("Unexpected JDBC Type " + jdbcType);
336         }
337         
338         // All the functions as multiple rows, one function per row.
339 StringBuffer rm = new StringBuffer ();
340         for (int f = 0; f < functions.length; f++) {
341             if (f != 0)
342                 rm.append(", ");
343             rm.append(functions[f]);
344         }
345         String mrow = rm.toString();
346         
347         // Select list with all the columns of this type
348 StringBuffer sb = new StringBuffer ();
349         sb.append(ccol); // Column without the defaul
350 for (int f = 0; f < functions.length; f++) {
351             sb.append(", ");
352             sb.append(dcol);
353             sb.append(f);
354         }
355         String typeColumnList = sb.toString();
356         String selectAllType = "SELECT " + typeColumnList + " FROM TIME_ALL";
357         
358         int[] tableColumns = new int[columns.length + 1];
359         for (int i = 0; i < tableColumns.length; i++)
360             tableColumns[i] = i+1;
361                     
362         // Insert multiple rows, one per function
363 // Check all the inserted value and the default
364 // columns have the same value.
365 String insert = "INSERT INTO TIME_ALL(" + ccol + ") VALUES " + mrow;
366         s.executeUpdate("DELETE FROM TIME_ALL");
367         long start = System.currentTimeMillis();
368         s.executeUpdate(insert);
369         long end = System.currentTimeMillis();
370         ResultSet rs = s.executeQuery(selectAllType);
371         rs.next();
372         checkCurrentMultiple(jdbcType, start, end, rs, tableColumns,
373                 functions.length * (functions.length + 1));
374         rs.close();
375         
376         // Insert of multiple rows from a query with a delay
377 // All the functions as multiple rows, one function per row
378 // with a SLEEP as the first column.
379 sb = new StringBuffer ();
380         for (int f = 0; f < functions.length; f++) {
381             if (f != 0)
382                 sb.append(", ");
383             sb.append("(SLEEP(), ");
384             sb.append(functions[f]);
385             sb.append(")");
386         }
387         String mSleepRow = sb.toString();
388         
389         insert = "INSERT INTO TIME_ALL(ID, " + ccol + ") " +
390           " SELECT * FROM TABLE (VALUES " +
391           mSleepRow +
392           ") AS T";
393        
394         s.executeUpdate("DELETE FROM TIME_ALL");
395         start = System.currentTimeMillis();
396         s.executeUpdate(insert);
397         end = System.currentTimeMillis();
398         rs = s.executeQuery(selectAllType);
399         rs.next();
400         checkCurrentMultiple(jdbcType, start, end, rs, tableColumns,
401                 functions.length * (functions.length + 1));
402         rs.close();
403         
404         // Ensure a PreparedStatement (psI) resets its current time correctly
405 // and does not get stuck with a single value for all executions.
406 PreparedStatement psQ = prepareStatement(
407                 selectAllType + " WHERE ID = ?");
408         
409         Object last = null;
410         for (int f = 0; f < functions.length; f++) {
411             PreparedStatement psI = prepareStatement("INSERT INTO TIME_ALL(ID, " +
412                     ccol + ")" +
413                     " VALUES (?, " + functions[f] +")");
414             s.executeUpdate("DELETE FROM TIME_ALL");
415
416             for (int i = 1; i <=3; i++) {
417                psI.setInt(1, i);
418                psQ.setInt(1, i);
419                start = System.currentTimeMillis();
420                psI.executeUpdate();
421                end = System.currentTimeMillis();
422
423                rs = psQ.executeQuery();
424                rs.next();
425                Object next = checkCurrentMultiple(jdbcType, start, end, rs,
426                        tableColumns, functions.length + 1);
427                rs.close();
428                
429                if (last != null) {
430                    // This check is redundant because the last and next have
431 // been checked they are within limit of the start and end.
432 // But why not check it.
433 assertFalse("CURRENT value not changed over executions",
434                            last.equals(next));
435                }
436                last = next;
437                      
438                // Ensure the next execution is meant to get a different value
439 Thread.sleep(SLEEP_TIME);
440             }
441             psI.close();
442
443         }
444
445         psQ.close();
446         s.close();
447     }
448     
449     /**
450      * Execute a query that uses CURRENT expressions directly.
451      * The time returned for these values should be between the
452      * start of execution and after the return from the first rs.next().
453      * @param sqlType
454      * @param s
455      * @param sql
456      * @param columns
457      * @param expectedCount
458      * @throws SQLException
459      */
460     private void checkCurrentQuery(int sqlType,
461             Statement s, String sql, int[] columns, int expectedCount)
462     throws SQLException
463     {
464         long start = System.currentTimeMillis();
465         ResultSet rs = s.executeQuery(sql);
466         rs.next();
467         long end = System.currentTimeMillis();
468         checkCurrentMultiple(sqlType, start, end, rs,
469                 columns, expectedCount);
470         rs.close();
471     }
472     
473     /**
474      * Check the validity of all CURRENT time values returned and
475      * that they are identical.
476      * @param jdbcType Types.TIME or TIMESTAMP
477      * @param start Start of window for valid value.
478      * @param end End of window for valid value.
479      * @param rs Result set positioned on row.
480      * @param columns Columns holding current values.
481      * @param expectedCount Total number of values exected to see
482      * (row count times column count)
483      * @return
484      * @throws SQLException
485      */
486     private Object checkCurrentMultiple(int jdbcType, long start, long end, ResultSet rs,
487             int[] columns, int expectedCount) throws SQLException
488    {
489         switch (jdbcType)
490         {
491         case Types.TIME:
492             return checkCurrentTimeMultiple(start, end, rs, columns, expectedCount);
493         case Types.TIMESTAMP:
494             return checkCurrentTimestampMultiple(start, end, rs, columns, expectedCount);
495         default:
496             fail("Unexpected type " + jdbcType);
497         return null;
498         }
499   }
500     
501     /**
502      * Check a set of rows and columns with values set to CURRENT TIME
503      * in a single statement are the same.
504      * @param start Start time for the statement that set the values.
505      * @param end End time for the statement that set the values.
506      * @param rs ResultSet positioned on the first row.
507      * @param columns Set of columns holding the TIME values
508      * @param expectedCount Number of values we are execpted to check.
509      * @throws SQLException
510      */
511     private Time checkCurrentTimeMultiple(long start, long end, ResultSet rs,
512             int[] columns, int expectedCount) throws SQLException
513     {
514         // Result set is positioned on starting row
515 // Since all values must be the same since they are based upon
516 // CURRENT TIME from a single statement, pick one as the base
517 // and compare the rest to it.
518 Time base = checkCurrentTimeValue(start, end, rs, columns[0]);
519         assertNotNull(base);
520         int count = 1;
521         
522         // check the remaining columns on this row.
523 for (int i = 1; i < columns.length; i++)
524         {
525             Time t = checkCurrentTimeValue(start, end, rs, columns[i]);
526             assertEquals("CURENT TIME changed during execution", base, t);
527             count++;
528         }
529         
530         // now check all columns on any remaining rows
531 while (rs.next()) {
532             for (int i = 0; i < columns.length; i++)
533             {
534                 Time t = checkCurrentTimeValue(start, end, rs, columns[i]);
535                 assertEquals("CURENT TIME changed during execution", base, t);
536                 count++;
537             }
538         }
539         
540         assertEquals(expectedCount, count);
541         
542         return base;
543     }
544     /**
545      * Check a set of rows and columns with values set to CURRENT TIMESTAMP
546      * in a single statement are the same.
547      * @param start Start time for the statement that set the values.
548      * @param end End time for the statement that set the values.
549      * @param rs ResultSet positioned on the first row.
550      * @param columns Set of columns holding the TIME values
551      * @param expectedCount Number of values we are execpted to check.
552      * @throws SQLException
553      */
554     private Timestamp checkCurrentTimestampMultiple(long start, long end, ResultSet rs,
555             int[] columns, int expectedCount) throws SQLException
556     {
557         // Result set is positioned on starting row
558 // Since all values must be the same since they are based upon
559 // CURRENT TIME from a single statement, pick one as the base
560 // and compare the rest to it.
561 Timestamp base = checkCurrentTimestampValue(start, end, rs, columns[0]);
562         assertNotNull(base);
563         int count = 1;
564         
565         // check the remaining columns on this row.
566 for (int i = 1; i < columns.length; i++)
567         {
568             Timestamp ts = checkCurrentTimestampValue(start, end, rs, columns[i]);
569             assertEquals("CURENT TIMESTAMP changed during execution", base, ts);
570             count++;
571         }
572         
573         // now check all columns on any remaining rows
574 while (rs.next()) {
575             for (int i = 0; i < columns.length; i++)
576             {
577                 Timestamp ts = checkCurrentTimestampValue(start, end, rs, columns[i]);
578                 assertEquals("CURENT TIMESTAMP changed during execution", base, ts);
579                 count++;
580             }
581         }
582         
583         assertEquals(expectedCount, count);
584         
585         return base;
586     }
587     /**
588      * Check the consistency of a ResultSet column that returns
589      * a TIME value. Can be used for any column of type TIME.
590      *
591      * @param rs ResultSet holding the column, positioned on a row
592      * @param column Column with the TIME value.
593      * @return Returns the Time object obtained from the column.
594      * @throws SQLException
595      */
596     private Time checkTimeValue(ResultSet rs, int column) throws SQLException
597     {
598         assertEquals(java.sql.Types.TIME, rs.getMetaData().getColumnType(column));
599         
600         try {
601             rs.getDate(column);
602             fail("ResultSet.getDate() succeeded on TIME column");
603         } catch (SQLException e) {
604             assertSQLState("22005", e);
605         }
606   
607         Time tv = rs.getTime(column);
608         assertEquals(tv == null, rs.wasNull());
609         
610         Object ov = rs.getObject(column);
611         assertEquals(ov == null, rs.wasNull());
612         
613         if (tv == null) {
614             assertNull(ov);
615             return null;
616         }
617         
618         assertTrue(ov instanceof java.sql.Time );
619         assertEquals(tv, ov);
620          
621         // Check the date portion is set to 1970/01/01
622 assertTime1970(tv);
623         cal.clear();
624         cal.setTime(tv);
625         
626         // Check the milli-seconds portion is 0
627 // Derby does not support that precision in TIME
628 assertEquals(0, cal.get(Calendar.MILLISECOND));
629         
630         long now = System.currentTimeMillis();
631         Timestamp tsv = rs.getTimestamp(column);
632         long now2 = System.currentTimeMillis();
633         assertNotNull(tsv);
634         assertFalse(rs.wasNull());
635         
636         // Check the TIME portion is set to the same as tv
637 assertTimeEqual(tv, tsv);
638         
639         // DERBY-1811, DERBY-889 being fixed could add tests
640 // Check the returned date portion is the current date
641 // using the value from 'now' and 'now2'. Double check
642 // just in case this test runs at midnight.
643 if (!(isDateEqual(now, tsv) || isDateEqual(now2, tsv)))
644         {
645             fail("TIME to java.sql.Timestamp does not contain current date " + tsv);
646         }
647         
648         String sv = rs.getString(column);
649         assertNotNull(sv);
650         assertFalse(rs.wasNull());
651         
652         // Assert the string converted back into a Time matches the Time returned.
653 assertEquals("ResultSet String converted to java.sql.Time mismatch",
654                 tv, getTime19700101(sv, cal));
655         
656         return tv;
657     }
658     
659     /**
660      * Check the consistency of a ResultSet column that returns
661      * a TIMESTAMP value. Can be used for any column of type TIMESTAMP.
662      *
663      * @param rs ResultSet holding the column, positioned on a row
664      * @param column Column with the TIMESTAMP value.
665      * @return Returns the Time object obtained from the column.
666      * @throws SQLException
667      */
668     private Timestamp checkTimestampValue(ResultSet rs, int column) throws SQLException
669     {
670         assertEquals(java.sql.Types.TIMESTAMP,
671                 rs.getMetaData().getColumnType(column));
672         
673   
674         Timestamp tsv = rs.getTimestamp(column);
675         assertEquals(tsv == null, rs.wasNull());
676        
677         Object ov = rs.getObject(column);
678         assertEquals(ov == null, rs.wasNull());
679         
680         if (tsv == null) {
681             assertNull(ov);
682             return null;
683         }
684
685         assertTrue(ov instanceof java.sql.Timestamp );
686         assertEquals(tsv, ov);
687        
688         Time tv = rs.getTime(column);
689         assertNotNull(tv);
690         assertFalse(rs.wasNull());
691  
692         // Check the date portion is set to 1970/01/01
693 assertTime1970(tv);
694
695         // Check the TIME portion is set to the same as tv
696 // DERBY-1816 java.sql.Time values from TIMESTAMP
697 // colummns lose their precision with client.
698 if (!usingDerbyNetClient())
699             assertTimeEqual(tv, tsv);
700                
701         String sv = rs.getString(column);
702         assertNotNull(sv);
703         assertFalse(rs.wasNull());
704         
705         // Assert the string converted back into a Time matches the Time returned.
706 assertEquals("ResultSet String converted to java.sql.Timestamp mismatch",
707                 tsv, Timestamp.valueOf(sv));
708         
709         return tsv;
710     }
711
712     /**
713      * Check the consistency of a ResultSet column that returns
714      * CURRENT TIME or a value set from CURRENT TIME.
715      *
716      * @param start Time the statement settng the value was executed
717      * @param end Time after first rs.next() or update statement was executed
718      * @param rs ResultSet holding the column, positioned on a row
719      * @param column Column with the timestamp.
720      * @return Returns the Time object obtained from the column.
721      * @throws SQLException
722      */
723     private Time checkCurrentTimeValue(long start, long end,
724             ResultSet rs, int column) throws SQLException
725     {
726         Time tv = checkTimeValue(rs, column);
727
728         // The time returned should be between the value
729 // of start and end (inclusive of both)
730
731         Time st = getTime19700101(start, cal);
732         Time et = getTime19700101(end, cal);
733         
734         
735         if (st.after(et)) {
736             // Gone back in time!
737 // Well test was running around midnight and the
738 // time for the start time is equal to or before 23:59:59
739 // and end time is equal to or after 00:00:00
740
741             assertTrue("CURRENT TIME outside of range when test crossing midnight",
742                (tv.equals(st) || tv.after(st))
743                || (tv.equals(et) || tv.before(et)));
744         }
745         else
746         {
747             // End time is after or equal to start time, expected case.
748
749             // The returned time must not be before the
750 // start time or after the end time.
751 assertFalse("CURRENT TIME before start of statement", tv.before(st));
752             assertFalse("CURRENT TIME after end of statement", tv.after(et));
753         }
754         
755         return tv;
756     }
757     /**
758      * Check the consistency of a ResultSet column that returns
759      * CURRENT TIMESTAMP or a value set from CURRENT TIMESTAMP.
760      *
761      * @param start Time the statement settng the value was executed
762      * @param end Time after first rs.next() or update statement was executed
763      * @param rs ResultSet holding the column, positioned on a row
764      * @param column Column with the timestamp.
765      * @return Returns the Timestamp object obtained from the column.
766      * @throws SQLException
767      */
768     private Timestamp checkCurrentTimestampValue(long start, long end,
769             ResultSet rs, int column) throws SQLException
770     {
771         Timestamp tsv = checkTimestampValue(rs, column);
772
773         // The time returned should be between the value
774 // of start and end (inclusive of both)
775
776         Timestamp st = new Timestamp (start);
777         Timestamp et = new Timestamp (end);
778         
779         
780         if (st.after(et)) {
781             // Gone back in time!
782 // Well test was running around midnight and the
783 // time for the start time is equal to or before 23:59:59
784 // and end time is equal to or after 00:00:00
785
786             assertTrue("CURRENT TIME outside of range when test crossing midnight",
787                (tsv.equals(st) || tsv.after(st))
788                || (tsv.equals(et) || tsv.before(et)));
789         }
790         else
791         {
792             // End time is after or equal to start time, expected case.
793
794             // The returned time must not be before the
795 // start time or after the end time.
796 assertFalse("CURRENT TIME before start of statement", tsv.before(st));
797             assertFalse("CURRENT TIME after end of statement", tsv.after(et));
798         }
799         
800         return tsv;
801     }
802
803     /**
804      * Create a Time object that has its date components
805      * set to 1970/01/01 and its time to match the time
806      * represented by h, m and s. This matches Derby by
807      * setting the milli-second component to zero.
808      * <BR>
809      * Note that the Time(long) constructor for java.sql.Time
810      * does *not* set the date component to 1970/01/01.
811      * This is a requirement for JDBC java.sql.Time values though
812      */
813     private Time getTime19700101(int hour, int min, int sec)
814     {
815         cal.clear();
816         cal.set(1970, Calendar.JANUARY, 1);
817         cal.set(Calendar.MILLISECOND, 0);
818         
819         cal.set(Calendar.HOUR_OF_DAY, hour);
820         cal.set(Calendar.MINUTE, min);
821         cal.set(Calendar.SECOND, sec);
822         
823         Time to = new Time (cal.getTime().getTime());
824         assertTime1970(to);
825         return to;
826     }
827     
828     /**
829      * Create a Time object that has its date components
830      * set to 1970/01/01 and its time to match the time
831      * represented by t and cal. This matches Derby by
832      * setting the milli-second component to zero.
833      * <BR>
834      * Note that the Time(long) constructor for java.sql.Time
835      * does *not* set the date component to 1970/01/01.
836      * This is a requirement for JDBC java.sql.Time values though
837      */
838     private Time getTime19700101(long t, Calendar cal)
839     {
840         cal.clear();
841         // JDK 1.3 can't call this!
842 // cal.setTimeInMillis(t);
843 cal.setTime(new Date (t));
844         cal.set(1970, Calendar.JANUARY, 1);
845         cal.set(Calendar.MILLISECOND, 0);
846         
847         Time to = new Time (cal.getTime().getTime());
848         assertTime1970(to);
849         return to;
850     }
851     
852     /**
853      * Create a Time object that has its date components
854      * set to 1970/01/01 and its time to match the time
855      * represented by t and cal. This matches Derby by
856      * setting the milli-second component to zero.
857      * <BR>
858      * Note that the Time(long) constructor for java.sql.Time
859      * does *not* set the date component to 1970/01/01.
860      * This is a requirement for JDBC java.sql.Time values though
861      */
862     private Time getTime19700101(String s, Calendar cal)
863     {
864         cal.clear();
865         // JDK 1.3 can't call this!
866 // cal.setTimeInMillis(t);
867 cal.setTime(Time.valueOf(s));
868         cal.set(1970, Calendar.JANUARY, 1);
869         cal.set(Calendar.MILLISECOND, 0);
870         
871         Time to = new Time (cal.getTime().getTime());
872         assertTime1970(to);
873         return to;
874     }
875     
876     /**
877      * Javadoc for java.sql.Time states the components of
878      * date for a java.sql.Time value must be set to January 1, 1970.
879      * Note that the java.sql.Time class does not enforce this,
880      * it is up to the driver.
881      * @param t
882      */
883     private void assertTime1970(Time t) {
884         
885         /* Cannot do this because all these methods
886          * throw IllegalArgumentException by definition,
887          * see java.sql.Time javadoc.
888  
889         assertEquals(1970, t.getYear());
890         assertEquals(0, t.getMonth());
891         assertEquals(1, t.getDate());
892         */
893         cal.clear();
894         cal.setTime(t);
895         
896         assertEquals(1970, cal.get(Calendar.YEAR));
897         assertEquals(Calendar.JANUARY, cal.get(Calendar.MONTH));
898         assertEquals(1, cal.get(Calendar.DATE));
899     }
900     
901     /**
902      * Assert the SQL time portion of two SQL JDBC type
903      * types are equal.
904      * @param tv
905      * @param tsv
906      */
907     private void assertTimeEqual(java.util.Date tv1, java.util.Date tv2)
908     {
909         cal.clear();
910         cal.setTime(tv1);
911                 
912         int hour = cal.get(Calendar.HOUR_OF_DAY);
913         int min = cal.get(Calendar.MINUTE);
914         int sec = cal.get(Calendar.SECOND);
915         int ms = cal.get(Calendar.MILLISECOND);
916                         
917         // Check the time portion is set to the same as tv
918 cal.clear();
919         cal.setTime(tv2);
920         assertEquals(hour, cal.get(Calendar.HOUR_OF_DAY));
921         assertEquals(min, cal.get(Calendar.MINUTE));
922         assertEquals(sec, cal.get(Calendar.SECOND));
923         assertEquals(ms, cal.get(Calendar.MILLISECOND));
924     }
925     
926     /**
927      * Check if the date portion of a Timestamp value
928      * is equal to the date portion of a time value
929      * represented in milli-seconds since 1970.
930      */
931     private boolean isDateEqual(long d, Timestamp tsv)
932     {
933         cal.clear();
934         cal.setTime(new java.util.Date (d));
935         int day = cal.get(Calendar.DAY_OF_MONTH);
936         int month = cal.get(Calendar.MONTH);
937         int year = cal.get(Calendar.YEAR);
938         
939         cal.clear();
940         cal.setTime(tsv);
941         
942         return day == cal.get(Calendar.DAY_OF_MONTH)
943            && month == cal.get(Calendar.MONTH)
944            && year == cal.get(Calendar.YEAR);
945     }
946 }
947

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags