tags/phorum/include/db/mysql.php

<?php

////////////////////////////////////////////////////////////////////////////////
//                                                                            //
//   Copyright (C) 2007  Phorum Development Team                              //
//   http://www.phorum.org                                                    //
//                                                                            //
//   This program is free software. You can redistribute it and/or modify     //
//   it under the terms of either the current Phorum License (viewable at     //
//   phorum.org) or the Phorum License that was distributed with this file    //
//                                                                            //
//   This program 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.                     //
//                                                                            //
//   You should have received a copy of the Phorum License                    //
//   along with this program.                                                 //
//                                                                            //
////////////////////////////////////////////////////////////////////////////////

/**
 * This script implements a MySQL Phorum database layer.
 *
 * The other Phorum code does not care how data is stored.
 * The only requirement is that it is returned from these functions
 * in the right way. This means each database can use as many or as
 * few tables as it likes. It can store the fields anyway it wants.
 *
 * The only thing to worry about is the table_prefix for the tables.
 * all tables for a Phorum install should be prefixed with the
 * table_prefix that will be entered in include/db/config.php.  This
 * will allow multiple Phorum installations to use the same database.
 *
 * @todo
 *     phorum_api_user_check_access() is used in this layer, but the
 *     include file for that is not included here. Keep it like that
 *     or add the required include? Or is it functionality that doesn't
 *     belong here and could better go into the core maybe?
 *
 * @package    PhorumDBLayer
 * @copyright  2007, Phorum Development Team
 * @license    Phorum License, http://www.phorum.org/license.txt
 */

// Bail out if we're not loaded from the Phorum code.
if (!defined('PHORUM')) return;

// ----------------------------------------------------------------------
// Definitions
// ----------------------------------------------------------------------

// {{{ Constant and variable definitions

// The table prefix, which allows for storing multiple Phorum data sets
// in one single database.
$prefix = $PHORUM['DBCONFIG']['table_prefix'];

/**
 * These are the table names that are used by this database system.
 */
$PHORUM['message_table']            = $prefix . '_messages';
$PHORUM['user_newflags_table']      = $prefix . '_user_newflags';
$PHORUM['subscribers_table']        = $prefix . '_subscribers';
$PHORUM['files_table']              = $prefix . '_files';
$PHORUM['search_table']             = $prefix . '_search';
$PHORUM['settings_table']           = $prefix . '_settings';
$PHORUM['forums_table']             = $prefix . '_forums';
$PHORUM['user_table']               = $prefix . '_users';
$PHORUM['user_permissions_table']   = $prefix . '_user_permissions';
$PHORUM['groups_table']             = $prefix . '_groups';
$PHORUM['forum_group_xref_table']   = $prefix . '_forum_group_xref';
$PHORUM['user_group_xref_table']    = $prefix . '_user_group_xref';
$PHORUM['user_custom_fields_table'] = $prefix . '_user_custom_fields';
$PHORUM['banlist_table']            = $prefix . '_banlists';
$PHORUM['pm_messages_table']        = $prefix . '_pm_messages';
$PHORUM['pm_folders_table']         = $prefix . '_pm_folders';
$PHORUM['pm_xref_table']            = $prefix . '_pm_xref';
$PHORUM['pm_buddies_table']         = $prefix . '_pm_buddies';
$PHORUM['message_tracking_table']   = $prefix . '_messages_edittrack';

/**
 * Message fields which are always strings, even if they contain numbers only.
 * Used in post-message and update-message, otherwise strange things happen.
 */
$PHORUM['string_fields_message'] = array('author', 'subject', 'body', 'email');

/**
 * Forum fields which are always strings, even if they contain numbers only.
 */
$PHORUM['string_fields_forum'] = array('name', 'description', 'template');

/**
 * User fields which are always strings, even if they contain numbers only.
 */
$PHORUM['string_fields_user'] = array('username', 'real_name', 'display_name',
   'password', 'password_temp', 'sessid_lt', 'sessid_st', 'email', 'email_temp',
   'signature', 'user_language', 'user_template', 'moderator_data', 'settings_data'
);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function return a database connection handle.
 */
define('DB_RETURN_CONN',     0);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function return a SQL quoted value.
 */
define('DB_RETURN_QUOTED',   1);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function return the query statement handle for a SQL query.
 */
define('DB_RETURN_RES',      2);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function return a single database row for a SQL query.
 */
define('DB_RETURN_ROW',      3);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function return an array of rows for a SQL query.
 */
define('DB_RETURN_ROWS',     4);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function return a single database row for a SQL query
 * as an associative array
 */
define('DB_RETURN_ASSOC',    5);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function return an array of rows for a SQL query
 * as associative arrays.
 */
define('DB_RETURN_ASSOCS',   6);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function return a single value for a SQL query.
 */
define('DB_RETURN_VALUE',    7);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function return the number of selected rows for a SQL query.
 */
define('DB_RETURN_ROWCOUNT', 8);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function return the new auto_increment id value for
 * an insert SQL query.
 */
define('DB_RETURN_NEWID',    9);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function return an error for a SQL query or NULL if there
 * was no error.
 */
define('DB_RETURN_ERROR',   10);

/**
 * Function call parameter $return for {@link phorum_db_interact()}.
 * Makes the function close the connection to the database.
 * The function will return no data.
 */
define('DB_CLOSE_CONN',     11);

/**#@+
 * Constant for the phorum_db_interact() function call $flags parameter.
 */
define('DB_NOCONNECTOK',     1);
define('DB_MISSINGTABLEOK',  2);
define('DB_DUPFIELDNAMEOK',  4);
define('DB_DUPKEYNAMEOK',    8);
define('DB_DUPKEYOK',       16);
define('DB_TABLEEXISTSOK',  32);
define('DB_GLOBALQUERY',    64);
define('DB_MASTERQUERY',   128);
/**#@-*/

/**#@+
 * Constant for the phorum_db_get_recent_messages() function call
 * $list_type parameter.
 */
define('LIST_RECENT_MESSAGES',   0);
define('LIST_RECENT_THREADS',    1);
define('LIST_UPDATED_THREADS',   2);
/**#@-*/

// }}}

// ----------------------------------------------------------------------
// Utility functions (not directly part of the Phorum db API)
// ----------------------------------------------------------------------

// {{{ Function: phorum_db_mysql_connect()
/**
 * A wrapper function for connecting to the database.
 *
 * This function should not be used from the db layer code. Instead the
 * phorum_db_interact() function should be used in combination with the
 * DB_RETURN_CONN return type. This function is only implemented for
 * module writers that use this function in their code.
 *
 * @return $conn - A database connection resource handle.
 * @deprecated
 */
function phorum_db_mysql_connect() {
    return phorum_db_interact(DB_RETURN_CONN, NULL, NULL, DB_MASTERQUERY);
}
// }}}

// {{{ Function: phorum_db_sanitize_mixed()
/**
 * This function will sanitize a mixed variable based on a given type
 * for safe use in SQL queries.
 *
 * @param mixed &$var
 *     The variable to be sanitized. Passed by reference, so the original
 *     variable will be updated. It can be either a single variable or an
 *     array containing multiple variables.
 *
 * @param string $type
 *     Either "int" or "string" (the default).
 */
function phorum_db_sanitize_mixed(&$var, $type)
{
    if (is_array($var)) {
        foreach ($var as $id => $val) {
            if ($type == 'int') {
                $var[$id] = (int)$val;
            } else {
                $var[$id] = phorum_db_interact(DB_RETURN_QUOTED, $val);
            }
        }
    } else {
        if ($type=='int') {
            $var = (int)$var;
        } else {
            $var = phorum_db_interact(DB_RETURN_QUOTED, $var);
        }
    }
}
// }}}

// {{{ Function: phorum_db_validate_field()
/**
 * Check if a value that will be used as a field name in a SQL query
 * contains only characters that would appear in a field name.
 *
 * @param string $field_name
 *     The field name to check.
 *
 * @return boolean
 *     Whether the field name is valid or not (TRUE or FALSE).
 */
function phorum_db_validate_field($field_name)
{
    $valid = preg_match('!^[a-zA-Z0-9_]+$!', $field_name);
    return (bool)$valid;
}
// }}}


// ----------------------------------------------------------------------
// API functions
// ----------------------------------------------------------------------

// {{{ Function: phorum_db_check_connection()
/**
 * @todo
 *     we can save a function call by directly calling
 *     phorum_db_interact(). I'm also not sure if we need
 *     to do this check from common.php. We could take care
 *     of this in the db layer error handling too. Have to
 *     think about this ...
 *
 * Checks if a database connection can be made.
 *
 * @return boolean
 *     TRUE if a connection can be made, FALSE otherwise.
 */
function phorum_db_check_connection()
{
    return phorum_db_interact(
        DB_RETURN_CONN,
        NULL, NULL,
        DB_NOCONNECTOK | DB_MASTERQUERY
    ) ? TRUE : FALSE;
}
// }}}

// {{{ Function: phorum_db_close_connection()
/**
 * Close the database connection.
 */
function phorum_db_close_connection()
{
    phorum_db_interact(DB_CLOSE_CONN);
}
// }}}

// {{{ Function: phorum_db_run_queries()
/**
 * Execute an array of queries.
 *
 * @param array $queries
 *     An array of SQL queries to execute.
 *
 * @return mixed
 *     NULL if all queries were executed successfully or an error
 *     message on failure.
 */
function phorum_db_run_queries($queries)
{
    $PHORUM = $GLOBALS['PHORUM'];

    $error = NULL;

    foreach ($queries as $sql)
    {
        // Because this function is used from the upgrade scripts,
        // we ignore errors about duplicate fields and keys. That
        // way running the same upgrade scripts twice (in case there
        // were problems during the first run) won't bring up fatal
        // errors in case fields or keys are created a second time.
        $error = phorum_db_interact(
            DB_RETURN_ERROR,
            $sql, NULL,
            DB_DUPFIELDNAMEOK | DB_DUPKEYNAMEOK | DB_TABLEEXISTSOK |
            DB_GLOBALQUERY | DB_MASTERQUERY
        );

        if ($error !== NULL) break;
    }

    return $error;
}
// }}}

// {{{ Function: phorum_db_load_settings()
/**
 * Load the Phorum settings in the $PHORUM array.
 *
 * These settings are key/value pairs that are read from the settings
 * table. In the settings table, a data type is provided for each setting.
 * The supported types are:
 *
 * - V = Value: the value of this field is used as is.
 * - S = Serialized: the value of this field is a serialzed PHP variable,
 *       which will be unserialized before storing it in $PHORUM
 */
function phorum_db_load_settings()
{
    global $PHORUM;

    // At install time, there is no settings table.
    // So we ignore errors if we do not see that table.
    $settings = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT name, data, type
         FROM {$PHORUM['settings_table']}",
        NULL,
        DB_MISSINGTABLEOK
    );

    foreach ($settings as $setting)
    {
        $val = $setting[2] == 'V'
             ? $setting[1]
             : unserialize($setting[1]);

        $PHORUM[$setting[0]] = $val;
    }
}
// }}}

// {{{ Function: phorum_db_update_settings()
/**
 * Store or update Phorum settings.
 *
 * @param array $settings
 *     An array containing key/value pairs that have to be stored in the
 *     settings table. Values can be either scalars or arrays. This
 *     function will automatically serialize the arrays before storing them.
 *
 * @return boolean
 *     TRUE if all settings were stored successfully. This function will
 *     always return TRUE, so we could do without a return value. The
 *     return value is here for backward compatibility.
 */
function phorum_db_update_settings($settings)
{
    global $PHORUM;

    if (count($settings) > 0)
    {
        foreach ($settings as $field => $value)
        {
            if (is_array($value)) {
                $value = serialize($value);
                $type = 'S';
            } else {
                $type = 'V';
            }

            $field = phorum_db_interact(DB_RETURN_QUOTED, $field);
            $value = phorum_db_interact(DB_RETURN_QUOTED, $value);

            // Try to insert a new settings record.
            $res = phorum_db_interact(
                DB_RETURN_RES,
                "INSERT INTO {$PHORUM['settings_table']}
                        (data, type, name)
                 VALUES ('$value', '$type', '$field')",
                NULL,
                DB_DUPKEYOK | DB_MASTERQUERY
            );
            // If no result was returned, then the query failed. This probably
            // means that we already have the settings record in the database.
            // So instead of inserting a record, we need to update one here.
            if (!$res) {
              phorum_db_interact(
                  DB_RETURN_RES,
                  "UPDATE {$PHORUM['settings_table']}
                   SET    data = '$value',
                          type = '$type'
                   WHERE  name = '$field'",
                  NULL,
                  DB_MASTERQUERY
              );
            }
        }
    }
    else trigger_error(
        'phorum_db_update_settings(): $settings cannot be empty',
        E_USER_ERROR
    );

    return TRUE;
}
// }}}

// {{{ Function: phorum_db_get_thread_list()
/**
 * Retrieve a list of visible messages for a given page offset.
 *
 * By default, the message body is not included in the fetch queries.
 * To retrieve bodies as well, a true value has to be passed for the
 * $include_bodies parameter.
 *
 * NOTE: ALL dates must be returned as Unix timestamps
 *
 * @param integer $page
 *     The index of the page to return, starting with 0.
 *
 * @param boolean $include_bodies
 *     Whether to include the message bodies in the return data or not.
 *
 * @return array
 *     An array of messages, indexed by message id.
 */
function phorum_db_get_thread_list($page, $include_bodies=FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($page, 'int');

    // The messagefields that we want to fetch from the database.
    $messagefields =
       'author, datestamp, email, message_id, forum_id, meta,
        moderator_post, modifystamp, parent_id, msgid, sort, moved, status,
        subject, thread, thread_count, user_id, viewcount, threadviewcount,
        closed, ip, recent_message_id, recent_user_id, recent_author';

    // Include the message bodies in the thread list if requested.
    if ($include_bodies) {
        $messagefields .= ',body';
    }

    // The sort mechanism to use.
    if ($PHORUM['float_to_top']) {
        $sortfield = 'modifystamp';
        $index = 'list_page_float';
    } else {
        $sortfield = 'thread';
        $index = 'list_page_flat';
    }

    // Initialize the return array.
    $messages = array();

    // The groups of messages which we want to fetch from the database.
    // stickies : sticky messages (only on the first page)
    // threads  : thread starter messages (always)
    // replies  : thread reply messages (only in threaded list mode)
    $groups = array();
    if ($page == 0) $groups[] = 'stickies';
    $groups[] = 'threads';
    if ($PHORUM['threaded_list']) $groups[] = 'replies';

    // For remembering the message ids for which we want to fetch the replies.
    $replymsgids = array();

    // Process all groups.
    foreach ($groups as $group)
    {
        $sql = NULL;

        switch ($group)
        {
            // Stickies.
            case 'stickies':

                $sql = "SELECT $messagefields
                        FROM   {$PHORUM['message_table']}
                        WHERE  status=".PHORUM_STATUS_APPROVED." AND
                               parent_id=0 AND
                               sort=".PHORUM_SORT_STICKY." AND
                               forum_id={$PHORUM['forum_id']}
                        ORDER  BY sort, $sortfield desc";
                break;

            // Threads.
            case 'threads':

                if ($PHORUM['threaded_list']) {
                    $limit = $PHORUM['list_length_threaded'];
                } else {
                    $limit = $PHORUM['list_length_flat'];
                }
                $start = $page * $limit;

                $sql = "SELECT $messagefields
                        FROM   {$PHORUM['message_table']}
                        USE    INDEX ($index)
                        WHERE  $sortfield > 0 AND
                               forum_id = {$PHORUM['forum_id']} AND
                               status = ".PHORUM_STATUS_APPROVED." AND
                               parent_id = 0 AND
                               sort > 1
                        ORDER  BY $sortfield DESC
                        LIMIT  $start, $limit";
                break;

            // Reply messages.
            case 'replies':

                // We're done if we did not collect any messages with replies.
                if (! count($replymsgids)) break;

                $sortorder = "sort, $sortfield DESC, message_id";

                if (!empty($PHORUM['reverse_threading']))
                    $sortorder.=' DESC';

                $sql = "SELECT $messagefields
                        FROM   {$PHORUM['message_table']}
                        WHERE  status = ".PHORUM_STATUS_APPROVED." AND
                               thread in (" . implode(",",$replymsgids) .")
                        ORDER  BY $sortorder";
                break;

        } // End of switch ($group)

        // Continue with the next group if no SQL query was formulated.
        if ($sql === NULL) continue;

        // Query the messages for the current group.
        $rows = phorum_db_interact(DB_RETURN_ASSOCS, $sql, 'message_id');
        foreach ($rows as $id => $row)
        {
            // Unpack the thread message meta data.
            $row['meta'] = empty($row['meta'])
                         ? array()
                         : unserialize($row['meta']);

            // Add the row to the list of messages.
            $messages[$id] = $row;

            // We need the message ids for fetching reply messages.
            if ($group == 'threads' && $row['thread_count'] > 1) {
                $replymsgids[] = $id;
            }
        }
    }

    return $messages;
}
// }}}

// {{{ Function: phorum_db_get_recent_messages
/**
 * Retrieve a list of recent messages for all forums for which the user has
 * read permission, for a particular forum, for a list of forums or for a
 * particular thread. Optionally, only top level thread messages can be
 * retrieved.
 *
 * The original version of this function came from Jim Winstead of mysql.com
 *
 * @param integer $length
 *     Limit the number of returned messages to this number.
 *
 * @param integer $offset
 *     When using the $length parameter to limit the number of returned
 *     messages, this parameter can be used to specify the retrieval offset.
 *
 * @param integer $forum_id
 *     A forum_id, an array of forum_ids or 0 (zero) to retrieve messages
 *     from any forum.
 *
 * @param integer $thread
 *     A thread id or 0 (zero) to retrieve messages from any thread.
 *
 * @param integer $list_type
 *     This parameter determines the type of list that has to be returned.
 *     Options for this parameter are:
 *     - LIST_RECENT_MESSAGES: return a list of recent messages
 *     - LIST_RECENT_THREADS: return a list of recent threads
 *     - LIST_UPDATED_THREADS: return a list of recently updated threads
 *
 * @return array
 *     An array of recent messages, indexed by message_id. One special key
 *     "users" is set too. This one contains an array of all involved
 *     user_ids.
 */
function phorum_db_get_recent_messages($length, $offset = 0, $forum_id = 0, $thread = 0, $list_type = LIST_RECENT_MESSAGES)
{
    $PHORUM = $GLOBALS['PHORUM'];

    // Backward compatibility for the old $threads_only parameter.
    if (is_bool($list_type)) {
        $list_type = $list_type ? LIST_RECENT_THREADS : LIST_RECENT_MESSAGES;
    }

    settype($length,    'int');
    settype($offset,    'int');
    settype($thread,    'int');
    settype($list_type, 'int');
    phorum_db_sanitize_mixed($forum_id, 'int');

    // In case -1 is used as "any" value by the caller.
    if ($forum_id < 0) $forum_id = 0;
    if ($thread   < 0) $thread   = 0;

    // Parameter checking.
    if ($list_type < 0 || $list_type > 3) trigger_error(
        "phorum_db_get_recent_messages(): illegal \$list_type parameter used",
        E_USER_ERROR
    );
    if ($list_type != LIST_RECENT_MESSAGES && $thread) trigger_error(
        "phorum_db_get_recent_messages(): \$thread parameter can only be " .
        "used with \$list_type = LIST_RECENT_MESSAGES",
        E_USER_ERROR
    );

    // We have to check what forums the active Phorum user can read first.
    // Even if a $thread is passed, we have to make sure that the user
    // can read the containing forum. Here we convert the $forum_id argument
    // into an argument that is usable for phorum_api_user_check_access(),
    // in such way that it will always return an array of accessible forum_ids.
    if ($forum_id == 0) {
        $forum_id = PHORUM_ACCESS_LIST;
    } elseif(!is_array($forum_id)) {
        $forum_id = array($forum_id => $forum_id);
    }
    $allowed_forums = phorum_api_user_check_access(
        PHORUM_USER_ALLOW_READ, $forum_id
    );

    // If the user is not allowed to see any forum,
    // then return an empty array.
    if (empty($allowed_forums)) return array();

    // We need to differentiate on which key to use.
    // If selecting on a specific thread, then the best index
    // to use would be the thread_message index.
    if ($thread) {
        $use_key = 'thread_message';
    }
    // Indexes to use if we query exactly one forum.
    elseif (count($allowed_forums) == 1)
    {
        switch($list_type) {
            case LIST_RECENT_MESSAGES:
                $use_key = 'new_count';
                break;
            case LIST_RECENT_THREADS:
                $use_key = 'new_threads';
                break;
            case LIST_UPDATED_THREADS:
                $use_key = 'list_page_float';
                break;
        }
    }
    // Indexes to use if we query more than one forum.
    else
    {
        switch($list_type) {
            case LIST_RECENT_MESSAGES:
                $use_key = 'PRIMARY';
                break;
            case LIST_RECENT_THREADS:
                $use_key = 'recent_threads';
                break;
            case LIST_UPDATED_THREADS:
                $use_key = 'updated_threads';
                break;
        }
    }

    // Build the SQL query.
    $sql = "SELECT  *
            FROM    {$PHORUM['message_table']}
            USE     INDEX ($use_key)
            WHERE   status=".PHORUM_STATUS_APPROVED;

    if (count($allowed_forums) == 1) {
        $sql .= " AND forum_id = " . array_shift($allowed_forums);
    } else {
        $sql .= " AND forum_id IN (".implode(",", $allowed_forums).")";
    }

    if ($thread) {
        $sql.=" AND thread = $thread";
    }

    $sql .= " AND moved = 0";

    if ($list_type == LIST_RECENT_THREADS ||
        $list_type == LIST_UPDATED_THREADS) {
        $sql .= ' AND parent_id = 0';
    }

    if ($list_type == LIST_UPDATED_THREADS) {
        $sql .= ' ORDER BY modifystamp DESC';
    } else {
        $sql .= ' ORDER BY message_id DESC';
    }

    if ($length) {
        if ($offset > 0) {
            $sql .= " LIMIT $offset, $length";
        } else {
            $sql .= " LIMIT $length";
        }
    }

    // Retrieve matching messages from the database.
    $messages = phorum_db_interact(DB_RETURN_ASSOCS, $sql, 'message_id');

    // Post processing of received messages.
    $involved_users = array();
    foreach ($messages as $id => $message)
    {
        // Unpack the message meta data.
        $messages[$id]['meta'] = empty($message['meta'])
                               ? array()
                               : unserialize($message['meta']);

        // Collect all involved users.
        if (isset($message['user_id'])) {
            $involved_users[$message['user_id']] = $message['user_id'];
        }
    }

    // Store the involved users in the message array.
    $messages['users'] = $involved_users;

    return $messages;
}
// }}}

// {{{ Function: phorum_db_get_unapproved_list()
/**
 * Retrieve a list of messages which have not yet been approved by a moderator.
 *
 * NOTE: ALL dates must be returned as Unix timestamps
 *
 * @param $forum_id     - The forum id to work with or NULL in case all
 *                        forums have to be searched. You can also pass an
 *                        array of forum ids.
 * @param $on_hold_only - Only take into account messages which have to
 *                        be approved directly after posting. Do not include
 *                        messages which were hidden by a moderator.
 * @param $moddays      - Limit the search to the last $moddays number of days.
 *
 * @return              - An array of messages, indexed by message id.
 */
function phorum_db_get_unapproved_list($forum_id = NULL, $on_hold_only=FALSE, $moddays=0, $countonly = FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($on_hold_only, 'bool');
    settype($moddays, 'int');
    settype($countonly, 'bool');
    phorum_db_sanitize_mixed($forum_id, 'int');

    // Select a message count or full message records?
    $sql = 'SELECT ' . ($countonly ? 'count(*) ' : '* ') .
           'FROM ' . $PHORUM['message_table'] . ' WHERE ';

    if (is_array($forum_id)) {
        $sql .= 'forum_id IN (' . implode(', ', $forum_id) . ') AND ';
    } elseif ($forum_id !== NULL) {
        $sql .= "forum_id = $forum_id AND ";
    }

    if ($moddays > 0) {
        $checktime = time() - (86400*$moddays);
        $sql .= " datestamp > $checktime AND";
    }

    if ($on_hold_only) {
        $sql .= ' status = '.PHORUM_STATUS_HOLD;
    } else {
        // Use an UNION for speed. This is much faster than using
        // a (status=X or status=Y) query.
        $sql = "($sql status = ".PHORUM_STATUS_HOLD.") UNION " .
               "($sql status = ".PHORUM_STATUS_HIDDEN.")";
    }

    if (!$countonly) {
        $sql .= ' ORDER BY thread, message_id';
    }

    // Retrieve and return data for counting unapproved messages.
    if ($countonly) {
        $count_per_status = phorum_db_interact(DB_RETURN_ROWS, $sql);
        $sum = 0;
        foreach ($count_per_status as $count) $sum += $count[0];
        return $sum;
    }

    // Retrieve unapproved messages.
    $messages = phorum_db_interact(DB_RETURN_ASSOCS, $sql, 'message_id');

    // Post processing of received messages.
    foreach ($messages as $id => $message) {
        $messages[$id]['meta'] = empty($message['meta'])
                               ? array()
                               : unserialize($message['meta']);
    }

    return $messages;
}
// }}}

// {{{ Function: phorum_db_post_message()
/**
 * Store a new message in the database.
 *
 * The message will not be posted if it is a duplicate and if
 * $PHORUM['check_duplicate'] is set.
 *
 * The $message is passed by reference and in case the function completes
 * successfully, the "message_id" index will be set to the new value.
 * If the "thread" index is set to zero, a new thread will be started and the
 * "thread" index will be filled with the new thread id upon return.
 *
 * @param array &$message
 *     The message to post. This is an array, which should contain the
 *     following fields: forum_id, thread, parent_id, author, subject, email,
 *     ip, user_id, moderator_post, status, sort, msgid, body, closed.
 *     Additionally, the following optional fields can be set: meta,
 *     modifystamp, viewcount, threadviewcount.
 *
 * @param boolean $convert
 *     True in case the message is being inserted by a database conversion
 *     script. This will let you set the datestamp and message_id of the
 *     message from the $message data. Also, the duplicate message check
 *     will be fully skipped.
 *
 * @return integer
 *     The message_id that was assigned to the new message.
 */
function phorum_db_post_message(&$message, $convert=FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($convert, 'bool');

    foreach ($message as $key => $value) {
        if (is_numeric($value) &&
            !in_array($key,$PHORUM['string_fields_message'])) {
            $message[$key] = (int)$value;
        } elseif (is_array($value)) {
            $value = serialize($value);
            $message[$key] = phorum_db_interact(DB_RETURN_QUOTED, $value);
        } else {
            $message[$key] = phorum_db_interact(DB_RETURN_QUOTED, $value);
        }
    }

    // When converting messages, the post time should be in the message.
    $NOW = $convert ? $message['datestamp'] : time();

    // Check for duplicate posting of messages, unless we are converting a db.
    if (isset($PHORUM['check_duplicate']) && $PHORUM['check_duplicate'] && !$convert) {
        // Check for duplicate messages in the last hour.
        $check_timestamp = $NOW - 3600;
        $sql = "SELECT message_id
                FROM   {$PHORUM['message_table']}
                WHERE  forum_id  = {$message['forum_id']} AND
                       author    ='{$message['author']}' AND
                       subject   ='{$message['subject']}' AND
                       body      ='{$message['body']}' AND
                       datestamp > $check_timestamp";

        // Return 0 if at least one message can be found.
        if (phorum_db_interact(DB_RETURN_ROWCOUNT, $sql) > 0) return 0;
    }

    $insertfields = array(
        'forum_id'       => $message['forum_id'],
        'datestamp'      => $NOW,
        'thread'         => $message['thread'],
        'parent_id'      => $message['parent_id'],
        'author'         => "'" . $message['author'] . "'",
        'subject'        => "'" . $message['subject'] . "'",
        'email'          => "'" . $message['email'] . "'",
        'ip'             => "'" . $message['ip'] . "'",
        'user_id'        => $message['user_id'],
        'moderator_post' => $message['moderator_post'],
        'status'         => $message['status'],
        'sort'           => $message['sort'],
        'msgid'          => "'" . $message['msgid'] . "'",
        'body'           => "'" . $message['body'] . "'",
        'closed'         => $message['closed'],
        'moved'          => 0
    );

    // The meta field is optional.
    if (isset($message['meta'])) {
        $insertfields['meta'] = "'{$message['meta']}'";
    }

    // The moved field is optional.
    if (!empty($message['moved'])) {
        $insertfields['moved'] = 1;
    }

    // When handling a conversion, the message_id can be set.
    if ($convert && isset($message['message_id'])) {
        $insertfields['message_id'] = $message['message_id'];
    }

    if (isset($message['modifystamp'])) {
        $insertfields['modifystamp'] = $message['modifystamp'];
    }

    if (isset($message['viewcount'])) {
        $insertfields['viewcount'] = $message['viewcount'];
    }

    if (isset($message['threadviewcount'])) {
        $insertfields['threadviewcount'] = $message['threadviewcount'];
    }

    // Insert the message and get the new message_id.
    $message_id = phorum_db_interact(
        DB_RETURN_NEWID,
        "INSERT INTO {$PHORUM['message_table']}
                (".implode(', ', array_keys($insertfields)).")
         VALUES (".implode(', ', $insertfields).")",
        NULL,
        DB_MASTERQUERY
    );

    $message['message_id'] = $message_id;
    $message['datestamp']  = $NOW;

    // Updates for thread starter messages.
    if ($message['thread'] == 0)
    {
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['message_table']}
             SET    thread     = $message_id
             WHERE  message_id = $message_id",
            NULL,
            DB_MASTERQUERY
        );

        $message['thread'] = $message_id;
    }

    if(empty($PHORUM['DBCONFIG']['empty_search_table'])) {

        // Full text searching updates.
        $search_text = $message['author']  .' | '.
        $message['subject'] .' | '.
        $message['body'];

        phorum_db_interact(
            DB_RETURN_RES,
            "INSERT DELAYED INTO {$PHORUM['search_table']}
            (message_id, forum_id,
            search_text)
            VALUES ({$message['message_id']}, {$message['forum_id']},
            '$search_text')",
            NULL,
            DB_MASTERQUERY
        );

    }

    return $message_id;
}
// }}}

// {{{ Function: phorum_db_update_message()
/**
 * Update a message in the database.
 *
 * Note: an update of the full text searching database is only handled
 * if all fields that we incorporate in full text searching (author,
 * subject and body) are in the update fields. If one of the fields is
 * provided, without providing the other two, then changes in the field
 * will not reflect in the full text searching info.
 *
 * @param $message_id - The message_id of the message to update.
 * @param $message    - An array containing the data for the message fields
 *                      that have to be updated. You can pass as many or
 *                      as few message fields as you wish to update.
 */
function phorum_db_update_message($message_id, $message)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($message_id, 'int');

    if (count($message) == 0) trigger_error(
        '$message cannot be empty in phorum_update_message()',
        E_USER_ERROR
    );

    foreach ($message as $field => $value)
    {
        if (phorum_db_validate_field($field))
        {
            if (is_numeric($value) &&
                !in_array($field, $PHORUM['string_fields_message'])) {
                $fields[] = "$field = $value";
            } elseif (is_array($value)) {
                $value = phorum_db_interact(DB_RETURN_QUOTED,serialize($value));
                $message[$field] = $value;
                $fields[] = "$field = '$value'";
            } else {
                $value = phorum_db_interact(DB_RETURN_QUOTED, $value);
                $message[$field] = $value;
                $fields[] = "$field = '$value'";
            }
        }
    }

    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['message_table']}
         SET " . implode(', ', $fields) . "
         WHERE message_id = $message_id",
        NULL,
        DB_MASTERQUERY
    );

    // Full text searching updates.
    if (!empty($PHORUM['DBCONFIG']['mysql_use_ft']) &&
        isset($message['author']) &&
        isset($message['subject']) &&
        isset($message['body']) &&
        empty($PHORUM['DBCONFIG']['empty_search_table']) ) {


        $search_text = $message['author']  .' | '.
                       $message['subject'] .' | '.
                       $message['body'];

        phorum_db_interact(
            DB_RETURN_RES,
            "REPLACE DELAYED INTO {$PHORUM['search_table']}
             SET     message_id  = {$message_id},
                     forum_id    = {$message['forum_id']},
                     search_text = '$search_text'",
           NULL,
           DB_MASTERQUERY
        );
    }
}
// }}}

// {{{ Function: phorum_db_delete_message()
/**
 * Delete a message or a message tree from the database.
 *
 * @param integer $message_id
 *     The message_id of the message which should be deleted.
 *
 * @param integer $mode
 *     The mode of deletion. This is one of:
 *     - PHORUM_DELETE_MESSAGE: Delete a message and reconnect
 *       its reply messages to the parent of the deleted message.
 *     - PHORUM_DELETE_TREE: Delete a message and all its reply messages.
 */
function phorum_db_delete_message($message_id, $mode = PHORUM_DELETE_MESSAGE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($message_id, 'int');
    settype($mode, 'int');

    // Find the info for the message that has to be deleted.
    $msg = phorum_db_interact(
        DB_RETURN_ASSOC,
        "SELECT forum_id, message_id, thread, parent_id
         FROM   {$PHORUM['message_table']}
         WHERE  message_id = $message_id"
    );
    if (empty($msg)) trigger_error(
        "No message found for message_id $message_id", E_USER_ERROR
    );

    // Find all message_ids that have to be deleted, based on the mode.
    if ($mode == PHORUM_DELETE_TREE) {
        $mids = phorum_db_get_messagetree($message_id, $msg['forum_id']);
        $where = "message_id IN ($mids)";
        $mids = explode(',', $mids);
    } else {
        $mids = array($message_id);
        $where = "message_id = $message_id";
    }

    // First, the messages are unapproved, so replies will not get posted
    // during the time that we need for deleting them. There is still a
    // race condition here, but this already makes things quite reliable.
    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['message_table']}
         SET    status=".PHORUM_STATUS_HOLD."
         WHERE  $where",
        NULL,
        DB_MASTERQUERY
    );

    $thread = $msg['thread'];

    // Change reply messages to point to the parent of the deleted message.
    if ($mode == PHORUM_DELETE_MESSAGE)
    {
        // The forum_id is in here for speeding up the query
        // (with the forum_id a lookup key will be used).
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['message_table']}
             SET    parent_id = {$msg['parent_id']}
             WHERE  forum_id  = {$msg['forum_id']} AND
                    parent_id = {$msg['message_id']}",
            NULL,
            DB_MASTERQUERY
        );
    }

    // Delete the messages.
    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['message_table']}
         WHERE $where",
        NULL,
        DB_MASTERQUERY
    );

    // Delete the read flags.
    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['user_newflags_table']}
         WHERE $where",
        NULL,
        DB_MASTERQUERY
    );

    // Delete the edit tracking.
    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['message_tracking_table']}
         WHERE $where",
        NULL,
        DB_MASTERQUERY
    );

    // Full text searching updates.
    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['search_table']}
         WHERE $where",
        NULL,
        DB_MASTERQUERY
    );

    // It kind of sucks to have this here, but it is the best way
    // to ensure that thread info gets updated if messages are deleted.
    // Leave this include down here, so it is included conditionally.
    include_once('./include/thread_info.php');
    phorum_update_thread_info($thread);

    // We need to delete the subscriptions for the thread too.
    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['subscribers_table']}
         WHERE forum_id > 0 AND thread = $thread",
        NULL,
        DB_MASTERQUERY
    );

    // This function will be slow with a lot of messages.
    phorum_db_update_forum_stats(TRUE);

    return $mids;
}
// }}}

// {{{ Function: phorum_db_get_messagetree()
/**
 * Build a tree of all child (reply) messages below a message_id.
 *
 * @param integer $message_id
 *     The message_id for which to build the message tree.
 *
 * @param integer $forum_id
 *     The forum_id for the message.
 *
 * @return string
 *     A string containing a comma separated list of child message_ids
 *     for the given message_id.
 */
function phorum_db_get_messagetree($message_id, $forum_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($message_id, 'int');
    settype($forum_id, 'int');

    // Find all children for the provided message_id.
    $child_ids = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT message_id
         FROM {$PHORUM['message_table']}
         WHERE forum_id  = $forum_id AND
               parent_id = $message_id"
    );

    // Recursively build the message tree.
    $tree = "$message_id";
    foreach ($child_ids as $child_id) {
        $tree .= ',' . phorum_db_get_messagetree($child_id[0], $forum_id);
    }

    return $tree;
}
// }}}

// {{{ Function: phorum_db_get_message()
/**
 * Retrieve message(s) from the messages table by comparing value(s)
 * for a specified field in that table.
 *
 * You can provide either a single value or an array of values to search
 * for. If a single value is provided, then the function will return the
 * first matching message in the table. If an array of values is provided,
 * the function will return all matching messages in an array.
 *
 * @param mixed $value
 *     The value that you want to search on in the messages table.
 *     This can be either a single value or an array of values.
 *
 * @param string $field
 *     The message field (database column) to search on.
 *
 * @param boolean $ignore_forum_id
 *     By default, this function will only search for messages within the
 *     active forum (as defined by $PHORUM["forum_id"). By setting this
 *     parameter to a true value, the function will search in any forum.
 *
 * @param boolean $write_server
 *     This value can be set to true to specify that the message should be
 *     retrieved from the master (aka write-server) in case replication
 *     is used.
 *
 * @return mixed
 *     Either a single message or an array of messages (indexed by
 *     message_id), depending on the $value parameter. If no message is
 *     found at all, then either an empty array or NULL is returned
 *     (also depending on the $value parameter).
 */
function phorum_db_get_message($value, $field='message_id', $ignore_forum_id=FALSE, $write_server=FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    phorum_db_sanitize_mixed($value, 'string');
    settype($ignore_forum_id, 'bool');
    if (!phorum_db_validate_field($field)) trigger_error(
        'phorum_db_get_message(): Illegal database field ' .
        '"' . htmlspecialchars($field) . '"', E_USER_ERROR
    );

    $forum_id_check = '';
    if (!$ignore_forum_id && !empty($PHORUM['forum_id'])) {
        $forum_id_check = "forum_id = {$PHORUM['forum_id']} AND ";
    }

    if (is_array($value)) {
        $multiple = TRUE;
        $checkvar = "$field IN ('".implode("','",$value)."')";
        $limit = '';
    } else {
        $multiple=FALSE;
        $checkvar = "$field = '$value'";
        $limit = 'LIMIT 1';
    }

    $return = $multiple ? array() : NULL;

    if($write_server) {
        $flags = DB_MASTERQUERY;
    } else {
        $flags = 0;
    }

    $messages = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT *
         FROM   {$PHORUM['message_table']}
         WHERE  $forum_id_check $checkvar
         $limit",
        NULL,
        $flags
    );

    foreach ($messages as $message)
    {
        $message['meta'] = empty($message['meta'])
                         ? array()
                         : unserialize($message['meta']);

        if (! $multiple) {
            $return = $message;
            break;
        }

        $return[$message['message_id']] = $message;
    }

    return $return;
}
// }}}

// {{{ Function: phorum_db_get_messages()
/**
 * Retrieve messages from a specific thread.
 *
 * @param integer $thread
 *     The id of the thread.
 *
 * @param integer $page
 *     A page offset (based on the configured read_length) starting with 1.
 *     All messages are returned in case $page is 0.
 *
 * @param boolean $ignore_mod_perms
 *     If this parameter is set to a true value, then the function will
 *     return hidden messages, even if the active Phorum user is not
 *     a moderator.
 *
 * @param boolean $write_server
 *     This value can be set to true to specify that the message should be retrieved
 *     from the master (aka write-server) in case replication is used
 *
 * @return array
 *     An array of messages, indexed by message_id. One special key "users"
 *     is set too. This one contains an array of all involved user_ids.
 */
function phorum_db_get_messages($thread, $page=0, $ignore_mod_perms=FALSE, $write_server = FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($thread, 'int');
    settype($page, 'int');
    settype($ignore_mod_perms, 'int');

    // Check if the forum_id has to be checked.
    $forum_id_check = '';
    if (!empty($PHORUM['forum_id'])) {
        $forum_id_check = "forum_id = {$PHORUM['forum_id']} AND";
    }

    // Determine if not approved messages should be displayed.
    $approvedval = '';
    if (!$ignore_mod_perms &&
        !phorum_api_user_check_access(PHORUM_USER_ALLOW_MODERATE_MESSAGES)) {
        $approvedval = 'AND status ='.PHORUM_STATUS_APPROVED;
    }

    $sql = "SELECT *
            FROM   {$PHORUM['message_table']}
            WHERE  $forum_id_check
                   thread = $thread
                   $approvedval
            ORDER  BY message_id";

    if ($page > 0) {
       // Handle the page offset.
       $start = $PHORUM['read_length'] * ($page-1);
       $sql .= " LIMIT $start,".$PHORUM['read_length'];
    } else {
       // Handle reverse threading. This is only done if $page is 0.
       // In that case, the messages for threaded read are retrieved.
       if (!empty($PHORUM['reverse_threading']))
           $sql.=' DESC';
    }

    if($write_server) {
        $flags = DB_MASTERQUERY;
    } else {
        $flags = 0;
    }

    $messages = phorum_db_interact(DB_RETURN_ASSOCS, $sql, 'message_id', $flags);
    $involved_users = array();

    foreach ($messages as $id => $message)
    {
        // Unpack the message meta data.
        $messages[$id]['meta'] = empty($message['meta'])
                               ? array()
                               : unserialize($message['meta']);

        // Collect all involved users.
        if ($message['user_id']) {
            $involved_users[$message['user_id']] = $message['user_id'];
        }
    }

    // Always include the thread starter message in the return data.
    // It might not be in the messagelist if a page offset is used
    // (since the thread starter is only on the first page).
    if (count($messages) && !isset($messages[$thread]))
    {
        $starter = phorum_db_interact(
            DB_RETURN_ASSOC,
            "SELECT *
             FROM   {$PHORUM['message_table']}
             WHERE  $forum_id_check
                    message_id = $thread
                    $approvedval",
            NULL,
            $flags
        );

        if ($starter)
        {
            // Unpack the message meta data.
            $starter['meta'] = empty($starter['meta'])
                             ? array()
                             : unserialize($starter['meta']);

            $messages[$thread] = $starter;

            // Add to involved users.
            if ($starter['user_id']) {
                $involved_users[$starter['user_id']] = $starter['user_id'];
            }
        }
    }

    // Store the involved users in the message array.
    $messages['users'] = $involved_users;

    return $messages;
}
// }}}

// {{{ Function: phorum_db_get_message_index()
/**
 * Retrieve the index of a message (the offset from the thread starter
 * message) within a thread.
 *
 * @param integer $thread
 *     The thread id.
 *
 * @param integer $message_id
 *     The message id for which to determine the index.
 *
 * @return integer
 *     The index of the message, starting with 0.
 */
function phorum_db_get_message_index($thread=0, $message_id=0)
{
    $PHORUM = $GLOBALS['PHORUM'];

    // check for valid values
    if (empty($thread) || empty($message_id)) {
        return 0;
    }

    settype($thread, 'int');
    settype($message_id, 'int');

    $forum_id_check = '';
    if (!empty($PHORUM['forum_id'])) {
        $forum_id_check = "forum_id = {$PHORUM['forum_id']} AND";
    }

    $approvedval = '';
    if (!phorum_api_user_check_access(PHORUM_USER_ALLOW_MODERATE_MESSAGES)) {
        $approvedval='AND status ='.PHORUM_STATUS_APPROVED;
    }

    $index = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT count(*)
         FROM   {$PHORUM['message_table']}
         WHERE  $forum_id_check
                thread = $thread
                $approvedval AND
                message_id <= $message_id
         ORDER  BY message_id"
    );

    return $index;
}
// }}}

// {{{ Function: phorum_db_search()
/**
 * Search the database using the provided search criteria and return
 * an array containing the total count of matches and the visible
 * messages based on the page $offset and $length.
 *
 * @param string $search
 *     The query to search on in messages and subjects.
 *
 * @param mixed $author
 *     The query to search on in the message authors or a numerical user_id
 *     if searching for all messages for a certain user_id.
 *
 * @param boolean $return_threads
 *     Whether to return the results as threads (TRUE) or messages (FALSE).
 *
 * @param integer $offset
 *     The result page offset starting with 0.
 *
 * @param integer $length
 *     The result page length (nr. of results per page).
 *
 * @param string $match_type
 *     The type of search. This can be one of:
 *     - ALL:     search on all of the words (uses $search)
 *     - ANY:     search on any of the words (uses $search)
 *     - PHRASE:  search for an exact phrase (uses $search)
 *     - USER_ID: search for an author id (uses $author)
 *
 * @param integer $days
 *     The number of days to go back in the database for searching
 *     (last [x] days) or zero to search within all dates.
 *
 * @param string $match_forum
 *     The forum restriction. This can be either the string "ALL" to search
 *     in any of the readable forums or a comma separated list of forum_ids.
 *
 * @return array
 *     An array containing two fields:
 *     - "count" contains the total number of matching messages.
 *     - "rows" contains the messages that are visible, based on the page
 *       $offset and page $length. The messages are indexed by message_id.
 */
function phorum_db_search($search, $author, $return_threads, $offset, $length, $match_type, $days, $match_forum)
{
    $PHORUM = $GLOBALS['PHORUM'];

    $fulltext_mode = isset($PHORUM['DBCONFIG']['mysql_use_ft']) &&
                     $PHORUM['DBCONFIG']['mysql_use_ft'];

    $search = trim($search);
    $author = trim($author);
    settype($return_threads, 'bool');
    settype($offset, 'int');
    settype($length, 'int');
    settype($days, 'int');

    // For spreading search results over multiple pages.
    $start = $offset * $length;

    // Initialize the return data.
    $return = array('count' => 0, 'rows' => array());

    // Return immediately if the search queries are empty.
    if ($search == '' && $author == '') return $return;

    // Check what forums the active Phorum user can read.
    $allowed_forums = phorum_api_user_check_access(
        PHORUM_USER_ALLOW_READ, PHORUM_ACCESS_LIST
    );

    // If the user is not allowed to search any forum or the current
    // active forum, then return the emtpy search results array.
    if (empty($allowed_forums) ||
        ($PHORUM['forum_id']>0 &&
         !in_array($PHORUM['forum_id'], $allowed_forums))) return $return;

    // Prepare forum_id restriction.
    $match_forum_arr = explode(",", $match_forum);
    $search_forums = array();
    foreach ($match_forum_arr as $forum_id) {
        if ($forum_id == "ALL") {
            $search_forums = $allowed_forums;
            break;
        }
        if (isset($allowed_forums[$forum_id])) {
            $search_forums[] = $forum_id;
        }
    }
    if (count($search_forums)){
        $forum_where = "AND forum_id in (".implode(",", $search_forums).")";
    } else {
        // Hack attempt or error. Return empty search results.
        return $return;
    }

    // Prepare day limit restriction.
    if ($days > 0) {
        $ts = time() - 86400*$days;
        $datestamp_where = "AND datestamp >= $ts";
    } else {
        $datestamp_where = '';
    }

    // We make use of temporary tables for storing intermediate search
    // results. These tables are stored in $tables during processing.
    $tables = array();

    // ----------------------------------------------------------------------
    // Handle search for user_id only.
    // ----------------------------------------------------------------------

    if ($search == '' && $author != '' && $match_type == 'USER_ID')
    {
        $user_id = (int) $author;
        if (empty($user_id)) return $return;

        // Seach for messages.
        $sql = "SELECT SQL_CALC_FOUND_ROWS *
                FROM   {$PHORUM['message_table']}
                USE    KEY(user_messages)
                WHERE  user_id = $user_id AND
                       status=".PHORUM_STATUS_APPROVED." AND
                       moved=0
                       $forum_where
                ORDER  BY message_id DESC
                LIMIT  $start, $length";
        $rows = phorum_db_interact(DB_RETURN_ASSOCS, $sql);

        // Retrieve the number of found messages.
        $count = phorum_db_interact(
            DB_RETURN_VALUE,
            "SELECT found_rows()"
        );

        // Fill the return data.
        $return = array("count" => $count, "rows"  => $rows);

        return $return;
    }

    // ----------------------------------------------------------------------
    // Handle search for message and subject.
    // ----------------------------------------------------------------------

    if ($search != '')
    {
        $match_str = '';
        $tokens = array();

        if ($match_type == "PHRASE")
        {
            $search = str_replace('"', '', $search);
            $match_str = '"'.phorum_db_interact(DB_RETURN_QUOTED, $search).'"';
        }
        else
        {
            // Surround with spaces so matching is easier.
            $search = " $search ";

            // Pull out all grouped terms, e.g. (nano mini).
            $paren_terms = array();
            if (strstr($search, '(')) {
                preg_match_all('/ ([+\-~]*\(.+?\)) /', $search, $m);
                $search = preg_replace('/ [+\-~]*\(.+?\) /', ' ', $search);
                $paren_terms = $m[1];
            }

            // Pull out all the double quoted strings,
            // e.g. '"iMac DV" or -"iMac DV".
            $quoted_terms = array();
            if (strstr( $search, '"')) {
                preg_match_all('/ ([+\-~]*".+?") /', $search, $m);
                $search = preg_replace('/ [+\-~]*".+?" /', ' ', $search);
                $quoted_terms = $m[1];
            }

            // Finally, pull out the rest words in the string.
            $norm_terms = preg_split("/\s+/", $search, 0, PREG_SPLIT_NO_EMPTY);

            // Merge all search terms together.
            $tokens =  array_merge($quoted_terms, $paren_terms, $norm_terms);
        }

        // Handle full text message / subject search.
        if ($fulltext_mode)
        {
            // Create a match string based on the parsed query tokens.
            if (count($tokens))
            {
                $match_str = '';

                foreach ($tokens as $term)
                {
                    if (!strstr("+-~", substr($term, 0, 1)))
                    {
                        if (strstr($term, ".") &&
                            !preg_match('!^".+"$!', $term) &&
                            substr($term, -1) != "*") {
                            $term = "\"$term\"";
                        }
                        if ($match_type == "ALL") {
                            $term = "+".$term;
                        }
                    }

                    $match_str .= "$term ";
                }

                $match_str = trim($match_str);
                $match_str = phorum_db_interact(DB_RETURN_QUOTED, $match_str);
            }

            $table_name = $PHORUM['search_table']."_ft_".md5(microtime());

            phorum_db_interact(
                DB_RETURN_RES,
                "CREATE TEMPORARY TABLE $table_name (
                     KEY (message_id)
                 ) ENGINE=HEAP
                   SELECT message_id
                   FROM   {$PHORUM['search_table']}
                   WHERE  MATCH (search_text)
                          AGAINST ('$match_str' IN BOOLEAN MODE)"
            );

            $tables[] = $table_name;
        }
        // Handle standard message / subject search.
        else
        {
            if (count($tokens))
            {
                $condition = ($match_type == "ALL") ? "AND" : "OR";

                foreach($tokens as $tid => $token) {
                     $tokens[$tid] = phorum_db_interact(DB_RETURN_QUOTED, $token);
                }

                $match_str = "search_text LIKE " .
                             "('%".implode("%' $condition '%", $tokens)."%')";
            }

            $table_name = $PHORUM['search_table']."_like_".md5(microtime());

            phorum_db_interact(
                DB_RETURN_RES,
                "CREATE TEMPORARY TABLE $table_name (
                     KEY (message_id)
                 ) ENGINE=HEAP
                   SELECT message_id
                   FROM   {$PHORUM['search_table']}
                   WHERE  $match_str"
            );

            $tables[] = $table_name;
        }
    }

    // ----------------------------------------------------------------------
    // Handle search for author.
    // ----------------------------------------------------------------------

    if ($author != '')
    {
        $table_name = $PHORUM['search_table']."_author_".md5(microtime());

        // Search either by user_id or by username.
        if ($match_type == "USER_ID") {
            $author = (int) $author;
            $author_where = "user_id = $author";
        } else {
            $author = phorum_db_interact(DB_RETURN_QUOTED, $author);
            $author_where = "author = '$author'";
        }

        phorum_db_interact(
            DB_RETURN_RES,
            "CREATE TEMPORARY TABLE $table_name (
               KEY (message_id)
             ) ENGINE=HEAP
               SELECT message_id
               FROM   {$PHORUM["message_table"]}
               WHERE  $author_where"
        );

        $tables[] = $table_name;
    }

    // ----------------------------------------------------------------------
    // Gather the results.
    // ----------------------------------------------------------------------

    if (count($tables))
    {
        // If we only have one temporary table, we can use it directly.
        if (count($tables) == 1) {
            $table = array_shift($tables);
        }
        // In case we have multiple temporary tables, we join them together
        // in a new temporary table for retrieving the results.
        else
        {
            $table = $PHORUM['search_table']."_final_".md5(microtime());

            $joined_tables = "";
            $main_table = array_shift($tables);
            foreach ($tables as $tbl) {
                $joined_tables.= "INNER JOIN $tbl USING (message_id)";
            }

            phorum_db_interact(
                DB_RETURN_RES,
                "CREATE TEMPORARY TABLE $table (
                   KEY (message_id)
                 ) ENGINE=HEAP
                   SELECT m.message_id
                   FROM   $main_table m $joined_tables"
            );
        }

        // When only threads need to be returned, then join the results
        // that we have so far with the message table into a result set
        // that only contains the threads for the results.
        if ($return_threads)
        {
            $threads_table = $PHORUM['search_table']."_final_".md5(microtime());
            phorum_db_interact(
                DB_RETURN_RES,
                "CREATE TEMPORARY TABLE $threads_table (
                   KEY (message_id)
                 ) ENGINE=HEAP
                   SELECT distinct thread AS message_id
                   FROM   {$PHORUM['message_table']}
                          INNER JOIN $table
                          USING (message_id)"
            );

            $table = $threads_table;
        }

        // Retrieve the found messages.
        $rows = phorum_db_interact(
            DB_RETURN_ASSOCS,
            "SELECT SQL_CALC_FOUND_ROWS *
             FROM   {$PHORUM['message_table']}
                    INNER JOIN $table USING (message_id)
             WHERE  status=".PHORUM_STATUS_APPROVED."
                    $forum_where
                    $datestamp_where
             ORDER  BY datestamp DESC
             LIMIT  $start, $length"
        );

        // Retrieve the number of found messages.
        $count = phorum_db_interact(
            DB_RETURN_VALUE,
            "SELECT found_rows()"
        );

        // Fill the return data.
        $return = array("count" => $count, "rows"  => $rows);
    }

    return $return;
}
// }}}

// {{{ Function: phorum_db_get_neighbour_thread()
/**
 * Retrieve the closest neighbour thread. What "neighbour" is, depends on the
 * float to top setting. If float to top is enabled, then the
 * modifystamp is used for comparison (so the time at which the last
 * message was posted to a thread). Otherwise, the thread id is used
 * (so the time at which a thread was started).
 *
 * @param integer $key
 *     The key value of the message for which the neighbour must be returned.
 *     The key value is either the modifystamp (if float to top is enabled)
 *     or the thread id.
 *
 * @param string $direction
 *     Either "older" or "newer".
 *
 * @return integer
 *     The thread id for the requested neigbour thread or 0 (zero) if there
 *     is no neighbour available.
 */
function phorum_db_get_neighbour_thread($key, $direction)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($key, 'int');

    $keyfield = $PHORUM['float_to_top'] ? 'modifystamp' : 'thread';

    switch ($direction) {
        case 'newer': $compare = '>'; $orderdir = 'ASC';  break;
        case 'older': $compare = '<'; $orderdir = 'DESC'; break;
        default:
            trigger_error(
                'phorum_db_get_neighbour_thread(): ' .
                'Illegal direction "'.htmlspecialchars($direction).'"',
                E_USER_ERROR
            );
    }

    // If the active Phorum user is not a moderator for the forum, then
    // the neighbour message should be approved.
    $approvedval = '';
    if (!phorum_api_user_check_access(PHORUM_USER_ALLOW_MODERATE_MESSAGES)) {
        $approvedval = 'AND status = '.PHORUM_STATUS_APPROVED;
    }

    // Select the neighbour from the database.
    $thread = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT thread
         FROM   {$PHORUM['message_table']}
         WHERE  forum_id = {$PHORUM['forum_id']} AND
                parent_id = 0
                $approvedval AND
                $keyfield $compare $key
         ORDER  BY $keyfield $orderdir
         LIMIT  1"
    );

    return $thread;
}
// }}}

// {{{ Function: phorum_db_get_forums()
/**
 * Retrieve a list of forums. The forums which are returned can be filtered
 * through the function parameters. Note that only one parameter is
 * effective at a time.
 *
 * @param mixed $forum_ids
 *     A single forum_id or an array of forum_ids for which to retrieve the
 *     forum data. If this parameter is 0 (zero), then the $parent_id
 *     parameter will be checked.
 *
 * @param mixed $parent_id
 *     Retrieve the forum data for all forums that have their parent_id set
 *     to $parent_id. If this parameter is NULL, then the $vroot parameter
 *     will be checked.
 *
 * @param mixed $vroot
 *     Retrieve the forum data for all forums that are in the given $vroot.
 *     If this parameter is NULL, then the $inherit_id parameter will be
 *     checked.
 *
 * @param mixed $inherit_id
 *     Retrieve the forum data for all forums that inherit their settings
 *     from the forum with id $inherit_id.
 *
 * @return array
 *     An array of forums, indexed by forum_id.
 */
function phorum_db_get_forums($forum_ids = 0, $parent_id = NULL, $vroot = NULL, $inherit_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    phorum_db_sanitize_mixed($forum_ids, 'int');
    if ($parent_id  !== NULL) settype($parent_id, 'int');
    if ($vroot      !== NULL) settype($vroot, 'int');
    if ($inherit_id !== NULL) settype($inherit_id, 'int');

    // Backward compatibility: previously, -1 was used for $parent_id
    // instead of NULL for indicating "any parent_id".
    if ($parent_id !== NULL && $parent_id == -1) $parent_id = NULL;

    $where = '';
    if (!empty($forum_ids)) {
        if (is_array($forum_ids)) {
            $where .= 'forum_id IN ('.implode(', ', $forum_ids).')';
        } else {
            $where .= "forum_id = $forum_ids";
        }
    } elseif ($inherit_id !== NULL) {
        $where .= "inherit_id = $inherit_id";
        if (!defined('PHORUM_ADMIN')) $where.=' AND active=1';
    } elseif ($parent_id !== NULL) {
        $where .= "parent_id = $parent_id";
        if (!defined('PHORUM_ADMIN')) $where.=' AND active=1';
    } elseif ($vroot !== NULL) {
        $where .= "vroot = $vroot";
    } else {
        $where .= 'forum_id <> 0';
    }

    $forums = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT *
         FROM   {$PHORUM['forums_table']}
         WHERE  $where
         ORDER  BY display_order ASC, name",
       'forum_id'
    );

    return $forums;
}
// }}}

// {{{ Function: phorum_db_update_forum_stats()
/**
 * Update the forums stats. This function will update the thread count,
 * message count, sticky message count and last post timestamp for a forum.
 * The function can either process delta values for the stats (this is
 * the most friendly way of updating) or fill the stats from scratch by
 * querying the database for the correct value.
 *
 * When the forum stats are updated, the cache_version for the forum
 * will be raised by one. This will flag the cache system that cached
 * data for the forum has to be refreshed.
 *
 * @param boolean $refresh
 *     If TRUE, the all stats will be filled from scratch by querying
 *     the database.
 *
 * @param integer $msg_count_change
 *     Delta for the message count or zero to query the value
 *     from the database.
 *
 * @param integer $timestamp
 *     The post time of the last message or zero to query the value from
 *     the database.
 *
 * @param integer $thread_count_change
 *     Delta for the thread count or zero to query the value
 *     from the database.

 * @param integer $sticky_count_change
 *     Delta for the sticky message count or zero to query the value
 *     from the database.
 */
function phorum_db_update_forum_stats($refresh=FALSE, $msg_count_change=0, $timestamp=0, $thread_count_change=0, $sticky_count_change=0)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($refresh, 'bool');
    settype($msg_count_change, 'int');
    settype($timestamp, 'int');
    settype($thread_count_change, 'int');
    settype($sticky_count_change, 'int');

    // Always do a full refresh on small forums.
    if (isset($PHORUM['message_count']) && $PHORUM['message_count']<1000) {
        $refresh = TRUE;
    }

    if ($refresh || empty($msg_count_change)) {
        $message_count = phorum_db_interact(
            DB_RETURN_VALUE,
            "SELECT count(*)
             FROM   {$PHORUM['message_table']}
             WHERE  forum_id = {$PHORUM['forum_id']} AND
                    status   = ".PHORUM_STATUS_APPROVED
        );
    } else {
        $message_count = "message_count+$msg_count_change";
    }

    if ($refresh || empty($timestamp)) {
        $last_post_time = phorum_db_interact(
            DB_RETURN_VALUE,
            "SELECT max(modifystamp)
             FROM   {$PHORUM['message_table']}
             WHERE  status   = ".PHORUM_STATUS_APPROVED." AND
                    forum_id = {$PHORUM['forum_id']}"
        );
        // In case we're calling this function for an empty forum.
        if ($last_post_time === NULL) {
            $last_post_time = 0;
        }
    } else {
        $last_post_time = $timestamp;
    }

    if ($refresh || empty($thread_count_change)) {
        $thread_count = phorum_db_interact(
            DB_RETURN_VALUE,
            "SELECT count(*)
             FROM   {$PHORUM['message_table']}
             WHERE  forum_id  = {$PHORUM['forum_id']} AND
                    parent_id = 0 AND
                    status    = ".PHORUM_STATUS_APPROVED
        );
    } else {
        $thread_count = "thread_count+$thread_count_change";
    }

    if ($refresh || empty($sticky_count_change)) {
        $sticky_count = phorum_db_interact(
            DB_RETURN_VALUE,
            "SELECT count(*)
             FROM   {$PHORUM['message_table']}
             WHERE  forum_id  = {$PHORUM['forum_id']} AND
                    sort      = ".PHORUM_SORT_STICKY." AND
                    parent_id = 0 AND
                    status    = ".PHORUM_STATUS_APPROVED
        );
    } else {
        $sticky_count = "sticky_count+$sticky_count_change";
    }

    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['forums_table']}
         SET    cache_version  = cache_version + 1,
                thread_count   = $thread_count,
                message_count  = $message_count,
                sticky_count   = $sticky_count,
                last_post_time = $last_post_time
         WHERE  forum_id = {$PHORUM['forum_id']}",
        NULL,
        DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_move_thread()
/**
 * Move a thread to another forum.
 *
 * @param integer $thread_id
 *     The id of the thread that has to be moved.
 *
 * @param integer
 *     The id of the destination forum.
 */
function phorum_db_move_thread($thread_id, $toforum)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($thread_id, 'int');
    settype($toforum, 'int');

    if ($toforum > 0 && $thread_id > 0)
    {
        // Retrieve the messages from the thread, so we know for which
        // messages we have to update the newflags and search data below.
        $thread_messages = phorum_db_get_messages($thread_id);
        unset($thread_messages['users']);

        // All we have to do to move the thread to a different forum,
        // is update the forum_id for the messages in that thread.
        // Simple, isn't it?
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['message_table']}
             SET    forum_id = $toforum
             WHERE  thread   = $thread_id",
            NULL,
            DB_MASTERQUERY
        );

        // Update the stats for the source forum.
        phorum_db_update_forum_stats(TRUE);

        // Update the stats for the destination forum.
        $old_id = $GLOBALS['PHORUM']['forum_id'];
        $GLOBALS['PHORUM']['forum_id'] = $toforum;
        phorum_db_update_forum_stats(TRUE);
        $GLOBALS['PHORUM']['forum_id'] = $old_id;

        // Move the newflags and search data to the destination forum.

        /**
         * @todo In the move thread code, there are some flaws. The
         *       newflags for the user that is moving the message
         *       are used as the source for deciding what flags
         *       to delete or move for all other users. This results
         *       in strange newflag problems.
         *
         *       This main issue here is that the newflags should be
         *       handled separately for each user; no updates should be
         *       based on the newflags for the active user. The current
         *       algorithm will only make sure that the newflags will look
         *       correct for that specific user. The problem is that we
         *       do not yet have an idea on how to handle this with
         *       enough performance.
         */
        // First, gather information for doing the updates.
        $new_newflags = phorum_db_newflag_get_flags($toforum);
        $message_ids  = array();
        $delete_ids   = array();
        $search_ids   = array();
        foreach ($thread_messages as $mid => $data)
        {
            // Gather information for updating the newflags.
            // Moving the newflag is only useful if it is higher than the
            // min_id of the target forum.
            if (!empty($new_newflags['min_id'][$toforum]) &&
                $mid > $new_newflags['min_id'][$toforum]) {
                $message_ids[] = $mid;
            } else {
            // Other newflags can be deleted.
                $delete_ids[] = $mid;
            }

            // gather the information for updating the search table
            $search_ids[] = $mid;
        }

        // Move newflags.
        if (count($message_ids)) {
            phorum_db_newflag_update_forum($message_ids);
        }

        // Update subscriptions.
        if (count($message_ids)) {
            $ids_str = implode(', ',$message_ids);
            phorum_db_interact(
                DB_RETURN_RES,
                "UPDATE {$PHORUM['subscribers_table']}
                 SET    forum_id = $toforum
                 WHERE  thread IN ($ids_str)",
                NULL,
                DB_MASTERQUERY
            );
        }

        // Delete newflags.
        if (count($delete_ids)) {
            $ids_str = implode(', ',$delete_ids);
            phorum_db_interact(
                DB_RETURN_RES,
                "DELETE FROM {$PHORUM['user_newflags_table']}
                 WHERE  message_id IN($ids_str)",
                NULL,
                DB_MASTERQUERY
            );
        }

        // Update search data.
        if (count($search_ids)) {
            $ids_str = implode(', ',$search_ids);
            phorum_db_interact(
                DB_RETURN_RES,
                "UPDATE {$PHORUM['search_table']}
                 SET    forum_id = $toforum
                 WHERE  message_id in ($ids_str)",
                NULL,
                DB_MASTERQUERY
            );
        }
    }
}
// }}}

// {{{ Function: phorum_db_close_thread()
/**
 * Close a thread for posting.
 *
 * @param integer
 *     The id of the thread that has to be closed.
 */
function phorum_db_close_thread($thread_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($thread_id, 'int');

    if ($thread_id > 0) {
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['message_table']}
             SET    closed = 1
             WHERE  thread = $thread_id",
            NULL,
            DB_MASTERQUERY
        );
    }
}
// }}}

// {{{ Function: phorum_db_reopen_thread()
/**
 * (Re)open a thread for posting.
 *
 * @param integer
 *     The id of the thread that has to be opened.
 */
function phorum_db_reopen_thread($thread_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($thread_id, 'int');

    if ($thread_id > 0) {
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['message_table']}
             SET    closed = 0
             WHERE  thread = $thread_id",
            NULL,
            DB_MASTERQUERY
        );
    }
}
// }}}

// {{{ Function: phorum_db_add_forum()
/**
 * Create a forum.
 *
 * @param array $forum
 *     The forum to create. This is an array, which should contain the
 *     following fields: name, active, description, template, folder_flag,
 *     parent_id, list_length_flat, list_length_threaded, read_length,
 *     moderation, threaded_list, threaded_read, float_to_top,
 *     display_ip_address, allow_email_notify, language, email_moderators,
 *     display_order, edit_post, pub_perms, reg_perms.
 *
 * @return integer
 *     The forum_id that was assigned to the new forum.
 */
function phorum_db_add_forum($forum)
{
    $PHORUM = $GLOBALS['PHORUM'];

    // check for fields that must be set for mysql strict mode
    if(empty($forum["description"])) $forum["description"] = "";
    if(empty($forum["forum_path"])) $forum["forum_path"] = "";
    if(empty($forum["template_settings"])) $forum["template_settings"] = "";

    $insertfields = array();
    foreach ($forum as $key => $value)
    {
        if (phorum_db_validate_field($key))
        {
            if (is_numeric($value) &&
                !in_array($key,$PHORUM['string_fields_forum'])) {
                $value = (int)$value;
                $insertfields[$key] = $value;
            /**
             * @todo Wouldn't it be better to have this one set to a real
             *       NULL value from the script that calls this function?
             *       If for some reason somebody wants to use the string
             *       'NULL' for a value (a geek setting up a Phorum
             *       probably ;-), then strange things will happen.
             */
            } elseif ($value == 'NULL') {
                $insertfields[$key] = $value;
            } else {
                $value = phorum_db_interact(DB_RETURN_QUOTED, $value);
                $insertfields[$key] = "'$value'";
            }
        }
    }


    $forum_id = phorum_db_interact(
        DB_RETURN_NEWID,
        "INSERT INTO {$PHORUM['forums_table']}
                (".implode(', ', array_keys($insertfields)).")
         VALUES (".implode(', ', $insertfields).")",
        NULL,
        DB_MASTERQUERY
    );

    return $forum_id;
}
// }}}

// {{{ Function: phorum_db_add_message_edit()
/**
 * Add a message-edit item
 *
 * @param array $edit_data
 *     The edit_data to add. This is an array, which should contain the
 *     following fields: diff_body, diff_subject, time, message_id and user_id.
 *
 * @return integer
 *     The tracking_id that was assigned to that edit
 */
function phorum_db_add_message_edit($edit_data)
{
    $PHORUM = $GLOBALS['PHORUM'];


    foreach ($edit_data as $key => $value) {
        if (is_numeric($value)) {
            $edit_data[$key] = (int)$value;
        } elseif (is_array($value)) {
            $value = serialize($value);
            $edit_data[$key] = phorum_db_interact(DB_RETURN_QUOTED, $value);
        } else {
            $edit_data[$key] = phorum_db_interact(DB_RETURN_QUOTED, $value);
        }
    }

    $insertfields = array(
        'message_id'     => $edit_data['message_id'],
        'user_id'        => $edit_data['user_id'],
        'time'           => $edit_data['time'],
        'diff_body'      => "'" . $edit_data['diff_body'] . "'",
        'diff_subject'   => "'" . $edit_data['diff_subject'] . "'",
    );

    // Insert the tracking-entry and get the new tracking_id.
    $tracking_id = phorum_db_interact(
        DB_RETURN_NEWID,
        "INSERT INTO {$PHORUM['message_tracking_table']}
                (".implode(', ', array_keys($insertfields)).")
         VALUES (".implode(', ', $insertfields).")",
        NULL,
        DB_MASTERQUERY
    );

    return $tracking_id;
}
// }}}

// {{{ Function: phorum_db_get_message_edits()
/**
 * Retrieve a list of message-edits for a message
 *
 * @param integer $message_id
 *     The message id for which to retrieve the edits.
 *
 * @return array
 *     An array of message edits, indexed by track_id. The array elements
 *     are arrays containing the fields: user_id, time, diff_body
 *     and diff_subject.
 */
function phorum_db_get_message_edits($message_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($message_id, 'int');

    // Select the message files from the database.
    $edits = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT user_id,
                time,
                diff_body,
                diff_subject,
                track_id
         FROM   {$PHORUM['message_tracking_table']}
         WHERE  message_id = $message_id
         ORDER  BY track_id ASC",
        'track_id'
    );

    foreach ($edits as $id => $edit)
    {
        // Unpack the message meta data.
        $edits[$id]['diff_body'] = empty($edit['diff_body'])
                               ? array()
                               : unserialize($edit['diff_body']);

        // Unpack the message meta data.
        $edits[$id]['diff_subject'] = empty($edit['diff_subject'])
                               ? array()
                               : unserialize($edit['diff_subject']);

    }

    return $edits;
}
// }}}

// {{{ Function: phorum_db_drop_forum()
/**
 * Drop a forum and all of its messages.
 *
 * @param integer $forum_id
 *     The id of the forum to drop.
 */
function phorum_db_drop_forum($forum_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($forum_id, 'int');

    // These are the tables that hold forum related data.
    $tables = array (
        $PHORUM['message_table'],
        $PHORUM['user_permissions_table'],
        $PHORUM['user_newflags_table'],
        $PHORUM['subscribers_table'],
        $PHORUM['forum_group_xref_table'],
        $PHORUM['forums_table'],
        $PHORUM['banlist_table'],
        $PHORUM['search_table']
    );

    // Delete the data for the $forum_id from all those tables.
    foreach ($tables as $table) {
        phorum_db_interact(
            DB_RETURN_RES,
            "DELETE FROM $table
             WHERE forum_id = $forum_id",
            NULL,
            DB_MASTERQUERY
        );
    }

    // Collect all orphin message attachment files from the database.
    // These are all messages that are linked to a message, but for which
    // the message_id does not exist in the message table (anymore).
    // This might catch some more messages than only the ones for the
    // deleted forum alone. That should never be a problem.
    $files = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT file_id
         FROM   {$PHORUM['files_table']}
                LEFT JOIN {$PHORUM['message_table']}
                USING (message_id)
         WHERE  {$PHORUM['files_table']}.message_id > 0 AND
                link = '" . PHORUM_LINK_MESSAGE . "' AND
                {$PHORUM['message_table']}.message_id is NULL",
        0 // keyfield 0 is the file_id
    );

    // Delete all orphan message attachment files.
    if (!empty($files)) {
        phorum_db_interact(
            DB_RETURN_RES,
            "DELETE FROM {$PHORUM['files_table']}
             WHERE  file_id IN (".implode(",", array_keys($files)).")",
            NULL,
            DB_MASTERQUERY
        );
    }
}
// }}}

// {{{ Function: phorum_db_drop_folder()
/**
 * Drop a forum folder. If the folder contains child forums or folders,
 * then the parent_id for those will be updated to point to the parent
 * of the folder that is being dropped.
 *
 * @param integer $forum_id
 *     The id of the folder to drop.
 */
function phorum_db_drop_folder($forum_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($forum_id, 'int');

    // See if the $forum_id is really a folder and find its
    // parent_id, which we can use to reattach children of the folder.
    $new_parent_id = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT parent_id
         FROM   {$PHORUM['forums_table']}
         WHERE  forum_id = $forum_id AND
                folder_flag = 1"
    );
    if ($new_parent_id === NULL) trigger_error(
        "phorum_db_drop_folder(): id $forum_id not found or not a folder",
        E_USER_ERROR
    );

    // Start with reattaching the folder's children to the new parent.
    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['forums_table']}
         SET    parent_id = $new_parent_id
         WHERE  parent_id = $forum_id",
        NULL,
        DB_MASTERQUERY
    );

    // Now, drop the folder.
    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['forums_table']}
         WHERE  forum_id = $forum_id",
        NULL,
        DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_update_forum()
/**
 * Update the settings for one or more forums.
 *
 * @param array $forum
 *     The forum to update. This is an array, which should contain at least
 *     the field "forum_id" to indicate what forum to update. Next to that,
 *     one or more of the other fields from phorum_db_add_forum() can be
 *     used to describe changed values. The "forum_id" field can also
 *     contain an array of forum_ids. By using that, the settings can be
 *     updated for all the forum_ids at once.
 *
 * @return boolean
 *     True if all settings were stored successfully. This function will
 *     always return TRUE, so we could do without a return value. The
 *     return value is here for backward compatibility.
 */
function phorum_db_update_forum($forum)
{
    $PHORUM = $GLOBALS['PHORUM'];

    // Check if the forum_id is set.
    if (!isset($forum['forum_id']) || empty($forum['forum_id'])) trigger_error(
        'phorum_db_update_forum(): $forum["forum_id"] cannot be empty',
        E_USER_ERROR
    );

    phorum_db_sanitize_mixed($forum['forum_id'], 'int');

    // See what forum(s) to update.
    if (is_array($forum['forum_id'])) {
        $forumwhere = 'forum_id IN ('.implode(', ',$forum['forum_id']).')';
    } else {
        $forumwhere = 'forum_id = ' . $forum['forum_id'];
    }
    unset($forum['forum_id']);

    // Prepare the SQL code for updating the fields.
    $fields = array();
    foreach ($forum as $key => $value)
    {
        if (phorum_db_validate_field($key))
        {
            if ($key == 'forum_path') {
                $value = serialize($value);
                $value = phorum_db_interact(DB_RETURN_QUOTED, $value);
                $fields[] = "$key = '$value'";
            } elseif (is_numeric($value) &&
                !in_array($key,$PHORUM['string_fields_forum'])) {
                $value = (int)$value;
                $fields[] = "$key = $value";
            } elseif ($value == 'NULL') {
                $fields[] = "$key = $value";
            } else {
                $value = phorum_db_interact(DB_RETURN_QUOTED, $value);
                $fields[] = "$key = '$value'";
            }
        }
    }

    // Run the update, if there are fields to update.
    if (count($fields)) {
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['forums_table']}
             SET "  .implode(', ', $fields) . "
             WHERE  $forumwhere",
            NULL,
            DB_MASTERQUERY
        );
    }

    return TRUE;
}
// }}}

// {{{ Function: phorum_db_get_groups()
/**
 * Retrieve all groups or one specific group.
 *
 * @param mixed $group_id
 *     A single group id or an array of group ids for which to retrieve
 *     the group data. If this parameter is 0 (zero), then all groups will
 *     be returned.
 *
 * @param boolean $sorted
 *     If this parameter has a true value, then the list of groups will
 *     be sorted by the group name field.
 *
 * @return array
 *     An array of groups, indexed by group_id.
 */
function phorum_db_get_groups($group_id = 0, $sorted = FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];


    phorum_db_sanitize_mixed($group_id,"int");

    if(is_array($group_id) && count($group_id)) {
        $group_str=implode(',',$group_id);
        $group_where=" where group_id IN($group_str)";
    } elseif(!is_array($group_id) && $group_id!=0) {
        $group_where=" where group_id=$group_id";
    } else {
        $group_where="";
    }


    // Retrieve the group(s) from the database.
    $groups = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT *
         FROM {$PHORUM['groups_table']}
         $group_where",
        'group_id'
    );

    // Retrieve the group permissions from the database.
    $perms = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT *
         FROM {$PHORUM['forum_group_xref_table']}
         $group_where"
    );

    // Add the permissions to the group(s).
    foreach ($groups as $id => $group) $groups[$id]['permissions'] = array();
    foreach ($perms as $perm)
    {
        // Little safety net against orphin records (shouldn't happen).
        if (!isset($groups[$perm['group_id']])) continue;

        $groups[$perm['group_id']]['permissions'][$perm['forum_id']]
            = $perm['permission'];
    }

    // Sort the list by group name.
    if ($sorted) {
        uasort($groups, 'phorum_db_sort_groups');
    }

    return $groups;
}

function phorum_db_sort_groups($a,$b) {
    return strcasecmp($a["name"], $b["name"]);
}
// }}}

// {{{ Function: phorum_db_get_group_members()
/**
 * Retrieve a list of members for a group or for a list of groups.
 *
 * If the member list for a list of groups is requested, any member matching
 * the specified status in any of the groups will be included in the return
 * array. There will be no group info in the return array however, so this
 * function cannot be used to retrieve a full group to member mapping. This
 * specific functionality is used from the Phorum scripts to see if there are
 * unapproved group members in any of the forums for which the active user
 * can moderate the group members.
 *
 * @param mixed $group_id
 *     A single group_id or an array of group_ids, for which to retrieve
 *     the members.
 *
 * @param integer $status
 *     A specific member status to look for. Defaults to all.
 *     Possible statuses are:
 *     - PHORUM_USER_GROUP_SUSPENDED:  (temporarily) deactivated
 *     - PHORUM_USER_GROUP_UNAPPROVED: on hold, not yet approved
 *     - PHORUM_USER_GROUP_APPROVED:   active in the group
 *     - PHORUM_USER_GROUP_MODERATOR:  active + member moderator
 *
 * @return array $members
 *     An array containing members for the specified group(s). The array
 *     contains a simple mapping from user_id to group permission. Note
 *     that the permission is only useful in case a single group was
 *     requested (see the function description).
 */
function phorum_db_get_group_members($group_id, $status = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    phorum_db_sanitize_mixed($group_id, 'int');
    if ($status !== NULL) settype($status, 'int');

    if (is_array($group_id)) {
        $group_where = 'AND group_id IN (' . implode(', ', $group_id) . ')';
    } else {
        $group_where = "AND group_id = $group_id";
    }

    if ($status !== NULL) {
        $status_where = "AND xref.status = $status";
    } else {
        $status_where = '';
    }

    // This join is only here so that the list of members comes out sorted.
    // If phorum_db_user_get() sorts results itself, this join can go away.
    $members = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT xref.user_id AS user_id,
                xref.status  AS status
         FROM   {$PHORUM['user_table']} AS users,
                {$PHORUM['user_group_xref_table']} AS xref
         WHERE  users.user_id = xref.user_id
                $group_where
                $status_where
         ORDER  BY username ASC",
        0
    );

    // The records are full rows, but we want a user_id -> status mapping.
    foreach ($members as $id => $member) $members[$id] = $member[1];

    return $members;
}
// }}}

// {{{ Function: phorum_db_add_group()
/**
 * Add a group. This will merely create the group in the database. For
 * changing settings for the group, the function phorum_db_update_group()
 * has to be used.
 *
 * @param string $group_name
 *     The name to assign to the group.
 *
 * @param integer $group_id
 *     The group id to assign to the group or 0 (zero) to assign a new
 *     group id. Assigning a specific group id is and should only be
 *     used by conversion scripts.
 *
 * @return integer
 *     The group id of the newly created group.
 */
function phorum_db_add_group($group_name, $group_id=0)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($group_id, 'int');
    $group_name = phorum_db_interact(DB_RETURN_QUOTED, $group_name);

    $fields = $group_id > 0 ? 'name, group_id' : 'name';
    $values = $group_id > 0 ? "'$group_name', $group_id" : "'$group_name'";

    $group_id = phorum_db_interact(
        DB_RETURN_NEWID,
        "INSERT INTO {$PHORUM['groups_table']}
                ($fields)
         VALUES ($values)",
         NULL,
         DB_MASTERQUERY
    );

    return $group_id;
}
// }}}

// {{{ Function: phorum_db_update_group()
/**
 * Update the settings for a group.
 *
 * @param array $group
 *     The group to update. This is an array, which should contain at least
 *     the field "group_id" to indicate what group to update. Next to that,
 *     one or more of the following fields can be used:
 *     - name:
 *       The name for the group.
 *     - open:
 *       This field determines how new members are added to the group.
 *       Available options are:
 *       - PHORUM_GROUP_CLOSED:
 *         Only the administrator can add users to this group.
 *       - PHORUM_GROUP_OPEN:
 *         The group is open for membership requests by users and
 *         membership is assigned on request immediately.
 *       - PHORUM_GROUP_REQUIRE_APPROVAL:
 *         The group is open for membership requests by users,
 *         but membership has to be approved by an administrator or
 *         a user moderator for the group.
 *     - permissions:
 *       An array containing forum permissions for the group
 *       (key = forum_id and value = permission value).
 *
 * @return boolean
 *     True if all settings were stored successfully. This function will
 *     always return TRUE, so we could do without a return value.
 *     The return value is here for backward compatibility.
 */
function phorum_db_update_group($group)
{
    $PHORUM = $GLOBALS['PHORUM'];

    // Check if the group_id is set.
    if (!isset($group['group_id']) || empty($group['group_id'])) trigger_error(
        'phorum_db_update_group(): $group["group_id"] cannot be empty',
        E_USER_ERROR
    );

    settype($group['group_id'], 'int');
    $group_where = 'group_id = ' . $group['group_id'];

    // See what group fields we have to update.
    $fields = array();
    if (isset($group['name'])) {
        $fields[] = "name = '" .
                    phorum_db_interact(DB_RETURN_QUOTED, $group['name']) ."'";
    }
    if (isset($group['open'])) {
        $fields[] = 'open = ' . (int)$group['open'];
    }

    // Update group fields.
    if (count($fields)) {
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['groups_table']}
             SET ". implode(', ', $fields) . "
             WHERE  $group_where",
            NULL,
            DB_MASTERQUERY
        );
    }

    // Update the group permissions if requested.
    if (isset($group['permissions']))
    {
        // First, all existing forum permissions for the group are deleted.
        phorum_db_interact(
            DB_RETURN_RES,
            "DELETE FROM {$PHORUM['forum_group_xref_table']}
             WHERE  $group_where",
            NULL,
            DB_MASTERQUERY
        );

        // Second, all new permissions are inserted.
        foreach ($group['permissions'] as $forum_id => $permission)
        {
            settype($forum_id, 'int');
            settype($permission, 'int');

            phorum_db_interact(
                DB_RETURN_RES,
                "INSERT INTO {$PHORUM['forum_group_xref_table']}
                        (group_id, permission, forum_id)
                 VALUES ({$group['group_id']}, $permission, $forum_id)",
                NULL,
                DB_MASTERQUERY
            );
        }
    }

    return TRUE;
}
// }}}

// {{{ Function: phorum_db_delete_group()
/**
 * Delete a group.
 *
 * @param integer $group_id
 *     The id of the group to delete.
 */
function phorum_db_delete_group($group_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($group_id, 'int');

    // These are the tables that hold group related data.
    $tables = array (
        $PHORUM['groups_table'],
        $PHORUM['user_group_xref_table'],
        $PHORUM['forum_group_xref_table']
    );

    // Delete the data for the $group_id from all those tables.
    foreach ($tables as $table) {
        phorum_db_interact(
            DB_RETURN_RES,
            "DELETE FROM $table
             WHERE group_id = $group_id",
            NULL,
            DB_MASTERQUERY
        );
    }
}
// }}}

// {{{ Function: phorum_db_user_get_moderators()
/**
 * Retrieve a list of moderators for a particular forum.
 *
 * @param integer $forum_id
 *     The forum for which to retrieve the moderators.
 *
 * @param boolean $exclude_admin
 *     If this parameter has a true value, then the admin users are kept
 *     out of the list.
 *
 * @param boolean $for_email
 *     If this parameter has a true value, then a list of moderators is
 *     created for sending moderator mail messages. Moderators which
 *     have disabled the moderation_email option will be excluded from
 *     the list in this case.
 *
 * @return array
 *     An array of moderators. The keys are user_ids and
 *     the values are email addresses.
 */
function phorum_db_user_get_moderators($forum_id, $exclude_admin=FALSE, $for_email=FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($forum_id, 'int');
    settype($exclude_admin, 'bool');
    settype($for_email, 'bool');

    // Exclude admins from the list, if requested.
    $or_where_admin = $exclude_admin ? '' : 'OR user.admin=1';

    // If we are gathering email addresses for mailing the moderators,
    // then honour the moderation_email setting for the user.
    $where_moderation_mail = $for_email ? 'AND user.moderation_email = 1' : '';

    $moderators = array();

    // Look up moderators which are configured through user permissions.
    $usermods = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT DISTINCT user.user_id AS user_id,
                user.email AS email
         FROM   {$PHORUM['user_table']} AS user
                LEFT JOIN {$PHORUM['user_permissions_table']} AS perm
                ON perm.user_id = user.user_id
         WHERE  ((perm.permission>=".PHORUM_USER_ALLOW_MODERATE_MESSAGES." AND
                  (perm.permission & ".PHORUM_USER_ALLOW_MODERATE_MESSAGES.">0)
                  AND perm.forum_id = $forum_id) $or_where_admin)
                $where_moderation_mail"
    );

    // Add them to the moderator list.
    foreach ($usermods as $mod) $moderators[$mod[0]] = $mod[1];
    unset($usermods);

    // Look up moderators which are configured through group permissions.
    $groupmods = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT DISTINCT user.user_id AS user_id,
                user.email AS email
         FROM   {$PHORUM['user_table']} AS user,
                {$PHORUM['groups_table']} AS groups,
                {$PHORUM['user_group_xref_table']} AS usergroup,
                {$PHORUM['forum_group_xref_table']} AS forumgroup
         WHERE  user.user_id       = usergroup.user_id AND
                usergroup.group_id = groups.group_id AND
                groups.group_id    = forumgroup.group_id AND
                forum_id           = $forum_id AND
                permission & ".PHORUM_USER_ALLOW_MODERATE_MESSAGES." > 0 AND
                usergroup.status  >= ".PHORUM_USER_GROUP_APPROVED."
                $where_moderation_mail"
    );

    // Add them to the moderator list.
    foreach ($groupmods as $mod) $moderators[$mod[0]] = $mod[1];
    unset($groupmods);

    return $moderators;
}
// }}}

// {{{ Function: phorum_db_user_count()
/**
 * Count the total number of users in the Phorum system.
 *
 * @return integer
 *     The number of users.
 */
function phorum_db_user_count()
{
    $PHORUM = $GLOBALS["PHORUM"];

    return phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT count(*)
         FROM   {$PHORUM['user_table']}"
    );
}
// }}}

// {{{ Function: phorum_db_user_get_all()
/**
 * Retrieve all users from the database.
 *
 * This function returns a query resource handle. This handle can be used
 * to retrieve the users from the database one-by-one, by calling the
 * phorum_db_fetch_row() function.
 *
 * @return resource
 *     A query resource handle is returned. This handle can be used
 *     to retrieve the users from the database one-by-one, by
 *     calling the phorum_db_fetch_row() function.
 *
 * @todo This function might be as well replaced with user search and get
 *       functionality from the user API, if search is extended with an
 *       option to return a resource handle.
 */
function phorum_db_user_get_all($offset = 0, $length = 0)
{
    $PHORUM = $GLOBALS["PHORUM"];

    settype($offset, 'int');
    settype($length, 'int');

    $limit = '';
    if ($length > 0) {
        $limit = "LIMIT $offset, $length";
    }

    return phorum_db_interact(
        DB_RETURN_RES,
        "SELECT *
         FROM   {$PHORUM['user_table']}
        $limit"
    );
}
// }}}

// {{{ Function: phorum_db_user_get()
/**
 * Retrieve one or more users.
 *
 * @param mixed $user_id
 *     The user_id or an array of user_ids for which to
 *     retrieve the user data.
 *
 * @param boolean $detailed
 *     If this parameter has a true value, then the user's
 *     permissions and groups are included in the return data.
 *
 * @param boolean $write_server
 *     This value can be set to true to specify that the user should be
 *     retrieved from the master (aka write-server) in case replication
 *     is used
 *
 * @return mixed
 *     If $user_id is a single user_id, then either a single user or NULL
 *     (in case the user_id was not found in the database) is returned.
 *     If $user_id is an array of user_ids, then an array of users is
 *     returned, indexed by user_id. For user_ids that cannot be found,
 *     there will be no array element at all.
 */
function phorum_db_user_get($user_id, $detailed = FALSE, $write_server = FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    phorum_db_sanitize_mixed($user_id, 'int');

    if (is_array($user_id)) {
        if (count($user_id)) {
            $user_where = 'user_id IN ('.implode(', ', $user_id).')';
        } else {
            return array();
        }
    } else {
        $user_where = "user_id = $user_id";
    }

    if($write_server) {
        $flags = DB_MASTERQUERY;
    } else {
        $flags = 0;
    }

    // Retrieve the requested user(s) from the database.
    $users = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT *
         FROM   {$PHORUM['user_table']}
         WHERE  $user_where",
        'user_id',
        $flags
    );

    // No users found?
    if (count($users) == 0) return array();

    // Unpack the settings_data.
    foreach ($users as $id => $user) {
        $users[$id]['settings_data'] = empty($user['settings_data'])
                                     ? array()
                                     : unserialize($user['settings_data']);
    }

    // Retrieve detailed group and permission information for the user(s).
    if ($detailed)
    {
        // Retrieve forum user permissions for the requested users.
        $forum_permissions = phorum_db_interact(
            DB_RETURN_ROWS,
            "SELECT user_id,
                    forum_id,
                    permission
             FROM   {$PHORUM['user_permissions_table']}
             WHERE  $user_where",
            NULL,
            $flags
        );

        // Add forum user permissions to the users.
        foreach ($forum_permissions as $perm) {
            $users[$perm[0]]['forum_permissions'][$perm[1]] = $perm[2];
        }

        // Retrieve forum group permissions and groups for the requested users.
        // "status >= ..." is used to retrieve both approved group users
        // and group moderators.
        $group_permissions = phorum_db_interact(
            DB_RETURN_ROWS,
            "SELECT user_id,
                    {$PHORUM['user_group_xref_table']}.group_id AS group_id,
                    forum_id,
                    permission
             FROM   {$PHORUM['user_group_xref_table']}
                    LEFT JOIN {$PHORUM['forum_group_xref_table']}
                    USING (group_id)
             WHERE  $user_where AND
                    status >= ".PHORUM_USER_GROUP_APPROVED,
            NULL,
            $flags
        );

        // Add groups and forum group permissions to the users.
        foreach ($group_permissions as $perm)
        {
            // Skip permissions for users which are not in our
            // $users array. This should not happen, but it could
            // happen in case some orphin group permissions are
            // lingering in the database.
            if (!isset($users[$perm[0]])) continue;

            // Add the group_id to the user data.
            $users[$perm[0]]['groups'][$perm[1]] = $perm[1];

            // Are we handling a group permissions record?
            if (!empty($perm[2]))
            {
                // Initialize group permissions for the forum_id in the
                // user data.
                if (!isset($users[$perm[0]]['group_permissions'][$perm[2]])) {
                    $users[$perm[0]]['group_permissions'][$perm[2]] = 0;
                }

                // Merge the group permission by logical OR-ing the permission
                // with the permissions that we've got so far for the forum_id
                // in the user data.
                $users[$perm[0]]['group_permissions'][$perm[2]] |= $perm[3];
            }
        }
    }

    // Retrieve custom user profile fields for the requested users.
    $custom_fields = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT *
         FROM   {$PHORUM['user_custom_fields_table']}
         WHERE  $user_where",
        NULL,
        $flags
    );

    // Add custom user profile fields to the users.
    foreach ($custom_fields as $fld)
    {
        // Skip profile fields for users which are not in our
        // $users array. This should not happen, but it could
        // happen in case some orphin custom user fields
        // are lingering in the database.
        if (!isset($users[$fld['user_id']])) continue;

        // Skip unknown custom profile fields.
        if (! isset($PHORUM['PROFILE_FIELDS'][$fld['type']])) continue;

        // Fetch the name for the custom profile field.
        $name = $PHORUM['PROFILE_FIELDS'][$fld['type']]['name'];

        // For "html_disabled" fields, the data is XSS protected by
        // replacing special HTML characters with their HTML entities.
        if ($PHORUM['PROFILE_FIELDS'][$fld['type']]['html_disabled']) {
            $users[$fld['user_id']][$name] = htmlspecialchars($fld['data']);
            continue;
        }

        // Other fields can either contain raw values or serialized
        // arrays. For serialized arrays, the field data is prefixed with
        // a magic "P_SER:" (Phorum serialized) marker.
        if (substr($fld['data'],0,6) == 'P_SER:') {
            $users[$fld['user_id']][$name]=unserialize(substr($fld['data'],6));
            continue;
        }

        // The rest of the fields contain raw field data.
        $users[$fld['user_id']][$name] = $fld['data'];
    }

    if (is_array($user_id)) {
        return $users;
    } else {
        return isset($users[$user_id]) ? $users[$user_id] : NULL;
    }
}
// }}}

// {{{ Function: phorum_db_user_get_fields()
/**
 * Retrieve the data for a couple of user table fields for one or more users.
 *
 * @param mixed $user_id
 *     The user_id or an array of user_ids for which to retrieve
 *     the field data.
 *
 * @param mixed $fields
 *     The field or an array of fields for which to retrieve the data.
 *
 * @return array $users
 *     An array of users (no matter what type of variable $user_id is),
 *     indexed by user_id. For user_ids that cannot be found, there
 *     will be no array element at all.
 */
function phorum_db_user_get_fields($user_id, $fields)
{
    $PHORUM = $GLOBALS['PHORUM'];

    phorum_db_sanitize_mixed($user_id, 'int');

    if (is_array($user_id)) {
        if (count($user_id)) {
            $user_where = 'user_id IN ('.implode(', ', $user_id).')';
        } else {
            return array();
        }
    } else {
        $user_where = "user_id = $user_id";
    }

    if (!is_array($fields)) {
        $fields = array($fields);
    }
    foreach ($fields as $key => $field) {
        if (!phorum_db_validate_field($field)) {
            unset($fields[$key]);
        }
    }

    $users = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT user_id, ".implode(', ', $fields)."
         FROM   {$PHORUM['user_table']}
         WHERE  $user_where",
        'user_id'
    );

    return $users;
}
// }}}

// {{{ Function: phorum_db_user_get_list()
/**
 * Retrieve a list of all users for a given type.
 *
 * @param integer $type
 *     The type of users to retrieve. Available options are:
 *     - 0 = all users
 *     - 1 = all active users
 *     - 2 = all inactive users
 *
 * @return array $users
 *     An array of users, indexed by user_id. The values are arrays
 *     containing the fields "user_id", "username" and "display_name".
 *
 * @todo This function might be as well replaced with user search and get
 *       functionality from the user API,
 */
function phorum_db_user_get_list($type = 0)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($type, 'int');

    $where = '';
    if     ($type == 1) $where = 'WHERE active  = 1';
    elseif ($type == 2) $where = 'WHERE active != 1';

    $users = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT user_id,
                username,
                display_name
         FROM   {$PHORUM['user_table']}
                $where
         ORDER  BY username ASC",
        'user_id'
    );

    return $users;
}
// }}}

// {{{ Function: phorum_db_user_check_login()
/**
 * Check if a user's authentication credentials are correct.
 *
 * @param string $username
 *     The username for the user.
 *
 * @param string $password
 *     The password for the user.
 *
 * @param boolean $temp_password
 *     If this parameter has a true value, the password_temp field will
 *     be checked instead of the password field.
 *
 * @return integer $user_id
 *     The user_id if the password is correct or 0 (zero)
 *     if the password is wrong.
 */
function phorum_db_user_check_login($username, $password, $temp_password=FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($temp_password, 'bool');
    $username = phorum_db_interact(DB_RETURN_QUOTED, $username);
    $password = phorum_db_interact(DB_RETURN_QUOTED, $password);

    $pass_field = $temp_password ? 'password_temp' : 'password';

    $user_id = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT user_id
         FROM   {$PHORUM['user_table']}
         WHERE  username    = '$username' AND
                $pass_field = '$password'"
    );

    return $user_id ? $user_id : 0;
}
// }}}

// {{{ Function: phorum_db_user_search()
/**
 * Search for users, based on a simple search condition,
 * which can be used to search on any field in the user table.
 *
 * The parameters $field, $value and $operator (which are used for defining
 * the search condition) can be arrays or single values. If arrays are used,
 * then all three parameter arrays must contain the same number of elements
 * and the key values in the arrays must be the same.
 *
 * @param mixed $field
 *     The user table field (string) or fields (array) to search on.
 *
 * @param mixed $value
 *     The value (string) or values (array) to search for.
 *
 * @param mixed $operator
 *     The operator (string) or operators (array) to use. Valid operators are
 *     "=", "!=", "<>", "<", ">", ">=" and "<=", "*". The
 *     "*" operator is for executing a "LIKE '%value%'" matching query.
 *
 * @param boolean $return_array
 *     If this parameter has a true value, then an array of all matching
 *     user_ids will be returned. Else, a single user_id will be returned.
 *
 * @param string $type
 *     The type of search to perform. This can be one of:
 *     - AND  match against all fields
 *     - OR   match against any of the fields
 *
 * @param mixed $sort
 *     The field (string) or fields (array) to sort the results by. For
 *     ascending sort, "fieldname" or "+fieldname" can be used. For
 *     descending sort, "-fieldname" can be used. By default, the results
 *     will be sorted by user_id.
 *
 * @param integer $offset
 *     The result page offset starting with 0.
 *
 * @param integer $length
 *     The result page length (nr. of results per page)
 *     or 0 (zero, the default) to return all results.
 *
 * @return mixed
 *     An array of matching user_ids or a single user_id (based on the
 *     $return_array parameter). If no user_ids can be found at all,
 *     then 0 (zero) will be returned.
 */
function phorum_db_user_search($field, $value, $operator='=', $return_array=FALSE, $type='AND', $sort=NULL, $offset=0, $length=0)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($return_array, 'bool');
    settype($offset, 'int');
    settype($length, 'int');

    // Convert all search condition parameters to arrays.
    if (!is_array($field))    $field    = array($field);
    if (!is_array($value))    $value    = array($value);
    if (!is_array($operator)) $operator = array($operator);
    if (!is_array($sort) && $sort!==NULL) $sort = array($sort);

    // Basic check to see if all condition arrays contain the
    // same number of elements.
    if (count($field) != count($value) ||
        count($field) != count($operator)) trigger_error(
        'phorum_db_user_search(): array parameters $field, $value, ' .
        'and $operator do not contain the same number of elements',
        E_USER_ERROR
    );

    $type = strtoupper($type);
    if ($type != 'AND' && $type != 'OR') trigger_error(
        'phorum_db_user_search(): Illegal search type parameter (must ' .
        'be either AND" or "OR")',
        E_USER_ERROR
    );

    $valid_operators = array('=', '<>', '!=', '>', '<', '>=', '<=', '*');

    // Construct the required "WHERE" clause.
    $clauses = array();
    foreach ($field as $key => $name) {
        if (in_array($operator[$key], $valid_operators) &&
            phorum_db_validate_field($name)) {
            $value[$key] = phorum_db_interact(DB_RETURN_QUOTED, $value[$key]);
            if ($operator[$key] == '*') {
                $clauses[] = "$name LIKE '%$value[$key]%'";
            } else {
                $clauses[] = "$name $operator[$key] '$value[$key]'";
            }
        }
    }
    if (!empty($clauses)) {
        $where = 'WHERE ' . implode(" $type ", $clauses);
    } else {
        $where = '';
    }

    // Construct the required "ORDER BY" clause.
    if (!empty($sort)) {
        foreach ($sort as $id => $spec) {
            if (substr($spec, 0, 1) == '+') {
                $fld = substr($spec, 1);
                $dir = 'ASC';
            } elseif (substr($spec, 0, 1) == '-') {
                $fld = substr($spec, 1);
                $dir = 'DESC';
            } else {
                $fld = $spec;
                $dir = 'ASC';
            }

            if (!phorum_db_validate_field($fld)) trigger_error(
                'phorum_db_user_search(): Illegal sort field: ' .
                htmlspecialchars($spec),
                E_USER_ERROR
            );

            $sort[$id] = "$fld $dir";
        }
        $order = 'ORDER BY ' . implode(', ', $sort);
    } else {
        $order = '';
    }

    // Construct the required "LIMIT" clause.
    if (!empty($length)) {
        $limit = "LIMIT $offset, $length";
    } else {
        // If we do not need to return an array, the we can limit the
        // query results to only one record.
        $limit = $return_array ? '' : 'LIMIT 1';
    }

    // Retrieve the matching user_ids from the database.
    $user_ids = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT user_id
         FROM   {$PHORUM['user_table']}
         $where $order $limit",
        0 // keyfield 0 is the user_id
    );

    // No user_ids found at all?
    if (count($user_ids) == 0) return 0;

    // Return an array of user_ids.
    if ($return_array) {
        foreach ($user_ids as $id => $user_id) $user_ids[$id] = $user_id[0];
        return $user_ids;
    }

    // Return a single user_id.
    list ($user_id, $dummy) = each($user_ids);
    return $user_id;
}
// }}}

// {{{ Function: phorum_db_user_add()
/**
 * Add a user.
 *
 * @param array $userdata
 *     An array containing the fields to insert into the user table.
 *     This array should contain at least a "username" field. See
 *     phorum_db_user_save() for some more info on the other data
 *     in this array.
 *
 * @return integer $user_id
 *     The user_id that was assigned to the new user.
 */
function phorum_db_user_add($userdata)
{
    $PHORUM = $GLOBALS['PHORUM'];

    // We need at least the username for the user.
    if (! isset($userdata['username'])) trigger_error(
        'phorum_db_user_add: Missing field in userdata: username',
        E_USER_ERROR
    );
    $username = phorum_db_interact(DB_RETURN_QUOTED, $userdata['username']);

    // We can set the user_id. If not, then we'll create a new user_id.
    if (isset($userdata['user_id'])) {
        $user_id = (int)$userdata['user_id'];
        $fields = 'user_id, username, signature, moderator_data, settings_data';
        $values = "$user_id, '$username', '', '', ''";
    } else {
        $fields = 'username, signature, moderator_data, settings_data';
        $values = "'$username', '', '', ''";
    }

    // Insert a bare bone user in the database.
    $user_id = phorum_db_interact(
        DB_RETURN_NEWID,
        "INSERT INTO {$PHORUM['user_table']}
                ($fields)
         VALUES ($values)",
        NULL,
        DB_MASTERQUERY
    );

    // Set the rest of the data using the phorum_db_user_save() function.
    $userdata['user_id'] = $user_id;
    phorum_db_user_save($userdata);

    return $user_id;
}
// }}}

// {{{ Function: phorum_db_user_save()
/**
 * Update a user.
 *
 * @param array $userdata
 *     An array containing the fields to update in the user table.
 *     The array should contain at least the user_id field to identify
 *     the user for which to update the data. The array can contain two
 *     special fields:
 *     - forum_permissions:
 *       This field can contain an array with forum permissions for the user.
 *       The keys are forum_ids and the values are permission values.
 *     - user_data:
 *       This field can contain an array of key/value pairs which will be
 *       inserted in the database as custom profile fields. The keys are
 *       profile type ids (as defined by $PHORUM["PROFILE_FIELDS"]).
 *
 * @return boolean
 *     True if all settings were stored successfully. This function will
 *     always return TRUE, so we could do without a return value.
 *     The return value is here for backward compatibility.
 */
function phorum_db_user_save($userdata)
{
    $PHORUM = $GLOBALS['PHORUM'];

    // Pull some non user table fields from the userdata. These can be
    // set in case the $userdata parameter that is used is coming from
    // phorum_api_user_get() or phorum_db_user_get().
    if (isset($userdata['permissions'])) {
        unset($userdata['permissions']);
    }
    if (isset($userdata['groups'])) {
        unset($userdata['groups']);
    }
    if (isset($userdata['group_permissions'])) {
        unset($userdata['group_permissions']);
    }

    // Forum permissions and custom profile fields are handled by this
    // function too, but they need to be extracted from the userdata, so
    // they won't be used for updating the standard user fields.
    if (isset($userdata['forum_permissions'])) {
        if (is_array($userdata['forum_permissions'])) {
            $forum_perms = $userdata['forum_permissions'];
        }
        unset($userdata['forum_permissions']);
    }
    if (isset($userdata['user_data'])) {
        $custom_profile_data = $userdata['user_data'];
        unset($userdata['user_data']);
    }

    // The user_id is required for doing the update.
    if (!isset($userdata['user_id'])) trigger_error(
        'phorum_db_user_save(): the user_id field is missing in the ' .
        '$userdata argument',
        E_USER_ERROR
    );
    $user_id = $userdata['user_id'];
    unset($userdata['user_id']);

    // If there are standard user table fields in the userdata then
    // update the user table for the user.
    if (count($userdata))
    {
        // Prepare the user table fields.
        $values = array();
        foreach ($userdata as $key => $value) {
            if (!phorum_db_validate_field($key)) continue;
            if ($key === 'settings_data') {
                if (is_array($value)) {
                    $value = serialize($value);
                } else trigger_error(
                    'Internal error: settings_data field for ' .
                    'phorum_db_user_save() must be an array', E_USER_ERROR
                );
            }
            $value = phorum_db_interact(DB_RETURN_QUOTED, $value);

            if( in_array($key, $PHORUM['string_fields_user'] ) ) {
                $values[] = "$key = '$value'";
            } else {
                $values[] = "$key = $value";
            }
        }

        // Update the fields in the database.
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['user_table']}
             SET    ".implode(', ', $values)."
             WHERE  user_id = $user_id",
            NULL,
            DB_MASTERQUERY
        );
    }

    // Update forum permissions for the user.
    if (isset($forum_perms))
    {
        // Delete all the existing forum permissions.
        phorum_db_interact(
            DB_RETURN_RES,
            "DELETE FROM {$PHORUM['user_permissions_table']}
             WHERE  user_id = $user_id",
            NULL,
            DB_MASTERQUERY
        );

        // Add new forum permissions.
        foreach ($forum_perms as $forum_id => $permission) {
            phorum_db_interact(
                DB_RETURN_RES,
                "INSERT INTO {$PHORUM['user_permissions_table']}
                        (user_id, forum_id, permission)
                 VALUES ($user_id, $forum_id, $permission)",
                NULL,
                DB_MASTERQUERY
            );
        }
    }

    // Update custom user fields for the user.
    if (isset($custom_profile_data))
    {
        // Insert new custom profile fields.
        foreach ($custom_profile_data as $key => $val)
        {
            settype($key, "int");

            // Arrays need to be serialized. The serialized data is prefixed
            // with "P_SER:" as a marker for serialization.
            if (is_array($val)) $val = 'P_SER:'.serialize($val);

            $val = phorum_db_interact(DB_RETURN_QUOTED, $val);

            // Try to insert a new record.
            $res = phorum_db_interact(
                DB_RETURN_RES,
                "INSERT INTO {$PHORUM['user_custom_fields_table']}
                        (user_id, type, data)
                 VALUES ($user_id, $key, '$val')",
                NULL,
                DB_DUPKEYOK | DB_MASTERQUERY
            );
            // If no result was returned, then the query failed. This probably
            // means that we already have a record in the database.
            // So instead of inserting a record, we need to update one here.
            if (!$res) {
              phorum_db_interact(
                  DB_RETURN_RES,
                  "UPDATE {$PHORUM['user_custom_fields_table']}
                   SET    data = '$val'
                   WHERE  user_id = $user_id AND type = $key",
                  NULL,
                  DB_MASTERQUERY
              );
            }
        }
    }

    return TRUE;
}
// }}}

// {{{ Function: phorum_db_user_display_name_updates()
/**
 * Run the updates that are needed after changing the display_name for a user.
 *
 * The display_name for users is stored redundant at several places
 * in the database (this improves the speed of the system, because joins
 * with the user table do not have to be made). This function will update
 * that redundant information to match the active display_name field in
 * the user data.
 *
 * @param array $userdata
 *     A userdata array containing at least the fields "user_id" and
 *     "display_name".
 */
function phorum_db_user_display_name_updates($userdata)
{
    $PHORUM = $GLOBALS['PHORUM'];
    if (!isset($userdata['user_id'])) trigger_error(
        'phorum_db_user_display_name_updates(): Missing user_id field in ' .
        'the $userdata parameter',
        E_USER_ERROR
    );
    if (!isset($userdata['display_name'])) trigger_error(
        'phorum_db_user_display_name_updates(): Missing display_name field ' .
        'in the $userdata parameter',
        E_USER_ERROR
    );

    $author = phorum_db_interact(DB_RETURN_QUOTED, $userdata['display_name']);
    $user_id = (int) $userdata['user_id'];

    // Update forum message authors.
    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['message_table']}
         SET    author = '$author'
         WHERE  user_id = $user_id",
        NULL,
        DB_GLOBALQUERY | DB_MASTERQUERY
    );

    // Update recent forum reply authors.
    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['message_table']}
         SET    recent_author = '$author'
         WHERE  recent_user_id = $user_id",
        NULL,
        DB_GLOBALQUERY | DB_MASTERQUERY
    );

    // Update PM author data.
    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['pm_messages_table']}
         SET    author = '$author'
         WHERE  user_id = $user_id",
        NULL,
        DB_MASTERQUERY
    );

    // Update PM recipient data.
    $res = phorum_db_interact(
        DB_RETURN_RES,
        "SELECT m.pm_message_id AS pm_message_id, meta
         FROM   {$PHORUM['pm_messages_table']} AS m,
                {$PHORUM['pm_xref_table']} AS x
         WHERE  m.pm_message_id = x.pm_message_id AND
                x.user_id = $user_id AND
                special_folder != 'outbox'",
         NULL,
         DB_MASTERQUERY
    );
    while ($row = phorum_db_fetch_row($res, DB_RETURN_ASSOC)) {
        $meta = unserialize($row['meta']);
        $meta['recipients'][$user_id]['display_name'] = $author;
        $meta = phorum_db_interact(DB_RETURN_QUOTED, serialize($meta));
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['pm_messages_table']}
             SET    meta='$meta'
             WHERE  pm_message_id = {$row['pm_message_id']}",
            NULL,
            DB_MASTERQUERY
        );
    }
}
// }}}

// {{{ Function: phorum_db_user_save_groups()
/**
 * Save the group memberships for a user.
 *
 * @param integer $user_id
 *     The user_id for which to save the group memberships.
 *
 * @param array $groups
 *     The group memberships to save. This is an array in which the keys
 *     are group_ids and the values are group statuses.
 *
 * @return boolean
 *     True if all settings were stored successfully. This function will
 *     always return TRUE, so we could do without a return value.
 *     The return value is here for backward compatibility.
 */
function phorum_db_user_save_groups($user_id, $groups)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($user_id, 'int');

    // Delete all existing group memberships.
    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['user_group_xref_table']}
         WHERE  user_id = $user_id",
        NULL,
        DB_MASTERQUERY
    );

    // Insert new group memberships.
    foreach ($groups as $group_id => $group_status) {
        $group_id = (int)$group_id;
        $group_status = (int)$group_status;
        phorum_db_interact(
            DB_RETURN_RES,
            "INSERT INTO {$PHORUM['user_group_xref_table']}
                    (user_id, group_id, status)
             VALUES ($user_id, $group_id, $group_status)",
            NULL,
            DB_MASTERQUERY
        );
    }

    return TRUE;
}
// }}}

// {{{ Function: phorum_db_user_subscribe()
/**
 * Subscribe a user to a forum or thread.
 *
 * Remark: Currently, there is no active support for subscription type
 * PHORUM_SUBSCRIPTION_DIGEST in the Phorum core.
 *
 * @param integer $user_id
 *     The id of the user to create the subscription for.
 *
 * @param integer $thread
 *     The id of the thread to describe to.
 *
 * @param integer $forum_id
 *     The if of the forum to subscribe to.
 *
 * @param integer $type
 *     The type of subscription. Available types are:
 *     - {@link PHORUM_SUBSCRIPTION_NONE}
 *       Explicitly note that the user has no subscription at all.
 *     - {@link PHORUM_SUBSCRIPTION_MESSAGE}
 *       Send a mail message for every new message.
 *     - {@link PHORUM_SUBSCRIPTION_BOOKMARK}
 *       Make new messages visible from the followed threads interface.
 *     - {@link PHORUM_SUBSCRIPTION_DIGEST}
 *       Periodically, send a mail message containing a list of new messages.
 *
 * @return boolean
 *     True if the subscription was stored successfully.
 */
function phorum_db_user_subscribe($user_id, $thread, $forum_id, $type)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($user_id, 'int');
    settype($forum_id, 'int');
    settype($thread, 'int');
    settype($type, 'int');

    // Try to insert a new record.
    $res = phorum_db_interact(
        DB_RETURN_RES,
        "INSERT INTO {$PHORUM['subscribers_table']}
                (user_id, forum_id, thread, sub_type)
         VALUES ($user_id, $forum_id, $thread, $type)",
        NULL,
        DB_DUPKEYOK | DB_MASTERQUERY
    );
    // If no result was returned, then the query failed. This probably
    // means that we already have the record in the database.
    // So instead of inserting a record, we need to update one here.
    if (!$res) {
      phorum_db_interact(
      DB_RETURN_RES,
          "UPDATE {$PHORUM['subscribers_table']}
           SET    sub_type = $type
           WHERE  user_id  = $user_id AND
                  forum_id = $forum_id AND
                  thread   = $thread",
      NULL,
      DB_MASTERQUERY
      );
    }

    return TRUE;
}
// }}}

// {{{ Function: phorum_db_user_unsubscribe()
/**
 * Unsubscribe a user from a forum/thread.
 *
 * @param integer $user_id
 *     The id of the user to remove a suscription for.
 *
 * @param integer $thread
 *     The id of the thread to unsubscribe from.
 *
 * @param integer $forum_id
 *     The id of the forum to unsubscribe from (or 0 (zero)
 *     to simply unsubscribe by the thread id alone).
 *
 * @return boolean
 *     True if the subscription was stored successfully.
 */
function phorum_db_user_unsubscribe($user_id, $thread, $forum_id=0)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($user_id, 'int');
    settype($forum_id, 'int');
    settype($thread, 'int');

    $forum_where = $forum_id ? "AND forum_id = $forum_id" : '';

    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['subscribers_table']}
         WHERE  user_id = $user_id AND
                thread  = $thread
                $forum_where",
        NULL,
        DB_MASTERQUERY
    );

    return TRUE;
}
// }}}

// {{{ Function: phorum_db_user_increment_posts()
/**
 * Increment the posts counter for a user.
 *
 * @param integer $user_id
 *     The user_id for which to increment the posts counter.
 */
function phorum_db_user_increment_posts($user_id)
{
    settype($user_id, 'int');

    if (!empty($user_id)) {
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$GLOBALS['PHORUM']['user_table']}
             SET    posts = posts + 1
             WHERE  user_id = $user_id",
            NULL,
            DB_MASTERQUERY
        );
    }
}
// }}}

// {{{ Function: phorum_db_user_get_groups()
/**
 * Retrieve a list of group memberships and their statuses for a user.
 *
 * @param integer $user_id
 *     The user id for which to retrieve the groups.
 *
 * @return array
 *     An array of groups for the user. The keys are group_ids and the
 *     values are the membership statuses.
 */
function phorum_db_user_get_groups($user_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($user_id, 'int');

    // Retrieve the groups for the user_id from the database.
    $groups = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT group_id,
                status
         FROM   {$PHORUM['user_group_xref_table']}
         WHERE  user_id = $user_id
         ORDER  BY status DESC",
        0 // keyfield 0 is the group_id
    );

    // The records are full rows, but we want a group_id -> status mapping.
    foreach ($groups as $id => $group) $groups[$id] = $group[1];

    return $groups;
}
// }}}

// {{{ Function: phorum_db_user_get_unapproved()
/**
 * Retrieve the users that await signup approval.
 *
 * @return $users
 *     An array or users, indexed by user_id, that await approval.
 *     The elements of the array are arrays containing the fields:
 *     user_id, username and email.
 *
 * @todo This function might be as well replaced with user search and get
 *       functionality from the user API.
 */
function phorum_db_user_get_unapproved()
{
    $PHORUM = $GLOBALS['PHORUM'];

    $users = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT user_id,
                username,
                email
         FROM   {$PHORUM['user_table']}
         WHERE  active in (".PHORUM_USER_PENDING_BOTH.",
                           ".PHORUM_USER_PENDING_MOD.")
         ORDER  BY username",
        'user_id'
    );

    return $users;
}
// }}}

// {{{ Function: phorum_db_user_delete
/**
 * Delete a user completely. Messages that were posted by the user in the
 * forums, will be changed into anonymous messages (user_id = 0). If the
 * constant PHORUM_DELETE_CHANGE_AUTHOR is set to a true value, then the
 * author name of all postings will be set to {LANG->AnonymousUser}. If
 * it is set to a false value, then the original author name will be kept.
 *
 * @param integer $user_id
 *     The id of the user to delete.
 *
 * @return boolean
 *     True if the user was deleted successfully.
 */
function phorum_db_user_delete($user_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($user_id, 'int');

    // Retrieve a list of private mesage xrefs for this user. After we delete
    // the pm xrefs for this user in the code afterwards, we might have
    // created orphin PM messages (messages with no xrefs linked to them),
    // so we'll have to check for that later on.
    $pmxrefs = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT pm_message_id
         FROM   {$PHORUM['pm_xref_table']}
         WHERE  user_id = $user_id",
        NULL,
        DB_MASTERQUERY
    );

    // These are tables that hold user related data.
    $tables = array (
        $PHORUM['user_table'],
        $PHORUM['user_permissions_table'],
        $PHORUM['user_newflags_table'],
        $PHORUM['subscribers_table'],
        $PHORUM['user_group_xref_table'],
        $PHORUM['pm_buddies_table'],
        $PHORUM['pm_folders_table'],
        $PHORUM['pm_xref_table'],
        $PHORUM['user_custom_fields_table']
    );

    // Delete the data for the $user_id from all those tables.
    foreach ($tables as $table) {
        phorum_db_interact(
            DB_RETURN_RES,
            "DELETE FROM $table
             WHERE user_id = $user_id",
            NULL,
            DB_GLOBALQUERY | DB_MASTERQUERY
        );
    }

    // See if we created any orphin private messages. We do this in
    // a loop using the standard phorum_db_pm_update_message_info()
    // function and not a single SQL statement with something like
    // pm_message_id IN (...) in it, because MySQL won't use an index
    // for that, making the full lookup very slow on large PM tables.
    foreach ($pmxrefs as $row) {
        phorum_db_pm_update_message_info($row[0]);
    }

    // Change the forum postings into anonymous postings.
    // If PHORUM_DELETE_CHANGE_AUTHOR is set, then the author field is
    // updated to {LANG->AnonymousUser}.
    $author = 'author';

    if (defined('PHORUM_DELETE_CHANGE_AUTHOR') && PHORUM_DELETE_CHANGE_AUTHOR) {
        $anonymous = $PHORUM['DATA']['LANG']['AnonymousUser'];
        $author = "'".phorum_db_interact(DB_RETURN_QUOTED, $anonymous)."'";
    }

    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['message_table']}
         SET    user_id = 0,
                email   = '',
                author  = $author
         WHERE  user_id = $user_id",
        NULL,
        DB_GLOBALQUERY | DB_MASTERQUERY
    );

    return TRUE;
}
// }}}

// {{{ Function: phorum_db_get_file_list()
/**
 * Retrieve a list of files from the database.
 *
 * @param string $link_type
 *     The type of link to retrieve from the database. Normally this is one
 *     of the Phorum built-in link types, but it can also be a custom
 *     link type (e.g. if a module uses the file storage on its own).
 *     This parameter can be NULL to retrieve any link type.
 *
 * @param integer $user_id
 *     The user_id to retrieve files for or NULL to retrieve files for
 *     any user_id.
 *
 * @param integer $message_id
 *     The message_id to retrieve files for or NULL to retrieve files for
 *     any message_id.
 *
 * @return array
 *     An array of files, indexed by file_id.
 *     The array elements are arrays containing the fields:
 *     file_id, filename, filesize and add_datetime.
 */
function phorum_db_get_file_list($link_type = NULL, $user_id = NULL, $message_id = NULL)
{
    $PHORUM = $GLOBALS["PHORUM"];

    $where = '';
    $clauses = array();
    if ($link_type !== NULL) {
        $qtype = phorum_db_interact(DB_RETURN_QUOTED, $link_type);
        $clauses[] = "link = '$qtype'";
    }
    if ($user_id !== NULL) {
        $clauses[] = 'user_id = ' . (int) $user_id;
    }
    if ($message_id !== NULL) {
        $clauses[] = 'message_id = ' . (int) $message_id;
    }
    if (count($clauses)) {
        $where = 'WHERE ' . implode(' AND ', $clauses);
    }

    return phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT file_id,
                filename,
                filesize,
                add_datetime
         FROM   {$PHORUM['files_table']}
         $where
         ORDER  BY file_id",
        'file_id'
    );
}
// }}}

// {{{ Function: phorum_db_get_user_file_list()
/**
 * Retrieve a list of personal files for a user.
 *
 * @param integer $user_id
 *     The user id for which to retrieve the file list.
 *
 * @return array
 *     An array of personal user files, indexed by file_id.
 *     The array elements are arrays containing the fields:
 *     file_id, filename, filesize and add_datetime.
 */
function phorum_db_get_user_file_list($user_id)
{
    return phorum_db_get_file_list(PHORUM_LINK_USER, $user_id, 0);
}
// }}}

// {{{ Function: phorum_db_get_message_file_list()
/**
 * Retrieve a list of files for a message (a.k.a. attachments).
 *
 * @param integer $message_id
 *     The message id for which to retrieve the file list.
 *
 * @return array
 *     An array of message files, indexed by file_id.
 *     The array elements are arrays containing the fields:
 *     file_id, filename, filesize and add_datetime.
 */
function phorum_db_get_message_file_list($message_id)
{
    return phorum_db_get_file_list(PHORUM_LINK_MESSAGE, NULL, $message_id);
}
// }}}

// {{{ Function: phorum_db_file_get()
/**
 * Retrieve a file.
 *
 * @param integer $file_id
 *     The file id to retrieve from the database.
 *
 * @param boolean $include_file_data
 *     If this parameter is set to a false value (TRUE is the default),
 *     the file data will not be included in the return data.
 *
 * @return array
 *     An array, containing the data for all file table fields.
 *     If the file id cannot be found in the database, an empty
 *     array will be returned instead.
 */
function phorum_db_file_get($file_id, $include_file_data = TRUE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($file_id, 'int');

    $fields = "file_id, user_id, filename, filesize, " .
              "add_datetime, message_id, link";
    if ($include_file_data) $fields .= ",file_data";

    // Select the file from the database.
    $files = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT $fields
         FROM   {$PHORUM['files_table']}
         WHERE  file_id = $file_id"
    );

    if (count($files) == 0) {
        return array();
    } else {
        return $files[0];
    }
}
// }}}

// {{{ Function: phorum_db_file_save()
/**
 * Add or updates a file.
 *
 * @todo Update docs, because it now is based on separate parameters,
 *       while the function itself requires an array now.
 *
 * @param integer $user_id
 *     The id of the user for which to add the file.
 *     If this file is linked to a message by an anonymous
 *     user, then this value can be 0 (zero).
 *
 * @param string $filename
 *     The name of the file.
 *
 * @param integer $filesize
 *     The size of the file in bytes.
 *
 * @param string $file_data
 *     The file contents. This should be data which is safe to store in a
 *     TEXT field in the database. The calling application has to take
 *     care of this. The database layer will simply store and retrieve
 *     the file data as provided by the caller.
 *
 * @param integer $message_id
 *     The message_id to link the file to. If this file is not linked to
 *     a posted message (the link type PHORUM_LINK_MESSAGE) then this value
 *     can be 0 (zero).
 *
 * @param string $link
 *     A file can be linked to a number of different types of objects.
 *     The available link types are:
 *     - PHORUM_LINK_USER:
 *       The file is linked to a user. This means that the file is
 *       available from within the files section in the user's Control Center.
 *     - PHORUM_LINK_MESSAGE:
 *       The file is linked to a message. This means that the file is
 *       available as an attachment on a posted forum message.
 *     - PHORUM_LINK_EDITOR
 *       The file is linked to the editor. This means that the file was
 *       uploaded as part of editing a forum message. This message was
 *       not yet posted. As soon as the message is posted for final, the
 *       link type for the message will be updated to
 *       PHORUM_LINK_MESSAGE.
 *       Note: these are the official link types. Calling functions are
 *       allowed to provide different custom link types if they need to.
 *
 * @param integer $file_id
 *     If the $file_id is set, then this will be used for updating the
 *     existing file data for the given $file_id.
 *
 * @return integer
 *     The file_id that was assigned to the new file or the file_id of
 *     the existing file if the $file_id parameter was used.
 */
function phorum_db_file_save($file)
{
    $PHORUM = $GLOBALS['PHORUM'];

    // If a link type is not provided, we'll guess for the type of link.
    // This is done to provide some backward compatibility.
    if ($file["link"] === NULL) {
        if     ($message_id) $file["link"] = PHORUM_LINK_MESSAGE;
        elseif ($user_id)    $file["link"] = PHORUM_LINK_USER;
        else trigger_error(
            'phorum_db_file_save(): Missing link field in the $file parameter',
            E_USER_ERROR
        );
    }

    $user_id    = (int)$file["user_id"];
    $message_id = (int)$file["message_id"];
    $filesize   = (int)$file["filesize"];
    $file_id    = !isset($file["file_id"]) || $file["file_id"] === NULL
                ? NULL : (int)$file["file_id"];
    $link       = phorum_db_interact(DB_RETURN_QUOTED, $file["link"]);
    $filename   = phorum_db_interact(DB_RETURN_QUOTED, $file["filename"]);
    $file_data  = phorum_db_interact(DB_RETURN_QUOTED, $file["file_data"]);
    $datetime   = empty($file['add_datetime']) 
                ? time() : (int)$file['add_datetime'];

    // Create a new file record.
    if ($file_id === NULL) {
        $file_id = phorum_db_interact(
            DB_RETURN_NEWID,
            "INSERT INTO {$PHORUM['files_table']}
                    (user_id, message_id, link,
                     filename, filesize, file_data, add_datetime)
             VALUES ($user_id, $message_id, '$link',
                     '$filename', $filesize, '$file_data', $datetime)",
            NULL,
            DB_MASTERQUERY
        );
    }
    // Update an existing file record.
    else {
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['files_table']}
             SET    user_id      = $user_id,
                    message_id   = $message_id,
                    link         = '$link',
                    filename     = '$filename',
                    filesize     = $filesize,
                    file_data    = '$file_data'
             WHERE  file_id      = $file_id",
            NULL,
            DB_MASTERQUERY
        );
    }

    return $file_id;
}
// }}}

// {{{ Function: phorum_db_file_delete()
/**
 * Delete a file.
 *
 * @param integer $file_id
 *     The id of the file to delete.
 *
 * @return boolean
 *     True if the file was deleted successfully. This function will
 *     always return TRUE, so we could do without a return value.
 *     The return value is here for backward compatibility.
 */
function phorum_db_file_delete($file_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($file_id, 'int');

    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['files_table']}
         WHERE  file_id = $file_id",
        NULL,
        DB_MASTERQUERY
    );

    return TRUE;
}
// }}}

// {{{ Function: phorum_db_file_link()
/**
 * Update the message to which a file is linked and/or the link type.
 *
 * @param integer $file_id
 *     The id of the file to update.
 *
 * @param integer $message_id
 *     The id of the message to link the file to.
 *
 * @param string $link
 *     A file can be linked to a number of different types of objects.
 *     See phorum_db_file_save() for the possible link types.
 *
 * @return boolean
 *     True if the file link was updated successfully. This function will
 *     always return TRUE, so we could do without a return value.
 *     The return value is here for backward compatibility.
 */
function phorum_db_file_link($file_id, $message_id, $link = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($file_id, 'int');
    settype($message_id, 'int');

    $link = $link === NULL
          ? PHORUM_LINK_MESSAGE
          : phorum_db_interact(DB_RETURN_QUOTED, $link);

    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['files_table']}
         SET    message_id = $message_id,
                link       = '$link'
         WHERE  file_id    = $file_id",
        NULL,
        DB_MASTERQUERY
    );

    return TRUE;
}
// }}}

// {{{ Function: phorum_db_get_user_filesize_total()
/**
 * Retrieve the total size for all personal files for a user.
 *
 * @param integer $user_id
 *     The user to compute the total size for.
 *
 * @return integer
 *     The total size in bytes.
 */
function phorum_db_get_user_filesize_total($user_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($user_id, 'int');

    $size = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT SUM(filesize)
         FROM   {$PHORUM['files_table']}
         WHERE  user_id    = $user_id AND
                message_id = 0 AND
                link       = '".PHORUM_LINK_USER."'"
    );

    return $size;
}
// }}}

// {{{ Function: phorum_db_list_stale_files()
/**
 * Retrieve a list of stale files from the database.
 *
 * Stale files are files that are not linked to anything anymore.'
 * These can for example be caused by users that are writing a message
 * with attachments, but never post it.
 *
 * @return array
 *     An array of stale Phorum files, indexed by file_id. Every item in
 *     this array is an array on its own, containing the fields:
 *     - file_id: the file id of the stale file
 *     - filename: the name of the stale file
 *     - filesize: the size of the file
 *     - add_datetime: the time at which the file was added
 *     - reason: the reason why it's a stale file
 */
function phorum_db_list_stale_files()
{
    $PHORUM = $GLOBALS['PHORUM'];

    // Select orphin editor files.
    // These are files that are linked to the editor and that were added
    // a while ago. These are from posts that were abandoned before posting.
    $stale_files = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT file_id,
                filename,
                filesize,
                add_datetime,
                'Attachments, left behind by unposted messages' AS reason
         FROM   {$PHORUM['files_table']}
         WHERE  link = '".PHORUM_LINK_EDITOR."'
                AND
                add_datetime < ". (time()-PHORUM_MAX_EDIT_TIME),
        'file_id',
        DB_GLOBALQUERY
    );

    return $stale_files;
}
// }}}

// {{{ Function: phorum_db_newflag_allread()
/**
 * Mark all messages for a forum read for the active Phorum user.
 *
 * @param integer $forum_id
 *     The forum to mark read or 0 (zero) to mark the current forum read.
 */
function phorum_db_newflag_allread($forum_id=0)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if (empty($forum_id)) $forum_id = $PHORUM['forum_id'];
    settype($forum_id, 'int');

    // Delete all the existing newflags for this user for this forum.
    phorum_db_newflag_delete(0, $forum_id);

    // Retrieve the maximum message_id in this forum.
    $max_id = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT max(message_id)
         FROM   {$PHORUM['message_table']}
         WHERE  forum_id = $forum_id"
    );

    // Set this message_id as the min-id for the forum.
    if ($max_id) {
        phorum_db_newflag_add_read(array(
            0 => array(
              'id'    => $max_id,
              'forum' => $forum_id
            )
        ));
    }
}
// }}}

// {{{ Function: phorum_db_newflag_get_flags()
/**
 * Retrieve the read messages for a forum for the active Phorum user.
 *
 * @param integer $forum_id
 *     The forum to retrieve the read messages for or 0 (zero) to
 *     retrieve them for the current forum.
 *
 * @return array
 *     An array containing the message_ids that have been read for the
 *     forum (key and value are both the message_id). Also an element
 *     for the key "min_id", which holds the minimum read message_id. All
 *     message_ids lower than the min_id are also considered to be read.
 */
function phorum_db_newflag_get_flags($forum_id=NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if ($forum_id === NULL) $forum_id = $PHORUM['forum_id'];
    settype($forum_id, 'int');

    // Initialize the read messages array.
    $read_msgs = array('min_id' => 0);

    // Select the read messages from the newflags table.
    $newflags = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT message_id
         FROM   {$PHORUM['user_newflags_table']}
         WHERE  user_id  = {$PHORUM['user']['user_id']} AND
                forum_id = $forum_id
         ORDER  BY message_id ASC"
    );

    // Add the newflags to the $read_msgs.
    // The first newflags element also determines the min_id.
    foreach ($newflags as $index => $newflag) {
        if ($index == 0) $read_msgs['min_id'] = $newflag[0];
        $read_msgs[$newflag[0]] = $newflag[0];
    }

    return $read_msgs;
}
// }}}

// {{{ Function: phorum_db_newflag_check()
/**
 * Checks if there are new messages in the forums given in forum_ids
 *
 * @param array $forum_ids
 *     The forums to check for new messages
 *
 * @return array
 *     An array containing forum_ids as the key and a boolean for
 *     the values.
 */
function phorum_db_newflag_check($forum_ids)
{
    $PHORUM = $GLOBALS['PHORUM'];

    phorum_db_sanitize_mixed($forum_ids, 'int');

    $sql = "select forum_id, min(message_id) as message_id
            from {$PHORUM['user_newflags_table']}
            where user_id=".$PHORUM["user"]["user_id"]."
            group by forum_id";

    $list = phorum_db_interact(DB_RETURN_ASSOCS, $sql, "forum_id");

    $sql = "select forum_id, count(*) as count
            from {$PHORUM['user_newflags_table']}
            where user_id=".$PHORUM["user"]["user_id"]."
            group by forum_id";

    $counts = phorum_db_interact(DB_RETURN_ASSOCS, $sql, "forum_id");

    $new_checks = array();

    foreach($forum_ids as $forum_id){

        if(empty($list[$forum_id]) || empty($counts[$forum_id])){

            $new_checks[$forum_id] = FALSE;

        } else {

            // check for new messages
            $sql = "select count(*) as count from {$PHORUM['message_table']}
                    where forum_id=".$forum_id." and
                    message_id>=".$list[$forum_id]["message_id"]." and
                    status=".PHORUM_STATUS_APPROVED." and
                    moved=0";

            list($count) = phorum_db_interact(DB_RETURN_ROW, $sql);

            $new_checks[$forum_id] = ($count > $counts[$forum_id]["count"]);

        }
    }

    return $new_checks;

}
// }}}

// {{{ Function: phorum_db_newflag_count()
/**
 * Gets a count of new messages and threads for the forum ids given
 *
 * @param array $forum_ids
 *     The forums to check for new messages.
 *
 * @return array
 *     An array containing forum_ids as the key and a two element array
 *     for each entry with messages and threads counts.
 */
function phorum_db_newflag_count($forum_ids)
{
    $PHORUM = $GLOBALS['PHORUM'];

    phorum_db_sanitize_mixed($forum_ids, 'int');

    // get a list of forum_ids and minimum message ids from the newflags table
    $sql = "select forum_id, min(message_id) as message_id
            from {$PHORUM['user_newflags_table']}
            where user_id=".$PHORUM["user"]["user_id"]."
            group by forum_id";

    $list = phorum_db_interact(DB_RETURN_ASSOCS, $sql, "forum_id");

    // get the total number of messages the user has read in each forum
    $sql = "select {$PHORUM['user_newflags_table']}.forum_id, count(*) as count
            from {$PHORUM['user_newflags_table']}
            inner join {$PHORUM['message_table']} using (message_id, forum_id)
            where {$PHORUM['user_newflags_table']}.user_id=".$PHORUM["user"]["user_id"]." and
            status=".PHORUM_STATUS_APPROVED."
            group by forum_id";

    $message_counts = phorum_db_interact(DB_RETURN_ASSOCS, $sql, "forum_id");


    // get the number of threads the user has read in each forum
    $sql = "select {$PHORUM['user_newflags_table']}.forum_id, count(*) as count
            from {$PHORUM['user_newflags_table']}
            inner join {$PHORUM['message_table']} using (message_id, forum_id)
            where {$PHORUM['user_newflags_table']}.user_id=".$PHORUM["user"]["user_id"]." and
            parent_id=0 and
            status=".PHORUM_STATUS_APPROVED."
            group by forum_id";

    $thread_counts = phorum_db_interact(DB_RETURN_ASSOCS, $sql, "forum_id");

    $new_checks = array();

    foreach($forum_ids as $forum_id){

        if(empty($list[$forum_id])){

            $new_checks[$forum_id] = array("messages"=>0, "threads"=>0);

        } else {

            if(empty($message_counts[$forum_id])){

                $new_checks[$forum_id]["messages"] = 0;

            } else {

                // check for new messages
                $sql = "select count(*) as count from {$PHORUM['message_table']}
                        where forum_id=".$forum_id." and
                        message_id>=".$list[$forum_id]["message_id"]." and
                        status=".PHORUM_STATUS_APPROVED." and
                        moved=0";

                list($count) = phorum_db_interact(DB_RETURN_ROW, $sql);

                $new_checks[$forum_id]["messages"] = max(0, $count - $message_counts[$forum_id]["count"]);
            }

            // no this is not a typo
            // we need to calculate their thread count if they have ANY read messages
            // in the table.  the only way to do that is to see if message count was set
            if(empty($message_counts[$forum_id])){

                $new_checks[$forum_id]["threads"] = 0;

            } else {

                // check for new threads
                $sql = "select count(*) as count from {$PHORUM['message_table']}
                        where forum_id=".$forum_id." and
                        message_id>=".$list[$forum_id]["message_id"]." and
                        parent_id=0 and
                        status=".PHORUM_STATUS_APPROVED." and
                        moved=0";

                list($count) = phorum_db_interact(DB_RETURN_ROW, $sql);

                if(isset($thread_counts[$forum_id]["count"])){
                    $new_checks[$forum_id]["threads"] = max(0, $count - $thread_counts[$forum_id]["count"]);
                } else {
                    $new_checks[$forum_id]["threads"] = max(0, $count);
                }
            }
        }
    }

    return $new_checks;

}
// }}}

// {{{ Function: phorum_db_newflag_get_unread_count()
/**
 * Retrieve the number of new threads and messages for a forum for the
 * active Phorum user.
 *
 * @param integer $forum_id
 *     The forum to retrieve the new counts for or
 *     0 (zero) to retrieve them for the current forum.
 *
 * @return array
 *     An array containing two elements. The first element is the number
 *     of new messages. The second one is the number of new threads.
 */
function phorum_db_newflag_get_unread_count($forum_id=NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if ($forum_id === NULL) $forum_id = $PHORUM['forum_id'];
    settype($forum_id, 'int');

    // Retrieve the minimum message_id from newflags for the forum.
    $min_message_id = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT  min(message_id)
         FROM    {$PHORUM['user_newflags_table']}
         WHERE   user_id  = {$PHORUM['user']['user_id']} AND
                 forum_id = {$forum_id}"
    );

    // No result found? Then we know that the user never read a
    // message from this forum. We won't count the new messages
    // in that case. Return an empty result.
    if (!$min_message_id) return array(0,0);

    // Retrieve the unread thread count.
    $new_threads = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT count(*)
         FROM {$PHORUM['message_table']} AS m
              LEFT JOIN {$PHORUM['user_newflags_table']} AS n ON
               m.message_id = n.message_id AND
               n.user_id    = {$PHORUM['user']['user_id']}
         WHERE m.forum_id   = {$forum_id} AND
               m.message_id > $min_message_id AND
               n.message_id IS NULL AND
               m.parent_id  = 0 AND
               m.status     = ".PHORUM_STATUS_APPROVED." AND
               m.thread     = m.message_id"
    );

    // Retrieve the unread message count.
    $new_messages = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT count(*)
         FROM   {$PHORUM['message_table']} AS m
                LEFT JOIN {$PHORUM['user_newflags_table']} AS n ON
                m.message_id = n.message_id AND
                m.forum_id   = n.forum_id AND
                n.user_id    = {$PHORUM['user']['user_id']}
         WHERE  m.forum_id   = {$forum_id} AND
                m.message_id > $min_message_id AND
                n.message_id IS NULL AND
                m.status = ".PHORUM_STATUS_APPROVED
    );

    $counts = array(
        $new_messages,
        $new_threads
    );

    return $counts;
}
// }}}

// {{{ Function: phorum_db_newflag_add_read()
/**
 * Mark a message as read for the active Phorum user.
 *
 * @param mixed $message_ids
 *     The message_id of the message to mark read in the active forum or an
 *     array description of messages to mark read. Elements in this array
 *     can be:
 *     - Simple message_id values, to mark messages read in the active forum.
 *     - An array containing two fields: "forum" containing a forum_id and
 *       "id" containing a message_id. This notation can be used to mark
 *       messages read in other forums than te active one.
 */
function phorum_db_newflag_add_read($message_ids)
{
    $PHORUM = $GLOBALS['PHORUM'];

    // Find the number of newflags for the user
    $num_newflags = phorum_db_newflag_get_count();

    if (!is_array($message_ids)) {
        $message_ids = array(0 => $message_ids);
    }

    // Delete newflags which would exceed the maximum number of
    // newflags that are allowed in the database per user.
    $num_end = $num_newflags + count($message_ids);
    if ($num_end > PHORUM_MAX_READ_COUNT_PER_FORUM) {
        phorum_db_newflag_delete($num_end - PHORUM_MAX_READ_COUNT_PER_FORUM);
    }

    // Insert newflags.
    foreach ($message_ids as $id => $data)
    {
        if (is_array($data)) {
            $user_id    = $PHORUM['user']['user_id'];
            $forum_id   = (int)$data['forum'];
            $message_id = (int)$data['id'];
        } else {
            $user_id    = $PHORUM['user']['user_id'];
            $forum_id   = $PHORUM['forum_id'];
            $message_id = (int)$data;
        }

        // We ignore duplicate record errors here.
        phorum_db_interact(
            DB_RETURN_RES,
            "INSERT INTO {$PHORUM['user_newflags_table']}
                    (user_id, forum_id, message_id)
             VALUES ($user_id, $forum_id, $message_id)",
            NULL,
            DB_DUPKEYOK | DB_MASTERQUERY
        );
    }
}
// }}}

// {{{ Function: phorum_db_newflag_get_count()
/**
 * Retrieve the total number of newflags for a forum for the active
 * Phorum user.
 *
 * @param integer $forum_id
 *     The forum to retrieve the count for or 0 (zero) to retrieve it
 *     for the current forum.
 *
 * @return integer
 *     The total number of newflags.
 */
function phorum_db_newflag_get_count($forum_id=0)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if (empty($forum_id)) $forum_id = $PHORUM['forum_id'];
    settype($forum_id, 'int');

    $count = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT count(*)
         FROM   {$PHORUM['user_newflags_table']}
         WHERE  user_id  = {$PHORUM['user']['user_id']} AND
                forum_id = {$forum_id}"
    );

    return $count;
}
// }}}

// {{{ Function: phorum_db_newflag_delete()
/**
* Remove newflags for a forum for the active Phorum user.
*
* @param integer $numdelete
*     The number of newflags to delete or 0 (zero) to delete them all.
*
* @param integer $forum_id
*     The forum for which to delete the newflags or 0 (zero) to
*     delete them for the current forum.
*/
function phorum_db_newflag_delete($numdelete=0,$forum_id=0)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if (empty($forum_id)) $forum_id = $PHORUM['forum_id'];
    settype($numdelete, 'int');
    settype($forum_id, 'int');

    $limit = $numdelete > 0 ? "ORDER BY message_id ASC LIMIT $numdelete" : '';

    // Delete the provided amount of newflags.
    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['user_newflags_table']}
         WHERE  user_id  = {$PHORUM['user']['user_id']} AND
                forum_id = {$forum_id}
         $limit",
        NULL,
        DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_newflag_update_forum()
/**
 * Update the forum_id for the newflags. The newsflags are updated by setting
 * their forum_ids to the forum_ids of the referenced message table records.
 *
 * @param array $message_ids
 *     An array of message_ids which should be updated.
 */
function phorum_db_newflag_update_forum($message_ids)
{
    phorum_db_sanitize_mixed($message_ids, 'int');
    $ids_str = implode(', ', $message_ids);

    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE IGNORE {$GLOBALS['PHORUM']['user_newflags_table']} AS flags,
                       {$GLOBALS['PHORUM']['message_table']} AS msg
         SET    flags.forum_id   = msg.forum_id
         WHERE  flags.message_id = msg.message_id AND
                flags.message_id IN ($ids_str)",
        NULL,
        DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_user_list_subscribers()
/**
 * Retrieve the email addresses of the users that are subscribed to a
 * forum/thread, grouped by the preferred language for these users.
 *
 * @param integer $forum_id
 *     The forum_id to check on.
 *
 * @param integer $thread
 *     The thread id to check on.
 *
 * @param integer $type
 *     The type of subscription to check on. See the documentation for the
 *     function {@link phorum_db_user_subscribe()} for available
 *     subscription types.
 *
 * @param boolean $ignore_active_user
 *     If this parameter is set to FALSE (it is TRUE by default), then the
 *     active Phorum user will be excluded from the list.
 *
 * @return array $addresses
 *     An array containing the subscriber email addresses. The keys in the
 *     result array are language names. The values are arrays. Each array
 *     contains a list of email addresses of users which are using the
 *     language from the key field.
 */
function phorum_db_user_list_subscribers($forum_id, $thread, $type, $ignore_active_user=TRUE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($forum_id, 'int');
    settype($thread, 'int');
    settype($type, 'int');
    settype($ignore_active_user, 'bool');

    $userignore = '';
    if ($ignore_active_user && $PHORUM['DATA']['LOGGEDIN'])
       $userignore = "AND u.user_id != {$PHORUM['user']['user_id']}";

    // Select all subscriptions for the requested thread.
    // This query also checks on s.thread = 0. This is for
    // subscriptions that are set on a full forum. This is a
    // feature which never really made it to the core (because the
    // posting performance would get too low with a lot of forum
    // subscribers, but we'll leave it in the query here, in case
    // somebody wants to write a module for handling this functionality.
    $users = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT DISTINCT(u.email) AS email,
                user_language
         FROM   {$PHORUM['subscribers_table']} AS s,
                {$PHORUM['user_table']} AS u
         WHERE  s.forum_id = $forum_id AND
                (s.thread = $thread or s.thread = 0) AND
                s.sub_type = $type AND
                u.user_id = s.user_id
                $userignore"
    );

    $addresses = array();

    // Add the subscriptions to the addresses array.
    foreach ($users as $user)
    {
        // Determine what language to use for this user.
        $lang = empty($user[1]) ? $PHORUM['language'] : $user[1];

        $addresses[$lang][] = $user[0];
    }

    return $addresses;
}
// }}}

// {{{ Function: phorum_db_user_list_subscriptions()
/**
 * Retrieve a list of threads to which a user is subscribed. The list can be
 * limited to those threads which did receive contributions recently.
 *
 * @param integer $user_id
 *     The id of the user for which to retrieve the subscribed threads.
 *
 * @param integer $days
 *     If set to 0 (zero), then all subscriptions will be returned. If set to
 *     a different value, then only threads which have received contributions
 *     within the last $days days will be included in the list.
 *
 * @param integer $forum_ids
 *     If this parameter is NULL, then subscriptions from all forums will
 *     be included. This parameter can also be an array of forum_ids, in
 *     which case the search will be limited to the forums in this array.
 *
 * @return array $threads
 *     An array of matching threads, indexed by thread id. One special key
 *     "forum_ids" is set too. This one contains an array of all involved
 *     forum_ids.
 */
function phorum_db_user_list_subscriptions($user_id, $days=0, $forum_ids=NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($user_id, 'int');
    settype($days, 'int');
    if ($forum_ids !== NULL) phorum_db_sanitize_mixed($forums_ids, 'int');

    $time_where = $days > 0
                ? " AND (".time()." - m.modifystamp) <= ($days * 86400)"
                : '';

    $forum_where = ($forum_ids !== NULL and is_array($forum_ids))
                 ? " AND s.forum_id IN (" . implode(",", $forum_ids) . ")"
                 : '';

    // Retrieve all subscribed threads from the database for which the
    // latest message in the thread was posted within the provided time limit.
    $threads = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT s.thread         AS thread,
                s.forum_id       AS forum_id,
                s.sub_type       AS sub_type,
                m.subject        AS subject,
                m.modifystamp    AS modifystamp,
                m.author         AS author,
                m.user_id        AS user_id,
                m.email          AS email,
                m.recent_author  AS recent_author,
                m.recent_user_id AS recent_user_id,
                m.meta           AS meta
         FROM   {$PHORUM['subscribers_table']} AS s,
                {$PHORUM['message_table']} AS m
         WHERE  s.user_id    = $user_id AND
                m.message_id = s.thread AND
                (s.sub_type  = ".PHORUM_SUBSCRIPTION_MESSAGE." OR
                 s.sub_type  = ".PHORUM_SUBSCRIPTION_BOOKMARK.")
                $time_where
                $forum_where
         ORDER  BY m.modifystamp DESC",
        'thread'
    );

    // An array for keeping track of all involved forum ids.
    $forum_ids = array();

    foreach ($threads as $id => $thread)
    {
        // Unpack the thread's meta data.
        $threads[$id]['meta'] = empty($thread['meta'])
                              ? array() : unserialize($thread['meta']);

        $forum_ids[$thread['forum_id']] = $thread['forum_id'];
    }

    // Store the involved forum_ids in the thread array.
    $threads['forum_ids'] = $forum_ids;

    return $threads;
}
// }}}

// {{{ Function: phorum_db_user_get_subscription()
/**
 * Retrieve the subscription of a user for a thread.
 *
 * @param integer $user_id
 *     The user_id to retrieve the subscription for.
 *
 * @param integer $forum_id
 *     The forum_id to retrieve the subscription for.
 *
 * @param integer $thread
 *     The thread id to retrieve the subscription for.
 *
 * @return integer
 *     The type of subscription if there is a subscription available or
 *     NULL in case no subscription was found. For a list of available
 *     subscription types see the documentation for function
 *     phorum_db_user_subscribe().
 */
function phorum_db_user_get_subscription($user_id, $forum_id, $thread)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($user_id, 'int');
    settype($forum_id, 'int');
    settype($thread, 'int');
    settype($type, 'int');

    $type = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT sub_type
         FROM   {$PHORUM['subscribers_table']}
         WHERE  forum_id = $forum_id AND
                thread   = $thread AND
                user_id  = $user_id"
    );

    return $type;
}
// }}}

// {{{ Function: phorum_db_get_banlists()
/**
 * Retrieve the ban lists for the active forum.
 *
 * @param boolean $ordered
 *     If this parameter has a true value (default is FALSE),
 *     then the results will be ordered by ban type and string.
 *
 * @return array
 *     An array of active bans, indexed by the type of ban. For available
 *     ban types, see the documentation for the function
 *     phorum_db_mod_banlists(). Each value for a ban type is an array of
 *     bans. Each ban in those arrays is an array containing the fields:
 *     prce, string and forum_id.
 */
function phorum_db_get_banlists($ordered=FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($ordered, 'bool');

    $forum_where = '';
    if (isset($PHORUM['forum_id']) && !empty($PHORUM['forum_id']))
    {
        $forum_where = "WHERE forum_id = {$PHORUM['forum_id']} " .
                       // forum_id = 0 is used for GLOBAL ban items
                       'OR forum_id = 0';

        // If we're inside a vroot, then retrieve the ban items that apply
        // to this vroot as well.
        if (isset($PHORUM['vroot']) && !empty($PHORUM['vroot'])) {
            $forum_where .= " OR forum_id = {$PHORUM['vroot']}";
        }
    }

    $order = $ordered ? 'ORDER BY type, string' : '';

    $bans = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT *
         FROM {$PHORUM['banlist_table']}
         $forum_where
         $order"
    );

    $banlists = array();
    foreach ($bans as $ban) {
        $banlists[$ban['type']][$ban['id']] = array (
            'pcre'     => $ban['pcre'],
            'string'   => $ban['string'],
            'comments' => $ban['comments'],
            'forum_id' => $ban['forum_id']
        );
    }

    return $banlists;
}
// }}}

// {{{ Function: phorum_db_get_banitem
/**
 * Retrieve a single ban item from the ban lists.
 *
 * @param integer $banid
 *     The id of the ban item to retrieve.
 *
 * @return array
 *     A ban item array, containing the fields: pcre, string, forum_id,
 *     type. If no ban can be found for the $banid, then an empty array
 *     is returned instead.
 */
function phorum_db_get_banitem($banid)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($banid, 'int');

    $bans = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT *
         FROM   {$PHORUM['banlist_table']}
         WHERE  id = $banid"
    );

    if (count($bans)) {
        $ban = array(
            'pcre'     => $bans[0]['pcre'],
            'string'   => $bans[0]['string'],
            'forum_id' => $bans[0]['forum_id'],
            'type'     => $bans[0]['type'],
            'comments' => $bans[0]['comments']
        );
    } else {
        $ban = array();
    }

    return $ban;
}
// }}}

// {{{ Function: phorum_db_del_banitem
/**
 * Delete a single ban item from the ban lists.
 *
 * @param integer $banid
 *     The id of the ban item to delete.
 */
function phorum_db_del_banitem($banid)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($banid, 'int');

    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['banlist_table']}
         WHERE  id = $banid",
        NULL,
        DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_mod_banlists()
/**
 * Add or modify a single ban list item.
 *
 * @param $type
 *     The type of ban list item. Available ban types are:
 *     - PHORUM_BAD_IPS:        Match IP address or hostname.
 *     - PHORUM_BAD_NAMES:      Mach name or username.
 *     - PHORUM_BAD_EMAILS:     Match the email address.
 *     - PHORUM_BAD_USERID:     Match the user_id.
 *     - PHORUM_BAD_SPAM_WORDS: Match for spam words.
 *
 * @param boolean $pcre
 *      Whether the ban string has to be handled as a standard match
 *      string or as a pcre (Perl Compatible Regular Expression).
 *
 * @param string $string
 *     The ban string for performing the match.
 *
 * @param integer $forum_id
 *     The forum_id to link the ban to. This can be a real forum_id, a
 *     vroot id or 0 (zero) to indicate a GLOBAL ban item.
 *
 * @param string $comments
 *     Comments to add to the ban item. This can be used for documenting the
 *     ban item (why was the ban created, when was this done or generally
 *     any info that an administrator finds useful).
 *
 * @param integer $banid
 *     This parameter can be set to the id of a ban item to let the
 *     function update an existing ban. If set to 0 (zero), a new ban
 *     item will be created.
 *
 * @return boolean
 *     True if the ban item was created or updated successfully.
 */
function phorum_db_mod_banlists($type, $pcre, $string, $forum_id, $comments, $banid=0)
{
    $PHORUM = $GLOBALS['PHORUM'];

    $retarr = array();

    settype($type, 'int');
    settype($pcre, 'int');
    settype($forum_id, 'int');
    settype($banid, 'int');

    $string = phorum_db_interact(DB_RETURN_QUOTED, $string);
    $comments = phorum_db_interact(DB_RETURN_QUOTED, $comments);

    // Update an existing ban item.
    if ($banid > 0) {
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['banlist_table']}
             SET    forum_id = $forum_id,
                    type     = $type,
                    pcre     = $pcre,
                    string   = '$string',
                    comments = '$comments'
             WHERE  id = $banid",
            NULL,
            DB_MASTERQUERY
        );
    }
    // Create a new ban item.
    else {
        phorum_db_interact(
            DB_RETURN_RES,
            "INSERT INTO {$PHORUM['banlist_table']}
                    (forum_id, type, pcre, string, comments)
             VALUES ($forum_id, $type, $pcre, '$string', '$comments')",
            NULL,
            DB_MASTERQUERY
        );
    }

    return TRUE;
}
// }}}

// {{{ Function: phorum_db_pm_list()
/**
 * Retrieve all private messages for a user in a folder.
 *
 * @param mixed $folder
 *     The folder to use. Either a special folder (PHORUM_PM_INBOX or
 *     PHORUM_PM_OUTBOX) or the id of a custom user folder.
 *
 * @param integer $user_id
 *     The user to retrieve messages for or NULL to use the active
 *     Phorum user (default).
 *
 * @param boolean $reverse
 *     If set to a true value (default), sorting of messages is done
 *     in reverse (newest first).
 *
 * @return array
 *     An array of private messages for the folder.
 */
function phorum_db_pm_list($folder, $user_id = NULL, $reverse = TRUE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');
    settype($reverse, 'bool');

    if (is_numeric($folder)) {
        $folder_where = "pm_folder_id = $folder";
    } elseif ($folder == PHORUM_PM_INBOX || $folder == PHORUM_PM_OUTBOX) {
        $folder_where = "(pm_folder_id = 0 AND special_folder = '$folder')";
    } else trigger_error(
        'phorum_db_pm_list(): Illegal folder "'.htmlspecialchars($folder).'" '.
        'requested for user id "'.$user_id.'"',
        E_USER_ERROR
    );

    // Retrieve the messages from the folder.
    $messages = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT m.pm_message_id AS pm_message_id,
                m.user_id,       author,
                subject,         datestamp,
                meta,            pm_xref_id,
                pm_folder_id,    special_folder,
                read_flag,       reply_flag
         FROM   {$PHORUM['pm_messages_table']} AS m,
                {$PHORUM['pm_xref_table']} AS x
         WHERE  x.user_id = $user_id AND
                $folder_where AND
                x.pm_message_id = m.pm_message_id
         ORDER  BY x.pm_message_id " . ($reverse ? 'DESC' : 'ASC'),
        'pm_message_id'
    );

    // Add the recipient information unserialized to the messages.
    foreach ($messages as $id => $message) {
        $meta = unserialize($message['meta']);
        $messages[$id]['recipients'] = $meta['recipients'];
    }

    return $messages;
}
// }}}

// {{{ Function: phorum_db_pm_get()
/**
 * Retrieve a private message from the database.
 *
 * @param integer $pm_id
 *     The id for the private message to retrieve.
 *
 * @param mixed $folder
 *     The folder to retrieve the message from or NULL if the folder
 *     does not matter.
 *
 * @param integer $user_id
 *     The user to retrieve the message for or NULL to use the active
 *     Phorum user (default).
 *
 * @return array
 *     Return the private message on success or NULL if the message
 *     could not be found.
 */
function phorum_db_pm_get($pm_id, $folder = NULL, $user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');
    settype($pm_id, 'int');

    if ($folder === NULL) {
        $folder_where = '';
    } elseif (is_numeric($folder)) {
        $folder_where = "pm_folder_id = $folder AND ";
    } elseif ($folder == PHORUM_PM_INBOX || $folder == PHORUM_PM_OUTBOX) {
        $folder_where = "pm_folder_id = 0 AND special_folder = '$folder' AND ";
    } else trigger_error(
        'phorum_db_pm_get(): Illegal folder "'.htmlspecialchars($folder).'" '.
        'requested for user id "'.$user_id.'"',
        E_USER_ERROR
    );

    // Retrieve the private message.
    $messages = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT m.pm_message_id  AS pm_message_id,
                m.user_id        AS user_id,
                m.author         AS author,
                m.subject        AS subject,
                m.message        AS message,
                m.datestamp      AS datestamp,
                m.meta           AS meta,
                x.pm_xref_id     AS pm_xref_id,
                x.pm_folder_id   AS pm_folder_id,
                x.special_folder AS special_folder,
                x.pm_message_id  AS pm_message_id,
                x.read_flag      AS read_flag,
                x.reply_flag     AS reply_flag
         FROM {$PHORUM['pm_messages_table']} AS m,
              {$PHORUM['pm_xref_table']} AS x
         WHERE $folder_where
               x.pm_message_id = $pm_id AND
               x.user_id       = $user_id AND
               x.pm_message_id = m.pm_message_id"
    );

    // Prepare the return data.
    if (count($messages) == 0) {
        $message = NULL;
    } else {
        $message = $messages[0];

        // Add the recipient information unserialized to the message..
        $meta = unserialize($message['meta']);
        $message['recipients'] = $meta['recipients'];
    }

    return $message;
}
// }}}

// {{{ Function: phorum_db_pm_create_folder()
/**
 * Create a new private messages folder for a user.
 *
 * @param string $foldername
 *     The name of the folder to create.
 *
 * @param mixed $user_id
 *     The id of the user to create the folder for or NULL to use the
 *     active Phorum user (default).
 *
 * @return integer $pm_folder_id
 *     The id that was assigned to the new folder.
 */
function phorum_db_pm_create_folder($foldername, $user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');
    $foldername = phorum_db_interact(DB_RETURN_QUOTED, $foldername);

    $pm_folder_id = phorum_db_interact(
        DB_RETURN_NEWID,
        "INSERT INTO {$PHORUM['pm_folders_table']}
                (user_id, foldername)
         VALUES ($user_id, '$foldername')",
        NULL,
        DB_MASTERQUERY
    );

    return $pm_folder_id;
}
// }}}

// {{{ Function: phorum_db_pm_rename_folder()
/**
 * Rename a private message folder for a user.
 *
 * @param integer $folder_id
 *     The id of the folder to rename.
 *
 * @param string $newname
 *     The new name for the folder.
 *
 * @param mixed $user_id
 *     The user to rename the folder for or NULL to use the active
 *     Phorum user (default).
 */
function phorum_db_pm_rename_folder($folder_id, $newname, $user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');
    settype($folder_id, 'int');
    $newname = phorum_db_interact(DB_RETURN_QUOTED, $newname);

    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['pm_folders_table']}
         SET    foldername = '$newname'
         WHERE  pm_folder_id = $folder_id AND
                user_id = $user_id",
        NULL,
        DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_pm_delete_folder()
/**
 * Delete a private message folder for a user. Along with the folder,
 * all contained messages are deleted as well.
 *
 * @param integer $folder_id
 *     The id of the folder to delete.
 *
 * @param mixed $user_id
 *     The user to delete the folder for or NULL to use the active
 *     Phorum user (default).
 */
function phorum_db_pm_delete_folder($folder_id, $user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');
    settype($folder_id, 'int');

    // Retrieve the private messages in this folder and delete them.
    $list = phorum_db_pm_list($folder_id, $user_id);
    foreach ($list as $id => $data) {
        phorum_db_pm_delete($id, $folder_id, $user_id);
    }

    // Delete the folder itself.
    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['pm_folders_table']}
         WHERE pm_folder_id = $folder_id AND
               user_id      = $user_id",
        NULL,
        DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_pm_getfolders()
/**
 * Retrieve a list of private message folders for a user.
 *
 * @param mixed $user_id
 *     The user to retrieve folders for or NULL to use the active
 *     Phorum user (default).
 *
 * @param boolean $count
 *     If this parameter is set to a true value, the number of messages
 *     for each folder is included in the return data. By default,
 *     this is not done.
 *
 * @return array
 *     An array of private message folders, indexed by the folder id.
 *     The values are arrays, containing the fields "id" and "name".
 *     If $count is enabled, the  keys "total" and "new" will be added
 *     to the forum info.
 */
function phorum_db_pm_getfolders($user_id = NULL, $count = FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');
    settype($count, 'bool');

    // Initialize the list of folders. Our special folders are
    // not in the database, so these are added hard-coded to the list.
    // Add the incoming folder.
    $folders = array(
        PHORUM_PM_INBOX => array(
            'id'   => PHORUM_PM_INBOX,
            'name' => $PHORUM['DATA']['LANG']['INBOX'],
        ),
    );

    // Select all custom folders for the user.
    $customfolders = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT pm_folder_id AS id,
                foldername   AS name
         FROM   {$PHORUM['pm_folders_table']}
         WHERE  user_id = $user_id
         ORDER  BY foldername",
        'id'
    );

    // Add them to the folder list.
    foreach ($customfolders as $id => $customfolder) {
        $folders[$id] = $customfolder;
    }

    // Add the outgoing folder.
    $folders[PHORUM_PM_OUTBOX] = array(
        'id'   => PHORUM_PM_OUTBOX,
        'name' => $PHORUM['DATA']['LANG']['SentItems'],
    );

    // Count the number of messages in the folders if requested.
    if ($count)
    {
        // Initialize counters.
        foreach ($folders as $id => $data) {
            $folders[$id]['total'] = 0;
            $folders[$id]['new']   = 0;
        }

        // Collect count information.
        $countinfo = phorum_db_interact(
            DB_RETURN_ASSOCS,
            "SELECT pm_folder_id,
                    special_folder,
                    count(*) AS total,
                    (count(*) - sum(read_flag)) AS new
             FROM   {$PHORUM['pm_xref_table']}
             WHERE  user_id = $user_id
             GROUP  BY pm_folder_id, special_folder"
        );

        // Merge the count information with the folders.
        foreach ($countinfo as $info)
        {
            $folder_id = $info['pm_folder_id']
                       ? $info['pm_folder_id']
                       : $info['special_folder'];

            // If there are stale messages for no longer existing folders
            // (shouldn't happen), we do not want them to create non-existent
            // mailboxes in the list.
            if (isset($folders[$folder_id])) {
                $folders[$folder_id]['total'] = $info['total'];
                $folders[$folder_id]['new']   = $info['new'];
            }
        }
    }

    return $folders;
}
// }}}

// {{{ Function: phorum_db_pm_messagecount
/**
 * Compute the total number of private messages a user has and return
 * both the total number of messages and the number of unread messages.
 *
 * @param mixed $folder
 *     The id of the folder to use. Either a special folder
 *     (PHORUM_PM_INBOX or PHORUM_PM_OUTBOX), the id of a user's custom
 *     folder or PHORUM_PM_ALLFOLDERS for looking at all folders.
 *
 * @param mixed $user_id
 *     The user to retrieve messages for or NULL to use the
 *     active Phorum user (default).
 *
 * @return array
 *     An array containing the elements "total" and "new".
 */
function phorum_db_pm_messagecount($folder, $user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');

    if (is_numeric($folder)) {
        $folder_where = "pm_folder_id = $folder AND";
    } elseif ($folder == PHORUM_PM_INBOX || $folder == PHORUM_PM_OUTBOX) {
        $folder_where = "pm_folder_id = 0 AND special_folder = '$folder' AND";
    } elseif ($folder == PHORUM_PM_ALLFOLDERS) {
        $folder_where = '';
    } else trigger_error(
        'phorum_db_pm_messagecount(): Illegal folder "' .
        htmlspecialchars($folder).'" requested for user id "'.$user_id.'"',
        E_USER_ERROR
    );

    $counters = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT count(*) AS total,
                (count(*) - sum(read_flag)) AS new
         FROM   {$PHORUM['pm_xref_table']}
         WHERE  $folder_where user_id = $user_id"
     );

     $count = array( 'total' => 0, 'new' => 0 );
     if (count($counters) > 0) {
        $count['total'] = $counters[0]['total'];
        $count['new']   = ($counters[0]['new'] >= 1) ? $counters[0]['new'] : 0;
    }

    return $count;
}
// }}}

// {{{ Function: phorum_db_pm_checknew()
/**
 * Check if the user has any new private messages. This is useful in case
 * you only want to know whether the user has new messages or not and when
 * you are not interested in the exact amount of new messages.
 *
 * @param mixed $user_id
 *     The user to check for or NULL to use the active Phorum user (default).
 *
 * @return boolean
 *     TRUE in case there are new messages, FALSE otherwise.
 */
function phorum_db_pm_checknew($user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');

    $new = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT user_id
         FROM   {$PHORUM['pm_xref_table']}
         WHERE  user_id   = $user_id AND
                read_flag = 0 LIMIT 1"
    );

    return (bool)$new;
}
// }}}

// {{{ Function: phorum_db_pm_send
/**
 * Send a private message.
 *
 * @param string $subject
 *     The subject for the private message.
 *
 * @param string $message
 *     The message text for the private message.
 *
 * @param mixed $to
 *     A single user_id or an array of user_ids for specifying the
 *     recipient(s).
 *
 * @param mixed $from
 *     The id of the sending user or NULL to use the active Phorum user
 *     (default).
 *
 * @param $keepcopy
 *     If set to a true value, a copy of the mail will be kept in the
 *     outbox of the user. Default value is FALSE.
 *
 * @return integer
 *     The id that was assigned to the new message.
 */
function phorum_db_pm_send($subject, $message, $to, $from=NULL, $keepcopy=FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    // Prepare the sender.
    if ($from === NULL) $from = $PHORUM['user']['user_id'];
    settype($from, 'int');
    $fromuser = phorum_db_user_get($from, FALSE);
    if (! $fromuser) trigger_error(
        "phorum_db_pm_send(): Unknown sender user_id '$from'",
        E_USER_ERROR
    );
    $fromuser = phorum_db_interact(DB_RETURN_QUOTED, $fromuser['display_name']);
    $subject = phorum_db_interact(DB_RETURN_QUOTED, $subject);
    $message = phorum_db_interact(DB_RETURN_QUOTED, $message);

    // Prepare the list of recipients and xref entries.
    $xref_entries = array();
    $rcpts = array();
    if (! is_array($to)) $to = array($to);
    foreach ($to as $user_id)
    {
        settype($user_id, 'int');

        $user = phorum_db_user_get($user_id, FALSE);
        if (! $user) trigger_error(
            "phorum_db_pm_send(): Unknown recipient user_id '$user_id'",
            E_USER_ERROR
        );
        $rcpts[$user_id] = array(
            'user_id'        => $user_id,
            'display_name'   => $user['display_name'],
            'read_flag'      => 0,
        );
        $xref_entries[] = array(
            'user_id'        => $user_id,
            'pm_folder_id'   => 0,
            'special_folder' => PHORUM_PM_INBOX,
            'read_flag'      => 0,
        );
    }

    // Keep copy of this message in outbox?
    if ($keepcopy) {
        $xref_entries[] = array(
            'user_id'        => $from,
            'pm_folder_id'   => 0,
            'special_folder' => PHORUM_PM_OUTBOX,
            'read_flag'      => 1,
        );
    }
    // Prepare message meta data.
    $meta = phorum_db_interact(DB_RETURN_QUOTED, serialize(array(
        'recipients' => $rcpts
    )));

    // Create the message.
    $pm_id = phorum_db_interact(
        DB_RETURN_NEWID,
        "INSERT INTO {$PHORUM['pm_messages_table']}
                (user_id, author, subject,
                 message, datestamp, meta)
         VALUES ($from, '$fromuser', '$subject',
                 '$message', '".time()."', '$meta')",
        NULL,
        DB_MASTERQUERY
    );

    // Put the message in the recipient inboxes.
    foreach ($xref_entries as $xref)
    {
        phorum_db_interact(
            DB_RETURN_RES,
            "INSERT INTO {$PHORUM['pm_xref_table']}
                    (user_id, pm_folder_id,
                     special_folder, pm_message_id,
                     read_flag, reply_flag)
             VALUES ({$xref['user_id']}, {$xref['pm_folder_id']},
                     '{$xref['special_folder']}', $pm_id,
                     {$xref['read_flag']}, 0)",
            NULL,
            DB_MASTERQUERY
        );
    }

    return $pm_id;
}
// }}}

// {{{ Function: phorum_db_pm_setflag()
/**
 * Update a flag for a private message.
 *
 * @param integer $pm_id
 *     The id of the message to update.
 *
 * @param integer $flag
 *     The flag to update. Possible flags are: PHORUM_PM_READ_FLAG and
 *     PHORUM_PM_REPLY_FLAG.
 *
 * @param boolean $value
 *     The value for the flag (either TRUE or FALSE).
 *
 * @param $user_id
 *     The user to set a flag for or NULL to use the active Phorum
 *     user (default).
 */
function phorum_db_pm_setflag($pm_id, $flag, $value, $user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($pm_id, 'int');

    if ($flag != PHORUM_PM_READ_FLAG &&
        $flag != PHORUM_PM_REPLY_FLAG) trigger_error(
        'phorum_db_pm_setflag(): Illegal value "' . htmlspecialchars($flag) .
        '" for parameter $flag',
        E_USER_WARNING
    );

    $value = $value ? 1 : 0;

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');

    // Update the flag in the database.
    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['pm_xref_table']}
         SET    $flag = $value
         WHERE  pm_message_id = $pm_id AND
                user_id       = $user_id",
        NULL,
        DB_MASTERQUERY
    );

    // Update message counters.
    if ($flag == PHORUM_PM_READ_FLAG) {
        phorum_db_pm_update_message_info($pm_id);
    }
}
// }}}

// {{{ Function: phorum_db_pm_delete()
/**
 * Delete a private message from a folder.
 *
 * @param integer $pm_id
 *     The id of the private message to delete
 *
 * @param mixed $folder
 *     The folder from which to delete the message
 *
 * @param integer $user_id
 *     The id of the user to delete the message for
 *     or NULL to use the active Phorum user (default).
 */
function phorum_db_pm_delete($pm_id, $folder, $user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($pm_id, 'int');

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');

    if (is_numeric($folder)) {
        $folder_where = "pm_folder_id = $folder";
    } elseif ($folder == PHORUM_PM_INBOX || $folder == PHORUM_PM_OUTBOX) {
        $folder_where = "(pm_folder_id = 0 AND special_folder = '$folder')";
    } else trigger_error(
        'phorum_db_pm_delete(): Illegal folder "' .
        htmlspecialchars($folder).'" requested for user id "'.$user_id.'"',
        E_USER_ERROR
    );

    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['pm_xref_table']}
         WHERE user_id       = $user_id AND
               pm_message_id = $pm_id AND
               $folder_where",
        NULL,
        DB_MASTERQUERY
    );

    // Update message counters.
    phorum_db_pm_update_message_info($pm_id);
}
// }}}

// {{{ Function: phorum_db_pm_move()
/**
 * Move a private message to a different folder.
 *
 * @param integer $pm_id
 *     The id of the private message to move.
 *
 * @param mixed $from
 *     The folder to move the message from.
 *
 * @param mixed $to
 *     The folder to move the message to.
 *
 * @param mixed $user_id
 *     The id or the user to move the message for
 *     or NULL to use the active Phorum user (default).
 */
function phorum_db_pm_move($pm_id, $from, $to, $user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($pm_id, 'int');

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');

    if (is_numeric($from)) {
        $folder_where = "pm_folder_id = $from";
    } elseif ($from == PHORUM_PM_INBOX || $from == PHORUM_PM_OUTBOX) {
        $folder_where = "(pm_folder_id = 0 AND special_folder = '$from')";
    } else trigger_error(
        'phorum_db_pm_move(): Illegal source folder "' .
        htmlspecialchars($from).'" requested for user id "'.$user_id.'"',
        E_USER_ERROR
    );

    if (is_numeric($to)) {
        $pm_folder_id = $to;
        $special_folder = 'NULL';
    } elseif ($to == PHORUM_PM_INBOX || $to == PHORUM_PM_OUTBOX) {
        $pm_folder_id = 0;
        $special_folder = "'$to'";
    } else trigger_error(
        'phorum_db_pm_move(): Illegal target folder "' .
        htmlspecialchars($to).'" requested for user_id "'.$user_id.'"',
        E_USER_ERROR
    );

    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['pm_xref_table']}
         SET    pm_folder_id   = $pm_folder_id,
                special_folder = $special_folder
         WHERE  user_id        = $user_id AND
                pm_message_id  = $pm_id AND
                $folder_where",
        NULL,
        DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_pm_update_message_info()
/**
 * Update the meta information for a message.
 *
 * This function will update the meta information using the information
 * from the xrefs table. If we find that no xrefs are available for the
 * message anymore, the message will be deleted from the database.
 *
 * @param integer $pm_id
 *     The id of the private message for which to update the meta information.
 */
function phorum_db_pm_update_message_info($pm_id)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($pm_id, 'int');

    // Retrieve the meta data for the private message.
    $pm = phorum_db_interact(
        DB_RETURN_ASSOC,
        "SELECT meta
         FROM   {$PHORUM['pm_messages_table']}
         WHERE  pm_message_id = $pm_id",
        NULL,
        DB_MASTERQUERY
    );

    # Return immediately if no message was found.
    if (empty($pm)) return;

    // Find the xrefs for this message.
    $xrefs = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT user_id, read_flag
         FROM   {$PHORUM['pm_xref_table']}
         WHERE  pm_message_id = $pm_id",
        NULL,
        DB_MASTERQUERY
    );

    // No xrefs left? Then the message can be fully deleted.
    if (count($xrefs) == 0) {
        phorum_db_interact(
            DB_RETURN_RES,
            "DELETE FROM {$PHORUM['pm_messages_table']}
             WHERE  pm_message_id = $pm_id",
            NULL,
            DB_MASTERQUERY
        );
        return;
    }

    // Update the read flags for the recipients in the meta data.
    $meta = unserialize($pm['meta']);
    $rcpts = $meta['recipients'];
    foreach ($xrefs as $xref) {
        // Only update if available. A copy that is kept in the outbox will
        // not be in the meta list, so if the copy is read, the meta data
        // does not have to be updated here.
        if (isset($rcpts[$xref[0]])) {
            $rcpts[$xref[0]]['read_flag'] = $xref[1];
        }
    }
    $meta['recipients'] = $rcpts;

    // Store the new meta data.
    $meta = phorum_db_interact(DB_RETURN_QUOTED, serialize($meta));
    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['pm_messages_table']}
         SET    meta = '$meta'
         WHERE  pm_message_id = $pm_id",
        NULL,
        DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_pm_is_buddy()
/**
 * Check if a user is buddy of another user.
 *
 * @param integer $buddy_user_id
 *     The user_id for which to check if the user is a buddy.
 *
 * @param mixed $user_id
 *     The user_id for which the buddy list must be checked
 *     or NULL to use the active Phorum user (default).
 *
 * @return mixed
 *     If the user is a buddy, then the pm_buddy_id for the buddy will be
 *     returned. If not, then NULL will be returned.
 */
function phorum_db_pm_is_buddy($buddy_user_id, $user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($buddy_user_id, 'int');

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');

    $pm_buddy_id = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT pm_buddy_id
         FROM   {$PHORUM['pm_buddies_table']}
         WHERE  user_id       = $user_id AND
                buddy_user_id = $buddy_user_id"
    );

    return $pm_buddy_id;
}
// }}}

// {{{ Function: phorum_db_pm_buddy_add()
/**
 * Add a buddy for a user.
 *
 * @param integer $buddy_user_id
 *     The user_id that has to be added as a buddy.
 *
 * @param mixed $user_id
 *     The user_id the buddy has to be added for
 *     or NULL to use the active Phorum user (default).
 *
 * @return mixed
 *     The id that was assigned to the new buddy or the existing id if
 *     the buddy already existed. If no user can be found for the
 *     $buddy_user_id parameter, then NULL will be returned.
 */
function phorum_db_pm_buddy_add($buddy_user_id, $user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($buddy_user_id, 'int');

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');

    // Check if the buddy_user_id is a valid user_id.
    $valid = phorum_db_user_get($buddy_user_id, FALSE);
    if (! $valid) return NULL;

    // See if the user is already a buddy.
    $pm_buddy_id = phorum_db_pm_is_buddy($buddy_user_id);

    // If not, then create insert a new buddy relation.
    if ($pm_buddy_id === NULL) {
        $pm_buddy_id = phorum_db_interact(
            DB_RETURN_NEWID,
            "INSERT INTO {$PHORUM['pm_buddies_table']}
                    (user_id, buddy_user_id)
             VALUES ($user_id, $buddy_user_id)",
            NULL,
            DB_MASTERQUERY
        );
    }

    return $pm_buddy_id;
}
// }}}

// {{{ Function: phorum_db_pm_buddy_delete()
/**
 * Delete a buddy for a user.
 *
 * @param integer $buddy_user_id
 *     The user_id that has to be deleted as a buddy.
 *
 * @param mixed $user_id
 *     The user_id the buddy has to be delete for
 *     or NULL to use the active Phorum user (default).
 */
function phorum_db_pm_buddy_delete($buddy_user_id, $user_id = NULL)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($buddy_user_id, 'int');

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');

    phorum_db_interact(
        DB_RETURN_RES,
        "DELETE FROM {$PHORUM['pm_buddies_table']}
         WHERE buddy_user_id = $buddy_user_id AND
               user_id       = $user_id",
        NULL,
        DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_pm_buddy_list()
/**
 * Retrieve a list of buddies for a user.
 *
 * @param mixed $user_id
 *     The user_id for which to retrieve the buddies or NULL to user the
 *     active Phorum user (default).
 *
 * @param boolean $find_mutual
 *     Whether to find mutual buddies or not (default FALSE).
 *
 * @return array
 *     An array of buddies. The keys in this array are user_ids. The values
 *     are arrays, which contain the field "user_id" and possibly the
 *     boolean field "mutual" if the $find_mutual parameter was set to
 *     a true value.
 */
function phorum_db_pm_buddy_list($user_id = NULL, $find_mutual = FALSE)
{
    $PHORUM = $GLOBALS['PHORUM'];

    if ($user_id === NULL) $user_id = $PHORUM['user']['user_id'];
    settype($user_id, 'int');

    settype($find_mutual, 'bool');

    // Retrieve all buddies for this user.
    $buddies = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT buddy_user_id AS user_id
         FROM {$PHORUM['pm_buddies_table']}
         WHERE user_id = $user_id",
        'user_id'
    );

    // If we do not have to lookup mutual buddies, we're done.
    if (! $find_mutual) return $buddies;

    // Initialize mutual buddy value.
    foreach ($buddies as $id => $data) {
        $buddies[$id]['mutual'] = FALSE;
    }

    // Retrieve all mutual buddies.
    $mutuals = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT DISTINCT a.buddy_user_id AS buddy_user_id
         FROM {$PHORUM['pm_buddies_table']} AS a,
              {$PHORUM['pm_buddies_table']} AS b
         WHERE a.user_id       = $user_id AND
               b.user_id       = a.buddy_user_id AND
               b.buddy_user_id = $user_id"
    );

    // Merge the mutual buddies with the buddies array.
    foreach ($mutuals as $mutual) {
        $buddies[$mutual[0]]['mutual'] = TRUE;
    }

    return $buddies;
}
// }}}

// {{{ Function: phorum_db_split_thread()
/**
 * Split a thread.
 *
 * @param integer $message_id
 *     The id of the message at which to split a thread.
 *
 * @param integer $forum_id
 *     The id of the forum in which the message can be found.
 */
function phorum_db_split_thread($message_id, $forum_id)
{
    settype($message_id, 'int');
    settype($forum_id, 'int');

    if ($message_id > 0 && $forum_id > 0)
    {
        // Retrieve the message tree for all messages below the split message.
        // This tree is used for updating the thread ids of the children
        // below the split message.
        $tree = phorum_db_get_messagetree($message_id, $forum_id);

        // Turn the message into a thread starter message.
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$GLOBALS['PHORUM']['message_table']}
             SET    thread     = $message_id,
                    parent_id  = 0
             WHERE  message_id = $message_id",
            NULL,
            DB_MASTERQUERY
        );

        // Link the messages below the split message to the split off thread.
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$GLOBALS['PHORUM']['message_table']}
             SET    thread = $message_id
             WHERE  message_id IN ($tree)",
            NULL,
            DB_MASTERQUERY
        );
    }
}
// }}}

// {{{ Function: phorum_db_get_max_messageid()
/**
 * Retrieve the maximum message_id in the database.
 *
 * @return integer $max_id
 *     The maximum available message_id or 0 (zero)
 *     if no message was found at all.
 */
function phorum_db_get_max_messageid()
{
    $PHORUM = $GLOBALS['PHORUM'];

    $maxid = phorum_db_interact(
        DB_RETURN_VALUE,
        "SELECT max(message_id)
         FROM   {$PHORUM["message_table"]}"
    );

    return $maxid === NULL ? 0 : $maxid;
}
// }}}

// {{{ Function: phorum_db_increment_viewcount()
/**
 * Increment the viewcount field for a post.
 *
 * @param integer $message_id
 *     The id of the message for which to increment the viewcount.
 *
 * @param boolean $thread_id
 *     If this parameter is set to a thread_id, then the threadviewcount
 *     for that thread will be incremented as well.
 */
function phorum_db_increment_viewcount($message_id, $thread_id = NULL)
{
    settype($message_id, 'int');
    if ($thread_id !== NULL) settype($thread_id, 'int');

    // Check if the message is the thread starter, in which case we can
    // handle the increment with only one SQL query later on in this function.
    $tvc = '';
    if ($thread_id !== NULL) {
        if ($thread_id == $message_id) {
            $tvc = ',threadviewcount = threadviewcount + 1';
        } else {
            phorum_db_interact(
                DB_RETURN_RES,
                "UPDATE {$GLOBALS['PHORUM']['message_table']}
                 SET    threadviewcount = threadviewcount + 1
                 WHERE  message_id = $thread_id",
                NULL,
                DB_MASTERQUERY
            );
        }
    }

    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$GLOBALS['PHORUM']['message_table']}
         SET    viewcount = viewcount + 1
                $tvc
         WHERE  message_id = $message_id",
        NULL,
        DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_rebuild_search_data()
/**
 * Rebuild the search table data from scratch.
 */
function phorum_db_rebuild_search_data()
{
    $PHORUM = $GLOBALS['PHORUM'];

    // Delete all records from the search table.
    phorum_db_interact(
        DB_RETURN_RES,
        "TRUNCATE TABLE {$PHORUM['search_table']}",
        NULL,
        DB_GLOBALQUERY | DB_MASTERQUERY
    );

    // Rebuild all search data from scratch.
    phorum_db_interact(
        DB_RETURN_RES,
        "INSERT INTO {$PHORUM['search_table']}
                (message_id, search_text, forum_id)
         SELECT message_id,
                concat(author, ' | ', subject, ' | ', body),
                forum_id
         FROM   {$PHORUM['message_table']}",
        NULL,
        DB_GLOBALQUERY | DB_MASTERQUERY
    );
}
// }}}

// {{{ Function: phorum_db_rebuild_user_posts()
/**
 * Rebuild the user post counts from scratch.
 */
function phorum_db_rebuild_user_posts()
{
    $PHORUM = $GLOBALS['PHORUM'];

    // Reset the post count for all users.
    phorum_db_interact(
        DB_RETURN_RES,
        "UPDATE {$PHORUM['user_table']}
         SET posts = 0",
        NULL,
        DB_GLOBALQUERY | DB_MASTERQUERY
    );

    // Retrieve the post counts for all user_ids in the message table.
    $postcounts = phorum_db_interact(
        DB_RETURN_ROWS,
        "SELECT user_id, count(*)
         FROM   {$PHORUM['message_table']}
         GROUP  BY user_id",
        NULL,
        DB_GLOBALQUERY | DB_MASTERQUERY
    );

    // Set the post counts for the users to their correct values.
    foreach ($postcounts as $postcount) {
        phorum_db_interact(
            DB_RETURN_RES,
            "UPDATE {$PHORUM['user_table']}
             SET    posts   = {$postcount[1]}
             WHERE  user_id = {$postcount[0]}",
            NULL,
            DB_MASTERQUERY
        );
    }
}
// }}}

// {{{ Function: phorum_db_user_search_custom_profile_field()
/**
 * Search for users, based on a simple search condition,
 * which can be used to search on custom profile fields.
 *
 * The parameters $field_id, $value and $operator (which are used for defining
 * the search condition) can be arrays or single values. If arrays are used,
 * then all three parameter arrays must contain the same number of elements
 * and the key values in the arrays must be the same.
 *
 * @param mixed $field_id
 *     The custom profile field id (integer) or ids (array) to search on.
 *
 * @param mixed $value
 *     The value (string) or values (array) to search for.
 *
 * @param mixed $operator
 *     The operator (string) or operators (array) to use. Valid operators are
 *     "=", "!=", "<>", "<", ">", ">=" and "<=", "*". The
 *     "*" operator is for executing a "LIKE '%value%'" matching query.
 *
 * @param boolean $return_array
 *     If this parameter has a true value, then an array of all matching
 *     user_ids will be returned. Else, a single user_id will be returned.
 *
 * @param string $type
 *     The type of search to perform. This can be one of:
 *     - AND  match against all fields
 *     - OR   match against any of the fields
 *
 * @param integer $offset
 *     The result page offset starting with 0.
 *
 * @param integer $length
 *     The result page length (nr. of results per page)
 *     or 0 (zero, the default) to return all results.
 *
 * @return mixed
 *     An array of matching user_ids or a single user_id (based on the
 *     $return_array parameter). If no user_ids can be found at all,
 *     then 0 (zero) will be returned.
 */
function phorum_db_user_search_custom_profile_field($field_id, $value, $operator = '=', $return_array = FALSE, $type = 'AND', $offset = 0, $length = 0)
{
    $PHORUM = $GLOBALS['PHORUM'];

    settype($return_array, 'bool');
    settype($offset, 'int');
    settype($length, 'int');

    // Convert all search condition parameters to arrays.
    if (!is_array($field_id)) $field_id = array($field_id);
    if (!is_array($value))    $value    = array($value);
    if (!is_array($operator)) $operator = array($operator);

    // Basic check to see if all condition arrays contain the
    // same number of elements.
    if (count($field_id) != count($value) ||
        count($field_id) != count($operator)) trigger_error(
        'phorum_db_user_search_custom_profile_field(): ' .
        'array parameters $field_id, $value, and $operator do not contain ' .
        'the same number of elements',
        E_USER_ERROR
    );

    $type = strtoupper($type);
    if ($type != 'AND' && $type != 'OR') trigger_error(
        'phorum_db_user_search_custom_profile_field(): ' .
        'Illegal search type parameter (must be either AND" or "OR")',
        E_USER_ERROR
    );

    $valid_operators = array('=', '<>', '!=', '>', '<', '>=', '<=', '*');

    // Construct the required "WHERE" clause.
    $clauses = array();
    foreach ($field_id as $key => $id) {
        if (in_array($operator[$key], $valid_operators)) {
            settype($id, 'int');
            $value[$key] = phorum_db_interact(DB_RETURN_QUOTED, $value[$key]);
            if ($operator[$key] == '*') {
                $clauses[] = "(type = $id AND data LIKE '%$value[$key]%')";
            } else {
                $clauses[] = "(type = $id AND data $operator[$key] '$value[$key]')";
            }
        }
    }
    if (!empty($clauses)) {
        $where = 'WHERE ' . implode(" OR ", $clauses);
    } else {
        $where = '';
    }

    // Construct the required "LIMIT" clause.
    if (!empty($length)) {
        $limit = "LIMIT $offset, $length";
    } else {
        // If we do not need to return an array, the we can limit the
        // query results to only one record.
        $limit = $return_array ? '' : 'LIMIT 1';
    }

    // Build the final query.
    if ($type == 'OR' || count($clauses) == 1)
    {
        $sql = "SELECT DISTINCT(user_id)
                FROM   {$PHORUM['user_custom_fields_table']}
                $where
                $limit";
    } else {
        $sql = "SELECT user_id
                FROM   {$PHORUM['user_custom_fields_table']}
                $where
                GROUP  BY user_id
                HAVING count(*) = " . count($clauses) . " " .
                $limit;
    }

    // Retrieve the matching user_ids from the database.
    $user_ids = phorum_db_interact(
        DB_RETURN_ROWS, $sql, 0 // keyfield 0 is the user_id
    );

    // No user_ids found at all?
    if (count($user_ids) == 0) return 0;

    // Return an array of user_ids.
    if ($return_array) {
        foreach ($user_ids as $id => $user_id) $user_ids[$id] = $user_id[0];
        return $user_ids;
    }

    // Return a single user_id.
    list ($user_id, $dummy) = each($user_ids);
    return $user_id;
}
// }}}

// {{{ Function: phorum_db_metaquery_compile()
/**
 * Translates a message searching meta query into a real SQL WHERE
 * statement for this database backend. The meta query can be used to
 * define extended SQL queries, based on a meta description of the
 * search that has to be performed on the database.
 *
 * The meta query is an array, containing:
 * - query conditions
 * - grouping using "(" and ")"
 * - AND/OR specifications using "AND" and "OR".
 *
 * The query conditions are arrays, containing the following elements:
 * <ul>
 * <li>condition<br>
 *   <br>
 *   A description of a condition. The syntax for this is:
 *   <field name to query> <operator> <match specification><br>
 *   <br>
 *   The <field name to query> is a field in the message query that
 *   we are running in this function.<br>
 *   <br>
 *   The <operator> can be one of "=", "!=", "<", "<=", ">", ">=".
 *   Note that there is nothing like "LIKE" or "NOT LIKE". If a "LIKE"
 *   query has to be done, then that is setup through the
 *   <match specification> (see below).<br>
 *   <br>
 *   The <match specification> tells us with what the field should be
 *   matched. The string "QUERY" inside the specification is preserved to
 *   specify at which spot in the query the "query" element from the
 *   condition array should be inserted. If "QUERY" is not available in
 *   the specification, then a match is made on the exact value in the
 *   specification. To perform "LIKE" searches (case insensitive wildcard
 *   searches), you can use the "*" wildcard character in the specification
 *   to do so.<br><br>
 * </li>
 * <li>query<br>
 *   <br>
 *   The data to use in the query, in case the condition element has a
 *   <match specification> that uses "QUERY" in it.
 * </li>
 * </ul>
 * Example:
 * <code>
 * $metaquery = array(
 *     array(
 *         "condition"  =>  "field1 = *QUERY*",
 *         "query"      =>  "test data"
 *     ),
 *     "AND",
 *     "(",
 *     array("condition"  => "field2 = whatever"),
 *     "OR",
 *     array("condition"  => "field2 = something else"),
 *     ")"
 * );
 * </code>
 *
 * For MySQL, this would be turned into the MySQL WHERE statement:
 * <code>
 * ... WHERE field1 LIKE '%test data%'
 *     AND (field2 = 'whatever' OR field2 = 'something else')
 * </code>
 *
 * @param array $metaquery
 *     A meta query description array.
 *
 * @return array
 *     An array containing two elements. The first element is either
 *     TRUE or FALSE, based on the success state of the function call
 *     (FALSE means that there was an error). The second argument contains
 *     either a WHERE statement or an error message.
 */
function phorum_db_metaquery_compile($metaquery)
{
    $where = '';

    $expect_condition  = TRUE;
    $expect_groupstart = TRUE;
    $expect_groupend   = FALSE;
    $expect_combine    = FALSE;
    $in_group          = 0;

    foreach ($metaquery as $part)
    {
        // Found a new condition.
        if ($expect_condition && is_array($part))
        {
            $cond = trim($part['condition']);
            if (preg_match('/^([\w_\.]+)\s+(!?=|<=?|>=?)\s+(\S*)$/', $cond, $m))
            {
                $field = $m[1];
                $comp  = $m[2];
                $match = $m[3];

                $matchtokens = preg_split(
                    '/(\*|QUERY|NULL)/',
                    $match, -1,
                    PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY
                );

                $matchsql = "'";
                $is_like_query = FALSE;
                foreach ($matchtokens as $m) {
                    if ($m == '*') {
                        $is_like_query = TRUE;
                        $matchsql .= '%';
                    } elseif ($m == 'QUERY') {
                        $p = $part['query'];
                        $matchsql .= phorum_db_interact(DB_RETURN_QUOTED, $p);
                    } else {
                        $matchsql .= phorum_db_interact(DB_RETURN_QUOTED, $m);
                    }
                }
                $matchsql .= "'";

                if ($is_like_query)
                {
                    if ($comp == '=') { $comp = ' LIKE '; }
                    elseif ($comp == '!=') { $comp = ' NOT LIKE '; }
                    else return array(
                        FALSE,
                        'Illegal metaquery token ' . htmlspecialchars($cond) .
                        ": wildcard match does not combine with $comp operator"
                    );
                }

                $where .= "$field $comp $matchsql ";
            } else {
                return array(
                    FALSE,
                    'Illegal metaquery token ' . htmlspecialchars($cond) .
                    ': condition does not match the required format'
                );
            }

            $expect_condition   = FALSE;
            $expect_groupstart  = FALSE;
            $expect_groupend    = $in_group;
            $expect_combine     = TRUE;
        }
        // Found a new group start.
        elseif ($expect_groupstart && $part == '(')
        {
            $where .= '(';
            $in_group ++;

            $expect_condition   = TRUE;
            $expect_groupstart  = FALSE;
            $expect_groupend    = FALSE;
            $expect_combine     = FALSE;
        }
        // Found a new group end.
        elseif ($expect_groupend && $part == ')')
        {
            $where .= ') ';
            $in_group --;

            $expect_condition   = FALSE;
            $expect_groupstart  = FALSE;
            $expect_groupend    = $in_group;
            $expect_combine     = TRUE;
        }
        // Found a combine token (AND or OR).
        elseif ($expect_combine && preg_match('/^(OR|AND)$/i', $part, $m))
        {
            $where .= strtoupper($m[1]) . ' ';

            $expect_condition   = TRUE;
            $expect_groupstart  = TRUE;
            $expect_groupend    = FALSE;
            $expect_combine     = FALSE;
        }
        // Unexpected or illegal token.
        else trigger_error(
            'Internal error: unexpected token in metaquery description: ' .
            (is_array($part) ? 'condition' : htmlspecialchars($part)),
            E_USER_ERROR
        );
    }

    if ($expect_groupend) die ('Internal error: unclosed group in metaquery');

    // If the metaquery is empty, then provide a safe true WHERE statement.
    if ($where == '') { $where = '1 = 1'; }

    return array(TRUE, $where);
}
// }}}

// {{{ Function: phorum_db_metaquery_messagesearch()
/**
 * Run a search on the messages, using a metaquery. See the documentation
 * for the phorum_db_metaquery_compile() function for more info on the
 * metaquery syntax.
 *
 * The query that is run here, does create a view on the messages, which
 * includes some thread and user info. This is used so these can also
 * be taken into account when selecting messages. For the condition elements
 * in the meta query, you can use fully qualified field names for the
 * <field name to query>. You can use message.*, user.* and thread.* for this.
 *
 * The primary goal for this function is to provide a backend for the
 * message pruning interface.
 *
 * @param array $metaquery
 *     A metaquery array. See {@link phorum_db_metaquery_compile()} for
 *     more information about the metaquery syntax.
 *
 * @return array
 *     An array of message records.
 */
function phorum_db_metaquery_messagesearch($metaquery)
{
    $PHORUM = $GLOBALS['PHORUM'];

    // Compile the metaquery into a where statement.
    list($success, $where) = phorum_db_metaquery_compile($metaquery);
    if (!$success) trigger_error($where, E_USER_ERROR);

    // Retrieve matching messages.
    $messages = phorum_db_interact(
        DB_RETURN_ASSOCS,
        "SELECT message.message_id  AS message_id,
                message.thread      AS thread,
                message.parent_id   AS parent_id,
                message.forum_id    AS forum_id,
                message.subject     AS subject,
                message.author      AS author,
                message.datestamp   AS datestamp,
                message.body        AS body,
                message.ip          AS ip,
                message.status      AS status,
                message.user_id     AS user_id,
                user.username       AS user_username,
                thread.closed       AS thread_closed,
                thread.modifystamp  AS thread_modifystamp,
                thread.thread_count AS thread_count
         FROM   {$PHORUM['message_table']} AS thread,
                {$PHORUM['message_table']} AS message
                    LEFT JOIN {$PHORUM['user_table']} AS user
                    ON message.user_id = user.user_id
         WHERE  message.thread  = thread.message_id AND
                ($where)
         ORDER BY message_id ASC",
        'message_id'
    );

    return $messages;
}
// }}}

// {{{ Function: phorum_db_create_tables()
/**
 * Create the tables that are needed in the database. This function will
 * only be called at install time. After installation, changes in the
 * database schema will be handled by the database upgrade system.
 *
 * @return mixed
 *     NULL on success or an error message on failure.
 *
 * @todo It might be nice to have some feedback mechanism through a
 *       callback. Using that, table create status can be provided
 *       to the interface which is creating the tables. This is especially
 *       useful in case the create process fails at some point, in which
 *       case you currently have no good feedback about the create
 *       table progress.
 */
function phorum_db_create_tables()
{
    $PHORUM = $GLOBALS['PHORUM'];

    $lang = PHORUM_DEFAULT_LANGUAGE;

    $charset = empty($PHORUM['DBCONFIG']['charset'])
             ? ''
             : "DEFAULT CHARACTER SET {$PHORUM['DBCONFIG']['charset']}";

    $create_table_queries = array(

      "CREATE TABLE {$PHORUM['forums_table']} (
           forum_id                 int unsigned   NOT NULL auto_increment,
           name                     varchar(50)    NOT NULL default '',
           active                   tinyint(1)     NOT NULL default '0',
           description              text           NOT NULL,
           template                 varchar(50)    NOT NULL default '',
           folder_flag              tinyint(1)     NOT NULL default '0',
           parent_id                int unsigned   NOT NULL default '0',
           list_length_flat         int unsigned   NOT NULL default '0',
           list_length_threaded     int unsigned   NOT NULL default '0',
           moderation               int unsigned   NOT NULL default '0',
           threaded_list            tinyint(1)     NOT NULL default '0',
           threaded_read            tinyint(1)     NOT NULL default '0',
           float_to_top             tinyint(1)     NOT NULL default '0',
           check_duplicate          tinyint(1)     NOT NULL default '0',
           allow_attachment_types   varchar(100)   NOT NULL default '',
           max_attachment_size      int unsigned   NOT NULL default '0',
           max_totalattachment_size int unsigned   NOT NULL default '0',
           max_attachments          int unsigned   NOT NULL default '0',
           pub_perms                int unsigned   NOT NULL default '0',
           reg_perms                int unsigned   NOT NULL default '0',
           display_ip_address       tinyint(1)     NOT NULL default '1',
           allow_email_notify       tinyint(1)     NOT NULL default '1',
           language                 varchar(100)   NOT NULL default '$lang',
           email_moderators         tinyint(1)     NOT NULL default '0',
           message_count            int unsigned   NOT NULL default '0',
           sticky_count             int unsigned   NOT NULL default '0',
           thread_count             int unsigned   NOT NULL default '0',
           last_post_time           int unsigned   NOT NULL default '0',
           display_order            int unsigned   NOT NULL default '0',
           read_length              int unsigned   NOT NULL default '0',
           vroot                    int unsigned   NOT NULL default '0',
           edit_post                tinyint(1)     NOT NULL default '1',
           template_settings        text           NOT NULL,
           forum_path               text           NOT NULL,
           count_views              tinyint(1)     NOT NULL default '0',
           count_views_per_thread   tinyint(1)     NOT NULL default '0',
           display_fixed            tinyint(1)     NOT NULL default '0',
           reverse_threading        tinyint(1)     NOT NULL default '0',
           inherit_id               int unsigned       NULL default NULL,
           cache_version            int unsigned   NOT NULL default '0',

           PRIMARY KEY (forum_id),
           KEY name (name),
           KEY active (active, parent_id),
           KEY group_id (parent_id)
       ) $charset",

      "CREATE TABLE {$PHORUM['message_table']} (
           message_id               int unsigned   NOT NULL auto_increment,
           forum_id                 int unsigned   NOT NULL default '0',
           thread                   int unsigned   NOT NULL default '0',
           parent_id                int unsigned   NOT NULL default '0',
           user_id                  int unsigned   NOT NULL default '0',
           author                   varchar(255)   NOT NULL default '',
           subject                  varchar(255)   NOT NULL default '',
           body                     text           NOT NULL,
           email                    varchar(100)   NOT NULL default '',
           ip                       varchar(255)   NOT NULL default '',
           status                   tinyint(4)     NOT NULL default '2',
           msgid                    varchar(100)   NOT NULL default '',
           modifystamp              int unsigned   NOT NULL default '0',
           thread_count             int unsigned   NOT NULL default '0',
           moderator_post           tinyint(1)     NOT NULL default '0',
           sort                     tinyint(4)     NOT NULL default '2',
           datestamp                int unsigned   NOT NULL default '0',
           meta                     mediumtext         NULL,
           viewcount                int unsigned   NOT NULL default '0',
           threadviewcount          int unsigned   NOT NULL default '0',
           closed                   tinyint(1)     NOT NULL default '0',
           recent_message_id        int unsigned   NOT NULL default '0',
           recent_user_id           int unsigned   NOT NULL default '0',
           recent_author            varchar(255)   NOT NULL default '',
           moved                    tinyint(1)     NOT NULL default '0',

           PRIMARY KEY (message_id),
           KEY thread_message (thread,message_id),
           KEY thread_forum (thread,forum_id),
           KEY special_threads (sort,forum_id),
           KEY status_forum (status,forum_id),
           KEY list_page_float (forum_id,parent_id,modifystamp),
           KEY list_page_flat (forum_id,parent_id,thread),
           KEY new_count (forum_id,status,moved,message_id),
           KEY new_threads (forum_id,status,parent_id,moved,message_id),
           KEY recent_threads (status, parent_id, message_id, forum_id),
           KEY updated_threads (status, parent_id, modifystamp),
           KEY dup_check (forum_id,author(50),subject,datestamp),
           KEY forum_max_message (forum_id,message_id,status,parent_id),
           KEY last_post_time (forum_id,status,modifystamp),
           KEY next_prev_thread (forum_id,status,thread),
           KEY recent_user_id (recent_user_id),
           KEY user_messages (user_id, message_id)
       ) $charset",

      "CREATE TABLE {$PHORUM['settings_table']} (
           name                     varchar(255)   NOT NULL default '',
           type                     enum('V','S')  NOT NULL default 'V',
           data                     text           NOT NULL,

           PRIMARY KEY (name)
       ) $charset",

      "CREATE TABLE {$PHORUM['subscribers_table']} (
           user_id                  int unsigned   NOT NULL default '0',
           forum_id                 int unsigned   NOT NULL default '0',
           sub_type                 tinyint(4)     NOT NULL default '0',
           thread                   int unsigned   NOT NULL default '0',

           PRIMARY KEY (user_id,forum_id,thread),
           KEY forum_id (forum_id,thread,sub_type)
       ) $charset",

      "CREATE TABLE {$PHORUM['user_permissions_table']} (
           user_id                  int unsigned   NOT NULL default '0',
           forum_id                 int unsigned   NOT NULL default '0',
           permission               int unsigned   NOT NULL default '0',

           PRIMARY KEY  (user_id,forum_id),
           KEY forum_id (forum_id,permission)
       ) $charset",

      // When creating extra fields, then mind to update the file
      // include/api/custom_profile_fields.php script too (it contains a
      // list of reserved names for custom profile fields).
      "CREATE TABLE {$PHORUM['user_table']} (
           user_id                  int unsigned   NOT NULL auto_increment,
           username                 varchar(50)    NOT NULL default '',
           real_name                varchar(255)   NOT NULL default '',
           display_name             varchar(255)   NOT NULL default '',
           password                 varchar(50)    NOT NULL default '',
           password_temp            varchar(50)    NOT NULL default '',
           sessid_lt                varchar(50)    NOT NULL default '',
           sessid_st                varchar(50)    NOT NULL default '',
           sessid_st_timeout        int unsigned   NOT NULL default '0',
           email                    varchar(100)   NOT NULL default '',
           email_temp               varchar(110)   NOT NULL default '',
           hide_email               tinyint(1)     NOT NULL default '0',
           active                   tinyint(1)     NOT NULL default '0',
           signature                text           NOT NULL,
           threaded_list            tinyint(1)     NOT NULL default '0',
           posts                    int(10)        NOT NULL default '0',
           admin                    tinyint(1)     NOT NULL default '0',
           threaded_read            tinyint(1)     NOT NULL default '0',
           date_added               int unsigned   NOT NULL default '0',
           date_last_active         int unsigned   NOT NULL default '0',
           last_active_forum        int unsigned   NOT NULL default '0',
           hide_activity            tinyint(1)     NOT NULL default '0',
           show_signature           tinyint(1)     NOT NULL default '0',
           email_notify             tinyint(1)     NOT NULL default '0',
           pm_email_notify          tinyint(1)     NOT NULL default '1',
           tz_offset                float(4,2)     NOT NULL default '-99.00',
           is_dst                   tinyint(1)     NOT NULL default '0',
           user_language            varchar(100)   NOT NULL default '',
           user_template            varchar(100)   NOT NULL default '',
           moderator_data           text           NOT NULL,
           moderation_email         tinyint(1)     NOT NULL default '1',
           settings_data            mediumtext     NOT NULL,

           PRIMARY KEY (user_id),
           UNIQUE KEY username (username),
           KEY active (active),
           KEY userpass (username,password),
           KEY sessid_st (sessid_st),
           KEY sessid_lt (sessid_lt),
           KEY activity (date_last_active,hide_activity,last_active_forum),
           KEY date_added (date_added),
           KEY email_temp (email_temp)
       ) $charset",

      "CREATE TABLE {$PHORUM['user_newflags_table']} (
           user_id                  int unsigned   NOT NULL default '0',
           forum_id                 int unsigned   NOT NULL default '0',
           message_id               int unsigned   NOT NULL default '0',

           PRIMARY KEY  (user_id,forum_id,message_id),
           KEY move (message_id, forum_id)
       ) $charset",

      "CREATE TABLE {$PHORUM['groups_table']} (
           group_id                 int unsigned   NOT NULL auto_increment,
           name                     varchar(255)   NOT NULL default '',
           open                     tinyint(1)     NOT NULL default '0',

           PRIMARY KEY  (group_id)
       ) $charset",

      "CREATE TABLE {$PHORUM['forum_group_xref_table']} (
           forum_id                 int unsigned   NOT NULL default '0',
           group_id                 int unsigned   NOT NULL default '0',
           permission               int unsigned   NOT NULL default '0',

           PRIMARY KEY  (forum_id,group_id),
           KEY group_id (group_id)
       ) $charset",

      "CREATE TABLE {$PHORUM['user_group_xref_table']} (
           user_id                  int unsigned   NOT NULL default '0',
           group_id                 int unsigned   NOT NULL default '0',
           status                   tinyint(4)     NOT NULL default '1',

           PRIMARY KEY  (user_id,group_id)
       ) $charset",

      "CREATE TABLE {$PHORUM['files_table']} (
           file_id                  int unsigned   NOT NULL auto_increment,
           user_id                  int unsigned   NOT NULL default '0',
           filename                 varchar(255)   NOT NULL default '',
           filesize                 int unsigned   NOT NULL default '0',
           file_data                mediumtext     NOT NULL,
           add_datetime             int unsigned   NOT NULL default '0',
           message_id               int unsigned   NOT NULL default '0',
           link                     varchar(10)    NOT NULL default '',

           PRIMARY KEY (file_id),
           KEY add_datetime (add_datetime),
           KEY message_id_link (message_id,link),
           KEY user_id_link (user_id,link)
       ) $charset",

      "CREATE TABLE {$PHORUM['banlist_table']} (
           id                       int unsigned   NOT NULL auto_increment,
           forum_id                 int unsigned   NOT NULL default '0',
           type                     tinyint(4)     NOT NULL default '0',
           pcre                     tinyint(1)     NOT NULL default '0',
           string                   varchar(255)   NOT NULL default '',
           comments                 text           NOT NULL,

           PRIMARY KEY (id),
           KEY forum_id (forum_id)
       ) $charset",

      "CREATE TABLE {$PHORUM['search_table']} (
           message_id               int unsigned   NOT NULL default '0',
           forum_id                 int unsigned   NOT NULL default '0',
           search_text              mediumtext     NOT NULL,

           PRIMARY KEY (message_id),
           KEY forum_id (forum_id),
           FULLTEXT KEY search_text (search_text)
       ) TYPE=MyISAM $charset",

      "CREATE TABLE {$PHORUM['user_custom_fields_table']} (
           user_id                  int unsigned   NOT NULL default '0',
           type                     int unsigned   NOT NULL default '0',
           data                     text           NOT NULL,

           PRIMARY KEY (user_id, type)
       ) $charset",

      "CREATE TABLE {$PHORUM['pm_messages_table']} (
           pm_message_id            int unsigned   NOT NULL auto_increment,
           user_id                  int unsigned   NOT NULL default '0',
           author                   varchar(255)   NOT NULL default '',
           subject                  varchar(100)   NOT NULL default '',
           message                  text           NOT NULL,
           datestamp                int unsigned   NOT NULL default '0',
           meta                     mediumtext     NOT NULL,

           PRIMARY KEY (pm_message_id),
           KEY user_id (user_id)
       ) $charset",

      "CREATE TABLE {$PHORUM['pm_folders_table']} (
           pm_folder_id             int unsigned   NOT NULL auto_increment,
           user_id                  int unsigned   NOT NULL default '0',
           foldername               varchar(20)    NOT NULL default '',

           PRIMARY KEY (pm_folder_id)
       ) $charset",

      "CREATE TABLE {$PHORUM['pm_xref_table']} (
           pm_xref_id               int unsigned   NOT NULL auto_increment,
           user_id                  int unsigned   NOT NULL default '0',
           pm_folder_id             int unsigned   NOT NULL default '0',
           special_folder           varchar(10)        NULL default NULL,
           pm_message_id            int unsigned   NOT NULL default '0',
           read_flag                tinyint(1)     NOT NULL default '0',
           reply_flag               tinyint(1)     NOT NULL default '0',

           PRIMARY KEY (pm_xref_id),
           KEY xref (user_id,pm_folder_id,pm_message_id),
           KEY read_flag (read_flag)
       ) $charset",

      "CREATE TABLE {$PHORUM['pm_buddies_table']} (
           pm_buddy_id              int unsigned   NOT NULL auto_increment,
           user_id                  int unsigned   NOT NULL default '0',
           buddy_user_id            int unsigned   NOT NULL default '0',

           PRIMARY KEY pm_buddy_id (pm_buddy_id),
           UNIQUE KEY userids (user_id, buddy_user_id),
           KEY buddy_user_id (buddy_user_id)
       ) $charset",

      "CREATE TABLE {$PHORUM['message_tracking_table']} (
           track_id                 int unsigned   NOT NULL auto_increment,
           message_id               int unsigned   NOT NULL default '0',
           user_id                  int unsigned   NOT NULL default '0',
           time                     int unsigned   NOT NULL default '0',
           diff_body                text               NULL ,
           diff_subject             text               NULL ,

           PRIMARY KEY track_id (track_id),
           KEY message_id (message_id)
       ) $charset",
    );

    foreach ($create_table_queries as $sql) {
        $error = phorum_db_interact(DB_RETURN_ERROR, $sql, NULL, DB_MASTERQUERY);
        if ($error !== NULL) {
            return $error;
        }
    }

    return NULL;
}
// }}}

// {{{ Function: phorum_db_maxpacketsize()
/**
 * This function is used by the sanity checking system in the admin
 * interface to determine how much data can be transferred in one query.
 * This is used to detect problems with uploads that are larger than the
 * database server can handle. The function returns the size in bytes.
 * For database implementations which do not have this kind of limit,
 * NULL can be returned.
 *
 * @return integer
 *     The maximum packet size in bytes.
 */
function phorum_db_maxpacketsize()
{
    $maxsize = phorum_db_interact(
        DB_RETURN_VALUE,
        'SELECT @@global.max_allowed_packet',
        NULL,
        DB_MASTERQUERY
    );

    return $maxsize;
}
// }}}

// {{{ Function: phorum_db_sanitychecks()
/**
 * This function is used by the sanity checking system to let the
 * database layer do sanity checks of its own. This function can
 * be used by every database layer to implement specific checks.
 *
 * The return value for this function should be exactly the same
 * as the return value expected for regular sanity checking
 * function (see include/admin/sanity_checks.php for information).
 *
 * There's no need to load the sanity_check.php file for the needed
 * constants, because this function should only be called from the
 * sanity checking system.
 *
 * @return array
 *     A return value as expected by Phorum's sanity checking system.
 */
function phorum_db_sanitychecks()
{
    global $PHORUM;

    // For Phorum 5.2+, we need the "charset" option to be set
    // in the config.php.
    if (!isset($PHORUM['DBCONFIG']['charset'])) return array(
        PHORUM_SANITY_CRIT,
        "Database configuration parameter \"charset\" missing.",
        "The option \"charset\" is missing in your database configuration.
         This might indicate that you are using a config.php from an
         older Phorum version, which does not yet contain this option.
         Please, copy include/db/config.php.sample to
         include/db/config.php and edit this new config.php. Read
         Phorum's install.txt for installation instructions."
    );

    // Retrieve the MySQL server version.
    $version = phorum_db_interact(
        DB_RETURN_VALUE,
        'SELECT @@global.version',
        NULL,
        DB_MASTERQUERY
    );
    if (!$version) return array(
        PHORUM_SANITY_WARN,
        "The database layer could not retrieve the version of the
         running MySQL server",
        "This probably means that you are running a really old MySQL
         server, which does not support \"SELECT @@global.version\"
         as a SQL command. If you are not running a MySQL server
         with version 4.0.18 or higher, then please upgrade your
         MySQL server. Else, contact the Phorum developers to see
         where this warning is coming from"
    );

    // See if we recognize the version numbering.
    if (!preg_match('/^(\d+)\.(\d+)\.(\d+)/', $version, $ver)) return array(
        PHORUM_SANITY_WARN,
        "The database layer was unable to recognize the MySQL server's
         version number \"" . htmlspecialchars($version) . "\". Therefore,
         checking if the right version of MySQL is used is not possible.",
        "Contact the Phorum developers and report this specific
         version number, so the checking scripts can be updated."
    );

    // MySQL before version 4.
    if ($ver[1] < 4) return array(
        PHORUM_SANITY_CRIT,
        "The MySQL database server that is used is too old. The
         running version is \"" . htmlspecialchars($version) . "\",
         while MySQL version 4.0.18 or higher is recommended.",
        "Upgrade your MySQL server to a newer version. If your
         website is hosted with a service provider, please contact
         the service provider to upgrade your MySQL database."
    );

    // MySQL before version 4.0.18, with full text search enabled.
    if (isset($PHORUM['DBCONFIG']['mysql_use_ft']) &&
        $PHORUM['DBCONFIG']['mysql_use_ft'] &&
        $ver[2] == 4 && $ver[2] == 0 && $ver[3] < 18) return array(
        PHORUM_SANITY_WARN,
        "The MySQL database server that is used does not
         support all Phorum features. The running version is
         \"" . htmlspecialchars($version) . "\", while MySQL version
         4.0.18 or higher is recommended.",
        "Upgrade your MySQL server to a newer version. If your
         website is hosted with a service provider, please contact
         the service provider to upgrade your MySQL database."
    );

    // MySQL before version 5.0
    if ($ver[1] < 5) return array(
        PHORUM_SANITY_WARN,
        "The MySQL database server that is used does not
         support all Phorum features. The running version is
         \"" . htmlspecialchars($version) . "\", while MySQL version
         5.0 or higher is recommended. MySQL has discontinued active development
         for all versions below 5.0. The Phorum teams uses 5.0 for all
         development. Phorum has been known to work with MySQL 4.1 and some
         later 4.0 versions. However, there is no testing with these versions.
         It is recommended that all users upgrade to 5.0 as soon as possible
         to get the most out of MySQL and Phorum.",
        "Upgrade your MySQL server to a newer version. If your
         website is hosted with a service provider, please contact
         the service provider to upgrade your MySQL database."
    );

    // All checks are okay.
    return array (PHORUM_SANITY_OK, NULL);
}
// }}}

// ----------------------------------------------------------------------
// Load specific code for the required PHP database module.
// ----------------------------------------------------------------------

// PHP has support for MySQL connections through multiple extensions.
// If the config.php specifies a PHP database extension, then this one is
// used for loading the specific PHP database extension code. Otherwise,
// we try to auto-detect which one is available.

$ext = NULL;
// could be unset in Phorum < 5.2.7
if(!isset($PHORUM['DBCONFIG']['socket'])) {
    $PHORUM['DBCONFIG']['socket']=NULL;
}
if(!isset($PHORUM['DBCONFIG']['port'])) {
    $PHORUM['DBCONFIG']['port']=NULL;
}

if (isset($PHORUM['DBCONFIG']['mysql_php_extension'])) {
   $ext = basename($PHORUM['DBCONFIG']['mysql_php_extension']);
} elseif (function_exists('mysqli_connect')) {
   $ext = "mysqli";
} elseif (function_exists('mysql_connect')) {
   $ext = "mysql";

   // build the right hostname for the mysql extension
   // not having separate args for port and socket
   if(!empty($PHORUM['DBCONFIG']['socket'])) {
       $PHORUM['DBCONFIG']['server'].=":".$PHORUM['DBCONFIG']['socket'];
   } elseif(!empty($PHORUM['DBCONFIG']['port'])) {
       $PHORUM['DBCONFIG']['server'].=":".$PHORUM['DBCONFIG']['port'];
   }
} else {
   // Up to here, no PHP extension was found. This probably means that no
   // MySQL extension is loaded. Here we'll try to dynamically load an
   // extension ourselves.
   @dl("mysqli.so");
   if (function_exists('mysqli_connect')) {
       $ext = "mysqli";
   } else {
       @dl("mysql.so");
       if (function_exists('mysql_connect')) {
           $ext = "mysql";
       }
   }
}

// If we have no extension by now, we are very much out of luck.
if ($ext === NULL) trigger_error(
   "The Phorum MySQL database layer is unable to determine the PHP " .
   "MySQL extension to use. This might indicate that there is no " .
   "extension loaded from the php.ini.",
   E_USER_ERROR
);

// Load the specific code for the PHP extension that we use.
$extfile = "./include/db/mysql/{$ext}.php";
if (!file_exists($extfile)) trigger_error(
   "The Phorum MySQL database layer is unable to find the extension " .
   "file $extfile on the system. Check if all Phorum files are uploaded " .
   "and if you did specify the correct \"mysql_php_extension\" in the file " .
   "include/db/config.php (valid options are \"mysql\" and \"mysqli\").",
   E_USER_ERROR
);
include($extfile);

?>