tinysqlcmd.java

TinySQL是一个轻量级的纯java数据库引擎

/*
 * Java program to execute SQL commands using the tinySQL JDBC driver.
 *
 * $Author: davis $
 * $Date: 2004/12/18 21:26:02 $
 * $Revision: 1.1 $
 *
 * 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 (at your option) 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
 *
 * Revision History:
 *
 * Written by Davis Swan in November, 2003.
 */

package com.sqlmagic.tinysql;

import java.io.*;
import java.sql.*;
import java.net.*;
import java.util.*;
public class tinySQLCmd
{
   static Vector tableList;
   static String dbVersion;
   static FileWriter spoolFileWriter = (FileWriter)null;
   static String newLine = System.getProperty("line.separator"); 
   public static void main(String[] args) throws IOException,SQLException
   {
      DatabaseMetaData dbMeta;
      ResultSetMetaData meta;
      ResultSet display_rs,typesRS;
      BufferedReader stdin,loadFileReader;
      BufferedReader startReader=(BufferedReader)null;
      String[] fields;
      Connection con;
      Statement stmt;
      FieldTokenizer ft;
      PreparedStatement pstmt=(PreparedStatement)null;
      int i,rsColCount,endAt,colWidth,colScale,colPrecision,typeCount,
      colType,parameterIndex,b1,b2,parameterInt,startAt,columnIndex,valueIndex;
      String fName,tableName=null,inputString,cmdString,colTypeName,dbType,
      parameterString,loadString,fieldString,readString;
      StringBuffer lineOut,prepareBuffer,valuesBuffer,inputBuffer;
      boolean echo=false;
      stdin = new BufferedReader(new InputStreamReader(System.in));
      try 
      {
/*
 *       Register the JDBC driver for dBase
 */
         Class.forName("com.sqlmagic.tinysql.dbfFileDriver");
      } catch (ClassNotFoundException e) {
         System.err.println(
              "JDBC Driver could not be registered!!\n");
         if ( tinySQLGlobals.DEBUG ) e.printStackTrace();
      }
      fName = ".";
      if ( args.length > 0 ) fName = args[0];
/* 
 *    Establish a connection to dBase
 */
      con = dbConnect(fName);
      if ( con == (Connection)null )
      {
         fName = ".";
         con = dbConnect(fName);
      }
      dbMeta = con.getMetaData();
      dbType = dbMeta.getDatabaseProductName();		
      dbVersion = dbMeta.getDatabaseProductVersion();		
      System.out.println("===================================================");
      System.out.println(dbType + " Command line interface version " 
      + dbVersion + " released March 15, 2007");
      System.out.println("Type HELP to get information on available commands.");
      cmdString = "NULL";
      stmt = con.createStatement();
      inputString = (String)null;
      if ( args.length > 1 ) inputString = args[1].trim();
      while ( !cmdString.toUpperCase().equals("EXIT") )
      {
         try
         {
            if ( startReader != (BufferedReader)null )
            {
/*
 *             Command START files can contain comments and can have
 *             commands broken over several lines.  However, they 
 *             cannot have partial commands on a line.
 */
               inputBuffer = new StringBuffer();
               inputString = (String)null;
               while ( ( readString = startReader.readLine() ) != null )
               {
                  if ( readString.startsWith("--") |
                       readString.startsWith("#") ) continue;
                  inputBuffer.append(readString + " ");
/*
 *                A field tokenizer must be used to avoid problems with
 *                semi-colons inside quoted strings.
 */
                  ft = new FieldTokenizer(inputBuffer.toString(),';',true);
                  if ( ft.countFields() > 1 )
                  {
                     inputString = inputBuffer.toString();
                     break;
                  }
               }
               if ( inputString == (String)null ) 
               {
                  startReader = (BufferedReader)null;
                  continue;
               }
            } else if ( args.length == 0 ) {
               System.out.print("tinySQL>");
               inputString = stdin.readLine().trim();
            }
            if ( inputString == (String)null ) break;
            if (inputString.toUpperCase().startsWith("EXIT") |
                inputString.toUpperCase().startsWith("QUIT") ) break;
            startAt = 0;
            while ( startAt < inputString.length() - 1 )
            {
               endAt = inputString.indexOf(";",startAt);
               if ( endAt == -1 )
                  endAt = inputString.length();
               cmdString = inputString.substring(startAt,endAt);
               if ( echo ) System.out.println(cmdString);
               startAt = endAt + 1;
               if ( cmdString.toUpperCase().startsWith("SELECT") ) 
               {
                  display_rs = stmt.executeQuery(cmdString);
                  if ( display_rs == (ResultSet)null )
                  {
                     System.out.println("Null ResultSet returned from query");
                     continue;
                  }
                  meta = display_rs.getMetaData();
/*
 *                The actual number of columns retrieved has to be checked
 */
                  rsColCount = meta.getColumnCount();
                  lineOut = new StringBuffer(100);
                  int[] columnWidths = new int[rsColCount];
                  int[] columnScales = new int[rsColCount];
                  int[] columnPrecisions = new int[rsColCount];
                  int[] columnTypes = new int[rsColCount];
                  String[] columnNames = new String[rsColCount];
                  for ( i = 0; i < rsColCount; i++ )
                  {
                     columnNames[i] = meta.getColumnName(i + 1);
                     columnWidths[i] = meta.getColumnDisplaySize(i + 1);
                     columnTypes[i] = meta.getColumnType(i + 1);
                     columnScales[i] = meta.getScale(i + 1);
                     columnPrecisions[i] = meta.getPrecision(i + 1);
                     if ( columnNames[i].length() > columnWidths[i] )
                        columnWidths[i] = columnNames[i].length(); 
                     lineOut.append(padString(columnNames[i],columnWidths[i]) + " ");
                  }
                  if ( tinySQLGlobals.DEBUG )
                     System.out.println(lineOut.toString());
                  displayResults(display_rs);
               } else if ( cmdString.toUpperCase().startsWith("CONNECT") ) {
                  con = dbConnect(cmdString.substring(8,cmdString.length()));
               } else if ( cmdString.toUpperCase().startsWith("HELP") ) {
                  helpMsg(cmdString);
               } else if ( cmdString.toUpperCase().startsWith("DESCRIBE") ) {
                  dbMeta = con.getMetaData();
                  tableName = cmdString.toUpperCase().substring(9);
                  display_rs = dbMeta.getColumns(null,null,tableName,null);
                  System.out.println("\nColumns for table " + tableName + "\n"
                  + "Name                            Type");               
                  while (display_rs.next())
                  {
                     lineOut = new StringBuffer(100);
                     lineOut.append(padString(display_rs.getString(4),32));
                     colTypeName = display_rs.getString(6);
                     colType = display_rs.getInt(5);
                     colWidth = display_rs.getInt(7);
                     colScale = display_rs.getInt(9);
                     colPrecision = display_rs.getInt(10);
                     if ( colTypeName.equals("CHAR") )
                     {
                        colTypeName = colTypeName + "(" 
                        + Integer.toString(colWidth) + ")";
                     } else if ( colTypeName.equals("FLOAT") ) {
                        colTypeName += "("+ Integer.toString(colPrecision)
                        + "," + Integer.toString(colScale) + ")";
                     }  
                     lineOut.append(padString(colTypeName,20) + padString(colType,12));
                     System.out.println(lineOut.toString());
                  }
               } else if ( cmdString.toUpperCase().equals("SHOW TABLES") ) {
                  for ( i = 0; i < tableList.size(); i++ )
                     System.out.println((String)tableList.elementAt(i));
               } else if ( cmdString.toUpperCase().equals("SHOW TYPES") ) {
                  typesRS = dbMeta.getTypeInfo();
                  typeCount = displayResults(typesRS);
               } else if ( cmdString.toUpperCase().startsWith("SET ") ) {
/*
 *                Support for SET DEBUG ON/OFF and SET ECHO ON/OFF
 */
                  ft = new FieldTokenizer(cmdString.toUpperCase(),' ',false);
                  fields = ft.getFields();
                  if ( fields[1].equals("ECHO") )
                  { 
                     if ( fields[2].equals("ON") ) echo = true;
                     else echo = false;
                  } else if ( fields[1].equals("DEBUG") ) {
                     if ( fields[2].equals("ON") ) tinySQLGlobals.DEBUG = true;
                     else tinySQLGlobals.DEBUG = false;
                  } else if ( fields[1].equals("PARSER_DEBUG") ) {
                     if ( fields[2].equals("ON") ) 
                        tinySQLGlobals.PARSER_DEBUG = true;
                     else tinySQLGlobals.PARSER_DEBUG = false;
                  } else if ( fields[1].equals("WHERE_DEBUG") ) {
                     if ( fields[2].equals("ON") ) 
                        tinySQLGlobals.WHERE_DEBUG = true;
                     else tinySQLGlobals.WHERE_DEBUG = false;
                  } else if ( fields[1].equals("EX_DEBUG") ) {
                     if ( fields[2].equals("ON") ) 
                        tinySQLGlobals.EX_DEBUG = true;
                     else tinySQLGlobals.EX_DEBUG = false;
                  }
               } else if ( cmdString.toUpperCase().startsWith("SPOOL ") ) {
/*
 *                Spool output to a file.
 */
                  ft = new FieldTokenizer(cmdString,' ',false);
                  fName = ft.getField(1);
                  if ( fName.equals("OFF") )
                  {
                     try
                     {
                        spoolFileWriter.close();
                     }  catch (Exception spoolEx ) {
                        System.out.println("Unable to close spool file " 
                        + spoolEx.getMessage() + newLine);
                     }
                  } else { 
                     try
                     {
                        spoolFileWriter = new FileWriter(fName);
                        if ( spoolFileWriter != (FileWriter)null) 
                           System.out.println("Output spooled to " + fName);
                     }  catch (Exception spoolEx ) {
                        System.out.println("Unable to spool to file " 
                        + spoolEx.getMessage() + newLine);
                     } 
                  }
               } else if ( cmdString.toUpperCase().startsWith("START ") ) {
                  ft = new FieldTokenizer(cmdString,' ',false);
                  fName = ft.getField(1);
                  if ( !fName.toUpperCase().endsWith(".SQL") ) fName += ".SQL";
                  try
                  {
                     startReader = new BufferedReader(new FileReader(fName));
                  } catch ( Exception ex ) {
                     startReader = (BufferedReader)null;
                     throw new tinySQLException("No such file: " + fName);
                  }
               } else if ( cmdString.toUpperCase().startsWith("LOAD") ) {
                  ft = new FieldTokenizer(cmdString,' ',false);
                  fName = ft.getField(1);
                  tableName = ft.getField(3);
                  display_rs = stmt.executeQuery("SELECT * FROM " + tableName);
                  meta = display_rs.getMetaData();
                  rsColCount = meta.getColumnCount();
/*
 *                Set up the PreparedStatement for the inserts
 */
                  prepareBuffer = new StringBuffer("INSERT INTO " + tableName);
                  valuesBuffer = new StringBuffer(" VALUES");
                  for ( i = 0; i < rsColCount; i++ )
                  {
                     if ( i == 0 )
                     {
                        prepareBuffer.append(" (");
                        valuesBuffer.append(" (");
                     } else {
                        prepareBuffer.append(",");
                        valuesBuffer.append(",");
                     }
                     prepareBuffer.append(meta.getColumnName(i + 1));
                     valuesBuffer.append("?");
                  }
                  prepareBuffer.append(")" + valuesBuffer.toString() + ")");
                  try
                  {
                     pstmt = con.prepareStatement(prepareBuffer.toString());
                     loadFileReader = new BufferedReader(new FileReader(fName));
                     while ( (loadString=loadFileReader.readLine()) != null ) 
                     {
                        if ( loadString.toUpperCase().equals("ENDOFDATA") )
                           break;
                        columnIndex = 0;
                        valueIndex = 0;
                        ft = new FieldTokenizer(loadString,'|',true);