/* A program to edit sqlite3 databases
 *
 * Copyright (C) 2015 Sidney Marshall (swm@cs.rit.edu)
 *
 * This program is free software: you can redistribute it and/or
 * modify it under the terms of the GNU General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program 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
 * General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see
 * <http://www.gnu.org/licenses/>.
 */

//import javax.swing.ListCellRenderer;
//import javax.swing.filechooser.FileFilter;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Component;
import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.Point;
import java.awt.Rectangle;
import java.awt.Toolkit;
import java.awt.datatransfer.DataFlavor;
import java.awt.datatransfer.StringSelection;
import java.awt.datatransfer.Transferable;
import java.awt.datatransfer.UnsupportedFlavorException;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.awt.print.PrinterException;
import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import javax.swing.AbstractAction;
import javax.swing.ActionMap;
import javax.swing.Box;
import javax.swing.BoxLayout;
import javax.swing.DefaultComboBoxModel;
import javax.swing.DefaultListCellRenderer;
import javax.swing.DropMode;
import javax.swing.InputMap;
import javax.swing.JButton;
import javax.swing.JCheckBox;
import javax.swing.JComboBox;
import javax.swing.JComponent;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JList;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.KeyStroke;
import javax.swing.ListSelectionModel;
import javax.swing.ScrollPaneConstants;
import javax.swing.SwingConstants;
import javax.swing.SwingUtilities;
import javax.swing.TransferHandler;
import javax.swing.border.EmptyBorder;
import javax.swing.event.DocumentEvent;
import javax.swing.event.DocumentListener;
import javax.swing.event.UndoableEditEvent;
import javax.swing.event.UndoableEditListener;
import javax.swing.filechooser.FileNameExtensionFilter;
import javax.swing.table.AbstractTableModel;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.TableCellEditor;
import javax.swing.table.TableColumn;
import javax.swing.text.Caret;
import javax.swing.text.Document;
import javax.swing.undo.CannotUndoException;
import javax.swing.undo.UndoManager;

// Note that 'xxx' is a string and "xxx" is an identifier in SQL.
// could fix this by using illegal character as db/table separator.

/**
 * This class implements a window displaying the tables and views
 * contained in a database (or all tables and views in all attached
 * databases).
 *
 * Left clicking on a table name will bring up a table editor. Right
 * clicking on a table name will bring up a find/replace window that
 * operates on tables. There are four buttons:
 *   Refresh - refreshes the window contents (needed after adding tables)
 *   Show Hidden Tables - shows sqlite tables and name of database
 *   Open Command Window - opens a command window for sql statements
 *   Open Database - opens a database in a new process
 *
 * Types of windows:
 *   database chooser - chooses a database file to edit or view
 *   database editor - list of tables and views in database
 *   table editor - contents of a database table or view or result set
 *   result table viewer - a view on a select statement result
 *   cell editor - editor for a cell in a table, view, or result set
 *   command window - a window for executing sql commands
 *   find/replace - controls finding and replacing on a cell editor
 *   column search window - searches a table column
 *
 * All internal operations oprate on strings. When accessing the
 * database the dbCharset is used for translation to and from the
 * database to Strings (the second argument on the command line
 * defaulting to UTF-8). When accessing files the fileCharset is used
 * to convert file bytes to and from Strings (the third argument on
 * the command line defaulting to UTF-8).
 */
public class DatabaseEditor extends JFrame {
  final static long serialVersionUID = 42;

  Connection conn; // the database connection
  ArrayList<JFrame> dependents = new ArrayList<JFrame>();
  //static Charset charset = Charset.availableCharsets().get("windows-1252");
  //static Charset charset = Charset.availableCharsets().get("UTF-8");
  // null for default character set
  static Charset dbCharset = Charset.availableCharsets().get("UTF-8"); // use (UTF-8)
  static Charset fileCharset = Charset.availableCharsets().get("UTF-8"); // use (UTF-8)

  /**
   * Closes a closable closable file/object ignoring null arguments
   * and handling exceptions.
   *
   * @param file the file/object to be closed
   */
  static void close(Closeable file) {
    try {
      if(file != null) file.close();
    } catch(IOException e) {
      e.printStackTrace();
    }
  }

  /**
   * Gets an item from a ResultSet using the statically defined
   * character set.
   *
   * @param rs the result set to use
   * @param i the column number to fetch
   * @return the result string or null
   * @throws SQLException Throws if problem with SQLite.
   */
  String getWithCharset(ResultSet rs, int i) throws SQLException {
    //if(dbCharset == null) return rs.getString(i);
    byte[] data = rs.getBytes(i);
    if(data == null) return rs.getString(i); // to return possible empty string
    return new String(data, dbCharset);
  }

  /**
   * Sets a ? item in a PreparedStatement from a string using the java
   * default character set
   *
   * This method originally created blobs created by setBytes causing
   * problems when comparing with numerical values. The commented out
   * lines exhibit this behavior. The current setString method does
   * not create blobs. We probably should check and use Strings for
   * numbers and blobs for non-numbers.
   *
   * Could cast the ? to a TEXT via "cast(? as text)"
   *
   * @param stat the prepared statement with ?
   * @param i the index of the ? to be set
   * @param data the string of data or null
   * @throws SQLException thrown if problems with SQLite
   */
  void putWithCharset(PreparedStatement stat, int i, String data) throws SQLException {
    if(data == null) {
      stat.setNull(i, java.sql.Types.NULL);
    } else {
      //byte[] raw = dbCharset == null ? data.getBytes() : data.getBytes(dbCharset);
      //stat.setBytes(i, raw);
      stat.setString(i, data);
    }
  }

  static final int BUFSIZ = 4096; // used for buffer size for reading

  /**
   * Read an entire file into a String using fileCharset for
   * conversion. Note that the conversion must be all at once because
   * characters may straddle buffer boundaries. Returns null on error.
   *
   * @param file the name of the file to be read in
   * @return the entire contents of the file converted to a String
   */
  String readFileIntoString(File file) {
    FileInputStream in = null;
    int totalRead = 0;
    int numRead;
    byte[] bytearray = null;
    byte[] buffer = new byte[BUFSIZ];
    try {
      in = new FileInputStream(file);
      ArrayList<byte[]> buffers = new ArrayList<byte[]>();
      while((numRead = in.read(buffer, 0, BUFSIZ)) > 0) {
        if(numRead != BUFSIZ) {
          byte[] temp = new byte[numRead];
          System.arraycopy(buffer, 0, temp, 0, numRead);
          buffer = temp;
        }
        buffers.add(buffer);
        buffer = new byte[BUFSIZ];
        totalRead += numRead;
      }
      bytearray = new byte[totalRead];
      int bufPointer = 0;
      for(byte[] array : buffers) {
        int size = array.length;
        System.arraycopy(array, 0, bytearray, bufPointer, size);
        bufPointer += size;
      }
    } catch(IOException ex) {
      ex.printStackTrace();
    } finally {
      close(in);
    }
    return new String(bytearray, fileCharset);
  }

  /**
   * if null return "null" otherwise surround string with the quote
   * character and escape quote characters with a double quote
   * character. This will allow using strings in SQL statements and
   * double quotes for identifiers.
   *
   * We now use ? in sql for data - this handles nulls too.
   *
   * @param s the String to escape
   * @param quote the quote character
   * @return the escaped string
   */
  static String escapeString(String s, char quote) {
    if(s == null) return "null";
    String q1 = Character.toString(quote);
    String q2 = q1 + q1;
    return q1 + s.replace(q1, q2) + q1;
  }

  /**
   * Finds String in another String ignoring case if ignoreCase is
   * true. If s1 is null, returns -1. Search starts at fromIndex. To
   * search the entire string set fromIndex to 0.
   *
   * @param ignoreCase true if case is to be ignored
   * @param s1 String to search
   * @param s2 String to search for
   * @param fromIndex location to start search
   * @return index of s2 in s1 or -1 if unsuccessful
   */
  int findIgnoreCase(boolean ignoreCase, String s1, String s2, int fromIndex) {
    if(s2.length() == 0) return -1;
    if(s1 == null) return -1;
    if(ignoreCase) {
      int length = s2.length();
      for(int i = fromIndex; i <= s1.length() - length; ++i) {
        if(s1.substring(i, i + length).equalsIgnoreCase(s2)) {
          return i;
        }
      }
      return -1;
    }
    return s1.indexOf(s2, fromIndex);
  }

  /**
   * An undoable text edit window
   *
   * This is the base class for all of the text windows. Since the
   * layout is BorderLayout (the default) clients can add buttons or
   * other things around the text window. The variable textArea can be
   * used to add listeners to the text area. The number of undos or
   * redos is unlimited.
   */
  class EditWindow extends JFrame {
    final static long serialVersionUID = 42;

    ArrayList<JFrame> dependents = new ArrayList<JFrame>();
    JTextArea textArea;

    /**
     * Make a text edit window with given title and
     * contents. Dependent windows will be closed. The caller is
     * responsible for displaying the window and adding any
     * appropriate listeners.
     *
     * @param title the title of the window
     * @param contents the initial contents of the window
     * @param parent the JFrame on which this window is dependent.
     */
    EditWindow(String title, String contents, JFrame parent) {
      super(title);
      addWindowListener(new WindowAdapter() {
          public void windowClosing(WindowEvent e) { EditWindow.this.dispose(); }
          public void windowClosed(WindowEvent e) {
            for(JFrame d : dependents) d.dispose();
            if(parent instanceof Table) {
              Table p = (Table)parent;
              if(p.dependents.remove(EditWindow.this)) {
              } else if(DatabaseEditor.this.dependents.remove(EditWindow.this)) {
              } else {
                System.out.println("Can't find#1: " + EditWindow.this.getTitle());
              }
            } else if(parent instanceof DatabaseEditor) {
              DatabaseEditor p = (DatabaseEditor)parent;
              if(p.dependents.remove(EditWindow.this)) {
              } else {
                System.out.println("Can't find#2: " + EditWindow.this.getTitle());
              }
            } else {
              System.out.println("bad parent: " + parent);
            }
          }});
      textArea = new UndoableTextArea(contents);
      textArea.setLineWrap(true);
      textArea.setWrapStyleWord(true);
      textArea.addMouseListener(new MouseAdapter() {
          public void mouseClicked(MouseEvent e) {
            if(e.getButton() == 3) {
              JFrame findReplace = new FindReplace(EditWindow.this, true);
              EditWindow.this.dependents.add(findReplace);
              findReplace.pack();
              findReplace.setVisible(true);
            }
          }
        });
      JScrollPane areaScrollPane = new JScrollPane(textArea);
      setPreferredSize(new Dimension(600, 300));
      getContentPane().add(areaScrollPane);
    }

    /**
     * Wrap lines in text that are too long. Insert prefix before broken
     * lines. Delete (some) trailing whitespace
     *
     * @param text The text to wrap lines in.
     * @param maxLineLength the maximum line length desired
     * @param prefix the prefix for lines that had to be split
     * @return a String with all lines wrapped
     */
    String wrapLines(String text, int maxLineLength, String prefix) {
      int maxLength = maxLineLength;
      int shortLength = maxLineLength - prefix.length();
      StringBuilder newText = new StringBuilder();
      //String text = textArea.getText();
      int textLength = text.length();
      while(textLength > 0 && (text.charAt(textLength - 1) == '\n' ||
                               text.charAt(textLength - 1) == ' ')) {
        --textLength;
      }
      text = text.substring(0, textLength) + '\n';
      int end;

      for(int start = 0 ; start < textLength ; start = end) {
        end = text.indexOf('\n', start) + 1;
        if(end == -1) end = textLength;
        int lineLength = end - start - 1;
        if(lineLength <= maxLength) {
          newText.append(text.substring(start, end));
          maxLength = maxLineLength;
        } else {
          end = text.lastIndexOf(' ', start + maxLength);
          if(end <= start) end = start + maxLength;
          newText.append(text.substring(start, end));
          newText.append('\n');
          newText.append(prefix);
          maxLength = shortLength;
        }
      }
      return newText.toString();
    }

    /**
     * Break lines in paragaphs respecting the maxLength parameter.
     *
     * start processing =&gt;
     *  get indent, line length, skip whitespace
     *  set NLin
     *
     * letter: add to word
     *
     * space: if word != ""
     *
     * generally maxLength = 70
     *
     * @param maxLength the maximum line length desired
     */
    void breakLines(int maxLength) {
      String text = textArea.getText();
      int start = textArea.getSelectionStart();
      int end = textArea.getSelectionEnd();
      // Find start of current line - OK if it is -1
      int in = text.lastIndexOf('\n', start - 1);
      int lineLength = start - in - 1;
      StringBuilder newText = new StringBuilder();
      StringBuilder indent = new StringBuilder();
      int NLin = 0; // number of consecutive NLs
      // set first indent
      char c = 0;
      while(++in < end && (c = text.charAt(in)) <= ' ') {
        if(c == '\n') {
          //lineLength = maxLength;
          indent.setLength(0);
          NLin = 1;
          //newText.append('\n');
          newText.append(indent);
          //newText.append("xxxxx");
          break;
        } else {
          indent.append(c);
          if(in >= start) {
            ++lineLength;
          }
        }
      }
      // first indent is now set
      // handle first word at beginning of selection
      in = start;
      // don't touch if start to middle of word
      // or if not at end of word
      // if start in indent
      while(in < end) {
        c = text.charAt(in);
        if(c > ' ') {
          newText.append(c);
          ++lineLength;
          ++in;
        } else break;
      }
      StringBuilder word = new StringBuilder();
      /*
       * State
       * in - in into input string
       * lineLength - length of current line
       *            - maxLength if forcing a new line
       * word - current word being built
       * newText - replacement text being built
       * indent - current indentation string
       * NLin - number of NLs in whitespace
       *
       */
      //System.out.println("------------------");
      while(in < end) {
        /*
          System.out.println("in: " + in +
          " lineLength: " + lineLength +
          " word: |" + word +
          "| newText: |" + newText +
          "| indent: |" + indent +
          "| NLin: " + NLin);
        */
        c = text.charAt(in++);
        if(c > ' ') {
          word.append(c);
        } else {
          if(word.length() > 0) {
            int newLength
              = lineLength + word.length() + 1;
            if(newLength < maxLength) {
              newText.append(' ');
              lineLength = newLength;
            } else {
              newText.append('\n');
              newText.append(indent);
              lineLength = indent.length() + word.length();
            }
            newText.append(word);
            word.setLength(0);
            NLin = 0;
          }
          if(c == '\n') {
            ++NLin;
            if(NLin > 1) {
              indent.setLength(0);
              lineLength = maxLength;
              newText.append('\n');
            }
          } else if(NLin > 1) {
            indent.append(c);
          }
        }
      }
      // need to cleanup last word
      // should get length of word after end
      // preserve space after last word
      if(word.length() > 0) {
        int wl = word.length();
        for(int i = end; i < text.length(); i++) {
          if(text.charAt(i) > ' ') {
            ++wl;
          } else break;
        }
        int newLength = lineLength + wl + 1;
        if(newLength < maxLength) {
          newText.append(' ');
          lineLength = newLength;
        } else {
          newText.append('\n');
          newText.append(indent);
        }
        newText.append(word);
        if(c == '\n') newText.append(c);
        else if(c <= ' ') newText.append(' ');
      } else {
        // add ' ' if ???
      }
      textArea.replaceSelection(newText.toString());
      end = textArea.getSelectionEnd();
      textArea.select(end - newText.length(), end);
      textArea.grabFocus(); // to show current selection
    }

    /**
     * Print the contents of the EditWindow.
     * //////////// should be in separate thread.
     *
     * @param title The title on each page
     */
    void print(String title) {
      MessageFormat footer = new MessageFormat("Page - {0}");
      try {
        JTextArea ta = new JTextArea(wrapLines(textArea.getText(), 80, ">>>>") + "\n "); // needed to print last line
        ta.print(new MessageFormat(title), footer);
      } catch(PrinterException e) {
        System.out.println(e);
      }
    }
  } // class EditWindow extends JFrame

  /**
   * This class implements a window displaying the contents of a
   * database table, view, or result set. The first constructor
   * displays tables and views and the second constructor displays
   * result sets.
   */
  class Table extends JFrame {
    final static long serialVersionUID = 42;

    String tableName; // the name of the table (and title of window)
    ArrayList<JFrame> dependents = new ArrayList<JFrame>();
    JTable table;
    ArrayList<String> heading;
    ArrayList<ArrayList<String>> data; // row of columns
    ArrayList<ArrayList<String>> oldData; // original data saved for possible restore
    ArrayList<String> rowid; // _rowid_ for each row for updating

    /**
     * Initialize tables for a table or view with null argument.
     * Initialize tables for a result set with result set argument.
     * If called with a null argument then the variable tableName is used
     * as the table to view.
     *
     * Assume that names of databases and tables do not contain "."
     *
     * @param rs the result set to view or null if a table or view
     */
    void initTables(ResultSet rs) {
      boolean flag = rs == null; // true if table; false if result set
      heading = new ArrayList<String>();
      data = new ArrayList<ArrayList<String>>();
      oldData = new ArrayList<ArrayList<String>>();
      rowid = new ArrayList<String>();
      Statement stat = null;
      try {
        if(flag) {
          stat = conn.createStatement();
          //views no longer have a _rowid_ so must check
          try {
            rs = stat.executeQuery("select _rowid_,* from \""
                                   + tableName.replace(".", "\".\"")
                                   + "\";");
          } catch(SQLException ex) {
            rs = stat.executeQuery("select null as _rowid_,* from \""
                                   + tableName.replace(".", "\".\"")
                                   + "\";");
          }
        }
        ResultSetMetaData md = rs.getMetaData();
        int n = md.getColumnCount();

        for(int i = (flag ? 2 : 1); i <= n; i++) {
          heading.add(md.getColumnName(i));
        }
        while (rs.next()) {
          rowid.add(flag ? rs.getString(1) : null);
          ArrayList<String> row = new ArrayList<String>();
          ArrayList<String> oldRow = new ArrayList<String>();
          for(int i = (flag ? 2 : 1); i <= n; i++) {
            String s = getWithCharset(rs, i);
            row.add(s);
            oldRow.add(s);
          }
          data.add(row);
          oldData.add(oldRow);
        }
      } catch(SQLException ex) {
        ex.printStackTrace();
      } finally {
        try {
          if(stat != null) stat.close();
        } catch(SQLException closeex) {
          closeex.printStackTrace();
        }
      }
    }

    /**
     * Compare two Strings for equality. Also handles nulls.
     *
     * @param x first string
     * @param y second string
     * @return true if strings are equal or both are null
     */
    boolean eql(String x, String y) {
      if(x == y) return true;
      if(x == null || y == null) return false;
      return x.equals(y);
    }

    /**
     * This TableModel gets data from and stores data into the
     * containing Table class.  The model allows drag &amp; drop and
     * cell editing. The default editor is disabled if there is a
     * new-line in the string.
     */
    AbstractTableModel tableModel = new AbstractTableModel() {
        final static long serialVersionUID = 42;

        public int getColumnCount() {
          return heading.size();
        }

        public int getRowCount() {
          return data.size();
        }

        public String getColumnName(int col) {
          return heading.get(col);
        }

        public Object getValueAt(int row, int col) {
          return data.get(row).get(col);
        }

        /*
         * JTable uses this method to determine the default renderer/
         * editor for each cell. If we didn't implement this method then
         * the editor might not be a String editor. I always use
         * Strings.
         */
        public Class<?> getColumnClass(int c) {
          return String.class;
        }

        /**
         * Disable the default editor if the cell contains a new line as
         * the default editor is not reliable in this case.
         */
        public boolean isCellEditable(int row, int col) {
          String value = (String)tableModel.getValueAt(row, col);
          return value == null || value.indexOf('\n') < 0;
        }

        public void setValueAt(Object value, int row, int col) {
          if(row < data.size()) {
            String current = data.get(row).get(col);
            if(!eql((String)value, current)) {
              data.get(row).set(col, (String)value);
              fireTableCellUpdated(row, col);
            }
          }
        }
      };

    /**
     * Create an edit window on a Table cell and update table focus to
     * edited cell. Caller is responsible for setting location and
     * showing the window. Don't search for an existing cellEditWindow
     * if rowid is null.
     *
     * @param jtable the table containing the cell to be edited
     * @param i the row of the Table being edited
     * @param jj the column of the Table being edited
     * @param location the location to put the CellEditWindow
     * @return returns the new EditWindow
     */
    EditWindow makeCellEditWindow(Table jtable, int i, int jj, Rectangle location) {
      table.changeSelection(i, jj, false, false); // update focus
      int j = jtable.table.convertColumnIndexToModel(jj);
      String id = rowid.get(i); // used in case rows are deleted
      String title = tableName + "." + heading.get(j)
        + "[" + id + "]";
      if(id != null) {
        for(JFrame jf : jtable.dependents) {
          if(jf.getTitle().equals(title)) {
            EditWindow editw = (EditWindow)jf;
            editw.setExtendedState(JFrame.NORMAL);
            editw.setVisible(true);
            return editw;
          }
        }
      }
      EditWindow editWindow = new EditWindow(title, (String)tableModel.getValueAt(i, j), jtable);
      JTextArea textArea = editWindow.textArea;
      DocumentListener documentListener = new DocumentListener() {
          Color color = Color.white;

          void checkForChanges() {
            String window = textArea.getText();
            String table = (String)tableModel.getValueAt(i, j);
            Color temp = eql(window, table) ? Color.white : Color.pink;
            if(!color.equals(temp)) {
              textArea.setBackground(temp);
              color = temp;
            }
          }

          public void changedUpdate(DocumentEvent e) {
            checkForChanges();
          };

          public void insertUpdate(DocumentEvent e) {
            checkForChanges();
          };

          public void removeUpdate(DocumentEvent e) {
            checkForChanges();
          };
        };
      textArea.getDocument().addDocumentListener(documentListener);
      JPanel btns = new JPanel(new GridLayout(id != null ? 4 : 3, 1));
      btns.add(new JButton("Open in Command Window") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  textArea.grabFocus(); // to show current selection
                  makeCommandWindow("Data Command Window",
                                    textArea.getText());
                }
              });
          }
        });
      btns.add(new JButton("Break Lines in Selection") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  editWindow.breakLines(70);
                }
              });
          }
        });
      btns.add(new JButton("Print") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  editWindow.print(editWindow.getTitle());
                }
              });
          }
        });
      if(id != null) {
        btns.add(new JButton("Update") {
            final static long serialVersionUID = 42;

            {
              addActionListener(new ActionListener() {
                  public void actionPerformed(ActionEvent e) {
                    String s = textArea.getText();
                    int newi = rowid.indexOf(id);
                    if(newi >= 0) {
                      tableModel.setValueAt(s, newi, j);
                      documentListener.changedUpdate(null);
                    }
                    textArea.grabFocus(); // to show current selection
                  }
                });
            }
          });
      }
      editWindow.getContentPane().add(btns, BorderLayout.SOUTH);
      if(id != null) Table.this.dependents.add(editWindow); // if result set then keep longer
      else DatabaseEditor.this.dependents.add(editWindow); // if table or view then depend on table
      if(location != null) {
        editWindow.setLocation(location.x, location.y + location.height);
        editWindow.pack();
        editWindow.setVisible(true);
      } else {
        editWindow.setLocationByPlatform(true);
        editWindow.pack();
        editWindow.setVisible(true);
      }
      return editWindow;
    }

    /**
     * This is the JPanel displaying the database table, view, or
     * result set.  Left clicking on a cell will select it. Double
     * clicking on a cell will enable the cell editor if the cell does
     * not contain new lines. Right clicking on a cell will bring up a
     * cell editor window. Right clicking on a column label will bring
     * up a find/replace window on the column.
     */
    class DBTable extends JPanel {
      final static long serialVersionUID = 42;

      /**
       * Constructor for displaying a table, view, or ResultSet. With
       * a null argument this will display the table names in the
       * variable tableName.
       *
       * @param rs ResultSet to display or null if table of view
       */
      public DBTable(ResultSet rs) {
        super(new GridLayout(1,0));

        setOpaque(true);

        initTables(rs);

        table = new JTable(tableModel);
        table.setAutoCreateColumnsFromModel(true);
        table.setPreferredScrollableViewportSize(new Dimension(500, 70));
        table.setFillsViewportHeight(true);
        table.setRowSelectionAllowed(false);
        table.setColumnSelectionAllowed(false);
        table.setCellSelectionEnabled(true);
        table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
        table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
        table.setDragEnabled(true);
        table.setDropMode(DropMode.ON);
        table.setDefaultRenderer(String.class,
                                 new DefaultTableCellRenderer() {
          final static long serialVersionUID = 42;

          /**
           * This class controls the background color for table cells.
           */
          public Component getTableCellRendererComponent(JTable table,
                                                         Object value,
                                                         boolean isSelected,
                                                         boolean hasFocus,
                                                         int row,
                                                         int viewColumn) {
            if (table == null) {
              return this;
            }

            int column = table.convertColumnIndexToModel(viewColumn);

            Color fg = null;
            Color bg = null;

            String val = data.get(row).get(column);
            boolean changed = !eql(oldData.get(row).get(column), val);

            JTable.DropLocation dropLocation = table.getDropLocation();
            if (dropLocation != null
                && !dropLocation.isInsertRow()
                && !dropLocation.isInsertColumn()
                && dropLocation.getRow() == row
                && table.convertColumnIndexToModel(dropLocation.getColumn()) == column) {
              fg = Color.blue;
              bg = Color.pink;
              isSelected = true;
            }

            if (isSelected) {
              super.setForeground(fg == null
                                  ? table.getSelectionForeground()
                                  : fg);
              super.setBackground(bg == null
                                  ? !changed
                                  ? table.getSelectionBackground()
                                  : Color.red
                                  : bg);
            } else {
              Color background = table.getBackground();
              if(changed) {
                background = Color.pink;
              } else if (val == null) {
                background = Color.gray;
              }
              super.setForeground(table.getForeground());
              super.setBackground(background);
            }
            setFont(table.getFont());
            setBorder(new EmptyBorder(1, 1, 1, 1));
            setValue(value);
            return this;
          }
        });
        table.setTransferHandler(new TransferHandler() {
            final static long serialVersionUID = 42;

            /**
             * This class handles drag & drop. Strings and files can
             * be dropped. For files only the file name without
             * directory information is retained. Dropping multiple
             * files is not supported.
             */
            public boolean canImport(TransferHandler.TransferSupport info) {
              if (!info.isDrop()) return false; // no clipboard past
              info.setShowDropLocation(true);
              if (info.isDataFlavorSupported(DataFlavor.javaFileListFlavor)) {
                // fetch the drop location
                JTable.DropLocation dl = (JTable.DropLocation)info.getDropLocation();
                int row = dl.getRow();
                int col = table.convertColumnIndexToModel(dl.getColumn());
                if(col < 0) return false;
                return true;
              }
              if (info.isDataFlavorSupported(DataFlavor.stringFlavor)) {
                JTable.DropLocation dl = (JTable.DropLocation)info.getDropLocation();
                int row = dl.getRow();
                int col = table.convertColumnIndexToModel(dl.getColumn());
                if(col < 0) return false;
                return true;
              }
              return false;
            }

            public boolean importData(TransferHandler.TransferSupport info) {
              if (!canImport(info)) {
                return false;
              }
              JTable.DropLocation dl = (JTable.DropLocation)info.getDropLocation();
              int row = dl.getRow();
              int col = table.convertColumnIndexToModel(dl.getColumn());
              String data;
              try {
                if(info.isDataFlavorSupported(DataFlavor.javaFileListFlavor)) {
                  List<?> files = (List<?>)info.getTransferable().getTransferData(DataFlavor.javaFileListFlavor);
                  if(files.size() != 1) return false; // can handle only one file
                  data = ((File)files.get(0)).getName();
                } else if(info.isDataFlavorSupported(DataFlavor.stringFlavor)) {
                  data = (String)info.getTransferable().getTransferData(DataFlavor.stringFlavor);
                } else {
                  return false;
                }
              } catch(UnsupportedFlavorException | IOException e) {
                return false;
              }
              table.getModel().setValueAt(data, row, col);
              table.changeSelection(row, dl.getColumn(), false, false); // update focus
              return true;
            }

            public int getSourceActions(JComponent c) {
              return COPY;
            }

            public Transferable createTransferable(JComponent c) {
              return new StringSelection(data.get(table.getSelectedRow()).get(table.convertColumnIndexToModel(table.getSelectedColumn())));
            }

            public void exportDone(JComponent c, Transferable t, int i) {
            }
          });
        table.getTableHeader().addMouseListener(new MouseAdapter() {
            public void mouseClicked(MouseEvent e) {
              if(e.getButton() == 3) {
                Point p = e.getPoint();
                int jj = table.columnAtPoint(p);
                int j = table.convertColumnIndexToModel(jj);
                String columnName = Table.this.heading.get(j);
                Search search = new Search(Table.this);
                DefaultComboBoxModel<String> model = (DefaultComboBoxModel<String>)search.columnField.getModel();
                model.setSelectedItem(columnName);
                Table.this.dependents.add(search);
                Rectangle r = Table.this.getBounds();
                search.setLocation(r.x + r.width, r.y);
                search.tableField.setText(tableName);
                search.pack();
                search.setVisible(true);
              }
            }
          });
        table.addMouseListener(new MouseAdapter() {
            public void mouseClicked(MouseEvent e) {
              if(e.getButton() == 3) {
                Point p = e.getPoint();
                int i = table.rowAtPoint(p);
                int jj = table.columnAtPoint(p);
                EditWindow cellEditor = makeCellEditWindow(Table.this, i, jj, null);
              }
            }
          });
        for (int i = 0; i < heading.size(); i++) {
          TableColumn column = table.getColumnModel().getColumn(i);
          column.setPreferredWidth(100);
        }
        ((DefaultTableCellRenderer)table.getTableHeader().getDefaultRenderer())
          .setHorizontalAlignment(SwingConstants.LEFT);

        JScrollPane scrollPane =
          new JScrollPane(table, ScrollPaneConstants.VERTICAL_SCROLLBAR_ALWAYS,
                          ScrollPaneConstants.HORIZONTAL_SCROLLBAR_ALWAYS);
        add(scrollPane);
      }
    } // class DBTable extends JPanel

    /**
     * For displaying tables or views
     *
     * @param tableName the title of the window and table or view to display
     */
    Table(String tableName) {
      this.tableName = tableName;
      DatabaseEditor.this.dependents.add(this);
      addWindowListener(new WindowAdapter() {
          public void windowClosing(WindowEvent e) { dispose(); }
          public void windowClosed(WindowEvent e) {
            for(JFrame d : dependents) d.dispose();
            if(DatabaseEditor.this.dependents.remove(Table.this)) {
            } else {
              System.out.println("Can't find#3: " + Table.this.getTitle());
            }
          }
        });
      setTitle(tableName);
      getContentPane().add(new DBTable(null));
      JPanel buttons = new JPanel(new GridLayout(2, 4));
      buttons.add(new JButton("Delete Selected Rows") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  TableCellEditor te = table.getCellEditor();
                  if(te != null) te.stopCellEditing();
                  deleteRows();
                }
              });
          }
        });
      buttons.add(new JButton("Make NULL") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  TableCellEditor te = table.getCellEditor();
                  if(te != null) te.stopCellEditing();
                  makeNull();
                }
              });
          }
        });
      buttons.add(new JButton("Revert Selected") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  TableCellEditor te = table.getCellEditor();
                  if(te != null) te.stopCellEditing();
                  revertSelected();
                }
              });
          }
        });
      buttons.add(new JButton("Revert Table") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  TableCellEditor te = table.getCellEditor();
                  if(te != null) te.stopCellEditing();
                  revertTable();
                }
              });
          }
        });
      buttons.add(new JButton("Add Row") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  TableCellEditor te = table.getCellEditor();
                  if(te != null) te.stopCellEditing();
                  addRow();
                }
              });
          }
        });
      buttons.add(new JButton("Fill Column") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  int i = table.getSelectedRow();
                  int j = table.convertColumnIndexToModel(table.getSelectedColumn());
                  if(i < 0 || j < 0) return;
                  if(data.get(i).get(j) == null) {
                    if(i == 0) return;
                    if(data.get(i - 1).get(j) != null) {
                      fillColumn(i, j);
                    } else {
                      return;
                    }
                  } else if(i < data.size() - 1 && data.get(i + 1).get(j) == null) {
                      fillColumn(i + 1, j);
                  } else {
                    if(i < data.size() - 2 && data.get(i + 2).get(j) == null) {
                      fillColumn(i + 2, j, data.get(i).get(j), data.get(i + 1).get(j));
                    }
                  }
                }
              });
          }
        });
      buttons.add(new JButton("Update Selected") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  TableCellEditor te = table.getCellEditor();
                  if(te != null) te.stopCellEditing();
                  updateSelected();
                }
              });
          }
        });
      buttons.add(new JButton("Update Table") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  TableCellEditor te = table.getCellEditor();
                  if(te != null) te.stopCellEditing();
                  updateTable();
                }
              });
          }
        });
      getContentPane().add(buttons, BorderLayout.SOUTH);
      setLocationByPlatform(true);
    }

    /**
     * For displaying result sets
     *
     * @param rs the ResultSet to display
     * @param tableName the title of the window
     */
    Table(ResultSet rs, String tableName) {
      this.tableName = tableName;
      DatabaseEditor.this.dependents.add(this);
      addWindowListener(new WindowAdapter() {
          public void windowClosing(WindowEvent e) { dispose(); }
          public void windowClosed(WindowEvent e) {
            for(JFrame d : dependents) d.dispose();
            if(DatabaseEditor.this.dependents.remove(Table.this)) {
            } else {
              System.out.println("Can't find#4: " + Table.this.getTitle());
            }
          }
        });
      setTitle(tableName);
      getContentPane().add(new DBTable(rs));
      JLabel tableLabel = new JLabel("<html>Table:");
      JTextField tableField = new JTextField("temp", 10);
      JTextField fileField = new JTextField("data.txt", 10);
      JTextArea columnArea = new UndoableTextArea(",");
      JTextArea rowArea = new UndoableTextArea("\n");
      JTextArea nullArea = new UndoableTextArea("null");
      JCheckBox checkBox = new JCheckBox("<html><font size=-2>Escape Strings</font>");
      JButton writeButton = new JButton("<html>Write File:") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  TableCellEditor te = table.getCellEditor();
                  if(te != null) te.stopCellEditing();
                  outputTable(fileField.getText(),
                              columnArea.getText(),
                              rowArea.getText(),
                              nullArea.getText(),
                              checkBox.getModel().isSelected());
                }
              });
          }
        };
      JButton browseButton = new JButton("<html><font size=-2>Browse</font>") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  File file = chooseFile("set", new String[][]{});
                  if(file == null) return;
                  String fileName = file.getPath();
                  if(fileName != null) fileField.setText(fileName);
                }
              });
          }
        };
      JLabel columnLabel = new JLabel("<html>Column Separator:");
      JScrollPane columnScrollPane
        = new JScrollPane(columnArea,
                          ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED,
                          ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);
      columnScrollPane.setPreferredSize(new Dimension(100, 60));
      JLabel rowLabel = new JLabel("<html>Row End:");
      JScrollPane rowScrollPane
        = new JScrollPane(rowArea,
                          ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED,
                          ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);
      rowScrollPane.setPreferredSize(new Dimension(100, 60));
      JLabel nullLabel = new JLabel("<html>null:");
      JScrollPane nullScrollPane
        = new JScrollPane(nullArea,
                          ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED,
                          ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);
      nullScrollPane.setPreferredSize(new Dimension(80, 60));

      Box panel = new Box(BoxLayout.X_AXIS);

      Box filePanel = new Box(BoxLayout.Y_AXIS);
      panel.add(Box.createHorizontalStrut(5));

      filePanel.add(writeButton);
      filePanel.add(fileField);
      Box browseEscapePanel = new Box(BoxLayout.X_AXIS);
      browseEscapePanel.add(browseButton);
      browseEscapePanel.add(checkBox);
      browseEscapePanel.setAlignmentX(0.0f);
      filePanel.add(browseEscapePanel);
      panel.add(filePanel);

      panel.add(Box.createHorizontalStrut(5));

      Box columnPanel = new Box(BoxLayout.Y_AXIS);
      columnPanel.add(columnLabel);
      columnPanel.add(columnScrollPane);
      columnPanel.add(Box.createVerticalGlue());
      panel.add(columnPanel);

      panel.add(Box.createHorizontalStrut(5));

      Box rowPanel = new Box(BoxLayout.Y_AXIS);
      rowPanel.add(rowLabel);
      rowPanel.add(rowScrollPane);
      rowPanel.add(Box.createVerticalGlue());
      panel.add(rowPanel);

      panel.add(Box.createHorizontalStrut(5));

      Box nullPanel = new Box(BoxLayout.Y_AXIS);
      nullPanel.add(nullLabel);
      nullPanel.add(nullScrollPane);
      nullPanel.add(Box.createVerticalGlue());
      nullPanel.setMaximumSize(nullPanel.getPreferredSize());
      panel.add(nullPanel);

      panel.add(Box.createHorizontalGlue());

      getContentPane().add(panel, BorderLayout.SOUTH);
      setLocationByPlatform(true);
    }

    /**
     * Output a result set to a text file separating rows by a new
     * line and cells in a row by the separator String.
     *
     * @param fileName the name of the file to write
     * @param column the cell separator
     * @param row the row cell separator
     * @param nil the String used for a null entry
     * @param escape true if replace ' with ''
     */
    void outputTable(String fileName, String column, String row, String nil, boolean escape) {
      FileOutputStream out = null;
      try {
        out = new FileOutputStream(fileName);
        for(ArrayList<String> a : data) {
          for(int i = 0; i < a.size(); i++) {
            if(i != 0) out.write(column.getBytes(fileCharset));
            String s = a.get(i); // .getBytes(fileCharset);
            if(s == null) {
              out.write(nil.getBytes(fileCharset));
            } else {
              out.write((escape ? escapeString(s, '\'') : s).getBytes(fileCharset));
            }
          }
          out.write(row.getBytes(fileCharset));
        }
      } catch(IOException e) {
        e.printStackTrace();
      } finally {
        close(out);
      }
    }

    /**
     * Delete all of the rows that are selected. Then reloads the
     * table from the database. Currently only a single cell can be
     * selected so this method deletes a single row. Note that this
     * method throws away all pending edits that have not been
     * committed.
     */
    void deleteRows() {
      Statement stat = null;
      try {
        stat = conn.createStatement();
        for(int i : table.getSelectedRows()) {
          int count = stat.executeUpdate("delete from \""
                                         + tableName.replace(".", "\".\"")
                                         + "\" where (_rowid_ = " + rowid.get(i)
                                         + ");");
          // count = 1 for both tables and views ???
          tableModel.fireTableRowsDeleted(i, i); // is this needed?
        }
        initTables(null); // update table from database
        tableModel.fireTableDataChanged();
      } catch(SQLException ex) {
        ex.printStackTrace();
      } finally {
        try{
          if(stat != null) stat.close();
        } catch(SQLException closeex) {
          closeex.printStackTrace();
        }
      }
    }

    /**
     * This method sets the selected cell to null
     */
    void makeNull() {
      int i = table.getSelectedRow();
      int j = table.convertColumnIndexToModel(table.getSelectedColumn());
      if(i < 0 || j < 0) return;
      ArrayList<String> row = data.get(i);
      row.set(j, null);
      tableModel.fireTableCellUpdated(i, j);
    }

    /**
     * This method undoes all of the uncommitted edits in the selected
     * row. Currently only one cell/row can be selected.
     */
    void revertSelected() {
      int r[] = table.getSelectedRows();
      for(int i : r) {
        ArrayList<String> row = data.get(i);
        ArrayList<String> oldrow = oldData.get(i);
        for(int j = 0; j < row.size(); j++) {
          String old = oldrow.get(j);
          if(!eql(old, row.get(j))) {
            row.set(j, old);
            tableModel.fireTableCellUpdated(i, j);
          }
        }
      }
    }

    /**
     * This method undoes all of the uncommitted edits in the entire
     * table.
     */
    void revertTable() {
      for(int i = 0; i < data.size(); i++) {
        ArrayList<String> row = data.get(i);
        ArrayList<String> oldrow = oldData.get(i);
        for(int j = 0; j < row.size(); j++) {
          String old = oldrow.get(j);
          if(!eql(old, row.get(j))) {
            row.set(j, old);
            tableModel.fireTableCellUpdated(i, j);
          }
        }
      }
    }

    /**
     * This method adds a new row to the table. All cells in this new
     * row are set to null. If at least one entry is not set non-null
     * then this row will not be added to the table. To add an all
     * null row first add a row with a cell not null and then edit
     * this cell to null (by dragging a null cell value to it).
     */
    void addRow() {
      int oldSize = data.size();
      data.add(new ArrayList<String>(Collections.nCopies(heading.size(),
                                                      (String)null)));
      oldData.add(new ArrayList<String>(Collections.nCopies(heading.size(),
                                                         (String)null)));
      rowid.add(null);
      tableModel.fireTableRowsInserted(oldSize, data.size() - 1);
      table.scrollRectToVisible(table.getCellRect(oldSize, -1, true));
    }

    /**
     * This method fills in all cells from cell[i, j] to the end of
     * the table stopping at the first nonnull entry
     *
     * @param i the row number of the first cell to be filled
     * @param j the column number of the first cell to be filled
     */
    void fillColumn(int i, int j) {
      String s = data.get(i - 1).get(j);
      int size = data.size();
      while(i < size && data.get(i).get(j) == null) {
        data.get(i).set(j, s);
        tableModel.fireTableCellUpdated(i, j);
        ++i;
      }
    }

    /**
     * This method fills in all cells from cell[i, j] to the end of
     * the table stopping at the first nonnull entry. It requires that
     * s1 and s2 are Strings of equal length and differ in only one
     * character. The differing character is continually incremented
     * by the difference between the two differing characters and the
     * resulting patched String isinserted in successive cells. This
     * makes the successive cells in arithmetic progression. No checks
     * are made for generating illegal characters.
     *
     * @param i the row number of the first cell to be filled
     * @param j the column number of the first cell to be filled
     * @param s1 the first string in sequence
     * @param s2 the second string in sequence
     */
    void fillColumn(int i, int j, String s1, String s2) {
      int length = s1.length();
      if(length != s2.length()) return;
      int k;
      for(k = 0; k < length; ++k) {
        if(s1.charAt(k) != s2.charAt(k)) break;
      }
      if(k == length) {
        fillColumn(i + 1, j);
        return;
      }
      char c1 = s1.charAt(k);
      char c2 = s2.charAt(k);
      int cdiff = c2 - c1;
      for(int kk = k + 1; kk < length; ++kk) {
        if(s1.charAt(kk) != s2.charAt(kk)) return;
      }
      // OK now do it
      String front = s1.substring(0, k);
      String back = s1.substring(k + 1);
      char cx = c2;

      int size = data.size();
      while(i < size && data.get(i).get(j) == null) {
        cx = (char)(cx + cdiff);
        String s = front + cx + back;
        data.get(i).set(j, s);
        tableModel.fireTableCellUpdated(i, j);
        ++i;
      }
    }

    /**
     * This method updates all cells in the selected row.
     */
    void updateSelected() {
      int r[] = table.getSelectedRows();
      for(int i : r) {
        updateRow(i);
        tableModel.fireTableRowsUpdated(i, i);
      }
    }

    /**
     * This method updates all cells in the entire table.
     */
    void updateTable() {
      for(int i = 0; i < data.size(); i++) {
        updateRow(i);
      }
      initTables(null);
      tableModel.fireTableDataChanged();
    }

    /**
     * New strategy is to use ? instead of putting new values in the
     * statement itself. There are row.size() ? marks and the same
     * number of putWithCharset(PreparedStatement stat, int i, String
     * data) calls. The advantage of the new way is that the character
     * encoding can be changed easily (and from the command line)
     * although this is currently disables because of the difficulty
     * of using blobs. Also avoids sql injection attacks.
     *
     * This method updates the specified row
     *
     * @param i the row number to update
     */
    void updateRow(int i) {
      PreparedStatement stat = null;
      try {
        // rowid = null is insert
        ArrayList<String> row = data.get(i);
        ArrayList<String> oldRow = oldData.get(i);
        String id = rowid.get(i);
        if(id == null) { // add row
          String colNames = "";
          String colValues = "";
          for(int j = 0; j < row.size(); j++) {
            String old = oldRow.get(j);
            if(!eql(old, row.get(j))) {
              String h = '"' + heading.get(j) + '"';
              colNames += (colNames == "" ? h : "," + h); //////// fails on group as a column name - must use (')s around row name
              // "cast(? as text)"
              colValues += (colValues == "" ? "?" : ",?");
            }
          }
          if(!colNames.equals("")) {
            stat = conn.prepareStatement("insert into \""
                                         + tableName.replace(".", "\".\"")
                                         + "\"(" + colNames
                                         + ") values (" + colValues + ");");
            int jj = 1;
            for(int j = 0; j < row.size(); j++) {
              if(!eql(oldRow.get(j), row.get(j))) {
                putWithCharset(stat, jj++, row.get(j));
              }
            }
            int count = stat.executeUpdate();
            //count = 1 for normal table and count = 0 for view update
            for(int j = 0; j < row.size(); j++) {
              oldRow.set(j, row.get(j));
            }
          }
        } else { // update row
          for(int j = 0; j < row.size(); j++) {
            String newData = row.get(j);
            if(!eql(oldRow.get(j), newData)) {
              // "cast(? as text)"
              stat = conn.prepareStatement("update \""
                                           + tableName.replace(".", "\".\"")
                                           + "\" set " + heading.get(j)
                                           + " = ? where _rowid_ = "
                                           + rowid.get(i)
                                           + ";");
              putWithCharset(stat, 1, row.get(j));
              int count = stat.executeUpdate();
              //count = 1 for normal table and count = 0 for view update
              oldRow.set(j, newData);
            }
          }
        }
      } catch(SQLException ex) {
        ex.printStackTrace();
      } finally {
        try {
          if (stat != null) stat.close();
        } catch(SQLException closeex) {
          closeex.printStackTrace();
        }
      }
      tableModel.fireTableRowsUpdated(i, i);
    }
  } // class Table extends JFrame

  /**
   * Show the table with the given name
   *
   * @param tableName the table to display
   */
  void showTable(String tableName) {
    Table frame;
    search: {
      for(JFrame jf : dependents) {
        if(jf.getTitle().equals(tableName)) {
          frame = (Table)jf;
          frame.setExtendedState(JFrame.NORMAL);
          break search;
        }
      }
      frame = new Table(tableName);
      frame.pack();
      frame.setVisible(true);
    }
    frame.setVisible(true);
    return;
  }

  File editFile = new File(".", "temp.txt"); // default edit text file

  /**
   * Make a command window for executing sql statements. The Execute
   * Selection button will execute the command in the window or the
   * command in the current selection if the current selection is not
   * empty.
   *
   * The Execute Selection button executes the current selection as an
   * SQL command. If there is no selection the entire contents of the
   * window is executed.
   *
   * The Select Next Command button selects the sql command following
   * the current selection.
   *
   * The Break Lines in Selection button breaks long line in the
   * current selection.
   *
   * The Print button prints the edit window to a printer.
   *
   * The Read File button reads the selected file and replaces the
   * window contents with the contents of the file. This operation is
   * undoable.
   *
   * The Write File button writes the window contents to the selected
   * file.
   *
   * @param title the title of the window
   * @param contents the initial contents of the command window
   */
  void makeCommandWindow(String title, String contents) {
    EditWindow commandWindow = new EditWindow(title, contents, DatabaseEditor.this);
    dependents.add(commandWindow); // to database table window

    JTextArea textArea = commandWindow.textArea;
    JPanel inner = new JPanel(new BorderLayout());
    JPanel btns = new JPanel(new GridLayout(4,1));
    JLabel status = new JLabel("(status line)");
    btns.add(new JButton("Execute Selection") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                Statement stat = null;
                try {
                  String s = textArea.getSelectedText();
                  if(s == null) s = textArea.getText();
                  stat = conn.createStatement();
                  stat.execute(s);
                  int count = stat.getUpdateCount();
                  if(count >= 0) {
                    status.setText("Update count = " + count);
                  } else {
                    ResultSet rs = stat.getResultSet();
                    if(rs != null) {
                      status.setText("Result Set Generated");
                      JFrame frame = new Table(rs, "SQL Command Result");
                      frame.pack();
                      frame.setVisible(true);
                    }
                  }
                } catch(SQLException ex) {
                  status.setText(ex.toString());
                } finally {
                  try {
                    if(stat != null) stat.close();
                  } catch(SQLException closeex) {
                    closeex.printStackTrace();
                  }
                }
                textArea.grabFocus();
              }
            });
        }
      });
    btns.add(new JButton("Select Next Command") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                String s = textArea.getText();
                int end = textArea.getSelectionEnd();
                int statementEnd = getEndOfSqlStatement(s, end);
                textArea.select(end, statementEnd);
                Caret caret = textArea.getCaret();
                caret.setVisible(true);
                caret.setSelectionVisible(true);
              }
            });
        }
      });
    btns.add(new JButton("Break Lines in Selection") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                commandWindow.breakLines(70);
              }
            });
        }
      });
    btns.add(new JButton("Print") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                commandWindow.print("Command Window");
              }
            });
        }
      });
    btns.add(new JButton("Read File") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                File file = chooseFile("Read",
                                       new String[][]{{"*.txt", "txt"}});
                if(file != null) {
                  editFile = file;
                  String d = file.getParent();
                  if(d != null) currentDirectory = d;
                  String s = readFileIntoString(file);
                  if(s != null) {
                    textArea.setText(s);
                    textArea.setCaretPosition(0);
                    textArea.grabFocus();
                    commandWindow.setTitle("SQL Command Window - " + file);
                  }
                }
                textArea.setCaretPosition(0);
                textArea.grabFocus();
              }
            });
        }
      });
    btns.add(new JButton("Write File") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                File file = chooseFile("Write",
                                       new String[][]{{"*.txt", "txt"}});
                if(file != null) {
                  editFile = file;
                  String d = file.getParent();
                  if(d != null) currentDirectory = d;
                  OutputStream out = null;
                  try {
                    out = new FileOutputStream(editFile);
                    out.write(textArea.getText().getBytes(fileCharset));
                  } catch(IOException ex) {
                    ex.printStackTrace();
                  } finally {
                    close(out);
                  }
                }
                textArea.grabFocus();
              }
            });
        }
      });
    inner.add(btns, BorderLayout.NORTH);
    inner.add(status, BorderLayout.SOUTH);
    commandWindow.getContentPane().add(inner, BorderLayout.SOUTH);
    commandWindow.setLocationByPlatform(true);
    commandWindow.pack();
    commandWindow.setVisible(true);
  }

  /**
   * This class provides a search and replace function for table
   * entries. It can do global replaces or confine its activities to a
   * single field, table, or entry.
   *
   * Layout:
   *
   * (((Table |XXX| Field |XXX| _rowid_ |XXX|)))
   *
   * |   Find   | |XXXXX|   |  Replace   | |XXXXX|
   * |Find Funny| |XXXXX|   |Replace/Find| |XXXXX|
   *
   * Find: select next occurance after current selection
   * Find Funny: find the next "funny" character
   * Replace: replace current selection with replacement string
   * Replace/Find: do a Replace followed by a Find
   */
  class FindReplace extends JFrame {
    final static long serialVersionUID = 42;

    JTextArea textArea;
    JPanel statusLine = new JPanel(new BorderLayout());
    JLabel status = new JLabel("Status area");
    JTextArea findField = new UndoableTextArea(4, 15);
    JTextArea replaceField = new UndoableTextArea(4, 15);
    /**
     * Make a find/replace window operating on the given textArea.
     *
     * @param editWindow the EditWindow to operate on
     * @param ignoreCase true if case is ignored in comparisons
     */
    FindReplace(EditWindow editWindow, boolean ignoreCase) {
      this.textArea = editWindow.textArea;
      setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE); // currently no dependents to close
      Rectangle r = editWindow.getBounds();
      setLocation(r.x + r.width, r.y);
      setTitle("Find - Replace");
      JPanel args2 = new JPanel();
      JPanel btns1 = new JPanel(new GridLayout(2, 1));
      JCheckBox checkBox = new JCheckBox("Ignore Case", ignoreCase);
      btns1.add(new JButton("Find:") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  status.setText("");
                  String text = textArea.getText();
                  String find = findField.getText();
                  int end = textArea.getSelectionEnd();
                  int location = findIgnoreCase(checkBox.isSelected(), text, find, end);
                  if(location < 0) {
                    status.setText("not found");
                    textArea.select(0, 0);
                  } else {
                    textArea.select(location, location + find.length());
                    editWindow.toFront();
                  }
                }
              });
          }
        });
      btns1.add(new JButton("Find Funny") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  status.setText("");
                  String text = textArea.getText();
                  int end = textArea.getSelectionEnd();
                  for(int i = end; i < text.length(); i++) {
                    char c = text.charAt(i);
                    if(c != 0xa && (c < 32 || c > 126)) {
                      status.setText("Hex Character = "
                                     + Integer.toHexString(c));
                      textArea.select(i, i + 1);
                      editWindow.toFront();
                      return;
                    }
                  }
                  status.setText("not found");
                  textArea.select(0, 0);
                }
              });
          }
        });
      args2.add(btns1);
      JScrollPane findScrollPane = new JScrollPane(findField);
      args2.add(findScrollPane);
      args2.add(new JLabel("      "));
      JPanel btns2 = new JPanel(new GridLayout(2, 1));
      btns2.add(new JButton("Replace:") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  status.setText("");
                  String replace = replaceField.getText();
                  textArea.replaceSelection(replace);
                  int end = textArea.getSelectionEnd();
                  textArea.select(end - replace.length(), end);
                }
              });
          }
        });
      args2.add(btns2);
      btns2.add(new JButton("Replace/Find:") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  status.setText("");
                  String replace = replaceField.getText();
                  textArea.replaceSelection(replace);
                  int end = textArea.getSelectionEnd();
                  textArea.select(end - replace.length(), end);
                  String text = textArea.getText();
                  String find = findField.getText();
                  int location = findIgnoreCase(checkBox.isSelected(), text, find, end);
                  if(location < 0) {
                    status.setText("not found");
                    textArea.select(0, 0);
                  } else {
                    textArea.select(location, location + find.length());
                    editWindow.toFront();
                  }
                }
              });
          }
        });
      args2.add(btns2);
      JScrollPane replaceScrollPane = new JScrollPane(replaceField);
      args2.add(replaceScrollPane);
      getContentPane().add(args2, BorderLayout.CENTER);
      statusLine.add(status, BorderLayout.CENTER);
      statusLine.add(checkBox, BorderLayout.EAST);
      getContentPane().add(statusLine, BorderLayout.SOUTH);
    }
  } // class FindReplace extends JFrame

  /**
   * This class searches many cells in a table column.
   * This class provides a search and replace function for table
   * entries. It can do global replaces or confine its activities to a
   * single field, table, or entry.
   *
   * Layout:
   *
   * Table |XXX| Field |XXX|
   *
   * |   Find   | |XXXXX|   | To Hex | |XXXXX|
   * |          | |XXXXX|   |        | |XXXXX|
   * |Find Funny| |XXXXX|   |From Hex| |XXXXX|
   *
   * Find: select next occurance after current selection
   * Find Funny: find the next "funny" character
   * To Hex: replace contents with hex representation
   * Replace/Find: replace hex representation with text
   *
   * Current state is [table, row, column, textArea position]
   * or
   * [table selection, textArea position]
   *
   * Maybe just bring up table, selection, edit window on found instances
   */
  class Search extends JFrame {
    final static long serialVersionUID = 42;

    Table table; // the table to search
    JTextField tableField = new JTextField(10);
    DefaultComboBoxModel<String> model = new DefaultComboBoxModel<String>();
    JComboBox<String> columnField = new JComboBox<String>(model);
    JCheckBox checkBox = new JCheckBox("Ignore Case", true);
    JTextArea findField = new UndoableTextArea(4, 15);
    JTextArea replaceField = new UndoableTextArea(4, 15);
    JLabel status = new JLabel("Status area");

    /**
     * Make a find/replace window operating on the given textArea.
     *
     * @param table the table to operate on
     */
    Search(Table table) {
      this.table = table;
      setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE); // currently no dependents
      setTitle("Searching table: " + table.getTitle());
      JPanel args1 = new JPanel();
      args1.add(new JLabel("Table:"));
      args1.add(tableField);
      tableField.setEditable(false);
      args1.add(new JLabel("    Column:"));
      args1.add(columnField);
      for(int i = 0; i < table.heading.size(); i++) {
        model.addElement(table.heading.get(i));
      }
      args1.add(checkBox);
      getContentPane().add(args1, BorderLayout.NORTH);
      JPanel args2 = new JPanel();
      JPanel btns1 = new JPanel(new GridLayout(2, 1));
      btns1.add(new JButton("Find:") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  Table table = Search.this.table;
                  status.setText("");
                  String find = findField.getText();
                  String replace = replaceField.getText();
                  int column
                    = table.heading.indexOf(columnField.getSelectedItem());
                  if(column < 0 || column >= table.heading.size()) {
                    status.setText("Illegal column specification");
                    return; // illegal column
                  }
                  boolean ignoreCase = checkBox.isSelected();
                  for(int row = table.table.getSelectedRow() + 1;
                      row < table.data.size();
                      ++row) {
                    String cell = table.data.get(row).get(column);
                    String value = table.data.get(row).get(column);
                    if(findIgnoreCase(ignoreCase, value, find, 0) >= 0) {
                      int col = table.table.convertColumnIndexToView(column);
                      Rectangle r = Search.this.getBounds();
                      // bring up an editor window
                      EditWindow editWindow = table.makeCellEditWindow(table, row, col, r);
                      FindReplace findReplace = new FindReplace(editWindow, ignoreCase);
                      editWindow.dependents.add(findReplace);
                      findReplace.findField.setText(find);
                      findReplace.replaceField.setText(replace);
                      findReplace.pack();
                      findReplace.setVisible(true);
                      return;
                    }
                  }
                  status.setText("not found");
                  table.table.clearSelection();
                }
              });
          }
        });
      btns1.add(new JButton("Find Funny") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  Table table = Search.this.table;
                  status.setText("");
                  String find = findField.getText();
                  String replace = replaceField.getText();
                  int column
                    = table.heading.indexOf(columnField.getSelectedItem());
                  if(column < 0 || column >= table.heading.size()) {
                    status.setText("Illegal column specification");
                    return; // illegal column
                  }
                  for(int row = table.table.getSelectedRow() + 1;
                      row < table.data.size();
                      ++row) {
                    String cell = table.data.get(row).get(column);
                    int location = -1; // initialize to not found
                    String value = table.data.get(row).get(column);
                    if(value != null) {
                      for(int i = 0; i < value.length(); i++) {
                        char c = value.charAt(i);
                        if(c != 0xa && (c < 32 || c > 126)) {
                          status.setText("Hex Character = "
                                         + Integer.toHexString(c));
                          location = 0;
                          break;
                        }
                      }
                    }
                    if(location >= 0) {
                      int col = table.table.convertColumnIndexToView(column);
                      // bring up an editor window
                      Rectangle r = Search.this.getBounds();
                      EditWindow editWindow = table.makeCellEditWindow(table, row, col, r);
                      FindReplace findReplace = new FindReplace(editWindow, true);
                      editWindow.dependents.add(findReplace);
                      findReplace.findField.setText(find);
                      findReplace.replaceField.setText(replace);
                      findReplace.pack();
                      findReplace.setVisible(true);
                      return;
                    }
                  }
                  status.setText("not found");
                  table.table.clearSelection();
                }
              });
          }
        });
      args2.add(btns1);
      JScrollPane findScrollPane = new JScrollPane(findField);
      args2.add(findScrollPane);
      args2.add(new JLabel("      "));
      JPanel btns2 = new JPanel(new GridLayout(2, 1));
      btns2.add(new JButton("To Hex:") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  String replace = replaceField.getText();
                  StringBuilder hex = new StringBuilder();
                  for(int i = 0; i < replace.length(); i++) {
                    if(hex.length() != 0) hex.append('|');
                    hex.append(Integer.toHexString(replace.charAt(i)));
                  }
                  replaceField.setText(hex.toString());
                }
              });
          }
        });
      args2.add(btns2);
      btns2.add(new JButton("From Hex:") {
          final static long serialVersionUID = 42;

          {
            addActionListener(new ActionListener() {
                public void actionPerformed(ActionEvent e) {
                  String replace = replaceField.getText();
                  StringBuilder hex = new StringBuilder();
                  int charValue = 0;
                  boolean haveValue = false;
                  for(int i = 0; i < replace.length(); i++) {
                    char c = replace.charAt(i);
                    if(c >= '0' && c <= '9') {
                      haveValue = true;
                      charValue = 16*charValue + c - '0';
                    } else if(c >= 'A' && c <= 'F') {
                      haveValue = true;
                      charValue = 16*charValue + c - 'A' + 10;
                    } else if(c >= 'a' && c <= 'f') {
                      haveValue = true;
                      charValue = 16*charValue + c - 'a' + 10;
                    } else {
                      if(haveValue) {
                        hex.append((char)charValue);
                        charValue = 0;
                        haveValue = false;
                      }
                    }
                  }
                  if(haveValue) {
                    hex.append((char)charValue);
                    haveValue = false;
                  }
                  replaceField.setText(hex.toString());
                }
              });
          }
        });
      args2.add(btns2);
      JScrollPane replaceScrollPane = new JScrollPane(replaceField);
      args2.add(replaceScrollPane);
      getContentPane().add(args2, BorderLayout.CENTER);
      getContentPane().add(status, BorderLayout.SOUTH);
    }
  } // class Search extends JFrame

  /**
   * This class is identical to a JTextArea but also has undo / redo
   * capability.
   *
   * cntrl-Z is undo
   * cntrl-Y is redo
   */
  class UndoableTextArea extends JTextArea {
    final static long serialVersionUID = 42;

    UndoableTextArea() {
      super();
    }

    UndoableTextArea(Document doc) {
      super(doc);
    }

    UndoableTextArea(Document doc, String text, int rows, int columns) {
      super(doc, text, rows, columns);
    }

    UndoableTextArea(int rows, int columns) {
      super(rows, columns);
    }

    UndoableTextArea(String text) {
      super(text);
    }

    UndoableTextArea(String text, int rows, int columns) {
      super(text, rows, columns);
    }

    {
      UndoManager undoManager = new UndoManager();
      undoManager.setLimit(-1); // unlimited undos
      Document doc = getDocument();
      doc.addUndoableEditListener(new UndoableEditListener() {
          //@Override
          public void undoableEditHappened(UndoableEditEvent e) {
            undoManager.addEdit(e.getEdit());
          }
        });

      InputMap im = getInputMap(JComponent.WHEN_FOCUSED);
      ActionMap am = getActionMap();

      Toolkit toolkit = Toolkit.getDefaultToolkit();

      im.put(KeyStroke.getKeyStroke(KeyEvent.VK_Z,
                                    toolkit.getMenuShortcutKeyMask()),
             "Undo");
      im.put(KeyStroke.getKeyStroke(KeyEvent.VK_Y,
                                    toolkit.getMenuShortcutKeyMask()),
             "Redo");

      am.put("Undo", new AbstractAction() {
          final static long serialVersionUID = 42;
          //@Override
          public void actionPerformed(ActionEvent e) {
            try {
              if (undoManager.canUndo()) {
                undoManager.undo();
              }
            } catch (CannotUndoException exp) {
              exp.printStackTrace();
            }
          }
        });
      am.put("Redo", new AbstractAction() {
          final static long serialVersionUID = 42;
         //@Override
          public void actionPerformed(ActionEvent e) {
            try {
              if (undoManager.canRedo()) {
                undoManager.redo();
              }
            } catch (CannotUndoException exp) {
              exp.printStackTrace();
            }
          }
        });
    }
  } // class UndoableTextArea extends JTextArea

  // Here are classes and methods used to dump the database. Note that
  // the method println is not used as this method outputs a
  // platform-dependent end-of-line character.

  /**
   * Abstract class to operate on rows of a table.
   */
  abstract static class RowProc {
    /**
     * Method called on each row of a table resulting from a query.
     *
     * @param row the row of the table resulting from a query
     */
    abstract void doRow(ArrayList<String> row);
  } // abstract class RowProc

  /**
   * Queries a database and calls a method on each row of the result set.
   *
   * @param query the query (select statement) to execute
   * @param rowProc a class encapsulating the method to run on each row
   */
  void queryTable(String query, RowProc rowProc) {
    ArrayList<String> row = new ArrayList<String>();
    Statement stat = null;
    try {
      stat = conn.createStatement();
      stat.execute(query);
      int count = stat.getUpdateCount();
      if(count >= 0) {
        System.err.println("No table result");
        System.err.println(query);
        return;
      }
      ResultSet rs = stat.getResultSet();
      ResultSetMetaData md = rs.getMetaData();
      int tablecolumns = md.getColumnCount();
      ArrayList<Integer> columnIndex = new ArrayList<Integer>();
      while(rs.next()) {
        ArrayList<String> data = new ArrayList<String>();
        for(int i = 1; i <= tablecolumns; i++) {
          data.add(getWithCharset(rs, i));
        }
        rowProc.doRow(data);
      }
    } catch(SQLException e) {
      System.err.println("SQL problem in queryTable function:");
      System.err.println(query);
      e.printStackTrace();
      return;
      //throw e;
    } finally {
      try {
        if(stat != null) stat.close();
      } catch(SQLException e) {
        e.printStackTrace();
      }
    }
  }

  /**
   * Run query and output result followed by a ';' for each
   * row. Assume only one column in the result.
   *
   * @param out the OutputStream to be used for output
   * @param query the query string
   */
  void runTableDumpQuery(OutputStream out, String query) {
    queryTable(query, new RowProc() {
        void doRow(ArrayList<String> row) {
          try {
          if(row.size() != 1) System.err.println("### error number of columns in runTableDumpQuery");
          String s = row.get(0);
          if (s == null) out.write("null".getBytes(fileCharset));
          else out.write(s.getBytes(fileCharset));
          if(s.indexOf("--") >= 0) out.write("\n;\n".getBytes(fileCharset));
          else out.write(";\n".getBytes(fileCharset));
          } catch(IOException e) {
            e.printStackTrace();
          }
        }
      });
  }

  /**
   * Dump table etc. in the main database.
   *
   * @param out the OutputStream to be used for output
   * @param query the query string
   */
  void runSchemaDumpQuery(OutputStream out, String query) {
    queryTable(query, new RowProc() {
        void doRow(ArrayList<String> row) {
          try {
            String table = row.get(0);
            String type = row.get(1);
            String sql = row.get(2);
            if("sqlite_sequence".equals(table)) {
              out.write("DELETE FROM sqlite_sequence;\n".getBytes(fileCharset));
            } else if(table.length() == 12 && table.substring(0,11).equals("sqlite_stat")) {
              out.write("ANALYZE sqlite_master;\n".getBytes(fileCharset));
            } else if(table.length() >= 7 && table.substring(0,7).equals("sqlite_")) {
              return;
            } else if(table.length() >= 20 && table.substring(0,20).equals("CREATE VIRTUAL TABLE")) {
              // not handled
              return;
            } else {
              out.write((sql + ";\n").getBytes(fileCharset));
            }
            if(type.equals("table")) {
              StringBuilder names = new StringBuilder();
              queryTable("PRAGMA table_info(" + escapeString(table, '"') + ");",
                         new RowProc() {
                           void doRow(ArrayList<String> row) {
                             if(names.length() != 0) names.append("||','||");
                             names.append("quote(");
                             names.append(escapeString(row.get(1), '"'));
                             names.append(")");
                           }
                         });
              String query = "SELECT 'INSERT INTO ' || "
                + escapeString(table, '"')
                + " || ' VALUES(' || "
                + names
                + "|| ')' FROM  "
                + escapeString(table, '"');
              runTableDumpQuery(out, query);
            }
          } catch(IOException e) {
            e.printStackTrace();
          }
          return;
        }
      });
  }

  File backupFile = new File(".", "database.txt"); // default backup and restore text file

  /**
   * Dump the main database
   *
   * @param out the OutputStream to be used for output
   */
  void dumpDatabase(OutputStream out) {
    try{
      out.write("PRAGMA foreign_keys=OFF;\n".getBytes(fileCharset));
      out.write("BEGIN TRANSACTION;\n".getBytes(fileCharset));
      // "SAVEPOINT dump; PRAGMA writable_schema=ON"
      runSchemaDumpQuery(out,
                         "SELECT name, type, sql FROM sqlite_master " +
                         "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'");
      runSchemaDumpQuery(out,
                         "SELECT name, type, sql FROM sqlite_master " +
                         "WHERE name=='sqlite_sequence'");
      runTableDumpQuery(out,
                        "SELECT sql FROM sqlite_master " +
                        "WHERE sql NOT NULL AND type IN ('index','trigger','view')");
      out.write("COMMIT;\n".getBytes(fileCharset));
    } catch(IOException e) {
      e.printStackTrace();
    }
  }

  static boolean IdChar(char c) {
    if('A' <= c && c <= 'Z') return true;
    if('a' <= c && c <= 'z') return true;
    if('0' <= c && c <= '9') return true;
    if(c == '_') return true;
    return false;
  }

  /**
   * Returns the line number of a character position in a string
   *
   * @param s the string to search
   * @param charPos the position in the string
   * @return the line number (one-based) of the character position
   */
  static int lineNumber(String s, int charPos) {
    int count = 0;
    for(int i = 0; i < charPos; i++) {
      if(s.charAt(i) == '\n') count++;
    }
    return count;
  }

  /**
   * Check to see if the String query is at position in String s. This comparison ignores case.
   *
   * @param s the base String to check
   * @param position character position in s to check
   * @param query the string to match
   * @return true if String query is in s at position
   */
  static boolean stringCompare(String s, int position, String query) {
    if(s == null) return false; // may not be needed
    int sLength = s.length();
    int qLength = query.length();
    if(position + qLength > sLength) return false;
    return s.substring(position, position + qLength).compareToIgnoreCase(query) == 0;
  }

  // State machine for partially parsing SQL statements.
  static final int trans[][] = new int[][]{
                /* Token:                                                */
    /* State:    **  SEMI  WS  OTHER  EXPLAIN  CREATE  TEMP  TRIGGER  END */
    /* 0 INVALID: */ { 1,  0,     2,       3,      4,    2,       2,   2, },
    /* 1   START: */ { 1,  1,     2,       3,      4,    2,       2,   2, },
    /* 2  NORMAL: */ { 1,  2,     2,       2,      2,    2,       2,   2, },
    /* 3 EXPLAIN: */ { 1,  3,     3,       2,      4,    2,       2,   2, },
    /* 4  CREATE: */ { 1,  4,     2,       2,      2,    4,       5,   2, },
    /* 5 TRIGGER: */ { 6,  5,     5,       5,      5,    5,       5,   5, },
    /* 6    SEMI: */ { 6,  6,     5,       5,      5,    5,       5,   7, },
    /* 7     END: */ { 1,  7,     5,       5,      5,    5,       5,   5, },
  };

  /**
   * Find the end of an SQL staement starting at character position
   * start. Only works for valid SQL statements. Note that the
   * discovered SQL statement may not be valid for illegal SQL
   * statements. The complexity is because of the syntax of CREATE
   * TRIGGER statements. This code was taken from the sqlite database
   * C code.
   *
   * This implementation uses a state machine with 8 states:
   *
   *   (0) INVALID We have not yet seen a non-whitespace character.
   *
   *   (1) START At the beginning or end of an SQL statement.  This
   *   routine returns if it ends in the START state or reaches the
   *   end of the string.
   *
   *   (2) NORMAL We are in the middle of statement which ends with a
   *   single semicolon.
   *
   *   (3) EXPLAIN The keyword EXPLAIN has been seen at the beginning
   *   of a statement.
   *
   *   (4) CREATE The keyword CREATE has been seen at the beginning of
   *   a statement, possibly preceded by EXPLAIN and/or followed by
   *   TEMP or TEMPORARY
   *
   *   (5) TRIGGER We are in the middle of a trigger definition that
   *   must be ended by a semicolon, the keyword END, and another
   *   semicolon.
   *
   *   (6) SEMI We've seen the first semicolon in the ";END;" that
   *   occurs at the end of a trigger definition.
   *
   *   (7) END We've seen the ";END" of the ";END;" that occurs at the
   *   end of a trigger definition.
   *
   * @param s the String of SQL statements
   * @param start the character position of the beginning of the SQL statement
   * @return the character position one beyond the end of the discovered statement
   */
  int getEndOfSqlStatement(String s, int start) {
    final int tkSEMI = 0;    //  A semicolon.
    final int tkWS = 1;      //  Whitespace.
    final int tkOTHER = 2;   //  Any other SQL token.
    final int tkEXPLAIN = 3; //  The "explain" keyword.
    final int tkCREATE = 4;  //  The "create" keyword.
    final int tkTEMP = 5;    //  The "temp" or "temporary" keyword.
    final int tkTRIGGER = 6; //  The "trigger" keyword.
    final int tkEND = 7;     //  The "end" keyword.

    int sLength = s.length();
    int state = 0;   /* Current state, using numbers defined in header comment */
    int token;       /* Value of the next token */
    int p = start;
    while(state != 1 && p < sLength) {
      loop: switch( s.charAt(p) ) {
      case ';': {  /* A semicolon */
        token = tkSEMI;
        break;
      }
      case ' ':
      case '\r':
      case '\t':
      case '\n':
      case '\f': {  /* White space is ignored */
        token = tkWS;
        break;
      }
      case '/': {   /* C-style comments */
        if( p + 1 >= sLength || s.charAt( p + 1 ) !='*' ) {
          token = tkOTHER;
          break loop;
        }
        p += 2;
        while( p < sLength && !stringCompare(s, p, "*/") ) p++;
        if(p < sLength) p++;
        token = tkWS;
        break loop;
      }
      case '-': {   /* SQL-style comments from "--" to end of line */
        if( p + 1 < sLength && s.charAt(p + 1)!='-' ) {
          token = tkOTHER;
          break loop;
        }
        while( p < sLength && s.charAt(p) != '\n' ) { p++; }
        token = tkWS;
        break loop;
      }
      case '[': {   /* Microsoft-style identifiers in [...] */
        p++;
        while( p < sLength && s.charAt(p) != ']' ) { p++; }
        token = tkOTHER;
        break loop;
      }
      case '`':     /* Grave-accent quoted symbols used by MySQL */
      case '"':     /* single- and double-quoted strings */
      case '\'': {
        char c = s.charAt(p);
        p++;
        while( p < sLength && s.charAt(p)!=c ) { p++; }
        token = tkOTHER;
        break loop;
      }
      default: {
        if( IdChar(s.charAt(p)) ) {
          /* Keywords and unquoted identifiers */
          int nId;
          for(nId=1;
              p + nId < sLength && IdChar(s.charAt(p + nId));
              nId++) {}
          inner: switch( s.charAt(p) ) {
          case 'c': case 'C': {
            if( nId==6 && stringCompare(s, p, "create") ) {
              token = tkCREATE;
            }else{
              token = tkOTHER;
            }
            break inner;
          }
          case 't': case 'T': {
            if( nId==7 && stringCompare(s, p, "trigger") ) {
              token = tkTRIGGER;
            }else if( nId==4 && stringCompare(s, p, "temp") ) {
              token = tkTEMP;
            }else if( nId==9 && stringCompare(s, p, "temporary") ) {
              token = tkTEMP;
            }else{
              token = tkOTHER;
            }
            break inner;
          }
          case 'e':  case 'E': {
            if( nId==3 && stringCompare(s, p, "end") ) {
              token = tkEND;
            }else
              if( nId==7 && stringCompare(s, p, "explain") ) {
                token = tkEXPLAIN;
              }else {
                token = tkOTHER;
              }
            break inner;
          }
          default: {
            token = tkOTHER;
            break inner;
          }
          }
          p += nId-1;
        }else{
          /* Operators and special symbols */
          token = tkOTHER;
        }
        break loop;
      }
      }
      state = trans[state][token];
      p++;
    }
    return p;
  }

  static String currentDirectory = ".";

  /**
   * Bring up a file browser to choose a file.
   *
   * Second argument is a String[][] where each array is {desc, ext, ext, ext}.
   * this matches the arguments to Class FileNameExtensionFilter
   *
   * @param approve the label in the approve button
   * @param filters a description of the filters to be used
   * @return the name of the chosen file or null if no selection
   */
  static File chooseFile(String approve, String[]...filters) {
    File file;
    JFileChooser fc = new JFileChooser(currentDirectory);
    fc.setFileHidingEnabled(false);
    fc.setApproveButtonText(approve);
    for(String[] filter : filters) {
      String[] extensions = Arrays.copyOfRange(filter, 1, filter.length);
      fc.addChoosableFileFilter(new FileNameExtensionFilter(filter[0],
                                                            extensions));
    }
    int status = fc.showDialog(null, approve);
    if (status == JFileChooser.APPROVE_OPTION) {
      file = fc.getSelectedFile();
    } else {
      file = null;
    }
    if(file != null) {
      String d = file.getParent();
      if(d != null) currentDirectory = d;
    }
    return file;
  }

  /**
   * This class is a pure data structure.
   */
  static class DatabaseTableEntry implements Comparable<DatabaseTableEntry> {
    String dbName;
    String tableName;
    String type;
    boolean ambiguous = false;

    /**
     * Create a DatabaseTableEntry.
     *
     * @param dbName the database name
     * @param tableName the name of the table in the database
     * @param type table or view or index or trigger etc.
     */
    DatabaseTableEntry(String dbName, String tableName, String type) {
      this.dbName = dbName;
      this.tableName = tableName;
      this.type = type;
    }

    public int compareTo(DatabaseTableEntry other) {
      int c = tableName.compareTo(other.tableName);
      if(c != 0) return c;
      ambiguous = other.ambiguous = true;
      c = dbName.compareTo(other.dbName);
      return c;
    }

    String fullName() {
      return dbName + '.' + tableName;
    }

    public String toString() {
      if(!ambiguous) return tableName;
      return dbName + '.' + tableName;
    }
  } // static class DatabaseTableEntry implements Comparable<DatabaseTableEntry>

  ArrayList<DatabaseTableEntry> tables;
  JList<Object> list;
  boolean hidden;

  /**
   * Bring up a database editor
   *
   * @param db the name of the database to display
   */
  static void makeTableList(String db) {
    if(db == null) {
      File file = chooseFile("Open",
                             new String[][]{{"*.db *.sqlite", "db", "sqlite"}});
      if(file == null) return;
      db = file.getPath();
    }
    if(db == null) return;
    try {
      Connection conn = DriverManager.getConnection("jdbc:sqlite:" + db);
      DatabaseEditor frame = new DatabaseEditor(conn, db, false);
      frame.pack();
      frame.setVisible(true);
    } catch(SQLException ex) {
      ex.printStackTrace();
    }
  }

  /**
   * Create a database editor
   *
   * @param conn the database connection
   * @param db the name of the database
   * @param hidden true if showing hidden tables (always called with false)
   */
  DatabaseEditor(Connection conn, String db, boolean hidden) {
    this.hidden = hidden;
    this.conn = conn;
    addWindowListener(new WindowAdapter() {
        public void windowClosing(WindowEvent e) {DatabaseEditor.this.dispose(); }
        public void windowClosed(WindowEvent e) {
          for(JFrame d : dependents) d.dispose();
          try {
            conn.close();
          } catch(SQLException ex) {
            System.err.println("Exception on closing top window");
            ex.printStackTrace(); ///////////// ???????????
          }
        }
      });
    setLocationByPlatform(true);
    setTitle(db);
    initList(hidden);
    list = new JList<Object>(tables.toArray()) {
        final static long serialVersionUID = 42;

        public String getToolTipText(MouseEvent e) {
          int index = locationToIndex(e.getPoint());
          if (index > -1) {
            DatabaseTableEntry item
              = (DatabaseTableEntry)getModel().getElementAt(index);
            return item.dbName + '.' + item.tableName;
          }
          return null;
        }
      };
    list.setCellRenderer(new DefaultListCellRenderer() {
        final static long serialVersionUID = 42;

        public Component getListCellRendererComponent(JList<?> list,
                                                      Object value,
                                                      int index,
                                                      boolean isSelected,
                                                      boolean cellHasFocus) {
          DatabaseTableEntry entry = (DatabaseTableEntry)value;
          JLabel label = new JLabel(entry.toString());
          label.setOpaque(true);
          // 0xffffc0 is light yellow
          if(entry.type.equals("view")) label.setBackground(new Color(0xffffc0));
          return label;
        }
      });
    JScrollPane scrollPane = new JScrollPane(list);
    scrollPane.setPreferredSize(new Dimension(200, 600));
    getContentPane().add(scrollPane);
    list.addMouseListener(new MouseAdapter() {
        public void mouseClicked(MouseEvent e) {
          if(e.getButton() == 1) {
            int i = list.locationToIndex(e.getPoint());
            if(i >= 0) {
              showTable(tables.get(i).fullName());
            }
          } else if(e.getButton() == 3) {
            int i = list.locationToIndex(e.getPoint());
            if(i >= 0) {
              String tableName = tables.get(i).toString();
              Table table = new Table(tableName);
              table.pack();
              table.setVisible(true);
              Search search = new Search(table);
              table.dependents.add(search);
              Rectangle r = table.getBounds();
              search.setLocation(r.x + r.width, r.y);
              search.tableField.setText(tableName);
              search.pack();
              search.setVisible(true);
            }
          }
        }
      });

    JPanel buttons = new JPanel(new GridLayout(6, 1));

    buttons.add(new JButton("Refresh") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                initList(DatabaseEditor.this.hidden);
                list.setListData(tables.toArray());
              }
            });
        }
      });
    buttons.add(new JButton("Show Hidden Tables") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                DatabaseEditor.this.hidden = !DatabaseEditor.this.hidden;
                setText(DatabaseEditor.this.hidden
                        ? "Hide Hidden Tables"
                        : "Show Hidden Tables");
                initList(DatabaseEditor.this.hidden);
                list.setListData(tables.toArray());
              }
            });
        }
      });
    buttons.add(new JButton("Open Command Window") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                makeCommandWindow("SQL Command Window", "");
              }
            });
        }
      });
    buttons.add(new JButton("Open Database") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                makeTableList(null);
              }
            });
        }
      });
    buttons.add(new JButton("Read Database") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                File file = chooseFile("Read", new String[][]{});
                if(file != null) {
                  backupFile = file;
                  String d = file.getParent();
                  if(d != null) currentDirectory = d;
                  String fileString = readFileIntoString(backupFile);
                  if(fileString != null) {
                    int start = 0;
                    int end = 0;
                    Statement stat = null;
                    try {
                      do {
                        end = getEndOfSqlStatement(fileString, start);
                        stat = conn.createStatement();
                        stat.execute(fileString.substring(start, end));
                        int count = stat.getUpdateCount();
                        if(count >= 0) {
                          //status.setText("Update count = " + count);
                        } else {
                          ResultSet rs = stat.getResultSet();
                          if(rs != null) {
                            //status.setText("Result Set Generated");
                            JFrame frame = new Table(rs, "SQL Command Result");
                            frame.pack();
                            frame.setVisible(true);
                          }
                        }
                        stat.close();
                        start = end;
                      } while(start + 3 < fileString.length());
                      System.out.println("Done!");
                    } catch(SQLException ex) {
                      int startLine = lineNumber(fileString, start);
                      int endLine = lineNumber(fileString, end);
                      System.err.println("error at lines " + startLine + " to " + endLine + ".");
                      System.err.println(fileString.substring(start, end));
                      System.err.println(ex.getMessage());
                    } finally {
                      try{
                        if(stat != null) stat.close();
                      } catch(SQLException closeex) {
                        closeex.printStackTrace();
                      }
                    }
                  }
                }
              }
            });
        }
      });
    buttons.add(new JButton("Write Database") {
        final static long serialVersionUID = 42;

        {
          addActionListener(new ActionListener() {
              public void actionPerformed(ActionEvent e) {
                File file = chooseFile("Write", new String[][]{});
                if(file != null) {
                  backupFile = file;
                  String d = file.getParent();
                  if(d != null) currentDirectory = d;
                  OutputStream out = null;
                  try {
                    out = new FileOutputStream(backupFile);
                    dumpDatabase(out);
                  } catch(IOException ex) {
                    ex.printStackTrace();
                  } finally {
                    close(out);
                  }
                }
              }
            });
        }
      });
    getContentPane().add(buttons, BorderLayout.SOUTH);
  }

  /**
   * Using the sqlite_master and sqlite_temp_master tables construct a
   * list of all tables in the attached databases.
   *
   * Currently assume table and database names do not contain "." or
   * use "." in db.name
   *
   * Note that the sqlite_master and sqlite_temp_master tables are not
   * in the list of tables so they have to be added separately.
   *
   * @param hidden true if listing hidden tables
   */
  void initList(boolean hidden) {
    tables = new ArrayList<DatabaseTableEntry>();
    try {
      Statement dbstat = conn.createStatement();
      ResultSet dbrs = dbstat.executeQuery("pragma database_list;");
      ResultSetMetaData dbmd = dbrs.getMetaData();
      while(dbrs.next()) {
        String database = dbrs.getString(2);
        String master = database.equals("temp") ? "sqlite_temp_master"
          : "sqlite_master";
        if(hidden) {
          tables.add(new DatabaseTableEntry(database, master, "table"));
        }
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from \""
                                         + database + "\".\"" + master
                                         + "\";");
        ResultSetMetaData md = rs.getMetaData();
        while (rs.next()) {
          String type = rs.getString(1);
          if(type.equals("table") || type.equals("view")) {
            String tableName = rs.getString(2);
            if(hidden || tableName.indexOf("sqlite_") != 0) {
              tables.add(new DatabaseTableEntry(database, tableName, type));
            }
          }
        }
        stat.close();
      }
      dbstat.close();
      Collections.sort(tables);
    } catch(SQLException ex) {
      System.err.println("Editor initialization failed for " + ex);
      ex.printStackTrace();
    }
  }

  /**
   * View or edit a sqlite database. Note that there are two character
   * sets - one for the database and one for the file system. They
   * both default to UTF-8.
   *
   * Usage: java DatabaseEditor &lt;dbname&gt; [&lt;dbCharset&gt; [&lt;File System charset&gt;]]
   *
   * @param args The command line arguments.
   */
  public static void main(String[] args) {
    try {
      Class.forName("org.sqlite.JDBC");
    } catch(ClassNotFoundException ex) {
      ex.printStackTrace();
    }
    if(args.length > 3) {
      System.err.println("Usage: java DatabaseEditor <dbname> [<dbCharset> [<File System charset]]");
      return;
    }
    if(args.length > 2) {
      fileCharset = Charset.availableCharsets().get(args[1]);
      if(fileCharset == null) {
        System.err.println("Unknown charset: " + args[1]);
        System.err.println();
        System.err.println("Available charsets are: " +
                           Charset.availableCharsets().keySet());
        System.err.println();
        System.err.println("Default charset is: " + Charset.defaultCharset());
        return;
      }
    }

    if(args.length > 1) {
      dbCharset = Charset.availableCharsets().get(args[1]);
      if(dbCharset == null) {
        System.err.println("Unknown charset: " + args[1]);
        System.err.println();
        System.err.println("Available charsets are: " +
                           Charset.availableCharsets().keySet());
        System.err.println();
        System.err.println("Default charset is: " + Charset.defaultCharset());
        return;
      }
    }
    String db = args.length > 0 ? args[0] : null;
    SwingUtilities.invokeLater(new Runnable() {
        public void run() {
          makeTableList(db);
        }
      });
  }
} // class DatabaseEditor extends JFrame