Changeset 9238

Show
Ignore:
Timestamp:
03/16/10 18:55:56 (2 years ago)
Author:
p_lindheimer
Message:

migration to add dahdi tables and move trunks_dialpatterns re #4135 and re #4143 and re #4144

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • freepbx/trunk/upgrades/2.8.0alpha2/tables.php

    r9213 r9238  
    1111$autoincrement = (($amp_conf["AMPDBENGINE"] == "sqlite") || ($amp_conf["AMPDBENGINE"] == "sqlite3")) ? "AUTOINCREMENT":"AUTO_INCREMENT"; 
    1212 
    13 $outbound_routes = " 
    14 CREATE TABLE outbound_routes ( 
    15   `route_id` INTEGER NOT NULL PRIMARY KEY $autoincrement, 
    16   `name` VARCHAR( 40 ), 
    17   `outcid` VARCHAR( 40 ), 
    18   `outcid_mode` VARCHAR( 20 ), 
    19   `password` VARCHAR( 30 ), 
    20   `emergency_route` VARCHAR( 4 ), 
    21   `intracompany_route` VARCHAR( 4 ), 
    22   `mohclass` VARCHAR( 80 ), 
    23   `time_group_id` INTEGER DEFAULT NULL 
    24 ) 
     13$dadhi_table = " 
     14CREATE TABLE `dadhi` ( 
     15  `id` varchar(20) NOT NULL default '-1', 
     16  `keyword` varchar(30) NOT NULL default '', 
     17  `data` varchar(255) NOT NULL default '', 
     18  `flags` int(1) NOT NULL default '0', 
     19  PRIMARY KEY  (`id`,`keyword`) 
     20); 
    2521"; 
    2622 
    27 $outbound_route_patterns = " 
    28 CREATE TABLE outbound_route_patterns ( 
    29   `route_id` INTEGER NOT NULL, 
    30   `match_pattern_prefix` VARCHAR( 60 ), 
    31   `match_pattern_pass` VARCHAR( 60 ), 
    32   `match_cid` VARCHAR( 60 ), 
    33   `prepend_digits` VARCHAR( 100 ), 
    34   PRIMARY KEY (`route_id`, `match_pattern_prefix`, `match_pattern_pass`,`match_cid`,`prepend_digits`) 
    35 ) 
    36 "; 
    37  
    38 $outbound_route_trunks = " 
    39 CREATE TABLE outbound_route_trunks ( 
    40   `route_id` INTEGER NOT NULL, 
    41   `trunk_id` INTEGER NOT NULL, 
    42   `seq` INTEGER NOT NULL, 
    43   PRIMARY KEY  (`route_id`, `trunk_id`, `seq`)  
    44 ) 
    45 "; 
    46  
    47 $outbound_route_sequence = " 
    48 CREATE TABLE outbound_route_sequence ( 
    49   `route_id` INTEGER NOT NULL, 
    50   `seq` INTEGER NOT NULL, 
    51   PRIMARY KEY  (`route_id`, `seq`)  
    52 ) 
    53 "; 
    54  
    55 outn("Create new outbound_routes table.. "); 
    56 $result = $db->query($outbound_routes); 
     23outn("Create dahdi table.."); 
     24$result = $db->query($dahdi_table); 
    5725if (DB::IsError($result) && $result->getCode() == DB_ERROR_ALREADY_EXISTS ) { 
    58   out("Table exists, skipping migration"); 
     26  out("already exists"); 
    5927} elseif (DB::IsError($result)) { 
    60   out("failed, FATAL Error"); 
     28  out("FAILED..not created"); 
    6129  out($result->getMessage()); 
    6230} else { 
    6331  out("ok"); 
    64   outn("create outbound_route_patterns.. "); 
    65   $result = $db->query($outbound_route_patterns); 
    66   if (DB::IsError($result) && $result->getCode() != DB_ERROR_ALREADY_EXISTS ) { 
    67     out("failed, FATAL Error"); 
    68     out($result->getDebugInfo()); 
     32} 
     33 
     34$trunk_dialpatterns = " 
     35CREATE TABLE `trunk_dialpatterns`  
     36(  
     37  `trunkid` INTEGER, 
     38  `match_pattern_prefix` VARCHAR(50) NULL,  
     39  `match_pattern_pass` VARCHAR(50) NULL,  
     40  `prepend_digits` VARCHAR(50) NULL, 
     41  `seq` INTEGER, 
     42  PRIMARY KEY  (`trunkid`, `match_pattern_prefix`, `match_pattern_pass`, `prepend_digits`, `seq`)  
     43)  
     44"; 
     45outn(_("Checking if trunk_dialpatterns table exists..")); 
     46$check = $db->query($trunk_dialpatterns); 
     47if(DB::IsError($check) && $check->getCode() != DB_ERROR_ALREADY_EXISTS) { 
     48  die_freepbx(_("Can not create trunk_dialpatterns table").$check->getDebugInfo()); 
     49} else if(DB::IsError($check) && $check->getCode() == DB_ERROR_ALREADY_EXISTS) { 
     50  out(_("already exists")); 
     51} else { 
     52  out(_("created")); 
     53  outn(_("migrating rules..")); 
     54  $patterns = sql('SELECT * FROM trunks_dialpatterns','getAll',DB_FETCHMODE_ASSOC); 
     55  $patterns_insert = array(); 
     56  foreach ($patterns as $key => $pattern_rec) { 
     57    $pattern = $pattern_rec['rule']; 
     58    $trunkid = $pattern_rec['trunkid']; 
     59    $seq = $pattern_rec['seq']; 
     60 
     61    // convert x n and z to uppercase 
     62    $regex = str_replace(array('x','n','z'), array('X','N','Z'), $pattern); 
     63    // sanitize the pattern - remove any non-pattern chars 
     64    $regex = preg_replace("/[^0-9XNZwW#*\.\[\]\-\+\|]/", "", $regex); 
     65    // Also kill the '-' characters outside of groups 
     66    $regex = preg_replace("/((?:\[[^\]]*\])*)([^\[\]\-]*)-?/", "$1$2", $regex); 
     67 
     68    if (preg_match('/^(([0-9XNZwW#*\.\[\]\-]+)\|)?(([0-9XNZwW#*\.\[\]\-]+)\+)?([0-9XNZwW#*\.\[\]\-]*)$/', $regex, $matches)) { 
     69      // one of NXXXXXX, 613|NXXXXXX   1+NXXXXXX    613|1+NXXXXXX,   
     70      // matches[2] = drop (eg 613),  matches[4] = prepend_digits (eg 1),  matches[5] = rest of number (eg NXXXXX) 
     71 
     72      $match_pattern_prefix = $matches[2]; 
     73      $prepend_digits = $matches[4]; 
     74      $match_pattern_pass = $matches[5]; 
     75    } else if (preg_match('/^(([0-9XNZwW#*\.\[\]\-]+)\+)?(([0-9XNZwW#*\.\[\]\-]+)\|)?([0-9XNZwW#*\.\[\]\-]*)$/', $regex, $matches)) { 
     76      // one of NXXXXXX,  613|NXXXXXX   1+NXXXXXX    1+613|NXXXXXX 
     77      // matches[2] = prepend_digits (eg 1),  matches[4] = drop (eg 613),  matches[5] = rest of number (eg NXXXXX) 
     78 
     79      $match_pattern_prefix = $matches[4]; 
     80      $prepend_digits = $matches[2]; 
     81      $match_pattern_pass = $matches[5]; 
     82    } else { 
     83      // UNRECOGNIZED PATTERN 
     84      out(sprintf(_("unrecognized rule: %s discarding"),$pattern)); 
     85      outn(_("continuing..")); 
     86    } 
     87    $patterns[$key]['match_pattern_prefix'] = $match_pattern_prefix; 
     88    $patterns[$key]['match_pattern_pass'] = $match_pattern_pass; 
     89    $patterns[$key]['prepend_digits'] = $prepend_digits; 
     90 
     91    $patterns_insert[] = array($trunkid,$match_pattern_prefix,$match_pattern_pass,$prepend_digits,$seq); 
     92  } 
     93  $compiled = $db->prepare('INSERT INTO `trunk_dialpatterns`  
     94    (`trunkid`, `match_pattern_prefix`, `match_pattern_pass`, `prepend_digits`, `seq`)  
     95    VALUES (?,?,?,?,?)' 
     96  ); 
     97  $result = $db->executeMultiple($compiled,$patterns_insert); 
     98  if(DB::IsError($result)) { 
     99    out(_("FATAL, migration failed")); 
     100    die_freepbx($result->getDebugInfo()); 
     101  } 
     102  out(_('ok')); 
     103  unset($pattern_insert); 
     104  outn(_('dropping old trunks_dialpatterns table..')); 
     105  $check = $db->query('DROP TABLE `trunks_dialpatterns`'); 
     106  if(DB::IsError($check)) { 
     107    out(_('ERROR - could not drop table').$check->getDebugInfo()); 
    69108  } else { 
    70     out("ok"); 
    71     outn("create outbound_route_trunks.. "); 
    72     $result = $db->query($outbound_route_trunks); 
    73     if (DB::IsError($result) && $result->getCode() != DB_ERROR_ALREADY_EXISTS ) { 
    74       out("failed, FATAL Error"); 
    75       out($result->getDebugInfo()); 
    76     } else { 
    77       out("ok"); 
    78       outn("create outbound_route_sequence.. "); 
    79       $result = $db->query($outbound_route_sequence); 
    80       if (DB::IsError($result) && $result->getCode() != DB_ERROR_ALREADY_EXISTS ) { 
    81         out("failed, FATAL Error"); 
    82         out($result->getDebugInfo()); 
    83       } else { 
    84         out("ok"); 
    85  
    86         $routepriority = core_routing_getroutenames(); 
    87  
    88         $routes = array(); 
    89         $accum = array(); 
    90         $dialpattern = array(); 
    91         $turnkpriority = array(); 
    92         foreach ($routepriority as $route) { 
    93           $extdisplay = $route[0]; 
    94           $accum[] = substr($extdisplay,4); 
    95    
    96           $routecid_array = core_routing_getroutecid($extdisplay); 
    97           $accum[] = $routecid_array['routecid']; 
    98           $accum[] = $routecid_array['routecid_mode']; 
    99  
    100           $accum[] = core_routing_getroutepassword($extdisplay); 
    101           $accum[] = core_routing_getrouteemergency($extdisplay); 
    102           $accum[] = core_routing_getrouteintracompany($extdisplay); 
    103           $accum[] = core_routing_getroutemohsilence($extdisplay); 
    104  
    105           $dialpattern[$extdisplay] = core_routing_getroutepatterns($extdisplay); 
    106           $trunkpriority[$extdisplay] = core_routing_getroutetrunks($extdisplay); 
    107  
    108           $routes[$extdisplay] = $accum; 
    109           unset($accum); 
    110         } 
    111  
    112         $compiled = $db->prepare('INSERT INTO `outbound_routes` (`name`, `outcid`, `outcid_mode`, `password`, `emergency_route`, `intracompany_route`, `mohclass`) values (?,?,?,?,?,?,?)'); 
    113         $result = $db->executeMultiple($compiled,$routes); 
    114         if(DB::IsError($result)) { 
    115           out("FATAL: ".$result->getDebugInfo()."\n".'error inserting into outbound_routes table');  
    116         } 
    117         $route_ids = $db->getCol('SELECT `route_id` FROM `outbound_routes` ORDER BY `route_id`'); 
    118         if(DB::IsError($route_ids)) { 
    119           out("FATAL: ".$route_ids->getDebugInfo()."\n".'error getting route_ids to create outbound_route_sequence');  
    120         } 
    121         // assumption here is that routepriorities always return in order, I think that is correct, which means we inserted in order 
    122         $seq = 0; 
    123         $outbound_route_sequence = array(); 
    124         foreach ($route_ids as $route_id) { 
    125           outn("processing route_id $route_id.."); 
    126           $outbound_route_sequence[] = array($route_id,$seq); 
    127           $seq++; 
    128  
    129           $this_patterns = array_shift($dialpattern); 
    130  
    131           $insert_patterns = array(); 
    132           foreach ($this_patterns as $pattern) { 
    133             $parts = explode('|',$pattern,2); 
    134             if (count($parts) == 1) { 
    135               $prepend = ''; 
    136               $exten = $pattern; 
    137             } else { 
    138               $prepend = $parts[0]; 
    139               $exten = $parts[1]; 
    140             } 
    141             $parts = explode('/',$exten,2); 
    142             if (count($parts) == 1) { 
    143               $insert_patterns[] = array($route_id, $prepend, $exten, ''); 
    144             } else { 
    145               if ($parts[1][0] == "_") { 
    146                 $parts[1] = substr($parts[1],1); 
    147               } 
    148               $insert_patterns[] = array($route_id, $prepend, $parts[0], $parts[1]); 
    149             } 
    150           } 
    151           $compiled = $db->prepare('INSERT INTO `outbound_route_patterns` (`route_id`, `match_pattern_prefix`, `match_pattern_pass`, `match_cid`) values (?,?,?,?)'); 
    152           $result = $db->executeMultiple($compiled,$insert_patterns); 
    153           if(DB::IsError($result)) { 
    154             out("FATAL: ".$result->getDebugInfo()."\n".'error inserting into outbound_route_patterns table');  
    155           } else { 
    156             outn('patterns..'); 
    157           } 
    158           unset($insert_pattern); 
    159  
    160           $this_trunks = array_shift($trunkpriority); 
    161           $trunk_seq = 0; 
    162           $insert_trunks = array(); 
    163           foreach ($this_trunks as $trunk) { 
    164             $insert_trunks[] = array($route_id, substr($trunk,4),$trunk_seq); 
    165             $trunk_seq++; 
    166           } 
    167  
    168           $compiled = $db->prepare('INSERT INTO `outbound_route_trunks` (`route_id`, `trunk_id`, `seq`) values (?,?,?)'); 
    169           $result = $db->executeMultiple($compiled,$insert_trunks); 
    170           if(DB::IsError($result)) { 
    171             out("FATAL: ".$result->getDebugInfo()."\n".'error inserting into outbound_route_trunks table');  
    172           } else { 
    173             outn('trunks..'); 
    174           } 
    175           unset($insert_trunks); 
    176           out("migrated"); 
    177         } 
    178  
    179         outn('Updating route sequence..'); 
    180         $compiled = $db->prepare('INSERT INTO `outbound_route_sequence` (`route_id`, `seq`) values (?,?)'); 
    181         $result = $db->executeMultiple($compiled,$outbound_route_sequence); 
    182         if(DB::IsError($result)) { 
    183           out("FATAL: ".$result->getDebugInfo()."\n".'error inserting into outbound_route_sequence table');  
    184         } else { 
    185           out("ok"); 
    186           outn('Removing old extenions table..'); 
    187           //TODO: add removal code once thoroughly tested 
    188           out("not implemented until thouroghly tested"); 
    189         } 
    190       } 
    191     } 
     109    out(_('ok')); 
    192110  } 
    193111}