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 km...@apache.org on 2007/04/23 21:37:25 UTC
svn commit: r531571 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/timestampArith.out suites/derbylang.runall
tests/lang/TimestampArithTest.java tests/lang/_Suite.java
tests/lang/timestampArith.java
Author: kmarsden
Date: Mon Apr 23 12:37:23 2007
New Revision: 531571
URL: http://svn.apache.org/viewvc?view=rev&rev=531571
Log:
DERBY-2539 convert lang/timestampArith.java to JUnit
contributed by Ugo Matrangolo (matra@santippe.dyndns.org)
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TimestampArithTest.java (with props)
Removed:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?view=diff&rev=531571&r1=531570&r2=531571
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Mon Apr 23 12:37:23 2007
@@ -63,7 +63,6 @@
lang/subquery.sql
lang/subquery2.sql
lang/subqueryFlattening.sql
-lang/timestampArith.java
lang/triggerBeforeTrig.sql
lang/triggerGeneral.sql
lang/triggerRecursion.sql
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TimestampArithTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TimestampArithTest.java?view=auto&rev=531571
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TimestampArithTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TimestampArithTest.java Mon Apr 23 12:37:23 2007
@@ -0,0 +1,657 @@
+/**
+ * Derby - Class org.apache.derbyTesting.functionTests.tests.lang.TimestampArithTest
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Types;
+import java.util.Calendar;
+import java.util.GregorianCalendar;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
+
+/**
+ * Test the JDBC TIMESTAMPADD and TIMESTAMPDIFF escape functions.
+ *
+ * Things to test:
+ * + Test each interval type with timestamp, date, and time inputs.
+ * + Test diff with all 9 combinations of datetime input types (timestamp - timestamp, timestamp - date, etc).
+ * + Test PreparedStatements with parameters, '?', in each argument, and Statements. (Statements are prepared
+ * internally so we do not also have to test PrepardStatements without parameters).
+ * + Test with null inputs.
+ * + Test with input string that is convertible to timestamp.
+ * + Test with invalid interval type.
+ * + Test with invalid arguments in the date time arguments.
+ * + Test TIMESTAMPADD with an invalid type in the count argument.
+ * + Test overflow cases.
+ */
+public class TimestampArithTest extends BaseJDBCTestCase {
+
+ /** Abstract class that factors out all the common code for the timestamps tests. */
+ private abstract class OneTest {
+ final int interval; // FRAC_SECOND_INTERVAL, SECOND_INTERVAL, ... or
+ final String expectedSQLState; // Null if no SQLException is expected
+ final String expectedMsg; // Null if no SQLException is expected
+ String sql;
+
+ OneTest(int interval, String expectedSQLState, String expectedMsg) {
+ this.interval = interval;
+ this.expectedSQLState = expectedSQLState;
+ this.expectedMsg = expectedMsg;
+ }
+
+ void runTest() throws SQLException {
+ ResultSet rs = null;
+
+ sql = composeSQL();
+ try {
+ rs = stmt.executeQuery(sql);
+ checkResultSet(rs, sql);
+ if (expectedSQLState != null) {
+ fail("Statement '" + sql + "' did not generate an exception");
+ }
+ } catch (SQLException sqle) {
+ if (expectedSQLState == null) {
+ printStackTrace(sqle);
+ fail("Unexpected exception from statement '" + sql + "'");
+ }
+ assertSQLState("Incorrect SQLState from statement '" + sql + "'", expectedSQLState, sqle);
+ }
+ if (rs != null) {
+ rs.close();
+ rs = null;
+ }
+
+ try {
+ rs = executePS();
+ checkResultSet(rs, sql);
+ if (expectedSQLState != null) {
+ fail("Prepared Statement '" + sql + "' did not generate an exception");
+ }
+ } catch (SQLException sqle) {
+ if (expectedSQLState == null) {
+ printStackTrace(sqle);
+ fail("Unexpected exception from prepared statement '" + sql + "'");
+ }
+ assertSQLState("Incorrect SQLState from prepared statement '" + sql + "'", expectedSQLState, sqle);
+ }
+ if (rs != null) {
+ rs.close();
+ rs = null;
+ }
+ }
+
+ private void checkResultSet(ResultSet rs, String sql) throws SQLException {
+ assertTrue("'" + sql + "' did not return any rows.", rs.next());
+ checkResultRow(rs, sql);
+ assertFalse("'" + sql + "' returned more than one row.", rs.next());
+ }
+
+ abstract String composeSQL();
+ abstract void checkResultRow(ResultSet rs, String sql) throws SQLException;
+ abstract ResultSet executePS() throws SQLException;
+ }
+
+ private class OneDiffTest extends OneTest {
+ private final java.util.Date ts1;
+ private final java.util.Date ts2;
+ private final int expectedDiff;
+ protected boolean expectNull;
+
+ OneDiffTest(int interval, java.util.Date ts1, java.util.Date ts2,
+ int expectedDiff, String expectedSQLState, String expectedMsg) {
+ super(interval, expectedSQLState, expectedMsg);
+ this.ts1 = ts1;
+ this.ts2 = ts2;
+ this.expectedDiff = expectedDiff;
+ expectNull = (ts1 == null) || (ts2 == null);
+ }
+
+ void checkResultRow(ResultSet rs, String sql) throws SQLException {
+ int actualDiff = rs.getInt(1);
+ assertFalse("Unexpected null result from '" + sql + "'.", rs.wasNull() && !expectNull);
+ assertFalse("Expected null result from '" + sql + "'.", !rs.wasNull() && expectNull);
+ assertEquals("Unexpected result from '" + sql + "'.", expectedDiff, actualDiff);
+ }
+
+ String composeSQL() {
+ return composeSqlStr("DIFF", interval, dateTimeToLiteral(ts1),
+ dateTimeToLiteral(ts2));
+ }
+
+ ResultSet executePS() throws SQLException {
+ setDateTime(tsDiffPS[interval], 1, ts1);
+ setDateTime(tsDiffPS[interval], 2, ts2);
+ return tsDiffPS[interval].executeQuery();
+ }
+ }
+
+ private class OneStringDiffTest extends OneDiffTest {
+ private final String ts1;
+ private final String ts2;
+
+ OneStringDiffTest(int interval, String ts1, String ts2,
+ int expectedDiff, String expectedSQLState, String expectedMsg) {
+ super(interval, (java.util.Date) null, (java.util.Date) null,
+ expectedDiff, expectedSQLState, expectedMsg);
+ this.ts1 = ts1;
+ this.ts2 = ts2;
+ expectNull = (ts1 == null) || (ts2 == null);
+ }
+
+ String composeSQL() {
+ return composeSqlStr("DIFF", interval, dateTimeToLiteral(ts1),
+ dateTimeToLiteral(ts2));
+ }
+
+ ResultSet executePS() throws SQLException {
+ tsDiffPS[interval].setString(1, ts1);
+ tsDiffPS[interval].setString(2, ts2);
+ return tsDiffPS[interval].executeQuery();
+ }
+ }
+
+ private class OneAddTest extends OneTest {
+ private final java.util.Date ts;
+ final int count;
+ final java.sql.Timestamp expected;
+
+ OneAddTest(int interval, int count, java.util.Date ts,
+ java.sql.Timestamp expected, String expectedSQLState,
+ String expectedMsg) {
+ super(interval, expectedSQLState, expectedMsg);
+ this.count = count;
+ this.ts = ts;
+ this.expected = expected;
+ }
+
+ String composeSQL() {
+ return composeSqlStr("ADD", interval, String.valueOf(count),
+ dateTimeToLiteral(ts));
+ }
+
+ void checkResultRow(ResultSet rs, String sql) throws SQLException {
+ java.sql.Timestamp actual = rs.getTimestamp(1);
+ assertFalse("Unexpected null result from '" + sql + "'.", (rs.wasNull() || actual == null) && expected != null);
+ assertFalse("Expected null result from '" + sql + "'.", !(rs.wasNull() || actual == null) && expected == null);
+ assertEquals("Unexpected result from '" + sql + "'.", expected, actual);
+ }
+
+ ResultSet executePS() throws SQLException {
+ tsAddPS[interval].setInt(1, count);
+ setDateTime(tsAddPS[interval], 2, ts);
+ return tsAddPS[interval].executeQuery();
+ }
+ }
+
+ private class OneStringAddTest extends OneAddTest {
+ private final String ts;
+
+ OneStringAddTest(int interval, int count, String ts,
+ java.sql.Timestamp expected, String expectedSQLState,
+ String expectedMsg) {
+ super(interval, count, (java.util.Date) null, expected,
+ expectedSQLState, expectedMsg);
+ this.ts = ts;
+ }
+
+ String composeSQL() {
+ return composeSqlStr("ADD", interval, String.valueOf(count),
+ dateTimeToLiteral(ts));
+ }
+
+ ResultSet executePS() throws SQLException {
+ tsAddPS[interval].setInt(1, count);
+ tsAddPS[interval].setString(2, ts);
+ return tsAddPS[interval].executeQuery();
+ }
+ }
+
+ /** timestamp - timestamp */
+ private final OneDiffTest[] diffBetweenTsTests = {
+ new OneDiffTest(FRAC_SECOND_INTERVAL, ts("2005-05-10 08:25:00"), ts("2005-05-10 08:25:00.000001"), 1000, null, null),
+ new OneDiffTest(SECOND_INTERVAL, ts("2005-05-10 08:25:01"), ts("2005-05-10 08:25:00"), -1, null, null),
+ new OneDiffTest(SECOND_INTERVAL, ts("2005-05-10 08:25:00.1"), ts("2005-05-10 08:25:00"), 0, null, null),
+ new OneDiffTest(SECOND_INTERVAL, ts("2005-05-10 08:25:00"), ts("2005-05-10 08:26:00"), 60, null, null),
+ new OneDiffTest(MINUTE_INTERVAL, ts("2005-05-11 08:25:00"), ts("2005-05-10 08:25:00"), -24 * 60, null, null),
+ new OneDiffTest(HOUR_INTERVAL, ts("2005-05-10 08:25:00"), ts("2005-05-11 08:25:00"), 24, null, null),
+ new OneDiffTest(DAY_INTERVAL, ts("2005-05-10 08:25:00"), ts("2005-05-11 08:25:00"), 1, null, null),
+ new OneDiffTest(DAY_INTERVAL, ts("2005-05-10 08:25:01"), ts("2005-05-11 08:25:00"), 0, null, null),
+ new OneDiffTest(WEEK_INTERVAL, ts("2005-02-23 08:25:00"), ts("2005-03-01 08:25:00"), 0, null, null),
+ new OneDiffTest(MONTH_INTERVAL, ts("2005-02-23 08:25:00"), ts("2005-03-23 08:25:00"), 1, null, null),
+ new OneDiffTest(MONTH_INTERVAL, ts("2005-02-23 08:25:01"), ts("2005-03-23 08:25:00"), 0, null, null),
+ new OneDiffTest(QUARTER_INTERVAL, ts("2005-02-23 08:25:00"), ts("2005-05-23 08:25:00"), 1, null, null),
+ new OneDiffTest(QUARTER_INTERVAL, ts("2005-02-23 08:25:01"), ts("2005-05-23 08:25:00"), 0, null, null),
+ new OneDiffTest(YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts("2005-05-23 08:25:00"), 0, null, null),
+ new OneDiffTest(YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts("2006-02-23 08:25:00"), 1, null, null)
+ };
+
+ /** timestamp - date */
+ private final OneDiffTest[] diffBetweenTsAndDateTests = {
+ new OneDiffTest(FRAC_SECOND_INTERVAL, ts("2004-05-10 00:00:00.123456"), dt("2004-05-10"), -123456000, null, null),
+ new OneDiffTest(SECOND_INTERVAL, ts("2004-05-10 08:25:01"), dt("2004-05-10"), -(1 + 60 * (25 + 60 * 8)), null, null),
+ new OneDiffTest(MINUTE_INTERVAL, ts("2004-05-11 08:25:00"), dt("2004-05-10"), -(24 * 60 + 8 * 60 + 25), null, null),
+ new OneDiffTest(HOUR_INTERVAL, ts("2004-02-28 08:25:00"), dt("2004-03-01"), 39, null, null),
+ new OneDiffTest(DAY_INTERVAL, ts("2004-05-10 08:25:00"), dt("2004-05-11"), 0, null, null),
+ new OneDiffTest(WEEK_INTERVAL, ts("2004-02-23 00:00:00"), dt("2004-03-01"), 1, null, null),
+ new OneDiffTest(MONTH_INTERVAL, ts("2004-02-23 08:25:00"), dt("2004-03-24"), 1, null, null),
+ new OneDiffTest(QUARTER_INTERVAL, ts("2004-02-23 08:25:00"), dt("2004-05-24"), 1, null, null),
+ new OneDiffTest(YEAR_INTERVAL, ts("2004-02-23 08:25:00"), dt("2004-05-23"), 0, null, null)
+ };
+
+ /** date - timestamp */
+ private final OneDiffTest[] diffBetweenDateAndTsTests = {
+ new OneDiffTest(FRAC_SECOND_INTERVAL, dt("2004-05-10"), ts("2004-05-10 00:00:00.123456"), 123456000, null, null),
+ new OneDiffTest(SECOND_INTERVAL, dt("2004-05-10"), ts("2004-05-09 23:59:00"), -60, null, null),
+ new OneDiffTest(MINUTE_INTERVAL, dt("2004-05-10"), ts("2004-05-11 08:25:00"), 24 * 60 + 8 * 60 + 25, null, null),
+ new OneDiffTest(HOUR_INTERVAL, dt("2005-03-01"), ts("2005-02-28 08:25:00"), -15, null, null),
+ new OneDiffTest(DAY_INTERVAL, dt("2004-05-10"), ts("2004-05-11 08:25:00"), 1, null, null),
+ new OneDiffTest(WEEK_INTERVAL, dt("2004-03-01"), ts("2004-02-23 00:00:00"), -1, null, null),
+ new OneDiffTest(MONTH_INTERVAL, dt("2005-03-24"), ts("2004-02-23 08:25:00"), -13, null, null),
+ new OneDiffTest(QUARTER_INTERVAL, dt("2004-05-23"), ts("2004-02-23 08:25:01"), 0, null, null),
+ new OneDiffTest(YEAR_INTERVAL, dt("2004-05-23"), ts("2003-02-23 08:25:00"), -1, null, null)
+ };
+
+ /** timestamp + timestamp */
+ private final OneAddTest[] addBetweenTsTests = {
+ new OneAddTest(FRAC_SECOND_INTERVAL, 1000, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:55:00.000001"), null, null),
+ new OneAddTest(SECOND_INTERVAL, 60, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:56:00"), null, null),
+ new OneAddTest(MINUTE_INTERVAL, -1, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:54:00"), null, null),
+ new OneAddTest(HOUR_INTERVAL, 2, ts("2005-05-11 15:55:00"), ts("2005-05-11 17:55:00"), null, null),
+ new OneAddTest(DAY_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-12 15:55:00"), null, null),
+ new OneAddTest(WEEK_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-18 15:55:00"), null, null),
+ new OneAddTest(MONTH_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-06-11 15:55:00"), null, null),
+ new OneAddTest(QUARTER_INTERVAL, 1, ts("2005-10-11 15:55:00"), ts("2006-01-11 15:55:00"), null, null),
+ new OneAddTest(YEAR_INTERVAL, -10, ts("2005-10-11 15:55:00"), ts("1995-10-11 15:55:00"), null, null)
+ };
+
+ /** date + timestamp */
+ private final OneAddTest[] addBetweenDateAndTsTests = {
+ // following gives an error with J2ME j9_foundation 1.1 (DERBY-2225):
+ new OneAddTest(FRAC_SECOND_INTERVAL, -1000, dt("2005-05-11"), ts("2005-05-10 23:59:59.999999"), null, null),
+ new OneAddTest(SECOND_INTERVAL, 60, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
+ new OneAddTest(MINUTE_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
+ new OneAddTest(HOUR_INTERVAL, -2, dt("2005-05-11"), ts("2005-05-10 22:00:00"), null, null),
+ new OneAddTest(DAY_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-12 00:00:00"), null, null),
+ new OneAddTest(WEEK_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-18 00:00:00"), null, null),
+ new OneAddTest(MONTH_INTERVAL, -1, dt("2005-03-29"), ts("2005-02-28 00:00:00"), null, null),
+ new OneAddTest(QUARTER_INTERVAL, -2, dt("2005-05-05"), ts("2004-11-05 00:00:00"), null, null),
+ new OneAddTest(YEAR_INTERVAL, 2, dt("2005-05-05"), ts("2007-05-05 00:00:00"), null, null)
+ };
+
+ private final OneStringDiffTest[] diffBetweenStringTests = {
+ new OneStringDiffTest(SECOND_INTERVAL, "2005-05-10 08:25:00", "2005-05-10 08:26:00", 60, null, null)
+ };
+
+ private final OneStringAddTest[] addBetweenStringTests = {
+ new OneStringAddTest(DAY_INTERVAL, 1, "2005-05-11 15:55:00", ts("2005-05-12 15:55:00"), null, null)
+ };
+
+ /** check overflow conditions */
+ private final OneTest[] overflowTests = {
+ new OneDiffTest(FRAC_SECOND_INTERVAL, ts("2004-05-10 00:00:00.123456"), ts("2004-05-10 00:00:10.123456"), 0, "22003",
+ "The resulting value is outside the range for the data type INTEGER."),
+ new OneDiffTest(FRAC_SECOND_INTERVAL, ts("2004-05-10 00:00:00.123456"), ts("2005-05-10 00:00:00.123456"), 0, "22003",
+ "The resulting value is outside the range for the data type INTEGER."),
+ new OneDiffTest(SECOND_INTERVAL, ts("1904-05-10 00:00:00"), ts("2205-05-10 00:00:00"), 0, "22003",
+ "The resulting value is outside the range for the data type INTEGER."),
+ new OneAddTest(YEAR_INTERVAL, 99999, ts("2004-05-10 00:00:00.123456"), null, "22003",
+ "The resulting value is outside the range for the data type TIMESTAMP.")
+ };
+
+ private final String[][] invalid = {
+ {"values( {fn TIMESTAMPDIFF( SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
+ "Syntax error: Encountered \"SECOND\" at line 1, column 28."},
+ {"values( {fn TIMESTAMPDIFF( , CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
+ "Syntax error: Encountered \",\" at line 1, column 28."},
+ {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 5)})", "42X01",
+ "Syntax error: Encountered \",\" at line 1, column 80."},
+ {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, 'x')})", "42X45",
+ "CHAR is an invalid type for argument number 3 of TIMESTAMPDIFF."},
+ {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, 'x', CURRENT_TIMESTAMP)})", "42X45",
+ "CHAR is an invalid type for argument number 2 of TIMESTAMPDIFF."},
+ {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP)})", "42X01",
+ "Syntax error: Encountered \")\" at line 1, column 61."},
+ {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND)})", "42X01",
+ "Syntax error: Encountered \")\" at line 1, column 42."},
+ {"values( {fn TIMESTAMPADD( x, 1, CURRENT_TIMESTAMP)})", "42X01",
+ "Syntax error: Encountered \"x\" at line 1, column 27."},
+ {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, CURRENT_DATE, CURRENT_TIMESTAMP)})", "42X45",
+ "DATE is an invalid type for argument number 2 of TIMESTAMPADD."},
+ {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 'XX', CURRENT_TIMESTAMP)})", "42X45",
+ "CHAR is an invalid type for argument number 2 of TIMESTAMPADD."},
+ {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1.1, CURRENT_TIMESTAMP)})", "42X45",
+ "DECIMAL is an invalid type for argument number 2 of TIMESTAMPADD."},
+ {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 2.1)})", "42X45",
+ "DECIMAL is an invalid type for argument number 3 of TIMESTAMPADD."},
+ {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 'XX')})", "42X45",
+ "CHAR is an invalid type for argument number 3 of TIMESTAMPADD."},
+ {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1)})", "42X01",
+ "Syntax error: Encountered \")\" at line 1, column 44."},
+ {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND)})", "42X01",
+ "Syntax error: Encountered \")\" at line 1, column 41."}
+ };
+
+ private static final String[] intervalJdbcNames = { "SQL_TSI_FRAC_SECOND",
+ "SQL_TSI_SECOND", "SQL_TSI_MINUTE", "SQL_TSI_HOUR", "SQL_TSI_DAY",
+ "SQL_TSI_WEEK", "SQL_TSI_MONTH", "SQL_TSI_QUARTER", "SQL_TSI_YEAR" };
+
+ private static Statement stmt;
+ private static PreparedStatement[] tsAddPS = new PreparedStatement[intervalJdbcNames.length];
+ private static PreparedStatement[] tsDiffPS = new PreparedStatement[intervalJdbcNames.length];
+
+ private static final int FRAC_SECOND_INTERVAL = 0;
+ private static final int SECOND_INTERVAL = 1;
+ private static final int MINUTE_INTERVAL = 2;
+ private static final int HOUR_INTERVAL = 3;
+ private static final int DAY_INTERVAL = 4;
+ private static final int WEEK_INTERVAL = 5;
+ private static final int MONTH_INTERVAL = 6;
+ private static final int QUARTER_INTERVAL = 7;
+ private static final int YEAR_INTERVAL = 8;
+
+ private static final int ONE_BILLION = 1000000000;
+
+ private static String TODAY;
+ private static String TOMORROW;
+ private static String YEAR_FROM_TOMORROW;
+ private static String YEAR_FROM_TODAY;
+ private static String YESTERDAY;
+ private static String WEEK_FROM_TODAY;
+
+ /**
+ * Basic constructor.
+ */
+ public TimestampArithTest(String name) {
+ super(name);
+ }
+
+ protected void initializeConnection(Connection conn) throws SQLException {
+ conn.setAutoCommit(false);
+ }
+
+ public static Test suite() {
+ return new BaseJDBCTestSetup(new TestSuite(TimestampArithTest.class,
+ "TimestampArithTest")) {
+
+ protected void setUp() throws Exception {
+ Calendar calendar = Calendar.getInstance();
+
+ super.setUp();
+
+ /*
+ * Make sure that we are not so close to midnight that TODAY
+ * might be yesterday before we are finished using it.
+ */
+ while (calendar.get(Calendar.HOUR) == 23
+ && calendar.get(Calendar.MINUTE) >= 58) {
+ try {
+ Thread.sleep((60 - calendar.get(Calendar.SECOND)) * 1000);
+ } catch (InterruptedException ie) {
+ // ignore it
+ }
+ }
+
+ TODAY = isoFormatDate(calendar);
+ calendar.add(Calendar.DATE, -1);
+ YESTERDAY = isoFormatDate(calendar);
+ calendar.add(Calendar.DATE, 2);
+ TOMORROW = isoFormatDate(calendar);
+ calendar.add(Calendar.YEAR, 1);
+ YEAR_FROM_TOMORROW = isoFormatDate(calendar);
+ calendar.add(Calendar.DATE, -1);
+ YEAR_FROM_TODAY = isoFormatDate(calendar);
+ calendar.add(Calendar.YEAR, -1); // today
+ calendar.add(Calendar.DATE, 7);
+ WEEK_FROM_TODAY = isoFormatDate(calendar);
+
+ for (int i = 0; i < intervalJdbcNames.length; i++) {
+ tsAddPS[i] = getConnection().prepareStatement(
+ composeSqlStr("ADD", i, "?", "?"));
+ tsDiffPS[i] = getConnection().prepareStatement(
+ composeSqlStr("DIFF", i, "?", "?"));
+ }
+
+ stmt = getConnection().createStatement();
+ }
+ };
+ }
+
+ public void testDiffBetweenTimestamp() throws SQLException {
+ getConnection();
+
+ for (int i = 0; i < diffBetweenTsTests.length; i++) {
+ diffBetweenTsTests[i].runTest();
+ }
+ }
+
+ public void testDiffBetweenTimestampAndDate() throws SQLException {
+ for (int i = 0; i < diffBetweenTsAndDateTests.length; i++) {
+ diffBetweenTsAndDateTests[i].runTest();
+ }
+ }
+
+ public void testDiffBetweenDateAndTimestamp() throws SQLException {
+ for (int i = 0; i < diffBetweenDateAndTsTests.length; i++) {
+ diffBetweenDateAndTsTests[i].runTest();
+ }
+ }
+
+ public void testAddBetweenTimestamp() throws SQLException {
+ for (int i = 0; i < addBetweenTsTests.length; i++) {
+ addBetweenTsTests[i].runTest();
+ }
+ }
+
+ public void testAddBetweenDateAndTimestamps() throws SQLException {
+ for (int i = 0; i < addBetweenDateAndTsTests.length; i++) {
+ addBetweenDateAndTsTests[i].runTest();
+ }
+ }
+
+ public void testDiffBetweenString() throws SQLException {
+ for (int i = 0; i < diffBetweenStringTests.length; i++) {
+ diffBetweenStringTests[i].runTest();
+ }
+ }
+
+ public void testAddBetweenString() throws SQLException {
+ for (int i = 0; i < addBetweenStringTests.length; i++) {
+ addBetweenStringTests[i].runTest();
+ }
+ }
+
+ public void testOverflow() throws SQLException {
+ for (int i = 0; i < overflowTests.length; i++) {
+ overflowTests[i].runTest();
+ }
+ }
+
+ /**
+ * Tests null inputs, each position, each type.
+ *
+ * @throws SQLException
+ */
+ public void testNullInputs() throws SQLException {
+ tsDiffPS[HOUR_INTERVAL].setTimestamp(1, ts( "2005-05-11 15:26:00"));
+ tsDiffPS[HOUR_INTERVAL].setNull(2, Types.TIMESTAMP);
+ expectNullResult(tsDiffPS[HOUR_INTERVAL], "TIMESTAMPDIFF with null timestamp in third argument");
+
+ tsDiffPS[HOUR_INTERVAL].setNull(2, Types.DATE);
+ expectNullResult(tsDiffPS[HOUR_INTERVAL], "TIMESTAMPDIFF with null date in third argument");
+
+ tsDiffPS[HOUR_INTERVAL].setTimestamp(2, ts( "2005-05-11 15:26:00"));
+ tsDiffPS[HOUR_INTERVAL].setNull(1, Types.TIMESTAMP);
+ expectNullResult(tsDiffPS[HOUR_INTERVAL], "TIMESTAMPDIFF with null timestamp in second argument");
+
+ tsDiffPS[HOUR_INTERVAL].setNull(1, Types.DATE);
+ expectNullResult(tsDiffPS[HOUR_INTERVAL], "TIMESTAMPDIFF with null date in second argument");
+
+ tsAddPS[MINUTE_INTERVAL].setTimestamp(2, ts( "2005-05-11 15:26:00"));
+ tsAddPS[MINUTE_INTERVAL].setNull(1, Types.INTEGER);
+ expectNullResult(tsAddPS[MINUTE_INTERVAL], "TIMESTAMPADD with null integer in second argument");
+
+ tsAddPS[MINUTE_INTERVAL].setInt(1, 1);
+ tsAddPS[MINUTE_INTERVAL].setNull(2, Types.TIMESTAMP);
+ expectNullResult(tsAddPS[MINUTE_INTERVAL], "TIMESTAMPADD with null timestamp in third argument");
+
+ tsAddPS[MINUTE_INTERVAL].setNull(2, Types.DATE);
+ expectNullResult(tsAddPS[MINUTE_INTERVAL], "TIMESTAMPADD with null date in third argument");
+ }
+
+ public void testInvalidLengths() throws SQLException {
+ ResultSet rs;
+
+ for (int i = 0; i < invalid.length; i++) {
+ try {
+ rs = stmt.executeQuery(invalid[i][0]);
+ rs.next();
+ fail(invalid[i][0] + " did not throw an exception.");
+ } catch (SQLException sqle) {
+ assertSQLState("Unexpected SQLState from " + invalid[i][0], invalid[i][1], sqle);
+ }
+ }
+ }
+
+ public void testInvalidArgTypes() throws SQLException {
+ expectException( tsDiffPS[ HOUR_INTERVAL], ts( "2005-05-21 15:26:00"), new Double( 2.0), "XCL12",
+ "TIMESTAMPDIFF with double ts2");
+ expectException( tsDiffPS[ HOUR_INTERVAL], new Double( 2.0), ts( "2005-05-11 15:26:00"), "XCL12",
+ "TIMESTAMPDIFF with double ts1");
+ expectException( tsAddPS[ MINUTE_INTERVAL], new Integer(1), new Integer(-1), "XCL12",
+ "TIMESTAMPADD with int ts");
+ expectException( tsAddPS[ MINUTE_INTERVAL], ts( "2005-05-11 15:26:00"), ts( "2005-05-11 15:26:00"), "XCL12",
+ "TIMESTAMPADD with timestamp count");
+ }
+
+ private static void expectException(PreparedStatement ps, Object obj1, Object obj2,
+ String expectedSQLState, String label) {
+ ResultSet rs;
+
+ try {
+ ps.setObject(1, obj1);
+ ps.setObject(2, obj2);
+ rs = ps.executeQuery();
+ rs.next();
+ fail(label + " did not throw an exception.");
+ } catch (SQLException sqle) {
+ assertSQLState("Unexpected SQLState from " + label, expectedSQLState, sqle);
+ }
+ }
+
+ private static void expectNullResult(PreparedStatement ps, String label) {
+ ResultSet rs;
+ try {
+ rs = ps.executeQuery();
+ assertTrue(label + " returned no rows.", rs.next());
+ assertNull(label + " did not return null.", rs.getObject(1));
+ } catch (SQLException e) {
+ printStackTrace(e);
+ fail("Unexpected SQL exception from " + label);
+ }
+ }
+
+ private static String isoFormatDate(Calendar cal) {
+ StringBuffer sb = new StringBuffer();
+ String s = String.valueOf(cal.get(Calendar.YEAR));
+ for (int i = s.length(); i < 4; i++)
+ sb.append('0');
+ sb.append(s);
+ sb.append('-');
+
+ s = String.valueOf(cal.get(Calendar.MONTH) + 1);
+ for (int i = s.length(); i < 2; i++)
+ sb.append('0');
+ sb.append(s);
+ sb.append('-');
+
+ s = String.valueOf(cal.get(Calendar.DAY_OF_MONTH));
+ for (int i = s.length(); i < 2; i++)
+ sb.append('0');
+ sb.append(s);
+
+ return sb.toString();
+ }
+
+ private static String dateTimeToLiteral(Object ts) {
+ if (ts instanceof java.sql.Timestamp)
+ return "{ts '" + ((java.sql.Timestamp) ts).toString() + "'}";
+ else if (ts instanceof java.sql.Time)
+ return "{t '" + ((java.sql.Time) ts).toString() + "'}";
+ else if (ts instanceof java.sql.Date)
+ return "{d '" + ((java.sql.Date) ts).toString() + "'}";
+ else if (ts instanceof String)
+ return "TIMESTAMP( '" + ((String) ts) + "')";
+ else
+ return ts.toString();
+ }
+
+ private static String composeSqlStr(String fn, int interval, String parm1, String parm2) {
+ return "values( {fn TIMESTAMP" + fn + "( "
+ + intervalJdbcNames[interval] + ", " + parm1 + "," + parm2
+ + ")})";
+ }
+
+ private static void setDateTime(PreparedStatement ps, int parameterIdx,
+ java.util.Date dateTime) throws SQLException {
+ if (dateTime instanceof java.sql.Timestamp)
+ ps.setTimestamp(parameterIdx, (java.sql.Timestamp) dateTime);
+ else if (dateTime instanceof java.sql.Date)
+ ps.setDate(parameterIdx, (java.sql.Date) dateTime);
+ else if (dateTime instanceof java.sql.Time)
+ ps.setTime(parameterIdx, (java.sql.Time) dateTime);
+ else
+ ps.setTimestamp(parameterIdx, (java.sql.Timestamp) dateTime);
+ }
+
+ private static java.sql.Timestamp ts(String s) {
+ // Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff
+ if (s.length() < 29) {
+ // Pad out the fraction with zeros
+ StringBuffer sb = new StringBuffer(s);
+ if (s.length() == 19)
+ sb.append('.');
+ while (sb.length() < 29)
+ sb.append('0');
+ s = sb.toString();
+ }
+ try {
+ return java.sql.Timestamp.valueOf(s);
+ } catch (Exception e) {
+ System.out.println(s + " is not a proper timestamp string.");
+ System.out.println(e.getClass().getName() + ": " + e.getMessage());
+ e.printStackTrace();
+ System.exit(1);
+ return null;
+ }
+ }
+
+ private static java.sql.Date dt(String s) {
+ return java.sql.Date.valueOf(s);
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TimestampArithTest.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?view=diff&rev=531571&r1=531570&r2=531571
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Mon Apr 23 12:37:23 2007
@@ -101,6 +101,7 @@
suite.addTest(SimpleTest.suite());
suite.addTest(ReleaseCompileLocksTest.suite());
suite.addTest(ErrorCodeTest.suite());
+ suite.addTest(TimestampArithTest.suite());
// Add the XML tests, which exist as a separate suite
// so that users can "run all XML tests" easily.