André Guimarães, 2012-08-22
Switzernet
1. New functionalities and corrections
2. Performance
4. Minor bugs
5. Wish list for next versions
6. Resources
This version is
compatible with Astrad v14. It also might be compatible with 13 but that was
not tested.
New functionalities and corrections
As Debian 5.0 is now EOL this version uses Debian 6.0. Due to this, previous versions of DBA cannot be update by puppet to this new version. Previous versions require a server installation with Debian 6.0 followed by a full installation of the Puppet module.
MySQL version used is now 5.5.
This version supports multiple Billings. This support is made by adding a new field to all replicated tables with the Billing in which the data exists in PBS. Then DBA filters all data and generates the data to be feed to each Astrad with the correct authentication Billing and all Billings where the account is active. To decide which Billing is used to authenticate, a new table AccountsAuth is replicated from PBS where the active Billing is defined.
Replication
In the script setup-mysql-replication.pl, support to replication from MySQL installed on non standard ports was added as well as support for two Billings. To guarantee consistency of the dumped SQL data, this scripts connects to each entry MySQL in PBS and stops replication. It then makes a SQL dump from the third SQL, the mixer, which as the data of the other two MySQLs. In this third MySQL each table as a new column ibilling which indicates the origin of the data. After finishing the SQL dump replication is restarted in the entry MySQLs in PBS.
A new table AccountsAuth was added to the replication list. This is the table that is used to decide which the active Billing for authentication is. The accounting information is sent to all Billings where the account is active and valid. This table contains only accounts with numeric names.
DROP TABLE IF EXISTS `AccountsAuth`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `AccountsAuth` (
`accountid` varchar(32) NOT NULL,
`ibillauth` int(11) NOT NULL DEFAULT 1,
PRIMARY KEY (`accountid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Data synchronization
exec-registration.pl was replaced by two scripts one for each Master: exec-registration-m1.pl and exec-registration-m2.pl. This is a limitation to multiple Billing as we would have to create a script for each Master that we add. We should have only one script that sends the current location of each phone to its respective Master(s). This limit the current active Masters to two. Each one of the new scripts is the same as exec-registration.pl with the difference that each one is responsible to select and send the location information of its Master. exec-registration-m1.pl selects all information regarding accounts where i_bill_auth = 1 and sends it to Master 1 and exec-registration-m2.pl does the same with the accounts with i_bill_auth = 2 and sends it to Master 2. Both scripts send the same information to all other DBAs.
Monitoring
SNMP was not being installed by puppet. This problem was solved by correcting the module.
Database
[Download]
In the table sipdevices in DB asterisk and vendors in DB astrad, two fields were added: i_bill_auth and i_bill_acc. i_bill_auth defines to which Billing the Astrad should send the authentication requests while i_bill_acc has a list of all Billings where the account is active and valid. This permits switching transparently between the two Billings as the Radius packets used for billing the customer will be sent to all Billings where the account exists. To send to just to one Billing it is necessary to disable the account by changing its name to a non numeric value or changing the i_rep of the customer. The unique keys of each table in porta-billing DB are now the old keys plus the i_billing column to allow having the same account in multiple Billings.
The following triggers, functions, procedures and events replace the one that were used before to filter the accounts, nodes and vendors from the Porta-Billing.
DELIMITER |
DROP PROCEDURE IF EXISTS `LOG_TRIGGER`|
CREATE PROCEDURE `LOG_TRIGGER`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN actiontext VARCHAR(150))
BEGIN
IF actiontext != '' AND actiontext is not NULL THEN
INSERT INTO `astrad`.triggers_log (table_name,event,log_time,comment) VALUES (tablename,todo,NOW(),actiontext);
END IF;
END |
DELIMITER ;
The procedure LOG_TRIGGER just writes in the table astrad.triggers_log every modification made by the triggers.
DELIMITER |
DROP FUNCTION IF EXISTS `GET_IBILLACC_FOR_VENDORS` |
CREATE FUNCTION `GET_IBILLACC_FOR_VENDORS`(accountid VARCHAR(100)) RETURNS VARCHAR(100) DETERMINISTIC READS SQL DATA
BEGIN
DECLARE Temp VARCHAR(100);
SELECT GROUP_CONCAT(i_billing) INTO Temp FROM (SELECT i_billing FROM Nodes WHERE i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%' AND ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND ip = accountid UNION SELECT i_billing FROM Connections WHERE i_env=1 AND remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND sip='Y' AND remote_ip = accountid) a;
RETURN Temp;
END |
DELIMITER ;
The procedure GET_IBILLACC_FOR_VENDORS is used to retrieve the list of Billings where a vendor’s account is active and is valid. A account is considered valid if exists in Nodes with i_env 1, i_node_type 11, name doesn’t contain ‘[Deleted]’ and the ip is in the IPv4 format or it exists in Connections with i_env 1, sip is ‘Y’ and remote_ip is in IPv4 format. If a vendor’s account does not match any of these conditions is not added to the Astrad. A vendor in this table is considered an outgoing route and can be one of our nodes (Astrad or Porta-SIP) or an interconnection with one of our outgoing providers.
DELIMITER |
DROP FUNCTION IF EXISTS `GET_IBILLACC_FOR_USERS` |
CREATE FUNCTION `GET_IBILLACC_FOR_USERS`(accountid VARCHAR(100)) RETURNS VARCHAR(100) DETERMINISTIC READS SQL DATA
BEGIN
DECLARE Temp VARCHAR(100);
SELECT GROUP_CONCAT(i_billing) INTO Temp FROM (SELECT distinct `porta-billing`.Accounts.id, `porta-billing`.Accounts.i_billing FROM `porta-billing`.Accounts, `porta-billing`.Customers WHERE `porta-billing`.Accounts.i_env=1 AND `porta-billing`.Customers.i_rep IN (3,5,9,12,17) AND `porta-billing`.Accounts.i_customer = `porta-billing`.Customers.i_customer AND `porta-billing`.Accounts.i_billing = `porta-billing`.Customers.i_billing AND `porta-billing`.Accounts.id = accountid) a GROUP BY id;
RETURN Temp;
END |
DELIMITER ;
The procedure GET_IBILLACC_FOR_USERS is used to retrieve the list of Billings where a customer’s account is active and is valid. An account is currently considered active and valid if the Customer’s i_rep is either 3,5,9,12,17, the i_env is 1 and the account name is numeric (all customer’s account numbers should be in E164 format). If a customer’s account does not match any of these conditions is not added to the Astrad.
DELIMITER |
DROP FUNCTION IF EXISTS `GET_IBILLACC_FOR_IPCUSTOMERS` |
CREATE FUNCTION `GET_IBILLACC_FOR_IPCUSTOMERS`(accountid VARCHAR(100)) RETURNS VARCHAR(100) DETERMINISTIC READS SQL DATA
BEGIN
DECLARE Temp VARCHAR(100);
SELECT GROUP_CONCAT(i_billing) INTO Temp FROM (SELECT Accounts.i_billing FROM Accounts, Customers WHERE Accounts.i_env=1 AND Customers.i_rep IN (3,5,9,12,17) AND Accounts.i_customer = Customers.i_customer AND Accounts.i_billing = Customers.i_billing AND Accounts.id = accountid UNION SELECT Nodes.i_billing FROM Nodes WHERE ip = accountid AND i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%') a;
RETURN Temp;
END |
DELIMITER ;
The procedure GET_IBILLACC_FOR_IPCUSTOMERS is used to retrieve the list of Billings where a ipcustomer’s account is active and is valid. An account is currently considered active and valid if the customer’s i_rep is either 3,5,9,12,17, the i_env is 1 and the account name is in IPv4 format or if the account exists in Nodes with an IP in IP v4 format, i_env 1, i_node_type 11, name doesn’t contain ‘[Deleted]’. If an ip customer’s account does not match any of these conditions is not added to the Astrad. An ip customer is a node or a server for incoming calls from PSTN.
DELIMITER |
DROP FUNCTION IF EXISTS `GET_HOST_PORT` |
CREATE FUNCTION `GET_HOST_PORT`(accountid VARCHAR(100)) RETURNS INT(11) DETERMINISTIC READS SQL DATA
BEGIN
DECLARE Temp INT(11);
SELECT IFNULL((SELECT port FROM astrad_confs WHERE host = accountid),5060) INTO Temp;
RETURN Temp;
END |
DELIMITER ;
This function is used to retrieve the port in which the node is running. If no result is found it returns the default SIP port.
The procedure INS_ACC_USERS, decides if an account that exists in Accounts is added or updated into table astrad.users. An account is only added if it respects the conditions already described and if it is in the Billing configured as active for authentication in table AccountsAuth. The password is chosen from the active account in AccountsAuth if more than one valid account exists.
DELIMITER |
DROP PROCEDURE IF EXISTS `INS_ACC_USERS`|
CREATE PROCEDURE `INS_ACC_USERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
DECLARE rm VARCHAR(80);
DECLARE md varchar(32);
DECLARE ibillauth tinyint(11);
DECLARE ibillacc VARCHAR(100);
IF (account REGEXP '^[0-9]+$') AND
(SELECT IFNULL((SELECT 1 FROM Customers WHERE i_customer = (SELECT i_customer FROM Accounts WHERE id = account AND i_billing=billing AND i_env=1 limit 1) AND i_rep IN (3,5,9,12,17) AND i_billing=billing limit 1),0)) THEN
SET ibillauth = (SELECT IFNULL((SELECT ibillauth FROM AccountsAuth WHERE accountid=account LIMIT 1),billing));
SET ibillacc = (SELECT GET_IBILLACC_FOR_USERS(account));
IF (ibillauth = billing) THEN
SET rm = (SELECT val FROM `astrad`.realm ORDER BY id DESC LIMIT 1);
SET md = (SELECT md5(CONCAT(account,':',rm,':',IFNULL((SELECT h323_password FROM Accounts WHERE id = account AND i_billing=ibillauth limit 1),''))));
INSERT INTO `astrad`.users (username, md5secret, i_bill_auth, i_bill_acc) values (account, md, billing, ibillacc) ON DUPLICATE KEY UPDATE md5secret=md, i_bill_auth = ibillauth, i_bill_acc = ibillacc;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Created or modified users account ',account));
ELSE
UPDATE `astrad`.users SET i_bill_acc = ibillacc WHERE username = account;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Modified users account ',account));
END IF;
END IF;
END |
DELIMITER ;
An older version of the procedure didn’t use table accounts and always chose the valid Billing with lesser billing id as active. The following code was replaced by the code above.
DELIMITER |
DROP PROCEDURE IF EXISTS `INS_ACC_USERS`|
CREATE PROCEDURE `INS_ACC_USERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
DECLARE rm VARCHAR(80);
DECLARE md varchar(32);
DECLARE ibillacc VARCHAR(100);
IF (account REGEXP '^[0-9]+$') AND
(SELECT IFNULL((SELECT 1 FROM Customers WHERE i_customer = (SELECT i_customer FROM Accounts WHERE id = account AND i_billing=billing AND i_env=1 limit 1) AND i_rep IN (3,5,9,12,17) AND i_billing=billing limit 1),0)) THEN
SET ibillacc = (SELECT GET_IBILLACC_FOR_USERS(account));
IF (SELECT IFNULL((SELECT 1 FROM `astrad`.users WHERE username = account AND i_bill_auth < billing limit 1),0)) = 0 THEN
SET rm = (SELECT val FROM `astrad`.realm ORDER BY id DESC LIMIT 1);
SET md = (SELECT md5(CONCAT(account,':',rm,':',IFNULL((SELECT h323_password FROM Accounts WHERE id = account AND i_billing=billing limit 1),''))));
INSERT INTO `astrad`.users (username, md5secret, i_bill_auth, i_bill_acc) values (account, md, billing, ibillacc) ON DUPLICATE KEY UPDATE md5secret=md, i_bill_auth = billing, i_bill_acc = ibillacc;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Created or modified users account ',account));
ELSE
UPDATE `astrad`.users SET i_bill_acc = ibillacc WHERE username = account;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Modified users account ',account));
END IF;
END IF;
END |
DELIMITER ;
The procedure DEL_ACC_USERS deletes the account from table astrad.users when the active account is deleted or is changed to invalid in Accounts or AccountsAuth.
DROP PROCEDURE IF EXISTS `DEL_ACC_USERS`|
CREATE PROCEDURE `DEL_ACC_USERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
DECLARE ibillauth tinyint(2);
SET ibillauth = (SELECT IFNULL((SELECT ibillauth FROM AccountsAuth WHERE accountid=account LIMIT 1),billing));
IF (SELECT IFNULL((SELECT 1 FROM `astrad`.users WHERE username = account AND i_bill_auth = ibillauth AND i_bill_auth = billing LIMIT 1),0)) THEN
DELETE FROM `astrad`.users WHERE username = account;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Deleted users account ',account));
END IF;
END |
DELIMITER ;
Before using the table AccountsAuth the same procedure verified if there was another valid account in another Billing and in that case it would use that account instead as active account.
DELIMITER |
DROP PROCEDURE IF EXISTS `DEL_ACC_USERS`|
CREATE PROCEDURE `DEL_ACC_USERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
DECLARE nb tinyint(2) unsigned default 1;
DECLARE ibillacc VARCHAR(100);
DECLARE rm VARCHAR(80);
DECLARE md varchar(32);
IF (SELECT IFNULL((SELECT 1 FROM `astrad`.users WHERE username = account LIMIT 1),0)) THEN
SET nb = (SELECT IFNULL((SELECT MIN(i_billing) FROM Accounts WHERE id = account AND i_billing > billing AND i_customer IN (SELECT i_customer FROM Customers where i_rep IN (3,5,9,12,17) AND i_billing > billing) AND i_env=1 AND account REGEXP '^[0-9]+$' LIMIT 1),0));
IF nb > 0 THEN
SET rm = (SELECT val FROM `astrad`.realm ORDER BY id DESC LIMIT 1);
SET md = (SELECT md5(CONCAT(account,':',rm,':',IFNULL((SELECT h323_password FROM Accounts WHERE id = account AND i_billing=nb limit 1),''))));
SET ibillacc = (SELECT GET_IBILLACC_FOR_USERS(account));
UPDATE `astrad`.users SET md5secret=md, i_bill_auth = nb, i_bill_acc = ibillacc WHERE username = account;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Updated users account ',account));
ELSE
DELETE FROM `astrad`.users WHERE username = account;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Deleted users account ',account));
END IF;
END IF;
END |
DELIMITER ;
The procedures INS_ACC_VENDORS insert a new entry in table astrad.vendors if the vendor meets all requirements previously described. If a line already exists it does nothing if the i_billing is higher than the entry that already exists. If the i_billing is the same or lower it updates the values of i_bill_acc and i_bill_auth. This means that authentication for a vendor is always the Billing with the lowest id.
DELIMITER |
DROP PROCEDURE IF EXISTS `INS_ACC_VENDORS`|
CREATE PROCEDURE `INS_ACC_VENDORS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
DECLARE ibillacc VARCHAR(100);
DECLARE hostport INT(11) DEFAULT 5060;
IF (SELECT IFNULL((SELECT 1 FROM Nodes WHERE i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%' AND ip = account AND i_billing = billing AND ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' LIMIT 1),0)) OR
(SELECT IFNULL((SELECT 1 FROM Connections WHERE i_env=1 AND remote_ip = account AND i_billing = billing AND remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND sip='Y' LIMIT 1),0))
THEN
SET ibillacc = (SELECT GET_IBILLACC_FOR_VENDORS(account));
IF (SELECT IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip = account AND i_bill_auth < billing limit 1),0)) = 0 THEN
SET hostport = (SELECT GET_HOST_PORT(account));
INSERT INTO `astrad`.vendors (remote_ip,port,i_bill_auth,i_bill_acc) values (account,hostport,billing,ibillacc) ON DUPLICATE KEY UPDATE i_bill_auth = billing,i_bill_acc=ibillacc;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Created vendor with IP ',account));
ELSE
UPDATE `astrad`.vendors SET i_bill_acc = ibillacc WHERE remote_ip = account;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Updated vendor with IP ',account));
END IF;
END IF;
END |
DELIMITER ;
In DEL_ACC_VENDORS the vendor is deleted from astrad.vendors if it has the same i_billing. If another vendor with same IP is valid in other Billing that vendor is used instead.
DELIMITER |
DROP PROCEDURE IF EXISTS `DEL_ACC_VENDORS`|
CREATE PROCEDURE `DEL_ACC_VENDORS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
DECLARE nb tinyint(2) unsigned DEFAULT 1;
DECLARE ibillacc VARCHAR(100);
DECLARE hostport INT(11) DEFAULT 5060;
IF (SELECT IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip = account LIMIT 1),0)) THEN
SET nb = (SELECT IFNULL((SELECT MIN(i_billing) FROM (SELECT i_billing FROM Nodes WHERE i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%' AND name REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND name = account UNION SELECT i_billing FROM Connections WHERE i_env=1 AND remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND sip='Y' AND remote_ip = account) a LIMIT 1),0));
IF nb > 0 THEN
SET ibillacc = (SELECT GET_IBILLACC_FOR_VENDORS(account));
SET hostport = (SELECT GET_HOST_PORT(account));
UPDATE `astrad`.vendors SET port = hostport, i_bill_auth = nb, i_bill_acc = ibillacc WHERE remote_ip = account;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Updated vendor with IP ',account));
ELSE
DELETE FROM `astrad`.vendors WHERE remote_ip = account;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Deleted vendors account ',account));
END IF;
END IF;
END |
DELIMITER ;
The procedure INS_ACC_IPCUSTOMERS decides if a new entry in porta-billing tables Accounts or Nodes, with the IPv4 format as id or ip, is inserted or not in table astrad.ip_customers. The new entry should meet the already described requirements. If an entry with a lower i_billing is added the i_bill_auth used is the lowest. This means that authentication for an IP customer is always the Billing with the lowest id.
DELIMITER |
DROP PROCEDURE IF EXISTS `INS_ACC_IPCUSTOMERS`|
CREATE PROCEDURE `INS_ACC_IPCUSTOMERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
DECLARE isnode enum('Y','N') default 'N';
DECLARE ibillacc VARCHAR(100);
DECLARE hostport INT(11) DEFAULT 5060;
IF (SELECT IFNULL((SELECT 1 FROM Customers WHERE i_billing = billing AND i_rep IN (3,5,9,12,17) AND i_customer = (SELECT i_customer FROM Accounts WHERE id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND id = account AND i_billing = billing LIMIT 1) LIMIT 1),0)) OR
(SELECT IFNULL((SELECT 1 FROM Nodes WHERE ip = account AND i_billing = billing AND i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%' LIMIT 1),0))
THEN
SET ibillacc = (SELECT GET_IBILLACC_FOR_IPCUSTOMERS(account));
IF (SELECT IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip = account AND i_bill_auth < billing limit 1),0)) = 0 THEN
SET isnode = (SELECT IFNULL((SELECT 'Y' FROM Nodes WHERE i_env = 1 AND i_node_type = 11 and name not like '%[Deleted]%' AND ip = account AND i_billing = billing AND ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' LIMIT 1),'N'));
SET hostport = (SELECT GET_HOST_PORT(account));
INSERT INTO `astrad`.ip_customers (remote_ip,port,is_node,i_bill_auth,i_bill_acc) values (account,hostport,isnode,billing,ibillacc) ON DUPLICATE KEY UPDATE i_bill_auth = billing,is_node=isnode,i_bill_acc=ibillacc;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Created ip_customer with IP ',account));
ELSE
UPDATE `astrad`.ip_customers SET i_bill_acc = ibillacc WHERE remote_ip = account;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Updated ip_customer with IP ',account));
END IF;
END IF;
END |
DELIMITER ;
The procedure DEL_ACC_IPCUSTOMERS decides if an ip customer should be deleted or not. If the active ip customer is deleted but there is another valid ip customer in another Billing that one is used instead.
DELIMITER |
DROP PROCEDURE IF EXISTS `DEL_ACC_IPCUSTOMERS`|
CREATE PROCEDURE `DEL_ACC_IPCUSTOMERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
DECLARE isnode enum('Y','N') default 'N';
DECLARE nb tinyint(2) unsigned default 1;
DECLARE hostport INT(11) DEFAULT 5060;
DECLARE ibillacc VARCHAR(100);
IF (SELECT IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip = account AND i_bill_auth = billing limit 1),0)) THEN
SET nb = (SELECT IFNULL((SELECT MIN(i_billing) FROM Nodes WHERE ip = account AND i_billing > billing AND i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%' limit 1),0));
IF nb > 0 THEN
SET isnode = (SELECT IFNULL((SELECT 'Y' FROM Nodes WHERE i_env = 1 AND i_node_type = 11 and name not like '%[Deleted]%' AND ip = account AND i_billing = nb AND ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' LIMIT 1),'N'));
SET hostport = (SELECT GET_HOST_PORT(account));
SET ibillacc = (SELECT GET_IBILLACC_FOR_IPCUSTOMERS(account));
UPDATE `astrad`.ip_customers SET port = hostport, is_node = isnode, i_bill_acc = ibillacc WHERE remote_ip = account;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Updated ip_customer with IP ',account));
ELSE
DELETE FROM `astrad`.ip_customers WHERE remote_ip = account;
CALL LOG_TRIGGER(todo,tablename,CONCAT('Deleted ip_customer with IP ',account));
END IF;
END IF;
END |
DELIMITER ;
The following procedure decides which procedure to call based on the account name and depending on the action. It calls the procedure to insert/delete an IP customer if the new name of the account matches an IP v4 if not runs the user’s procedures. Currently the code has the limitation that it doesn’t support conversion of a user in an IP customer and vice-versa. An UPDATE is a DELETE followed by an INSERT. The called procedures might not do anything depending on the Account’s or Node’s data.
DELIMITER |
DROP PROCEDURE IF EXISTS `CHANGE_IP_CUSTOMERS_AND_USERS`|
CREATE PROCEDURE `CHANGE_IP_CUSTOMERS_AND_USERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN newaccount varchar(30), IN oldaccount VARCHAR(30))
BEGIN
IF todo != "IGNORE" THEN
IF newaccount REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
IF todo = "DELETE" THEN
CALL DEL_ACC_IPCUSTOMERS(todo, tablename, billing, newaccount);
END IF;
IF todo = "UPDATE" AND newaccount != oldaccount THEN
CALL DEL_ACC_IPCUSTOMERS(todo, tablename, billing, oldaccount);
END IF;
IF todo = "INSERT" OR todo = "UPDATE" THEN
CALL INS_ACC_IPCUSTOMERS(todo, tablename, billing, newaccount);
END IF;
ELSE
IF todo = "DELETE" THEN
CALL DEL_ACC_USERS(todo, tablename, billing, newaccount);
END IF;
IF todo = "UPDATE" AND newaccount != oldaccount THEN
CALL DEL_ACC_USERS(todo, tablename, billing, oldaccount);
END IF;
IF todo = "INSERT" OR todo = "UPDATE" THEN
CALL INS_ACC_USERS(todo, tablename, billing, newaccount);
END IF;
END IF;
END IF;
END |
DELIMITER ;
The procedure CHANGE_VENDORS decides which procedure to call depending on the action. An update is a DELETE followed by an INSERT. The called procedures might not do anything depending on the vendor’s data.
DELIMITER |
DROP PROCEDURE IF EXISTS `CHANGE_VENDORS`|
CREATE PROCEDURE `CHANGE_VENDORS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN newaccount varchar(30), IN oldaccount VARCHAR(30))
BEGIN
IF todo != "IGNORE" THEN
IF todo = "DELETE" THEN
CALL DEL_ACC_VENDORS(todo, tablename, billing, newaccount);
END IF;
IF todo = "UPDATE" AND newaccount != oldaccount THEN
CALL DEL_ACC_VENDORS(todo, tablename, billing, oldaccount);
END IF;
IF todo = "INSERT" OR todo = "UPDATE" THEN
CALL INS_ACC_VENDORS(todo, tablename, billing, newaccount);
END IF;
END IF;
END |
DELIMITER ;
This procedure validates i_rep changes in Customers to decide if IP customer or customer’s account is still valid and active. It adds or removes them according to the previously described conditions.
DELIMITER |
DROP PROCEDURE IF EXISTS `UPDATE_CUSTOMER`|
CREATE PROCEDURE `UPDATE_CUSTOMER`(IN billing INT(2), IN icustomer INT(10) unsigned, IN oirep INT(10) unsigned, IN nirep INT(10) unsigned, IN oienv tinyint(1) unsigned, IN nienv tinyint(1) unsigned)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE test INT DEFAULT 1;
DECLARE account VARCHAR(30);
DECLARE todo VARCHAR(10) DEFAULT 'IGNORE';
DECLARE cur CURSOR FOR SELECT id FROM Accounts where i_customer=icustomer AND i_billing=billing;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF ((oirep NOT IN (3,17,5,9,12) AND nirep IN (3,17,5,9,12) AND oienv=1 AND nienv=1) OR
(oirep NOT IN (3,17,5,9,12) AND nirep IN (3,17,5,9,12) AND oienv!=1 AND nienv=1) OR
(oirep IN (3,17,5,9,12) AND nirep IN (3,17,5,9,12) AND oienv!=1 AND nienv=1)) THEN
SET todo = 'INSERT';
ELSEIF ((oirep IN (3,17,5,9,12) AND nirep IN (3,17,5,9,12) AND oienv=1 AND nienv!=1) OR
(oirep IN (3,17,5,9,12) AND nirep NOT IN (3,17,5,9,12) AND oienv=1 AND nienv=1) OR
(oirep IN (3,17,5,9,12) AND nirep NOT IN (3,17,5,9,12) AND oienv=1 AND nienv!=1)) THEN
SET todo = 'DELETE';
END IF;
IF todo != "IGNORE" THEN
OPEN cur;
read_loop: LOOP
FETCH cur INTO account;
IF done THEN
LEAVE read_loop;
END IF;
IF todo = "INSERT" THEN
CALL CHANGE_IP_CUSTOMERS_AND_USERS('INSERT','Customers',billing,account,NULL);
ELSEIF todo = "DELETE" THEN
CALL CHANGE_IP_CUSTOMERS_AND_USERS('DELETE','Customers',billing,account,NULL);
END IF;
END LOOP;
CLOSE cur;
END IF;
END |
DELIMITER ;
The list of triggers for the tables Accounts, Nodes, Customers, Connections and AccountsAuth follow. Each trigger calls one or more of the above procedures depending on the table and the change that was made.
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_Accounts`;
CREATE TRIGGER `Trg_Insert_Accounts` AFTER INSERT ON `Accounts` FOR EACH ROW BEGIN
CALL CHANGE_IP_CUSTOMERS_AND_USERS('INSERT','Accounts',NEW.i_billing,NEW.id,NULL);
END |
DELIMITER ;
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Update_Accounts`;
CREATE TRIGGER `Trg_Update_Accounts` AFTER UPDATE ON `Accounts` FOR EACH ROW BEGIN
DECLARE todo VARCHAR(10) DEFAULT 'IGNORE';
IF NEW.i_env=1 AND OLD.i_env=1 AND (NEW.id <> OLD.id OR IFNULL(NEW.h323_password,'_PBNULL_') <> IFNULL(OLD.h323_password,'_PBNULL_')) THEN
SET todo = 'UPDATE';
ELSE
IF NEW.i_env=1 AND IFNULL(OLD.i_env,-1) <> 1 THEN
SET todo = 'INSERT';
ELSEIF IFNULL(NEW.i_env,-1) <> 1 AND OLD.i_env=1 THEN
SET todo = 'DELETE';
END IF;
END IF;
IF todo != "IGNORE" THEN
CALL CHANGE_IP_CUSTOMERS_AND_USERS(todo,'Accounts',NEW.i_billing,NEW.id,OLD.id);
END IF;
END |
DELIMITER ;
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Delete_Accounts`;
CREATE TRIGGER `Trg_Delete_Accounts` AFTER DELETE ON `Accounts` FOR EACH ROW BEGIN
CALL CHANGE_IP_CUSTOMERS_AND_USERS('DELETE','Accounts',OLD.i_billing,OLD.id,NULL);
END |
DELIMITER ;
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_Connections`|
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
CALL CHANGE_VENDORS('INSERT','Connections',NEW.i_billing,NEW.remote_ip,NULL);
END IF;
END |
DELIMITER ;
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Update_Connections`|
CREATE TRIGGER `Trg_Update_Connections` AFTER UPDATE ON `Connections` FOR EACH ROW BEGIN
DECLARE todo VARCHAR(10) DEFAULT 'IGNORE';
DECLARE exist INT default 0;
DECLARE valid INT default 0;
IF NEW.remote_ip!=OLD.remote_ip OR NEW.i_env!=OLD.i_env OR NEW.sip!=OLD.sip THEN
SET exist=IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip = OLD.remote_ip AND i_bill_auth = NEW.i_billing),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;
CALL CHANGE_VENDORS(todo,'Connections',NEW.i_billing,NEW.remote_ip,OLD.remote_ip);
END IF;
END |
DELIMITER ;
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Delete_Connections`|
CREATE TRIGGER `Trg_Delete_Connections` AFTER DELETE ON `Connections` FOR EACH ROW BEGIN
CALL CHANGE_VENDORS('DELETE','Connections',OLD.i_billing,OLD.remote_ip,NULL);
END |
DELIMITER ;
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_Nodes`|
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
CALL CHANGE_IP_CUSTOMERS_AND_USERS('INSERT','Nodes',NEW.i_billing,NEW.ip,NULL);
CALL CHANGE_VENDORS('INSERT','Nodes',NEW.i_billing,NEW.ip,NULL);
END IF;
END |
DELIMITER ;
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Update_Nodes`|
CREATE TRIGGER `Trg_Update_Nodes` AFTER UPDATE ON `Nodes` FOR EACH ROW BEGIN
DECLARE todo VARCHAR(10) DEFAULT 'IGNORE';
DECLARE exist INT default 0;
DECLARE valid INT default 0;
SET exist=IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip=OLD.ip AND is_node='Y' AND i_bill_auth=NEW.i_billing),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 THEN
IF exist = 1 AND NEW.ip != OLD.ip THEN
SET todo = 'UPDATE';
ELSE
SET todo = 'INSERT';
END IF;
ELSE
IF exist = 1 THEN
SET todo = 'DELETE';
END IF;
END IF;
CALL CHANGE_IP_CUSTOMERS_AND_USERS(todo,'Nodes',NEW.i_billing,NEW.ip,OLD.ip);
CALL CHANGE_VENDORS(todo,'Nodes',NEW.i_billing,NEW.ip,OLD.ip);
END |
DELIMITER ;
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Delete_Nodes`|
CREATE TRIGGER `Trg_Delete_Nodes` AFTER DELETE ON `Nodes` FOR EACH ROW BEGIN
IF (SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip = OLD.ip AND is_node='Y' AND i_bill_auth=OLD.i_billing) THEN
CALL CHANGE_IP_CUSTOMERS_AND_USERS('DELETE','Nodes',OLD.i_billing,OLD.ip,NULL);
CALL CHANGE_VENDORS('DELETE','Nodes',OLD.i_billing,OLD.ip,NULL);
END IF;
END |
DELIMITER ;
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Update_Customers`|
CREATE TRIGGER `Trg_Update_Customers` AFTER UPDATE ON `Customers` FOR EACH ROW BEGIN
DECLARE n_i_rep int(10) unsigned;
DECLARE o_i_rep int(10) unsigned;
DECLARE n_i_env tinyint(1) unsigned;
DECLARE o_i_env tinyint(1) unsigned;
SET n_i_rep=IFNULL(NEW.i_rep,0);
SET o_i_rep=IFNULL(OLD.i_rep,0);
SET n_i_env=IFNULL(NEW.i_env,0);
SET o_i_env=IFNULL(OLD.i_env,0);
IF o_i_rep != n_i_rep OR o_i_env != n_i_env THEN
CALL UPDATE_CUSTOMER(NEW.i_billing,NEW.i_customer,o_i_rep,n_i_rep,o_i_env,n_i_env);
END IF;
END |
DELIMITER ;
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_AccountsAuth`|
CREATE TRIGGER `Trg_Insert_AccountsAuth` AFTER INSERT ON `AccountsAuth` FOR EACH ROW BEGIN
CALL INS_ACC_USERS("INSERT", "AccountsAuth", NEW.ibillauth, NEW.accountid);
END |
DROP TRIGGER IF EXISTS `Trg_Update_AccountsAuth`|
CREATE TRIGGER `Trg_Update_AccountsAuth` AFTER UPDATE ON `AccountsAuth` FOR EACH ROW BEGIN
IF (OLD.accountid != NEW.accountid OR OLD.ibillauth != NEW.ibillauth) THEN
CALL DEL_ACC_USERS("DELETE", "AccountsAuth", OLD.ibillauth, OLD.accountid);
END IF;
CALL INS_ACC_USERS("INSERT", "AccountsAuth", NEW.ibillauth, NEW.accountid);
END |
DROP TRIGGER IF EXISTS `Trg_Delete_AccountsAuth`|
CREATE TRIGGER `Trg_Delete_AccountsAuth` AFTER DELETE ON `AccountsAuth` FOR EACH ROW BEGIN
CALL DEL_ACC_USERS("DELETE", "AccountsAuth", OLD.ibillauth, OLD.accountid);
END |
DELIMITER ;
Performance
The new version of DBA receives half of the traffic the old version used to receive. The periodic peaks in transmission also disappeared. This might be due to stopping the replication of Rates, Service_Attribute_Values and Follow_Me in PBS. There is a slight increase in outgoing traffic, as expected, due to the addition of the new fields to table sipdevices.
Fig.1: Traffic in DBA v8
Fig.2: Traffic in DBA v7
Concerning the CPU usage it seems to be the same. There isn’t a considerable difference between the two.
Fig.3: Traffic in DBA v8
Fig.4: Traffic in DBA v8
Known problems
Due to high CPU usage in PBS, the replication for tables Rates, Service_Attribute_Values and Follow_Me was stopped in that server between the entry MaSQLs and the mixer. This means that the information for these tables in DBA is not up to date and cannot be used.
When generating the MySQL configuration file, sometimes old configurations are kept in addition to the new ones (like the server_id and tables to be replicated) instead of being replaced.
The page that is monitoring each server is not correctly showing replication problems. It shows only the time difference between the transference of data from the Master. However if the execution begins to be delayed the graph still shows green.
Minor bugs
i_billing column was added to astrad.config without need. get_deny function is not used anymore.
Wish list for next versions
Allow replication from multiple Masters.
Replace the two scripts exec-registration-mX.pl by one script that retrieves the i_bill_auth for each account and sends the information to the correct Master.
Change Representatives filter by Customer Classes.
multiple_ua should be cleaned when there is only one telephone registered with the same account.
Centralize location history in one or more monitor servers. Use the new location history code that provides hourly, daily and monthly statistics and a new database that is not dropped on replication.
Resources
DBA versions (Astrad Master/Billing Slave server)
http://switzernet.com/3/public/110317-db3-versions/
Astrad/DBA schema
http://switzernet.com/3/public/111019-astrad-dba-schema/
Astrad versions
http://switzernet.com/3/public/110126-astrad-versions/
Server monitoring
http://portasip-monitor.switzernet.com/110726-monitor