sqlshell-svn

[Sqlshell-svn] /devel/src: main/java/nl/improved/sqlclient main/java/nl/improved/sqlclient/commands test/java/nl/improved/sqlclient/commands

[SVN by r. <sv...@ca...>]

Author: roy
Date: 2009-09-26 14:51:06 +0200 (Sat, 26 Sep 2009)
New Revision: 422

Added:
   src/main/java/nl/improved/sqlclient/commands/DumpCommand.java
   src/main/java/nl/improved/sqlclient/commands/ReadCommand.java
   src/main/java/nl/improved/sqlclient/commands/ReadDumpCommand.java
   src/test/java/nl/improved/sqlclient/commands/DumpCommandTest.java
Modified:
   src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java
Log:
unit test for dump command
added support for dump as <filename>
fixed 'where clause' in dump


Modified: src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java
===================================================================
--- src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java	2009-09-22 18:54:54 UTC (rev 421)
+++ src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java	2009-09-26 12:51:06 UTC (rev 422)
@@ -21,21 +21,14 @@
 import java.io.FileReader;
 import java.io.FileWriter;
 import java.io.IOException;
-import java.io.InputStream;
 import java.io.InputStreamReader;
 import java.io.PrintStream;
 import java.io.PrintWriter;
 import java.io.StringWriter;
 import java.io.Writer;
 import java.sql.Connection;
-import java.sql.Date;
-import java.sql.PreparedStatement;
 import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
-import java.sql.Statement;
-import java.sql.Types;
-import java.text.SimpleDateFormat;
 import java.util.Arrays;
 import java.util.ArrayList;
 import java.util.Iterator;
@@ -47,14 +40,6 @@
 import java.util.concurrent.TimeUnit;
 import java.util.logging.Level;
 import java.util.logging.Logger;
-import javax.xml.parsers.DocumentBuilder;
-import javax.xml.parsers.DocumentBuilderFactory;
-import javax.xml.transform.OutputKeys;
-import javax.xml.transform.Transformer;
-import javax.xml.transform.TransformerConfigurationException;
-import javax.xml.transform.sax.SAXTransformerFactory;
-import javax.xml.transform.sax.TransformerHandler;
-import javax.xml.transform.stream.StreamResult;
 import nl.improved.sqlclient.DBConnector.ConnectionSettings;
 import nl.improved.sqlclient.commands.*;
 import nl.improved.sqlclient.history.HistoryPersister;
@@ -63,14 +48,6 @@
 //import nl.improved.sqlclient.util.Function;
 import nl.improved.sqlclient.util.LimitedArrayList;
 //import nl.improved.sqlclient.util.oracle.Functions;
-import org.w3c.dom.Document;
-import org.w3c.dom.Element;
-import org.w3c.dom.Node;
-import org.w3c.dom.NodeList;
-import org.xml.sax.SAXException;
-import org.xml.sax.helpers.AttributesImpl;
-import sun.misc.BASE64Decoder;
-import sun.misc.BASE64Encoder;
 
 /**
  * The SQLShell abstract main class.
@@ -1597,7 +1574,7 @@
      * @param fileName the filename to convert
      * @return the converted filename
      */
-    private static String toFileName(String fileName) {
+    public static String toFileName(String fileName) {
         if (fileName.startsWith("~/")) {
             return System.getProperty("user.home")+fileName.substring(1);
         }
@@ -1610,7 +1587,7 @@
      * @param otherMatches an optional list of other matches that are not filenames
      * @return the tabcompletion information found based on the filename prefix and other matches (optional)
      */
-    private static TabCompletionInfo getFileNameTabCompletionInfo(String fileNamePrefix, String... otherMatches) {
+    public static TabCompletionInfo getFileNameTabCompletionInfo(String fileNamePrefix, String... otherMatches) {
         String dirName;
         if (fileNamePrefix.equals("")) {
             fileNamePrefix = ".";
@@ -1768,330 +1745,10 @@
         }
         @Override
         public boolean backgroundProcessSupported() {
-            return false;
+            return true; // must be placed at the end of the background command list
         }
     }
-    private abstract class ReadDumpCommand implements Command {
-        public static final String DATE_FORMAT = "yyyyMMddHHmmss";
 
-        boolean isBinary(int columnType) {
-            if (columnType == Types.BINARY ||
-                    columnType == Types.BLOB ||
-                    columnType == Types.CLOB ||
-                    //columnType == Types.LONGNVARCHAR || // jdk 1.6
-                    columnType == Types.LONGVARBINARY ||
-                    columnType == Types.LONGVARCHAR ||
-                    //columnType == Types.NCLOB || // jdk 1.6
-                    columnType == Types.OTHER) {
-                return true;
-            }
-            return false;
-
-        }
-    }
-    /**
-     * Writes the result of a query into a dump file so that it can be read back.
-     */
-    private class DumpCommand extends ReadDumpCommand {
-        private String fileName;
-
-        @Override
-        public CommandResult execute(SQLCommand cmd) {
-            String command = cmd.getCommandString();
-            String nextPart = command.substring("dump".length()).trim();
-            String dumpFileName;
-            if (nextPart.indexOf(' ') > 0) {
-                dumpFileName = nextPart.substring(0, nextPart.indexOf(' '));
-            } else {
-                dumpFileName = nextPart;
-            }
-            int rowCount = 0;
-            PrintWriter out = null;
-            try {
-                File f;
-                if (dumpFileName.toLowerCase().endsWith(".dmp")) {
-                    f = new File(toFileName(dumpFileName));
-                } else {
-                    f = new File(toFileName(dumpFileName +".dmp"));
-                }
-                fileName = f.getAbsolutePath();
-                if ((f.exists() && !f.canWrite()) || (!f.exists() && !f.createNewFile())) {
-                    throw new IllegalStateException("Failed to create spool to file: '"+fileName+"'");
-                }
-                out = new PrintWriter(new FileWriter(fileName));
-                StreamResult streamResult = new StreamResult(out);
-                SAXTransformerFactory tf = (SAXTransformerFactory) SAXTransformerFactory.newInstance();
-                // SAX2.0 ContentHandler.
-                TransformerHandler hd = tf.newTransformerHandler();
-                Transformer serializer = hd.getTransformer();
-                serializer.setOutputProperty(OutputKeys.ENCODING,"ISO-8859-1");
-                //serializer.setOutputProperty(OutputKeys.INDENT,"no");
-                hd.setResult(streamResult);
-                hd.startDocument();
-                AttributesImpl atts = new AttributesImpl();
-                atts.addAttribute("", "", "tablename", "", dumpFileName);
-                // USERS tag.
-                hd.startElement("","","dump",atts);
-
-                String query = "select * from " + nextPart;
-                Connection c = DBConnector.getInstance().getConnection();
-                Statement stmt = c.createStatement();
-                ResultSet rs = stmt.executeQuery(query);
-                SimpleDateFormat formatter = new SimpleDateFormat(DATE_FORMAT);
-                while (rs.next()) {
-                    atts.clear();
-                    hd.startElement("","","row",atts);
-                    ResultSetMetaData metaData = rs.getMetaData();
-                    for (int col = 1; col <= metaData.getColumnCount(); col++) {
-                        atts.addAttribute("","","name","",metaData.getColumnName(col));
-                        if (metaData.getColumnType(col) == Types.DATE || metaData.getColumnType(col) == Types.TIMESTAMP) {
-                            atts.addAttribute("","","type","","date");
-                            atts.addAttribute("","","type_name","",metaData.getColumnTypeName(col));
-                            hd.startElement("","","col",atts);
-                            Date date = rs.getDate(col);
-                            if (date != null) {
-                                String dateString = formatter.format(date);
-                                hd.characters(dateString.toCharArray(), 0, dateString.length());
-                            }
-                        } else if (isBinary(metaData.getColumnType(col))) {
-                            atts.addAttribute("","","type","","binary");
-                            atts.addAttribute("","","type_name","",metaData.getColumnTypeName(col));
-                            hd.startElement("","","col",atts);
-
-                            BASE64Encoder enc = new BASE64Encoder();
-                            int bytesSize = 128;
-                            byte[] bytes = new byte[bytesSize];
-                            int read;
-                            InputStream valueStream = rs.getBinaryStream(col);
-                            if (valueStream != null) {
-                                while ( (read = valueStream.read(bytes)) != -1) {
-                                    if (read == 0) {
-                                        continue;
-                                    }
-                                    if (read != bytes.length) {
-                                        bytes = Arrays.copyOf(bytes, read);
-                                    }
-                                    String stringValue = enc.encode(bytes) +"\n";
-                                    hd.characters(stringValue.toCharArray(), 0, stringValue.length());
-                                    if (bytes.length != bytesSize) {
-                                        bytes = new byte[bytesSize];
-                                    }
-                                }
-                            }
-                        } else {
-                            atts.addAttribute("","","type","",Integer.toString(metaData.getColumnType(col)));
-                            atts.addAttribute("","","type_name","",metaData.getColumnTypeName(col));
-                            hd.startElement("","","col",atts);
-                            String value= rs.getString(col);
-                            if (value != null) {
-                                hd.characters(value.toCharArray(), 0, value.length());
-                            }
-                        }
-                        hd.endElement("","","col");
-                    }
-                    hd.endElement("","","row");
-                    rowCount++;
-                }
-                hd.endElement("","","dump");
-                hd.endDocument();
-            } catch (SAXException ex) {
-                Logger.getLogger(AbstractSQLShellWindow.class.getName()).log(Level.SEVERE, null, ex);
-            } catch (TransformerConfigurationException e) {
-                throw new IllegalStateException("Failed to create xml handler: " + e.toString(), e);
-            } catch (SQLException e) {
-                throw new IllegalStateException("Failed to execute query for dump("+fileName+"): " + e.toString(), e);
-            } catch (IOException e) {
-                throw new IllegalStateException("Failed to create dump ("+fileName+"): " + e.toString(), e);
-            } finally {
-                if (out != null) {
-                    out.close();
-                }
-            }
-            return new SimpleCommandResult(true, "Dump to "+fileName+" done. ("+ rowCount+" rows written)");
-        }
-
-        @Override
-        public CharSequence getCommandString() {
-            return "dump";
-        }
-
-        /**
-         * Returns some tab completion info for the specified command.
-         * @param commandInfo the command lines
-         * @param commandPoint the cursor position
-         * @return some tab completion info for the specified command.
-         */
-        @Override
-        public TabCompletionInfo getTabCompletionInfo(SQLCommand command, Point commandPoint) {
-            List<String> commandInfo = new ArrayList<String>(2);
-            commandInfo.add("SELECT * FROM");
-            commandInfo.add(command.getCommandString().substring("dump ".length()));
-            debug(commandInfo.toString());
-            Point point = new Point(commandPoint.x - "dump ".length(), commandPoint.y+1);
-            return SQLUtil.getTabCompletionInfo(commandInfo, point);
-        }
-
-        @Override
-        public CharSequence getHelp() {
-            return "tablename [where clause]\n" +
-                    "For example: dump users where role='manager';\n\n" +
-                    "See 'read' for options to read the dump file back into the table";
-        }
-        @Override
-        public boolean abort() {
-            return false;// not implemented
-        }
-        @Override
-        public boolean backgroundProcessSupported() {
-            return false;
-        }
-    }
-    /**
-     * Read the result of a dump file.
-     */
-    private class ReadCommand extends ReadDumpCommand {
-        private String fileName;
-
-        @Override
-        public CommandResult execute(SQLCommand cmd) {
-            String command = cmd.getCommandString();
-            String nextPart = command.substring("read".length()).trim();
-            String dumpFileName;
-            if (nextPart.indexOf(' ') > 0) {
-                dumpFileName = nextPart.substring(0, nextPart.indexOf(' '));
-            } else {
-                dumpFileName = nextPart;
-            }
-            int rowCount = 0;
-            try {
-                File f = new File(toFileName(dumpFileName +".dmp"));
-                fileName = f.getAbsolutePath();
-                if (!f.exists() && !f.canRead()) {
-                    throw new IllegalStateException("Failed to read dump file: '"+fileName+"'");
-                }
-                // Step 1: create a DocumentBuilderFactory
-                DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
-                
-                // Step 2: create a DocumentBuilder
-                DocumentBuilder db = dbf.newDocumentBuilder();
-                
-                // Step 3: parse the input file to get a Document object
-                Document doc = db.parse(new File(fileName));
-
-                Element documentElement = doc.getDocumentElement();
-                String tableName = documentElement.getAttribute("tablename");
-                NodeList nodeList = documentElement.getElementsByTagName("row");
-
-                String query = "insert into " + tableName +" (";
-                String values = ") values (";
-                Element n = (Element) nodeList.item(0); // row
-                NodeList cols = n.getElementsByTagName("col");
-                for (int colNr = 0; colNr < cols.getLength(); colNr++) {
-                    query += cols.item(colNr).getAttributes().getNamedItem("name").getNodeValue();
-                    values  += "?";
-                    if (colNr +1 < cols.getLength()) {
-                        query +=", ";
-                        values +=", ";
-                    }
-                }
-                query = query + values +")";
-                Connection c = DBConnector.getInstance().getConnection();
-                PreparedStatement pstmt = c.prepareStatement(query);
-                SimpleDateFormat formatter = new SimpleDateFormat(DATE_FORMAT);
-                for (int nodeNr = 0; nodeNr < nodeList.getLength(); nodeNr++) {
-                    Element row = (Element) nodeList.item(nodeNr); // row
-                    NodeList columns = row.getElementsByTagName("col");
-                    for (int colNr = 0; colNr < columns.getLength(); colNr++) {
-                        Element column = (Element) columns.item(colNr);
-                        Node type = column.getAttributes().getNamedItem("type");
-                        if (type != null) {
-                            String typeString = type.getNodeValue();
-                            if (typeString.equals("date")) {
-                                String nodeValue = column.getTextContent();
-                                if (nodeValue == null || nodeValue.equals("")) {
-                                    pstmt.setDate(colNr+1, null);
-                                } else {
-                                    pstmt.setDate(colNr+1, new Date(formatter.parse(nodeValue).getTime()));
-                                }
-                            } else if (typeString.equals("binary")) { // (isBinary(metaData.getColumnType(col)))
-                                String nodeValue = column.getTextContent();
-                                BASE64Decoder decoder = new BASE64Decoder();
-                                byte[] value = decoder.decodeBuffer(nodeValue);
-                                //pstmt.setBinaryStream(colNr+1, new ByteArrayInputStream(value));
-                                pstmt.setBytes(colNr+1, value);
-                            } else {
-                                String nodeValue = column.getTextContent();
-                                int iType = Integer.parseInt(typeString);
-                                switch (iType) {
-                                    case Types.INTEGER :
-                                    case Types.SMALLINT :
-                                    case Types.BIGINT :
-                                        pstmt.setInt(colNr+1, Integer.parseInt(nodeValue));
-                                        break;
-                                    case Types.DOUBLE : pstmt.setDouble(colNr+1, Double.parseDouble(nodeValue));
-                                        break;
-                                    case Types.VARCHAR :
-                                    case Types.CHAR :
-                                    case Types.LONGNVARCHAR :
-                                    case Types.NCHAR :
-                                    case Types.NVARCHAR :
-                                        pstmt.setString(colNr+1, nodeValue);
-                                        break;
-                                    default:
-                                        debug("WARNING Unhandled type: "+ typeString +" trying to fallback to String");
-                                        pstmt.setString(colNr+1, nodeValue);
-                                }
-                            }
-                        } else {
-                            //debug(nodeNr +" ? "+column.getTextContent());
-                            pstmt.setString(colNr+1, column.getTextContent());
-                        }
-                    }
-                    pstmt.executeUpdate();
-                    rowCount++;
-                }
-            } catch (SQLException e) {
-                throw new IllegalStateException("Failed to execute update query for dump("+fileName+"): " + e.toString(), e);
-            } catch (IOException e) {
-                throw new IllegalStateException("Failed to read dump ("+fileName+"): " + e.toString(), e);
-            } catch (Exception e) {
-                throw new IllegalStateException("Failed to read dump ("+fileName+"): " + e.toString(), e);
-            }
-            return new SimpleCommandResult(true, "Read from "+fileName+" done. (" + rowCount+" rows imported)");
-        }
-
-        @Override
-        public CharSequence getCommandString() {
-            return "read";
-        }
-
-        /**
-         * Returns some tab completion info for the specified command.
-         * @param commandInfo the command lines
-         * @param commandPoint the cursor position
-         * @return some tab completion info for the specified command.
-         */
-        @Override
-        public TabCompletionInfo getTabCompletionInfo(SQLCommand command, Point commandPoint) {
-            String fn = command.getCommandString().substring("read".length()).trim();
-            return getFileNameTabCompletionInfo(fn);
-        }
-
-        @Override
-        public CharSequence getHelp() {
-            return "filename: read dump file from filename\n"+
-                    "See 'dump' for options to create a dump file";
-        }
-        @Override
-        public boolean abort() {
-            return false;// not implemented
-        }
-        @Override
-        public boolean backgroundProcessSupported() {
-            return false;
-        }
-    }
-
     public static class ExecuteBatchCommand implements Command {
         private boolean cancelled;
         private Command currentCommand;

Added: src/main/java/nl/improved/sqlclient/commands/DumpCommand.java
===================================================================
--- src/main/java/nl/improved/sqlclient/commands/DumpCommand.java	2009-09-22 18:54:54 UTC (rev 421)
+++ src/main/java/nl/improved/sqlclient/commands/DumpCommand.java	2009-09-26 12:51:06 UTC (rev 422)
@@ -0,0 +1,219 @@
+package nl.improved.sqlclient.commands;
+
+import nl.improved.sqlclient.*;
+import java.io.File;
+import java.io.FileWriter;
+import java.io.IOException;
+import java.io.InputStream;
+import java.io.PrintWriter;
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Types;
+import java.text.SimpleDateFormat;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+import java.util.logging.Level;
+import java.util.logging.Logger;
+import javax.xml.transform.OutputKeys;
+import javax.xml.transform.Transformer;
+import javax.xml.transform.TransformerConfigurationException;
+import javax.xml.transform.sax.SAXTransformerFactory;
+import javax.xml.transform.sax.TransformerHandler;
+import javax.xml.transform.stream.StreamResult;
+import org.xml.sax.SAXException;
+import org.xml.sax.helpers.AttributesImpl;
+import sun.misc.BASE64Encoder;
+
+/**
+ * Writes the result of a query into a dump file so that it can be read back.
+ */
+public class DumpCommand extends ReadDumpCommand {
+
+    private String fileName;
+
+    // 0 filename
+    // 1 tablename
+    // 2 query
+    static String[] getParsedStrings(SQLCommand cmd) {
+        String[] result = new String[3];
+        String command = cmd.getCommandString();
+        String commandUpperCase = command.toUpperCase();
+        if (!commandUpperCase.startsWith("DUMP")) {
+            return result;
+        }
+        String nextPart;
+        String tableName;
+        String dumpFileName;
+        if (commandUpperCase.matches("DUMP AS [A-Z]+.*\\s+[A-Z]*.*")) {
+            int start = commandUpperCase.indexOf(' ',"DUMP AS ".length() +1);
+            dumpFileName = command.substring("DUMP AS".length(), start).trim();
+            nextPart = command.substring(start).trim();
+        } else {
+            nextPart = command.substring("dump".length()).trim();
+            dumpFileName = null;
+        }
+        if (nextPart.indexOf(' ') > 0) {
+            tableName = nextPart.substring(0, nextPart.indexOf(' ')).trim();
+        } else {
+            tableName = nextPart;
+        }
+        if (dumpFileName == null) {
+            dumpFileName = tableName;
+        }
+        result[0] = dumpFileName;
+        result[1] = tableName;
+        result[2] = nextPart.substring(nextPart.indexOf(tableName));
+        return result;
+    }
+
+    @Override
+    public CommandResult execute(SQLCommand cmd) {
+        String[] result = getParsedStrings(cmd);
+        String dumpFileName = result[0];
+        String tableName = result[1];
+        String query = "select * from " + result[2];
+        int rowCount = 0;
+        PrintWriter out = null;
+        try {
+            File f;
+            if (dumpFileName.toLowerCase().endsWith(".dmp")) {
+                f = new File(AbstractSQLShellWindow.toFileName(dumpFileName));
+            } else {
+                f = new File(AbstractSQLShellWindow.toFileName(dumpFileName + ".dmp"));
+            }
+            fileName = f.getAbsolutePath();
+            if ((f.exists() && !f.canWrite()) || (!f.exists() && !f.createNewFile())) {
+                throw new IllegalStateException("Failed to create spool to file: \'" + fileName + "\'");
+            }
+            out = new PrintWriter(new FileWriter(fileName));
+            StreamResult streamResult = new StreamResult(out);
+            SAXTransformerFactory tf = (SAXTransformerFactory) SAXTransformerFactory.newInstance();
+            // SAX2.0 ContentHandler.
+            TransformerHandler hd = tf.newTransformerHandler();
+            Transformer serializer = hd.getTransformer();
+            serializer.setOutputProperty(OutputKeys.ENCODING, "ISO-8859-1");
+            //serializer.setOutputProperty(OutputKeys.INDENT,"no");
+            hd.setResult(streamResult);
+            hd.startDocument();
+            AttributesImpl atts = new AttributesImpl();
+            atts.addAttribute("", "", "tablename", "", tableName);
+            // USERS tag.
+            hd.startElement("", "", "dump", atts);
+            Connection c = DBConnector.getInstance().getConnection();
+            Statement stmt = c.createStatement();
+            ResultSet rs = stmt.executeQuery(query);
+            SimpleDateFormat formatter = new SimpleDateFormat(DATE_FORMAT);
+            while (rs.next()) {
+                atts.clear();
+                hd.startElement("", "", "row", atts);
+                ResultSetMetaData metaData = rs.getMetaData();
+                for (int col = 1; col <= metaData.getColumnCount(); col++) {
+                    atts.addAttribute("", "", "name", "", metaData.getColumnName(col));
+                    if (metaData.getColumnType(col) == Types.DATE || metaData.getColumnType(col) == Types.TIMESTAMP) {
+                        atts.addAttribute("", "", "type", "", "date");
+                        atts.addAttribute("", "", "type_name", "", metaData.getColumnTypeName(col));
+                        hd.startElement("", "", "col", atts);
+                        Date date = rs.getDate(col);
+                        if (date != null) {
+                            String dateString = formatter.format(date);
+                            hd.characters(dateString.toCharArray(), 0, dateString.length());
+                        }
+                    } else if (isBinary(metaData.getColumnType(col))) {
+                        atts.addAttribute("", "", "type", "", "binary");
+                        atts.addAttribute("", "", "type_name", "", metaData.getColumnTypeName(col));
+                        hd.startElement("", "", "col", atts);
+                        BASE64Encoder enc = new BASE64Encoder();
+                        int bytesSize = 128;
+                        byte[] bytes = new byte[bytesSize];
+                        int read;
+                        InputStream valueStream = rs.getBinaryStream(col);
+                        if (valueStream != null) {
+                            while ((read = valueStream.read(bytes)) != -1) {
+                                if (read == 0) {
+                                    continue;
+                                }
+                                if (read != bytes.length) {
+                                    bytes = Arrays.copyOf(bytes, read);
+                                }
+                                String stringValue = enc.encode(bytes) + "\n";
+                                hd.characters(stringValue.toCharArray(), 0, stringValue.length());
+                                if (bytes.length != bytesSize) {
+                                    bytes = new byte[bytesSize];
+                                }
+                            }
+                        }
+                    } else {
+                        atts.addAttribute("", "", "type", "", Integer.toString(metaData.getColumnType(col)));
+                        atts.addAttribute("", "", "type_name", "", metaData.getColumnTypeName(col));
+                        hd.startElement("", "", "col", atts);
+                        String value = rs.getString(col);
+                        if (value != null) {
+                            hd.characters(value.toCharArray(), 0, value.length());
+                        }
+                    }
+                    hd.endElement("", "", "col");
+                }
+                hd.endElement("", "", "row");
+                rowCount++;
+            }
+            hd.endElement("", "", "dump");
+            hd.endDocument();
+        } catch (SAXException ex) {
+            Logger.getLogger(AbstractSQLShellWindow.class.getName()).log(Level.SEVERE, null, ex);
+        } catch (TransformerConfigurationException e) {
+            throw new IllegalStateException("Failed to create xml handler: " + e.toString(), e);
+        } catch (SQLException e) {
+            throw new IllegalStateException("Failed to execute query for dump(" + fileName + "): " + e.toString(), e);
+        } catch (IOException e) {
+            throw new IllegalStateException("Failed to create dump (" + fileName + "): " + e.toString(), e);
+        } finally {
+            if (out != null) {
+                out.close();
+            }
+        }
+        return new SimpleCommandResult(true, "Dump to " + fileName + " done. (" + rowCount + " rows written)");
+    }
+
+    @Override
+    public CharSequence getCommandString() {
+        return "dump";
+    }
+
+    /**
+     * Returns some tab completion info for the specified command.
+     * @param commandInfo the command lines
+     * @param commandPoint the cursor position
+     * @return some tab completion info for the specified command.
+     */
+    @Override
+    public TabCompletionInfo getTabCompletionInfo(SQLCommand command, Point commandPoint) {
+        String[] parsedStrings = getParsedStrings(command);
+        List<String> commandInfo = new ArrayList<String>(2);
+        commandInfo.add("SELECT * FROM ");
+        commandInfo.add(parsedStrings[2]);
+        //debug(commandInfo.toString());
+        int correction = command.getCommandString().length() - parsedStrings[2].length();
+        Point point = new Point(commandPoint.x - correction, 1); // TODO fix.
+        return SQLUtil.getTabCompletionInfo(commandInfo, point);
+    }
+
+    @Override
+    public CharSequence getHelp() {
+        return "tablename [where clause]\n" + "For example: dump users where role=\'manager\';\n\n" + "See \'read\' for options to read the dump file back into the table";
+    }
+
+    @Over...
 
[truncated message content]

[Sqlshell-svn] /devel/src: main/java/nl/improved/sqlclient main/java/nl/improved/sqlclient/commands test/java/nl/improved/sqlclient/commands

[SVN by r. <sv...@ca...>]

Author: roy
Date: 2009-12-08 10:45:35 +0100 (Tue, 08 Dec 2009)
New Revision: 428

Modified:
   src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java
   src/main/java/nl/improved/sqlclient/SQLShell.java
   src/main/java/nl/improved/sqlclient/commands/DumpCommand.java
   src/main/java/nl/improved/sqlclient/commands/ReadCommand.java
   src/main/java/nl/improved/sqlclient/commands/ReadDumpCommand.java
   src/main/java/nl/improved/sqlclient/commands/SettingsCommand.java
   src/test/java/nl/improved/sqlclient/commands/DumpCommandTest.java
Log:
improved dump/read command with progress output



Modified: src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java
===================================================================
--- src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java	2009-10-16 14:12:51 UTC (rev 427)
+++ src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java	2009-12-08 09:45:35 UTC (rev 428)
@@ -141,8 +141,8 @@
         commands.register("HELP[\\s]*.*", new HelpCommand());
         commands.register("HISTORY[\\s]*.*", new HistoryCommand());
         commands.register("SPOOL[\\s]*.*", new SpoolCommand());
-        commands.register("DUMP[\\s]*.*[A-Z]+.*", new DumpCommand());
-        commands.register("READ[\\s]*.*[A-Z]+.*", new ReadCommand());
+        commands.register("DUMP[\\s]*.*[A-Z]+.*", new DumpCommand(this));
+        commands.register("READ[\\s]*.*[A-Z]+.*", new ReadCommand(this));
         commands.register("QUIT[\\s]*", new QuitCommand("quit"));
         commands.register("EXIT[\\s]*", new QuitCommand("exit"));
         commands.register("SAVE[\\s]*.*", new SaveCommand());
@@ -807,9 +807,10 @@
      * Output data to the screen.
      * @param data the data to print to the screen.
      */
-    protected void output(CharSequence data) {
+    public void output(CharSequence data) {
         output(data, true);
     }
+
     protected void output(CharSequence data, boolean spool) {
         synchronized(this) {
             List<CharSequence> screenBuffer = screen.getScreenBuffer();

Modified: src/main/java/nl/improved/sqlclient/SQLShell.java
===================================================================
--- src/main/java/nl/improved/sqlclient/SQLShell.java	2009-10-16 14:12:51 UTC (rev 427)
+++ src/main/java/nl/improved/sqlclient/SQLShell.java	2009-12-08 09:45:35 UTC (rev 428)
@@ -169,7 +169,7 @@
                 }
 
                 @Override
-                protected void output(CharSequence data) {
+                public void output(CharSequence data) {
                     if (!argsMap.containsKey(OUTPUT)) {
                         System.out.println(data);
                     }

Modified: src/main/java/nl/improved/sqlclient/commands/DumpCommand.java
===================================================================
--- src/main/java/nl/improved/sqlclient/commands/DumpCommand.java	2009-10-16 14:12:51 UTC (rev 427)
+++ src/main/java/nl/improved/sqlclient/commands/DumpCommand.java	2009-12-08 09:45:35 UTC (rev 428)
@@ -36,6 +36,10 @@
 
     private String fileName;
 
+    public DumpCommand(AbstractSQLShellWindow window) {
+        super(window);
+    }
+
     // 0 filename
     // 1 tablename
     // 2 query
@@ -160,9 +164,13 @@
                 }
                 hd.endElement("", "", "row");
                 rowCount++;
+                if (rowCount % 1000 == 0) {
+                    output(Integer.toString(rowCount)+" rows written");
+                }
             }
             hd.endElement("", "", "dump");
             hd.endDocument();
+
         } catch (SAXException ex) {
             Logger.getLogger(AbstractSQLShellWindow.class.getName()).log(Level.SEVERE, null, ex);
         } catch (TransformerConfigurationException e) {

Modified: src/main/java/nl/improved/sqlclient/commands/ReadCommand.java
===================================================================
--- src/main/java/nl/improved/sqlclient/commands/ReadCommand.java	2009-10-16 14:12:51 UTC (rev 427)
+++ src/main/java/nl/improved/sqlclient/commands/ReadCommand.java	2009-12-08 09:45:35 UTC (rev 428)
@@ -21,9 +21,20 @@
  * Read the result of a dump file.
  */
 public class ReadCommand extends ReadDumpCommand {
+    private static boolean autoCommit;
 
+    static void setAutoCommit(boolean b) {
+        autoCommit = b;
+    }
+
     private String fileName;
 
+    public ReadCommand(AbstractSQLShellWindow window) {
+        super(window);
+    }
+
+
+
     @Override
     public CommandResult execute(SQLCommand cmd) {
         String command = cmd.getCommandString();
@@ -118,6 +129,14 @@
                 }
                 pstmt.executeUpdate();
                 rowCount++;
+                if (rowCount % 1000 == 0) {
+                    if (autoCommit) {
+                        pstmt.getConnection().commit();
+                        output(Integer.toString(rowCount)+" rows comitted");
+                    } else {
+                        output(Integer.toString(rowCount)+" rows inserted");
+                    }
+                }
             }
         } catch (SQLException e) {
             throw new IllegalStateException("Failed to execute update query for dump(" + fileName + "): " + e.toString(), e);

Modified: src/main/java/nl/improved/sqlclient/commands/ReadDumpCommand.java
===================================================================
--- src/main/java/nl/improved/sqlclient/commands/ReadDumpCommand.java	2009-10-16 14:12:51 UTC (rev 427)
+++ src/main/java/nl/improved/sqlclient/commands/ReadDumpCommand.java	2009-12-08 09:45:35 UTC (rev 428)
@@ -1,12 +1,24 @@
 package nl.improved.sqlclient.commands;
 
 import java.sql.Types;
+import nl.improved.sqlclient.AbstractSQLShellWindow;
 import nl.improved.sqlclient.commands.Command;
 
 abstract class ReadDumpCommand implements Command {
 
     public static final String DATE_FORMAT = "yyyyMMddHHmmss";
+    private final AbstractSQLShellWindow window;
 
+    public ReadDumpCommand(AbstractSQLShellWindow window) {
+        this.window = window;
+    }
+
+    void output(CharSequence out) {
+        if (window != null) {
+            window.output(out);
+        }
+    }
+
     boolean isBinary(int columnType) {
         if (columnType == Types.BINARY || columnType == Types.BLOB || columnType == Types.CLOB || columnType == Types.LONGVARBINARY || columnType == Types.LONGVARCHAR || columnType == Types.OTHER) {
             return true;

Modified: src/main/java/nl/improved/sqlclient/commands/SettingsCommand.java
===================================================================
--- src/main/java/nl/improved/sqlclient/commands/SettingsCommand.java	2009-10-16 14:12:51 UTC (rev 427)
+++ src/main/java/nl/improved/sqlclient/commands/SettingsCommand.java	2009-12-08 09:45:35 UTC (rev 428)
@@ -30,6 +30,15 @@
             ResultBuilder.setVerticalSeparator(cmdString.charAt(cmdString.indexOf('=')+1));
             return new SimpleCommandResult(true, "Vertical separator updated to '"+ResultBuilder.getVerticalSeparator()+"'.");
         }
+        if (setter.equals("readAutoCommit")) {
+            //ResultBuilder.setVerticalSeparator(cmdString.charAt(cmdString.indexOf('=')+1));
+            char readAutoCommit = cmdString.charAt(cmdString.indexOf('=')+1);
+            if (readAutoCommit == 'Y' || readAutoCommit == 'N') {
+                return new SimpleCommandResult(true, "Auto commit for the read command is set to '"+ResultBuilder.getVerticalSeparator()+"'.");
+            } else {
+                return new SimpleCommandResult(false, "Auto commit for the read command can only be set to 'Y' or 'N' (value: " + readAutoCommit+")");
+            }
+        }
         if (setter.startsWith("@")) { // variable
             // see
             // http://www.science.uva.nl/ict/ossdocs/mysql/manual_Reference.html
@@ -58,7 +67,8 @@
         return "Change settings of sqlshell properties" +
                "Settings are:\n"+
                "hSeparatorChar\n"+
-               "vSeparatorChar\n" +
+               "vSeparatorChar (values Y/N)\n" +
+               "readAutoCommit\n" +
                "@variable=value";
     }
 

Modified: src/test/java/nl/improved/sqlclient/commands/DumpCommandTest.java
===================================================================
--- src/test/java/nl/improved/sqlclient/commands/DumpCommandTest.java	2009-10-16 14:12:51 UTC (rev 427)
+++ src/test/java/nl/improved/sqlclient/commands/DumpCommandTest.java	2009-12-08 09:45:35 UTC (rev 428)
@@ -76,7 +76,7 @@
     public void testGetTabCompletionInfo() {
         System.out.println("getTabCompletionInfo");
         SQLCommand cmd = new SQLCommand("dump t");
-        DumpCommand dcmd = new DumpCommand();
+        DumpCommand dcmd = new DumpCommand(null);
         TabCompletionInfo completionInfo = dcmd.getTabCompletionInfo(cmd, new Point(cmd.getCommandString().length(), 0));
         assertNotNull(completionInfo);
         assertEquals(TabCompletionInfo.MatchType.TABLE_NAMES, completionInfo.getMatchType());