[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  /*
   4  $HeadURL: http://svn.textpattern.com/releases/4.0.6/source/textpattern/lib/txplib_db.php $
   5  $LastChangedRevision: 2748 $
   6  */
   7  
   8  if (!defined('PFX')) {
   9      if (!empty($txpcfg['table_prefix'])) {
  10          define ("PFX",$txpcfg['table_prefix']);
  11      } else define ("PFX",'');
  12  }
  13  
  14  set_magic_quotes_runtime(0);
  15  
  16  class DB {
  17      function DB()
  18      {
  19          global $txpcfg;
  20  
  21          $this->host = $txpcfg['host'];
  22          $this->db    = $txpcfg['db'];
  23          $this->user = $txpcfg['user'];
  24          $this->pass = $txpcfg['pass'];
  25  
  26          $this->link = @mysql_connect($this->host, $this->user, $this->pass);
  27          if (!$this->link) die(db_down());
  28  
  29          $this->version = mysql_get_server_info();
  30  
  31          if (!$this->link) {
  32              $GLOBALS['connected'] = false;
  33          } else $GLOBALS['connected'] = true;
  34          @mysql_select_db($this->db) or die(db_down());
  35  
  36          $version = $this->version;
  37          // be backwardscompatible
  38          if ( isset($txpcfg['dbcharset']) && (intval($version[0]) >= 5 || preg_match('#^4\.[1-9]#',$version)) )
  39              mysql_query("SET NAMES ". $txpcfg['dbcharset']);
  40      }
  41  }
  42  $DB = new DB;
  43  
  44  //-------------------------------------------------------------
  45  	function safe_pfx($table) {
  46          $name = PFX.$table;
  47          if (preg_match('@[^\w._$]@', $name))
  48              return '`'.$name.'`';
  49          return $name;
  50      }
  51  
  52  //-------------------------------------------------------------
  53  	function safe_pfx_j($table)
  54      {
  55          $ts = array();
  56          foreach (explode(',', $table) as $t) {
  57              $name = PFX.trim($t);
  58              if (preg_match('@[^\w._$]@', $name))
  59                  $ts[] = "`$name`".(PFX ? " as `$t`" : '');
  60              else
  61                  $ts[] = "$name".(PFX ? " as $t" : '');
  62          }
  63          return join(', ', $ts);
  64      }
  65  
  66  //-------------------------------------------------------------
  67  	function safe_query($q='',$debug='',$unbuf='')
  68      {
  69          global $DB,$txpcfg, $qcount, $qtime, $production_status;
  70          $method = (!$unbuf) ? 'mysql_query' : 'mysql_unbuffered_query';
  71          if (!$q) return false;
  72          if ($debug or TXP_DEBUG === 1) dmp($q);
  73  
  74          $start = getmicrotime();
  75          $result = $method($q,$DB->link);
  76          $time = sprintf('%02.6f', getmicrotime() - $start);
  77          @$qtime += $time;
  78          @$qcount++;
  79          if ($result === false and (txpinterface === 'admin' or @$production_status == 'debug' or @$production_status == 'testing')) {
  80              $caller = ($production_status == 'debug') ? n . join("\n", get_caller()) : '';
  81              trigger_error(mysql_error() . n . $q . $caller, E_USER_WARNING);
  82          }
  83  
  84          trace_add("[SQL ($time): $q]");
  85  
  86          if(!$result) return false;
  87          return $result;
  88      }
  89  
  90  // -------------------------------------------------------------
  91  	function safe_delete($table, $where, $debug='')
  92      {
  93          $q = "delete from ".safe_pfx($table)." where $where";
  94          if ($r = safe_query($q,$debug)) {
  95              return true;
  96          }
  97          return false;
  98      }
  99  
 100  // -------------------------------------------------------------
 101  	function safe_update($table, $set, $where, $debug='') 
 102      {
 103          $q = "update ".safe_pfx($table)." set $set where $where";
 104          if ($r = safe_query($q,$debug)) {
 105              return true;
 106          }
 107          return false;
 108      }
 109  
 110  // -------------------------------------------------------------
 111  	function safe_insert($table,$set,$debug='') 
 112      {
 113          global $DB;
 114          $q = "insert into ".safe_pfx($table)." set $set";
 115          if ($r = safe_query($q,$debug)) {
 116              $id = mysql_insert_id($DB->link);
 117              return ($id === 0 ? true : $id);
 118          }
 119          return false;
 120      }
 121  
 122  // -------------------------------------------------------------
 123  // insert or update
 124  	function safe_upsert($table,$set,$where,$debug='') 
 125      {
 126          // FIXME: lock the table so this is atomic?
 127          $r = safe_update($table, $set, $where, $debug);
 128          if ($r and (mysql_affected_rows() or safe_count($table, $where, $debug)))
 129              return $r;
 130          else
 131              return safe_insert($table, join(', ', array($where, $set)), $debug);
 132      }
 133  
 134  // -------------------------------------------------------------
 135  	function safe_alter($table, $alter, $debug='') 
 136      {
 137          $q = "alter table ".safe_pfx($table)." $alter";
 138          if ($r = safe_query($q,$debug)) {
 139              return true;
 140          }
 141          return false;
 142      }
 143  
 144  // -------------------------------------------------------------
 145  	function safe_optimize($table, $debug='') 
 146      {
 147          $q = "optimize table ".safe_pfx($table)."";
 148          if ($r = safe_query($q,$debug)) {
 149              return true;
 150          }
 151          return false;
 152      }
 153  
 154  // -------------------------------------------------------------
 155  	function safe_repair($table, $debug='') 
 156      {
 157          $q = "repair table ".safe_pfx($table)."";
 158          if ($r = safe_query($q,$debug)) {
 159              return true;
 160          }
 161          return false;
 162      }
 163  
 164  // -------------------------------------------------------------
 165  	function safe_field($thing, $table, $where, $debug='') 
 166      {
 167          $q = "select $thing from ".safe_pfx_j($table)." where $where";
 168          $r = safe_query($q,$debug);
 169          if (@mysql_num_rows($r) > 0) {
 170              $f = mysql_result($r,0);
 171              mysql_free_result($r);
 172              return $f;
 173          }
 174          return false;
 175      }
 176  
 177  // -------------------------------------------------------------
 178  	function safe_column($thing, $table, $where, $debug='') 
 179      {
 180          $q = "select $thing from ".safe_pfx_j($table)." where $where";
 181          $rs = getRows($q,$debug);
 182          if ($rs) {
 183              foreach($rs as $a) {
 184                  $v = array_shift($a);
 185                  $out[$v] = $v;
 186              }
 187              return $out;
 188          }
 189          return array();
 190      }
 191  
 192  // -------------------------------------------------------------
 193  	function safe_row($things, $table, $where, $debug='') 
 194      {
 195          $q = "select $things from ".safe_pfx_j($table)." where $where";
 196          $rs = getRow($q,$debug);
 197          if ($rs) {
 198              return $rs;
 199          }
 200          return array();
 201      }
 202  
 203  
 204  // -------------------------------------------------------------
 205  	function safe_rows($things, $table, $where, $debug='') 
 206      {
 207          $q = "select $things from ".safe_pfx_j($table)." where $where";
 208          $rs = getRows($q,$debug);
 209          if ($rs) {
 210              return $rs;
 211          }
 212          return array();
 213      }
 214  
 215  // -------------------------------------------------------------
 216  	function safe_rows_start($things, $table, $where, $debug='') 
 217      {
 218          $q = "select $things from ".safe_pfx_j($table)." where $where";
 219          return startRows($q,$debug);
 220      }
 221  
 222  //-------------------------------------------------------------
 223  	function safe_count($table, $where, $debug='') 
 224      {
 225          return getThing("select count(*) from ".safe_pfx_j($table)." where $where",$debug);
 226      }
 227  
 228  // -------------------------------------------------------------
 229  	function safe_show($thing, $table, $debug='') 
 230      {
 231          $q = "show $thing from ".safe_pfx($table)."";
 232          $rs = getRows($q,$debug);
 233          if ($rs) {
 234              return $rs;
 235          }
 236          return array();
 237      }
 238  
 239  
 240  //-------------------------------------------------------------
 241  	function fetch($col,$table,$key,$val,$debug='') 
 242      {
 243          $key = doSlash($key);
 244          $val = (is_int($val)) ? $val : "'".doSlash($val)."'";
 245          $q = "select $col from ".safe_pfx($table)." where `$key` = $val limit 1";
 246          if ($r = safe_query($q,$debug)) {
 247              $thing = (mysql_num_rows($r) > 0) ? mysql_result($r,0) : '';
 248              mysql_free_result($r);
 249              return $thing;
 250          }
 251          return false;
 252      }
 253  
 254  //-------------------------------------------------------------
 255  	function getRow($query,$debug='') 
 256      {
 257          if ($r = safe_query($query,$debug)) {
 258              $row = (mysql_num_rows($r) > 0) ? mysql_fetch_assoc($r) : false;
 259              mysql_free_result($r);
 260              return $row;
 261          }
 262          return false;
 263      }
 264  
 265  //-------------------------------------------------------------
 266  	function getRows($query,$debug='') 
 267      {
 268          if ($r = safe_query($query,$debug)) {
 269              if (mysql_num_rows($r) > 0) {
 270                  while ($a = mysql_fetch_assoc($r)) $out[] = $a; 
 271                  mysql_free_result($r);
 272                  return $out;
 273              }
 274          }
 275          return false;
 276      }
 277  
 278  //-------------------------------------------------------------
 279  	function startRows($query,$debug='')
 280      {
 281          return safe_query($query,$debug);
 282      }
 283  
 284  //-------------------------------------------------------------
 285  	function nextRow($r)
 286      {
 287          $row = mysql_fetch_assoc($r);
 288          if ($row === false)
 289              mysql_free_result($r);
 290          return $row;
 291      }
 292  
 293  //-------------------------------------------------------------
 294  	function numRows($r)
 295      {
 296          return mysql_num_rows($r);
 297      }
 298  
 299  //-------------------------------------------------------------
 300  	function getThing($query,$debug='') 
 301      {
 302          if ($r = safe_query($query,$debug)) {
 303              $thing = (mysql_num_rows($r) != 0) ? mysql_result($r,0) : '';
 304              mysql_free_result($r);
 305              return $thing;
 306          }
 307          return false;
 308      }
 309  
 310  //-------------------------------------------------------------
 311  	function getThings($query,$debug='') 
 312      // return values of one column from multiple rows in an num indexed array
 313      {
 314          $rs = getRows($query,$debug);
 315          if ($rs) {
 316              foreach($rs as $a) $out[] = array_shift($a);
 317              return $out;
 318          }
 319          return array();
 320      }
 321      
 322  //-------------------------------------------------------------
 323  	function getCount($table,$where,$debug='') 
 324      {
 325          return getThing("select count(*) from ".safe_pfx_j($table)." where $where",$debug);
 326      }
 327  
 328  // -------------------------------------------------------------
 329  	function getTree($root, $type, $where='1=1')
 330      {
 331  
 332          $root = doSlash($root);
 333          $type = doSlash($type);
 334  
 335          $rs = safe_row(
 336              "lft as l, rgt as r",
 337              "txp_category",
 338              "name='$root' and type = '$type'"
 339          );
 340  
 341          if (!$rs) return array();
 342          extract($rs);
 343  
 344          $out = array();
 345          $right = array(); 
 346  
 347          $rs = safe_rows_start(
 348              "id, name, lft, rgt, parent, title",
 349              "txp_category",
 350              "lft between $l and $r and type = '$type' and name != 'root' and $where order by lft asc"
 351          ); 
 352  
 353          while ($rs and $row = nextRow($rs)) {
 354              extract($row);
 355              while (count($right) > 0 && $right[count($right)-1] < $rgt) {
 356                  array_pop($right);
 357              }
 358  
 359              $out[] =
 360                  array(
 361                      'id' => $id,
 362                      'name' => $name,
 363                      'title' => $title,
 364                      'level' => count($right),
 365                      'children' => ($rgt - $lft - 1) / 2
 366                  );
 367  
 368              $right[] = $rgt;
 369          }
 370          return($out);
 371      }
 372  
 373  // -------------------------------------------------------------
 374  	function getTreePath($target, $type)
 375      {
 376  
 377          $rs = safe_row(
 378              "lft as l, rgt as r",
 379              "txp_category",
 380              "name='".doSlash($target)."' and type = '".doSlash($type)."'"
 381          );
 382          if (!$rs) return array();
 383          extract($rs);
 384  
 385          $rs = safe_rows_start(
 386              "*",
 387              "txp_category",
 388                  "lft <= $l and rgt >= $r and type = '".doSlash($type)."' order by lft asc"
 389          );
 390  
 391          $out = array();
 392          $right = array();
 393  
 394          while ($rs and $row = nextRow($rs)) {
 395              extract($row);
 396              while (count($right) > 0 && $right[count($right)-1] < $rgt) {
 397                  array_pop($right);
 398              }
 399  
 400              $out[] =
 401                  array(
 402                      'id' => $id,
 403                      'name' => $name,
 404                      'title' => $title,
 405                      'level' => count($right),
 406                      'children' => ($rgt - $lft - 1) / 2
 407                  );
 408  
 409              $right[] = $rgt;
 410          }
 411          return $out;
 412      }
 413  
 414  // -------------------------------------------------------------
 415  	function rebuild_tree($parent, $left, $type)
 416      {
 417          $left  = assert_int($left);
 418          $right = $left+1;
 419  
 420          $parent = doSlash($parent);
 421          $type   = doSlash($type);
 422  
 423          $result = safe_column("name", "txp_category",
 424              "parent='$parent' and type='$type' order by name");
 425  
 426          foreach($result as $row) {
 427              $right = rebuild_tree($row, $right, $type);
 428          }
 429  
 430          safe_update(
 431              "txp_category",
 432              "lft=$left, rgt=$right",
 433              "name='$parent' and type='$type'"
 434          );
 435          return $right+1;
 436      }
 437      
 438  //-------------------------------------------------------------
 439  	function rebuild_tree_full($type)
 440      {
 441          # fix circular references, otherwise rebuild_tree() could get stuck in a loop
 442          safe_update('txp_category', "parent=''", "type='".doSlash($type)."' and name='root'");
 443          safe_update('txp_category', "parent='root'", "type='".doSlash($type)."' and parent=name");
 444          
 445          rebuild_tree('root', 1, $type);
 446      }
 447  
 448  //-------------------------------------------------------------
 449  	function get_prefs()
 450      {
 451          $r = safe_rows_start('name, val', 'txp_prefs', 'prefs_id=1');
 452          if ($r) {
 453              while ($a = nextRow($r)) {
 454                  $out[$a['name']] = $a['val'];
 455              }
 456              return $out;
 457          }
 458          return array();
 459      }
 460  
 461  // -------------------------------------------------------------
 462  	function db_down()
 463      {
 464          // 503 status might discourage search engines from indexing or caching the error message
 465          txp_status_header('503 Service Unavailable');
 466          $error = mysql_error();
 467          return <<<eod
 468  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 469          "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 470  <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 471  <head>
 472      <meta http-equiv="content-type" content="text/html; charset=utf-8" />
 473      <title>Untitled</title>
 474  </head>
 475  <body>
 476  <p align="center" style="margin-top:4em">Database unavailable.</p>
 477  <!-- $error -->
 478  </body>
 479  </html>
 480  eod;
 481      }
 482  
 483  ?>