Quadcap Embeddable Database

com/quadcap/sql/tools/Loader.java

Go to the documentation of this file.

package com.quadcap.sql.tools;

/* Copyright 1999 - 2003 Quadcap Software.  All rights reserved.
 *
 * This software is distributed under the Quadcap Free Software License.
 * This software may be used or modified for any purpose, personal or
 * commercial.  Open Source redistributions are permitted.  Commercial
 * redistribution of larger works derived from, or works which bundle
 * this software requires a "Commercial Redistribution License"; see
 * http://www.quadcap.com/purchase.
 *
 * Redistributions qualify as "Open Source" under  one of the following terms:
 *   
 *    Redistributions are made at no charge beyond the reasonable cost of
 *    materials and delivery.
 *
 *    Redistributions are accompanied by a copy of the Source Code or by an
 *    irrevocable offer to provide a copy of the Source Code for up to three
 *    years at the cost of materials and delivery.  Such redistributions
 *    must allow further use, modification, and redistribution of the Source
 *    Code under substantially the same terms as this license.
 *
 * Redistributions of source code must retain the copyright notices as they
 * appear in each source code file, these license terms, and the
 * disclaimer/limitation of liability set forth as paragraph 6 below.
 *
 * Redistributions in binary form must reproduce this Copyright Notice,
 * these license terms, and the disclaimer/limitation of liability set
 * forth as paragraph 6 below, in the documentation and/or other materials
 * provided with the distribution.
 *
 * The Software is provided on an "AS IS" basis.  No warranty is
 * provided that the Software is free of defects, or fit for a
 * particular purpose.  
 *
 * Limitation of Liability. Quadcap Software shall not be liable
 * for any damages suffered by the Licensee or any third party resulting
 * from use of the Software.
 */

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;

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 com.quadcap.util.ConfigNumber;
import com.quadcap.util.ConfigString;
import com.quadcap.util.Debug;
import com.quadcap.util.Util;

/**
 * A simple SQL loader utility which scans the input file for 
 * semicolon-delimited SQL statements which are executed using JDBC
 * against a database connection.  If a statement generates a ResultSet,
 * the ResultSet is displayed to a PrintWriter.
 *
 * @author Stan Bailes
 */
public class Loader {
    static final ConfigNumber   trace =
        ConfigNumber.find("trace.sql.tools.Loader", "0");

    Connection                  conn;
    StringBuffer                buffer = new StringBuffer();
    PrintWriter                 writer = null;
    int                         rsLimit = Integer.MAX_VALUE;
    static final int            COLMAX  = 12;

    /**
     * No-argument bean constructor.  We need a connection to do something
     * useful.
     */
    public Loader() {
    }

    /**
     * Construct a new Loader object bound to the specified database
     * connection
     *
     * @param conn the database connection
     */
    public Loader(Connection conn) {
        this.conn = conn;
    }

    /**
     * Set this loader's database connection
     *
     * @param conn the new database connection
     */
    public void setConnection(Connection conn) {
        this.conn = conn;
    }

    /**
     * Get this loader's database connection
     *
     * @return the current database connection
     */
    public Connection getConnection() {
        return this.conn;
    }
    
    /**
     * Set the loader's writer object.  Depending on the loader's 
     * trace level, the loader will write a trace of SQL statement execution
     * to this writer.
     *
     * @param writer the new writer object
     */
    public void setWriter(PrintWriter writer) {
        this.writer = writer;
    }

    /**
     * Return the loader's writer.
     *
     * @return the current writer.
     */
    public PrintWriter getWriter() {
        return writer;
    }

    final void print(String s) {
        if (trace.intValue() > 1) {
            buffer.append(s);
        }
        if (writer != null) writer.print(s);
    }

    final void println(String s) {
        if (trace.intValue() > 1) {
            buffer.append(s);
            Debug.println(buffer.toString());
            buffer.setLength(0);
        }
        if (writer != null) writer.println(s);
    }

    final void print(Throwable t) {
        if (trace.intValue() > 1) Debug.print(t);
        if (writer != null) {
            t.printStackTrace(writer);
        }
        if (t instanceof SQLException) {
            SQLException e = ((SQLException)t).getNextException();
            if (e != null) {
                print(e);
            }
        }
    }

    static final String pad(int wid, String s) {
        StringBuffer sb = new StringBuffer();
        if (s.length() > wid) {
            sb.append(s.substring(0, wid));
        } else {
            sb.append(s);
        }
        while (sb.length() < wid) sb.append(' ');
        return sb.toString();
    }

    final void showResultSet(ResultSet rs) throws SQLException {
        showResultSet(rs, Integer.MAX_VALUE);
    }

    final void showResultSet(ResultSet rs, int lim) throws SQLException {
        ResultSetMetaData rmeta = rs.getMetaData();
        String delim = "";
        for (int i = 1; i <= rmeta.getColumnCount(); i++) {
            int wid = Math.max(rmeta.getColumnDisplaySize(i),
                               rmeta.getColumnLabel(i).length());
            if (wid > COLMAX) wid = COLMAX;
            print(delim);
            delim = " ";
            print(pad(wid, rmeta.getColumnName(i)));
        }
        println("");
        while (lim-- > 0 && rs.next()) {
            delim = "";
            for (int i = 1; i <= rmeta.getColumnCount(); i++) {
                int wid = Math.max(rmeta.getColumnDisplaySize(i),
                                   rmeta.getColumnLabel(i).length());
                if (wid > COLMAX) wid = COLMAX;
                print(delim);
                delim = " ";
                Object obj = rs.getObject(i);
                if (obj == null) obj = "<null>";
                if (obj instanceof byte[]) {
                    obj = Util.hexBytes((byte[])obj);
                }
                print(pad(wid, obj.toString()));
            }
            println("");
        }
    }

    public static String getLine(InputStream is) throws IOException {
        StringBuffer sb = new StringBuffer();
        int ch;
        int state = 0;
        while ((ch = is.read()) > 0) {
            char c = (char)ch;
            switch (state) {
            case 0:
                if (c == '-') {
                    state = 1;
                } else if (c == ';') {
                    if (sb.length() > 0) {
                        return sb.toString();   // Found ';', we're outta here
                    }
                } else if (Character.isWhitespace(c) && sb.length() == 0) {
                } else {
                    sb.append(c);
                }
                break;
            case 1:
                if (c == '-') {
                    state = 2;
                } else {
                    sb.append('-');
                    sb.append(c);
                    state = 0;
                }
                break;
            case 2:
                if (c == '\r') state = 3;
                if (c == '\n') state = 0;
                break;
            case 3:
                if (c == '\n') state = 0;
                break;
            }
        }
        if (sb.length() > 0) {
            return sb.toString();               // 'add' missing trailing ';'
        }
        return null;
    }

    /**
     * Execute a single SQL statement.  There are several statements that
     * are not passed directly to the JDBC driver, but instead are executed
     * by the loader:
     * 
     * <p><table border=1>
     * <tr><th align=left>BEGINTRANSACTION</th>
     *     <td>Upon seeing this, the loader performs a
     *         <code>Connection.setAutoCommit(false)</code> call on the
     *         current database connection.</td></tr>
     * <tr><th align=left>ENDTRANSACTION</th>
     *     <td>The loader performs:
     *     <pre><code>
     *          Connection.commit()
     *          Connection.setAutocommit(true);
     *     </code></pre></td></tr>
     * <tr><th align=left>LIMITK</th>
     *     <td>The loader performs:
     *     <pre><code>
     *          Connection.rollback()
     *          Connection.setAutocommit(true);
     *     </code></pre></td></tr>
     * </table></p>
     *
     * @param sql the SQL statement to execute.
     */
    public void execute(String sql) {
        try {
            Statement stmt = conn.createStatement();
            try {
                if (sql.equals("BEGINTRANSACTION")) {
                    conn.setAutoCommit(false);
                } else if (sql.equals("ENDTRANSACTION")) {
                    conn.commit();
                    conn.setAutoCommit(true);
                } else if (sql.startsWith("LIMIT")) {
                    rsLimit = Integer.parseInt(sql.substring(5).trim());
                } else {
                    if (stmt.execute(sql)) {
                        ResultSet rs = stmt.getResultSet();
                        try {
                            showResultSet(rs, rsLimit);
                        } finally {
                            rs.close();
                        }
                    }
                }
            } catch (Throwable t) {
                print(t);
            } finally {
                stmt.close();
            }
        } catch (Throwable t) {
            print(t);
        }
    }

    public void loadStream(InputStream in)
        throws IOException, SQLException
    {
        Statement stmt = conn.createStatement();
        try {
            String sql = null;
            while ((sql = getLine(in)) != null) {
                if (trace.intValue() > 0) {
                    Debug.println(0, "Executing: " + sql);
                }
                try {
                    if (sql.equals("BEGINTRANSACTION")) {
                        conn.setAutoCommit(false);
                    } else if (sql.equals("ENDTRANSACTION") ||
                               sql.equals("COMMIT")) {
                        conn.commit();
                        conn.setAutoCommit(true);
                    } else if (sql.equals("ROLLBACK")) {
                        conn.rollback();
                        conn.setAutoCommit(true);
                    } else if (sql.startsWith("LIMIT")) {
                        rsLimit = Integer.parseInt(sql.substring(5).trim());
                    } else {
                        if (stmt.execute(sql)) {
                            ResultSet rs = stmt.getResultSet();
                            showResultSet(rs, rsLimit);
                        }
                    }
                } catch (Throwable t) {
                    print("Exception: " + t.toString());
                    print("Statement: " + sql);
                    print(t);
                }
            }
        } finally {
            stmt.close();
        }
    }

    /**
     * The specified file is scanned for semicolon-delimited SQL statements
     * which are executed one at a time.
     *
     * @param filename the name of the input file
     * @see execute
     */
    public void loadFile(String filename) {
        try {
            FileInputStream fis = new FileInputStream(filename);
            BufferedInputStream bis = new BufferedInputStream(fis);
            try {
                loadStream(bis);
            } finally {
                fis.close();
            }
        } catch (Throwable t) {
            print(t);
        }
    }
}