/* 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.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import junit.framework.TestCase;
import junit.framework.TestResult;

Test sql statements via jdbc against a database with cached tables
Author:
Fred Toussi (fredt@users dot sourceforge.net)/**
 * Test sql statements via jdbc against a database with cached tables
 * @author Fred Toussi (fredt@users dot sourceforge.net)
 */
public class TestSqlPersistent extends TestCase {

    // change the url to reflect your preferred db location and name
//    String url = "jdbc:hsqldb:hsql://localhost/mytest";
    String     url = "jdbc:hsqldb:/hsql/test/testpersistent";
    String     user;
    String     password;
    Statement  stmnt;
    Connection connection;

    public TestSqlPersistent(String name) {
        super(name);
    }

    protected void setUp() throws Exception {

        super.setUp();

        user       = "sa";
        password   = "";
        stmnt      = null;
        connection = null;

        TestUtil.deleteDatabase("/hsql/test/testpersistent");

        try {
            Class.forName("org.hsqldb.jdbc.JDBCDriver");

            connection = DriverManager.getConnection(url, user, password);
            stmnt      = connection.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("TestSqlPersistence.setUp() error: "
                               + e.getMessage());
        }
    }

    
 demonstration of bug fix #482109 - inserting Integers
 and Strings with PreparedStatement.setObject() did not work;
 String, Integer and Array types are inserted and retrieved
 demonstration of retrieving values using different getXXX methods
/**
     *  demonstration of bug fix #482109 - inserting Integers
     *  and Strings with PreparedStatement.setObject() did not work;
     *  String, Integer and Array types are inserted and retrieved<b>
     *
     *  demonstration of retrieving values using different getXXX methods
     */
    public void testInsertObject() {

        Object  stringValue        = null;
        Object  integerValue       = null;
        Object  arrayValue         = null;
        Object  bytearrayValue     = null;
        Object  stringValueResult  = null;
        Object  integerValueResult = null;
        Object  arrayValueResult   = null;
        boolean wasNull            = false;
        String  message            = "DB operation completed";

        try {
            String sqlString = "DROP TABLE PREFERENCE IF EXISTS;"
                               + "CREATE CACHED TABLE PREFERENCE ("
                               + "User_Id INTEGER NOT NULL, "
                               + "Pref_Name VARCHAR(30) NOT NULL, "
                               + "Pref_Value OBJECT NOT NULL, "
                               + "DateCreated DATETIME DEFAULT NOW NOT NULL, "
                               + "PRIMARY KEY(User_Id, Pref_Name) )";

            stmnt.execute(sqlString);

            sqlString = "INSERT INTO PREFERENCE "
                        + "(User_Id,Pref_Name,Pref_Value,DateCreated) "
                        + "VALUES (?,?,?,current_timestamp)";

            PreparedStatement ps = connection.prepareStatement(sqlString);

            // initialise
            stringValue  = "String Value for Preference 1";
            integerValue = Integer.valueOf(1000);
            arrayValue   = new Double[] {
                Double.valueOf(1), Double.valueOf(Double.NaN),
                Double.valueOf(Double.NEGATIVE_INFINITY),
                Double.valueOf(Double.POSITIVE_INFINITY)
            };
            bytearrayValue = new byte[] {
                1, 2, 3, 4, 5, 6,
            };

            // String as Object
            ps.setInt(1, 1);
            ps.setString(2, "String Type Object 1");

// fredt - in order to store Strings in OBJECT columns setObject should
// explicitly be called with a Types.OTHER type
//            ps.setObject(3, stringValue); will throw an exception
            ps.setObject(3, stringValue, Types.OTHER);
            ps.execute();

            // Integer as Object
            ps.setInt(1, 2);
            ps.setString(2, "Integer Type Object 2");

//            ps.setObject(3, integerValue, Types.OTHER); should work too
            ps.setObject(3, integerValue);
            ps.execute();

            // Array as object
            ps.setInt(1, 3);
            ps.setString(2, "Array Type Object 3");
            /*
            ps.setCharacterStream(
                2, new java.io.StringReader("Array Type Object 3"), 19);
            */

            // ps.setObject(3, arrayValue, Types.OTHER); should work too
            ps.setObject(3, arrayValue);
            ps.execute();

            // byte arrray as object
            ps.setInt(1, 3);
            ps.setString(2, "byte Array Type Object 3");
            /*
            ps.setCharacterStream(
                2, new java.io.StringReader("byte Array Type Object 3"), 19);
            */

            // ps.setObject(3, bytearrayValue); will fail
            // must use this to indicate we are inserting into an OTHER column
            ps.setObject(3, bytearrayValue, Types.OTHER);
            ps.execute();

            ResultSet rs     = stmnt.executeQuery("SELECT * FROM PREFERENCE");
            boolean   result = rs.next();

            // a string can be retrieved as a String or a stream
            // as Unicode string
            String str = rs.getString(2);

            System.out.println(str);

            // as Unicode stream
            InputStream is = rs.getUnicodeStream(2);
            int         c;

            while ((c = is.read()) > -1) {
                c = is.read();

                System.out.print((char) c);
            }

            System.out.println();

            // as ASCII stream, ignoring the high order bytes
            is = rs.getAsciiStream(2);

            while ((c = is.read()) > -1) {
                System.out.print((char) c);
            }

            System.out.println();

            // JAVA 2 specific
            // as character stream via a Reader
            Reader re = rs.getCharacterStream(2);

            while ((c = re.read()) > -1) {
                System.out.print((char) c);
            }


            // retrieving objects inserted into the third column
            stringValueResult = rs.getObject(3);

            rs.next();

            integerValueResult = rs.getObject(3);

            rs.next();

            arrayValueResult = rs.getObject(3);

            // how to check if the last retrieved value was null
            wasNull = rs.wasNull();

            // cast objects to original types - will throw if type is wrong
            String   castStringValue      = (String) stringValueResult;
            Integer  castIntegerValue     = (Integer) integerValueResult;
            Double[] castDoubleArrayValue = (Double[]) arrayValueResult;

            {
                sqlString = "DELETE FROM PREFERENCE WHERE user_id = ?";

                PreparedStatement st = connection.prepareStatement(sqlString);

                st.setString(1, "2");

                int ret = st.executeUpdate();

                // here, ret is equal to 1, that is expected
                //conn.commit(); // not needed, as far as AUTO_COMMIT is set to TRUE
                st.close();

                st = connection.prepareStatement(
                    "SELECT user_id FROM PREFERENCE WHERE user_id=?");

                st.setString(1, "2");

                rs = st.executeQuery();

                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } catch (IOException e1) {}

        /*
        boolean success = stringValue.equals(stringValueResult)
                          && integerValue.equals(integerValueResult)
                          && java.util.Arrays.equals((Double[]) arrayValue,
                              (Double[]) arrayValueResult);
        */
        boolean success = true;

        assertEquals(true, success);
    }

    public void testSelectObject() throws IOException {

        String   stringValue        = null;
        Integer  integerValue       = null;
        Double[] arrayValue         = null;
        byte[]   byteArrayValue     = null;
        String   stringValueResult  = null;
        Integer  integerValueResult = null;
        Double[] arrayValueResult   = null;
        boolean  wasNull            = false;
        String   message            = "DB operation completed";

        try {
            String sqlString = "DROP TABLE TESTOBJECT IF EXISTS;"
                               + "CREATE CACHED TABLE TESTOBJECT ("
                               + "ID INTEGER NOT NULL IDENTITY, "
                               + "STOREDOBJECT OTHER, STOREDBIN BINARY(100) )";

            stmnt.execute(sqlString);

            sqlString = "INSERT INTO TESTOBJECT "
                        + "(STOREDOBJECT, STOREDBIN) " + "VALUES (?,?)";

            PreparedStatement ps = connection.prepareStatement(sqlString);

            // initialise
            stringValue  = "Test String Value";
            integerValue = Integer.valueOf(1000);
            arrayValue   = new Double[] {
                Double.valueOf(1), Double.valueOf(Double.NaN),
                Double.valueOf(Double.NEGATIVE_INFINITY),
                Double.valueOf(Double.POSITIVE_INFINITY)
            };
            byteArrayValue = new byte[] {
                1, 2, 3
            };

            // String as Object
// fredt - in order to store Strings in OBJECT columns setObject should
// explicitly be called with a Types.OTHER type
            ps.setObject(1, stringValue, Types.OTHER);
            ps.setBytes(2, byteArrayValue);
            ps.execute();

            // Integer as Object
            ps.setObject(1, integerValue, Types.OTHER);
            ps.setBinaryStream(2, new ByteArrayInputStream(byteArrayValue),
                               byteArrayValue.length);
            ps.execute();

            // Array as object
            ps.setObject(1, arrayValue, Types.OTHER);

            // file as binary - works fine but file path and name has to be modified for test environment
            /*
            int length = (int) new File("c://ft/db.jar").length();
            FileInputStream fis = new FileInputStream("c://ft/db.jar");
            ps.setBinaryStream(2,fis,length);
            */
            ps.execute();

            ResultSet rs     = stmnt.executeQuery("SELECT * FROM TESTOBJECT");
            boolean   result = rs.next();

            // retrieving objects inserted into the third column
            stringValueResult = (String) rs.getObject(2);

            rs.next();

            integerValueResult = (Integer) rs.getObject(2);

            rs.next();

            arrayValueResult = (Double[]) rs.getObject(2);

            // cast objects to original types - will throw if type is wrong
            String   castStringValue      = (String) stringValueResult;
            Integer  castIntegerValue     = (Integer) integerValueResult;
            Double[] castDoubleArrayValue = (Double[]) arrayValueResult;

            for (int i = 0; i < arrayValue.length; i++) {
                if (!arrayValue[i].equals(arrayValueResult[i])) {
                    System.out.println("array mismatch: " + arrayValue[i]
                                       + " : " + arrayValueResult[i]);
                }
            }

            rs.close();
            ps.close();

            sqlString = "SELECT * FROM TESTOBJECT WHERE STOREDOBJECT = ?";
            ps        = connection.prepareStatement(sqlString);

            ps.setObject(1, Integer.valueOf(1000));

            rs = ps.executeQuery();

            rs.next();

            Object returnVal = rs.getObject(2);

            rs.next();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        boolean success = stringValue.equals(stringValueResult)
                          && integerValue.equals(integerValueResult)
                          && java.util.Arrays.equals((Double[]) arrayValue,
                              (Double[]) arrayValueResult);

        assertEquals(true, success);

        try {
            String            sqlString = "drop table objects if exists";
            PreparedStatement ps = connection.prepareStatement(sqlString);

            ps.execute();

            sqlString =
                "create cached table objects (object_id INTEGER IDENTITY,"
                + "object_name VARCHAR(128) NOT NULL,role_name VARCHAR(128) NOT NULL,"
                + "value LONGVARBINARY(1000) NOT NULL,description LONGVARCHAR(1000))";
            ps = connection.prepareStatement(sqlString);

            ps.execute();

            sqlString =
                "INSERT INTO objects VALUES(1, 'name','role',?,'description')";
            ps = connection.prepareStatement(sqlString);

            ps.setBytes(1, new byte[] {
                1, 2, 3, 4, 5
            });
            ps.executeUpdate();

            sqlString = "UPDATE objects SET value = ?, description = ? WHERE "
                        + "object_name = ? AND role_name = ?";
            ps = connection.prepareStatement(sqlString);

            ps.setBytes(1, new byte[] {
                1, 2, 3, 4, 5
            });
            ps.setString(2, "desc");
            ps.setString(3, "new");
            ps.setString(4, "role");
            ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public void testDoubleNaN() {
        doTestDoubleNan(false);
    }

    private void doTestDoubleNan(boolean shutdown) {

        double  value    = 0;
        boolean wasEqual = false;
        String  message  = "DB operation completed";
        String ddl1 =
            "DROP TABLE t1 IF EXISTS;"
            + "CREATE TABLE t1 ( d DECIMAL, f DOUBLE, l BIGINT, i INTEGER, s SMALLINT, t TINYINT, "
            + "dt DATE DEFAULT CURRENT_DATE, ti TIME DEFAULT CURRENT_TIME, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP );";

        try {
            stmnt.execute(ddl1);

            PreparedStatement ps = connection.prepareStatement(
                "INSERT INTO t1 (d,f,l,i,s,t,dt,ti,ts) VALUES (?,?,?,?,?,?,?,?,?)");

            ps.setString(1, "0.2");
            ps.setDouble(2, 0.2);
            ps.setLong(3, java.lang.Long.MAX_VALUE);
            ps.setInt(4, Integer.MAX_VALUE);
            ps.setInt(5, Short.MAX_VALUE);
            ps.setInt(6, 0);
            ps.setDate(7, new java.sql.Date(System.currentTimeMillis()));
            ps.setTime(8, new java.sql.Time(System.currentTimeMillis()));
            ps.setTimestamp(
                9, new java.sql.Timestamp(System.currentTimeMillis()));
            ps.execute();
            ps.setInt(1, 0);
            ps.setDouble(2, java.lang.Double.NaN);
            ps.setLong(3, java.lang.Long.MIN_VALUE);
            ps.setInt(4, Integer.MIN_VALUE);
            ps.setInt(5, Short.MIN_VALUE);
            ps.setInt(6, 0);

            // allowed conversions
            ps.setTimestamp(
                7, new java.sql.Timestamp(System.currentTimeMillis() + 1));
            ps.setTime(8, new java.sql.Time(System.currentTimeMillis() + 1));
            ps.setDate(9, new java.sql.Date(System.currentTimeMillis() + 1));
            ps.execute();

            //
            ps.setInt(1, 0);
            ps.setDouble(2, java.lang.Double.POSITIVE_INFINITY);
            ps.setInt(4, Integer.MIN_VALUE);

            // test conversion
            // ps.setObject(5, Boolean.TRUE); // no longer converts boolean to int
            // ps.setBoolean(5, true);
            ps.setObject(5, Short.valueOf((short) 2), Types.SMALLINT);
            ps.setObject(6, Integer.valueOf(2), Types.TINYINT);

            // allowed conversions
            ps.setObject(7, new java.sql.Date(System.currentTimeMillis() + 2));
            ps.setObject(8, new java.sql.Time(System.currentTimeMillis() + 2));
            ps.setObject(9, new java.sql.Timestamp(System.currentTimeMillis()
                                                   + 2));
            ps.execute();
            ps.setObject(1, Float.valueOf(0), Types.INTEGER);
            ps.setObject(4, Float.valueOf(1), Types.INTEGER);
            ps.setDouble(2, java.lang.Double.NEGATIVE_INFINITY);
            ps.execute();

            ResultSet rs =
                stmnt.executeQuery("SELECT d, f, l, i, s*2, t FROM t1");
            boolean result = rs.next();

            value = rs.getDouble(2);

//            int smallintValue = rs.getShort(3);
            int integerValue = rs.getInt(4);

            if (rs.next()) {
                value        = rs.getDouble(2);
                wasEqual     = Double.isNaN(value);
                integerValue = rs.getInt(4);

                // tests for conversion
                // getInt on DECIMAL
                integerValue = rs.getInt(1);
            }

            if (rs.next()) {
                value    = rs.getDouble(2);
                wasEqual = wasEqual && value == Double.POSITIVE_INFINITY;
            }

            if (rs.next()) {
                value    = rs.getDouble(2);
                wasEqual = wasEqual && value == Double.NEGATIVE_INFINITY;
            }

            rs = stmnt.executeQuery("SELECT MAX(i) FROM t1");

            if (rs.next()) {
                int max = rs.getInt(1);

                System.out.println("Max value for i: " + max);
            }

            try {

                // cause errors
                ps.setString(5, "three");
                fail("update did not fail");
            } catch (SQLException e) {}

            {
                stmnt.execute("drop table CDTYPE if exists");

                // test for the value MAX(column) in an empty table
                stmnt.execute(
                    "CREATE TABLE cdType (ID INTEGER NOT NULL, name VARCHAR(50), PRIMARY KEY(ID))");

                rs = stmnt.executeQuery("SELECT MAX(ID) FROM cdType");

                if (rs.next()) {
                    int max = rs.getInt(1);

                    System.out.println("Max value for ID: " + max);
                } else {
                    System.out.println("Max value for ID not returned");
                }

                stmnt.executeUpdate(
                    "INSERT INTO cdType VALUES (10,'Test String');");
                stmnt.execute("CALL IDENTITY();");

                try {
                    stmnt.executeUpdate(
                        "INSERT INTO cdType VALUES (10,'Test String');");
                } catch (SQLException e1) {
                    connection.rollback();
                }
            }
        } catch (SQLException e) {
            fail(e.getMessage());
        }

        System.out.println("testDoubleNaN complete");

        // assert new behaviour
        assertEquals(true, wasEqual);
    }

    protected void tearDown() {

        try {
            stmnt.execute("SHUTDOWN");
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("TestSql.tearDown() error: " + e.getMessage());
        }
    }

    public static void main(String[] argv) {

        TestResult result = new TestResult();
        TestCase   testC  = new TestSqlPersistent("testInsertObject");
        TestCase   testD  = new TestSqlPersistent("testSelectObject");

        testC.run(result);
        testD.run(result);
        System.out.println("TestSqlPersistent error count: "
                           + result.failureCount());
    }
}