Repository: incubator-ranger Updated Branches: refs/heads/master 78cc53a11 -> 6781cc9c4
RANGER-226: Support JDBC based SQL invocation - adding jisql Signed-off-by: sneethiraj <[email protected]> Project: http://git-wip-us.apache.org/repos/asf/incubator-ranger/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ranger/commit/6781cc9c Tree: http://git-wip-us.apache.org/repos/asf/incubator-ranger/tree/6781cc9c Diff: http://git-wip-us.apache.org/repos/asf/incubator-ranger/diff/6781cc9c Branch: refs/heads/master Commit: 6781cc9c4593b22cc3b497f46b7be8ec7bfaabd6 Parents: 78cc53a Author: vperiasamy <[email protected]> Authored: Tue Jan 27 19:16:21 2015 -0500 Committer: sneethiraj <[email protected]> Committed: Tue Jan 27 21:32:12 2015 -0500 ---------------------------------------------------------------------- jisql/pom.xml | 43 ++ .../util/outputformatter/CSVFormatter.java | 160 ++++ .../util/outputformatter/DefaultFormatter.java | 353 +++++++++ .../util/outputformatter/JisqlFormatter.java | 96 +++ .../util/outputformatter/XMLFormatter.java | 129 ++++ .../main/java/org/apache/util/sql/Jisql.java | 734 +++++++++++++++++++ .../java/org/apache/util/sql/MaskingThread.java | 71 ++ .../java/org/apache/util/sql/MySQLPLRunner.java | 345 +++++++++ pom.xml | 1 + src/main/assembly/admin-web.xml | 23 +- 10 files changed, 1954 insertions(+), 1 deletion(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6781cc9c/jisql/pom.xml ---------------------------------------------------------------------- diff --git a/jisql/pom.xml b/jisql/pom.xml new file mode 100644 index 0000000..6d955d7 --- /dev/null +++ b/jisql/pom.xml @@ -0,0 +1,43 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +--> +<project xmlns="http://maven.apache.org/POM/4.0.0"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd";> + <modelVersion>4.0.0</modelVersion> + <artifactId>jisql</artifactId> + <version>0.1.0</version> + <name>Jdbc SQL Connector</name> + <description>Jdbc SQL Connector to execute sql statement in any db</description> + <packaging>jar</packaging> + <parent> + <groupId>org.apache.ranger</groupId> + <artifactId>ranger</artifactId> + <version>0.4.0</version> + <relativePath>..</relativePath> + </parent> + <dependencies> + <dependency> + <groupId>net.sourceforge.javacsv</groupId> + <artifactId>javacsv</artifactId> + <version>2.0</version> + </dependency> + <dependency> + <groupId>net.sf.jopt-simple</groupId> + <artifactId>jopt-simple</artifactId> + <version>3.2</version> + </dependency> + </dependencies> +</project> http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6781cc9c/jisql/src/main/java/org/apache/util/outputformatter/CSVFormatter.java ---------------------------------------------------------------------- diff --git a/jisql/src/main/java/org/apache/util/outputformatter/CSVFormatter.java b/jisql/src/main/java/org/apache/util/outputformatter/CSVFormatter.java new file mode 100644 index 0000000..6e80b42 --- /dev/null +++ b/jisql/src/main/java/org/apache/util/outputformatter/CSVFormatter.java @@ -0,0 +1,160 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.util.outputformatter; + +import java.io.PrintStream; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; + +import java.nio.charset.Charset; + +import joptsimple.OptionParser; +import joptsimple.OptionSet; + +import com.csvreader.CsvWriter; + + +/** + * This is the default formatter for Jisql. It outputs data in a "normal" + * format that is similar to most other database command line formatters. + * + */ +public class CSVFormatter implements JisqlFormatter { + private char delimiter = ','; + private boolean includeColumnNames = false; + + + /** + * Sets a the supported option list for this formatter. This formatter accepts + * the following options: + * + * <p> </p> + * + * <ul> + * <li><<b>delimiter</b> specifies the delimiter to use. By default a comma is + * used</li> + * <li><b>colnames</b> if included then column names are printed as the first + * line of output. By default they are not included</li> + * </ul> + * + * @param parser the OptionParser to use. + * + */ + public void setSupportedOptions( OptionParser parser ) { + parser.accepts( "delimiter" ).withRequiredArg().ofType( String.class ); + parser.accepts( "colnames" ); + } + + /** + * Consumes any options that were specified on the command line. + * + * @param options the OptionSet that the main driver is using. + * + * @throws Exception if there is a problem parsing the command line arguments. + * + */ + public void consumeOptions( OptionSet options ) throws Exception { + if( options.has( "delimiter" ) ) + delimiter = ((String)(options.valueOf( "delimiter" ))).charAt( 0 ); + + if( options.has( "colnames" ) ) + includeColumnNames = true; + } + + /** + * Called to output a usage message to the command line window. This + * message should contain information on how to call the formatter. + * + * @param out the PrintStream to display the usage message on. + * + */ + public void usage( PrintStream out ) { + out.println("\t-delimiter specifies the character to use as the delimiter. This defaults to \"" + delimiter + "\"" ); + out.println("\t-colnames outputs column names. By default there are no column names." ); + } + + /** + * Outputs an optional header for the CSV data. This header is only enabled + * if the "colnames" parameter is included. + * + * @param out a PrintStream to send any output to. + * @param metaData the ResultSetMetaData for the output. + * + */ + public void formatHeader( PrintStream out, ResultSetMetaData metaData ) throws Exception { + if( includeColumnNames ) { + int numColumns = metaData.getColumnCount(); + + // + // output the column names + // + for (int i = 1; i <= numColumns; i++) { + out.print( metaData.getColumnName(i).trim() ); + if( (i + 1) <= numColumns ) + out.print( delimiter ); + } + + out.println(); + } + } + + + /** + * Called to output the data. This class uses a third party library to output + * the CSV data. The library escapes the data as needed. + * + * @param out the PrintStream to output data to. + * @param resultSet the ResultSet for the row. + * @param metaData the ResultSetMetaData for the row. + * + * + */ + public void formatData( PrintStream out, ResultSet resultSet, ResultSetMetaData metaData ) throws Exception { + + CsvWriter csvWriter = new CsvWriter( out, delimiter, Charset.forName( "us-ascii" ) ); + + while( resultSet.next() ) { + int numColumns = metaData.getColumnCount(); + + for (int i = 1; i <= numColumns; i++) { + String result = resultSet.getString(i); + if( !resultSet.wasNull() ) + csvWriter.write( result ); + else + csvWriter.write( "" ); + } + + csvWriter.endRecord(); + } + + csvWriter.flush(); + } + + + /** + * Outputs a footer for a query. For the CSVFormatter this method does nothing. + * + * @param out the PrintStream to output data to. + * @param metaData the ResultSetMetaData for the output. + * + */ + public void formatFooter( PrintStream out, ResultSetMetaData metaData ) throws Exception { + } +} http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6781cc9c/jisql/src/main/java/org/apache/util/outputformatter/DefaultFormatter.java ---------------------------------------------------------------------- diff --git a/jisql/src/main/java/org/apache/util/outputformatter/DefaultFormatter.java b/jisql/src/main/java/org/apache/util/outputformatter/DefaultFormatter.java new file mode 100644 index 0000000..a9fac4d --- /dev/null +++ b/jisql/src/main/java/org/apache/util/outputformatter/DefaultFormatter.java @@ -0,0 +1,353 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.util.outputformatter; + +import java.io.PrintStream; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; + +import joptsimple.OptionParser; +import joptsimple.OptionSet; + + +/** + * This is the default formatter for Jisql. It outputs data in a "normal" + * format that is similar to most other database command line formatters. + * + */ +public class DefaultFormatter implements JisqlFormatter { + private boolean trimColumns = false; + private int columnWidth = 2048; // can be overridden with the -w switch + private char spacer = ' '; + private boolean printNull = true; + private boolean leftJustify = false; + private boolean printHeader = true; + private boolean debug = false; + private String delimiter = " | "; + + + /** + * Sets a the option list for this formatter. This formatter accepts the + * following options: + * + * <li><b>-noheader</b> do not print the header column info.</li> + * <li><b>-spacer</b> The character to use for "empty" space. This + * defaults to the space character. From mrider - "I added the ability to + * specify the spacer for columns - which used to be the single char ' '. I did + * this because of brain-dead Windows' command line copy/paste. It seems that when + * a line of text ends in space, copy does not copy that space. Which makes it + * difficult to copy/paste into another program. This can probably be ignored + * most of the time."</li> + * <li><b>-delimiter</b> Specify a single character delimiter for columns.</li> + * <li><b>-trim</b> trim the spaces from columns.</li> + * <li><b>-nonull</b> print an empty string instead of the word "NULL" + * when there is a null value.<li> + * <li><b>-left</b> left justify the output</li> + * <li><b>-w</b> specify the max width of a column. The default is 2048</li> + * <li><b>-debug</b> add debug headers to the output</li> + * </ul> + * + * @param parser the OptionParser to use. + * + */ + public void setSupportedOptions( OptionParser parser ) { + parser.accepts( "trim" ); + parser.accepts( "w" ).withRequiredArg().ofType( Integer.class ); + parser.accepts( "spacer" ).withRequiredArg().ofType( String.class ); + parser.accepts( "left" ); + parser.accepts( "nonull" ); + parser.accepts( "noheader" ); + parser.accepts( "debug" ); + parser.accepts( "delimiter" ).withRequiredArg().ofType( String.class ); + } + + /** + * Consumes any options that were specified on the command line. + * + * @param options the OptionSet that the main driver is using. + * + * @throws Exception if there is a problem parsing the command line arguments. + * + */ + public void consumeOptions( OptionSet options ) throws Exception { + + if( options.has( "trim" ) ) + trimColumns = true; + + if( options.has( "w" ) ) + columnWidth = (Integer)options.valueOf( "w" ); + + if( options.has( "spacer" ) ) + spacer = ((String)(options.valueOf( "spacer" ))).charAt( 0 ); + + if( options.has( "left" ) ) + leftJustify = true; + + if( options.has( "nonull" ) ) + printNull = false; + + if( options.has( "noheader" ) ) + printHeader = false; + + if( options.has( "debug" ) ) + debug = true; + + if( options.hasArgument( "delimiter" ) ) + delimiter = (String)options.valueOf( "delimiter" ); + } + + + + /** + * Called to output a usage message to the command line window. This + * message should contain information on how to call the formatter. + * + * @param out the stream to print the output on + * + */ + public void usage( PrintStream out ) { + out.println("\t-w specifies the maximum field width for a column. The default is to output the full width of the column"); + out.println("\t-spacer changes the spacer between columns from a single space to the first character of the argument"); + out.println("\t-noheader do not print any header columns"); + out.println("\t-left left justify the output"); + out.println("\t-trim trim the data output. This is useful when specifying a delimiter."); + out.println("\t-nonull print the empty string instead of the word \"NULL\" for null values."); + out.println("\t-debug shows extra information about the output." ); + out.println("\t-delimiter specifies the delimiter. The default is \"" + delimiter + "\"." ); + + } + + /** + * Outputs a header for a query. For the DefaultFormatter the data is output + * by default unless the "noheader" option is specified. + * + * @param out - a PrintStream to send any output to. + * @param metaData - the ResultSetMetaData for the output. + * + */ + public void formatHeader( PrintStream out, ResultSetMetaData metaData ) throws Exception { + if( printHeader ) { + int numColumns = metaData.getColumnCount(); + + if( debug ) { + for (int i = 1; i <= numColumns; i++) { + out.print( formatLabel( metaData.getColumnTypeName(i), + metaData.getColumnDisplaySize(i))); + out.print(delimiter); + } + } + // + // output the column names + // + for (int i = 1; i <= numColumns; i++) { + out.print( formatLabel( metaData.getColumnName(i), + metaData.getColumnDisplaySize(i))); + out.print(delimiter); + } + + out.println(); + + // + // output pretty dividers + // + for (int i = 1; i <= numColumns; i++) { + out.print( formatSeparator( metaData.getColumnName(i), + metaData.getColumnDisplaySize(i))); + + if (i == numColumns) + out.print("-|"); + else + out.print("-+-"); + } + + out.println(); + } + } + + + /** + * Called to output the data. + * + * @param out the PrintStream to output data to. + * @param resultSet the ResultSet for the row. + * @param metaData the ResultSetMetaData for the row. + * + * + */ + public void formatData( PrintStream out, ResultSet resultSet, ResultSetMetaData metaData ) throws Exception { + + while( resultSet.next() ) { + int numColumns = metaData.getColumnCount(); + + for (int i = 1; i <= numColumns; i++) { + out.print( formatValue( metaData.getColumnName(i), + resultSet.getString(i), + metaData.getColumnDisplaySize(i))); + out.print( delimiter ); + } + + out.println(); + } + } + + + /** + * Outputs a footer for a query. This is called after all data has been + * exhausted. This method isn't used in the DefaultFormatter. + * + * @param out the PrintStream to output data to. + * @param metaData the ResultSetMetaData for the output. + * + */ + public void formatFooter( PrintStream out, ResultSetMetaData metaData ) throws Exception { + } + + + + /** + * Formats a label for output. + * + * @param s - the label to format + * @param width - the width of the field + * + * @return the formated label + * + */ + private String formatLabel(String s, int width) { + if (s == null) + s = "NULL"; + + if (columnWidth != 0) { + if (width > columnWidth) + width = columnWidth; + } + + if (width < s.length()) + width = s.length(); + + int len = s.length(); + + if (len >= width) + return s.substring(0, width); + + int fillWidth = width - len; + StringBuffer fill = new StringBuffer(fillWidth); + for (int i = 0; i < fillWidth; ++i) + fill.append(spacer); + if (leftJustify) + return s + fill; + else if (s.startsWith("-")) + return "-" + fill + s.substring(1); + else + return fill + s; + } + + /** + * Formats a separator for display. + * + * @param s - the field for which the separator is being generated + * @param width - the width of the field + * + * @return the formated separator + * + */ + private String formatSeparator(String s, int width) { + s = "NULL"; + + if (columnWidth != 0) { + if (width > columnWidth) + width = columnWidth; + } + + if (width < s.length()) + width = s.length(); + + int len = s.length(); + + if (len >= width) + width = len; + + StringBuffer fill = new StringBuffer(width); + for (int i = 0; i < width; ++i) + fill.append('-'); + + if( trimColumns ) + return fill.toString().trim(); + else + return fill.toString(); + } + + /** + * Formats a value for display. + * + * @param label the label associated with the value (for width purposes) + * @param s - the value to format + * @param width - the width of the field from the db. + * + * @return the formatted field. + * + */ + private String formatValue(String label, String s, int width) { + if (s == null) { + if( printNull ) + s = "NULL"; + else + s = ""; + } + + if (columnWidth != 0) { + if (width > columnWidth) + width = columnWidth; + } + + if (width < label.length()) + width = label.length(); + + int len = s.length(); + + if (len >= width) { + if( trimColumns ) + return s.substring(0, width).trim(); + else + return s.substring(0, width); + } + + int fillWidth = width - len; + StringBuffer fill = new StringBuffer(fillWidth); + for (int i = 0; i < fillWidth; ++i) + fill.append(spacer); + + StringBuilder returnValue = new StringBuilder(); + + if (leftJustify) + returnValue.append( s + fill ); + else if (s.startsWith("-")) + returnValue.append( "-" + fill + s.substring(1) ); + else { + returnValue.append( fill + s ); + } + + if( trimColumns ) { + return returnValue.toString().trim(); + } + else { + return returnValue.toString(); + } + } +} http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6781cc9c/jisql/src/main/java/org/apache/util/outputformatter/JisqlFormatter.java ---------------------------------------------------------------------- diff --git a/jisql/src/main/java/org/apache/util/outputformatter/JisqlFormatter.java b/jisql/src/main/java/org/apache/util/outputformatter/JisqlFormatter.java new file mode 100644 index 0000000..838225b --- /dev/null +++ b/jisql/src/main/java/org/apache/util/outputformatter/JisqlFormatter.java @@ -0,0 +1,96 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.util.outputformatter; + +import java.io.PrintStream; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; + +import joptsimple.OptionParser; +import joptsimple.OptionSet; + + +/** + * This is the definition of what a JisqlFormatter does. + * + */ +public interface JisqlFormatter { + + /** + * Sets a the option list for this formatter. + * + * @param parser - the OptionParser to use. + */ + public void setSupportedOptions( OptionParser parser ); + + /** + * Consumes any options that were specified on the command line. + * + * @param options the OptionSet that the main driver is using. Implementing + * classes should add their supported parameters to the list. + * + * @throws Exception if there is a problem parsing the command line arguments. + * Note that Jisql includes jopt-simple so you can use that + * to parse your command line. See + * <a href="http://jopt-simple.sourceforge.net/";>http://jopt-simple.sourceforge.net/</a> + * for more information. + * + */ + public void consumeOptions( OptionSet options ) throws Exception; + + /** + * Called to output a usage message to the command line window. This + * message should contain information on how to call the formatter. + * + * @param out where to put the usage message. + * + */ + public void usage( PrintStream out ); + + + /** + * Outputs a header for a query. This is called before any data is + * output. + * + * @param out where to put header output. + * @param metaData the ResultSetMetaData for the output. + * + */ + public void formatHeader( PrintStream out, ResultSetMetaData metaData ) throws Exception; + + /** + * Called to output the data. + * + * @param out where to put output data. + * @param resultSet the ResultSet for the row. + * @param metaData the ResultSetMetaData for the row. + * + */ + public void formatData( PrintStream out, ResultSet resultSet, ResultSetMetaData metaData ) throws Exception; + + /** + * Outputs a footer for a query. This is called after all data has been + * exhausted. + * + * @param out where to put footer output. + * @param metaData the ResultSetMetaData for the output. + * + */ + public void formatFooter( PrintStream out, ResultSetMetaData metaData ) throws Exception; +} http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6781cc9c/jisql/src/main/java/org/apache/util/outputformatter/XMLFormatter.java ---------------------------------------------------------------------- diff --git a/jisql/src/main/java/org/apache/util/outputformatter/XMLFormatter.java b/jisql/src/main/java/org/apache/util/outputformatter/XMLFormatter.java new file mode 100644 index 0000000..f6ddbb6 --- /dev/null +++ b/jisql/src/main/java/org/apache/util/outputformatter/XMLFormatter.java @@ -0,0 +1,129 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.util.outputformatter; + +import java.io.PrintStream; +import java.nio.charset.Charset; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; + +import joptsimple.OptionParser; +import joptsimple.OptionSet; + + +/** + * This is the default XML formatter for Jisql. It outputs data in an + * XML format. + * + */ +public class XMLFormatter implements JisqlFormatter { + + /** + * Sets a the option list for this formatter. This is a no-op in the + * XMLFormatter. + * + * @param parser the OptionParser to use. + * + */ + public void setSupportedOptions( OptionParser parser ) { + /* no options for the XMLFormatter */ + } + + /** + * Consumes any options that were specified on the command line. There are + * no options to set for the XMLFormatter so this method is a no-op. + * + * @param options the OptionSet that the main driver is using. + * + * @throws Exception if there is a problem parsing the command line arguments. + * + */ + public void consumeOptions( OptionSet options ) throws Exception { + /* no options for the XMLFormatter */ + } + + /** + * Called to output a usage message to the command line window. This + * message should contain information on how to call the formatter. + * There are no options to set for the XMLFormatter so this method is + * a no-op. + * + */ + public void usage( PrintStream out ) { + /* no options for the XMLFormatter */ + } + + + /** + * Outputs a header for a query. For the XMLFormater this outputs the XML + * pre-amble. The character encoding defaults to the current character + * encoding in use. + * + * @param out a PrintStream to send any output to. + * @param metaData the ResultSetMetaData for the output. + * + */ + public void formatHeader( PrintStream out, ResultSetMetaData metaData ) throws Exception { + out.print( "<?xml version=\"1.0\" encoding=\"" ); + out.print( Charset.defaultCharset().displayName().toLowerCase() ); + out.println( "\" ?>" ); + } + + + /** + * Called to output the data. Note that for the XMLFormatter null fields are + * just output as an empty field. + * + * @param out the PrintStream to output data to. + * @param resultSet the ResultSet for the row. + * @param metaData the ResultSetMetaData for the row. + * + */ + public void formatData( PrintStream out, ResultSet resultSet, ResultSetMetaData metaData ) throws Exception { + + while( resultSet.next() ) { + int numColumns = metaData.getColumnCount(); + + for (int i = 1; i <= numColumns; i++) { + out.print( "<" ); + out.print( metaData.getColumnName( i ).trim() ); + out.print( ">" ); + String result = resultSet.getString(i); + if( !resultSet.wasNull() ) + out.print( result.trim() ); + out.print( "</" ); + out.print( metaData.getColumnName( i ).trim() ); + out.print( ">" ); + } + + out.println(); + } + } + + + /** + * Outputs a footer for a query. This method isn't used in the XMLFormatter. + * + * @param out the PrintStream to output data to. + * @param metaData the ResultSetMetaData for the output. + * + */ + public void formatFooter( PrintStream out, ResultSetMetaData metaData ) throws Exception { + } +} http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6781cc9c/jisql/src/main/java/org/apache/util/sql/Jisql.java ---------------------------------------------------------------------- diff --git a/jisql/src/main/java/org/apache/util/sql/Jisql.java b/jisql/src/main/java/org/apache/util/sql/Jisql.java new file mode 100644 index 0000000..f3fab71 --- /dev/null +++ b/jisql/src/main/java/org/apache/util/sql/Jisql.java @@ -0,0 +1,734 @@ + /* Copyright (C) 2004-2011 Scott Dunbar ([email protected]) + * + * Licensed under the Apache License, Version 2.0 (the "License"); you may not + * use this file except in compliance with the License. You may obtain a copy of + * the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT + * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the + * License for the specific language governing permissions and limitations under + * the License. + */ +package org.apache.util.sql; + +import java.io.BufferedReader; +import java.io.Console; +import java.io.File; +import java.io.FileNotFoundException; +import java.io.FileReader; +import java.io.IOException; +import java.io.InputStreamReader; +import java.sql.Connection; +import java.sql.DatabaseMetaData; +import java.sql.Driver; +import java.sql.DriverManager; +import java.sql.DriverPropertyInfo; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.Properties; +import joptsimple.OptionParser; +import joptsimple.OptionSet; +import org.apache.util.outputformatter.JisqlFormatter; + +/** + * A simple utility to provide an interactive session with a SQL server. This + * application is conceptually modeled on the Sybase 'isql' program with, + * obviously, strong similarities to Microsoft SQL/Server isql and osql (as + * Microsoft got SQL Server from Sybase). + * <p> + * + * The program can act in a similar way to Oracle's sqlplus and PostgreSQL's + * psql. + * <p> + * + * A simple command line might look like (this should be all on one line) is: <br> + * <code> + * java -classpath lib/jisql.jar:<file containing native driver> + * org.apache.util.sql.Jisql -user scott -password blah -driver postgresql + * -cstring jdbc:postgresql://localhost:5432/scott -c \; + * </code> + * <p> + * + * This logs into a PostgreSQL database as the user "scott", password "blah". It + * connects to the database named "scott". It uses the command terminator of + * ";", just like psql or sqlplus (which is escaped in the example so that it + * will not be interpreted by the Unix shell). If you do not use this the + * default is the term "go" on a single line like Sybase's isql or MS/SQL's + * isql/osql. Note that there is a dependency on <a + * href="http://jopt-simple.sourceforge.net/";>JOpt Simple</a> in for the base + * configuration. Additionally, if you are using the CSVFormatter then it is + * dependent on <a href="http://sourceforge.net/projects/javacsv/";>Java CSV</a>. + * <p> + * + * + * Options: + * <ul> + * <li><b>-driver </b> This option allows you to specify the JDBC driver class + * name of the driver. There are several shortcuts that can be used: + * <ul> + * <li><b>jconnect4 </b>- short for <code>com.sybase.jdbc.SybDriver</code></li> + * <li><b>jconnect5 </b>- short for <code>com.sybase.jdbc2.jdbc.SybDriver</code> + * <li><b>jconnect6 </b>- short for <code>com.sybase.jdbc3.jdbc.SybDriver</code> + * </li> + * <li><b>oraclethin </b>- short for + * <code>oracle.jdbc.driver.OracleDriver</code></li> + * <li><b>db2app </b>- the DB2 "App" driver - + * <code>COM.ibm.db2.jdbc.app.DB2Driver</code></li> + * <li><b>db2net </b>- the DB2 "Net" driver - + * <code>COM.ibm.db2.jdbc.net.DB2Driver</code></li> + * <li><b>mssql </b>- short for + * <code>com.microsoft.jdbc.sqlserver.SQLServerDriver</code></li> + * <li><b>cloudscape </b>- short for <code>COM.cloudscape.core.JDBCDriver</code> + * </li> + * <li><b>pointbase </b>- short for + * <code>com.pointbase.jdbc.jdbcUniversalDriver</code></li> + * <li><b>postgresql </b>- short for <code>org.postgresql.Driver</code></li> + * <li><b>mysqlconj </b>- short for <code>com.mysql.jdbc.Driver</code>- the + * Connector/J driver for MySQL</li> + * <li><b>mysqlcaucho </b>- short for <code>com.caucho.jdbc.mysql.Driver</code>- + * the Caucho driver for MySQL</li> + * </ul> + * + * Alternatively, any class name can be specified here. The shortcuts only exist + * for those of us who generate more typos than real text :)</li> + * + * <li><b>-cstring </b> This option allows you to specify the connection string + * to the database. This string is driver specific but almost always starts with + * "jdbc:". Connection strings for the drivers I have tested look + * like: + * <ul> + * <li><b>jconnect4, jconnect5, jconnect6 </b>- Sybase connection strings take + * the form "jdbc:sybase:Tds:[hostname]:[port]/[db_name]"</li> + * <li><b>oraclethin </b>- The Oracle "thin" driver connection string + * looks like "jdbc:oracle:thin:@[hostname]:[port]:[oracle sid]"</li> + * <li><b>db2app </b>- The DB2 "App" driver connection string looks + * like "jdbc:db2:[db_name]"</li> + * <li><b>db2net </b>- The DB2 "Net" driver connection string looks + * like "jdbc:db2://[hostname]:[port]/[db_name]"</li> + * <li><b>mssql </b>- The MS/SQL driver connection string looks like + * "jdbc:microsoft:sqlserver://[hostname]:[port]/[db_name]"</li> + * <li><b>cloudscape </b>- The Cloudscape driver connection string looks like + * "jdbc:cloudscape:[db_name];create=true;autocommit=false"</li> + * <li><b>pointbase </b>- The Pointbase driver connection string looks like + * "jdbc:pointbase:server://[hostname]:[port]/[db_name]"</li> + * <li><b>postgresql </b>- The PostgreSQL driver connection string looks like + * "jdbc:postgresql://[hostname]:[port]/[db_name]"</li> + * <li><b>mysqlconj </b>- The MySQL Connector/J driver connection string looks + * like "jdbc:mysql://[hostname]:[port]/[db_name]"</li> + * <li><b>mysqlcaucho </b>- The MySQL Cahcho driver connection string looks like + * "jdbc:mysql-caucho://[hostname]:[port]/[db_name]"</li> + * </ul> + * + * <b>Important </b>- each JDBC vendor has other flags and parameters that can + * be passed on the connection string. You should look at the documentation for + * your JDBC driver for more information. The strings listed are just a sample + * and may change with a new release of the driver. None of these strings are + * coded within the application - the list is provided for reference only.</li> + * + * <li><b>-user or -u </b> The user name to use to log into the database with.</li> + * <li><b>-password or -p </b> The password to use to log into the database + * with. If this option is missing then the program asks for the password.</li> + * <li><b>-c </b> The "command terminator" to use. By default this + * application uses the string "go" (case insensitive) on a line by + * itself to determine when to send the string buffer to the database. You may + * specify something else with the -c option. For example, users of Oracle may + * prefer either the ";" (semi-colon) character or the "/" + * (forwardslash) character as that is what sqlplus uses. This string may occur + * as a standalone line or at the end of a particular line.</li> + * <li><b>-pf</b> Optional file to specify the password. This prevents having to + * have it visible when looking at a process status. The first line of the file + * is read and used as the password. If both the command line password and this + * option are specified the command line password is used.</li> + * <li><b>-input </b> The name of a file to read commands from instead of + * System.in.</li> + * <li><b>-query </b> An optional single query to run instead of interacting + * with the command line or a file. <b>Note</b> - the command <i>must</i> have a + * command terminator. So, for example, your command line may be something like + * "-c \; -query "select * from blah;". If you do not include the command + * terminator then the command will hang, waiting for you to enter the default + * "go".</li> + * <li><b>-debug </b> This turns on some internal debugging code. Not generally + * useful.</li> + * <li><b>-driverinfo </b> Allows you to print some information that the driver + * returns. Generally not very useful in all but a few cases.</li> + * <li><b>-formatter</b> Optionally specify a class name or short cut to format + * the output. There are three built in short cuts: + * <ul> + * <li><b>csv</b> output the data in CSV format.</li> + * <li><b>xml</b> output the data in XML format.</li> + * <li><b>default</b> (does not have to be specified) - output the format in the + * "normal" format.</li> + * </ul> + * Otherwise, this is a class name that implements + * org.apache.util.outputformatter.JisqlFormatter. See the code for more + * information on implementing your own output formatter.</li> + * </ul> + * <p> + * + * </p> + * The included default formatter supports the following command line options: + * <ul> + * <li><b>-noheader</b> do not print the header column info.</li> + * <li><b>-spacer</b> The character to use for "empty" space. This + * defaults to the space character. From mrider - "I added the ability to + * specify the spacer for columns - which used to be the single char ' '. I did + * this because of brain-dead Windows' command line copy/paste. It seems that + * when a line of text ends in space, copy does not copy that space. Which makes + * it difficult to copy/paste into another program. This can probably be ignored + * most of the time."</li> + * <li><b>-w</b>Specifies the maximum field width for a column. By default jisql + * defaults columns to a maximum width of 2048. By specifying a value + * for this jisql with truncate the output of columns that are wider than this + * parameter.</li> + * <li><b>-delimiter</b> Specify a single character delimiter for columns.</li> + * <li><b>-trim</b> trim the spaces from columns.</li> + * <li><b>-nonull</b> print an empty string instead of the word "NULL" + * when there is a null value. + * <li> + * <li><b>-left</b> left justify the output</li> + * <li><b>-debug</b> print debugging information about the result set.</li> + * </ul> + * <p> + * + * </p> + * The include CSV formatter supports the following command line options: + * <ul> + * <li><b>-delimiter</b> specifies the delimiter to use. By default a comma is + * used</li> + * <li><b>-colnames</b> if included then column names are printed as the first + * line of output. By default they are not included</li> + * </ul> + * <p> + * + * </p> + * The included XML formatter does not have any additional output options. + * <p> + * + * </p> + */ +public class Jisql { + private static final String sybaseJConnect6DriverName = "com.sybase.jdbc3.jdbc.SybDriver"; + private static final String sybaseJConnect5DriverName = "com.sybase.jdbc2.jdbc.SybDriver"; + private static final String sybaseJConnect4DriverName = "com.sybase.jdbc.SybDriver"; + private static final String oracleThinDriverName = "oracle.jdbc.driver.OracleDriver"; + private static final String db2AppDriverName = "COM.ibm.db2.jdbc.app.DB2Driver"; + private static final String db2NetDriverName = "COM.ibm.db2.jdbc.net.DB2Driver"; + private static final String cloudscapeDriverName = "COM.cloudscape.core.JDBCDriver"; + private static final String msqlDriverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; + private static final String pointbaseDriverName = "com.pointbase.jdbc.jdbcUniversalDriver"; + private static final String postgresqlDriverName = "org.postgresql.Driver"; + private static final String mySQLConnectJDriverName = "com.mysql.jdbc.Driver"; + private static final String mySQLCauchoDriverName = "com.caucho.jdbc.mysql.Driver"; + + private static final String defaultFormatterClassName = "org.apache.util.outputformatter.DefaultFormatter"; + private static final String csvFormatterClassName = "org.apache.util.outputformatter.CSVFormatter"; + private static final String xmlFormatterClassName = "org.apache.util.outputformatter.XMLFormatter"; + + private String driverName = null; + private String connectString = null; + private String userName = null; + private String password = null; + private String passwordFileName = null; + private String formatterClassName = defaultFormatterClassName; + + private JisqlFormatter formatter = null; + + private Connection connection = null; + private boolean printDebug = false; + private boolean printDriverDetails = false; + private Driver driver = null; + private Properties props = null; + private String inputFileName = null; + private String commandTerminator = "go"; + private String inputQuery = null; + + /** + * Runs Jisql with the command line arguments provided. + * + */ + public static void main(String argv[]) { + Jisql jisql = new Jisql(); + + try { + jisql.parseArgs(argv); + } + catch (Throwable t) { + t.printStackTrace(); + jisql.usage(); + System.exit(1); + } + + try { + jisql.run(); + } + catch (Exception e) { + e.printStackTrace(); + System.exit(1); + } + + System.exit(0); + } + + public void run() throws Exception { + + try { + driver = (Driver) Class.forName(driverName).newInstance(); + props = new Properties(); + + props.put("user", userName); + if (password != null) + props.put("password", password); + + connection = DriverManager.getConnection(connectString, props); + if (printDriverDetails) { + printDriverInfo(); + } + else { + if(connectString.toLowerCase().startsWith("jdbc:mysql") && inputFileName!=null){ + MySQLPLRunner scriptRunner = new MySQLPLRunner(connection, false, true,printDebug); + scriptRunner.setDelimiter(commandTerminator,false); + scriptRunner.runScript(new FileReader(inputFileName)); + }else{ + doIsql(); + } + } + } + catch (SQLException sqle) { + printAllExceptions(sqle); + } + catch (ClassNotFoundException cnfe) { + System.err.println("Cannot find the driver class \"" + driverName + "\" in the current classpath."); + } + catch (InstantiationException ie) { + System.err.println("Cannot instantiate the driver class \"" + driverName + "\""); + ie.printStackTrace(System.err); + } + catch (IllegalAccessException iae) { + System.err.println("Cannot instantiate the driver class \"" + driverName + "\" because of an IllegalAccessException"); + iae.printStackTrace(System.err); + } + finally { + if (connection != null) { + try { + connection.close(); + } + catch (SQLException ignore) { + /* ignored */ + } + } + } + } + + /** + * The main loop for the Jisql program. This method handles the input from + * either a command line or from a file. Output is handled through the + * Formatter. + * + * @throws SQLException + * if an exception occurs. + * + */ + public void doIsql() throws SQLException { + BufferedReader reader = null; + Statement statement = null; + ResultSet resultSet = null; + ResultSetMetaData resultSetMetaData = null; + StringBuffer query = null; + + if (inputFileName != null) { + try { + reader = new BufferedReader(new FileReader(inputFileName)); + } + catch (FileNotFoundException fnfe) { + System.err.println("Unable to open \"" + inputFileName + "\""); + fnfe.printStackTrace(System.err); + return; + } + } + else { + reader = new BufferedReader(new InputStreamReader(System.in)); + } + if(printDebug) + printAllExceptions(connection.getWarnings()); + statement = connection.createStatement(); + connection.clearWarnings(); + String trimmedLine=null; + while (true) { + int linecount = 1; + query = new StringBuffer(); + + try { + if ((inputFileName == null) && (inputQuery == null)) + System.out.print("\nEnter a query:\n"); + + while (true) { + if ((inputFileName == null) && (inputQuery == null)) { + System.out.print(linecount++ + " > "); + System.out.flush(); + } + + String line = null; + if (inputQuery == null) + line = reader.readLine(); + else + line = inputQuery.toString(); + + if (line == null || line.equalsIgnoreCase("quit") || line.equalsIgnoreCase("exit")){ + if ((inputFileName != null) && (inputQuery != null)) { + break; + }else{ + return; + } + } + + if (line.equals("reset")) { + query = new StringBuffer(); + break; + } + trimmedLine=line.trim(); + if (trimmedLine.startsWith("--") ||trimmedLine.length()<1) { + continue; + } + if(connectString.toLowerCase().startsWith("jdbc:oracle") && inputFileName!=null){ + if (trimmedLine.startsWith("/") ||trimmedLine.length()<2) { + continue; + } + } + + if (line.trim().equalsIgnoreCase(commandTerminator) || line.trim().endsWith(commandTerminator)) { + if (line.trim().endsWith(commandTerminator)) { + line = line.substring(0, line.length() - commandTerminator.length()); + query.append("\n"); + query.append(line); + } + break; + } + + query.append("\n"); + query.append(line); + } + + if (query.toString().length() == 0) + continue; + + if (printDebug) + System.out.println("executing: " + query.toString()); + + boolean moreResults = statement.execute(query.toString()); + int rowsAffected = 0; + do { + if(printDebug) + printAllExceptions(statement.getWarnings()); + statement.clearWarnings(); + if (moreResults) { + resultSet = statement.getResultSet(); + if(printDebug) + printAllExceptions(resultSet.getWarnings()); + resultSet.clearWarnings(); + resultSetMetaData = resultSet.getMetaData(); + + formatter.formatHeader(System.out, resultSetMetaData); + formatter.formatData(System.out, resultSet, resultSetMetaData); + formatter.formatFooter(System.out, resultSetMetaData); + + int rowsSelected = statement.getUpdateCount(); + + if (rowsSelected >= 0 && printDebug) { + System.out.println(rowsSelected + " rows affected."); + } + } + else { + rowsAffected = statement.getUpdateCount(); + if (printDebug) + printAllExceptions(statement.getWarnings()); + statement.clearWarnings(); + if (rowsAffected >= 0 && printDebug) { + System.out.println(rowsAffected + " rows affected."); + } + } + + // + // I was having problems with the PostgreSQL driver throwing + // a NullPointerException here so I just catch it and tell + // the loop that it is done if it happens. + // + try { + moreResults = statement.getMoreResults(); + } + catch (NullPointerException npe) { + moreResults = false; + } + } + + while (moreResults || rowsAffected != -1); + } + catch (SQLException sqle) { + printAllExceptions(sqle); + statement.cancel(); + statement.clearWarnings(); + } + catch (Exception e) { + e.printStackTrace(System.err); + } + + if (inputQuery != null) + return; + } + } + + /** + * Prints some information about the JDBC driver in use. + * + * @throws SQLException + * if one of the methods called does. + * + */ + private void printDriverInfo() throws SQLException { + System.out.println("driver.getMajorVersion() is " + driver.getMajorVersion()); + System.out.println("driver.getMinorVersion() is " + driver.getMinorVersion()); + System.out.println("driver is " + (driver.jdbcCompliant() ? "" : "not ") + "JDBC compliant"); + + DriverPropertyInfo info[] = driver.getPropertyInfo(connectString, props); + + for (int i = 0; i < info.length; i++) { + System.out.println("driver property named \"" + info[i].name + "\""); + if (info[i].choices != null) { + System.out.println("choices:"); + for (int j = 0; j < info[i].choices.length; j++) + System.out.println("\tchoice " + j + ": \"" + info[i].choices[j] + "\""); + } + System.out.println("description: \"" + info[i].description + "\""); + System.out.println("required parameter?: \"" + info[i].required + "\""); + System.out.println("current value: \"" + info[i].value + "\"\n"); + } + + DatabaseMetaData metaData = connection.getMetaData(); + + System.out.println("metaData.getDatabaseProductName(): \"" + metaData.getDatabaseProductName() + "\""); + System.out.println("metaData.getDatabaseProductVersion(): \"" + metaData.getDatabaseProductVersion() + "\""); + + System.out.println("metaData.getDriverName(): \"" + metaData.getDriverName() + "\""); + System.out.println("metaData.getDriverVersion(): \"" + metaData.getDriverVersion() + "\""); + } + + /** + * Parse the command line arguments. This method parses what is needed for + * the Jisql driver program and lets the configured formatter do the same. + * + * @param argv the command line arguments. + * + * @throws Exception if there are any errors parsing the command line arguments. + * + */ + public void parseArgs(String argv[]) throws Throwable { + // + // I'm sure that there has to be a better way but I couldn't find a + // command lineparser that would let me ignore unknown arguments. so + // walk through the list once to find the formatter. then, use the + // command line parser to do it "for real" + // + for (int argumentIndex = 0; argumentIndex < argv.length; argumentIndex++) { + if (argv[argumentIndex].equals("-formatter")) { + formatterClassName = argv[argumentIndex + 1]; + break; + } + } + + if (formatterClassName.compareToIgnoreCase("csv") == 0) + formatterClassName = csvFormatterClassName; + else if (formatterClassName.compareToIgnoreCase("xml") == 0) + formatterClassName = xmlFormatterClassName; + else if (formatterClassName.compareToIgnoreCase("default") == 0) + formatterClassName = defaultFormatterClassName; + + formatter = (JisqlFormatter) Class.forName(formatterClassName).newInstance(); + + OptionParser parser = new OptionParser(); + parser.posixlyCorrect(false); + + parser.accepts("c").withRequiredArg().ofType(String.class); + parser.accepts("cstring").withRequiredArg().ofType(String.class); + parser.accepts("debug"); + parser.accepts("driver").withRequiredArg().ofType(String.class); + parser.accepts("driverinfo"); + parser.accepts("formatter").withRequiredArg().ofType(String.class); + parser.accepts("help"); + parser.accepts("input").withRequiredArg().ofType(String.class); + parser.accepts("password").withOptionalArg().ofType(String.class); + parser.accepts("p").withOptionalArg().ofType(String.class); + parser.accepts("pf").withRequiredArg().ofType(String.class); + parser.accepts("query").withRequiredArg().ofType(String.class); + parser.accepts("user").withRequiredArg().ofType(String.class); + parser.accepts("u").withRequiredArg().ofType(String.class); + + formatter.setSupportedOptions(parser); + + OptionSet options = parser.parse(argv); + + if (options.has("help")) { + usage(); + System.exit(1); + } + + if (options.has("driver")) { + driverName = (String) options.valueOf("driver"); + + if (driverName.compareToIgnoreCase("jconnect4") == 0) + driverName = sybaseJConnect4DriverName; + else if (driverName.compareToIgnoreCase("jconnect5") == 0) + driverName = sybaseJConnect5DriverName; + else if (driverName.compareToIgnoreCase("jconnect6") == 0) + driverName = sybaseJConnect6DriverName; + else if (driverName.compareToIgnoreCase("oraclethin") == 0) + driverName = oracleThinDriverName; + else if (driverName.compareToIgnoreCase("db2app") == 0) + driverName = db2AppDriverName; + else if (driverName.compareToIgnoreCase("db2net") == 0) + driverName = db2NetDriverName; + else if (driverName.compareToIgnoreCase("cloudscape") == 0) + driverName = cloudscapeDriverName; + else if (driverName.compareToIgnoreCase("mssql") == 0) + driverName = msqlDriverName; + else if (driverName.compareToIgnoreCase("pointbase") == 0) + driverName = pointbaseDriverName; + else if (driverName.compareToIgnoreCase("postgresql") == 0) + driverName = postgresqlDriverName; + else if (driverName.compareToIgnoreCase("mysqlconj") == 0) + driverName = mySQLConnectJDriverName; + else if (driverName.compareToIgnoreCase("mysqlcaucho") == 0) + driverName = mySQLCauchoDriverName; + } + + connectString = (String) options.valueOf("cstring"); + + if (options.has("c")) + commandTerminator = (String) options.valueOf("c"); + + if (options.has("debug")) + printDebug = true; + + if (options.has("user")) + userName = (String) options.valueOf("user"); + else if (options.has("u")) + userName = (String) options.valueOf("u"); + + if (options.has("password")) + password = (String) options.valueOf("password"); + else if (options.has("p")) + password = (String) options.valueOf("p"); + + if (options.has("driverinfo")) + printDriverDetails = true; + + if (options.has("input")) + inputFileName = (String) options.valueOf("input"); + + if (options.has("pf")) + passwordFileName = (String) options.valueOf("pf"); + + if (options.has("query")) + inputQuery = (String) options.valueOf("query"); + + if (driverName == null) + throw new Exception("driver name must exist"); + + if (connectString == null) + throw new Exception("connect string must exist"); + + if (userName == null) + throw new Exception("user name must exist"); + + if ((password == null) && (passwordFileName == null)) { + Console console = System.console(); + password = new String( console.readPassword("Password (hit enter for no password): ") ); + } + else if (password == null) { + File passwordFile = null; + BufferedReader reader = null; + + passwordFile = new File(passwordFileName); + if (!passwordFile.exists()) + throw new Exception("the password file \"" + passwordFileName + "\" does not exist"); + + if (!passwordFile.isFile()) + throw new Exception("the password file \"" + passwordFileName + "\" is not a normal file"); + + if (!passwordFile.canRead()) + throw new Exception("the password file \"" + passwordFileName + "\" is not readable"); + + try { + reader = new BufferedReader(new FileReader(passwordFile)); + password = reader.readLine().trim(); + } + catch (Exception e) { + throw new Exception("An error occured reading the password file", e); + } + finally { + if (reader != null) { + try { + reader.close(); + } + catch (Exception ignore) { /* ignored */ + } + } + } + } + + formatter.consumeOptions(options); + } + + /** + * Walks through a SQLException and prints out every exception. + * + * @param sqle the Exception to print + * + */ + private void printAllExceptions(SQLException sqle) { + while (sqle != null) { + System.err.println("SQLException : " + "SQL state: " + sqle.getSQLState() + " " + sqle.toString() + " ErrorCode: " + + sqle.getErrorCode()); + sqle = sqle.getNextException(); + } + } + + /** + * Prints out the usage message for the Jisql driver and the configured + * formatter. + * + */ + private void usage() { + System.err.println(); + System.err.println("usage: java " + getClass().getName() + + " -driver driver -cstring connect_string -user|-u username -password|-p password [-pf password_file] " + + "[-c command_term] [-input file_name] [-debug] [-driverinfo] [-formatter formatter]"); + System.err.println("where:"); + System.err + .println("\t-driver specifies the JDBC driver to use. There are several builtin shortcuts - see the docs for details."); + System.err.println("\t-cstring specifies the connection string to use. These are driver specific."); + System.err.println("\t-user specifies a user name to log into a database server with."); + System.err.println("\t-password specifies the user name to log into a database server with."); + System.err.println("\t-pf specifies the name of a file that contains the password to log into a database server with."); + System.err.println("\t The first line of file should contain the password and nothing else."); + System.err.println("\t-c specifies the command terminator. The default is \"" + commandTerminator + "\""); + System.err.println("\t-input specifies a file name to read commands from."); + System.err + .println("\t-query specifies an optional single query to run instead of interacting with the command line or a file."); + System.err.println("\t Note that the command must include a command terminator or the command will hang"); + System.err.println("\t-debug prints to stdout (System.out) debugging information"); + System.err.println("\t-driverinfo prints to stdout (System.out) detailed driver information and then exits"); + System.err + .println("\t-formatter specifies either a class name or a pre-configured output formatter. See the docs for details."); + + if (formatter != null) { + System.err.println("Additional command line arguments of the " + formatter.getClass().getName() + " class are"); + formatter.usage(System.err); + } + + System.err.println(); + } +} http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6781cc9c/jisql/src/main/java/org/apache/util/sql/MaskingThread.java ---------------------------------------------------------------------- diff --git a/jisql/src/main/java/org/apache/util/sql/MaskingThread.java b/jisql/src/main/java/org/apache/util/sql/MaskingThread.java new file mode 100644 index 0000000..e938e69 --- /dev/null +++ b/jisql/src/main/java/org/apache/util/sql/MaskingThread.java @@ -0,0 +1,71 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.util.sql; + + +public class MaskingThread extends Thread +{ + private boolean stop = false; + private String prompt = null; + + /** + * @param thePrompt The prompt displayed to the user + */ + public MaskingThread(String thePrompt) + { + prompt = thePrompt; + } + + /** + * Begin masking until asked to stop. + */ + public void run() + { + while (!stop) + { + try + { + // attempt masking at this rate (refresh every 1 ms.) + sleep(1); + } + catch (InterruptedException iex) + { + iex.printStackTrace(); + } + if (!stop) + { +// +// sd - note what this does. If your prompt is really short +// and your password is really long this won't work - some +// characters will become visible +// + System.out.print( "\r" + prompt + " \r" + prompt ); + } + System.out.flush(); + } + } + + /** + * Instruct the thread to stop masking. + */ + public void stopMasking() + { + stop = true; + } +} http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6781cc9c/jisql/src/main/java/org/apache/util/sql/MySQLPLRunner.java ---------------------------------------------------------------------- diff --git a/jisql/src/main/java/org/apache/util/sql/MySQLPLRunner.java b/jisql/src/main/java/org/apache/util/sql/MySQLPLRunner.java new file mode 100644 index 0000000..dc5de79 --- /dev/null +++ b/jisql/src/main/java/org/apache/util/sql/MySQLPLRunner.java @@ -0,0 +1,345 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.util.sql; + + +import java.io.FileReader; +import java.io.IOException; +import java.io.LineNumberReader; +import java.io.PrintWriter; +import java.io.Reader; +import java.sql.Connection; +import java.sql.Driver; +import java.sql.DriverManager; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.List; +import java.util.Properties; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + + +public class MySQLPLRunner { + + private static final String DEFAULT_DELIMITER = ";"; + private Connection connection; + private boolean stopOnError; + private boolean autoCommit; + private PrintWriter logWriter = new PrintWriter(System.out); + private PrintWriter errorLogWriter = new PrintWriter(System.err); + private String delimiter = DEFAULT_DELIMITER; + private boolean fullLineDelimiter = false; + private static final String DELIMITER_LINE_REGEX = "(?i)delimiter.+"; + private static final String DELIMITER_LINE_SPLIT_REGEX = "(?i)delimiter"; + private boolean printDebug = false; + /** + * Default constructor + */ + public MySQLPLRunner(Connection connection, boolean autoCommit, + boolean stopOnError,boolean printDebug) { + this.connection = connection; + this.autoCommit = autoCommit; + this.stopOnError = stopOnError; + this.printDebug=printDebug; + } + + public void setDelimiter(String delimiter, boolean fullLineDelimiter) { + this.delimiter = delimiter; + this.fullLineDelimiter = fullLineDelimiter; + } + + /** + * Setter for logWriter property + * + * @param logWriter + * - the new value of the logWriter property + */ + public void setLogWriter(PrintWriter logWriter) { + this.logWriter = logWriter; + } + + /** + * Setter for errorLogWriter property + * + * @param errorLogWriter + * - the new value of the errorLogWriter property + */ + public void setErrorLogWriter(PrintWriter errorLogWriter) { + this.errorLogWriter = errorLogWriter; + } + + /** + * Runs an SQL script (read in using the Reader parameter) + * + * @param reader + * - the source of the script + */ + public void runScript(Reader reader) throws IOException, SQLException { + try { + boolean originalAutoCommit = connection.getAutoCommit(); + try { + if (originalAutoCommit != this.autoCommit) { + connection.setAutoCommit(this.autoCommit); + } + runScript(connection, reader); + } finally { + connection.setAutoCommit(originalAutoCommit); + } + } catch (IOException e) { + throw e; + } catch (SQLException e) { + throw e; + } catch (Exception e) { + throw new RuntimeException("Error running script. Cause: " + e, e); + } + } + + /** + * Runs an SQL script (read in using the Reader parameter) using the + * connection passed in + * + * @param conn + * - the connection to use for the script + * @param reader + * - the source of the script + * @throws SQLException + * if any SQL errors occur + * @throws IOException + * if there is an error reading from the Reader + */ + private void runScript(Connection conn, Reader reader) throws IOException, + SQLException { + StringBuffer command = null; + try { + LineNumberReader lineReader = new LineNumberReader(reader); + String line = null; + while ((line = lineReader.readLine()) != null) { + if (command == null) { + command = new StringBuffer(); + } + String trimmedLine = line.trim(); + + if (trimmedLine.startsWith("--")) { + //println(trimmedLine); + } else if (trimmedLine.length() < 1 + || trimmedLine.startsWith("//")) { + // Do nothing + } else if (trimmedLine.length() < 1 + || trimmedLine.startsWith("--")) { + // Do nothing + } else if (!fullLineDelimiter + && trimmedLine.endsWith(getDelimiter()) + || fullLineDelimiter + && trimmedLine.equals(getDelimiter())) { + + + Pattern pattern = Pattern.compile(DELIMITER_LINE_REGEX); + Matcher matcher = pattern.matcher(trimmedLine); + if (matcher.matches()) { + setDelimiter(trimmedLine.split(DELIMITER_LINE_SPLIT_REGEX)[1].trim(), fullLineDelimiter); + continue; + /*line = lineReader.readLine(); + if (line == null) { + break; + } + trimmedLine = line.trim();*/ + } + + if(line!=null && line.endsWith(getDelimiter()) && !DEFAULT_DELIMITER.equalsIgnoreCase(getDelimiter())){ + command.append(line.substring(0, line.lastIndexOf(getDelimiter()))); + }else{ + command.append(line); + } + + command.append(" "); + Statement statement = conn.createStatement(); + if(printDebug) + println(command); + //System.out.println(getDelimiter()); + boolean hasResults = false; + if (stopOnError) { + hasResults = statement.execute(command.toString()); + } else { + try { + statement.execute(command.toString()); + } catch (SQLException e) { + e.fillInStackTrace(); + printlnError("Error executing: " + command); + printlnError(e); + } + } + + if (autoCommit && !conn.getAutoCommit()) { + conn.commit(); + } + + ResultSet rs = statement.getResultSet(); + if (hasResults && rs != null) { + ResultSetMetaData md = rs.getMetaData(); + int cols = md.getColumnCount(); + for (int i = 0; i < cols; i++) { + String name = md.getColumnLabel(i); + print(name + "\t"); + } + println(""); + while (rs.next()) { + for (int i = 1; i <= cols; i++) { + String value = rs.getString(i); + print(value + "\t"); + } + println(""); + } + } + + command = null; + try { + if(rs!=null){ + rs.close(); + } + } catch (Exception e) { + e.printStackTrace(); + } + try { + statement.close(); + } catch (Exception e) { + e.printStackTrace(); + // Ignore to workaround a bug in Jakarta DBCP + } + Thread.yield(); + } else { + Pattern pattern = Pattern.compile(DELIMITER_LINE_REGEX); + Matcher matcher = pattern.matcher(trimmedLine); + if (matcher.matches()) { + setDelimiter(trimmedLine.split(DELIMITER_LINE_SPLIT_REGEX)[1].trim(), fullLineDelimiter); + continue; + /*line = lineReader.readLine(); + if (line == null) { + break; + } + trimmedLine = line.trim();*/ + } + command.append(line); + command.append(" "); + } + } + if (!autoCommit) { + conn.commit(); + } + } catch (SQLException e) { + e.fillInStackTrace(); + printlnError("Error executing: " + command); + printlnError(e); + throw e; + } catch (IOException e) { + e.fillInStackTrace(); + printlnError("Error executing: " + command); + printlnError(e); + throw e; + } finally { + conn.rollback(); + flush(); + } + } + + private String getDelimiter() { + return delimiter; + } + + private void print(Object o) { + if (logWriter != null) { + System.out.print(o); + } + } + + private void println(Object o) { + if (logWriter != null) { + logWriter.println(o); + } + } + + private void printlnError(Object o) { + if (errorLogWriter != null) { + errorLogWriter.println(o); + } + } + + private void flush() { + if (logWriter != null) { + logWriter.flush(); + } + if (errorLogWriter != null) { + errorLogWriter.flush(); + } + } + + public static void main(String args[]){ + // Creating object of ScriptRunner class + Connection con = null; + String driverName = "com.mysql.jdbc.Driver"; + Properties props = null; + try { + Driver driver = (Driver) Class.forName(driverName).newInstance(); + props = new Properties(); + + props.put("user", "root"); + + props.put("password", "root"); + String connectString = "jdbc:mysql://localhost:3306/ranger"; + con = DriverManager.getConnection(connectString, props); + + + MySQLPLRunner scriptRunner = new MySQLPLRunner(con, false, true,true); + String aSQLScriptFilePath = "/disk1/zero/jisql-2.0.11/xa_core_db.sql"; + + + // Executing SQL Script + scriptRunner.runScript(new FileReader(aSQLScriptFilePath)); + + + } + catch (SQLException sqle) { + sqle.printStackTrace(); + } + catch (ClassNotFoundException cnfe) { + System.err.println("Cannot find the driver class \"" + driverName + "\" in the current classpath."); + } + catch (InstantiationException ie) { + System.err.println("Cannot instantiate the driver class \"" + driverName + "\""); + ie.printStackTrace(System.err); + } + catch (IllegalAccessException iae) { + System.err.println("Cannot instantiate the driver class \"" + driverName + "\" because of an IllegalAccessException"); + iae.printStackTrace(System.err); + }catch (Exception sqle) { + sqle.printStackTrace(); + } + finally { + if (con != null) { + try { + con.close(); + } + catch (SQLException ignore) { + /* ignored */ + } + } + } + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6781cc9c/pom.xml ---------------------------------------------------------------------- diff --git a/pom.xml b/pom.xml index 9e4e151..bf6b1d3 100644 --- a/pom.xml +++ b/pom.xml @@ -74,6 +74,7 @@ </mailingLists> <modules> + <module>jisql</module> <module>agents-audit</module> <module>agents-common</module> <module>agents-cred</module> http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6781cc9c/src/main/assembly/admin-web.xml ---------------------------------------------------------------------- diff --git a/src/main/assembly/admin-web.xml b/src/main/assembly/admin-web.xml index 6eb417b..e7cbf3a 100644 --- a/src/main/assembly/admin-web.xml +++ b/src/main/assembly/admin-web.xml @@ -102,7 +102,28 @@ <include>org.apache.ranger:credentialbuilder</include> </includes> </moduleSet> - +<moduleSet> + <binaries> + <includeDependencies>false</includeDependencies> + <outputDirectory>/jisql/lib</outputDirectory> + <unpack>false</unpack> + <directoryMode>755</directoryMode> + <fileMode>644</fileMode> + <dependencySets> + <dependencySet> + <outputDirectory>/jisql/lib</outputDirectory> + <unpack>false</unpack> + <includes> + <include>net.sourceforge.javacsv:javacsv</include> + <include>net.sf.jopt-simple:jopt-simple</include> + </includes> + </dependencySet> + </dependencySets> + </binaries> + <includes> + <include>org.apache.ranger:jisql</include> + </includes> + </moduleSet> </moduleSets> <fileSets>
