[Sommaire] [Imprimer]

   1  <?php
   2  /*
   3   *  $Id: CreoleSQLExecTask.php 3076 2006-12-18 08:52:12Z fabien $
   4   *
   5   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   6   * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   7   * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
   8   * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
   9   * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  10   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  11   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  12   * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  13   * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  14   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  15   * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  16   *
  17   * This software consists of voluntary contributions made by many individuals
  18   * and is licensed under the LGPL. For more information please see
  19   * <http://phing.info>.
  20   */
  21  
  22  require_once 'phing/tasks/ext/CreoleTask.php';
  23  include_once 'phing/system/io/StringReader.php';
  24  
  25  /**
  26   * Executes a series of SQL statements on a database using Creole.
  27   *
  28   * <p>Statements can
  29   * either be read in from a text file using the <i>src</i> attribute or from 
  30   * between the enclosing SQL tags.</p>
  31   * 
  32   * <p>Multiple statements can be provided, separated by semicolons (or the 
  33   * defined <i>delimiter</i>). Individual lines within the statements can be 
  34   * commented using either --, // or REM at the start of the line.</p>
  35   * 
  36   * <p>The <i>autocommit</i> attribute specifies whether auto-commit should be 
  37   * turned on or off whilst executing the statements. If auto-commit is turned 
  38   * on each statement will be executed and committed. If it is turned off the 
  39   * statements will all be executed as one transaction.</p>
  40   * 
  41   * <p>The <i>onerror</i> attribute specifies how to proceed when an error occurs 
  42   * during the execution of one of the statements. 
  43   * The possible values are: <b>continue</b> execution, only show the error;
  44   * <b>stop</b> execution and commit transaction;
  45   * and <b>abort</b> execution and transaction and fail task.</p>
  46   *
  47   * @author    Hans Lellelid <hans@xmpl.org> (Phing)
  48   * @author    Jeff Martin <jeff@custommonkey.org> (Ant)
  49   * @author    Michael McCallum <gholam@xtra.co.nz> (Ant)
  50   * @author    Tim Stephenson <tim.stephenson@sybase.com> (Ant)
  51   * @package   phing.tasks.ext
  52   * @version   $Revision: 1.21 $
  53   */
  54  class CreoleSQLExecTask extends CreoleTask {
  55  
  56      private $goodSql = 0;
  57      private $totalSql = 0;
  58  
  59      const DELIM_ROW = "row";
  60      const DELIM_NORMAL = "normal";
  61  
  62      /**
  63       * Database connection
  64       */
  65      private $conn = null;
  66  
  67      /**
  68       * files to load
  69       */
  70      private $filesets = array();
  71  
  72      /**
  73       * SQL statement
  74       */
  75      private $statement = null;
  76  
  77      /**
  78       * SQL input file
  79       */
  80      private $srcFile = null;
  81  
  82      /**
  83       * SQL input command
  84       */
  85      private $sqlCommand = "";
  86  
  87      /**
  88       * SQL transactions to perform
  89       */
  90      private $transactions = array();
  91  
  92      /**
  93       * SQL Statement delimiter
  94       */
  95      private $delimiter = ";";
  96      
  97      /**
  98       * The delimiter type indicating whether the delimiter will
  99       * only be recognized on a line by itself
 100       */
 101      private $delimiterType = "normal"; // can't use constant just defined
 102      
 103      /**
 104       * Print SQL results.
 105       */
 106      private $print = false;
 107  
 108      /**
 109       * Print header columns.
 110       */
 111      private $showheaders = true;
 112  
 113      /**
 114       * Results Output file.
 115       */
 116      private $output = null;
 117  
 118      
 119      /**
 120       * Action to perform if an error is found
 121       **/
 122      private $onError = "abort";
 123      
 124      /**
 125       * Encoding to use when reading SQL statements from a file
 126       */
 127      private $encoding = null;
 128  
 129      /**
 130       * Append to an existing file or overwrite it?
 131       */
 132      private $append = false;
 133          
 134      /**
 135       * Set the name of the SQL file to be run.
 136       * Required unless statements are enclosed in the build file
 137       */
 138      public function setSrc(PhingFile $srcFile) {       
 139          $this->srcFile = $srcFile;
 140      }
 141      
 142      /**
 143       * Set an inline SQL command to execute. 
 144       * NB: Properties are not expanded in this text.
 145       */
 146      public function addText($sql) {
 147          $this->sqlCommand .= $sql;
 148      }
 149      
 150      /**
 151       * Adds a set of files (nested fileset attribute).
 152       */
 153      public function addFileset(FileSet $set) {
 154          $this->filesets[] = $set;
 155      }
 156  
 157      /**
 158       * Add a SQL transaction to execute
 159       */
 160      public function createTransaction() {
 161          $t = new SQLExecTransaction($this);
 162          $this->transactions[] = $t;
 163          return $t;
 164      }
 165      
 166      /**
 167       * Set the file encoding to use on the SQL files read in
 168       *
 169       * @param encoding the encoding to use on the files
 170       */
 171      public function setEncoding($encoding) {
 172          $this->encoding = $encoding;
 173      }
 174      
 175      /**
 176       * Set the statement delimiter.
 177       *
 178       * <p>For example, set this to "go" and delimitertype to "ROW" for
 179       * Sybase ASE or MS SQL Server.</p>
 180       *
 181       * @param delimiter
 182       */
 183      public function setDelimiter($delimiter)
 184      {
 185          $this->delimiter = $delimiter;
 186      }
 187  
 188      /**
 189       * Set the Delimiter type for this sql task. The delimiter type takes two
 190       * values - normal and row. Normal means that any occurence of the delimiter
 191       * terminate the SQL command whereas with row, only a line containing just
 192       * the delimiter is recognized as the end of the command.
 193       *
 194       * @param string $delimiterType
 195       */
 196      public function setDelimiterType($delimiterType)
 197      {
 198          $this->delimiterType = $delimiterType;
 199      }
 200      
 201      /**
 202       * Set the print flag.
 203       *
 204       * @param boolean $print
 205       */
 206      public function setPrint($print)
 207      {
 208          $this->print = (boolean) $print;
 209      }
 210          
 211      /**
 212       * Print headers for result sets from the 
 213       * statements; optional, default true.
 214       * @param boolean $showheaders
 215       */
 216      public function setShowheaders($showheaders) {
 217          $this->showheaders = (boolean) $showheaders;
 218      }
 219  
 220      /**
 221       * Set the output file; 
 222       * optional, defaults to the console.
 223       * @param PhingFile $output
 224       */
 225      public function setOutput(PhingFile $output) {
 226          $this->output = $output;
 227      }
 228  
 229      /**
 230       * whether output should be appended to or overwrite
 231       * an existing file.  Defaults to false.
 232       * @param $append
 233       */
 234      public function setAppend($append) {
 235          $this->append = (boolean) $append;
 236      }
 237  
 238      
 239      /**
 240       * Action to perform when statement fails: continue, stop, or abort
 241       * optional; default &quot;abort&quot;
 242       */
 243      public function setOnerror($action) {
 244          $this->onError = $action;
 245      }
 246  
 247      /**
 248       * Load the sql file and then execute it
 249       * @throws BuildException
 250       */
 251      public function main()  {
 252              
 253          $savedTransaction = array();
 254          for($i=0,$size=count($this->transactions); $i < $size; $i++) {
 255              $savedTransaction[] = clone $this->transactions[$i];
 256          }
 257          
 258          $savedSqlCommand = $this->sqlCommand;
 259  
 260          $this->sqlCommand = trim($this->sqlCommand);
 261  
 262          try {
 263              if ($this->srcFile === null && $this->sqlCommand === "" 
 264                  && empty($this->filesets)) { 
 265                  if (count($this->transactions) === 0) {
 266                      throw new BuildException("Source file or fileset, "
 267                                               . "transactions or sql statement "
 268                                               . "must be set!", $this->location);
 269                  }
 270              }
 271          
 272              if ($this->srcFile !== null && !$this->srcFile->exists()) {
 273                  throw new BuildException("Source file does not exist!", $this->location);
 274              }
 275  
 276              // deal with the filesets
 277              for ($i = 0,$size=count($this->filesets); $i < $size; $i++) {
 278                  $fs = $this->filesets[$i];
 279                  $ds = $fs->getDirectoryScanner($this->project);
 280                  $srcDir = $fs->getDir($this->project);
 281                  
 282                  $srcFiles = $ds->getIncludedFiles();
 283                  
 284                  // Make a transaction for each file
 285                  for ($j=0, $size=count($srcFiles); $j < $size; $j++) {
 286                      $t = $this->createTransaction();
 287                      $t->setSrc(new PhingFile($srcDir, $srcFiles[$j]));
 288                  }
 289              }
 290              
 291              // Make a transaction group for the outer command
 292              $t = $this->createTransaction();
 293              if ($this->srcFile) $t->setSrc($this->srcFile);
 294              $t->addText($this->sqlCommand);
 295              $this->conn = $this->getConnection();
 296  
 297              try {
 298                  
 299                  $this->statement = $this->conn->createStatement();
 300                  
 301                  $out = null;
 302                  
 303                  try {
 304                      
 305                      if ($this->output !== null) {
 306                          $this->log("Opening output file " . $this->output, PROJECT_MSG_VERBOSE);
 307                          $out = new BufferedWriter(new FileWriter($this->output->getAbsolutePath(), $this->append));
 308                      }
 309                      
 310                      // Process all transactions
 311                      for ($i=0,$size=count($this->transactions); $i < $size; $i++) {
 312                          $this->transactions[$i]->runTransaction($out);
 313                          if (!$this->isAutocommit()) {
 314                              $this->log("Commiting transaction", PROJECT_MSG_VERBOSE);
 315                              $this->conn->commit();
 316                          }
 317                      }
 318                      if ($out) $out->close();
 319                  } catch (Exception $e) {
 320                      if ($out) $out->close();
 321                      throw $e;
 322                  } 
 323              } catch (IOException $e) {
 324                  if (!$this->isAutocommit() && $this->conn !== null && $this->onError == "abort") {
 325                      try {
 326                          $this->conn->rollback();
 327                      } catch (SQLException $ex) {}
 328                  }
 329                  throw new BuildException($e->getMessage(), $this->location);
 330              } catch (SQLException $e){
 331                  if (!$this->isAutocommit() && $this->conn !== null && $this->onError == "abort") {
 332                      try {
 333                          $this->conn->rollback();
 334                      } catch (SQLException $ex) {}
 335                  }
 336                  throw new BuildException($e->getMessage(), $this->location);
 337              }
 338              
 339              $this->log($this->goodSql . " of " . $this->totalSql .
 340                  " SQL statements executed successfully");
 341          } catch (Exception $e) {
 342              $this->transactions = $savedTransaction;
 343              $this->sqlCommand = $savedSqlCommand;
 344              throw $e;
 345          }
 346          // finally {
 347          $this->transactions = $savedTransaction;
 348          $this->sqlCommand = $savedSqlCommand;
 349          
 350      }
 351  
 352  
 353      /**
 354       * read in lines and execute them
 355       * @throws SQLException, IOException 
 356       */
 357      public function runStatements(Reader $reader, $out = null) {
 358          $sql = "";
 359          $line = "";
 360          $in = new BufferedReader($reader);
 361          try {
 362              while (($line = $in->readLine()) !== null) {
 363                  $line = trim($line);
 364                  $line = ProjectConfigurator::replaceProperties($this->project, $line,
 365                          $this->project->getProperties());
 366                  
 367                  if (StringHelper::startsWith("//", $line) || 
 368                      StringHelper::startsWith("--", $line) ||
 369                      StringHelper::startsWith("#", $line)) {
 370                      continue;
 371                  }
 372                  
 373                  if (strlen($line) > 4
 374                          && strtoupper(substr($line,0, 4)) == "REM ") {
 375                      continue;
 376                  }
 377  
 378                  $sql .= " " . $line;
 379                  $sql = trim($sql);
 380  
 381                  // SQL defines "--" as a comment to EOL
 382                  // and in Oracle it may contain a hint
 383                  // so we cannot just remove it, instead we must end it
 384                  if (strpos($line, "--") !== false) {
 385                      $sql .= "\n";
 386                  }
 387  
 388                  if ($this->delimiterType == self::DELIM_NORMAL
 389                          && StringHelper::endsWith($this->delimiter, $sql)
 390                          || $this->delimiterType == self::DELIM_ROW
 391                          && $line == $this->delimiter) {
 392                      $this->log("SQL: " . $sql, PROJECT_MSG_VERBOSE);
 393                      $this->execSQL(StringHelper::substring($sql, 0, strlen($sql) - strlen($this->delimiter) - 1), $out);
 394                      $sql = "";
 395                  }
 396              }
 397  
 398              // Catch any statements not followed by ;
 399              if ($sql !== "") {
 400                  $this->execSQL($sql, $out);
 401              }
 402          } catch (SQLException $e) {
 403              throw new BuildException("Error running statements", $e);
 404          }
 405      }
 406   
 407          
 408      /**
 409       * Exec the sql statement.
 410       * @throws SQLException 
 411       */
 412      protected function execSQL($sql, $out = null) {
 413          // Check and ignore empty statements
 414          if (trim($sql) == "") {
 415              return;
 416          }
 417  
 418          try {
 419              $this->totalSql++;
 420              if (!$this->statement->execute($sql)) {
 421                  $this->log($this->statement->getUpdateCount() . " rows affected", PROJECT_MSG_VERBOSE);
 422              } else {
 423                  if ($this->print) {
 424                      $this->printResults($out);
 425                  }
 426              }
 427              
 428              $this->goodSql++;
 429              
 430          } catch (SQLException $e) {            
 431              $this->log("Failed to execute: " . $sql, PROJECT_MSG_ERR);
 432              if ($this->onError != "continue") {            
 433                  throw new BuildException("Failed to execute SQL", $e);
 434              }
 435              $this->log($e->getMessage(), PROJECT_MSG_ERR);
 436          }
 437      }
 438      
 439      /**
 440       * print any results in the statement.
 441       * @throw SQLException
 442       */
 443      protected function printResults($out = null) {
 444          $lSep = Phing::getProperty('line.separator');
 445          $rs = null;        
 446          do {
 447              $rs = $this->statement->getResultSet();
 448              
 449              if ($rs !== null) {
 450              
 451                  $this->log("Processing new result set.", PROJECT_MSG_VERBOSE);            
 452      
 453                  $line = "";
 454  
 455                  $colsprinted = false;
 456                  
 457                  while ($rs->next()) {
 458                      $fields = $rs->getRow();
 459                      
 460                      if (!$colsprinted && $this->showheaders) {
 461                          $first = true;
 462                          foreach($fields as $fieldName => $ignore) {
 463                              if ($first) $first = false; else $line .= ",";
 464                              $line .= $fieldName;
 465                          }
 466                          if ($out !== null) {
 467                              $out->write($line);
 468                              $out->newLine();
 469                          } else {
 470                              print($line.$lSep);
 471                          }
 472                          $line = "";
 473                          $colsprinted = true;
 474                      } // if show headers
 475                      
 476                      $first = true;
 477                      foreach($fields as $columnValue) {
 478                          
 479                          if ($columnValue != null) {
 480                              $columnValue = trim($columnValue);
 481                          }
 482  
 483                          if ($first) {
 484                              $first = false;
 485                          } else {
 486                              $line .= ",";
 487                          }
 488                          $line .= $columnValue;
 489                      }
 490                      
 491                      if ($out !== null) {
 492                          $out->write($line);
 493                          $out->newLine();
 494                      } else {                    
 495                          print($line . $lSep);
 496                      }
 497                      $line = "";
 498                      
 499                  } // while rs->next()
 500              }
 501          } while ($this->statement->getMoreResults());
 502          print($lSep);
 503          if ($out !== null) $out->newLine();
 504      }
 505  }
 506  
 507  
 508  /**
 509   * "Inner" class that contains the definition of a new transaction element.
 510   * Transactions allow several files or blocks of statements
 511   * to be executed using the same JDBC connection and commit
 512   * operation in between.
 513   */
 514  class SQLExecTransaction {
 515  
 516      private $tSrcFile = null;
 517      private $tSqlCommand = "";
 518      private $parent;
 519      
 520      function __construct($parent)
 521      {
 522          // Parent is required so that we can log things ...
 523          $this->parent = $parent;
 524      }
 525      
 526      public function setSrc(PhingFile $src)
 527      {
 528          $this->tSrcFile = $src;
 529      }
 530  
 531      public function addText($sql)
 532      {
 533          $this->tSqlCommand .= $sql;
 534      }
 535  
 536      /**
 537       * @throws IOException, SQLException
 538       */
 539      public function runTransaction($out = null)
 540      {
 541          if (!empty($this->tSqlCommand)) {
 542              $this->parent->log("Executing commands", PROJECT_MSG_INFO);
 543              $this->parent->runStatements(new StringReader($this->tSqlCommand), $out);
 544          }
 545  
 546          if ($this->tSrcFile !== null) {
 547              $this->parent->log("Executing file: " . $this->tSrcFile->getAbsolutePath(),
 548                  PROJECT_MSG_INFO);
 549              $reader = new FileReader($this->tSrcFile);
 550              $this->parent->runStatements($reader, $out);
 551              $reader->close();
 552          }
 553      }
 554  }
 555  
 556