Subversion-Projekte lars-tiefland.php_share

<?php

/*

 *  $Id: PropelSQLExec.php 1262 2009-10-26 20:54:39Z francois $

 *

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

 */

require_once 'phing/Task.php';

/**

 * Executes all SQL files referenced in the sqldbmap file against their mapped databases.

 *

 * This task uses an SQL -> Database map in the form of a properties

 * file to insert each SQL file listed into its designated database.

 *

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

 * @author     Dominik del Bondio

 * @author     Jeff Martin <jeff@custommonkey.org> (Torque)

 * @author     Michael McCallum <gholam@xtra.co.nz> (Torque)

 * @author     Tim Stephenson <tim.stephenson@sybase.com> (Torque)

 * @author     Jason van Zyl <jvanzyl@apache.org> (Torque)

 * @author     Martin Poeschl <mpoeschl@marmot.at> (Torque)

 * @version    $Revision: 1262 $

 * @package    propel.phing

 */

class PropelSQLExec extends Task {

	private $goodSql = 0;

	private $totalSql = 0;

	const DELIM_ROW = "row";

	const DELIM_NORMAL = "normal";

	/**

	 * 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

	//private static $delimiterTypes = array(DELIM_NORMAL, DELIM_ROW);

	//private static $errorActions = array("continue", "stop", "abort");

	/** PDO Database connection */

	private $conn = null;

	/** Autocommit flag. Default value is false */

	private $autocommit = false;

	/** DB url. */

	private $url = null;

	/** User name. */

	private $userId = null;

	/** Password */

	private $password = null;

	/** SQL input command */

	private $sqlCommand = "";

	/** SQL transactions to perform */

	private $transactions = array();

	/** SQL Statement delimiter */

	private $delimiter = ";";

	/** Print SQL results. */

	private $print = false;

	/** Print header columns. */

	private $showheaders = true;

	/** Results Output file. */

	private $output = null;

	/** RDBMS Product needed for this SQL. */

	private $rdbms = null;

	/** RDBMS Version needed for this SQL. */

	private $version = null;

	/** Action to perform if an error is found */

	private $onError = "abort";

	/** Encoding to use when reading SQL statements from a file */

	private $encoding = null;

	/** Src directory for the files listed in the sqldbmap. */

	private $srcDir;

	/** Properties file that maps an individual SQL file to a database. */

	private $sqldbmap;

	/**

	 * Set the sqldbmap properties file.

	 *

	 * @param      sqldbmap filename for the sqldbmap

	 */

	public function setSqlDbMap($sqldbmap)

	{

		$this->sqldbmap = $this->project->resolveFile($sqldbmap);

	}

	/**

	 * Get the sqldbmap properties file.

	 *

	 * @return     filename for the sqldbmap

	 */

	public function getSqlDbMap()

	{

		return $this->sqldbmap;

	}

	/**

	 * Set the src directory for the sql files listed in the sqldbmap file.

	 *

	 * @param      PhingFile $srcDir sql source directory

	 */

	public function setSrcDir(PhingFile $srcDir)

	{

		$this->srcDir = $srcDir;

	}

	/**

	 * Get the src directory for the sql files listed in the sqldbmap file.

	 *

	 * @return     PhingFile SQL Source directory

	 */

	public function getSrcDir()

	{

		return $this->srcDir;

	}

	/**

	 * Set the sql command to execute

	 *

	 * @param      sql sql command to execute

	 */

	public function addText($sql)

	{

		$this->sqlCommand .= $sql;

	}

	/**

	 * Set the DB connection url.

	 *

	 * @param      string $url connection url

	 */

	public function setUrl($url)

	{

		$this->url = $url;

	}

	/**

	 * Set the user name for the DB connection.

	 *

	 * @param      string $userId database user

	 * @deprecated Specify userid in the DSN URL.

	 */

	public function setUserid($userId)

	{

		$this->userId = $userId;

	}

	/**

	 * Set the password for the DB connection.

	 *

	 * @param      string $password database password

	 * @deprecated Specify password in the DSN URL.

	 */

	public function setPassword($password)

	{

		$this->password = $password;

	}

	/**

	 * Set the autocommit flag for the DB connection.

	 *

	 * @param      boolean $autocommit the autocommit flag

	 */

	public function setAutoCommit($autocommit)

	{

		$this->autocommit = (boolean) $autocommit;

	}

	/**

	 * Set the statement delimiter.

	 *

	 * <p>For example, set this to "go" and delimitertype to "ROW" for

	 * Sybase ASE or MS SQL Server.</p>

	 *

	 * @param      string $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;

	}

	/**

	 * Set the print flag.

	 *

	 * @param      boolean $print

	 */

	public function setPrint($print)

	{

		$this->print = (boolean) $print;

	}

	/**

	 * Set the showheaders flag.

	 *

	 * @param      boolean $showheaders

	 */

	public function setShowheaders($showheaders)

	{

		$this->showheaders = (boolean) $showheaders;

	}

	/**

	 * Set the output file.

	 *

	 * @param      PhingFile $output

	 */

	public function setOutput(PhingFile $output)

	{

		$this->output = $output;

	}

	/**

	 * Set the action to perform onerror

	 *

	 * @param      string $action

	 */

	public function setOnerror($action)

	{

		$this->onError = $action;

	}

	/**

	 * Load the sql file and then execute it

	 *

	 * @throws     BuildException

	 */

	public function main()

	{

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

		if ($this->sqldbmap === null || $this->getSqlDbMap()->exists() === false) {

			throw new BuildException("You haven't provided an sqldbmap, or "

					. "the one you specified doesn't exist: " . $this->sqldbmap->getPath());

		}

		if ($this->url === null) {

			throw new BuildException("DSN url attribute must be set!");

		}

		$map = new Properties();

		try {

			$map->load($this->getSqlDbMap());

		} catch (IOException $ioe) {

			throw new BuildException("Cannot open and process the sqldbmap!");

		}

		$databases = array();

		foreach ($map->keys() as $sqlfile) {

			$database = $map->getProperty($sqlfile);

			// Q: already there?

			if (!isset($databases[$database])) {

			// A: No.

				$databases[$database] = array();

			}

			// We want to make sure that the base schemas

			// are inserted first.

			if (strpos($sqlfile, "schema.sql") !== false) {

				// add to the beginning of the array

				array_unshift($databases[$database], $sqlfile);

			} else {

				array_push($databases[$database], $sqlfile);

			}

		}

		foreach ($databases as $db => $files) {

			$transactions = array();

			foreach ($files as $fileName) {

				$file = new PhingFile($this->srcDir, $fileName);

				if ($file->exists()) {

					$this->log("Executing statements in file: " . $file->__toString());

					$transaction = new PropelSQLExecTransaction($this);

					$transaction->setSrc($file);

					$transactions[] = $transaction;

				} else {

					$this->log("File '" . $file->__toString()

							. "' in sqldbmap does not exist, so skipping it.");

				}

			}

			$this->insertDatabaseSqlFiles($this->url, $db, $transactions);

		}

	}

	/**

	 * Take the base url, the target database and insert a set of SQL

	 * files into the target database.

	 *

	 * @param      string $url

	 * @param      string $database

	 * @param      array $transactions

	 */

	private function insertDatabaseSqlFiles($url, $database, $transactions)

	{

		$url = str_replace("@DB@", $database, $url);

		$this->log("Our new url -> " . $url);

		try {

			$buf = "Database settings:" . PHP_EOL

			. " URL: " . $url . PHP_EOL

			. ($this->userId ? " user: " . $this->userId . PHP_EOL : "")

			. ($this->password ? " password: " . $this->password . PHP_EOL : "");

			$this->log($buf, Project::MSG_VERBOSE);

			// Set user + password to null if they are empty strings

			if (!$this->userId) { $this->userId = null; }

			if (!$this->password) { $this->password = null; }

			$this->conn = new PDO($url, $this->userId, $this->password);

			$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

			// $this->conn->setAutoCommit($this->autocommit);

			// $this->statement = $this->conn->createStatement();

			$out = null;

			try {

				if ($this->output !== null) {

					$this->log("Opening PrintStream to output file " . $this->output->__toString(), Project::MSG_VERBOSE);

					$out = new FileWriter($this->output);

				}

				// Process all transactions

				for ($i=0,$size=count($transactions); $i < $size; $i++) {

					$transactions[$i]->runTransaction($out);

					if (!$this->autocommit) {

						$this->log("Commiting transaction", Project::MSG_VERBOSE);

						$this->conn->commit();

					}

				}

			} catch (Exception $e) {

				if ($out) $out->close();

			}

		} catch (IOException $e) {

			if (!$this->autocommit && $this->conn !== null && $this->onError == "abort") {

				try {

					$this->conn->rollBack();

				} catch (PDOException $ex) {

					// do nothing.

					System::println("Rollback failed.");

				}

			}

			if ($this->statement) $this->statement = null; // close

			throw new BuildException($e);

		} catch (PDOException $e) {

			if (!$this->autocommit && $this->conn !== null && $this->onError == "abort") {

				try {

					$this->conn->rollBack();

				} catch (PDOException $ex) {

					// do nothing.

					System::println("Rollback failed");

				}

			}

			if ($this->statement) $this->statement = null; // close

			throw new BuildException($e);

		}

		   $this->statement = null; // close

		$this->log($this->goodSql . " of " . $this->totalSql

				. " SQL statements executed successfully");

	}

	/**

	 * Read the statements from the .sql file and execute them.

	 * Lines starting with '//', '--' or 'REM ' are ignored.

	 *

	 * Developer note:  must be public in order to be called from

	 * sudo-"inner" class PropelSQLExecTransaction.

	 *

	 * @param      Reader $reader

	 * @param      $out Optional output stream.

	 * @throws     PDOException

	 * @throws     IOException

	 */

	public function runStatements(Reader $reader, $out = null)

	{

		$sql = "";

		$line = "";

		$sqlBacklog = "";

		$hasQuery = false;

		$in = new BufferedReader($reader);

		$parser['pointer'] = 0;

		$parser['isInString'] = false;

		$parser['stringQuotes'] = "";

		$parser['backslashCount'] = 0;

		$parser['parsedString'] = "";

		$sqlParts = array();

		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 . PHP_EOL;

			// 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 .= PHP_EOL;

			}

			// DELIM_ROW doesn't need this (as far as i can tell)

			if ($this->delimiterType == self::DELIM_NORMAL) {

				// old regex, being replaced due to segfaults:

				// See: http://propel.phpdb.org/trac/ticket/294

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

				//$sqlParts = preg_split($reg, $sql, 0, PREG_SPLIT_DELIM_CAPTURE);

				$i = $parser['pointer'];

				$c = strlen($sql);

				while ($i < $c) {

					$char = $sql[$i];

					switch($char) {

						case "\\":

							$parser['backslashCount']++;

							$this->log("c$i: found ".$parser['backslashCount']." backslash(es)", Project::MSG_VERBOSE);

							break;

						case "'":

						case "\"":

							if ($parser['isInString'] && $parser['stringQuotes'] == $char) {

								if (($parser['backslashCount'] & 1) == 0) {

									#$this->log("$i: out of string", Project::MSG_VERBOSE);

									$parser['isInString'] = false;

								} else {

									$this->log("c$i: rejected quoted delimiter", Project::MSG_VERBOSE);

								}

							} elseif (!$parser['isInString']) {

								$parser['stringQuotes']	= $char;

								$parser['isInString'] = true;

								#$this->log("$i: into string with $parser['stringQuotes']", Project::MSG_VERBOSE);

							}

							break;

					}

					if ($char == $this->delimiter && !$parser['isInString']) {

						$this->log("c$i: valid end of command found!", Project::MSG_VERBOSE);

						$sqlParts[] = $parser['parsedString'];

						$sqlParts[] = $this->delimiter;

						break;

					}

					$parser['parsedString'] .= $char;

					if ($char !== "\\") {

						if ($parser['backslashCount']) $this->log("$i: backslash reset", Project::MSG_VERBOSE);

						$parser['backslashCount'] = 0;

					}

					$i++;

					$parser['pointer']++;

				}

				$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, $out);

				$sql = "";

				$hasQuery = false;

				$parser['pointer'] = 0;

				$parser['isInString'] = false;

				$parser['stringQuotes'] = "";

				$parser['backslashCount'] = 0;

				$parser['parsedString'] = "";

				$sqlParts = array();

			}

		}

		// Catch any statements not followed by ;

		if ($sql !== "") {

			$this->execSQL($sql, $out);

		}

	}

	/**

	 * Exec the sql statement.

	 *

	 * @param      sql

	 * @param      out

	 * @throws     PDOException

	 */

	protected function execSQL($sql, $out = null)

	{

		// Check and ignore empty statements

		if (trim($sql) == "") {

			return;

		}

		try {

			$this->totalSql++;

			if (!$this->autocommit) $this->conn->beginTransaction();

			$stmt = $this->conn->prepare($sql);

			$stmt->execute();

			$this->log($stmt->rowCount() . " rows affected", Project::MSG_VERBOSE);

			if (!$this->autocommit) $this->conn->commit();

			$this->goodSql++;

		} catch (PDOException $e) {

			$this->log("Failed to execute: " . $sql, Project::MSG_ERR);

			if ($this->onError != "continue") {

				throw $e;

			}

			$this->log($e->getMessage(), Project::MSG_ERR);

		}

	}

	/**

	 * print any results in the statement.

	 *

	 * @param      out

	 * @throws     PDOException

	 */

	protected function printResults($out = null)

	{

		$rs = null;

		do {

			$rs = $this->statement->getResultSet();

			if ($rs !== null) {

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

				$line = "";

				$colsprinted = false;

				while ($rs->next()) {

					if (!$colsprinted && $this->showheaders) {

						$first = true;

						foreach ($this->fields as $fieldName => $ignore) {

							if ($first) $first = false; else $line .= ",";

							$line .= $fieldName;

						}

					} // if show headers

					$first = true;

					foreach ($rs->fields as $columnValue) {

						if ($columnValue != null) {

							$columnValue = trim($columnValue);

						}

						if ($first) {

							$first = false;

						} else {

							$line .= ",";

						}

						$line .= $columnValue;

					}

					if ($out !== null) {

						$out->write($line);

						$out->newLine();

					}

					System::println($line);

					$line = "";

				} // while rs->next()

			}

		} while ($this->statement->getMoreResults());

		System::println();

		if ($out !== null) $out->newLine();

	}

}

/**

 * "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 Creole connection and commit

 * operation in between.

 * @package    propel.phing

 */

class PropelSQLExecTransaction {

	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($out = null)

	{

		if (!empty($this->tSqlCommand)) {

			$this->parent->log("Executing commands", Project::MSG_INFO);

			$this->parent->runStatements($this->tSqlCommand, $out);

		}

		if ($this->tSrcFile !== null) {

			$this->parent->log("Executing file: " . $this->tSrcFile->getAbsolutePath(), Project::MSG_INFO);

			$reader = new FileReader($this->tSrcFile);

			$this->parent->runStatements($reader, $out);

			$reader->close();

		}

	}

}