spdol.php

易于使用的php数据访问对象,从业务逻辑代码中剥离sql查询,易于维护和扩充功能.目前仅在自己的代码中使用,我一个人水平有限,osdn的高手们,一起来改进完善它吧,Simple PHP Databas

<?php
/**********************************************
	Please do not remove this comment.

	Simple PHP Database Object Layer
	

	This is an attempt to detach database
	access operations from business logic code.
	The aim of design is simple and easy to use.
	Currently it worked with PHP version > 4.2.

	Created: Xiiin. 2006.09.16
	Last modified: Xiiin. 2006.09.16
	current version: 0.25beta
**********************************************/

/*
The abstract general database interface class.
need to be inherited according to different DBMS.
*/

class dbi{
	var $conn;
	var $dbhost;
	var $dbuser;
	var $dbpass;
	var $dbname;
	function dbi(){
		//need to be implemented in child class.
	}
	function getconn(){
		//need to be implemented in child class.
	}
	function query(){
		//need to be implemented in child class.
	}
	function geterror(){
		//need to be implemented in child class.
	}
	function getnumrows($rs){
		//need to be implemented in child class.
	}
	function getrow($rs){
		//need to be implemented in child class.
	}
	function close(){
		//need to be implemented in child class.
	}
	function startta(){
		//need to be implemented in child class.
	}
	function rollback(){
		//need to be implemented in child class.
	}
	function commit(){
		//need to be implemented in child class.
	}
}

/*
The concrete dbi class that worked with MySQL version >= 4.1.
*/
class dbi_mysql extends dbi{
	function dbi_mysql($host, $user, $pass, $db){
		$this->conn = $this->getconn($host, $user, $pass, $db);
		if (!$this->conn){
			die('can not establish db connection');
		}
	}
	function getconn($host, $user, $pass, $db){
		$conn =  mysql_connect($host, $user, $pass);
		mysql_select_db($db, $conn);
		return $conn;
	}
	function query($sql){
		$rs =  mysql_query($sql, $this->conn);
		return $rs;
	}
	function geterror(){
		return mysql_error($this->conn);
	}
	function getnumrows($rs){
		return mysql_num_rows($rs);
	}
	function getrow($rs){
		return mysql_fetch_array($rs);
	}
	function close(){
		return mysql_close($this->conn);
	}
	function startta(){
		return mysql_query('start transaction', $this->conn);
	}
	function rollback(){
		return mysql_query('rollback', $this->conn);
	}
	function commit(){
		return mysql_query('commit', $this->conn);
	}
}


/*
The concrete dbi class that worked with PostgreSQL.
ps: not completed yet.
*/
class dbi_postgresql extends dbi{
	function dbi_postgresql(){
		//TODO
	}
	function getconn(){
		//TODO
	}
	function query(){
		//TODO
	}
	function geterror(){
		//TODO
	}
	function getnumrows($rs){
		//TODO
	}
	function getrow($rs){
		//TODO
	}
	function close(){
		//TODO
	}
	function startta(){
		//TODO
	}
	function rollback(){
		//TODO
	}
	function commit(){
		//TODO
	}
}


class table{
	var $_tablename_;
	var $_dbi_;
	var $_defaultvalue_ = '!_have_no_value_!';
	
	function table($tablename, &$dbi){
		$fields = get_object_vars($this);
		foreach ($fields as $field => $value){
			$this->$field = $this->_defaultvalue_;
		}
		$this->_tablename_ = $tablename;
		$this->_dbi_ = $dbi;	
	}

	function getinstance(){
		$classname = get_class($this);		
		$instance = new $classname($this->_tablename_, $this->_dbi_);
		return $instance;
	}

	function getfieldsarray(){
		$fields = get_object_vars($this);
		$array = array();
		foreach ($fields as $field => $value){
			if (!preg_match('/^!_.*_!$/', $field) && $value !== $this->_defaultvalue_){
				$array[$field] = $value; 
			}
		}
		return $array;
	}
	function cc($value){
		//return iconv('utf-8', 'gb2312', $value);
		return $value;
	}
	function query($sql){
		$rs = $this->_dbi_->query($sql);
		if (!$rs){
			die('sql error: bad query: ' . $this->_dbi_->geterror());
		}
		$ra = array();
		while ($row = $this->_dbi_->getrow($rs)){
			$o = $this->getinstance();
			foreach ($row as $key => $value){
				$key = strtolower($key);
				$o->$key = $value;
			}
			$ra[] = $o;
		}
		return $ra;
	}
	function select($orderby=-1, $ascdesc=-1, $startpos=-1, $length=-1, $gta=-1, $lta=-1, $neqa=-1){
		$array = $this->getfieldsarray();
		$sql = "select * from " . $this->_tablename_;
		if (count($array) != 0){
			$sql .= ' where 1';
		}
		foreach ($array as $key => $value){
			$sql .= " and " . $key . "='" . $this->cc($value) . "'";
		}
		if ($gta != -1){
			foreach ($gta as $key => $value ){
				$key = strtolower($key);
				if (!isset($this->$key)){
					die('gt field does not exist in your class');
				}
				$sql .= " and " .$key . ">'" . $value . "'";
			}
		}
		if ($lta != -1){
			foreach ($lta as $key => $value ){
				$key = strtolower($key);
				if (!isset($this->$key)){
					die('lt field does not exist in your class');
				}
				$sql .= " and " .$key . "<'" . $value . "'";
			}
		}
		if ($neqa != -1){
			foreach ($neqa as $key => $value ){
				$key = strtolower($key);
				if (!isset($this->$key)){
					die('neq field does not exist in your class');
				}
				$sql .= " and " .$key . "<>'" . $value . "'";
			}
		}
		$orderby = strtolower($orderby);
		if ($orderby != -1){
			if (!isset($this->$orderby)){
				die('order by field does not exist in your class');
			}
			$sql .= " order by " . $orderby;
			if ($ascdesc == 0){
				$sql .= ' asc';	
			}else{
				$sql .= ' desc';
			}			
		}
		if ($startpos != -1){
			if (!is_a($this->_dbi_, 'dbi_mysql')){
				die('your dbms does not support limited select operation.');
			}
			$sql .= ' limit '.$startpos;
			if ($length != -1){
				$sql .= ','.$length;
			}
		}
		$rs = $this->_dbi_->query($sql);
		if (!$rs){
			die('sql error: bad select: ' . $this->_dbi_->geterror());
		}
		$ra = array();
		while ($row = $this->_dbi_->getrow($rs)){
			$o = $this->getinstance();
			foreach ($row as $key => $value){
				$key = strtolower($key);
				$o->$key = $value;
			}
			$ra[] = $o;
		}
		return $ra;
	}

	function selectmax($field){
		if (!isset($this->$field)){
			die('max field does not exist in your class');
		}
		$sql = "select max(" . $field . ") from " . $this->_tablename_ . " where 1";
		$array = $this->getfieldsarray();
		foreach ($array as $key => $value){
			$sql .= " and " . $key . "='" . $this->cc($value) . "'";
		}
		$rs = $this->_dbi_->query($sql);
		if (!$rs){
			die('sql error: bad selectmax: ' . $this->_dbi_->geterror());
		}
		$row = $this->_dbi_->getrow($rs);
		return $row[0];
	}

	function selectmin($field){
		if (!isset($this->$field)){
			die('min field does not exist in your class');
		}
		$sql = "select min(" . $field . ") from " . $this->_tablename_ . " where 1";
		$array = $this->getfieldsarray();
		foreach ($array as $key => $value){
			$sql .= " and " . $key . "='" . $this->cc($value) . "'";
		}
		$rs = $this->_dbi_->query($sql);
		if (!$rs){
			die('sql error: bad selectmin: ' . $this->_dbi_->geterror());
		}
		$row = $this->_dbi_->getrow($rs);
		return $row[0];
	}

	function selectsum($field){
		if (!isset($this->$field)){
			die('sum field does not exist in your class');
		}
		$sql = "select sum(" . $field . ") from " . $this->_tablename_ . " where 1";
		$array = $this->getfieldsarray();
		foreach ($array as $key => $value){
			$sql .= " and " . $key . "='" . $this->cc($value) . "'";
		}
		$rs = $this->_dbi_->query($sql);
		if (!$rs){
			die('sql error: bad selectsum: ' . $this->_dbi_->geterror());
		}
		$row = $this->_dbi_getrow($rs);
		return $row[0];
	}

	function update($b){
		if (count($b) == 0){
			die('sql error: bad update parameter');
		}
		$array = $this->getfieldsarray();
		$sql = "update " . $this->_tablename_ . " set ";
		foreach ($b as $key => $value){
			$sql .= strtolower($key) . "='" . $value . "',";
		}
		$sql = substr($sql, 0, strlen($sql) - 1);
		$sql .= " where 1";
		foreach ($array as $key => $value){
			$sql .= " and " . $key . "='" . $this->cc($value) . "'";
		}
		$rs = $this->_dbi_->query($sql);
		if (!$rs){
			die('sql error: bad insert: '. $this->_dbi_->geterror());
		}
		return $rs;
	}

	function delete(){
		$array = $this->getfieldsarray();
		$sql = "delete from " . $this->_tablename_ . " where 1";
		foreach ($array as $key => $value){
			$sql .= " and " . $key . "='" . $this->cc($value) . "'";
		}
		$rs = $this->_dbi_->query($sql);
		if (!$rs){
			die('sql error: bad delete: ' . $this->_dbi_->geterror());
		}
		return $rs;
	}

	function insert(){
		$array = $this->getfieldsarray();
		if (count($array) == 0){
			die("sql error: bad insert query");
		}
		$tmpsql1 = "insert into " . $this->_tablename_ . "(";
		$tmpsql2 = '(';
		foreach ($array as $key => $value){
			$tmpsql1 .= $key . ",";
			$tmpsql2 .= "'" . $value . "',";
		}
		$tmpsql1 = substr($tmpsql1, 0, strlen($tmpsql1) - 1);
		$tmpsql1 .= ")values";
		$tmpsql2 = substr($tmpsql2, 0, strlen($tmpsql2) - 1);
		$tmpsql2 .= ')';
		$sql = $tmpsql1 . $tmpsql2;
		$rs = $this->_dbi_->query($sql);
		if (!$rs){
			die('sql error: bad insert: ' . $this->_dbi_->geterror());
		}
		return $rs;
	}

	function numrows($gta=-1, $lta=-1, $neqa=-1){
		$array = $this->getfieldsarray();
		$sql = "select count(*) from " . $this->_tablename_;
		if (count($array) != 0){
			$sql .= ' where 1';
		}
		foreach ($array as $key => $value){
			$sql .= " and " . $key . "='" . $this->cc($value) . "'";
		}
		if ($gta != -1){
			foreach ($gta as $key => $value ){
				$key = strtolower($key);
				if (!isset($this->$key)){
					die('gt field does not exist in your class');
				}
				$sql .= " and " .$key . ">'" . $value . "'";
			}
		}
		if ($lta != -1){
			foreach ($lta as $key => $value ){
				$key = strtolower($key);
				if (!isset($this->$key)){
					die('lt field does not exist in your class');
				}
				$sql .= " and " .$key . "<'" . $value . "'";
			}
		}
		if ($neqa != -1){
			foreach ($neqa as $key => $value ){
				$key = strtolower($key);
				if (!isset($this->$key)){
					die('neq field does not exist in your class');
				}
				$sql .= " and " .$key . "<>'" . $value . "'";
			}
		}
		$rs = $this->_dbi_->query($sql);
		if (!$rs){
			die('sql error: bad select: ' . $this->_dbi_->geterror());
		}
		$row = $this->_dbi_->getrow($rs);
		return $row[0];
	}
}


//table类加入numrows()方法，update()方法传入参数数组键名转换为小写，强化select()功能，现在可以排序和限量查询
//table类增加selectmax()，selectmin()方法，selectnum()方法，参数为取max，min的字段名，修正查询条件字段值不能为0的bug
//table类的numrows()方法参数列表修改为三个数组，分别为大于，小于和不等于查询，select()方法,修正selectmax()min()sum()的bug
//dbi类加入startts(),rollback(),commit()方法，从而提供对事务的支持，table类加入query方法。
?>