<?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;
}
}
?>