Location: PHPKode > scripts > Lightweight Club Calendar > lc-calendar-0.9.4/drivers/event/pearmdb2.class.php
<?php
/**
* PEAR::MDB2 based EventDriver for LCC
*
* PHP Version 4, 5
*
* @author <hide@address.com>
* @copyright Copyright (c) 2007, Benedikt Hallinger
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library 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
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the
* Free Software Foundation, 51 Franklin St, Fifth Floor,
* Boston, MA  02110-1301  USA
*/

/**
* Inclusion of PEAR::MDB2
* This needs MDB2 installed in the PHP include path
*/
@include_once 'MDB2.php';
class_exists('MDB2') or die('<b>'.__FILE__.' ERROR:</b> PEAR::MDB2 not found! Please make sure that it is properly installed.');

/**
* A EventDriver that is capaple of storing and retrieving Eventdata from various databases
*
* PEAR::MDB2 (http://pear.php.net/package/MDB2) is a database abstraction layer
* for PHP supporting many different databases.
* Using this driver, you are able to store events in all mayor databases
* like MySQL, MSSQL, oracle and many others.
*
* This driver needs a working PEAR installation with the MDB2 package installed.
* In addition to that, you need to install the DB-Driver package of the
* driver you want to use.
*
* The DB schema must be prepared in such a way, that all data for an event
* could be stored in a single table. Primary keys should be 'id' and 'field'.
* 'id' should be an int, 'field' should be varchar and 'data' should be text.
* +---------+----------------+-------------------+
* |   id    |   field        |         data      |
* +---------+----------------+-------------------+
* |       1 | title          | test1             |
* |       1 | description    | Testevent         |
* |       1 | ...            | ...               |
* |       2 | title          | test1             |
* |       2 | description    | Testevent         |
* |       2 | ...            | ...               |
* |     ... | ...            | ...               |
* +---------+----------------+-------------------+
*
* Possible configuration:
*   'DSN'                  The data source name, eg. "mysql://foo:hide@address.com:port/database".
*                          See http://pear.php.net/manual/en/package.database.mdb2.intro-dsn.php
*
*   'options'              array of options of the connection.
*                          See http://pear.php.net/manual/en/package.database.mdb2.intro-connect.php
*
*   'table'                Name of the table where events data is to be stored (default: 'events')
*   'col_id'               Name of the row for event id          (default: 'id')
*   'col_field'            Name of the row for event field names (default: 'field')
*   'col_data'             Name of the row for event field data  (default: 'data')
*
* @see http://pear.php.net/package/MDB2
*/
class LCC_EventDriver_PearMDB2 extends LCC_EventDriver
{
	/**
	* Default config of the driver
	*
	* @var array
	*/
	var $config = array(
			'DSN'        => '',              // DSN to connect to, e.g. mysql://foo:hide@address.com:port/database
			'options'    => array(
					'ssl'              => false,   // Use SSL for the connection (recommendet, but disabled for compatibility)
					'persistent'       => true     // Use a persistent DB link
				),
			'table'      => 'events',        // Table name in which events are to be stored
			'col_id'     => 'id',            // Name of the row for event id
			'col_field'  => 'field',         // Name of the row for event field names
			'col_data'   => 'data'           // Name of the row for event field data
		);
		
	/**
	* PEAR::MDB2 object, the connected Database
	*
	* The connection is established within {@link configure()} below.
	*
	* @var MDB2
	* @access private
	*/
	var $_mdb2 = null;
	
	/**
	* Caches for getEventsForDate()
	*
	* @var array
	* @access private
	*/
	var $_getEventsForDate_Cache = array();
	var $_getEventsForDate_SQLCache = null;
	
	/**
	* Returns the IDs of events for a given date
	*
	* This is a cached version because otherwise we would make tons of DB querys
	*
	* @param int $timestamp       UNIX timestamp for the day in question
	* @return false|array         Indexed array containing the ID's of events for $timestamp
	* @see $_getEventsForDate_Cache
	* @todo Performance tuning needet! currently, all events start and end dates will be read from the database!
	*/
	function getEventsForDate($timestamp)
	{
		if (!array_key_exists($timestamp, $this->_getEventsForDate_Cache) || !is_array($this->_getEventsForDate_Cache[$timestamp])) {
			// Day not cached, try to fill cache
			// This is done from an cached SQL result, if possible
			if (null === $this->_getEventsForDate_SQLCache) {
				$sql = 'SELECT '.$this->_mdb2->quoteIdentifier($this->config['col_id']).', '.$this->_mdb2->quoteIdentifier($this->config['col_field']).', '.$this->_mdb2->quoteIdentifier($this->config['col_data']).'
					FROM '.$this->_mdb2->quoteIdentifier($this->config['table']).'
					WHERE '.$this->_mdb2->quoteIdentifier($this->config['col_field']).' = \'start_date\'
					OR '.$this->_mdb2->quoteIdentifier($this->config['col_field']).' = \'end_date\'';
				$res =& $this->_mdb2->query($sql);
			
				// Always check that result is not an error
				if (PEAR::isError($res)) {
					return false;
				}
				
				$this->_getEventsForDate_SQLCache = array();
				while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) {
					$this->_getEventsForDate_SQLCache[$row[$this->config['col_id']]][$row[$this->config['col_field']]] = $row[$this->config['col_data']];
				}
				// cache is filled now or empty array
				$res->free();
			}

			// Fill the days events cache
			$this->_getEventsForDate_Cache[$timestamp] = array(); // no events for this day by default
			foreach ($this->_getEventsForDate_SQLCache as $eventid => $event) {
				if ($event['start_date'] <= $timestamp && $event['end_date'] >= $timestamp) {
					// events found!
					if (!is_array($this->_getEventsForDate_Cache[$timestamp])) {
						$this->_getEventsForDate_Cache[$timestamp] = array($eventid);
					} else {
						array_push($this->_getEventsForDate_Cache[$timestamp], $eventid);
					}
				}
			}
		}

		return $this->_getEventsForDate_Cache[$timestamp]; // return cached result
	}

	/**
	* Used by the core to fetch all Data from the Event $id from the backend.
	*
	* The return value must be a associative array containing $key => $value pairs.
	* If there is no such event in the backend, return false - this will be used by the core
	* to test if a event exists.
	*
	* @param int $id    ID of the event in question
	* @return array|false
	*/
	function getData($id)
	{
		$return = false;
		$res =& $this->_mdb2->query('SELECT * FROM '.$this->_mdb2->quoteIdentifier($this->config['table']).'
			WHERE '.$this->_mdb2->quoteIdentifier($this->config['col_id']).' = '.$this->_mdb2->quote($id));
		
		if (!PEAR::isError($res) && $res->numRows() > 0) {
			$return = array();
			while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) {
				$value = $row[$this->config['col_data']];
				if ($value == ' ') $value = ''; // Since we store a blank if value is empty, we must correct it here
				$return[$row[$this->config['col_field']]] = $value;
			}
		}
		
		return $return;
	}

	/**
	* This method is used to write data into the backend. $data is a associative array similar to the return value of getData().
	*
	* If $id is empty (= false, null or '') or not present in the backend, a new event must be created.
	* Otherwise the present data must be updated.
	* Return the ID of the event written or false, depending if storage succeedet
	*
	* @return int|false
	*/
	function storeData($id, $data)
	{
		$written_id = false;
		if (is_array($data)) {
			if (!empty($id)) {
				// ID requested, see if event exists
				$olddata = $this->getData($id);
				if (false !== $olddata) {
					$written_id = $id;
				}
			}
			
			// Now we know wheter we must create a new event or update an existing one
			$this->_mdb2->loadModule('Extended', null, false);
			if (false === $written_id) {
				// New event: get new ID
				$written_id = $this->_mdb2->nextID('EventID');
				if (!PEAR::isError($written_id)) {
					// prepare dataset
					$mdb2_dataset = array();
					foreach ($data as $field => $value) {
						if ($written_id && $field && $value) {
							$mdb2_dataset[] = array($written_id, $field, $value);
						}
					}
					
					$sth = $this->_mdb2->prepare('INSERT INTO '.$this->_mdb2->quoteIdentifier($this->config['table']).' VALUES (?, ?, ?)');
					$result = $this->_mdb2->extended->executeMultiple($sth, $mdb2_dataset);
					if (PEAR::isError($result) || $result == 0) {
						$written_id = false;  // return false because error
						echo "error: ".$result->getmessage();
					}
				}
			} else {
				// Old event, see what must be iserted/updated
				$updates = array_diff_assoc($data, $olddata);
				$inserts = array();
				foreach ($updates as $key => $newval) {
					if (!array_key_exists($key, $olddata)) {
						// PEAR::MDB2 seems to have a problem with null values, if
						// database schema does not allow null.
						if (empty($newval)) $newval = ' ';
						
						// if updated key does not exist in old data,
						// then it is an insert, not an update!
						$inserts[$key] = $newval;
						unset($updates[$key]);
					}
				}
				
				// prepare datasets for MDB2 insert/update
				$mdb2_insert_dataset = array();
				foreach ($inserts as $field => $value) {
					$mdb2_insert_dataset[] = array($written_id, $field, $value);
				}
				$mdb2_update_dataset = array();
				foreach ($updates as $field => $value) {
					$mdb2_update_dataset[] = array($value, $written_id, $field);
				}
				
				if (count($mdb2_insert_dataset) > 0) {
					$i_sth = $this->_mdb2->prepare('INSERT INTO '.$this->_mdb2->quoteIdentifier($this->config['table']).' VALUES (?, ?, ?)');
					$result = $this->_mdb2->extended->executeMultiple($i_sth, $mdb2_insert_dataset);
					if (PEAR::isError($result) || $result == 0) {
						$written_id = false;  // return false because error
						die('e1'.$result->getMessage());
					}
				}
				
				if (count($mdb2_update_dataset) > 0) {
					$u_sth = $this->_mdb2->prepare('UPDATE '.$this->_mdb2->quoteIdentifier($this->config['table']).'
						SET '.$this->_mdb2->quoteIdentifier($this->config['col_data']).' = ?
						WHERE '.$this->_mdb2->quoteIdentifier($this->config['col_id']).' = ?
						AND '.$this->_mdb2->quoteIdentifier($this->config['col_field']).' = ?');
					$result = $this->_mdb2->extended->executeMultiple($u_sth, $mdb2_update_dataset);
					if (PEAR::isError($result) || $result == 0) {
						$written_id = false;  // return false because error
						die('e2'.$result->getMessage());
					}
				}
			}
			
		}
		return $written_id;
		
	}

	/**
	* This is used to delete this specific event in the backend.
	*
	* @return true|false
	*/
	function deleteEvent($id)
	{
		$return = false;
		$sql  = 'DELETE FROM '.$this->_mdb2->quoteIdentifier($this->config['table']).' WHERE '.$this->_mdb2->quoteIdentifier($this->config['col_id']).' = '.$this->_mdb2->quote($id);
		$affected =& $this->_mdb2->exec($sql);
		if (!PEAR::isError($affected)) {
			$return = true;
		}
		
		return $return;
	}
	
	/**
	* Custom configuration hook
	*
	* This hook is called after LCC initialized and configured the driver.
	* we use it to set up PEAR::MDB2 and establish a DB-Connection
	*
	* @return none
	* @todo I think a initial check against the schema would be good, to ensure, that all configured table-cols are present
	*/
	function configure()
	{
		$this->_checkConfig();
		$this->config['options']['result_buffering'] = true; // Enforce result buffering
		
		$this->_mdb2 =& MDB2::connect($this->config['DSN'], $this->config['options']);
		if (PEAR::isError($this->_mdb2)) {
			die('<b>'.get_class($this).' CONNECTION ERROR:</b> '.$this->_mdb2->getMessage());
		}
	}
	
	
	/*******************************************************************
	* Here come some driver internal methods that make our life easier *
	*******************************************************************/
	
	/**
	* Verify that the drivers config is loaded and sane
	*
	* @return true      Return true or dies
	* @access private
	*/
	function _checkConfig()
	{
		if(!is_array($this->config) ||
		   !isset($this->config['DSN']) ||
		   empty($this->config['DSN'])) {
			die('<b>'.get_class($this).' ERROR:</b> DSN empty or not valid');
		}

		return true;
	}
}
?>
Return current item: Lightweight Club Calendar