Astrad Version 10

André Guimarães, 2011-11-29

Switzernet

 

This Astrad version requires version 6 of DBA.

 

Changes and new functionalities

Puppet

Changes in replication

Changes in /usr/share/asterisk/agi-bin/agi-rad-auth.pl

[Download /usr/share/asterisk/agi-bin/agi-rad-auth.pl]

 

Changes in /etc/asterisk/extensions.conf

 

Example:

When h323-return-code is not defined these tests

 

exten => _[*0-9]!,n,GotoIf($[ ${h323-return-code} = 2 ]?wrong_passwd,${EXTEN},1)

exten => _[*0-9]!,n,GotoIf($[ ${h323-return-code} = 3 ]?in_use,${EXTEN},1)

exten => _[*0-9]!,n,GotoIf($[ ${h323-return-code} = 4 ]?zero_balance,${EXTEN},1)

exten => _[*0-9]!,n,GotoIf($[ ${h323-return-code} = 5 ]?account_expired,${EXTEN},1)

exten => _[*0-9]!,n,GotoIf($[ ${h323-return-code} = 6 ]?credit_limit,${EXTEN},1)

exten => _[*0-9]!,n,GotoIf($[ ${h323-return-code} = 7 ]?user_denied,${EXTEN},1)

exten => _[*0-9]!,n,GotoIf($[ ${h323-return-code} = 8 ]?not_avail,${EXTEN},1)

exten => _[*0-9]!,n,GotoIf($[ ${h323-return-code} = 9 ]?no-route-exists-to-dest,${EXTEN},1)

exten => _[*0-9]!,n,GotoIf($[ ${h323-return-code} = 10 ]?retries,${EXTEN},1)

exten => _[*0-9]!,n,GotoIf($[ ${h323-return-code} = 12 ]?insufficient_balance,${EXTEN},1)

exten => _[*0-9]!,n,GotoIf($[ ${h323-return-code} = 0 | ${h323-return-code} = 13 ]?welcome-msg,${EXTEN},1)

 

give the following warnings:

 

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 1 #012  ^

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:411 in ast_yyerror: If you have questions, please refer to doc/channelvariables.txt in the asterisk source.

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 2 #012  ^

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:411 in ast_yyerror: If you have questions, please refer to doc/channelvariables.txt in the asterisk source.

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 3 #012  ^

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:411 in ast_yyerror: If you have questions, please refer to doc/channelvariables.txt in the asterisk source.

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 4 #012  ^

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:411 in ast_yyerror: If you have questions, please refer to doc/channelvariables.txt in the asterisk source.

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 5 #012  ^

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:411 in ast_yyerror: If you have questions, please refer to doc/channelvariables.txt in the asterisk source.

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 6 #012  ^

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:411 in ast_yyerror: If you have questions, please refer to doc/channelvariables.txt in the asterisk source.

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 7 #012  ^

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:411 in ast_yyerror: If you have questions, please refer to doc/channelvariables.txt in the asterisk source.

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 8 #012  ^

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:411 in ast_yyerror: If you have questions, please refer to doc/channelvariables.txt in the asterisk source.

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 9 #012  ^

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:411 in ast_yyerror: If you have questions, please refer to doc/channelvariables.txt in the asterisk source.

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 10 #012  ^

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:411 in ast_yyerror: If you have questions, please refer to doc/channelvariables.txt in the asterisk source.

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 12 #012  ^

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:411 in ast_yyerror: If you have questions, please refer to doc/channelvariables.txt in the asterisk source.

Oct  3 15:18:46 astrad5 asterisk[3667]: WARNING[5584]: ast_expr2.fl:407 in ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '=', expecting $end; Input:#012  = 0 |  = 13 #012  ^

 

Changes in /etc/asterisk/modules.conf

 

 

Nov 29 15:14:32 astrad asterisk[12610]: ERROR[12837]: app_amd.c:361 in load_config: Configuration file amd.conf missing.

Nov 29 15:14:32 astrad asterisk[12610]: ERROR[12837]: chan_zap.c:12645 in setup_zap: Unable to load config zapata.conf

Nov 29 15:14:46 astrad asterisk[12934]: WARNING[12934]: res_odbc.c:228 in load_odbc_config: Unable to load config file res_odbc.conf

Nov 29 15:14:46 astrad asterisk[12934]: WARNING[12934]: cdr_odbc.c:256 in odbc_load_module: cdr_odbc: Unable to load config for ODBC CDR's: cdr_odbc.conf

Nov 29 15:14:46 astrad asterisk[12934]: ERROR[12934]: chan_phone.c:1335 in load_module: Unable to load config phone.conf

Nov 29 15:14:46 astrad asterisk[12934]: WARNING[12934]: app_festival.c:546 in load_module: No such configuration file festival.conf

Nov 29 15:14:46 astrad asterisk[12934]: NOTICE[12934]: chan_agent.c:1084 in read_agent_config: No agent configuration found -- agent support disabled

Nov 29 15:14:46 astrad asterisk[12934]: ERROR[12934]: pbx_dundi.c:4377 in set_config: Unable to load config dundi.conf

Nov 29 15:14:46 astrad asterisk[12934]: WARNING[12934]: app_followme.c:303 in reload_followme: No follow me config file (followme.conf), so no follow me

Nov 29 15:14:46 astrad asterisk[12934]: NOTICE[12934]: func_odbc.c:548 in odbc_load_module: Unable to load config for func_odbc: func_odbc.conf

Nov 29 15:14:46 astrad asterisk[12934]: WARNING[12934]: cdr_pgsql.c:302 in my_load_module: Unable to load config for PostgreSQL CDR's: cdr_pgsql.conf

Nov 29 15:14:46 astrad asterisk[12934]: NOTICE[12934]: chan_oss.c:1836 in load_module: Unable to load config oss.conf

Nov 29 15:14:46 astrad asterisk[12934]: NOTICE[12934]: pbx_ael.c:4131 in pbx_load_module: Starting AEL load process.

Nov 29 15:14:46 astrad asterisk[12934]: NOTICE[12934]: pbx_ael.c:4138 in pbx_load_module: AEL load process: calculated config file name '/etc/asterisk/extensions.ael'.

Nov 29 15:14:46 astrad asterisk[12934]: NOTICE[12934]: pbx_ael.c:4141 in pbx_load_module: File /etc/asterisk/extensions.ael not found; AEL declining load

 

To prevent loading the unconfigured modules the following lines were added to /etc/asterisk/modules.conf:

 

noload => app_voicemail.so

noload => chan_zap.so

noload => app_zapbarge.so

noload => app_zapateller.so

noload => app_zapscan.so

noload => app_zapras.so

noload => app_amd.so

noload => res_odbc

noload => cdr_odbc

noload => chan_phone

noload => app_festival

noload => chan_agent

noload => pbx_dundi

noload => app_followme

noload => func_odbc

noload => cdr_pgsql

noload => chan_oss

noload => pbx_ael

Database changes

[Download SQL]

ALTER TABLE sippeers2 ADD COLUMN `fromuser` varchar(80) DEFAULT NULL;

ALTER TABLE sippeers2 ADD COLUMN `trustrpid` enum('yes','no') DEFAULT NULL;

ALTER TABLE sippeers2 ADD COLUMN `sendrpid` enum('yes','no') DEFAULT NULL;

ALTER TABLE sippeers2 ADD COLUMN `useragent` varchar(20) DEFAULT 'Unknown';

 

--

-- Triggers of  table `sippeers`

--

 

DELIMITER |

DROP TRIGGER IF EXISTS `Trg_Insert_sippeers`|

CREATE TRIGGER `Trg_Insert_sippeers` AFTER INSERT ON `sippeers` FOR EACH ROW BEGIN

DECLARE devicetrustrpid enum('yes','no') DEFAULT NULL;

DECLARE devicesendrpid enum('yes','no') DEFAULT NULL;

DECLARE devicefromuser varchar(80) DEFAULT NULL;

IF NEW.context='fromnode' THEN

  SET devicetrustrpid='yes';

  SET devicesendrpid='yes';

  SET devicefromuser='nodes';

END IF;

INSERT IGNORE INTO sippeers2 (name,host,port,context,insecure,md5secret,trustrpid,sendrpid,fromuser) VALUES (NEW.name,NEW.host,NEW.port,NEW.context,NEW.insecure,NEW.md5secret,devicetrustrpid,devicesendrpid,devicefromuser);

END |

 

DROP TRIGGER IF EXISTS `Trg_Update_sippeers`|

CREATE TRIGGER `Trg_Update_sippeers` AFTER UPDATE ON `sippeers` FOR EACH ROW BEGIN

DECLARE devicetrustrpid enum('yes','no') DEFAULT NULL;

DECLARE devicesendrpid enum('yes','no') DEFAULT NULL;

DECLARE devicefromuser varchar(80) DEFAULT NULL;

IF NEW.context='fromnode' THEN

  SET devicetrustrpid='yes';

  SET devicesendrpid='yes';

  SET devicefromuser='nodes';

END IF;

IF NEW.port IS NOT NULL THEN

  UPDATE sippeers2 SET name=NEW.name, host=NEW.host, port=NEW.port, context=NEW.context, insecure=NEW.insecure, md5secret=NEW.md5secret, trustrpid=devicetrustrpid, sendrpid=devicesendrpid, fromuser=devicefromuser WHERE name=OLD.name;

ELSE

  UPDATE sippeers2 SET name=NEW.name, host=NEW.host, context=NEW.context, insecure=NEW.insecure, md5secret=NEW.md5secret, trustrpid=devicetrustrpid, sendrpid=devicesendrpid, fromuser=devicefromuser WHERE name=OLD.name;

END IF;

END |

 

DROP TRIGGER IF EXISTS `Trg_Delete_sippeers`|

CREATE TRIGGER `Trg_Delete_sippeers` AFTER DELETE ON `sippeers` FOR EACH ROW BEGIN

DELETE FROM sippeers2 WHERE name=OLD.name;

END |

 

DELIMITER ;

--

-- Trigger of  table `sippeers2`

--

 

DELIMITER |

DROP TRIGGER IF EXISTS `Trg_Update_sippeers2`|

CREATE TRIGGER `Trg_Update_sippeers2` AFTER UPDATE ON `sippeers2` FOR EACH ROW BEGIN

IF NEW.regseconds IS NOT NULL AND NEW.port IS NOT NULL AND NEW.ipaddr IS NOT NULL AND NEW.name IS NOT NULL AND FROM_UNIXTIME(NEW.regseconds) > (NOW() - INTERVAL 5 SECOND) AND NEW.ipaddr NOT LIKE '0.0.0.0' AND NEW.port != 0 THEN

  CALL REGISTER (NEW.name,NEW.ipaddr,NEW.port,FROM_UNIXTIME(NEW.regseconds),NEW.useragent);

END IF;

END |

DELIMITER ;

 

--

-- Procedure REGISTER

--

 

DELIMITER |

DROP PROCEDURE IF EXISTS `REGISTER`|

CREATE PROCEDURE `REGISTER`(IN USR VARCHAR(80), IN IP_ VARCHAR(15), IN PORT_ smallint(5) unsigned, IN EXR DATETIME, IN UA_ VARCHAR(20))

BEGIN

IF (PORT_ != 0 && IP_ != '(null)') THEN

  IF (SELECT 1 FROM location3 WHERE username=USR AND ipaddr=IP_ AND port=PORT_) THEN

    UPDATE location3 SET expires=EXR WHERE username=USR AND ipaddr=IP_ AND port=PORT_ AND useragent=UA_;

  ELSE

    INSERT INTO location3 (username,ipaddr,port,expires,useragent) VALUES (USR,IP_,PORT_,EXR,UA_);

  END IF;

END IF;

END |

DELIMITER ;

 

--

-- Table structure for table `location3`

--

 

DROP TABLE IF EXISTS `location3`;

CREATE TABLE `location3` (

  `username` varchar(80) NOT NULL default '',

  `ipaddr` varchar(15) default NULL,

  `port` smallint(5) unsigned default NULL,

  `expires` datetime default NULL,

  `register` timestamp(14) NOT NULL,

  `useragent` varchar(20) DEFAULT NULL,

  PRIMARY KEY  (`username`,`ipaddr`,`port`)

) ;

 

--

-- Trigger of table `location3`

--

 

DELIMITER |

DROP TRIGGER IF EXISTS `Trg_Insert_location3`|

CREATE TRIGGER `Trg_Insert_location3` AFTER INSERT ON `location3` FOR EACH ROW BEGIN

DECLARE IP  VARCHAR(15)  DEFAULT '';

IF NEW.ipaddr IS NOT NULL AND NEW.port IS NOT NULL AND NEW.expires IS NOT NULL THEN

  SET IP=(SELECT value FROM sipconfig WHERE name='ipaddr');

  INSERT INTO registration (username,domain,ipaddr,port,expires,register,useragent,action) VALUES (NEW.username,IP,NEW.ipaddr,NEW.port,NEW.expires,NEW.register,NEW.useragent,'REGISTER');

END IF;

END |

 

--

 

DROP TRIGGER IF EXISTS `Trg_Update_location3`|

CREATE TRIGGER `Trg_Update_location3` AFTER UPDATE ON `location3` FOR EACH ROW BEGIN

DECLARE IP  VARCHAR(15)  DEFAULT '';

IF NEW.ipaddr IS NOT NULL AND NEW.port IS NOT NULL AND NEW.expires IS NOT NULL THEN

  SET IP=(SELECT value FROM sipconfig WHERE name='ipaddr');

  INSERT INTO registration (username,domain,ipaddr,port,expires,register,useragent,action) VALUES (NEW.username,IP,NEW.ipaddr,NEW.port,NEW.expires,NEW.register,NEW.useragent,'REGISTER');

END IF;

END |

 

--

 

DROP TRIGGER IF EXISTS `Trg_Delete_location3`|

CREATE TRIGGER `Trg_Delete_location3` AFTER DELETE ON `location3` FOR EACH ROW BEGIN

DECLARE IP  VARCHAR(15)  DEFAULT '';

SET IP=(SELECT value FROM sipconfig WHERE name='ipaddr');

INSERT INTO registration (username,domain,ipaddr,port,expires,useragent,action) VALUES (OLD.username,IP,OLD.ipaddr,OLD.port,OLD.expires,OLD.useragent,'UNREGISTER');

END |

DELIMITER ;

 

--

-- Table structure for table `registration`

--

 

DROP TABLE IF EXISTS `registration`;

CREATE TABLE `registration` (

  `id`  int(11) NOT NULL auto_increment,

  `username` varchar(80) NOT NULL default '',

  `domain` varchar(15) NOT NULL default '',

  `ipaddr` varchar(15) default NULL,

  `port` smallint(5) unsigned default NULL,

  `expires` datetime default NULL,

  `register` datetime default NULL,

  `useragent` varchar(20) DEFAULT NULL,

  `action`  varchar(15) default 'REGISTER',

  PRIMARY KEY  (`id`)

) ;

--

-- PROCEDURE set_route

--

 

DELIMITER |

DROP PROCEDURE IF EXISTS `set_route`|

CREATE PROCEDURE `set_route`(IN USR VARCHAR(80), IN ipsource VARCHAR(15))

BEGIN

DECLARE ROUTE text DEFAULT '';

DECLARE done INT DEFAULT 0;

DECLARE tdomain varchar(15) default NULL;

DECLARE tipaddr varchar(15) default NULL;

DECLARE tport smallint(5) unsigned default NULL;

DECLARE prev_ipaddr varchar(15) default NULL;

DECLARE prev_port smallint(5) unsigned default NULL;

DECLARE fg  INT DEFAULT 0;

DECLARE ID_ INT DEFAULT 0;

DECLARE myIP VARCHAR(15);

DECLARE local_call INT DEFAULT 0;

DECLARE cur CURSOR FOR SELECT domain,ipaddr,port FROM multiple_ua WHERE username=USR;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

DROP TABLE IF EXISTS tmpdomain;

CREATE TEMPORARY TABLE tmpdomain (

  domain varchar(15) NOT NULL DEFAULT '' ,

  PRIMARY KEY  (`domain`)

);

DELETE FROM route_multiple_ua WHERE username=USR;

IF (SELECT 1 FROM  multiple_ua WHERE domain=ipsource AND username=USR LIMIT 1) THEN

  SET local_call=1;

END IF;

  SET myIP=(SELECT value FROM sipconfig WHERE name='ipaddr');

  SET ID_=(SELECT value FROM sipconfig WHERE name='ua_id');

  OPEN cur;

  read_loop: LOOP

  FETCH cur INTO tdomain,tipaddr,tport;

  IF done THEN

    LEAVE read_loop;

  END IF;

  IF (tdomain=myIP) THEN

    IF (fg=0) THEN

      SET fg=1;

      SET prev_ipaddr=tipaddr;

      SET prev_port=tport;

    ELSEIF (fg=1) THEN

      SET fg=2;

      INSERT INTO sippeers2 (name,host,ipaddr,port) VALUES (CONCAT('UA',ID_),prev_ipaddr,prev_ipaddr,prev_port) ON DUPLICATE KEY UPDATE host=prev_ipaddr, ipaddr=prev_ipaddr, port=prev_port;

      SET ROUTE=CONCAT(ROUTE,'&SIP/',USR,'@UA',ID_);

      SET ID_=ID_+1;

      IF (ID_>1000) THEN

        SET ID_=1;

      END IF;

      INSERT INTO sippeers2 (name,host,ipaddr,port) VALUES (CONCAT('UA',ID_),tipaddr,tipaddr,tport) ON DUPLICATE KEY UPDATE host=tipaddr, ipaddr=tipaddr, port=tport;

      SET ROUTE=CONCAT(ROUTE,'&SIP/',USR,'@UA',ID_);

      SET ID_=ID_+1;

      IF (ID_>1000) THEN

        SET ID_=1;

      END IF;

    ELSE

      INSERT INTO sippeers2 (name,host,ipaddr,port) VALUES (CONCAT('UA',ID_),tipaddr,tipaddr,tport) ON DUPLICATE KEY UPDATE host=tipaddr, ipaddr=tipaddr, port=tport;

      SET ROUTE=CONCAT(ROUTE,'&SIP/',USR,'@UA',ID_);

      SET ID_=ID_+1;

      IF (ID_>1000) THEN

        SET ID_=1;

      END IF;

    END IF;

  ELSEIF local_call=0 AND (SELECT 1 FROM tmpdomain WHERE domain=tdomain LIMIT 1) IS NULL THEN

    SET ROUTE=CONCAT(ROUTE,'&SIP/',USR,'@',tdomain);

    INSERT INTO tmpdomain VALUES (tdomain);

  END IF;

  END LOOP;

  CLOSE cur;

  IF (fg=1) THEN

    SET ROUTE=CONCAT(ROUTE,'&SIP/',USR);

  ELSE

    UPDATE sipconfig SET value=ID_ WHERE name='ua_id';

  END IF;

  IF ROUTE NOT LIKE '' THEN

    INSERT IGNORE INTO route_multiple_ua VALUES (USR,SUBSTRING(ROUTE,2));

  END IF;

END |

DELIMITER ;

 

mysql> SELECT * FROM location3 WHERE username="41215502419";

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

| username    | ipaddr       | port  | expires             | register            | useragent            |

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

| 41215502419 | (null)       |     0 | 2011-11-21 14:45:50 | 2011-11-21 14:44:50 | X-Lite 4 release 4.1 | <= This line doesn’t appear now

| 41215502419 | 212.147.8.99 | 62093 | 2011-11-21 14:45:50 | 2011-11-21 14:44:50 | X-Lite 4 release 4.1 |

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

Problems detected / Wishlist

 

Correct the following problems/add features:

References

 

Master Mysql Astrad DBA V006

http://switzernet.com/3/public/111114-dba-v6/

 

Master MySQL-Astrad versions (DBA)

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

 

Astrad Versioning

http://switzernet.com/3/public/110126-astrad-versions/

 

List of functionalities to add to Astrad

http://switzernet.com/3/public/110523-astrad-wish-list/