Java > Open Source Codes > org > opensubsystems > core > persist > db > driver > TimestampTest


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: TimestampTest.java,v 1.15 2007/01/07 06:14:51 bastafidli Exp $
7  *
8  * This program is free software; you can redistribute it and/or modify
9  * it under the terms of the GNU General Public License as published by
10  * the Free Software Foundation; version 2 of the License.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program; if not, write to the Free Software
19  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20  */
21
22 package org.opensubsystems.core.persist.db.driver;
23
24 import java.sql.PreparedStatement ;
25 import java.sql.ResultSet ;
26 import java.sql.SQLException ;
27 import java.text.DateFormat ;
28 import java.util.Calendar ;
29
30 import junit.extensions.TestSetup;
31 import junit.framework.Test;
32 import junit.framework.TestSuite;
33
34 import org.opensubsystems.core.error.OSSException;
35 import org.opensubsystems.core.persist.db.Database;
36 import org.opensubsystems.core.persist.db.DatabaseImpl;
37 import org.opensubsystems.core.persist.db.DatabaseTest;
38 import org.opensubsystems.core.persist.db.DatabaseTestSetup;
39 import org.opensubsystems.core.persist.db.DatabaseTestSuite;
40 import org.opensubsystems.core.util.DatabaseUtils;
41 import org.opensubsystems.core.util.DateUtils;
42
43 /**
44  * All tests related to timestamp functionality.
45  *
46  * To compute timestamp based on the current timestamp in sapdb you can use
47  * one of these:
48  *
49  * select timestamp(date(now()), maketime((24 + hour(now()) - 4) mod 24,
50  * minute(now()), second(now()))) from dual
51  *
52  * select timestamp(date(present), maketime((24 + hour(present) - 4) mod 24,
53  * minute(present), second(present))) from (select now() as present from dual)
54  *
55  * @version $Id: TimestampTest.java,v 1.15 2007/01/07 06:14:51 bastafidli Exp $
56  * @author Miro Halas
57  * @code.reviewer Miro Halas
58  * @code.reviewed Initial revision
59  */
60 public final class TimestampTest
61 {
62    // Constructors /////////////////////////////////////////////////////////////
63
64    /**
65     * Private constructor since this class cannot be instantiated
66     */
67    private TimestampTest(
68    )
69    {
70       // Do nothing
71 }
72    
73    // Public methods ///////////////////////////////////////////////////////////
74
75    /**
76     * Create the suite for this test since this is the only way how to create
77     * test setup which can initialize and shutdown the database for us
78     *
79     * @return Test - suite of tests to run for this database
80     */
81    public static Test suite(
82    )
83    {
84       TestSuite suite = new DatabaseTestSuite("TimestampTest");
85       suite.addTestSuite(TimestampTestInternal.class);
86       TestSetup wrapper = new DatabaseTestSetup(suite);
87
88       return wrapper;
89    }
90
91    /**
92     * Internal class which can be included in other test suites directly without
93     * including the above suite. This allows us to group multiple tests
94     * together and the execute the DatabaseTestSetup only once
95     */
96    public static class TimestampTestInternal extends DatabaseTest
97    {
98       /**
99        * Static initializer
100        */
101       static
102       {
103          // This test use special database schema so make the database aware of it
104 Database dbDatabase;
105    
106          try
107          {
108             dbDatabase = DatabaseImpl.getInstance();
109             // Add schema database tests needs to the database
110 dbDatabase.add(DatabaseTestSchema.class);
111          }
112          catch (OSSException bfeExc)
113          {
114             throw new RuntimeException ("Unexpected exception.", bfeExc);
115          }
116       }
117    
118       /**
119        * Create new timestamp test.
120        *
121        * @param strTestName - name of the test
122        */
123       public TimestampTestInternal(
124          String strTestName
125       )
126       {
127          super(strTestName);
128       }
129    
130       /**
131        * Test the database support for Timestamp objects.
132        * Timestamp object takes into account both, the date and the time portion
133        * of the Java Date.
134        *
135        * This class inserts timestamp into database, then retrieve it back using
136        * the same java date and deletes it using cursor.
137        *
138        * Uses the already setup connection and transaction.
139        * No need to close the connection since base class is doing it for us.
140        *
141        * @throws Throwable - an error has occured during test
142        */
143       public void testBasicTimestampSupport(
144       ) throws Throwable
145       {
146          final String INSERT_TIMESTAMP = "insert into TIMESTAMP_TEST(TIMESTAMP_TEST) values (?)";
147          // See OracleTests class why we need to select tablename.*
148 final String SELECT_TIMESTAMP = "select TIMESTAMP_TEST.* from TIMESTAMP_TEST " +
149                                          "where TIMESTAMP_TEST = ?";
150          final String DELETE_TIMESTAMP = "delete from TIMESTAMP_TEST where TIMESTAMP_TEST = ?";
151          // Set this to maximal possible number of nanoseconds to see if the nanoseconds matter
152 // SapDB retrieves the large value for nanoseconds
153 final int PRESET_NANOSECONDS = 999999999;
154           
155          Calendar calGenerate = Calendar.getInstance();
156          java.sql.Timestamp insertTimestamp;
157          PreparedStatement insertStatement;
158          int iUpdateCount;
159          
160          // Set date of my birthday ;-)
161 calGenerate.set(1974, 9, 15, 1, 2, 3);
162          insertTimestamp = new java.sql.Timestamp (calGenerate.getTimeInMillis());
163          // Set the nanosecond portion
164 insertTimestamp.setNanos(PRESET_NANOSECONDS);
165          
166 // DatabaseConnectionFactoryImpl.getInstance().returnConnection(m_connection);
167 m_transaction.begin();
168 // m_connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
169
170          try
171          {
172             insertStatement = m_connection.prepareStatement(INSERT_TIMESTAMP);
173             insertStatement.setTimestamp(1, insertTimestamp);
174    
175             iUpdateCount = DatabaseUtils.executeUpdateAndClose(insertStatement);
176    
177             m_transaction.commit();
178          }
179          catch (Throwable throwable)
180          {
181             m_transaction.rollback();
182             throw throwable;
183          }
184          
185          assertEquals("Exactly one record with time data shoud have been inserted.",
186                              iUpdateCount, 1);
187          
188          // Now select it back to be sure it is there
189 PreparedStatement selectStatement = null;
190          PreparedStatement deleteStatement = null;
191    
192          ResultSet results = null;
193          java.sql.Timestamp retrievedTimestamp;
194          boolean bHasMoreThanOne;
195          int iDeletedCount = 0;
196    
197 // DatabaseConnectionFactoryImpl.getInstance().returnConnection(m_connection);
198 m_transaction.begin();
199 // m_connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
200
201          try
202          {
203             java.sql.Timestamp selectTimestamp;
204             boolean bUpdatableResultSet = true;
205             
206             try
207             {
208                selectStatement = m_connection.prepareStatement(
209                                     SELECT_TIMESTAMP,
210                                     ResultSet.TYPE_SCROLL_SENSITIVE,
211                                     ResultSet.CONCUR_UPDATABLE);
212             }
213             catch (SQLException sqleExc)
214             {
215                // Maybe it doesn't support updatable results set (e.g HSQLDB)
216 // so try to work around it
217 bUpdatableResultSet = false;
218                selectStatement = m_connection.prepareStatement(SELECT_TIMESTAMP);
219                // Don't catch any exception here, if this doesn't work, something
220 // is really wrong
221 }
222    
223             // Set the same date because it should be exactly the same
224 calGenerate.set(1974, 9, 15, 1, 2, 3);
225             selectTimestamp = new java.sql.Timestamp (calGenerate.getTimeInMillis());
226             // Set the nanosecond portion
227 selectTimestamp.setNanos(PRESET_NANOSECONDS);
228    
229             selectStatement.setTimestamp(1, selectTimestamp);
230             results = selectStatement.executeQuery();
231             
232             // Get the timestamp from the database
233 assertTrue("The inserted timestamp is not in the database.", results.next());
234             retrievedTimestamp = results.getTimestamp(1);
235             // Delete the inserted row
236 if (bUpdatableResultSet)
237             {
238                // This is the optimal way to do it, but not every database supports
239 // updatable ResultSet (e.g. HSQLDB, ResultSetUnitTest tests for this)
240 // so provide an alternative
241 try
242                {
243                   results.deleteRow();
244                   iDeletedCount = 1;
245                }
246                catch (SQLException sqleExc)
247                {
248                   // Maybe it doesn't support updatable results set (e.g Firebird)
249 // so try to work around it
250 bUpdatableResultSet = false;
251                }
252             }
253             if (!bUpdatableResultSet)
254             {
255                deleteStatement = m_connection.prepareStatement(DELETE_TIMESTAMP);
256                deleteStatement.setTimestamp(1, insertTimestamp);
257                iDeletedCount = DatabaseUtils.executeUpdateAndClose(deleteStatement);
258             }
259             // Get the assert check without throwing exception so we can correctly
260 // cleanup
261 bHasMoreThanOne = results.next();
262             m_transaction.commit();
263          }
264          catch (Throwable throwable)
265          {
266             m_transaction.rollback();
267             throw throwable;
268          }
269          finally
270          {
271             DatabaseUtils.closeResultSetAndStatement(results, selectStatement);
272             
273 // DatabaseConnectionFactoryImpl.getInstance().returnConnection(m_connection);
274 m_transaction.begin();
275 // m_connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
276 try
277             {
278                deleteStatement = m_connection.prepareStatement(
279                         "delete from TIMESTAMP_TEST");
280                deleteStatement.execute();
281                m_transaction.commit();
282             }
283             catch (Throwable thr)
284             {
285                m_transaction.rollback();
286                throw thr;
287             }
288             finally
289             {
290                DatabaseUtils.closeStatement(deleteStatement);
291             }
292          }
293          
294          assertFalse("There should be only one inserted timestamp in the database.",
295                             bHasMoreThanOne);
296       
297          assertEquals("Exactly one record with date data shoud have been deleted.",
298                              iDeletedCount, 1);
299    
300          // And now test the timestamp
301 assertNotNull("The inserted timestamp shouldn't be retrieved" +
302                               " as null from the database",
303                               retrievedTimestamp);
304          // Use toSting instead of DateFormat.getDateTimeInstance().format
305 // since format doesn't take nanoseconds into account.
306 assertTrue("The timestamp retrieved from database "
307                     + retrievedTimestamp.toString()
308                     + " is not the same as the inserted one "
309                     + insertTimestamp.toString(),
310                     DateUtils.dateAndTimeEquals(retrievedTimestamp, insertTimestamp));
311                            
312          // SapDB ignores the bottom portion 1000 of nanosecond value
313 assertEquals("The database doesn't support correct retrieval of large nanosecond " +
314                              "portion of the timestamp. The retrieved one is not the same as the " +
315                              "inserted one.",
316                              insertTimestamp.getNanos(), retrievedTimestamp.getNanos());
317       }
318    
319       /**
320        * Test the database support for Timestamp objects.
321        * Timestamp object takes into account both, the date and the time portion
322        * of the Java Date.
323        *
324        * This class inserts timestamp into database, then retrieve it back using
325        * the same java date and deletes it using cursor. This test with small value
326        * for nanoseconds to see how the database behaves.
327        *
328        * Uses the already setup connection and transaction.
329        * No need to close the connection since base class is doing it for us.
330        *
331        * @throws Throwable - an error has occured during test
332        */
333       public void testSmallNanosTimestampSupport(
334       ) throws Throwable
335       {
336          final String INSERT_TIMESTAMP = "insert into TIMESTAMP_TEST(TIMESTAMP_TEST) values (?)";
337          // See OracleTests class why we need to select tablename.*
338 final String SELECT_TIMESTAMP
339                           = "select TIMESTAMP_TEST.* from TIMESTAMP_TEST where TIMESTAMP_TEST = ?";
340          final String DELETE_TIMESTAMP = "delete from TIMESTAMP_TEST where TIMESTAMP_TEST = ?";
341          // Use very small nanosecond number since SapDB Ignores the small nannoseconds
342 final int PRESET_NANOSECONDS = 5;
343           
344          Calendar calGenerate = Calendar.getInstance();
345          java.sql.Timestamp insertTimestamp;
346          PreparedStatement insertStatement;
347          int iUpdateCount;
348          
349          // Set date of my birthday ;-)
350 calGenerate.set(1974, 9, 15, 1, 2, 3);
351          insertTimestamp = new java.sql.Timestamp (calGenerate.getTimeInMillis());
352          // Set the nanosecond portion
353 insertTimestamp.setNanos(PRESET_NANOSECONDS);
354          
355 // DatabaseConnectionFactoryImpl.getInstance().returnConnection(m_connection);
356 m_transaction.begin();
357 // m_connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
358
359          try
360          {
361             insertStatement = m_connection.prepareStatement(INSERT_TIMESTAMP);
362             insertStatement.setTimestamp(1, insertTimestamp);
363    
364             iUpdateCount = DatabaseUtils.executeUpdateAndClose(insertStatement);
365    
366             m_transaction.commit();
367          }
368          catch (Throwable throwable)
369          {
370             m_transaction.rollback();
371             throw throwable;
372          }
373          
374          assertEquals("Exactly one record with time data shoud have been inserted.",
375                       1, iUpdateCount);
376          
377          // Now select it back to be sure it is there
378 PreparedStatement selectStatement = null;
379          PreparedStatement deleteStatement = null;
380          ResultSet results = null;
381          java.sql.Timestamp retrievedTimestamp;
382          boolean bHasMoreThanOne;
383          int iDeletedCount = 0;
384    
385 // DatabaseConnectionFactoryImpl.getInstance().returnConnection(m_connection);
386 m_transaction.begin();
387 // m_connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
388
389          try
390          {
391             java.sql.Timestamp selectTimestamp;
392             boolean bUpdatableResultSet = true;
393             
394             try
395             {
396                selectStatement = m_connection.prepareStatement(
397                                     SELECT_TIMESTAMP,
398                                     ResultSet.TYPE_SCROLL_SENSITIVE,
399                                     ResultSet.CONCUR_UPDATABLE);
400             }
401             catch (SQLException sqleExc)
402             {
403                // Maybe it doesn't support updatable results set (e.g HSQLDB)
404 // so try to work around it
405 bUpdatableResultSet = false;
406                selectStatement = m_connection.prepareStatement(SELECT_TIMESTAMP);
407                // DOn't catch any exception here, if this doesn't work, something
408 // is really wrong
409 }
410    
411             // Set the same date because it should be exactly the same
412 calGenerate.set(1974, 9, 15, 1, 2, 3);
413             selectTimestamp = new java.sql.Timestamp (calGenerate.getTimeInMillis());
414             // Set the nanosecond portion
415 selectTimestamp.setNanos(PRESET_NANOSECONDS);
416             
417             selectStatement.setTimestamp(1, selectTimestamp);
418             results = selectStatement.executeQuery();
419             
420             // Get the timestamp from the database
421 assertTrue("The inserted timestamp is not in the database.",
422                               results.next());
423             retrievedTimestamp = results.getTimestamp(1);
424             // Delete the inserted row
425 if (bUpdatableResultSet)
426             {
427                // This is the optimal way to do it, but not every database supports
428 // updatable ResultSet (e.g. HSQLDB, ResultSetUnitTest tests for this)
429 // so provide an alternative
430 try
431                {
432                   results.deleteRow();
433                   iDeletedCount = 1;
434                }
435                catch (SQLException sqleExc)
436                {
437                   // Maybe it doesn't support updatable results set (e.g Firebird)
438 // so try to work around it
439 bUpdatableResultSet = false;
440                }
441             }
442             if (!bUpdatableResultSet)
443             {
444                deleteStatement = m_connection.prepareStatement(DELETE_TIMESTAMP);
445                deleteStatement.setTimestamp(1, insertTimestamp);
446                iDeletedCount = DatabaseUtils.executeUpdateAndClose(deleteStatement);
447             }
448             // Get the assert check without throwing exception so we can correctly
449 // cleanup
450 bHasMoreThanOne = results.next();
451             m_transaction.commit();
452          }
453          catch (Throwable throwable)
454          {
455             m_transaction.rollback();
456             throw throwable;
457          }
458          finally
459          {
460             DatabaseUtils.closeResultSetAndStatement(results, selectStatement);
461             
462 // DatabaseConnectionFactoryImpl.getInstance().returnConnection(m_connection);
463 m_transaction.begin();
464 // m_connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
465
466             try
467             {
468                deleteStatement = m_connection.prepareStatement(
469                         "delete from TIMESTAMP_TEST");
470                deleteStatement.execute();
471                m_transaction.commit();
472             }
473             catch (Throwable thr)
474             {
475                m_transaction.rollback();
476                throw thr;
477             }
478             finally
479             {
480                DatabaseUtils.closeStatement(deleteStatement);
481             }
482          }
483          
484          assertFalse("There should be only one inserted timestamp in the database.",
485                             bHasMoreThanOne);
486       
487          assertEquals("Exactly one record with date data shoud have been deleted.",
488                              iDeletedCount, 1);
489    
490          // And now test the timestamp
491 assertNotNull("The inserted timestamp shouldn't be retrieved" +
492                               " as null from the database",
493                               retrievedTimestamp);
494          assertTrue("The timestamp retrieved from database "
495                            + DateFormat.getDateTimeInstance().format(retrievedTimestamp)
496                            + " is not the same as the inserted one "
497                            + DateFormat.getDateTimeInstance().format(insertTimestamp),
498                            DateUtils.dateAndTimeEquals(retrievedTimestamp, insertTimestamp));
499    
500          // SapDB ignores nanoseconds smaller than 1000
501 assertEquals("The database doesn't support correct retrieval of small nanosecond " +
502                              "portion of the timestamp. The retrieved one is not the same as the " +
503                              "inserted one.",
504                              insertTimestamp.getNanos(), retrievedTimestamp.getNanos());
505       }
506    
507       /**
508        * Test the database support for Timestamp objects.
509        * Timestamp object takes into account both, the date and the time portion
510        * of the Java Date.
511        *
512        * This class inserts timestamp into database, then retrieve it back using
513        * the same java date and deletes it using cursor.
514        *
515        * Uses the already setup connection and transaction.
516        * No need to close the connection since base class is doing it for us.
517        *
518        * @throws Throwable - an error has occured during test
519        */
520       public void testTimestampNanosSupport(
521       ) throws Throwable
522       {
523          final String INSERT_TIMESTAMP = "insert into TIMESTAMP_TEST(TIMESTAMP_TEST) values (?)";
524          // See OracleTests class why we need to select tablename.*
525 final String SELECT_TIMESTAMP
526                           = "select TIMESTAMP_TEST.* from TIMESTAMP_TEST where TIMESTAMP_TEST = ?";
527          final int PRESET_NANOSECONDS = 5;
528          final int DIFFERENT_PRESET_NANOSECONDS = 999999999;
529           
530          Calendar calGenerate = Calendar.getInstance();
531          java.sql.Timestamp insertTimestamp;
532          PreparedStatement insertStatement;
533          int iUpdateCount;
534          
535          // Set date of my birthday ;-)
536 calGenerate.set(1974, 9, 15, 1, 2, 3);
537          insertTimestamp = new java.sql.Timestamp (calGenerate.getTimeInMillis());
538          // Set the nanosecond portion
539 insertTimestamp.setNanos(PRESET_NANOSECONDS);
540          
541 // DatabaseConnectionFactoryImpl.getInstance().returnConnection(m_connection);
542 m_transaction.begin();
543 // m_connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
544
545          try
546          {
547             insertStatement = m_connection.prepareStatement(INSERT_TIMESTAMP);
548             insertStatement.setTimestamp(1, insertTimestamp);
549    
550             iUpdateCount = DatabaseUtils.executeUpdateAndClose(insertStatement);
551    
552             m_transaction.commit();
553          }
554          catch (Throwable throwable)
555          {
556             m_transaction.rollback();
557             throw throwable;
558          }
559          
560          assertEquals("Exactly one record with time data shoud have been inserted.",
561                              iUpdateCount, 1);
562          
563          // Now select it back to be sure it is there
564 PreparedStatement selectStatement = null;
565          PreparedStatement deleteStatement = null;
566          ResultSet results = null;
567    
568          try
569          {
570             java.sql.Timestamp selectTimestamp;
571             
572             selectStatement = m_connection.prepareStatement(SELECT_TIMESTAMP);
573    
574             // Set the same date using the time we have inserted
575 selectTimestamp = new java.sql.Timestamp (insertTimestamp.getTime());
576             selectTimestamp.setNanos(DIFFERENT_PRESET_NANOSECONDS);
577             
578             selectStatement.setTimestamp(1, selectTimestamp);
579             results = selectStatement.executeQuery();
580             
581             // Get the timestamp from the database
582 assertFalse("The inserted timestamp was retrieved from" +
583                         " the database even though different nanoseconds" +
584                         " were specified and no result should have been retrieved.",
585                         results.next());
586          }
587          catch (Throwable throwable)
588          {
589             throw throwable;
590          }
591          finally
592          {
593             DatabaseUtils.closeResultSetAndStatement(results, selectStatement);
594             
595 // DatabaseConnectionFactoryImpl.getInstance().returnConnection(m_connection);
596 m_transaction.begin();
597 // m_connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
598
599             try
600             {
601                deleteStatement = m_connection.prepareStatement(
602                         "delete from TIMESTAMP_TEST");
603                deleteStatement.executeUpdate();
604                m_transaction.commit();
605             }
606             catch (Throwable thr)
607             {
608                m_transaction.rollback();
609                throw thr;
610             }
611             finally
612             {
613                DatabaseUtils.closeStatement(deleteStatement);
614             }
615          }
616       }
617    }
618 }
619

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags