Menu
▾
▴
[Sqlshell-svn] /devel/src/main/java/nl/improved/sqlclient/commands
|
From: SVN by r. <sv...@ca...> - 2009-12-15 08:58:53
|
Author: roy
Date: 2009-12-15 09:58:40 +0100 (Tue, 15 Dec 2009)
New Revision: 433
Modified:
src/main/java/nl/improved/sqlclient/commands/ReadCommand.java
Log:
fix read command for very large files
(tried dump of 4.9G)
Modified: src/main/java/nl/improved/sqlclient/commands/ReadCommand.java
===================================================================
--- src/main/java/nl/improved/sqlclient/commands/ReadCommand.java 2009-12-08 10:18:57 UTC (rev 432)
+++ src/main/java/nl/improved/sqlclient/commands/ReadCommand.java 2009-12-15 08:58:40 UTC (rev 433)
@@ -1,5 +1,7 @@
package nl.improved.sqlclient.commands;
+import java.text.ParseException;
+import javax.xml.parsers.SAXParser;
import nl.improved.sqlclient.*;
import java.io.File;
import java.io.IOException;
@@ -9,12 +11,15 @@
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 java.util.ArrayList;
+import java.util.List;
+import javax.xml.parsers.SAXParserFactory;
+import org.xml.sax.Attributes;
+import org.xml.sax.ContentHandler;
+import org.xml.sax.Locator;
+import org.xml.sax.SAXException;
+import org.xml.sax.XMLReader;
+import org.xml.sax.helpers.DefaultHandler;
import sun.misc.BASE64Decoder;
/**
@@ -28,6 +33,7 @@
}
private String fileName;
+ private ReadCommandContentHandler contentHandler;
public ReadCommand(AbstractSQLShellWindow window) {
super(window);
@@ -45,109 +51,138 @@
} else {
dumpFileName = nextPart;
}
- int rowCount = 0;
try {
- File f = new File(AbstractSQLShellWindow.toFileName(dumpFileName + ".dmp"));
+ File f = new File(AbstractSQLShellWindow.toFileName(dumpFileName));
fileName = f.getAbsolutePath();
if (!f.exists() && !f.canRead()) {
- throw new IllegalStateException("Failed to read dump file: \'" + fileName + "\'");
+ f = new File(AbstractSQLShellWindow.toFileName(dumpFileName + ".dmp"));
+ fileName = f.getAbsolutePath();
+ if (!f.exists() && !f.canRead()) {
+ f = new File(AbstractSQLShellWindow.toFileName(dumpFileName + ".DMP"));
+ fileName = f.getAbsolutePath();
+ 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 += ", ";
+
+ final SimpleDateFormat formatter = new SimpleDateFormat(DATE_FORMAT);
+ final Connection c = DBConnector.getInstance().getConnection();
+ SAXParser parser = SAXParserFactory.newInstance().newSAXParser();
+ XMLReader reader = parser.getXMLReader();
+ reader.setFeature("http://xml.org/sax/features/validation", false);
+ contentHandler = new ReadCommandContentHandler(fileName) {
+ final String NULL_DATE="null_date";
+ String query = null;
+ String values = ") values (";
+ List valuesList = new ArrayList();
+ PreparedStatement pstmt;
+
+ @Override
+ public void startElement(String uri, String localName, String qName, Attributes atts) throws SAXException {
+ if (qName.equals("dump")) {
+ query = "insert into " + atts.getValue("tablename") + " (";
+ }
+ super.startElement(uri, localName, qName, atts);
}
- }
- 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();
+
+ @Override
+ public void handleElement(ElementInfo info, String content) throws SQLException, IOException {
+ if (info.name.equals("row")) {
+ if (!query.endsWith(")")) {
+ query = query + values + ")";
+ pstmt = c.prepareStatement(query);
+ }
+ for (int i = 0; i < valuesList.size() ; i++) {
+ Object o = valuesList.get(i);
+ if (o == NULL_DATE) {
+ pstmt.setDate(i+1, null);
+ } else {
+ pstmt.setObject(i+1, o);
+ }
+ }
+ // execute query...
+ pstmt.executeUpdate();
+ valuesList.clear();
+ rowCount++;
+ if (rowCount % 100000 == 0) {
+ if (autoCommit) {
+ pstmt.getConnection().commit();
+ output(Integer.toString(rowCount)+" rows comitted");
+ } else {
+ output(Integer.toString(rowCount)+" rows inserted");
+ }
+ }
+ }
+ if (info.name.equals("col")) {
+ if (!query.endsWith(")")) {
+ if (values.endsWith("?")) {
+ values+=",";
+ }
+ values += "?";
+ }
+ String typeString = info.atts.getValue("type");
if (typeString.equals("date")) {
- String nodeValue = column.getTextContent();
+ String nodeValue = content;
if (nodeValue == null || nodeValue.equals("")) {
- pstmt.setDate(colNr + 1, null);
+ valuesList.add(NULL_DATE);
} else {
- pstmt.setDate(colNr + 1, new Date(formatter.parse(nodeValue).getTime()));
+ try {
+ //pstmt.setDate(colNr + 1, new Date(formatter.parse(nodeValue).getTime()));
+ valuesList.add(new Date(formatter.parse(nodeValue).getTime()));
+ } catch (ParseException ex) {
+ throw new IOException("Failed to parse date :"+ nodeValue);
+ }
}
} else if (typeString.equals("binary")) {
- String nodeValue = column.getTextContent();
+ String nodeValue = content;
BASE64Decoder decoder = new BASE64Decoder();
byte[] value = decoder.decodeBuffer(nodeValue);
- pstmt.setBytes(colNr + 1, value);
+ valuesList.add(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);
+ String nodeValue = content;
+ if (typeString.startsWith(".")) {
+ typeString = typeString.substring(1);
}
+ try {
+ int iType = Integer.parseInt(typeString);
+ switch (iType) {
+ case Types.INTEGER:
+ case Types.SMALLINT:
+ case Types.BIGINT:
+ valuesList.add(Integer.parseInt(nodeValue));
+ break;
+ case Types.DOUBLE:
+ valuesList.add(Double.parseDouble(nodeValue));
+ break;
+ case Types.VARCHAR:
+ case Types.CHAR:
+ case Types.LONGNVARCHAR:
+ case Types.NCHAR:
+ case Types.NVARCHAR:
+ valuesList.add(nodeValue);
+ break;
+ default:
+ valuesList.add(nodeValue);
+ }
+ } catch(Exception e) {
+ valuesList.add(nodeValue);
+ }
}
- } else {
- //debug(nodeNr +" ? "+column.getTextContent());
- pstmt.setString(colNr + 1, column.getTextContent());
}
}
- 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);
- } catch (IOException e) {
- throw new IllegalStateException("Failed to read dump (" + fileName + "): " + e.toString(), e);
+ };
+ reader.setContentHandler(contentHandler);
+ //parser.parse(new File(fileName), new DefaultHandler());
+ parser.parse(new File(fileName), (DefaultHandler)null);
+ contentHandler = null;
+ return new SimpleCommandResult(true, "Read from " + fileName + " done. (" + contentHandler.rowCount + " rows imported)");
+ } catch (AbortException e) {
+ return new SimpleCommandResult(true, "Read from " + fileName + " aborted. (" + contentHandler.rowCount + " rows imported)");
} 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";
@@ -172,11 +207,117 @@
@Override
public boolean abort() {
+ if (contentHandler != null) {
+ contentHandler.abort = true;
+ }
return false;
}
@Override
public boolean backgroundProcessSupported() {
- return false;
+ return true;
}
+
+ private static abstract class ReadCommandContentHandler implements ContentHandler {
+
+ int rowCount = 0;
+ private List<ElementInfo> nameTree = new ArrayList<ElementInfo>();
+ private StringBuffer content;
+ private boolean failed = false;
+ private final String fileName;
+ private boolean abort;
+
+ public ReadCommandContentHandler(String fileName) {
+ this.fileName = fileName;
+ }
+
+
+ public void setDocumentLocator(Locator locator) {
+ }
+
+ public void startDocument() throws SAXException {
+ nameTree.clear();
+ content = new StringBuffer();
+ }
+
+ public void endDocument() throws SAXException {
+ nameTree.clear();
+ }
+
+ public void startPrefixMapping(String prefix, String uri) throws SAXException {
+ }
+
+ public void endPrefixMapping(String prefix) throws SAXException {
+ }
+
+ public void startElement(String uri, String localName, String qName, Attributes atts) throws SAXException {
+ nameTree.add(new ElementInfo(qName, atts));
+ if (abort) {
+ throw new AbortException();
+ }
+ }
+
+ public void endElement(String uri, String localName, String qName) throws SAXException {
+ if (nameTree.get(nameTree.size()-1).name.equals(qName)) {
+ try {
+ handleElement(nameTree.remove(nameTree.size() - 1), content.toString());
+ } 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);
+ }
+ content = new StringBuffer();
+ } else {
+ throw new IllegalStateException("Closing unopened element");
+ }
+ }
+
+ public abstract void handleElement(ElementInfo info, String content) throws SQLException, IOException;
+
+ public void characters(char[] ch, int start, int length) throws SAXException {
+ content.append(ch, start, length);
+ }
+
+ public void ignorableWhitespace(char[] ch, int start, int length) throws SAXException {
+ content.append(ch, start, length);
+ }
+
+ public void processingInstruction(String target, String data) throws SAXException {
+ throw new UnsupportedOperationException("Not supported yet.");
+ }
+
+ public void skippedEntity(String name) throws SAXException {
+ throw new UnsupportedOperationException("Not supported yet.");
+ }
+
+ }
+
+ private static class ElementInfo {
+ private final String name;
+ private final Attributes atts;
+
+ public ElementInfo(String name, Attributes atts) {
+ this.name = name;
+ this.atts = atts;
+ }
+ }
+
+ private static class AbortException extends RuntimeException {
+
+ }
+
+ public static void main(String[] args) throws Exception {
+ final String fileName = args[0];
+ SAXParser parser = SAXParserFactory.newInstance().newSAXParser();
+ XMLReader reader = parser.getXMLReader();
+ reader.setFeature("http://xml.org/sax/features/validation", false);
+ reader.setContentHandler(new ReadCommandContentHandler(fileName) {
+ @Override
+ public void handleElement(ElementInfo info, String content) throws SQLException, IOException {
+ System.out.println(info.name);
+ }
+ });
+ //parser.parse(new File(fileName), new DefaultHandler());
+ parser.parse(new File(fileName), (DefaultHandler)null);
+ }
}
|
