/include/db/mysql.php
PHP | 7740 lines | 4257 code | 857 blank | 2626 comment | 539 complexity | 20abd1693334d73630673cbe96199826 MD5 | raw file
Possible License(s): LGPL-2.1
Large files files are truncated, but you can click here to view the full file
- <?php
- ////////////////////////////////////////////////////////////////////////////////
- // //
- // Copyright (C) 2010 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 2010, 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(*)
- …
Large files files are truncated, but you can click here to view the full file