Menu
▾
▴
[Sqlshell-svn] /devel/src/main/java/nl/improved/sqlclient
|
From: SVN by r. <sv...@ca...> - 2008-07-23 09:50:24
|
Author: roy Date: 2008-07-23 11:50:14 +0200 (Wed, 23 Jul 2008) New Revision: 266 Modified: src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java Log: added dump/read command implementation.. not final yet though! Modified: src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java =================================================================== --- src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java 2008-07-23 07:24:09 UTC (rev 265) +++ src/main/java/nl/improved/sqlclient/AbstractSQLShellWindow.java 2008-07-23 09:50:14 UTC (rev 266) @@ -17,8 +17,14 @@ import java.io.*; 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; @@ -28,7 +34,13 @@ import java.util.LinkedHashMap; import java.util.logging.Level; import java.util.logging.Logger; +import javax.xml.parsers.DocumentBuilder; +import javax.xml.parsers.DocumentBuilderFactory; import nl.improved.sqlclient.commands.*; +import org.w3c.dom.Document; +import org.w3c.dom.Element; +import org.w3c.dom.Node; +import org.w3c.dom.NodeList; /** * The SQLShell main class. @@ -130,6 +142,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("QUIT[\\s]*", new QuitCommand("quit")); commands.register("EXIT[\\s]*", new QuitCommand("exit")); //commands.register("\\\\Q[\\s]*", new QuitCommand("\\q")); @@ -1273,7 +1287,210 @@ return false; } } + /** + * Writes the result of a query into a dump file so that it can be read back. + */ + private class DumpCommand implements Command { + private String fileName; + private FileWriter dumpWriter; + @Override + public CharSequence 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; + } + try { + File 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+"'"); + } + dumpWriter = new FileWriter(fileName); + dumpWriter.write("<dump tablename=\"" + dumpFileName+"\">\n"); + String query = "select * from " + nextPart; + Connection c = DBConnector.getInstance().getConnection(); + Statement stmt = c.createStatement(); + ResultSet rs = stmt.executeQuery(query); + SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss"); + while (rs.next()) { + dumpWriter.write(" <row>\n"); + ResultSetMetaData metaData = rs.getMetaData(); + for (int col = 1; col <= metaData.getColumnCount(); col++) { + dumpWriter.write(" <col name=\""+metaData.getColumnName(col)+"\""); + if (metaData.getColumnType(col) == Types.DATE) { + dumpWriter.write(" type=\"date\">"); + Date date = rs.getDate(col); + if (date != null) { + dumpWriter.write(formatter.format(date)); + } + } else { + dumpWriter.write(">"); + if (rs.getString(col) != null) { + dumpWriter.write(rs.getString(col)); // TODO fix xml + } + } + dumpWriter.write("</col>\n"); + } + dumpWriter.write(" </row>\n"); + } + dumpWriter.write("</dump>"); + } 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 { + try { + dumpWriter.close(); + } catch (IOException ex) { + Logger.getLogger(AbstractSQLShellWindow.class.getName()).log(Level.SEVERE, null, ex); + } + } + return "Dump to "+fileName+" done."; + } + + @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) { + return null; + } + @Override + public CharSequence getHelp() { + return "tablename [where clause]"; + } + @Override + public boolean abort() { + return false;// not implemented + } + @Override + public boolean backgroundProcessSupported() { + return false; + } + } + /** + * Read the result of a dump file. + */ + private class ReadCommand implements Command { + private String fileName; + + @Override + public CharSequence 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; + } + 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"); + output("LENGTH : "+ n.getNodeName() +"/ "+ cols.getLength()); + 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("yyyyMMddHHmmss"); + for (int nodeNr = 0; nodeNr < nodeList.getLength(); nodeNr++) { + Element row = (Element) nodeList.item(0); // 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 && type.getNodeValue().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 { + pstmt.setString(colNr+1, column.getTextContent()); + } + } + pstmt.executeUpdate(); + } + } 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 "Read from "+fileName+" done."; + } + + @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) { + return null; // TODO + } + @Override + public CharSequence getHelp() { + return "filename: read dump file from filename\n"; + } + @Override + public boolean abort() { + return false;// not implemented + } + @Override + public boolean backgroundProcessSupported() { + return false; + } + } + private class ExecuteBatchCommand implements Command { private boolean cancelled; private Command currentCommand; |
