Database Module

From FreeDESK
Jump to: navigation, search

Database modules provide the ability for different underlying databases to be used. They handle the connection, escaping and some other functionality.

The default shipped module supports MySQL and any alternatives must be derived from DatabaseBase. The base class and MySQL implementation are included below.

The DatabaseBase also includes the QueryBuilder class which can be used anywhere in the system to build SQL queries easily (the database implementation must therefore support QueryBuilder objects for SQL construction).

DatabaseBase Base Class 

<?php 
/* -------------------------------------------------------------
This file is part of FreeDESK
 
FreeDESK is (C) Copyright 2012 David Cutting
 
FreeDESK is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
FreeDESK is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with FreeDESK.  If not, see www.gnu.org/licenses
 
For more information see www.purplepixie.org/freedesk/
-------------------------------------------------------------- */
/**
 * Database Query Type
**/
abstract class QueryType
{
	const Equal = 0;
	const Like = 1;
	const MoreThan = 2;
	const MoreThanEqual = 3;
	const LessThan = 4;
	const LessThanEqual = 5;
	const NotEqual = 6;
 
	const OpenBracket = 100;
	const CloseBracket = 101;
 
	const opAND = 200;
	const opOR = 201;
}
 
/**
 * Query Builder Class
**/
class QueryBuilder
{
	/**
	 * Query items array
	**/
	var $items = array();
 
	/**
	 * Limit Flag
	**/
	var $limit = false;
 
	/**
	 * Start (for limit)
	**/
	var $start = 0;
 
	/**
	 * Entries (for limit)
	**/
	var $entries = 30;
 
	/**
	 * Order Flag
	**/
	var $order = false;
 
	/**
	 * Order fields
	**/
	var $orderlist = array();
 
	/**
	 * Add item
	 * @param string $field Field
	 * @param mixed $type QueryType const
	 * @param mixed $value Value
	**/
	function Add($field, $type, $value)
	{
		$this->items[] = array(
			"field" => $field,
			"type" => $type,
			"value" => $value );
	}
 
	/**
	 * Add an order field
	 * @param string $field Field
	 * @param bool $asc Ascending (optional, default true) - false is descending
	**/
	function AddOrder($field, $asc = true)
	{
		if (!$this->order)
			$this->order = true;
		$this->orderlist[$field]=$asc;
	}
 
	/**
	 * Open bracket
	**/
	function OpenBracket()
	{
		$this->items[] = array("type" => QueryType::OpenBracket);
	}
 
	/**
	 * Close bracket
	**/
	function CloseBracket()
	{
		$this->items[] = array("type" => QueryType::CloseBracket);
	}
 
	/**
	 * Add operation
	 * @param mixed $operation Op of type QueryType
	**/
	function AddOperation($operation)
	{
		$this->items[] = array("type" => $operation);
	}
}
 
 
/**
 * DatabaseBase is the abstract base class for database system implementations
**/
 
abstract class DatabaseBase
{
	/**
	 * Constructor
	 * @param object $freeDESK FreeDESK instance
	**/
	//abstract function DatabaseBase(&$freekDESK);
 
	/**
	 * Connect
	 * @param string $server Database server
	 * @param string $username Database username
	 * @param string $password Database password
	 * @param string $database Database name
	 * @param string $prefix Database table prefix (optonal, default "")
	 * @return bool Successful connection or not
	**/
	abstract function Connect($server, $username, $password, 
		$database, $prefix="");
 
	/**
	 * Disconnect
	**/
	abstract function Disconnect();
 
	/**
	 * Return table name with correct prefix and escaping
	 * @param string $table table un-prefixed
	 * @return string table with prefix and escape
	**/
	abstract function Table($table);
 
	/**
	 * Sanitise user-input using correct escaping
	 * @param string $input user input
	 * @return string Sanitised output
	**/
	abstract function Safe($input);
 
	/**
	 * Sanitise user-input string and quote
	 * @param string $input user input
	 * @return string Sanitised quoted output
	**/
	abstract function SafeQuote($input);
 
 
 
	/**
	 * Contain a field correctly
	 * @param string $field The field name
	 * @return string Escaped field
	**/
	abstract function Field($field);
 
	/**
	 * Escape and contain a field correctly
	 * @param string $value The value of the field
	 * @return string Escaped and prefixed+suffixed data
	**/
	function FieldSafe($value)
	{
		return $this->Field($this->Safe($value));
	}
 
	/**
	 * Perform a query
	 * @param string $query SQL query
	 * @param bool $report Record any errors using LoggingEngine (optonal, default true)
	 * @return mixed Results of query
	**/
	abstract function Query($query, $report=true);
 
	/**
	 * Number of rows affected by last query
	 * @return int number of rows affected
	**/
	abstract function RowsAffected();
 
	/**
	 * Number of rows in a result set
	 * @param mixed $result Result set
	 * @return int number of rows in the set
	**/
	abstract function NumRows(&$result);
 
	/**
	 * Fetch next associated array from result set
	 * @param mixed $result Result Set
	 * @return array Assocative Array of Results
	**/
	abstract function FetchAssoc(&$result);
 
	/**
	 * Free a result set
	 * @param mixed $result Result Set
	**/
	abstract function Free(&$result);
 
	/**
	 * Return an error flag
	 * @return bool Experienced error on last command
	**/
	abstract function Error();
 
	/**
	 * Last error code
	 * @return int Error code
	**/
	abstract function ErrorCode();
 
	/**
	 * Last error description
	 * @return string Error description
	**/
	abstract function ErrorDescription();
 
	/**
	 * Details of the last error
	 * @return string Code and error description
	**/
	function LastError()
	{
		return $this->ErrorCode().": ".$this->ErrorDescription();
	}
 
	/**
	 * The last inserted ID
	 * @return mixed Last inserted ID
	**/
	abstract function InsertID();
 
	/**
	 * Generate a clause from a QueryBuilder object
	 * @param object &$query QueryBuilder object
	 * @return string query string
	**/
	abstract function Clause(&$query);
 
}
 
?>

MySQL Database Implementation 

<?php 
/* -------------------------------------------------------------
This file is part of FreeDESK
 
FreeDESK is (C) Copyright 2012 David Cutting
 
FreeDESK is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
FreeDESK is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with FreeDESK.  If not, see www.gnu.org/licenses
 
For more information see www.purplepixie.org/freedesk/
-------------------------------------------------------------- */
 
/**
 * MySQL concrete implementation of DatabaseBase
**/
 
class MySQL extends DatabaseBase
{
	/**
	 * Pointer to the FreeDESK instance
	**/
	private $DESK = null;
	/**
	 * MySQL data connection
	**/
	private $connection = null;
 
	/**
	 * Table prefix
	**/
	var $prefix = "";
 
	/**
	 * Constructor
	 * @param object $freeDESK FreeDESK instance
	**/
	function MySQL(&$freeDESK)
	{
		$this->DESK = &$freeDESK;
 
		$this->DESK->PluginManager->Register(new Plugin(
			"MySQL Database Engine","0.01","Core","DB" ));
	}
 
	/**
	 * Connect
	 * @param string $server Database server
	 * @param string $username Database username
	 * @param string $password Database password
	 * @param string $database Database name
	 * @param string $prefix Database table prefix (optonal, default "")
	 * @return bool Successful connection or not
	**/
	function Connect($server, $username, $password, 
		$database, $prefix="")
	{
		$this->prefix = $prefix;
 
		$this->connection = mysql_connect($server, $username, $password);
		if ($this->connection <= 0) return false;
 
		if (!mysql_select_db($database, $this->connection))
			return false;
 
		return true;
	}
 
	/**
	 * Disconnect
	**/
	function Disconnect()
	{
		mysql_close($this->connection);
	}
 
	/**
	 * Return table name with correct prefix and escaping
	 * @param string $table table un-prefixed
	 * @return string table with prefix and escape
	**/
	function Table($table)
	{
		return "`".$this->prefix.$table."`";
	}
 
	/**
	 * Sanitise user-input using correct escaping
	 * @param string $input user input
	 * @return string Sanitised output
	**/
	function Safe($input)
	{
		return mysql_real_escape_string($input, $this->connection);
	}
 
 
	/**
	 * Sanitise user-input string and quote
	 * @param string $input user input
	 * @return string Sanitised quoted output
	**/
	function SafeQuote($input)
	{
		return "\"".$this->Safe($input)."\"";
	}
 
 
	/**
	 * Contain a field correctly
	 * @param string $field The field name
	 * @return string Escaped field
	**/
	function Field($field)
	{
		return "`".$field."`";
	}
 
	/**
	 * Escape and contain a field correctly
	 * @param string $value The value of the field
	 * @return string Escaped and prefixed+suffixed data
	**/
	function FieldSafe($value)
	{
		return $this->Field($this->Safe($value));
	}
 
	/**
	 * Perform a query
	 * @param string $query SQL query
	 * @param bool $report Record any errors using LoggingEngine (optonal, default true)
	 * @return mixed Results of query
	**/
	function Query($query, $report=true)
	{
		$result=mysql_query($query, $this->connection);
 
		if ($report && $this->Error()) // has an error and to be reported
		{
			$err="Query Failed: ".$query;
			$error="SQL Error: ".$this->LastError();
			$this->DESK->LoggingEngine->Log($err, "SQL", "Fail", 1);
			$this->DESK->LoggingEngine->Log($error, "SQL", "Error", 1);
		}
 
		return $result;
	}
 
	/**
	 * Number of rows affected by last query
	 * @return int number of rows affected
	**/
	function RowsAffected()
	{
		return mysql_affected_rows($this->connection);
	}
 
	/**
	 * Number of rows in a result set
	 * @param mixed $result Result set
	 * @return int number of rows in the set
	**/
	function NumRows(&$result)
	{
		return mysql_num_rows($result);
	}
 
	/**
	 * Fetch next associated array from result set
	 * @param mixed $result Result Set
	 * @return array Assocative Array of Results
	**/
	function FetchAssoc(&$result)
	{
		return mysql_fetch_assoc($result);
	}
 
	/**
	 * Free a result set
	 * @param mixed $result Result Set
	**/
	function Free(&$result)
	{
		mysql_free_result($result);
	}
 
	/**
	 * Return an error flag
	 * @return bool Experienced error on last command
	**/
	function Error()
	{
		if (mysql_errno($this->connection)>0)
			return true;
		return false;
	}
 
	/**
	 * Last error code
	 * @return int Error code
	**/
	function ErrorCode()
	{
		return mysql_errno($this->connection);
	}
 
	/**
	 * Last error description
	 * @return string Error description
	**/
	function ErrorDescription()
	{
		return mysql_error($this->connection);
	}
 
	/**
	 * The last inserted ID
	 * @return mixed Last inserted ID
	**/
	function InsertID()
	{
		return mysql_insert_id($this->connection);
	}
 
	/**
	 * Generate a clause from a QueryBuilder object
	 * @param object &$query QueryBuilder object
	 * @return string query string
	**/
	function Clause(&$query)
	{
		$c = "";
		foreach($query->items as $item)
		{
			if (isset($item['field']))
			{
				if ($c!="")
					$c.=" ";
				$c.=$this->Field($item['field']);
 
				switch($item['type'])
				{
					case QueryType::Equal:
						$c.="=";
						break;
					case QueryType::Like:
						$c.=" LIKE ";
						break;
					case QueryType::MoreThan:
						$c.=" > ";
						break;
					case QueryType::MoreThanEqual:
						$c.=" >= ";
						break;
					case QueryType::LessThan:
						$c.=" < ";
						break;
					case QueryType::LessThanEqual:
						$c.=" <= ";
						break;
					case QueryType::NotEqual;
						$c.=" != ";
						break;
				}
 
				$c.=$item['value'];
			}
			else
			{
				switch($item['type'])
				{
					case QueryType::OpenBracket:
						$c.=" ( ";
						break;
					case QueryType::CloseBracket:
						$c.=" ) ";
						break;
					case QueryType::opAND:
						$c.=" AND ";
						break;
					case QueryType::opOR:
						$c.=" OR ";
						break;
				}
			}
		}
 
		if ($c=="")
			$c="1";
 
		return $c;
	}
}
 
?>
Retrieved from "http://wiki.freedesk.purplepixie.org/w/index.php?title=Database_Module&oldid=71"
Personal tools
Namespaces

Variants
Actions
Navigation
Toolbox