package org.mariadb.jdbc;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Before;
import org.junit.Test;

/**
 * This test case tests the "is"-methods in ResultSet: isBeforeFirst(), isFirst(), isLast(),
 * isAfterLast() and isClosed().
 * 
 * @author Lennart Schedin
 */
public class ResultSetCursorTest {
    
    /*
      To use Mysql JDBC and H2 in-memory database add the following to the pom.xml:
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
            <version>5.1.26</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.178</version>
            <scope>test</scope>
        </dependency>

      To enable easy switch between MariaDB JDBC and Mysql JDBC:
      Change the class src/main/java/org/mariadb/jdbc/JDBCUrl.java in the method parse()
      At line 97-ish change to "return null" so the code looks like this: 
      
      if(url.startsWith("jdbc:mysql://")) {
          return null;
      }
      This will make MariaDB JDBC not to accept "jdbc:mysql://" and therefore Mysql will be used.
     */
    
    private Connection connection;
    private Statement statement;
    
    /**
     * Change this method to switch between the 3 different JDBC implementations. Note that H2
     * is an in-memory database that will run inside your JVM and thus no external database server
     * is needed for H2.
     */
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mariadb://localhost:3306/test?user=root");
//        return DriverManager.getConnection("jdbc:mysql://localhost:3306/test?user=root");
//        return DriverManager.getConnection("jdbc:h2:mem:myTestDatabase;DB_CLOSE_DELAY=-1");
    }
    
    @Before
    public void setUp() throws SQLException {
        connection = getConnection();
        statement = connection.createStatement();
        statement.execute("drop table if exists result_set_cursor_pointing_test");
        statement.execute("create table result_set_cursor_pointing_test (id int not null primary key auto_increment, name char(20))");
    }
    
    @Test
    public void isClosed_Test() throws SQLException {
        insertRows(1);
        ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_cursor_pointing_test");
        assertFalse(resultSet.isClosed()); //MariaDB JDBC will fail here
        while(resultSet.next()) {
            assertFalse(resultSet.isClosed());
        }
        assertFalse(resultSet.isClosed());
        resultSet.close();
        assertTrue(resultSet.isClosed());
    }
    
    @Test
    public void isBefore_FirstTest() throws SQLException {
        insertRows(1);
        ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_cursor_pointing_test");
        assertTrue(resultSet.isBeforeFirst());
        while(resultSet.next()) {
            assertFalse(resultSet.isBeforeFirst());
        }
        assertFalse(resultSet.isBeforeFirst());
        resultSet.close();
        try {
            resultSet.isBeforeFirst();
            fail("The above row should have thrown an SQLException"); //MariaDB JDBC will fail here
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }
    
    @Test
    public void isFirst_ZeroRowsTest() throws SQLException {
        insertRows(0);
        ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_cursor_pointing_test");
        assertFalse(resultSet.isFirst());
        
        resultSet.next(); //No more rows after this
        assertFalse(resultSet.isFirst()); //MariaDB JDBC will fail here

        resultSet.close();
        try {
            resultSet.isFirst();
            fail("The above row should have thrown an SQLException");
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }
    
    @Test
    public void isFirst_TwoRowsTest() throws SQLException {
        insertRows(2);
        ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_cursor_pointing_test");
        assertFalse(resultSet.isFirst());
        
        resultSet.next();
        assertTrue(resultSet.isFirst());
        
        resultSet.next();
        assertFalse(resultSet.isFirst());
        
        resultSet.next(); //No more rows after this
        assertFalse(resultSet.isFirst());

        resultSet.close();
        try {
            resultSet.isFirst();
            fail("The above row should have thrown an SQLException"); //MariaDB JDBC will fail here
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }

    @Test
    public void isLast_ZeroRowsTest() throws SQLException {
        insertRows(0);
        ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_cursor_pointing_test");
        assertFalse(resultSet.isLast()); //MariaDB JDBC will fail here
        
        resultSet.next(); //No more rows after this
        assertFalse(resultSet.isLast());

        resultSet.close();
        try {
            resultSet.isLast();
            fail("The above row should have thrown an SQLException");
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }
    
    
    @Test
    public void isLast_TwoRowsTest() throws SQLException {
        insertRows(2);
        ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_cursor_pointing_test");
        assertFalse(resultSet.isLast());
        
        resultSet.next();
        assertFalse(resultSet.isLast());
        
        resultSet.next();
        assertTrue(resultSet.isLast());
        
        resultSet.next(); //No more rows after this
        assertFalse(resultSet.isLast());

        resultSet.close();
        try {
            resultSet.isLast();
            fail("The above row should have thrown an SQLException"); //MariaDB JDBC will fail here
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }
    
    @Test
    public void isAfterLast_ZeroRowsTest() throws SQLException {
        insertRows(0);
        ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_cursor_pointing_test");
        assertFalse(resultSet.isAfterLast());
        
        resultSet.next(); //No more rows after this
        assertFalse(resultSet.isAfterLast()); //MariaDB JDBC will fail here

        resultSet.close();
        try {
            resultSet.isAfterLast();
            fail("The above row should have thrown an SQLException");
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }
    
    @Test
    public void isAfterLast_TwoRowsTest() throws SQLException {
        insertRows(2);
        ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_cursor_pointing_test");
        assertFalse(resultSet.isAfterLast());
        
        resultSet.next();
        assertFalse(resultSet.isAfterLast());
        
        resultSet.next();
        assertFalse(resultSet.isAfterLast());
        
        resultSet.next(); //No more rows after this
        assertTrue(resultSet.isAfterLast());

        resultSet.close();
        try {
            resultSet.isAfterLast();
            fail("The above row should have thrown an SQLException"); //MariaDB JDBC will fail here
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }
    
    private void insertRows(int numberOfRowsToInsert) throws SQLException {
        for (int i = 1; i <= numberOfRowsToInsert; i++) {
            statement.executeUpdate("INSERT INTO result_set_cursor_pointing_test VALUES(" + i + ", 'row" + i + "')");    
        }
    }

    /** This case case is only used for debugging to show the ResultSet state in different phases */
    @Test
    public void debugIterateTest() throws SQLException {
        insertRows(2);
        
        ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_cursor_pointing_test");
        //Print out with JDBC driver that was used (look at the package name too see the difference)
        System.out.println(resultSet.getClass().getName());
        
        
        int i = 0;
        printDebug(Integer.toString(i), resultSet);
        
        while(resultSet.next()) {
            i++;
            printDebug(Integer.toString(i), resultSet);
        }
        
        printDebug("Before close", resultSet);
        resultSet.close();
        
        printDebug("After close", resultSet);
    }

    /** Debug method used to print values from all five methods from a ResultSet at once */
    private void printDebug(String header, ResultSet resultSet) {
        System.out.println();
        System.out.println(header);
        System.out.println("======================");
        
        try {
            System.out.println("isBeforeFirst = " + resultSet.isBeforeFirst());
            System.out.println("isFirst = " + resultSet.isFirst());
            System.out.println("isLast = " + resultSet.isLast());
            System.out.println("isAfterLast = " + resultSet.isAfterLast());
            System.out.println("isClosed = " + resultSet.isClosed());
        } catch (SQLException e) {
            System.out.println("java.sql.SQLException: " + e.getMessage());
        }
    }

}