txplib_db.php | searchcode

/textpattern/lib/txplib_db.php

https://bitbucket.org/Manfre/xpattern
PHP | 596 lines | 456 code | 88 blank | 52 comment | 55 complexity | df1505d2b5af0d71c3308e5488008518 MD5 | raw file
Possible License(s): AGPL-1.0

<?php





/*

$HeadURL: http://textpattern.googlecode.com/svn/development/4.0/textpattern/lib/txplib_db.php $

$LastChangedRevision: 2924 $

*/



if (!defined('PFX')) {

	if (!empty($txpcfg['table_prefix'])) {

		define ("PFX",$txpcfg['table_prefix']);

	} else define ("PFX",'');

}



if (get_magic_quotes_runtime()) {

    // Deactivate

    set_magic_quotes_runtime(false);

}



class DB {

	function DB($host, $db, $user, $pass)

	{

		global $txpcfg;



		$this->host = $host;

		$this->db	= $db;

		$this->user = $user;

		$this->pass = $txpcfg['pass'];

		$this->client_flags = isset($txpcfg['client_flags']) ? $txpcfg['client_flags'] : 0;



		$this->link = @mysql_connect($this->host, $this->user, $this->pass, false, $this->client_flags);



		if (!$this->link) die(db_down());



		$this->version = mysql_get_server_info();



		if (!$this->link) {

			$GLOBALS['connected'] = false;

		} else $GLOBALS['connected'] = true;

		@mysql_select_db($this->db) or die(db_down());



		$version = $this->version;

		// be backwardscompatible

		if ( isset($txpcfg['dbcharset']) && (intval($version[0]) >= 5 || preg_match('#^4\.[1-9]#',$version)) )

			mysql_query("SET NAMES ". $txpcfg['dbcharset']);

	}



	function query($q='', $debug='', $unbuf='')

	{

		global $qcount, $qtime;



		$method = (!$unbuf) ? 'mysql_query' : 'mysql_unbuffered_query';

		if (!$q) return false;

		if ($debug or TXP_DEBUG === 1) dmp($q);



		$start = getmicrotime();

		$result = $method($q,$this->link);

		$time = getmicrotime() - $start;

		@$qtime += $time;

		@$qcount++;



		trace_add("[SQL ($time): $q]");



		return $result;

	}

}

$DB = new DB($txpcfg['host'], $txpcfg['db'], $txpcfg['user'], $txpcfg['pass']);



//-------------------------------------------------------------

	function safe_pfx($table) {

		$name = PFX.trim($table);

		return '`'.$name.'`';

	}



//-------------------------------------------------------------

	function safe_pfx_j($table)

	{

		$ts = array();

		foreach (explode(',', $table) as $t) {

			$t = trim($t);

			$name = PFX.$t;

			$ts[] = "`$name`".(PFX ? " as `$t`" : '');

		}

		return join(', ', $ts);

	}



//-------------------------------------------------------------

function safe_pfx_complex($table)

{

   $ts = array(); 

   $names = array();



   foreach (explode(',', $table) as $t) {

        $name = PFX.trim($t);

        $names[] = $name;

        $values = array_count_values($names);

        $alias = ($values[$name] > 1) ? $name.$values[$name] : $name;



        $ts[] = "`$name`".(PFX ? " as `$alias`" : '');

   }

   return join(', ', $ts);

}

	

//-------------------------------------------------------------

	function safe_query($q='',$debug='',$unbuf='')

	{

		global $DB, $txpcfg, $production_status;



		$result = $DB->query($q, $debug, $unbuf);



		if ($result === false and (txpinterface === 'admin' or @$production_status == 'debug' or @$production_status == 'testing')) {

			$caller = ($production_status == 'debug') ? n . join("\n", get_caller()) : '';

			trigger_error(mysql_error() . n . $q . $caller, E_USER_WARNING);

		}



		if(!$result) return false;

		return $result;

	}



// -------------------------------------------------------------

	function safe_delete($table, $where, $debug='')

	{

		$q = "delete from ".safe_pfx($table)." where $where";

		if ($r = safe_query($q,$debug)) {

			return true;

		}

		return false;

	}



// -------------------------------------------------------------

	function safe_update($table, $set, $where, $debug='')

	{

		$q = "update ".safe_pfx($table)." set $set where $where";

		if ($r = safe_query($q,$debug)) {

			return true;

		}

		return false;

	}



// -------------------------------------------------------------

	function safe_insert($table,$set,$debug='')

	{

		global $DB;

		$q = "insert into ".safe_pfx($table)." set $set";

		if ($r = safe_query($q,$debug)) {

			$id = mysql_insert_id($DB->link);

			return ($id === 0 ? true : $id);

		}

		return false;

	}



// -------------------------------------------------------------

	function build_bulk_string($fields, $values)

	{

		$q = '';

		if (!empty($fields) && !empty($values))

		{

			$q .= '(' . join(',', $fields) . ') values ';

			

			$rows = array();

			foreach ($values as $v)

			{

				$rows[] = '(' . join(',', $v) . ')';

			}

			$q .= join(',', $rows);

		}



		return $q;

	}

	

// -------------------------------------------------------------

	function safe_bulk_insert($table,$fields,$values,$debug='')

	{

		global $DB;



		$q = "insert into ".safe_pfx($table) . ' ' . build_bulk_string($fields, $values);

		if ($r = safe_query($q,$debug)) {

			$affected_rows = mysql_affected_rows($DB->link);

			return ($affected_rows === -1 ? false : $affected_rows);

		}

		return false;

	}





// -------------------------------------------------------------

	function safe_replace($table,$set,$debug='')

	{

		global $DB;

		// New to mysql 5

		$q = "replace into ".safe_pfx($table)." set $set";

		if ($r = safe_query($q,$debug)) {

			return true;

		}

		return false;

	}



// -------------------------------------------------------------

	function safe_bulk_replace($table,$fields,$values,$debug='')

	{

		global $DB;



		$q = "replace into ".safe_pfx($table) . ' ' . build_bulk_string($fields, $values);

		if ($r = safe_query($q,$debug)) {

			$affected_rows = mysql_affected_rows($DB->link);

			return ($affected_rows === -1 ? false : $affected_rows);

		}

		return false;

	}





// -------------------------------------------------------------

// insert or update

	function safe_upsert($table,$set,$where,$debug='')

	{

		// FIXME: lock the table so this is atomic?

		$r = safe_update($table, $set, $where, $debug);

		if ($r and (mysql_affected_rows() or safe_count($table, $where, $debug)))

			return $r;

		else

			return safe_insert($table, join(', ', array($where, $set)), $debug);

	}



// -------------------------------------------------------------

	function safe_alter($table, $alter, $debug='')

	{

		$q = "alter table ".safe_pfx($table)." $alter";

		if ($r = safe_query($q,$debug)) {

			return true;

		}

		return false;

	}



// -------------------------------------------------------------

	function safe_optimize($table, $debug='')

	{

		$q = "optimize table ".safe_pfx($table)."";

		if ($r = safe_query($q,$debug)) {

			return true;

		}

		return false;

	}



// -------------------------------------------------------------

	function safe_repair($table, $debug='')

	{

		$q = "repair table ".safe_pfx($table)."";

		if ($r = safe_query($q,$debug)) {

			return true;

		}

		return false;

	}

// -------------------------------------------------------------

	function build_select($fields, $table, $where)

	{

		return "select $fields from ".safe_pfx_j($table) . (!empty($where)?" where $where":'');

	}



// -------------------------------------------------------------

	function safe_field($thing, $table, $where='', $debug='')

	{

		$q = build_select($thing, $table, $where);

		$r = safe_query($q,$debug);

		if (@mysql_num_rows($r) > 0) {

			$f = mysql_result($r,0);

			mysql_free_result($r);

			return $f;

		}

		return false;

	}



// -------------------------------------------------------------

	function safe_column($thing, $table, $where='', $debug='')

	{

		$q = build_select($thing, $table, $where);

		$rs = getRows($q,$debug);

		if ($rs) {

			foreach($rs as $a) {

				$v = array_shift($a);

				$out[$v] = $v;

			}

			return $out;

		}

		return array();

	}

	

	function safe_column2($thing, $table, $where='', $debug='')

	{

		$q = build_select($thing, $table, $where);

		$rs = getRows($q,$debug);

		if ($rs) {

			foreach($rs as $a => $b) {

				//$v = array_shift($a);

				$out[$a] = $b;

				//$out[$v] = $v;

			}

			return $out;

		}

		return array();

	}	



// -------------------------------------------------------------

	function safe_row($things, $table, $where='', $debug='')

	{

		$q = build_select($things, $table, $where);

		$rs = getRow($q,$debug);

		if ($rs) {

			return $rs;

		}

		return array();

	}





// -------------------------------------------------------------

	function safe_rows($things, $table, $where='', $debug='')

	{

		$q = build_select($things, $table, $where);

		$rs = getRows($q,$debug);

		if ($rs) {

			return $rs;

		}

		return array();

	}



// -------------------------------------------------------------

	function safe_rows_start($things, $table, $where='', $debug='')

	{

		$q = build_select($things, $table, $where);

		return startRows($q,$debug);

	}



//-------------------------------------------------------------

	function safe_count($table, $where, $debug='')

	{

		$q = build_select('count(*)', $table, $where);

		return getThing($q,$debug);

	}



// -------------------------------------------------------------

	function safe_show($thing, $table, $debug='')

	{

		$q = "show $thing from ".safe_pfx($table)."";

		$rs = getRows($q,$debug);

		if ($rs) {

			return $rs;

		}

		return array();

	}





//-------------------------------------------------------------

	function fetch($col,$table,$key,$val,$debug='')

	{

		$key = doSlash($key);

		$val = (is_int($val)) ? $val : "'".doSlash($val)."'";

		$q = build_select($col, $table, "`$key` = $val limit 1");

		if ($r = safe_query($q,$debug)) {

			$thing = (mysql_num_rows($r) > 0) ? mysql_result($r,0) : '';

			mysql_free_result($r);

			return $thing;

		}

		return false;

	}



//-------------------------------------------------------------

	function getRow($query,$debug='')

	{

		if ($r = safe_query($query,$debug)) {

			$row = (mysql_num_rows($r) > 0) ? mysql_fetch_assoc($r) : false;

			mysql_free_result($r);

			return $row;

		}

		return false;

	}



//-------------------------------------------------------------

	function getRows($query,$debug='')

	{

		if ($r = safe_query($query,$debug)) {

			if (mysql_num_rows($r) > 0) {

				while ($a = mysql_fetch_assoc($r)) $out[] = $a;

				mysql_free_result($r);

				return $out;

			}

		}

		return false;

	}



//-------------------------------------------------------------

	function startRows($query,$debug='')

	{

		return safe_query($query,$debug);

	}



//-------------------------------------------------------------

	function nextRow($r)

	{

		$row = mysql_fetch_assoc($r);

		if ($row === false)

			mysql_free_result($r);

		return $row;

	}



//-------------------------------------------------------------

	function numRows($r)

	{

		return mysql_num_rows($r);

	}



//-------------------------------------------------------------

	function getThing($query,$debug='')

	{

		if ($r = safe_query($query,$debug)) {

			$thing = (mysql_num_rows($r) != 0) ? mysql_result($r,0) : '';

			mysql_free_result($r);

			return $thing;

		}

		return false;

	}



//-------------------------------------------------------------

	function getThings($query,$debug='')

	// return values of one column from multiple rows in an num indexed array

	{

		$rs = getRows($query,$debug);

		if ($rs) {

			foreach($rs as $a) $out[] = array_shift($a);

			return $out;

		}

		return array();

	}



//-------------------------------------------------------------

	function getCount($table,$where,$debug='')

	{

		$q = build_select('count(*)', $table, $where);

		return getThing($q,$debug);

	}



// -------------------------------------------------------------

	function getTree($root, $type, $where='1=1', $tbl='txp_category')

	{



		$root = doSlash($root);

		$type = doSlash($type);



		$rs = safe_row(

			"lft as l, rgt as r",

			$tbl,

			"name='$root' and type = '$type'"

		);



		if (!$rs) return array();

		extract($rs);



		$out = array();

		$right = array();



		$rs = safe_rows_start(

			"id, name, lft, rgt, parent, title",

			$tbl,

			"lft between $l and $r and type = '$type' and name != 'root' and $where order by lft asc"

		);



		while ($rs and $row = nextRow($rs)) {

			extract($row);

			while (count($right) > 0 && $right[count($right)-1] < $rgt) {

				array_pop($right);

			}



			$out[] =

				array(

					'id' => $id,

					'name' => $name,

					'title' => $title,

					'level' => count($right),

					'children' => ($rgt - $lft - 1) / 2,

					'parent' => $parent

				);



			$right[] = $rgt;

		}

		return($out);

	}



// -------------------------------------------------------------

	function getTreePath($target, $type, $tbl='txp_category')

	{



		$rs = safe_row(

			"lft as l, rgt as r",

			$tbl,

			"name='".doSlash($target)."' and type = '".doSlash($type)."'"

		);

		if (!$rs) return array();

		extract($rs);



		$rs = safe_rows_start(

			"*",

			$tbl,

				"lft <= $l and rgt >= $r and type = '".doSlash($type)."' order by lft asc"

		);



		$out = array();

		$right = array();



		while ($rs and $row = nextRow($rs)) {

			extract($row);

			while (count($right) > 0 && $right[count($right)-1] < $rgt) {

				array_pop($right);

			}



			$out[] =

				array(

					'id' => $id,

					'name' => $name,

					'title' => $title,

					'level' => count($right),

					'children' => ($rgt - $lft - 1) / 2

				);



			$right[] = $rgt;

		}

		return $out;

	}



// -------------------------------------------------------------

	function rebuild_tree($parent, $left, $type, $tbl='txp_category')

	{

		$left  = assert_int($left);

		$right = $left+1;



		$parent = doSlash($parent);

		$type   = doSlash($type);



		$result = safe_column("name", $tbl,

			"parent='$parent' and type='$type' order by name");



		foreach($result as $row) {

			$right = rebuild_tree($row, $right, $type, $tbl);

		}



		safe_update(

			$tbl,

			"lft=$left, rgt=$right",

			"name='$parent' and type='$type'"

		);

		return $right+1;

	}



//-------------------------------------------------------------

	function rebuild_tree_full($type, $tbl='txp_category')

	{

		# fix circular references, otherwise rebuild_tree() could get stuck in a loop

		safe_update($tbl, "parent=''", "type='".doSlash($type)."' and name='root'");

		safe_update($tbl, "parent='root'", "type='".doSlash($type)."' and parent=name");



		rebuild_tree('root', 1, $type, $tbl);

	}



//-------------------------------------------------------------

	function get_prefs()

	{

		$r = safe_rows_start('name, val', 'txp_prefs', 'prefs_id=1');

		if ($r) {

			while ($a = nextRow($r)) {

				$out[$a['name']] = $a['val'];

			}

			return $out;

		}

		return array();

	}





// -------------------------------------------------------------

	function db_down()

	{

		// 503 status might discourage search engines from indexing or caching the error message

		txp_status_header('503 Service Unavailable');

		$error = mysql_error();

		return <<<eod

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>

	<meta http-equiv="content-type" content="text/html; charset=utf-8" />

	<title>Untitled</title>

</head>

<body>

<p align="center" style="margin-top:4em">Database unavailable.</p>

<!-- $error -->

</body>

</html>

eod;

	}



?>