001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.sql.Connection;
034: import java.sql.DriverManager;
035: import java.sql.PreparedStatement;
036: import java.sql.ResultSet;
037: import java.sql.SQLException;
038: import java.sql.Statement;
039: import java.sql.Timestamp;
040: import java.text.SimpleDateFormat;
041: import java.util.Locale;
042: import java.util.TimeZone;
043:
044: import junit.framework.Assert;
045: import junit.framework.TestCase;
046:
047: public class TestTimestamp extends TestCase {
048:
049: Connection conn = null;
050: TimeZone timeZone = null;
051: long id = 10;
052: String checkTimestamp = "2003-09-04 16:42:58";
053: String checkTimestampOra = "2003-09-04 16:42:58";
054:
055: public TestTimestamp(String testName) {
056: super (testName);
057: }
058:
059: private void initOracle() throws Exception {
060:
061: Class.forName("oracle.jdbc.driver.OracleDriver");
062:
063: conn = DriverManager.getConnection(
064: "jdbc:oracle:thin:@oracle:1521:MILL", "aaa", "qqq");
065:
066: conn.setAutoCommit(false);
067: }
068:
069: private void initHypersonic() throws Exception {
070:
071: Class.forName("org.hsqldb.jdbcDriver");
072:
073: // conn = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/yourtest", "sa", "");
074: conn = DriverManager.getConnection("jdbc:hsqldb:mem:.", "sa",
075: "");
076:
077: conn.setAutoCommit(false);
078: }
079:
080: /*
081: public void testOracle() throws Exception {
082:
083: nameTable = "AAA_TEST";
084: checkTimestamp = checkTimestampOra;
085:
086: setTimeZone();
087: initOracle();
088: dropAllTables();
089: createTestTable("CREATE TABLE " + nameTable + "(T DATE, id DECIMAL)");
090: createTestTable(
091: "create table \"CASH_CURRENCY\" ( \"ID_CURRENCY\" DECIMAL NOT NULL , \"ID_SITE\" DECIMAL )");
092: createTestTable(
093: "create table \"CASH_CURR_VALUE\" ( \"ID_CURRENCY\" DECIMAL NOT NULL , \"DATE_CHANGE\" DATE DEFAULT sysdate, \"CURS\" DECIMAL, \"ID_CURVAL\" DECIMAL NOT NULL )");
094: insertTestData();
095: conn.createStatement().executeUpdate(
096: "INSERT INTO \"CASH_CURR_VALUE\" VALUES(134, to_date('2003-09-04 16:42:58', 'yyyy-mm-dd hh24:mi:ss'),1.01,155)");
097: conn.createStatement().executeUpdate(
098: "INSERT INTO \"CASH_CURR_VALUE\" VALUES(135, to_date('"
099: + checkTimestamp + "', 'yyyy-mm-dd hh24:mi:ss'),34.51,156)");
100: doTest();
101:
102: // dropTestTable();
103: }
104: */
105: private void checkExceptionTableExistsOracle(SQLException e) {
106: }
107:
108: private void checkExceptionTableExistsHsql(SQLException e) {
109:
110: Assert
111: .assertTrue(
112: "Error code of SQLException is wrong",
113: e.getErrorCode() == -org.hsqldb.Trace.TABLE_ALREADY_EXISTS);
114: }
115:
116: public void testHypersonic() throws Exception {
117:
118: nameTable = "\"AAA_TEST\"";
119:
120: setTimeZone();
121: initHypersonic();
122: dropAllTables();
123: createTestTable("CREATE TABLE " + nameTable
124: + " (T timestamp, id DECIMAL)");
125:
126: try {
127: createTestTable("CREATE TABLE " + nameTable
128: + " (T timestamp, id DECIMAL)");
129: } catch (SQLException e) {
130: checkExceptionTableExistsHsql(e);
131: }
132:
133: // conn.createStatement().execute("create table \"SITE_LIST_SITE\" ( \"ID_SITE\" DECIMAL NOT NULL , \"ID_FIRM\" DECIMAL, \"DEF_LANGUAGE\" VARCHAR NOT NULL , \"DEF_COUNTRY\" VARCHAR NOT NULL , \"DEF_VARIANT\" VARCHAR, \"NAME_SITE\" VARCHAR NOT NULL , \"ADMIN_EMAIL\" VARCHAR, \"IS_CSS_DYNAMIC\" DECIMAL DEFAULT 0 NOT NULL , \"CSS_FILE\" VARCHAR DEFAULT '/front_styles.css', \"IS_REGISTER_ALLOWED\" DECIMAL DEFAULT 1 NOT NULL , \"ORDER_EMAIL\" VARCHAR, \"IS_ACTIVATE_EMAIL_ORDER\" DECIMAL DEFAULT 0 NOT NULL , CONSTRAINT ID_SITE_SLS_PK PRIMARY KEY ( ID_SITE ) )");
134: // conn.createStatement().execute("create table \"SITE_VIRTUAL_HOST\" ( \"ID_SITE_VIRTUAL_HOST\" DECIMAL NOT NULL , \"ID_SITE\" DECIMAL NOT NULL , \"NAME_VIRTUAL_HOST\" VARCHAR NOT NULL , CONSTRAINT ID_VIRT_HST_SVH_PK PRIMARY KEY ( ID_SITE_VIRTUAL_HOST ) )");
135: // conn.createStatement().execute("create table \"SITE_SUPPORT_LANGUAGE\" ( \"ID_SITE_SUPPORT_LANGUAGE\" DECIMAL NOT NULL , \"ID_SITE\" DECIMAL, \"ID_LANGUAGE\" DECIMAL, \"CUSTOM_LANGUAGE\" VARCHAR, \"NAME_CUSTOM_LANGUAGE\" VARCHAR, CONSTRAINT ID_SITE_LNG_SSL_PK PRIMARY KEY ( ID_SITE_SUPPORT_LANGUAGE ) )");
136: // conn.createStatement().execute("create table \"CASH_CURRENCY\" ( \"ID_CURRENCY\" DECIMAL NOT NULL , \"CURRENCY\" VARCHAR, \"IS_USED\" DECIMAL, \"NAME_CURRENCY\" VARCHAR, \"IS_USE_STANDART\" DECIMAL DEFAULT 0, \"ID_STANDART_CURS\" DECIMAL, \"ID_SITE\" DECIMAL, \"PERCENT_VALUE\" DECIMAL, CONSTRAINT PK_CURRENCY PRIMARY KEY ( ID_CURRENCY ) )");
137: // conn.createStatement().execute("create table \"CASH_CURR_VALUE\" ( \"ID_CURRENCY\" DECIMAL NOT NULL , \"DATE_CHANGE\" TIMESTAMP DEFAULT sysdate, \"CURS\" DECIMAL, \"ID_CURVAL\" DECIMAL NOT NULL , CONSTRAINT ID_CURVAL_CCV_PK PRIMARY KEY ( ID_CURVAL ) )");
138: createTestTable("create table \"CASH_CURRENCY\" ( \"ID_CURRENCY\" bigint NOT NULL , \"ID_SITE\" bigint )");
139: createTestTable("create table \"CASH_CURR_VALUE\" ( \"ID_CURRENCY\" bigint NOT NULL , \"DATE_CHANGE\" TIMESTAMP DEFAULT sysdate, \"CURS\" bigint, \"ID_CURVAL\" DECIMAL NOT NULL )");
140: insertTestData();
141: conn
142: .createStatement()
143: .executeUpdate(
144: "INSERT INTO \"CASH_CURR_VALUE\" VALUES(134,'2003-09-04 16:42:58.729',1.01,155)");
145: conn.createStatement().executeUpdate(
146: "INSERT INTO \"CASH_CURR_VALUE\" VALUES(135,'"
147: + checkTimestamp + "',34.51,156)");
148: doTest();
149: conn.close();
150:
151: conn = null;
152:
153: // dropTestTable();
154: }
155:
156: private void dropAllTables() throws Exception {
157:
158: dropTestTable(nameTable);
159: dropTestTable("\"SITE_LIST_SITE\"");
160: dropTestTable("\"SITE_VIRTUAL_HOST\"");
161: dropTestTable("\"SITE_SUPPORT_LANGUAGE\"");
162: dropTestTable("\"CASH_CURRENCY\"");
163: dropTestTable("\"CASH_CURR_VALUE\"");
164: }
165:
166: private String nameTable = null;
167:
168: private void doTest() throws Exception {
169:
170: PreparedStatement ps = conn
171: .prepareStatement("select max(T) T1 from " + nameTable
172: + " where ID=?");
173:
174: ps.setLong(1, id);
175:
176: ResultSet rs = ps.executeQuery();
177: boolean isRecordFound = rs.next();
178:
179: Assert.assertTrue("Record in DB not found", isRecordFound);
180:
181: Timestamp ts = rs.getTimestamp("T1");
182:
183: ps.close();
184:
185: ps = null;
186:
187: {
188: Assert.assertTrue("Timestamp not found", ts != null);
189:
190: SimpleDateFormat df = new SimpleDateFormat(
191: "yyyy-MM-dd HH:mm:ss", Locale.ENGLISH);
192:
193: df.setTimeZone(timeZone);
194:
195: String tsString = df.format(ts);
196: String testTsString = df.format(testTS);
197:
198: System.out.println("db timestamp " + tsString
199: + ", test timestamp " + testTsString);
200: Assert.assertTrue("Timestamp is wrong", tsString
201: .equals(testTsString));
202: }
203:
204: {
205: Timestamp cursTs = getCurrentCurs();
206:
207: Assert.assertTrue("Timestamp curs not found",
208: cursTs != null);
209:
210: SimpleDateFormat df = new SimpleDateFormat(
211: "yyyy-MM-dd HH:mm:ss", Locale.ENGLISH);
212:
213: df.setTimeZone(timeZone);
214:
215: String tsString = df.format(cursTs);
216:
217: Assert.assertTrue("Timestamp curs is wrong", tsString
218: .equals(checkTimestamp));
219: System.out.println("db timestamp curs " + tsString
220: + ", test timestamp curs " + checkTimestamp);
221: }
222: }
223:
224: private static Timestamp testTS = new Timestamp(System
225: .currentTimeMillis());
226:
227: private void insertTestData() throws Exception {
228:
229: // conn.createStatement().executeUpdate("INSERT INTO \"SITE_LIST_SITE\" VALUES(23,1,'ru','RU',NULL,'\u041f\u0440\u043e\u0431\u043d\u044b\u0439 \u0441\u0430\u0439\u0442',NULL,0,'''/front_styles.css''',1,NULL,0)");
230: // conn.createStatement().executeUpdate("INSERT INTO \"SITE_VIRTUAL_HOST\" VALUES(36,23,'test-host')");
231: // conn.createStatement().executeUpdate("INSERT INTO \"SITE_SUPPORT_LANGUAGE\" VALUES(115,23,1,'ru_RU','ru_RU')");
232: // conn.createStatement().executeUpdate("INSERT INTO \"CASH_CURRENCY\" VALUES(134,'\u0420\u0443\u0431',1,'\u0420\u0443\u0431',0,3,23,0.0)");
233: // conn.createStatement().executeUpdate("INSERT INTO \"CASH_CURRENCY\" VALUES(135,'EURO',1,'EURO',0,7,23,0.0)");
234: conn.createStatement().executeUpdate(
235: "INSERT INTO \"CASH_CURRENCY\" VALUES(134,23)");
236: conn.createStatement().executeUpdate(
237: "INSERT INTO \"CASH_CURRENCY\" VALUES(135,23)");
238:
239: PreparedStatement ps = conn.prepareStatement("insert into "
240: + nameTable + "(T, ID) values (?, ?)");
241:
242: ps.setTimestamp(1, testTS);
243: ps.setLong(2, id);
244: ps.executeUpdate();
245: ps.close();
246:
247: ps = null;
248:
249: conn.commit();
250: }
251:
252: private void createTestTable(String sql) throws Exception {
253:
254: Statement ps = conn.createStatement();
255:
256: ps.execute(sql);
257: ps.close();
258:
259: ps = null;
260: }
261:
262: private void dropTestTable(String nameTableDrop) throws Exception {
263:
264: String sql = "drop table " + nameTableDrop;
265: Statement ps = conn.createStatement();
266:
267: try {
268: ps.execute(sql);
269: } catch (SQLException e) {
270: }
271:
272: ps.close();
273:
274: ps = null;
275: }
276:
277: private void setTimeZone() {
278:
279: timeZone = TimeZone.getTimeZone("Asia/Irkutsk");
280:
281: TimeZone.setDefault(timeZone);
282: }
283:
284: private Timestamp getCurrentCurs() throws Exception {
285:
286: long idCurrency = 134;
287: long idSite = 23;
288: String sql_ = "select max(f.DATE_CHANGE) LAST_DATE "
289: + "from CASH_CURR_VALUE f, CASH_CURRENCY b "
290: + "where f.ID_CURRENCY=b.ID_CURRENCY and b.ID_SITE=? and f.ID_CURRENCY=? ";
291: PreparedStatement ps = null;
292: ResultSet rs = null;
293: Timestamp stamp = null;
294:
295: try {
296: ps = conn.prepareStatement(sql_);
297:
298: ps.setLong(1, idSite);
299: ps.setLong(2, idCurrency);
300:
301: rs = ps.executeQuery();
302:
303: if (rs.next()) {
304: stamp = rs.getTimestamp("LAST_DATE");
305: } else {
306: return null;
307: }
308: } finally {
309: rs.close();
310: ps.close();
311:
312: rs = null;
313: ps = null;
314: }
315:
316: System.out.println("ts in db " + stamp);
317:
318: if (stamp == null) {
319: return null;
320: }
321:
322: try {
323: SimpleDateFormat df = new SimpleDateFormat(
324: "dd.MM.yyyy HH:mm:ss.SSS", Locale.ENGLISH);
325:
326: df.setTimeZone(timeZone);
327:
328: String st = df.format(stamp);
329:
330: System.out.println("String ts in db " + st);
331: } catch (Throwable th) {
332: System.out.println("Error get timestamp " + th.toString());
333: }
334:
335: sql_ = "select a.ID_CURRENCY, a.DATE_CHANGE, a.CURS "
336: + "from CASH_CURR_VALUE a, CASH_CURRENCY b "
337: + "where a.ID_CURRENCY=b.ID_CURRENCY and "
338: + "b.ID_SITE=? and " + "a.ID_CURRENCY=? and "
339: + "DATE_CHANGE = ?";
340: ps = null;
341: rs = null;
342:
343: double curs;
344: Timestamp tsCurs = null;
345: long idCurrencyCurs;
346:
347: try {
348: ps = conn.prepareStatement(sql_);
349:
350: ps.setLong(1, idSite);
351: ps.setLong(2, idCurrency);
352: ps.setTimestamp(3, stamp);
353:
354: rs = ps.executeQuery();
355:
356: if (rs.next()) {
357: curs = rs.getDouble("CURS");
358: tsCurs = rs.getTimestamp("DATE_CHANGE");
359: }
360:
361: return tsCurs;
362: } finally {
363: rs.close();
364: ps.close();
365:
366: rs = null;
367: ps = null;
368: }
369: }
370: }
|