se/SE/se-lib/OBJ.php

<?php

/*
 * json files in schema/gui/core/
 *   default_db-{tbl_name}.json - config for raw table - no 'parent_object' defined
 *   {object_name}.json - config for object - require 'parent_object' (another object or default_db-* raw table object)

# Instance table:
  `{tbl_name}__INSTANCE_CLOSURE`: `PRIMARY_KEY`, `INSTANCE`(przodek), `PARENT_INSTANCE`(przodek), `PARENT_DEPTH`(głębokość)

TODO: closure tree http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html

## sql - fetch rows with instance:
```sql
  select t.*
       , (select inst.INSTANCE
          from {tbl_name}__INSTANCE_CLOSURE inst
          where inst.PRIMARY_KEY = t.{primary_key}
          order by inst.PARENT_DEPTH DESC limit 1) as _instance
  from {tbl_name} t
```

## sql - fetch all
```sql
  select t.*
  from {tbl_name} t
    right join {tbl_name}__INSTANCE_CLOSURE inst on(t.{primary_key} = inst.PRIMARY_KEY)
```

# Ref tables:
  `{tbl_name}__REF__{target_table_name}`: `PRIMARY_KEY`, `REMOTE_PRIMARY_KEY`

## sql - make ref connection
```sql
  insert into `{tbl_name}__REF__{target_table_name}` (`PRIMARY_KEY`, `REMOTE_PRIMARY_KEY`)
  -- TODO: save action to _HIST table
```

# Hist table:
  `{tbl_name}__ACTIVITY_LOG`:
  - `ID` int PRIMARY auto_increment - used in update to check conflicts
  - `PRIMARY_KEY` - same type as root table primary key
  - `ACTION_USER` varchar(20)
  - `ACTION_DATE` datetime
  - `ACTION_TYPE` enum('CREATE', 'UPDATE', 'DELETE')
  - `LOG` text - json with action details
  - `ERRORS` text - json with errors

 */
class OBJ {

  public static function getName($json) {
    if (empty($json['name'])) throw new Exception("Struct error - name not defined in " . json_encode($json));
    return (is_array($json['name']))? end($json['name']) : $json['name'];
  }

  public static function getLabel($json) {
    if (empty($json['label'])) return null;
    return (is_array($json['label']))? end($json['label']) : $json['label'];
  }

  // @returns array of parent object names
  public static function getParentList($json) {
    if (empty($json['parent_object'])) return array();
    return (!is_array($json['parent_object']))? array($json['parent_object']) : array_reverse($json['parent_object']);
  }

  public static function getFields($json) {
    if (empty($json['fields'])) throw new Exception("Struct error - object has no fields");
    return $json['fields'];
  }

  public static function getTableFields($json) {
    if (empty($json['fields'])) throw new Exception("Struct error - object has no fields");
    $fields = array();
    foreach ($json['fields'] as $fldName => $fldConf) {
      switch ($fldConf['type']) {
        case 'int': $fields[] = $fldName; break;
        case 'string': $fields[] = $fldName; break;
        // TODO: more field types
      }
    }
    return $fields;
  }

  public static function getStorageName($json) {
    if (empty($json['db_source'])) throw new Exception("Struct error - object has no source defined");
    return $json['db_source'];
  }

  public static function getMainTableName($json) {
    if (empty($json['db_table'])) throw new Exception("Struct error - object has no table name defined");
    return $json['db_table'];
  }

  public static function getPrimaryKeyType($json) {
    // [keys] => Array(
    //         [PRIMARY] => Array(
    //                 [fields] => ID
    //                 [auto_increment] => 1
    if (empty($json['keys'])) throw new Exception("Struct error - object has no keys defined");
    if (empty($json['keys']['PRIMARY'])) throw new Exception("Struct error - object has no PRIMARY key defined");
    if (empty($json['keys']['PRIMARY']['fields'])) throw new Exception("Struct error - object PRIMARY key has no fields defined");
    if (is_array($json['keys']['PRIMARY']['fields'])) throw new Exception("Not implemented - multiple fields primary key");
    $primaryKey = $json['keys']['PRIMARY']['fields'];
    if (empty($json['fields'][$primaryKey])) throw new Exception("Struct error - object priary key field not defined in fields list");
    $type = V::get('type', '', $json['fields'][$primaryKey]);
    if (empty($type)) throw new Exception("Struct error - object primary field type not defined");
    if ('int' != $type) throw new Exception("Not implemented - primary key field type is no int");
    return $type;
  }

  public static function getCoreObjectFromFile($objectName) {
		$objectFileName = str_replace("/", '-', $objectName);
		$filePath = APP_PATH_SCHEMA . "/gui/core/{$objectFileName}.json";
		if (!file_exists($filePath)) throw new Exception("File not exists {$filePath}", 404);

		$json = file_get_contents($filePath);
		DBG::_('DBG', '>1', "{$objectName} filePath", $filePath, __CLASS__, __FUNCTION__, __LINE__);
		DBG::_('DBG', '>1', "{$objectName} file content", $json, __CLASS__, __FUNCTION__, __LINE__);
		$json = @json_decode($json, $assoc = true);
		DBG::_('DBG', '>1', "{$objectName} json_last_error()", json_last_error(), __CLASS__, __FUNCTION__, __LINE__);
		if (null == $json && 0 !== json_last_error()) throw new Exception("Parse json error for object '{$objectName}': " . json_last_error());
		if (empty($json['parent_object'])) return $json;
		$jsonParent = array();
		$parent = (is_array($json['parent_object']))? end($json['parent_object']) : $json['parent_object'];
		$jsonParent = self::getCoreObjectFromFile($parent);
		return array_merge_recursive($jsonParent, $json);
	}

  public static function getCoreObjectList() {
    $objectList = array();
    $files = glob(APP_PATH_SCHEMA . "/gui/core/*.json", GLOB_NOSORT);
    //DBG::_(true, true, "files", $files, __CLASS__, __FUNCTION__, __LINE__);
    foreach ($files as $filePath) {
			$fileName = basename($filePath);
			$objName = substr($fileName, 0, -5);// remove ext '.json'
      $objectList[] = $objName;
    }
    return $objectList;
  }

  public static function checkInstall($json) {
    self::checkInstanceTable($json);
    self::checkRefTables($json);
    self::checkActivityLogTable($json);
  }

  public static function checkInstanceTable($json) {
    $mainTableName = self::getMainTableName($json);
    $instanceTableName = "{$mainTableName}__INSTANCE_CLOSURE";
    $primaryKeyType = "int(11)";// TODO: fetch from main table - default int(11)

    $storageName = self::getStorageName($json);
    $storagePdo = DB::getStorage($storageName);
    try {
      $mainTableStruct = $storagePdo->getTableStruct($mainTableName);
    } catch (Exception $e) {
      throw $e;
      // TODO: if (404 != $e->getCode()) -> CREATE BASE TABLE -- mv to TODO: checkMainTableInstall($json)
    }
    try {
      $instanceTableStruct = DB::getStorage()->getTableStruct($instanceTableName);
    } catch (Exception $e) {
      if (404 != $e->getCode()) throw $e;
      $instanceTableStruct = null;
    }

    $sqlCreate = "
      CREATE TABLE IF NOT EXISTS `{$instanceTableName}` (
        `PRIMARY_KEY` {$primaryKeyType} NOT NULL,
        `INSTANCE` varchar(124) NOT NULL,
        `PARENT_INSTANCE` varchar(124) DEFAULT NULL,
        `PARENT_DEPTH` int(11) NOT NULL,
        KEY `PRIMARY_KEY` (`PRIMARY_KEY`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin2
    ";
    DBG::_('DBG', '>2', "mainTableStruct", $mainTableStruct, __CLASS__, __FUNCTION__, __LINE__);
    DBG::_('DBG', '>2', "instanceTableStruct", $instanceTableStruct, __CLASS__, __FUNCTION__, __LINE__);
    DBG::_('DBG', '>2', "sqlCreate", $sqlCreate, __CLASS__, __FUNCTION__, __LINE__);

    if (!$instanceTableStruct) {
      DB::getPDO()->exec($sqlCreate);
    }
    try {
      $instanceTableStruct = DB::getStorage()->getTableStruct($instanceTableName);
    } catch (Exception $e) {
      throw new Exception("Wystąpiły błędy podczas tworzenie tabeli instancji dla {$mainTableName}");
    }
    DBG::_('DBG', '>2', "OK instance table exists - instanceTableStruct", $instanceTableStruct, __CLASS__, __FUNCTION__, __LINE__);
  }
  public static function checkRefTables($json) {
    // TODO: should go recurse or create on demand? - create on demand
    $objectName = self::getName($json);
    $mainTableName = self::getMainTableName($json);
    $objectPrimaryKeyType = self::getPrimaryKeyType($json);
    if (empty($json['fields'])) return;
    $toCreateRefTables = array();// ref table name => target primary key type
    foreach ($json['fields'] as $fldName => $fld) {
      if ('ref' != V::get('type', '', $fld)) continue;
      // [ref_LOCAL_EMAIL] => Array(
      //     [label] => Adresy email lokalnej poczty
      //     [type] => ref
      //     [ref_config] => Array(
      //             [name] => user__ref__local_email
      //             [target] => local_email
      $refConf = V::get('ref_config', '', $fld);
      if (!$refConf) throw new Exception("Struct error - ref config not defined for field {$objectName}/{$fldName}");
      $refTarget = V::get('target', '', $refConf);
      if (!$refConf) throw new Exception("Struct error - ref target not defined for field {$objectName}/{$fldName}");
      $jsonTarget = OBJ::getCoreObjectFromFile($refTarget);
      $targetMainTableName = self::getMainTableName($jsonTarget);
      $refTableName = "{$mainTableName}__REF__{$targetMainTableName}";
      $targetPrimaryKeyType = self::getPrimaryKeyType($jsonTarget);
      DBG::_('DBG', '>1', "create table `{$refTableName}` ( `PRIMARY_KEY` {$objectPrimaryKeyType}, `REMOTE_PRIMARY_KEY` {$targetPrimaryKeyType}) ", null, __CLASS__, __FUNCTION__, __LINE__);
      $toCreateRefTables[ $refTableName ] = $targetPrimaryKeyType;
    }
    foreach ($toCreateRefTables as $refTableName => $targetPrimaryKeyType) {
      // `{tbl_name}__REF__{target_table_name}`: `PRIMARY_KEY`, `REMOTE_PRIMARY_KEY`
      $sqlCreate = "
        CREATE TABLE IF NOT EXISTS `{$refTableName}` (
          `PRIMARY_KEY` {$objectPrimaryKeyType} NOT NULL,
          `REMOTE_PRIMARY_KEY` {$targetPrimaryKeyType} NOT NULL,
          KEY `PRIMARY_KEY` (`PRIMARY_KEY`),
          KEY `REMOTE_PRIMARY_KEY` (`REMOTE_PRIMARY_KEY`)
        ) ENGINE=MyISAM DEFAULT CHARSET=latin2
      ";
      DB::getPDO()->exec($sqlCreate);
      try {
        $refTableStruct = DB::getStorage()->getTableStruct($refTableName);
      } catch (Exception $e) {
        throw new Exception("Wystąpiły błędy podczas tworzenie tabeli ref {$refTableName}");
      }
    }
  }
  public static function checkActivityLogTable($json) {
    // `{tbl_name}__ACTIVITY_LOG`:
    // - `ID` int PRIMARY auto_increment - used in update to check conflicts
    // - `PRIMARY_KEY` - same type as root table primary key
    // - `ACTION_USER` varchar(20)
    // - `ACTION_DATE` datetime
    // - `ACTION_TYPE` enum('CREATE', 'UPDATE', 'DELETE')
    // - `LOG` text - json with action details
    // - `ERRORS` text - json with errors
    $mainTableName = self::getMainTableName($json);
    $histTableName = "{$mainTableName}__ACTIVITY_LOG";
    $primaryKeyType = self::getPrimaryKeyType($json);// default "int";

    $storageName = self::getStorageName($json);
    $storagePdo = DB::getStorage($storageName);
    $mainTableStruct = $storagePdo->getTableStruct($mainTableName);
    try {
      $histTableStruct = DB::getStorage()->getTableStruct($histTableName);
    } catch (Exception $e) {
      if (404 != $e->getCode()) throw $e;
      $histTableStruct = null;
    }

    $sqlCreate = "
      CREATE TABLE IF NOT EXISTS `{$histTableName}` (
        `ID` int(11) NOT NULL AUTO_INCREMENT,
        `PRIMARY_KEY` {$primaryKeyType} NOT NULL,
        `ACTION_USER` varchar(20) NOT NULL,
        `ACTION_DATE` datetime NOT NULL,
        `ACTION_TYPE` enum('CREATE','UPDATE','DELETE') NOT NULL,
        `LOG` text NOT NULL,
        `ERRORS` text NOT NULL,
        PRIMARY KEY (`ID`),
        KEY `PRIMARY_KEY` (`PRIMARY_KEY`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin2
    ";
    DBG::_('DBG', '>2', "mainTableStruct", $mainTableStruct, __CLASS__, __FUNCTION__, __LINE__);
    DBG::_('DBG', '>2', "instanceTableStruct", $histTableStruct, __CLASS__, __FUNCTION__, __LINE__);
    DBG::_('DBG', '>2', "sqlCreate", $sqlCreate, __CLASS__, __FUNCTION__, __LINE__);

    if (!$histTableStruct) {
      DB::getPDO()->exec($sqlCreate);
    }
    try {
      $histTableStruct = DB::getStorage()->getTableStruct($histTableName);
    } catch (Exception $e) {
      throw new Exception("Wystąpiły błędy podczas tworzenie tabeli hist dla {$mainTableName}");
    }
    DBG::_('DBG', '>2', "OK hist table exists - histTableStruct", $histTableStruct, __CLASS__, __FUNCTION__, __LINE__);
  }

  public static function parseAll() {
    DBG::activate();
    $mapInheritance = array();
    $objList = self::getCoreObjectList();
    foreach ($objList as $objName) {
      $json = self::getCoreObjectFromFile($objName);
      $parentList = OBJ::getParentList($json);
      DBG::_(true, true, "obj({$objName}) parentList:", $parentList, __CLASS__, __FUNCTION__, __LINE__);
      if (!empty($parentList)) {
        /*
          obj(stanowisko) parentList = Array(
              [0] => group
              [1] => zasob
              [2] => default_db/crm_lista_zasobow
        */
        $rootTable = array_pop($parentList);
        if (!array_key_exists($rootTable, $mapInheritance)) $mapInheritance[$rootTable] = array();
        array_unshift($parentList, $objName);
        foreach ($parentList as $parentName) {
          if (!in_array($parentName, $mapInheritance[$rootTable])) $mapInheritance[$rootTable][] = $parentName;
        }
      }
      DBG::_(true, true, "mapInheritance:", $mapInheritance, __CLASS__, __FUNCTION__, __LINE__);
    }
  }

}