se/SE/se-lib/Schema/SystemObjectFieldStorageAcl.php

<?php

Lib::loadClass('Core_AclSimpleSchemaBase');
Lib::loadClass('ParseOgcFilter');
Lib::loadClass('Router');

class Schema_SystemObjectFieldStorageAcl extends Core_AclSimpleSchemaBase {

	public $_simpleSchema = [
		'root' => [
			'@namespace' => 'default_objects/SystemObjectField',
			'@primaryKey' => 'namespace',
			'idZasob' => [ '@type' => 'xsd:integer' ],
			'idDatabase' => [ '@type' => 'xsd:integer' ],
			'_rootTableName' => [ '@type' => 'xsd:string' ],
			'namespace' => [ '@type' => 'xsd:string' ],
			'objectNamespace' => [ '@type' => 'xsd:string' ],
			'fieldNamespace' => [ '@type' => 'xsd:string' ],
			'xsdType' => [ '@type' => 'xsd:string' ],
			'xsdRestrictions' => [ '@type' => 'xsd:string' ],
			'appInfo' => [ '@type' => 'xsd:string' ],
			'minOccurs' => [ '@type' => 'xsd:string' ],
			'maxOccurs' => [ '@type' => 'xsd:string' ], //	'0..unbounded',
			'isActive' => [ '@type' => 'xsd:integer' ], // installed
			'description' => [ '@type' => 'xsd:string' ],
			'isLocal' => [ '@type' => 'xsd:integer', '@comment' => "is in _rootTableName" ],
			// 'A_RECORD_CREATE_AUTHOR' => [ '@type' => 'xsd:string' , '@label' => 'autor' ],
			// 'A_RECORD_CREATE_DATE' => [ '@type' => 'xsd:date' , '@label' => 'utworzono' ],
			// 'A_RECORD_UPDATE_AUTHOR' => [ '@type' => 'xsd:string' , '@label' => 'zaktualizował' ],
			// 'A_RECORD_UPDATE_DATE' => [ '@type' => 'xsd:date', '@label' => 'zaktualizowano' ],
		]
	];
	// public $_rootTableName = 'CRM_LISTA_ZASOBOW';
	public $_rootTableName = 'CRM_#CACHE_ACL_OBJECT_FIELD';
	public $_enumRootTableName = 'CRM_#CACHE_ACL_OBJECT_FIELD_enum';
	public $_version = '2';

	public function __construct($simpleSchema = null) {
		parent::__construct($simpleSchema);
		$version = (int)DB::getPDO()->fetchValue(" select CONF_VAL from CRM_CONFIG where CONF_KEY='Schema_SystemObjectFieldStorageAcl__version' ");
		if ($version < $this->_version) {
			DBG::log("UPDATE Schema_SystemObjectFieldStorageAcl__version");
			DB::getPDO()->execSql("
				create table if not exists `{$this->_rootTableName}` (
					`namespace` varchar(255) DEFAULT '',
					`fieldNamespace` varchar(255) DEFAULT '',
					`idZasob` int(11) DEFAULT NULL,
					`idDatabase` int(11) NOT NULL,
					`_rootTableName` varchar(255) DEFAULT '',
					`objectNamespace` varchar(255) DEFAULT '',
					`xsdType` varchar(255) DEFAULT '',
					`xsdRestrictions` varchar(1000) DEFAULT '',
					`appInfo` varchar(1000) DEFAULT '',
					`minOccurs` int(11) DEFAULT '0',
					`maxOccurs` varchar(11) DEFAULT '1' COMMENT '0..unbounded',
					`isActive` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'installed',
					`description` varchar(255) DEFAULT '',
					`isLocal` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is in _rootTableName',
					UNIQUE KEY `idZasob` (idZasob),
					PRIMARY KEY (`namespace`),
					KEY `isActive` (isActive)
				) ENGINE=MyISAM DEFAULT CHARSET=latin2
			");
			try {
				DB::getPDO()->execSql(" ALTER TABLE `{$this->_rootTableName}` ADD `appInfo` VARCHAR(1000) NOT NULL DEFAULT '' AFTER `xsdRestrictions` ");
			} catch (Exception $e) {
				DBG::log($e);
			}
			try {
				DB::getPDO()->execSql(" ALTER TABLE `{$this->_rootTableName}` ADD `isLocal` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is in _rootTableName' AFTER `description` ");
			} catch (Exception $e) {
				DBG::log($e);
			}
			DB::getPDO()->execSql("
				create table if not exists `{$this->_enumRootTableName}` (
					`namespace` varchar(255) DEFAULT '' COMMENT 'concat obj ns / field ns / value',
					`fieldNamespace` varchar(255) DEFAULT '',
					`objectNamespace` varchar(255) DEFAULT '',
					`value` varchar(255) DEFAULT '',
					`label` varchar(255) DEFAULT '',
					`isActive` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'installed',
					`sortPrio` tinyint NOT NULL DEFAULT 0,
					KEY `objectNamespace` (`objectNamespace`),
					KEY `fieldNamespace` (`fieldNamespace`),
					KEY `isActive` (isActive),
					UNIQUE `enum_uniq_value` (`objectNamespace`, `fieldNamespace`, `value`, `isActive`)
				) ENGINE=MyISAM DEFAULT CHARSET=latin2
			");
			try {
				DB::getPDO()->execSql(" DELETE from `{$this->_enumRootTableName}` where isActive = 0 ");
				DB::getPDO()->execSql(" ALTER TABLE `{$this->_enumRootTableName}` ADD UNIQUE `enum_uniq_value` (`objectNamespace`, `fieldNamespace`, `value`, `isActive`) ");
			} catch (Exception $e) {
				DBG::log($e);
			}
			try {
				DB::getPDO()->execSql(" ALTER TABLE `{$this->_enumRootTableName}` ADD `sortPrio` tinyint NOT NULL DEFAULT 0 ");
			} catch (Exception $e) {
				DBG::log($e);
			}
			DB::getPDO()->execSql(" REPLACE INTO CRM_CONFIG (`CONF_KEY`,`CONF_VAL`) VALUES ('Schema_SystemObjectFieldStorageAcl__version', '{$this->_version}') ");
		}
	}

	public static function getAntAclXsdBasePath($typeName) {
		if (!$typeName) throw new Exception("Missing typeName");
		$antAclPath = null;
		if ($activeProject = Config::getProjectPath()) {
			$tryProjectSchemaPath = "{$activeProject}/schema/ant-object/" . str_replace(['__x3A__', ':'], ['.', '/'], $typeName);
			if (file_exists($tryProjectSchemaPath)) {
				$antAclPath = $tryProjectSchemaPath;
			}
		}
		if (!$antAclPath) {
			$antAclPath = APP_PATH_SCHEMA . DS . 'ant-object' . DS . str_replace(['__x3A__', ':'], ['.', '/'], $typeName);
		}
		return $antAclPath;
	}
	public function updateCache($namespace = null) {
		DBG::log("SystemObjectField::updateCache({$namespace})...");

		if (!$namespace) return;

		DB::getPDO()->update($this->_rootTableName, 'objectNamespace', $namespace, ['isActive' => 0]);
		DB::getPDO()->update("{$this->_enumRootTableName}", 'objectNamespace', $namespace, ['isActive' => 0]);
		if (!$namespace) throw new Exception("Missing namespace '{$namespace}'");
		$objectItem = SchemaFactory::loadDefaultObject('SystemObject')->getItem($namespace);
		DBG::nicePrint($objectItem, '$objectItem');
		DBG::log($objectItem, 'array', '$objectItem');

		switch ($objectItem['_type']) {
			case 'AntAcl': $this->updateCacheAntAcl($objectItem); break;
			case 'TableAcl': $this->updateCacheTableAcl($objectItem); break;
			case 'StorageAcl': $this->updateCacheStorageAcl($objectItem); break;
			default: throw new Exception("TODO: Not Implemented type '{$objectItem['_type']}'");
		}
		// TODO: mv from methods: SchemaFactory::loadDefaultObject('SystemObject')->updateItem([
		// 'namespace' => $item['namespace'],
		// 'isStructInstalled' => 1
		// ]);
		$dbName = DB::getPDO()->getDatabaseName();
		DB::getPDO()->execSql("
			update `{$this->_rootTableName}` t
			set t.isLocal = IF(
				(	select c.COLUMN_NAME
					from information_schema.COLUMNS c
					where c.COLUMN_NAME = t.fieldNamespace
						and c.TABLE_SCHEMA = '{$dbName}'
						and c.TABLE_NAME = '{$objectItem['_rootTableName']}'
					limit 1
				) is null
			, 0, 1)
			where t.objectNamespace = '{$objectItem['namespace']}'
			and t._rootTableName = '{$objectItem['_rootTableName']}'
		");
	}

	public function updateCacheAntAcl($item) {
		Lib::loadClass('AntAclBase');
		$antAclPath = Schema_SystemObjectFieldStorageAcl::getAntAclXsdBasePath($item['typeName']);
		DBG::log(['typeName'=>$item['typeName'], 'antAclPath'=>$antAclPath], 'array', "AntAcl build.xml path");
		if (!file_exists("{$antAclPath}/build.xml")) throw new Exception("Ant build file not exists");

		Lib::loadClass('XML');
		$xsdType = XML::getXsdTypeFromXsdSchema("{$antAclPath}/{$item['name']}.xsd", $namespace = $item['namespace'], $name = $item['name']);
		DBG::nicePrint($xsdType, '$xsdType');
		if (empty($xsdType['struct'])) throw new Exception("Field list not found for '{$item['namespace']}'");
		foreach ($xsdType['struct'] as $fieldName => $x) {
			$listEnum = [];
			if (!empty($x['restrictions']['enumeration'])) {
				$listEnum = $x['restrictions']['enumeration'];
				unset($x['restrictions']['enumeration']);
			}
			DB::getPDO()->insertOrUpdate($this->_rootTableName, [
				'namespace' => "{$item['namespace']}/{$fieldName}",
				'objectNamespace' => $item['namespace'],
				'idDatabase' => $item['idDatabase'],
				'_rootTableName' => $item['_rootTableName'],
				'fieldNamespace' => $fieldName,
				'xsdType' => $x['type'],
				'xsdRestrictions' => json_encode($x['restrictions']),
				'appInfo' => json_encode($x['appInfo']),
				'minOccurs' => $x['minOccurs'],
				'maxOccurs' => $x['maxOccurs'],
				'isActive' => 1
			]);
			if (!empty($listEnum)) {
				DBG::nicePrint($listEnum, '$listEnum');
				$sortPrio = count($listEnum);
				foreach ($listEnum as $value => $label) {
					DB::getPDO()->insertOrUpdate("{$this->_enumRootTableName}", [
						'namespace' => "{$item['namespace']}/{$fieldName}/@{$value}",
						'fieldNamespace' => $fieldName,
						'objectNamespace' => $item['namespace'],
						'value' => $value,
						'label' => $label,
						'isActive' => 1,
						'sortPrio' => $sortPrio--
					]);
				}
			}
		}
		SchemaFactory::loadDefaultObject('SystemObject')->updateItem([
			'namespace' => $item['namespace'],
			'isStructInstalled' => 1,
			'primaryKey' => $xsdType['primaryKey'],
		]);

		DB::getPDO()->execSql("
			DELETE from `{$this->_enumRootTableName}`
			where isActive = 0
				and objectNamespace = :objectNamespace
		", [
			':objectNamespace' => $item['namespace'],
		]);

		$zasobTableName = substr($item['objectNamespace'], strlen('default_db/'));
		$zasobTableName = (false !== strpos($zasobTableName, '/'))
		?	$item['objectNamespace']
		:	$zasobTableName;
		DB::getPDO()->execSql("
			update `{$this->_rootTableName}` t
			join CRM_LISTA_ZASOBOW zp on(zp.PARENT_ID = {$item['idDatabase']} and zp.`DESC` = '{$zasobTableName}')
			join CRM_LISTA_ZASOBOW z on(z.PARENT_ID = zp.ID and z.`DESC` = t.fieldNamespace)
			set t.idZasob = z.ID
			where t.objectNamespace = '{$item['namespace']}'
		");
		UI::alert('success', "Gotowe");

		{// TODO: DBG
			$schema = XML::readXmlFileToArray("{$antAclPath}/{$item['name']}.xsd");
			DBG::nicePrint($schema, '$schema');
			Lib::loadClass('Core_XmlWriter');
			ob_start();
			$xmlWriter = new Core_XmlWriter();
			$xmlWriter->openUri('php://output');
			$xmlWriter->setIndent(true);
			$xmlWriter->startDocument('1.0','UTF-8');
			$xmlWriter->h($schema[0], $schema[1], $schema[2]);
			$xmlWriter->endDocument();
			echo UI::h('pre', ['style' => 'max-height:400px; overflow:scroll'], htmlspecialchars(ob_get_clean()));
		}
	}
	public function updateCacheStorageAcl($item) {
		Lib::loadClass('SchemaFactory');
		$aclName = substr($item['namespace'], strlen('default_objects/'));
		DBG::log($aclName, 'array', "\$aclName");
		$fvAcl = SchemaFactory::loadDefaultObject($aclName);
		DBG::log($fvAcl->getFields(), 'array', "\$fvAcl->getFields()");
		foreach ($fvAcl->getFields() as $field) {
			// 'name' => 'id',
			// 'perms' => '',
			// 'idZasob' => 10000,
			// 'xsdType' => 'xsd:integer',
			// 'label' => 'id',
			$fieldName = $field['name'];
			$xsdType = $field['xsdType'];
			DB::getPDO()->insertOrUpdate($this->_rootTableName, [
				'namespace' => "{$item['namespace']}/{$fieldName}",
				'objectNamespace' => $item['namespace'],
				'idDatabase' => $item['idDatabase'],
				'_rootTableName' => $item['_rootTableName'],
				'fieldNamespace' => $fieldName,
				'xsdType' => $xsdType,
				// 'xsdRestrictions' => '',
				// 'appInfo' => '',
				// 'minOccurs' => '',
				// 'maxOccurs' => '',
				'isActive' => 1
			]);
			$listEnum = [];
			if (!empty($listEnum)) {
				DBG::nicePrint($listEnum, '$listEnum');
				foreach ($listEnum as $value => $label) {
					DB::getPDO()->insertOrUpdate("{$this->_enumRootTableName}", [
						'namespace' => "{$item['namespace']}/{$fieldName}/@{$value}",
						'fieldNamespace' => $fieldName,
						'objectNamespace' => $item['namespace'],
						'value' => $value,
						'label' => $label,
						'isActive' => 1
					]);
				}
			}
		}
		SchemaFactory::loadDefaultObject('SystemObject')->updateItem([
			'namespace' => $item['namespace'],
			'isStructInstalled' => 1
		]);
		// $zasobTableName = substr($item['objectNamespace'], strlen('default_db/'));
		// $zasobTableName = (false !== strpos($zasobTableName, '/'))
		// ? $item['objectNamespace']
		// : $zasobTableName;
		// DB::getPDO()->execSql("
		// 	update `{$this->_rootTableName}` t
		// 	join CRM_LISTA_ZASOBOW zp on(zp.PARENT_ID = {$item['idDatabase']} and zp.`DESC` = '{$zasobTableName}')
		// 	join CRM_LISTA_ZASOBOW z on(z.PARENT_ID = zp.ID and z.`DESC` = t.fieldNamespace)
		// 	set t.idZasob = z.ID
		// 	where t.objectNamespace = '{$item['namespace']}'
		// ");
	}
	public function updateCacheTableAcl($item) {
		// TODO: xsdType - convert mysql type to xsdType, xsdRestrictions
		$xsdInfo = [];
		Lib::loadClass('SchemaHelper');
		$dbName = DB::getPDO()->getDatabaseName();
		$mysqlTypes = DB::getPDO()->fetchAll("
			select c.COLUMN_NAME, c.COLUMN_TYPE, c.DATA_TYPE, c.IS_NULLABLE, c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, c.NUMERIC_SCALE
			from `information_schema`.`COLUMNS` c
			where c.TABLE_SCHEMA = '{$dbName}'
				and c.TABLE_NAME = '{$item['_rootTableName']}'
		");
		foreach ($mysqlTypes as $mysqlType) {
			$xsdInfo[ $mysqlType['COLUMN_NAME'] ] = SchemaHelper::convertMysqlTypeToSchemaXsd($mysqlType);
		}
		DBG::nicePrint($xsdInfo, '$xsdInfo');

		foreach ($xsdInfo as $fieldName => $x) {
			$listEnum = [];
			if (!empty($x['restrictions']['enumeration'])) {
				$listEnum = $x['restrictions']['enumeration'];
				unset($x['restrictions']['enumeration']);
			}
			DB::getPDO()->insertOrUpdate($this->_rootTableName, [
				'namespace' => "{$item['namespace']}/{$fieldName}",
				'fieldNamespace' => $fieldName,
				'isActive' => 1,
				'idDatabase' => $item['idDatabase'],
				'_rootTableName' => $item['_rootTableName'],
				'objectNamespace' => $item['namespace'],
				'xsdType' => $x['type'],
				'xsdRestrictions' => json_encode($x['restrictions']),
				'appInfo' => json_encode([]),
			]);
			if (!empty($listEnum)) {
				DBG::nicePrint($listEnum, '$listEnum');
				foreach ($listEnum as $value => $label) {
					DB::getPDO()->insertOrUpdate("{$this->_enumRootTableName}", [
						'namespace' => "{$item['namespace']}/{$fieldName}/@{$value}",
						'fieldNamespace' => $fieldName,
						'objectNamespace' => $item['namespace'],
						'value' => $value,
						'label' => $label,
						'isActive' => 1
					]);
				}
			}
		}
		SchemaFactory::loadDefaultObject('SystemObject')->updateItem([
			'namespace' => $item['namespace'],
			'isStructInstalled' => 1
		]);
		$zasobTableName = substr($item['namespace'], strlen('default_db/'));
		$zasobTableName = (false !== strpos($zasobTableName, '/'))
		?	$item['namespace']
		:	$zasobTableName;
		DB::getPDO()->execSql("
			update `{$this->_rootTableName}` t
			join CRM_LISTA_ZASOBOW zp on(zp.PARENT_ID = {$item['idDatabase']} and zp.`DESC` = '{$zasobTableName}' and zp.PARENT_ID = {$item['idDatabase']})
			join CRM_LISTA_ZASOBOW z on(z.PARENT_ID = zp.ID and z.`DESC` = t.fieldNamespace)
			set t.idZasob = z.ID
			where t.objectNamespace = '{$item['namespace']}'
		");

		$struct = $this->getItems([
			'__backRef' => [
				'namespace' => 'default_objects/SystemObject',
				'primaryKey' => $item['namespace']
			]
		]);
		DBG::nicePrint($struct, '$struct');

		{// TODO: DBG
			$schema = ['xsd:schema', [
				'xmlns:xsd' => "http://www.w3.org/2001/XMLSchema",
				'xmlns:gml' => "http://www.opengis.net/gml",
				'xmlns:p5' => "https://biuro.biall-net.pl/wfs",
				'xmlns:default_db' => "https://biuro.biall-net.pl/wfs/default_db",
				'xmlns:default_objects' => "https://biuro.biall-net.pl/wfs/default_objects",
				'elementFormDefault' => "qualified",
				'version' => "1.0.0",
			], []];
			Lib::loadClass('Api_WfsNs');
			$tnsUri = Api_WfsNs::getNsUri($item['nsPrefix']);
			$schema[1]['xmlns:' . Api_WfsNs::getNsPrefix($tnsUri)] = $tnsUri;
			$schema[1]['targetNamespace'] = $tnsUri;
			$schema[2][] = ['xsd:import', ['namespace'=>"http://www.opengis.net/gml", 'schemaLocation'=>"https://biuro.biall-net.pl/dev-pl/se-master/schema/gml/2.1.2/feature.xsd"], null];
			$schema[2][] = ['xsd:complexType', [ 'name'=> $item['name'] . "Type" ], [
				[ 'xsd:complexContent', [], [
					[ 'xsd:extension', ['base'=>"gml:AbstractFeatureType"], [
						[ 'xsd:sequence', [], array_map(function ($field) {
							$attrs = [
								'name' => $field['fieldNamespace'],
								// <xsd:element minOccurs="1" maxOccurs="1" name="ID" type="xsd:integer" nillable="true"/>
								'minOccurs' => $field['minOccurs'],
								'maxOccurs' => $field['maxOccurs'],
							];
							$childrens = null;
							$restrictions = [];
							foreach ($this->getXsdRestrictionsValue($field) as $k => $v) {
								if ('enumeration' == $k) {
									foreach ($v as $enumValue) {
										$restrictions[] = [ 'xsd:enumeration', [ 'value' => $enumValue ], null ];
									}
								} else if ('maxLength' == $k) {
									$restrictions[] = [ 'xsd:maxLength', [ 'value' => $v ], null ];
								} else if ('totalDigits' == $k) {
									$restrictions[] = [ 'xsd:totalDigits', [ 'value' => $v ], null ];
								} else if ('fractionDigits' == $k) {
									$restrictions[] = [ 'xsd:fractionDigits', [ 'value' => $v ], null ];
								} else if ('nillable' == $k) {
									if ($v) $attrs['nillable'] = "true";
								} else {
									DBG::log(['TODO' => $k, $v]);
								}
							}
							if (!empty($restrictions)) {
								$childrens = [
									[ 'xsd:simpleType', [], [
										[ 'xsd:restriction', [ 'base' => $field['xsdType'] ], $restrictions ]
									] ]
								];
							} else {
								$attrs['type'] = $field['xsdType'];
							}
							return [ 'xsd:element', $attrs, $childrens ];
						}, $struct) ]
					] ]
				] ]
			] ];
			$schema[2][] = ['xsd:element', [ 'name' => $item['name'], 'type' => "{$item['nsPrefix']}:{$item['name']}Type", 'substitutionGroup' => "gml:_Feature" ], null];

			DBG::nicePrint($schema, '$schema');
			Lib::loadClass('Core_XmlWriter');
			ob_start();
			$xmlWriter = new Core_XmlWriter();
			$xmlWriter->openUri('php://output');
			$xmlWriter->setIndent(true);
			$xmlWriter->startDocument('1.0','UTF-8');
			$xmlWriter->h($schema[0], $schema[1], $schema[2]);
			$xmlWriter->endDocument();
			echo UI::h('pre', ['style' => 'max-height:400px; overflow:scroll'], htmlspecialchars(ob_get_clean()));
		}
	}

	public function _parseWhere($params = []) {
		$sqlWhere = [];
		DBG::log($params, 'array', "SystemObject::_parseWhere");
		if (!empty($params['__backRef'])) {
			// '__backRef' => [
			//	 'namespace' => 'default_objects/SystemObject',
			//	 'primaryKey' => 'default_db/TEST_PERMS',
			//	),
			if (empty($params['__backRef']['namespace'])) throw new Exception("Missing refFrom/namespace");
			if (empty($params['__backRef']['primaryKey'])) throw new Exception("Missing refFrom/primaryKey");

			if ('default_objects/SystemObject' != $params['__backRef']['namespace']) throw new Exception("Unsupported refFrom/namespace '{$params['__backRef']['namespace']}'");
			$sqlWhere[] = "t.objectNamespace = " . DB::getPDO()->quote($params['__backRef']['primaryKey'], PDO::PARAM_INT);
		}
		{
			$filterParams = [];
			$xsdFields = $this->getXsdTypes();
			DBG::log($xsdFields);
			foreach ($params as $k => $v) {
				if ('f_' != substr($k, 0, 2)) continue;
				$fieldName = substr($k, 2);
				if (!array_key_exists($fieldName, $xsdFields)) {
					// TODO: check query by xpath or use different param prefix
					throw new Exception("Field '{$fieldName}' not found in '{$this->_namespace}'");
				}
				if ('p5:' == substr($xsdFields[$fieldName], 0, 3)) {
					continue;
				}
				$filterParams[$fieldName] = $v;
			}
		}
		if (!empty($filterParams)) {
			DBG::log($filterParams, 'array', "SystemObject::_parseWhere TODO \$filterParams");
			foreach ($filterParams as $fieldName => $value) {
				if (is_array($value)) {
					DBG::log($value, 'array', "TODO SystemObject::_parseWhere array value for \$filterParams[{$fieldName}]");
				} else if (is_scalar($value)) {
					if ('=' == substr($value, 0, 1)) {
						$sqlWhere[] = "t.{$fieldName} = " . DB::getPDO()->quote(substr($value, 1), PDO::PARAM_STR);
					} else {
						$sqlWhere[] = "t.{$fieldName} like " . DB::getPDO()->quote("%{$value}%", PDO::PARAM_STR);
					}
				} else {
					DBG::log($value, 'array', "BUG SystemObject::_parseWhere unknown type for \$filterParams[{$fieldName}]");
				}
			}
		}
		return (!empty($sqlWhere)) ? "where " . implode(" and ", $sqlWhere) : '';
	}

	public function getTotal($params = []) {
		$sqlWhere = $this->_parseWhere($params);
		return DB::getPDO()->fetchValue("
			select count(1) as cnt
			from `{$this->_rootTableName}` t
			{$sqlWhere}
		");
	}

	public function getItem($pk, $params = []) {
		if (!$pk) throw new Exception("Missing primary key '{$this->_namespace}'");
		$pkField = $this->getSqlPrimaryKeyField();
		if (!$pkField) throw new Exception("Missing primary key field defined in '{$this->_namespace}'");
		$sqlPk = DB::getPDO()->quote($pk, PDO::PARAM_STR);
		$item = DB::getPDO()->fetchFirst("
			select t.*
			from `{$this->_rootTableName}` t
			where t.`{$pkField}` = {$sqlPk}
		");
		if (!$item) throw new Exception("Item '{$pk}' not exists - type '{$this->_namespace}'");
		return $this->buildFeatureFromSqlRow($item, $params);
	}

	public function getItems($params = []) {
		$sqlWhere = $this->_parseWhere($params);

		$currSortCol = V::get('order_by', 'idZasob', $params);
		$currSortFlip = strtolower(V::get('order_dir', 'desc', $params));
		// TODO: validate $currSortCol is in field list
		// TODO: validate $currSortFlip ('asc' or 'desc')
		$xsdFields = $this->getXsdTypes();
		if (!array_key_exists($currSortCol, $xsdFields)) throw new Exception("Field '{$currSortCol}' not found in '{$this->_namespace}'");
		if (!in_array($currSortFlip, ['asc', 'desc'])) throw new Exception("Sort dir not allowed");
		$sqlOrderBy = "order by t.`{$currSortCol}` {$currSortFlip}";

		$limit = V::get('limit', 0, $params, 'int');
		$limit = ($limit < 0) ? 0 : $limit;
		$offset = V::get('limitstart', 0, $params, 'int');
		$offset = ($offset < 0) ? 0 : $offset;
		$sqlLimit = ($limit > 0)
		?	"limit {$limit} offset {$offset}"
		:	'';

		return array_map(array($this, 'buildFeatureFromSqlRow'), DB::getPDO()->fetchAll("
			select t.*
			from `{$this->_rootTableName}` t
			{$sqlWhere}
			{$sqlOrderBy}
			{$sqlLimit}
		"));
	}

	public function getXsdRestrictionsValue($item) {
		$xsdRestrictions = @json_decode($item['xsdRestrictions'], $assoc = true);
		if (empty($xsdRestrictions)) $xsdRestrictions = [];
		if (array_key_exists('__DBG__', $xsdRestrictions)) unset($xsdRestrictions['__DBG__']);
		return $xsdRestrictions;
	}

	public function buildFeatureFromSqlRow($item) {
		if ('p5:enum' == V::get('xsdType', '', $item)) {
			$xsdRestrictions = @json_decode($item['xsdRestrictions'], $assoc = true);
			$xsdRestrictions['enumeration'] = [];
			foreach (DB::getPDO()->fetchAll("
				select t.value, t.label
				from `{$this->_enumRootTableName}` t
				where t.objectNamespace = '{$item['objectNamespace']}'
					and t.fieldNamespace = '{$item['fieldNamespace']}'
					and t.isActive = 1
				order by sortPrio DESC
			") as $enum) {
				$xsdRestrictions['enumeration'][ $enum['value'] ] = $enum['label'];
			}
			$item['xsdRestrictions'] = json_encode($xsdRestrictions);
		}
		return $item;
	}

	public function updateItem($itemPatch) {
		SchemaFactory::loadDefaultObject('SystemObject')->clearGetItemCache();
		$pkField = $this->getPrimaryKeyField();
		$pk = V::get($pkField, null, $itemPatch);
		if (null === $pk) throw new Exception("BUG missing primary key field for '{$this->_namespace}' updateItem");
		DBG::log(['updateItem $itemPatch', $itemPatch]);
		unset($itemPatch[$pkField]);
		if (empty($itemPatch)) return 0;
		foreach ($itemPatch as $fieldName => $value) {
			if ('idZasob' == $fieldName) continue;
			throw new Exception("Update field '{$fieldName}' not allowed for '{$this->_namespace}'");
		}
		return DB::getPDO()->update($this->_rootTableName, $pkField, $pk, $itemPatch);
	}

}