/* 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.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.GregorianCalendar;

Author:
karl/**
 * @author karl
 *
 */
public class TestKarl {

    static final String SHUTDOWN             = "SHUTDOWN";
    static final String SHUTDOWN_IMMEDIATELY = "SHUTDOWN IMMEDIATELY";
    static final String SHUTDOWN_COMPACT     = "SHUTDOWN COMPACT";

    
Params:
args – /**
     *
     * @param args
     */
    public static void main(String[] args) {

        Connection c = null;

        try {

            //copy db
            File f = new File("db");

            if (!f.exists()) {
                f.mkdir();
            } else {
                File[] list = f.listFiles();

                for (int i = 0; i < list.length; i++) {
                    list[i].delete();
                }
            }

            copy("save/brwahl.properties", "db/brwahl.properties");
            copy("save/brwahl.script", "db/brwahl.script");
            copy("save/waehler.csv", "db/waehler.csv");

            // config test
            String  shutdown   = SHUTDOWN;
            boolean autocommit = false;

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

            c = DriverManager.getConnection("jdbc:hsqldb:file:db/brwahl",
                                            "sa", "");

            c.setAutoCommit(autocommit);

            // open ok
            printMeta(c);
            printTable(c, "WAEHLER");

            // test existing
            doUpdateInsertDeleteWaehler(c);
            printTable(c, "WAEHLER");

            // test new
            doCreateTableTest(c);
            printTable(c, "TEST");

            // colse
            Statement st = c.createStatement();

            st.execute(shutdown);
            st.close();
            c.close();

            c = null;

            // reopen test
            System.out.println("\nDB OK? ...");

            c = DriverManager.getConnection("jdbc:hsqldb:file:db/brwahl",
                                            "sa", "");

            c.setAutoCommit(false);
            printTable(c, "WAEHLER");
            printTable(c, "TEST");
            doUpdateInsertDeleteWaehler(c);
            c.rollback();

            if (!autocommit) {
                doCreateTableTest(c);
                c.rollback();
            }

            c.close();

            c = null;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (c != null) {
                try {
                    c.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    
Params:
p_connection – 
Throws:
SQLException – /**
     * @param p_connection
     * @throws SQLException
     */
    private static void doCreateTableTest(Connection p_connection)
    throws SQLException {

        System.out.println("CREATE TESTTABLE START ...");

        Statement st = p_connection.createStatement();

        st.executeUpdate("DROP TABLE TEST IF EXISTS");
        st.executeUpdate("CREATE TABLE TEST (TEST INTEGER)");
        st.close();
        System.out.println("END CREATE TESTTABLE");
        System.out.println("INSERT INTO TESTTABLE START ...");

        PreparedStatement p = p_connection.prepareStatement(
            "INSERT INTO TEST (TEST) values (?)");

        p.setInt(1, 123);
        p.execute();
        p.close();
        System.out.println("END INSERT INTO TESTTABLE");
    }

    
Params:
p_connection – 
Throws:
SQLException – /**
     * @param p_connection
     * @throws SQLException
     */
    private static void doUpdateInsertDeleteWaehler(Connection p_connection)
    throws SQLException {

        System.out.println("UPDATE WAEHLER START ...");

        PreparedStatement p = p_connection.prepareStatement(
            "UPDATE WAEHLER SET AUSTRITTSDATUM=? WHERE NAME=?");

        p.setDate(1, null);
        p.setString(2, "Muster1");
        p.execute();
        p.close();
        System.out.println("END UPDATE WAEHLER");
        System.out.println("INSERT INTO WAEHLER START ...");

        p = p_connection.prepareStatement(
            "INSERT INTO WAEHLER (NAME, AUSTRITTSDATUM) VALUES (?,?)");

        Calendar cal = GregorianCalendar.getInstance();

        p.setString(1, "Muster3");
        p.setDate(2, new Date(cal.getTimeInMillis()), cal);
        p.execute();
        p.close();
        System.out.println("END INSERT INTO WAEHLER");
        System.out.println("DELETE FROM WAEHLER START ...");

        p = p_connection.prepareStatement(
            "DELETE FROM WAEHLER WHERE NAME = ?");

        p.setString(1, "Muster2");
        p.execute();
        p.close();
        System.out.println("END DELETE FROM WAEHLER");
    }

    
Params:
p_connection – 
p_table – 
Throws:
SQLException – /**
     * @param p_connection
     * @param p_table
     * @throws SQLException
     */
    private static void printTable(Connection p_connection,
                                   String p_table) throws SQLException {

        System.out.println("GET TABLE " + p_table + " START ...");

        Statement st        = p_connection.createStatement();
        ResultSet rs        = st.executeQuery("SELECT * FROM " + p_table);
        int       col_count = rs.getMetaData().getColumnCount();

        for (int i = 1; i <= col_count; i++) {
            System.out.print(rs.getMetaData().getColumnLabel(i) + "\t");
        }

        System.out.println("");

        while (rs.next()) {
            for (int i = 1; i <= col_count; i++) {
                System.out.print(rs.getObject(i));
                System.out.print("\t");
            }

            System.out.println("");
        }

        st.close();
        System.out.println("... END GET TABLE " + p_table);
    }

    
Params:
p_connection – 
Throws:
SQLException – /**
     * @param p_connection
     * @throws SQLException
     */
    private static void printMeta(Connection p_connection)
    throws SQLException {

        System.out.println("GET METADATA START ...");

        ResultSet rs = p_connection.getMetaData().getTables(null, null, null,
            null);

        System.out.println(rs.toString());

        int col_count = rs.getMetaData().getColumnCount();

        for (int i = 1; i <= col_count; i++) {
            System.out.print(rs.getMetaData().getColumnLabel(i) + "\t");
        }

        System.out.println("");

        while (rs.next()) {
            for (int i = 1; i <= col_count; i++) {
                System.out.print(rs.getObject(i));
                System.out.print("\t");
            }

            System.out.println("");
        }

        System.out.println("END GET METADATA");
    }

    private static void copy(String src, String dest) throws IOException {

        File inputFile  = new File(src);
        File outputFile = new File(dest);

        if (!inputFile.exists()) {
            return;
        }

        FileInputStream  in  = new FileInputStream(inputFile);
        FileOutputStream out = new FileOutputStream(outputFile);
        int              c;

        while ((c = in.read()) != -1) {
            out.write(c);
        }

        in.close();
        out.close();
    }
}