DELIMITER |
CREATE TRIGGER `Trg_Insert_ip_customers` AFTER INSERT ON `ip_customers` FOR EACH ROW BEGIN
DECLARE DN text DEFAULT '';
DECLARE cont VARCHAR(10) DEFAULT '';
IF NEW.is_node='Y' THEN
 SET cont='fromnode';
 ELSEIF NEW.is_node='N' THEN
  SET cont='fromhost';
END IF;
IF (SELECT 1 FROM astrad.sippeers WHERE name=NEW.remote_ip AND insecure='no' AND context='forbidden') IS NULL THEN
  INSERT IGNORE INTO astrad.sippeers (name,host,port,context,insecure) VALUES (NEW.remote_ip,NEW.remote_ip,5060,cont,'port');
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Add to sippeers ',NEW.remote_ip,' context=',cont));
ELSE
  UPDATE astrad.sippeers SET context=cont, insecure='port' WHERE name=NEW.remote_ip AND insecure='no' AND context='forbidden';
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Set context (',cont,') to ip_customer in sippeers ',NEW.remote_ip));
END IF;
SET DN=(SELECT get_deny());
UPDATE astrad.sipusers SET deny=DN;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Update deny in sipusers',NEW.remote_ip));
END |

--

CREATE TRIGGER `Trg_Update_ip_customers` AFTER UPDATE ON `ip_customers` FOR EACH ROW BEGIN
DECLARE DN text DEFAULT '';
DECLARE cont VARCHAR(10) DEFAULT '';
IF NEW.is_node='Y' THEN
 SET cont='fromnode';
 ELSEIF NEW.is_node='N' THEN
  SET cont='fromhost';
END IF;
UPDATE astrad.sippeers SET name=NEW.remote_ip, host=NEW.remote_ip, context=cont WHERE name=OLD.remote_ip;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','UPDATE',NOW(),CONCAT('update ip customer in sippeers remote_ip=',NEW.remote_ip,' old remote_ip=',OLD.remote_ip));
IF OLD.remote_ip <> NEW.remote_ip THEN
 SET DN=(SELECT get_deny());
 UPDATE astrad.sipusers SET deny=DN;
 INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','DELETE',NOW(),CONCAT('Update deny in sipusers from',OLD.remote_ip,' to ',NEW.remote_ip));
END IF;
END |

--

CREATE TRIGGER `Trg_Delete_ip_customers` AFTER DELETE ON `ip_customers` FOR EACH ROW BEGIN
DECLARE DN text DEFAULT '';
IF (SELECT 1 FROM vendors WHERE remote_ip=OLD.remote_ip) THEN
 UPDATE astrad.sippeers SET context='forbidden', insecure='no' WHERE name=OLD.remote_ip;
 INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','DELETE',NOW(),CONCAT('Update context to forbidden in sippeers ',OLD.remote_ip));
ELSE
 DELETE FROM astrad.sippeers WHERE name=OLD.remote_ip;
 INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','DELETE',NOW(),CONCAT('Delete from sippeers ',OLD.remote_ip));
END IF;
SET DN=(SELECT get_deny());
UPDATE astrad.sipusers SET deny=DN;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','DELETE',NOW(),CONCAT('Update deny in sipusers',OLD.remote_ip));
END |

--

CREATE TRIGGER `Trg_Insert_realm` AFTER INSERT ON `realm` FOR EACH ROW BEGIN
  CALL SET_REALM(NEW.val,'INSERT');
END |

--

CREATE TRIGGER `Trg_Update_realm` AFTER UPDATE ON `realm` FOR EACH ROW BEGIN
 IF NEW.id=(SELECT id FROM realm ORDER BY id DESC LIMIT 1) THEN
  IF NEW.val <> OLD.val THEN
   CALL SET_REALM(NEW.val,'UPDATE');
END IF;
 END IF;
END |

--

CREATE TRIGGER `Trg_Delete_realm` AFTER DELETE ON `realm` FOR EACH ROW BEGIN
DECLARE rm VARCHAR(30);
SET rm=(SELECT val FROM realm ORDER BY id DESC LIMIT 1);
 IF rm IS NOT NULL THEN
  IF rm <> OLD.val THEN
   CALL SET_REALM(rm,'DELETE');
END IF;
 END IF;
END |

--

CREATE TRIGGER `Trg_Insert_users` AFTER INSERT ON `users` FOR EACH ROW BEGIN
DECLARE DN text DEFAULT '';
SET DN=(SELECT get_deny());
INSERT IGNORE INTO astrad.sipusers (name,md5secret,deny) VALUES (NEW.username,NEW.md5secret,DN);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','INSERT',NOW(),CONCAT('Add to sipusers ',NEW.username));
INSERT IGNORE INTO astrad.sippeers (name,md5secret) VALUES (NEW.username,NEW.md5secret);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','INSERT',NOW(),CONCAT('Add to sippeers ',NEW.username));
END |

--

CREATE TRIGGER `Trg_Update_users` AFTER UPDATE ON `users` FOR EACH ROW BEGIN
UPDATE astrad.sipusers SET name=NEW.username, md5secret=NEW.md5secret WHERE name=OLD.username;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','UPDATE',NOW(),CONCAT('update sipusers new username=',NEW.username,' old username=',OLD.username));
UPDATE astrad.sippeers SET name=NEW.username, md5secret=NEW.md5secret WHERE name=OLD.username;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','UPDATE',NOW(),CONCAT('update sippeers user=',NEW.username,' old username=',OLD.username));
END |

--

CREATE TRIGGER `Trg_Delete_users` AFTER DELETE ON `users` FOR EACH ROW BEGIN
DELETE FROM astrad.sipusers WHERE name=OLD.username;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','DELETE',NOW(),CONCAT('Delete from sipusers ',OLD.username));
DELETE FROM astrad.sippeers WHERE name=OLD.username;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','DELETE',NOW(),CONCAT('Delete from sippeers ',OLD.username));
END |

--

CREATE TRIGGER `Trg_Insert_vendors` AFTER INSERT ON `vendors` FOR EACH ROW BEGIN
IF (SELECT 1 FROM astrad.sippeers WHERE name=NEW.remote_ip) IS NULL THEN
 INSERT IGNORE INTO astrad.sippeers (name,host,port) VALUES (NEW.remote_ip,NEW.remote_ip,5060);
 INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','INSERT',NOW(),CONCAT('Add vendor to sippeers ',NEW.remote_ip));
ELSE
 INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','INSERT',NOW(),CONCAT('Vendor already exist on sippeers (ip existe on ip_customers)',NEW.remote_ip));
END IF;
END |

--

CREATE TRIGGER `Trg_Update_vendors` AFTER UPDATE ON `vendors` FOR EACH ROW BEGIN
UPDATE astrad.sippeers SET name=NEW.remote_ip, host=NEW.remote_ip WHERE name=OLD.remote_ip;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','UPDATE',NOW(),CONCAT('update sippeers remote_ip=',NEW.remote_ip,' old remote_ip=',OLD.remote_ip));
END |

--

CREATE TRIGGER `Trg_Delete_vendors` AFTER DELETE ON `vendors` FOR EACH ROW BEGIN
IF (SELECT 1 FROM astrad.sippeers WHERE name=OLD.remote_ip AND insecure='no' AND context='forbidden') THEN
 DELETE FROM astrad.sippeers WHERE name=OLD.remote_ip AND insecure='no';
 INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','DELETE',NOW(),CONCAT('Delete vendor from sippeers ',OLD.remote_ip));
ELSE
 INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','DELETE',NOW(),CONCAT('Deletion vendor from sippeers ignored (ip existe on ip_customers) ',OLD.remote_ip));
END IF;
END |

--

CREATE FUNCTION `get_deny`() RETURNS text CHARSET latin1
  DETERMINISTIC
BEGIN
   DECLARE DN text DEFAULT '';
   DECLARE done INT DEFAULT 0;
   DECLARE IP VARCHAR(15);
   DECLARE cur CURSOR FOR SELECT DISTINCT remote_ip FROM ip_customers ORDER BY remote_ip;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   OPEN cur;
   read_loop: LOOP
    FETCH cur INTO IP;
    IF done THEN
     LEAVE read_loop;
    END IF;
    SET DN=CONCAT(DN,';',IP);
   END LOOP;
   CLOSE cur;
   RETURN LTRIM(SUBSTRING(DN,2,LENGTH(DN)-1));
END |

--

CREATE PROCEDURE `SET_REALM`(IN val varchar(80),IN event varchar(30))
BEGIN
 DECLARE account VARCHAR(30);
 DECLARE secret char(80);
 DECLARE md5_secret char(32);
 DECLARE no_more_rows BOOLEAN;
 DECLARE loop_cntr INT DEFAULT 0;
 DECLARE num_rows INT DEFAULT 0;
 DECLARE friends_cur CURSOR FOR SELECT acc.id, acc.h323_password FROM `porta-billing`.Accounts acc,`porta-billing`.Customers cus WHERE acc.i_customer=cus.i_customer AND cus.i_rep IN (3,5,9,12) AND acc.i_env=1 AND acc.id NOT regexp '^([0-9]{1,3}[.]){3}[0-9]{1,3}$';
 DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET no_more_rows = TRUE;
 OPEN friends_cur;
 SELECT FOUND_ROWS() INTO num_rows;
 the_loop: LOOP
  FETCH friends_cur INTO account, secret;
  IF no_more_rows THEN
    CLOSE friends_cur;
    LEAVE the_loop;
  END IF;
IF secret IS NOT NULL THEN
   UPDATE users SET md5secret=md5(CONCAT(account,':',val,':',secret)) WHERE username=account;
ELSE
 UPDATE users SET md5secret=md5(CONCAT(account,':',val,':')) WHERE username=account;
END IF;
END LOOP the_loop;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('realm',event,NOW(),CONCAT('update realm of all users :',num_rows));
END |
DELIMITER ;