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

Oussama Hammami, 2011-03-14

Switzernet

Afin de préparer une base de données conforme à l’utilisation du module Realtime d’Asterisk, On a programmé un ensemble de triggers décrits ci-dessous qui assurent la conversion de notre structure vers la structure exigée par ce module.

Porta-billing [porta-billing-routines.sql]

La structure de notre base de données est la suivante :

Accounts

Cette table décrit les comptes sip associés à un client (dans la table Customers).

mysql> desc Accounts;

+--------------------------+----------------------+------+-----+------------+----------------+

+--------------------------+----------------------+------+-----+------------+----------------+

| id | char(32) | NO | MUL | | |

| balance | decimal(15,5) | NO | | 0.00000 | |

| opening_balance | decimal(15,5) | NO | | 0.00000 | |

| issue_date | date | NO | | 0000-00-00 | |

| i_customer | int(10) unsigned | NO | MUL | 0 | |

| billing_model | tinyint(2) | NO | | 0 | |

| activation_date | date | NO | | 0000-00-00 | |

| i_product | int(10) unsigned | NO | MUL | 0 | |

| blocked | char(1) | NO | | N | |

| i_batch | int(10) unsigned | NO | MUL | 0 | |

| i_env | tinyint(1) unsigned | NO | | 1 | |

| control_number | int(10) unsigned | NO | | 0 | |

| iso_639_1 | char(2) | YES | | NULL | |

| i_time_zone | int(10) unsigned | NO | | 1 | |

| iso_4217 | char(3) | NO | MUL | | |

| um_enabled | char(1) | NO | MUL | N | |

| i_acl | int(10) unsigned | NO | MUL | 155 | |

| mac | char(23) | YES | MUL | NULL | |

| follow_me_enabled | char(1) | NO | | N | |

| ecommerce_enabled | char(1) | YES | | N | |

+--------------------------+----------------------+------+-----+------------+----------------+

id : le compte SIP.

h323_password : mot de passe du compte SIP

i_env : environnement de déploiement, la valeur de notre environnement est 1.

i_cutomer est une clé étrangère du table Customers (pour savoir le propriétaire d’un compte il faut passer par une jointure)

Customers

Notre clientèle et les informations nécessaires (adresse …).

mysql> desc Customers;

+----------------------------+------------------------+------+-----+---------------------+----------------+

+----------------------------+------------------------+------+-----+---------------------+----------------+

| name | char(41) | NO | MUL | | |

| timestamp | datetime | NO | | 0000-00-00 00:00:00 | |

| balance | decimal(15,5) | NO | | 0.00000 | |

| iso_4217 | char(3) | NO | MUL | | |

| zip | char(13) | NO | | | |

| bcc | char(99) | YES | | NULL | |

| send_statistics | char(1) | NO | | N | |

| i_customer_type | int(10) unsigned | NO | | 1 | |

| i_billing_period | tinyint(2) unsigned | NO | | 4 | |

| i_time_zone | int(10) unsigned | NO | | 1 | |

| i_env | tinyint(1) unsigned | NO | | 1 | |

| i_payment_term | int(10) unsigned | NO | | 0 | |

| blocked | enum('Y','N') | NO | | N | |

| ppm_enabled | char(1) | NO | | N | |

| i_rep | int(10) unsigned | NO | MUL | 0 | |

| drm_enabled | char(1) | NO | | N | |

| i_online_payment_processor | tinyint(3) unsigned | NO | | 0 | |

| reccuring_enabled | char(1) | NO | | N | |

| test_mode | char(1) | NO | | Y | |

| i_acl | int(10) unsigned | NO | MUL | 0 | |

| opening_balance | decimal(15,5) | NO | | 0.00000 | |

| min_allowed_payment | decimal(15,5) unsigned | NO | | 0.00000 | |

| callshop_enabled | char(1) | NO | | N | |

| i_customer_class | int(10) unsigned | NO | MUL | 0 | |

| unallocated_payments | decimal(15,5) | NO | | 0.00000 | |

| bill_status | enum('O','S','C') | NO | MUL | O | |

+----------------------------+------------------------+------+-----+---------------------+----------------+

La valeur du champ i_rep présente le statut du client (on ne considère que les valeurs : 3, 5, 9,12)

mysql> SELECT i_rep,name FROM Representatives;

+-------+------------------------+

| i_rep | name |

+-------+------------------------+

| 3 | Billable |

| 4 | Cancelled Unclassified |

| 15 | Collection |

| 2 | Dealer ABC |

| 10 | Deleted |

| 13 | Fraud |

| 5 | Internal |

| 14 | Loss |

| 6 | Overdue |

| 12 | Shop |

| 11 | Stock |

| 16 | Support |

| 7 | Terminated |

| 9 | VIP |

+-------+------------------------+

Connections

Les IPs de nos vendeurs (remote_ip).

mysql> desc Connections;

+-------------------------------+---------------------------------------------------------+------+-----+---------+----------------+

+-------------------------------+---------------------------------------------------------+------+-----+---------+----------------+

| i_vendor | int(10) unsigned | NO | MUL | 0 | |

| CLD | char(32) | YES | | NULL | |

| i_env | tinyint(1) unsigned | NO | | 1 | |

| capacity | smallint(5) unsigned | NO | | 0 | |

| asr | float(7,4) | YES | | NULL | |

| sip | enum('Y','N') | NO | | Y | |

| h323 | enum('Y','N') | NO | | N | |

+-------------------------------+---------------------------------------------------------+------+-----+---------+----------------+

Nodes

Nos serveurs sip. Le nom de ce dernier ne doit pas contenir ‘[Deleted]’

mysql> desc Nodes;

+----------------------+----------------------------------------------------------------------+------+-----+---------+----------------+

+----------------------+----------------------------------------------------------------------+------+-----+---------+----------------+

| ip | char(64) | NO | UNI | | |

| h323_id | char(255) | NO | UNI | | |

| radius_client | char(1) | NO | | N | |

| i_env | tinyint(1) unsigned | YES | | 1 | |

| i_time_zone | int(10) unsigned | NO | | 1 | |

| id | char(32) | YES | | NULL | |

+----------------------+----------------------------------------------------------------------+------+-----+---------+----------------+

On ne considère que les serveurs SIP dont la valeur de i_node_type est 11.

Ast-replication [ast-replication-routines-structure.sql]

Structure

Avant de passer à la structure Asterisk-Realtime, on a créé des tables (vendors, ip_customers, users) dont l’objective est de regrouper tous les informations nécessaires dans des tables avec une structure plus simple, ces tables seront utilisées de la même façon (triggers) pour générer la base de données Realtime à répliquer dans plusieurs serveurs Asterisk (réplication MySQL).

vendors

Cette table regroupe les IPs (remote_ip) de nos vendeurs (les connections)

mysql> desc vendors;

+-----------+-------------+------+-----+---------+----------------+

+-----------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| remote_ip | varchar(15) | NO | UNI | | |

+-----------+-------------+------+-----+---------+----------------+

ip_customers

Cette table regroupe les IPs qui ont le droit d’envoyer des appels entrant à Asterisk sans l’authentification.

Le champ is_node indique si le serveur SIP fait parti de notre réseau (Y) ou c’est un serveur interconnecté avec un autre réseau (N).

mysql> desc ip_customers;

+-----------+---------------+------+-----+---------+----------------+

+-----------+---------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| remote_ip | varchar(15) | NO | UNI | | |

| is_node | enum('Y','N') | NO | | N | |

+-----------+---------------+------+-----+---------+----------------+

users

Cette table décrit les comptes SIP (username) et le md5 du mot de passe (md5secret).

mysql> desc users;

+-----------+-------------+------+-----+---------+----------------+

+-----------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| username | varchar(30) | NO | UNI | | |

+-----------+-------------+------+-----+---------+----------------+

Ci dessous un exemple pour calculer le md5secret :

mysql> SELECT MD5('Account:Realm:Password');

realm

Pour savoir quelle valeur du Realm Asterisk utilise il faut sélectionner la dernière ligne de cette table.

mysql> desc realm;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| val | varchar(80) | NO | UNI | | |

+-------+-------------+------+-----+---------+----------------+

mysql> select val as Realm from realm order by id desc limit 1;

+------------+

| Realm |

+------------+

| Your_Realm |

+------------+

Triggers

Customers & Accounts

Procedures

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

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|

Delimiter ;

Exemple d’utilisation

Ajouter un nouveau client dans users

mysql> CALL INSERT_USER(41215500329,'password','INSERT','');

Mettre à jour le client 41215500329

mysql> CALL INSERT_USER(41215500327,'password','UPDATE','41215500329');

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

Delimiter |

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;

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 LOOP;

CLOSE cur;

END;

Delimiter ;

Exemple d’utilisation

Désactiver les comptes associés au client 16165

mysql> CALL UPDATE_CUSTOMER(1,16165);

mysql> select * from `ast-replication`.users where username IN (select id from Accounts where i_customer=16165);

Activer les comptes associés au client 16165

mysql> CALL UPDATE_CUSTOMER(5,16165);

mysql> select * from `ast-replication`.users where username IN (select id from Accounts where i_customer=16165);

Triggers

AFTER INSERT ON Accounts

Delimiter |

CREATE TRIGGER Trg_Insert_Accounts AFTER INSERT ON Accounts

FOR EACH ROW BEGIN

IF NEW.i_env=1 THEN

IF (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;

Delimiter ;

AFTER DELETE ON Accounts

Delimiter |

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;

Delimiter ;

AFTER UPDATE ON Accounts

Delimiter |

CREATE TRIGGER Trg_Update_Accounts AFTER UPDATE ON Accounts

FOR EACH ROW BEGIN

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

IF NEW.i_env=1 THEN

IF OLD.i_env=1 THEN

IF NEW.id <> OLD.id 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));

END IF;

ELSE

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));

END IF;

ELSEIF OLD.i_env=1 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));

END IF;

ELSE

IF NEW.i_env=1 THEN

IF OLD.i_env=1 THEN

IF NEW.h323_password <> OLD.h323_password THEN

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 password : ',OLD.id));

ELSEIF NEW.id <> OLD.id THEN

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 account',OLD.id,' to ',NEW.id));

END IF;

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;

ELSEIF OLD.i_env=1 THEN

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;

Delimiter ;

AFTER UPDATE ON Customers

Delimiter |

CREATE TRIGGER Trg_Update_Customers AFTER UPDATE ON Customers

FOR EACH ROW BEGIN

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

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

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

END IF;

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

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

END IF;

END;

Delimiter ;

Nodes

AFTER INSERT ON Nodes

Delimiter |

CREATE TRIGGER Trg_Insert_Nodes AFTER INSERT ON Nodes

FOR EACH ROW BEGIN

IF NEW.i_node_type=11 THEN

IF NEW.i_env=1 THEN

IF 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;

Delimiter ;

AFTER DELETE ON Nodes

Delimiter |

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;

Delimiter ;

AFTER UPDATE ON Nodes

Delimiter |

CREATE TRIGGER Trg_Update_Nodes AFTER UPDATE ON Nodes

FOR EACH ROW BEGIN

IF NEW.i_env=1 THEN

IF NEW.name NOT LIKE '%[Deleted]%' THEN

IF NULLIF(NEW.ip,OLD.ip) THEN

IF OLD.name NOT LIKE '%[Deleted]%' 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));

ELSE

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,' old name should containte Deleted -> ',OLD.name));

END IF;

ELSE

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);

END IF;

ELSEIF OLD.ip NOT LIKE '%[Deleted]%' 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,' name -> ',New.name));

END IF;

ELSEIF OLD.i_env=1 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,' drop from our env.[1] new -> ',NEW.i_env));

END IF;

END;

Delimiter ;

Connections

AFTER INSERT ON Connections

Delimiter |

CREATE TRIGGER Trg_Insert_Connections AFTER INSERT ON Connections

FOR EACH ROW BEGIN

IF NEW.i_env=1 THEN

IF NEW.remote_ip NOT IN ('SIP-UA','') 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;

Delimiter ;

AFTER DELETE ON Connections

Delimiter |

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;

Delimiter ;

AFTER UPDATE ON Connections

Delimiter |

CREATE TRIGGER Trg_Update_Connections AFTER UPDATE ON Connections

FOR EACH ROW BEGIN

IF NEW.i_env=1 THEN

IF NEW.remote_ip NOT IN ('SIP-UA','') THEN

IF NULLIF(NEW.remote_ip,OLD.remote_ip) THEN

IF OLD.remote_ip NOT IN ('SIP-UA','') 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));

ELSE

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));

END IF;

ELSE

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,' new in our env.[1] old -> ',OLD.i_env));

END IF;

ELSEIF OLD.remote_ip NOT IN ('SIP-UA','') 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));

END IF;

ELSEIF OLD.i_env=1 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,' drop from our env.[1] new -> ',NEW.i_env));

END IF;

END;

Delimiter ;

Realm

Procedures

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

DELIMITER |

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;