VirtualDatabaseConsole.java

/**
 * C-JDBC: Clustered JDBC.
 * Copyright (C) 2002-2004 French National Institute For Research In Computer
 * Science And Control (INRIA).
 * Contact: c-jdbc@objectweb.org
 * 
 * This library is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as published by the
 * Free Software Foundation; either version 2.1 of the License, or any later
 * version.
 * 
 * This library is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
 * for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public License
 * along with this library; if not, write to the Free Software Foundation,
 * Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
 *
 * Initial developer(s): Emmanuel Cecchet.
 * Contributor(s): Mathieu Peltier, Nicolas Modrzyk.
 */

package org.objectweb.cjdbc.console.text.module;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.objectweb.cjdbc.common.i18n.ConsoleTranslate;
import org.objectweb.cjdbc.common.util.Constants;
import org.objectweb.cjdbc.console.text.Console;
import org.objectweb.cjdbc.console.text.ConsoleException;

/**
 * C-JDBC Controller Virtual Database Console module.
 * 
 * @author <a href="mailto:Emmanuel.Cecchet@inria.fr">Emmanuel Cecchet </a>
 * @author <a href="mailto:Mathieu.Peltier@inrialpes.fr">Mathieu Peltier </a>
 * @author <a href="mailto:Nicolas.Modrzyk@inrialpes.fr">Nicolas Modrzyk </a>
 * @version 1.0
 */
public class VirtualDatabaseConsole extends AbstractConsoleModule
{

  /** Max column width when displaying a <code>ResultSet</code>. */
  private static final int MAX_COLUMN_DISPLAY_WIDTH = 25;

  private Connection       connection               = null;

  /** Default query timeout. */
  private int              timeout                  = 60;

  private int              fetchsize                = 0;

  private int              maxrows                  = 0;

  private String           login, url;

  /**
   * Creates a new <code>VirtualDatabaseAdmin</code> instance. Loads the
   * driver
   * 
   * @param console console console
   */
  public VirtualDatabaseConsole(Console console) 
  {
    super(console);
    try
    {
      Class.forName("org.objectweb.cjdbc.driver.Driver");
    }
    catch (Exception e)
    {
      console.printError(ConsoleTranslate
          .get("sql.login.cannot.load.driver", e), e);
      Runtime.getRuntime().exit(1);
    }
    console.println(ConsoleTranslate.get("sql.login.loaded.driver",
        Constants.VERSION));
  }

  /**
   * Gets a new connection from the driver.
   * 
   * @param url the C-JDBC url
   * @param login the login to use to open the connection
   * @param password the password to use to open the connection
   * @return a new connection
   * @throws ConsoleException if login failed
   */
  public Connection getConnection(String url, String login, String password)
      throws ConsoleException
  {
    try
    {
      return DriverManager.getConnection(url, login, password);
    }
    catch (Exception e)
    {
      throw new ConsoleException(ConsoleTranslate.get("sql.login.connection.failed",
        new String[]{url, e.getMessage()}));
    }
  }

  private void displaySeparatorLine(int columnCount, ResultSetMetaData meta)
      throws SQLException
  {

    console.print("+");
    for (int i = 1; i <= columnCount; i++)
    {
      int size = meta.getColumnDisplaySize(i);
      if (size > MAX_COLUMN_DISPLAY_WIDTH)
        size = MAX_COLUMN_DISPLAY_WIDTH;
      console.print("-");
      for (int j = 0; j < size; j++)
        console.print("-");
      console.print("-+");
    }
    console.println();
  }

  private void displayPad(String text, int size)
  {
    if (size > MAX_COLUMN_DISPLAY_WIDTH)
      size = MAX_COLUMN_DISPLAY_WIDTH;
    if (size < text.length())
    {
      console.print(text.substring(0, size - 1) + "~");
      return;
    }
    StringBuffer toPad = new StringBuffer(size);
    toPad.insert(0, text);
    while (toPad.length() < size)
      toPad.append(' ');
    console.print(toPad.toString());
  }

  /**
   * Display the given <code>ResultSet</code>.
   * 
   * @param rs the <code>ResultSet</code> to display
   * @throws SQLException if an error occurs
   */
  public void displayResultSet(ResultSet rs) throws SQLException
  {
    // Get the metadata
    ResultSetMetaData meta = rs.getMetaData();
    int columnCount = meta.getColumnCount();

    displaySeparatorLine(columnCount, meta);

    // Print the column names
    console.print("|");
    for (int i = 1; i <= columnCount; i++)
    {
      console.print(" ");
      // Pad the column name and print it
      int size = meta.getColumnDisplaySize(i);
      String columnName = meta.getColumnName(i);
      if (size <= 0)
      {
        if (columnName != null)
          size = columnName.length();
        else
          size = 0;
      }
      displayPad(columnName, size);
      console.print(" |");
    }
    console.println();

    displaySeparatorLine(columnCount, meta);

    // Display the results
    Object object;
    int line = 0;
    while (rs.next())
    {
      console.print("|");
      for (int i = 1; i <= columnCount; i++)
      {
        console.print(" ");
        object = rs.getObject(i);
        String value = (object != null) ? rs.getObject(i).toString() : "";
        // Pad the value and print it
        int size = meta.getColumnDisplaySize(i);
        if (size <= 0)
        {
          if (value != null)
            size = value.length();
          else
            size = 0;
        }
        displayPad(value, size);
        console.print(" |");
      }
      console.println("");
      line++;
      if (fetchsize != 0)
      {
        if (line % fetchsize == 0)
        {
          try
          {
            console.readLine(ConsoleTranslate.get("sql.display.next.rows",
                new Integer[]{new Integer(fetchsize), new Integer(line)}));
          }
          catch (ConsoleException ignore)
          {
          }
        }
      }
    }

    displaySeparatorLine(columnCount, meta);
  }

  /**
   * Call a store procedure.
   * 
   * @param proc the stored procedure to call
   * @param displayResult <code>true</code> if the result must be printed on
   *          the standard output
   */
  public synchronized void callStoredProcedure(String proc,
      boolean displayResult)
  {
    CallableStatement cs = null;
    try
    {
      cs = connection.prepareCall(proc);
      cs.setQueryTimeout(timeout);

      long start = System.currentTimeMillis();
      long end;
      ResultSet rs = cs.executeQuery();
      end = System.currentTimeMillis();
      if (displayResult)
        displayResultSet(rs);
      console.println(ConsoleTranslate.get("sql.display.query.time",
          new Long[]{new Long((end - start) / 1000),
              new Long((end - start) % 1000)}));
    }
    catch (Exception e)
    {
      console.printError(ConsoleTranslate.get(
          "sql.command.storeProcedure.error", e), e);
    }
    finally
    {
      try
      {
        cs.close();
      }
      catch (Exception ignore)
      {
      }
    }
  }

  /**
   * Executes a SQL statement.
   * 
   * @param request the SQL request to execute
   * @param displayResult <code>true</code> if the result must be printed on
   *          the standard output
   */
  public synchronized void execSQL(String request, boolean displayResult)
  {
    PreparedStatement stmt = null;
    try
    {
      stmt = connection.prepareStatement(request);
      stmt.setQueryTimeout(timeout);
      if (fetchsize != 0)
        stmt.setFetchSize(fetchsize);
      if (maxrows != 0)
        stmt.setMaxRows(maxrows);

      long start = System.currentTimeMillis();
      long end;
      if (request.regionMatches(true, 0, "select ", 0, 7))
      {
        ResultSet rs = stmt.executeQuery();
        end = System.currentTimeMillis();
        if (displayResult)
          displayResultSet(rs);
      }
      else
      {
        int result = stmt.executeUpdate();
        end = System.currentTimeMillis();
        if (displayResult)
          console.println(ConsoleTranslate.get("sql.display.affected.rows",
              result));
      }
      console.println(ConsoleTranslate.get("sql.display.query.time",
          new Long[]{new Long((end - start) / 1000), new Long((end - start) % 1000)}));
    }
    catch (Exception e)
    {
      console.printError(ConsoleTranslate.get("sql.command.sqlquery.error", e),
          e);
    }
    finally
    {
      try
      {
        stmt.close();
      }
      catch (Exception ignore)
      {
      }
    }
  }

  /** Display all tables of this virtual database */
  public void showtables()
  {
    try
    {
      DatabaseMetaData dbmd = connection.getMetaData();
      ResultSet tableSet = dbmd.getTables(null, null, null, null);
      while (!tableSet.isLast())
      {
        tableSet.next();
        console.println(tableSet.getString(tableSet.findColumn("TABLE_NAME")));
      }
    }
    catch (Exception e)
    {
      console.printError(ConsoleTranslate.get("sql.command.sqlquery.error", e),
          e);
    }

  }

  /**
   * Executes all the SQL requests contained in the specified file.
   * 
   * @param fileName the file name to open
   */
  public void load(String fileName)
  {
    BufferedReader file = null;
    try
    {
      file = new BufferedReader(new FileReader(fileName));
    }
    catch (Exception e)
    {
      console.printError(
          ConsoleTranslate.get("sql.command.load.file.error", e), e);
      return;
    }

    console.println(ConsoleTranslate.get("sql.command.loading.file", fileName));
    try
    {
      String request;

      while ((request = file.readLine()) != null)
      {
        request = request.trim();
        console.println(request);

        if (request.equalsIgnoreCase("begin"))
          connection.setAutoCommit(false);
        else if (request.equalsIgnoreCase("commit"))
          connection.commit();
        else if (request.equalsIgnoreCase("rollback"))
          connection.rollback();
        else
        { // Regular SQL request
          execSQL(request, false);
        }
      }
    }
    catch (Exception e)
    {
      console.printError(ConsoleTranslate.get("sql.command.load.execute.error",
          e), e);
    }
    finally
    {
      try
      {
        file.close();
      }
      catch (IOException ignore)
      {
      }
    }
  }

  /**
   * Displays help message.
   */
  public void help()
  {
    console.println(ConsoleTranslate.get("module.commands.available",
        getDescriptionString()));
    console.println(" begin");
    console.println("   " + ConsoleTranslate.get("sql.command.begin"));
    console.println(" commit");
    console.println("   " + ConsoleTranslate.get("sql.command.commit"));
    console.println(" fetchsize x");
    console.println("   " + ConsoleTranslate.get("sql.command.fetchsize"));
    console.println(" help");
    console.println("   " + ConsoleTranslate.get("console.command.help"));
    console.println(" load <file>");
    console.println("   " + ConsoleTranslate.get("sql.command.load"));
    console.println(" maxrows x");
    console.println("   " + ConsoleTranslate.get("sql.command.maxrows"));
    console.println(" quit");
    console.println("   " + ConsoleTranslate.get("sql.command.quit"));
    console.println(" rollback");
    console.println("   " + ConsoleTranslate.get("sql.command.rollback"));
    console.println(" showtables");
    console.println("   " + ConsoleTranslate.get("sql.command.showtables"));
    console.println(" timeout x");
    console.println("   " + ConsoleTranslate.get("sql.command.timeout"));
    console.println(" {call proc_name(?,?,...)}");
    console.println("   " + ConsoleTranslate.get("sql.command.procedure"));
    console.println(ConsoleTranslate.get("sql.command.other"));
  }

  /**
   * Connects to a virtual database.
   */
  public void handlePrompt()
  {
    boolean quit = false;
    while (!quit)
    {
      try
      {
        String cmd = console.readLine(this.getPromptString());
        if (cmd == null)
          cmd = "quit";

        if (cmd.length() == 0)
          continue;

        if (cmd.equalsIgnoreCase("begin"))
        {
          connection.setAutoCommit(false);
          console.println(ConsoleTranslate
              .get("sql.display.transaction.started"));
        }
        else if (cmd.equalsIgnoreCase("commit"))
        {
          connection.commit();
          connection.setAutoCommit(true);
        }
        else if (cmd.equalsIgnoreCase("rollback"))
        {
          connection.rollback();
          connection.setAutoCommit(true);
        }
        else if (cmd.equalsIgnoreCase("showtables"))
        {
          showtables();
        }
        else if (cmd.toLowerCase().startsWith("{call"))
        {
          callStoredProcedure(cmd, true);
        }
        else if (cmd.toLowerCase().startsWith("load"))
        {
          cmd = cmd.substring(4).trim();
          load(cmd);
        }
        else if (cmd.toLowerCase().startsWith("fetchsize"))
        {
          cmd = cmd.substring(9).trim();
          try
          {
            fetchsize = new Integer(cmd).intValue();
            console.println(ConsoleTranslate.get("sql.display.new.fetchsize",
                fetchsize));
          }
          catch (NumberFormatException e)
          {
            console.printError(ConsoleTranslate.get(
                "sql.display.new.fetchsize.failed", e), e);
          }
        }
        else if (cmd.toLowerCase().startsWith("maxrows"))
        {
          cmd = cmd.substring(7).trim();
          try
          {
            maxrows = new Integer(cmd).intValue();
            console.println(ConsoleTranslate.get("sql.display.new.maxrows",
                maxrows));
          }
          catch (NumberFormatException e)
          {
            console.printError(ConsoleTranslate.get(
                "sql.display.new.maxrows.failed", e), e);
          }
        }
        else if (cmd.toLowerCase().startsWith("timeout"))
        {
          cmd = cmd.substring(7).trim();
          try
          {
            timeout = new Integer(cmd).intValue();
            console.println(ConsoleTranslate.get("sql.display.new.timeout",
                timeout));
          }
          catch (NumberFormatException e)
          {
            console.printError(ConsoleTranslate.get(
                "sql.display.new.timeout.failed", e), e);
          }
        }
        else if (cmd.equalsIgnoreCase("help"))
          help();
        else if (cmd.equalsIgnoreCase("quit"))
        {
          quit = true;
          if (connection != null)
            connection.close();
        }
        else
          // Consider it is an SQL statement
          execSQL(cmd, true);
      }
      catch (Exception e)
      {
        console.printError(ConsoleTranslate.get("sql.login.exception", e), e);
        if (e instanceof RuntimeException)
        {
          System.exit(0);
        }
      }
    }
  }

  /**
   * @see org.objectweb.cjdbc.console.text.module.AbstractConsoleModule#getDescriptionString()
   */
  public String getDescriptionString()
  {
    return "SQL Console";
  }

  /**
   * @see org.objectweb.cjdbc.console.text.module.AbstractConsoleModule#getPromptString()
   */
  public String getPromptString()
  {
    int ind1 = url.lastIndexOf('?');
    int ind2 = url.lastIndexOf(';');
    if (ind1 != -1 || ind2 != -1)
    {
      String prompt;
      prompt = (ind1 != -1) ? url.substring(0, ind1) : url;
      prompt = (ind2 != -1) ? url.substring(0, ind2) : url;
      return prompt + " (" + login + ")";
    }
    else
      return url + " (" + login + ")";
  }

  /**
   * @see org.objectweb.cjdbc.console.text.module.AbstractConsoleModule#loadCommands()
   */
  protected void loadCommands()
  {

  }

  /**
   * @see org.objectweb.cjdbc.console.text.module.AbstractConsoleModule#login(java.lang.String[])
   */
  public void login(String[] params) throws Exception
  {
    login = null;
    url = (params.length > 0 && params[0] != null) ? params[0].trim() : null;
    try
    {
      if ((url == null) || url.trim().equals(""))
      {
        url = console.readLine(ConsoleTranslate.get("sql.login.prompt.url"));
        if (url == null)
          return;
      }
      login = console.readLine(ConsoleTranslate.get("sql.login.prompt.user"));
      if (login == null)
        return;
      String password = console.readPassword(ConsoleTranslate
          .get("sql.login.prompt.password"));
      if (password == null)
        return;

      connection = getConnection(url, login, password);
    }
    catch (Exception e)
    {
      throw new ConsoleException(ConsoleTranslate.get("sql.login.exception", e));
    }
  }

  /**
   * @see org.objectweb.cjdbc.console.text.module.AbstractConsoleModule#quit()
   */
  public void quit()
  {
    quit = true;
    if (connection != null)
    {
      try
      {
        connection.close();
      }
      catch (Exception e)
      {
        // ignore
      }
    }
  }
}

---