Changeset 6228

Show
Ignore:
Timestamp:
07/31/08 12:35:25 (5 years ago)
Author:
ethans
Message:

Finalizes sqlite3 support for core functions.inc.php. Fixes #2778.
Adjusts SQL syntax for sqlite3 queries dealing with "globals" table variables with _ in their name. Sqlite3 doesn't escape the same as MySQL does.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • modules/branches/2.5/core/functions.inc.php

    r6182 r6228  
    30023002} 
    30033003 
     3004//Sort trunks for sqlite 
     3005function sort_trunks($a,$b)  { 
     3006        global $unique_trunks; 
     3007        ereg("OUT_([0-9]+)",$unique_trunks[$a][0],$trunk_num1); 
     3008        ereg("OUT_([0-9]+)",$unique_trunks[$b][0],$trunk_num2); 
     3009        return ($trunk_num1[1] >= $trunk_num2[1]? 1:-1); 
     3010} 
     3011 
    30043012//get unique trunks 
    30053013function core_trunks_list($assoc = false) { 
    …… 
    30093017  global $amp_conf; 
    30103018   
    3011   if ($amp_conf["AMPDBENGINE"] == "sqlite3") 
    3012   { 
    3013     // TODO: sqlite work arround - diego 
    3014     // TODO: WILL NOT WORK, need to remove the usage of SUBSTRING 
    3015     // need to reorder the trunks in PHP code 
    3016     $sqlstr  = "SELECT t.variable, t.value, d.value state FROM `globals` t "; 
    3017     $sqlstr .= "JOIN (SELECT x.variable, x.value FROM globals x WHERE x.variable LIKE 'OUTDISABLE\_%') d "; 
    3018     $sqlstr .= "ON substring(t.variable,5) = substring(d.variable,12) WHERE t.variable LIKE 'OUT\_%' "; 
    3019     $sqlstr .= "UNION ALL "; 
    3020     $sqlstr .= "SELECT v.variable, v.value, concat(substring(v.value,1,0),'off') state  FROM `globals` v "; 
    3021     $sqlstr .= "WHERE v.variable LIKE 'OUT\_%' AND concat('OUTDISABLE_',substring(v.variable,5)) NOT IN "; 
    3022     $sqlstr .= " ( SELECT variable from globals WHERE variable LIKE 'OUTDISABLE\_%' ) "; 
    3023     $sqlstr .= "ORDER BY variable"; 
    3024  
    3025     //$unique_trunks = sql("SELECT * FROM globals WHERE variable LIKE 'OUT_%' ORDER BY variable","getAll");  
    3026     $unique_trunks = sql($sqlstr,"getAll");  
    3027   } 
    3028   else 
    3029   { 
    3030     // we have to escape _ for mysql: normally a wildcard 
    3031     $sqlstr  = "SELECT t.variable, t.value, d.value state FROM `globals` t "; 
    3032     $sqlstr .= "JOIN (SELECT x.variable, x.value FROM globals x WHERE x.variable LIKE 'OUTDISABLE\\\_%') d "; 
    3033     $sqlstr .= "ON substring(t.variable,5) = substring(d.variable,12) WHERE t.variable LIKE 'OUT\\\_%' "; 
    3034     $sqlstr .= "UNION ALL "; 
    3035     $sqlstr .= "SELECT v.variable, v.value, concat(substring(v.value,1,0),'off') state  FROM `globals` v "; 
    3036     $sqlstr .= "WHERE v.variable LIKE 'OUT\\\_%' AND concat('OUTDISABLE_',substring(v.variable,5)) NOT IN "; 
    3037     $sqlstr .= " ( SELECT variable from globals WHERE variable LIKE 'OUTDISABLE\\\_%' ) "; 
    3038     $sqlstr .= "ORDER BY RIGHT( variable, LENGTH( variable ) - 4 )+0"; 
    3039  
    3040     //$unique_trunks = sql("SELECT * FROM globals WHERE variable LIKE 'OUT\\\_%' ORDER BY RIGHT( variable, LENGTH( variable ) - 4 )+0","getAll");  
    3041     $unique_trunks = sql($sqlstr,"getAll");  
    3042   } 
     3019        // sqlite doesn't support the syntax required for the SQL so we have to do it the hard way 
     3020        if ($amp_conf["AMPDBENGINE"] == "sqlite3") 
     3021        { 
     3022                $sqlstr = "SELECT variable, value FROM globals WHERE variable LIKE 'OUT_%'"; 
     3023                $my_unique_trunks = sql($sqlstr,"getAll",DB_FETCHMODE_ASSOC); 
     3024 
     3025                $sqlstr = "SELECT variable, value FROM globals WHERE variable LIKE 'OUTDISABLE_%'"; 
     3026                $disable_states = sql($sqlstr,"getAll",DB_FETCHMODE_ASSOC); 
     3027 
     3028                foreach($disable_states as $arr)  { 
     3029                        $disable_states_assoc[$arr['variable']] = $arr['value']; 
     3030                } 
     3031                global $unique_trunks; 
     3032                $unique_trunks = array(); 
     3033 
     3034                foreach ($my_unique_trunks as $this_trunk) { 
     3035 
     3036                        $trunk_num = substr($this_trunk['variable'],4); 
     3037      $this_state = (isset($disable_states_assoc['OUTDISABLE_'.$trunk_num]) ? $disable_states_assoc['OUTDISABLE_'.$trunk_num] : 'off'); 
     3038                        $unique_trunks[] = array($this_trunk['variable'], $this_trunk['value'], $this_state); 
     3039                } 
     3040                // sort this array using a custom function sort_trunks(), defined above 
     3041                uksort($unique_trunks,"sort_trunks"); 
     3042                // re-index the newly sorted array 
     3043                foreach($unique_trunks as $arr) { 
     3044                        $unique_trunks_t[] = array($arr[0],$arr[1],$arr[2]); 
     3045                } 
     3046                $unique_trunks = $unique_trunks_t; 
     3047 
     3048        } 
     3049        else 
     3050        { 
     3051                $sqlstr  = "SELECT t.variable, t.value, d.value state FROM `globals` t "; 
     3052                $sqlstr .= "JOIN (SELECT x.variable, x.value FROM globals x WHERE x.variable LIKE 'OUTDISABLE\_%') d "; 
     3053                $sqlstr .= "ON substring(t.variable,5) = substring(d.variable,12) WHERE t.variable LIKE 'OUT\_%' "; 
     3054                $sqlstr .= "UNION ALL "; 
     3055                $sqlstr .= "SELECT v.variable, v.value, concat(substring(v.value,1,0),'off') state  FROM `globals` v "; 
     3056                $sqlstr .= "WHERE v.variable LIKE 'OUT\_%' AND concat('OUTDISABLE_',substring(v.variable,5)) NOT IN "; 
     3057                $sqlstr .= " ( SELECT variable from globals WHERE variable LIKE 'OUTDISABLE\_%' ) "; 
     3058                $sqlstr .= "ORDER BY variable"; 
     3059                //$unique_trunks = sql("SELECT * FROM globals WHERE variable LIKE 'OUT_%' ORDER BY variable","getAll"); 
     3060                $unique_trunks = sql($sqlstr,"getAll"); 
     3061        } 
    30433062 
    30443063  //if no trunks have ever been defined, then create the proper variables with the default zap trunk 
    …… 
    30813100//write the OUTIDS global variable (used in dialparties.agi) 
    30823101function core_trunks_writeoutids() { 
    3083   // we have to escape _ for mysql: normally a wildcard 
    3084   $unique_trunks = sql("SELECT variable FROM globals WHERE variable LIKE 'OUT\\\_%'","getAll");  
     3102  // we have to escape _ for mysql: normally a wildcard (but not for sqlite3, it breaks!) 
     3103  if ($amp_conf["AMPDBENGINE"] == "sqlite3")  { 
     3104    $sql = "SELECT variable FROM globals WHERE variable LIKE 'OUT_%'"; 
     3105  } 
     3106  else  { 
     3107    $sql = "SELECT variable FROM globals WHERE variable LIKE 'OUT\\\_%'"; 
     3108  } 
     3109  $unique_trunks = sql($sql,"getAll");  
    30853110 
    30863111  $outids = null; // Start off with nothing 
    …… 
    33343359  if (count($results) == 0) { 
    33353360    // see if they're still using the old dialprefix method 
    3336     $results = sql("SELECT variable,value FROM globals WHERE variable LIKE 'DIAL\\\_OUT\\\_%'","getAll"); 
     3361    if ($amp_conf["AMPDBENGINE"] == "sqlite3")  { 
     3362      $sql ="SELECT variable,value FROM globals WHERE variable LIKE 'DIAL_OUT_%'"; 
     3363    } 
     3364    else  { 
     3365      $sql ="SELECT variable,value FROM globals WHERE variable LIKE 'DIAL\\\_OUT\\\_%'"; 
     3366    } 
     3367    $results = sql($sql,"getAll"); 
    33373368    // we SUBSTRING() to remove "outrt-" 
    33383369     
    …… 
    33893420       
    33903421      // delete old values 
    3391       sql("DELETE FROM globals WHERE (variable LIKE 'DIAL\\\_OUT\\\_%') OR (variable = 'OUT') "); 
     3422      if ($amp_conf["AMPDBENGINE"] == "sqlite3")  { 
     3423        $sql = "DELETE FROM globals WHERE (variable LIKE 'DIAL_OUT_%') OR (variable = 'OUT') "; 
     3424      } 
     3425      else  { 
     3426        $sql = "DELETE FROM globals WHERE (variable LIKE 'DIAL\\\_OUT\\\_%') OR (variable = 'OUT') "; 
     3427      } 
     3428      sql($sql); 
    33923429 
    33933430      // we need to re-generate extensions_additional.conf