/** * 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'); }
/** * {@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; }
/** * 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); while ($row = $this->db->sql_fetchrow($result)) { // 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++; } $this->db->sql_freeresult($result); if ($acquired_new_lock) { $this->db->sql_transaction('commit'); $this->lock->release(); } return $new_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; }
/** * 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; }
/** * 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; }
/** * 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 \src\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 src_update_rows_avoiding_duplicates_notify_status(\src\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; /** * 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->src_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]}"; } $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; }
/** * 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\n\t\t\t\tWHERE m.post_id = {$post_id}\n\t\t\t\t\tAND 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) . "\n\t\t\t\t\t\tAND 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); }