se/SE/se-lib/Core/Pdo.php

<?php

Lib::loadClass('DBG');

class Core_Pdo extends PDO {

	protected $_database_name;
	protected $_zasob_id;
	protected $_type;

	// public PDO::__construct ( string $dsn [, string $username [, string $password [, array $options ]]] )
	public function __construct($dsn, $username, $password, $options = array()) {
		$this->_database_name = $options['database'];
		$this->_zasob_id = $options['zasob_id'];
		$this->_type = $options['type'];
		unset($options['database']);
		unset($options['zasob_id']);
		parent::__construct($dsn, $username, $password, $options);
		$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
	}

	public function getDatabaseName() {
		return $this->_database_name;
	}

	public function getZasobId() {
		return $this->_zasob_id;
	}

	public function getType() {
		return $this->_type;
	}

	public function getTableStruct($tblName) {// TODO: mved to Core_Storage_*
		$sth = $this->prepare("
			-- show fields from {$tblName}
			select cols.COLUMN_NAME as name
				, cols.DATA_TYPE as type
				, cols.COLUMN_TYPE as type_mysql
				, if('YES' = cols.IS_NULLABLE, 1, 0) as is_nullable
				, cols.COLUMN_DEFAULT as default_value
				, if(cols.COLUMN_DEFAULT is null, 1, 0) as default_is_null
				, cols.CHARACTER_MAXIMUM_LENGTH as max_length
				, cols.NUMERIC_PRECISION as num_precision
				, cols.NUMERIC_SCALE as num_scale
				, cols.CHARACTER_SET_NAME as char_encoding -- latin2
				, cols.COLLATION_NAME as char_collation -- latin2_general_ci
				, cols.EXTRA as extra
		--		, cols.*
			from INFORMATION_SCHEMA.COLUMNS cols
			where cols.TABLE_SCHEMA = :db_name
				and cols.TABLE_NAME = :tbl_name
		");
		$sth->bindValue(':db_name', $this->getDatabaseName(), PDO::PARAM_STR);
		$sth->bindValue(':tbl_name', $tblName, PDO::PARAM_STR);
		$sth->execute();
		$structRaw = $sth->fetchAll();
		if (empty($structRaw)) throw new Exception("Empty struct for table '{$tblName}'", 404);
		foreach ($structRaw as $field) {
			$struct[$field['name']] = $field;
		}
		return $struct;
	}

	public function assertTableStructXsd($tblName, $expectedStructXsd) {
		throw new Exception("Unimplemented - TODO!");
/*
- `decimal(5,2)`:
	<xsd:element name="A" type="decimal_5_2"/>

	<xsd:simpleType name="decimal_5_2">
		<xsd:restriction base="xsd:decimal">
			<xsd:totalDigits value="5"/>
			<xsd:fractionDigits value="2"/>
		</xsd:restriction>
	</xsd:simpleType>
*/
/* MySQL types:
	int tinyint smallint mediumint bigint
	decimal
	float double real => double
	date datetime timestamp time year
	char varchar
	text tinytext mediumtext longtext
	enum
	set

	bit
	boolean => `tinyint(1)` -- 0 or 1
	serial  => `bigint(20) unsigned` and unique key
	binary varbinary
	blob tinyblob mediumblob longblob
	geometry point linestring polygon multipoint multilinestring multipolygon geometrycollection
*/
	}

	/*
	 * TODO: update keys:
	 * TODO: keys name may be different - try to find and connect with given schema?
	 * TODO: remove old uniq keys?
	 */
	public function assertTableStruct($tblName, $expectedStruct, $params = array()) {
		// TODO: make backup for table?
		$expectedStruct = $this->_fixExpectedStruct($expectedStruct);
		//DBG::_(true, true, "fixedEpectedStruct", $expectedStruct, __CLASS__, __FUNCTION__, __LINE__);
		//DBG::_(true, true, "fixedEpectedStruct", $this->showCreateStruct($tblName, $expectedStruct, $params), __CLASS__, __FUNCTION__, __LINE__);
		$struct = $this->getTableStruct($tblName);
		$expectedFields = array();//array_keys($expectedStruct);
		foreach ($expectedStruct as $fldName => $fld) {
			if ('UNIQUE KEY' == $fld['type']) continue;
			if ('KEY' == $fld['type']) continue;
			$expectedFields[] = $fldName;
		}
		$currentFields = array_keys($struct);

		$missingFields = array_diff($expectedFields, $currentFields);
		DBG::_(true, true, "struct", $struct, __CLASS__, __FUNCTION__, __LINE__);
		DBG::_(true, true, "missingFields", $missingFields, __CLASS__, __FUNCTION__, __LINE__);

		foreach ($missingFields as $fldName) {
			$fld = $expectedStruct[$fldName];
			DBG::_(true, true, "add missing field[{$fldName}]:", $fld, __CLASS__, __FUNCTION__, __LINE__);
			$sqlFieldStruct = $this->showTableStructField($fldName, $fld);
			if ($sqlFieldStruct) {
				$sqlAdd = "alter table {$tblName} add {$sqlFieldStruct}";
				DBG::_(true, true, "sqlAdd", $sqlAdd, __CLASS__, __FUNCTION__, __LINE__);
				$this->exec($sqlAdd);
			} else {
				throw new Exception("Unimplemented type '{$fld['type']}': " . json_encode($fld));
			}
		}

		$toUpdateFields = array_intersect($expectedFields, $currentFields);
		DBG::_(true, true, "toUpdateFields", $toUpdateFields, __CLASS__, __FUNCTION__, __LINE__);
		foreach ($toUpdateFields as $fldName) {
			$current = $struct[$fldName];
			$expected = $expectedStruct[$fldName];
			$needChange = false;

			$sqlFieldStruct = $this->showTableStructField($fldName, $expected);
			if (!$sqlFieldStruct) throw new Exception("Unimplemented type '{$expected['type']}' for field '{$fldName}': " . json_encode($expected));
			$sqlChange = "alter table `{$tblName}` change `{$fldName}` {$sqlFieldStruct}";
			DBG::_(true, true, "DBG: sqlChange", $sqlChange, __CLASS__, __FUNCTION__, __LINE__);

			//DBG::_(true, true, "TODO: update field[{$fldName}]:", array('expected'=>$expected,'current'=>$current), __CLASS__, __FUNCTION__, __LINE__);
			if ($current['type'] != $expected['type']) {
				throw new Exception("Unimplemented change field type from '{$current['type']}' to '{$expected['type']}' for field '{$fldName}': " . json_encode($expected));
			}

			if ($current['is_nullable'] != $expected['is_nullable']) {
				$needChange = true;
				if ($current['is_nullable'] && !$expected['is_nullable']) {
					throw new Exception("Field struct needs change 'is_nullable' to false but this change is not implemented - field '{$fldName}': " . json_encode(array('expected'=>$expected,'current'=>$current)));
				}
			}
			if ($current['max_length'] != $expected['max_length']) {
				$needChange = true;
				if ($current['max_length'] > $expected['max_length']) {
					throw new Exception("Field struct needs decrease 'max_length' but this change is not implemented - field '{$fldName}': " . json_encode(array('expected'=>$expected,'current'=>$current)));
				}
			}
			if ($current['default_value'] !== $expected['default_value']) $needChange = true;

			if ($needChange) {
				DBG::_(true, true, "EXEC sqlChange ...", $sqlChange, __CLASS__, __FUNCTION__, __LINE__);
				$this->exec($sqlChange);
			}
		}
	}

	/* assert's that field struct has defined or throw exception if cannot set default value:
			['type'] = 'varchar', 'int', ... (MySQL Types)
			['is_nullable'] = true, false
			['default_value'] = NULL or (string, numeric - based on type)
			  default_value is not set when default_value == NULL and is_nullable == false
			['default_is_null'] = true, false
			['max_length'] = NULL, [0-...] // MySQL `CHARACTER_MAXIMUM_LENGTH`
			['num_precision'] = NULL, [0-65]
			['num_scale'] = NULL, [0-12]
			['char_encoding'] = 'utf8', 'latin2', ...
			['char_collation'] = 'utf8_general_ci', 'latin2_general_ci', ...
			['extra'] = 'auto_increment', 'on update CURRENT_TIMESTAMP'
			['values'] = null or array for enum and set

	TODO: validate - wrong related values. ex: {type: 'int', char_collation: 'latin2'}
	TODO: validate - not allowed value. ex: {type: 'xyz'}
	*/
	public function _fixExpectedStruct($expectedStruct) {
		$fixedStruct = array();
		foreach ($expectedStruct as $fldName => $expected) {
			//DBG::_(true, true, "TODO: expected", $expected, __CLASS__, __FUNCTION__, __LINE__);
			if (!array_key_exists('type', $expected)) throw new Exception("Undefined type for field '{$fldName}'");

			if (!array_key_exists('is_nullable', $expected)) {
				$expected['is_nullable'] = false;
				if (array_key_exists('default_value', $expected) && null === $expected['default_value']) {
					$expected['is_nullable'] = true;
				}
			}
			if (!array_key_exists('default_is_null', $expected)) $expected['default_is_null'] = false;
			if (!array_key_exists('default_value', $expected)) {
				$expected['default_value'] = null;
//				switch ($expected['type']) {
//					case 'char':
//					case 'varchar': $expected['default_value'] = ($expected['is_nullable'])? null : ''; break;
//					case 'tinyint':
//					case 'bigint':
//					case 'int': $expected['default_value'] = ($expected['is_nullable'])? null : 0; break;
//				}
				if ($expected['is_nullable'] && null === $expected['default_value']) {
					$expected['default_is_null'] = true;
				}
			}

			if (!array_key_exists('max_length', $expected)) {
				switch ($expected['type']) {
					case 'char':
					case 'varchar': $expected['max_length'] = 255; break;
					case 'binary': $expected['max_length'] = 255; break;// bainary(0) is possible - why? Cannot store values.
					case 'varbinary': $expected['max_length'] = 255; break;
					case 'binary':
					case 'varbinary':
					case 'bit': throw new Exception("Undefined max_length for field '{$fldName}' with type '{$expected['type']}'");
					//case 'blob': $expected['max_length'] = 65535; break;// is set by engine
					//case 'tinyblob': $expected['max_length'] = 255; break;// is set by engine
					//case 'mediumblob': $expected['max_length'] = 16777215; break;// is set by engine
					//case 'longblob': $expected['max_length'] = 4294967295; break;// is set by engine
					//case 'text': $expected['max_length'] = 65535; break;// is set by engine
					//case 'tinytext': $expected['max_length'] = 255; break;// is set by engine
					//case 'mediumtext': $expected['max_length'] = 16777215; break;// is set by engine
					//case 'longtext': $expected['max_length'] = 4294967295; break;// is set by engine
					//case 'enum': $expected['max_length'] = 255; break;// is set by engine
					//case 'set': $expected['max_length'] = 255; break;// is set by engine
					default: $expected['max_length'] = null;//throw new Exception("Undefined max_length for field '{$fldName}'");
				}
			}
			if (!array_key_exists('num_precision', $expected)) {
				switch ($expected['type']) {
					case 'int': $expected['num_precision'] = 10; break;// int(11) - +1 in type definition
					case 'tinyint': $expected['num_precision'] = 3; break;// int(4)
					case 'smallint': $expected['num_precision'] = 5; break;// int(6)
					case 'mediumint': $expected['num_precision'] = 7; break;// int(8)
					case 'bigint': $expected['num_precision'] = 19; break;// int(20)
					case 'decimal': $expected['num_precision'] = 10; break;// decimal(10,0)
					default: $expected['num_precision'] = null;
				}
				//throw new Exception("Undefined num_precision for field '{$fldName}'");
			}
			if (!array_key_exists('num_scale', $expected)) {
				switch ($expected['type']) {
					case 'int': $expected['num_scale'] = 0; break;
					case 'tinyint': $expected['num_scale'] = 0; break;
					case 'smallint': $expected['num_scale'] = 0; break;
					case 'mediumint': $expected['num_scale'] = 0; break;
					case 'bigint': $expected['num_scale'] = 0; break;
					case 'decimal': $expected['num_scale'] = 0; break;// ex.: decimal(3,2); decimal(24,0)
					case 'double': $expected['num_scale'] = null; break;// ex.: double(10,4); double; double(17,0);
					case 'float': $expected['num_scale'] = null; break;// ex.: float(6,2); float; float(17,0);
					default: $expected['num_scale'] = null;
				}
				//throw new Exception("Undefined num_scale for field '{$fldName}'");
			}
			if (!array_key_exists('char_encoding', $expected)) {
				switch ($expected['type']) {
					case 'char':
					case 'varchar': $expected['char_encoding'] = 'utf8'; break;
					case 'enum':
					case 'set': $expected['char_encoding'] = 'utf8'; break;
					case 'text':
					case 'tinytext':
					case 'mediumtext':
					case 'longtext': $expected['char_encoding'] = 'utf8'; break;
					default: $expected['char_encoding'] = null;
				}
				//throw new Exception("Undefined char_encoding for field '{$fldName}'");
			}
			if (!array_key_exists('char_collation', $expected)) {
				switch ($expected['type']) {
					case 'char':
					case 'varchar': $expected['char_collation'] = 'utf8_general_ci'; break;
					case 'enum':
					case 'set': $expected['char_collation'] = 'utf8_general_ci'; break;
					case 'text':
					case 'tinytext':
					case 'mediumtext':
					case 'longtext': $expected['char_collation'] = 'utf8_general_ci'; break;
					default: $expected['char_collation'] = null;
				}
				//throw new Exception("Undefined char_collation for field '{$fldName}'");
			}
			if (!array_key_exists('extra', $expected)) {
				$expected['extra'] = null;//throw new Exception("Undefined extra for field '{$fldName}'");
			}
			if (!array_key_exists('values', $expected) || !is_array($expected['values']) || empty($expected['values'])) {
				switch ($expected['type']) {
					case 'enum':
					case 'set': throw new Exception("Undefined values for field '{$fldName}' with type '{$expected['type']}'");
					default: $expected['values'] = null;
				}
			}

			$fixedStruct[$fldName] = $expected;
		}
		return $fixedStruct;
	}

	public function showCreateStruct($tblName, $struct, $params = array()) {
		// TODO: check database type, if == MySQL - fix construct
		$expectedStruct = $this->_fixExpectedStruct($struct);
		$linesSql = array();
		$dbgSql = array();
		foreach ($expectedStruct as $fldName => $fld) {
			$sqlFieldStruct = $this->showTableStructField($fldName, $fld);
			if ($sqlFieldStruct) {
				$linesSql[] = $sqlFieldStruct;
			} else {
				$dbgSql[] = "-- Unimplemented type '{$fld['type']}': " . json_encode($fld);
			}
		}
		$linesSql = implode("\n\t\t, ", $linesSql);
		$dbgSql = implode("\n\t\t", $dbgSql);
		$tblCharEncoding = V::get('char_encoding', 'utf8', $params);
		$structSql = <<<EOF_STRUCT_MYSQL
	CREATE TABLE IF NOT EXISTS `{$tblName}` (
		{$linesSql}
		{$dbgSql}
	) ENGINE=MyISAM DEFAULT CHARSET={$tblCharEncoding}
EOF_STRUCT_MYSQL;
		return $structSql;
	}

	public function showTableStructField($fldName, $fld) {
		// TODO: check database type, if == MySQL - fix construct
		$nullSql = ($fld['is_nullable'])? '' : 'NOT NULL';
		$defaultSql = (is_null($fld['default_value']))? 'DEFAULT NULL' : "DEFAULT '{$fld['default_value']}'";
		if (is_null($fld['default_value']) && !$fld['is_nullable']) $defaultSql = '';
		switch ($fld['type']) {
			case 'char':
			case 'varchar': return "`{$fldName}` {$fld['type']}({$fld['max_length']}) {$nullSql} {$defaultSql}"; break;
			case 'text':
			case 'tinytext':
			case 'longtext':
			case 'mediumtext': return "`{$fldName}` {$fld['type']} {$nullSql}"; break;
			case 'time':
			case 'timestamp':
			case 'year':
			case 'date':
			case 'datetime': return "`{$fldName}` {$fld['type']} {$nullSql} {$defaultSql}"; break;
// 		-- Unimplemented type 'int': {"type":"int","is_nullable":false,"default_is_null":false,"default_value":null,"max_length":null,"num_precision":10,"num_scale":0,"char_encoding":null,"char_collation":null,"extra":null}
//		-- Unimplemented type 'int': {"type":"int","num_precision":11,"default_value":null,"is_nullable":true,"default_is_null":false,"max_length":null,"num_scale":0,"char_encoding":null,"char_collation":null,"extra":null}
			case 'int':
			case 'tinyint':
			case 'smallint':
			case 'mediumint':
			case 'bigint':
				if ($fld['num_precision'] > 0) {
					$typeParamsSql = "(" . ($fld['num_precision'] + 1) . ")";
				}
				//if ($fld['num_scale']) $typeParamsSql = ",{$fld['num_scale']}";
				return "`{$fldName}` {$fld['type']}{$typeParamsSql} {$nullSql} {$defaultSql}";
				break;
			case 'decimal':
				$typeParamsSql = "{$fld['num_precision']},{$fld['num_scale']}";
				return "`{$fldName}` {$fld['type']}({$typeParamsSql}) {$nullSql} {$defaultSql}";
				break;
			case 'float':
			case 'double':
			case 'real':
				return "`{$fldName}` {$fld['type']} {$nullSql} {$defaultSql}";
				break;
			case 'enum':
			case 'set':
				$typeParamsSql = "'" . implode("','", $fld['values']) . "'";
				return "`{$fldName}` {$fld['type']}({$typeParamsSql}) {$nullSql} {$defaultSql}";
				break;
			case 'bit':
			case 'binary':
			case 'varbinary':
				return "`{$fldName}` {$fld['type']}({$fld['max_length']}) {$nullSql} {$defaultSql}";
				break;
			case 'boolean':
			case 'serial':
			case 'blob':
			case 'tinyblob':
			case 'mediumblob':
			case 'longblob':
				return "`{$fldName}` {$fld['type']} {$nullSql} {$defaultSql}";
				break;
			case 'geometry':
			case 'point':
			case 'linestring':
			case 'polygon':
			case 'multipoint':
			case 'multilinestring':
			case 'multipolygon':
			case 'geometrycollection':
				return "`{$fldName}` {$fld['type']} {$nullSql} {$defaultSql}";
				break;
			case 'UNIQUE KEY':
				$keyFieldsSql = "`" . implode("`,`", $fld['key_fields']) . "`";
				return "UNIQUE KEY `{$fldName}` ({$keyFieldsSql})";
				break;
			case 'KEY':
				$keyFieldsSql = "`" . implode("`,`", $fld['key_fields']) . "`";
				return "KEY `{$fldName}` ({$keyFieldsSql})";
				break;
			case 'PRIMARY KEY':
				$keyFieldsSql = "`" . implode("`,`", $fld['key_fields']) . "`";
				return "PRIMARY KEY ({$keyFieldsSql})";
				break;
		}
		return null;
	}

	// for sql like `select count() from ...`
	public function fetchValue($sql) {
		DBG::_('DBG_SQL', '>3', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
		DBG::log($sql, 'sql');
		$sth = $this->query($sql);
		return $sth->fetchColumn();
	}

	public function fetchFirst($sql) {// fetch only first row
		DBG::_('DBG_SQL', '>3', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
		DBG::log($sql, 'sql');
		$sth = $this->prepare($sql);
		$sth->execute();
		return $sth->fetch();
	}

	public function fetchAll($sql) {
		DBG::_('DBG_SQL', '>3', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
		DBG::log($sql, 'sql');
		$sth = $this->prepare($sql);
		$sth->execute();
		return $sth->fetchAll();
	}

	public function fetchAllByKey($sql, $key = 'ID') {
		DBG::_('DBG_SQL', '>3', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
		DBG::log($sql, 'sql');
		$rowsByKey = array();
		$sth = $this->prepare($sql);
		$sth->execute();
		$rows = $sth->fetchAll();
		foreach ($rows as $row) {
			$keyRow = V::get($key, null, $row);
			$rowsByKey[$keyRow] = $row;
		}
		return $rowsByKey;
	}

	public function bindValues($sth, $values) {
		foreach ($values as $name => $value) {
			$val = $value;
			$type = PDO::PARAM_STR;
			if (is_array($value)) {
				$val = $value[0];
				if (count($value) > 1) {
					$type = $value[1];
				}
			}
			$sth->bindValue($name, $val, $type);
			if (!isset($sth->bindedValues)) $sth->bindedValues = array();
			$sth->bindedValues[$name] = array($val, $type);
		}
	}

	public function getRawSql($sth, $values = array()) {
		$sql = $sth->queryString;
		$params = array();
		if (!empty($sth->bindedValues)) {
			foreach ($sth->bindedValues as $name => $value) {
				$params[$name] = array($value[0], $value[1]);
			}
		}
		foreach ($values as $name => $value) {
			$val = $value;
			$type = PDO::PARAM_STR;
			if (is_array($value)) {
				$val = $value[0];
				if (count($value) > 1) {
					$type = $value[1];
				}
			}
			$params[$name] = array($val, $type);
		}

		if (!empty($params)) {
			foreach ($params as $name => $val) {
				$outValue = $val[0];
				if (PDO::PARAM_STR == $val[1]) $outValue = "'{$outValue}'";
				$sql = str_replace(":{$name}", $outValue, $sql);
			}
		}
		return $sql;
	}

	public function insert($tableName, $item, $sqlSchema = []) {// @returns int last inserted id
		$sqlFields = [];
		$sqlValues = [];
		foreach ($item as $field => $val) {
			if ('NOW()' == $val) {
				$sqlVal = 'NOW()';
			} else {
				$sqlVal = $this->quote($val, PDO::PARAM_STR);// TODO: use $sqlSchema if set
			}
			$sqlFields[] = "`{$field}`";
			$sqlValues[] = $sqlVal;
		}
		$sql = "
			insert into `{$tableName}` (" . implode(", ", $sqlFields) . ")
				values (" . implode(", ", $sqlValues) . ")
		";
		DBG::_('DBG_SQL', '>3', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
		DBG::log($sql, 'sql');
		$this->exec($sql);
		return $this->lastInsertId();
	}

	public function update($tableName, $primaryKeyName, $primaryKey, $item, $sqlSchema = []) {// @returns int affected rows
		if (empty($primaryKeyName)) throw new Exception("Missing primaryKey name");
		if (empty($primaryKey)) throw new Exception("Missing primaryKey");
		if (empty($item) || !is_array($item)) throw new Exception("Missing item");
		$sqlPrimaryKey = $this->quote($primaryKey, PDO::PARAM_STR);
		$sqlUpdateSet = [];
		foreach ($item as $field => $val) {
			if ('NOW()' == $val) {
				$sqlVal = 'NOW()';
			} else {
				$sqlVal = $this->quote($val, PDO::PARAM_STR);// TODO: use $sqlSchema if set
			}
			$sqlUpdateSet[] = "`{$field}` = {$sqlVal}";
		}
		$sql = "
			update `{$tableName}`
			set " . implode("\n , ", $sqlUpdateSet) . "
			where `{$primaryKeyName}` = {$sqlPrimaryKey}
		";
		DBG::_('DBG_SQL', '>3', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
		DBG::log($sql, 'sql');
		return $this->exec($sql);
	}

	public function execSql($sql) {
		DBG::_('DBG_SQL', '>3', "sql", $sql, __CLASS__, __FUNCTION__, __LINE__);
		DBG::log($sql, 'sql');
		return $this->exec($sql);
	}

}