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

<?php

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

class Schema_SystemObjectStorageAcl extends Core_AclSimpleSchemaBase {

  public $_simpleSchema = [
    'root' => [
      '@namespace' => 'default_objects/SystemObject',
      '@primaryKey' => 'idZasob',
      'idZasob' => [ '@type' => 'xsd:integer' ],
      'idDatabase' => [ '@type' => 'xsd:integer' ],
      'namespace' => [ '@type' => 'xsd:string' ],
      '_rootTableName' => [ '@type' => 'xsd:string' ],
      '_type' => [ '@type' => 'xsd:string' ],
      'isActive' => [ '@type' => 'xsd:integer' ], // installed
      'description' => [ '@type' => 'xsd:string' ],
      'name' => [ '@type' => 'p5:string' ],
      'typeName' => [ '@type' => 'p5:string' ],
      'reinstallLink' => [ '@type' => 'p5:www_link' ],
      // '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' ],
    ]
    // 'root' => [
    //   '@namespace' => 'default_objects/SystemObject',
    //   '@primaryKey' => 'ID',
    //   'ID' => [ '@type' => 'xsd:integer' ],
    //   'namespace' => [ '@type' => 'xsd:string' ],
    //   'typeName' => [ '@type' => 'xsd:string' ],
    //   'tabela' => [ '@type' => 'xsd:string', '@alias' => 'DESC' ],
    //   'nazwa' => [ '@type' => 'xsd:string', '@alias' => 'DESC' ],
    //   'opis' => [ '@type' => 'xsd:string', '@alias' => 'OPIS' ],
    //   'id_zasob' => [ '@type' => 'xsd:integer' ],
    //   'autor' => [ '@type' => 'xsd:string' , '@alias' => 'A_RECORD_CREATE_AUTHOR' ],
    //   'utworzono' => [ '@type' => 'xsd:date' , '@alias' => 'A_RECORD_CREATE_DATE' ],
    //   'zaktualizował' => [ '@type' => 'xsd:string' , '@alias' => 'A_RECORD_UPDATE_AUTHOR' ],
    //   'zaktualizowano' => [ '@type' => 'xsd:date', '@alias' => 'A_RECORD_UPDATE_DATE' ]
    // ]
  ];
  // public $_rootTableName = 'CRM_LISTA_ZASOBOW';
  public $_rootTableName = 'CRM_#CACHE_ACL_OBJECT';
  public $_version = '1';

  // public function __construct($simpleSchema = null) {
  //   parent::__construct($simpleSchema);
  //   self::updateCacheIfNeeded(array($this, 'updateCache'));
  // }

  // public static function updateCacheIfNeeded($updateCallback) {
  //   static $_cacheUpdated = null;
  //   if (null !== $_cacheUpdated) return;
  //   $conf = DB::getPDO()->fetchFirst("
  //     select (select CONF_VAL from CRM_CONFIG where CONF_KEY = 'SystemObject__last_cache_update_date') as lastCacheUpdate
  //         ,  (select CONF_VAL from CRM_CONFIG where CONF_KEY = 'SystemObject__force_cache_update') as forceCacheUpdate
  //         ,  (select CONF_VAL from CRM_CONFIG where CONF_KEY = 'SystemObject__schema_version') as schemaVersion
  //         ,  (select CONF_VAL from CRM_CONFIG where CONF_KEY = 'tbl_indexer_CRM_PROCES_last_exec_end') as lastProcesIndexer
  //         ,  (
  //               select IF('N/S;' = A_RECORD_UPDATE_DATE or A_RECORD_UPDATE_DATE is null or '' = A_RECORD_UPDATE_DATE, A_RECORD_CREATE_DATE, A_RECORD_UPDATE_DATE) as lastUpdateDate
  //               from CRM_LISTA_ZASOBOW_HIST
  //               order by ID DESC
  //               limit 1
  //             ) as lastZasobyUpdate
  //   ");
  //   if ($conf['lastZasobyUpdate']) {// fix date format - convert to Mysql date time format '2002-05-30 09:00:00'
  //     if (preg_match('/^\d\d\d\d\-\d\d\-\d\d\-\d\d:\d\d$/', $conf['lastZasobyUpdate'])) { // 'Y-m-d-H:i'
  //       $conf['lastZasobyUpdate'] = substr($conf['lastZasobyUpdate'], 0, 10) . " " . substr($conf['lastZasobyUpdate'], 11) . ":00";
  //     } else if (preg_match('/^\d\d\d\d\-\d\d\-\d\d\-\d\d:\d\d:\d\d$/', $conf['lastZasobyUpdate'])) { // 'Y-m-d-H:i:s'
  //       $conf['lastZasobyUpdate'] = substr($conf['lastZasobyUpdate'], 0, 10) . " " . substr($conf['lastZasobyUpdate'], 11);
  //     } else if (preg_match('/^\d\d\d\d\-\d\d\-\d\d\ \d\d:\d\d:\d\d$/', $conf['lastZasobyUpdate'])) {
  //       $conf['lastZasobyUpdate'] = substr($conf['lastZasobyUpdate'], 0, 10) . " " . substr($conf['lastZasobyUpdate'], 11);
  //     }
  //   }
  //   // $lastProcesIndexer = DB::getPDO()->fetchValue(" select CONF_VAL from CRM_CONFIG where CONF_KEY = 'tbl_indexer_CRM_PROCES_last_exec_end' ");
  //   $updateReason = null;
  //   if ('1' == $conf['forceCacheUpdate']) $updateReason = 'forceCacheUpdate = 1';
  //   if (!$conf['lastCacheUpdate']) $updateReason = '!lastCacheUpdate';
  //   if (!$conf['schemaVersion']) $updateReason = '!schemaVersion';
  //   else if ($conf['schemaVersion'] < $this->_version) $updateReason = 'schemaVersion < ' . $this->_version;
  //   if ($conf['lastCacheUpdate'] && $conf['lastZasobyUpdate'] && $conf['lastCacheUpdate'] < $conf['lastZasobyUpdate']) $updateReason = 'lastZasobyUpdate';
  //   if ($updateReason) {
  //     DBG::simpleLog('schema', "SystemObject: update cache reason '{$updateReason}' '" . json_encode($conf) . "'");
  //     call_user_func($updateCallback);
  //   }
  //   DB::getPDO()->execSql("
  //     insert into CRM_CONFIG (CONF_KEY, CONF_VAL)
  //       values('SystemObject__last_cache_update_date', NOW())
  //       on duplicate key update CONF_VAL = NOW()
  //   ");
  //   DB::getPDO()->execSql("
  //     insert into CRM_CONFIG (CONF_KEY, CONF_VAL)
  //       values('SystemObject__schema_version', {$this->_version})
  //       on duplicate key update CONF_VAL = {$this->_version}
  //   ");
  //   if ('1' == $conf['forceCacheUpdate']) DB::getPDO()->execSql(" update CRM_CONFIG set CONF_VAL = '0' where CONF_KEY = 'SystemObject__force_cache_update' ");
  //   $_cacheUpdated = true;
  // }

  public function updateCache($idDatabase = null) {
    DBG::simpleLog('schema', "SystemObject::updateCache...");
    // DB::getPDO()->execSql(" drop table if exists `{$this->_rootTableName}` "); // TODO: DBG
    DB::getPDO()->execSql("
      create table if not exists `{$this->_rootTableName}` (
        `idZasob` int(11) DEFAULT NULL,
        `idDatabase` int(11) NOT NULL,
        `namespace` varchar(255) DEFAULT '',
        `_rootTableName` varchar(255) DEFAULT '',
        `_type` varchar(255) DEFAULT '',
        `isActive` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'installed',
        `description` varchar(255) DEFAULT '',
        UNIQUE KEY `idZasob` (idZasob),
        UNIQUE KEY `namespace` (namespace),
        KEY `isActive` (isActive)
      ) ENGINE=MyISAM  DEFAULT CHARSET=latin2
    ");
    DB::getPDO()->execSql(" update `{$this->_rootTableName}` set isActive = 0 ");
    $idDefDB = DB::getPDO()->getZasobId();

    $sourceStorage = SchemaFactory::loadDefaultObject('SystemSource');
    foreach ($sourceStorage->getItems([ 'f_hasConfig' => 1 ]) as $source) {
      if ('default_objects' == $source['nsPrefix']) {
        foreach (glob(APP_PATH_LIB . "/Schema/*StorageAcl.php", GLOB_NOSORT) as $file) {
					$clsName = substr(basename($file), 0, -1 * strlen('StorageAcl.php'));
					try {
						$acl = SchemaFactory::loadDefaultObject($clsName);
						$namespace = $acl->getNamespace();
		        DB::getPDO()->insertOrUpdate($this->_rootTableName, [
              'namespace' => $namespace,
              'idDatabase' => $source['idZasob'],
              '_type' => "StorageAcl",
              'isActive' => 1
            ]);
					} catch (Exception $e) {
						UI::alert('danger', $e->getMessage());
					}
				}
        DB::getPDO()->execSql("
          insert into `{$this->_rootTableName}` (namespace, idZasob, idDatabase, description, isActive)
            select concat('{$source['nsPrefix']}/', t.`DESC`)
                ,  t.ID as idZasob
                ,  '{$source['idZasob']}' as idDatabase
                ,  t.`OPIS` as description
                ,  1 as isActive
            from CRM_LISTA_ZASOBOW t
            where t.`TYPE` = 'TABELA'
              and t.A_STATUS in('NORMAL', 'WAITING')
              and t.PARENT_ID = {$source['idZasob']}
              and t.`DESC` not like '%/%'
          on duplicate key update idZasob = t.ID
                                , isActive = 1
        ");
      }
      else {
        try {
          $dbName = DB::getPDO($source['idZasob'])->getDatabaseName();
          $dbType = DB::getPDO($source['idZasob'])->getType();
          if ('mysql' == $dbType) {
            UI::alert('warning', "TODO: { id: {$source['idZasob']}, nsPrefix: '{$source['nsPrefix']}', dbName: '{$dbName}', dbType: '{$dbType}'} ...");
            // TODO: if another DB split select and insert
            DB::getPDO()->execSql("
              insert into `{$this->_rootTableName}` (namespace, idDatabase, _rootTableName, _type, description)
                select concat('{$source['nsPrefix']}/', t.TABLE_NAME) as namespace
                    , '{$source['idZasob']}' as idDatabase
                    , t.TABLE_NAME as _rootTableName
                    , 'TableAcl' as _type
                    , t.TABLE_COMMENT as description
                from INFORMATION_SCHEMA.TABLES t
          			where t.TABLE_SCHEMA = '{$dbName}'
              on duplicate key update _rootTableName = t.TABLE_NAME
            ");
            DB::getPDO()->execSql("
              insert into `{$this->_rootTableName}` (namespace, idZasob, idDatabase, description, isActive)
                select concat('{$source['nsPrefix']}/', t.`DESC`) as namespace
                    , t.ID as idZasob
                    , '{$source['idZasob']}' as idDatabase
                    , t.`OPIS` as description
                    , 1 as isActive
                from CRM_LISTA_ZASOBOW t
                where t.`TYPE` = 'TABELA'
                  and t.A_STATUS in('NORMAL', 'WAITING')
                  and t.PARENT_ID = {$source['idZasob']}
              on duplicate key update idZasob = t.ID
                                    , isActive = 1
            ");
          // } else if ('pgsql' == $dbType) {// TODO: use pgsql @see Storage Pgsql getTables from information_schema
          } else {
            UI::alert('warning', "TODO: { id: {$source['idZasob']}, nsPrefix: '{$source['nsPrefix']}', dbName: '{$dbName}', dbType: <b>'{$dbType}'</b>} ...");
          }
        } catch (Exception $e) {
          UI::alert('danger', "Error source '{$source['idZasob']}' " . $e->getMessage());
          continue;
        }
      }
    }

    // Ant objects in: SE/schema/ant-object/
    foreach (glob(APP_PATH_SCHEMA . "/ant-object/*/*/build.xml", GLOB_NOSORT) as $file) {
      // SE/schema/ant-object/default_db.test_perms/TestPermsAnt/build.xml
      $file = substr($file, strlen(APP_PATH_SCHEMA . '/ant-object/'), -1 * strlen('/build.xml'));
      DBG::nicePrint($file, '$file');
      list($partSource, $name) = explode('/', $file);
      list($sourceName, $rootTableName) = explode('.', $partSource);
      DBG::nicePrint([$sourceName, $rootTableName, $name], '[$lowerSource, $rootTableName, $name]');
      // $clsName = substr(basename($file), 0, -1 * strlen('StorageAcl.php'));
      try {
        Lib::loadClass('AntAclBase');
        $acl = AntAclBase::buildInstance(0, [
          'source' => $sourceName,
          'rootTableName' => $rootTableName,
          'name' => $name
        ]);
        // $acl = SchemaFactory::loadDefaultObject($clsName);
        // $namespace = $acl->getNamespace();
        // $name = $acl->getName();
        $idDatabase = DB::getPDO($sourceName)->getZasobId();
        $namespace = "{$sourceName}/{$rootTableName}/{$name}";
        DB::getPDO()->insertOrUpdate($this->_rootTableName, [
          'namespace' => $namespace,
          'idDatabase' => $idDatabase,
          '_type' => "AntAcl",
          'isActive' => 1
        ]);
      } catch (Exception $e) {
        UI::alert('danger', $e->getMessage());
      }
    }

    // // foreach ... DB::getPDO($idDatabase)->fetchAll(select real _rootTableName)
    // foreach (Core_AclHelper::getAclList() as $typeName) {
    //   $ns = Core_AclHelper::parseTypeName($typeName);
    //   $namespace = str_replace('__x3A__', '/', $ns['prefix']) . "/{$ns['name']}";
    //   $sqlNs = DB::getPDO()->quote($namespace, PDO::PARAM_STR);
    //   $idZasob = DB::getPDO()->fetchValue(" select ID from CRM_LISTA_ZASOBOW where `DESC` = {$sqlNs} and `TYPE` = 'TABELA' and A_STATUS in('WAITING', 'NORMAL') ");
    //   if (!$idZasob) {
    //     DBG::nicePrint($ns, "TODO: insert zasob PARENT_ID = ?");
    //   }
    // }
  }

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

      if ('default_objects/SystemSource' != $params['#refFrom']['namespace']) throw new Exception("Unsupported refFrom/namespace '{$params['#refFrom']['namespace']}'");
      $sqlWhere[] = "t.idDatabase = " . DB::getPDO()->quote($params['#refFrom']['primaryKey'], PDO::PARAM_INT);
    }
    {
      $filterParams = [];
      $xsdFields = $this->getXsdTypes();
      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:www_link' == $xsdFields[$fieldName]) {
          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 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 buildFeatureFromSqlRow($item) {
    $exNs = explode('/', $item['namespace']);
    $item['name'] = array_pop($exNs);
    $item['typeName'] = implode('__x3A__', $exNs) . ':' . $item['name'];
    $item['reinstallLink'] = Router::getRoute('Storage')->getLink('objectReinstall', [ 'namespace' => $item['namespace'] ]);
    return $item;
  }

}