se/SE/se-lib/Route/FixProjectPath.php

<?php

Lib::loadClass('RouteBase');

class Route_FixProjectPath extends RouteBase {

	public function defaultAction() {
		echo 'TODO: F.' . __FUNCTION__;
		//$sqlList['Check'] = "SHOW PROCEDURE STATUS where `Name`='update_project_path_idx_rec'";
	}

	public function checkPathsAction() {
		$sql = "
			select p.`ID`
				, p.`path` as proj_path
				, k.`path` as koresp_path
				, i.`idx_PATH` as idx_path
			from `IN7_MK_BAZA_DYSTRYBUCJI` p
				join `_project_path_idx` i on(i.`ID`=p.`ID`)
				left join `IN7_DZIENNIK_KORESP` k on(k.`ID_PROJECT`=p.`ID`)
			where (p.`path`!=i.`idx_PATH` or k.`path`!=i.`idx_PATH`)
		";
		$rows = array();
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$rows[] = $r;
		}
		echo $sql;
		echo'<pre>';print_r($rows);echo'</pre>';
		die('OK');
	}

	public function runAction() {
		$sql = "call `update_project_path_idx_rec`();";
		// TODO: update fields:
		//   `IN7_MK_BAZA_DYSTRYBUCJI`.`path`
		//   `IN7_DZIENNIK_KORESP`.`path`
		//   `PROBLEMS`.`ID_PROJECT_path`
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		$res = $db->query($sql);
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		die('OK');
	}

	public function reinstallAction() {
		// TODO: reinstall triggers for after insert/update `IN7_MK_BAZA_DYSTRYBUCJI`?
		// TODO: reinstall triggers for after insert/update `IN7_DZIENNIK_KORESP` and `PROBLEMS`?
		// coalesce((select p.`path` from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=4480 limit 1), 'Brak projektu')
		$sqlList = array();
		$sqlList['RemoveTable'] = "DROP TABLE IF EXISTS `_project_path_idx`";
		$sqlList['InstallTable'] = "
			CREATE TABLE IF NOT EXISTS `_project_path_idx` (
				`ID` int(11) NOT NULL
				, `P_ID` int(11) NOT NULL DEFAULT '0'
				, `idx_PATH` varchar(255) NOT NULL DEFAULT ''
				, KEY `ID` (`ID`)
				, KEY `P_ID` (`P_ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2
		";
		$sqlList['RemoveProcedure'] = "DROP PROCEDURE if exists `update_project_path_idx_rec`";
		$sqlList['CreateProcedure'] = "
			CREATE PROCEDURE `update_project_path_idx_rec`()
			BEGIN
				SET @conf_last_exec_key = 'tbl_indexer_project_last_exec';
				replace into `CRM_CONFIG` (`conf_key`, `conf_val`) values (@conf_last_exec_key, NOW());

				truncate table `_project_path_idx`;
				-- delete from `_project_path_idx`;

				insert into `_project_path_idx` (`ID`,`P_ID`)
					select p.`ID`, p.`P_ID`
					from `IN7_MK_BAZA_DYSTRYBUCJI` p
					where 1=1
				;

				update `_project_path_idx` as p set p.`idx_PATH`=concat('0-', p.`ID`) where p.`P_ID` is null or p.`P_ID`=0;
				update `_project_path_idx` as p set p.`idx_PATH`=concat('-1-', p.`ID`) where p.`P_ID`=-1;
				SET @i = 0;
				SET @loopLomit = 100;
				SET @pinitCnt = 1;
				WHILE @i < @loopLomit and @pinitCnt > 0 DO
					update `_project_path_idx` p join `_project_path_idx` pp on(pp.`ID`=p.`P_ID`)
						set p.`idx_PATH`=concat(pp.`idx_PATH`, '-', p.`ID`)
						where p.`idx_PATH`='' and pp.`idx_PATH`!='';
					SET @pinitCnt = ROW_COUNT();
					SET @i = @i + 1;
				END WHILE;
			END ;
		";
		$sqlList['RemoveTrigger_BeforeInsertProject'] = "DROP TRIGGER IF EXISTS `_IN7_MK_BAZA_DYSTRYBUCJI_tree_INSERT`";
		$sqlList['CreateTrigger_BeforeInsertProject'] = "
			CREATE TRIGGER `_IN7_MK_BAZA_DYSTRYBUCJI_tree_INSERT` BEFORE INSERT ON `IN7_MK_BAZA_DYSTRYBUCJI`
				FOR EACH ROW BEGIN
					-- SET NEW.path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.P_ID);
					SET NEW.path = (select CONCAT(
							(select IF (NEW.`P_ID`>0,
								coalesce(
									(select p.`path` from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=NEW.`P_ID` limit 1)
									, '?')
								, NEW.`P_ID`
							))
							, '-'
							, (select AUTO_INCREMENT from information_schema.TABLES where TABLE_SCHEMA=DATABASE() AND TABLE_NAME='IN7_MK_BAZA_DYSTRYBUCJI')
						));
				END
		";
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		foreach ($sqlList as $sqlName => $sql) {
			$res = $db->query($sql);
			if ($db->has_errors()) {
				throw new Exception("DB Errors at sql '{$sqlName}': " . implode("\n<br>", $db->get_errors()));
			}
		}
		die('OK');
	}

}

/**
 * Old triggers before 2015-06-10:

CREATE TRIGGER `_IN7_MK_BAZA_DYSTRYBUCJI_tree_INSERT` BEFORE INSERT ON `IN7_MK_BAZA_DYSTRYBUCJI`
	FOR EACH ROW BEGIN
		SET NEW.path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.P_ID);
	END

CREATE TRIGGER `_IN7_MK_BAZA_DYSTRYBUCJI_tree_UPDATE` BEFORE UPDATE ON `IN7_MK_BAZA_DYSTRYBUCJI`
	FOR EACH ROW BEGIN
		IF NEW.P_ID<>OLD.P_ID THEN
			SET NEW.path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.P_ID);
			update IN7_DZIENNIK_KORESP ik set ik.path=IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID) where path like concat('%',NEW.ID,'%');
			--	update PROBLEMS ik set ik.ID_PROJET_path=IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID) where ID_PROJET_path like concat('%',NEW.ID,'%');
		END IF;
	END


CREATE TRIGGER `_IN7_DZIENNIK_KORESP_tree_INSERT` BEFORE INSERT ON `IN7_DZIENNIK_KORESP`
	FOR EACH ROW BEGIN
		IF NEW.ID_PROJECT IS NOT NULL THEN
			SET NEW.path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID_PROJECT);
		END IF;
	END

CREATE TRIGGER `_IN7_DZIENNIK_KORESP_tree_UPDATE` BEFORE UPDATE ON `IN7_DZIENNIK_KORESP`
	FOR EACH ROW BEGIN
		IF NEW.ID_PROJECT is null THEN
			SET NEW.path = '';
		ELSEIF OLD.ID_PROJECT IS NULL or NEW.ID_PROJECT<>OLD.ID_PROJECT THEN
			SET NEW.path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID_PROJECT);
		END IF;
	END


CREATE FUNCTION `IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path`(`delimiter` TEXT, `node` INT) RETURNS text CHARSET latin2
	READS SQL DATA
	BEGIN
		DECLARE _path TEXT;
		DECLARE _type CHAR(255);
		DECLARE _lvl INT;
		DECLARE _cpath TEXT;
		DECLARE _id INT;
		DECLARE _id_cur INT;
		DECLARE EXIT HANDLER FOR NOT FOUND RETURN _path;
		SET _id = COALESCE(node, @id);
		SET _path = _id;
		SET _lvl=1;
		the_loop: LOOP
			SET _lvl =_lvl+1;
			IF _lvl>100 THEN
				RETURN concat('ERROR',_path);
				LEAVE the_loop;
			END IF;
			SELECT  P_ID,M_DIST_TYPE,ID
				INTO    _id,_type,_id_cur
					FROM    IN7_MK_BAZA_DYSTRYBUCJI
					WHERE   id = _id
						AND COALESCE(id <> @start_with, TRUE);
			SET _path = CONCAT(_id, delimiter, _path);
		END LOOP the_loop;
	END


CREATE EVENT `_IN7_MK_BAZA_DYSTRYBUCJI__IN7_DZIENNIK_KORESP_tree_event` ON SCHEDULE EVERY 1 DAY STARTS '2015-05-15 05:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
	update  IN7_MK_BAZA_DYSTRYBUCJI set path=IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-',ID);
	update  IN7_DZIENNIK_KORESP set path=IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-',ID_PROJECT);
END

 */