<?php

/*========================================================*\
||########################################################||
||#                                                      #||
||#     WB News v2.0.0                                   #||
||# ---------------------------------------------------- #||
||#     Copyright (c) 2004-2007                          #||
||#     Created: 22nd Dec 2006                           #||
||#     Filename: DBase.php                              #||
||#                                                      #||
||########################################################||
/*========================================================*/

/**
 *  @author $Author: pmcilwaine $
 *	@version $Id: DB_Sql.php,v 1.1.2.4.2.1 2008/07/14 11:07:43 pmcilwaine Exp $
 */

class DB_Sql
{

	var $dbhost = NULL;
	var $dbuser = NULL;
	var $dbpass = NULL;
	var $dbname = NULL;
	var $dbport = 3306;

	var $dbconn;
	var $dbres;

	var $connected = FALSE;

	var $current_record = array();
	var $last_sql = NULL;

	var $err_msg;

	/**
	 *	this is important to note for transactions with mysql
	 *	if we have one we must be able to rollback errors
	 *	
	 *	there are no guarantees this will not create further corruption
	 */
	var $transaction = FALSE;
	var $transaction_sql = array();

	/** DEBUG **/
	var $query_counter = 0;

	/** public properties used to determine what the DB can do **/
	var $triggers = FALSE;
	var $transactions = FALSE;

	/** get type **/
	var $db_type = "MySQL";
	
	function DB_Sql( $dbhost, $dbuser, $dbpass, $dbname, $dbport = 3306 )
	{
		$this->dbhost = $dbhost;
		$this->dbuser = $dbuser;
		$this->dbpass = $dbpass;
		$this->dbname = $dbname;
		$this->dbport = $dbport;

		return $this->connect();
	}

	/**
	 *	Make a connection to the database
	 */
	function connect()
	{
		$this->dbconn = @mysql_connect(
			$this->dbhost . ":" . $this->dbport ,
			$this->dbuser,
			$this->dbpass,
			TRUE
			);
			
		if ( !$this->dbconn )
		{
			$this->err_msg = mysql_error();
			return FALSE;
		}

		if ( !@mysql_select_db( $this->dbname ) )
		{
			$this->err_msg = mysql_error( $this->dbconn );
			return FALSE;
		}

		/** set our session **/
		$this->query( "SET SESSION sql_mode='POSTGRESQL'" );

		$this->connected = TRUE;
		return TRUE;
	}

	/**
	 *	Run an SQL Query on the database
	 *	
	 *	@param String $sql A query string
	 *	@return bool
	 */
	function query( $sql )
	{
		/** TODO add transaction support **/
		$this->last_sql = trim($sql);
		$this->current_record = array();

		/** things to replace **/
		
		$this->dbres = @mysql_query( $sql, $this->dbconn );
		$this->query_counter++;

		if ( !$this->dbres )
		{
			return FALSE;
		}

		return TRUE;

		/** transactions **/
		$this->last_sql = trim($sql);
		if ( ( "BEGIN" == strtoupper($this->last_sql) ) && !$this->transaction )
		{
			$this->transaction = TRUE;
		}

		$this->current_record = array();

		// we need to look for Updates & Delete so we can easily rollback
		if ( $this->transaction && preg_match("/^update/i", $this->last_sql) )
		{
			preg_match( "/update\\s+(.+?)\\s+/i", $this->last_sql, $table );
			preg_match( "/where\\s(.+?)$/i", $this->last_sql, $where );

			if ( $table )
			{
				$table = $table[1];
			}

			if ( $where )
			{
				$where = $where[1];
			}

			// we need to make a select to get the current record values

			// build a new query
			$affected_records = array();
			$sql = "SELECT * FROM $table" . ( $where  ? " WHERE $where" : "" );
			$res = @mysql_query( $sql );
			if ( !$res )
			{
				// FAILED we need to stop everything ( and give the script a chance to roll
				// back what we have done
			}
			
			while ( $row = mysql_fetch_assoc($res) )
			{
				$affected_records[] = $row;
			}
		}
		else if ( $this->transaction && preg_match("/^delete/i", $this->last_sql) )
		{
			// get affected deleted records
			preg_match( "/delete\\sfrom\\s+(.+?)\\s+/i", $this->last_sql, $table );
			preg_match( "/where\\s(.+?)$/i", $this->last_sql, $where );
			
			if ( $table )
			{
				$table = $table[1];
			}

			if ( $where )
			{
				$where = $where[1];
			}

			$affected_records = array();
			$sql = "SELECT * FROM $table" . ( $where ? " WHERE $where" : "" );
			$res = @mysql_query( $sql );
			if ( !$res )
			{
				// FAILED we need to stop everything ( and give the script a chance to roll
				// back what we have done
			}

			while ( $row = mysql_fetch_assoc($res) )
			{
				$affected_records[] = $row;
			}
		}
		
		$this->dbres = @mysql_query( $this->last_sql );
		
		if ( $this->transaction )
		{
			// check what type of query it was
			if ( preg_match("/^insert/i", $this->last_sql )  )
			{
			}
		}
		
		if ( !$this->dbres )
		{
			return FALSE;
		}
	}

	/**
	 *	We just need to return mysql_fetch_assoc 
	 *	an empty array means FALSE whilst an array
	 *	with values with be TRUE thats all we need to know
	 *	
	 *	@return bool
	 */
	function next_record()
	{
		if ( !is_resource( $this->dbres ) )
		{
			return FALSE;
		}
		
		$this->current_record = mysql_fetch_assoc( $this->dbres );
		if ( $this->current_record )
		{
			return TRUE;
		}

		return FALSE;
	}

	/**
	 *
	 *	@return int
	 */
	function numrows()
	{
		return mysql_num_rows( $this->dbres );
	}

	/**
	 *	@return mixed
	 */
	function lastid()
	{
		return mysql_insert_id();
	}

	/**
	 *	@return int
	 */
	function affectedrows()
	{
		if ( !is_resource( $this->dbres ) )
		{
			return FALSE;
		}
		
		return @mysql_affected_rows( $this->dbres );
	}

	/**
	 *
	 *	@param String $name
	 *	@return mixed
	 */
	function field( $name )
	{
		if ( !$this->dbres )
		{
			return FALSE;
		}
		
		return $this->current_record[$name];
	}

	/**
	 * string NewID( $seq );
	 *
	 * For MySQL this returns NULL as we use auto_increment
	 * 
	 * @param string $seq the sequence to get the id from
	 * @return string
	 */
	function NewID( $seq )
	{
		return "NULL";
	}

	/**
	 * string escape( string $string );
	 * 
	 * Return an escaped string
	 * 
	 * @param string $string
	 * @return string
	 */
	function escape( $string )
	{
		return mysql_real_escape_string( $string, $this->dbconn );
	}
	
	function GetVersion()
	{
		$this->query( "SELECT version() as v" );
		if ( $this->next_record() )
		{
			return $this->field( "v" );
		}

		return FALSE;
	}

	function GetTableList()
	{
		$this->query( "SHOW TABLES" );

		$ret = array();
		while ( $this->next_record() )
		{
			$ret[] = $this->field( "Tables_in_" . $this->dbname );
		}

		return $ret;
	}

	function GetTableFields( $table )
	{
		if ( NULL == $table )
		{
			return FALSE;
		}
		
		$this->query( "SHOW FIELDS FROM \"$table\"" );
		$ret = array();
		
		while ( $this->next_record() )
		{
			$ret[] = $this->current_record;
		}

		return $ret;
	}

	function getTableKeys($table)
	{
		if ( NULL == $table )
		{
			return FALSE;
		}

		$this->query( "SHOW KEYS FROM \"$table\"" );
		$ret = array();

		while ( $this->next_record() )
		{
			$ret[] = $this->current_record;
		}

		return $ret;
	}

}

?>