jvanzyl 01/09/29 21:21:39
Modified: src/tdk/task/org/apache/tdk SQLExec.java
Log:
- first working version of a modified <sql> task will will use
a properties file that maps SQL files to databases. the map is
being created by the Torque tasks and this new task makes sure
that SQL files are inserted into the right database when multiple
databases are used and that the ordering is safe. for example, the
idtable is created before any idtable init sql is inserted.
the new method is working for tambora but everything is still pretty
raw, most of the torque tasks have been heavily modified as i'm trying
to integrate the disparate tasks so that they work together to manage
all things database related in Turbine. all this is being done in
the 2.x tree, and when i'm finished i'll move it into the decoupled
torque.
Revision Changes Path
1.2 +372 -182 jakarta-turbine-tdk/src/tdk/task/org/apache/tdk/SQLExec.java
Index: SQLExec.java
===================================================================
RCS file: /home/cvs/jakarta-turbine-tdk/src/tdk/task/org/apache/tdk/SQLExec.java,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- SQLExec.java 2001/09/26 16:10:24 1.1
+++ SQLExec.java 2001/09/30 04:21:39 1.2
@@ -1,3 +1,5 @@
+package org.apache.tdk.task;
+
/*
* The Apache Software License, Version 1.1
*
@@ -51,49 +53,70 @@
* information on the Apache Software Foundation, please see
* <http://www.apache.org/>.
*/
-
-//package org.apache.tools.ant.taskdefs;
-package org.apache.tdk.task;
-
-import org.apache.tools.ant.*;
-import org.apache.tools.ant.types.*;
-import java.io.*;
+import java.io.BufferedOutputStream;
+import java.io.BufferedReader;
+import java.io.IOException;
+import java.io.InputStreamReader;
+import java.io.File;
+import java.io.FileInputStream;
+import java.io.FileOutputStream;
+import java.io.FileReader;
+import java.io.PrintStream;
+import java.io.StringReader;
+import java.io.Reader;
+import java.util.ArrayList;
+import java.util.Iterator;
import java.util.Enumeration;
+import java.util.Hashtable;
import java.util.StringTokenizer;
import java.util.Vector;
import java.util.Properties;
-import java.util.zip.*;
-import java.sql.*;
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.Driver;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.SQLWarning;
+import java.sql.Statement;
+import org.apache.commons.util.StringUtils;
+import org.apache.tools.ant.AntClassLoader;
+import org.apache.tools.ant.BuildException;
+import org.apache.tools.ant.Project;
+import org.apache.tools.ant.ProjectHelper;
+import org.apache.tools.ant.Task;
+import org.apache.tools.ant.types.EnumeratedAttribute;
+import org.apache.tools.ant.types.Path;
+import org.apache.tools.ant.types.Reference;
/**
- * Reads in a text file containing SQL statements seperated with semicolons
- * and executes it in a given db.
- * Comments may be created with REM -- or //.
+ * This task uses an SQL -> Database map in the form of a properties
+ * file to insert each SQL file listed into its designated database.
*
* @author <a href="mailto:[EMAIL PROTECTED]";>Jeff Martin</a>
- * @author <A href="mailto:[EMAIL PROTECTED]";>Michael McCallum</A>
- * @author <A href="mailto:[EMAIL PROTECTED]";>Tim Stephenson</A>
+ * @author <a href="mailto:[EMAIL PROTECTED]";>Michael McCallum</A>
+ * @author <a href="mailto:[EMAIL PROTECTED]";>Tim Stephenson</A>
+ * @author <a href="mailto:[EMAIL PROTECTED]";>Jason van Zyl</A>
*/
-public class SQLExec extends Task {
-
- static public class DelimiterType extends EnumeratedAttribute {
+public class SQLExec
+ extends Task
+{
+ static public class DelimiterType
+ extends EnumeratedAttribute
+ {
static public final String NORMAL = "normal";
static public final String ROW = "row";
- public String[] getValues() {
+ public String[] getValues()
+ {
return new String[] {NORMAL, ROW};
}
}
-
private int goodSql = 0, totalSql = 0;
-
private Path classpath;
-
private AntClassLoader loader;
- private Vector filesets = new Vector();
-
/**
* Database connection
*/
@@ -130,11 +153,6 @@
private String password = null;
/**
- * SQL input file
- */
- private File srcFile = null;
-
- /**
* SQL input command
*/
private String sqlCommand = "";
@@ -189,14 +207,63 @@
* Encoding to use when reading SQL statements from a file
*/
private String encoding = null;
+
+ /**
+ * Src directory for the files listed in the sqldbmap.
+ */
+ private String srcDir;
+
+ /**
+ * Properties file that maps an individual SQL
+ * file to a database.
+ */
+ private File sqldbmap;
+
+ /**
+ * Set the sqldbmap properties file.
+ */
+ public void setSqlDbMap(File sqldbmap)
+ {
+ this.sqldbmap = sqldbmap;
+ }
+
+ /**
+ * Get the sqldbmap properties file.
+ */
+ public File getSqlDbMap()
+ {
+ return sqldbmap;
+ }
+
+ /**
+ * Set the src directory for the sql files
+ * listed in the sqldbmap file.
+ */
+ public void setSrcDir(String srcDir)
+ {
+ this.srcDir = srcDir;
+ }
+
+ /**
+ * Get the src directory for the sql files
+ * listed in the sqldbmap file.
+ */
+ public String getSrcDir()
+ {
+ return srcDir;
+ }
/**
* Set the classpath for loading the driver.
*/
- public void setClasspath(Path classpath) {
- if (this.classpath == null) {
+ public void setClasspath(Path classpath)
+ {
+ if (this.classpath == null)
+ {
this.classpath = classpath;
- } else {
+ }
+ else
+ {
this.classpath.append(classpath);
}
}
@@ -204,8 +271,10 @@
/**
* Create the classpath for loading the driver.
*/
- public Path createClasspath() {
- if (this.classpath == null) {
+ public Path createClasspath()
+ {
+ if (this.classpath == null)
+ {
this.classpath = new Path(project);
}
return this.classpath.createPath();
@@ -214,59 +283,40 @@
/**
* Set the classpath for loading the driver using the classpath reference.
*/
- public void setClasspathRef(Reference r) {
+ public void setClasspathRef(Reference r)
+ {
createClasspath().setRefid(r);
}
/**
- * Set the name of the sql file to be run.
- */
- public void setSrc(File srcFile) {
- this.srcFile = srcFile;
- }
-
- /**
* Set the sql command to execute
*/
- public void addText(String sql) {
+ public void addText(String sql)
+ {
this.sqlCommand += sql;
}
/**
- * Adds a set of files (nested fileset attribute).
- */
- public void addFileset(FileSet set) {
- filesets.addElement(set);
- }
-
-
- /**
- * Set the sql command to execute
- */
- public Transaction createTransaction() {
- Transaction t = new Transaction();
- transactions.addElement(t);
- return t;
- }
-
- /**
* Set the JDBC driver to be used.
*/
- public void setDriver(String driver) {
+ public void setDriver(String driver)
+ {
this.driver = driver;
}
/**
* Set the DB connection url.
*/
- public void setUrl(String url) {
+ public void setUrl(String url)
+ {
this.url = url;
}
/**
* Set the user name for the DB connection.
*/
- public void setUserid(String userId) {
+ public void setUserid(String userId)
+ {
this.userId = userId;
}
@@ -275,22 +325,24 @@
*
* @param encoding the encoding to use on the files
*/
- public void setEncoding(String encoding) {
+ public void setEncoding(String encoding)
+ {
this.encoding = encoding;
}
-
/**
* Set the password for the DB connection.
*/
- public void setPassword(String password) {
+ public void setPassword(String password)
+ {
this.password = password;
}
/**
* Set the autocommit flag for the DB connection.
*/
- public void setAutocommit(boolean autocommit) {
+ public void setAutocommit(boolean autocommit)
+ {
this.autocommit = autocommit;
}
@@ -300,7 +352,8 @@
* <p>For example, set this to "go" and delimitertype to "ROW" for
* Sybase ASE or MS SQL Server.</p>
*/
- public void setDelimiter(String delimiter) {
+ public void setDelimiter(String delimiter)
+ {
this.delimiter = delimiter;
}
@@ -310,131 +363,204 @@
* terminate the SQL command whereas with row, only a line containing just the
* delimiter is recognized as the end of the command.
*/
- public void setDelimiterType(DelimiterType delimiterType) {
+ public void setDelimiterType(DelimiterType delimiterType)
+ {
this.delimiterType = delimiterType.getValue();
}
/**
* Set the print flag.
*/
- public void setPrint(boolean print) {
+ public void setPrint(boolean print)
+ {
this.print = print;
}
/**
* Set the showheaders flag.
*/
- public void setShowheaders(boolean showheaders) {
+ public void setShowheaders(boolean showheaders)
+ {
this.showheaders = showheaders;
}
/**
* Set the output file.
*/
- public void setOutput(File output) {
+ public void setOutput(File output)
+ {
this.output = output;
}
/**
* Set the rdbms required
*/
- public void setRdbms(String vendor) {
+ public void setRdbms(String vendor)
+ {
this.rdbms = vendor.toLowerCase();
}
/**
* Set the version required
*/
- public void setVersion(String version) {
+ public void setVersion(String version)
+ {
this.version = version.toLowerCase();
}
/**
* Set the action to perform onerror
*/
- public void setOnerror(OnError action) {
+ public void setOnerror(OnError action)
+ {
this.onError = action.getValue();
}
/**
* Load the sql file and then execute it
*/
- public void execute() throws BuildException {
+ public void execute()
+ throws BuildException
+ {
sqlCommand = sqlCommand.trim();
-
- if (srcFile == null && sqlCommand.length()==0 && filesets.isEmpty()) {
- if (transactions.size() == 0) {
- throw new BuildException("Source file or fileset, transactions or
sql statement must be set!", location);
- }
- } else {
- // deal with the filesets
- for (int i=0; i<filesets.size(); i++) {
- FileSet fs = (FileSet) filesets.elementAt(i);
- DirectoryScanner ds = fs.getDirectoryScanner(project);
- File srcDir = fs.getDir(project);
- String[] srcFiles = ds.getIncludedFiles();
-
- // Make a transaction for each file
- for ( int j=0 ; j<srcFiles.length ; j++ ) {
- Transaction t = createTransaction();
- t.setSrc(new File(srcDir, srcFiles[j]));
- }
- }
-
- // Make a transaction group for the outer command
- Transaction t = createTransaction();
- t.setSrc(srcFile);
- t.addText(sqlCommand);
- }
-
- if (driver == null) {
+ if (sqldbmap == null || getSqlDbMap().exists() == false)
+ {
+ throw new BuildException("You haven't provided an sqldbmap, or " +
+ "the one you specified doesn't exist: " + sqldbmap);
+ }
+
+ if (driver == null)
+ {
throw new BuildException("Driver attribute must be set!", location);
}
- if (userId == null) {
+ if (userId == null)
+ {
throw new BuildException("User Id attribute must be set!", location);
}
- if (password == null) {
+ if (password == null)
+ {
throw new BuildException("Password attribute must be set!", location);
}
- if (url == null) {
+ if (url == null)
+ {
throw new BuildException("Url attribute must be set!", location);
+ }
+
+ Properties p = new Properties();
+
+ try
+ {
+ FileInputStream fis = new FileInputStream(getSqlDbMap());
+ p.load(fis);
+ fis.close();
+ }
+ catch (IOException ioe)
+ {
+ throw new BuildException("Cannot open and process the sqldbmap!");
+ }
+
+ Hashtable h = new Hashtable();
+
+ for (Enumeration e = p.propertyNames(); e.hasMoreElements();)
+ {
+ String sqlfile = (String) e.nextElement();
+ String database = p.getProperty(sqlfile);
+
+ ArrayList x = (ArrayList) h.get(database);
+
+ if (x == null)
+ {
+ x = new ArrayList();
+ h.put(database, x);
+ }
+
+ // We want to make sure that the base schemas
+ // are inserted first.
+ if (sqlfile.indexOf("schema.sql") != -1)
+ {
+ x.add(0, sqlfile);
+ }
+ else
+ {
+ x.add(sqlfile);
+ }
}
- if (srcFile != null && !srcFile.exists()) {
- throw new BuildException("Source file does not exist!", location);
+
+ Iterator k = h.keySet().iterator();
+
+ while (k.hasNext())
+ {
+ String db = (String) k.next();
+ ArrayList l = (ArrayList) h.get(db);
+ Iterator j = l.iterator();
+ Vector ts = new Vector();
+ while (j.hasNext())
+ {
+ String s = (String) j.next();
+ Transaction t = new Transaction();
+ t.setSrc(new File(srcDir,s));
+ ts.addElement(t);
+ }
+
+ insertDatabaseSqlFiles(url, db, ts);
}
+ }
+
+ /**
+ * Take the base url, the target database and insert a set of SQL
+ * files into the target database.
+ */
+ private void insertDatabaseSqlFiles(String url, String database, Vector
transactions)
+ {
+ url = StringUtils.replace(url, "@DB@", database);
+ System.out.println("Our new url -> " + url);
+
Driver driverInstance = null;
- // Load the driver using the
- try {
+ try
+ {
Class dc;
- if (classpath != null) {
+ if (classpath != null)
+ {
log( "Loading " + driver + " using AntClassLoader with classpath "
+ classpath,
Project.MSG_VERBOSE );
loader = new AntClassLoader(project, classpath);
dc = loader.loadClass(driver);
}
- else {
+ else
+ {
log("Loading " + driver + " using system loader.",
Project.MSG_VERBOSE);
dc = Class.forName(driver);
}
driverInstance = (Driver) dc.newInstance();
- }catch(ClassNotFoundException e){
- throw new BuildException("Class Not Found: JDBC driver " + driver + "
could not be loaded", location);
- }catch(IllegalAccessException e){
- throw new BuildException("Illegal Access: JDBC driver " + driver + "
could not be loaded", location);
- }catch(InstantiationException e) {
- throw new BuildException("Instantiation Exception: JDBC driver " +
driver + " could not be loaded", location);
+ }
+ catch(ClassNotFoundException e)
+ {
+ throw new BuildException("Class Not Found: JDBC driver " + driver +
+ " could not be loaded", location);
+ }
+ catch(IllegalAccessException e)
+ {
+ throw new BuildException("Illegal Access: JDBC driver " + driver +
+ " could not be loaded", location);
+ }
+ catch(InstantiationException e)
+ {
+ throw new BuildException("Instantiation Exception: JDBC driver " +
+ driver + " could not be loaded", location);
}
- try{
+ try
+ {
log("connecting to " + url, Project.MSG_VERBOSE );
Properties info = new Properties();
info.put("user", userId);
info.put("password", password);
conn = driverInstance.connect(url, info);
- if (conn == null) {
+ if (conn == null)
+ {
// Driver doesn't understand the URL
throw new SQLException("No suitable Driver for "+url);
}
@@ -442,54 +568,75 @@
if (!isValidRdbms(conn)) return;
conn.setAutoCommit(autocommit);
-
statement = conn.createStatement();
-
-
PrintStream out = System.out;
- try {
- if (output != null) {
+ try
+ {
+ if (output != null)
+ {
log("Opening PrintStream to output file " + output,
Project.MSG_VERBOSE);
out = new PrintStream(new BufferedOutputStream(new
FileOutputStream(output)));
}
// Process all transactions
- for (Enumeration e = transactions.elements();
- e.hasMoreElements();) {
-
+ for (Enumeration e = transactions.elements(); e.hasMoreElements();)
+ {
((Transaction) e.nextElement()).runTransaction(out);
- if (!autocommit) {
+ if (!autocommit)
+ {
log("Commiting transaction", Project.MSG_VERBOSE);
conn.commit();
}
}
}
- finally {
- if (out != null && out != System.out) {
+ finally
+ {
+ if (out != null && out != System.out)
+ {
out.close();
}
}
- } catch(IOException e){
- if (!autocommit && conn != null && onError.equals("abort")) {
- try {
+ }
+ catch(IOException e)
+ {
+ if (!autocommit && conn != null && onError.equals("abort"))
+ {
+ try
+ {
conn.rollback();
- } catch (SQLException ex) {}
+ }
+ catch (SQLException ex)
+ {
+ // do nothing.
+ }
}
throw new BuildException(e, location);
- } catch(SQLException e){
- if (!autocommit && conn != null && onError.equals("abort")) {
- try {
+ }
+ catch(SQLException e)
+ {
+ if (!autocommit && conn != null && onError.equals("abort"))
+ {
+ try
+ {
conn.rollback();
- } catch (SQLException ex) {}
+ }
+ catch (SQLException ex)
+ {
+ // do nothing.
+ }
}
throw new BuildException(e, location);
}
- finally {
- try {
- if (statement != null) {
+ finally
+ {
+ try
+ {
+ if (statement != null)
+ {
statement.close();
}
- if (conn != null) {
+ if (conn != null)
+ {
conn.close();
}
}
@@ -500,14 +647,18 @@
" SQL statements executed successfully");
}
- protected void runStatements(Reader reader, PrintStream out) throws
SQLException, IOException {
+ protected void runStatements(Reader reader, PrintStream out)
+ throws SQLException, IOException
+ {
String sql = "";
String line = "";
BufferedReader in = new BufferedReader(reader);
- try{
- while ((line=in.readLine()) != null){
+ try
+ {
+ while ((line=in.readLine()) != null)
+ {
line = line.trim();
line = ProjectHelper.replaceProperties(project, line,
project.getProperties());
@@ -533,48 +684,59 @@
}
// Catch any statements not followed by ;
- if(!sql.equals("")){
+ if(!sql.equals(""))
+ {
execSQL(sql, out);
}
- }catch(SQLException e){
+ }
+ catch(SQLException e)
+ {
throw e;
}
-
}
/**
* Verify if connected to the correct RDBMS
**/
- protected boolean isValidRdbms(Connection conn) {
+ protected boolean isValidRdbms(Connection conn)
+ {
if (rdbms == null && version == null)
+ {
return true;
+ }
- try {
+ try
+ {
DatabaseMetaData dmd = conn.getMetaData();
- if (rdbms != null) {
+ if (rdbms != null)
+ {
String theVendor = dmd.getDatabaseProductName().toLowerCase();
log("RDBMS = " + theVendor, Project.MSG_VERBOSE);
- if (theVendor == null || theVendor.indexOf(rdbms) < 0) {
+ if (theVendor == null || theVendor.indexOf(rdbms) < 0)
+ {
log("Not the required RDBMS: "+rdbms, Project.MSG_VERBOSE);
return false;
}
}
- if (version != null) {
+ if (version != null)
+ {
String theVersion = dmd.getDatabaseProductVersion().toLowerCase();
log("Version = " + theVersion, Project.MSG_VERBOSE);
if (theVersion == null ||
!(theVersion.startsWith(version) ||
- theVersion.indexOf(" " + version) >= 0)) {
+ theVersion.indexOf(" " + version) >= 0))
+ {
log("Not the required version: \""+ version +"\"",
Project.MSG_VERBOSE);
return false;
}
}
}
- catch (SQLException e) {
+ catch (SQLException e)
+ {
// Could not get the required information
log("Failed to obtain required RDBMS information", Project.MSG_ERR);
return false;
@@ -586,33 +748,43 @@
/**
* Exec the sql statement.
*/
- protected void execSQL(String sql, PrintStream out) throws SQLException {
+ protected void execSQL(String sql, PrintStream out) throws SQLException
+ {
// Check and ignore empty statements
if ("".equals(sql.trim())) return;
- try {
+ try
+ {
totalSql++;
- if (!statement.execute(sql)) {
+ if (!statement.execute(sql))
+ {
log(statement.getUpdateCount()+" rows affected",
Project.MSG_VERBOSE);
}
- else {
- if (print) {
+ else
+ {
+ if (print)
+ {
printResults(out);
}
}
SQLWarning warning = conn.getWarnings();
- while(warning!=null){
+ while(warning!=null)
+ {
log(warning + " sql warning", Project.MSG_VERBOSE);
warning=warning.getNextWarning();
}
conn.clearWarnings();
goodSql++;
}
- catch (SQLException e) {
+ catch (SQLException e)
+ {
log("Failed to execute: " + sql, Project.MSG_ERR);
- if (!onError.equals("continue")) throw e;
+ if (!onError.equals("continue"))
+ {
+ throw e;
+ }
log(e.toString(), Project.MSG_ERR);
}
}
@@ -620,17 +792,22 @@
/**
* print any results in the statement.
*/
- protected void printResults(PrintStream out) throws java.sql.SQLException {
+ protected void printResults(PrintStream out) throws java.sql.SQLException
+ {
ResultSet rs = null;
- do {
+ do
+ {
rs = statement.getResultSet();
- if (rs != null) {
+ if (rs != null)
+ {
log("Processing new result set.", Project.MSG_VERBOSE);
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
StringBuffer line = new StringBuffer();
- if (showheaders) {
- for (int col = 1; col < columnCount; col++) {
+ if (showheaders)
+ {
+ for (int col = 1; col < columnCount; col++)
+ {
line.append(md.getColumnName(col));
line.append(",");
}
@@ -638,18 +815,23 @@
out.println(line);
line.setLength(0);
}
- while (rs.next()) {
+ while (rs.next())
+ {
boolean first = true;
- for (int col = 1; col <= columnCount; col++) {
+ for (int col = 1; col <= columnCount; col++)
+ {
String columnValue = rs.getString(col);
- if (columnValue != null) {
+ if (columnValue != null)
+ {
columnValue = columnValue.trim();
}
- if (first) {
+ if (first)
+ {
first = false;
}
- else {
+ else
+ {
line.append(",");
}
line.append(columnValue);
@@ -667,8 +849,10 @@
* Enumerated attribute with the values "continue", "stop" and "abort"
* for the onerror attribute.
*/
- public static class OnError extends EnumeratedAttribute {
- public String[] getValues() {
+ public static class OnError extends EnumeratedAttribute
+ {
+ public String[] getValues()
+ {
return new String[] {"continue", "stop", "abort"};
}
}
@@ -679,25 +863,32 @@
* to be executed using the same JDBC connection and commit
* operation in between.
*/
- public class Transaction {
+ public class Transaction
+ {
private File tSrcFile = null;
private String tSqlCommand = "";
- public void setSrc(File src) {
+ public void setSrc(File src)
+ {
this.tSrcFile = src;
}
- public void addText(String sql) {
+ public void addText(String sql)
+ {
this.tSqlCommand += sql;
}
- private void runTransaction(PrintStream out) throws IOException,
SQLException {
- if (tSqlCommand.length() != 0) {
+ private void runTransaction(PrintStream out)
+ throws IOException, SQLException
+ {
+ if (tSqlCommand.length() != 0)
+ {
log("Executing commands", Project.MSG_INFO);
runStatements(new StringReader(tSqlCommand), out);
}
- if (tSrcFile != null) {
+ if (tSrcFile != null)
+ {
log("Executing file: " + tSrcFile.getAbsolutePath(),
Project.MSG_INFO);
Reader reader = (encoding == null) ? new FileReader(tSrcFile)
@@ -707,5 +898,4 @@
}
}
}
-
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
