/*
 
   Derby - Class CallableStatementTestSetup
 
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to you under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at
 
      http://www.apache.org/licenses/LICENSE-2.0
 
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 
 */

package org.apache.derbyTesting.functionTests.tests.jdbc4;

import junit.framework.Assert;
import junit.framework.Test;
import junit.extensions.TestSetup;

import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseJDBCTestSetup;

import java.io.UnsupportedEncodingException;
import java.sql.*;

/**
 * Create the necessary tables, function and procedures for running the 
 * CallableStatement tests under JDK 1.6.
 * Java methods used as functions and procedures are also implemented here,
 * along with helper methods that returns CallableStatements for the various
 * functions and procedures.
 */
public class CallableStatementTestSetup
    extends BaseJDBCTestSetup {

    private static final String SOURCECLASS = "org.apache.derbyTesting." +
        "functionTests.tests.jdbc4.CallableStatementTestSetup.";
    
    /** List of tables to drop on tear-down */
    private static final String[] TABLE_DROPS = new String[] {
        "CSDATA"};
    /** List of functions to drop on tear-down. */
    private static final String[] FUNCTION_DROPS = new String[] {
        "INT_TO_STRING", "GET_BINARY_DB", "GET_VARCHAR_DB"};
    /** List of procedures to drop on tear-down. */
    private static final String[] PROCEDURE_DROPS = new String[] {
        "GET_BINARY_DIRECT"};

    /** Id for row with byte representation of a string. */
    public static final int STRING_BYTES_ID = 1;
    /** 
     * String converted to bytes in UTF-16BE representation. 
     * Note that the charset used matters, and for Derby it must be UTF-16BE.
     */
    public static final String STRING_BYTES =
        "This is a string, converted to bytes and inserted into the database";

    /** Id for row with SQL NULL values. */
    public static final int SQL_NULL_ID = 2;
    
    /**
     * Create a new test setup for the CallableStatementTest.
     *
     * @param test the test/suite to provide setup for.
     */
    public CallableStatementTestSetup(Test test) {
        super(test);
    }

    protected void setUp()
        throws SQLException {
        Connection con = getConnection();
        // Create the tables, functions and procedures we need.
        Statement stmt = con.createStatement();
        // Create table CSDATA and populate
        stmt.execute("CREATE TABLE CSDATA (ID INT PRIMARY KEY," +
                "BINARYDATA VARCHAR(256) FOR BIT DATA, " +
                "CHARDATA VARCHAR(256))");
        PreparedStatement pStmt = 
            con.prepareStatement("INSERT INTO CSDATA VALUES (?,?,?)");
        pStmt.setInt(1, STRING_BYTES_ID);
        try {
            pStmt.setBytes(2, STRING_BYTES.getBytes("UTF-16BE"));
        } catch (UnsupportedEncodingException uee) {
           SQLException sqle = new SQLException(uee.getMessage());
           sqle.initCause(uee);
           throw sqle;
        }
        pStmt.setString(3, STRING_BYTES);
        pStmt.execute();
        pStmt.setInt(1, SQL_NULL_ID);
        pStmt.setNull(2, Types.VARBINARY);
        pStmt.setNull(3, Types.VARCHAR);
        pStmt.execute();
        pStmt.close();

        // Create function INT_TO_STRING
        stmt.execute("CREATE FUNCTION INT_TO_STRING(INTNUM INT) " +
                "RETURNS VARCHAR(10) " +
                "PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA " +
                "EXTERNAL NAME 'java.lang.Integer.toString'");
        // Create procedure GET_BINARY_DIRECT
        stmt.execute("CREATE PROCEDURE GET_BINARY_DIRECT(IN INSTRING " +
                "VARCHAR(40), OUT OUTBYTES VARCHAR(160) FOR BIT DATA) " +
                "DYNAMIC RESULT SETS 0 " +
                "PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA " +
                "EXTERNAL NAME '" + SOURCECLASS + "getBinaryDirect'");
        // Create function GET_BINARY_DB
        stmt.execute("CREATE FUNCTION GET_BINARY_DB(ID INT) " +
                "RETURNS VARCHAR(256) FOR BIT DATA " +
                "PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA " +
                "EXTERNAL NAME '" + SOURCECLASS + "getBinaryFromDb'");
        // Create function GET_VARCHAR_DB
        stmt.execute("CREATE FUNCTION GET_VARCHAR_DB(ID INT) " +
                "RETURNS VARCHAR(256) " +
                "PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA " +
                "EXTERNAL NAME '" + SOURCECLASS + "getVarcharFromDb'");
        stmt.close();
    }

    protected void tearDown()
        throws Exception {
        Connection con = getConnection();
        Statement stmt = con.createStatement();
        // Drop functions
        for (String function : FUNCTION_DROPS) {
            stmt.execute("DROP FUNCTION "  + function);
        }
        // Drop procedures
        for (String procedure : PROCEDURE_DROPS) {
            stmt.execute("DROP PROCEDURE "  + procedure);
        }
        // Drop tables
        for (String table : TABLE_DROPS) {
            stmt.execute("DROP TABLE "  + table);
        }
        stmt.close();
        super.tearDown();
    }

    // Methods for getting CallableStatements

    /**
     * Return function converting an integer to a string.
     * Parameter 1: output - String/VARCHAR
     * Parameter 2: input  - int/INT
     */
    public static CallableStatement getIntToStringFunction(Connection con)
        throws SQLException {
        Assert.assertNotNull("Connection cannot be null", con);
        CallableStatement cStmt = con.prepareCall("?= CALL INT_TO_STRING(?)");
        cStmt.registerOutParameter(1, Types.VARCHAR);
        return cStmt;
    }
    
    /**
     * Return statement for calling procedure that converts a string to a 
     * byte array (UTF-16BE charset).
     * Parameter 1: input  - String/VARCHAR(40)
     * Parameter 2: output - byte[]/VARCHAR(160) FOR BIT DATA
     */
    public static CallableStatement getBinaryDirectProcedure(Connection con)
        throws SQLException {
        Assert.assertNotNull("Connection cannot be null", con);
        CallableStatement cStmt = 
            con.prepareCall("CALL GET_BINARY_DIRECT(?,?)");
        cStmt.registerOutParameter(2, Types.VARBINARY);
        return cStmt;
    }

    /**
     * Return statement for calling getBinaryFromDb function.
     * Parameter 1: return/output - byte[]/VARCHAR FOR BINARY - data from db
     * Parameter 2: input         - int/INT - id for row to fetch
     *
     * @param con database connection.
     * @return statement for executing getBinaryFromDb function.
     */
    public static CallableStatement getBinaryFromDbFunction(Connection con)
        throws SQLException {
        Assert.assertNotNull("Connection cannot be null", con);
        CallableStatement cStmt =
            con.prepareCall("?= CALL GET_BINARY_DB(?)");
        cStmt.registerOutParameter(1, Types.VARBINARY);
        return cStmt;
    }

    /**
     * Return statement for calling getVarcharFromDb function.
     * Parameter 1: return/output - String/VARCHAR - data from db
     * Parameter 2: input         - int/INT - id for row to fetch
     *
     * @param con database connection.
     * @return statement for executing getVarcharFromDb function.
     */
    public static CallableStatement getVarcharFromDbFunction(Connection con)
        throws SQLException {
        Assert.assertNotNull("Connection cannot be null", con);
        CallableStatement cStmt =
            con.prepareCall("?= CALL GET_VARCHAR_DB(?)");
        cStmt.registerOutParameter(1, Types.VARCHAR);
        return cStmt;
    }

    // Methods used as functions and procedures in the db
    
    /**
     * Procedure creating a byte representation of a string.
     *
     * @param inputString a string.
     * @param outputByte string returned as UTF-16BE byte representation.
     */
    public static void getBinaryDirect(String inputString, byte[][] outputByte) {
        try {
            outputByte[0] = inputString.getBytes("UTF-16BE");
        } catch (java.io.UnsupportedEncodingException uee) {
            outputByte[0] = new byte[0];
        }
    }
    
    /**
     * Function fetching binary data from the database.
     *
     * @param id id of row to fetch.
     * @return a byte array.
     */
    public static byte[] getBinaryFromDb(int id) 
        throws Exception {
        Connection con = DriverManager.getConnection("jdbc:default:connection");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT BINARYDATA FROM CSDATA " +
                "WHERE ID = " + id);
        rs.next();
        byte[] bytes = rs.getBytes(1);
        rs.close();
        stmt.close();
        con.close();
        return bytes;
    }
    
    /**
     * Function fetching character data from the database.
     * 
     * @param id id of row to fetch.
     * @return a string.
     */
    public static String getVarcharFromDb(int id) 
        throws Exception {
        Connection con = DriverManager.getConnection("jdbc:default:connection");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT CHARDATA FROM CSDATA " +
                "WHERE ID = " + id);
        rs.next();
        String chardata = rs.getString(1);
        rs.close();
        stmt.close();
        con.close();
        return chardata;
    }

}