/*
 * 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.util.sql;


import java.io.FileReader;
import java.io.IOException;
import java.io.LineNumberReader;
import java.io.PrintWriter;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class MySQLPLRunner {

    private static final String DEFAULT_DELIMITER = ";";
    private Connection connection;
    private boolean stopOnError;
    private boolean autoCommit;
    private PrintWriter logWriter = new PrintWriter(System.out);
    private PrintWriter errorLogWriter = new PrintWriter(System.err);
    private String delimiter = DEFAULT_DELIMITER;
    private boolean fullLineDelimiter = false;
    private static final String DELIMITER_LINE_REGEX = "(?i)delimiter.+";
    private static final String DELIMITER_LINE_SPLIT_REGEX = "(?i)delimiter";
    private boolean printDebug = false;
    /**
     * Default constructor
     */
    public MySQLPLRunner(Connection connection, boolean autoCommit,
            boolean stopOnError,boolean printDebug) {
        this.connection = connection;
        this.autoCommit = autoCommit;
        this.stopOnError = stopOnError;
        this.printDebug=printDebug;
    }

    public void setDelimiter(String delimiter, boolean fullLineDelimiter) {
        this.delimiter = delimiter;
        this.fullLineDelimiter = fullLineDelimiter;
    }

    /**
     * Setter for logWriter property
     *
     * @param logWriter
     *            - the new value of the logWriter property
     */
    public void setLogWriter(PrintWriter logWriter) {
        this.logWriter = logWriter;
    }

    /**
     * Setter for errorLogWriter property
     *
     * @param errorLogWriter
     *            - the new value of the errorLogWriter property
     */
    public void setErrorLogWriter(PrintWriter errorLogWriter) {
        this.errorLogWriter = errorLogWriter;
    }

    /**
     * Runs an SQL script (read in using the Reader parameter)
     *
     * @param reader
     *            - the source of the script
     */
    public void runScript(Reader reader) throws IOException, SQLException {
        try {
            boolean originalAutoCommit = connection.getAutoCommit();
            try {
                if (originalAutoCommit != this.autoCommit) {
                    connection.setAutoCommit(this.autoCommit);
                }
                runScript(connection, reader);
            } finally {
                connection.setAutoCommit(originalAutoCommit);
            }
        } catch (IOException e) {
            throw e;
        } catch (SQLException e) {
            throw e;
        } catch (Exception e) {
            throw new RuntimeException("Error running script.  Cause: " + e, e);
        }
    }

    /**
     * Runs an SQL script (read in using the Reader parameter) using the
     * connection passed in
     *
     * @param conn
     *            - the connection to use for the script
     * @param reader
     *            - the source of the script
     * @throws SQLException
     *             if any SQL errors occur
     * @throws IOException
     *             if there is an error reading from the Reader
     */
    private void runScript(Connection conn, Reader reader) throws IOException,
            SQLException {
        StringBuilder command = null;
        try {
            LineNumberReader lineReader = new LineNumberReader(reader);
            String line = null;
            while ((line = lineReader.readLine()) != null) {
                if (command == null) {
                    command = new StringBuilder();
                }
                String trimmedLine = line.trim();

                if (trimmedLine.length() < 1 || trimmedLine.startsWith("--")
                    || trimmedLine.startsWith("//")) { //NOPMD
                    //println(trimmedLine);
                    // Do nothing
                } else if (!fullLineDelimiter
                        && trimmedLine.endsWith(getDelimiter())
                        || fullLineDelimiter
                        && trimmedLine.equals(getDelimiter())) {


                    Pattern pattern = Pattern.compile(DELIMITER_LINE_REGEX);
                    Matcher matcher = pattern.matcher(trimmedLine);
                    if (matcher.matches()) {
                        setDelimiter(trimmedLine.split(DELIMITER_LINE_SPLIT_REGEX)[1].trim(), fullLineDelimiter);
                        continue;
                        /*line = lineReader.readLine();
                        if (line == null) {
                            break;
                        }
                        trimmedLine = line.trim();*/
                    }

                    if(line!=null && line.endsWith(getDelimiter()) && !DEFAULT_DELIMITER.equalsIgnoreCase(getDelimiter())){
                    	 command.append(line.substring(0, line.lastIndexOf(getDelimiter())));
                    }else{
                    	command.append(line);
                    }

                    command.append(" ");
                    Statement statement = conn.createStatement();
                    if(printDebug)
                    	println(command);
                    //System.out.println(getDelimiter());
                    boolean hasResults = false;
                    if (stopOnError) {
                        hasResults = statement.execute(command.toString());
                    } else {
                        try {
                            statement.execute(command.toString());
                        } catch (SQLException e) {
                            e.fillInStackTrace();
                            printlnError("Error executing: " + command);
                            printlnError(e);
                        }
                    }

                    if (autoCommit && !conn.getAutoCommit()) {
                        conn.commit();
                    }

                    ResultSet rs = statement.getResultSet();
                    if (hasResults && rs != null) {
                        ResultSetMetaData md = rs.getMetaData();
                        int cols = md.getColumnCount();
                        for (int i = 1; i <= cols; i++) {
                            String name = md.getColumnLabel(i);
                            print(name + "\t");
                        }
                        println("");
                        while (rs.next()) {
                            for (int i = 1; i <= cols; i++) {
                                String value = rs.getString(i);
                                print(value + "\t");
                            }
                            println("");
                        }
                    }

                    command = null;
                    try {
                    	if(rs!=null){
                        rs.close();
                    	}
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    try {
                        statement.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                        // Ignore to workaround a bug in Jakarta DBCP
                    }
                    Thread.yield();
                } else {
                    Pattern pattern = Pattern.compile(DELIMITER_LINE_REGEX);
                    Matcher matcher = pattern.matcher(trimmedLine);
                    if (matcher.matches()) {
                        setDelimiter(trimmedLine.split(DELIMITER_LINE_SPLIT_REGEX)[1].trim(), fullLineDelimiter);
                        continue;
                        /*line = lineReader.readLine();
                        if (line == null) {
                            break;
                        }
                        trimmedLine = line.trim();*/
                    }
                    command.append(line);
                    command.append(" ");
                }
            }
            if (!autoCommit) {
                conn.commit();
            }
        } catch (SQLException e) {
            e.fillInStackTrace();
            printlnError("Error executing: " + command);
            printlnError(e);
            throw e;
        } catch (IOException e) {
            e.fillInStackTrace();
            printlnError("Error executing: " + command);
            printlnError(e);
            throw e;
        } finally {
            conn.rollback();
            flush();
        }
    }

    private String getDelimiter() {
        return delimiter;
    }

    private void print(Object o) {
        if (logWriter != null) {
            System.out.print(o);
        }
    }

    private void println(Object o) {
        if (logWriter != null) {
            logWriter.println(o);
        }
    }

    private void printlnError(Object o) {
        if (errorLogWriter != null) {
            errorLogWriter.println(o);
        }
    }

    private void flush() {
        if (logWriter != null) {
            logWriter.flush();
        }
        if (errorLogWriter != null) {
            errorLogWriter.flush();
        }
    }

    public static void main(String args[]){
    	// Creating object of ScriptRunner class
    	  Connection con = null;
    	  String driverName = "com.mysql.jdbc.Driver";
    	  Properties props = null;
    	  try {
              Class.forName(driverName).newInstance();
              props = new Properties();

              props.put("user", "root");

              props.put("password", "root");
              String connectString = "jdbc:mysql://localhost:3306/ranger";
              con = DriverManager.getConnection(connectString, props);


    	MySQLPLRunner scriptRunner = new MySQLPLRunner(con, false, true,true);
     	String aSQLScriptFilePath = "/disk1/zero/jisql-2.0.11/xa_core_db.sql";
    	
    	
    	// Executing SQL Script
     	FileReader reader = new FileReader(aSQLScriptFilePath);
     	
     	try {
     		scriptRunner.runScript(reader);
     	}
     	finally {
     		if (reader != null) {
     			try {
     			reader.close();
     			}
     			catch(IOException ioe) {
     				// Ignore IOException when reader is getting closed
     			}
     		}
     	}

    	
    	  }
          catch (SQLException sqle) {
        	  sqle.printStackTrace();
          }
          catch (ClassNotFoundException cnfe) {
              System.err.println("Cannot find the driver class \"" + driverName + "\" in the current classpath.");
          }
          catch (InstantiationException ie) {
              System.err.println("Cannot instantiate the driver class \"" + driverName + "\"");
              ie.printStackTrace(System.err);
          }
          catch (IllegalAccessException iae) {
              System.err.println("Cannot instantiate the driver class \"" + driverName + "\" because of an IllegalAccessException");
              iae.printStackTrace(System.err);
          }catch (Exception sqle) {
        	  sqle.printStackTrace();
          }
          finally {
              if (con != null) {
                  try {
                	  con.close();
                  }
                  catch (SQLException ignore) {
                      /* ignored */
                  }
              }
          }
    }
}