contrib/scripts/retrieve_sip_conf_from_mysql.pl

   1 #!/usr/bin/perl -Tw
   2 # Retrieves the sip user/peer entries from the database
   3 # Use these commands to create the appropriate tables in MySQL
   4 #
   5 #CREATE TABLE sip (id INT(11) DEFAULT -1 NOT NULL,keyword VARCHAR(20) NOT NULL,data VARCHAR(50) NOT NULL, flags INT(1) DEFAULT 0 NOT NULL,PRIMARY KEY (id,keyword));
   6 #
   7 # if flags = 1 then the records are not included in the output file
   8
   9 use DBI;
  10 ################### BEGIN OF CONFIGURATION ####################
  11
  12 # the name of the extensions table
  13 $table_name = "sip";
  14 # the path to the extensions.conf file
  15 # WARNING: this file will be substituted by the output of this program
  16 $sip_conf = "/etc/asterisk/sip_additional.conf";
  17 # the name of the box the MySQL database is running on
  18 $hostname = "localhost";
  19 # the name of the database our tables are kept
  20 $database = "sip";
  21 # username to connect to the database
  22 $username = "root";
  23 # password to connect to the database
  24 $password = "";
  25
  26 ################### END OF CONFIGURATION #######################
  27
  28 $additional = "";
  29
  30 open EXTEN, ">$sip_conf" || die "Cannot create/overwrite extensions file: $sip_conf\n";
  31
  32 $dbh = DBI->connect("dbi:mysql:dbname=$database;host=$hostname", "$username", "$password");
  33 $statement = "SELECT keyword,data from $table_name where id=0 and keyword <> 'account' and flags <> 1";
  34 my $result = $dbh->selectall_arrayref($statement);
  35 unless ($result) {
  36   # check for errors after every single database call
  37   print "dbh->selectall_arrayref($statement) failed!\n";
  38   print "DBI::err=[$DBI::err]\n";
  39   print "DBI::errstr=[$DBI::errstr]\n";
  40   exit;
  41 }
  42 my @resultSet = @{$result};
  43 if ( $#resultSet > -1 ) {
  44         foreach $row (@{ $result }) {
  45                 my @result = @{ $row };
  46                 $additional .= $result[0]."=".$result[1]."\n";
  47         }
  48 }
  49
  50 $statement = "SELECT data,id from $table_name where keyword='account' and flags <> 1 group by data";
  51
  52 $result = $dbh->selectall_arrayref($statement);
  53 unless ($result) {
  54   # check for errors after every single database call
  55   print "dbh->selectall_arrayref($statement) failed!\n";
  56   print "DBI::err=[$DBI::err]\n";
  57   print "DBI::errstr=[$DBI::errstr]\n";
  58 }
  59
  60 @resultSet = @{$result};
  61 if ( $#resultSet == -1 ) {
  62   print "No sip accounts defined in $table_name\n";
  63   exit;
  64 }
  65
  66 foreach my $row ( @{ $result } ) {
  67         my $account = @{ $row }[0];
  68         my $id = @{ $row }[1];
  69         print EXTEN "[$account]\n";
  70         $statement = "SELECT keyword,data from $table_name where id=$id and keyword <> 'account' and flags <> 1 order by keyword";
  71         my $result = $dbh->selectall_arrayref($statement);
  72         unless ($result) {
  73                 # check for errors after every single database call
  74                 print "dbh->selectall_arrayref($statement) failed!\n";
  75                 print "DBI::err=[$DBI::err]\n";
  76                 print "DBI::errstr=[$DBI::errstr]\n";
  77                 exit;
  78         }
  79
  80         my @resSet = @{$result};
  81         if ( $#resSet == -1 ) {
  82                 print "no results\n";
  83                 exit;
  84         }
  85         foreach my $row ( @{ $result } ) {
  86                 my @result = @{ $row };
  87                 print EXTEN "$result[0]=$result[1]\n";
  88         }
  89         print EXTEN "$additional\n";
  90 }
  91
  92 exit 0;
  93