/libs/dibi/drivers/oracle.php

<?php



/**

 * dibi - tiny'n'smart database abstraction layer

 * ----------------------------------------------

 *

 * @copyright  Copyright (c) 2005, 2010 David Grudl

 * @license    http://dibiphp.com/license  dibi license

 * @link       http://dibiphp.com

 * @package    dibi

 */





/**

 * The dibi driver for Oracle database.

 *

 * Connection options:

 *   - 'database' (or 'db') - the name of the local Oracle instance or the name of the entry in tnsnames.ora

 *   - 'username' (or 'user')

 *   - 'password' (or 'pass')

 *   - 'lazy' - if TRUE, connection will be established only when required

 *   - 'formatDate' - how to format date in SQL (@see date)

 *   - 'formatDateTime' - how to format datetime in SQL (@see date)

 *   - 'charset' - character encoding to set

 *   - 'resource' - connection resource (optional)

 *

 * @copyright  Copyright (c) 2005, 2010 David Grudl

 * @package    dibi

 */

class DibiOracleDriver extends DibiObject implements IDibiDriver

{

	/** @var resource  Connection resource */

	private $connection;



	/** @var resource  Resultset resource */

	private $resultSet;



	/** @var bool */

	private $autocommit = TRUE;



	/** @var string  Date and datetime format */

	private $fmtDate, $fmtDateTime;







	/**

	 * @throws DibiException

	 */

	public function __construct()

	{

		if (!extension_loaded('oci8')) {

			throw new DibiDriverException("PHP extension 'oci8' is not loaded.");

		}

	}







	/**

	 * Connects to a database.

	 * @return void

	 * @throws DibiException

	 */

	public function connect(array &$config)

	{

		DibiConnection::alias($config, 'charset');

		$this->fmtDate = isset($config['formatDate']) ? $config['formatDate'] : 'U';

		$this->fmtDateTime = isset($config['formatDateTime']) ? $config['formatDateTime'] : 'U';



		if (isset($config['resource'])) {

			$this->connection = $config['resource'];

		} else {

			$this->connection = @oci_new_connect($config['username'], $config['password'], $config['database'], $config['charset']); // intentionally @

		}



		if (!$this->connection) {

			$err = oci_error();

			throw new DibiDriverException($err['message'], $err['code']);

		}

	}







	/**

	 * Disconnects from a database.

	 * @return void

	 */

	public function disconnect()

	{

		oci_close($this->connection);

	}







	/**

	 * Executes the SQL query.

	 * @param  string      SQL statement.

	 * @return IDibiDriver|NULL

	 * @throws DibiDriverException

	 */

	public function query($sql)

	{

		$this->resultSet = oci_parse($this->connection, $sql);

		if ($this->resultSet) {

			oci_execute($this->resultSet, $this->autocommit ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT);

			$err = oci_error($this->resultSet);

			if ($err) {

				throw new DibiDriverException($err['message'], $err['code'], $sql);

			}

		} else {

			$err = oci_error($this->connection);

			throw new DibiDriverException($err['message'], $err['code'], $sql);

		}



		return is_resource($this->resultSet) ? clone $this : NULL;

	}







	/**

	 * Gets the number of affected rows by the last INSERT, UPDATE or DELETE query.

	 * @return int|FALSE  number of rows or FALSE on error

	 */

	public function getAffectedRows()

	{

		throw new NotImplementedException;

	}







	/**

	 * Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.

	 * @return int|FALSE  int on success or FALSE on failure

	 */

	public function getInsertId($sequence)

	{

		$this->query("SELECT $sequence.CURRVAL AS ID FROM DUAL");

		$row = $this->fetch(TRUE);

		return isset($row['ID']) : (int) $row['ID'] : FALSE;

	}







	/**

	 * Begins a transaction (if supported).

	 * @param  string  optional savepoint name

	 * @return void

	 */

	public function begin($savepoint = NULL)

	{

		$this->autocommit = FALSE;

	}







	/**

	 * Commits statements in a transaction.

	 * @param  string  optional savepoint name

	 * @return void

	 * @throws DibiDriverException

	 */

	public function commit($savepoint = NULL)

	{

		if (!oci_commit($this->connection)) {

			$err = oci_error($this->connection);

			throw new DibiDriverException($err['message'], $err['code']);

		}

		$this->autocommit = TRUE;

	}







	/**

	 * Rollback changes in a transaction.

	 * @param  string  optional savepoint name

	 * @return void

	 * @throws DibiDriverException

	 */

	public function rollback($savepoint = NULL)

	{

		if (!oci_rollback($this->connection)) {

			$err = oci_error($this->connection);

			throw new DibiDriverException($err['message'], $err['code']);

		}

		$this->autocommit = TRUE;

	}







	/**

	 * Returns the connection resource.

	 * @return mixed

	 */

	public function getResource()

	{

		return $this->connection;

	}







	/********************* SQL ****************d*g**/







	/**

	 * Encodes data for use in a SQL statement.

	 * @param  mixed     value

	 * @param  string    type (dibi::TEXT, dibi::BOOL, ...)

	 * @return string    encoded value

	 * @throws InvalidArgumentException

	 */

	public function escape($value, $type)

	{

		switch ($type) {

		case dibi::TEXT:

		case dibi::BINARY:

			return "'" . str_replace("'", "''", $value) . "'"; // TODO: not tested



		case dibi::IDENTIFIER:

			// @see http://download.oracle.com/docs/cd/B10500_01/server.920/a96540/sql_elements9a.htm

			$value = str_replace('"', '""', $value);

			return '"' . str_replace('.', '"."', $value) . '"';



		case dibi::BOOL:

			return $value ? 1 : 0;



		case dibi::DATE:

			return $value instanceof DateTime ? $value->format($this->fmtDate) : date($this->fmtDate, $value);



		case dibi::DATETIME:

			return $value instanceof DateTime ? $value->format($this->fmtDateTime) : date($this->fmtDateTime, $value);



		default:

			throw new InvalidArgumentException('Unsupported type.');

		}

	}







	/**

	 * Decodes data from result set.

	 * @param  string    value

	 * @param  string    type (dibi::BINARY)

	 * @return string    decoded value

	 * @throws InvalidArgumentException

	 */

	public function unescape($value, $type)

	{

		if ($type === dibi::BINARY) {

			return $value;

		}

		throw new InvalidArgumentException('Unsupported type.');

	}







	/**

	 * Injects LIMIT/OFFSET to the SQL query.

	 * @param  string &$sql  The SQL query that will be modified.

	 * @param  int $limit

	 * @param  int $offset

	 * @return void

	 */

	public function applyLimit(&$sql, $limit, $offset)

	{

		if ($offset > 0) {

			// see http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

			$sql = 'SELECT * FROM (SELECT t.*, ROWNUM AS "__rnum" FROM (' . $sql . ') t ' . ($limit >= 0 ? 'WHERE ROWNUM <= ' . ((int) $offset + (int) $limit) : '') . ') WHERE "__rnum" > '. (int) $offset;



		} elseif ($limit >= 0) {

			$sql = 'SELECT * FROM (' . $sql . ') WHERE ROWNUM <= ' . (int) $limit;

		}

	}







	/********************* result set ****************d*g**/







	/**

	 * Returns the number of rows in a result set.

	 * @return int

	 */

	public function getRowCount()

	{

		throw new NotSupportedException('Row count is not available for unbuffered queries.');

	}







	/**

	 * Fetches the row at current position and moves the internal cursor to the next position.

	 * @param  bool     TRUE for associative array, FALSE for numeric

	 * @return array    array on success, nonarray if no next record

	 * @internal

	 */

	public function fetch($assoc)

	{

		return oci_fetch_array($this->resultSet, ($assoc ? OCI_ASSOC : OCI_NUM) | OCI_RETURN_NULLS);

	}







	/**

	 * Moves cursor position without fetching row.

	 * @param  int      the 0-based cursor pos to seek to

	 * @return boolean  TRUE on success, FALSE if unable to seek to specified record

	 */

	public function seek($row)

	{

		throw new NotImplementedException;

	}







	/**

	 * Frees the resources allocated for this result set.

	 * @return void

	 */

	public function free()

	{

		oci_free_statement($this->resultSet);

		$this->resultSet = NULL;

	}







	/**

	 * Returns metadata for all columns in a result set.

	 * @return array

	 */

	public function getColumnsMeta()

	{

		$count = oci_num_fields($this->resultSet);

		$res = array();

		for ($i = 1; $i <= $count; $i++) {

			$res[] = array(

				'name'      => oci_field_name($this->resultSet, $i),

				'table'     => NULL,

				'fullname'  => oci_field_name($this->resultSet, $i),

				'nativetype'=> oci_field_type($this->resultSet, $i),

			);

		}

		return $res;

	}







	/**

	 * Returns the result set resource.

	 * @return mixed

	 */

	public function getResultResource()

	{

		return $this->resultSet;

	}







	/********************* reflection ****************d*g**/







	/**

	 * Returns list of tables.

	 * @return array

	 */

	public function getTables()

	{

		$this->query('SELECT * FROM cat');

		$res = array();

		while ($row = $this->fetch(FALSE)) {

			if ($row[1] === 'TABLE' || $row[1] === 'VIEW') {

				$res[] = array(

					'name' => $row[0],

					'view' => $row[1] === 'VIEW',

				);

			}

		}

		$this->free();

		return $res;

	}







	/**

	 * Returns metadata for all columns in a table.

	 * @param  string

	 * @return array

	 */

	public function getColumns($table)

	{

		throw new NotImplementedException;

	}







	/**

	 * Returns metadata for all indexes in a table.

	 * @param  string

	 * @return array

	 */

	public function getIndexes($table)

	{

		throw new NotImplementedException;

	}







	/**

	 * Returns metadata for all foreign keys in a table.

	 * @param  string

	 * @return array

	 */

	public function getForeignKeys($table)

	{

		throw new NotImplementedException;

	}



}