se/SE/se-lib/RefConfig.php

<?php

Lib::loadClass('Type_Field');
Lib::loadClass('Type_RefConfig');
Lib::loadClass('Api_WfsNs');
Lib::loadClass('ACL');

/*
	RefStorage - for ref data manipulation
	RefConfig - for ref config

	`CRM_REF_CONFIG`:
	- `ID`: primaryKey (used to generate **Storage** name for data)
	- `ROOT_OBJECT_NS`: root object namespace
	- `CHILD_NAME`: child typeName (or alias name - not supported yet)
	- `CHILD_NS`: child namespace (required when use `CHILD_NAME` as alias)
	- `A_STATUS`: status
		- 'WAITING': not Active, 'NORMAL': Active, 'DELETED': removed
	- `VERSION`: **Storage** version
	- `A_LAST_ACTION_DATE`: last action timestamp
	- `SOURCE`: place where data is stored
		- 'table': table with data
		- 'view': view by flat relation cache
		- 'backRef': view on ref table with replaced fields

	**Storage** - table or view - place where data is stored

	RefConfig::isActive($objectNamespace, $childTypeName) // Ref is active when: CRM_REF_CONFIG.A_STATUS = NORMAL
	RefConfig::getRefTable($objectNamespace, $childTypeName) // ref table - TODO not needed?
 */

class RefConfig {

	static function isActive($objectNamespace, $childTypeName) {
		$refInfo = self::fetch($objectNamespace, $childTypeName);
		return ('NORMAL' === $refInfo->status);
	}

	static function fetch($rootObjectNamespace, $childName, $childNamespace = null) {
		if (!$childTypeName) $childTypeName = Api_WfsNs::namespaceFromTypeName($fieldName);
		$rootObjectNamespace = ACL::getBaseNamespace($rootObjectNamespace);
		if (!$childNamespace) {
			$rootAcl = ACL::getAclByNamespace($rootObjectNamespace);
			$childXsdType = $rootAcl->getXsdFieldType($childName);
			list($typePrefix, $childNamespace) = explode(':', $childXsdType, 2);
			DBG::log(['$childXsdType' => $childXsdType, '$typePrefix' => $typePrefix, '$childNamespace' => $childNamespace], 'array', "DBG get ref table ...");
			switch ($typePrefix) {
				case 'ref_uri': $childAcl = ACL::getAclByNamespace($childNamespace); break;
				case 'ref': $childAcl = ACL::getAclByTypeName($childNamespace); break;
				default: throw new Exception("Expected ref type for field '{$childName}' in object '{$rootObjectNamespace}'");
			}
		}
		$refInfo = [];// define $refInfo = [ ID, A_STATUS, VERSION ]
		try {// check that ref config table exists
			$sqlRootTableNs = DB::getPDO()->quote($rootObjectNamespace, PDO::PARAM_STR);
			$sqlChildName = DB::getPDO()->quote($childName, PDO::PARAM_STR);
			$sqlChildNamespace = DB::getPDO()->quote($childNamespace, PDO::PARAM_STR);
			$refInfo = DB::getPDO()->fetchFirst("
				select c.ID, c.A_STATUS, c.VERSION, c.SOURCE
				from `CRM_REF_CONFIG` c
				where c.ROOT_OBJECT_NS = {$sqlRootTableNs}
					and c.CHILD_NAME = {$sqlChildName}
					and c.CHILD_NS = {$sqlChildNamespace}
			");
		} catch (Exception $e) {
			DB::getPDO()->execSql("
				CREATE TABLE IF NOT EXISTS `CRM_REF_CONFIG` (
					`ID` INT NOT NULL AUTO_INCREMENT
					, `ROOT_OBJECT_NS` VARCHAR(255) NOT NULL
					, `CHILD_NAME` VARCHAR(255) NOT NULL
					, `CHILD_NS` VARCHAR(255) NOT NULL
					, `A_STATUS` enum('WAITING', 'NORMAL', 'DELETED') NOT NULL DEFAULT 'WAITING'
					, `VERSION` int(11) NOT NULL DEFAULT 0
					, `A_LAST_ACTION_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
					, PRIMARY KEY (`ID`)
				) ENGINE = MyISAM DEFAULT CHARSET=latin2;
			");
			try {
				DB::getPDO()->execSql(" ALTER TABLE `CRM_REF_CONFIG` ADD `SOURCE` enum('table', 'view') not null default 'table' ");
			} catch (Exception $e) {
				DBG::log($e);
			}
		}
		try {
			DB::getPDO()->execSql(" ALTER TABLE `CRM_REF_CONFIG` CHANGE `SOURCE` `SOURCE` enum('table', 'view', 'backRef') not null default 'table' ");
		} catch (Exception $e) {
			DBG::log($e);
		}
		if (empty($refInfo)) {
			$refInfo = [ 'ID' => 0, 'A_STATUS' => 'WAITING', 'VERSION' => 0, 'SOURCE' => 'table' ];
			$refInfo['ID'] = DB::getPDO()->insert("CRM_REF_CONFIG", [
				'ROOT_OBJECT_NS' => $rootObjectNamespace,
				'CHILD_NAME' => $childName,
				'CHILD_NS' => $childNamespace
			]);
		}

		// { // TODO: fix source if ref for `SystemObjects__x3A__*` and defined flat_relation_cache - move to AclReinstall?
		// 	if ('SystemObjects__x3A__' === substr($childNamespace, 0, strlen('SystemObjects__x3A__'))) {
		// 		if ('table' === $refInfo['SOURCE']) {
		// 			$toUpdate = [
		// 				'SOURCE' => 'view',
		// 				'A_STATUS' => 'WAITING',
		// 			];
		// 			DB::getPDO()->update('CRM_REF_CONFIG', 'ID', $refInfo['ID'], $toUpdate);
		// 			$refInfo = array_merge($refInfo, $toUpdate);
		// 		}
		// 	}
		// }

		if (!$refInfo['ID']) throw new Exception("Ref table not found in ref config table for field '{$childName}' in object '{$rootObjectNamespace}'");
		return Type_RefConfig::build($refInfo);
	}

	static function getChildRefFullList($namespace) {
		$namespace = ACL::getBaseNamespace($namespace);
		if (!$namespace) throw new Exception("Missing namespace");
		return array_map('Type_RefConfig::build', DB::getPDO()->fetchAll("
			select c.ID
				, c.A_STATUS
				, c.SOURCE
				, c.CHILD_NAME
			from CRM_REF_CONFIG c
			where c.ROOT_OBJECT_NS = :namespace
			--	and c.A_STATUS = 'NORMAL'
		", [
			':namespace' => $namespace,
		]));
	}

	static function isRefField($objectNamespace, $fieldName) {
		return (false !== strpos($fieldName, ':'));
	}

	/*
		@param $appInfo = [
			[type] => ref:default_db__x3A__BI_audit_CEIDG:BI_audit_CEIDG
			[minOccurs] => 0
			[maxOccurs] => unbounded
			[restrictions] => []
			[appInfo] => []
		]
	*/
	static function needUpdate($objectNamespace, $childTypeName, Type_Field $newField, Type_Field $oldField = null) {
		DBG::log(['objectNamespace' => $objectNamespace, 'childTypeName' => $childTypeName, 'newField' => $newField, 'oldField' => $oldField], 'array', "RefConfig::needUpdate...");
		if (!$oldField) throw new Exception("Missig oldField in RefConfig::needUpdate({$objectNamespace}, {$childTypeName}, ...) - TODO: fetch from #acl cache");

		if (!($newField instanceof Type_Field_Ref)) return false;
		if (!($oldField instanceof Type_Field_Ref)) return false;

		$oldRefActive = self::isActive($objectNamespace, $childTypeName);
		if (!$oldRefActive) return false; // if old not installed / adtivated then just fix struct and install

		$newRefSource = $newField->source;
		$oldRefConf = self::fetch($objectNamespace, $childTypeName);
		$oldRefSource = $oldRefConf->source;
		if ($newRefSource !== $oldRefSource) DBG::log("RefConfig::needUpdate Change ref source from '{$oldRefSource}' to '{$newRefSource}'");
		if ($newRefSource !== $oldRefSource) return true;

		return false;
	}

	static function update($objectNamespace, $childTypeName, Type_Field $newField) {
		if (!($newField instanceof Type_Field_Ref)) return;

		// $oldRefActive = self::isActive($objectNamespace, $childTypeName);
		// if (!$oldRefActive) return; // if old not installed / adtivated then just fix struct and install

		$newRefSource = $newField->source;
		$oldRefConf = self::fetch($objectNamespace, $childTypeName);
		$oldRefSource = $oldRefConf->source;
		if ($newRefSource !== $oldRefSource) DBG::log("RefConfig::update Change ref source from '{$oldRefSource}' to '{$newRefSource}'");
		{ // always update ref config at reinstall - drop / create ref tables (table or view)
			switch ($newRefSource) {
				case 'table': return self::installRefTable($objectNamespace, $childTypeName, $newField, $oldRefConf);
				case 'view': return self::installRefView($objectNamespace, $childTypeName, $newField, $oldRefConf);
				case 'backRef': return self::installBackRef($objectNamespace, $childTypeName, $newField, $oldRefConf);
			}
		}
		if ($newRefSource !== $oldRefSource) {
			if ('table' === $oldRefSource) {
				// TODO: check if table has data
				// TODO: if no data in table then DROP ?
			}
			throw new Exception("TODO: RefConfig::update Change ref source from '{$oldRefSource}' to '{$newRefSource}' in '{$objectNamespace}'-&gt;'{$childTypeName}'");
		}
	}

	static function installRefTable($objectNamespace, $childTypeName, Type_Field $newField, Type_RefConfig $refConfig = null) {
		if (!$refConfig) $refConfig = self::fetch($objectNamespace, $childTypeName);
		$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
			'SOURCE' => 'table',
		]);
	}

	static function installRefView($objectNamespace, $childTypeName, Type_Field $typeField, Type_RefConfig $refConfig = null) {
		if (!$refConfig) $refConfig = self::fetch($objectNamespace, $childTypeName);
		$viewSelectSql = RefConfig::generateRefSelectSqlByFlatRelationCache($objectNamespace, $childTypeName, $typeField);
		$refTableName = "CRM__#REF_TABLE__{$refConfig->id}_VIEW";
		DB::getPDO()->execSql(" CREATE OR REPLACE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `{$refTableName}` AS {$viewSelectSql} ");

		$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
			'SOURCE' => 'view',
		]);
	}

	static function installBackRef($objectNamespace, $childTypeName, Type_Field $newField, Type_RefConfig $refConfig = null) {
		$viewSelectSql = self::generateRefSelectSqlByBackRef($objectNamespace, $childTypeName);
		if (!$refConfig) $refConfig = self::fetch($objectNamespace, $childTypeName);
		$refTableName = "CRM__#REF_TABLE__{$refConfig->id}_VIEW";
		DB::getPDO()->execSql(" CREATE OR REPLACE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `{$refTableName}` AS {$viewSelectSql} ");

		$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
			'SOURCE' => 'backRef',
		]);
	}

	static function generateRefSelectSqlByBackRef($rootObjectNamespace, $childName) {
		// generate view which is select from {replaced(pk, remote pk) on ref table from backRef}
		// {
		// 	DBG::nicePrint($refInfo, "\$refInfo");
		// 	DBG::nicePrint($rootObjectNamespace, "\$rootObjectNamespace");
		// 	DBG::nicePrint($childName, "\$childName");
		// 	DBG::nicePrint($childNamespace, "\$childNamespace");
		// 	$replacedObjNs = Api_WfsNs::namespaceFromTypeName($childName);
		// 	$replacedChildName = Api_WfsNs::typeName($rootObjectNamespace);
		// 	DBG::nicePrint($replacedObjNs, "\$replacedObjNs");
		// 	DBG::nicePrint($replacedChildName, "\$replacedChildName");
		// 	return ACL::getRefTable($replacedObjNs, $replacedChildName, 1);
		// 	throw new Exception("Not Implemented ref SOURCE = '{$refInfo['SOURCE']}'");
		// }
		$childNs = Api_WfsNs::namespaceFromTypeName($childName);
		$rootTypeName = Api_WfsNs::typeName($rootObjectNamespace);
		$backRefTable = ACL::getRefTable($childNs, $rootTypeName);
		DBG::nicePrint($backRefTable, "ACL::getRefTable({$childNs}, {$rootTypeName})");

		// TODO: check if ref_config is not backRef to avoid loop // $refInfo = self::getRefConfig($fieldNs, $item['typeName'], $item['typeName']);

		$lastActionDateField = "NULL"; // , IF(l.A_RECORD_UPDATE_DATE > r.A_RECORD_UPDATE_DATE, l.A_RECORD_UPDATE_DATE, r.A_RECORD_UPDATE_DATE) as A_LAST_ACTION_DATE
		$sql = "
			select backRef.REMOTE_PRIMARY_KEY as PRIMARY_KEY
					, backRef.PRIMARY_KEY as REMOTE_PRIMARY_KEY
					, backRef.REMOTE_TYPENAME as REMOTE_TYPENAME
					, backRef.A_STATUS as A_STATUS
					, 0 as TRANSACTION_ID
					, {$lastActionDateField} as A_LAST_ACTION_DATE
			from `{$backRefTable}` backRef
		";
		DBG::log($sql, 'sql', "generateRefSelectSqlByBackRef");
		return $sql;
	}

	static function generateRefSelectSqlByFlatRelationCache($rootObjectNamespace, $childName, Type_Field $typeField) { // CRM_REF_CONFIG
		$appInfo = $typeField->appInfo;
		if (empty($appInfo)) throw new Exception("Empty app:info for field '{$rootObjectNamespace}/{$childName}'");

		DBG::log(['$appInfo'=>$appInfo, '$rootObjectNamespace'=>$rootObjectNamespace, '$childName'=>$childName], 'array', "\$appInfo");
		$rootAcl = ACL::getAclByNamespace($rootObjectNamespace);
		$childXsdType = $rootAcl->getXsdFieldType($childName);
		list($typePrefix, $childNamespace) = explode(':', $childXsdType, 2);
		switch ($typePrefix) {
			case 'ref_uri': $childAcl = ACL::getAclByNamespace($childNamespace); break;
			case 'ref': $childAcl = ACL::getAclByTypeName($childNamespace); break;
			default: throw new Exception("Expected ref type for field '{$childName}' in object '{$rootObjectNamespace}'");
		}

		$lastActionDateField = "NULL"; // , IF(l.A_RECORD_UPDATE_DATE > r.A_RECORD_UPDATE_DATE, l.A_RECORD_UPDATE_DATE, r.A_RECORD_UPDATE_DATE) as A_LAST_ACTION_DATE
		$rootPrimaryKeyField = $rootAcl->getPrimaryKeyField();
		$childPrimaryKeyField = $childAcl->getPrimaryKeyField();
		$rootTableName = $rootAcl->getRootTableName();
		$childTableName = $childAcl->getRootTableName();
		//   '$appInfo' => [
		//     'flat_relation_cache' => [
		//       'source' => [
		//         '@name' => 'ID',
		//         '@xpath' => 'default_db__x3A__CRM_WSKAZNIK:CRM_WSKAZNIK/ID_PROCES',
		//       ),
		//     ),
		//   ),
		//   '$rootObjectNamespace' => 'default_db/CRM_PROCES/PROCES',
		//   '$childName' => 'default_db__x3A__CRM_WSKAZNIK:CRM_WSKAZNIK',

		//   '$appInfo' => [
		//   'flat_relation_cache' => [
		//     'source' => [
		//       '@name' => 'ID',
		//       '@xpath' => 'default_db__x3A__CRM_PROCES:PROCES/PARENT_ID',
		//     ),
		//   ),
		// ),
		// '$rootObjectNamespace' => 'default_db/CRM_PROCES/PROCES',
		// '$childName' => 'default_db__x3A__CRM_PROCES:PROCES',
		$appInfoRootFieldName = null;
		$appInfoChildFieldName = null;
		{
			if (empty($appInfo['flat_relation_cache']['source']['@name'])) throw new Exception("Missing flat_relation_cache/source/@name");
			if (empty($appInfo['flat_relation_cache']['source']['@xpath'])) throw new Exception("Missing flat_relation_cache/source/@xpath");
			$appInfoName = $appInfo['flat_relation_cache']['source']['@name'];
			$appInfoXpath = $appInfo['flat_relation_cache']['source']['@xpath'];
			// $rootNs = $rootAcl->getNamespace()
			if ("{$childName}/" === substr($appInfoXpath, 0, strlen("{$childName}/"))) {
				$appInfoRootFieldName = substr($appInfoXpath, strlen("{$childName}/"));
				$appInfoChildFieldName = $appInfoName;
			} else {
				throw new Exception("TODO parse flat_relation_cache");
			}
		}
		if (!$appInfoRootFieldName || !$appInfoChildFieldName) throw new Exception("Error Processing flat_relation_cache");
		$sqlWhereFromRestrictions = [];
		DBG::log(['root'=>$rootAcl->getFields(), 'child'=>$childAcl->getFields()], 'array', "rootAcl and childAcl fields - xsdRestrictions");
		if ($rootAcl instanceof AntAclBase && $childAcl instanceof AntAclBase) {
			$rootLocalFieldsWithRestrictions = array_filter($rootAcl->getFields(), function ($field) {
				if (!$field['isLocal']) return false;
				if (empty($field['xsdRestrictions'])) return false;
				if ('[]' == $field['xsdRestrictions']) return false;
				return true;
			});
			$childLocalFieldsWithRestrictions = array_filter($childAcl->getFields(), function ($field) {
				if (!$field['isLocal']) return false;
				if (empty($field['xsdRestrictions'])) return false;
				if ('[]' == $field['xsdRestrictions']) return false;
				return true;
			});
			DBG::log(['root'=>$rootLocalFieldsWithRestrictions, 'child'=>$childLocalFieldsWithRestrictions], 'array', "root and child fields with xsdRestrictions");
			if (!empty($rootLocalFieldsWithRestrictions)) {
				$sqlTablePrefix = 'root';
				$sqlWhereFromRestrictions = array_reduce(
					array_map(function ($field) use ($sqlTablePrefix) {
						$sqlRestrictions = [];
						// 'xsdRestrictions' => '{"enumeration":{"PROCES":"PROCES"}}',
						$restrictions = @json_decode($field['xsdRestrictions'], $assoc = true);
						if (!empty($restrictions)) {
							if (!empty($restrictions['enumeration'])) {
								$sqlRestrictions[] = "{$sqlTablePrefix}.`{$field['fieldNamespace']}` in (" . implode(",", array_map([DB::getPDO(), 'quote'], array_keys($restrictions['enumeration']))) . ")";
							}
						}
						return $sqlRestrictions;
					}, $rootLocalFieldsWithRestrictions),
					function ($ret, $cur) {
						return array_merge($ret, array_filter($cur, ['V', 'filterNotEmpty']));
					},
					$sqlWhereFromRestrictions
				);
			}
			if (!empty($childLocalFieldsWithRestrictions)) {
				$sqlTablePrefix = 'child';
				$sqlWhereFromRestrictions = array_reduce(
					array_map(function ($field) use ($sqlTablePrefix) {
						$sqlRestrictions = [];
						// 'xsdRestrictions' => '{"enumeration":{"PROCES":"PROCES"}}',
						$restrictions = @json_decode($field['xsdRestrictions'], $assoc = true);
						if (!empty($restrictions)) {
							if (!empty($restrictions['enumeration'])) {
								$sqlRestrictions[] = "{$sqlTablePrefix}.`{$field['fieldNamespace']}` in (" . implode(",", array_map([DB::getPDO(), 'quote'], array_keys($restrictions['enumeration']))) . ")";
							}
						}
						return $sqlRestrictions;
					}, $childLocalFieldsWithRestrictions),
					function ($ret, $cur) {
						return array_merge($ret, array_filter($cur, ['V', 'filterNotEmpty']));
					},
					$sqlWhereFromRestrictions
				);
			}
		}
		$sqlWhereFromRestrictions = (!empty($sqlWhereFromRestrictions)) ? implode(" and ", $sqlWhereFromRestrictions) : "1=1";
		$sqlChildFieldName = $childAcl->getSqlFieldName($appInfoRootFieldName);
		$sql = "
			select root.{$rootPrimaryKeyField} as PRIMARY_KEY
					, child.{$childPrimaryKeyField} as REMOTE_PRIMARY_KEY
					, '' as REMOTE_TYPENAME
					, 'WAITING' as A_STATUS
					, 0 as TRANSACTION_ID
					, {$lastActionDateField} as A_LAST_ACTION_DATE
			from `{$rootTableName}` root
				join `{$childTableName}` child on(child.{$sqlChildFieldName} = root.{$appInfoChildFieldName})
			where {$sqlWhereFromRestrictions}
		";
		DBG::log($sql, 'sql', "generateRefSelectSqlByFlatRelationCache");
		return $sql;
	}

}