Logo Search packages:      
Sourcecode: nag version File versions  Download package

sql.php

<?php
// $Horde: nag/lib/Driver/sql.php,v 1.23.2.5 2002/10/15 15:36:52 jan Exp $

/**
 * Nag storage implementation for PHP's PEAR database abstraction layer.
 *
 * Required values for $params:<pre>
 *      '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 tasks table in 'database'.</pre>
 *
 * 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 can be created by the scripts/drivers/nag_tasks.sql
 * script.
 *
 * @author  Jon Parise <jon@horde.org>
 * @version $Revision: 1.23.2.5 $
 * @since   Nag 0.1
 * @package nag
 */
class Nag_Driver_sql extends Nag_Driver {

    /** 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 storage object.
     *
     * @param string $user      The user who owns these tasks.
     * @param array  $params    A hash containing connection parameters.
     */
    function Nag_Driver_sql($user, $params = array())
    {
        $this->_user = $user;
        $this->_params = $params;
    }

    /**
     * Attempts to open a persistent connection to the SQL server.
     *
     * @return boolean    True on success; exits (Horde::fatal()) on error.
     */
    function _connect()
    {
        if (!$this->_connected) {
            if (!is_array($this->_params)) {
                Horde::fatal(new PEAR_Error(_("No configuration information specified for SQL Tasks.")), __FILE__, __LINE__);
            }
            if (!isset($this->_params['phptype'])) {
                Horde::fatal(new PEAR_Error(_("Required 'phptype' not specified in tasks configuration.")), __FILE__, __LINE__);
            }
            if (!isset($this->_params['hostspec'])) {
                Horde::fatal(new PEAR_Error(_("Required 'hostspec' not specified in tasks configuration.")), __FILE__, __LINE__);
            }
            if (!isset($this->_params['username'])) {
                Horde::fatal(new PEAR_Error(_("Required 'username' not specified in tasks configuration.")), __FILE__, __LINE__);
            }
            if (!isset($this->_params['password'])) {
                Horde::fatal(new PEAR_Error(_("Required 'password' not specified in tasks configuration.")), __FILE__, __LINE__);
            }
            if (!isset($this->_params['database'])) {
                Horde::fatal(new PEAR_Error(_("Required 'database' not specified in tasks configuration.")), __FILE__, __LINE__);
            }
            if (!isset($this->_params['table'])) {
                Horde::fatal(new PEAR_Error(_("Required 'table' not specified in tasks configuration.")), __FILE__, __LINE__);
            }

            /* Connect to the SQL server using the supplied parameters. */
            include_once 'DB.php';
            $this->_db = &DB::connect($this->_params, true);
            if (DB::isError($this->_db)) {
                Horde::fatal($this->_db, __FILE__, __LINE__);
            }

            /* Enable the "portability" option. */
            $this->_db->setOption('optimize', 'portability');

            $this->_connected = true;
        }

        return true;
    }

    /**
     * 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 mixed  True on success, PEAR_Error on failure.
     */
    function retrieve()
    {
        /* Make sure we have a valid database connection. */
        $this->_connect();

        /* Build the SQL query. */
        $query = sprintf('SELECT * FROM %s WHERE task_owner = %s',
                         $this->_params['table'], $this->_db->quote($this->_user));

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('Nag_Driver_sql::retrieve(): %s', $query),
                          __FILE__, __LINE__, LOG_DEBUG);

        /* 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 $row;
            }

            /* 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['task_id'] = $row['task_id'];
                $task['name'] = $row['task_name'];
                $task['desc'] = $row['task_desc'];
                $task['category'] = $row['task_category'];
                $task['due'] = $row['task_due'];
                $task['priority'] = $row['task_priority'];
                $task['completed'] = $row['task_completed'];
                $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 $result;
        }

        return true;
    }

    /**
     * Stores the user's tasks to SQL server.
     *
     * @return mixed  True on success, PEAR_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 true;
        }

        /* Make sure we have a valid database connection. */
        $this->_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_due, task_priority, ' .
                    'task_completed, task_category, task_modified) ' .
                    'VALUES (%s, %d, %s, %s, %d, %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['due'],
                    $task['priority'],
                    $task['completed'],
                    $task['category'],
                    time());

                    /* Log the query at a DEBUG log level. */
                    Horde::logMessage(sprintf('Nag_Driver_sql::store(): %s', $query),
                                      __FILE__, __LINE__, LOG_DEBUG);

                /* Attempt the insertion query. */
                $result = $this->_db->query($query);

                /* Return an error immediately if the query failed. */
                if (DB::isError($result)) {
                    return $result;
                }

                /* 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_due = %d, ', $task['due']);
                $query .= sprintf('task_priority = %d, ', $task['priority']);
                $query .= sprintf('task_completed = %d, ', $task['completed']);
                $query .= sprintf('task_category = %d, ', $task['category']);
                $query .= sprintf('task_modified = %d ', time());
                $query .= sprintf('WHERE task_owner = %s AND task_id = %d',
                                  $this->_db->quote($this->_user), $task_id);

                /* Log the query at a DEBUG log level. */
                Horde::logMessage(sprintf('Nag_Driver_sql::store(): %s', $query),
                                  __FILE__, __LINE__, LOG_DEBUG);

                /* Attempt the update query. */
                $result = $this->_db->query($query);

                /* Return an error immediately if the query failed. */
                if (DB::isError($result)) {
                    return $result;
                }

                /* 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 task_owner = %s AND (%s)',
                             $this->_params['table'],
                             $this->_db->quote($this->_user),
                             $where);

            /* Log the query at a DEBUG log level. */
            Horde::logMessage(sprintf('Nag_Driver_sql::store(): %s', $query),
                              __FILE__, __LINE__, LOG_DEBUG);


            /* Attempt the delete query. */
            $result = $this->_db->query($query);

            /* Return an error immediately if the query failed. */
            if (DB::isError($result)) {
                return $result;
            }

            /* Purge the deleted tasks. */
            $this->purgeDeleted();
        }

        return true;
    }

}
?>

Generated by  Doxygen 1.6.0   Back to index