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

<?php

class Core_Pdo extends PDO {

	protected $_database_name;
	protected $_zasob_id;

	// 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'];
		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 getTableStruct($tblName) {
		$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}'");
		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: 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()) {
		// $expectedStruct['t1'] = array('type'=>'varchar', 'max_length'=>300, 'default_value'=>null)
		$expectedStruct = $this->_fixExpectedStruct($expectedStruct);
		//DBG::_(true, true, "fixedEpectedStruct", $expectedStruct, __CLASS__, __FUNCTION__, __LINE__);
		//DBG::_(true, true, "fixedEpectedStruct", $this->showCreateStructMysql($tblName, $expectedStruct, $params), __CLASS__, __FUNCTION__, __LINE__);
		$struct = $this->getTableStruct($tblName);
		$expectedFields = array_keys($expectedStruct);
		$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 => $expected) {
			DBG::_(true, true, "TODO: add missing field[{$fldName}]:", $expected, __CLASS__, __FUNCTION__, __LINE__);
			$sqlType = array();
			$sqlType['type'] = $expected['type'];
			$sqlType['null'] = ($expected['is_nullable'])? '' : 'NOT NULL';
			$sqlType['default'] = ($expected['is_nullable'])? '' : 'NOT NULL';
			$sqlAdd = "alter table CRM_NOTIFY add {$fldName} {$sqlType['type']} {$sqlType['null']} {$sqlType['default']}";
			DBG::_(true, true, "TODO: sqlChange", $sqlAdd, __CLASS__, __FUNCTION__, __LINE__);
		}

		$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;
			$toChange = array();
			$toChange['type'] = $current['type'];
			$toChange['null'] = ($current['is_nullable'])? '' : 'NOT NULL';
			// TODO: cannot determine when default is NULL
			$toChange['default'] = '';
			if (!empty($current['default_value'])) $toChange['default'] = "DEFAULT '{$current['default_value']}'";
			if (is_null($current['default_value'])) $toChange['default'] = "DEFAULT NULL";

			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']}");
			}

			if ($current['is_nullable'] != $expected['is_nullable']) {
				if (!$current['is_nullable']) {
					$toChange['null'] = '';
				}
			}
			$sqlChange = "alter table CRM_NOTIFY change {$fldName} {$fldName} {$toChange['type']} {$toChange['null']} {$toChange['default']}";
			if ($needChange) {
				DBG::_(true, true, "TODO: sqlChange", $sqlChange, __CLASS__, __FUNCTION__, __LINE__);
			}
		}
		if(0)foreach ($struct as $col) {
			$fldName = $col['Field'];
			if (!array_key_exists($fldName, $expectedStruct)) continue;// skip not defined cols
			$expected = $expectedStruct[$fldName];
			$toChange = array();
			$toChange['need_change'] = false;
			$toChange['type'] = $col['Type'];
			$toChange['null'] = ('YES' == $col['Null'])? '' : 'NOT NULL';
			// TODO: cannot determine when default is NULL
			$toChange['default'] = (!is_null($col['Default']))? "DEFAULT {$col['Default']}" : '';

			$expectedNullable = (array_key_exists('nullable', $expected) && $expected['nullable']);
			$colNullable = ('YES' == $col['Null']);
			if ($expectedNullable != $colNullable) {
				$toChange['null'] = ($expectedNullable)? '' : 'NOT NULL';
				$toChange['need_change'] = true;
			}

			// examples:
			//   1 - NOT NULL
			//   2 - NOT NULL DEFAULT ''
			// x 3 - NOT NULL DEFAULT NULL
			// x 4 - NULL
			//   5 - NULL DEFAULT ''
			//   6 - NULL DEFAULT NULL
			//       1 == 2, 4 -> 6
			$expectedHasDefault = array_key_exists('default', $expected);
			$expectedDefaultValue = (array_key_exists('default', $expected))? $expected['default'] : "''";
			$colDefault = $col['Default'];
			if ($expectedDefault != $colDefault) {
				$toChange['default'] = 'DEFAULT ' . ((is_null($expectedDefault))? 'NULL' : $expectedDefault);
				$toChange['need_change'] = true;
			}

			// ALTER TABLE `CRM_NOTIFY` CHANGE `who` `who` VARCHAR(20) NOT NULL
			// ALTER TABLE `CRM_NOTIFY` CHANGE `who` `who` VARCHAR(20) NULL
			// ALTER TABLE `CRM_NOTIFY` CHANGE `who` `who` VARCHAR(20) NULL DEFAULT NULL
			$sqlChange = "alter table CRM_NOTIFY change {$fldName} {$fldName} {$toChange['type']} {$toChange['null']} {$toChange['default']}";
			if ($toChange['need_change']) {
				DBG::_(true, true, "TODO: sqlChange", $sqlChange, __CLASS__, __FUNCTION__, __LINE__);
			}
		}
	}

	/* assert's that field struct has defined or throw exception if cannot set default value:
			['type'] = 'int', 'varchar', ... (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 showCreateStructMysql($tblName, $struct, $params = array()) {
		$expectedStruct = $this->_fixExpectedStruct($struct);
		$linesSql = array();
		$dbgSql = array();
		foreach ($expectedStruct as $fldName => $fld) {
			$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': $linesSql[] = "`{$fldName}` {$fld['type']}({$fld['max_length']}) {$nullSql} {$defaultSql}"; break;
				case 'text':
				case 'tinytext':
				case 'longtext':
				case 'mediumtext': $linesSql[] = "`{$fldName}` {$fld['type']} {$nullSql}"; break;
				case 'time':
				case 'timestamp':
				case 'year':
				case 'date':
				case 'datetime': $linesSql[] = "`{$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']}";
					$linesSql[] = "`{$fldName}` {$fld['type']}{$typeParamsSql} {$nullSql} {$defaultSql}";
					break;
				case 'decimal':
					$typeParamsSql = "{$fld['num_precision']},{$fld['num_scale']}";
					$linesSql[] = "`{$fldName}` {$fld['type']}({$typeParamsSql}) {$nullSql} {$defaultSql}";
					break;
				case 'float':
				case 'double':
				case 'real':
					$linesSql[] = "`{$fldName}` {$fld['type']} {$nullSql} {$defaultSql}";
					break;
				case 'enum':
				case 'set':
					$typeParamsSql = "'" . implode("','", $fld['values']) . "'";
					$linesSql[] = "`{$fldName}` {$fld['type']}({$typeParamsSql}) {$nullSql} {$defaultSql}";
					break;
				case 'bit':
				case 'binary':
				case 'varbinary':
					$linesSql[] = "`{$fldName}` {$fld['type']}({$fld['max_length']}) {$nullSql} {$defaultSql}";
					break;
				case 'boolean':
				case 'serial':
				case 'blob':
				case 'tinyblob':
				case 'mediumblob':
				case 'longblob':
					$linesSql[] = "`{$fldName}` {$fld['type']} {$nullSql} {$defaultSql}";
					break;
				case 'geometry':
				case 'point':
				case 'linestring':
				case 'polygon':
				case 'multipoint':
				case 'multilinestring':
				case 'multipolygon':
				case 'geometrycollection':
					$linesSql[] = "`{$fldName}` {$fld['type']} {$nullSql} {$defaultSql}";
					break;
				case 'UNIQUE KEY':
					$keyFieldsSql = "`" . implode("`,`", $fld['key_fields']) . "`";
					$linesSql[] = "UNIQUE KEY `{$fldName}` ({$keyFieldsSql})";
					break;
				case 'KEY':
					$keyFieldsSql = "`" . implode("`,`", $fld['key_fields']) . "`";
					$linesSql[] = "KEY `{$fldName}` ({$keyFieldsSql})";
					break;
//			$expectedStruct['uniq_key_1'] = array('type'=>'UNIQUE KEY', 'key_fields'=>array('who','when','what'));// UNIQUE KEY `uniq_key_1` (`who`,`when`,`what`)
//			$expectedStruct['key_who'] = array('type'=>'KEY', 'key_fields'=>array('who'));// KEY `key_who` (`who`)
				default: $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 fetchAll($sql) {
		$sth = $this->prepare($sql);
		$sth->execute();
		return $sth->fetchAll();
	}

	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;
	}

}