[Sommaire] [Imprimer]

   1  <?php
   2  /*

   3   *  $Id: SQLStatementExtractor.php,v 1.5 2004/07/27 23:13:46 hlellelid Exp $

   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://creole.phpdb.org>.

  20   */
  21   
  22  /**

  23   * Static class for extracting SQL statements from a string or file.

  24   *

  25   * @author    Hans Lellelid <hans@xmpl.org>

  26   * @version   $Revision: 1.5 $

  27   * @package   creole.util.sql

  28   */
  29  class SQLStatementExtractor {
  30      
  31      protected static $delimiter = ';';
  32      
  33      /**

  34       * Get SQL statements from file.

  35       * 

  36       * @param string $filename Path to file to read.

  37       * @return array SQL statements

  38       */
  39      public static function extractFile($filename) {
  40          $buffer = file_get_contents($filename);
  41          if ($buffer === false) {
  42             throw new Exception("Unable to read file: " . $filename);
  43          }
  44          return self::extractStatements(self::getLines($buffer));
  45      }
  46      
  47      /**

  48       * Extract statements from string.

  49       * 

  50       * @param string $txt

  51       * @return array

  52       */
  53      public static function extract($buffer) {
  54          return self::extractStatements(self::getLines($buffer));
  55      }
  56      
  57      /**

  58       * Extract SQL statements from array of lines.

  59       *

  60       * @param array $lines Lines of the read-in file.

  61       * @return string

  62       */
  63      protected static function extractStatements($lines) {
  64          
  65          $statements = array();
  66          $sql = "";
  67                 
  68          foreach($lines as $line) {
  69          
  70                  $line = trim($line);
  71                  
  72                  if (self::startsWith("//", $line) || 
  73                      self::startsWith("--", $line) ||
  74                      self::startsWith("#", $line)) {
  75                      continue;
  76                  }
  77                  
  78                  if (strlen($line) > 4 && strtoupper(substr($line,0, 4)) == "REM ") {
  79                      continue;
  80                  }
  81  
  82                  $sql .= " " . $line;
  83                  $sql = trim($sql);
  84  
  85                  // SQL defines "--" as a comment to EOL

  86                  // and in Oracle it may contain a hint

  87                  // so we cannot just remove it, instead we must end it

  88                  if (strpos($line, "--") !== false) {
  89                      $sql .= "\n";
  90                  }
  91      
  92                  if (self::endsWith(self::$delimiter, $sql)) {
  93                      $statements[] = self::substring($sql, 0, strlen($sql)-1 - strlen(self::$delimiter));
  94                      $sql = "";
  95                  }
  96              }
  97          return $statements;           
  98      }
  99      
 100      //

 101      // Some string helper methods

 102      // 

 103      
 104      /**

 105       * Tests if a string starts with a given string.

 106       * @param string $check The substring to check.

 107       * @param string $string The string to check in (haystack).

 108       * @return boolean True if $string starts with $check, or they are equal, or $check is empty.

 109       */
 110      protected static function startsWith($check, $string) {
 111          if ($check === "" || $check === $string) {
 112              return true;
 113          } else {
 114              return (strpos($string, $check) === 0) ? true : false;
 115          }
 116      }
 117      
 118      /**

 119       * Tests if a string ends with a given string.

 120       * @param string $check The substring to check.

 121       * @param string $string The string to check in (haystack).

 122       * @return boolean True if $string ends with $check, or they are equal, or $check is empty.

 123       */
 124      protected static function endsWith($check, $string) {
 125          if ($check === "" || $check === $string) {
 126              return true;
 127          } else {
 128              return (strpos(strrev($string), strrev($check)) === 0) ? true : false;
 129          }
 130      } 
 131  
 132      /**

 133       * a natural way of getting a subtring, php's circular string buffer and strange

 134       * return values suck if you want to program strict as of C or friends 

 135       */
 136      protected static function substring($string, $startpos, $endpos = -1) {
 137          $len    = strlen($string);
 138          $endpos = (int) (($endpos === -1) ? $len-1 : $endpos);
 139          if ($startpos > $len-1 || $startpos < 0) {
 140              trigger_error("substring(), Startindex out of bounds must be 0<n<$len", E_USER_ERROR);
 141          }
 142          if ($endpos > $len-1 || $endpos < $startpos) {
 143              trigger_error("substring(), Endindex out of bounds must be $startpos<n<".($len-1), E_USER_ERROR);
 144          }
 145          if ($startpos === $endpos) {
 146              return (string) $string{$startpos};
 147          } else {
 148              $len = $endpos-$startpos;
 149          }
 150          return substr($string, $startpos, $len+1);
 151      }
 152      
 153      /**

 154       * Convert string buffer into array of lines.

 155       * 

 156       * @param string $filename

 157       * @return array string[] lines of file.

 158       */
 159      protected static function getLines($buffer) {       
 160         $lines = preg_split("/\r?\n|\r/", $buffer);
 161         return $lines;
 162      }
 163      
 164  }