/** * Get the notification type id from the name * * @param string $notification_type_name The name * @return int the notification_type_id * @throws \phpbb\notification\exception */ public function get_notification_type_id($notification_type_name) { $notification_type_ids = $this->cache->get('notification_type_ids'); $this->db->sql_transaction('begin'); if ($notification_type_ids === false) { $notification_type_ids = array(); $sql = 'SELECT notification_type_id, notification_type_name FROM ' . $this->notification_types_table; $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { $notification_type_ids[$row['notification_type_name']] = (int) $row['notification_type_id']; } $this->db->sql_freeresult($result); $this->cache->put('notification_type_ids', $notification_type_ids); } if (!isset($notification_type_ids[$notification_type_name])) { if (!isset($this->notification_types[$notification_type_name]) && !isset($this->notification_types['notification.type.' . $notification_type_name])) { throw new \phpbb\notification\exception('NOTIFICATION_TYPE_NOT_EXIST', array($notification_type_name)); } $sql = 'INSERT INTO ' . $this->notification_types_table . ' ' . $this->db->sql_build_array('INSERT', array('notification_type_name' => $notification_type_name, 'notification_type_enabled' => 1)); $this->db->sql_query($sql); $notification_type_ids[$notification_type_name] = (int) $this->db->sql_nextid(); $this->cache->put('notification_type_ids', $notification_type_ids); } $this->db->sql_transaction('commit'); return $notification_type_ids[$notification_type_name]; }
/** * {@inheritDoc} */ public function move($group_id, $delta) { $delta = (int) $delta; if (!$delta) { return false; } $move_up = $delta > 0 ? true : false; $current_value = $this->get_group_value($group_id); if ($current_value != self::GROUP_DISABLED) { $this->db->sql_transaction('begin'); // First we move all groups between our current value and the target value up/down 1, // so we have a gap for our group to move. $sql = 'UPDATE ' . GROUPS_TABLE . ' SET group_legend = group_legend' . ($move_up ? ' + 1' : ' - 1') . ' WHERE group_legend > ' . self::GROUP_DISABLED . ' AND group_legend' . ($move_up ? ' >= ' : ' <= ') . ($current_value - $delta) . ' AND group_legend' . ($move_up ? ' < ' : ' > ') . $current_value; $this->db->sql_query($sql); // Because there might be fewer groups above/below the group than we wanted to move, // we use the number of changed groups, to update the group. $delta = (int) $this->db->sql_affectedrows(); if ($delta) { // And now finally, when we moved some other groups and built a gap, // we can move the desired group to it. $sql = 'UPDATE ' . GROUPS_TABLE . ' SET group_legend = group_legend ' . ($move_up ? ' - ' : ' + ') . $delta . ' WHERE group_id = ' . (int) $group_id; $this->db->sql_query($sql); $this->db->sql_transaction('commit'); return true; } $this->db->sql_transaction('commit'); } return false; }
/** * Delete category content * * @return array */ private function _delete_cat_content() { $this->db->sql_transaction('begin'); // Before we remove anything we make sure we are able to adjust the post counts later. ;) $sql = 'SELECT link_id, link_banner FROM ' . DIR_LINK_TABLE . ' WHERE link_cat = ' . (int) $this->cat_id; $result = $this->db->sql_query($sql); $link_ids = array(); while ($row = $this->db->sql_fetchrow($result)) { $link_ids[] = $row['link_id']; if ($row['link_banner'] && !preg_match('/^(http:\\/\\/|https:\\/\\/|ftp:\\/\\/|ftps:\\/\\/|www\\.).+/si', $row['link_banner'])) { $banner_img = $this->dir_helper->get_banner_path(basename($row['link_banner'])); if (file_exists($banner_img)) { @unlink($banner_img); } } } $this->db->sql_freeresult($result); if (sizeof($link_ids)) { // Delete links datas $link_datas_ary = array(DIR_COMMENT_TABLE => 'comment_link_id', DIR_VOTE_TABLE => 'vote_link_id'); foreach ($link_datas_ary as $table => $field) { $this->db->sql_query("DELETE FROM {$table} WHERE " . $this->db->sql_in_set($field, $link_ids)); } } // Delete cats datas $cat_datas_ary = array(DIR_LINK_TABLE => 'link_cat', DIR_WATCH_TABLE => 'cat_id'); foreach ($cat_datas_ary as $table => $field) { $this->db->sql_query("DELETE FROM {$table} WHERE {$field} = " . (int) $this->cat_id); } $this->db->sql_transaction('commit'); return array(); }
/** * Update the similar topics columns in the forums table * * @param string $column The name of the column to update * @param array $forum_ids An array of forum_ids */ protected function update_forum($column, $forum_ids) { $this->db->sql_transaction('begin'); // Set marked forums (in set) to 1 $sql = 'UPDATE ' . FORUMS_TABLE . "\n\t\t\tSET {$column} = 1\n\t\t\tWHERE " . $this->db->sql_in_set('forum_id', $forum_ids, false, true); $this->db->sql_query($sql); // Set unmarked forums (not in set) to 0 $sql = 'UPDATE ' . FORUMS_TABLE . "\n\t\t\tSET {$column} = 0\n\t\t\tWHERE " . $this->db->sql_in_set('forum_id', $forum_ids, true, true); $this->db->sql_query($sql); $this->db->sql_transaction('commit'); }
/** * Delete a comment * * @param string $link_id The link ID * @param string $comment_id The comment ID * @return null */ public function del($link_id, $comment_id) { global $request; $this->db->sql_transaction('begin'); $sql = 'DELETE FROM ' . DIR_COMMENT_TABLE . ' WHERE comment_id = ' . (int) $comment_id; $this->db->sql_query($sql); $sql = 'UPDATE ' . DIR_LINK_TABLE . ' SET link_comment = link_comment - 1 WHERE link_id = ' . (int) $link_id; $this->db->sql_query($sql); $this->db->sql_transaction('commit'); if ($request->is_ajax()) { $sql = 'SELECT COUNT(comment_id) AS nb_comments FROM ' . DIR_COMMENT_TABLE . ' WHERE comment_link_id = ' . (int) $link_id; $result = $this->db->sql_query($sql); $nb_comments = (int) $this->db->sql_fetchfield('nb_comments'); $this->db->sql_freeresult($result); $json_response = new \phpbb\json_response(); $json_response->send(array('success' => true, 'MESSAGE_TITLE' => $this->user->lang['INFORMATION'], 'MESSAGE_TEXT' => $this->user->lang['DIR_COMMENT_DELETE_OK'], 'COMMENT_ID' => $comment_id, 'TOTAL_COMMENTS' => $this->user->lang('DIR_NB_COMMS', $nb_comments))); } }
/** * Mass set configuration options: Receives an associative array, * treats array keys as configuration option names and associated * array values as their configuration option values. * * @param array $map Map from configuration names to values * * @return null */ public function set_array(array $map) { $this->db->sql_transaction('begin'); foreach ($map as $key => $value) { $sql = 'UPDATE ' . $this->table . "\n\t\t\t\tSET config_value = '" . $this->db->sql_escape($value) . "'\n\t\t\t\tWHERE config_name = '" . $this->db->sql_escape($key) . "'"; $result = $this->db->sql_query($sql); if (!$this->db->sql_affectedrows($result)) { $sql = 'INSERT INTO ' . $this->table . ' ' . $this->db->sql_build_array('INSERT', array('config_name' => (string) $key, 'config_value' => (string) $value)); $this->db->sql_query($sql); } } $this->db->sql_transaction('commit'); }
/** * Regenerate left/right ids from parent/child relationship * * This method regenerates the left/right ids for the tree based on * the parent/child relations. This function executes three queries per * item, so it should only be called, when the set has one of the following * problems: * - The set has a duplicated value inside the left/right id chain * - The set has a missing value inside the left/right id chain * - The set has items that do not have a left/right id set * * When regenerating the items, the items are sorted by parent id and their * current left id, so the current child/parent relationships are kept * and running the function on a working set will not change the order. * * @param int $new_id First left_id to be used (should start with 1) * @param int $parent_id parent_id of the current set (default = 0) * @param bool $reset_ids Should we reset all left_id/right_id on the first call? * @return int $new_id The next left_id/right_id that should be used */ public function regenerate_left_right_ids($new_id, $parent_id = 0, $reset_ids = false) { if ($acquired_new_lock = $this->acquire_lock()) { $this->db->sql_transaction('begin'); if (!$reset_ids) { $sql = 'UPDATE ' . $this->table_name . ' SET ' . $this->column_item_parents . " = ''\n\t\t\t\t\t" . $this->get_sql_where('WHERE'); $this->db->sql_query($sql); } } if ($reset_ids) { $sql = 'UPDATE ' . $this->table_name . ' SET ' . $this->db->sql_build_array('UPDATE', array($this->column_left_id => 0, $this->column_right_id => 0, $this->column_item_parents => '')) . ' ' . $this->get_sql_where('WHERE'); $this->db->sql_query($sql); } $sql = 'SELECT * FROM ' . $this->table_name . ' WHERE ' . $this->column_parent_id . ' = ' . (int) $parent_id . ' ' . $this->get_sql_where('AND') . ' ORDER BY ' . $this->column_left_id . ', ' . $this->column_item_id . ' ASC'; $result = $this->db->sql_query($sql); $rows = $this->db->sql_fetchrowset($result); $this->db->sql_freeresult($result); foreach ($rows as $row) { // First we update the left_id for this module if ($row[$this->column_left_id] != $new_id) { $sql = 'UPDATE ' . $this->table_name . ' SET ' . $this->db->sql_build_array('UPDATE', array($this->column_left_id => $new_id)) . ' WHERE ' . $this->column_item_id . ' = ' . (int) $row[$this->column_item_id]; $this->db->sql_query($sql); } $new_id++; // Then we go through any children and update their left/right id's $new_id = $this->regenerate_left_right_ids($new_id, $row[$this->column_item_id]); // Then we come back and update the right_id for this module if ($row[$this->column_right_id] != $new_id) { $sql = 'UPDATE ' . $this->table_name . ' SET ' . $this->db->sql_build_array('UPDATE', array($this->column_right_id => $new_id)) . ' WHERE ' . $this->column_item_id . ' = ' . (int) $row[$this->column_item_id]; $this->db->sql_query($sql); } $new_id++; } if ($acquired_new_lock) { $this->db->sql_transaction('commit'); $this->lock->release(); } return $new_id; }
/** * Store user input * * Event: core.acp_manage_forums_request_data */ public function acp_manage_forums_request_data($event) { $sort_topics_by = $this->request->variable('sk', $this->default_sort_by); $sort_topics_order = $this->request->variable('sd', $this->default_sort_order); $sort_topics_subforums = $this->request->variable('sort_topics_subforums', 0); $event['forum_data'] = array_merge($event['forum_data'], array('sort_topics_by' => $sort_topics_by, 'sort_topics_order' => $sort_topics_order)); // Apply this forum's sorting to all sub-forums if ($sort_topics_subforums) { $subforum_ids = array(); foreach (get_forum_branch($event['forum_data']['forum_id'], 'children', 'descending', false) as $subforum) { $subforum_ids[] = (int) $subforum['forum_id']; } if (!empty($subforum_ids)) { $this->db->sql_transaction('begin'); foreach ($subforum_ids as $subforum_id) { $sql_ary = 'UPDATE ' . FORUMS_TABLE . ' SET ' . sprintf("sort_topics_by = '%s', sort_topics_order = '%s'", $sort_topics_by, $sort_topics_order) . ' WHERE forum_id = ' . (int) $subforum_id; $this->db->sql_query($sql_ary); } $this->db->sql_transaction('commit'); } } }
/** * Install style * * @param array $style style data * @return int Style id */ protected function install_style($style) { // Generate row $sql_ary = array(); foreach ($style as $key => $value) { if ($key != 'style_id' && substr($key, 0, 1) != '_') { $sql_ary[$key] = $value; } } // Add to database $this->db->sql_transaction('begin'); $sql = 'INSERT INTO ' . STYLES_TABLE . ' ' . $this->db->sql_build_array('INSERT', $sql_ary); $this->db->sql_query($sql); $id = $this->db->sql_nextid(); $this->db->sql_transaction('commit'); add_log('admin', 'LOG_STYLE_ADD', $sql_ary['style_name']); return $id; }
/** * Wrapper for running queries to generate user feedback on updates * * @param string $sql SQL query to run on the database * @return mixed Query result from db->sql_query() */ protected function sql_query($sql) { $this->queries[] = $sql; $this->db->sql_return_on_error(true); if ($sql === 'begin') { $result = $this->db->sql_transaction('begin'); } else { if ($sql === 'commit') { $result = $this->db->sql_transaction('commit'); } else { $result = $this->db->sql_query($sql); if ($this->db->get_sql_error_triggered()) { $this->errors[] = array('sql' => $this->db->get_sql_error_sql(), 'code' => $this->db->get_sql_error_returned()); } } } $this->db->sql_return_on_error(false); return $result; }
/** * Re-clean user names * Only user names that are unclean will be re-cleaned * * @param int $start An offset index * @return bool|int Return the next offset index or true if all records have been processed. */ protected function reclean_usernames($start = 0) { $limit = 500; $i = 0; $this->db->sql_transaction('begin'); $sql = 'SELECT user_id, username, username_clean FROM ' . USERS_TABLE; $result = $this->db->sql_query_limit($sql, $limit, $start); while ($row = $this->db->sql_fetchrow($result)) { $i++; $username_clean = $this->db->sql_escape(utf8_clean_string($row['username'])); if ($username_clean != $row['username_clean']) { $sql = 'UPDATE ' . USERS_TABLE . "\n\t\t\t\t\tSET username_clean = '{$username_clean}'\n\t\t\t\t\tWHERE user_id = {$row['user_id']}"; $this->db->sql_query($sql); $this->processed++; } $this->progress->advance(); } $this->db->sql_freeresult($result); $this->db->sql_transaction('commit'); return $i < $limit ? true : $start + $i; }
/** * Add a vote in db, for a specifi link * * @param int $link_id Link_id from db * @return null */ public function add_vote($link_id) { $data = array('vote_link_id' => (int) $link_id, 'vote_user_id' => $this->user->data['user_id'], 'vote_note' => $this->request->variable('vote', 0)); $this->db->sql_transaction('begin'); $sql = 'INSERT INTO ' . DIR_VOTE_TABLE . ' ' . $this->db->sql_build_array('INSERT', $data); $this->db->sql_query($sql); $sql = 'UPDATE ' . DIR_LINK_TABLE . ' SET link_vote = link_vote + 1, link_note = link_note + ' . (int) $data['vote_note'] . ' WHERE link_id = ' . (int) $link_id; $this->db->sql_query($sql); $this->db->sql_transaction('commit'); if ($this->request->is_ajax()) { $sql = 'SELECT link_vote, link_note FROM ' . DIR_LINK_TABLE . ' WHERE link_id = ' . (int) $link_id; $result = $this->db->sql_query($sql); $data = $this->db->sql_fetchrow($result); $note = $this->display_note($data['link_note'], $data['link_vote'], true); $json_response = new \phpbb\json_response(); $json_response->send(array('success' => true, 'MESSAGE_TITLE' => $this->user->lang['INFORMATION'], 'MESSAGE_TEXT' => $this->user->lang['DIR_VOTE_OK'], 'NOTE' => $note, 'NB_VOTE' => $this->user->lang('DIR_NB_VOTES', (int) $data['link_vote']), 'LINK_ID' => $link_id)); } }
/** * Resynchronize the Custom BBCodes order field * (Based on Custom BBCode Sorting MOD by RMcGirr83) * * @return null * @access public */ public function resynchronize_bbcode_order() { $this->db->sql_transaction('begin'); // By default, check that order is valid and fix it if necessary $sql = 'SELECT bbcode_id, bbcode_order FROM ' . BBCODES_TABLE . ' ORDER BY bbcode_order, bbcode_id'; $result = $this->db->sql_query($sql); if ($row = $this->db->sql_fetchrow($result)) { $order = 0; do { // pre-increment $order ++$order; if ($row['bbcode_order'] != $order) { $sql = 'UPDATE ' . BBCODES_TABLE . "\n\t\t\t\t\t\tSET bbcode_order = {$order}\n\t\t\t\t\t\tWHERE bbcode_id = {$row['bbcode_id']}"; $this->db->sql_query($sql); } } while ($row = $this->db->sql_fetchrow($result)); } $this->db->sql_freeresult($result); $this->db->sql_transaction('commit'); }
/** * Updates wordlist and wordmatch tables when a message is posted or changed * * @param string $mode Contains the post mode: edit, post, reply, quote * @param int $post_id The id of the post which is modified/created * @param string &$message New or updated post content * @param string &$subject New or updated post subject * @param int $poster_id Post author's user id * @param int $forum_id The id of the forum in which the post is located */ public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id) { if (!$this->config['fulltext_native_load_upd']) { /** * The search indexer is disabled, return */ return; } // Split old and new post/subject to obtain array of 'words' $split_text = $this->split_message($message); $split_title = $this->split_message($subject); $cur_words = array('post' => array(), 'title' => array()); $words = array(); if ($mode == 'edit') { $words['add']['post'] = array(); $words['add']['title'] = array(); $words['del']['post'] = array(); $words['del']['title'] = array(); $sql = 'SELECT w.word_id, w.word_text, m.title_match FROM ' . SEARCH_WORDLIST_TABLE . ' w, ' . SEARCH_WORDMATCH_TABLE . " m WHERE m.post_id = $post_id AND w.word_id = m.word_id"; $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { $which = ($row['title_match']) ? 'title' : 'post'; $cur_words[$which][$row['word_text']] = $row['word_id']; } $this->db->sql_freeresult($result); $words['add']['post'] = array_diff($split_text, array_keys($cur_words['post'])); $words['add']['title'] = array_diff($split_title, array_keys($cur_words['title'])); $words['del']['post'] = array_diff(array_keys($cur_words['post']), $split_text); $words['del']['title'] = array_diff(array_keys($cur_words['title']), $split_title); } else { $words['add']['post'] = $split_text; $words['add']['title'] = $split_title; $words['del']['post'] = array(); $words['del']['title'] = array(); } unset($split_text); unset($split_title); // Get unique words from the above arrays $unique_add_words = array_unique(array_merge($words['add']['post'], $words['add']['title'])); // We now have unique arrays of all words to be added and removed and // individual arrays of added and removed words for text and title. What // we need to do now is add the new words (if they don't already exist) // and then add (or remove) matches between the words and this post if (sizeof($unique_add_words)) { $sql = 'SELECT word_id, word_text FROM ' . SEARCH_WORDLIST_TABLE . ' WHERE ' . $this->db->sql_in_set('word_text', $unique_add_words); $result = $this->db->sql_query($sql); $word_ids = array(); while ($row = $this->db->sql_fetchrow($result)) { $word_ids[$row['word_text']] = $row['word_id']; } $this->db->sql_freeresult($result); $new_words = array_diff($unique_add_words, array_keys($word_ids)); $this->db->sql_transaction('begin'); if (sizeof($new_words)) { $sql_ary = array(); foreach ($new_words as $word) { $sql_ary[] = array('word_text' => (string) $word, 'word_count' => 0); } $this->db->sql_return_on_error(true); $this->db->sql_multi_insert(SEARCH_WORDLIST_TABLE, $sql_ary); $this->db->sql_return_on_error(false); } unset($new_words, $sql_ary); } else { $this->db->sql_transaction('begin'); } // now update the search match table, remove links to removed words and add links to new words foreach ($words['del'] as $word_in => $word_ary) { $title_match = ($word_in == 'title') ? 1 : 0; if (sizeof($word_ary)) { $sql_in = array(); foreach ($word_ary as $word) { $sql_in[] = $cur_words[$word_in][$word]; } $sql = 'DELETE FROM ' . SEARCH_WORDMATCH_TABLE . ' WHERE ' . $this->db->sql_in_set('word_id', $sql_in) . ' AND post_id = ' . intval($post_id) . " AND title_match = $title_match"; $this->db->sql_query($sql); $sql = 'UPDATE ' . SEARCH_WORDLIST_TABLE . ' SET word_count = word_count - 1 WHERE ' . $this->db->sql_in_set('word_id', $sql_in) . ' AND word_count > 0'; $this->db->sql_query($sql); unset($sql_in); } } $this->db->sql_return_on_error(true); foreach ($words['add'] as $word_in => $word_ary) { $title_match = ($word_in == 'title') ? 1 : 0; if (sizeof($word_ary)) { $sql = 'INSERT INTO ' . SEARCH_WORDMATCH_TABLE . ' (post_id, word_id, title_match) SELECT ' . (int) $post_id . ', word_id, ' . (int) $title_match . ' FROM ' . SEARCH_WORDLIST_TABLE . ' WHERE ' . $this->db->sql_in_set('word_text', $word_ary); $this->db->sql_query($sql); $sql = 'UPDATE ' . SEARCH_WORDLIST_TABLE . ' SET word_count = word_count + 1 WHERE ' . $this->db->sql_in_set('word_text', $word_ary); $this->db->sql_query($sql); } } $this->db->sql_return_on_error(false); $this->db->sql_transaction('commit'); // destroy cached search results containing any of the words removed or added $this->destroy_cache(array_unique(array_merge($words['add']['post'], $words['add']['title'], $words['del']['post'], $words['del']['title'])), array($poster_id)); unset($unique_add_words); unset($words); unset($cur_words); }
/** * Performs a search on an author's posts without caring about message contents. Depends on display specific params * * @param string $type contains either posts or topics depending on what should be searched for * @param boolean $firstpost_only if true, only topic starting posts will be considered * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query * @param string $sort_key is the key of $sort_by_sql for the selected sorting * @param string $sort_dir is either a or d representing ASC and DESC * @param string $sort_days specifies the maximum amount of days a post may be old * @param array $ex_fid_ary specifies an array of forum ids which should not be searched * @param string $post_visibility specifies which types of posts the user can view in which forums * @param int $topic_id is set to 0 or a topic id, if it is not 0 then only posts in this topic should be searched * @param array $author_ary an array of author ids * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered * @param int $start indicates the first index of the page * @param int $per_page number of ids each page is supposed to contain * @return boolean|int total number of results */ public function author_search($type, $firstpost_only, $sort_by_sql, $sort_key, $sort_dir, $sort_days, $ex_fid_ary, $post_visibility, $topic_id, $author_ary, $author_name, &$id_ary, &$start, $per_page) { // No author? No posts if (!sizeof($author_ary)) { return 0; } // generate a search_key from all the options to identify the results $search_key_array = array('', $type, $firstpost_only ? 'firstpost' : '', '', '', $sort_days, $sort_key, $topic_id, implode(',', $ex_fid_ary), $post_visibility, implode(',', $author_ary), $author_name); /** * Allow changing the search_key for cached results * * @event core.search_postgres_by_author_modify_search_key * @var array search_key_array Array with search parameters to generate the search_key * @var string type Searching type ('posts', 'topics') * @var boolean firstpost_only Flag indicating if only topic starting posts are considered * @var int sort_days Time, in days, of the oldest possible post to list * @var string sort_key The sort type used from the possible sort types * @var int topic_id Limit the search to this topic_id only * @var array ex_fid_ary Which forums not to search on * @var string post_visibility Post visibility data * @var array author_ary Array of user_id containing the users to filter the results to * @var string author_name The username to search on * @since 3.1.7-RC1 */ $vars = array('search_key_array', 'type', 'firstpost_only', 'sort_days', 'sort_key', 'topic_id', 'ex_fid_ary', 'post_visibility', 'author_ary', 'author_name'); extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_by_author_modify_search_key', compact($vars))); $search_key = md5(implode('#', $search_key_array)); if ($start < 0) { $start = 0; } // try reading the results from cache $result_count = 0; if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) { return $result_count; } $id_ary = array(); // Create some display specific sql strings if ($author_name) { // first one matches post of registered users, second one guests and deleted users $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')'; } else { $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary); } $sql_fora = sizeof($ex_fid_ary) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; $sql_topic_id = $topic_id ? ' AND p.topic_id = ' . (int) $topic_id : ''; $sql_time = $sort_days ? ' AND p.post_time >= ' . (time() - $sort_days * 86400) : ''; $sql_firstpost = $firstpost_only ? ' AND p.post_id = t.topic_first_post_id' : ''; // Build sql strings for sorting $sql_sort = $sort_by_sql[$sort_key] . ($sort_dir == 'a' ? ' ASC' : ' DESC'); $sql_sort_table = $sql_sort_join = ''; switch ($sql_sort[0]) { case 'u': $sql_sort_table = USERS_TABLE . ' u, '; $sql_sort_join = $type == 'posts' ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster '; break; case 't': $sql_sort_table = $type == 'posts' && !$firstpost_only ? TOPICS_TABLE . ' t, ' : ''; $sql_sort_join = $type == 'posts' && !$firstpost_only ? ' AND t.topic_id = p.topic_id ' : ''; break; case 'f': $sql_sort_table = FORUMS_TABLE . ' f, '; $sql_sort_join = ' AND f.forum_id = p.forum_id '; break; } $m_approve_fid_sql = ' AND ' . $post_visibility; /** * Allow changing the query used to search for posts by author in fulltext_postgres * * @event core.search_postgres_author_count_query_before * @var int result_count The previous result count for the format of the query. * Set to 0 to force a re-count * @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen * @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table * @var array author_ary Array of user_id containing the users to filter the results to * @var string author_name An extra username to search on * @var string sql_author SQL WHERE condition for the post author ids * @var int topic_id Limit the search to this topic_id only * @var string sql_topic_id SQL of topic_id * @var string sort_by_sql The possible predefined sort types * @var string sort_key The sort type used from the possible sort types * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir * @var string sort_days Time, in days, that the oldest post showing can have * @var string sql_time The SQL to search on the time specifyed by sort_days * @var bool firstpost_only Wether or not to search only on the first post of the topics * @var array ex_fid_ary Forum ids that must not be searched on * @var array sql_fora SQL query for ex_fid_ary * @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions * @var int start How many posts to skip in the search results (used for pagination) * @since 3.1.5-RC1 */ $vars = array('result_count', 'sql_sort_table', 'sql_sort_join', 'author_ary', 'author_name', 'sql_author', 'topic_id', 'sql_topic_id', 'sort_by_sql', 'sort_key', 'sort_dir', 'sql_sort', 'sort_days', 'sql_time', 'firstpost_only', 'ex_fid_ary', 'sql_fora', 'm_approve_fid_sql', 'start'); extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_author_count_query_before', compact($vars))); // Build the query for really selecting the post_ids if ($type == 'posts') { $sql = "SELECT p.post_id\n\t\t\t\tFROM " . $sql_sort_table . POSTS_TABLE . ' p' . ($firstpost_only ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "\n\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t{$sql_time}\n\t\t\t\tORDER BY {$sql_sort}"; $field = 'post_id'; } else { $sql = "SELECT t.topic_id\n\t\t\t\tFROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p\n\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t{$sql_fora}\n\t\t\t\t\tAND t.topic_id = p.topic_id\n\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t{$sql_time}\n\t\t\t\tGROUP BY t.topic_id, {$sort_by_sql[$sort_key]}\n\t\t\t\tORDER BY {$sql_sort}"; $field = 'topic_id'; } $this->db->sql_transaction('begin'); // Only read one block of posts from the db and then cache it $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); while ($row = $this->db->sql_fetchrow($result)) { $id_ary[] = $row[$field]; } $this->db->sql_freeresult($result); // retrieve the total result count if needed if (!$result_count) { if ($type == 'posts') { $sql_count = "SELECT COUNT(*) as result_count\n\t\t\t\t\tFROM " . $sql_sort_table . POSTS_TABLE . ' p' . ($firstpost_only ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "\n\t\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t\t{$sql_time}"; } else { $sql_count = "SELECT COUNT(*) as result_count\n\t\t\t\t\tFROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p\n\t\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t\tAND t.topic_id = p.topic_id\n\t\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t\t{$sql_time}\n\t\t\t\t\tGROUP BY t.topic_id, {$sort_by_sql[$sort_key]}"; } $this->db->sql_query($sql_count); $result_count = (int) $this->db->sql_fetchfield('result_count'); if (!$result_count) { return false; } } $this->db->sql_transaction('commit'); if ($start >= $result_count) { $start = floor(($result_count - 1) / $per_page) * $per_page; $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); while ($row = $this->db->sql_fetchrow($result)) { $id_ary[] = (int) $row[$field]; } $this->db->sql_freeresult($result); $id_ary = array_unique($id_ary); } if (sizeof($id_ary)) { $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir); $id_ary = array_slice($id_ary, 0, $per_page); return $result_count; } return false; }
/** * Moves an item up/down * * @param int $teampage_id teampage_id of the item to be moved * @param int $delta number of steps: * - positive = move up * - negative = move down * @return bool True if the group was moved successfully */ public function move_teampage($teampage_id, $delta) { $delta = (int) $delta; if (!$delta) { return false; } $move_up = $delta > 0 ? true : false; $data = $this->get_teampage_values($teampage_id); $current_value = (int) $data['teampage_position']; if ($current_value != self::GROUP_DISABLED) { $this->db->sql_transaction('begin'); if (!$move_up && $data['teampage_parent'] == self::NO_PARENT) { // If we move items down, we need to grab the one sibling more, // so we do not ignore the children of the previous sibling. // We will remove the additional sibling later on. $delta = abs($delta) + 1; } $sql = 'SELECT teampage_id, teampage_position FROM ' . TEAMPAGE_TABLE . ' WHERE teampage_parent = ' . (int) $data['teampage_parent'] . ' AND teampage_position' . ($move_up ? ' < ' : ' > ') . $current_value . ' ORDER BY teampage_position' . ($move_up ? ' DESC' : ' ASC'); $result = $this->db->sql_query_limit($sql, $delta); $sibling_count = 0; $sibling_limit = $delta; // Reset the delta, as we recalculate the new real delta $delta = 0; while ($row = $this->db->sql_fetchrow($result)) { $sibling_count++; $delta = $current_value - $row['teampage_position']; // Remove the additional sibling we added previously // But only, if we included it, this is not be the case // when we reached the end of our list if (!$move_up && $data['teampage_parent'] == self::NO_PARENT && $sibling_count == $sibling_limit) { $delta++; } } $this->db->sql_freeresult($result); if ($delta) { $sql = 'SELECT COUNT(teampage_id) as num_items FROM ' . TEAMPAGE_TABLE . ' WHERE teampage_id = ' . (int) $teampage_id . ' OR teampage_parent = ' . (int) $teampage_id; $result = $this->db->sql_query($sql); $num_items = (int) $this->db->sql_fetchfield('num_items'); $this->db->sql_freeresult($result); // First we move all items between our current value and the target value up/down 1, // so we have a gap for our item to move. $sql = 'UPDATE ' . TEAMPAGE_TABLE . ' SET teampage_position = teampage_position' . ($move_up ? ' + ' : ' - ') . $num_items . ' WHERE teampage_position' . ($move_up ? ' >= ' : ' <= ') . ($current_value - $delta) . ' AND teampage_position' . ($move_up ? ' < ' : ' > ') . $current_value . ' AND NOT (teampage_id = ' . (int) $teampage_id . ' OR teampage_parent = ' . (int) $teampage_id . ')'; $this->db->sql_query($sql); $delta = !$move_up && $data['teampage_parent'] == self::NO_PARENT ? abs($delta) - ($num_items - 1) : abs($delta); // And now finally, when we moved some other items and built a gap, // we can move the desired item to it. $sql = 'UPDATE ' . TEAMPAGE_TABLE . ' SET teampage_position = teampage_position ' . ($move_up ? ' - ' : ' + ') . $delta . ' WHERE teampage_id = ' . (int) $teampage_id . ' OR teampage_parent = ' . (int) $teampage_id; $this->db->sql_query($sql); $this->db->sql_transaction('commit'); $this->cache->destroy('sql', TEAMPAGE_TABLE); return true; } $this->db->sql_transaction('commit'); } $this->cache->destroy('sql', TEAMPAGE_TABLE); return false; }
/** * Event: core.mcp_lock_unlock_after */ public function mcp_lock_unlock_after($event) { if ($event['action'] == 'lock') { $topic_ids = $event['ids']; $topic_id = (int) current($topic_ids); $topic_data = $event['data']; $forum_id = (int) $topic_data[$topic_id]['forum_id']; $to_forum_id = (int) $topic_data[$topic_id]['move_topics_to']; $forum_data = phpbb_get_forum_data($to_forum_id); $to_forum_name = $forum_data[$to_forum_id]['forum_name']; $topics_moved = $topics_moved_unapproved = $topics_moved_softdeleted = 0; foreach ($topic_data as $topic_id => $topic_info) { if ($topic_info['topic_visibility'] == ITEM_APPROVED) { $topics_moved++; } else { if ($topic_info['topic_visibility'] == ITEM_UNAPPROVED || $topic_info['topic_visibility'] == ITEM_REAPPROVE) { $topics_moved_unapproved++; } else { if ($topic_info['topic_visibility'] == ITEM_DELETED) { $topics_moved_softdeleted++; } } } } $this->db->sql_transaction('begin'); // Move topics, but do not resync yet if (!function_exists('move_topics')) { include $this->root_path . 'includes/functions_admin.' . $this->php_ext; } move_topics($topic_ids, $to_forum_id, false); foreach ($topic_data as $topic_id => $row) { // We add the $to_forum_id twice, because 'forum_id' is updated // when the topic is moved again later. $this->log->add('mod', $this->user->data['user_id'], $this->user->ip, 'LOG_MOVED_LOCKED_TOPIC', false, array('forum_id' => $to_forum_id, 'topic_id' => $topic_id, $row['topic_title'], $row['forum_name'], $to_forum_name)); } unset($topic_data); $sync_sql = array(); if ($topics_moved) { $sync_sql[$to_forum_id][] = 'forum_topics_approved = forum_topics_approved + ' . (int) $topics_moved; if ($topics_moved > 0) { $sync_sql[$forum_id][] = 'forum_topics_approved = forum_topics_approved - ' . (int) $topics_moved; } } if ($topics_moved_unapproved) { $sync_sql[$to_forum_id][] = 'forum_topics_unapproved = forum_topics_unapproved + ' . (int) $topics_moved_unapproved; $sync_sql[$forum_id][] = 'forum_topics_unapproved = forum_topics_unapproved - ' . (int) $topics_moved_unapproved; } if ($topics_moved_softdeleted) { $sync_sql[$to_forum_id][] = 'forum_topics_softdeleted = forum_topics_softdeleted + ' . (int) $topics_moved_softdeleted; $sync_sql[$forum_id][] = 'forum_topics_softdeleted = forum_topics_softdeleted - ' . (int) $topics_moved_softdeleted; } foreach ($sync_sql as $forum_id_key => $array) { $sql = 'UPDATE ' . FORUMS_TABLE . ' SET ' . implode(', ', $array) . ' WHERE forum_id = ' . $forum_id_key; $this->db->sql_query($sql); } $this->db->sql_transaction('commit'); sync('forum', 'forum_id', array($forum_id, $to_forum_id)); } }
/** * Moves topics to a new forum after they have been locked * * @param array $topic_data * @param string $action */ public function move_topics($topic_data, $action) { $first_topic_data = reset($topic_data); $is_enabled = (int) $first_topic_data[$action]; $to_forum_id = (int) $first_topic_data['move_topics_when_locked_to']; /** * This event allows you to perform additional actions before locked topics are moved. * * @event kasimi.movetopicswhenlocked.move_topics_before * @var array topic_data Array with general topic data * @var string action Who triggered the forums to be moved, one of move_topics_when_locked|move_topics_when_locked_solved * @var int is_enabled Whether or not the forum's ACP settings specify the topics to be moved * @var int to_forum_id The destination forum * @since 1.0.2 */ $vars = array('topic_data', 'action', 'is_enabled', 'to_forum_id'); extract($this->dispatcher->trigger_event('kasimi.movetopicswhenlocked.move_topics_before', compact($vars))); // Forum settings are set to not move the topics if (!$is_enabled || !$to_forum_id) { return; } $forum_id = (int) $first_topic_data['forum_id']; // The topics are already in the destination forum if ($forum_id == $to_forum_id) { return; } if (!function_exists('phpbb_get_forum_data')) { include $this->root_path . 'includes/functions_mcp.' . $this->php_ext; } $to_forum_data = phpbb_get_forum_data($to_forum_id, 'f_post'); // The destination forum does not exist if (empty($to_forum_data)) { return; } // The following code is taken from the mcp_move_topic() function in /includes/mpc/mcp_main.php $topics_moved = $topics_moved_unapproved = $topics_moved_softdeleted = 0; $posts_moved = $posts_moved_unapproved = $posts_moved_softdeleted = 0; foreach ($topic_data as $topic_id => $topic_info) { if ($topic_info['topic_visibility'] == ITEM_APPROVED) { $topics_moved++; } else { if ($topic_info['topic_visibility'] == ITEM_UNAPPROVED || $topic_info['topic_visibility'] == ITEM_REAPPROVE) { $topics_moved_unapproved++; } else { if ($topic_info['topic_visibility'] == ITEM_DELETED) { $topics_moved_softdeleted++; } } } $posts_moved += $topic_info['topic_posts_approved']; $posts_moved_unapproved += $topic_info['topic_posts_unapproved']; $posts_moved_softdeleted += $topic_info['topic_posts_softdeleted']; } $this->db->sql_transaction('begin'); if (!function_exists('move_topics')) { include $this->root_path . 'includes/functions_admin.' . $this->php_ext; } // Move topics, but do not resync yet move_topics(array_keys($topic_data), $to_forum_id, false); foreach ($topic_data as $topic_id => $row) { // We add the $to_forum_id twice, because 'forum_id' is updated // when the topic is moved again later. $this->log->add('mod', $this->user->data['user_id'], $this->user->ip, 'LOG_MOVED_LOCKED_TOPIC', false, array('forum_id' => (int) $to_forum_id, 'topic_id' => (int) $topic_id, $row['topic_title'], $row['forum_name'], $to_forum_data[$to_forum_id]['forum_name'])); } $sync_sql = array(); if ($posts_moved) { $sync_sql[$to_forum_id][] = 'forum_posts_approved = forum_posts_approved + ' . (int) $posts_moved; $sync_sql[$forum_id][] = 'forum_posts_approved = forum_posts_approved - ' . (int) $posts_moved; } if ($posts_moved_unapproved) { $sync_sql[$to_forum_id][] = 'forum_posts_unapproved = forum_posts_unapproved + ' . (int) $posts_moved_unapproved; $sync_sql[$forum_id][] = 'forum_posts_unapproved = forum_posts_unapproved - ' . (int) $posts_moved_unapproved; } if ($posts_moved_softdeleted) { $sync_sql[$to_forum_id][] = 'forum_posts_softdeleted = forum_posts_softdeleted + ' . (int) $posts_moved_softdeleted; $sync_sql[$forum_id][] = 'forum_posts_softdeleted = forum_posts_softdeleted - ' . (int) $posts_moved_softdeleted; } if ($topics_moved) { $sync_sql[$to_forum_id][] = 'forum_topics_approved = forum_topics_approved + ' . (int) $topics_moved; if ($topics_moved > 0) { $sync_sql[$forum_id][] = 'forum_topics_approved = forum_topics_approved - ' . (int) $topics_moved; } } if ($topics_moved_unapproved) { $sync_sql[$to_forum_id][] = 'forum_topics_unapproved = forum_topics_unapproved + ' . (int) $topics_moved_unapproved; $sync_sql[$forum_id][] = 'forum_topics_unapproved = forum_topics_unapproved - ' . (int) $topics_moved_unapproved; } if ($topics_moved_softdeleted) { $sync_sql[$to_forum_id][] = 'forum_topics_softdeleted = forum_topics_softdeleted + ' . (int) $topics_moved_softdeleted; $sync_sql[$forum_id][] = 'forum_topics_softdeleted = forum_topics_softdeleted - ' . (int) $topics_moved_softdeleted; } foreach ($sync_sql as $forum_id_key => $array) { $sql = 'UPDATE ' . FORUMS_TABLE . ' SET ' . implode(', ', $array) . ' WHERE forum_id = ' . (int) $forum_id_key; $this->db->sql_query($sql); } $this->db->sql_transaction('commit'); sync('forum', 'forum_id', array($forum_id, $to_forum_id)); /** * This event allows you to perform additional actions after locked topics have been moved. * * @event kasimi.movetopicswhenlocked.move_topics_after * @var array topic_data Array with general topic data * @var string action Who triggered the forums to be moved, one of move_topics_when_locked|move_topics_when_locked_solved * @var int to_forum_id The destination forum * @var int topics_moved Number of moved topics * @var int topics_moved_unapproved Number of moved unapproved topics * @var int topics_moved_softdeleted Number of moved soft-deleted topics * @var int posts_moved Number of moved posts * @var int posts_moved_unapproved Number of moved unapproved posts * @var int posts_moved_softdeleted Number of moved soft-deleted posts * @since 1.0.2 */ $vars = array('topic_data', 'action', 'to_forum_id', 'topics_moved', 'topics_moved_unapproved', 'topics_moved_softdeleted', 'posts_moved', 'posts_moved_unapproved', 'posts_moved_softdeleted'); extract($this->dispatcher->trigger_event('kasimi.movetopicswhenlocked.move_topics_after', compact($vars))); unset($topic_data); }
/** * Updates rows in given table from a set of values to a new value. * If this results in rows violating uniqueness constraints, the duplicate * rows are merged respecting notify_status (0 takes precedence over 1). * * The only supported table is topics_watch. * * @param \phpbb\db\driver\driver_interface $db Database object * @param string $table Table on which to perform the update * @param string $column Column whose values to change * @param array $from_values An array of values that should be changed * @param int $to_value The new value * @return null */ function phpbb_update_rows_avoiding_duplicates_notify_status(\phpbb\db\driver\driver_interface $db, $table, $column, $from_values, $to_value) { $sql = "SELECT {$column}, user_id, notify_status\n\t\tFROM {$table}\n\t\tWHERE " . $db->sql_in_set($column, $from_values); $result = $db->sql_query($sql); $old_user_ids = array(); while ($row = $db->sql_fetchrow($result)) { $old_user_ids[(int) $row['notify_status']][$row[$column]][] = (int) $row['user_id']; } $db->sql_freeresult($result); $sql = "SELECT {$column}, user_id\n\t\tFROM {$table}\n\t\tWHERE {$column} = " . (int) $to_value; $result = $db->sql_query($sql); $new_user_ids = array(); while ($row = $db->sql_fetchrow($result)) { $new_user_ids[$row[$column]][] = (int) $row['user_id']; } $db->sql_freeresult($result); $queries = array(); $extra_updates = array(0 => 'notify_status = 0', 1 => ''); foreach ($from_values as $from_value) { foreach ($extra_updates as $notify_status => $extra_update) { if (!isset($old_user_ids[$notify_status][$from_value])) { continue; } if (empty($new_user_ids)) { $sql = "UPDATE {$table}\n\t\t\t\t\tSET {$column} = " . (int) $to_value . "\n\t\t\t\t\tWHERE {$column} = '" . $db->sql_escape($from_value) . "'"; $queries[] = $sql; } else { $different_user_ids = array_diff($old_user_ids[$notify_status][$from_value], $new_user_ids[$to_value]); if (!empty($different_user_ids)) { $sql = "UPDATE {$table}\n\t\t\t\t\t\tSET {$column} = " . (int) $to_value . "\n\t\t\t\t\t\tWHERE {$column} = '" . $db->sql_escape($from_value) . "'\n\t\t\t\t\t\tAND " . $db->sql_in_set('user_id', $different_user_ids); $queries[] = $sql; } if ($extra_update) { $same_user_ids = array_diff($old_user_ids[$notify_status][$from_value], $different_user_ids); if (!empty($same_user_ids)) { $sql = "UPDATE {$table}\n\t\t\t\t\t\t\tSET {$extra_update}\n\t\t\t\t\t\t\tWHERE {$column} = '" . (int) $to_value . "'\n\t\t\t\t\t\t\tAND " . $db->sql_in_set('user_id', $same_user_ids); $queries[] = $sql; } } } } } if (!empty($queries)) { $db->sql_transaction('begin'); foreach ($queries as $sql) { $db->sql_query($sql); } $sql = "DELETE FROM {$table}\n\t\t\tWHERE " . $db->sql_in_set($column, $from_values); $db->sql_query($sql); $db->sql_transaction('commit'); } }
/** * Performs a search on an author's posts without caring about message contents. Depends on display specific params * * @param string $type contains either posts or topics depending on what should be searched for * @param boolean $firstpost_only if true, only topic starting posts will be considered * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query * @param string $sort_key is the key of $sort_by_sql for the selected sorting * @param string $sort_dir is either a or d representing ASC and DESC * @param string $sort_days specifies the maximum amount of days a post may be old * @param array $ex_fid_ary specifies an array of forum ids which should not be searched * @param string $post_visibility specifies which types of posts the user can view in which forums * @param int $topic_id is set to 0 or a topic id, if it is not 0 then only posts in this topic should be searched * @param array $author_ary an array of author ids * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered * @param int $start indicates the first index of the page * @param int $per_page number of ids each page is supposed to contain * @return boolean|int total number of results */ public function author_search($type, $firstpost_only, $sort_by_sql, $sort_key, $sort_dir, $sort_days, $ex_fid_ary, $post_visibility, $topic_id, $author_ary, $author_name, &$id_ary, &$start, $per_page) { // No author? No posts if (!sizeof($author_ary)) { return 0; } // generate a search_key from all the options to identify the results $search_key = md5(implode('#', array('', $type, $firstpost_only ? 'firstpost' : '', '', '', $sort_days, $sort_key, $topic_id, implode(',', $ex_fid_ary), $post_visibility, implode(',', $author_ary), $author_name))); if ($start < 0) { $start = 0; } // try reading the results from cache $result_count = 0; if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) { return $result_count; } $id_ary = array(); // Create some display specific sql strings if ($author_name) { // first one matches post of registered users, second one guests and deleted users $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')'; } else { $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary); } $sql_fora = sizeof($ex_fid_ary) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; $sql_topic_id = $topic_id ? ' AND p.topic_id = ' . (int) $topic_id : ''; $sql_time = $sort_days ? ' AND p.post_time >= ' . (time() - $sort_days * 86400) : ''; $sql_firstpost = $firstpost_only ? ' AND p.post_id = t.topic_first_post_id' : ''; // Build sql strings for sorting $sql_sort = $sort_by_sql[$sort_key] . ($sort_dir == 'a' ? ' ASC' : ' DESC'); $sql_sort_table = $sql_sort_join = ''; switch ($sql_sort[0]) { case 'u': $sql_sort_table = USERS_TABLE . ' u, '; $sql_sort_join = $type == 'posts' ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster '; break; case 't': $sql_sort_table = $type == 'posts' && !$firstpost_only ? TOPICS_TABLE . ' t, ' : ''; $sql_sort_join = $type == 'posts' && !$firstpost_only ? ' AND t.topic_id = p.topic_id ' : ''; break; case 'f': $sql_sort_table = FORUMS_TABLE . ' f, '; $sql_sort_join = ' AND f.forum_id = p.forum_id '; break; } $m_approve_fid_sql = ' AND ' . $post_visibility; // Build the query for really selecting the post_ids if ($type == 'posts') { $sql = "SELECT p.post_id\n\t\t\t\tFROM " . $sql_sort_table . POSTS_TABLE . ' p' . ($firstpost_only ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "\n\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t{$sql_time}\n\t\t\t\tORDER BY {$sql_sort}"; $field = 'post_id'; } else { $sql = "SELECT t.topic_id\n\t\t\t\tFROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p\n\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t{$sql_fora}\n\t\t\t\t\tAND t.topic_id = p.topic_id\n\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t{$sql_time}\n\t\t\t\tGROUP BY t.topic_id, {$sort_by_sql[$sort_key]}\n\t\t\t\tORDER BY {$sql_sort}"; $field = 'topic_id'; } $this->db->sql_transaction('begin'); // Only read one block of posts from the db and then cache it $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); while ($row = $this->db->sql_fetchrow($result)) { $id_ary[] = $row[$field]; } $this->db->sql_freeresult($result); // retrieve the total result count if needed if (!$result_count) { if ($type == 'posts') { $sql_count = "SELECT COUNT(*) as result_count\n\t\t\t\t\tFROM " . $sql_sort_table . POSTS_TABLE . ' p' . ($firstpost_only ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "\n\t\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t\t{$sql_time}"; } else { $sql_count = "SELECT COUNT(*) as result_count\n\t\t\t\t\tFROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p\n\t\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t\tAND t.topic_id = p.topic_id\n\t\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t\t{$sql_time}\n\t\t\t\t\tGROUP BY t.topic_id, {$sort_by_sql[$sort_key]}"; } $result = $this->db->sql_query($sql_count); $result_count = (int) $this->db->sql_fetchfield('result_count'); if (!$result_count) { return false; } } $this->db->sql_transaction('commit'); if ($start >= $result_count) { $start = floor(($result_count - 1) / $per_page) * $per_page; $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); while ($row = $this->db->sql_fetchrow($result)) { $id_ary[] = (int) $row[$field]; } $this->db->sql_freeresult($result); $id_ary = array_unique($id_ary); } if (sizeof($id_ary)) { $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir); $id_ary = array_slice($id_ary, 0, $per_page); return $result_count; } return false; }
/** * The main cron task code. */ public function cron_tidy_warnings($topic_ids = array()) { $warning_list = $user_list = $unban_list = array(); $current_time = time(); $sql = 'SELECT * FROM ' . WARNINGS_TABLE . "\n\t\t\tWHERE warning_end < {$current_time} \n\t\t\tAND warning_end > 0 \n\t\t\tAND warning_status = 1"; $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { $warning_list[] = $row['warning_id']; $user_list[$row['user_id']] = isset($user_list[$row['user_id']]) ? ++$user_list[$row['user_id']] : 1; } $this->db->sql_freeresult($result); if (sizeof($warning_list)) { $this->db->sql_transaction('begin'); $sql = 'UPDATE ' . WARNINGS_TABLE . ' SET warning_status = 0 WHERE ' . $this->db->sql_in_set('warning_id', $warning_list); $this->db->sql_query($sql); foreach ($user_list as $user_id => $value) { $sql = 'UPDATE ' . USERS_TABLE . " SET user_warnings = user_warnings - {$value}\n\t\t\t\t\tWHERE user_id = {$user_id}"; $this->db->sql_query($sql); } // Try to get storage engine type to detect if transactions are supported // to apply proper bans selection (MyISAM/InnoDB) $operator = '<'; /* Comment out this part of code for now as get_table_status() * as unavailable for \phpbb\db\driver\driver_interface if (strpos($this->db->get_sql_layer(), 'mysql') !== false) { $table_status = $this->db->get_table_status(USERS_TABLE); if (isset($table_status['Engine'])) { $operator = ($table_status['Engine'] === 'MyISAM') ? '<' : '<='; } } */ $sql = 'SELECT u.user_id, b.ban_id FROM ' . USERS_TABLE . ' u, ' . BANLIST_TABLE . " b\n\t\t\t\tWHERE u.user_ban_id = 1\n\t\t\t\t\tAND u.user_warnings {$operator} " . (int) $this->config['warnings_for_ban'] . ' AND u.user_id = b.ban_userid'; $result = $this->db->sql_query($sql); while ($row = $this->db->sql_fetchrow($result)) { $unban_list[(int) $row['user_id']] = (int) $row['ban_id']; } $this->db->sql_freeresult($result); if (sizeof($unban_list)) { $sql = 'UPDATE ' . USERS_TABLE . ' SET user_ban_id = 0 WHERE ' . $this->db->sql_in_set('user_id', array_keys($unban_list)); $this->db->sql_query($sql); $sql = 'SELECT u.username AS unban_info, u.user_id FROM ' . USERS_TABLE . ' u, ' . BANLIST_TABLE . ' b WHERE ' . $this->db->sql_in_set('b.ban_id', $unban_list) . ' AND u.user_id = b.ban_userid'; $result = $this->db->sql_query($sql); $l_unban_list = ''; $user_ids_ary = array(); while ($row = $this->db->sql_fetchrow($result)) { $l_unban_list .= ($l_unban_list != '' ? ', ' : '') . $row['unban_info']; $user_ids_ary[] = $row['user_id']; } $this->db->sql_freeresult($result); $sql = 'DELETE FROM ' . BANLIST_TABLE . ' WHERE ' . $this->db->sql_in_set('ban_id', $unban_list); $this->db->sql_query($sql); // Add to moderator log, admin log and user notes $this->phpbb_log->add('admin', $this->user->data['user_id'], $this->user->ip, 'LOG_UNBAN_USER', false, $l_unban_list); $this->phpbb_log->add('mod', 0, 0, 'LOG_UNBAN_USER', false, $l_unban_list); foreach ($user_ids_ary as $user_id) { $this->phpbb_log->add('user', $user_id, 0, 'LOG_UNBAN_USER', false, $l_unban_list); } } $this->db->sql_transaction('commit'); } $this->cache->destroy('sql', array(WARNINGS_TABLE, BANLIST_TABLE)); $this->config->set('warnings_last_gc', time(), true); }