se/SE/se-lib/SchemaVersionUpgrade.php

<?php

// CRM_CONFIG:
// - Schema_SystemObjectFieldStorageAcl__version	3
// - Schema_SystemObjectStorageAcl__version	2
// - RefConfig__version	2

// Instance - `CRM_INSTANCE_CONFIG`
// Ref - `CRM_REF_CONFIG`
// SystemObject - `CRM_#CACHE_ACL_OBJECT`
// SystemObjectField - `CRM_#CACHE_ACL_OBJECT_FIELD`, `CRM_#CACHE_ACL_OBJECT_FIELD_enum`

// TODO: remove `CRM_INSTANCE_CONFIG`.`idInstanceBase`
// TODO: DROP FUNCTION IF EXISTS `{$dbName}`.`isInstance_{$idInstance}` // for every instance

class SchemaVersionUpgrade {

	static $REF_DB_SCHEMA_VERSION = 2;
	static $SYSTEM_OBJECT_DB_SCHEMA_VERSION = 2;
	static $SYSTEM_OBJECT_FIELD_DB_SCHEMA_VERSION = 2;

	static function _getDBVersion() {
		return (int)DB::getPDO()->fetchValue(" select `CONF_VAL` from `CRM_CONFIG` where `CONF_KEY` = 'SchemaVersionUpgrade__version' ");
	}
	static function _updateDBVersion($version) {
		DB::getPDO()->execSql(" REPLACE INTO CRM_CONFIG (`CONF_KEY`,`CONF_VAL`) VALUES ('SchemaVersionUpgrade__version', '{$version}') ");
	}
	static function upgradeSchema() {
		static $_DB_SCHEMA_VERSION = null;
		if (!$_DB_SCHEMA_VERSION) DBG::log("DBG: SchemaVersionUpgrade: Brak VERSION w static");
		if (!$_DB_SCHEMA_VERSION) $_DB_SCHEMA_VERSION = V::get('_DB_SCHEMA_VERSION', 0, $_SESSION, 'int');
		if (!$_DB_SCHEMA_VERSION) DBG::log("DBG: SchemaVersionUpgrade: Brak VERSION w sesji");
		if (!$_DB_SCHEMA_VERSION) $_DB_SCHEMA_VERSION = self::_getDBVersion();
		if (!$_DB_SCHEMA_VERSION) DBG::log("DBG: SchemaVersionUpgrade: Brak VERSION w bazie danych");
		if (!$_DB_SCHEMA_VERSION) {
			self::_fixRefConfigDatabaseSchema();
			self::_fixSystemObjectDatabaseSchema();
			self::_fixSystemObjectFieldDatabaseSchema();
			self::_createInstanceConfigTable();
			$_DB_SCHEMA_VERSION = 4;
			$_SESSION['_DB_SCHEMA_VERSION'] = 4;
			self::_updateDBVersion(4);
		}
		if ($_DB_SCHEMA_VERSION < 5) {
			self::_upgradeFrom4To5();
			$_DB_SCHEMA_VERSION = 5;
			$_SESSION['_DB_SCHEMA_VERSION'] = 5;
			self::_updateDBVersion(5);
		}
		if ($_DB_SCHEMA_VERSION < 6) {
			self::_upgradeFrom5To6();
			$_DB_SCHEMA_VERSION = 6;
			$_SESSION['_DB_SCHEMA_VERSION'] = 6;
			self::_updateDBVersion(6);
		}
		if ($_DB_SCHEMA_VERSION < 7) { // set 'view' as default instance table
			self::_upgradeFrom6To7();
			$_DB_SCHEMA_VERSION = 7;
			$_SESSION['_DB_SCHEMA_VERSION'] = 7;
			self::_updateDBVersion(7);
		}
		// if ($_DB_SCHEMA_VERSION < 8) {
		// 	self::_upgradeFrom7To8();
		// 	$_DB_SCHEMA_VERSION = 8;
		// 	$_SESSION['_DB_SCHEMA_VERSION'] = 8;
		// 	self::_updateDBVersion(8);
		// }
	}

	static function _getRefConfigDBVersion() {
		return (int)DB::getPDO()->fetchValue(" select `CONF_VAL` from `CRM_CONFIG` where `CONF_KEY` = 'RefConfig__version' ");
	}
	static function _updateRefConfigDBVersion($version) {
		DB::getPDO()->execSql(" REPLACE INTO CRM_CONFIG (`CONF_KEY`,`CONF_VAL`) VALUES ('RefConfig__version', '{$version}') ");
	}
	static function _fixRefConfigDatabaseSchema() {
		static $_REF_DB_SCHEMA_VERSION;
		DBG::log("_fixRefConfigDatabaseSchema \$_REF_DB_SCHEMA_VERSION({$_REF_DB_SCHEMA_VERSION})");
		if (!$_REF_DB_SCHEMA_VERSION) {
			$_REF_DB_SCHEMA_VERSION = self::_getRefConfigDBVersion();
			if (2 === self::$REF_DB_SCHEMA_VERSION && $_REF_DB_SCHEMA_VERSION < self::$REF_DB_SCHEMA_VERSION) {
				DB::getPDO()->execSql("
					CREATE TABLE IF NOT EXISTS `CRM_REF_CONFIG` (
						`ID` INT NOT NULL AUTO_INCREMENT
						, `ROOT_OBJECT_NS` VARCHAR(255) NOT NULL
						, `CHILD_NAME` VARCHAR(255) NOT NULL
						, `CHILD_NS` VARCHAR(255) NOT NULL
						, `A_STATUS` enum('WAITING', 'NORMAL', 'DELETED') NOT NULL DEFAULT 'WAITING'
						, `VERSION` int(11) NOT NULL DEFAULT 0
						, `A_LAST_ACTION_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
						, `SOURCE` enum('table', 'view', 'backRef') NOT NULL DEFAULT 'table'
						, PRIMARY KEY (`ID`)
					) ENGINE = MyISAM DEFAULT CHARSET=latin2;
				");

				try { // upgrade from version 0.1
					DB::getPDO()->execSql(" ALTER TABLE `CRM_REF_CONFIG` ADD `SOURCE` enum('table', 'view', 'backRef') not null default 'table' ");
				} catch (Exception $e) {
					DBG::log($e);
				}
				try { // upgrade from version 0.2
					DB::getPDO()->execSql(" ALTER TABLE `CRM_REF_CONFIG` CHANGE `SOURCE` `SOURCE` enum('table', 'view', 'backRef') not null default 'table' ");
				} catch (Exception $e) {
					DBG::log($e);
				}

				DB::getPDO()->execSql("
					update CRM_REF_CONFIG
					set CHILD_NS = REPLACE(REPLACE(CHILD_NS, '__x3A__', '/'), ':', '/')
					where CHILD_NS = CHILD_NAME
				");
				self::_updateRefConfigDBVersion(2);
			}
		}
	}

	static function _getSystemObjectDBVersion() {
		return (int)DB::getPDO()->fetchValue(" select `CONF_VAL` from `CRM_CONFIG` where `CONF_KEY` = 'Schema_SystemObjectStorageAcl__version' ");
	}
	static function _updateSystemObjectDBVersion($version) {
		DB::getPDO()->execSql(" REPLACE INTO CRM_CONFIG (`CONF_KEY`,`CONF_VAL`) VALUES ('Schema_SystemObjectStorageAcl__version', '{$version}') ");
	}
	static function _fixSystemObjectDatabaseSchema() {
		$dbVersion = self::_getSystemObjectDBVersion();
		if (!$dbVersion || $dbVersion < 2) { // version is 1 - upgrade to 2
			DB::getPDO()->execSql("
				create table if not exists `CRM_#CACHE_ACL_OBJECT` (
					`idZasob` int(11) DEFAULT NULL,
					`idDatabase` int(11) NOT NULL,
					`namespace` varchar(255) DEFAULT '',
					`_rootTableName` varchar(255) DEFAULT '',
					`_type` varchar(255) DEFAULT '',
					`hasStruct` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'has structure',
					`isStructInstalled` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'has installed structure',
					`isObjectActive` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'object is active',
					`description` varchar(255) DEFAULT '',
					`primaryKey` varchar(255) DEFAULT '',
					`hasWriteGroupField` tinyint(1) NOT NULL DEFAULT 0,
					`hasReadGroupField` tinyint(1) NOT NULL DEFAULT 0,
					`hasOwnerField` tinyint(1) NOT NULL DEFAULT 0,
					UNIQUE KEY `idZasob` (idZasob),
					UNIQUE KEY `namespace` (namespace),
					KEY `isObjectActive` (isObjectActive)
				) ENGINE=MyISAM	DEFAULT CHARSET=latin2
			");
			try {
				DB::getPDO()->execSql(" ALTER TABLE `CRM_#CACHE_ACL_OBJECT` ADD `primaryKey` VARCHAR(255) NOT NULL DEFAULT '' AFTER `description` ");
			} catch (Exception $e) {
				DBG::log($e);
			}
			try {
				DB::getPDO()->execSql(" ALTER TABLE `CRM_#CACHE_ACL_OBJECT` ADD `hasWriteGroupField` tinyint(1) NOT NULL DEFAULT 0 ");
			} catch (Exception $e) {
				DBG::log($e);
			}
			try {
				DB::getPDO()->execSql(" ALTER TABLE `CRM_#CACHE_ACL_OBJECT` ADD `hasReadGroupField` tinyint(1) NOT NULL DEFAULT 0 ");
			} catch (Exception $e) {
				DBG::log($e);
			}
			try {
				DB::getPDO()->execSql(" ALTER TABLE `CRM_#CACHE_ACL_OBJECT` ADD `hasOwnerField` tinyint(1) NOT NULL DEFAULT 0 ");
			} catch (Exception $e) {
				DBG::log($e);
			}

			self::fixSystemObjectCoreTablesStructInstalled();

			$dbVersion = 2; self::_updateSystemObjectDBVersion($dbVersion);
		}

		// self::$SYSTEM_OBJECT_DB_SCHEMA_VERSION

		// if ($dbVersion < 3) {
		//	// sql ...
		// 	$dbVersion = 3; self::_updateSystemObjectDBVersion($dbVersion);
		// }
	}
	static function fixSystemObjectCoreTablesStructInstalled() { // Fix hasWriteGroupField, hasReadGroupField, hasOwnerField
		// TODO: mv to updateCache function - required after clear cache tables
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `tmp_cache_acl_fields` (
			  `table_name` varchar(64) NOT NULL DEFAULT '',
			  `column_name` varchar(64) NOT NULL DEFAULT '',
			  UNIQUE KEY `table_2` (`table_name`,`column_name`),
			  KEY `table` (`table_name`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2
		");
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `tmp_cache_acl_group_fields` (
			  `table_name` varchar(64) NOT NULL DEFAULT '',
			  `has_write` tinyint(1) NOT NULL DEFAULT 0,
			  `has_read` tinyint(1) NOT NULL DEFAULT 0,
			  `has_owner` tinyint(1) NOT NULL DEFAULT 0,
			  UNIQUE KEY `table_name` (`table_name`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2
		");
		DB::getPDO()->execSql(" TRUNCATE TABLE `tmp_cache_acl_fields` ");
		DB::getPDO()->execSql(" TRUNCATE TABLE `tmp_cache_acl_group_fields` ");
		DB::getPDO()->execSql("
			insert into tmp_cache_acl_group_fields(table_name, has_write, has_read, has_owner)
				select c.TABLE_NAME as table_name
					, IF(c.COLUMN_NAME = 'A_ADM_COMPANY', 1, 0) as has_write
					, IF(c.COLUMN_NAME = 'A_CLASSIFIED', 1, 0) as has_read
					, IF(c.COLUMN_NAME = 'L_APPOITMENT_USER', 1, 0) as has_owner
				from information_schema.COLUMNS c
					where c.TABLE_SCHEMA = 'SES_USERS2'
				  and c.COLUMN_NAME in ('A_ADM_COMPANY', 'A_CLASSIFIED', 'L_APPOITMENT_USER')
				ON DUPLICATE KEY UPDATE
					has_write = has_write + IF(c.COLUMN_NAME = 'A_ADM_COMPANY', 1, 0),
					has_read = has_read + IF(c.COLUMN_NAME = 'A_CLASSIFIED', 1, 0),
					has_owner = has_owner + IF(c.COLUMN_NAME = 'L_APPOITMENT_USER', 1, 0)
		");

		DB::getPDO()->execSql("
			update `CRM_#CACHE_ACL_OBJECT` c
				join `tmp_cache_acl_group_fields` t on (t.table_name = c._rootTableName)
			set
				c.hasWriteGroupField = t.has_write,
				c.hasReadGroupField = t.has_read,
				c.hasOwnerField = t.has_owner
		");
	}

	static function _getSystemObjectFieldDBVersion() {
		return (int)DB::getPDO()->fetchValue(" select `CONF_VAL` from `CRM_CONFIG` where `CONF_KEY` = 'Schema_SystemObjectFieldStorageAcl__version' ");
	}
	static function _updateSystemObjectFieldDBVersion($version) {
		DB::getPDO()->execSql(" REPLACE INTO CRM_CONFIG (`CONF_KEY`,`CONF_VAL`) VALUES ('Schema_SystemObjectFieldStorageAcl__version', '{$version}') ");
	}
	static function _fixSystemObjectFieldDatabaseSchema() {
		$version = self::_getSystemObjectFieldDBVersion();
		if ($version < 2) {
			DBG::log("UPDATE Schema_SystemObjectFieldStorageAcl__version");
			DB::getPDO()->execSql("
				create table if not exists `CRM_#CACHE_ACL_OBJECT_FIELD` (
					`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',
					`sortPrio` int(11) NOT NULL DEFAULT 0,
					UNIQUE KEY `idZasob` (idZasob),
					PRIMARY KEY (`namespace`),
					KEY `isActive` (isActive)
				) ENGINE=MyISAM DEFAULT CHARSET=latin2
			");
			try {
				DB::getPDO()->execSql(" ALTER TABLE `CRM_#CACHE_ACL_OBJECT_FIELD` ADD `appInfo` VARCHAR(1000) NOT NULL DEFAULT '' AFTER `xsdRestrictions` ");
			} catch (Exception $e) {
				DBG::log($e);
			}
			try {
				DB::getPDO()->execSql(" ALTER TABLE `CRM_#CACHE_ACL_OBJECT_FIELD` ADD `isLocal` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is in _rootTableName' AFTER `description` ");
			} catch (Exception $e) {
				DBG::log($e);
			}
			try {
				DB::getPDO()->execSql(" ALTER TABLE `CRM_#CACHE_ACL_OBJECT_FIELD` ADD `sortPrio` int(11) NOT NULL DEFAULT 0 AFTER `isLocal` ");
				DB::getPDO()->execSql("
					UPDATE `CRM_#CACHE_ACL_OBJECT_FIELD`
					SET sortPrio = idZasob
					WHERE idZasob > 0
				");
				DB::getPDO()->execSql("
					UPDATE `CRM_#CACHE_ACL_OBJECT_FIELD` c
						LEFT JOIN `CRM_LISTA_ZASOBOW` z on ( z.ID = c.idZasob )
					SET c.sortPrio = z.SORT_PRIO
					WHERE c.idZasob > 0
						and z.ID is not null
				");
			} catch (Exception $e) {
				DBG::log($e);
			}
			DB::getPDO()->execSql("
				create table if not exists `CRM_#CACHE_ACL_OBJECT_FIELD_enum` (
					`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 `CRM_#CACHE_ACL_OBJECT_FIELD_enum` where isActive = 0 ");
				DB::getPDO()->execSql(" ALTER TABLE `CRM_#CACHE_ACL_OBJECT_FIELD_enum` ADD UNIQUE `enum_uniq_value` (`objectNamespace`, `fieldNamespace`, `value`, `isActive`) ");
			} catch (Exception $e) {
				DBG::log($e);
			}
			try {
				DB::getPDO()->execSql(" ALTER TABLE `CRM_#CACHE_ACL_OBJECT_FIELD_enum` ADD `sortPrio` tinyint NOT NULL DEFAULT 0 ");
			} catch (Exception $e) {
				DBG::log($e);
			}
			self::_updateSystemObjectFieldDBVersion(self::$SYSTEM_OBJECT_FIELD_DB_SCHEMA_VERSION);
		}

		// if ($version < $SYSTEM_OBJECT_FIELD_DB_SCHEMA_VERSION) {
		//
		// }
	}

	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,
				`SOURCE` enum('table', 'view') not null default 'table',
				`VERSION` int(11) not null default 0,
				UNIQUE KEY `namespace` (`namespace`),
				KEY `rootNamespace` (`rootNamespace`),
				PRIMARY KEY (`id`)
			) ENGINE=MyISAM  DEFAULT CHARSET=latin2
		");
	}
	static function _upgradeFrom4To5() {
		try {
			DB::getPDO()->execSql(" ALTER TABLE `CRM_INSTANCE_CONFIG` ADD `SOURCE` enum('table', 'view') not null default 'table' ");
		} catch (Exception $e) {
			DBG::log($e);
		}
		try {
			DB::getPDO()->execSql(" ALTER TABLE `CRM_INSTANCE_CONFIG` ADD `VERSION` int(11) not null default 0 ");
		} catch (Exception $e) {
			DBG::log($e);
		}
	}

	static function _upgradeFrom5To6() {
		try {
			DB::getPDO()->execSql(" ALTER TABLE `CRM_INSTANCE_CONFIG` ADD `A_STATUS` enum('WAITING', 'NORMAL', 'DELETED') NOT NULL DEFAULT 'WAITING' ");
		} catch (Exception $e) {
			DBG::log($e);
		}
	}

	static function _upgradeFrom6To7() {
		try {
			DB::getPDO()->execSql(" ALTER TABLE `CRM_#CACHE_ACL_OBJECT` ADD `instanceTableSource` enum('table', 'view') not null default 'view' ");
		} catch (Exception $e) {
			DBG::log($e);
		}
	}

	// static function _upgradeFrom7To8() {
	// }

}