André Guimarães, 2011-11-14
Switzernet
1. Corrections
· Useragent
· Sipdevices table
· New tables
3. Wish list for next versions
5. Resources
This version is only
compatible with Astrad versions 8, 9 and 10.
Corrections
This update corrects the problem detected in previous DBA version in procedure UPDATE_CUSTOMER that doesn't include all representative values. Due to that not all of customers were propagated to Astrads.
DROP PROCEDURE IF EXISTS `UPDATE_CUSTOMER`|
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,17) THEN
IF ienv=1 THEN
IF account REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
IF (SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip = account AND is_node='N') THEN
DELETE FROM `astrad`.ip_customers WHERE remote_ip = account AND is_node='N';
INSERT INTO `astrad`.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 `astrad`.users WHERE username = account) THEN
DELETE FROM `astrad`.users WHERE username = account;
INSERT INTO `astrad`.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 `astrad`.ip_customers (remote_ip,is_node) values (account,'N');
INSERT INTO `astrad`.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 `astrad`.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 |
DELIMITER ;
New functionalities
Useragent:
Support for field Useragent that Astrad v10 now updates. A new field useragent was added to location2 and registration (table used by script exec-registration.pl). To maintain compatibility between DBA versions the script does an update of the useragent field instead of writing it in the insert. On DBAs without this field only the UPDATE query fails. In an Astrad V008 the procedure used is Register while on newer versions it is used Register2 (which receives the user agent as an additional parameter).
These changes required modifications in:
- Script /etc/dba/exec-registration.pl
- Database astrad:
ALTER TABLE location2 ADD COLUMN useragent varchar(20) default NULL;
ALTER TABLE registration ADD COLUMN useragent varchar(20) default NULL;
--
-- Dumping triggers for table `location2`
--
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_location2`|
CREATE TRIGGER `Trg_Insert_location2` AFTER INSERT ON `location2` FOR EACH ROW BEGIN
DECLARE IPAD varchar(15) DEFAULT '';
SET IPAD=(SELECT value FROM config WHERE name='ipaddr');
IF NEW.ipaddr IS NOT NULL AND NEW.port IS NOT NULL AND NEW.expires IS NOT NULL AND NEW.dba=IPAD THEN
INSERT INTO registration (username,dba,domain,ipaddr,port,expires,register,useragent,action) VALUES (NEW.username,NEW.dba,NEW.domain,NEW.ipaddr,NEW.port,NEW.expires,NEW.register,NEW.useragent,'REGISTER');
END IF;
CALL add_multiple_ua(NEW.username,NEW.domain,NEW.ipaddr,NEW.port,"ADD");
END |
--
DROP TRIGGER IF EXISTS `Trg_Update_location2`|
CREATE TRIGGER `Trg_Update_location2` AFTER UPDATE ON `location2` FOR EACH ROW BEGIN
DECLARE IPAD varchar(15) DEFAULT '';
SET IPAD=(SELECT value FROM config WHERE name='ipaddr');
IF NEW.ipaddr IS NOT NULL AND NEW.port IS NOT NULL AND NEW.expires IS NOT NULL AND NEW.dba=IPAD THEN
INSERT INTO registration (username,dba,domain,ipaddr,port,expires,register,useragent,action) VALUES (NEW.username,NEW.dba,NEW.domain,NEW.ipaddr,NEW.port,NEW.expires,NEW.register,NEW.useragent,'REGISTER');
END IF;
END |
--
DROP TRIGGER IF EXISTS `Trg_Delete_location2`|
CREATE TRIGGER `Trg_Delete_location2` AFTER DELETE ON `location2` FOR EACH ROW BEGIN
DECLARE IPAD varchar(15) DEFAULT '';
SET IPAD=(SELECT value FROM config WHERE name='ipaddr');
IF OLD.dba=IPAD THEN
INSERT INTO registration (username,dba,domain,ipaddr,port,expires,register,useragent,action) VALUES (OLD.username,OLD.dba,OLD.domain,OLD.ipaddr,OLD.port,OLD.expires,OLD.register,OLD.useragent,'UNREGISTER');
END IF;
CALL add_multiple_ua(OLD.username,OLD.domain,OLD.ipaddr,OLD.port,"DEL");
END |
DROP PROCEDURE IF EXISTS `REGISTER2`|
CREATE PROCEDURE `REGISTER2`(IN USR VARCHAR(80),IN DOM VARCHAR(15),IN IP_ VARCHAR(15), IN PORT_ smallint(5) unsigned, IN EXR DATETIME,IN REG DATETIME,IN UA_ VARCHAR(20))
BEGIN
DECLARE MAXD INT(5) DEFAULT 0;
DECLARE MAXI INT(5) DEFAULT 0;
DECLARE IPAD varchar(15) DEFAULT '';
SET IPAD=(SELECT value FROM config WHERE name='ipaddr');
IF (UA_ is NULL) THEN
SET UA_='Not Available';
END IF;
IF (SELECT 1 FROM location2 WHERE username=USR AND ipaddr=IP_ AND port=PORT_ AND domain=DOM AND dba=IPAD) THEN
UPDATE location2 SET expires=EXR,register=REG WHERE username=USR AND ipaddr=IP_ AND port=PORT_ AND domain=DOM AND dba=IPAD;
ELSE
INSERT INTO location2 (username,domain,dba,ipaddr,port,expires,register,useragent) VALUES (USR,DOM,IPAD,IP_,PORT_,EXR,REG,UA_);
END IF;
INSERT INTO location2_history (start,stop,domain,username,ipaddr,port,count) VALUES (REG,REG,DOM,USR,IP_,PORT_,1) ON DUPLICATE KEY UPDATE count=count+1, stop=REG;
SET MAXI=(SELECT value FROM config WHERE name='max_reg_to_check');
IF (SELECT value FROM config WHERE name='tmp_reg_to_check') < MAXI THEN
UPDATE config SET value=value+1 WHERE name='tmp_reg_to_check';
ELSE
UPDATE config SET value=1 WHERE name='tmp_reg_to_check';
SET MAXD=(SELECT value FROM config WHERE name='day_log_history');
DELETE FROM location2_history WHERE stop < DATE_ADD(NOW(), INTERVAL -MAXD DAY);
END IF;
END |
DELIMITER ;
Sipdevices table:
Created new registration table sipdevices for Astrad v10 that joins sippeers and sipusers in the same table, decreasing the amount of data transferred to the Astrads. Also this was required by the new asterisk version that exists in Astrad v10. To maintain compatibility with previous versions the tables sippeers and sipusers are kept for now. The triggers were changed to write also in this new table besides sippeers and sipusers. The changes in DB Astrad can be seen here.
New tables:
Rates, Destinations, Service_Attribute_Values, Active_Calls and Follow_Me are now replicated from Billing Master. These tables are needed by the new Astrad version to replicate the functions available with Radius when Master is not contactable. These tables will be used to provide more autonomy to each Astrad from the state of the Billing Master and even from DBAs. In /etc/mysql/my.cnf the following lines were added:
[mysqld]
…
master-user = astrob
binlog-do-db = asterisk
replicate-do-table = porta-billing.Customers
replicate-do-table = porta-billing.Accounts
replicate-do-table = porta-billing.Nodes
replicate-do-table = porta-billing.Connections
replicate-do-table = porta-billing.Rates
replicate-do-table = porta-billing.Destinations
replicate-do-table = porta-billing.Service_Attribute_Values
replicate-do-table = porta-billing.Active_Calls
replicate-do-table = porta-billing.Follow_Me
replicate-wild-do-table = porta-billing.Customers
replicate-wild-do-table = porta-billing.Accounts
replicate-wild-do-table = porta-billing.Nodes
replicate-wild-do-table = porta-billing.Connections
replicate-wild-do-table = porta-billing.Rates
replicate-wild-do-table = porta-billing.Destinations
replicate-wild-do-table = porta-billing.Service_Attribute_Values
replicate-wild-do-table = porta-billing.Active_Calls
replicate-wild-do-table = porta-billing.Follow_Me
slave-skip-errors = all
SQL used to create tables can be seen here.
Installation options
It was added to the setup-replication script the option to choose starting the replication from the Master, from a PBS, a DBA or dump files. The last three options will prevent the locking of tables while making the database dump. The procedure is as follows:
- If replication from another server: stop replication on server from where will read the data, read replication state of this DBA, dump desired tables and restart replication. Configure replication on new DBA, stop replication, recreate table structure, import dump, change replication state to the same point of the server from where the dump was made and restart replication.
- If replication is made from file: Configure replication on new DBA, stop replication, recreate table structure, import dump from zipped file with configurations (this file is created when doing a dump from another DBA), read replication information from zipped file and set replication state to that point and restart replication.
Wish list for next versions
· Add triggers to the new tables Rates, Destinations, Service_Attribute_Values, Active_Calls and Follow_me to create simplified versions of its contents. These simplified tables will then be replicated to the Astrads, allowing them to authenticate and make all decisions based on the information on their DB without having to connect to the Billing Master or a DBA. The Rates and Destinations tables for instance should be very simplified, instead of the thousands entries it should have the minimum entries possible (maybe prefix, order of servers to this prefix and respective cost).
· Prevent location replication scripts from exiting when a DBA server or the Billing Master are down. At the moment if a server is down the replication scripts will exit. This prevents the location2 table to be update in the working servers as well as multiple_ua table. Also the table registration starts to fill with queries and when finally the problematic server is up it may take up to half an hour to the changes inside this table to propagate.
· Remove from multiple_ua and location2 tables all phone registration information that has already expired
· There should be a table with active calls in all Astrad servers (and porta-sip if possible).
· Create a table with only the registered telephones in each Astrad. At the moment each Astrad has a list of all customers. Maybe using a guest account we can know what it is the login of the customer and then add that login to the table of sipdevices (see autocreatepeer, allowguest, regcontext, asterisk manager). We can also modify asterisk to add a request to DBA to add the username if a registration fails (it would contact DBA, ask if the username exists and if so add it to sipdevices table).
Other changes
The rules in puppet where modified to prevent updates of Perl CPAN after the installation. This updates where automatically executed each puppet cycle if the DBA module wasn’t commented. It is possible now to leave the module active and if there are changes in configurations they will propagate automatically.
The configuration script was modified to allow full replication of any database. This is not of interest to a DBA, but a modified version of the script can be used to make a full Billing Master replica.
It was added a field to the BD with the DBA version.
Resources
DBA versions (Astrad Master/Billing Slave server)
http://switzernet.com/3/public/110317-db3-versions/
Merging SIPPeers and SIPUsers table
http://ftp.switzernet.com/3/public/111102-mergingsippeersandsipusers/
Astrad/DBA schema
http://switzernet.com/3/public/111019-astrad-dba-schema/
Astrad versions