ant-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bode...@locus.apache.org
Subject cvs commit: jakarta-ant/src/main/org/apache/tools/ant/taskdefs SQLExec.java
Date Fri, 29 Sep 2000 15:51:14 GMT
bodewig     00/09/29 08:51:14

  Modified:    docs     sql.html
               src/main/org/apache/tools/ant AntClassLoader.java
               src/main/org/apache/tools/ant/taskdefs SQLExec.java
  Log:
  - Add new attributes onerror= (continue, stop, abort) to <sql>
  - Add new nested element <transaction> to <sql> which allows executing
    multiple transactions, multiple files on the same connection.
  - Added documentation on sql.html
  - Modified AntClassLoader.java to send most of the "loading class..."
    messages to MSG_DEBUG instead of MSG_VERBOSE. So that it does not
    cluter debugging other tasks.
  - Add new attributes 'rdbms="product"' and 'version="release"'
    if those versions do not match what is returned by the connection, the
    task will be skipped.
  
  Submitted by:	Jose  Alberto Fernandez <JFernandez@viquity.com>
  
  Revision  Changes    Path
  1.3       +83 -0     jakarta-ant/docs/sql.html
  
  Index: sql.html
  ===================================================================
  RCS file: /home/cvs/jakarta-ant/docs/sql.html,v
  retrieving revision 1.2
  retrieving revision 1.3
  diff -u -r1.2 -r1.3
  --- sql.html	2000/09/27 07:42:21	1.2
  +++ sql.html	2000/09/29 15:51:12	1.3
  @@ -10,6 +10,12 @@
   <p>Multiple statements can be set and each statement is delimited from the next use
a semi-colon. Individual lines within the statements can be commented using either -- or //
at the start of the line.</p>
   
   <p>The auto-commit attribute specifies whether auto commit should be turned on or
off whilst executing the statements. If auto-commit is turned on each statement will be executed
and commited. If it is turned off the statements will all be executed as one transaction.</p>
  +
  +<p>The <i>onerror</i> attribute specifies how to preceed when an error
occurs during the execution of one of the statements. 
  +The possible values are: <b>continue</b> execution, only show the error;
  +<b>stop</b> execution and commit transaction;
  +and <b>abort</b> execution and transaction and fail task.
  +
   <h3>Parameters</h3>
   <table border="1" cellpadding="2" cellspacing="0">
   <tr>
  @@ -67,7 +73,49 @@
     <td width="78%" valign="top">Classpath used to load driver</td>
     <td width="10%" valign="top">No (use system classpath)</td>
   </tr>
  +<tr>
  +  <td width="12%" valign="top">onerror</td>
  +  <td width="78%" valign="top">Action to perform when statement fails: continue,
stop, abort</td>
  +  <td width="10%" valign="top">No, default "abort"</td>
  +</tr>
  +<tr>
  +  <td width="12%" valign="top">rdbms</td>
  +  <td width="78%" valign="top">Execute task only if this rdbms</td>
  +  <td width="10%" valign="top">No (no restriction)</td>
  +</tr>
  +<tr>
  +  <td width="12%" valign="top">version</td>
  +  <td width="78%" valign="top">Execute task only if rdbms version match</td>
  +  <td width="10%" valign="top">No (no restriction)</td>
  +</tr>
  +</table>
  +
  +<h3>Parameters specified as nested elements</h3>
  +<h4>transaction</h4>
  +<p>Use nested <code>&lt;transaction&gt;</code> 
  +elements to specify multiple blocks of commands to the executed
  +executed in the same connection but different transactions. This
  +is particularly usefull when there are multiple files to execute
  +on the same schema.</p>
  +<table border="1" cellpadding="2" cellspacing="0">
  +  <tr>
  +    <td valign="top"><b>Attribute</b></td>
  +    <td valign="top"><b>Description</b></td>
  +    <td align="center" valign="top"><b>Required</b></td>
  +  </tr>
  +  <tr>
  +    <td valign="top">src</td>
  +    <td valign="top">File containing sql statements</td>
  +    <td valign="top" align="center">Yes, unless statements enclosed within tags</td>
  +  </tr>
   </table>
  +<h4>classpath</h4>
  +<p><code>Sql</code>'s <em>classpath</em> attribute is a <a
  +href="#path">PATH like structure</a> and can also be set via a nested
  +<em>classpath</em> element. It is used to load the JDBC classes.</p>
  +<p>
  +The 
  +</p>
   
   <h3>Examples</h3>
   <pre><blockquote>&lt;sql
  @@ -114,6 +162,22 @@
   ]]&gt;&lt;/sql&gt;
   </pre></blockquote>
   
  +<p>The following connects to the database given in url as the sa user using the org.database.jdbcDriver
and executes the sql statements contained within the files data1.sql, data2.sql and data3.sql
and then executes the truncate operation on <i>some_other_table</i>.</p>
  +
  +<pre><blockquote>&lt;sql
  +    driver="org.database.jdbcDriver"
  +    url="jdbc:database-url"
  +    userid="sa"
  +    password="pass" &gt;
  +  &lt;transaction  src="data1.sql" /&gt;
  +  &lt;transaction  src="data2.sql" /&gt;
  +  &lt;transaction  src="data3.sql" /&gt;
  +  &lt;transaction&gt;
  +    truncate table some_other_table;
  +  &lt;/transaction&gt;
  +&lt;/sql&gt;
  +</pre></blockquote>
  +
   <p>The following connects to the database given in url as the sa user using the org.database.jdbcDriver
and executes the sql statements contained within the file data.sql, with output piped to outputfile.txt,
searching /some/jdbc.jar as well as the system classpath for the driver class.</p>
   
   <pre><blockquote>&lt;sql
  @@ -128,6 +192,25 @@
   &lt;classpath&gt;
   	&lt;pathelement location="/some/jdbc.jar"&gt;
   &lt;/classpath&gt;
  +&lt;/sql&gt;
  +</pre></blockquote>
  +
  +<p>The following will only execute if the RDBMS is "oracle" and the version starts
with "8.1."</p>
  +
  +<pre><blockquote>&lt;sql
  +    driver="org.database.jdbcDriver"
  +    url="jdbc:database-url"
  +    userid="sa"
  +    password="pass"
  +    src="data.sql"
  +    rdbms="oracle"
  +    version="8.1."
  +    &gt;
  +insert
  +into table some_table
  +values(1,2,3,4);
  +
  +truncate table some_other_table;
   &lt;/sql&gt;
   </pre></blockquote>
   
  
  
  
  1.8       +7 -7      jakarta-ant/src/main/org/apache/tools/ant/AntClassLoader.java
  
  Index: AntClassLoader.java
  ===================================================================
  RCS file: /home/cvs/jakarta-ant/src/main/org/apache/tools/ant/AntClassLoader.java,v
  retrieving revision 1.7
  retrieving revision 1.8
  diff -u -r1.7 -r1.8
  --- AntClassLoader.java	2000/09/27 07:42:22	1.7
  +++ AntClassLoader.java	2000/09/29 15:51:12	1.8
  @@ -168,7 +168,7 @@
        * this loader's classpath.
        */
       public Class forceLoadClass(String classname) throws ClassNotFoundException {
  -        project.log("force loading " + classname, Project.MSG_VERBOSE);
  +        project.log("force loading " + classname, Project.MSG_DEBUG);
           Class theClass = findLoadedClass(classname);
   
           if (theClass == null) {
  @@ -192,7 +192,7 @@
        * this loader's classpath.
        */
       public Class forceLoadSystemClass(String classname) throws ClassNotFoundException {
  -        project.log("force system loading " + classname, Project.MSG_VERBOSE);
  +        project.log("force system loading " + classname, Project.MSG_DEBUG);
           Class theClass = findLoadedClass(classname);
   
           if (theClass == null) {
  @@ -324,21 +324,21 @@
               if (useSystemFirst) {
                   try {
                       theClass = findSystemClass(classname);
  -                    project.log("Class " + classname + " loaded from system loader", Project.MSG_VERBOSE);
  +                    project.log("Class " + classname + " loaded from system loader", Project.MSG_DEBUG);
                   }
                   catch (ClassNotFoundException cnfe) {
                       theClass = findClass(classname);
  -                    project.log("Class " + classname + " loaded from ant loader", Project.MSG_VERBOSE);
  +                    project.log("Class " + classname + " loaded from ant loader", Project.MSG_DEBUG);
                   }
               }
               else {
                   try {
                       theClass = findClass(classname);
  -                    project.log("Class " + classname + " loaded from ant loader", Project.MSG_VERBOSE);
  +                    project.log("Class " + classname + " loaded from ant loader", Project.MSG_DEBUG);
                   }
                   catch (ClassNotFoundException cnfe) {
                       theClass = findSystemClass(classname);
  -                    project.log("Class " + classname + " loaded from system loader", Project.MSG_VERBOSE);
  +                    project.log("Class " + classname + " loaded from system loader", Project.MSG_DEBUG);
                   }
               }
           }
  @@ -400,7 +400,7 @@
        * this loader's classpath.
        */
       public Class findClass(String name) throws ClassNotFoundException {
  -        project.log("Finding class " + name, Project.MSG_VERBOSE);
  +        project.log("Finding class " + name, Project.MSG_DEBUG);
   
           try {
               return findClass(name, classpath);
  
  
  
  1.9       +191 -33   jakarta-ant/src/main/org/apache/tools/ant/taskdefs/SQLExec.java
  
  Index: SQLExec.java
  ===================================================================
  RCS file: /home/cvs/jakarta-ant/src/main/org/apache/tools/ant/taskdefs/SQLExec.java,v
  retrieving revision 1.8
  retrieving revision 1.9
  diff -u -r1.8 -r1.9
  --- SQLExec.java	2000/09/27 09:00:29	1.8
  +++ SQLExec.java	2000/09/29 15:51:13	1.9
  @@ -74,6 +74,8 @@
    */
   public class SQLExec extends Task {
       
  +    private int goodSql = 0, totalSql = 0;
  +
       private Path classpath;
   
       private AntClassLoader loader;
  @@ -124,6 +126,11 @@
       private String sqlCommand = "";
   
       /**
  +     * SQL transactions to perform
  +     */
  +    private Vector transactions = new Vector();
  +
  +    /**
        * Print SQL results.
        */
       private boolean print = false;
  @@ -139,6 +146,21 @@
       private File output = null;
   
       /**
  +     * RDBMS Product needed for this SQL.
  +     **/
  +    private String rdbms = null;
  +
  +    /**
  +     * RDBMS Version needed for this SQL.
  +     **/
  +    private String version = null;
  +
  +    /**
  +     * Action to perform if an error is found
  +     **/
  +    private String onError = "abort";
  +
  +    /**
        * Set the classpath for loading the driver.
        */
       public void setClasspath(Path classpath) {
  @@ -174,13 +196,22 @@
       }
       
       /**
  -     * Set the name of the sql file to be run.
  +     * Set the sql command to execute
        */
       public void addText(String sql) {
           this.sqlCommand += sql;
       }
       
       /**
  +     * 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) {
  @@ -237,14 +268,43 @@
       }
   
       /**
  +     * Set the rdbms required
  +     */
  +    public void setRdbms(String vendor) {
  +        this.rdbms = vendor.toLowerCase();
  +    }
  +
  +    /**
  +     * Set the version required
  +     */
  +    public void setVersion(String version) {
  +        this.version = version.toLowerCase();
  +    }
  +
  +    /**
  +     * Set the action to perform onerror
  +     */
  +    public void setOnerror(OnError action) {
  +        this.onError = action.getValue();
  +    }
  +
  +    /**
        * Load the sql file and then execute it
        */
       public void execute() throws BuildException {
           sqlCommand = sqlCommand.trim();
   
  -        if (srcFile == null && sqlCommand.length() == 0) {
  -            throw new BuildException("Source file or sql statement must be set!", location);
  +        if (srcFile == null && sqlCommand.length() == 0) { 
  +            if (transactions.size() == 0) {
  +                throw new BuildException("Source file, transactions or sql statement must
be set!", location);
  +            }
  +        } else {
  +            // Make a transaction group for the outer command
  +            Transaction t = createTransaction();
  +            t.setSrc(srcFile);
  +            t.addText(sqlCommand);
           }
  +
           if (driver == null) {
               throw new BuildException("Driver attribute must be set!", location);
           }
  @@ -294,31 +354,31 @@
                   throw new SQLException("No suitable Driver for "+url);
               }
   
  +            if (!isValidRdbms(conn)) return;
  +
               conn.setAutoCommit(autocommit);
   
               statement = conn.createStatement();
   
  -            if (sqlCommand.length() != 0) {
  -                runStatements(new StringReader(sqlCommand));
  -            }
  -            
  -            if (srcFile != null) {
  -                runStatements(new FileReader(srcFile));
  -            }
  -
  -            if (!autocommit) {
  -                conn.commit();
  +            // Process all transactions
  +            for (Enumeration e = transactions.elements(); 
  +                 e.hasMoreElements();) {
  +                ((Transaction) e.nextElement()).runTransaction();
  +                if (!autocommit) {
  +                    log("Commiting transaction", Project.MSG_VERBOSE);
  +                    conn.commit();
  +                }
               }
               
           } catch(IOException e){
  -            if (!autocommit && conn != null) {
  +            if (!autocommit && conn != null && onError.equals("abort"))
{
                   try {
                       conn.rollback();
                   } catch (SQLException ex) {}
               }
               throw new BuildException(e, location);
           } catch(SQLException e){
  -            if (!autocommit && conn != null) {
  +            if (!autocommit && conn != null && onError.equals("abort"))
{
                   try {
                       conn.rollback();
                   } catch (SQLException ex) {}
  @@ -337,7 +397,8 @@
               catch (SQLException e) {}
           }
             
  -        log("SQL statements executed successfully", Project.MSG_VERBOSE);
  +        log(goodSql + " of " + totalSql + 
  +            " SQL statements executed successfully");
       }
   
       protected void runStatements(Reader reader) throws SQLException, IOException {
  @@ -350,9 +411,15 @@
               while ((line=in.readLine()) != null){
                   if (line.trim().startsWith("//")) continue;
                   if (line.trim().startsWith("--")) continue;
  -      
  +
                   sql += " " + line;
                   sql = sql.trim();
  +
  +                // SQL defines "--" as a comment to EOL
  +                // and in Oracle it may contain a hint
  +                // so we cannot just remove it, instead we must end it
  +                if (line.indexOf("--") >= 0) sql += "\n";
  +
                   if (sql.endsWith(";")){
                       log("SQL: " + sql, Project.MSG_VERBOSE);
                       execSQL(sql.substring(0, sql.length()-1));
  @@ -365,34 +432,82 @@
                   execSQL(sql);
               }
           }catch(SQLException e){
  -            log("Failed to execute: " + sql, Project.MSG_ERR);
               throw e;
           }
   
       }
    
  -
       /**
  +     * Verify if connected to the correct RDBMS
  +     **/
  +    protected boolean isValidRdbms(Connection conn) {
  +        if (rdbms == null && version == null)
  +            return true;
  +        
  +        try {
  +            DatabaseMetaData dmd = conn.getMetaData();
  +            
  +            if (rdbms != null) {
  +                String theVendor = dmd.getDatabaseProductName().toLowerCase();
  +                
  +                log("RDBMS = " + theVendor, Project.MSG_VERBOSE);
  +                if (theVendor == null || theVendor.indexOf(rdbms) < 0) {
  +                    log("Not the required RDBMS: "+rdbms, Project.MSG_VERBOSE);
  +                    return false;
  +                }
  +            }
  +            
  +            if (version != null) {
  +                String theVersion = dmd.getDatabaseProductVersion().toLowerCase();
  +                
  +                log("Version = " + theVersion, Project.MSG_VERBOSE);
  +                if (theVersion == null || 
  +                    !(theVersion.startsWith(version) || 
  +                      theVersion.indexOf(" " + version) >= 0)) {
  +                    log("Not the required version: \""+ version +"\"", Project.MSG_VERBOSE);
  +                    return false;
  +                }
  +            }
  +        }
  +        catch (SQLException e) {
  +            // Could not get the required information
  +            log("Failed to obtain required RDBMS information", Project.MSG_ERR);
  +            return false;
  +        }
  +        
  +        return true;
  +    }
  +    
  +    /**
        * Exec the sql statement.
        */
       protected void execSQL(String sql) throws SQLException {
  -        if (!statement.execute(sql)) {
  -            log(statement.getUpdateCount()+" rows affected", 
  -                Project.MSG_VERBOSE);
  -        }
  -
  -        if (print) {
  -            printResults();
  +        try {  
  +            totalSql++;
  +            if (!statement.execute(sql)) {
  +                log(statement.getUpdateCount()+" rows affected", 
  +                    Project.MSG_VERBOSE);
  +            }
  +            
  +            if (print) {
  +                printResults();
  +            }
  +            
  +            SQLWarning warning = conn.getWarnings();
  +            while(warning!=null){
  +                log(warning + " sql warning", Project.MSG_VERBOSE);
  +                warning=warning.getNextWarning();
  +            }
  +            conn.clearWarnings();
  +            goodSql++;
           }
  -
  -        SQLWarning warning = conn.getWarnings();
  -        while(warning!=null){
  -            log(warning + " sql warning", Project.MSG_VERBOSE);
  -            warning=warning.getNextWarning();
  +        catch (SQLException e) {
  +            log("Failed to execute: " + sql, Project.MSG_ERR);
  +            if (!onError.equals("continue")) throw e;
  +            log(e.toString(), Project.MSG_ERR);
           }
  -        conn.clearWarnings();
       }
  -
  +    
       /**
        * print any results in the statement.
        */
  @@ -439,4 +554,47 @@
               }
           }
       }
  +
  +    /**
  +     * Enumerated attribute with the values "continue", "stop" and "abort"
  +     * for the onerror attribute.  
  +     */
  +    public static class OnError extends EnumeratedAttribute {
  +        public String[] getValues() {
  +            return new String[] {"continue", "stop", "abort"};
  +        }
  +    }
  +
  +    /**
  +     * Contains the definition of a new transaction element.
  +     * Transactions allow several files or blocks of statements
  +     * to be executed using the same JDBC connection and commit
  +     * operation in between.
  +     */
  +    public class Transaction {
  +        private File tSrcFile = null;
  +        private String tSqlCommand = "";
  +
  +        public void setSrc(File src) {
  +            this.tSrcFile = src;
  +        }
  +
  +        public void addText(String sql) {
  +            this.tSqlCommand += sql;
  +        }
  +
  +        private void runTransaction() throws IOException, SQLException {
  +            if (tSqlCommand.length() != 0) {
  +                log("Executing commands", Project.MSG_VERBOSE);
  +                runStatements(new StringReader(tSqlCommand));
  +            }
  +      
  +            if (tSrcFile != null) {
  +                log("Executing file: " + tSrcFile.getAbsolutePath(), 
  +                    Project.MSG_VERBOSE);
  +                runStatements(new FileReader(tSrcFile));
  +            }
  +        }
  +    }
  +
   }
  
  
  

Mime
View raw message