Menu
▾
▴
[Sqlshell-svn] /devel/src: main/java/nl/improved/sqlclient main/java/nl/improved/sqlclient/commands test/java/nl/improved/sqlclient/commands
|
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());
+ }
+}
|
