/**
* Creates a configuration container with a default set of values
*
* @param \src\db\driver\driver_interface $db Database connection
* @param \src\cache\driver\driver_interface $cache Cache instance
* @param string $table Configuration table name
*/
public function __construct(\src\db\driver\driver_interface $db, \src\cache\driver\driver_interface $cache, $table)
{
$this->db = $db;
$this->cache = $cache;
$this->table = $table;
if (($config = $cache->get('config')) !== false) {
$sql = 'SELECT config_name, config_value
FROM ' . $this->table . '
WHERE is_dynamic = 1';
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$config[$row['config_name']] = $row['config_value'];
}
$this->db->sql_freeresult($result);
} else {
$config = $cached_config = array();
$sql = 'SELECT config_name, config_value, is_dynamic
FROM ' . $this->table;
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
if (!$row['is_dynamic']) {
$cached_config[$row['config_name']] = $row['config_value'];
}
$config[$row['config_name']] = $row['config_value'];
}
$this->db->sql_freeresult($result);
$cache->put('config', $cached_config);
}
parent::__construct($config);
}
/**
* Fetches language entries for options from DB
*
* @param int $lang_id
*/
public function load_option_lang($lang_id)
{
$sql = 'SELECT field_id, option_id, lang_value
FROM ' . $this->language_table . '
WHERE lang_id = ' . (int) $lang_id . "\n\t\t\t\tORDER BY option_id";
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$this->options_lang[$row['field_id']][$lang_id][$row['option_id'] + 1] = $row['lang_value'];
}
$this->db->sql_freeresult($result);
}
/**
* 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;
}
/**
* Display various options that can be configured for the backend from the acp
*
* @return associative array containing template and config variables
*/
public function acp()
{
$tpl = '
<dl>
<dt><label>' . $this->user->lang['FULLTEXT_POSTGRES_VERSION_CHECK'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_VERSION_CHECK_EXPLAIN'] . '</span></dt>
<dd>' . ($this->db->get_sql_layer() == 'postgres' ? $this->user->lang['YES'] : $this->user->lang['NO']) . '</dd>
</dl>
<dl>
<dt><label>' . $this->user->lang['FULLTEXT_POSTGRES_TS_NAME'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_TS_NAME_EXPLAIN'] . '</span></dt>
<dd><select name="config[fulltext_postgres_ts_name]">';
if ($this->db->get_sql_layer() == 'postgres') {
$sql = 'SELECT cfgname AS ts_name
FROM pg_ts_config';
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$tpl .= '<option value="' . $row['ts_name'] . '"' . ($row['ts_name'] === $this->config['fulltext_postgres_ts_name'] ? ' selected="selected"' : '') . '>' . $row['ts_name'] . '</option>';
}
$this->db->sql_freeresult($result);
} else {
$tpl .= '<option value="' . $this->config['fulltext_postgres_ts_name'] . '" selected="selected">' . $this->config['fulltext_postgres_ts_name'] . '</option>';
}
$tpl .= '</select></dd>
</dl>
<dl>
<dt><label for="fulltext_postgres_min_word_len">' . $this->user->lang['FULLTEXT_POSTGRES_MIN_WORD_LEN'] . $this->user->lang['COLON'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_MIN_WORD_LEN_EXPLAIN'] . '</span></dt>
<dd><input id="fulltext_postgres_min_word_len" type="number" size="3" maxlength="3" min="0" max="255" name="config[fulltext_postgres_min_word_len]" value="' . (int) $this->config['fulltext_postgres_min_word_len'] . '" /></dd>
</dl>
<dl>
<dt><label for="fulltext_postgres_max_word_len">' . $this->user->lang['FULLTEXT_POSTGRES_MAX_WORD_LEN'] . $this->user->lang['COLON'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_MAX_WORD_LEN_EXPLAIN'] . '</span></dt>
<dd><input id="fulltext_postgres_max_word_len" type="number" size="3" maxlength="3" min="0" max="255" name="config[fulltext_postgres_max_word_len]" value="' . (int) $this->config['fulltext_postgres_max_word_len'] . '" /></dd>
</dl>
';
// These are fields required in the config table
return array('tpl' => $tpl, 'config' => array('fulltext_postgres_ts_name' => 'string', 'fulltext_postgres_min_word_len' => 'integer:0:255', 'fulltext_postgres_max_word_len' => 'integer:0:255'));
}
/**
* 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;
}
/**
* Computes the stats and store them in the $this->stats associative array
*/
protected function get_stats()
{
if (strpos($this->db->get_sql_layer(), 'mysql') === false) {
$this->stats = array();
return;
}
$sql = 'SHOW INDEX
FROM ' . POSTS_TABLE;
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
// deal with older MySQL versions which didn't use Index_type
$index_type = isset($row['Index_type']) ? $row['Index_type'] : $row['Comment'];
if ($index_type == 'FULLTEXT') {
if ($row['Key_name'] == 'post_subject') {
$this->stats['post_subject'] = $row;
} else {
if ($row['Key_name'] == 'post_content') {
$this->stats['post_content'] = $row;
}
}
}
}
$this->db->sql_freeresult($result);
$this->stats['total_posts'] = empty($this->stats) ? 0 : $this->db->get_estimated_row_count(POSTS_TABLE);
}
/**
* Get the notification type id from the name
*
* @param string $notification_type_name The name
* @return int the notification_type_id
* @throws \src\notification\exception
*/
public function get_notification_type_id($notification_type_name)
{
$notification_type_ids = $this->cache->get('notification_type_ids');
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 \src\notification\exception($this->user->lang('NOTIFICATION_TYPE_NOT_EXIST', $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);
}
return $notification_type_ids[$notification_type_name];
}
/**
* A helper function that performs the query for retrieving an access token
*
* @param array $data
* @return mixed
*/
protected function get_access_token_row($data)
{
$sql = 'SELECT oauth_token FROM ' . $this->auth_provider_oauth_table . '
WHERE ' . $this->db->sql_build_array('SELECT', $data);
$result = $this->db->sql_query($sql);
$row = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
return $row;
}
/**
* Get attachment file count and size of upload directory
*
* @param $limit string Additional limit for WHERE clause to filter stats by.
* @return array Returns array with stats: num_files and upload_dir_size
*/
public function get_attachment_stats($limit = '')
{
$sql = 'SELECT COUNT(a.attach_id) AS num_files, SUM(a.filesize) AS upload_dir_size
FROM ' . ATTACHMENTS_TABLE . " a\n\t\t\tWHERE a.is_orphan = 0\n\t\t\t\t{$limit}";
$result = $this->db->sql_query($sql);
$row = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
return array('num_files' => (int) $row['num_files'], 'upload_dir_size' => (double) $row['upload_dir_size']);
}
function get_item()
{
if (!isset($this->result)) {
if (!$this->get_sql()) {
return false;
}
// Query database
$sql = $this->db->sql_build_query('SELECT', $this->sql);
$this->result = $this->db->sql_query_limit($sql, $this->num_items);
}
return $this->db->sql_fetchrow($this->result);
}
/**
* 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');
}
}
protected function execute(InputInterface $input, OutputInterface $output)
{
$sql = 'SELECT user_id, user_email, user_email_hash
FROM ' . USERS_TABLE . '
WHERE user_type <> ' . USER_IGNORE . "\n\t\t\t\tAND user_email <> ''";
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$user_email_hash = src_email_hash($row['user_email']);
if ($user_email_hash !== $row['user_email_hash']) {
$sql_ary = array('user_email_hash' => $user_email_hash);
$sql = 'UPDATE ' . USERS_TABLE . '
SET ' . $this->db->sql_build_array('UPDATE', $sql_ary) . '
WHERE user_id = ' . (int) $row['user_id'];
$this->db->sql_query($sql);
if ($output->getVerbosity() >= OutputInterface::VERBOSITY_DEBUG) {
$output->writeln(sprintf('user_id %d, email %s => %s', $row['user_id'], $row['user_email'], $user_email_hash));
}
}
}
$this->db->sql_freeresult($result);
$output->writeln('<info>' . $this->user->lang('CLI_FIXUP_RECALCULATE_EMAIL_HASH_SUCCESS') . '</info>');
}
/**
* Mass get configuration options: Receives a set of configuration
* option names and returns the result as a key => value map where
* array keys are configuration option names and array values are
* associated config option values.
*
* @param array $keys Set of configuration option names
*
* @return array Map from configuration names to values
*/
public function get_array(array $keys)
{
$sql = 'SELECT *
FROM ' . $this->table . '
WHERE ' . $this->db->sql_in_set('config_name', $keys, false, true);
$result = $this->db->sql_query($sql);
$map = array();
while ($row = $this->db->sql_fetchrow($result)) {
$map[$row['config_name']] = $row['config_value'];
}
$this->db->sql_freeresult($result);
return $map;
}
/**
* {@inheritdoc}
*/
public function login($username, $password)
{
// Temporary workaround for only having one authentication provider available
if (!$this->request->is_set('oauth_service')) {
$provider = new \src\auth\provider\db($this->db, $this->config, $this->passwords_manager, $this->request, $this->user, $this->src_container, $this->src_root_path, $this->php_ext);
return $provider->login($username, $password);
}
// Requst the name of the OAuth service
$service_name_original = $this->request->variable('oauth_service', '', false);
$service_name = 'auth.provider.oauth.service.' . strtolower($service_name_original);
if ($service_name_original === '' || !array_key_exists($service_name, $this->service_providers)) {
return array('status' => LOGIN_ERROR_EXTERNAL_AUTH, 'error_msg' => 'LOGIN_ERROR_OAUTH_SERVICE_DOES_NOT_EXIST', 'user_row' => array('user_id' => ANONYMOUS));
}
// Get the service credentials for the given service
$service_credentials = $this->service_providers[$service_name]->get_service_credentials();
$storage = new \src\auth\provider\oauth\token_storage($this->db, $this->user, $this->auth_provider_oauth_token_storage_table);
$query = 'mode=login&login=external&oauth_service=' . $service_name_original;
$service = $this->get_service($service_name_original, $storage, $service_credentials, $query, $this->service_providers[$service_name]->get_auth_scope());
if ($this->request->is_set('code', \src\request\request_interface::GET)) {
$this->service_providers[$service_name]->set_external_service_provider($service);
$unique_id = $this->service_providers[$service_name]->perform_auth_login();
// Check to see if this provider is already assosciated with an account
$data = array('provider' => $service_name_original, 'oauth_provider_id' => $unique_id);
$sql = 'SELECT user_id FROM ' . $this->auth_provider_oauth_token_account_assoc . '
WHERE ' . $this->db->sql_build_array('SELECT', $data);
$result = $this->db->sql_query($sql);
$row = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
if (!$row) {
// The user does not yet exist, ask to link or create profile
return array('status' => LOGIN_SUCCESS_LINK_PROFILE, 'error_msg' => 'LOGIN_OAUTH_ACCOUNT_NOT_LINKED', 'user_row' => array(), 'redirect_data' => array('auth_provider' => 'oauth', 'login_link_oauth_service' => $service_name_original));
}
// Retrieve the user's account
$sql = 'SELECT user_id, username, user_password, user_passchg, user_email, user_type, user_login_attempts
FROM ' . $this->users_table . '
WHERE user_id = ' . (int) $row['user_id'];
$result = $this->db->sql_query($sql);
$row = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
if (!$row) {
throw new \Exception('AUTH_PROVIDER_OAUTH_ERROR_INVALID_ENTRY');
}
// Update token storage to store the user_id
$storage->set_user_id($row['user_id']);
// The user is now authenticated and can be logged in
return array('status' => LOGIN_SUCCESS, 'error_msg' => false, 'user_row' => $row);
} else {
$url = $service->getAuthorizationUri();
header('Location: ' . $url);
}
}
/**
* 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;
}
/**
* Obtain disallowed usernames
*/
function obtain_disallowed_usernames()
{
if (($usernames = $this->driver->get('_disallowed_usernames')) === false) {
$sql = 'SELECT disallow_username
FROM ' . DISALLOW_TABLE;
$result = $this->db->sql_query($sql);
$usernames = array();
while ($row = $this->db->sql_fetchrow($result)) {
$usernames[] = str_replace('%', '.*?', preg_quote(utf8_clean_string($row['disallow_username']), '#'));
}
$this->db->sql_freeresult($result);
$this->driver->put('_disallowed_usernames', $usernames);
}
return $usernames;
}
/**
* Loads all migrations and their application state from the database.
*
* @return null
*/
public function load_migration_state()
{
$this->migration_state = array();
// prevent errors in case the table does not exist yet
$this->db->sql_return_on_error(true);
$sql = "SELECT *\n\t\t\tFROM " . $this->migrations_table;
$result = $this->db->sql_query($sql);
if (!$this->db->get_sql_error_triggered()) {
while ($migration = $this->db->sql_fetchrow($result)) {
$this->migration_state[$migration['migration_name']] = $migration;
$this->migration_state[$migration['migration_name']]['migration_depends_on'] = unserialize($migration['migration_depends_on']);
}
}
$this->db->sql_freeresult($result);
$this->db->sql_return_on_error(false);
}
/**
* Tidy up indexes: Tag 'common words' and remove
* words no longer referenced in the match table
*/
public function tidy()
{
// Is the fulltext indexer disabled? If yes then we need not
// carry on ... it's okay ... I know when I'm not wanted boo hoo
if (!$this->config['fulltext_native_load_upd']) {
set_config('search_last_gc', time(), true);
return;
}
$destroy_cache_words = array();
// Remove common words
if ($this->config['num_posts'] >= 100 && $this->config['fulltext_native_common_thres']) {
$common_threshold = (double) $this->config['fulltext_native_common_thres'] / 100.0;
// First, get the IDs of common words
$sql = 'SELECT word_id, word_text
FROM ' . SEARCH_WORDLIST_TABLE . '
WHERE word_count > ' . floor($this->config['num_posts'] * $common_threshold) . '
OR word_common = 1';
$result = $this->db->sql_query($sql);
$sql_in = array();
while ($row = $this->db->sql_fetchrow($result)) {
$sql_in[] = $row['word_id'];
$destroy_cache_words[] = $row['word_text'];
}
$this->db->sql_freeresult($result);
if (sizeof($sql_in)) {
// Flag the words
$sql = 'UPDATE ' . SEARCH_WORDLIST_TABLE . '
SET word_common = 1
WHERE ' . $this->db->sql_in_set('word_id', $sql_in);
$this->db->sql_query($sql);
// by setting search_last_gc to the new time here we make sure that if a user reloads because the
// following query takes too long, he won't run into it again
set_config('search_last_gc', time(), true);
// Delete the matches
$sql = 'DELETE FROM ' . SEARCH_WORDMATCH_TABLE . '
WHERE ' . $this->db->sql_in_set('word_id', $sql_in);
$this->db->sql_query($sql);
}
unset($sql_in);
}
if (sizeof($destroy_cache_words)) {
// destroy cached search results containing any of the words that are now common or were removed
$this->destroy_cache(array_unique($destroy_cache_words));
}
set_config('search_last_gc', time(), true);
}
/**
* Build Array for user insertion into custom profile fields table
*/
public function build_insert_sql_array($cp_data)
{
$sql_not_in = array();
foreach ($cp_data as $key => $null) {
$sql_not_in[] = strncmp($key, 'pf_', 3) === 0 ? substr($key, 3) : $key;
}
$sql = 'SELECT f.field_type, f.field_ident, f.field_default_value, l.lang_default_value
FROM ' . $this->fields_language_table . ' l, ' . $this->fields_table . ' f
WHERE l.lang_id = ' . $this->user->get_iso_lang_id() . '
' . (sizeof($sql_not_in) ? ' AND ' . $this->db->sql_in_set('f.field_ident', $sql_not_in, true) : '') . '
AND l.field_id = f.field_id';
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result)) {
$profile_field = $this->type_collection[$row['field_type']];
$cp_data['pf_' . $row['field_ident']] = $profile_field->get_default_field_value($row);
}
$this->db->sql_freeresult($result);
return $cp_data;
}
/**
* 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;
}
/**
* 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 ($mode == 'edit') {
$this->sphinx->UpdateAttributes($this->indexes, array('forum_id', 'poster_id'), array((int) $post_id => array((int) $forum_id, (int) $poster_id)));
} else {
if ($mode != 'post' && $post_id) {
// Update topic_last_post_time for full topic
$sql_array = array('SELECT' => 'p1.post_id', 'FROM' => array(POSTS_TABLE => 'p1'), 'LEFT_JOIN' => array(array('FROM' => array(POSTS_TABLE => 'p2'), 'ON' => 'p1.topic_id = p2.topic_id')), 'WHERE' => 'p2.post_id = ' . (int) $post_id);
$sql = $this->db->sql_build_query('SELECT', $sql_array);
$result = $this->db->sql_query($sql);
$post_updates = array();
$post_time = time();
while ($row = $this->db->sql_fetchrow($result)) {
$post_updates[(int) $row['post_id']] = array($post_time);
}
$this->db->sql_freeresult($result);
if (sizeof($post_updates)) {
$this->sphinx->UpdateAttributes($this->indexes, array('topic_last_post_time'), $post_updates);
}
}
}
}
/**
* Set topic visibility
*
* Allows approving (which is akin to undeleting/restore) or soft deleting an entire topic.
* Calls set_post_visibility as needed.
*
* Note: By default, when a soft deleted topic is restored. Only posts that
* were approved at the time of soft deleting, are being restored.
* Same applies to soft deleting. Only approved posts will be marked
* as soft deleted.
* If you want to update all posts, use the force option.
*
* @param $visibility int Element of {ITEM_APPROVED, ITEM_DELETED, ITEM_REAPPROVE}
* @param $topic_id mixed Topic ID to act on
* @param $forum_id int Forum where $topic_id is found
* @param $user_id int User performing the action
* @param $time int Timestamp when the action is performed
* @param $reason string Reason why the visibilty was changed.
* @param $force_update_all bool Force to update all posts within the topic
* @return array Changed topic data, empty array if an error occured.
*/
public function set_topic_visibility($visibility, $topic_id, $forum_id, $user_id, $time, $reason, $force_update_all = false)
{
if (!in_array($visibility, array(ITEM_APPROVED, ITEM_DELETED, ITEM_REAPPROVE))) {
return array();
}
if (!$force_update_all) {
$sql = 'SELECT topic_visibility, topic_delete_time
FROM ' . $this->topics_table . '
WHERE topic_id = ' . (int) $topic_id;
$result = $this->db->sql_query($sql);
$original_topic_data = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
if (!$original_topic_data) {
// The topic does not exist...
return array();
}
}
// Note, we do not set a reason for the posts, just for the topic
$data = array('topic_visibility' => (int) $visibility, 'topic_delete_user' => (int) $user_id, 'topic_delete_time' => (int) $time ?: time(), 'topic_delete_reason' => truncate_string($reason, 255, 255, false));
$sql = 'UPDATE ' . $this->topics_table . '
SET ' . $this->db->sql_build_array('UPDATE', $data) . '
WHERE topic_id = ' . (int) $topic_id;
$this->db->sql_query($sql);
if (!$this->db->sql_affectedrows()) {
return array();
}
if (!$force_update_all && $original_topic_data['topic_delete_time'] && $original_topic_data['topic_visibility'] == ITEM_DELETED && $visibility == ITEM_APPROVED) {
// If we're restoring a topic we only restore posts, that were soft deleted through the topic soft deletion.
$this->set_post_visibility($visibility, false, $topic_id, $forum_id, $user_id, $time, '', true, true, $original_topic_data['topic_visibility'], $original_topic_data['topic_delete_time']);
} else {
if (!$force_update_all && $original_topic_data['topic_visibility'] == ITEM_APPROVED && $visibility == ITEM_DELETED) {
// If we're soft deleting a topic we only mark approved posts as soft deleted.
$this->set_post_visibility($visibility, false, $topic_id, $forum_id, $user_id, $time, '', true, true, $original_topic_data['topic_visibility']);
} else {
$this->set_post_visibility($visibility, false, $topic_id, $forum_id, $user_id, $time, '', true, true);
}
}
return $data;
}
/**
* {@inheritDoc}
*/
function sql_save(\src\db\driver\driver_interface $db, $query, $query_result, $ttl)
{
// Remove extra spaces and tabs
$query = preg_replace('/[\\n\\r\\s\\t]+/', ' ', $query);
$query_id = sizeof($this->sql_rowset);
$this->sql_rowset[$query_id] = array();
$this->sql_row_pointer[$query_id] = 0;
while ($row = $db->sql_fetchrow($query_result)) {
$this->sql_rowset[$query_id][] = $row;
}
$db->sql_freeresult($query_result);
if ($this->_write('sql_' . md5($query), $this->sql_rowset[$query_id], $ttl + time(), $query)) {
return $query_id;
}
return $query_result;
}
/**
* Handles authentication when downloading attachments from a post or topic
*
* @param \src\db\driver\driver_interface $db The database object
* @param \src\auth\auth $auth The authentication object
* @param int $topic_id The id of the topic that we are downloading from
*
* @return null
*/
function src_download_handle_forum_auth($db, $auth, $topic_id)
{
$sql_array = array('SELECT' => 't.topic_visibility, t.forum_id, f.forum_name, f.forum_password, f.parent_id', 'FROM' => array(TOPICS_TABLE => 't', FORUMS_TABLE => 'f'), 'WHERE' => 't.topic_id = ' . (int) $topic_id . '
AND t.forum_id = f.forum_id');
$sql = $db->sql_build_query('SELECT', $sql_array);
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
if ($row && $row['topic_visibility'] != ITEM_APPROVED && !$auth->acl_get('m_approve', $row['forum_id'])) {
send_status_line(404, 'Not Found');
trigger_error('ERROR_NO_ATTACHMENT');
} else {
if ($row && $auth->acl_get('u_download') && $auth->acl_get('f_download', $row['forum_id'])) {
if ($row['forum_password']) {
// Do something else ... ?
login_forum_box($row);
}
} else {
send_status_line(403, 'Forbidden');
trigger_error('SORRY_AUTH_VIEW_ATTACH');
}
}
}
/**
* 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;
}
/**
* {@inheritDoc}
*/
function sql_save(\src\db\driver\driver_interface $db, $query, $query_result, $ttl)
{
// Remove extra spaces and tabs
$query = preg_replace('/[\\n\\r\\s\\t]+/', ' ', $query);
$hash = md5($query);
// determine which tables this query belongs to
// Some queries use backticks, namely the get_database_size() query
// don't check for conformity, the SQL would error and not reach here.
if (!preg_match_all('/(?:FROM \\(?(`?\\w+`?(?: \\w+)?(?:, ?`?\\w+`?(?: \\w+)?)*)\\)?)|(?:JOIN (`?\\w+`?(?: \\w+)?))/', $query, $regs, PREG_SET_ORDER)) {
// Bail out if the match fails.
return $query_result;
}
$tables = array();
foreach ($regs as $match) {
if ($match[0][0] == 'F') {
$tables = array_merge($tables, array_map('trim', explode(',', $match[1])));
} else {
$tables[] = $match[2];
}
}
foreach ($tables as $table_name) {
// Remove backticks
$table_name = $table_name[0] == '`' ? substr($table_name, 1, -1) : $table_name;
if (($pos = strpos($table_name, ' ')) !== false) {
$table_name = substr($table_name, 0, $pos);
}
$temp = $this->_read('sql_' . $table_name);
if ($temp === false) {
$temp = array();
}
$temp[$hash] = true;
// This must never expire
$this->_write('sql_' . $table_name, $temp, 0);
}
// store them in the right place
$query_id = sizeof($this->sql_rowset);
$this->sql_rowset[$query_id] = array();
$this->sql_row_pointer[$query_id] = 0;
while ($row = $db->sql_fetchrow($query_result)) {
$this->sql_rowset[$query_id][] = $row;
}
$db->sql_freeresult($query_result);
$this->_write('sql_' . $hash, $this->sql_rowset[$query_id], $ttl);
return $query_id;
}
/**
* 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();
}
