Author: [log in to unmask]
Date: Wed Oct 29 18:59:03 2014
New Revision: 1335
Log:
Add first version of command line tool for importing conditions from text files HPSJAVA-280
Added:
java/trunk/conditions/src/main/java/org/hps/conditions/cli/
java/trunk/conditions/src/main/java/org/hps/conditions/cli/AbstractCommand.java (with props)
java/trunk/conditions/src/main/java/org/hps/conditions/cli/CommandLineTool.java (with props)
java/trunk/conditions/src/main/java/org/hps/conditions/cli/LoadCommand.java (with props)
Modified:
java/trunk/conditions/src/main/java/org/hps/conditions/QueryBuilder.java
Modified: java/trunk/conditions/src/main/java/org/hps/conditions/QueryBuilder.java
=============================================================================
--- java/trunk/conditions/src/main/java/org/hps/conditions/QueryBuilder.java (original)
+++ java/trunk/conditions/src/main/java/org/hps/conditions/QueryBuilder.java Wed Oct 29 18:59:03 2014
@@ -1,16 +1,18 @@
package org.hps.conditions;
+
+import java.util.List;
/**
* This is a static utility class for building SQL queries for the conditions system.
*
* @author Jeremy McCormick <[log in to unmask]>
*/
-final class QueryBuilder {
+public final class QueryBuilder {
private QueryBuilder() {
}
- static String buildSelect(String tableName, int collectionId, String[] fields, String order) {
+ public static String buildSelect(String tableName, int collectionId, String[] fields, String order) {
StringBuffer buff = new StringBuffer();
buff.append("SELECT ");
if (fields == null) {
@@ -32,7 +34,7 @@
return buff.toString();
}
- static String buildUpdate(String tableName, int rowId, String[] fields, Object[] values) {
+ public static String buildUpdate(String tableName, int rowId, String[] fields, Object[] values) {
if (fields.length != values.length)
throw new IllegalArgumentException("The field and value arrays are different lengths.");
StringBuffer buff = new StringBuffer();
@@ -45,7 +47,7 @@
return buff.toString();
}
- static String buildInsert(String tableName, int collectionId, String[] fields, Object[] values) {
+ public static String buildInsert(String tableName, int collectionId, String[] fields, Object[] values) {
if (fields.length != values.length)
throw new IllegalArgumentException("The field and value arrays are different lengths.");
StringBuffer buff = new StringBuffer();
@@ -60,12 +62,31 @@
buff.append(") ");
return buff.toString();
}
+
+ public static String buildInsert(String tableName, int collectionID, List<String> columnNames, List<List<String>> rows) {
+ StringBuffer buff = new StringBuffer();
+ buff.append("INSERT INTO " + tableName + " ( collection_id");
+ for (String column : columnNames) {
+ buff.append(", " + column);
+ }
+ buff.append(" ) VALUES ");
+ for (List<String> values : rows) {
+ buff.append("( ");
+ buff.append(collectionID);
+ for (String value : values) {
+ buff.append(", '" + value + "'");
+ }
+ buff.append("), ");
+ }
+ buff.setLength(buff.length() - 2);
+ return buff.toString();
+ }
+
- static String buildDelete(String tableName, int rowId) {
+ public static String buildDelete(String tableName, int rowId) {
if (rowId <= 0)
throw new IllegalArgumentException("Invalid row ID: " + rowId);
String query = "DELETE FROM " + tableName + " WHERE id = " + rowId;
return query;
- }
-
+ }
}
Added: java/trunk/conditions/src/main/java/org/hps/conditions/cli/AbstractCommand.java
=============================================================================
--- java/trunk/conditions/src/main/java/org/hps/conditions/cli/AbstractCommand.java (added)
+++ java/trunk/conditions/src/main/java/org/hps/conditions/cli/AbstractCommand.java Wed Oct 29 18:59:03 2014
@@ -0,0 +1,78 @@
+package org.hps.conditions.cli;
+
+import org.apache.commons.cli.HelpFormatter;
+import org.apache.commons.cli.Options;
+import org.apache.commons.cli.Parser;
+import org.apache.commons.cli.PosixParser;
+
+/**
+ * This is the API that sub-commands must implement in the conditions command line interface.
+ *
+ * @author Jeremy McCormick <[log in to unmask]>
+ */
+public abstract class AbstractCommand {
+
+ String name;
+ String description;
+ Options options = new Options();
+ Parser parser = new PosixParser();
+ boolean verbose = false;
+
+ /**
+ * Class constructor.
+ * @param name The string that invokes this command.
+ * @param description The description of this command.
+ */
+ AbstractCommand(String name, String description) {
+ this.name = name;
+ this.description = description;
+ }
+
+ /**
+ * Get the name of this command.
+ * @return A String of the name of this command.
+ */
+ String getName() {
+ return this.name;
+ }
+
+ /**
+ * Get the description of this command.
+ * @return A String of the description of this command.
+ */
+ String getDescription() {
+ return this.description;
+ }
+
+ /**
+ * Options for this command.
+ * @return Options object for this command.
+ */
+ Options getOptions() {
+ return options;
+ }
+
+ /**
+ * Print the usage of this sub-command.
+ * @param doExit Whether or not to exit after printing usage.
+ */
+ void printUsage() {
+ HelpFormatter help = new HelpFormatter();
+ help.printHelp(getName(), getOptions());
+ }
+
+ /**
+ * Set whether verbose output is enabled.
+ * @param verbose True to enable verbose output.
+ */
+ void setVerbose(boolean verbose) {
+ this.verbose = verbose;
+ }
+
+ /**
+ * Execute the command with the arguments.
+ * This is the only method that sub-classes must implement.
+ * @param arguments The sub-command's arguments.
+ */
+ abstract void execute(String[] arguments);
+}
Added: java/trunk/conditions/src/main/java/org/hps/conditions/cli/CommandLineTool.java
=============================================================================
--- java/trunk/conditions/src/main/java/org/hps/conditions/cli/CommandLineTool.java (added)
+++ java/trunk/conditions/src/main/java/org/hps/conditions/cli/CommandLineTool.java Wed Oct 29 18:59:03 2014
@@ -0,0 +1,147 @@
+package org.hps.conditions.cli;
+
+import java.io.File;
+import java.util.HashMap;
+import java.util.Map;
+import java.util.logging.Level;
+
+import org.apache.commons.cli.CommandLine;
+import org.apache.commons.cli.HelpFormatter;
+import org.apache.commons.cli.Option;
+import org.apache.commons.cli.Options;
+import org.apache.commons.cli.ParseException;
+import org.apache.commons.cli.PosixParser;
+import org.hps.conditions.DatabaseConditionsManager;
+
+/**
+ * <p>
+ * This class is a command-line tool for performing commands on the conditions database.
+ * It has sub-commands much like the cvs or svn clients. The only current implemented
+ * command is 'load' to import text files, but more will be added.
+ * <p>
+ * Command line options allow a custom connection properties file or XML configuration
+ * to be supplied by the user which will override the default.
+ *
+ * @author Jeremy McCormick <[log in to unmask]>
+ */
+public class CommandLineTool {
+
+ Options options = new Options();
+ Map<String, AbstractCommand> commands = new HashMap<String, AbstractCommand>();
+ PosixParser parser = new PosixParser();
+ static final String CONNECTION_PROPERTIES_RESOURCE = "/org/hps/conditions/config/conditions_dev.properties";
+ static final String XML_CONFIG_RESOURCE = "/org/hps/conditions/config/conditions_dev.xml";
+ DatabaseConditionsManager conditionsManager;
+ boolean verbose = false;
+
+ public static void main(String[] arguments) {
+ CommandLineTool.create().run(arguments);
+ }
+
+ void run(String[] arguments) {
+ try {
+ if (arguments.length == 0) {
+ printUsage();
+ exit(0);
+ }
+
+ CommandLine commandLine = null;
+ try {
+ commandLine = parser.parse(options, arguments, true);
+ } catch (ParseException e) {
+ e.printStackTrace();
+ printUsage();
+ exit(1);
+ }
+
+ if (commandLine.hasOption("h") || commandLine.getArgs().length == 0) {
+ printUsage();
+ exit(0);
+ }
+
+ if (commandLine.hasOption("v")) {
+ verbose = true;
+ }
+
+ setupConditionsManager(commandLine);
+
+ String commandName = commandLine.getArgs()[0];
+
+ AbstractCommand command = commands.get(commandName);
+ if (command == null) {
+ throw new IllegalArgumentException("Unknown command " + commandName);
+ }
+
+ String[] commandArguments = new String[commandLine.getArgs().length - 1];
+ System.arraycopy(commandLine.getArgs(), 1, commandArguments, 0, commandArguments.length);
+
+ command.setVerbose(verbose);
+ command.execute(commandArguments);
+ } catch (Exception e) {
+ e.printStackTrace();
+ } finally {
+ conditionsManager.closeConnection();
+ }
+ }
+
+ private void setupConditionsManager(CommandLine commandLine) {
+ conditionsManager = new DatabaseConditionsManager();
+ if (verbose)
+ conditionsManager.setLogLevel(Level.ALL);
+ else
+ conditionsManager.setLogLevel(Level.WARNING);
+ if (commandLine.hasOption("p")) {
+ File connectionPropertiesFile = new File(commandLine.getOptionValue("p"));
+ if (verbose)
+ System.out.println("using connection properties file " + connectionPropertiesFile.getPath());
+ conditionsManager.setConnectionProperties(connectionPropertiesFile);
+ } else {
+ if (verbose)
+ System.out.println("using connection resource " + CONNECTION_PROPERTIES_RESOURCE);
+ conditionsManager.setConnectionResource(CONNECTION_PROPERTIES_RESOURCE);
+ }
+ if (commandLine.hasOption("x")) {
+ File xmlConfigFile = new File(commandLine.getOptionValue("x"));
+ conditionsManager.configure(xmlConfigFile);
+ if (verbose)
+ System.out.println("using XML config file " + xmlConfigFile.getPath());
+ } else {
+ if (verbose)
+ System.out.println("using XML config resource " + XML_CONFIG_RESOURCE);
+ conditionsManager.configure(XML_CONFIG_RESOURCE);
+ }
+ conditionsManager.register();
+ conditionsManager.openConnection();
+ }
+
+ void printUsage() {
+ HelpFormatter help = new HelpFormatter();
+ StringBuffer s = new StringBuffer();
+ for (String command : commands.keySet()) {
+ s.append(command + '\n');
+ }
+ help.printHelp("CommandLineTool", "Commands:\n" + s.toString(), options, "");
+ }
+
+ void exit(int status) {
+ System.exit(status);
+ }
+
+ void registerCommand(AbstractCommand command) {
+ if (commands.containsKey(command.getName())) {
+ throw new IllegalArgumentException("There is already a command called " + command.getName());
+ }
+ commands.put(command.getName(), command);
+ }
+
+ static CommandLineTool create() {
+ CommandLineTool cli = new CommandLineTool();
+ cli.options.addOption(new Option("h", false, "Print help and exit"));
+ cli.options.addOption(new Option("v", false, "Enable verbose terminal output"));
+ cli.options.addOption(new Option("p", true, "Set the connection properties file"));
+ cli.options.addOption(new Option("x", true, "Set the conditions database XML configuration file"));
+ cli.registerCommand(new LoadCommand());
+ return cli;
+ }
+
+}
Added: java/trunk/conditions/src/main/java/org/hps/conditions/cli/LoadCommand.java
=============================================================================
--- java/trunk/conditions/src/main/java/org/hps/conditions/cli/LoadCommand.java (added)
+++ java/trunk/conditions/src/main/java/org/hps/conditions/cli/LoadCommand.java Wed Oct 29 18:59:03 2014
@@ -0,0 +1,143 @@
+package org.hps.conditions.cli;
+
+import java.io.BufferedReader;
+import java.io.File;
+import java.io.FileReader;
+import java.io.IOException;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.StringTokenizer;
+
+import org.apache.commons.cli.CommandLine;
+import org.apache.commons.cli.Option;
+import org.apache.commons.cli.ParseException;
+import org.hps.conditions.DatabaseConditionsManager;
+import org.hps.conditions.QueryBuilder;
+
+/**
+ * <p>
+ * This is a sub-command to add conditions data using an input text file.
+ * The file should be ASCII text that is tab or space delimited and includes headers with the names of
+ * the database columns. (These must match exactly!) The user must supply a table name as the target
+ * for the SQL insert. An optional collection ID can be supplied, which may not exist already in the table.
+ * Otherwise, the command will fail. By default, the next collection ID will be found by the conditions manager.
+ * <p>
+ * <pre>
+ * java -cp hps-distribution-bin.jar org.hps.conditions.cli.CommandLineTool -p conditions_dev_local.properties \
+ * load -t scratch_svt_gains -f ./scratch_svt_gains.txt -c 1
+ * </pre>
+ *
+ * @author Jeremy McCormick <[log in to unmask]>
+ */
+public class LoadCommand extends AbstractCommand {
+
+ LoadCommand() {
+ super("load", "Load a set of conditions into the database from a text file");
+ this.options.addOption(new Option("t", true, "Set the name of the target table in the database"));
+ this.options.addOption(new Option("c", true, "Set the collection ID of this conditions set"));
+ this.options.addOption(new Option("f", true, "Set the input data file"));
+ }
+
+ @Override
+ public void execute(String[] arguments) {
+ CommandLine commandLine;
+ try {
+ commandLine = parser.parse(options, arguments);
+ } catch (ParseException e) {
+ throw new RuntimeException(e);
+ }
+
+ String fileName = commandLine.getOptionValue("f");
+ if (fileName == null) {
+ throw new IllegalArgumentException("Missing file argument.");
+ }
+
+ String tableName = commandLine.getOptionValue("t");
+ if (tableName == null) {
+ throw new IllegalArgumentException("Missing table name.");
+ }
+
+ DatabaseConditionsManager conditionsManager = DatabaseConditionsManager.getInstance();
+ if (conditionsManager == null) {
+ throw new RuntimeException("The DatabaseConditionsManager was not setup properly.");
+ }
+
+ int collectionID;
+ if (commandLine.getOptionValue("c") != null) {
+ collectionID = Integer.parseInt(commandLine.getOptionValue("c"));
+ if (collectionExists(conditionsManager, tableName, collectionID)) {
+ throw new IllegalArgumentException("The user supplied collection ID " + collectionID + " already exists in this table.");
+ }
+ } else {
+ collectionID = conditionsManager.getNextCollectionID(tableName);
+ }
+
+ List<String> columnNames = new ArrayList<String>();
+ List<List<String>> rows = new ArrayList<List<String>>();
+ parseFile(fileName, columnNames, rows);
+
+ String insertSql = QueryBuilder.buildInsert(tableName, collectionID, columnNames, rows);
+ if (verbose)
+ System.out.println(insertSql);
+ List<Integer> IDs = conditionsManager.updateQuery(insertSql);
+ System.out.println("Inserted " + IDs.size() + " new rows into table " + tableName + " with collection_id " + collectionID);
+ }
+
+ void parseFile(String fileName, List<String> columnNames, List<List<String>> rows) {
+ File inputFile = new File(fileName);
+ BufferedReader reader = null;
+ try {
+ reader = new BufferedReader(new FileReader(inputFile));
+ String headerLine = reader.readLine();
+ if (headerLine == null) {
+ throw new IllegalArgumentException("The file is empty.");
+ }
+ StringTokenizer tokenizer = new StringTokenizer(headerLine, " \t");
+ while (tokenizer.hasMoreTokens()) {
+ columnNames.add(tokenizer.nextToken().trim());
+ }
+ String line = null;
+ while ((line = reader.readLine()) != null) {
+ tokenizer = new StringTokenizer(line, " \t");
+ List<String> row = new ArrayList<String>();
+ while (tokenizer.hasMoreTokens()) {
+ row.add(tokenizer.nextToken().trim());
+ }
+ rows.add(row);
+ }
+ } catch (Exception e) {
+ throw new RuntimeException(e);
+ } finally {
+ if (reader != null) {
+ try {
+ reader.close();
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ }
+ }
+ }
+
+ boolean collectionExists(DatabaseConditionsManager conditionsManager, String tableName, int collectionID) {
+ String sql = "SELECT * FROM " + tableName + " where collection_id = " + collectionID;
+ ResultSet resultSet = conditionsManager.selectQuery(sql);
+ try {
+ resultSet.last();
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ int rowCount = 0;
+ try {
+ rowCount = resultSet.getRow();
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ if (rowCount != 0) {
+ return true;
+ } else {
+ return false;
+ }
+ }
+}
