WebSVN – lars-tiefland.php_share – /symfony/plugins/sfPropelPlugin/lib/vendor/phing/tasks/ext/pdo/PDOSQLExecTask.php

<?php
/*
 *  $Id: CreoleSQLExecTask.php 83 2006-07-07 18:17:00Z mrook $
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the LGPL. For more information please see
 * <http://phing.info>.
 */

require_once 'phing/tasks/ext/pdo/PDOTask.php';
include_once 'phing/system/io/StringReader.php';
include_once 'phing/tasks/ext/pdo/PDOSQLExecFormatterElement.php';

/**
 * Executes a series of SQL statements on a database using PDO.
 *
 * <p>Statements can
 * either be read in from a text file using the <i>src</i> attribute or from 
 * between the enclosing SQL tags.</p>
 * 
 * <p>Multiple statements can be provided, separated by semicolons (or the 
 * defined <i>delimiter</i>). Individual lines within the statements can be 
 * commented using either --, // or REM at the start of the line.</p>
 * 
 * <p>The <i>autocommit</i> 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 committed. If it is turned off the 
 * statements will all be executed as one transaction.</p>
 * 
 * <p>The <i>onerror</i> attribute specifies how to proceed 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.</p>
 *
 * @author    Hans Lellelid <hans@xmpl.org> (Phing)
 * @author    Jeff Martin <jeff@custommonkey.org> (Ant)
 * @author    Michael McCallum <gholam@xtra.co.nz> (Ant)
 * @author    Tim Stephenson <tim.stephenson@sybase.com> (Ant)
 * @package   phing.tasks.ext
 * @version   $Revision: 1.21 $
 */
class PDOSQLExecTask extends PDOTask {

        /**
         * Count of how many statements were executed successfully.
         * @var int
         */
        private $goodSql = 0;

        /**
         * Count of total number of SQL statements.
         * @var int
         */
        private $totalSql = 0;

        const DELIM_ROW = "row";
        const DELIM_NORMAL = "normal";

    /**
     * Database connection
     * @var PDO
     */
    private $conn = null;

    /**
     * Files to load
     * @var array FileSet[]
     */
    private $filesets = array();

    /**
     * Formatter elements.
     * @var array PDOSQLExecFormatterElement[]
     */
    private $formatters = array();

    /**
     * SQL statement
     * @var PDOStatement
     */
    private $statement;

    /**
     * SQL input file
     * @var PhingFile
     */
    private $srcFile;

    /**
     * SQL input command
     * @var string
     */
    private $sqlCommand = "";

    /**
     * SQL transactions to perform
     */
    private $transactions = array();

    /**
     * SQL Statement delimiter (for parsing files)
     * @var string
     */
    private $delimiter = ";";

    /**
     * The delimiter type indicating whether the delimiter will
     * only be recognized on a line by itself
     */
    private $delimiterType = "normal"; // can't use constant just defined

    /**
     * Action to perform if an error is found
     **/
    private $onError = "abort";

    /**
     * Encoding to use when reading SQL statements from a file
     */
    private $encoding = null;

    /**
     * Fetch mode for PDO select queries.
     * @var int
     */
    private $fetchMode;

    /**
     * Set the name of the SQL file to be run.
     * Required unless statements are enclosed in the build file
     */
    public function setSrc(PhingFile $srcFile) {
        $this->srcFile = $srcFile;
    }

    /**
     * Set an inline SQL command to execute. 
     * NB: Properties are not expanded in this text.
     */
    public function addText($sql) {
        $this->sqlCommand .= $sql;
    }

    /**
     * Adds a set of files (nested fileset attribute).
     */
    public function addFileset(FileSet $set) {
        $this->filesets[] = $set;
    }

    /**
     * Creates a new PDOSQLExecFormatterElement for <formatter> element.
     * @return PDOSQLExecFormatterElement
     */
    public function createFormatter()
    {
        $fe = new PDOSQLExecFormatterElement($this);
        $this->formatters[] = $fe;
        return $fe;
    }

    /**
     * Add a SQL transaction to execute
     */
    public function createTransaction() {
        $t = new PDOSQLExecTransaction($this);
        $this->transactions[] = $t;
        return $t;
    }

    /**
     * Set the file encoding to use on the SQL files read in
     *
     * @param encoding the encoding to use on the files
     */
    public function setEncoding($encoding) {
        $this->encoding = $encoding;
    }

    /**
     * Set the statement delimiter.
     *
     * <p>For example, set this to "go" and delimitertype to "ROW" for
     * Sybase ASE or MS SQL Server.</p>
     *
     * @param delimiter
     */
    public function setDelimiter($delimiter)
    {
        $this->delimiter = $delimiter;
    }

    /**
     * Set the Delimiter type for this sql task. The delimiter type takes two
     * values - normal and row. Normal means that any occurence of the delimiter
     * terminate the SQL command whereas with row, only a line containing just
     * the delimiter is recognized as the end of the command.
     *
     * @param string $delimiterType
     */
    public function setDelimiterType($delimiterType)
    {
        $this->delimiterType = $delimiterType;
    }

    /**
     * Action to perform when statement fails: continue, stop, or abort
     * optional; default &quot;abort&quot;
     */
    public function setOnerror($action) {
        $this->onError = $action;
    }

    /**
     * Sets the fetch mode to use for the PDO resultset.
     * @param mixed $mode The PDO fetchmode integer or constant name.
     */
    public function setFetchmode($mode) {
        if (is_numeric($mode)) {
                $this->fetchMode = (int) $mode;
        } else {
                if (defined($mode)) {
                        $this->fetchMode = constant($mode);
                } else {
                        throw new BuildException("Invalid PDO fetch mode specified: " . $mode, $this->getLocation());
                }
        }
    }

    /**
     * Gets a default output writer for this task.
     * @return Writer
     */
    private function getDefaultOutput()
    {
        return new LogWriter($this);
    }

    /**
     * Load the sql file and then execute it
     * @throws BuildException
     */
    public function main()  {

        // Set a default fetchmode if none was specified
        // (We're doing that here to prevent errors loading the class is PDO is not available.)
        if ($this->fetchMode === null) {
                $this->fetchMode = PDO::FETCH_BOTH;
        }

        // Initialize the formatters here.  This ensures that any parameters passed to the formatter
        // element get passed along to the actual formatter object
        foreach($this->formatters as $fe) {
                $fe->prepare();
        }

        $savedTransaction = array();
        for($i=0,$size=count($this->transactions); $i < $size; $i++) {
                $savedTransaction[] = clone $this->transactions[$i];
        }

        $savedSqlCommand = $this->sqlCommand;

        $this->sqlCommand = trim($this->sqlCommand);

        try {
                if ($this->srcFile === null && $this->sqlCommand === ""
                && empty($this->filesets)) {
                        if (count($this->transactions) === 0) {
                                throw new BuildException("Source file or fileset, "
                                . "transactions or sql statement "
                                . "must be set!", $this->location);
                        }
                }

                if ($this->srcFile !== null && !$this->srcFile->exists()) {
                        throw new BuildException("Source file does not exist!", $this->location);
                }

                // deal with the filesets
                foreach($this->filesets as $fs) {
                        $ds = $fs->getDirectoryScanner($this->project);
                        $srcDir = $fs->getDir($this->project);
                        $srcFiles = $ds->getIncludedFiles();
                        // Make a transaction for each file
                        foreach($srcFiles as $srcFile) {
                                $t = $this->createTransaction();
                                $t->setSrc(new PhingFile($srcDir, $srcFile));
                        }
                }

                // Make a transaction group for the outer command
                $t = $this->createTransaction();
                if ($this->srcFile) $t->setSrc($this->srcFile);
                $t->addText($this->sqlCommand);
                $this->conn = $this->getConnection();

                try {

                        $this->statement = null;

                        // Initialize the formatters.
                        $this->initFormatters();

                        try {

                                // Process all transactions
                                for ($i=0,$size=count($this->transactions); $i < $size; $i++) {
                                        if (!$this->isAutocommit()) {
                                                $this->log("Beginning transaction", Project::MSG_VERBOSE);
                                                $this->conn->beginTransaction();
                                        }
                                        $this->transactions[$i]->runTransaction();
                                        if (!$this->isAutocommit()) {
                                                $this->log("Commiting transaction", Project::MSG_VERBOSE);
                                                $this->conn->commit();
                                        }
                                }
                        } catch (Exception $e) {
                                throw $e;
                        }
                } catch (IOException $e) {
                        if (!$this->isAutocommit() && $this->conn !== null && $this->onError == "abort") {
                                try {
                                        $this->conn->rollback();
                                } catch (PDOException $ex) {}
                        }
                        throw new BuildException($e->getMessage(), $this->location);
                } catch (PDOException $e){
                        if (!$this->isAutocommit() && $this->conn !== null && $this->onError == "abort") {
                                try {
                                        $this->conn->rollback();
                                } catch (PDOException $ex) {}
                        }
                        throw new BuildException($e->getMessage(), $this->location);
                }
                        
                // Close the formatters.
                $this->closeFormatters();

                $this->log($this->goodSql . " of " . $this->totalSql .
                " SQL statements executed successfully");

        } catch (Exception $e) {
                $this->transactions = $savedTransaction;
                $this->sqlCommand = $savedSqlCommand;
                throw $e;
        }
        // finally {
        $this->transactions = $savedTransaction;
        $this->sqlCommand = $savedSqlCommand;

    }


    /**
     * read in lines and execute them
     * @throws PDOException, IOException 
     */
    public function runStatements(Reader $reader) {
        $sql = "";
                $line = "";
                $sqlBacklog = "";
                $hasQuery = false;

                $in = new BufferedReader($reader);

                try {
                        while (($line = $in->readLine()) !== null) {
                                $line = trim($line);
                                $line = ProjectConfigurator::replaceProperties($this->project, $line,
                                                $this->project->getProperties());

                                if (StringHelper::startsWith("//", $line) ||
                                        StringHelper::startsWith("--", $line) ||
                                        StringHelper::startsWith("#", $line)) {
                                        continue;
                                }

                                if (strlen($line) > 4
                                                && strtoupper(substr($line,0, 4)) == "REM ") {
                                        continue;
                                }

                                if ($sqlBacklog !== "") {
                                        $sql = $sqlBacklog;
                                        $sqlBacklog = "";
                                }

                                $sql .= " " . $line . "\n";

                                // 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 (strpos($line, "--") !== false) {
                                        $sql .= "\n";
                                }

                                // DELIM_ROW doesn't need this (as far as i can tell)
                                if ($this->delimiterType == self::DELIM_NORMAL) {

                                        $reg = "#((?:\"(?:\\\\.|[^\"])*\"?)+|'(?:\\\\.|[^'])*'?|" . preg_quote($this->delimiter) . ")#";

                                        $sqlParts = preg_split($reg, $sql, 0, PREG_SPLIT_DELIM_CAPTURE);
                                        $sqlBacklog = "";
                                        foreach ($sqlParts as $sqlPart) {
                                                // we always want to append, even if it's a delim (which will be stripped off later)
                                                $sqlBacklog .= $sqlPart;

                                                // we found a single (not enclosed by ' or ") delimiter, so we can use all stuff before the delim as the actual query
                                                if ($sqlPart === $this->delimiter) {
                                                        $sql = $sqlBacklog;
                                                        $sqlBacklog = "";
                                                        $hasQuery = true;
                                                }
                                        }
                                }

                                if ($hasQuery || ($this->delimiterType == self::DELIM_ROW && $line == $this->delimiter)) {
                                        // this assumes there is always a delimter on the end of the SQL statement.
                                        $sql = StringHelper::substring($sql, 0, strlen($sql) - 1 - strlen($this->delimiter));
                                        $this->log("SQL: " . $sql, Project::MSG_VERBOSE);
                                        $this->execSQL($sql);
                                        $sql = "";
                                        $hasQuery = false;
                                }
                        }

                        // Catch any statements not followed by ;
                        if ($sql !== "") {
                                $this->execSQL($sql);
                        }
                } catch (PDOException $e) {
                        throw $e;
                }
    }

    /**
     * Whether the passed-in SQL statement is a SELECT statement.
     * This does a pretty simple match, checking to see if statement starts with
     * 'select' (but not 'select into').
     * 
     * @param string $sql
     * @return boolean Whether specified SQL looks like a SELECT query.
     */
    protected function isSelectSql($sql)
    {
        $sql = trim($sql);
        return (stripos($sql, 'select') === 0 && stripos($sql, 'select into ') !== 0);
    }

    /**
     * Exec the sql statement.
     * @throws PDOException 
     */
    protected function execSQL($sql) {

        // Check and ignore empty statements
        if (trim($sql) == "") {
                return;
        }

        try {
                $this->totalSql++;

                $this->statement = $this->conn->prepare($sql);
                $this->statement->execute();
                $this->log($this->statement->rowCount() . " rows affected", Project::MSG_VERBOSE);

                // only call processResults() for statements that return actual data (such as 'select')
                if ($this->statement->columnCount() > 0)
                {
                        $this->processResults();
                }

                $this->statement->closeCursor();
                $this->statement = null;

                $this->goodSql++;

        } catch (PDOException $e) {
                $this->log("Failed to execute: " . $sql, Project::MSG_ERR);
                if ($this->onError != "continue") {
                        throw new BuildException("Failed to execute SQL", $e);
                }
                $this->log($e->getMessage(), Project::MSG_ERR);
        }
    }

    /**
     * Returns configured PDOResultFormatter objects (which were created from PDOSQLExecFormatterElement objects).
     * @return array PDOResultFormatter[]
     */
    protected function getConfiguredFormatters()
    {
        $formatters = array();
        foreach ($this->formatters as $fe) {
                $formatters[] = $fe->getFormatter();
        }
        return $formatters;
    }

    /**
     * Initialize the formatters.
     */
    protected function initFormatters() {
        $formatters = $this->getConfiguredFormatters();
        foreach ($formatters as $formatter) {
                $formatter->initialize();
        }

    }

    /**
     * Run cleanup and close formatters.
     */
    protected function closeFormatters() {
        $formatters = $this->getConfiguredFormatters();
        foreach ($formatters as $formatter) {
                $formatter->close();
        }
    }

    /**
     * Passes results from query to any formatters.
     * @throw PDOException
     */
    protected function processResults() {

        try {

                $this->log("Processing new result set.", Project::MSG_VERBOSE);

                $formatters = $this->getConfiguredFormatters();

                while ($row = $this->statement->fetch($this->fetchMode)) {
                        foreach ($formatters as $formatter) {
                                $formatter->processRow($row);
                        }
                }

        } catch (Exception $x) {
                $this->log("Error processing reults: " . $x->getMessage(), Project::MSG_ERR);
                foreach ($formatters as $formatter) {
                        $formatter->close();
                }
                throw $x;
        }

    }
}

/**
 * "Inner" class that 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.
 */
class PDOSQLExecTransaction {

        private $tSrcFile = null;
        private $tSqlCommand = "";
        private $parent;

        function __construct($parent)
        {
                // Parent is required so that we can log things ...
                $this->parent = $parent;
        }

        public function setSrc(PhingFile $src)
        {
                $this->tSrcFile = $src;
        }

        public function addText($sql)
        {
                $this->tSqlCommand .= $sql;
        }

    /**
     * @throws IOException, PDOException
     */
    public function runTransaction()
    {
        if (!empty($this->tSqlCommand)) {
                $this->parent->log("Executing commands", Project::MSG_INFO);
                $this->parent->runStatements(new StringReader($this->tSqlCommand));
        }

        if ($this->tSrcFile !== null) {
                $this->parent->log("Executing file: " . $this->tSrcFile->getAbsolutePath(),
                Project::MSG_INFO);
                $reader = new FileReader($this->tSrcFile);
                $this->parent->runStatements($reader);
                $reader->close();
        }
    }
}
Subversion-Projekte lars-tiefland.php_share

(root)/symfony/plugins/sfPropelPlugin/lib/vendor/phing/tasks/ext/pdo/PDOSQLExecTask.php – Revision 1
