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

<?php

Lib::loadClass('RouteBase');

class Route_FixProjectPath extends RouteBase {

	public function defaultAction() {
		//$sqlList['Check'] = "SHOW PROCEDURE STATUS where `Name`='update_project_path_idx_rec'";
		SE_Layout::gora();
		//echo '<a href="/index.php?_route=FixProjectPath&_task=run">Zaktualizuj ścieżki projektów</a>';
		?>
<div class="jumbotron">
  <div class="container">
		<form class="form-inline" method="POST">
			<input type="hidden" name="_route" value="FixProjectPath" />
			<input type="hidden" name="_task" value="run" />
			<button type="submit" id="fldExecuteBtn" class="btn btn-primary" autocomplete="off">
				Zaktualizuj ścieżki projektów
			</button>
		</form>
	</div>
</div>
<script type="text/javascript">
jQuery(document).ready(function () {
  jQuery('#fldExecuteBtn').on('click', function () {
    jQuery(this).text(jQuery(this).text() + '...').attr('disabled', 'disabled');
		jQuery(this).parent().submit();
  })
});
</script>
		<?php
		SE_Layout::dol();
	}

	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`();";
		/* 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()));
		}

		SE_Layout::gora();
		SE_Layout::menu();
		?>
		<div class="container">
			<div class="alert alert-success">
				Zaktualizowano ścieżki projektów
			</div>
		</div>
		<?php
		SE_Layout::dol();
	}

	public function cleanAllPathsAction() {
		$sqlList = array();
		$sqlList['CleanPath_Projekty'] = "update `IN7_MK_BAZA_DYSTRYBUCJI` p set p.`path`='' ";
		$sqlList['CleanPath_Koresp'] = "update `IN7_DZIENNIK_KORESP` k set k.`path`='' ";
		$sqlList['CleanPath_Problems'] = "update `PROBLEMS` pr set pr.`path`='' ";
		$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');
	}

	public function reinstallAction() {
		$this->reinstall();
		die('OK');
	}

	public function reinstall() {
		$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;

				update `IN7_MK_BAZA_DYSTRYBUCJI` p join `_project_path_idx` i on(i.`ID`=p.`ID`)
					set p.`path`=i.`idx_PATH`;
				update `IN7_DZIENNIK_KORESP` k left join `_project_path_idx` i on(i.`ID`=k.`ID_PROJECT`)
					set k.`path`=coalesce(i.`idx_PATH`, '?');
				-- TODO: update `PROBLEMS` if exists
				update `PROBLEMS` pr left join `_project_path_idx` i on(i.`ID`=pr.`ID_PROJECT`)
					set pr.`ID_PROJECT_path`=coalesce(i.`idx_PATH`, '?');
			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
		";
		$sqlList['RemoveTrigger_BeforeUpdateProject'] = "DROP TRIGGER IF EXISTS `_IN7_MK_BAZA_DYSTRYBUCJI_tree_UPDATE`";
		// throws errors:
		//  #1146 - Table '{DATABASE_NAME}.ERROR: Loop detected ID=P_ID' doesn't exist
		//  #1146 - Table '{DATABASE_NAME}.ERROR: Parent item not exists' doesn't exist
		//  #1146 - Table '{DATABASE_NAME}.ERROR: Loop detected in path' doesn't exist
		$sqlList['CreateTrigger_BeforeUpdateProject'] = "
			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;
					IF NEW.`P_ID`!=OLD.`P_ID` THEN
						-- send error if loop
						-- OLD.path like concat('%-', NEW.P_ID, '-%')
						IF OLD.ID = NEW.P_ID THEN
							UPDATE `ERROR: Loop detected ID=P_ID` SET x=1;
							-- #1146 - Table 'biall.ERROR: Loop detected ID=P_ID' doesn't exist
						END IF;
						IF NEW.P_ID > 0 THEN
							-- check if project exists
							IF (select count(1) from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=NEW.P_ID) = 0 THEN
								UPDATE `ERROR: Parent item not exists` SET x=1;
								-- #1146 - Table 'biall.ERROR: Parent item not exists' doesn't exist
							END IF;
							-- check loop error
							-- state: 1276.path='0-868-1218-1276', 1218.path='0-868-1218'
							-- update P_ID=1276 where ID=1218 - should throw error
							IF (select IF(
									p.`path` like concat('%-',OLD.ID,'-%')
									or p.`path` like concat(OLD.ID,'-%')
									, 1
									, 0) from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=NEW.P_ID) > 0 THEN
								UPDATE `ERROR: Loop detected in path` SET x=1;
								-- #1146 - Table 'biall.ERROR: Loop detected in path' doesn't exist
							END IF;
						END IF;

						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`
								))
								, '-'
								, NEW.ID
							));

						-- DONT: throw warning to update all pathes in `IN7_MK_BAZA_DYSTRYBUCJI` with path under current?
						-- NOTE: throw error like that prevent update fields
						-- IF (select count(1) from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`P_ID`=NEW.ID) > 0 THEN
						-- 	UPDATE `WARNING: Update all paths` SET x=1;
						-- END IF;

						-- DONT: update `IN7_DZIENNIK_KORESP`.`path` - rows under NEW.ID has wrong path
						-- update `IN7_DZIENNIK_KORESP` k
						--	set k.`path`=(select p.`path` from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=k.`ID_PROJECT`)
						--	where k.`path` like concat(OLD.`path`, '-%');
						-- TODO: update `PROBLEMS`.`ID_PROJECT_path` if table exists
						-- update `PROBLEMS` k
						-- 	set k.`ID_PROJECT_path`=(select p.`path` from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=k.`ID_PROJECT`)
						-- 	where (k.`ID_PROJECT_path` like concat(OLD.`path`, '-%')
						-- 		or k.`ID_PROJECT_path`=OLD.`path`
						-- 	);
					END IF;
				END
		";
		$sqlList['RemoveTrigger_AfterUpdateProject'] = "DROP TRIGGER IF EXISTS `_IN7_MK_BAZA_DYSTRYBUCJI_tree_AFTER_UPDATE`";
		// throws errors:
		//  #1146 - Table '{DATABASE_NAME}.WARNING: Update all paths' doesn't exist
		$sqlList['CreateTrigger_AfterUpdateProject'] = "
			CREATE TRIGGER `_IN7_MK_BAZA_DYSTRYBUCJI_tree_AFTER_UPDATE` AFTER UPDATE ON `IN7_MK_BAZA_DYSTRYBUCJI`
				FOR EACH ROW BEGIN
					IF NEW.`P_ID`!=OLD.`P_ID` THEN
						-- throw warning to update all pathes in `IN7_MK_BAZA_DYSTRYBUCJI` with path under current?
						IF (select count(1) from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`P_ID`=NEW.ID) > 0 THEN
							UPDATE `WARNING: Update all paths` SET x=1;
						END IF;
					END IF;
				END
		";
		$sqlList['RemoveTrigger_BeforeInsertKoresp'] = "DROP TRIGGER IF EXISTS `_IN7_DZIENNIK_KORESP_tree_INSERT`";
		$sqlList['CreateTrigger_BeforeInsertKoresp'] = "
			CREATE TRIGGER `_IN7_DZIENNIK_KORESP_tree_INSERT` BEFORE INSERT ON `IN7_DZIENNIK_KORESP`
				FOR EACH ROW BEGIN
					IF NEW.ID_PROJECT IS NOT NULL and NEW.ID_PROJECT>0 THEN
						-- SET NEW.path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID_PROJECT);
						SET NEW.path = (select coalesce(
										(select p.`path` from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=NEW.`ID_PROJECT` limit 1)
										, '?'));
					END IF;
				END
		";
		$sqlList['RemoveTrigger_BeforeUpdateKoresp'] = "DROP TRIGGER IF EXISTS `_IN7_DZIENNIK_KORESP_tree_UPDATE`";
		$sqlList['CreateTrigger_BeforeUpdateKoresp'] = "
			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);
						IF NEW.ID_PROJECT>0 THEN
							SET NEW.path = (select coalesce(
											(select p.`path` from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=NEW.`ID_PROJECT` limit 1)
											, '?'));
						ELSE
							SET NEW.path = '';
						END IF;
					END IF;
				END
		";
		$sqlList['RemoveTrigger_BeforeInsertProblems'] = "DROP TRIGGER IF EXISTS `_PROBLEMS_tree_INSERT`";
		$sqlList['CreateTrigger_BeforeInsertProblems'] = "
			CREATE TRIGGER `_PROBLEMS_tree_INSERT` BEFORE INSERT ON `PROBLEMS`
				FOR EACH ROW BEGIN
					IF NEW.ID_PROJECT IS NOT NULL and NEW.ID_PROJECT>0 THEN
						-- SET NEW.ID_PROJECT_path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID_PROJECT);
						SET NEW.ID_PROJECT_path = (select coalesce(
										(select p.`path` from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=NEW.`ID_PROJECT` limit 1)
										, '?'));
					END IF;
				END
		";
		$sqlList['RemoveTrigger_BeforeUpdateProblems'] = "DROP TRIGGER IF EXISTS `_PROBLEMS_tree_UPDATE`";
		$sqlList['CreateTrigger_BeforeUpdateProblems'] = "
			CREATE TRIGGER `_PROBLEMS_tree_UPDATE` BEFORE UPDATE ON `PROBLEMS`
				FOR EACH ROW BEGIN
					IF NEW.ID_PROJECT IS NULL THEN
						SET NEW.ID_PROJECT_path = '';
					ELSEIF OLD.ID_PROJECT IS NULL or NEW.ID_PROJECT<>OLD.ID_PROJECT THEN
						-- SET NEW.ID_PROJECT_path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID_PROJECT);
						IF NEW.ID_PROJECT>0 THEN
							SET NEW.ID_PROJECT_path = (select coalesce(
											(select p.`path` from `IN7_MK_BAZA_DYSTRYBUCJI` p where p.`ID`=NEW.`ID_PROJECT` limit 1)
											, '?'));
						ELSE
							SET NEW.ID_PROJECT_path = '';
						END IF;
					END IF;
				END
		";
		$sqlList['RemoveEvent_everyDay'] = "DROP EVENT IF EXISTS `_IN7_MK_BAZA_DYSTRYBUCJI__IN7_DZIENNIK_KORESP_tree_event`";
		$sqlList['CreateEvent_everyDay'] = "
			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
				call `update_project_path_idx_rec`();
			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()));
			}
		}
	}

}

/**
 * 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 TRIGGER `_PROBLEMS_tree_INSERT` BEFORE INSERT ON `PROBLEMS`
	FOR EACH ROW BEGIN
		IF NEW.ID_PROJECT IS NOT NULL THEN
			SET NEW.ID_PROJECT_path = IN7_MK_BAZA_DYSTRYBUCJI_hierarchy_sys_connect_by_path('-', NEW.ID_PROJECT);
		END IF;
	END

CREATE TRIGGER `_PROBLEMS_tree_UPDATE` BEFORE UPDATE ON `PROBLEMS`
	FOR EACH ROW BEGIN
		IF NEW.ID_PROJECT is null THEN
			SET NEW.ID_PROJECT_path = '';
		ELSEIF OLD.ID_PROJECT IS NULL or NEW.ID_PROJECT<>OLD.ID_PROJECT THEN
			SET NEW.ID_PROJECT_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

 */