/**
* Load a user by username
*
* Stores the full data in the user cache so they do not need to be loaded again
* Returns the user id so you may use get_user() from the returned value
*
* @param string $username Raw username to load (will be cleaned)
* @return int User ID for the username
*/
public function load_user_by_username($username)
{
$sql = 'SELECT *
FROM ' . $this->users_table . "\n\t\t\tWHERE username_clean = '" . $this->db->sql_escape(utf8_clean_string($username)) . "'";
$result = $this->db->sql_query($sql);
$row = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
if ($row) {
$this->users[$row['user_id']] = $row;
return $row['user_id'];
}
return ANONYMOUS;
}
/**
* 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');
}
/**
* Get basic data of all parent items
*
* Basic data is defined in the $item_basic_data property.
* Data is cached in the item_parents column in the item table
*
* @param array $item The item to get the path from
* @return array Array of items (containing basic columns from the item table)
* ID => Item data
*/
public function get_path_basic_data(array $item)
{
$parents = array();
if ($item[$this->column_parent_id]) {
if (!$item[$this->column_item_parents]) {
$sql = 'SELECT ' . implode(', ', $this->item_basic_data) . '
FROM ' . $this->table_name . '
WHERE ' . $this->column_left_id . ' < ' . (int) $item[$this->column_left_id] . '
AND ' . $this->column_right_id . ' > ' . (int) $item[$this->column_right_id] . '
' . $this->get_sql_where('AND') . '
ORDER BY ' . $this->column_left_id . ' ASC';
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$parents[$row[$this->column_item_id]] = $row;
}
$this->db->sql_freeresult($result);
$item_parents = serialize($parents);
$sql = 'UPDATE ' . $this->table_name . '
SET ' . $this->column_item_parents . " = '" . $this->db->sql_escape($item_parents) . "'\n\t\t\t\t\tWHERE " . $this->column_parent_id . ' = ' . (int) $item[$this->column_parent_id];
$this->db->sql_query($sql);
} else {
$parents = unserialize($item[$this->column_item_parents]);
}
}
return $parents;
}
/**
* Uninstall style
*
* @param array $style Style data
* @return bool|string True on success, error message on error
*/
protected function uninstall_style($style)
{
$id = $style['style_id'];
$path = $style['style_path'];
// Check if style has child styles
$sql = 'SELECT style_id
FROM ' . STYLES_TABLE . '
WHERE style_parent_id = ' . (int) $id . " OR style_parent_tree = '" . $this->db->sql_escape($path) . "'";
$result = $this->db->sql_query($sql);
$conflict = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
if ($conflict !== false) {
return sprintf($this->user->lang['STYLE_UNINSTALL_DEPENDENT'], $style['style_name']);
}
// Change default style for users
$sql = 'UPDATE ' . USERS_TABLE . '
SET user_style = 0
WHERE user_style = ' . $id;
$this->db->sql_query($sql);
// Uninstall style
$sql = 'DELETE FROM ' . STYLES_TABLE . '
WHERE style_id = ' . $id;
$this->db->sql_query($sql);
return true;
}
/**
* Updates the user_id field in the database assosciated with the token
*
* @param int $user_id
*/
public function set_user_id($user_id)
{
if (!$this->cachedToken) {
return;
}
$sql = 'UPDATE ' . $this->auth_provider_oauth_table . '
SET ' . $this->db->sql_build_array('UPDATE', array('user_id' => (int) $user_id)) . '
WHERE user_id = ' . (int) $this->user->data['user_id'] . "\n\t\t\t\t\tAND session_id = '" . $this->db->sql_escape($this->user->data['session_id']) . "'";
$this->db->sql_query($sql);
}
/**
* Increments an integer config value directly in the database.
*
* Using this method instead of setting the new value directly avoids race
* conditions and unlike set_atomic it cannot fail.
*
* @param string $key The configuration option's name
* @param int $increment Amount to increment by
* @param bool $use_cache Whether this variable should be cached or if it
* changes too frequently to be efficiently cached.
*/
function increment($key, $increment, $use_cache = true)
{
if (!isset($this->config[$key])) {
$this->set($key, '0', $use_cache);
}
$sql_update = $this->db->cast_expr_to_string($this->db->cast_expr_to_bigint('config_value') . ' + ' . (int) $increment);
$this->db->sql_query('UPDATE ' . $this->table . '
SET config_value = ' . $sql_update . "\n\t\t\tWHERE config_name = '" . $this->db->sql_escape($key) . "'");
if ($use_cache) {
$this->cache->destroy('config');
}
$this->config[$key] += $increment;
}
/**
* {@inheritdoc}
*/
public function unlink_account(array $link_data)
{
if (!array_key_exists('oauth_service', $link_data) || !$link_data['oauth_service']) {
return 'LOGIN_LINK_MISSING_DATA';
}
// Remove user specified in $link_data if possible
$user_id = isset($link_data['user_id']) ? $link_data['user_id'] : $this->user->data['user_id'];
// Remove the link
$sql = 'DELETE FROM ' . $this->auth_provider_oauth_token_account_assoc . "\n\t\t\tWHERE provider = '" . $this->db->sql_escape($link_data['oauth_service']) . "'\n\t\t\t\tAND user_id = " . (int) $user_id;
$this->db->sql_query($sql);
// Clear all tokens belonging to the user on this servce
$service_name = 'auth.provider.oauth.service.' . strtolower($link_data['oauth_service']);
$storage = new \src\auth\provider\oauth\token_storage($this->db, $this->user, $this->auth_provider_oauth_token_storage_table);
$storage->clearToken($service_name);
}
/**
* Insert/Update migration row into the database
*
* @param string $name Name of the migration
* @param array $state
* @return null
*/
protected function set_migration_state($name, $state)
{
$migration_row = $state;
$migration_row['migration_depends_on'] = serialize($state['migration_depends_on']);
if (isset($this->migration_state[$name])) {
$sql = 'UPDATE ' . $this->migrations_table . '
SET ' . $this->db->sql_build_array('UPDATE', $migration_row) . "\n\t\t\t\tWHERE migration_name = '" . $this->db->sql_escape($name) . "'";
$this->db->sql_query($sql);
} else {
$migration_row['migration_name'] = $name;
$sql = 'INSERT INTO ' . $this->migrations_table . '
' . $this->db->sql_build_array('INSERT', $migration_row);
$this->db->sql_query($sql);
}
$this->migration_state[$name] = $state;
$this->last_run_migration['state'] = $state;
}
/**
* Create fulltext index
*
* @return string|bool error string is returned incase of errors otherwise false
*/
public function create_index($acp_module, $u_action)
{
// Make sure we can actually use PostgreSQL with fulltext indexes
if ($error = $this->init()) {
return $error;
}
if (empty($this->stats)) {
$this->get_stats();
}
if (!isset($this->stats['post_subject'])) {
$this->db->sql_query("CREATE INDEX " . POSTS_TABLE . "_" . $this->config['fulltext_postgres_ts_name'] . "_post_subject ON " . POSTS_TABLE . " USING gin (to_tsvector ('" . $this->db->sql_escape($this->config['fulltext_postgres_ts_name']) . "', post_subject))");
}
if (!isset($this->stats['post_content'])) {
$this->db->sql_query("CREATE INDEX " . POSTS_TABLE . "_" . $this->config['fulltext_postgres_ts_name'] . "_post_content ON " . POSTS_TABLE . " USING gin (to_tsvector ('" . $this->db->sql_escape($this->config['fulltext_postgres_ts_name']) . "', post_text || ' ' || post_subject))");
}
$this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE);
return false;
}
/**
* Find the users who want to receive notifications (helper)
*
* @param array $user_ids User IDs to check if they want to receive notifications
* (Bool False to check all users besides anonymous and bots (USER_IGNORE))
*
* @return array
*/
protected function check_user_notification_options($user_ids = false, $options = array())
{
$options = array_merge(array('ignore_users' => array(), 'item_type' => $this->get_type(), 'item_id' => 0), $options);
if ($user_ids === false) {
$user_ids = array();
$sql = 'SELECT user_id
FROM ' . USERS_TABLE . '
WHERE user_id <> ' . ANONYMOUS . '
AND user_type <> ' . USER_IGNORE;
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$user_ids[] = $row['user_id'];
}
$this->db->sql_freeresult($result);
}
if (empty($user_ids)) {
return array();
}
$rowset = $resulting_user_ids = array();
$sql = 'SELECT user_id, method, notify
FROM ' . $this->user_notifications_table . '
WHERE ' . $this->db->sql_in_set('user_id', $user_ids) . "\n\t\t\t\tAND item_type = '" . $this->db->sql_escape($options['item_type']) . "'\n\t\t\t\tAND item_id = " . (int) $options['item_id'];
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$resulting_user_ids[] = $row['user_id'];
if (!$row['notify'] || isset($options['ignore_users'][$row['user_id']]) && in_array($row['method'], $options['ignore_users'][$row['user_id']])) {
continue;
}
if (!isset($rowset[$row['user_id']])) {
$rowset[$row['user_id']] = array();
}
$rowset[$row['user_id']][] = $row['method'];
}
$this->db->sql_freeresult($result);
foreach ($user_ids as $user_id) {
if (!in_array($user_id, $resulting_user_ids) && !isset($options['ignore_users'][$user_id])) {
// No rows at all for this user, default to ''
$rowset[$user_id] = array('');
}
}
return $rowset;
}
/**
* Module Remove
*
* Remove a module
*
* @param string $class The module class(acp|mcp|ucp)
* @param int|string|bool $parent The parent module_id|module_langname(0 for no parent).
* Use false to ignore the parent check and check class wide.
* @param int|string $module The module id|module_langname
* specify that here
* @return null
* @throws \src\db\migration\exception
*/
public function remove($class, $parent = 0, $module = '')
{
// Imitation of module_add's "automatic" and "manual" method so the uninstaller works from the same set of instructions for umil_auto
if (is_array($module)) {
if (isset($module['module_langname'])) {
// Manual Method
return $this->remove($class, $parent, $module['module_langname']);
}
// Failed.
if (!isset($module['module_basename'])) {
throw new \src\db\migration\exception('MODULE_NOT_EXIST');
}
// Automatic method
$basename = $module['module_basename'];
$module_info = $this->get_module_info($class, $basename);
foreach ($module_info['modes'] as $mode => $info) {
if (!isset($module['modes']) || in_array($mode, $module['modes'])) {
$this->remove($class, $parent, $info['title']);
}
}
} else {
if (!$this->exists($class, $parent, $module)) {
return;
}
$parent_sql = '';
if ($parent !== false) {
// Allows '' to be sent as 0
$parent = $parent ?: 0;
if (!is_numeric($parent)) {
$sql = 'SELECT module_id
FROM ' . $this->modules_table . "\n\t\t\t\t\t\tWHERE module_langname = '" . $this->db->sql_escape($parent) . "'\n\t\t\t\t\t\t\tAND module_class = '" . $this->db->sql_escape($class) . "'";
$result = $this->db->sql_query($sql);
$module_id = $this->db->sql_fetchfield('module_id');
$this->db->sql_freeresult($result);
// we know it exists from the module_exists check
$parent_sql = 'AND parent_id = ' . (int) $module_id;
} else {
$parent_sql = 'AND parent_id = ' . (int) $parent;
}
}
$module_ids = array();
if (!is_numeric($module)) {
$sql = 'SELECT module_id
FROM ' . $this->modules_table . "\n\t\t\t\t\tWHERE module_langname = '" . $this->db->sql_escape($module) . "'\n\t\t\t\t\t\tAND module_class = '" . $this->db->sql_escape($class) . "'\n\t\t\t\t\t\t{$parent_sql}";
$result = $this->db->sql_query($sql);
while ($module_id = $this->db->sql_fetchfield('module_id')) {
$module_ids[] = (int) $module_id;
}
$this->db->sql_freeresult($result);
} else {
$module_ids[] = (int) $module;
}
if (!class_exists('acp_modules')) {
include $this->src_root_path . 'includes/acp/acp_modules.' . $this->php_ext;
$this->user->add_lang('acp/modules');
}
$acp_modules = new \acp_modules();
$acp_modules->module_class = $class;
foreach ($module_ids as $module_id) {
$result = $acp_modules->delete_module($module_id);
if (!empty($result)) {
return;
}
}
$this->cache->destroy("_modules_{$class}");
}
}
/**
* This function fills $this->search_query with the cleaned user search query
*
* If $terms is 'any' then the words will be extracted from the search query
* and combined with | inside brackets. They will afterwards be treated like
* an standard search query.
*
* Then it analyses the query and fills the internal arrays $must_not_contain_ids,
* $must_contain_ids and $must_exclude_one_ids which are later used by keyword_search()
*
* @param string $keywords contains the search query string as entered by the user
* @param string $terms is either 'all' (use search query as entered, default words to 'must be contained in post')
* or 'any' (find all posts containing at least one of the given words)
* @return boolean false if no valid keywords were found and otherwise true
*/
public function split_keywords($keywords, $terms)
{
$tokens = '+-|()*';
$keywords = trim($this->cleanup($keywords, $tokens));
// allow word|word|word without brackets
if (strpos($keywords, ' ') === false && strpos($keywords, '|') !== false && strpos($keywords, '(') === false) {
$keywords = '(' . $keywords . ')';
}
$open_bracket = $space = false;
for ($i = 0, $n = strlen($keywords); $i < $n; $i++) {
if ($open_bracket !== false) {
switch ($keywords[$i]) {
case ')':
if ($open_bracket + 1 == $i) {
$keywords[$i - 1] = '|';
$keywords[$i] = '|';
}
$open_bracket = false;
break;
case '(':
$keywords[$i] = '|';
break;
case '+':
case '-':
case ' ':
$keywords[$i] = '|';
break;
case '*':
if ($i === 0 || $keywords[$i - 1] !== '*' && strcspn($keywords[$i - 1], $tokens) === 0) {
if ($i === $n - 1 || $keywords[$i + 1] !== '*' && strcspn($keywords[$i + 1], $tokens) === 0) {
$keywords = substr($keywords, 0, $i) . substr($keywords, $i + 1);
}
}
break;
}
} else {
switch ($keywords[$i]) {
case ')':
$keywords[$i] = ' ';
break;
case '(':
$open_bracket = $i;
$space = false;
break;
case '|':
$keywords[$i] = ' ';
break;
case '-':
case '+':
$space = $keywords[$i];
break;
case ' ':
if ($space !== false) {
$keywords[$i] = $space;
}
break;
default:
$space = false;
}
}
}
if ($open_bracket) {
$keywords .= ')';
}
$match = array('# +#', '#\\|\\|+#', '#(\\+|\\-)(?:\\+|\\-)+#', '#\\(\\|#', '#\\|\\)#');
$replace = array(' ', '|', '$1', '(', ')');
$keywords = preg_replace($match, $replace, $keywords);
$num_keywords = sizeof(explode(' ', $keywords));
// We limit the number of allowed keywords to minimize load on the database
if ($this->config['max_num_search_keywords'] && $num_keywords > $this->config['max_num_search_keywords']) {
trigger_error($this->user->lang('MAX_NUM_SEARCH_KEYWORDS_REFINE', (int) $this->config['max_num_search_keywords'], $num_keywords));
}
// $keywords input format: each word separated by a space, words in a bracket are not separated
// the user wants to search for any word, convert the search query
if ($terms == 'any') {
$words = array();
preg_match_all('#([^\\s+\\-|()]+)(?:$|[\\s+\\-|()])#u', $keywords, $words);
if (sizeof($words[1])) {
$keywords = '(' . implode('|', $words[1]) . ')';
}
}
// set the search_query which is shown to the user
$this->search_query = $keywords;
$exact_words = array();
preg_match_all('#([^\\s+\\-|()]+)(?:$|[\\s+\\-|()])#u', $keywords, $exact_words);
$exact_words = $exact_words[1];
$common_ids = $words = array();
if (sizeof($exact_words)) {
$sql = 'SELECT word_id, word_text, word_common
FROM ' . SEARCH_WORDLIST_TABLE . '
WHERE ' . $this->db->sql_in_set('word_text', $exact_words) . '
ORDER BY word_count ASC';
$result = $this->db->sql_query($sql);
// store an array of words and ids, remove common words
while ($row = $this->db->sql_fetchrow($result)) {
if ($row['word_common']) {
$this->common_words[] = $row['word_text'];
$common_ids[$row['word_text']] = (int) $row['word_id'];
continue;
}
$words[$row['word_text']] = (int) $row['word_id'];
}
$this->db->sql_freeresult($result);
}
// Handle +, - without preceeding whitespace character
$match = array('#(\\S)\\+#', '#(\\S)-#');
$replace = array('$1 +', '$1 +');
$keywords = preg_replace($match, $replace, $keywords);
// now analyse the search query, first split it using the spaces
$query = explode(' ', $keywords);
$this->must_contain_ids = array();
$this->must_not_contain_ids = array();
$this->must_exclude_one_ids = array();
$mode = '';
$ignore_no_id = true;
foreach ($query as $word) {
if (empty($word)) {
continue;
}
// words which should not be included
if ($word[0] == '-') {
$word = substr($word, 1);
// a group of which at least one may not be in the resulting posts
if ($word[0] == '(') {
$word = array_unique(explode('|', substr($word, 1, -1)));
$mode = 'must_exclude_one';
} else {
$mode = 'must_not_contain';
}
$ignore_no_id = true;
} else {
// no prefix is the same as a +prefix
if ($word[0] == '+') {
$word = substr($word, 1);
}
// a group of words of which at least one word should be in every resulting post
if ($word[0] == '(') {
$word = array_unique(explode('|', substr($word, 1, -1)));
}
$ignore_no_id = false;
$mode = 'must_contain';
}
if (empty($word)) {
continue;
}
// if this is an array of words then retrieve an id for each
if (is_array($word)) {
$non_common_words = array();
$id_words = array();
foreach ($word as $i => $word_part) {
if (strpos($word_part, '*') !== false) {
$id_words[] = '\'' . $this->db->sql_escape(str_replace('*', '%', $word_part)) . '\'';
$non_common_words[] = $word_part;
} else {
if (isset($words[$word_part])) {
$id_words[] = $words[$word_part];
$non_common_words[] = $word_part;
} else {
$len = utf8_strlen($word_part);
if ($len < $this->word_length['min'] || $len > $this->word_length['max']) {
$this->common_words[] = $word_part;
}
}
}
}
if (sizeof($id_words)) {
sort($id_words);
if (sizeof($id_words) > 1) {
$this->{$mode . '_ids'}[] = $id_words;
} else {
$mode = $mode == 'must_exclude_one' ? 'must_not_contain' : $mode;
$this->{$mode . '_ids'}[] = $id_words[0];
}
} else {
if (!$ignore_no_id && sizeof($non_common_words)) {
trigger_error(sprintf($this->user->lang['WORDS_IN_NO_POST'], implode($this->user->lang['COMMA_SEPARATOR'], $non_common_words)));
}
}
unset($non_common_words);
} else {
if (($wildcard = strpos($word, '*') !== false) || isset($words[$word])) {
if ($wildcard) {
$len = utf8_strlen(str_replace('*', '', $word));
if ($len >= $this->word_length['min'] && $len <= $this->word_length['max']) {
$this->{$mode . '_ids'}[] = '\'' . $this->db->sql_escape(str_replace('*', '%', $word)) . '\'';
} else {
$this->common_words[] = $word;
}
} else {
$this->{$mode . '_ids'}[] = $words[$word];
}
} else {
if (!isset($common_ids[$word])) {
$len = utf8_strlen($word);
if ($len < $this->word_length['min'] || $len > $this->word_length['max']) {
$this->common_words[] = $word;
}
}
}
}
}
// Return true if all words are not common words
if (sizeof($exact_words) - sizeof($this->common_words) > 0) {
return true;
}
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 keywords depending on display specific params. You have to run split_keywords() first
*
* @param string $type contains either posts or topics depending on what should be searched for
* @param string $fields contains either titleonly (topic titles should be searched), msgonly (only message bodies should be searched), firstpost (only subject and body of the first post should be searched) or all (all post bodies and subjects should be searched)
* @param string $terms is either 'all' (use query as entered, words without prefix should default to "have to be in field") or 'any' (ignore search query parts and just return all posts that contain any of the specified words)
* @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 if the author should be ignored during the search the array is empty
* @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 keyword_search($type, $fields, $terms, $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 keywords? No posts
if (!$this->search_query) {
return false;
}
// generate a search_key from all the options to identify the results
$search_key = md5(implode('#', array(implode(', ', $this->split_words), $type, $fields, $terms, $sort_days, $sort_key, $topic_id, implode(',', $ex_fid_ary), $post_visibility, implode(',', $author_ary))));
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();
$join_topic = $type == 'posts' ? false : true;
// 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':
$join_topic = true;
break;
case 'f':
$sql_sort_table = FORUMS_TABLE . ' f, ';
$sql_sort_join = ' AND f.forum_id = p.forum_id ';
break;
}
// Build some display specific sql strings
switch ($fields) {
case 'titleonly':
$sql_match = 'p.post_subject';
$sql_match_where = ' AND p.post_id = t.topic_first_post_id';
$join_topic = true;
break;
case 'msgonly':
$sql_match = 'p.post_text';
$sql_match_where = '';
break;
case 'firstpost':
$sql_match = 'p.post_subject, p.post_text';
$sql_match_where = ' AND p.post_id = t.topic_first_post_id';
$join_topic = true;
break;
default:
$sql_match = 'p.post_subject, p.post_text';
$sql_match_where = '';
break;
}
$search_query = $this->search_query;
/**
* Allow changing the query used to search for posts using fulltext_mysql
*
* @event core.search_mysql_keywords_main_query_before
* @var string search_query The parsed keywords used for this search
* @var int result_count The previous result count for the format of the query.
* Set to 0 to force a re-count
* @var bool join_topic Weather or not TOPICS_TABLE should be CROSS JOIN'ED
* @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 (!empty(author_ary) must be true, to be relevant)
* @var array ex_fid_ary Which forums not to search on
* @var int topic_id Limit the search to this topic_id only
* @var string sql_sort_table Extra tables to include in the SQL query.
* Used in conjunction with sql_sort_join
* @var string sql_sort_join SQL conditions to join all the tables used together.
* Used in conjunction with sql_sort_table
* @var int sort_days Time, in days, of the oldest possible post to list
* @var string sql_match Which columns to do the search on.
* @var string sql_match_where Extra conditions to use to properly filter the matching process
* @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 int start How many posts to skip in the search results (used for pagination)
* @since 3.1.5-RC1
*/
$vars = array('search_query', 'result_count', 'join_topic', 'author_ary', 'author_name', 'ex_fid_ary', 'topic_id', 'sql_sort_table', 'sql_sort_join', 'sort_days', 'sql_match', 'sql_match_where', 'sort_by_sql', 'sort_key', 'sort_dir', 'sql_sort', 'start');
extract($this->src_dispatcher->trigger_event('core.search_mysql_keywords_main_query_before', compact($vars)));
$sql_select = !$result_count ? 'SQL_CALC_FOUND_ROWS ' : '';
$sql_select = $type == 'posts' ? $sql_select . 'p.post_id' : 'DISTINCT ' . $sql_select . 't.topic_id';
$sql_from = $join_topic ? TOPICS_TABLE . ' t, ' : '';
$field = $type == 'posts' ? 'post_id' : 'topic_id';
if (sizeof($author_ary) && $author_name) {
// first one matches post of registered users, second one guests and deleted users
$sql_author = ' AND (' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')';
} else {
if (sizeof($author_ary)) {
$sql_author = ' AND ' . $this->db->sql_in_set('p.poster_id', $author_ary);
} else {
$sql_author = '';
}
}
$sql_where_options = $sql_sort_join;
$sql_where_options .= $topic_id ? ' AND p.topic_id = ' . $topic_id : '';
$sql_where_options .= $join_topic ? ' AND t.topic_id = p.topic_id' : '';
$sql_where_options .= sizeof($ex_fid_ary) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '';
$sql_where_options .= ' AND ' . $post_visibility;
$sql_where_options .= $sql_author;
$sql_where_options .= $sort_days ? ' AND p.post_time >= ' . (time() - $sort_days * 86400) : '';
$sql_where_options .= $sql_match_where;
$sql = "SELECT {$sql_select}\n\t\t\tFROM {$sql_from}{$sql_sort_table}" . POSTS_TABLE . " p\n\t\t\tWHERE MATCH ({$sql_match}) AGAINST ('" . $this->db->sql_escape(htmlspecialchars_decode($this->search_query)) . "' IN BOOLEAN MODE)\n\t\t\t\t{$sql_where_options}\n\t\t\tORDER BY {$sql_sort}";
$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 the total result count is not cached yet, retrieve it from the db
if (!$result_count) {
$sql_found_rows = 'SELECT FOUND_ROWS() as result_count';
$result = $this->db->sql_query($sql_found_rows);
$result_count = (int) $this->db->sql_fetchfield('result_count');
$this->db->sql_freeresult($result);
if (!$result_count) {
return false;
}
}
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);
}
// store the ids, from start on then delete anything that isn't on the current page because we only need ids for one page
$this->save_ids($search_key, implode(' ', $this->split_words), $author_ary, $result_count, $id_ary, $start, $sort_dir);
$id_ary = array_slice($id_ary, 0, (int) $per_page);
return $result_count;
}
/**
* Enable all notifications of a certain type
*
* This should be called when an extension which has notification types
* that was disabled is re-enabled so that all those notifications that
* were hidden are shown again
*
* @param string $notification_type_name Type identifier of the subscription
*/
public function enable_notifications($notification_type_name)
{
$sql = 'UPDATE ' . $this->notification_types_table . "\n\t\t\tSET notification_type_enabled = 1\n\t\t\tWHERE notification_type_name = '" . $this->db->sql_escape($notification_type_name) . "'";
$this->db->sql_query($sql);
}
/**
* Permission Unset
*
* Allows you to unset (remove) permissions for a certain group/role
*
* @param string $name The name of the role/group
* @param string|array $auth_option The auth_option or array of
* auth_options you would like to set
* @param string $type The type (role|group)
* @return null
* @throws \src\db\migration\exception
*/
public function permission_unset($name, $auth_option, $type = 'role')
{
if (!is_array($auth_option)) {
$auth_option = array($auth_option);
}
$to_remove = array();
$sql = 'SELECT auth_option_id
FROM ' . ACL_OPTIONS_TABLE . '
WHERE ' . $this->db->sql_in_set('auth_option', $auth_option);
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$to_remove[] = (int) $row['auth_option_id'];
}
$this->db->sql_freeresult($result);
if (empty($to_remove)) {
return;
}
$type = (string) $type;
// Prevent PHP bug.
switch ($type) {
case 'role':
$sql = 'SELECT role_id
FROM ' . ACL_ROLES_TABLE . "\n\t\t\t\t\tWHERE role_name = '" . $this->db->sql_escape($name) . "'";
$this->db->sql_query($sql);
$role_id = (int) $this->db->sql_fetchfield('role_id');
if (!$role_id) {
throw new \src\db\migration\exception('ROLE_NOT_EXIST', $name);
}
$sql = 'DELETE FROM ' . ACL_ROLES_DATA_TABLE . '
WHERE ' . $this->db->sql_in_set('auth_option_id', $to_remove) . '
AND role_id = ' . (int) $role_id;
$this->db->sql_query($sql);
break;
case 'group':
$sql = 'SELECT group_id
FROM ' . GROUPS_TABLE . "\n\t\t\t\t\tWHERE group_name = '" . $this->db->sql_escape($name) . "'";
$this->db->sql_query($sql);
$group_id = (int) $this->db->sql_fetchfield('group_id');
if (!$group_id) {
throw new \src\db\migration\exception('GROUP_NOT_EXIST', $name);
}
// If the group has a role set for them we will remove the requested permissions from that role.
$sql = 'SELECT auth_role_id
FROM ' . ACL_GROUPS_TABLE . '
WHERE group_id = ' . $group_id . '
AND auth_role_id <> 0';
$this->db->sql_query($sql);
$role_id = (int) $this->db->sql_fetchfield('auth_role_id');
if ($role_id) {
$sql = 'SELECT role_name
FROM ' . ACL_ROLES_TABLE . '
WHERE role_id = ' . $role_id;
$this->db->sql_query($sql);
$role_name = $this->db->sql_fetchfield('role_name');
return $this->permission_unset($role_name, $auth_option, 'role');
}
$sql = 'DELETE FROM ' . ACL_GROUPS_TABLE . '
WHERE ' . $this->db->sql_in_set('auth_option_id', $to_remove);
$this->db->sql_query($sql);
break;
}
$this->auth->acl_clear_prefetch();
}
/**
* Handle deleting avatars
*
* @param \src\db\driver\driver_interface $db src dbal
* @param \src\user $user src user object
* @param array $avatar_data Cleaned user data containing the user's
* avatar data
* @param string $table Database table from which the avatar should be deleted
* @param string $prefix Prefix of user data columns in database
* @return null
*/
public function handle_avatar_delete(\src\db\driver\driver_interface $db, \src\user $user, $avatar_data, $table, $prefix)
{
if ($driver = $this->get_driver($avatar_data['avatar_type'])) {
$driver->delete($avatar_data);
}
$result = $this->prefix_avatar_columns($prefix, self::$default_row);
$sql = 'UPDATE ' . $table . '
SET ' . $db->sql_build_array('UPDATE', $result) . '
WHERE ' . $prefix . 'id = ' . (int) $avatar_data['id'];
$db->sql_query($sql);
// Make sure we also delete this avatar from the users
if ($prefix === 'group_') {
$result = $this->prefix_avatar_columns('user_', self::$default_row);
$sql = 'UPDATE ' . USERS_TABLE . '
SET ' . $db->sql_build_array('UPDATE', $result) . "\n\t\t\t\tWHERE user_avatar = '" . $db->sql_escape($avatar_data['avatar']) . "'";
$db->sql_query($sql);
}
}
