SimpleDb

SimpleDb.php

<?php

abstract class SimpleDbQueryBuilder {

	protected function tablesQuery() {
		return array(
				'sql'  => "SELECT name FROM sqlite_master WHERE type = 'table';",
				'args' => array(),
			);
	}

	protected function createTableQuery($name, $columns) {
		if(($offset = array_search('id', $columns)) !== FALSE) {
			array_splice($columns, $offset, 1);
		}
		return array(
				'sql' =>
					  'CREATE TABLE ' . $name . ' (id INTEGER PRIMARY KEY AUTOINCREMENT '
					. (!empty($columns) ? ', ' . implode(', ', $columns) : '') . ');',
				'args' => array(),
			);
	}

	protected function dropTableQuery($name) {
		return array(
				'sql'  => 'DROP TABLE ' . $name . ';',
				'args' => array(),
			);
	}
}

abstract class SimpleDbTableQueryBuilder {

	private   $name;
	private  $alias;
	private $select;
	private  $joins;
	private  $where;
	private  $group;
	private  $order;
	private  $limit;
	private $offset;
	private   $args;

	function __construct($name) {
		$this->name = $name;
	}

	function name() {
		return $this->name;
	}

	function alias($alias = NULL) {
		if(func_num_args()) {
			$this->alias = $alias;
			return $this;
		}
		return $this->alias;
	}

	function select($select = NULL) {
		if(func_num_args()) {
			$this->select = $select;
			return $this;
		}
		return $this->select;
	}

	function join($table, $on) {
		$this->joins []= array(
				'type'  => 'JOIN',
				'table' => $table,
				'on'    => $on,
			);
		return $this;
	}

	function leftJoin($table, $on) {
		$this->joins []= array(
				'type'  => 'LEFT JOIN',
				'table' => $table,
				'on'    => $on,
			);
		return $this;
	}

	function where($where = NULL, $args = NULL) {
		$count = func_num_args();
		if($count) {
			$this->where = $where;
			if($count > 1) {
				if(!is_array($args)) {
					$args = func_get_args();
					array_shift($args);
				}
				$this->args = $args;
			}
			return $this;
		}
		return $this->where;
	}

	function group($group = NULL) {
		if(func_num_args()) {
			$this->group = $group;
			return $this;
		}
		return $this->group;
	}

	function order($order = NULL) {
		if(func_num_args()) {
			$this->order = $order;
			return $this;
		}
		return $this->order;
	}

	function limit($limit = NULL) {
		if(func_num_args()) {
			$this->limit = $limit;
			return $this;
		}
		return $this->limit;
	}

	function offset($offset = NULL) {
		if(func_num_args()) {
			$this->offset = $offset;
			return $this;
		}
		return $this->offset;
	}

	function args($args = NULL) {
		if(func_num_args()) {
			$this->args = is_array($args) ? $args : func_get_args();
			return $this;
		}
		return $this->args;
	}

	protected function columnsQuery() {
		return array(
				'sql'  => "PRAGMA table_info('$this->name');",
				'args' => array(),
			);
	}

	protected function addColumnQuery($column) {
		return array(
				'sql'  => 'ALTER TABLE ' . $this->name . ' ADD COLUMN ' . $column . ';',
				'args' => array(),
			);
	}

	protected function indexesQuery() {
		return array(
				'sql'  => "SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = '$this->name';",
				'args' => array(),
			);
	}

	protected function columnsFromIndex($columns) {
		return preg_split('/\s*,\s*/',
				preg_replace('/\s*(COLLATE|BINARY|NOCASE|RTRIM|ASC|DESC|\(\s*\d+\s*\))\s*/', '', $columns)
			);
	}

	protected function indexName($columns) {
		return $this->name . '_' . implode('_', $this->columnsFromIndex($columns)) . '_idx';
	}

	protected function createIndexQuery($columns) {
		return array(
				'sql'  => 'CREATE INDEX ' . $this->indexName($columns) . ' ON ' . $this->name . ' (' . $columns . ');',
				'args' => array(),
			);
	}

	protected function dropIndexQuery($columns) {
		return array(
				'sql'  => 'DROP INDEX ' . $this->indexName($columns) . ';',
				'args' => array(),
			);
	}
	
	protected function getArgs() {
		return $this->args === NULL ? array() : $this->args;
	}

	protected function selectQuery() {
		$sql = 'SELECT';
		if($this->select !== NULL) {
			$sql .= ' ' . $this->select;
		} else {
			$sql .= ' *';
		}
		$sql .= ' FROM ' . $this->name;
		if($this->alias !== NULL) {
			$sql .= ' AS ' . $this->alias;
		}
		if($this->joins !== NULL) {
			foreach($this->joins as $join) {
				$sql .= ' ' . $join['type'] . ' ' . $join['table'];
				if(isset($join['on'])) {
					$sql .= ' ON ' . $join['on'];
				}
			}
		}
		if($this->where !== NULL) {
			$sql .= ' WHERE ' . $this->where;
		}
		if($this->group !== NULL) {
			$sql .= ' GROUP BY ' . $this->group;
		}
		if($this->order !== NULL) {
			$sql .= ' ORDER BY ' . $this->order;
		}
		if($this->limit !== NULL) {
			$sql .= ' LIMIT ' . $this->limit;
			if($this->offset !== NULL) {
				$sql .= ' OFFSET ' . $this->offset;
			}
		}
		return array(
				'sql'  => $sql . ';',
				'args' => $this->getArgs(),
			);
	}
	
	protected function isExistsQuery() {
		$clone = clone $this;
		$clone->select = '1';
		$clone->order  = NULL;
		return $clone->selectQuery();
	}
	
	protected function countQuery() {
		$clone = clone $this;
		$clone->select = 'COUNT(*)';
		$clone->order  = NULL;
		return $clone->selectQuery();
	}
	
	protected function insertQuery($row) {
		$columns = array_keys($row);
		return array(
				'sql' =>
					  'INSERT OR IGNORE INTO ' . $this->name
					. ' (' . implode(', ', $columns) . ')'
					. ' VALUES (' . implode(', ', array_fill(0, count($columns), '?')) . ');',
				'args' => array_values($row),
			);
	}
	
	protected function updateQuery($row) {
		if(!isset($row['id']) && $this->where === NULL) {
			throw new Exception('Missing criteria: id or where.');
		}
		$clone = clone $this;
		if(isset($row['id'])) {
			$clone->where = 'id = ?';
			$clone->args  = array($row['id']);
			unset($row['id']);
		}
		$columns = array_keys($row);
		return array(
				'sql' =>
					  'UPDATE ' . $clone->name . ' SET ' . implode(' = ?, ', $columns)
					. ' = ?' . ' WHERE ' . $clone->where . ';',
				'args' => array_merge(array_values($row), $clone->getArgs()),
			);
	}
	
	protected function deleteQuery($row) {
		if(!is_array($row)) {
			return array(
					'sql'  => 'DELETE FROM ' . $this->name . ' WHERE id = ?;',
					'args' => array($row),
				);
		}
		if(!isset($row['id']) && $this->where === NULL) {
			throw new Exception('Missing criteria: id or where.');
		}
		$clone = clone $this;
		if(isset($row['id'])) {
			$clone->where = 'id = ?';
			$clone->args  = array($row['id']);
		}
		return array(
				'sql' =>
					  'DELETE FROM ' . $clone->name . ' WHERE ' . $clone->where . ';',
				'args' => $clone->getArgs(),
			);
	}
}

class SimpleDb extends SimpleDbQueryBuilder {

	private          $path;
	private           $pdo;
	private $inTransaction;
	private        $tables;

	function __construct($path = 'SimpleDb.sqlite3') {
		$this->path = $path;
	}

	function path() {
		return $this->path;
	}

	function pdo() {
		if($this->pdo === NULL) {
			$this->pdo = new PDO('sqlite:' . $this->path);
		}
		return $this->pdo;
	}

	function execute($query) {
		$statement = $this->pdo()->prepare($query['sql']);
		$info      = $this->pdo->errorInfo();
		if($info[0] != '00000') {
			throw new Exception($info[2]);
		}
		$statement->execute($query['args']);
		$info = $statement->errorInfo();
		if($info[0] != '00000') {
			throw new Exception($info[2]);
		}
		$statement->setFetchMode(PDO::FETCH_OBJ);
		return $statement;
	}

	function cell($query) {
		return $this->execute($query)->fetchColumn();
	}

	function row($query) {
		return $this->execute($query)->fetch();
	}

	function rows($query) {
		return new IteratorIterator($this->execute($query));
	}

	function begin() {
		if(!$this->inTransaction) {
			$this->pdo()->beginTransaction();
			$this->inTransaction = TRUE;
		}
		return $this;
	}

	function end() {
		if($this->inTransaction) {
			$this->pdo()->commit();
			$this->inTransaction = FALSE;
		}
		return $this;
	}

	function tables() {
		if($this->tables === NULL) {
			$this->tables = array();
			foreach($this->rows($this->tablesQuery()) as $row) {
				if(!preg_match('/^sqlite_/', $row->name)) {
					$this->tables []= $row->name;
				}
			}
		}
		return $this->tables;
	}

	function createTable($name, $columns) {
		$this->execute($this->createTableQuery($name, $columns));
		if($this->tables !== NULL) {
			$this->tables []= $name;
		}
		return $this;
	}

	function dropTable($name) {
		$this->execute($this->dropTableQuery($name));
		if($this->tables !== NULL) {
			if(($offset = array_search($name, $this->tables)) !== FALSE) {
				array_splice($this->tables, $offset, 1);
			}
		}
		return $this;
	}

	function __get($name) {
		return new SimpleDbTable($this, $name);
	}

	function __call($name, $args) {
		$table = new SimpleDbTable($this, $name);
		if(count($args)) {
			if(is_array($args[0])) {
				foreach($args[0] as $key => $value) {
					call_user_func_array(array($table, $key), $value);
				}
			} else {
				call_user_func_array(array($table, 'where'), $args);
			}
		}
		return $table;
	}
}

class SimpleDbTable extends SimpleDbTableQueryBuilder implements IteratorAggregate {

	private      $db;
	private $columns;
	private $indexes;
	
	function __construct($db, $name) {
		parent::__construct($name);
		$this->db = $db;
	}
	
	function db() {
		return $this->db;
	}
	
	function columns() {
		if($this->columns === NULL) {
			$this->columns = array();
			foreach($this->db->rows($this->columnsQuery()) as $row) {
				$this->columns []= $row->name;
			}
		}
		return $this->columns;
	}
	
	private function addColumn($column) {
		$this->db->execute($this->addColumnQuery($column));
		if($this->columns !== NULL) {
			$this->columns []= $column;
		}
		return $this;
	}

	function indexes() {
		if($this->indexes === NULL) {
			$this->indexes = array();
			foreach($this->db->rows($this->indexesQuery()) as $row) {
				$this->indexes []= $row->name;
			}
		}
		return $this->indexes;
	}
	
	function initialize($columns) {
		if(!in_array($this->name(), $this->db->tables())) {
			$this->db->createTable($this->name(), $columns);
		}
		$difference = array_diff($columns, $this->columns());
		if(!empty($difference)) {
			$this->db->begin();
			foreach($difference as $column) {
				$this->addColumn($column);
			}
			$this->db->end();
		}
	}

	function index($columns) {
		$this->initialize($this->columnsFromIndex($columns));
		$name = $this->indexName($columns);
		if(!in_array($name, $this->indexes())) {
			$this->db->execute($this->createIndexQuery($columns));
			if($this->indexes !== NULL) {
				$this->indexes []= $name;
			}
		}
		return $this;
	}

	function dropIndex($columns) {
		$this->initialize($this->columnsFromIndex($columns));
		$name = $this->indexName($columns);
		if(in_array($name, $this->indexes())) {
			$this->db->execute($this->dropIndexQuery($columns));
			if(($offset = array_search($name, $this->indexes)) !== FALSE) {
				array_splice($this->indexes, $offset, 1);
			}
		}
		return $this;
	}

	function getIterator() {
		if(!in_array($this->name(), $this->db->tables())) {
			return new ArrayIterator(array());
		}
		try {
			return $this->db->rows($this->selectQuery());
		} catch(Exception $e) {
			return new ArrayIterator(array());
		}
	}
	
	function rows() {
		$rows = array();
		foreach($this as $row) {
			$rows []= $row;
		}
		return $rows;
	}

	function row() {
		if(!in_array($this->name(), $this->db->tables())) {
			return (object) array();
		}
		try {
			return $this->db->row($this->selectQuery());
		} catch(Exception $e) {
			return (object) array();
		}
	}

	function cell() {
		if(!in_array($this->name(), $this->db->tables())) {
			return;
		}
		try {
			return $this->db->cell($this->selectQuery());
		} catch(Exception $e) {
			return;
		}
	}

	function isExists() {
		if(!in_array($this->name(), $this->db->tables())) {
			return FALSE;
		}
		try {
			return $this->db->cell($this->isExistsQuery()) == 1;
		} catch(Exception $e) {
			return FALSE;
		}
	}

	function count() {
		if(!in_array($this->name(), $this->db->tables())) {
			return 0;
		}
		try {
			return $this->db->cell($this->countQuery());
		} catch(Exception $e) {
			return 0;
		}
	}
	
	function insert($row) {
		if(empty($row)) {
			return $this;
		}
		if(is_array(current($row))) {
			$this->db->begin();
			foreach($row as $value) {
				$this->insert($value);
			}
			$this->db->end();
			return $this;
		}
		$columns = array_keys($row);
		$this->initialize($columns);
		$this->db->execute($this->insertQuery($row));
		return $this;
	}
	
	function update($row) {
		if(empty($row)) {
			return $this;
		}
		if(is_array(current($row))) {
			$this->db->begin();
			foreach($row as $value) {
				$this->update($value);
			}
			$this->db->end();
			return $this;
		}
		$columns = array_keys($row);
		$this->initialize($columns);
		$this->db->execute($this->updateQuery($row));
		return $this;
	}
	
	function delete($row) {
		if(is_array($row)) {
			if(empty($row)) {
				return $this;
			}
			if(is_array(current($row))) {
				$this->db->begin();
				foreach($row as $value) {
					$this->update($value);
				}
				$this->db->end();
				return $this;
			}
			$columns = array_keys($row);
			$this->initialize($columns);
		} else {
			$this->initialize(array());
		}
		$this->db->execute($this->deleteQuery($row));
		return $this;
	}
	
	function drop() {
		if(!in_array($this->name(), $this->db->tables())) {
			return $this;
		}
		$this->db->dropTable($this->name());
		$this->columns = NULL;
		$this->indexes = NULL;
		return $this;
	}
	
	function begin() {
		$this->db->begin();
		return $this;
	}
	
	function end() {
		$this->db->end();
		return $this;
	}
}