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() {
$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
", $db->get_errors()));
}
$res = $db->query($sql);
if ($db->has_errors()) {
throw new Exception("DB Errors: " . implode("\n
", $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
", $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');
}
}
/**
* 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
*/