SQL Server via PDO

abstract-collection.php

<?php
/**
 * @copyright 2015-2016 (c) David Lundgren
 * @license   MIT <http://opensource.org/licenses/mit-license.php>
 */
namespace SqlServer\Collection;

/**
 * Abstract class for representing SQL Server Collections
 *
 * Due to how we retrieve ranges of data the orderBy property must be set by child classes unless that functionality is
 * not in use.
 *
 * This class can be used as a regular iterator, at which point it will retrieve all of the items.
 *
 * It can also be used with the SqlServer\Collection\Adapter\Pagerfanta to be used with Pagerfanta to handle
 * pagination when needed.
 * 
 */
abstract class AbstractCollection extends \Countable, \Iterator
{
	/**
	 * @var \PDO
	 */
	protected $pdo;

	/**
	 * @var int The total number of items
	 */
	protected $totalCount;

	/**
	 * @var int Where the iterator is internally
	 */
	protected $cursor = 0;

	/**
	 * @var string The base query to use for creating the count/item query
	 */
	protected $baseQuery;

	/**
	 * @var array The parameters to bind into the query
	 */
	protected $params;

	/**
	 * @var string The fields to use in the COUNT()
	 */
	protected $countFields;

	/**
	 * @var String The fields to be fetched
	 */
	protected $selectFields;

	/**
	 * @var array list of items
	 */
	protected $items;

	public function __construct($pdo)
	{
		$this->pdo = $pdo;
		$this->pdo->setAttribute(\PDO::ATTR_CASE, \PDO::CASE_LOWER);
	}

	/**
	 * Returns a list of all items
	 *
	 * @return array|\Traversable
	 */
	public function items()
	{
		return $this->slice(0);
	}

	/**
	 * Returns an slice of the results.
	 *
	 * @param integer $offset The offset.
	 * @param integer $length The length.
	 *
	 * @return array|\Traversable The slice.
	 */
	public function slice($offset, $length = null)
	{
		if (empty($this->orderBy)) {
			// in order to use OFFSET ... FETCH the ORDER BY clause exist
			throw new \RuntimeException("Missing orderBy value");
		}

		if ($length) {
			// FETCH may only be used with offset
			$sql = sprintf("%s ORDER BY %s OFFSET %d ROWS FETCH NEXT %d ROWS ONLY", $this->baseQuery, $this->orderBy, $offset, $length);
		}
		else {
			$sql = sprintf("%s ORDER BY %s OFFSET %d ROWS", $this->baseQuery, $this->orderBy, $offset);
		}
		$stmt    = $this->runQuery($sql, $this->selectFields);
		$results = $stmt->fetchAll();
		if (empty($results)) {
			return [];
		}

		// basic filtering of the resultset since SQL Server sometimes sends lots of spaces...
		foreach (array_keys($results) as $k) {
			$results[$k] = array_map('trim', $result);
		}

		return $results;
	}

	/***** Interface implementations *****/
	/**
	 * Count elements of an object
	 *
	 * {@inheritdoc}
	 */
	public function count()
	{
		if (isset($this->items)) {
			return count($this->items);
		}

		if (!isset($this->totalCount)) {
			$col = $this->runQuery($this->baseQuery, sprintf('COUNT(%s)', $this->countFields))
									->fetchColumn(0);
			$this->totalCount = empty($col) ? 0 : (int)$col;
		}

		return $this->totalCount;
	}

	/**
	 * Return the current element
	 *
	 * {@inheritdoc}
	 */
	public function current()
	{
		return $this->items[$this->cursor];
	}

	/**
	 * Move forward to next element
	 *
	 * {@inheritdoc}
	 */
	public function next()
	{
		$this->cursor++;

		if (!isset($this->items)) {
			$this->items = $this->items();
		}
	}

	/**
	 * Return the key of the current element
	 *
	 * {@inheritdoc}
	 */
	public function key()
	{
		return $this->cursor;
	}

	/**
	 * Checks if current position is valid
	 *
	 * {@inheritdoc}
	 */
	public function valid()
	{
		return $this->cursor < $this->totalCount;
	}

	/**
	 * Rewind the Iterator to the first element
	 *
	 * {@inheritdoc}
	 */
	public function rewind()
	{
		$this->cursor = 0;
		if (!isset($this->items)) {
			$this->items = $this->items();
		}
	}

	/**
	 * Runs the given query.
	 *
	 * Replaces {{FIELDS}} with the sent in fields
	 *
	 * @param $sql
	 * @param $fields
	 * @return \PDOStatement
	 */
	protected function runQuery($sql, $fields)
	{
		$sql = str_replace("{{FIELDS}}", $fields, $sql);
		if ($this->params) {
			$stmt = $this->pdo->prepare($sql);
			$stmt->setFetchMode(\PDO::FETCH_ASSOC);
			if ($stmt->execute($this->params) === false) {
				$err = $stmt;
			}
		}
		else {
			$stmt = $this->pdo->query($sql, \PDO::FETCH_ASSOC);
			if ($stmt === false) {
				$err =& $this->pdo;
			}
		}

		if (isset($err)) {
			throw new \Exception(
				"Server Error:" . json_encode(
					[
						'query' => $this->replaceBindings($sql, $this->params),
						'error' => $err->errorInfo()
					]));
		}

		return $stmt;
	}

	/**
	 *
	 * @see {http://stackoverflow.com/a/19326169}
	 * @param            $sql
	 * @param array|null $params
	 * @return mixed
	 */
	private function replaceBindings($sql, array $params = null)
	{
		if (!empty($params)) {
			$indexed = $params == array_values($params);
			foreach ($params as $k => $v) {
				if (is_object($v)) {
					if ($v instanceof \DateTime) {
						$v = $v->format('Y-m-d H:i:s');
					}
					else {
						continue;
					}
				}
				elseif (is_string($v)) {
					$v = "'$v'";
				}
				elseif ($v === null) {
					$v = 'NULL';
				}
				elseif (is_array($v)) {
					$v = implode(',', $v);
				}

				if ($indexed) {
					$sql = preg_replace('/\?/', $v, $sql, 1);
				}
				else {
					if ($k[0] != ':') {
						$k = ':' . $k;
					} //add leading colon if it was left out
					$sql = str_replace($k, $v, $sql);
				}
			}
		}

		return $sql;
	}
}

pagerfanta-collection-adapter.php

<?php
/**
 * @copyright 2015-2016 (c) David Lundgren
 * @license   MIT <http://opensource.org/licenses/mit-license.php>
 */
namespace SqlServer\Collection\Adapter;

use Pagerfanta\Adapter\AdapterInterface;
use SqlServer\Collection\AbstractCollection;

/**
 * Pagerfanta adapter for easier pagination of results
 */
class Pagerfanta
	implements AdapterInterface
{
	/**
	 * @var Collection
	 */
	private $collection;

	/**
	 * @var int The total number of items in the collection
	 */
	private $itemCount;

	public function __construct(Collection $collection)
	{
		$this->collection = $collection;
	}

	/**
	 * Returns the number of results.
	 *
	 * @return integer The number of results.
	 */
	public function getNbResults()
	{
		if (!$this->itemCount) {
			$this->itemCount = count($this->collection);
		}

		return $this->itemCount;
	}

	/**
	 * Returns an slice of the results.
	 *
	 * @param integer $offset The offset.
	 * @param integer $length The length.
	 *
	 * @return array|\Traversable The slice.
	 */
	public function getSlice($offset, $length)
	{
		return $this->collection->slice($offset, $length);
	}
}

pdo-connector.php

<?php
/**
 * @copyright 2015-2016 (c) David Lundgren
 * @license   MIT <http://opensource.org/licenses/mit-license.php>
 */
namespace SqlServer;

use PDO;

/**
 * Extends the PDO class to handle a lastInsertId for SqlServer
 */
class Connector
	extends PDO
{
	/**
	 * (PHP 5 &gt;= 5.1.0, PECL pdo &gt;= 0.1.0)<br/>
	 * Returns the ID of the last inserted row or sequence value
	 *
	 * @link http://php.net/manual/en/pdo.lastinsertid.php
	 * @param string $name [optional] <p>
	 *                     Name of the sequence object from which the ID should be returned.
	 *                     </p>
	 * @return string If a sequence name was not specified for the <i>name</i>
	 *                     parameter, <b>PDO::lastInsertId</b> returns a
	 *                     string representing the row ID of the last row that was inserted into
	 *                     the database.
	 *                     </p>
	 *                     <p>
	 *                     If a sequence name was specified for the <i>name</i>
	 *                     parameter, <b>PDO::lastInsertId</b> returns a
	 *                     string representing the last value retrieved from the specified sequence
	 *                     object.
	 *                     </p>
	 *                     <p>
	 *                     If the PDO driver does not support this capability,
	 *                     <b>PDO::lastInsertId</b> triggers an
	 *                     IM001 SQLSTATE.
	 */
	public function lastInsertId($name = null)
	{
		$stmt = $this->query("SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int)");

		if ($stmt === false) {
			throw new \PDOException("Driver does not support this function: driver does not support lastInsertId() in", "IM001");
		}

		return $stmt->fetch(self::FETCH_NUM)[0];
	}

}

<?php
/**
 * @copyright 2015-2016 (c) David Lundgren
 * @license   MIT <http://opensource.org/licenses/mit-license.php>
 */
class SpecialCollection extends SqlServer\Collection\AbstractCollection
{
    protected $countFields = "DISTINCT id";
    protected $selectFields = "DISTINCT id, name, date_added, description";
    protected $orderBy = "id";
    protected $baseQuery = "
        SELECT {{FIELDS}}
        FROM
            items i (nolock)
            INNER JOIN status s (nolock) ON (s.id = i.status_id)
        WHERE s.name = 'Active'
    ";

    /**
     * Returns an slice of the results.
     *
     * @param integer $offset The offset.
     * @param integer $length The length.
     *
     * @return array|\Traversable The slice.
     */
    public function slice($offset, $length = null)
    {
        return array_map([$this, 'createItem'], parent::slice($offset, $length));
    }

    /**
     * Creates the object from the result
     *
     * @param array $result
     * @return object
     */
    public function createItem(array $result)
    {
        $item = (object)$result;
        $item->date_added = new \DateTimeImmutable($result['date_added'], new \DateTimeZone('UTC'));

        return $item;
    }

}