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 {
		private $pdo;
		private $name;
		private $exists;
		private $columns;
		private $primaryKey;
		private $index;
		private $foreignKey;

		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 = [];
			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
						];
						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 stringFilter(...$args){
			return $this->pdo->stringFilter(...$args);
		}
		public function stringSet(...$args){
			return $this->pdo->stringSet(...$args);
		}
		public function commit(){
			$pdo = $this->pdo;
			if(!$this->exists){
				$columns = '';
				foreach($this->columns as $name => $column){
					$default = '';
					if(!is_null($column['default'])){
						$default .= "DEFAULT {$pdo->quote($column['default'])}";
					}
					$null = $column['null'] ? 'NULL' : 'NOT NULL';
					$ai = $column['increment'] ? 'AUTO_INCREMENT' : '';
					$columns .= "{$name} {$column['type']} {$null} {$default} {$ai},";
				}
				if(count($columns) > 0){
					$columns = rtrim($columns, ',');
				}
				$pk = $this->primaryKey();
				if(count($pk)){
					$pk = ", primary key (".implode(',',$pk).")";
				}
				$index = '';
				if(count($this->index) > 0){
					foreach($this->index as $name => $idx){
						if($idx['unique']){
							$index .= ", constraint unique index {$name} (".implode(',', $idx['columns']).")";
						}else{
							$index .= ", index {$name} (".implode(',', $idx['columns']).")";
						}
					}
				}
				$fk = '';
				if(count($this->foreignKeys) > 0){
					foreach($this->foreignKeys as $name => $k){
						$cols0 = '';
						$cols1 = '';
						foreach($k['columns'] as $row){
							$cols0 .= "{$row[0]},";
							$cols1 .= "{$row[1]},";
						}
						$cols0 = rtrim($cols0, ',');
						$cols1 = rtrim($cols1, ',');
						$fk .= ", constraint `{$name}` foreign key ({$cols0}) references `{$k['references']}` ({$cols1})";
					}
				}
				$count = $pdo->exec("create table `{$this->name}` ({$columns}{$pk}{$index}{$fk})");
				if($count === false){
					throw $pdo->getError();
				}
				if(!$this->exists()){
					throw new \Exception("Unable to create table {$this->name}");
				}
				$this->exists();
			}else{
				// @todo alter table to add and remove columns
				$columns = array_filter($this->columns, function($item){
					return $item['dirty'];
				});
			}
			$this->describe();
		}
		public function rollback(){
			$this->exists();
			$this->describe();
		}
		public function drop(){
			if($this->exists){
				$this->pdo->exec("drop table `{$this->name}`");
			}
		}
		public function column(string $name, string $type = null, string $default = null, bool $null = false, bool $increment = false){
			if(!is_null($type)){
				$this->columns[$name] = [
					'type'=> $type,
					'default'=> $default,
					'null'=> $null,
					'increment'=> $increment,
					'dirty'=> true
				];
				$column['dirty'] = true;
				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
				];
				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){
			return $this->exists ? $this->pdo->exec("insert into `{$this->name}` {$this->stringSet($data)}") : 0;
		}
		public function update(array $data, array $filter = null){
			return $this->exists ? $this->pdo->exec("update `{$this->name} {$this->stringSet($data)} {$this->stringFilter($filter)}`") : 0;
		}
		public function delete(array $filter = null){
			return $this->exists ? $this->pdo->exec("delete from `{$this->name}` {$this->stringFilter($filter)}") : 0;
		}
		public function fetch(array $filter = null){
			if($this->exists){
				$query = $this->pdo->query("select * from `{$this->name}` {$this->stringFilter($filter)}");
				$results = $query->fetchAll();
				$query->closeCursor();
			}else{
				$results = [];
			}
			return $results;
		}
		public function count(array $filter = null){
			return $this->exists ? $this->pdo->exec("select 1 from `{$this->name}` {$this->stringFilter($filter)}") : 0;
		}
	}
?>