Préparation de la base de données (MySQL) pour Asterisk

Oussama Hammami, 2011-03-16

Switzernet

 

 

Ce document explique les triggers et les procédures utilisées dans db3 (serveur MySQL) dont l’objectif est de préparer une base de données pour les serveurs Asterisk à partir de notre structure actuelle implémenté par Portaone.

Ces triggers assurent la conversion vers une structure conforme à l’utilisation du module Realtime d’Asterisk

Structure

 

 

Pour plus des détailles sur la structure de la base de données Porta-billing [http://switzernet.com/3/public/110314-astrad-triggers/]

Ci-dessous les dumps des ces bases de données (uniquement la structure):

Routines

Ci-dessous les dumps des routines des bases de données (porta-billing, ast-replication):

 

La base de données ‘astrad‘ ne contient aucun trigger.

porta-billing

Routine

Description

Delimiter |

 

 

CREATE PROCEDURE INSERT_USER (IN account varchar(30), IN secret VARCHAR(80), IN todo VARCHAR(30), IN us VARCHAR(30))

BEGIN

DECLARE rm VARCHAR(80);

DECLARE md varchar(32);

SET rm = (SELECT val FROM `ast-replication`.realm ORDER BY id DESC LIMIT 1);

SET md = (SELECT md5(CONCAT(account,':',rm,':',secret)));

IF secret IS NULL THEN

  SET md = (SELECT md5(CONCAT(account,':',rm,':')));

END IF;

IF todo LIKE "INSERT" THEN

  INSERT IGNORE INTO `ast-replication`.users (username,md5secret) values (account,md);

ELSEIF todo LIKE "UPDATE" THEN

  UPDATE `ast-replication`.users SET md5secret=md, username=account WHERE username=us;

END IF;

END|

 

Procedure INSERT_USER (IN account varchar(30), IN secret VARCHAR(80), IN todo VARCHAR(30), IN us VARCHAR(30))

Liste de parameters:

account la nouvelle valeur de compte SIP.

secret mot de passe du compte SIP.

todo l’action à traiter (INSERT ou UPDATE)

us l’ancienne valeur du compte SIP.

Valeurs de retour:

aucune

Exemple:

CALL INSERT_USER(‘41215500329’,’PASSWORD’,’INSERT’,’’);

Insertion d’un nouveau compte dans la table users.

 

CALL INSERT_USER(‘41215500327’,’PASSWORD2’,UPDATE,’41215500329’) ;

Modifier le mot de passe et le username du compte 41215500329

 

 

CREATE PROCEDURE UPDATE_CUSTOMER (IN irep INT(10) unsigned, IN icustomer INT(10) unsigned)

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE account VARCHAR(30);

DECLARE ienv    VARCHAR(30);

DECLARE pwd     VARCHAR(30);

DECLARE cur CURSOR FOR SELECT id,i_env,h323_password FROM Accounts where i_customer=icustomer;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;

read_loop: LOOP

FETCH cur INTO account,ienv,pwd ;

IF done THEN

  LEAVE read_loop;

END IF;

IF irep NOT IN (3,5,9,12) THEN

  IF ienv=1 THEN

    IF account REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN

      IF (SELECT 1 FROM `ast-replication`.ip_customers  WHERE remote_ip = account AND is_node='N') THEN

        DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = account AND is_node='N';

        INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Delete from ip_customers ',account));

      END IF;

    ELSE

      IF (SELECT 1 FROM `ast-replication`.users  WHERE username = account) THEN

        DELETE FROM `ast-replication`.users WHERE username = account;

        INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Delete from users ',account));

      END IF; 

    END IF;

END IF;

ELSE

  IF ienv=1 THEN 

  IF account REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN

      INSERT IGNORE INTO `ast-replication`.ip_customers (remote_ip,is_node) values (account,'N');

      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Add to ip_customers ',account));

  ELSE

    CALL INSERT_USER(account,pwd,'INSERT','');

      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Add to users ',account));     

  END IF;

END IF;

END IF;

END LOOP;

CLOSE cur;

END|

 

PROCEDURE UPDATE_CUSTOMER (IN irep INT(10) unsigned, IN icustomer INT(10) unsigned)

Liste de parameters:

i_rep la nouvelle valeur du représentative du client.

Icustomer la clé du client dans la table Customers.

Valeurs de retour:

aucune

Exemple:

CALL UPDATE_CUSTOMER(10,16165);

Supprimer tous les comptes SIP du client dont la clé est 16165.

 

CALL UPDATE_CUSTOMER(10,16165);

Activer les comptes SIP du client 16165.

 

CREATE TRIGGER Trg_Insert_Accounts AFTER INSERT ON Accounts

FOR EACH ROW BEGIN

  IF NEW.i_env=1 AND (SELECT 1 FROM Customers WHERE i_customer=NEW.i_customer AND i_rep IN (3,5,9,12)) THEN

       IF NEW.id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN

        INSERT INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.id,'N');

        INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','INSERT',NOW(),CONCAT('Add to ip_customers ',NEW.id));

       ELSE

         CALL INSERT_USER(NEW.id,NEW.h323_password,'INSERT','');

        INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','INSERT',NOW(),CONCAT('Add to users ',NEW.id));      

       END IF;

  END IF;

  END|

TRIGGER Trg_Insert_Accounts

Après l’insertion dans la table Accounts

 

Ce trigger cherche si le compte est une adresse IP ou non si c’est le cas il l’insert dans ip_customers sinon dans users.

 

 

CREATE TRIGGER Trg_Delete_Accounts AFTER DELETE ON Accounts

FOR EACH ROW BEGIN

  IF OLD.id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN

    IF (SELECT 1 FROM `ast-replication`.ip_customers  WHERE remote_ip = OLD.id AND is_node='N') THEN

      DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.id AND is_node='N';

      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','DELETE',NOW(),CONCAT('Delete from ip_customers ',OLD.id));

    END IF;

  ELSE

    IF (SELECT 1 FROM `ast-replication`.users  WHERE username = OLD.id) THEN

      DELETE FROM `ast-replication`.users WHERE username = OLD.id;

      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','DELETE',NOW(),CONCAT('Delete from users ',OLD.id));

    END IF; 

  END IF;

  END|

 

TRIGGER Trg_Delete_Accounts

Après la suppression d’un compte SIP de la table Accounts.

 

Ce trigger supprime le compte SIP de la table ip_customers ou users selon la valeur du compte SIP (adresse IP ou non)

 

CREATE TRIGGER Trg_Update_Accounts AFTER UPDATE ON Accounts

FOR EACH ROW BEGIN

DECLARE is_ip INT DEFAULT 0;

DECLARE todo VARCHAR(10) DEFAULT '';

IF NEW.id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN

SET is_ip = 1;

END IF;

 

IF NEW.i_env=1 AND OLD.i_env=1 AND (NEW.id <> OLD.id OR NEW.h323_password <> OLD.h323_password) THEN

SET todo = 'UPDATE';

ELSE

IF NEW.i_env=1 AND OLD.i_env <> 1 THEN

SET todo = 'INSERT';

ELSEIF NEW.i_env <> 1 AND OLD.i_env=1 THEN

SET todo = 'DELETE';

END IF;

END IF;

 

CASE todo

WHEN 'UPDATE' THEN

IF is_ip THEN

UPDATE `ast-replication`.ip_customers SET remote_ip=NEW.id WHERE remote_ip=OLD.id AND is_node='N';

INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Update ip_customers set ',OLD.id,' to ',NEW.id));

ELSE

CALL INSERT_USER(NEW.id,NEW.h323_password,'UPDATE',OLD.id);

INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('update user ',OLD.id));

END IF;

WHEN 'INSERT' THEN

IF is_ip THEN

INSERT IGNORE INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.id,'N');

INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Add to ip_customers ',NEW.id));

ELSE

CALL INSERT_USER(NEW.id,NEW.h323_password,'INSERT','');

INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Add to user ',NEW.id));

END IF;

WHEN 'DELETE' THEN

IF is_ip THEN

DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.id AND is_node='N';

INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Delete from ip_customers ',OLD.id,' (i_env <> 1) i_env: ',OLD.i_env,' to ',NEW.i_env));

ELSE

DELETE FROM `ast-replication`.users WHERE username = OLD.id;

INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Delete from users ',OLD.id,' (i_env <> 1) i_env: ',OLD.i_env,' to ',NEW.i_env));

END IF;

END CASE;

END|

 

TRIGGER Trg_Update_Accounts

Après la mise le jour de la table Accounts

 

On commence par chercher si le compte est une adresse IP ou non pour déterminer la table de réaction (ip_customers ou users).

 

 

 

Condition pour la mise à jour: l’ancienne et la nouvelle valeur de i_env est 1 et au moins une des valeurs de l’ID ou PASSWORD a été changé.

 

 

Il faut ignorer les comptes dont la valeur de l’environnement est différente de 1.

La mise à jour de cette valeur implique une suppression ou une insertion selon l’ancienne et la nouvelle valeur de cette dernière.

 

 

CREATE TRIGGER Trg_Update_Customers AFTER UPDATE ON Customers

FOR EACH ROW BEGIN

  IF OLD.i_rep IN (3,5,9,12) AND NEW.i_rep NOT IN (3,5,9,12) THEN

      CALL UPDATE_CUSTOMER(NEW.i_rep,NEW.i_customer);

  ELSEIF OLD.i_rep NOT IN (3,5,9,12) AND NEW.i_rep IN (3,5,9,12) THEN

      CALL UPDATE_CUSTOMER(NEW.i_rep,NEW.i_customer);

  END IF; 

END|

 

TRIGGER Trg_Update_Customers

Après la mis mise à jour de la table Customers.

 

Le champ i_rep détermine l’état du client : active (3, 5, 9, 12) ou non.

L’action sera une suppression ou une insertion de tous les comptes associée à ce client.

 

 

 

CREATE TRIGGER Trg_Insert_Nodes AFTER INSERT ON Nodes

FOR EACH ROW BEGIN

  IF NEW.i_env=1 AND NEW.i_node_type=11 AND NEW.name not like '%[Deleted]%' THEN

    INSERT INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.ip,'Y');

    INSERT INTO `ast-replication`.vendors (remote_ip) values (NEW.ip);

    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','INSERT',NOW(),CONCAT('Add to ip_customers and vendors ',NEW.ip));

  END IF;

END|

TRIGGER Trg_Insert_Nodes

Après l’insertion dans la table Nodes

 

 

CREATE TRIGGER Trg_Delete_Nodes AFTER DELETE ON Nodes

FOR EACH ROW BEGIN

    IF (SELECT 1 FROM `ast-replication`.ip_customers  WHERE remote_ip = OLD.ip AND is_node='Y') THEN

      DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.ip AND is_node='Y';

       DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.ip;

      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','DELETE',NOW(),CONCAT('Delete from ip_customers and vendors',OLD.ip));

    END IF;

  END|

 

TRIGGER Trg_Delete_Nodes

Après suppression d’une node de la table Nodes

 

 

CREATE TRIGGER Trg_Update_Nodes AFTER UPDATE ON Nodes

FOR EACH ROW BEGIN

DECLARE todo VARCHAR(10) DEFAULT 'IGNORED';

DECLARE exist INT default 0;

DECLARE valid INT default 0;

SET exist=IFNULL((SELECT 1 FROM `ast-replication`.ip_customers WHERE remote_ip=OLD.ip AND is_node='Y'),0);

IF NEW.i_node_type=11 AND NEW.name NOT LIKE '%[Deleted]%' AND NEW.i_env=1 AND NEW.ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN

  SET valid=1;

END IF;

IF valid = 1 AND exist = 1 THEN

  SET todo = 'UPDATE';

  ELSEIF valid <> 1 AND exist = 1 THEN

    SET todo = 'DELETE';

    ELSEIF valid = 1 AND exist <> 1 THEN

      SET todo = 'INSERT';

END IF;

INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('TEST ',NEW.ip,' valid ',valid,' exist ',exist));

CASE todo

  WHEN 'UPDATE' THEN

    UPDATE `ast-replication`.ip_customers SET remote_ip=NEW.ip WHERE remote_ip=OLD.ip AND is_node='Y';

    UPDATE `ast-replication`.vendors SET remote_ip=NEW.ip WHERE remote_ip=OLD.ip;

    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Update ip_customers and vendors set ',OLD.ip,' to ',NEW.ip));

  WHEN 'INSERT' THEN

    INSERT IGNORE INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.ip,'Y');

    INSERT IGNORE INTO `ast-replication`.vendors (remote_ip) values (NEW.ip);

    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Add to ip_customers and vendors',NEW.ip));

  WHEN 'DELETE' THEN

    DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.ip AND is_node='Y';

    DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.ip;

    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Delete from ip_customers and vendors',NEW.ip));

  WHEN 'IGNORED' THEN

    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Update ignored ',NEW.ip));

END CASE;

END|

TRIGGER Trg_Update_Nodes

Après la mise à jour d’une node dans la table Nodes

 

 

CREATE TRIGGER Trg_Insert_Connections AFTER INSERT ON Connections

FOR EACH ROW BEGIN

  IF NEW.i_env=1 AND NEW.remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND NEW.sip='Y' THEN

    INSERT IGNORE INTO `ast-replication`.vendors (remote_ip) values (NEW.remote_ip);

     INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','INSERT',NOW(),CONCAT('Add to vendors ',NEW.remote_ip));

  END IF;

END|

TRIGGER Trg_Insert_Connections

Après l’insertion d’une connexion d’un vendeur (adresse IP) dans la table Connections

 

 

CREATE TRIGGER Trg_Delete_Connections AFTER DELETE ON Connections

FOR EACH ROW BEGIN

    IF (SELECT 1 FROM `ast-replication`.vendors  WHERE remote_ip = OLD.remote_ip) THEN

      DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.remote_ip;

      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','DELETE',NOW(),CONCAT('Delete from vendors ',OLD.remote_ip));

    END IF;

  END|

 

TRIGGER Trg_Delete_Connections

Après la suppression d’une connexion dans la table Connections

 

CREATE TRIGGER Trg_Update_Connections AFTER UPDATE ON Connections

FOR EACH ROW BEGIN

DECLARE todo VARCHAR(10) DEFAULT 'IGNORED';

DECLARE exist INT default 0;

DECLARE valid INT default 0;

SET exist=IFNULL((SELECT 1 FROM `ast-replication`.vendors  WHERE remote_ip = OLD.remote_ip),0);

IF  NEW.i_env=1 AND NEW.remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND NEW.sip='Y' THEN

  SET valid=1;

END IF;

IF valid = 1 AND exist = 1 THEN

  SET todo = 'UPDATE';

  ELSEIF valid <> 1 AND exist = 1 THEN

    SET todo = 'DELETE';

    ELSEIF valid = 1 AND exist <> 1 THEN

      SET todo = 'INSERT';

END IF;

CASE todo

  WHEN 'UPDATE' THEN

    UPDATE `ast-replication`.vendors SET remote_ip=NEW.remote_ip WHERE remote_ip=OLD.remote_ip;

    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','UPDATE',NOW(),CONCAT('Update vendors set ',OLD.remote_ip,' to ',NEW.remote_ip));   

  WHEN 'INSERT' THEN

    INSERT IGNORE INTO `ast-replication`.vendors (remote_ip) values (NEW.remote_ip);

    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','UPDATE',NOW(),CONCAT('Add to vendors ',NEW.remote_ip,' old ip should be *SIP-UA* or NULL -> ',OLD.remote_ip));

  WHEN 'DELETE' THEN

    DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.remote_ip;

    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','UPDATE',NOW(),CONCAT('Delete from vendors ',NEW.remote_ip,' OLD.remote_ip -> ',OLD.remote_ip));    

  WHEN 'IGNORED' THEN

    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Update ignored ',NEW.remote_ip));

END CASE;

END|

TRIGGER Trg_Update_Connections

Après la mise à jour d’une connexion dans la table Connections

Delimiter ;

 

ast-replication

Routine

Description

Delimiter |

 

 

CREATE FUNCTION get_deny() RETURNS text

    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|

 

FUNCTION get_deny()

Retourne la liste des IPs de la table ip_cutomers séparé par ‘;’

C’est la valeur du champ deny de la table sipusers (Realtime)

Liste de parameters:

aucune

Valeurs de retour:

text

Exemple:

SELECT get_deny();

 

 

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 |

 

PROCEDURE SET_REALM(IN val varchar(80),IN event varchar(30))

Liste de parameters:

val la nouvelle valeur du realm.

event l’événement déclanchant ce procédure.

Valeurs de retour:

aucune

Exemple:

CALL SET('realm1','INSERT');

Met à jour la valeur du md5secret dans tous les comptes SIP active.

Cette procédure a été appelée par le triggers associé à l’événement d’insertion dans la table realm

 

CALL SET('realm2','UPDATE');

Met à jour la valeur du md5secret dans tous les comptes SIP active.

Cette procédure a été appelée par le triggers associé à l’événement de mise à jour dans la table realm

 

 

CREATE TRIGGER Trg_Insert_realm AFTER INSERT ON realm

FOR EACH ROW BEGIN

  CALL SET_REALM(NEW.val,'INSERT');

END |

 

TRIGGER Trg_Insert_realm

Après l’insertion d’une nouvelle valeur du REALM dans la table realm

 

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 |

 

TRIGGER Trg_Update_realm

Après la mise à jour de la valeur du REALM dans la table realm

 

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 |

 

TRIGGER Trg_Delete_realm

Après la suppression du REALM dans la table realm

 

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|

 

TRIGGER Trg_Insert_users

Après l’insertion d’un compte SIP dans users

 

Dans les requêtes d’insertion on tient compte des valeurs par défaut des autres champs (sipusers et sippeers).

 

 

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|

 

TRIGGER Trg_Delete_users

Après la suppression d’un compte SIP de la table users

 

 

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|

 

TRIGGER Trg_Update_users

Après la mise à jour d’un compte SIP de la table users

 

 

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|

 

TRIGGER Trg_Insert_vendors

Après l’insertion d’un nouveau vendeur dans la table vendors

 

Si un vendeur est déjà créé car il est aussi un client IP (trigger de la table ip_customers), l’insertion sera ignoré car cette entré est suffisante.

 

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|

 

TRIGGER Trg_Delete_vendors

Après la suppression d’un vendeur de la table vendors

 

On supprime le vendeur concerné s’il n’est pas un client IP.

 

 

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|

 

TRIGGER Trg_Update_vendors

Après la mise à jour d’un vendeur de la table vendors

 

 

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|

TRIGGER Trg_Insert_ip_customers

Après l’insertion d’un client IP dans la table ip_customers

 

 

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|

 

TRIGGER Trg_Delete_ip_customers

Après la suppression d’un client IP de la table ip_customers

 

Le client sera supprimé de la table sippeers s’il n’existe pas dans la table vendors sinon on le mit à jour pour qu’il corresponde à un vendeur.

 

 

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|

TRIGGER Trg_Update_ip_customers

Après la mise à jour d’un client IP de la table ip_customers

 

Delimiter ;

 

Référence

http://switzernet.com/3/public/110314-astrad-triggers/

http://switzernet.com/3/public/110302-test-asterisk-mysql/

http://switzernet.com/3/public/110317-db3-versions/