path: root/web/include/DB.inc

<?
/* Database Abstraction Class
 *
 * (c) 2002 Peter Palfrader <pp@3node.com>
 *          Florian Reitmeir <fr@3node.com>
 */

/**
 * Database Abstraction Class
 *
 * This is a small database abstraction class based on ADODB
 *
 * It provides only a very small set of functions but has
 * proven to be sufficient for most projects.
 *
 * @author	Peter Palfrader/3node
 * @author	Florian Reitmeir/3node
 * @version	$Id$
 */

include('/usr/share/adodb/adodb.inc.php');

class Database {
	var $connection;
	var $errorMsg;
	var $errorNo;

	/**
	 * create a new database object
	 *
	 * The constructor creates a database connection to the database
	 * specified with <code>host</code>, <code>user</code>,
	 * <code>password</code>, and <code>name</code>.
	 * 
	 * <code>type</code> specifies the kind of database you want to use
	 * (postgres, mysql, etc.)
	 *
	 * @param	string	type of DBMS
	 * @param	string	host where the DBMS runs
	 * @param	string	the username to connect as
	 * @param	string	the password to authenticate with
	 * @param	string	the name of the database to connect to
	 * @returns	boolean	true on success, false on error
	 */
	function Database($type, $host, $user, $password, $name) {
		$this->connection = &ADONewConnection($type);
		#if (!$this->connection->PConnect($host, $user, $password, $name))
		if (!$this->connection->Connect($host, $user, $password, $name))
			return false;

		assert( $this->connection );
		$this->database['ErrorMsg'] = '';
		$this->database['ErrorNo'] = 0;

		return true;
	}


	/** 
	 * remove/quote evil characters
	 *
	 * This function calls the database backend's quote string
	 * function which is supposed to quote evil charaters
	 * like semicolons, single and double quotes etc so that they
	 * can be used in SQL queries.
	 *
	 * @param	string	the string which should be cleaned
	 * @returns	string	the cleaned string
	 */
	function clean_string(&$string)
	{
		assert( $this->connection );
		$result = preg_replace ('/[^A-Za-z0-9_-]+/', '', $string);
		return $result;
	}


	/**
	 * execute an SQL query with parameters
	 *
	 * This function executes the SQL query passed in <code>query</code>.
	 * The query is first prepared and then executed with the values
	 * passed in <code>params</code>.
	 *
	 * The ADODB backend emulates binding of parameters if the database
	 * does not support it natively. Only <code>params</code>' values
	 * are passed to the binding, not its keys.
	 *
	 * You probably do not want to call this function from your code.
	 * Use the <code>query_row</code>, <code>query_all</code>,
	 * <code>insert</code>, or <code>update</code> function depending
	 * on what you want to do.
	 * 
	 * This function returns a recordset which is an ADODB result type.
	 * Please * refer to the ADODB documentation for details. On error
	 * false is * returned and <code>errorMsg</code> and <code>errorNo</code>
	 * are set appropriatly.
	 *
	 * @param	string	the SQL query to execute
	 * @param	array	the values to bind to the execute
	 * @returns	recordset	A recordset on success, false otherwhise
	 */
	function execute_query($query, &$params)
	{
		assert( $this->connection );
		$this->errormsg = '';
		$this->errorno = 0;
		
		$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
		$values = array_values($params);
		$stmt = $this->connection->Prepare($query);
		$recordset = $this->connection->Execute($stmt, $values);

		if ($recordset === false)
		{
			$this->errorMsg = $this->connection->ErrorMsg();
			$this->errorNo = $this->connection->ErrorNo();
		}
		return $recordset;
	}

	/**
	 * execute an SQL query and return the first row
	 *
	 * This function executes the SQL query passed in <code>query</code>.
	 * It takes an optional array of parameters and returns the fields of
	 * the first result row as an assoziative array.
	 *
	 * <code>execute_query</code> is called to do the real work.
	 *
	 * @param	string	the SQL query to execute
	 * @param	array	the values to bind to the execute
	 * @returns	array	An assiziative array on success, false otherwhise
	 */
	function query_row($query, $params=array())
	{
		assert( $this->connection );
		$recordset = $this->execute_query($query, $params);
		if ($recordset === False)
			return False;
		else
			return $recordset->fields;
	}

	/**
	 * execute an SQL query and return all results
	 *
	 * This function executes the SQL query passed in <code>query</code>.
	 * It takes an optional array of parameters and returns an array of
	 * assoziative arrays (one per result row).
	 *
	 * <code>execute_query</code> is called to do the real work.
	 *
	 * @param	string	the SQL query to execute
	 * @param	array	the values to bind to the execute
	 * @returns	array	An array of assiziative arrays on success, false otherwhise
	 */
	function query_all($query, $params=array())
	{
		assert( $this->connection );
		$recordset = $this->execute_query($query, $params);

		if ($recordset === False)
			return False;
		
		$output = array();
		while (! $recordset->EOF)
		{
			$output[] = $recordset->fields;
			$recordset->MoveNext();
		}

		return $output;
	}

	/**
	 * Insert values into a table
	 *
	 * <code>insert</code> inserts a new row into the table <code>table</code>.
	 * The values to insert are taken from the assoziative array
	 * <code>params</code>.
	 *
	 * This function handles selection of the primary key automatically if
	 * no <code>ref</code> value is set in the <code>params</code>.
	 *
	 * The SQL query is built using tablename and the params passed as
	 * arguments. <code>execute_query</code> is called to do the real work.
	 * 
	 * @param	string	the table to insert the new data into
	 * @param	array	assoziative array of values to insert
	 * @params	string	name of the tables primary key (defaults to <code>ref</code>)
	 * @returns	boolean	True on success, false otherwhise
	 */
	function insert($table, $params=array(), $refname = "ref")
	{
		assert( $this->connection );
		$this->errorMsg = '';
		$this->errorNo = 0;

		$keys = array_map($this->clean_string, array_keys($params));
		$qmarks = array();
		$values = array_values($params);
		foreach ($params as $key=>$value)
		{
			$qmarks[] = "?";
		}

		if ($params[$refname])
			$sqlinsert='insert into "'.$this->clean_string($table).'" ('.implode(',', $keys).') values ('.implode(',',$qmarks).')';
		else
			$sqlinsert='insert into "'.$this->clean_string($table).
				'" ('.$this->clean_string($refname).', '.implode(',', $keys).')
				values ( (select coalesce( max('.$this->clean_string($refname).')+1, 1 ) from "'.$this->clean_string($table).'"), ' . implode(',',$qmarks).')';

		$stmt = $this->connection->Prepare($sqlinsert);
		$recordset = $this->connection->Execute($stmt, $values);

		if ($recordset === False)
		{
			$this->errorMsg = $this->connection->ErrorMsg();
			$this->errorNo = $this->connection->ErrorNo();
			return False;
		}
		else
			return True;
	}

	/**
	 * Update a row in a table
	 *
	 * This function updates the table row given by the primary key
	 * <code>ref</code>. The new values are to be passed via the assoziative
	 * array <code>params</code>. An optional list of columns which should
	 * be set to NULL may be given too.
	 *
	 * The SQL query is built using tablename and the params passed as
	 * arguments. <code>execute_query</code> is called to do the real work.
	 * 
	 * @param	string	the table to update the row data in
	 * @param	integer	the primary key of the row to update
	 * @param	array	assoziative array of values to insert
	 * @param	array	array of columns to set to NULL
	 * @params	string	name of the tables primary key (defaults to <code>ref</code>)
	 * @returns	boolean	True on success, false otherwhise
	 */
	function update($table, $ref, $params=array(), $nullify = array(), $refname="ref")
	{
		assert( $this->connection );
		$this->errorMsg = '';
		$this->errorNo = 0;

		$sqlparams = array();
		foreach ( $params as $key=>$value )
		{
			$sqlparams[]=$this->clean_string($key).'=?';
		}
		foreach ( $nullify as $key )
		{
			$sqlparams[]=$this->clean_string($key).'= null';
		}
		
		$values = array_values($params);
		$values[] = $ref;

		$sqlupdate='update "'.$this->clean_string($table).'" set '.implode(',', $sqlparams).' where '.$this->clean_string($refname).'=?';
		$stmt = $this->connection->Prepare($sqlupdate);
		$recordset = $this->connection->Execute($stmt, $values);

		if ($recordset === False)
		{
			$this->errorMsg = $this->connection->ErrorMsg();
			$this->errorNo = $this->connection->ErrorNo();
			return False;
		}
		else
			return True;
	}

	/**
	 * Delete a row in a table
	 *
	 * This function delete the table row given by the primary key
	 * <code>ref</code>.
	 * 
	 * @param	string	the table to update the row data in
	 * @param	integer	the primary key of the row to update
	 * @params	string	name of the tables primary key (defaults to <code>ref</code>)
	 * @returns	boolean	True on success, false otherwhise
	 */
	function delete_row($table, $ref, $refname="ref")
	{
		assert( $this->connection );
		$this->errorMsg = '';
		$this->errorNo = 0;

		$values = array( $ref );
		$sqldelete='delete from "'.$this->clean_string($table).'" where '.$this->clean_string($refname).'=?';
		$stmt = $this->connection->Prepare($sqldelete);
		$recordset = $this->connection->Execute($stmt, $values);

		if ($recordset === False)
		{
			$this->errorMsg = $this->connection->ErrorMsg();
			$this->errorNo = $this->connection->ErrorNo();
			return False;
		}
		else
			return True;
	}

	/**
	 * error function
	 *
	 * This function returns a string specifying the Error Code and Message
	 * from the last statement, or the empty string if no error was raised.

	 * @returns	string	ErrorCode and Message if there was an error, an empty string otherwhise
	 */
	function error()
	{
		if ($this->errorNo)
			return "ErrorNo: ".$this->errorNo."; ErrorMsg: ".$this->errorMsg.";";
		else
			return "";
	}
};

# vim:set ts=4:
# vim:set shiftwidth=4:
?>