se/SE/se-lib/Core/Storage/Mysql.php

<?php

Lib::loadClass('Core_StorageBase');
Lib::loadClass('Core_StorageFactory');

class Core_Storage_Mysql extends Core_StorageBase {

	protected $_databaseName = '';
	protected $_zasob_id = 0;
	protected $_pdo = null;

	public function __construct($pdo) {
		$this->_pdo = $pdo;
		$this->_databaseName = $pdo->getDatabaseName();
		$this->_zasob_id = $pdo->getZasobId();
	}

	public function fetchAllAssoc($sql) {
		if (empty($sql)) {
			throw new Exception("Empty query");
		}
		$stmt = $this->_pdo->query($sql);
		return $stmt->fetchAll(PDO::FETCH_ASSOC);
	}

	public function query($query) {
		throw new Exception("Storage " . __FUNCTION__ . " not defined");
		$res = mysql_query($query, $this->_conn);
		if (!$res) {
			$this->_set_error('SQL QUERY FAILED: '.mysql_error($this->_conn));
			return $null;
		}
	}

	public function getTableStruct($tblName) {
		$sql = "
			-- show fields from {$tblName}
			select cols.COLUMN_NAME as name
				, cols.DATA_TYPE as type
				, 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.COLUMN_TYPE as raw_storage_type
		--		, cols.*
			from INFORMATION_SCHEMA.COLUMNS cols
			where cols.TABLE_SCHEMA like :db_name
				and cols.TABLE_NAME like :tbl_name
		";
		$sth = $this->_pdo->prepare($sql);
		$sth->bindValue(':db_name', $this->_pdo->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;
	}

	protected function _getTableStruct($tblName) {
		throw new Exception("Turned OFF C." . __CLASS__ . " F." . __FUNCTION__);

		$tblStructRaw = $this->getTableStructRaw($tblName);
		$tblStruct = array();
		foreach ($tblStructRaw as $fieldStruct) {
			$fldName = $fieldStruct['COLUMN_NAME'];
			$storageType = '';
			/*
				BOOLEAN xsd: {true, false, 1, 0}
				SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
				SET
			*/
			if ('varchar' == $fieldStruct['DATA_TYPE']
					|| 'char' == $fieldStruct['DATA_TYPE']
				 ) {
				$storageType = Core_StorageFactory::getType('String');
				if ($fieldStruct['CHARACTER_MAXIMUM_LENGTH'] > 0) {
					$storageType->setMaxLength($fieldStruct['CHARACTER_MAXIMUM_LENGTH']);
				}
			}
			else if ('text' == $fieldStruct['DATA_TYPE']
							 || 'longtext' == $fieldStruct['DATA_TYPE']
							 || 'mediumtext' == $fieldStruct['DATA_TYPE']
							 || 'tinytext' == $fieldStruct['DATA_TYPE']
							 ) {
				$storageType = Core_StorageFactory::getType('Text');
			}
			else if ('int' == $fieldStruct['DATA_TYPE']
							 || 'tinyint' == $fieldStruct['DATA_TYPE']
							 || 'smallint' == $fieldStruct['DATA_TYPE']
							 || 'mediumint' == $fieldStruct['DATA_TYPE']
							 || 'bigint' == $fieldStruct['DATA_TYPE']
							 || 'bit' == $fieldStruct['DATA_TYPE']
							) {
				$storageType = Core_StorageFactory::getType('Integer');
				$storageType->setTotalDigits($fieldStruct['NUMERIC_PRECISION']);
			}
			else if ('decimal' == $fieldStruct['DATA_TYPE']) {
				$storageType = Core_StorageFactory::getType('Decimal');
				if ($fieldStruct['NUMERIC_SCALE'] > 0) {
					$storageType->setFractionDigits($fieldStruct['NUMERIC_SCALE']);
				}
				if ($fieldStruct['NUMERIC_PRECISION'] > 0) {
					$storageType->setTotalDigits($fieldStruct['NUMERIC_PRECISION']);
				}
			}
			else if ('double' == $fieldStruct['DATA_TYPE']
							 || 'real' == $fieldStruct['DATA_TYPE']
							 || 'float' == $fieldStruct['DATA_TYPE']
							) {
				$storageType = Core_StorageFactory::getType('Double');
			}
			else if ('binary' == $fieldStruct['DATA_TYPE']
							 || 'varbinary' == $fieldStruct['DATA_TYPE']
							 || 'blob' == $fieldStruct['DATA_TYPE']
							 || 'tinyblob' == $fieldStruct['DATA_TYPE']
							 || 'longblob' == $fieldStruct['DATA_TYPE']
							 || 'mediumblob' == $fieldStruct['DATA_TYPE']
							) {
				$storageType = Core_StorageFactory::getType('Binary');
				$storageType->setRestriction('maxExclusive', $fieldStruct['CHARACTER_MAXIMUM_LENGTH']);
			}
			else if ('enum' == $fieldStruct['DATA_TYPE']) {
				$storageType = Core_StorageFactory::getType('Enumeration');
				$enumValues = explode(',', str_replace(array('(',')',"'",'"'), '', substr($fieldStruct['COLUMN_TYPE'], 5)));
				$enumList = array();
				foreach ($enumValues as $value) {
					$enumList[] = $value;
				}
				$storageType->setEnumeration($enumList);
			}
			else if ('timestamp' == $fieldStruct['DATA_TYPE']) {
				$storageType = Core_StorageFactory::getType('DateTime');
				$storageType->setFormat('yyyy-MM-dd hh:mm:ss');
			}
			else if ('datetime' == $fieldStruct['DATA_TYPE']) {
				$storageType = Core_StorageFactory::getType('DateTime');
				$storageType->setFormat('yyyy-MM-dd hh:mm');
			}
			else if ('date' == $fieldStruct['DATA_TYPE']) {
				$storageType = Core_StorageFactory::getType('Date');
				$storageType->setFormat('yyyy-MM-dd');
			}
			else if ('time' == $fieldStruct['DATA_TYPE']) {
				$storageType = Core_StorageFactory::getType('Time');
				$storageType->setFormat('hh:mm:ss');
			}
			else if ('year' == $fieldStruct['DATA_TYPE']) {
				$storageType = Core_StorageFactory::getType('Year');
				$storageType->setFormat('yyyy');
			}
			else if ('point' == $fieldStruct['DATA_TYPE']) {
				$storageType = Core_StorageFactory::getType('Point');
			}
			else if ('linestring' == $fieldStruct['DATA_TYPE']) {
				$storageType = Core_StorageFactory::getType('Linestring');
			}
			else if ('polygon' == $fieldStruct['DATA_TYPE']) {
				$storageType = Core_StorageFactory::getType('Polygon');
			}
			else if ('geometry' == $fieldStruct['DATA_TYPE']
							 || 'multipoint' == $fieldStruct['DATA_TYPE']
							 || 'multilinestring' == $fieldStruct['DATA_TYPE']
							 || 'multipolygon' == $fieldStruct['DATA_TYPE']
							 || 'geometrycollection' == $fieldStruct['DATA_TYPE']
							) {
				$storageType = Core_StorageFactory::getType('Geometry');
				$storageType->setSpatialType($fieldStruct['COLUMN_TYPE']);
			}
			else {
				$storageType = Core_StorageFactory::getType('Unknown');
				$storageType->setRawType($fieldStruct['COLUMN_TYPE']);
			}

			if (!$storageType) {
				echo "<div class=\"alert alert-warning\">Storage type '{$fieldStruct['COLUMN_TYPE']}' not recognized</div>";
				//TODO: throw new Exception("Storage type '{$fieldStruct['COLUMN_TYPE']}' not recognized");
			}

			if ('PRI' == $fieldStruct['COLUMN_KEY']) {
				$storageType->setPrimaryKey(true);
			}

			$vFldNillable = false;
			if ('YES' == $fieldStruct['IS_NULLABLE']) {
				$vFldNillable = true;
			} else if ('NO' == $fieldStruct['IS_NULLABLE']
								 && 'PRI' == $fieldStruct['COLUMN_KEY']
								 && 'auto_increment' == $fieldStruct['EXTRA']
								) {
				$vFldNillable = true;
			}
			$storageType->setNillable($vFldNillable);

			if (!empty($fieldStruct['COLUMN_DEFAULT'])) {
				$storageType->setDefault($fieldStruct['COLUMN_DEFAULT']);
			} else if ('0' === $fieldStruct['COLUMN_DEFAULT']) {
				$storageType->setDefault(0);
			} else if ('' === $fieldStruct['COLUMN_DEFAULT']) {
				$storageType->setDefault('');
			} else if (null === $fieldStruct['COLUMN_DEFAULT']
								 && 'YES' == $fieldStruct['IS_NULLABLE']
								 && 1 == $fieldStruct['COLUMN_DEFAULT_IS_NULL']
								) {
				$storageType->setDefault(null);
			}

			$tblStruct[$fldName] = $storageType;
		}
		return $tblStruct;
	}

	public function getTableStructRaw($tblName) {
		$tblNamePattern = '/^[a-zA-Z0-9_]+$/';
		if (!preg_match($tblNamePattern, $tblName)) {
			throw new Exception("Table name not allowed");
		}
		$sqlTblName = $tblName;
		$sql = "show fields from `{$sqlTblName}` ";
		$sql = "select cols.*
		--		, `IS_NULLABLE` as `Nullable`
		--		, `COLUMN_DEFAULT` as `DEFAULT`
				, IF(cols.`IS_NULLABLE`='YES' and cols.`COLUMN_DEFAULT` is null, 1, 0) as `COLUMN_DEFAULT_IS_NULL`
			from `INFORMATION_SCHEMA`.`COLUMNS` cols
			where cols.`TABLE_NAME`='{$sqlTblName}'
				and cols.`TABLE_SCHEMA`='{$this->_databaseName}'
		";
		return $this->fetchAllAssoc($sql);
	}

	public function getViewStructTODO($viewName) {// TODO: getViewStruct
		$viewNamePattern = '/^[a-zA-Z0-9_]+$/';
		if (!preg_match($viewNamePattern, $viewName)) {
			throw new Exception("View name not allowed");
		}
	}

	public function getViewStructRaw($viewName) {
		$viewNamePattern = '/^[a-zA-Z0-9_]+$/';
		if (!preg_match($viewNamePattern, $viewName)) {
			throw new Exception("View name not allowed");
		}
		$sqlViewName = $viewName;
		$sql = "show create view `{$sqlViewName}` ";
		return $this->fetchAllAssoc($sql);
	}

	public function getTableListWithInfo() {
		return $this->getTableFullList();
	}

	public function getTableList() {
		return array_filter($this->getTableFullList(), function($row) {
			return ('BASE TABLE' == $row['table_type']);
		});
	}

	public function getViewList() {
		return array_filter($this->getTableFullList(), function($row) {
			return ('VIEW' == $row['table_type']);
		});
	}

	public function getTableFullList() {
		return array_map(function($row) {
			$values = array_values($row);
			return array('table_name' => strtolower($values[0]), 'table_type' => $values[1]);
		}, $this->_pdo->fetchAll('show full tables'));
	}

	function createTableStructure($conf) { // @param $conf: [ 'tableName', 'fields', 'primaryKey', 'keys' ]
		$encoding = V::get('encoding', "latin2", $conf);
		$sqlLinesCreateCmd = array_map([ self, '_makeCreateFieldCommand' ], $conf['fields']);
		if (!empty($conf['primaryKey'])) $sqlLinesCreateCmd[] = "PRIMARY KEY (`{$conf['primaryKey']}`)";
		if (!empty($conf['keys'])) throw new Exception("");

		$sql = "CREATE TABLE IF NOT EXISTS `{$conf['tableName']}` (" .
			"\n\t" . implode(",\n\t", $sqlLinesCreateCmd) .
			"\n" . ") ENGINE=MyISAM DEFAULT CHARSET={$encoding};";
		DBG::nicePrint($sql, "DBG:create table sql");
		$this->_pdo->execSql($sql);

		// CREATE TABLE IF NOT EXISTS `CRM__@SELECTED_CONTEXT` (
		// 	`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
		// 	`name` varchar(32) NOT NULL,
		// 	`A_CREATE_TIME` datetime,
		// 	`A_LAST_ACCESS_TIME` datetime,
		// 	`A_LAST_UPDATE_TIME` datetime,
		// 	PRIMARY KEY (`ID`),
		// 	UNIQUE KEY `name` (`name`)
		// ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

	}

	static function _makeCreateFieldCommand($fieldStruct) {
		$isNullable = V::get('is_nullable', '', $fieldStruct);

		return implode(" ", [
			$fieldStruct['name'],
			$fieldStruct['raw_storage_type'],
			($isNullable) ? "" : "NOT NULL",
			V::get('extra', '', $fieldStruct),
		]);
	}

}