se/SE/se-lib/OBJ.php

<?php

// TODO: use char '#' in table name for system tables (instances, hist, ref's, etc.)

/*
 * 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ść), , `_TS`

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`, `ACCEPTED`, `_TS`

## 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

# TODO: table names with '#':
  CREATE TABLE `CRM_FILES__#INSTANCE` - create files:
  	CRM_FILES__@0023INSTANCE.MYD
  	CRM_FILES__@0023INSTANCE.MYI
  	CRM_FILES__@0023INSTANCE.frm

# TODO: db API (only inserts => versioning, transactions):
## system table names:
{baseTableName}__#CURRENT <- current version
{baseTableName}__#LOG <- old versions
{baseTableName}__#FIELD__{fieldName} <- values for field {fieldName}
{baseTableName}__#INSTANCE_CLOSURE <- instance closure

### example structures for {baseTableName} = `test_user` (`ID`, `LOGIN`, `PASSWORD`, `NAME`)
- {baseTableName}__#FIELD__LOGIN    (ID BIGINT UNSIGNED auto_increment, PRIMARY_KEY, VALUE type same as in baseTable)
- {baseTableName}__#FIELD__PASSWORD (ID BIGINT UNSIGNED auto_increment, PRIMARY_KEY, VALUE type same as in baseTable)
- {baseTableName}__#FIELD__NAME     (ID BIGINT UNSIGNED auto_increment, PRIMARY_KEY, VALUE type same as in baseTable)
- {baseTableName}__#CURRENT:
  - `#VERSION`:     BIGINT UNSIGNED -- current version from {baseTableName}__#LOG
  - `#PRIMARY_KEY`: BIGINT UNSIGNED (same as in {baseTable})
  - `#INSTANCE`:    varchar(100) -- current instance name
  - `LOGIN`:        BIGINT UNSIGNED -- id from {baseTableName}__#FIELD__LOGIN
  - `PASSWORD`:     BIGINT UNSIGNED -- id from {baseTableName}__#FIELD__PASSWORD
  - `NAME`:         BIGINT UNSIGNED -- id from {baseTableName}__#FIELD__NAME
  - ??? - `#REF__EMAIL_ALIAS`: BIGINT UNSIGNED
- {baseTableName}__#LOG:
  - ...

## php API:
### php API - insert - `DB::getSchema('TEST_USER')->insert({LOGIN: 'test', PASSWORD: '1234...', NAME: 'Test user'});`:
insert into `TEST_USER` (`LOGIN`, `PASSWORD`, `NAME`) values ('test', '1234...', 'Test User');
# fetch last inserted id -> $pk - PRIMARY_KEY
insert into `TEST_USER__#FIELD__LOGIN`    (`PRIMARY_KEY`, `VALUE`) values ({$pk}, 'test');      // fetch last insert id -> $idFldLogin
insert into `TEST_USER__#FIELD__PASSWORD` (`PRIMARY_KEY`, `VALUE`) values ({$pk}, '1234...');   // fetch last insert id -> $idFldPassword
insert into `TEST_USER__#FIELD__NAME`     (`PRIMARY_KEY`, `VALUE`) values ({$pk}, 'Test User'); // fetch last insert id -> $idFldName
insert into `TEST_USER__#LOG` (`#PRIMARY_KEY`, `LOGIN`, `PASSWORD`, `NAME`) values ({$pk}, {$idFldLogin}, {$idFldPassword}, {$idFldName}); // fetch from last inserted id -> $version
insert into `TEST_USER__#CURRENT` (`#VERSION`, `#PRIMARY_KEY`, `LOGIN`, `PASSWORD`, `NAME`)
  select ID as `#VERSION`
      , `#PRIMARY_KEY`
      , `LOGIN`
      , `PASSWORD`
      , `NAME`
  from `TEST_USER__#LOG`
  where ID = {$version};

### php API - update - `DB::getSchema('TEST_USER')->update($primary_key = 1, {PASSWORD: '9876...', NAME: 'Test Test'});`:
PROCEDURE UPDATE ($pk, $lastVersion = NULL, $arg[LOGIN], $arg[PASSOWRD], $arg[NAME]):
-- IF $lastVersion == NULL THEN -- dont check if current version is equal
SET $changed = FALSE
select t.`LOGIN`, t.`PASSWORD`, t.`NAME`
    , (select `VALUE` from `TEST_USER__#FIELD__LOGIN`    where `ID`=t.`LOGIN`)    as `val_LOGIN`
    , (select `VALUE` from `TEST_USER__#FIELD__PASSWORD` where `ID`=t.`PASSWORD`) as `val_PASSWORD`
    , (select `VALUE` from `TEST_USER__#FIELD__NAME`     where `ID`=t.`NAME`)     as `val_NAME`
  from `TEST_USER__#CURRENT` t where t.`#PRIMARY_KEY` = {$pk}
  into ($idFld[LOGIN], $idFld[PASSWORD], $idFld[NAME], $val[LOGIN], $val[PASSWORD], $val[NAME]);
foreach fields as $fld:
  IF (select `VALUE` from `TEST_USER__#FIELD__{$fld}` where `PRIMARY_KEY`={$pk} order by ID DESC limit 1) != $arg[$fld] THEN
    insert into `TEST_USER__#FIELD__{$fld}` (`PRIMARY_KEY`, `VALUE`) values ({$pk}, $arg[$fld]);
    select LAST_INSERT_ID() into $idFld[$fld]
    SET $changed = TRUE
IF $changed :
  -- insert into `#GLOBAL_LOG` -- last inserted id -> $idGlobalLog
  insert into `TEST_USER__#LOG` (`#PRIMARY_KEY`, `LOGIN`, `PASSWORD`, `NAME`)
    values ($pk, $idFld[LOGIN], $idFld[PASSWORD], $idFld[NAME]);
  select LAST_INSERT_ID() into $version
  update `TEST_USER__#CURRENT`
    set `#VERSION`=$version
      , `$fld` = $idFld[$fld] ...
    where `#PRIMARY_KEY` = $pk
  -- or more save with join -> move to procedure switch_version ($pk, $version)
  update `TEST_USER__#CURRENT` c, `TEST_USER__#LOG` l
    set c.`#VERSION` = l.`ID`
      , c.`{$fld}` - l.`{$fld}` ...
    where c.`#PRIMARY_KEY` = {$pk} and l.`ID` = {$version} and l.`#PRIMARY_KEY` = {$pk}

# TODO: #REF
  # TODO: update #REF should update #VERSION
    # TODO: #REF sctruct hould have #VERIFIED fields for both sides of relation - eg. change instance of some obj may break #REF from another obj

# NOTE use UTC_TIMESTAMP() for #VERSION - better for sync @see http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/

```sql
CREATE TABLE `TEST_USER` (
  `ID` int(11) NOT NULL,
  `LOGIN` varchar(20) NOT NULL,
  `PASSWORD` varchar(32) NOT NULL,
  `NAME` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `TEST_USER__#CURRENT` (
  `#VERSION` int(10) UNSIGNED NOT NULL,
  `#PRIMARY_KEY` int(10) UNSIGNED NOT NULL,
  `#INSTANCE` varchar(100) NOT NULL DEFAULT '',
  `LOGIN` int(11) NOT NULL,
  `PASSWORD` int(11) NOT NULL,
  `NAME` int(11) NOT NULL,
  UNIQUE KEY `PRIMARY_KEY` (`#PRIMARY_KEY`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `TEST_USER__#FIELD__LOGIN` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PRIMARY_KEY` int(11) NOT NULL,
  `VALUE` varchar(20) NOT NULL,
  PRIMARY KEY (`ID`), KEY `PRIMARY_KEY` (`PRIMARY_KEY`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `TEST_USER__#FIELD__PASSWORD` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PRIMARY_KEY` int(11) NOT NULL,
  `VALUE` varchar(32) NOT NULL,
  PRIMARY KEY (`ID`), KEY `PRIMARY_KEY` (`PRIMARY_KEY`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `TEST_USER__#FIELD__NAME` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PRIMARY_KEY` int(11) NOT NULL,
  `VALUE` varchar(64) NOT NULL,
  PRIMARY KEY (`ID`), KEY `PRIMARY_KEY` (`PRIMARY_KEY`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `TEST_USER__#LOG` (
  `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `#GLOBAL_LOG_ID` bigint UNSIGNED DEFAULT NULL,
  `#PRIMARY_KEY` int(10) UNSIGNED NOT NULL,
  `#INSTANCE` varchar(100) NOT NULL DEFAULT '',
  `LOGIN` int(11) NOT NULL,
  `PASSWORD` int(11) NOT NULL,
  `NAME` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `PRIMARY_KEY` (`#PRIMARY_KEY`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
```

 */
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__);
    }
  }

}