|
Nag's SQL Storage implementation
<?php
// $Horde: hordeweb/papers/oscon2001-horde_tutorial/41_nagdriver.xml.html,v 1.1 2001/12/05 16:48:07 chuck Exp $
/**
* Nag storage implementation for PHP's PEAR database abstraction layer.
*
* Required values for $params:
* 'phptype' The database type (e.g. 'pgsql', 'mysql, etc.).
* 'hostspec' The hostname of the database server.
* 'protocol' The communication protocol ('tcp', 'unix', etc.).
* 'username' The username with which to connect to the database.
* 'password' The password associated with 'username'.
* 'database' The name of the database.
* 'table' The name of the preferences table in 'database'.
*
* Required by some database implementations:
* 'options' Additional options to pass to the database.
* 'tty' The TTY on which to connect to the database.
* 'port' The port on which to connect to the database.
*
* The table structure is as follows:
*
* create table nag_tasks (
* task_owner varchar(32) not null,
* task_id int not null,
* task_name varchar(64) not null,
* task_desc text null,
* task_added int not null,
* task_due int null,
* task_priority int null default 0,
* task_depends int null default -1,
* primary key (task_owner, task_id)
* );
*
* @author Jon Parise <jon@horde.org>
* @version $Revision: 1.1 $
* @since Nag 0.1
* @package nag
*/
class Nag_Storage_sql extends Nag_Storage {
/** Hash containing connection parameters. */
var $params = array();
/** Handle for the current database connection.
@var object DB $db */
var $db;
/** Boolean indicating whether or not we're connected to the SQL server. */
var $connected = false;
/**
* Constructs a new SQL preferences object.
*
* @param string $user The user who owns these preferences.
* @param string $password The password associated with $user. (unused)
* @param string $scope The current preferences scope.
* @param array $params A hash containing connection parameters.
*/
function Nag_Storage_sql($user, $params = array())
{
$this->user = $user;
$this->params = $params;
}
/**
* Attempts to open a persistent connection to the SQL server.
*
* @return int STORAGE_OK on success, STORAGE_ERROR_* on failure.
*/
function connect()
{
if (!$this->connected) {
include_once 'DB.php';
if (!is_array($this->params)) return STORAGE_ERROR_PARAMS;
if (!isset($this->params['phptype'])) return STORAGE_ERROR_PARAMS;
if (!isset($this->params['hostspec'])) return STORAGE_ERROR_PARAMS;
if (!isset($this->params['username'])) return STORAGE_ERROR_PARAMS;
if (!isset($this->params['password'])) return STORAGE_ERROR_PARAMS;
if (!isset($this->params['database'])) return STORAGE_ERROR_PARAMS;
if (!isset($this->params['table'])) return STORAGE_ERROR_PARAMS;
/* Connect to the SQL server using the supplied parameters. */
$this->db = &DB::connect($this->params, true);
if (DB::isError($this->db) || DB::isWarning($this->db)) {
return STORAGE_ERROR_CONNECT;
}
$this->connected = true;
}
return STORAGE_OK;
}
/**
* Disconnect from the SQL server and clean up the connection.
*
* @return boolean true on success, false on failure.
*/
function disconnect()
{
if ($this->connected) {
$this->connected = false;
return $this->db->disconnect();
}
return true;
}
/**
* Retrieves the user's tasks from the database.
*
* @return int STORAGE_OK on success, or STORAGE_ERROR_* on failure.
*/
function retrieve()
{
/* If we're not already connected, invoke the connect() method now. */
if (!$this->connected) {
if ($this->connect() != STORAGE_OK) return STORAGE_ERROR_CONNECT;
}
/* Build the SQL query. */
$query = sprintf('select * from %s where task_owner = %s',
$this->params['table'], $this->db->quote($this->user));
/* Execute the query. */
$result = $this->db->query($query);
if (isset($result) && !DB::isError($result)) {
$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
if (DB::isError($row)) return STORAGE_ERROR_EMPTY;
/* Store the retrieved values in a fresh $tasks list. */
$this->tasks = array();
while ($row && !DB::isError($row)) {
/* Create a new task based on this row's values. */
$task = array();
$task['name'] = $row['task_name'];
$task['desc'] = $row['task_desc'];
$task['added'] = $row['task_added'];
$task['due'] = $row['task_due'];
$task['priority'] = $row['task_priority'];
$task['depends'] = $row['task_depends'];
$task['flags'] = 0;
/* Add this new task to the $tasks list. */
$this->tasks[($row['task_id'])] = $task;
/* Advance to the new row in the result set. */
$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
}
$result->free();
} else {
return STORAGE_ERROR;
}
return STORAGE_OK;
}
/**
* Stores the user's tasks to SQL server.
*
* @return int STORAGE_OK on success, or STORAGE_ERROR_* on failure.
*/
function store()
{
/* Build lists of the tasks that require pending database operations. */
$added_tasks = $this->listTasks(TASK_ADDED);
$modified_tasks = $this->listTasks(TASK_MODIFIED);
$deleted_tasks = $this->listTasks(TASK_DELETED);
/* If there are no pending operations, exit successfully now. */
if ((count($added_tasks) == 0) && (count($modified_tasks) == 0) &&
(count($deleted_tasks) == 0)) {
return STORAGE_OK;
}
/* If we're not already connected, invoke the connect() method now. */
if (!$this->connected) {
if ($this->connect() != STORAGE_OK) return STORAGE_ERROR_CONNECT;
}
/* Perform any pending additions. */
if (count($added_tasks) > 0) {
foreach($added_tasks as $task_id => $task) {
$query = sprintf(
'insert into %s (task_owner, task_id, task_name, ' .
'task_desc, task_added, task_due, task_priority, ' .
'task_depends) ' .
'values(%s, %d, %s, %s, %d, %d, %d, %d)',
$this->params['table'],
$this->db->quote($this->user),
$task_id,
$this->db->quote($task['name']),
$this->db->quote($task['desc']),
$task['added'],
$task['due'],
$task['priority'],
$task['depends']);
/* Attempt the insertion query. */
$result = $this->db->query($query);
/* Return an error immediately if the query failed. */
if ($result !== DB_OK) {
return STORAGE_ERROR;
}
/* Remove the "added" flag from this task. */
$this->setFlag($task_id, TASK_ADDED, false);
}
}
/* Perform any pending modifications. */
if (count($modified_tasks) > 0) {
foreach($modified_tasks as $task_id => $task) {
$query = sprintf('update %s set ', $this->params['table']);
$query .= sprintf('task_name = %s, ',
$this->db->quote($task['name']));
$query .= sprintf('task_desc = %s, ',
$this->db->quote($task['desc']));
$query .= sprintf('task_added = %d, ', $task['added']);
$query .= sprintf('task_due = %d, ', $task['due']);
$query .= sprintf('task_priority = %d, ', $task['priority']);
$query .= sprintf('task_depends = %d ', $task['depends']);
$query .= sprintf('where task_owner = %s and task_id = %d',
$this->db->quote($this->user), $task_id);
/* Attempt the update query. */
$result = $this->db->query($query);
/* Return an error immediately if the query failed. */
if ($result !== DB_OK) {
return STORAGE_ERROR;
}
/* Remove the "modified" flag from this task. */
$this->setFlag($task_id, TASK_MODIFIED, false);
}
}
/* Perform any pending deletions. */
if (count($deleted_tasks) > 0) {
$task_ids = array_keys($deleted_tasks);
$where = 'task_id = ' . $task_ids[0];
if (count($task_ids) > 1) {
array_shift($task_ids);
$where .= ' or task_id = ' . implode(' or task_id =', $task_ids);
}
$query = sprintf('delete from %s where %s',
$this->params['table'], $where);
/* Attempt the delete query. */
$result = $this->db->query($query);
/* Return an error immediately if the query failed. */
if ($result !== DB_OK) {
return STORAGE_ERROR;
}
/* Purge the deleted tasks. */
$this->purgeDeleted();
}
return STORAGE_OK;
}
}
?>
|
