package org.codehaus.mojo.sql;

 /*

  * 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.

  */

 import java.io.BufferedOutputStream;

 import java.io.BufferedReader;

 import java.io.File;

 import java.io.FileInputStream;

 import java.io.FileOutputStream;

 import java.io.FileReader;

 import java.io.IOException;

 import java.io.InputStream;

 import java.io.InputStreamReader;

 import java.io.OutputStream;

 import java.io.PrintStream;

 import java.io.Reader;

 import java.io.StringReader;

 import java.sql.Connection;

 import java.sql.Driver;

 import java.sql.ResultSet;

 import java.sql.ResultSetMetaData;

 import java.sql.SQLException;

 import java.sql.SQLWarning;

 import java.sql.Statement;

 import java.util.ArrayList;

 import java.util.Collections;

 import java.util.Enumeration;

 import java.util.List;

 import java.util.Properties;

 import java.util.StringTokenizer;

 import java.util.Vector;

 import org.apache.commons.io.IOUtils;

 import org.apache.commons.lang.StringEscapeUtils;

 import org.apache.maven.execution.MavenSession;

 import org.apache.maven.plugin.AbstractMojo;

 import org.apache.maven.plugin.MojoExecutionException;

 import org.apache.maven.project.MavenProject;

 import org.apache.maven.settings.Server;

 import org.apache.maven.settings.Settings;

 import org.apache.maven.shared.filtering.MavenFileFilter;

 import org.apache.maven.shared.filtering.MavenFileFilterRequest;

 import org.apache.maven.shared.filtering.MavenFilteringException;

 import org.codehaus.plexus.util.FileUtils;

 import org.codehaus.plexus.util.IOUtil;

 import org.codehaus.plexus.util.StringUtils;

 import org.springframework.core.io.DefaultResourceLoader;

 import org.springframework.core.io.Resource;

 import org.springframework.core.io.ResourceLoader;

 /**

  * Executes SQL against a database.

  *

  * @goal execute

  */

 public class SqlExecMojo extends AbstractMojo {

     /**

      * Call {@link #setOnError(String)} with this value to abort SQL command execution if an error is found.

      */

     public static final String ON_ERROR_ABORT = "abort";

     /**

      * Call {@link #setOnError(String)} with this value to continue SQL command execution until all commands have been

      * attempted, then abort the build if an SQL error occurred in any of the commands.

      */

     public static final String ON_ERROR_ABORT_AFTER = "abortAfter";

     /**

      * Call {@link #setOnError(String)} with this value to continue SQL command execution if an error is found.

      */

     public static final String ON_ERROR_CONTINUE = "continue";

     // ////////////////////////// User Info ///////////////////////////////////

     /**

      * Database username. If not given, it will be looked up through <code>settings.xml</code>'s server with

      * <code>${settingsKey}</code> as key.

      *

      * @since 1.0

      * @parameter expression="${username}"

      */

     private String username;

     /**

      * Database password. If not given, it will be looked up through <code>settings.xml</code>'s server with

      * <code>${settingsKey}</code> as key.

      *

      * @since 1.0

      * @parameter expression="${password}"

      */

     private String password;

     /**

      * Ignore the password and use anonymous access. This may be useful for databases like MySQL which do not allow

      * empty password parameters in the connection initialization.

      *

      * @since 1.4

      * @parameter default-value="false"

      */

     private boolean enableAnonymousPassword;

     /**

      * Additional key=value pairs separated by comma to be passed into JDBC driver.

      *

      * @since 1.0

      * @parameter expression="${driverProperties}" default-value = ""

      */

     private String driverProperties;

     /**

      * @parameter expression="${settings}"

      * @required

      * @since 1.0

      * @readonly

      */

     private Settings settings;

     /**

      * Server's <code>id</code> in <code>settings.xml</code> to look up username and password. Defaults to

      * <code>${url}</code> if not given.

      *

      * @since 1.0

      * @parameter expression="${settingsKey}"

      */

     private String settingsKey;

     /**

      * Skip execution when there is an error obtaining a connection. This is a special case to support databases, such

      * as embedded Derby, that can shutdown the database via the URL (i.e. <code>shutdown=true</code>).

      *

      * @since 1.1

      * @parameter expression="${skipOnConnectionError}" default-value="false"

      */

     private boolean skipOnConnectionError;

     /**

      * Setting this parameter to <code>true</code> will force the execution of this mojo, even if it would get skipped

      * usually.

      *

      * @parameter expression="${forceOpenJpaExecution}" default-value=false

      * @required

      */

     private boolean forceMojoExecution;

     /**

      * The Maven Project Object

      *

      * @parameter default-value="${project}"

      * @required

      * @readonly

      */

     protected MavenProject project;

     /**

      * @parameter default-value="${session}"

      * @required

      * @readonly

      */

     private MavenSession mavenSession;

     // ////////////////////////////// Source info /////////////////////////////

     /**

      * SQL input commands separated by <code>${delimiter}</code>.

      *

      * @since 1.0

      * @parameter expression="${sqlCommand}" default-value=""

      */

     private String sqlCommand = "";

     /**

      * List of files containing SQL statements to load.

      *

      * @since 1.0

      * @parameter

      */

     private File[] srcFiles;

     /**

      * List of resources containing SQL statements

      *

      * @since 1.5

      * @parameter

      */

     private String[] resourceLocations;

     /**

      * File(s) containing SQL statements to load.

      *

      * @since 1.0

      * @parameter

      */

     private Fileset fileset;

     /**

      * When <code>true</code>, skip the execution.

      *

      * @since 1.0

      * @parameter default-value="false"

      */

     private boolean skip;

     // //////////////////////////////// Database info /////////////////////////

     /**

      * Database URL.

      *

      * @parameter expression="${url}"

      * @required

      * @since 1.0-beta-1

      */

     private String url;

     /**

      * Database driver classname.

      *

      * @since 1.0

      * @parameter expression="${driver}"

      * @required

      */

     private String driver;

     // //////////////////////////// Operation Configuration ////////////////////

     /**

      * Set to <code>true</code> to execute none-transactional SQL.

      *

      * @since 1.0

      * @parameter expression="${autocommit}" default-value="false"

      */

     private boolean autocommit;

     /**

      * Action to perform if an error is found. Possible values are <code>abort</code> and <code>continue</code>.

      *

      * @since 1.0

      * @parameter expression="${onError}" default-value="abort"

      */

     private String onError = ON_ERROR_ABORT;

     // //////////////////////////// Parser Configuration ////////////////////

     /**

      * Set the delimiter that separates SQL statements.

      *

      * @since 1.0

      * @parameter expression="${delimiter}" default-value=";"

      */

     private String delimiter = ";";

     /**

      * <p>

      * The delimiter type takes two values - "normal" and "row". Normal means that any occurrence of the delimiter

      * terminate the SQL command whereas with row, only a line containing just the delimiter is recognized as the end of

      * the command.

      * </p>

      * <p>

      * For example, set this to "go" and delimiterType to "row" for Sybase ASE or MS SQL Server.

      * </p>

      *

      * @since 1.2

      * @parameter expression="${delimiterType}" default-value="normal"

      */

     private String delimiterType = DelimiterType.NORMAL;

     /**

      * Set the order in which the SQL files will be executed. Possible values are <code>ASCENDING</code> and

      * <code>DESCENDING</code> and <code>NONE</code>.

      *

      * @since 1.1

      * @parameter expression="${orderFile}" default-value="NONE";

      */

     private Order orderFile;

     /**

      * When <code>true</code>, the whole SQL content in <code>sqlCommand</code>, <code>srcFiles</code> and

      * <code>fileset</code> are sent directly to JDBC in one SQL statement. This option is for executing database stored

      * procedures/functions.

      *

      * @deprecated used <i>delimiterType<i> instead.

      * @since 1.1

      * @parameter expression="${enableBlockMode}"

      */

     @Deprecated

     private boolean enableBlockMode = false;

     /**

      * Keep the format of an SQL block.

      *

      * @since 1.1

      * @parameter expression="${keepFormat}" default-value="false"

      */

     private boolean keepFormat = false;

     // /////////////////////////////////////////////////////////////////////////////////////

     /**

      * Print SQL results.

      *

      * @parameter

      * @since 1.3

      */

     private boolean printResultSet = false;

     /**

      * Print header columns.

      */

     private boolean showheaders = true;

     /**

      * Dump the SQL exection's output to a file. Default is stdout.

      *

      * @parameter

      * @since 1.3

      */

     private File outputFile;

     /**

      * @parameter default-value=","

      * @since 1.4

      */

     private String outputDelimiter;

     /**

      * Encoding to use when reading SQL statements from a file.

      *

      * @parameter expression="${encoding}" default-value= "${project.build.sourceEncoding}"

      * @since 1.1

      */

     private String encoding = "";

     /**

      * Append to an existing file or overwrite it?

      */

     private boolean append = false;

     /**

      * Argument to Statement.setEscapeProcessing If you want the driver to use regular SQL syntax then set this to

      * false.

      *

      * @since 1.4

      * @parameter expression="${escapeProcessing}" default-value="true"

      */

     private boolean escapeProcessing = true;

     // //////////////////////////////// Internal

     // properties//////////////////////

     /**

      * number of successful executed statements

      */

     private int successfulStatements = 0;

     /**

      * number of total executed statements

      */

     private int totalStatements = 0;

     /**

      * Database connection

      */

     private Connection conn = null;

     /**

      * SQL statement

      */

     private Statement statement = null;

     /**

      * SQL transactions to perform

      */

     private Vector<Transaction> transactions = new Vector<Transaction>();

     /**

      * @component role="org.apache.maven.shared.filtering.MavenFileFilter"

      * @since 1.4

      */

     private MavenFileFilter fileFilter;

     /**

      * Set to true if you want to filter the srcFiles using system-, user- and project properties

      *

      * @parameter

      * @since 1.4

      */

     private boolean enableFiltering;

     /**

      * Set to false to disable executing SQL that appears without a delimiter at the end of a file (is enabled by

      * default for backwards compatibility)

      *

      * @parameter expression="${sql.executeTrailingSQL}" default-value="true"

      * @since 1.7

      */

     private boolean executeTrailingSQL;

     /**

      * Interpolator especially for braceless expressions

      */

     // private Interpolator interpolator = new

     // RegexBasedInterpolator("\\$([^\\s;)]+?)", "(?=[\\s;)])");

     /**

      * Add a SQL transaction to execute

      *

      * @return a new SqlExecMojo.Transaction

      */

     public Transaction createTransaction() {

         Transaction t = new Transaction();

         transactions.addElement(t);

         return t;

     }

     /**

      * Set an inline SQL command to execute. NB: Properties are not expanded in this text.

      *

      * @param sql

      *            the sql statement to add

      */

     public void addText(String sql) {

         this.sqlCommand += sql;

     }

     /**

      * Set the file encoding to use on the SQL files read in

      *

      * @param encoding

      *            the encoding to use on the files

      */

     public void setEncoding(String encoding) {

         this.encoding = encoding;

     }

     /**

      * Set the delimiter that separates SQL statements. Defaults to ";";

      *

      * @param delimiter

      *            the new delimiter

      */

     public void setDelimiter(String delimiter) {

         this.delimiter = delimiter;

     }

     /**

      * Set the delimiter type: "normal" or "row" (default "normal").

      *

      * @param delimiterType

      *            the new delimiterType

      */

     public void setDelimiterType(String delimiterType) {

         this.delimiterType = delimiterType;

     }

     /**

      * Print result sets from the statements; optional, default false

      *

      * @param print

      *            <code>true</code> to print the resultset, otherwise <code>false</code>

      * @deprecated typo, use setPrintResultSet()

      */

     @Deprecated

     public void setPrintResutlSet(boolean print) {

         setPrintResultSet(print);

     }

     /**

      * Print result sets from the statements; optional, default false

      *

      * @param print

      *            <code>true</code> to print the resultset, otherwise <code>false</code>

      */

     public void setPrintResultSet(boolean print) {

         this.printResultSet = print;

     }

     /**

      * Print headers for result sets from the statements; optional, default true.

      *

      * @param showheaders

      *            <code>true</code> to show the headers, otherwise <code>false</code>

      */

     public void setShowheaders(boolean showheaders) {

         this.showheaders = showheaders;

     }

     /**

      * Set the output file;

      *

      * @param output

      *            the output file

      */

     public void setOutputFile(File output) {

         this.outputFile = output;

     }

     /**

      * whether output should be appended to or overwrite an existing file. Defaults to false.

      *

      * @param append

      *            <code>true</code> to append, otherwise <code>false</code> to overwrite

      */

     public void setAppend(boolean append) {

         this.append = append;

     }

     /**

      * whether or not format should be preserved. Defaults to false.

      *

      * @param keepformat

      *            The keepformat to set

      */

     public void setKeepFormat(boolean keepformat) {

         this.keepFormat = keepformat;

     }

     /**

      * Set escape processing for statements.

      *

      * @param enable

      *            <code>true</code> to escape, otherwiser <code>false</code>

      */

     public void setEscapeProcessing(boolean enable) {

         escapeProcessing = enable;

     }

     /**

      * <p>

      * Determine if the mojo execution should get skipped.

      * </p>

      * This is the case if:

      * <ul>

      * <li>{@link #skip} is <code>true</code></li>

      * <li>if the mojo gets executed on a project with packaging type 'pom' and {@link #forceMojoExecution} is

      * <code>false</code></li>

      * </ul>

      *

      * @return <code>true</code> if the mojo execution should be skipped.

      */

     protected boolean skipMojo() {

         if (skip) {

             getLog().info("Skip sql execution");

             return true;

         }

         if (!forceMojoExecution && project != null && "pom".equals(project.getPackaging())) {

             getLog().info("Skipping sql execution for project with packaging type 'pom'");

             return true;

         }

         return false;

     }

     /**

      * Load the sql file and then execute it

      *

      * @throws MojoExecutionException

      */

     @Override

     public void execute() throws MojoExecutionException {

         if (skipMojo()) {

             return;

         }

         successfulStatements = 0;

         totalStatements = 0;

         loadUserInfoFromSettings();

         addCommandToTransactions();

         addFilesToTransactions();

         addFileSetToTransactions();

         addResourcesToTransactions();

         sortTransactions();

         try {

             conn = getConnection();

         } catch (SQLException e) {

             if (!this.skipOnConnectionError) {

                 throw new MojoExecutionException(e.getMessage(), e);

             } else {

                 // error on get connection and user asked to skip the rest

                 return;

             }

         }

         try {

             statement = conn.createStatement();

             statement.setEscapeProcessing(escapeProcessing);

             PrintStream out = System.out;

             try {

                 if (outputFile != null) {

                     getLog().debug("Opening PrintStream to output file " + outputFile);

                     out = new PrintStream(new BufferedOutputStream(new FileOutputStream(outputFile.getAbsolutePath(),

                             append)));

                 }

                 // Process all transactions

                 for (Enumeration<Transaction> e = transactions.elements(); e.hasMoreElements();) {

                     Transaction t = e.nextElement();

                     t.runTransaction(out);

                     if (!autocommit) {

                         getLog().debug("Committing transaction");

                         conn.commit();

                     }

                 }

             } finally {

                 if (out != null && out != System.out) {

                     out.close();

                 }

             }

         } catch (IOException e) {

             throw new MojoExecutionException(e.getMessage(), e);

         } catch (SQLException e) {

             if (!autocommit && conn != null && ON_ERROR_ABORT.equalsIgnoreCase(getOnError())) {

                 try {

                     conn.rollback();

                 } catch (SQLException ex) {

                     // ignore

                 }

             }

             throw new MojoExecutionException(e.getMessage(), e);

         } finally {

             try {

                 if (statement != null) {

                     statement.close();

                 }

                 if (conn != null) {

                     conn.close();

                 }

             } catch (SQLException ex) {

                 // ignore

             }

         }

         getLog().info(

                 getSuccessfulStatements() + " of " + getTotalStatements() + " SQL statements executed successfully");

         if (ON_ERROR_ABORT_AFTER.equalsIgnoreCase(getOnError()) && totalStatements != successfulStatements) {

             throw new MojoExecutionException("Some SQL statements failed to execute");

         }

     }

     /**

      * Add sql command to transactions list.

      *

      */

     private void addCommandToTransactions() {

         createTransaction().addText(sqlCommand.trim());

     }

     /**

      * Add user sql fileset to transation list

      *

      */

     private void addFileSetToTransactions() {

         String[] includedFiles;

         if (fileset != null) {

             fileset.scan();

             includedFiles = fileset.getIncludedFiles();

         } else {

             includedFiles = new String[0];

         }

         for (int j = 0; j < includedFiles.length; j++) {

             createTransaction().setSrc(new File(fileset.getBasedir(), includedFiles[j]));

         }

     }

     protected Resource[] getResources(String[] locations) throws MojoExecutionException {

         if (locations == null || locations.length == 0) {

             return new Resource[] {};

         }

         ResourceLoader loader = new DefaultResourceLoader();

         List<Resource> resources = new ArrayList<Resource>();

         for (int i = 0; i < locations.length; i++) {

             String location = locations[i];

             // Skip it if the location is empty

             if (StringUtils.isEmpty(location)) {

                 continue;

             }

             Resource resource = loader.getResource(location);

             if (!resource.exists()) {

                 // The location was not empty, but we couldn't find it

                 throw new MojoExecutionException("Resource " + location + " was not found");

             }

             resources.add(resource);

         }

         return resources.toArray(new Resource[resources.size()]);

     }

     protected void copy(Resource resource, File file) throws IOException {

         InputStream in = resource.getInputStream();

         OutputStream out = new FileOutputStream(file);

         IOUtils.copyLarge(in, out);

     }

     /**

      * Add user input of srcFiles to transaction list.

      *

      * @throws MojoExecutionException

      */

     private void addResourcesToTransactions() throws MojoExecutionException {

         String[] locations = getResourceLocations();

         Resource[] resources = getResources(locations);

         MavenFileFilterRequest request = new MavenFileFilterRequest();

         request.setEncoding(encoding);

         request.setMavenSession(mavenSession);

         request.setMavenProject(project);

         request.setFiltering(enableFiltering);

         for (int i = 0; i < resources.length; i++) {

             Resource resource = resources[i];

             String filename = resource.getFilename();

             String basename = FileUtils.basename(filename);

             String extension = FileUtils.extension(filename);

             if (!extension.startsWith(".")) {

                 extension = "." + extension;

             }

             File sourceFile = FileUtils.createTempFile(basename, extension, null);

             if (!getLog().isDebugEnabled()) {

                 sourceFile.deleteOnExit();

             }

             try {

                 copy(resource, sourceFile);

             } catch (IOException e) {

                 throw new MojoExecutionException("Error copying resource " + resource + " to a local temporary file", e);

             }

             if (!enableFiltering) {

                 createTransaction().setSrc(sourceFile);

                 continue;

             }

             File targetFile = FileUtils.createTempFile(basename, extension, null);

             if (!getLog().isDebugEnabled()) {

                 sourceFile.deleteOnExit();

             }

             request.setFrom(sourceFile);

             request.setTo(targetFile);

             try {

                 fileFilter.copyFile(request);

             } catch (MavenFilteringException e) {

                 throw new MojoExecutionException(e.getMessage());

             }

             createTransaction().setSrc(targetFile);

         }

     }

     /**

      * Add user input of srcFiles to transaction list.

      *

      * @throws MojoExecutionException

      */

     private void addFilesToTransactions() throws MojoExecutionException {

         File[] files = getSrcFiles();

         MavenFileFilterRequest request = new MavenFileFilterRequest();

         request.setEncoding(encoding);

         request.setMavenSession(mavenSession);

         request.setMavenProject(project);

         request.setFiltering(enableFiltering);

         for (int i = 0; files != null && i < files.length; ++i) {

             if (files[i] != null && !files[i].exists()) {

                 throw new MojoExecutionException(files[i].getPath() + " not found.");

             }

             if (!enableFiltering) {

                 createTransaction().setSrc(files[i]);

                 continue;

             }

             File sourceFile = files[i];

             String basename = FileUtils.basename(sourceFile.getName());

             String extension = FileUtils.extension(sourceFile.getName());

             if (!extension.startsWith(".")) {

                 extension = "." + extension;

             }

             File targetFile = FileUtils.createTempFile(basename, extension, null);

             if (!getLog().isDebugEnabled()) {

                 targetFile.deleteOnExit();

             }

             request.setFrom(sourceFile);

             request.setTo(targetFile);

             try {

                 fileFilter.copyFile(request);

             } catch (MavenFilteringException e) {

                 throw new MojoExecutionException(e.getMessage());

             }

             createTransaction().setSrc(targetFile);

         }

     }

     /**

      * Sort the transaction list.

      */

     protected void sortTransactions() {

         switch (orderFile) {

         case ASCENDING:

             Collections.sort(transactions);

             break;

         case DESCENDING:

             Collections.sort(transactions, Collections.reverseOrder());

             break;

         case NONE:

             break;

         default:

             throw new RuntimeException("Unknown value for orderFile: " + orderFile);

         }

     }

     /**

      * Load username password from settings if user has not set them in JVM properties

      *

      * @throws MojoExecutionException

      */

     private void loadUserInfoFromSettings() throws MojoExecutionException {

         if (this.settingsKey == null) {

             this.settingsKey = getUrl();

         }

         if ((getUsername() == null || getPassword() == null) && (settings != null)) {

             Server server = this.settings.getServer(this.settingsKey);

             if (server != null) {

                 if (getUsername() == null) {

                     setUsername(server.getUsername());

                 }

                 if (getPassword() == null) {

                     setPassword(server.getPassword());

                 }

             }

         }

         if (getUsername() == null) {

             // allow emtpy username

             setUsername("");

         }

         if (getPassword() == null) {

             // allow emtpy password

             setPassword("");

         }

     }

     /**

      * Creates a new Connection as using the driver, url, userid and password specified.

      *

      * The calling method is responsible for closing the connection.

      *

      * @return Connection the newly created connection.

      * @throws MojoExecutionException

      *             if the UserId/Password/Url is not set or there is no suitable driver or the driver fails to load.

      * @throws SQLException

      *             if there is problem getting connection with valid url

      *

      */

     private Connection getConnection() throws MojoExecutionException, SQLException {

         getLog().debug("connecting to " + getUrl());

         Properties info = new Properties();

         info.put("user", getUsername());

         if (!enableAnonymousPassword) {

             info.put("password", getPassword());

         }

         info.putAll(this.getDriverProperties());

         Driver driverInstance = null;

         try {

             Class<?> dc = Class.forName(getDriver());

             driverInstance = (Driver) dc.newInstance();

         } catch (ClassNotFoundException e) {

             throw new MojoExecutionException("Driver class not found: " + getDriver(), e);

         } catch (Exception e) {

             throw new MojoExecutionException("Failure loading driver: " + getDriver(), e);

         }

         Connection conn = driverInstance.connect(getUrl(), info);

         if (conn == null) {

             // Driver doesn't understand the URL

             throw new SQLException("No suitable Driver for " + getUrl());

         }

         conn.setAutoCommit(autocommit);

         return conn;

     }

     /**

      * parse driverProperties into Properties set

      *

      * @return the driver properties

      * @throws MojoExecutionException

      */

     protected Properties getDriverProperties() throws MojoExecutionException {

         Properties properties = new Properties();

         if (!StringUtils.isEmpty(this.driverProperties)) {

             String[] tokens = StringUtils.split(this.driverProperties, ",");

             for (int i = 0; i < tokens.length; ++i) {

                 String[] keyValueTokens = StringUtils.split(tokens[i].trim(), "=");

                 if (keyValueTokens.length != 2) {

                     throw new MojoExecutionException("Invalid JDBC Driver properties: " + this.driverProperties);

                 }

                 properties.setProperty(keyValueTokens[0], keyValueTokens[1]);

             }

         }

         return properties;

     }

     /**

      * read in lines and execute them

      *

      * @param reader

      *            the reader

      * @param out

      *            the outputstream

      * @throws SQLException

      * @throws IOException

      */

     private void runStatements(Reader reader, PrintStream out) throws SQLException, IOException {

         String line;

         if (enableBlockMode) {

             // no need to parse the content, ship it directly to jdbc in one sql

             // statement

             line = IOUtil.toString(reader);

             execSQL(line, out);

             return;

         }

         StringBuffer sql = new StringBuffer();

         BufferedReader in = new BufferedReader(reader);

         while ((line = in.readLine()) != null) {

             getLog().debug("line='" + line + "'");

             if (!keepFormat) {

                 line = line.trim();

             }

             if (!keepFormat) {

                 if (line.startsWith("//")) {

                     continue;

                 }

                 if (line.startsWith("--")) {

                     continue;

                 }

                 StringTokenizer st = new StringTokenizer(line);

                 if (st.hasMoreTokens()) {

                     String token = st.nextToken();

                     if ("REM".equalsIgnoreCase(token)) {

                         continue;

                     }

                 }

             }

             if (!keepFormat) {

                 sql.append(" ").append(line);

             } else {

                 sql.append("\n").append(line);

             }

             // SQL defines "--" as a comment to EOL

             // but in Oracle it may contain a hint

             // so we cannot just remove it, instead we must end it

             if (!keepFormat) {

                 if (SqlSplitter.containsSqlEnd(line, delimiter) == SqlSplitter.NO_END) {

                     sql.append("\n");

                 }

             }

             if (isEnd(line)) {

                 execSQL(sql.substring(0, sql.length() - delimiter.length()), out);

                 sql.setLength(0); // clean buffer

             }

         }

         // Catch any statements not followed by ;

         if (executeTrailingSQL && !sql.toString().equals("")) {

             execSQL(sql.toString(), out);

         }

     }

     protected boolean isEnd(String line) {

         if (delimiterType.equals(DelimiterType.ROW)) {

             return line.trim().equals(delimiter);

         }

         int pos = SqlSplitter.containsSqlEnd(line, delimiter);

         if (delimiterType.equals(DelimiterType.NORMAL) && pos > 0) {

             return true;

         }

         return false;

     }

     /**

      * Exec the sql statement.

      *

      * @param sql

      *            query to execute

      * @param out

      *            the outputstream

      */

     private void execSQL(String sql, PrintStream out) throws SQLException {

         // Check and ignore empty statements

         if ("".equals(sql.trim())) {

             return;

         }

         ResultSet resultSet = null;

         try {

             totalStatements++;

             getLog().debug("SQL: " + sql);

             boolean ret;

             int updateCountTotal = 0;

             ret = statement.execute(sql);

             do {

                 if (!ret) {

                     int updateCount = statement.getUpdateCount();

                     if (updateCount != -1) {

                         updateCountTotal += updateCount;

                     }

                 } else {

                     resultSet = statement.getResultSet();

                     if (printResultSet) {

                         printResultSet(resultSet, out);

                     }

                 }

                 ret = statement.getMoreResults();

             } while (ret);

             getLog().debug(updateCountTotal + " rows affected");

             if (printResultSet) {

                 StringBuffer line = new StringBuffer();

                 line.append(updateCountTotal).append(" rows affected");

                 out.println(line);

             }

             SQLWarning warning = conn.getWarnings();

             while (warning != null) {

                 getLog().debug(warning + " sql warning");

                 warning = warning.getNextWarning();

             }

             conn.clearWarnings();

             successfulStatements++;

         } catch (SQLException e) {

             getLog().error("Failed to execute: " + sql);

             if (ON_ERROR_ABORT.equalsIgnoreCase(getOnError())) {

                 throw e;

             }

             getLog().error(e.toString());

         } finally {

             if (resultSet != null) {

                 resultSet.close();

             }

         }

     }

     /**

      * print any results in the result set.

      *

      * @param rs

      *            the resultset to print information about

      * @param out

      *            the place to print results

      * @throws SQLException

      *             on SQL problems.

      */

     private void printResultSet(ResultSet rs, PrintStream out) throws SQLException {

         if (rs != null) {

             getLog().debug("Processing new result set.");

             ResultSetMetaData md = rs.getMetaData();

             int columnCount = md.getColumnCount();

             StringBuffer line = new StringBuffer();

             if (showheaders) {

                 boolean first = true;

                 for (int col = 1; col <= columnCount; col++) {

                     String columnValue = md.getColumnName(col);

                     if (columnValue != null) {

                         columnValue = columnValue.trim();

                         if (",".equals(outputDelimiter)) {

                             columnValue = StringEscapeUtils.escapeCsv(columnValue);

                         }

                     }

                     if (first) {

                         first = false;

                     } else {

                         line.append(outputDelimiter);

                     }

                     line.append(columnValue);

                 }

                 out.println(line);

                 line = new StringBuffer();

             }

             while (rs.next()) {

                 boolean first = true;

                 for (int col = 1; col <= columnCount; col++) {

                     String columnValue = rs.getString(col);

                     if (columnValue != null) {

                         columnValue = columnValue.trim();

                         if (",".equals(outputDelimiter)) {

                             columnValue = StringEscapeUtils.escapeCsv(columnValue);

                         }

                     }

                     if (first) {

                         first = false;

                     } else {

                         line.append(outputDelimiter);

                     }

                     line.append(columnValue);

                 }

                 out.println(line);

                 line = new StringBuffer();

             }

         }

         out.println();

     }

     /**

      * Contains the definition of a new transaction element. Transactions allow several files or blocks of statements to

      * be executed using the same JDBC connection and commit operation in between.

      */

     private class Transaction implements Comparable<Transaction> {

         private File tSrcFile = null;

         private String tSqlCommand = "";

         /**

          *

          */

         public void setSrc(File src) {

             this.tSrcFile = src;

         }

         /**

          *

          */

         public void addText(String sql) {

             this.tSqlCommand += sql;

         }

         /**

          *

          */

         private void runTransaction(PrintStream out) throws IOException, SQLException {

             if (tSqlCommand.length() != 0) {

                 getLog().info("Executing commands");

                 runStatements(new StringReader(tSqlCommand), out);

             }

             if (tSrcFile != null) {

                 getLog().info("Executing file: " + tSrcFile.getAbsolutePath());

                 Reader reader = null;

                 if (StringUtils.isEmpty(encoding)) {

                     reader = new FileReader(tSrcFile);

                 } else {

                     reader = new InputStreamReader(new FileInputStream(tSrcFile), encoding);

                 }

                 try {

                     runStatements(reader, out);

                 } finally {

                     reader.close();

                 }

             }

         }

         @Override

         public int compareTo(Transaction transaction) {

             if (transaction.tSrcFile == null) {

                 if (this.tSrcFile == null) {

                     return 0;

                 } else {

                     return Integer.MAX_VALUE;

                 }

             } else {

                 if (this.tSrcFile == null) {

                     return Integer.MIN_VALUE;

                 } else {

                     return this.tSrcFile.compareTo(transaction.tSrcFile);

                 }

             }

         }

     }

     //

     // helper accessors for unit test purposes

     //

     public String getUsername() {

         return this.username;

     }

     public void setUsername(String username) {

         this.username = username;

     }

     public String getPassword() {

         return this.password;

     }

     public void setPassword(String password) {

         this.password = password;

     }

     public String getUrl() {

         return this.url;

     }

     public void setUrl(String url) {

         this.url = url;

     }

     public String getDriver() {

         return this.driver;

     }

     public void setDriver(String driver) {

         this.driver = driver;

     }