org.hsqldb.test.TestSqlPeriodPredicates Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of hsqldb Show documentation
Show all versions of hsqldb Show documentation
HSQLDB - Lightweight 100% Java SQL Database Engine
/* Copyright (c) 2001-2019, The HSQL Development Group
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* Redistributions of source code must retain the above copyright notice, this
* list of conditions and the following disclaimer.
*
* Redistributions in binary form must reproduce the above copyright notice,
* this list of conditions and the following disclaimer in the documentation
* and/or other materials provided with the distribution.
*
* Neither the name of the HSQL Development Group nor the names of its
* contributors may be used to endorse or promote products derived from this
* software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
* OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
* EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
package org.hsqldb.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLDataException;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import junit.framework.Assert;
/**
* Base tests for the period predicates queries.
*
* @author Pascal-Eric Servais (peservais at users dot sourceforge dot net)
* @version 2.3.4
* @since 2.3.4
*/
public class TestSqlPeriodPredicates extends TestBase {
private Connection conn;
public TestSqlPeriodPredicates(String name) throws Exception {
super(name, "jdbc:hsqldb:mem:test", true, false);
}
public void setUp() throws Exception {
super.setUp();
conn = newConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate("DROP TABLE PUBLIC.emp IF EXISTS");
stmt.executeUpdate("CREATE TABLE PUBLIC.emp (emp_id INTEGER NOT NULL,name VARCHAR(30),salary DECIMAL(10,2),dept_id INTEGER,bus_start DATETIME NOT NULL,bus_end DATETIME NOT NULL);");
stmt.executeUpdate(
"insert into PUBLIC.emp (emp_id, name, salary, dept_id, bus_start, bus_end)"
+ "values"
+ "(1, 'Tom', 300000.00, 1, TIMESTAMP '2000-01-01 01:02:03', TIMESTAMP '2000-02-01 01:02:03'),"
+ "(2, 'Tom', 305000.00, 1, TIMESTAMP '2000-02-01 01:02:03', TIMESTAMP '2000-03-01 01:02:03'),"
+ "(3, 'Tom', 310000.00, 1, TIMESTAMP '2000-03-01 01:02:03', TIMESTAMP '2000-04-01 01:02:03')"
+ ";");
stmt.close();
}
public void tearDown() {
super.tearDown();
}
/**
* Set the two parameters of the prepared statement, execute the query and validate the returned result set.
*/
private void executeAndTestQuery(PreparedStatement stmt, String periodStart, String periodEnd, int... expectedIds) throws SQLException {
stmt.setString(1, periodStart);
stmt.setString(2, periodEnd);
ResultSet rs = stmt.executeQuery();
assertAllIdsPresent(rs, expectedIds);
rs.close();
}
/**
* Validate that all the expected ids are contained within the result set.
*
* @param rs result set containing the result of the query
* @param expectedIds list if the expected ids. If null, expects an empty result set.
* @throws SQLException
*/
private void assertAllIdsPresent(ResultSet rs, int ...expectedIds) throws
SQLException {
Set expected = new TreeSet ();
Set found = new TreeSet ();
if (expectedIds != null) {
for (int id : expectedIds) {
expected.add(id);
}
}
while (rs.next()) {
found.add(rs.getInt(1));
}
assertEquals(expected.size(), found.size());
assertTrue(found.containsAll(expected));
}
public void testFirstPeriodOverlapsSecondPeriod() throws SQLException {
String query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (BUS_START, BUS_END) OVERLAPS PERIOD (?, ?);";
PreparedStatement stmt = conn.prepareStatement(query);
executeAndTestQuery(stmt, "TIMESTAMP '1999-12-01 01:02:03'",
"TIMESTAMP '2000-01-01 01:02:03'");
executeAndTestQuery(stmt, "TIMESTAMP '1999-12-01 01:02:03'",
"TIMESTAMP '2000-01-12 01:02:03'", 1);
executeAndTestQuery(stmt, "TIMESTAMP '1999-12-01 01:02:03'",
"TIMESTAMP '2000-12-31 01:02:03'", 1, 2, 3);
executeAndTestQuery(stmt, "TIMESTAMP '2000-04-01 01:02:03'",
"TIMESTAMP '2000-05-01 01:02:03'");
stmt.close();
}
public void testFirstPeriodOverlapsSecondPeriodReversed() throws
SQLException {
String query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (?, ?) OVERLAPS PERIOD (BUS_START, BUS_END);";
PreparedStatement stmt = conn.prepareStatement(query);
executeAndTestQuery(stmt, "TIMESTAMP '1999-12-01 01:02:03'",
"TIMESTAMP '2000-01-12 01:02:03'", 1);
executeAndTestQuery(stmt, "TIMESTAMP '1999-12-01 01:02:03'",
"TIMESTAMP '2000-01-01 01:02:03'");
executeAndTestQuery(stmt, "TIMESTAMP '1999-12-01 01:02:03'",
"TIMESTAMP '2000-12-31 01:02:03'", 1, 2, 3);
executeAndTestQuery(stmt, "TIMESTAMP '2000-04-01 01:02:03'",
"TIMESTAMP '2000-05-01 01:02:03'");
stmt.close();
}
public void testFirstPeriodEqualsSecondPeriod() throws SQLException {
String query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (BUS_START, BUS_END) EQUALS PERIOD (?, ?);";
PreparedStatement stmt = conn.prepareStatement(query);
ResultSet rs;
executeAndTestQuery(stmt, "TIMESTAMP '2000-02-01 01:02:03'",
"TIMESTAMP '2000-03-01 01:02:03'", 2);
stmt.close();
query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (?, ?) EQUALS PERIOD (BUS_START, BUS_END);";
stmt = conn.prepareStatement(query);
executeAndTestQuery(stmt, "TIMESTAMP '2000-02-01 01:02:03'",
"TIMESTAMP '2000-03-01 01:02:03'", 2);
stmt.close();
query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (BUS_START, BUS_END) EQUALS PERIOD (TIMESTAMP '2000-03-01 01:02:03', TIMESTAMP '2000-04-01 01:02:03');";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertAllIdsPresent(rs, 3);
rs.close();
stmt.close();
}
public void testFirstPeriodContainsSecondPeriod() throws SQLException {
String query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (BUS_START, BUS_END) CONTAINS PERIOD (?, ?);";
PreparedStatement stmt = conn.prepareStatement(query);
executeAndTestQuery(stmt, "TIMESTAMP '2000-02-01 01:02:03'",
"TIMESTAMP '2000-02-01 01:02:04'", 2);
executeAndTestQuery(stmt, "TIMESTAMP '1999-03-01 01:02:03'",
"TIMESTAMP '1999-04-01 01:02:03'");
executeAndTestQuery(stmt, "TIMESTAMP '2000-03-01 01:02:03'",
"TIMESTAMP '2000-03-30 01:02:03'", 3);
stmt.close();
query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (?, ?) CONTAINS PERIOD (BUS_START, BUS_END);";
stmt = conn.prepareStatement(query);
executeAndTestQuery(stmt, "TIMESTAMP '1999-03-01 01:02:03'",
"TIMESTAMP '2001-03-01 01:02:03'", 1, 2, 3);
executeAndTestQuery(stmt, "TIMESTAMP '2000-01-01 01:02:03'",
"TIMESTAMP '2001-04-01 01:02:03'", 1, 2, 3);
executeAndTestQuery(stmt, "TIMESTAMP '2000-01-31 01:02:03'",
"TIMESTAMP '2000-03-01 01:02:03'", 2);
stmt.close();
}
public void testFirstPeriodContainsDate() throws SQLException {
String query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (BUS_START, BUS_END) CONTAINS TIMESTAMP '2000-02-01 01:02:03';";
PreparedStatement stmt = conn.prepareStatement(query);
ResultSet rs;
rs = stmt.executeQuery();
assertAllIdsPresent(rs, 2);
rs.close();
stmt.close();
query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (BUS_START, BUS_END) CONTAINS TIMESTAMP '1999-02-01 01:02:03';";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertAllIdsPresent(rs);
rs.close();
stmt.close();
}
public void testFirstPeriodPrecedesSecondPeriod() throws SQLException {
String query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (BUS_START, BUS_END) PRECEDES PERIOD (?, ?);";
PreparedStatement stmt = conn.prepareStatement(query);
executeAndTestQuery(stmt, "TIMESTAMP '2000-04-01 01:02:03'",
"TIMESTAMP '2000-05-01 01:02:03'", 1, 2, 3);
executeAndTestQuery(stmt, "TIMESTAMP '2000-03-31 01:02:03'",
"TIMESTAMP '2000-05-01 01:02:03'", 1, 2);
executeAndTestQuery(stmt, "TIMESTAMP '1999-03-01 01:02:03'",
"TIMESTAMP '1999-03-30 01:02:03'");
executeAndTestQuery(stmt, "TIMESTAMP '1999-12-01 01:02:03'",
"TIMESTAMP '2000-01-12 01:02:03'");
executeAndTestQuery(stmt, "TIMESTAMP '2000-02-01 01:02:03'",
"TIMESTAMP '2000-05-01 01:02:03'", 1);
stmt.close();
}
public void testFirstPeriodSuccedesSecondPeriod() throws SQLException {
String query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (BUS_START, BUS_END) SUCCEEDS PERIOD (?, ?);";
PreparedStatement stmt = conn.prepareStatement(query);
executeAndTestQuery(stmt, "TIMESTAMP '2000-04-01 01:02:03'",
"TIMESTAMP '2000-05-01 01:02:03'");
executeAndTestQuery(stmt, "TIMESTAMP '1999-03-01 01:02:03'",
"TIMESTAMP '1999-03-30 01:02:03'", 1, 2, 3);
executeAndTestQuery(stmt, "TIMESTAMP '1999-12-01 01:02:03'",
"TIMESTAMP '2000-01-12 01:02:03'", 2, 3);
executeAndTestQuery(stmt, "TIMESTAMP '2000-01-01 01:02:03'",
"TIMESTAMP '2000-03-01 01:02:03'", 3);
executeAndTestQuery(stmt, "TIMESTAMP '2000-02-01 01:02:03'",
"TIMESTAMP '2000-03-01 01:02:03'", 3);
stmt.close();
}
public void testFirstPeriodImmediatelyPrecedesSecondPeriod() throws
SQLException {
String query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (BUS_START, BUS_END) IMMEDIATELY PRECEDES PERIOD (?, ?);";
PreparedStatement stmt = conn.prepareStatement(query);
executeAndTestQuery(stmt, "TIMESTAMP '2000-04-01 01:02:03'",
"TIMESTAMP '2000-05-01 01:02:03'", 3);
executeAndTestQuery(stmt, "TIMESTAMP '2000-03-31 01:02:03'",
"TIMESTAMP '2000-05-01 01:02:03'");
executeAndTestQuery(stmt, "TIMESTAMP '1999-03-01 01:02:03'",
"TIMESTAMP '1999-03-30 01:02:03'");
executeAndTestQuery(stmt, "TIMESTAMP '2000-02-01 01:02:03'",
"TIMESTAMP '2000-05-01 01:02:03'", 1);
stmt.close();
}
public void testFirstPeriodImmediatelySuccedesSecondPeriod() throws
SQLException {
String query = "SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (BUS_START, BUS_END) IMMEDIATELY SUCCEEDS PERIOD (?, ?);";
PreparedStatement stmt = conn.prepareStatement(query);
executeAndTestQuery(stmt, "TIMESTAMP '2000-04-01 01:02:03'",
"TIMESTAMP '2000-05-01 01:02:03'");
executeAndTestQuery(stmt, "TIMESTAMP '1999-12-01 01:02:03'",
"TIMESTAMP '2000-01-01 01:02:03'", 1);
executeAndTestQuery(stmt, "TIMESTAMP '2000-01-01 01:02:03'",
"TIMESTAMP '2000-02-01 01:02:03'", 2);
executeAndTestQuery(stmt, "TIMESTAMP '2000-02-01 01:02:03'",
"TIMESTAMP '2000-03-01 01:02:03'", 3);
stmt.close();
}
/**
* When omiting the {@code PERIOD} keyword, it is possible to use a period constructor
* with the same date as start and and date.
*/
public void testPeriodOverlapsSinglePointInTime() throws SQLException {
String query =
"SELECT emp_id FROM PUBLIC.EMP WHERE (BUS_START, BUS_END) OVERLAPS (?, ?);";
PreparedStatement stmt = conn.prepareStatement(query);
executeAndTestQuery(stmt, "TIMESTAMP '2000-01-11 01:02:03'",
"TIMESTAMP '2000-01-11 01:02:03'", 1);
stmt.close();
query =
"SELECT emp_id FROM PUBLIC.EMP WHERE (?, ?) OVERLAPS (BUS_START, BUS_END);";
stmt = conn.prepareStatement(query);
executeAndTestQuery(stmt, "TIMESTAMP '2000-01-11 01:02:03'",
"TIMESTAMP '2000-01-11 01:02:03'", 1);
stmt.close();
}
/**
* When using the {@code PERIOD} keyword, the start date of the period must be before the end date.
*/
public void testInvalidPeriodDateSpecification() {
PreparedStatement stmt = null;
List predicates = new LinkedList ();
Collections.addAll(predicates, "OVERLAPS", "EQUALS", "CONTAINS",
"PRECEDES", "SUCCEEDS", "IMMEDIATELY PRECEDES",
"IMMEDIATELY SUCCEEDS");
for (String predicate : predicates) {
String query = String.format("SELECT emp_id FROM PUBLIC.EMP WHERE PERIOD (BUS_START, BUS_END) %s PERIOD (?, ?);",
predicate);
try {
stmt = conn.prepareStatement(query);
// testing with start after end
executeAndTestQuery(stmt, "TIMESTAMP '2000-01-11 01:02:03'",
"TIMESTAMP '2000-01-10 01:02:03'");
fail(String.format(
"An exception should have been raised for predicate %s when start is after end!",
predicate));
} catch (SQLDataException e) {
// This is Ok. The test pass.
assertEquals("data exception: invalid period value",
e.getMessage());
} catch (SQLException e) {
e.printStackTrace();
fail(e.getMessage());
}
try {
// testing with start equals end
executeAndTestQuery(stmt, "TIMESTAMP '2000-01-11 01:02:03'",
"TIMESTAMP '2000-01-11 01:02:03'");
fail(String.format(
"An exception should have been raised for predicate %s when start equals end!",
predicate));
} catch (SQLDataException e) {
// This is Ok. The test pass.
assertEquals("data exception: invalid period value",
e.getMessage());
} catch (SQLException e) {
e.printStackTrace();
fail(e.getMessage());
}
}
}
}