juju-php/PDO/table.class.php

<?php
	namespace Juju\PDO;
	require_once(realpath(dirname(__DIR__).'/pdo.class.php'));
	use Juju\{PDO, PDO\Transaction};

	class Table {
		const COLUMN = 'column';
		const INDEX = 'index';
		const FOREIGN_KEY = 'foreignkey';
		private $pdo;
		private $name;
		private $exists;
		private $primaryKey;
		private $columns;
		private $index;
		private $foreignKey;
		private $columns_renamed;
		private $columns_removed;
		private $index_removed;
		private $foreignKey_removed;

		public function __construct($pdo, string $name){
			if($pdo instanceof PDO || $pdo instanceof Transaction){
				$this->pdo = $pdo;
				$this->name = $name;
				$this->rollback();
			}else{
				throw new \Exception("Invalid pdo argument");
			}
		}
		public function __get($name){
			switch($name){
				case 'exists':case 'name':case 'columns':
					return $this->$name;
				break;
				default:
					throw new \Exception("Invalid property {$name}");
			}
		}
		public function exists(){
			$pdo = $this->pdo;
			try{
				$count = $pdo->exec("select count(1) from `{$this->name}` limit 1");
				$this->exists = $count > 0;
			}catch(\Exception $e){
				$this->exists = false;
			}
			return $this->exists;
		}
		public function describe(){
			$this->columns = [];
			$this->primaryKey = [];
			$this->index = [];
			$this->foreignKeys = [];
			$this->columns_renamed = [];
			$this->columns_removed = [];
			$this->index_removed = [];
			$this->foreignKey_removed = [];
			if($this->exists){
				$pdo = $this->pdo;

				$query = $pdo->query("show create table `{$this->name}`");
				$sql = $query->fetch()['create table'];
				$query->closeCursor();
				if(preg_match_all('/ PRIMARY KEY \(((?:`[^`]+`,?)+)\)/s', $sql, $primaryKey, PREG_SET_ORDER) > 0){
					$this->primaryKey = array_map(
						function($item){
							return preg_replace('/`([^`]+)`/', '\\1', $item);
						},
						array_merge(
							...array_map(
								function($item){
									return explode(',', $item[1]);
								}, $primaryKey
							)
						)
					);
				}
				unset($primaryKey);
				if(preg_match_all('/ ((?:UNIQUE)? ?KEY) `([^`]+)` \(((?:`[^`]+`,?)+)\)/s', $sql, $indexes, PREG_SET_ORDER) > 0){
					$this->indexes = array_reduce($indexes, function($indexes, $item){
						$indexes[$item[2]] = [
							'unique'=> $item[1] == 'UNIQUE KEY',
							'columns'=> array_map(function($item){
								return preg_replace('/`([^`]+)`/', '\\1', $item);
							}, explode(',', $item[3])),
							'dirty'=> false
						];
						return $indexes;
					});
				}
				unset($indexes);
				if(preg_match_all('/ CONSTRAINT `([^`]+)` FOREIGN KEY \(((?:`[^`]+`(?:, )?)+)\) REFERENCES `([^`]+)` \(((?:`[^`]+`(?:, )?)+)\)/s', $sql, $foreignKeys, PREG_SET_ORDER, 0) > 0){
					$this->foreignKeys = array_reduce($foreignKeys, function($foreignKeys, $item) use($sql){
						$columns = explode(',', $item[2]);
						$matches = explode(',', $item[4]);
						foreach($columns as $key => &$column){
							$column = [
								preg_replace('/`([^`]+)`/', '\\1', $column),
								preg_replace('/`([^`]+)`/', '\\1', $matches[$key])
							];
						}
						$foreignKeys[$item[1]] = [
							'references'=> $item[3],
							'columns'=> $columns,
							'dirty'=> false
						];
						return $foreignKeys;
					});
				}
				unset($foreignKeys);
				if(preg_match_all('/ `([^`]+)` ([^\(]+\([^\)]+\))[^,\n]*,?/s', $sql, $columns, PREG_SET_ORDER) > 0){
					foreach($columns as $column){
						$default = null;
						$line = $column[0];
						$line .= substr($line, -1, 1) === ',' ? '' : ',';
						if(preg_match('/ `[^`]+` [^\(]+\([^\)]+\)[^D$]+DEFAULT (?!NULL)(.+)(?: AUTO_INCREMENT|UNIQUE|KEY|PRIMARY|COMMENT|COLUMN_FORMAT|STORAGE|REFERENCES|,)/u', $line, $match) == 1){
							$default = preg_replace("/'(.+)'/", "\\1", $match[1]);
						}
						$this->column(
							$column[1], // name
							$column[2], // type
							$default, // default
							preg_match('/`[^`]+` [^\(]+\([^\)]+\) NOT NULL/', $column[0]) === 1, // null
							preg_match('/ `[^`]+` [^\(]+\([^\)]+\)[^A$]+AUTO_INCREMENT,?/u', $line) == 1 // increment
						);
						$this->columns[$column[1]]['dirty'] = false;
					}
				}
				unset($columns);
			}
		}
		public function commit(){
			$pdo = $this->pdo;
			if(!$this->exists){
				$columns = '';
				foreach($this->columns as $name => $column){
					$columns .= "{$pdo->stringColumn($name, $column)},";
				}
				if(count($columns) > 0){
					$columns = rtrim($columns, ',');
				}
				$pk = $this->primaryKey();
				if(count($pk)){
					$pk = ", primary key (".implode(',', $pk).")";
				}else{
					$pk = '';
				}
				$index = '';
				foreach($this->index as $name => $idx){
					$index .=", {$pdo->stringIndex($name, $idx)}";
				}
				$fk = '';
				foreach($this->foreignKeys as $name => $k){
					$fk .= ", {$pdo->stringForeignKey($name, $k)}";
				}
				$sql = "create table `{$this->name}` ({$columns}{$pk}{$index}{$fk})";
				$count = $pdo->exec($sql);
				if($count === false){
					throw $pdo->getError();
				}
				if(!$this->exists()){
					throw new \Exception("Unable to create table {$this->name}. Generated SQL: {$sql}");
				}
				unset($sql);
				$this->exists();
			}else{
				if(count($this->columns_renamed) > 0){
					$sql = "alter table `{$this->name}`";
					foreach($this->columns_renamed as $new => $old){
						if(isset($this->columns[$new])){
							$sql .= " change `{$old}` {$pdo->stringColumn($new, $this->columns[$new])}";
						}
					}
					$sql = rtrim($sql, ',');
					if($pdo->exec($sql) === false){
						throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
					}
				}
				$columns = array_filter($this->columns, function($item){
					return (bool)$item['dirty'];
				});
				if(count($columns) + count($this->columns_removed) > 0){
					$sql = "alter table `{$this->name}`";
					foreach($this->index_removed as $name){
						if($pdo->exec("show index from `{$this->name}` where KEY_name = ".$pdo->quote($name)) > 0){
							$sql .= " drop index {$name},";
						}
					}
					foreach($this->foreignKey_removed as $name){
						$sql .= " drop foreign key {$name},";
					}
					foreach($this->columns_removed as $name){
						$sql .= " drop column {$name},";
					}
					if(count($columns) > 0){
						$sql .= " add (";
						foreach($columns as $name => $column){
							$sql .= "{$pdo->stringColumn($name, $column)},";
						}
						$sql  = rtrim($sql , ',') . "),";
					}
					unset($columns);
					$sql = rtrim($sql, ',');
					if($pdo->exec($sql) === false){
						throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
					}
				}
				$sql = '';
				if($pdo->exec("show index from `{$this->name}` where KEY_name = 'PRIMARY'") > 0){
					$sql .= " drop primary key,";
				}
				if(count($this->primaryKey) > 0){
					$sql .= " add primary key (".implode(',', $this->primaryKey)."),";
				}
				foreach(array_filter($this->index, function($item){
					return (bool)$item['dirty'];
				}) as $name => $idx){
					$sql .= " add {$pdo->stringIndex($name, $idx)},";
				}
				if(count($sql) > 0){
					$sql = "alter table `{$this->name}`".rtrim($sql, ',');
					if($pdo->exec($sql) === false){
						throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
					}
				}
				$foreignKeys  =array_filter($this->foreignKeys, function($item){
					return (bool)$item['dirty'];
				});
				if(count($foreignKeys) > 0){
					$sql = "alter table `{$this->name}`";
					foreach($foreignKeys as $name => $fk){
						$sql .= " add {$pdo->stringForeignKey($name, $fk)},";
					}
					$sql = rtrim($sql, ',');
					if($pdo->exec($sql) === false){
						throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
					}
				}
				unset($foreignKeys);
			}
			$this->describe();
		}
		public function rollback(){
			$this->exists();
			$this->describe();
		}
		public function rename(string $type, string $old, string $new){
			switch($type){
				case self::COLUMN:
					if(isset($this->columns[$old])){
						if(!isset($this->columns[$new])){
							$this->columns[$new] = $this->columns[$old];
							unset($this->columns[$old]);
							if(isset($this->columns_renamed[$old])){
								unset($this->columns_renamed[$old]);
							}
							$this->columns_renamed[$new] = $old;
						}else{
							throw new \Exception("{$this->name}.{$new} already exists. Unable to rename {$this->name}.{$old}");
						}
					}
				break;
				default:
					throw new \Exception("Renaming {$type} is not implemented");
			}
			return $this;
		}
		public function drop(string $type = null, string $name = null){
			if(!is_null($type)){
				switch($type){
					case self::COLUMN:
						if(isset($this->columns[$name])){
							if($this->exists){
								$this->columns_removed = array_merge($this->columns_removed, [$name]);
							}
							unset($this->columns[$name]);
						}
						if(isset($this->columns_renamed[$name])){
							unset($this->columns_renamed[$name]);
						}
					break;
					case self::INDEX:
						if(isset($this->index[$name])){
							if($this->exists){
								$this->index_removed = array_merge($this->index_removed, [$name]);
							}
							unset($this->index[$name]);
						}
					break;
					case self::FOREIGN_KEY:
						if(isset($this->foreignKey[$name])){
							if($this->exists){
								$this->foreignKey_removed = array_merge($this->foreignKey_removed, [$name]);
							}
							unset($this->foreignKey[$name]);
						}
					break;
					default:
						throw new \Exception("Cannot drop {$name}. Unknown type {$type}");
				}
			}else{
				if($this->exists){
					$this->pdo->exec("drop table `{$this->name}`");
				}
				$this->rollback();
			}
			return $this;
		}
		public function column(string $name, string $type = null, $default = null, bool $null = false, bool $increment = false){
			if(!is_null($type)){
				$new = [
					'type'=> $type,
					'default'=> $default,
					'null'=> $null,
					'increment'=> $increment,
					'dirty'=> false
				];
				if(isset($this->columns[$name])){
					$old = $this->columns[$name];
					foreach($new as $key => $val){
						if($key != 'dirty' && $old[$key] !== $val){
							$new['dirty'] = true;
							break;
						}
					}
				}else{
					$new['dirty'] = true;
				}
				$this->columns[$name] = $new;
				return $this;
			}else{
				return isset($this->columns[$name]) ? $this->columns[$name] : null;
			}
		}
		public function index(string $name, array $columns = null, bool $unique = false){
			if(!is_null($columns)){
				foreach($columns as $column){
					if(!isset($this->columns[$column])){
						throw new \Exception("Can't add index. Column {$this->name}.{$column} doesn't exist");
					}
				}
				$this->index[$name] = [
					'columns'=> $columns,
					'unique'=> $unique,
					'dirty'=>true
				];
				return $this;
			}else{
				return isset($this->index[$name]) ? $this->index[$name] : null;
			}
		}
		public function addToIndex(string $name, string $column){
			if(!isset($this->index[$name])){
				throw new \Exception("Can't add column to index. Index {$this->name}.{$name} doesn't exist");
			}
			if(!isset($this->columns[$column])){
				throw new \Exception("Can't add column to index. Column {$this->name}.{$column} doesn't exist");
			}
			$this->index[$name]['columns'] = array_merge($this->index[$name]['columns'], [$column]);
		}
		public function foreignKey(string $name, string $references = null, array $columns = []){
			if(!is_null($references)){
				$table = $this->pdo->table($references);
				if(!$table->exists){
					throw new \Exception("Can't create foreign key {$name}. Table {$references} does not exist.");
				}
				foreach($columns as $column){
					if(is_null($this->column($column[0]))){
						throw new \Exception("Can't create foreign key {$name}. Column {$this->name}.{$column[0]} does not exist");
					}
					if(is_null($table->column($column[1]))){
						throw new \Exception("Can't create foreign key {$name}. Column {$references}.{$column[1]} does not exist");
					}
				}
				$this->foreignKeys[$name] = [
					'references'=> $references,
					'columns'=> $columns,
					'dirty'=> true
				];
				return $this;
			}else{
				return isset($this->foreignKeys[$name]) ? $this->foreignKeys[$name] : null;
			}
		}
		public function primaryKey(...$columns){
			if(count($columns) > 0){
				foreach($columns as $column){
					if(!isset($this->columns[$column])){
						throw new \Exception("Can't add Primary key. Column {$this->name}.{$column} doesn't exist");
					}
				}
				$this->primaryKey = $columns;
				return $this;
			}else{
				return $this->primaryKey;
			}
		}
		public function insert(array $data){
			if(!$this->exists){
				return 0;
			}
			$id = 0;
			$name = $this->name;
			$query = $this->pdo->query("insert into `{$name}` {$this->pdo->stringSet($data)}");
			$query->execute();
			$id = $this->lastInsertId();
			$query->closeCursor();
			return $id;
		}
		public function update(array $data, array $filter = null){
			return $this->exists ? $this->pdo->exec("update `{$this->name}` {$this->pdo->stringSet($data)} {$this->pdo->stringFilter($filter)}") : 0;
		}
		public function delete(array $filter = null){
			return $this->exists ? $this->pdo->exec("delete from `{$this->name}` {$this->pdo->stringFilter($filter)}") : 0;
		}
		public function fetch(array $columns = null, array $filter = null, int $start = null, int $amount = null){
			$results = [];
			$this->each(function($row) use(&$results){
				$results[] = $row;
			}, $columns, $filter, $start, $amount);
			return $results;
		}
		public function each(callable $fn, array $columns = null, array $filter = null, int $start = null, int $amount = null){
			if($this->exists){
				$limit = '';
				if(!is_null($start) && !is_null($amount)){
					$limit .= " limit {$start}, {$amount}";
				}
				$columns = $columns ?? ['*'];
				$cols = '';
				foreach($columns as $column){
					$cols .= "{$column},";
				}
				$cols = rtrim($cols, ',');
				$query = $this->pdo->query("select {$cols} from `{$this->name}` {$this->pdo->stringFilter($filter)} {$limit}");
				while($row = $query->fetch()){
					$fn($row);
				}
				$query->closeCursor();
			}
			return $this;
		}
		public function count(array $filter = null){
			if(!$this->exists){
				return 0;
			}
			$query = $this->pdo->query("select count(1) from `{$this->name}` {$this->pdo->stringFilter($filter)}");
			$count = $query->fetchColumn();
			$query->closeCursor();
			return $count;
		}
		public function lastInsertId(){
			if(!$this->exists){
				return 0;
			}
			$id = $this->pdo->lastInsertId();
			if($id == 0){
				$col = '';
				foreach($this->primaryKey as $key => $name){
					$column = $this->column($name);
					if($column['increment']){
						$col = $name;
						break;
					}
				}
				if(count($col) > 0){
					$query = $this->pdo->query("select {$col} from `{$this->name}` where {$col} = (select max({$col}) from `{$this->name}`)");
					$id = $query->fetchColumn();
				}
			}
			return $id;
		}
	}
?>