<?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();
		}
	}
}
