se/SE/se-lib/InstanceConfig.php

<?php

Lib::loadClass('SchemaVersionUpgrade');

class InstanceConfig {

	static function createInstanceConfigTable() {
		DB::getPDO()->execSql("
			create table if not exists `CRM_INSTANCE_CONFIG` (
				`id` int(11) not null AUTO_INCREMENT,
				`namespace` varchar(255) NOT NULL DEFAULT '',
				`rootNamespace` varchar(255) NOT NULL DEFAULT '',
				`idInstanceBase` int(11) NOT NULL DEFAULT 0,
				`_createdAt` datetime NOT NULL,
				UNIQUE KEY `namespace` (`namespace`),
				KEY `rootNamespace` (`rootNamespace`),
				PRIMARY KEY (`id`)
			) ENGINE=MyISAM  DEFAULT CHARSET=latin2
		");
	}

	static function getInstanceTable($namespace) { // @returns tableName with struct { pk, idInstance, _createdAt }
		$conf = self::getInstanceConfig($namespace);
		if (!empty($conf['idInstanceBase'])) return "CRM__#INSTANCE_TABLE__{$conf['idInstanceBase']}";

		$rootNs = $conf['rootNamespace'];
		$rootConf = self::getInstanceConfig($rootNs);
		$instanceTableName = "CRM__#INSTANCE_TABLE__{$rootConf['id']}";
		if (!empty($rootConf['idInstance'])) {
			$affected = DB::getPDO()->update("CRM_INSTANCE_CONFIG", 'rootNamespace', $rootNs, [
				'idInstanceBase' => $rootConf['id']
			]);
			return $instanceTableName;
		}

		// 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='{$rootNs} #INSTANCE';
		");
		$affected = DB::getPDO()->update("CRM_INSTANCE_CONFIG", 'rootNamespace', $rootNs, [
			'idInstanceBase' => $rootConf['id']
		]);
		return $instanceTableName;
	}

	static function getInstanceId($namespace) {
		return self::getInstanceConfig($namespace)['id'];
	}

	static function getInstanceConfig($namespace) { // @returns { id, namespace, rootNamespace, idInstanceBase, _createdAt }
		SchemaVersionUpgrade::upgradeSchema();
		try {
			$conf = self::fetchInstanceConfig($namespace);
		} catch (Exception $e) {
			self::createInstanceConfigTable();
			// TODO:?: `_tableInstalled` tinyint(1) not null default 0,
			$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}'");
		return $conf;
	}

	static function fetchInstanceConfig($namespace) {
		return DB::getPDO()->fetchFirst("
			select c.*
			from `CRM_INSTANCE_CONFIG` c
			where c.namespace = :namespace
		", [
			':namespace' => $namespace
		]);
	}

	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') { // @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) {
				$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']))) . ")";
					}
				}
				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) {
		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) {
		if (!$item) $item = SchemaFactory::loadDefaultObject('SystemObject')->getItem($namespace, [ 'propertyName' => '*,field' ]);
		if (!in_array( $item['_type'], [ 'AntAcl' ] )) return null;
		$idInstance = 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}";
		$sqlListWhere = self::generateSqlWhereFromFieldsWithRestrictions($fieldsWithRestrictions, $sqlTablePrefix);
		$sqlWhere = (!empty($sqlListWhere)) ? implode("\n\t  and ", $sqlListWhere) : "1=1";
		$rootTableName = $item['_rootTableName'];
		$primaryKey = 'ID'; // TODO: primaryKeyField into SystemObject structure // TODO: is in struct, read from `primaryKey`? field
		return implode("\n\t", [
			"select {$primaryKey}",
			"from `{$rootTableName}` `{$sqlTablePrefix}`",
			"where {$sqlWhere}",
		]);
	}

}