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;
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();
}
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();
}
private void assertAllIdsPresent(ResultSet rs, int ...expectedIds) throws
SQLException {
Set<Integer> expected = new TreeSet<Integer> ();
Set<Integer> found = new TreeSet<Integer> ();
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();
}
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();
}
public void testInvalidPeriodDateSpecification() {
PreparedStatement stmt = null;
List<String> predicates = new LinkedList<String> ();
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);
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) {
assertEquals("data exception: invalid period value",
e.getMessage());
} catch (SQLException e) {
e.printStackTrace();
fail(e.getMessage());
}
try {
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) {
assertEquals("data exception: invalid period value",
e.getMessage());
} catch (SQLException e) {
e.printStackTrace();
fail(e.getMessage());
}
}
}
}