import java.sql.*;
import java.util.*;
import java.io.*;

/* modsql.java
 *
 * Copyright (c) 2004 Chris Studholme <chris.studholme@utoronto.ca>
 *
 * May be copied or modified under the terms of the GNU General Public
 * License.  See COPYING for more information.
 */

/**
 * <p>JDBC client program for accessing ModSQL database.  This client, with
 * minor changes, should work with any other JDBC database.
 *
 * @author chris.studholme@utoronto.ca
 */
class modsql {

  public static final String progname = "modsql";
  public static final String version = "0.40 20040104";
  public static final String databaseURL = "jdbc:modsql";
  public static final String databaseClass = "ModSQL.Driver";
  public static String nullString = "<NULL>";
  public static String falseString = "false";
  public static String trueString = "true";

  // arguments
  public static int debuglevel=0;
  public static int headerlevel=0;
  public static boolean donothing=false;
  public static boolean parseonly=false;
  public static boolean squeeze=false;
  public static boolean delimited=false;
  public static char delimiter=' ';
  public static boolean verbose=false;
  public static boolean writeparse=false;
  public static String outputfile=null;
  public static String command=null;
  public static String programfile=null;
  public static String programargs[]=null;


  public static void usage(PrintStream out) {
    out.print("\nNAME\n    "+progname+" - Generic Java SQL interpreter\n\n");
    out.print("SYNOPSIS\n");
    out.print("    "+progname+" [ OPTIONS ] [ -o outfile ] [ -c string | programfile [args] ]\n\n");
    out.println("DESCRIPTION");
    out.println("    -c cmd     execute command given in string");
    out.println("    -d         increase debugging info to stderr");
    out.println("    -e cmd     same as -c");
    out.println("    -F char    delimited output (default is fixed width)");
    out.println("    -h         displays this help screen (to stdout)");
    out.println("    -hh        help with SQL command summary (to stdout)");
    out.println("    -H         include header line before data");
    out.println("    -HH        include header and line of -'s before data");
    out.println("    -n         parse commands without executing");
    out.println("    -o file    write data to output file");
    out.println("    -S         squeeze columns to minimum (fixed) width");
    out.println("    -v         verbose (output status on stderr)");
    out.println("    -x         write commands to stdout before executing");
    out.println("    --true     specify string for output of true value");
    out.println("    --false    specify string for output of false value");
    out.println("    --null     specify string for output of null value");
    out.println("    --version  write version number on stderr");
    out.println();
  }

  public static void outputSQLSummary(PrintStream out) {
    out.println("Create/Drop:");
    out.println("    CREATE TABLE tablename (columnname type, ...);");
    out.println("    DROP TABLE tablename;");
    out.println("    CREATE INDEX [indexname] ON tablename [USING {SORT|HASH}] (columnname);");
    out.println("    DROP INDEX tablename.columnname;");
    out.println();
    out.println("Insert/Update/Delete:");
    out.println("    INSERT INTO tablename [(column, ...)] VALUES (value, ...);");
    out.println("    INSERT INTO tablename [(column, ...)] SELECT ...;");
    out.println("    UPDATE tablename SET column=value, ... [WHERE condition];");
    out.println("    DELETE FROM tablename [WHERE condition];");
    out.println();
    out.println("Copy to/from:");
    out.println("    COPY table FROM {'filename'|stdin} [[USING] DELIMITERS 'delimiter']");
    out.println("        [WITH NULL AS 'null string']");
    out.println("        [WITH TRUE AS 'true string'] [WITH FALSE AS 'false string'];");
    out.println("    COPY table TO {'filename'|stdout} [[USING] DELIMITERS 'delimiter']");
    out.println("        [WITH NULL AS 'null string']");
    out.println("        [WITH TRUE AS 'true string'] [WITH FALSE AS 'false string'];");
    out.println();
    out.println("Query:");
    out.println("    SELECT [DISTINCT] expression, ... FROM tablename, ...");
    out.println("        [WHERE condition]");
    out.println("        [GROUP BY column, ... [HAVING condition]]");
    out.println("        [ORDER BY expression, ...];");
    out.println();
    out.println("Joins (in FROM section of query):");
    out.println("    table1 JOIN table2 USING (columnname, ...)");
    out.println("    table1 JOIN table2 ON condition");
    out.println();
  }
    

  public static void version(PrintStream out) {
    out.println(progname+" version "+version);
    try {
      Class.forName(databaseClass);
      java.sql.Driver driver = DriverManager.getDriver(databaseURL);
      out.println("  "+databaseClass+" version "+driver.getMajorVersion()+"."+
		  driver.getMinorVersion());

      java.sql.Connection connection = 
	DriverManager.getConnection(databaseURL);

      ModSQL.DatabaseManager[] managers = ModSQL.MetaManager.getManagers();
      if (managers!=null)
	for (int i=0; i<managers.length; ++i)
	  out.println("  "+managers[i].getClass().getName()+" version "+
		      managers[i].getMajorVersion()+"."+
		      managers[i].getMinorVersion());
    }
    catch (Exception e) {
      out.println(" ("+e.toString()+")");
    }
  }

  
  /*
   * returns true if arg parsing was successful and program can continue
   */
  public static boolean parseArgs(String[] args) {
    int help_level=0;
    
    // parse args
    for (int i=0; i<args.length; ++i) {
      if (args[i].startsWith("--")) {
	if (args[i].equals("--version")) {
	  version(System.err);
	  donothing=true;
	  return true;
	}
	else if (args[i].equals("--null")) {
	  if (i<args.length-1)
	    nullString=args[++i];
	  else
	    return false;
	}
	else if (args[i].equals("--true")) {
	  if (i<args.length-1)
	    trueString=args[++i];
	  else
	    return false;
	}
	else if (args[i].equals("--false")) {
	  if (i<args.length-1)
	    falseString=args[++i];
	  else
	    return false;
	}
	else
	  return false;
      }
      
      else if (args[i].startsWith("-")) {
	String optionlist=args[i];
	for (int j=1; j<optionlist.length(); ++j) 
	  switch (optionlist.charAt(j)) {
	  case 'c':
	  case 'e':
	    if (i<args.length-1)
	      command=args[++i];
	    else
	      return false;
	    break;

	  case 'd':
	    ++debuglevel;
	    break;

	  case 'h':
	    switch (++help_level) {
	    case 1:
	      usage(System.out);
	      break;
	    case 2:
	      outputSQLSummary(System.out);
	      break;
	    default:
	      return true;
	    }
	    donothing=true;
	    break;

	  case 'H':
	    ++headerlevel;
	    break;

	  case 'n':
	    parseonly=true;
	    break;

	  case 'o':
	    if (i>=args.length-1)
	      return false;
	    outputfile=args[++i];
	    break;

	  case 'F':
	    if (i>=args.length-1)
	      return false;
	    try {
	      delimiter=parseDelimiter(args[++i]);
	      delimited=true;
	    }
	    catch (SQLException e) {
	      throw new IllegalArgumentException("invalid delimiter");
	    }
	    break;

	  case 'S':
	    squeeze=true;
	    break;

	  case 'v':
	    verbose=true;
	    break;

	  case 'x':
	    writeparse=true;
	    break;

	  default:
	    return false;
	  }
      }	
      
      else {
	programfile=args[i++];
	if (i<args.length) {
	  programargs=new String[args.length-i];
	  System.arraycopy(args,i,programargs,0,args.length-i);
	}
	return true;
      }
    }
    return true;
  }


  public static StringBuffer LeftJustifyBuffer(StringBuffer src, int len) {
    while (src.length()<len)
      src.append(' ');
    return src;
  }


  public static StringBuffer RightJustifyBuffer(StringBuffer src, int len) {
    while (src.length()<len)
      src.insert(0,' ');
    return src;
  }


  public static StringBuffer JustifyBuffer(StringBuffer src, 
					   int len, int type) {
    switch (type) {
    case Types.BIT:     
    case Types.TINYINT:   
    case Types.SMALLINT:  
    case Types.INTEGER:   
    case Types.BIGINT:    
    case Types.FLOAT:     
    case Types.REAL:      
    case Types.DOUBLE:    
    case Types.NUMERIC:   
    case Types.DECIMAL:  
      return RightJustifyBuffer(src,len);
      
    case Types.NULL:         
    case Types.CHAR:      
    case Types.VARCHAR:   
    case Types.LONGVARCHAR: 
    case Types.DATE:        
    case Types.TIME:       
    case Types.TIMESTAMP:   
    case Types.BINARY:      
    case Types.VARBINARY:    
    case Types.LONGVARBINARY:
    default:
      return LeftJustifyBuffer(src,len);
    }
  }

  public static String commandName(String query) {
    query = query.trim().toUpperCase();
    int i = query.indexOf(' ');
    if (i<0) return query;
    String result = query.substring(0,i);
    if (result.equals("CREATE") || result.equals("DROP")) {
      query = query.substring(i+1).trim();
      i = query.indexOf(' ');
      if (i>0)
	result+=" "+query.substring(0,i);
    }
    return result;
  }

  public static char parseDelimiter(String delimiter) throws SQLException {
    if (delimiter.length()<=0)
      throw new SQLException("invalid delimiter");
    if (delimiter.length()==1) {
      if (delimiter.charAt(0)=='\\')
	throw new SQLException("invalid delimiter");
      return delimiter.charAt(0);
    }
    if (delimiter.charAt(0)!='\\')
      throw new SQLException("invalid delimiter");
    if (delimiter.length()==2) {
      switch (delimiter.charAt(1)) {
      case 't': return '\t';
      case 'b': return '\b';
      case 'f': return '\f';
      case 'r': return '\r';
      case 'v': return 11;
      }
    }
    try {
      char c = (char)Integer.parseInt(delimiter.substring(1),8);
      if (c!='\n' && c!='\\')
	return c;
    }
    catch (NumberFormatException e) {
    }
    throw new SQLException("invalid delimiter");
  }

  public static String quoteString(String src) {
    StringBuffer dest = new StringBuffer();
    dest.append('\'');
    for (int i=0; i<src.length(); ++i) {
      char c = src.charAt(i);
      if (c=='\\')
	dest.append("\\\\");
      else if (c=='\'')
	dest.append("\\'");
      else
	dest.append(c);
    }
    dest.append('\'');
    return dest.toString();
  }

  public static String applyEscapes(String src, char delimiter) {
    StringBuffer dest = new StringBuffer();
    for (int i=0; i<src.length(); ++i) {
      char c = src.charAt(i);
      if (c==delimiter)
	dest.append("\\"+delimiter);
      else if (c=='\\')
	dest.append("\\\\");
      else if (c<' ')
	switch (c) {
	case '\b': dest.append("\\b");  break;
	case '\f': dest.append("\\f");  break;
	case '\n': dest.append("\\n");  break;
	case '\r': dest.append("\\r");  break;
	case '\t': dest.append("\\t");  break;
	  //case 11:   dest.append("\\v");  break;
	default:
	  dest.append('\\');
	  dest.append(Integer.toString((int)c,8));
	}
      else
	dest.append(c);
    }
    return dest.toString();
  }

  public static String removeEscapes(String src) throws SQLException {
    StringBuffer dest = new StringBuffer();
    for (int i=0; i<src.length(); ++i) {
      char c = src.charAt(i);
      if (c!='\\')
	dest.append(c);
      else {
	if (++i>=src.length())
	  throw new SQLException("unfinished escape sequence");
	c=src.charAt(i);
	if (c>='0' && c<='9') {
	  StringBuffer v = new StringBuffer();
	  v.append(c);
	  if (i+1<src.length() && 
	      src.charAt(i+1)>='0' && src.charAt(i+1)<='9') {
	    ++i;
	    v.append(src.charAt(i+1));
	    if (i+1<src.length() && 
		src.charAt(i+1)>='0' && src.charAt(i+1)<='9') {
	      ++i;
	      v.append(src.charAt(i+1));
	    }
	  }
	  try {
	    v.append((char)Integer.parseInt(v.toString(),8));
	  }
	  catch (NumberFormatException e) {
	    throw new SQLException("invalid escape sequence");
	  }
	}
	else switch (c) {
	case 'b': dest.append('\b');  break;
	case 'f': dest.append('\f');  break;
	case 'n': dest.append('\n');  break;
	case 'r': dest.append('\r');  break;
	case 't': dest.append('\t');  break;
	case 'v': dest.append((char)11);  break;
	default:
	  dest.append(c);
	}
      }
    }    
    return dest.toString();
  }

  // split line into fields delimited by delimiter
  public static String[] parseFields(String line, char delimiter) 
    throws SQLException {
    ArrayList fields = new ArrayList();
    while (line.length()>0) {
      StringBuffer field = new StringBuffer();
      int i=0; 
      char c;
      while (i<line.length() && (c=line.charAt(i))!=delimiter) {
	field.append(c);
	if (c=='\\') {
	  if (++i>=line.length())
	    throw new SQLException("unfinished escape sequence");
	  field.append(line.charAt(i));
	}
	++i;
      }
      String f = field.toString();
      line = line.substring(i);
      if (delimiter==' ') {
	while (line.length()>0 && line.charAt(0)==' ')
	  line = line.substring(1);
      }
      if (f.equals("\\E"))
	f="";
      fields.add(f);
    } 
    String[] result = new String[fields.size()];
    return (String[])fields.toArray(result);
  }


  /**
   * COPY table FROM { 'filename' | stdin }
   *     [ [USING] DELIMITERS 'delimiter' ]
   *     [ WITH NULL AS 'null string' ]
   *     [ WITH TRUE AS 'true string' ]
   *     [ WITH FALSE AS 'false string' ]
   */
  public static void executeCopyFrom(String tablename, 
				     StreamTokenizer tokenizer,
				     Connection connection) 
    throws SQLException,IOException {
    tokenizer.nextToken();    

    BufferedReader in;
    String source;
    if (tokenizer.ttype==StreamTokenizer.TT_WORD) {
      if (!tokenizer.sval.equals("stdin"))
	throw new SQLException("STDIN expected at or before "+tokenizer);
      in = new BufferedReader(new InputStreamReader(System.in));
      source = "stdin";
    }
    else if (tokenizer.ttype=='\'') {
      in = new BufferedReader(new FileReader(tokenizer.sval));
      source = "'"+tokenizer.sval+"'";
    }
    else
      throw new SQLException("filename expected at or before "+tokenizer);
    
    String delimiter_str=null;
    String null_string=null;
    String true_string=null;
    String false_string=null;
    while (tokenizer.nextToken()==StreamTokenizer.TT_WORD) {
      if (tokenizer.sval.equals("using")) {
	if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD ||
	    !tokenizer.sval.equals("delimiters"))
	  throw new SQLException("'DELIMITERS' expected at or before "+
				 tokenizer);
	if (tokenizer.nextToken()!='\'')
	  throw new SQLException("column delimiter expected at or before "+
				 tokenizer);
	delimiter_str=tokenizer.sval;
      }
      else if (tokenizer.sval.equals("delimiters")) {
	if (tokenizer.nextToken()!='\'')
	  throw new SQLException("column delimiter expected at or before "+
				 tokenizer);
	delimiter_str=tokenizer.sval;
      }
      else if (tokenizer.sval.equals("with")) {
	if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD)
	  throw new SQLException("'NULL', 'TRUE' or 'FALSE' expected at or before "+tokenizer);
	if (tokenizer.sval.equals("null")) {
	  if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD ||
	      !tokenizer.sval.equals("as"))
	    throw new SQLException("'AS' expected at or before "+tokenizer);
	  if (tokenizer.nextToken()!='\'')
	    throw new SQLException("quoted string expected at or before "+
				   tokenizer);
	  null_string=tokenizer.sval;
	}
	else if (tokenizer.sval.equals("true")) {
	  if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD ||
	      !tokenizer.sval.equals("as"))
	    throw new SQLException("'AS' expected at or before "+tokenizer);
	  if (tokenizer.nextToken()!='\'')
	    throw new SQLException("quoted string expected at or before "+
				   tokenizer);
	  true_string=tokenizer.sval;
	}
	else if (tokenizer.sval.equals("false")) {
	  if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD ||
	      !tokenizer.sval.equals("as"))
	    throw new SQLException("'AS' expected at or before "+tokenizer);
	  if (tokenizer.nextToken()!='\'')
	    throw new SQLException("quoted string expected at or before "+
				   tokenizer);
	  false_string=tokenizer.sval;
	}
	else
	  throw new SQLException("'NULL', 'TRUE' or 'FALSE' expected at or before "+tokenizer);
      }
      else 
	throw new SQLException("unknown keyword "+tokenizer);
    }

    if (tokenizer.ttype!=StreamTokenizer.TT_EOF && tokenizer.ttype!=';')
      throw new SQLException("';' expected at or before "+tokenizer);

    char delimiter = delimiter_str==null ? ' ' : parseDelimiter(delimiter_str);

    if (writeparse) {
      System.out.print("COPY "+tablename+" FROM "+source);
      if (delimiter_str!=null)
	System.out.print(" USING DELIMITERS '"+delimiter+"'");
      if (null_string!=null)
	System.out.print(" WITH NULL AS '"+null_string+"'");
      if (true_string!=null)
	System.out.print(" WITH TRUE AS '"+true_string+"'");
      if (false_string!=null)
	System.out.print(" WITH FALSE AS '"+false_string+"'");
      System.out.println(";");
    }
    if (parseonly)
      return;

    if (null_string==null)
      null_string="\\N";
    if (true_string==null)
      true_string="1";
    if (false_string==null)
      false_string="0";

    String query = "SELECT * FROM "+tablename+" WHERE false";
    Statement statement = connection.createStatement();
    ResultSet set = statement.executeQuery(query);
    ResultSetMetaData metadata = set.getMetaData();
    int ncolumns = metadata.getColumnCount();
    long nrows = 0;

    int columntype[]  = new int[ncolumns];
    for (int i=0; i<ncolumns; ++i)
      columntype[i]=metadata.getColumnType(i+1);

    String line;
    while ((line=in.readLine())!=null) {
      if (delimiter==' ') {
	// strip leading spaces
	int start=0;
	while (line.length()>start && line.charAt(start)==' ') ++start;
	line = line.substring(start);
	// skip blank lines
	if (line.length()<=0)
	  continue;
	// skip comment lines (lines starting with #)
	if (line.charAt(0)=='#')
	  continue;
      }
      String[] fields = parseFields(line,delimiter);

      if (fields.length>columntype.length)
	throw new SQLException("too many columns to copy");

      StringBuffer insert = new StringBuffer();
      insert.append("INSERT INTO ");
      insert.append(tablename);
      insert.append(" VALUES (");
      for (int i=0; i<fields.length; ++i) {
	if (i>0) insert.append(',');
	String f;
	if (fields[i].equals(null_string))
	  f = "null";
	else if (fields[i].equals(true_string))
	  f = "true";
	else if (fields[i].equals(false_string))
	  f = "false";
	else {
	  f = removeEscapes(fields[i]);
	  switch (columntype[i]) {
	  case Types.CHAR:      
	  case Types.VARCHAR:   
	  case Types.LONGVARCHAR: 
	  case Types.DATE:        
	  case Types.TIME:       
	  case Types.TIMESTAMP:   
	  case Types.BINARY:      
	  case Types.VARBINARY:    
	  case Types.LONGVARBINARY:
	    f = quoteString(f);
	  }
	}
	insert.append(f);
      }
      for (int i=fields.length; i<columntype.length; ++i)
	insert.append(",null");
      insert.append(");");
      if (debuglevel>=2)
	System.err.println("modsql: "+insert.toString());
      statement.execute(insert.toString());
      ++nrows;
    }

    //if (!source.equals("stdin"))
    in.close();
    if (verbose)
      System.err.println("COPY FROM "+nrows);
  }

  /**
   * COPY table TO { 'filename' | stdout }
   *     [ [USING] DELIMITERS 'delimiter' ]
   *     [ WITH NULL AS 'null string' ]
   *     [ WITH TRUE AS 'true string' ]
   *     [ WITH FALSE AS 'false string' ]
   */
  public static void executeCopyTo(String tablename, 
				   StreamTokenizer tokenizer,
				   Connection connection) 
    throws SQLException,IOException {
    tokenizer.nextToken();    

    PrintStream out;
    String dest;
    if (tokenizer.ttype==StreamTokenizer.TT_WORD) {
      if (!tokenizer.sval.equals("stdout"))
	throw new SQLException("'STDOUT' expected at or before "+tokenizer);
      out = System.out;
      dest = "stdout";
    }
    else if (tokenizer.ttype=='\'') {
      out = new PrintStream(new FileOutputStream(tokenizer.sval));
      dest = "'"+tokenizer.sval+"'";
    }
    else
      throw new SQLException("filename expected at or before "+tokenizer);
    
    String delimiter_str=null;
    String null_string=null;
    String true_string=null;
    String false_string=null;
    while (tokenizer.nextToken()==StreamTokenizer.TT_WORD) {
      if (tokenizer.sval.equals("using")) {
	if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD ||
	    !tokenizer.sval.equals("delimiters"))
	  throw new SQLException("'DELIMITERS' expected at or before "+
				 tokenizer);
	if (tokenizer.nextToken()!='\'')
	  throw new SQLException("column delimiter expected at or before "+
				 tokenizer);
	delimiter_str=tokenizer.sval;
      }
      else if (tokenizer.sval.equals("delimiters")) {
	if (tokenizer.nextToken()!='\'')
	  throw new SQLException("column delimiter expected at or before "+
				 tokenizer);
	delimiter_str=tokenizer.sval;
      }
      else if (tokenizer.sval.equals("with")) {
	if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD)
	  throw new SQLException("'NULL', 'TRUE' or 'FALSE' expected at or before "+tokenizer);
	if (tokenizer.sval.equals("null")) {
	  if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD ||
	      !tokenizer.sval.equals("as"))
	    throw new SQLException("'AS' expected at or before "+tokenizer);
	  if (tokenizer.nextToken()!='\'')
	    throw new SQLException("quoted string expected at or before "+
				   tokenizer);
	  null_string=tokenizer.sval;
	}
	else if (tokenizer.sval.equals("true")) {
	  if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD ||
	      !tokenizer.sval.equals("as"))
	    throw new SQLException("'AS' expected at or before "+tokenizer);
	  if (tokenizer.nextToken()!='\'')
	    throw new SQLException("quoted string expected at or before "+
				   tokenizer);
	  true_string=tokenizer.sval;
	}
	else if (tokenizer.sval.equals("false")) {
	  if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD ||
	      !tokenizer.sval.equals("as"))
	    throw new SQLException("'AS' expected at or before "+tokenizer);
	  if (tokenizer.nextToken()!='\'')
	    throw new SQLException("quoted string expected at or before "+
				   tokenizer);
	  false_string=tokenizer.sval;
	}
	else
	  throw new SQLException("'NULL', 'TRUE' or 'FALSE' expected at or before "+tokenizer);
      }
      else 
	throw new SQLException("unknown keyword "+tokenizer);
    }

    if (tokenizer.ttype!=StreamTokenizer.TT_EOF && tokenizer.ttype!=';')
      throw new SQLException("';' expected at or before "+tokenizer);

    char delimiter = delimiter_str==null ? ' ' : parseDelimiter(delimiter_str);

    if (writeparse) {
      System.out.print("COPY "+tablename+" TO "+dest);
      if (delimiter_str!=null)
	System.out.print(" USING DELIMITERS '"+delimiter+"'");
      if (null_string!=null)
	System.out.print(" WITH NULL AS '"+null_string+"'");
      if (true_string!=null)
	System.out.print(" WITH TRUE AS '"+true_string+"'");
      if (false_string!=null)
	System.out.print(" WITH FALSE AS '"+false_string+"'");
      System.out.println(";");
    }
    if (parseonly)
      return;

    if (null_string==null)
      null_string="\\N";
    if (true_string==null)
      true_string="1";
    if (false_string==null)
      false_string="0";

    String query = "SELECT * FROM "+tablename;
    Statement statement = connection.createStatement();
    ResultSet set = statement.executeQuery(query);
    ResultSetMetaData metadata = set.getMetaData();
    int ncolumns = metadata.getColumnCount();
    long nrows = 0;
    
    while (set.next()) {
      for (int i=0; i<ncolumns; ++i) {
	Object value = set.getObject(i+1);
	String str;
	if (value==null)
	  str = null_string;
	else if (value instanceof Boolean)
	  str = ((Boolean)value).booleanValue() ? true_string : false_string;
	else
	  str = applyEscapes(value.toString(),delimiter);
	if (delimiter==' ') {
	  // check for empty string and comment character
	  if (str.length()==0)
	    str="\\E";
	  else if (str.charAt(0)=='#' && i==0)
	    str='\\'+str;
	}
	if (i>0)
	  out.print(delimiter);
	out.print(str);
      }
      out.println();
      ++nrows;
    }

    if (dest.equals("stdout"))
      out.flush();
    else
      out.close();

    if (verbose)
      System.err.println("COPY TO "+nrows);
  }

  public static void executeCopy(String query, Connection connection) 
    throws SQLException,IOException {
    StreamTokenizer tokenizer = new StreamTokenizer(new StringReader(query));
    tokenizer.resetSyntax();
    tokenizer.whitespaceChars(0,' ');
    tokenizer.ordinaryChar('!');
    tokenizer.quoteChar('"');
    tokenizer.ordinaryChar('#');
    tokenizer.ordinaryChar('$');
    tokenizer.ordinaryChar('%');
    tokenizer.ordinaryChar('&');
    tokenizer.quoteChar('\'');
    tokenizer.ordinaryChar('(');
    tokenizer.ordinaryChar(')');
    tokenizer.ordinaryChar('*');
    tokenizer.ordinaryChar('+');
    tokenizer.ordinaryChar(',');
    tokenizer.ordinaryChar('-');
    tokenizer.wordChars('.','.');
    tokenizer.ordinaryChar('/');
    tokenizer.wordChars('0','9');
    tokenizer.ordinaryChar(':');
    tokenizer.ordinaryChar(';');
    tokenizer.ordinaryChar('<');
    tokenizer.ordinaryChar('=');
    tokenizer.ordinaryChar('>');
    tokenizer.wordChars('?','?');
    tokenizer.ordinaryChar('@');
    tokenizer.wordChars('A','Z');
    tokenizer.ordinaryChar('[');
    tokenizer.ordinaryChar('\\');
    tokenizer.ordinaryChar(']');
    tokenizer.ordinaryChar('^');
    tokenizer.wordChars('_','_');
    tokenizer.ordinaryChar('`');
    tokenizer.wordChars('a','z');
    tokenizer.ordinaryChar('{');
    tokenizer.ordinaryChar('|');
    tokenizer.ordinaryChar('}');
    tokenizer.ordinaryChar('~');
    tokenizer.ordinaryChar(127);
    tokenizer.wordChars(128,255);
    tokenizer.eolIsSignificant(false);
    tokenizer.lowerCaseMode(true);

    if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD ||
	!tokenizer.sval.equals("copy"))
      throw new SQLException("'COPY' expected at or before "+tokenizer);
    
    if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD)
      throw new SQLException("table name expected at or before "+tokenizer);
    String tablename = tokenizer.sval;

    if (tokenizer.nextToken()!=StreamTokenizer.TT_WORD)
      throw new SQLException("'TO' or 'FROM' expected at or before "+
			     tokenizer);
    if (tokenizer.sval.equals("to"))
      executeCopyTo(tablename,tokenizer,connection);
    else if (tokenizer.sval.equals("from"))
      executeCopyFrom(tablename,tokenizer,connection);
    else
      throw new SQLException("'TO' or 'FROM' expected at or before "+
			     tokenizer);
  }

  /**
   * Fixed width output.
   */
  public static long fixedWidthOutput(ResultSet set, PrintStream datastream) 
    throws SQLException {
    
    ResultSetMetaData metadata = set.getMetaData();
    int ncolumns = metadata.getColumnCount();

    int columntype[]  = new int[ncolumns];
    int columnwidth[] = new int[ncolumns];
    int minwidth=nullString.length();
    for (int i=0; i<ncolumns; ++i) {
      columntype[i]=metadata.getColumnType(i+1);
      switch(columntype[i]) {    
      case Types.NULL:         
	columnwidth[i]=Math.max(nullString.length(),minwidth);
	break;
	
      case Types.BIT:     
	columnwidth[i]=Math.max(trueString.length(),minwidth);
	columnwidth[i]=Math.max(falseString.length(),minwidth);
	break;
	
      case Types.TINYINT:   
	columnwidth[i]=Math.max(4,minwidth);
	break;
	
      case Types.SMALLINT:  
	columnwidth[i]=Math.max(6,minwidth);
	break;
	
      case Types.INTEGER:   
	columnwidth[i]=Math.max(11,minwidth);
	break;
	
      case Types.BIGINT:    
	columnwidth[i]=Math.max(22,minwidth);
	break;
	
      case Types.FLOAT:     
      case Types.REAL:      
      case Types.DOUBLE:    
	columnwidth[i]=Math.max(22,minwidth);
	break;
	
      case Types.NUMERIC:   
      case Types.DECIMAL:  
	columnwidth[i]=Math.max(10,minwidth);
	break;
	
      case Types.CHAR:      
      case Types.VARCHAR:   
      case Types.LONGVARCHAR: 
	columnwidth[i]=Math.max(metadata.getColumnDisplaySize(i+1),minwidth);
	break;
	
      case Types.DATE:        
      case Types.TIME:       
      case Types.TIMESTAMP:   
	columnwidth[i]=Math.max(10,minwidth);
	break;
	
      case Types.BINARY:      
      case Types.VARBINARY:    
      case Types.LONGVARBINARY:
	columnwidth[i]=Math.max(10,minwidth);
	break;
	
      default:
	columnwidth[i]=Math.max(10,minwidth);
	break;
      }
    }

    // print headers
    if (headerlevel>0) {
      for (int i=0; i<ncolumns; ++i) {
	StringBuffer s = new StringBuffer(metadata.getColumnLabel(i+1));
	if (columnwidth[i]<s.length())
	  columnwidth[i] = s.length();
	JustifyBuffer(s,columnwidth[i],columntype[i]);
	if (i>0)
	  datastream.print(' ');
	datastream.print(s);
      }
      datastream.println();
      if (headerlevel>1) {
	for (int i=0; i<ncolumns; ++i) {
	  StringBuffer s = new StringBuffer(columnwidth[i]);
	  for (int j=0; j<columnwidth[i]; ++j)
	    s.append('-');
	  if (i>0)
	    datastream.print(' ');
	  datastream.print(s);
	}
	datastream.println();
      }
    }
    
    // print data
    long count=0;
    while (set.next()) {
      for (int i=0; i<ncolumns; ++i) {
	Object value = set.getObject(i+1);
	if (value==null)
	  value = nullString;
	if (value instanceof Boolean)
	  value = ((Boolean)value).booleanValue() ? trueString : falseString;
	//	if (value instanceof Number)
	//	  value = value.toString();
	StringBuffer s = new StringBuffer(value.toString());
	JustifyBuffer(s,columnwidth[i],columntype[i]);
	if (i>0)
	  datastream.print(' ');
	datastream.print(s);
      }
      datastream.println();
      ++count;
    }
    return count;
  }
    
  /**
   * Squeezed fixed width output.
   */
  public static long squeezedOutput(ResultSet set, PrintStream datastream) 
    throws SQLException {

    ResultSetMetaData metadata = set.getMetaData();
    int ncolumns = metadata.getColumnCount();

    // read all data and store in array
    ArrayList rows = new ArrayList();
    int[] columnwidth = new int[ncolumns];
    Arrays.fill(columnwidth,0);
    while (set.next()) {
      String[] row = new String[ncolumns];
      for (int i=0; i<ncolumns; ++i) {
	Object value = set.getObject(i+1);
	if (value==null)
	  value = nullString;
	else if (value instanceof Boolean)
	  value = ((Boolean)value).booleanValue() ? trueString : falseString;
	row[i] = value.toString();
	if (columnwidth[i]<row[i].length())
	  columnwidth[i]=row[i].length();
      }
      rows.add(row);
    }

    // note column types
    int columntype[] = new int[ncolumns];
    for (int i=0; i<ncolumns; ++i)
      columntype[i]=metadata.getColumnType(i+1);

    // print headers
    if (headerlevel>0) {
      for (int i=0; i<ncolumns; ++i) {
	StringBuffer s = new StringBuffer(metadata.getColumnLabel(i+1));
	if (columnwidth[i]<s.length())
	  columnwidth[i] = s.length();
	JustifyBuffer(s,columnwidth[i],columntype[i]);
	if (i>0)
	  datastream.print(' ');
	datastream.print(s);
      }
      datastream.println();
      if (headerlevel>1) {
	for (int i=0; i<ncolumns; ++i) {
	  StringBuffer s = new StringBuffer(columnwidth[i]);
	  for (int j=0; j<columnwidth[i]; ++j)
	    s.append('-');
	  if (i>0)
	    datastream.print(' ');
	  datastream.print(s);
	}
	datastream.println();
      }
    }

    // print data
    for (int j=0; j<rows.size(); ++j) {
      String[] row = (String[])rows.get(j);
      for (int i=0; i<ncolumns; ++i) {
	StringBuffer s = new StringBuffer(row[i]);
	JustifyBuffer(s,columnwidth[i],columntype[i]);
	if (i>0)
	  datastream.print(' ');
	datastream.print(s);
      }
      datastream.println();
    }
    return rows.size();
  }
  
  /**
   * Delimited output.
   */
  public static long delimitedOutput(ResultSet set, PrintStream datastream) 
    throws SQLException {

    ResultSetMetaData metadata = set.getMetaData();
    int ncolumns = metadata.getColumnCount();

    // print headers
    if (headerlevel>0) {
      int widths[]  = new int[ncolumns];
      for (int i=1; i<=ncolumns; ++i) {
	String label = metadata.getColumnLabel(i);
	widths[i-1] = label.length();
	if (i>1) datastream.print(delimiter);
	datastream.print(label);
      }
      datastream.println();
      if (headerlevel>1) {
	for (int i=0; i<ncolumns; ++i) {
	  StringBuffer s = new StringBuffer(widths[i]);
	  for (int j=0; j<widths[i]; ++j)
	    s.append('-');
	  if (i>0) datastream.print(delimiter);
	  datastream.print(s);
	}
	datastream.println();
      }
    }
    
    // print data
    long count=0;
    while (set.next()) {
      for (int i=0; i<ncolumns; ++i) {
	Object value = set.getObject(i+1);
	if (value==null)
	  value = nullString;
	else if (value instanceof Boolean)
	  value = ((Boolean)value).booleanValue() ? trueString : falseString;
	if (i>0) datastream.print(delimiter);
	datastream.print(value);
      }
      datastream.println();
      ++count;
    }
    return count;
  }
  
  /**
   * Execute an SQL query.
   */
  public static void executeQuery(String query, Connection connection) 
    throws SQLException,IOException {

    String command = commandName(query);
    if (command.equals("COPY")) {
      executeCopy(query,connection);
      return;
    }

    PreparedStatement statement = connection.prepareStatement(query);
    if (writeparse)
      System.out.println(statement+";");
    if (parseonly)
      return;
    if (!statement.execute()) {
      if (verbose)
	System.err.println(command+" "+statement.getUpdateCount());
      return;
    }

    PrintStream outstream = outputfile!=null ? 
      new PrintStream(new FileOutputStream(outputfile)) : 
      System.out;
    
    ResultSet set = statement.getResultSet();

    long count;
    if (delimited)
      count = delimitedOutput(set,outstream);
    else if (squeeze)
      count = squeezedOutput(set,outstream);
    else
      count = fixedWidthOutput(set,outstream);
    outstream.flush();

    if (verbose)
      System.err.println(command+" "+count);

    // release resources
    statement.close();
  }


  public static boolean isNumber(String s) {
    if ((s==null)||(s.length()==0))
      return false;
    for (int i=0; i<s.length(); ++i) {
      char c = s.charAt(i);
      if (((c<'0')||(c>'9'))&&
	  (c!='+')&&(c!='-')&&(c!='.')&&(c!='e')&&(c!='E'))
	return false;
    }
    return true;
  }

  public static boolean isAllNumber(String s[]) {
    if (s==null)
      return false;
    for (int i=0; i<s.length; ++i)
      if (!isNumber(s[i]))
	return false;
    return true;
  }

  public static String addQuotes(String s) {
    if (s.startsWith("'")&&s.endsWith("'"))
      return s;
    if (s.startsWith("\"")&&s.endsWith("\""))
      return s;
    return "'"+s+"'";
  }

  public static void appendList(StringBuffer command, InputStream in, 
				boolean quote) 
    throws SQLException,IOException {

    StreamTokenizer tokenizer = new StreamTokenizer(new InputStreamReader(in));
    tokenizer.resetSyntax();
    tokenizer.whitespaceChars(0,' ');
    tokenizer.wordChars('!','!');
    tokenizer.quoteChar('"');
    tokenizer.commentChar('#');
    tokenizer.wordChars('$','&');
    tokenizer.quoteChar('\'');
    tokenizer.wordChars('(','+');
    tokenizer.whitespaceChars(',',',');
    tokenizer.wordChars('-','/');
    tokenizer.wordChars('0','9');
    tokenizer.whitespaceChars(':',';');
    tokenizer.wordChars('<','@');
    tokenizer.wordChars('A','Z');
    tokenizer.wordChars('[','`');
    tokenizer.wordChars('a','z');
    tokenizer.wordChars('{',127);
    tokenizer.wordChars(128,255);
    tokenizer.eolIsSignificant(false);

    boolean first=true;
    while (tokenizer.nextToken()!=tokenizer.TT_EOF) {

      if (!first) 
	command.append(",");

      switch (tokenizer.ttype) {
      case '\'':
      case '"':
	if (first)
	  quote=true;

      case StreamTokenizer.TT_WORD:
	if (!isNumber(tokenizer.sval)) {
	  if (first)
	    quote=true;
	  else if (!quote)
	    throw new SQLException("list contains string data, use $-q to read");
	}
	command.append(quote?addQuotes(tokenizer.sval):tokenizer.sval);
	break;
	
      default:
	throw new SQLException("invalid token: "+tokenizer.ttype);
      }
      
      first=false;
    }    
  }

  /**
   * Parse argument from command line for use in script.
   */
  public static void parseArgAndAppend(StringBuffer command, String argstring) 
    throws SQLException,IOException {

    // figure out with arg and seperate from remaining data
    String arg = null;
    String nextdata = null;
    boolean quote = false;
    if (argstring.startsWith("-")) {
      arg="-";
      nextdata=argstring.substring(1);
    }
    else {
      arg="";
      while ((argstring.length()>0)&&
	     (argstring.charAt(0)>='0')&&
	     (argstring.charAt(0)<='9')) {
	arg+=argstring.charAt(0);
	argstring=argstring.substring(1);
      }
      nextdata=argstring;
    }
    if (nextdata.startsWith("q")||nextdata.startsWith("Q")) {
      quote=true;
      nextdata=nextdata.substring(1);
    }
    
    // read list from standard input
    if (arg.equals("-")) 
      appendList(command,System.in,quote);

    // positional parameter
    else if (arg.length()>0) {
      if (programargs==null)
	throw new SQLException("no positional arguments available");
      int i = Integer.parseInt(arg);

      // all args as list
      if (i==0) {
	if (!isAllNumber(programargs))
	  quote=true;
	command.append(quote?addQuotes(programargs[0]):programargs[0]);
	for (int j=1; j<programargs.length; ++j)
	  command.append(","+(quote?addQuotes(programargs[j]):programargs[j]));
      }
      
      // a particular arg
      else if (--i<programargs.length) {
	if (!isNumber(programargs[i]))
	  quote=true;
	command.append(quote?addQuotes(programargs[i]):programargs[i]);
      }

      // arg is out of range
      else
	throw new SQLException("positional argument $"+(i+1)+
			       " is not available");
    }

    // not recognized as parameter
    else
      command.append("$");

    // tack on remaining data
    command.append(nextdata);
  }


  /**
   * Read script file and execute each command found.
   */
  public static void readScript(InputStream programinput, 
				Connection connection)
    throws SQLException,IOException {

    StreamTokenizer tokenizer = 
      new StreamTokenizer(new InputStreamReader(programinput));
    tokenizer.resetSyntax();
    tokenizer.wordChars(0,255);

    tokenizer.commentChar('#');  // script style comments
    tokenizer.ordinaryChar('-'); // SQL style comments

    tokenizer.ordinaryChar('$'); // arguments
    tokenizer.ordinaryChar(';'); // end of SQL command

    tokenizer.quoteChar('"');
    tokenizer.quoteChar('\'');

    tokenizer.eolIsSignificant(true);
    tokenizer.whitespaceChars('\n','\n');
    tokenizer.whitespaceChars('\r','\r');

    do {
      // close files and run garbage collection
      System.runFinalization();
      System.gc();

      // create command string
      StringBuffer command = new StringBuffer();
      while (tokenizer.nextToken()!=';' &&
	     tokenizer.ttype!=tokenizer.TT_EOF) {
	switch (tokenizer.ttype) {
	case '$':
	  tokenizer.nextToken();
	  if (tokenizer.ttype==tokenizer.TT_WORD)
	    parseArgAndAppend(command,tokenizer.sval);
	  else if (tokenizer.ttype=='-') {
	    if (tokenizer.nextToken()==tokenizer.TT_WORD)
	      parseArgAndAppend(command,"-"+tokenizer.sval);
	    else {
	      tokenizer.pushBack();
	      parseArgAndAppend(command,"-");
	    }
	  }
	  else {
	    command.append('$');
	    tokenizer.pushBack();
	  }
	  break;

	case '-':
	  if (tokenizer.nextToken()=='-') {
	    // comment to end of line
	    do {
	      tokenizer.nextToken();
	    } while (tokenizer.ttype!=tokenizer.TT_EOL &&
		     tokenizer.ttype!=tokenizer.TT_EOF);
	    command.append(' ');
	  }
	  else {
	    command.append('-');
	    tokenizer.pushBack();
	  }
	  break;

	case '\'':
	case '"':
	  command.append("'"+tokenizer.sval+"'");
	  break;

	case StreamTokenizer.TT_WORD:
	  command.append(tokenizer.sval);
	  break;

	case StreamTokenizer.TT_EOL:
	  // end of line is white space
	  command.append(' ');
	  break;

	default:
	  throw new SQLException("unrecognized token: "+tokenizer.ttype);
	}
      }

      // execute query
      String c = command.toString().trim();
      if (c.length()>0)
	executeQuery(c,connection);

    } while (tokenizer.ttype!=tokenizer.TT_EOF);
  }


  /**
   * Main program.
   */
  public static void main(String[] args) {

    if (!parseArgs(args)) {
      System.err.println("failed to parse args");
      return;
    }

    if (donothing)
      return;

    try {
      InputStream programinput = null;
      if (programfile!=null) 
	programinput = 
	  new BufferedInputStream(new FileInputStream(programfile));

      if ((command==null)&&(programinput==null)) {
	System.err.println("nothing to do! (use -h for help)");
	return;
      }
    
      if ((command!=null)&&(programinput!=null)) {
	System.err.println("cannot specify command and program file (use -h for help)");
	return;
      }

      // load required classes
      Class.forName(databaseClass);

      if (debuglevel>0)
	ModSQL.Driver.setDebugLevel(debuglevel);

      Connection connection = DriverManager.getConnection(databaseURL);

      if (command!=null)
	executeQuery(command,connection);
      else 
	readScript(programinput,connection);
    }

    catch (Exception e) {
      if (debuglevel>0)
	e.printStackTrace();
      else
	System.err.println(e.toString());
    }
  }
}