From: SVN by r. <sv...@ca...> - 2009-09-26 12:51:30
|
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"; + } + + @Override + public boolean abort() { + return false; + } + + @Override + public boolean backgroundProcessSupported() { + return false; + } +} Added: src/main/java/nl/improved/sqlclient/commands/ReadCommand.java =================================================================== --- src/main/java/nl/improved/sqlclient/commands/ReadCommand.java 2009-09-22 18:54:54 UTC (rev 421) +++ src/main/java/nl/improved/sqlclient/commands/ReadCommand.java 2009-09-26 12:51:06 UTC (rev 422) @@ -0,0 +1,163 @@ +package nl.improved.sqlclient.commands; + +import nl.improved.sqlclient.*; +import java.io.File; +import java.io.IOException; +import java.sql.Connection; +import java.sql.Date; +import java.sql.PreparedStatement; +import java.sql.SQLException; +import java.sql.Types; +import java.text.SimpleDateFormat; +import javax.xml.parsers.DocumentBuilder; +import javax.xml.parsers.DocumentBuilderFactory; +import org.w3c.dom.Document; +import org.w3c.dom.Element; +import org.w3c.dom.Node; +import org.w3c.dom.NodeList; +import sun.misc.BASE64Decoder; + +/** + * Read the result of a dump file. + */ +public 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(AbstractSQLShellWindow.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")) { + String nodeValue = column.getTextContent(); + BASE64Decoder decoder = new BASE64Decoder(); + byte[] value = decoder.decodeBuffer(nodeValue); + 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: + 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 AbstractSQLShellWindow.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; + } + + @Override + public boolean backgroundProcessSupported() { + return false; + } +} Added: src/main/java/nl/improved/sqlclient/commands/ReadDumpCommand.java =================================================================== --- src/main/java/nl/improved/sqlclient/commands/ReadDumpCommand.java 2009-09-22 18:54:54 UTC (rev 421) +++ src/main/java/nl/improved/sqlclient/commands/ReadDumpCommand.java 2009-09-26 12:51:06 UTC (rev 422) @@ -0,0 +1,16 @@ +package nl.improved.sqlclient.commands; + +import java.sql.Types; +import nl.improved.sqlclient.commands.Command; + +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.LONGVARBINARY || columnType == Types.LONGVARCHAR || columnType == Types.OTHER) { + return true; + } + return false; + } +} Added: src/test/java/nl/improved/sqlclient/commands/DumpCommandTest.java =================================================================== --- src/test/java/nl/improved/sqlclient/commands/DumpCommandTest.java 2009-09-22 18:54:54 UTC (rev 421) +++ src/test/java/nl/improved/sqlclient/commands/DumpCommandTest.java 2009-09-26 12:51:06 UTC (rev 422) @@ -0,0 +1,97 @@ +/* + * To change this template, choose Tools | Templates + * and open the template in the editor. + */ + +package nl.improved.sqlclient.commands; + +import junit.framework.TestCase; +import nl.improved.sqlclient.Point; +import nl.improved.sqlclient.SQLCommand; +import nl.improved.sqlclient.TabCompletionInfo; + +/** + * + * @author roy + */ +public class DumpCommandTest extends TestCase { + + public DumpCommandTest(String testName) { + super(testName); + } + + /** + * Test of getCommandString method, of class DumpCommand. + */ + public void testGetParsedStrings() { + // Simple dump entire table + SQLCommand cmd = new SQLCommand("dump mytable"); + String[] result = DumpCommand.getParsedStrings(cmd); + assertNotNull(result); + assertEquals(3, result.length); + assertEquals("mytable", result[0]); + assertEquals("mytable", result[1]); + assertEquals("mytable", result[2]); + + // Dump table with where clause + cmd = new SQLCommand("dump mytable where dumped=false"); + result = DumpCommand.getParsedStrings(cmd); + assertNotNull(result); + assertEquals(3, result.length); + assertEquals("mytable", result[0]); + assertEquals("mytable", result[1]); + assertEquals("mytable where dumped=false", result[2]); + + // Dump table with filename + cmd = new SQLCommand("dump as myfile mytable"); + result = DumpCommand.getParsedStrings(cmd); + assertNotNull(result); + assertEquals(3, result.length); + assertEquals("myfile", result[0]); + assertEquals("mytable", result[1]); + assertEquals("mytable", result[2]); + + // Dump table with filename with where clause + cmd = new SQLCommand("dump as myfile mytable where dumped=false"); + result = DumpCommand.getParsedStrings(cmd); + assertNotNull(result); + assertEquals(3, result.length); + assertEquals("myfile", result[0]); + assertEquals("mytable", result[1]); + assertEquals("mytable where dumped=false", result[2]); + + // Dump incomplete command + cmd = new SQLCommand("dump as myfile "); + result = DumpCommand.getParsedStrings(cmd); + assertNotNull(result); + assertEquals(3, result.length); + assertEquals("myfile", result[0]); + assertEquals("", result[1]); + assertEquals("", result[2]); + } + + /** + * Test of getTabCompletionInfo method, of class DumpCommand. + */ + public void testGetTabCompletionInfo() { + System.out.println("getTabCompletionInfo"); + SQLCommand cmd = new SQLCommand("dump t"); + DumpCommand dcmd = new DumpCommand(); + TabCompletionInfo completionInfo = dcmd.getTabCompletionInfo(cmd, new Point(cmd.getCommandString().length(), 0)); + assertNotNull(completionInfo); + assertEquals(TabCompletionInfo.MatchType.TABLE_NAMES, completionInfo.getMatchType()); + assertEquals("t", completionInfo.getStart()); + + cmd = new SQLCommand("dump as tmp "); + completionInfo = dcmd.getTabCompletionInfo(cmd, new Point(cmd.getCommandString().length(), 0)); + assertNotNull(completionInfo); + assertEquals(TabCompletionInfo.MatchType.TABLE_NAMES, completionInfo.getMatchType()); + assertEquals("", completionInfo.getStart()); + + cmd = new SQLCommand("dump as tmp f"); + completionInfo = dcmd.getTabCompletionInfo(cmd, new Point(cmd.getCommandString().length(), 0)); + assertNotNull(completionInfo); + assertEquals(TabCompletionInfo.MatchType.TABLE_NAMES, completionInfo.getMatchType()); + assertEquals("f", completionInfo.getStart()); + } +} |