<?php
// Class: DBobject
//
// Objectify and automate database access.
//
// Typically used as a superclass. We're using the "_db" prefix in the local
// namespace to clearly see what is done by DBobject and what is done by the
// extending classes that will use it as a base.
//
// It was decided that the class should not discover table columns, primary
// keys and auto_increment attributes automatically, to keep runtime more
// efficient. It also paves the way for future automated table creation.
//
// WARNING:
// Development has stopped, as this superclass uses PHP's MySQL API.  I am
// using PDO now, which solves most of the per-row handling this used to take
// care of.  I'll eventually create a new PDO class for the paging aspect.
//
// Example:
//
// >	// Minimalist subclass example. It expects to represent a single row
// >	// in its lifespan.
// >	class MyTable extends DBobject {
// >
// >		function MyTable($id) {
// >			$this->DBobject(
// >					'user',
// >					'pass',
// >					'dbase',
// >					'MyTable',
// >					false,
// >					array('id' => 0, 'ColumnTwo' => 'default2', 'ColumnThree' => 'default3'),
// >					array('id'),
// >					null,
// >					20
// >			);
// >			$sid = mysql_real_escape_string($id);
// >			$this->_dbFetchRecord(array(
// >					'where' => "id = '$sid'"
// >			));
// >		}
// >
// >		function DESTROY() {
// >			$this->_dbDestroy();
// >		}
// >
// >		function Save() {
// >			return $this->_dbCommitRecord('replace');
// >		}
// >
// >	}
// >
// >
// >	// Now let's use our new subclass!
// >	$foo = new MyTable(34);
// >
// >	// I can safely access columns as properties of MyTable:
// >	echo $foo->ColumnTwo;
// >	$foo->ColumnThree = "bar";
// >	$foo->Save();
//
// TODO:
//
// - Replace <_dbSetColumns()> with overloading.
// - Cache <_dbFetchRecord()> in $_DBOBJECTS as $where => array($columns).
// - Data access on Page should be the same, so an array of DBobject not data direct.
//   (Perhaps DBobject extends Enumeration?)
// - Ask for CREATE TABLE and extract our 3 variables from that.

class DBobject
{
	// Private Group: Database Properties

	// Private Property: _dbconn
	// Resource handle of database connection.
	var $_dbconn;

	// Private Property: _dbislocal
	//
	// Bool defining whether this instance established <_dbconn>. Do not
	// alter this property!
	var $_dbislocal;

	// Private Property: _dbuser
	// Username to log into database with.
	var $_dbuser;

	// Private Property: _dbpass
	// Password to log into database with.
	var $_dbpass;

	// Private Property: _dbhost
	// Database server hostname to connect to.
	var $_dbhost;

	// Private Property: _dbname
	// Name of database.
	var $_dbname;

	// Private Property: _dbtable
	// Name of primary database table to relate to.
	var $_dbtable;

	// Private Property: _dbcolumns
	// Array of column names to care about in this table.
	// Caution: We want an associative array here, so be sure to give values to each key, be it NULL.
	var $_dbcolumns;

	// Private Property: _dbpkeys
	// Array of column names which form the primary key.
	var $_dbpkeys;

	// Private Property: _dbautoinc
	// Name of column which is auto_increment. (Optional.)
	var $_dbautoinc;

	// Private Group: Navigation Properties

	// Private Property: _dbrows_per_page
	//
	// For pagination, configures how many rows are in a page. Defaults to 0
	// to turn pagination off.
	//
	var $_dbrows_per_page;

	// Private Property: _dbpage
	// For pagination, holds the current page number for current query.
	var $_dbpage;

	// Private Property: _dblastpage
	// For pagination, holds the last valid page number for current query.
	var $_dblastpage;

	// Private Property: _dbrows
	// For pagination, holds the total number of results to current query.
	var $_dbrows;

	// Private Group: Query Properties

	// Private Property: _dbquery
	//
	// Array of elements to the current query.
	//
	// select - Columns to return, possibly across multiple tables. Leave
	//          empty to get all columns.
	// from   - List of tables. Leave empty to use default current table.
	// where  - SQL "WHERE" clause contents.
	// group  - SQL "GROUP BY" clause contents.
	// having - SQL "HAVING" clause contents.
	// sort   - SQL "ORDER BY" clause contents.
	// limit  - SQL "LIMIT" clause contents. (Pagination requires for this to
	//          remain empty.)
	var $_dbquery;

	// Private Property: _dbdata
	// Current (page of) results from database.
	var $_dbdata;

	// Private Property: _dberrors
	// Array of error messages.
	var $_dberrors;

	// Group: Methods

	// Constructor: DBobject
	//
	// Create an instance of the DBobject class.
	//
	// You will most likely want to create your own constructor if you're
	// using DBobject as a superclass, and start it by calling this function
	// so that you have sane defaults to start with.  Note that a database
	// connection is not yet opened at this stage, we only set defaults for
	// every property of the class. Database connection will be established
	// if necessary when a first I/O method will get called.
	//
	// Parameters:
	//
	// dbuser  - Username for database server authentication.
	// dbpass  - Password for database server authentication.
	// dbname  - Name of database.
	// dbtable - Name of table.
	// dbconn  - Resource of an existing database connection to use. (False if you have none.)
	// dbcolumns - Array of column names as keys, with default values.
	// dbpkeys   - Array of column names forming the primary key.
	// dbautoinc - Name of column which is auto_increment. (Optional. Null if you have none.)
	// dbrows_per_page - How many rows are in a page. (Optional. Default: no pages.)
	//
	function DBobject($dbuser, $dbpass, $dbname, $dbtable, $dbconn, $dbcolumns, $dbpkeys, $dbautoinc = null, $dbrows_per_page = 0) {
		// Set defaults based on arguments.
		$this->_dbconn = $dbconn;
		$this->_dbislocal = false;
		$this->_dbuser = $dbuser;
		$this->_dbpass = $dbpass;
		$this->_dbname = $dbname;
		$this->_dbtable = $dbtable;
		$this->_dbcolumns = $dbcolumns;
		$this->_dbpkeys = $dbpkeys;
		$this->_dbautoinc = $dbautoinc;
		$this->_dbrows_per_page = $dbrows_per_page;

		// Set defaults for other properties.
		$this->_dbpage = 1;
		$this->_dblastpage = 1;
		$this->_dbrows = 0;
		$this->_dbquery = array();
		$this->_dbdata = array();
		$this->_dberrors = array();
	}

	// Destructor: _dbDestroy
	//
	// Eliminate this instance of the DBobject class. Connection to MySQL
	// will be closed only if it was opened by this instance.
	//
	function _dbDestroy() {
		// PHP 4 can't free objects but we'll try to free as much memory as we
		// can at least.
		if ($this->_dbislocal) {
			mysql_close($this->_dbconn);
		};
		$this->_dbconn = NULL;
		$this->_dbislocal = NULL;
		$this->_dbuser = NULL;
		$this->_dbpass = NULL;
		$this->_dbname = NULL;
		$this->_dbtable = NULL;
		$this->_dbcolumns = NULL;
		$this->_dbpkeys = NULL;
		$this->_dbrows_per_page = NULL;
		$this->_dbpage = NULL;
		$this->_dblastpage = NULL;
		$this->_dbrows = NULL;
		$this->_dbquery = NULL;
		$this->_dbdata = NULL;
		$this->_dberrors = NULL;
	}

	// Destructor: DESTROY
	//
	// Equivalent to <_dbDestroy()>. Since DESTROY() is likely to be defined
	// in subclasses, in this class all the real work is actually done by
	// <_dbDestroy()>.
	//
	function DESTROY() {
		$this->_dbDestroy();
	}

	// Method: _dbEscape
	//
	// Escape string for use in a value inside a query string.
	//
	// Parameters:
	//
	//  string - String to escape.
	//
	// Returns:
	//
	// Input string escaped with the current database engine.
	//
	function _dbEscape($string) {
		// Confirm database connection. (Required by MySQL.)
		if (!$this->_dbConnect()) return false;

		// Actually escape.
		return(mysql_real_escape_string($string, $this->_dbconn));
	}

	// Method: _dbTableExists
	//
	// Verify our table's existence.
	//
	// Returns:
	//
	// True if our table exists, false otherwise.
	//
	function _dbTableExists() {
		$result = false;
		if ($sqlres = mysql_query("SHOW TABLES LIKE '". $this->_dbEscape($this->_dbtable) ."'", $this->_dbconn)) {
			if (($line = mysql_fetch_row($sqlres)) && ($line[0] == $this->_dbtable)) {
				$result = true;
			};
			if (is_resource($sqlres)) mysql_free_result($sqlres);
		};
		return($result);
	}

	// Method: _dbError
	//
	// Get next error message from this instance's stack. More than one error
	// message may be pending, so you may need to call this method in a loop.
	//
	// Returns:
	//
	// A string containing the most recent error message, or NULL if none is
	// left.
	//
	function _dbError() {
		return array_pop($this->_dberrors);
	}

	// Method: _dbSetColumns
	//
	// Set columns in <_dbcolumns> safely. Only keys present in <_dbcolumns>
	// get their values updated, other keys found in the supplied array are
	// safely ignored.
	//
	// Parameters:
	//
	// array - Array of column names and their new values.
	//
	// Returns:
	//
	// The number of <_dbcolumns> items that have been updated.
	//
	function _dbSetColumns($array) {
		$i = 0;
		if (is_array($array)) foreach ($array as $name => $value) {
			if (array_key_exists($name, $this->_dbcolumns)) {
				$this->_dbcolumns[$name] = $value;
				$i++;
			};
		};
		return $i;
	}

	// Method: _dbFetchRecord
	//
	// Fetch a single record. Any <_dbcolumns> keys which are found in the
	// resulting data will have their values updated, *others will remain
	// unchanged*. If you're renaming columns in your "select" or using
	// multiple tables in your "from" however, you need to use the returned
	// array in order to access all of your resulting data.
	//
	// Parameters:
	//
	// args - Array of arguments to build <_dbquery> with. In this method, it
	//        only makes sense to define: where, group, having. One can also
	//        override the defaults for: select, from.
	//
	// Returns:
	//
	// The resulting array if successful, false otherwise.
	//
	// See Also:
	//
	// <_dbquery>
	//
	function _dbFetchRecord($args) {

		// Confirm database connection.
		if (!$this->_dbConnect()) return false;

		$this->_dbquery = $args;
		$this->_dbdata = array();
		// Run query, limit to first result just in case.
		if ($result = mysql_query($this->_dbBuildQuery('select', '1'), $this->_dbconn)) {
			$this->_dbdata = mysql_fetch_assoc($result);
			if (is_resource($result)) mysql_free_result($result);
			$this->_dbSetColumns($this->_dbdata);
			return ($this->_dbdata);
		} else {
			$this->_dberrors[] = mysql_error($this->_dbconn);
			return false;
		}
	}

	// Method: _dbFetchPage
	//
	// Fetch a series of records.
	//
	// Parameters:
	//
	// args - Array of arguments to build <_dbquery> with. If you don't set
	//        "limit" here, pagination will work for you. Leave unset if
	//        you've already defined the current query in a previous call.
	// page - Current page number. Leave unset if you aren't using
	//        pagination. As per <_dbrows_per_page>, pagination is off by
	//        default.
	//
	// Returns:
	//
	// Two-dimensional array with each row of results if successful, false
	// otherwise or if no results were produced.
	//
	// See Also:
	//
	// <_dbquery>
	//
	function _dbFetchPage($args = false, $page = 1) {

		// Confirm database connection.
		if (!$this->_dbConnect()) return false;

		if ($args) $this->_dbquery = $args;
		$this->_dbdata = array();
		$this->_dbpage = $page;
		$this->_dblastpage = 1;
		$this->_dbrows = 0;
		$limit = NULL;

		// Don't split into pages unless necessary.
		if ($this->_dbrows_per_page > 0) {
			$this->_dbrows = $this->_dbCount();
			if ($this->_dbrows > 0) {
				$this->_dblastpage = ceil($this->_dbrows / $this->_dbrows_per_page);
				if ($page > $this->_dblastpage) $page = $this->lastpage;
				$this->_dbpage = $page;
				$limit = 'LIMIT ' . ($page - 1) * $this->_dbrows_per_page . ",{$this->_dbrows_per_page}";
			} else {
				// Empty result, we're done with false but no error.
				return false;
			}
		};

		// Run query with possible limits.
		if ($result = mysql_query($this->_dbBuildQuery('select', $limit), $this->_dbconn)) {
			while ($row = mysql_fetch_assoc($result)) {
				$this->_dbdata[] = $row;
			};
			if (is_resource($result)) mysql_free_result($result);
			return ($this->_dbdata);
		} else {
			$this->_dberrors[] = mysql_error($this->_dbconn);
			return false;
		}

	}

	// Method: _dbCommitRecord
	//
	// Inserts, updates or deletes a row in the table. Precede with a call to
	// <_dbSetColumns()> to define/replace column contents. Columns listed in
	// <_dbpkeys> must all be defined for this to succeed as they are used to
	// uniquely identify the record to process.
	//
	// Note that for updates you should call <_dbFetchRecord()> prior to
	// <_dbSetColumns()> if you want to be absolutely certain of the result in
	// the database. Failure to do so may write unexepcted previous contents
	// of <_dbcolumns> along with your data.
	//
	// Parameters:
	//
	// mode - One of: insert, replace, update, delete.
	//
	// Returns:
	//
	// True if successful, false otherwise.
	//
	function _dbCommitRecord($mode = 'insert') {

		// Confirm database connection.
		if (!$this->_dbConnect()) return false;

		// Run query.
		$this->_dbquery = array();
		if ($qs = $this->_dbBuildQuery($mode)) {
			if ($result = mysql_query($qs, $this->_dbconn)) {
				if (is_resource($result)) mysql_free_result($result);
				return true;
			} else {
				$this->_dberrors[] = mysql_error($this->_dbconn);
				return false;
			}
		} else {
			// Here, an error is already in the stack from _dbBuildQuery(). 
			return false;
		}
	}

	// Method: _dbCount
	//
	// Count how many results a query would yield.
	//
	// Parameters:
	//
	// args - Array of arguments to build <_dbquery> with. Leave unset if
	//        you've already defined the current query in a previous call.
	//
	// Returns:
	//
	// The number of results, or -1 if an error occured.
	//
	function _dbCount($args = false) {
		$result = 0;

		// Confirm database connection.
		if (!$this->_dbConnect()) return -1;

		if ($args) $this->_dbquery = $args;
		
		// Run counting query.
		if ($result = mysql_query($this->_dbBuildQuery('count'), $this->_dbconn)) {
			$qdata = mysql_fetch_row($result);
			if (is_resource($result)) mysql_free_result($result);
			$result = $qdata[0];
		} else {
			$this->_dberrors[] = mysql_error($this->_dbconn);
			$result = -1;
		};

		return $result;
	}

	// Private Group: Private Methods

	// Private Method: _dbConnect
	//
	// Connect to database if necessary. You never need to call this function
	// directly, as it is called by all I/O functions.
	//
	function _dbConnect() {

		// Try to establish database connection if we didn't have one already.
		if (!$this->_dbconn) {
			$this->_dbconn = mysql_connect($this->_dbhost, $this->_dbuser, $this->_dbpass);
			$this->_dbislocal = true;
		};

		// Select our database.
		if ($this->_dbconn) {
			if (mysql_select_db($this->_dbname, $this->_dbconn)) {
				return ($this->_dbconn);
			} else {
				$this->_dberrors[] = mysql_error($this->_dbconn);
				return false;
			}
		} else {
			$this->_dberrors[] = mysql_error($this->_dbconn);
			$this->_dbislocal = false;
			return false;
		}

	}

	// Private Method: _dbBuildQuery
	//
	// Build SQL query string based on <_dbquery> and optional limit argument. 
	// You never need to call this function directly, as it is used by some
	// I/O functions.
	//
	// If <_dbautoinc> is set, "insert" and "replace" modes will remove the
	// value of that column to allow the SQL server to auto_increment.
	//
	// Parameters:
	//
	// type  - One of: count, select, insert, replace, update, delete.
	// limit - If there is no limit in <_dbquery>, use this one. (Optional.)
	//
	// Returns:
	//
	// String representing the SQL query ready to use, or NULL if an error
	// occured.
	//
	function _dbBuildQuery($type, $limit = NULL) {

		// Prepare defaults.
		if (!array_key_exists('select',$this->_dbquery) || (array_key_exists('select',$this->_dbquery) && (strlen($this->_dbquery['select']) <= 0)))
			$this->_dbquery['select'] = '*';
		if (!array_key_exists('from',$this->_dbquery) || (array_key_exists('from',$this->_dbquery) && (strlen($this->_dbquery['from']) <= 0)))
			$this->_dbquery['from'] = $this->_dbtable;
		if ((strlen($limit) > 0) && (!array_key_exists('limit',$this->_dbquery) || (array_key_exists('limit',$this->_dbquery) && (strlen($this->_dbquery['limit']) <= 0))))
			$this->_dbquery['limit'] = $limit;

		$query = NULL;
		
		switch ($type) {

			case 'count':
			case 'select':
				if ($type == 'count') $select = 'count(*)';
				else $select = $this->_dbquery['select'];
				$query = "SELECT $select FROM {$this->_dbquery['from']}";
				if (array_key_exists('where',$this->_dbquery)) $query .= " WHERE {$this->_dbquery['where']}";
				if (array_key_exists('group',$this->_dbquery)) $query .= " GROUP BY {$this->_dbquery['group']}";
				if (array_key_exists('having',$this->_dbquery)) $query .= " HAVING {$this->_dbquery['having']}";
				if (array_key_exists('sort',$this->_dbquery)) $query .= " ORDER BY {$this->_dbquery['sort']}";
				if (array_key_exists('limit',$this->_dbquery)) $query .= " LIMIT {$this->_dbquery['limit']}";
			break;

			case 'insert':
			case 'replace':
			case 'update':
				// We could use "ON DUPLICATE KEY UPDATE" here for 'replace' but
				// it only appeared in MySQL 4.1.0 which is a bit too recent for
				// our goals.

				if ($type == 'insert')  $query = 'INSERT INTO';
				if ($type == 'replace') $query = 'REPLACE INTO';
				if ($type == 'update')  $query = 'UPDATE';
				$query .= " {$this->_dbquery['from']} SET";
				foreach ($this->_dbcolumns as $col => $value) {
					// Unless we have an auto_increment AND the column is that
					// auto_increment AND we're not updating, add column to
					// query.
					if (!( (strlen($this->_dbautoinc) > 0) && ($mode != 'update') && ($col == $this->_dbautoinc) )) {
						$query .= " $col='" . $this->_dbEscape($value) . "',";
					};
				};
				$query = rtrim($query, ',');
				if (($type == 'update') && $this->_dbquery['where']) $query .= " WHERE {$this->_dbquery['where']}";
			break;

			case 'delete':
				$query = "DELETE FROM {$this->_dbquery['from']} WHERE";
				foreach ($this->_dbpkeys as $col) {
					// CAUTION: This assumes that '' is invalid for a primary key.
					if (strlen($this->_dbcolumns[$col]) > 0) {
						$query .= " $col='" . $this->_dbEscape($this->_dbcolumns[$col]) . "',";
					} else {
						$this->_dberrors[] = "DBobject::dbBuildQuery(): Missing value for primary key '$col'.";
					}
				};
				if (count($this->_dberrors) <= 0) {
					$query = rtrim($query, ',');
				} else {
					$query = NULL;
				}
			break;

			default:
				$this->_dberrors[] = "DBobject::dbBuildQuery(): Unknown type '$type'.";

		};

		return $query;
	}

}