se/SE/se-lib/InstanceConfig.php

<?php

Lib::loadClass('SchemaVersionUpgrade');
Lib::loadClass('Type_InstanceConfig');
Lib::loadClass('SchemaFactory');

class InstanceConfig {

	static function getInstanceTable($namespace) { // @return string - tableName with struct { pk, idInstance, _createdAt }
		return self::getInstanceConfig($namespace)->tableName;
	}

	static function getInstanceId($namespace) { // @return int - idInstance
		return self::getInstanceConfig($namespace)->id;
	}

	static function getInstanceNamespaceById($idInstance) { // @return string - namespace
		$instanceRow = DB::getPDO()->fetchFirst("
			select c.*
			from `CRM_INSTANCE_CONFIG` c
			where c.id = :id
		", [
			':id' => $idInstance
		]);
		if (!$instanceRow) throw new HttpException("Not found instance {$idInstance}", 404);
		return Type_InstanceConfig::build($instanceRow)->namespace;
	}

	static function getInstanceConfig($namespace) { // @return Type_InstanceConfig or throw Exception
		SchemaVersionUpgrade::upgradeSchema();
		$conf = self::fetchInstanceConfig($namespace);
		if (!$conf) {
			$id = DB::getPDO()->insert("CRM_INSTANCE_CONFIG", [
				'namespace' => $namespace,
				'rootNamespace' => self::getRootNamespace($namespace),
				'_createdAt' => 'NOW()',
			]);
			$conf = self::fetchInstanceConfig($namespace);
		}
		if (!$conf) throw new Exception("Instance not found in config table '{$namespace}'");

		if ('NORMAL' !== $conf->status) {
			try {
				$objectItem = SchemaFactory::loadDefaultObject('SystemObject')->getItem($namespace, [ 'propertyName' => '*,field' ]);
			} catch (Exception $e) {
				throw new Exception("Object not installed '{$namespace}'");
			}
			$conf = self::createInstanceTable($conf, $objectItem);
		}

		return $conf;
	}

	static function fetchInstanceConfig($namespace) { // @return Type_InstanceConfig | null
		$instanceRow = DB::getPDO()->fetchFirst("
			select c.*
			from `CRM_INSTANCE_CONFIG` c
			where c.namespace = :namespace
		", [
			':namespace' => $namespace
		]);
		return ($instanceRow) ? Type_InstanceConfig::build($instanceRow) : null;
	}

	static function createInstanceTable(Type_InstanceConfig $conf, $objectItem) {
		switch ($objectItem['instanceTableSource']) {
			case 'table': {
				self::_createInstanceTable($conf, $objectItem);
				DB::getPDO()->update('CRM_INSTANCE_CONFIG', 'ID', $conf->id, [
					'A_STATUS' => "NORMAL",
					'version' => 1,
					'source' => "table"
				]);
				return self::fetchInstanceConfig($conf->namespace);
			}
			case 'view': {
				self::_createInstanceView($conf, $objectItem);
				DB::getPDO()->update('CRM_INSTANCE_CONFIG', 'ID', $conf->id, [
					'A_STATUS' => "NORMAL",
					'version' => 1,
					'source' => "view"
				]);
				return self::fetchInstanceConfig($conf->namespace);
			}
			default: {
				DBG::log(['$objectItem'=>$objectItem, '$conf'=>$conf], 'array', "Not implemented instance table source type '{$objectItem['instanceTableSource']}'");
				throw new Exception("Not implemented instance table source type '{$objectItem['instanceTableSource']}'");
			}
		}
		return self::fetchInstanceConfig($conf->namespace);
	}
	static function _createInstanceTable(Type_InstanceConfig $conf, $objectItem) {
		$instanceTableName = "CRM__#INSTANCE_TABLE__{$conf->id}";
		// // TODO: fetch primaryKeyType - TODO: store primaryKey and primaryKeyType in SystemObject item
		// $pkType = 'int';
		DB::getPDO()->exec("
			CREATE TABLE IF NOT EXISTS `{$instanceTableName}` (
				`pk` int(11) NOT NULL COMMENT 'primary key'
				, `idInstance` int(11) NOT NULL
				, `_createdAt` datetime NOT NULL
				, KEY `pk` (`pk`)
				, KEY `idInstance` (`idInstance`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2 COMMENT='{$conf->namespace} #INSTANCE';
		");
		// TODO: add author, idTransaction?, convert to EventSourcing
		$affected = DB::getPDO()->update("CRM_INSTANCE_CONFIG", 'rootNamespace', $rootNs, [
			'SOURCE' => 'table',
			'VERSION' => 1,
		]);
		// return $instanceTableName;
	}
	static function _createInstanceView(Type_InstanceConfig $conf, $objectItem) {
		$idInstance = $conf->id;
		$dbName = DB::getPDO()->getDatabaseName();
		$sqlIsInstanceView = self::generateIsInstanceView($objectItem['namespace'], $objectItem, $conf);
		$instanceViewTableName = "CRM__#INSTANCE_TABLE__{$idInstance}_VIEW";
		DB::getPDO()->execSql(" CREATE OR REPLACE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `{$dbName}`.`{$instanceViewTableName}` AS {$sqlIsInstanceView} ");
	}

	static function getRootNamespace($namespace) { // TODO: works only for relative urls! - mv to Acl->getRootNamespace
		Lib::loadClass('SchemaFactory');
		try {
			$objectItem = SchemaFactory::loadDefaultObject('SystemObject')->getItem($namespace);
		} catch (Exception $e) {
			throw new Exception("Object not installed '{$namespace}'");
		}
		if (!$objectItem['isStructInstalled']) throw new Exception("Object structure not installed '{$namespace}'");

		if ($objectItem['idDatabase'] != DB::getPDO()->getZasobId()) {
			if ('StorageAcl' === $objectItem['_type']) {
				DBG::log("getRootNamespace...");
				return $objectItem['namespace'];
			}
			else {
				throw new Exception("Only default_db supported"); // TODO: support more Sources
			}
		}

		return "default_db/{$objectItem['_rootTableName']}";
	}

	static function getNamespaceSiblings($namespace) {
		return array_map(function ($row) {
			return $row['namespace'];
		}, DB::getPDO()->fetchAll("
			select s.namespace
			from CRM_INSTANCE_CONFIG c
				join CRM_INSTANCE_CONFIG s on ( s.rootNamespace = c.rootNamespace and s.namespace != c.rootNamespace )
			where c.namespace = :namespace
		", [
			'namespace' => $namespace
		]));
	}

	static function getFeatureNamespaces($namespace, $pk) {
		$instanceTable = self::getInstanceTable($namespace);
		return array_map(function ($row) {
			return $row['namespace'];
		}, DB::getPDO()->fetchAll("
			select c.namespace
			from `{$instanceTable}` i
				join `CRM_INSTANCE_CONFIG` c on ( c.id = i.idInstance )
			where i.pk = :pk
		", [
			'pk' => $pk,
		]));
	}

	static function generateSqlWhereFromFieldsWithRestrictions($fieldsRestrictions, $sqlTablePrefix = 't', $charset = 'utf8') { // @param $fieldsRestrictions Array [ fieldName => xsdRestrictions | Null ]
		// TODO: add @param $acl to check if field is local, and if not then generate remote query (join)
		if (empty($fieldsRestrictions)) return '';
		$fieldsWithRestrictions = array_filter($fieldsRestrictions, ['V', 'filterNotEmpty']);
		if (empty($fieldsWithRestrictions)) return '';

		return array_reduce(
			array_map(function ($fieldName, $xsdRestriction) use ($sqlTablePrefix, $charset) {
				$sqlRestrictions = [];
				if (false !== strpos($fieldName, ':')) return $sqlRestrictions; // SKIP ref fields - TODO: generate remote query, require $acl
				if (!empty($xsdRestriction)) {
					if (!empty($xsdRestriction['enumeration'])) {
						// $sqlRestrictions[] = "{$sqlTablePrefix}.`{$fieldName}` in (" . implode(",", array_map([DB::getPDO(), 'quote'], array_keys($xsdRestriction['enumeration']))) . ")";
						$sqlRestrictions[] = "{$sqlTablePrefix}.`{$fieldName}` in (" . implode(",", array_map(function ($option) use ($charset) {
							return ($charset && $charset !== 'utf8')
							?	"CONVERT(" . DB::getPDO()->quote($option) . " using {$charset})"
							:	DB::getPDO()->quote($option);
						}, array_keys($xsdRestriction['enumeration']))) . ")";
					}
					if (array_key_exists('minInclusive', $xsdRestriction)) {
						$minInclusive = (int)$xsdRestriction['minInclusive'];
						$sqlRestrictions[] = "{$sqlTablePrefix}.`{$fieldName}` > {$minInclusive}";
					}
				}
				return $sqlRestrictions;
			}, array_keys($fieldsWithRestrictions), array_values($fieldsWithRestrictions)),
			function ($ret, $cur) {
				return array_merge($ret, array_filter($cur, ['V', 'filterNotEmpty']));
			},
			[]
		);
	}

	static function generateSqlWhereFromFieldRestrictions($fields, $sqlTablePrefix = 't') {
		return (!empty($fields))
			?	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;
					}, $fields),
					function ($ret, $cur) {
						return array_merge($ret, array_filter($cur, ['V', 'filterNotEmpty']));
					},
					[]
				)
			:	''
		;
	}

	static function generateIsInstanceFunctionBody($namespace, $item = null) { // TODO: NOT USED
		if (!$item) $item = SchemaFactory::loadDefaultObject('SystemObject')->getItem($namespace, [ 'propertyName' => '*,field' ]);
		if (!in_array( $item['_type'], [ 'AntAcl' ] )) return null;
		$localFieldsWithRestrictions = array_filter($item['field'], function ($field) {
			if (!$field['isLocal']) return false;
			if (empty($field['xsdRestrictions'])) return false;
			if ('[]' == $field['xsdRestrictions']) return false;
			return true;
		});
		// TODO: get fields with minOccurs > 1 (may require select by ref)
		$sqlTablePrefix = 'root';
		DBG::nicePrint($localFieldsWithRestrictions, "\$localFieldsWithRestrictions");
		$sqlWhereFromRestrictions = ACL::generateSqlWhereFromFieldRestrictions($localFieldsWithRestrictions, $sqlTablePrefix);
		DBG::nicePrint($sqlWhereFromRestrictions, "\$sqlWhereFromRestrictions");
		$sqlWhereFromRestrictions = (!empty($sqlWhereFromRestrictions)) ? implode(" and ", $sqlWhereFromRestrictions) : "1=1";
		$pkField = 'ID'; // TODO: primaryKeyField into SystemObject structure
		$rootTableName = $item['_rootTableName'];
		$sqlFunBody = ("1=1" !== $sqlWhereFromRestrictions)
		?	" RETURN IF(
				(select count(1) as cnt from `{$rootTableName}` root where root.`{$pkField}` = pk and {$sqlWhereFromRestrictions}) > 0
				, 1, 0)
			"
		:	" RETURN 1; ";
		return $sqlFunBody;
	}

	static function generateIsInstanceView($namespace, $item = null, Type_InstanceConfig $conf = null) {
		if (!$item) $item = SchemaFactory::loadDefaultObject('SystemObject')->getItem($namespace, [ 'propertyName' => '*,field' ]);
		if (!in_array( $item['_type'], [ 'AntAcl', 'StorageAcl' ] )) return null;
		$idInstance = ($conf) ? $conf->id : self::getInstanceId($namespace);

		$localFieldsWithRestrictions = array_filter($item['field'], function ($field) {
			if (!$field['isLocal']) return false;
			if (empty($field['xsdRestrictions'])) return false;
			if ('[]' == $field['xsdRestrictions']) return false;
			return true;
		});
		// TODO: get fields with minOccurs > 1 (may require select by ref)

		$fieldsWithRestrictions = array_reduce($localFieldsWithRestrictions, function ($ret, $field) {
			$fieldName = $field['fieldNamespace'];
			$restrictions = @json_decode($field['xsdRestrictions'], $assoc = true);
			$ret[$fieldName] = (!empty($restrictions)) ? $restrictions : null;
			return $ret;
		}, []);
		$sqlTablePrefix = "instance_{$idInstance}";
		$appInfo = (!empty($item['appInfo'])) ? @json_decode($item['appInfo'], $assoc = true) : null;
		$charset = (!empty($appInfo) && !empty($appInfo['table_structure']['@charset'])) ? $appInfo['table_structure']['@charset'] : null;
		$sqlListWhere = self::generateSqlWhereFromFieldsWithRestrictions($fieldsWithRestrictions, $sqlTablePrefix, $charset);
		$sqlWhere = (!empty($sqlListWhere)) ? implode("\n\t  and ", $sqlListWhere) : "1=1";
		$rootTableName = $item['_rootTableName'];
		$primaryKey = V::get('primaryKey', 'ID', $item);
		return implode("\n\t", [
			"select {$primaryKey}",
			"from `{$rootTableName}` `{$sqlTablePrefix}`",
			"where {$sqlWhere}",
		]);
	}

}