Zaktualizuj ścieżki zasobów'; ?>

has_errors()) { throw new Exception("DB Errors: " . implode("\n
", $db->get_errors())); } $res = $db->query($sql); while ($r = $db->fetch($res)) { $rows[] = $r; } echo $sql; echo'

';print_r($rows);echo'

'; die('OK'); } public function runAction() { SE_Layout::gora(); SE_Layout::menu(); $this->_callProcedure(); ?>

Zaktualizowano ścieżki zasobów

_callProcedure(); die('Zaktualizowano ścieżki zasobów'); } private function _callProcedure() { $sql = "call `update_zasob_path_idx_rec`();"; /* update fields: * `CRM_LISTA_ZASOBOW`.`path` * `CRM_WSKAZNIK`.`path_CRM_LISTA_ZASOBOW` */ $db = DB::getDB(); if ($db->has_errors()) { throw new Exception("DB Errors: " . implode("\n
", $db->get_errors())); } $res = $db->query($sql); if ($db->has_errors()) { throw new Exception("DB Errors: " . implode("\n
", $db->get_errors())); } } public function cleanAllPathsAction() { $sqlList = array(); $sqlList['CleanPath_Zasob'] = "update `CRM_LISTA_ZASOBOW` p set p.`path`='' "; $sqlList['CleanPath_Wskaznik'] = "update `CRM_WSKAZNIK` k set k.`path_CRM_LISTA_ZASOBOW`='' "; $db = DB::getDB(); if ($db->has_errors()) { throw new Exception("DB Errors: " . implode("\n
", $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
", $db->get_errors())); } } die('OK'); } public function reinstallAction() { $this->reinstall(); die('OK'); } public function reinstall() { $sqlList = array(); $sqlList['RemoveTable'] = "DROP TABLE IF EXISTS `_zasob_path_idx`"; $sqlList['InstallTable'] = " CREATE TABLE IF NOT EXISTS `_zasob_path_idx` ( `ID` int(11) NOT NULL , `P_ID` int(11) NOT NULL DEFAULT '0' , `DESC` varchar(255) NOT NULL DEFAULT '0' , `idx_PATH` varchar(255) NOT NULL DEFAULT '' , `idx_PATH_DESC` 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_zasob_path_idx_rec`"; $sqlList['CreateProcedure'] = " CREATE PROCEDURE `update_zasob_path_idx_rec`() BEGIN SET @conf_last_exec_key = 'tbl_indexer_zasob_last_exec'; replace into `CRM_CONFIG` (`conf_key`, `conf_val`) values (@conf_last_exec_key, NOW()); truncate table `_zasob_path_idx`; -- delete from `_zasob_path_idx`; insert into `_zasob_path_idx` (`ID`,`P_ID`,`DESC`) select z.`ID`, coalesce(z.`PARENT_ID`, 0), z.`DESC` from `CRM_LISTA_ZASOBOW` z where 1=1 ; update `_zasob_path_idx` as p set p.`idx_PATH`=concat('0-', p.`ID`), p.`idx_PATH_DESC`=concat('', p.`DESC`) where p.`P_ID`=0; update `_zasob_path_idx` as p set p.`idx_PATH`=concat('-1-', p.`ID`), p.`idx_PATH_DESC`=concat('-1/', p.`DESC`) where p.`P_ID`<0; SET @i = 0; SET @loopLomit = 100; SET @pinitCnt = 1; WHILE @i < @loopLomit and @pinitCnt > 0 DO update `_zasob_path_idx` p join `_zasob_path_idx` pp on(pp.`ID`=p.`P_ID`) set p.`idx_PATH`=concat(pp.`idx_PATH`, '-', p.`ID`) , p.`idx_PATH_DESC`=concat(pp.`idx_PATH_DESC`, '/', p.`DESC`) where p.`idx_PATH`='' and pp.`idx_PATH`!=''; SET @pinitCnt = ROW_COUNT(); SET @i = @i + 1; END WHILE; update `CRM_LISTA_ZASOBOW` z join `_zasob_path_idx` i on(i.`ID`=z.`ID`) set z.`path`=i.`idx_PATH_DESC`; update `CRM_WSKAZNIK` wsk left join `_zasob_path_idx` i on(i.`ID`=wsk.`ID_ZASOB`) set wsk.`path_CRM_LISTA_ZASOBOW`=coalesce(i.`idx_PATH_DESC`, '?'); CALL CRM_UI_MSGS__markTableEveryoneAsExecuted('update_zasob_path_idx_rec','FixZasobPath','Update all paths','CRM_LISTA_ZASOBOW'); END "; $sqlList['RemoveTrigger_BeforeInsertZasob'] = "DROP TRIGGER IF EXISTS `_CRM_LISTA_ZASOBOW_tree_INSERT`"; $sqlList['CreateTrigger_BeforeInsertZasob'] = " CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_LISTA_ZASOBOW_tree_INSERT` BEFORE INSERT ON `CRM_LISTA_ZASOBOW` FOR EACH ROW BEGIN -- SET NEW.path = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.PARENT_ID); SET NEW.path = (select CONCAT( (select IF (NEW.`PARENT_ID`>0 , coalesce( (select p.`path` from `CRM_LISTA_ZASOBOW` p where p.`ID`=NEW.`PARENT_ID` limit 1) , '?') , '' )) , '/' , NEW.`DESC` )); END "; $sqlList['RemoveTrigger_AfterInsertZasob'] = "DROP TRIGGER IF EXISTS `_CRM_LISTA_ZASOBOW_tree_AFTER_INSERT`"; $sqlList['CreateTrigger_AfterInsertZasob'] = " CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_LISTA_ZASOBOW_tree_AFTER_INSERT` AFTER INSERT ON `CRM_LISTA_ZASOBOW` FOR EACH ROW BEGIN insert into `_zasob_path_idx` (`ID`, `P_ID`, `DESC`, `idx_PATH`, `idx_PATH_DESC`) values( NEW.`ID` , NEW.`PARENT_ID` , NEW.`DESC` , (select CONCAT( (select IF (NEW.`PARENT_ID`>0, coalesce( (select p.`idx_PATH` from `_zasob_path_idx` p where p.`ID`=NEW.`PARENT_ID` limit 1) , '?') , NEW.`PARENT_ID` )) , '-' , NEW.ID )) , (select CONCAT( (select IF (NEW.`PARENT_ID`>0 , coalesce( (select p.`idx_PATH_DESC` from `_zasob_path_idx` p where p.`ID`=NEW.`PARENT_ID` limit 1) , '?') , '' )) , '/' , NEW.`DESC` )) ); END "; $sqlList['RemoveTrigger_BeforeUpdateZasob'] = "DROP TRIGGER IF EXISTS `_CRM_LISTA_ZASOBOW_tree_UPDATE`"; // throws errors: // #1146 - Table '{DATABASE_NAME}.P5-MSG:Route_FixZasobPath:ERROR: Loop detected ID=PARENT_ID' doesn't exist // #1146 - Table '{DATABASE_NAME}.P5-MSG:Route_FixZasobPath:ERROR: Parent item not exists' doesn't exist // #1146 - Table '{DATABASE_NAME}.P5-MSG:Route_FixZasobPath:ERROR: Loop detected in path' doesn't exist $sqlList['CreateTrigger_BeforeUpdateZasob'] = " CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_LISTA_ZASOBOW_tree_UPDATE` BEFORE UPDATE ON `CRM_LISTA_ZASOBOW` FOR EACH ROW BEGIN -- IF NEW.PARENT_ID<>OLD.PARENT_ID or NEW.`DESC`<>OLD.`DESC` THEN -- SET NEW.path = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.PARENT_ID); -- update CRM_WSKAZNIK ik set ik.path_CRM_LISTA_ZASOBOW=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID) where path_CRM_LISTA_ZASOBOW like concat('%',NEW.ID,'%'); -- -- update CRM_LISTA_ZASOBOW ik set ik.path=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', ik.ID) where path like concat('%',OLD.`DESC`,'%') and ik.ID<>OLD.ID; IF NEW.`PARENT_ID`!=OLD.`PARENT_ID` or NEW.`DESC`!=OLD.`DESC` THEN IF OLD.`ID` = NEW.`PARENT_ID` THEN UPDATE `P5-MSG:Route_FixZasobPath:ERROR: Loop detected ID=P_ID` SET x=1; -- #1146 - Table 'biall.ERROR: Loop detected ID=PARENT_ID' doesn't exist END IF; IF NEW.`PARENT_ID` > 0 THEN -- check if zasob exists IF (select count(1) from `CRM_LISTA_ZASOBOW` p where p.`ID`=NEW.`PARENT_ID`) = 0 THEN UPDATE `P5-MSG:Route_FixZasobPath: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.`idx_PATH` like concat('%-',OLD.ID,'-%') or p.`idx_PATH` like concat(OLD.ID,'-%') , 1 , 0) from `_zasob_path_idx` p where p.`ID`=NEW.`PARENT_ID`) > 0 THEN UPDATE `P5-MSG:Route_FixZasobPath: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.`PARENT_ID`>0 , coalesce( (select p.`path` from `CRM_LISTA_ZASOBOW` p where p.`ID`=NEW.`PARENT_ID` limit 1) , '?') , IF (NEW.`PARENT_ID`<0 , '-1' , '' ) )) , '/' , NEW.`DESC` )); -- DONT: throw warning to update all pathes in `CRM_LISTA_ZASOBOW` with path under current? -- NOTE: throw error like that prevent update fields -- IF (select count(1) from `CRM_LISTA_ZASOBOW` p where p.`P_ID`=NEW.ID) > 0 THEN -- UPDATE `P5-MSG:Route_FixZasobPath:WARNING: Update all paths` SET x=1; -- END IF; -- DONT: update `CRM_WSKAZNIK`.`path_CRM_LISTA_ZASOBOW` - rows under NEW.ID has wrong path END IF; END "; $sqlList['RemoveTrigger_AfterUpdateZasob'] = "DROP TRIGGER IF EXISTS `_CRM_LISTA_ZASOBOW_tree_AFTER_UPDATE`"; // throws errors: // #1146 - Table '{DATABASE_NAME}.WARNING: Update all paths' doesn't exist $sqlList['CreateTrigger_AfterUpdateZasob'] = " CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_LISTA_ZASOBOW_tree_AFTER_UPDATE` AFTER UPDATE ON `CRM_LISTA_ZASOBOW` FOR EACH ROW BEGIN IF NEW.`PARENT_ID`!=OLD.`PARENT_ID` or NEW.`DESC`!=OLD.`DESC` THEN -- throw warning to update all pathes in `CRM_LISTA_ZASOBOW` with path under current? IF (select count(1) from `CRM_LISTA_ZASOBOW` p where p.`PARENT_ID`=NEW.ID) > 0 THEN CALL CRM_UI_MSGS__addTableEveryoneUniqueMsg(NEW.A_RECORD_UPDATE_AUTHOR, 'FixZasobPath', 'danger', 'Update all paths', 'CRM_LISTA_ZASOBOW'); UPDATE `P5-MSG:Route_FixZasobPath:WARNING: Update all paths` SET x=1; END IF; END IF; END "; $sqlList['RemoveTrigger_BeforeInsertWskaznik'] = "DROP TRIGGER IF EXISTS `_CRM_WSKAZNIK_tree_INSERT`"; $sqlList['CreateTrigger_BeforeInsertWskaznik'] = " CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_WSKAZNIK_tree_INSERT` BEFORE INSERT ON `CRM_WSKAZNIK` FOR EACH ROW BEGIN IF NEW.ID_ZASOB IS NOT NULL and NEW.ID_ZASOB>0 THEN -- SET NEW.path_CRM_LISTA_ZASOBOW = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID_ZASOB); SET NEW.`path_CRM_LISTA_ZASOBOW` = (select coalesce( (select p.`path` from `CRM_LISTA_ZASOBOW` p where p.`ID`=NEW.`ID_ZASOB` limit 1) , '?')); END IF; END "; $sqlList['RemoveTrigger_BeforeUpdateWskaznik'] = "DROP TRIGGER IF EXISTS `_CRM_WSKAZNIK_tree_UPDATE`"; $sqlList['CreateTrigger_BeforeUpdateWskaznik'] = " CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_WSKAZNIK_tree_UPDATE` BEFORE UPDATE ON `CRM_WSKAZNIK` FOR EACH ROW BEGIN IF NEW.ID_ZASOB IS NULL THEN SET NEW.path_CRM_LISTA_ZASOBOW = ''; ELSEIF OLD.ID_ZASOB IS NULL or NEW.ID_ZASOB<>OLD.ID_ZASOB THEN -- SET NEW.path_CRM_LISTA_ZASOBOW = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID_ZASOB); IF NEW.ID_ZASOB>0 THEN SET NEW.`path_CRM_LISTA_ZASOBOW` = (select coalesce( (select p.`path` from `CRM_LISTA_ZASOBOW` p where p.`ID`=NEW.`ID_ZASOB` limit 1) , '?')); ELSE SET NEW.`path_CRM_LISTA_ZASOBOW` = ''; END IF; END IF; END "; $sqlList['RemoveEvent_everyDay'] = "DROP EVENT IF EXISTS `_CRM_LISTA_ZASOBOW__CRM_WSKAZNIK_tree_event`"; $sqlList['CreateEvent_everyDay'] = " CREATE EVENT `_CRM_LISTA_ZASOBOW__CRM_WSKAZNIK_tree_event` ON SCHEDULE EVERY 1 DAY STARTS '2015-05-15 05:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN call `update_zasob_path_idx_rec`(); END "; $db = DB::getDB(); if ($db->has_errors()) { throw new Exception("DB Errors: " . implode("\n
", $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
", $db->get_errors())); } } } /* Table 'DB.P5-MSG:Route_FixZasobPath:WARNING: Update all paths' doesn't exist Table 'DB.P5-MSG:Route_FixZasobPath:ERROR: Loop detected ID=P_ID' doesn't exist Table 'DB.P5-MSG:Route_FixZasobPath:ERROR: Parent item not exists' doesn't exist Table 'DB.P5-MSG:Route_FixZasobPath:ERROR: Loop detected in path' doesn't exist */ public function parseMessageFromStorage($msg) { switch ($msg) { case 'WARNING: Update all paths': { $msg = "Zaktualizuj ścieżki zasobów!"; break; } case 'ERROR: Loop detected ID=P_ID': { $msg = "Nr rekordu nadrzędnego musi różnić się od nr rekordu"; break; } case 'ERROR: Parent item not exists': { $msg = "Nie istnieje rekord o numerze podanym jako nr nadrzędny"; break; } case 'ERROR: Loop detected in path': { $msg = "Nieprawidłowy nr nadrzędny"; break; } } return $msg; } public function parseMessageFromMsgsSystem($msg) { switch ($msg) { case 'Update all paths': { $msg = "Zaktualizuj ścieżki zasobów"; break; } } return $msg; } public function runByMessageFromMsgsSystem($msg, &$execNotes) { switch ($msg) { case 'Update all paths': { $execNotes .= 'call procedure... '; $this->_callProcedure(); $execNotes .= ' done'; break; } } } } /** * Old triggers before 2015-06-16: CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_LISTA_ZASOBOW_tree_INSERT` BEFORE INSERT ON `CRM_LISTA_ZASOBOW` FOR EACH ROW BEGIN SET NEW.path = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.PARENT_ID); END CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_LISTA_ZASOBOW_tree_UPDATE` BEFORE UPDATE ON `CRM_LISTA_ZASOBOW` FOR EACH ROW BEGIN IF NEW.PARENT_ID<>OLD.PARENT_ID or NEW.`DESC`<>OLD.`DESC` THEN SET NEW.path = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.PARENT_ID); update CRM_WSKAZNIK ik set ik.path_CRM_LISTA_ZASOBOW=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID) where path_CRM_LISTA_ZASOBOW like concat('%',NEW.ID,'%'); -- update CRM_LISTA_ZASOBOW ik set ik.path=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', ik.ID) where path like concat('%',OLD.`DESC`,'%') and ik.ID<>OLD.ID; END IF; END CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_WSKAZNIK_tree_INSERT` BEFORE INSERT ON `CRM_WSKAZNIK` FOR EACH ROW BEGIN SET NEW.path_CRM_LISTA_ZASOBOW = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID_ZASOB); END CREATE DEFINER=`root`@`localhost` TRIGGER `_CRM_WSKAZNIK_tree_UPDATE` BEFORE UPDATE ON `CRM_WSKAZNIK` FOR EACH ROW BEGIN IF NEW.ID_ZASOB<>OLD.ID_ZASOB THEN SET NEW.path_CRM_LISTA_ZASOBOW = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID_ZASOB); END IF; END CREATE EVENT `_CRM_LISTA_ZASOBOW__CRM_WSKAZNIK_tree_event` ON SCHEDULE EVERY 1 DAY STARTS '2015-05-15 05:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN update CRM_LISTA_ZASOBOW set path=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/',ID); update CRM_WSKAZNIK set path_CRM_LISTA_ZASOBOW=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/',ID_ZASOB); END CREATE DEFINER=`root`@`localhost` FUNCTION `CRM_LISTA_ZASOBOW_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 = ''; SET _lvl=1; the_loop: LOOP SET _lvl =_lvl+1; IF _lvl>100 THEN RETURN concat('ERROR',_path); LEAVE the_loop; END IF; SELECT PARENT_ID,`DESC`,ID INTO _id,_type,_id_cur FROM CRM_LISTA_ZASOBOW WHERE id = _id AND COALESCE(id <> @start_with, TRUE); SET _path = CONCAT(_type, delimiter, _path); END LOOP the_loop; END */