<?php
/**
* MySQL based EventDriver for LCC
*
* PHP Version 4, 5
*
* @author Greg* <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
*/
/**
* A Driver that is capaple of storing and retrieving Eventdata from a MySQL database.
*
* The configuration is done via a more dimensional associative array (see below).
* This driver suits best the cases where you have a mysql database and no PEAR
* available. If you have a workin PEAR installation, you may consider
* using the PEAR::MDB2 event driver ('pearmbd2').
*
* The database schema is required as dollowing:
* +----+--------+---------+---------------+-----+
* | id | author | created | last_modified | ... |
* +----+--------+---------+---------------+-----+
* Please be sure to provide at least ALL meta event fields
* LCC requires. These are all default fields mentioned in
* the default configuration (see below).
*
* Possible configuration:
* 'db' (array) Database connection informations.
* Default configuration:
* 'host' => "localhost",
* 'user' => "user",
* 'pwd' => "secret",
* 'dbname' => "lcc"
*
* 'table' (string) The table name of the event table
* Default: 'events'
*
* 'fields' (array) User can specify his own db col names for fields
* syntax is internal_key => user_key
* Default configuration:
* 'id' => "id",
* 'author' => "author",
* 'created' => "created",
* 'last_modified' => "last_modified",
* 'last_modified_author' => "last_modified_author",
* 'event_start' => "event_start",
* 'event_end' => "event_end",
* 'title' => "title",
* 'description' => "description",
* 'color' => "color"
*/
class LCC_EventDriver_MySQL extends LCC_EventDriver
{
/**
* Default config of the driver.
*
* This gets overwritten by the core calling setConfig()
* Setting default values.
*
* @var array
*/
var $config = array(
'db' => array(
'host' => "localhost",
'user' => "user",
'pwd' => "secret",
'dbname' => "lcc"
),
'fields' => array(
'id' => "id",
'author' => "author",
'created' => "created",
'last_modified' => "last_modified",
'last_modified_author' => "last_modified_author",
'event_start' => "event_start",
'event_end' => "event_end",
'title' => "title",
'description' => "description",
'color' => "color"
),
'table' => "events"
);
/**
* The link-id for the connection.
*
* @access private
*/
var $_link = false;
/**
* A cache that holds all the events in a month.
*
* @access private
*/
var $_monthEvents = null;
/**
* Initiates connection to the database
*/
function configure()
{
$this->_link = mysql_connect($this->config['db']['host'], $this->config['db']['user'], $this->config['db']['pwd']);
if ( $this->_link !== false ) {
if ( mysql_select_db($this->config['db']['dbname'], $this->_link) === false ) {
$this->_error("DB can't be found !");
}
} else {
$this->_error("Can't connect to the MySQL Server.");
}
}
/**
* Returns event-IDs for a given month.
* If the passed timestamp corresponds to a day within the range [1-15], then, we will look for events of this month. Otherwise, it will be
* events of the next month.
*
* @param int $timestamp UNIX timestamp
* @return array Event-IDs
*/
function getEventsForMonth($timestamp)
{
$this->_monthEvents = array();
if ( $this->_link ) {
$month = date("m", $timestamp);
$year = date("Y", $timestamp);
// Make the timestamp of the start day of the month.
// 'j' <=> day without leading zero
if (date("j", $timestamp) <= 15) {
$start = mktime(0,0,0,$month, 1, $year);
} else {
$start = mktime(0,0,0, $month + 1, 1, $year);
}
// End of month.
$end = mktime(23,59,59, date("m", $start) + 1, 0, date("Y", $start));
// generates the month array.
// There, we create an array for each day, to be sure we return something.
for ($day = date("j", $start); $day <= $stop; $day++) {
$this->_monthEvents[ $day ] = array();
}
$fields =& $this->config['fields']; // shortcut
// SELECT id, event_start AS start, event_end AS end FROM events WHERE (event_start BETWEEN $start AND $end OR event_end BETWEEN $start AND $end) ORDER BY event_start ASC;
$query = "SELECT " . $fields['id'] . ", " . $fields['event_start'] . " AS start, " . $fields['event_end'] . " AS end FROM " . $this->config['table'] . " WHERE (`" . $fields['event_start'] . "` BETWEEN " . $start . " AND " . $end . " OR `" . $fields['event_end'] . "` BETWEEN " . $start . " AND " . $end . ") ORDER BY " . $fields['event_start'] . " ASC;";
$rsc = mysql_query($query, $this->_link);
if ( $rsc ) {
while ($event = mysql_fetch_assoc($rsc)) {
// More common values.
$from = date("j", $event['start']);
$to = date("j", $event['end']);
// Oh oh : this event spreads on multiple months.
if ( date("m", $event['start']) != date("m", $event['end']) ) {
// If it starts before the current month, then make it start at the 1st of the current month.
if (date("m", $event['start']) != date("m", $start)) {
$from = date("j", $start);
}
// Same with the end of the event.
if (date("m", $event['end']) != date("m", $end)) {
$to = date("j", $end);
}
}
// Then, fetch the events in days.
for ( $day = $from; $day <= $to; $day++ ) {
$this->_monthEvents[ $day ][] = $event['id'];
}
}
mysql_free_result($rsc);
}
}
}
/**
* Returns event-IDs of events for a given day.
*
* @param int $timestamp UNIX timestamp for the day
* @return false|array Indexed array containing event-IDs.
*/
function getEventsForDate($timestamp)
{
$events = false;
if ( $this->_link ) {
// Builds the month cache.
if ( is_null($this->_monthEvents) ) {
$this->getEventsForMonth($timestamp);
}
if ( array_key_exists(date("j", $timestamp), $this->_monthEvents) ) {
$events =& $this->_monthEvents[ date("j", $timestamp) ];
} else {
$events = array();
}
}
return $events;
}
/**
* Storing data in the DB.
*
* @param int $id The event-id to be updated. If empty, then a new event is inserted.
* @param array $data The event data.
* @return int|false
*/
function storeData($id, $data)
{
if ($this->_link) {
// By default, we assume a new event is to be inserted.
$query_head = "INSERT INTO ";
$query_body = "";
$query_foot = ";";
if ($id) { // Update an existing event
// build the UPDATE query
$query_head = "UPDATE ";
$query_foot = "WHERE `" . $this->config['fields']['id'] . "`=" . $id . " LIMIT 1;";
}
$query_head .= $this->config['table'] . " SET";
foreach ($data as $field => $value) {
if ( array_key_exists($field, $this->config['fields'])) {
$query_body .= " `" . $this->config['fields'][$field] . "`=" . (is_string($value) ? "\"":"") . $value . (is_string($value) ? "\"":"") . ",";
}
}
if (strlen($query_body)) {
$query_body[ strlen($query_body) - 1 ] = " ";
}
$query = $query_head . $query_body . $query_foot;
$success = mysql_query($query, $this->_link);
}
if ( $success ) {
if ( empty($id) ) {
$id = mysql_insert_id($this->_link);
}
}
return $id;
}
/**
* Used by the core to fetch all data from a given event-id.
*
* Return value is an associative array.
* If there is no event, return false.
*
* @return array|false
*/
function getData($id)
{
$data = false;
if ( $this->_link ) {
$rsc = mysql_query("SELECT * FROM " . $this->config['table'] . " WHERE `" . $this->config['fields']['id'] . "`=" . $id . " LIMIT 1;");
if ($rsc) {
// Raw data : fields name are those defined by the user.
// They have to be replaced by internal names (see loop below).
$raw_data = mysql_fetch_assoc($rsc);
// builds the correct data array.
$data = array();
if ( $raw_data ) {
foreach ($this->config['fields'] as $internal_key => $foreign_key) {
if ( !empty($raw_data[ $foreign_key ])) {
$data[$internal_key] = $raw_data[ $foreign_key ];
}
}
// Computes start_date and end_date
$data['start_date'] = date("Y-m-d", $data['event_start']);
$data['end_date'] = date("Y-m-d", $data['event_end']);
// Computes time_start and time_end
$data['start_time'] = date("H:i", $data['event_start']);
$data['end_time'] = date("H:i", $data['event_end']);
}
mysql_free_result($rsc);
}
}
return $data;
}
/**
* Deletes an event.
*
* @param int $id event-id to be deleted.
* @return true|false
*/
function deleteEvent($id)
{
$del_event = false;
if ( $this->_link ) {
$del_event = mysql_query("DELETE FROM " . $this->config['table'] . " WHERE `" . $this->config['fields']['id'] . "`=" . $id . " LIMIT 1;");
}
return $del_event;
}
/**
* Print an error message.
*
* @param string $message The error message to be displayed.
*/
function _error($message)
{
die('<b>' . get_class($this) . '</b>:' . $message . '<br />');
}
}
?>