se/SE/se-lib/RefConfig.php

<?php

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

/*
	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 $REF_TABLE_VERSION = 2;

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

	/** static function fetch(Type_Namespace $rootObjectNamespace, Type_TypeName $childName, Type_Namespace $childNamespace = null): Type_RefConfig */
	static function fetch($rootObjectNamespace, $childName, $childNamespace = null) { // @returns Type_RefConfig
		SchemaVersionUpgrade::upgradeSchema();

		$rootObjectNamespace = ACL::getBaseNamespace($rootObjectNamespace);

		$rootAcl = ACL::getAclByNamespace($rootObjectNamespace);
		if (!($rootAcl instanceof AntAclBase)) throw new Exception("Ref allowed only for AntAcl objects");
		// $fieldInfo = $rootAcl->_getField($childName); // throws Exception if field not exists
		$childFieldXsdType = $rootAcl->getXsdFieldType($childName); // throws Exception if field not exists

		if (!$childNamespace && false !== strpos($childName, '__x3A__') && false !== strpos($childName, ':')) $childNamespace = Api_WfsNs::namespaceFromTypeName($childName);
		if (!$childNamespace) {
			$childXsdType = $rootAcl->getXsdFieldType($childName);
			list($typePrefix, $childTypeName) = explode(':', $childXsdType, 2);
			$childNamespace = Api_WfsNs::namespaceFromTypeName($childTypeName);
			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 = DB::getPDO()->fetchFirst("
			select c.ID, c.A_STATUS, c.VERSION, c.SOURCE
			from `CRM_REF_CONFIG` c
			where c.ROOT_OBJECT_NS = :ROOT_OBJECT_NS
				and c.CHILD_NAME = :CHILD_NAME
				and c.CHILD_NS = :CHILD_NS
		", [
			':ROOT_OBJECT_NS' => $rootObjectNamespace,
			':CHILD_NAME' => $childName,
			':CHILD_NS' => $childNamespace,
		]);
		if (empty($refInfo)) {
			$refInfo = [ 'ID' => 0, 'A_STATUS' => 'WAITING', 'VERSION' => self::$REF_TABLE_VERSION, 'SOURCE' => 'table',
				'ROOT_OBJECT_NS' => $rootObjectNamespace,
				'CHILD_NAME' => $childName,
				'CHILD_NS' => $childNamespace,
			];
			$refInfo['ID'] = DB::getPDO()->insert("CRM_REF_CONFIG", [
				'ROOT_OBJECT_NS' => $rootObjectNamespace,
				'CHILD_NAME' => $childName,
				'CHILD_NS' => $childNamespace,
				'VERSION' => self::$REF_TABLE_VERSION
			]);
		}

		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;
		$refConfig = self::fetch($objectNamespace, $childTypeName);
		$oldRefSource = $refConfig->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, $refConfig);
				case 'view': return self::installRefView($objectNamespace, $childTypeName, $newField, $refConfig);
				case 'backRef': return self::installBackRef($objectNamespace, $childTypeName, $newField, $refConfig);
			}
		}
		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 createRefTable($objectNamespace, $childTypeName, Type_RefConfig $refConfig = null) {
		if (!$refConfig) $refConfig = self::fetch($objectNamespace, $childTypeName);
		$refTableName = "CRM__#REF_TABLE__{$refConfig->id}";
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `{$refTableName}` (
				`PRIMARY_KEY` int(11) NOT NULL
				, `REMOTE_PRIMARY_KEY` int(11) NOT NULL
				, `REMOTE_TYPENAME` varchar(255) NOT NULL DEFAULT ''
				, `A_STATUS` enum('WAITING', 'NORMAL', 'DELETED') NOT NULL DEFAULT 'WAITING'
				, `TRANSACTION_ID` int(11) NOT NULL
				, `A_LAST_ACTION_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
				, KEY `PRIMARY_KEY` (`PRIMARY_KEY`)
				, KEY `REMOTE_PRIMARY_KEY` (`REMOTE_PRIMARY_KEY`)
				, KEY `TRANSACTION_ID` (`TRANSACTION_ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2 COMMENT='{$objectNamespace} #REF {$childTypeName}';
		");
		$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
			'A_STATUS' => "NORMAL",
			// 'VERSION' => self::$REF_TABLE_VERSION
		]);
		self::upgradeRefTableFrom1to2($refConfig);
	}
	static function upgradeRefTableFrom1to2(Type_RefConfig $refConfig) { // TODO: rm ACL::upgradeRefConfigFrom1to2
		if (1 == $refConfig->version) {
			if ('table' === $refConfig->source && 'NORMAL' == $refConfig->status) {
				$refTableName = "CRM__#REF_TABLE__{$refConfig->id}";
				try {
					DB::getPDO()->execSql(" CREATE INDEX `TRANSACTION_ID` ON `{$refTableName}` (`TRANSACTION_ID`) ");
				} catch (Exception $e) {
					DBG::log($e);
				}
			}
			$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
				'VERSION' => 2
			]);
		}
		// TODO: return array_merge($refConfig, [ 'VERSION' => 2 ]);
	}

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

	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',
			'A_STATUS' => "NORMAL",
		]);
	}

	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',
			'A_STATUS' => "NORMAL",
			'VERSION' => self::$REF_TABLE_VERSION,
		]);
	}

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

	static function remove(Type_RefConfig $refConfig) {
		DB::getPDO()->update('CRM_REF_CONFIG', 'ID', $refConfig->id, [
			'A_STATUS' => 'DELETED',
			'A_LAST_ACTION_DATE' => 'NOW()',
		]);
	}

	static function reactivate(Type_RefConfig $refConfig) {
		DB::getPDO()->update('CRM_REF_CONFIG', 'ID', $refConfig->id, [ // TODO: update ref table, update source -- fixed below by RefConfig::update
			'A_STATUS' => 'WAITING',
			'A_LAST_ACTION_DATE' => 'NOW()',
		]);
	}

}