[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /*
   4   *  $Id: PropelSQLExec.php 137 2005-07-14 00:46:58Z hans $
   5   *
   6   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   7   * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   8   * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
   9   * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
  10   * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  11   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  12   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  13   * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  14   * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  15   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  16   * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  17   *
  18   * This software consists of voluntary contributions made by many individuals
  19   * and is licensed under the LGPL. For more information please see
  20   * <http://propel.phpdb.org>.
  21   */
  22  
  23  require_once 'phing/Task.php';
  24  include_once 'creole/Connection.php';
  25  
  26  /**
  27   * Executes all SQL files referenced in the sqldbmap file against their mapped databases.
  28   * 
  29   * This task uses an SQL -> Database map in the form of a properties
  30   * file to insert each SQL file listed into its designated database.
  31   *
  32   * @author Hans Lellelid <hans@xmpl.org>
  33   * @author Dominik del Bondio
  34   * @author Jeff Martin <jeff@custommonkey.org> (Torque)
  35   * @author Michael McCallum <gholam@xtra.co.nz> (Torque)
  36   * @author Tim Stephenson <tim.stephenson@sybase.com> (Torque)
  37   * @author Jason van Zyl <jvanzyl@apache.org> (Torque)
  38   * @author Martin Poeschl <mpoeschl@marmot.at> (Torque)
  39   * @version $Revision: 137 $
  40   * @package propel.phing
  41   */
  42  class PropelSQLExec extends Task {
  43  
  44      private $goodSql = 0;
  45      private $totalSql = 0;
  46  
  47      const DELIM_ROW = "row";
  48      const DELIM_NORMAL = "normal";
  49  
  50      /**
  51       * The delimiter type indicating whether the delimiter will
  52       * only be recognized on a line by itself
  53       */
  54      private $delimiterType = "normal"; // can't use constant just defined
  55          
  56      //private static $delimiterTypes = array(DELIM_NORMAL, DELIM_ROW);
  57      //private static $errorActions = array("continue", "stop", "abort");
  58      
  59      /** Database connection */
  60      private $conn = null;
  61  
  62      /** Autocommit flag. Default value is false */
  63      private $autocommit = false;
  64  
  65      /** SQL statement */
  66      private $statement = null;
  67  
  68      /** DB driver. */
  69      private $driver = null;
  70  
  71      /** DB url. */
  72      private $url = null;
  73  
  74      /** User name. */
  75      private $userId = null;
  76  
  77      /** Password */
  78      private $password = null;
  79  
  80      /** SQL input command */
  81      private $sqlCommand = "";
  82  
  83      /** SQL transactions to perform */
  84      private $transactions = array();
  85  
  86      /** SQL Statement delimiter */
  87      private $delimiter = ";";
  88  
  89      /** Print SQL results. */
  90      private $print = false;
  91  
  92      /** Print header columns. */
  93      private $showheaders = true;
  94  
  95      /** Results Output file. */
  96      private $output = null;
  97  
  98      /** RDBMS Product needed for this SQL. */
  99      private $rdbms = null;
 100  
 101      /** RDBMS Version needed for this SQL. */
 102      private $version = null;
 103  
 104      /** Action to perform if an error is found */
 105      private $onError = "abort";
 106  
 107      /** Encoding to use when reading SQL statements from a file */
 108      private $encoding = null;
 109  
 110      /** Src directory for the files listed in the sqldbmap. */
 111      private $srcDir;
 112  
 113      /** Properties file that maps an individual SQL file to a database. */
 114      private $sqldbmap;
 115  
 116      /**
 117       * Set the sqldbmap properties file.
 118       *
 119       * @param sqldbmap filename for the sqldbmap
 120       */
 121      public function setSqlDbMap($sqldbmap)
 122      {
 123          $this->sqldbmap = $this->project->resolveFile($sqldbmap);
 124      }
 125  
 126      /**
 127       * Get the sqldbmap properties file.
 128       *
 129       * @return filename for the sqldbmap
 130       */
 131      public function getSqlDbMap()
 132      {
 133          return $this->sqldbmap;
 134      }
 135  
 136      /**
 137       * Set the src directory for the sql files listed in the sqldbmap file.
 138       * 
 139       * @param PhingFile $srcDir sql source directory
 140       */
 141      public function setSrcDir(PhingFile $srcDir)
 142      {
 143          $this->srcDir = $srcDir;
 144      }
 145  
 146      /**
 147       * Get the src directory for the sql files listed in the sqldbmap file.
 148       *
 149       * @return PhingFile SQL Source directory
 150       */
 151      public function getSrcDir()
 152      {
 153          return $this->srcDir;
 154      }
 155      
 156      /**
 157       * Set the sql command to execute
 158       *
 159       * @param sql sql command to execute
 160       */
 161      public function addText($sql)
 162      {
 163          $this->sqlCommand .= $sql;
 164      }
 165  
 166      /**
 167       * Set the Creole driver to be used.
 168       *
 169       * @param string $driver driver class name
 170       */
 171      public function setDriver($driver)
 172      {
 173          $this->driver = $driver;
 174      }
 175  
 176      /**
 177       * Set the DB connection url.
 178       *
 179       * @param string $url connection url
 180       */
 181      public function setUrl($url)
 182      {
 183          $this->url = $url;
 184      }
 185  
 186      /**
 187       * Set the user name for the DB connection.
 188       *
 189       * @param string $userId database user
 190       * @deprecated Specify userid in the DSN URL.
 191       */
 192      public function setUserid($userId)
 193      {
 194          $this->userId = $userId;
 195      }   
 196  
 197      /**
 198       * Set the password for the DB connection.
 199       *
 200       * @param string $password database password
 201       * @deprecated Specify password in the DSN URL.
 202       */
 203      public function setPassword($password)
 204      {
 205          $this->password = $password;
 206      }
 207  
 208      /**
 209       * Set the autocommit flag for the DB connection.
 210       *
 211       * @param boolean $autocommit the autocommit flag
 212       */
 213      public function setAutoCommit($autocommit)
 214      {
 215          $this->autocommit = (boolean) $autocommit;
 216      }
 217  
 218      /**
 219       * Set the statement delimiter.
 220       *
 221       * <p>For example, set this to "go" and delimitertype to "ROW" for
 222       * Sybase ASE or MS SQL Server.</p>
 223       *
 224       * @param string $delimiter
 225       */
 226      public function setDelimiter($delimiter)
 227      {
 228          $this->delimiter = $delimiter;
 229      }
 230  
 231      /**
 232       * Set the Delimiter type for this sql task. The delimiter type takes two
 233       * values - normal and row. Normal means that any occurence of the delimiter
 234       * terminate the SQL command whereas with row, only a line containing just
 235       * the delimiter is recognized as the end of the command.
 236       *
 237       * @param string $delimiterType
 238       */
 239      public function setDelimiterType($delimiterType)
 240      {
 241          $this->delimiterType = $delimiterType;
 242      }
 243  
 244      /**
 245       * Set the print flag.
 246       *
 247       * @param boolean $print
 248       */
 249      public function setPrint($print)
 250      {
 251          $this->print = (boolean) $print;
 252      }
 253  
 254      /**
 255       * Set the showheaders flag.
 256       *
 257       * @param boolean $showheaders
 258       */
 259      public function setShowheaders($showheaders)
 260      {
 261          $this->showheaders = (boolean) $showheaders;
 262      }
 263  
 264      /**
 265       * Set the output file.
 266       *
 267       * @param PhingFile $output
 268       */
 269      public function setOutput(PhingFile $output)
 270      {
 271          $this->output = $output;
 272      }   
 273  
 274      /**
 275       * Set the action to perform onerror
 276       *
 277       * @param string $action
 278       */
 279      public function setOnerror($action)
 280      {
 281          $this->onError = $action;
 282      }
 283  
 284      /**
 285       * Load the sql file and then execute it
 286       *
 287       * @throws BuildException
 288       */
 289      public function main()
 290      {
 291          $this->sqlCommand = trim($this->sqlCommand);
 292  
 293          if ($this->sqldbmap === null || $this->getSqlDbMap()->exists() === false) {
 294              throw new BuildException("You haven't provided an sqldbmap, or "
 295                      . "the one you specified doesn't exist: " . $this->sqldbmap->getPath());
 296          }
 297               
 298          if ($this->url === null) {
 299              throw new BuildException("DSN url attribute must be set!");
 300          }                                  
 301          
 302          $map = new Properties();
 303  
 304          try {
 305              $map->load($this->getSqlDbMap());            
 306          } catch (IOException $ioe) {
 307              throw new BuildException("Cannot open and process the sqldbmap!");
 308          }
 309  
 310          $databases = array();
 311  
 312          foreach($map->keys() as $sqlfile) {
 313          
 314              $database = $map->getProperty($sqlfile);
 315              
 316              // Q: already there?
 317              if (!isset($databases[$database])) {
 318              // A: No.
 319                  $databases[$database] = array();
 320              }
 321  
 322              // We want to make sure that the base schemas
 323              // are inserted first.
 324              if (strpos($sqlfile, "schema.sql") !== false) {
 325                  // add to the beginning of the array
 326                  array_unshift($databases[$database], $sqlfile);
 327              } else {
 328                  array_push($databases[$database], $sqlfile);
 329              }
 330          }
 331      
 332          foreach($databases as $db => $files) {        
 333              $transactions = array();
 334              
 335              foreach($files as $fileName) {
 336  
 337                  $file = new PhingFile($this->srcDir, $fileName);
 338  
 339                  if ($file->exists()) {
 340                      $this->log("Executing statements in file: " . $file->__toString());
 341                      $transaction = new PropelSQLExecTransaction($this);
 342                      $transaction->setSrc($file);
 343                      $transactions[] = $transaction;
 344                  } else {
 345                      $this->log("File '" . $file->__toString()
 346                              . "' in sqldbmap does not exist, so skipping it.");
 347                  }
 348              }
 349              $this->insertDatabaseSqlFiles($this->url, $db, $transactions);
 350          }
 351      }
 352  
 353      /**
 354       * Take the base url, the target database and insert a set of SQL
 355       * files into the target database.
 356       *
 357       * @param string $url
 358       * @param string $database
 359       * @param array $transactions
 360       */
 361      private function insertDatabaseSqlFiles($url, $database, $transactions)
 362      {
 363          $url = str_replace("@DB@", $database, $url);
 364          $this->log("Our new url -> " . $url);
 365          
 366          try {        
 367              
 368              $buf = "Database settings:\n"
 369              . " driver: " . ($this->driver ? $this->driver : "(default)" ). "\n"
 370              . " URL: " . $url . "\n"
 371              . ($this->userId ? " user: " . $this->userId . "\n" : "")
 372              . ($this->password ? " password: " . $this->password . "\n" : "");
 373              
 374              $this->log($buf, PROJECT_MSG_VERBOSE);
 375              
 376              $dsn = Creole::parseDSN($url);
 377              
 378              if($this->userId) {
 379                  $dsn["username"] = $this->userId;
 380              }
 381              if ($this->password) {
 382                  $dsn["password"] = $this->password;
 383              }                        
 384              if ($this->driver) {
 385                  Creole::registerDriver($dsn['phptype'], $this->driver);
 386              }
 387              
 388              $this->conn = Creole::getConnection($dsn);
 389  
 390              $this->conn->setAutoCommit($this->autocommit);
 391              $this->statement = $this->conn->createStatement();
 392              
 393              $out = null;
 394              
 395              try {
 396                  if ($this->output !== null) {
 397                      $this->log("Opening PrintStream to output file " . $this->output->__toString(), PROJECT_MSG_VERBOSE);
 398                      $out = new FileWriter($this->output);
 399                  }
 400  
 401                  // Process all transactions
 402                  for ($i=0,$size=count($transactions); $i < $size; $i++) {
 403                      $transactions[$i]->runTransaction($out);
 404                      if (!$this->autocommit) {
 405                          $this->log("Commiting transaction", PROJECT_MSG_VERBOSE);
 406                          $this->conn->commit();
 407                      }
 408                  }
 409              } catch (Exception $e) {
 410                  if ($out) $out->close();
 411              }
 412              
 413          } catch (IOException $e) {
 414          
 415              if (!$this->autocommit && $this->conn !== null && $this->onError == "abort") {
 416                  try {
 417                      $this->conn->rollback();
 418                  } catch (SQLException $ex) {
 419                      // do nothing.
 420                      System::println("Rollback failed.");
 421                  }
 422              }
 423              if ($this->statement) $this->statement->close();
 424              throw new BuildException($e);
 425          } catch (SQLException $e) {
 426              if (!$this->autocommit && $this->conn !== null && $this->onError == "abort") {
 427                  try {
 428                      $this->conn->rollback();
 429                  } catch (SQLException $ex) {
 430                      // do nothing.
 431                      System::println("Rollback failed");
 432                  }
 433              }
 434              if ($this->statement) $this->statement->close();
 435              throw new BuildException($e);
 436          }
 437         
 438             $this->statement->close();
 439  
 440          $this->log($this->goodSql . " of " . $this->totalSql
 441                  . " SQL statements executed successfully");
 442      }
 443  
 444      /**
 445       * Read the statements from the .sql file and execute them.
 446       * Lines starting with '//', '--' or 'REM ' are ignored.
 447       *
 448       * Developer note:  must be public in order to be called from 
 449       * sudo-"inner" class PropelSQLExecTransaction.
 450       *
 451       * @param Reader $reader
 452       * @param $out Optional output stream.
 453       * @throws SQLException
 454       * @throws IOException
 455       */
 456      public function runStatements(Reader $reader, $out = null)            
 457      {
 458          $sql = "";
 459          $line = "";
 460          $sqlBacklog = "";
 461          $hasQuery = false;
 462  
 463          $in = new BufferedReader($reader);
 464  
 465          try {
 466              while (($line = $in->readLine()) !== null) {
 467                  $line = trim($line);
 468                  $line = ProjectConfigurator::replaceProperties($this->project, $line,
 469                          $this->project->getProperties());
 470                  
 471                  if (StringHelper::startsWith("//", $line) || 
 472                      StringHelper::startsWith("--", $line) ||
 473                      StringHelper::startsWith("#", $line)) {
 474                      continue;
 475                  }
 476                  
 477                  if (strlen($line) > 4
 478                          && strtoupper(substr($line,0, 4)) == "REM ") {
 479                      continue;
 480                  }
 481  
 482                  if($sqlBacklog !== "")
 483                  {
 484                      $sql = $sqlBacklog;
 485                      $sqlBacklog = "";
 486                  }
 487  
 488                  $sql .= " " . $line . "\n";
 489  
 490                  // SQL defines "--" as a comment to EOL
 491                  // and in Oracle it may contain a hint
 492                  // so we cannot just remove it, instead we must end it
 493                  if (strpos($line, "--") !== false) {
 494                      $sql .= "\n";
 495                  }
 496  
 497                  // DELIM_ROW doesn't need this (as far as i can tell)
 498                  if($this->delimiterType == self::DELIM_NORMAL) {
 499                  
 500                      $reg = "#((?:\"(?:\\\\.|[^\"])*\"?)+|'(?:\\\\.|[^'])*'?|" . preg_quote($this->delimiter) . ")#";
 501  
 502                      $sqlParts = preg_split($reg, $sql, 0, PREG_SPLIT_DELIM_CAPTURE);
 503                      $sqlBacklog = "";
 504                      foreach($sqlParts as $sqlPart) {
 505                          // we always want to append, even if it's a delim (which will be stripped off later)
 506                          $sqlBacklog .= $sqlPart; 
 507  
 508                          // we found a single (not enclosed by ' or ") delimiter, so we can use all stuff before the delim as the actual query
 509                          if($sqlPart === $this->delimiter) {
 510                              $sql = $sqlBacklog;
 511                              $sqlBacklog = "";
 512                              $hasQuery = true;
 513                          }
 514                      }
 515                  }
 516  
 517                  if ($hasQuery || ($this->delimiterType == self::DELIM_ROW && $line == $this->delimiter)) {
 518                      // this assumes there is always a delimter on the end of the SQL statement.
 519                      $sql = StringHelper::substring($sql, 0, strlen($sql) - 1 - strlen($this->delimiter));
 520                      $this->log("SQL: " . $sql, PROJECT_MSG_VERBOSE);
 521                      $this->execSQL($sql, $out);
 522                      $sql = "";
 523                      $hasQuery = false;
 524                  }
 525              }
 526  
 527              // Catch any statements not followed by ;
 528              if ($sql !== "") {
 529                  $this->execSQL($sql, $out);
 530              }
 531          } catch (SQLException $e) {
 532              throw $e;
 533          }
 534      }
 535  
 536      /**
 537       * Exec the sql statement.
 538       *
 539       * @param sql
 540       * @param out
 541       * @throws SQLException
 542       */
 543      protected function execSQL($sql, $out = null)
 544      {
 545          // Check and ignore empty statements
 546          if (trim($sql) == "") {
 547              return;
 548          }
 549  
 550          try {
 551              $this->totalSql++;
 552              if (!$this->statement->execute($sql)) {
 553                  $this->log($this->statement->getUpdateCount() . " rows affected",
 554                          PROJECT_MSG_VERBOSE);
 555              } else {
 556                  if ($this->print) {
 557                      $this->printResults($out);
 558                  }
 559              }
 560  
 561              $this->goodSql++;
 562              
 563          } catch (SQLException $e) {
 564              $this->log("Failed to execute: " . $sql, PROJECT_MSG_ERR);
 565              if ($this->onError != "continue") {
 566                  throw $e;
 567              }
 568              $this->log($e->getMessage(), PROJECT_MSG_ERR);
 569          }
 570      }
 571  
 572      /**
 573       * print any results in the statement.
 574       *
 575       * @param out
 576       * @throws SQLException
 577       */
 578      protected function printResults($out = null)
 579      {
 580          $rs = null;
 581          
 582          do {
 583              $rs = $this->statement->getResultSet();
 584              
 585              if ($rs !== null) {
 586              
 587                  $this->log("Processing new result set.", PROJECT_MSG_VERBOSE);            
 588      
 589                  $line = "";
 590  
 591                  $colsprinted = false;
 592                  
 593                  while ($rs->next()) {
 594                  
 595                      if (!$colsprinted && $this->showheaders) {
 596                          $first = true;                        
 597                          foreach($this->fields as $fieldName => $ignore) {
 598                              if ($first) $first = false; else $line .= ",";
 599                              $line .= $fieldName;
 600                          }
 601                      } // if show headers
 602                      
 603                      $first = true;
 604                      foreach($rs->fields as $columnValue) {
 605                          
 606                          if ($columnValue != null) {
 607                              $columnValue = trim($columnValue);
 608                          }
 609  
 610                          if ($first) {
 611                              $first = false;
 612                          } else {
 613                              $line .= ",";
 614                          }
 615                          $line .= $columnValue;
 616                      }
 617                      
 618                      if ($out !== null) {
 619                          $out->write($line);
 620                          $out->newLine();
 621                      }
 622                      
 623                      System::println($line);
 624                      $line = "";
 625                  } // while rs->next()
 626              }
 627          } while ($this->statement->getMoreResults());
 628          System::println();
 629          if ($out !== null) $out->newLine();
 630      }
 631  
 632  }
 633  
 634  /**
 635   * "Inner" class that contains the definition of a new transaction element.
 636   * Transactions allow several files or blocks of statements
 637   * to be executed using the same Creole connection and commit
 638   * operation in between.
 639   * @package propel.phing
 640   */
 641  class PropelSQLExecTransaction {
 642  
 643      private $tSrcFile = null;
 644      private $tSqlCommand = "";
 645      private $parent;
 646      
 647      function __construct($parent)
 648      {
 649          // Parent is required so that we can log things ...
 650          $this->parent = $parent;
 651      }
 652      
 653      public function setSrc(PhingFile $src)
 654      {
 655          $this->tSrcFile = $src;
 656      }
 657  
 658      public function addText($sql)
 659      {
 660          $this->tSqlCommand .= $sql;
 661      }
 662  
 663      /**
 664       * @throws IOException, SQLException
 665       */
 666      public function runTransaction($out = null)
 667      {
 668          if (!empty($this->tSqlCommand)) {
 669              $this->parent->log("Executing commands", PROJECT_MSG_INFO);
 670              $this->parent->runStatements($this->tSqlCommand, $out);
 671          }
 672  
 673          if ($this->tSrcFile !== null) {
 674              $this->parent->log("Executing file: " . $this->tSrcFile->getAbsolutePath(),
 675                  PROJECT_MSG_INFO);
 676              $reader = new FileReader($this->tSrcFile);
 677              $this->parent->runStatements($reader, $out);
 678              $reader->close();
 679          }
 680      }
 681  }
 682