[Summary view] [Print] [Text view]

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