/* 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/**
 * 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.
/**
     * 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.
Params:
rs – result set containing the result of the query
expectedIds – list if the expected ids. If null, expects an empty result set.
Throws:
SQLException – /**
     * 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<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();
    }

    
When omiting the PERIOD keyword, it is possible to use a period constructor with the same date as start and and date. /**
     * 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 PERIOD keyword, the start date of the period must be before the end date. /**
     * 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<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);
                // 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());
            }
        }
    }

}