<?php /*

 Composr
 Copyright (c) ocProducts, 2004-2016

 See text/EN/licence.txt for full licencing information.


 NOTE TO PROGRAMMERS:
   Do not edit this file. If you need to make changes, save your changed file to the appropriate *_custom folder
   **** If you ignore this advice, then your website upgrades (e.g. for bug fixes) will likely kill your changes ****

*/

/*EXTRA FUNCTIONS: fb*/

/**
 * @license    http://opensource.org/licenses/cpal_1.0 Common Public Attribution License
 * @copyright  ocProducts Ltd
 * @package    core
 */

/**
 * Standard code module initialisation function.
 *
 * @ignore
 */
function init__database()
{
    global $HAS_MULTI_LANG_CONTENT;
    $HAS_MULTI_LANG_CONTENT = null;

    global $QUERY_LIST, $QUERY_COUNT, $NO_QUERY_LIMIT, $NO_DB_SCOPE_CHECK, $QUERY_FILE_LOG, $SITE_INFO;
    $QUERY_LIST = array();
    $QUERY_COUNT = 0;
    /** Disable the query limit that operates during development mode
     *
     * @global boolean $NO_QUERY_LIMIT
     */
    $NO_QUERY_LIMIT = false;
    /** Disable the database driver scope check that operates during development mode
     *
     * @global boolean $NO_DB_SCOPE_CHECK
     */
    $NO_DB_SCOPE_CHECK = false;
    if (((!isset($SITE_INFO['no_extra_logs'])) || ($SITE_INFO['no_extra_logs'] != '1')) && (is_file(get_custom_file_base() . '/data_custom/queries.log'))) {
        $QUERY_FILE_LOG = fopen(get_custom_file_base() . '/data_custom/queries.log', 'at');
    } else {
        $QUERY_FILE_LOG = null;
    }

    // This will be generalised some-time
    require_code('database/' . get_db_type());
    $GLOBALS['DB_STATIC_OBJECT'] = object_factory('Database_Static_' . get_db_type());

    // Create our main database objects
    global $TABLE_LANG_FIELDS_CACHE;
    $TABLE_LANG_FIELDS_CACHE = array();
    if ((!empty($SITE_INFO['db_site'])) || (!empty($SITE_INFO['db_site_user']))) {
        global $SITE_DB;
        /** The connection to the active site database.
         *
         * @global object $SITE_DB
         */
        $SITE_DB = new DatabaseConnector(get_db_site(), get_db_site_host(), get_db_site_user(), get_db_site_password(), get_table_prefix());
    }

    global $UPON_QUERY_HOOKS_CACHE;
    $UPON_QUERY_HOOKS_CACHE = null;
}

/**
 * Extract certain fields, including any Tempcode details for them, from a DB table row array.
 *
 * @param  array $row DB table row.
 * @param  array $fields List of fields to copy through.
 * @param  ?array $remap Remapping of fields, if we need to do some kind of substitution as well (usually this will be because the row came from a join and we had to rename fields) (null: none).
 * @return array Map of fields.
 */
function db_map_restrict($row, $fields, $remap = null)
{
    // TODO: Change null to array() in v11; remove these 3 lines
    if ($remap === null) {
        $remap = array();
    }

    $out = array();
    foreach ($fields as $field) {
        $out[$field] = $row[(array_key_exists($field, $remap) && array_key_exists($remap[$field], $row)) ? $remap[$field] : $field];
        if (isset($row[$field . '__text_parsed'])) {
            $out[$field . '__text_parsed'] = $row[$field . '__text_parsed'];
        }
        if (array_key_exists($field . '__source_user', $row)) {
            $out[$field . '__source_user'] = $row[$field . '__source_user'];
        }
    }
    return $out;
}

/**
 * Find whether to run in multi-lang mode for content translations.
 *
 * @return boolean Whether to run in multi-lang mode for content translations.
 */
function multi_lang_content()
{
    global $HAS_MULTI_LANG_CONTENT;
    if ($HAS_MULTI_LANG_CONTENT === null) {
        global $SITE_INFO;
        $HAS_MULTI_LANG_CONTENT = isset($SITE_INFO['multi_lang_content']) ? ($SITE_INFO['multi_lang_content'] === '1') : true; // For legacy reasons
    }
    return $HAS_MULTI_LANG_CONTENT;
}

/**
 * Called once our DB connection becomes active.
 *
 * @ignore
 */
function _general_db_init()
{
    global $TABLE_LANG_FIELDS_CACHE;
    if (count($TABLE_LANG_FIELDS_CACHE) > 0) {
        return;
    }

    $TABLE_LANG_FIELDS_CACHE = function_exists('persistent_cache_get') ? persistent_cache_get('TABLE_LANG_FIELDS_CACHE') : null;
    if ($TABLE_LANG_FIELDS_CACHE === null) {
        reload_lang_fields();
    }
}

/**
 * Reload language fields from the database.
 *
 * @param boolean $full Whether we need to know about non-Comcode language fields
 * @param ?string $only_table The only table to reload for (null: all tables)
 */
function reload_lang_fields($full = false, $only_table = null)
{
    global $TABLE_LANG_FIELDS_CACHE;
    if ($only_table === null) {
        $TABLE_LANG_FIELDS_CACHE = array();
    } else {
        unset($TABLE_LANG_FIELDS_CACHE[$only_table]);
    }

    $msn_running = (is_on_multi_site_network()) && (get_forum_type() == 'cns') && (isset($GLOBALS['FORUM_DB'])); // TODO: Change in v11

    if (multi_lang_content() || $full) {
        // We need to know about any kind of translated fields in these cases
        $like = db_string_equal_to('m_type', 'SHORT_TRANS__COMCODE') . ' OR ' . db_string_equal_to('m_type', 'LONG_TRANS__COMCODE') . ' OR ' . db_string_equal_to('m_type', 'SHORT_TRANS') . ' OR ' . db_string_equal_to('m_type', 'LONG_TRANS') . ' OR ' . db_string_equal_to('m_type', '?SHORT_TRANS__COMCODE') . ' OR ' . db_string_equal_to('m_type', '?LONG_TRANS__COMCODE') . ' OR ' . db_string_equal_to('m_type', '?SHORT_TRANS') . ' OR ' . db_string_equal_to('m_type', '?LONG_TRANS');
    } else {
        // In this case we only really need to know about Comcode fields
        $like = db_string_equal_to('m_type', 'SHORT_TRANS__COMCODE') . ' OR ' . db_string_equal_to('m_type', 'LONG_TRANS__COMCODE') . ' OR ' . db_string_equal_to('m_type', '?SHORT_TRANS__COMCODE') . ' OR ' . db_string_equal_to('m_type', '?LONG_TRANS__COMCODE');
    }
    $sql = 'SELECT m_name,m_table,m_type FROM ' . get_table_prefix() . 'db_meta WHERE (' . $like . ')';
    if ($only_table !== null) {
        $sql .= ' AND ' . db_string_equal_to('m_table', $only_table);
    }
    if (($msn_running) && ($only_table !== null) && (substr($only_table, 0, 2) === 'f_')) {
        $_table_lang_fields = array(); // Optimisation, as it'll get overwritten anyway
    } else {
        $_table_lang_fields = $GLOBALS['SITE_DB']->query($sql, null, null, true);
    }
    if ($_table_lang_fields !== null) {
        // Load in our data
        foreach ($_table_lang_fields as $lang_field) {
            if (!isset($TABLE_LANG_FIELDS_CACHE[$lang_field['m_table']])) {
                $TABLE_LANG_FIELDS_CACHE[$lang_field['m_table']] = array();
            }

            $TABLE_LANG_FIELDS_CACHE[$lang_field['m_table']][$lang_field['m_name']] = $lang_field['m_type'];
        }

        // Get correct forum DB metadata from central site
        if (($msn_running) && (($only_table === null) || (substr($only_table, 0, 2) === 'f_'))) {
            if ($only_table !== null) {
                unset($TABLE_LANG_FIELDS_CACHE[$only_table]);
            }
            unset($TABLE_LANG_FIELDS_CACHE['f_member_custom_fields']); // This may vary between sites in undefined ways

            $sql .= ' AND m_table LIKE \'' . db_encode_like('f_%') . '\'';

            $_table_lang_fields_forum = $GLOBALS['FORUM_DB']->query($sql, null, null, true);
            if ($_table_lang_fields_forum !== null) {
                // Load in our data
                foreach ($_table_lang_fields_forum as $lang_field) {
                    if (!isset($TABLE_LANG_FIELDS_CACHE[$lang_field['m_table']])) {
                        $TABLE_LANG_FIELDS_CACHE[$lang_field['m_table']] = array();
                    }

                    $TABLE_LANG_FIELDS_CACHE[$lang_field['m_table']][$lang_field['m_name']] = $lang_field['m_type'];
                }
            }
        }
    }

    if (function_exists('persistent_cache_set')) {
        persistent_cache_set('TABLE_LANG_FIELDS_CACHE', $TABLE_LANG_FIELDS_CACHE);
    }
}

/**
 * Find whether the database may run GROUP BY unfettered with restrictions on the SELECT'd fields having to be represented in it or aggregate functions
 *
 * @return boolean Whether it can
 */
function can_arbitrary_groupby()
{
    if (!method_exists($GLOBALS['DB_STATIC_OBJECT'], 'can_arbitrary_groupby')) {
        return false;
    }
    return $GLOBALS['DB_STATIC_OBJECT']->can_arbitrary_groupby();
}

/**
 * Get the ID of the first row in an auto-increment table (used whenever we need to reference the first).
 *
 * @return integer First ID used
 */
function db_get_first_id()
{
    return $GLOBALS['DB_STATIC_OBJECT']->db_get_first_id();
}

/**
 * Encode an SQL statement fragment for a conditional to see if two strings are equal.
 *
 * @param  ID_TEXT $attribute The attribute
 * @param  string $compare The comparison
 * @return string The SQL
 */
function db_string_equal_to($attribute, $compare)
{
    return $GLOBALS['DB_STATIC_OBJECT']->db_string_equal_to($attribute, $compare);
}

/**
 * Encode an SQL statement fragment for a conditional to see if two strings are not equal.
 *
 * @param  ID_TEXT $attribute The attribute
 * @param  string $compare The comparison
 * @return string The SQL
 */
function db_string_not_equal_to($attribute, $compare)
{
    return $GLOBALS['DB_STATIC_OBJECT']->db_string_not_equal_to($attribute, $compare);
}

/**
 * Encode a LIKE string comparision fragement for the database system. The pattern is a mixture of characters and ? and % wildcard symbols.
 *
 * @param  string $pattern The pattern
 * @return string The encoded pattern
 */
function db_encode_like($pattern)
{
    $ret = $GLOBALS['DB_STATIC_OBJECT']->db_encode_like($pattern);

    if (($GLOBALS['DEV_MODE']) || (!function_exists('has_solemnly_declared')) || (!has_solemnly_declared(I_UNDERSTAND_SQL_INJECTION))) {
        require_code('database_security_filter');
        $GLOBALS['DB_ESCAPE_STRING_LIST'][$ret] = true;
        $GLOBALS['DB_ESCAPE_STRING_LIST'][trim($ret, ' %')] = true;
    }

    return $ret;
}

/**
 * Find whether drop table "if exists" is present
 *
 * @param  array $db A DB connection
 * @return boolean Whether it is
 */
function db_supports_drop_table_if_exists($db)
{
    if ((is_array($db)) && (count($db) > 4)) { // Okay, we can't be lazy anymore
        $db = call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'], 'db_get_connection'), $db);
        _general_db_init();
    }

    if (!method_exists($GLOBALS['DB_STATIC_OBJECT'], 'db_supports_drop_table_if_exists')) {
        return false;
    }

    return $GLOBALS['DB_STATIC_OBJECT']->db_supports_drop_table_if_exists();
}

/**
 * Find whether full-text-search is present
 *
 * @param  array $db A DB connection
 * @param  ?string $table Table to check we have an index on (null: no check)
 * @param  ?string $field Field to check we have an index on (null: no check)
 * @return boolean Whether it is
 */
function db_has_full_text($db, $table = null, $field = null)
{
    if (($table !== null) && ($field !== null)) {
        $field = preg_replace('#^.*\.(.*)#', '$1', $field);
        $indexes = $GLOBALS['FORUM_DB']->query_select('db_meta_indices', array('i_fields', 'i_name'), array('i_table' => $table));
        $okay = false;
        foreach ($indexes as $index) {
            if ($index['i_name'][0] == '#') {
                $fields = explode(',', $index['i_fields']);
                if (in_array($field, $fields)) {
                    $okay = true;
                    break;
                }
            }
        }
        if (!$okay) {
            return false;
        }
    }

    if ((is_array($db)) && (count($db) > 4)) { // Okay, we can't be lazy anymore
        $db = call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'], 'db_get_connection'), $db);
        _general_db_init();
    }

    return $GLOBALS['DB_STATIC_OBJECT']->db_has_full_text($db);
}

/**
 * Assemble part of a WHERE clause for doing full-text search
 *
 * @param  string $content Our match string (assumes "?" has been stripped already)
 * @param  boolean $boolean Whether to do a boolean full text search
 * @return string Part of a WHERE clause for doing full-text search
 */
function db_full_text_assemble($content, $boolean)
{
    $ret = $GLOBALS['DB_STATIC_OBJECT']->db_full_text_assemble($content, $boolean);

    if (($GLOBALS['DEV_MODE']) || (!has_solemnly_declared(I_UNDERSTAND_SQL_INJECTION))) {
        require_code('database_security_filter');
        $GLOBALS['DB_ESCAPE_STRING_LIST'][$GLOBALS['DB_STATIC_OBJECT']->db_escape_string($content)] = true;
    }

    return $ret;
}

/**
 * Find whether subquery support is present
 *
 * @param  array $db A DB connection
 * @return boolean Whether it is
 */
function db_has_subqueries($db)
{
    if (!method_exists($GLOBALS['DB_STATIC_OBJECT'], 'db_has_subqueries')) {
        return true;
    }

    if ((is_array($db)) && (count($db) > 4)) { // Okay, we can't be lazy anymore
        $db = call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'], 'db_get_connection'), $db);
        _general_db_init();
    }

    return $GLOBALS['DB_STATIC_OBJECT']->db_has_subqueries($db);
}

/**
 * Find whether expression ordering support is present
 *
 * @param  array $db A DB connection
 * @return boolean Whether it is
 */
function db_has_expression_ordering($db)
{
    if ((is_array($db)) && (count($db) > 4)) { // Okay, we can't be lazy anymore
        $db = call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'], 'db_get_connection'), $db);
        _general_db_init();
    }

    if (!method_exists($GLOBALS['DB_STATIC_OBJECT'], 'db_has_expression_ordering')) {
        return false;
    }
    return $GLOBALS['DB_STATIC_OBJECT']->db_has_expression_ordering($db);
}

/**
 * Whether 'OFFSET' syntax is used on limit clauses.
 *
 * @param  array $db A DB connection
 * @return boolean Whether it is
 */
function db_uses_offset_syntax($db)
{
    if ((is_array($db)) && (count($db) > 4)) { // Okay, we can't be lazy anymore
        $db = call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'], 'db_get_connection'), $db);
        _general_db_init();
    }

    if (!method_exists($GLOBALS['DB_STATIC_OBJECT'], 'db_has_expression_ordering')) {
        return false;
    }
    return $GLOBALS['DB_STATIC_OBJECT']->db_uses_offset_syntax($db);
}

/**
 * Find whether table truncation support is present
 *
 * @param  array $db A DB connection
 * @return boolean Whether it is
 */
function db_supports_truncate_table($db)
{
    if ((is_array($db)) && (count($db) > 4)) { // Okay, we can't be lazy anymore
        $db = call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'], 'db_get_connection'), $db);
        _general_db_init();
    }

    if (!method_exists($GLOBALS['DB_STATIC_OBJECT'], 'db_supports_truncate_table')) {
        return false;
    }
    return $GLOBALS['DB_STATIC_OBJECT']->db_supports_truncate_table($db);
}

/**
 * Start a transaction
 *
 * @param  array $db A DB connection
 */
function db_start_transaction($db)
{
    if ((is_array($db)) && (count($db) > 4)) { // Okay, we can't be lazy anymore
        $db = call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'], 'db_get_connection'), $db);
        _general_db_init();
    }

    if (method_exists($GLOBALS['DB_STATIC_OBJECT'], 'db_start_transaction')) {
        $GLOBALS['DB_STATIC_OBJECT']->db_start_transaction($db);
    }
}

/**
 * End a transaction
 *
 * @param  array $db A DB connection
 */
function db_end_transaction($db)
{
    if ((is_array($db)) && (count($db) > 4)) { // Okay, we can't be lazy anymore
        $db = call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'], 'db_get_connection'), $db);
        _general_db_init();
    }

    if (method_exists($GLOBALS['DB_STATIC_OBJECT'], 'db_end_transaction')) {
        $GLOBALS['DB_STATIC_OBJECT']->db_end_transaction($db);
    }
}

/**
 * Escape a string so it may be inserted into a query. If SQL statements are being built up and passed using db_query then it is essential that this is used for security reasons. Otherwise, the abstraction layer deals with the situation.
 *
 * @param  string $string The string
 * @return string The escaped string
 */
function db_escape_string($string)
{
    if ((is_array($GLOBALS['SITE_DB']->connection_read)) && (count($GLOBALS['SITE_DB']->connection_read) > 4)) { // Okay, we can't be lazy anymore
        $GLOBALS['SITE_DB']->connection_read = call_user_func_array(array($GLOBALS['DB_STATIC_OBJECT'], 'db_get_connection'), $GLOBALS['SITE_DB']->connection_read);
        _general_db_init();
    }

    $ret = $GLOBALS['DB_STATIC_OBJECT']->db_escape_string($string);

    if (function_exists('has_solemnly_declared')) {
        if (($GLOBALS['DEV_MODE']) || (!has_solemnly_declared(I_UNDERSTAND_SQL_INJECTION))) {
            require_code('database_security_filter');
            $GLOBALS['DB_ESCAPE_STRING_LIST'][trim($ret, ' %')] = true;
        }
    }

    return $ret;
}

/**
 * Call a database function that may be different on different database drivers.
 * We are using MySQL syntax as a de-facto standard. SQL does not standardise this stuff well.
 * This is HACKHACK and should move into database drivers into the future.
 * Basic arithmetic and inequality operators are assumed supported without needing a function.
 *
 * @param string $function Function name
 * @set IFF CONCAT REPLACE SUBSTR LENGTH RAND COALESCE LEAST GREATEST MOD GROUP_CONCAT X_ORDER_BY_BOOLEAN
 * @param ?array $args List of string arguments, assumed already quoted/escaped correctly for the particular database (null: none)
 * @return string SQL fragment
 */
function db_function($function, $args = null)
{
    if ($args === null) {
        $args = array();
    }

    $args = @array_map('strval', $args);

    if (method_exists($GLOBALS['DB_STATIC_OBJECT'], 'db_function')) {
        return $GLOBALS['DB_STATIC_OBJECT']->db_function($function, $args);
    }

    switch ($function) {
        case 'IFF':
            /*
                         CASE construct   IF func   IIF func   IF construct

            mysql        Yes              Yes       No         IF...THEN...ELSEIF...ELSE...END IF
            postgresql   Yes              No        No         IF...THEN...ELSIF...ELSE...END IF
            sqlite       Yes              No        No         No
            oracle       Yes              No        Yes        IF...THEN...ELSIF...ELSE...END IF
            db2          Yes              No        No         IF...THEN...ELSEIF...ELSE...END IF
            access       No               No        Yes        No
            SQL server   Yes              No        Yes        IF...ELSE...

            Anything supporting CASE supports both simple and complex forms, as both are standardised.
            */
            switch (get_db_type()) {
                case 'mysql':
                case 'mysqli':
                case 'mysql_pdo':
                case 'mysql_dbx':
                    return 'IF(' . implode(',', $args) . ')';

                case 'postgresql':
                case 'sqlite':
                case 'db2':
                    return 'CASE WHEN ' . $args[0] . ' THEN ' . $args[1] . ' ELSE ' . $args[2] . ' END';
            }

        case 'CONCAT':
            switch (get_db_type()) {
                // Supported on most

                case 'sqlite':
                    return implode(' || ', $args);

                case 'access':
                    return implode(' & ', $args);
            }
            break;

        case 'REPLACE':
            if (count($args) != 3) {
                fatal_exit(do_lang_tempcode('INTERNAL_ERROR'));
            }
            switch (get_db_type()) {
                // Supported on all http://troels.arvin.dk/db/rdbms/#functions-REPLACE
                // You don't even need to call this function.
            }
            break;

        case 'SUBSTR':
            if (count($args) != 3) {
                fatal_exit(do_lang_tempcode('INTERNAL_ERROR'));
            }
            if ($args[1] != '1') {
                fatal_exit(do_lang_tempcode('INTERNAL_ERROR')); // Can only act as a 'LEFT'
            }
            switch (get_db_type()) {
                case 'sqlserver':
                case 'sqlserver_odbc':
                    $function = 'SUBSTRING'; // http://troels.arvin.dk/db/rdbms/#functions-REPLACE
                    break;

                case 'access':
                    $function = 'LEFT'; // http://stackoverflow.com/questions/809120/is-there-an-equivalent-to-the-substring-function-in-ms-access-sql
                    $args = array($args[0], $args[2]);
                    break;
            }
            break;

        case 'LENGTH':
            if (count($args) != 1) {
                fatal_exit(do_lang_tempcode('INTERNAL_ERROR'));
            }
            switch (get_db_type()) {
                case 'mysql':
                case 'mysqli':
                case 'mysql_pdo':
                case 'mysql_dbx':
                    $function = 'CHAR_LENGTH';
                    break;

                case 'sqlserver':
                case 'sqlserver_odbc':
                case 'access':
                    $function = 'LEN';
                    break;
            }
            break;

        case 'RAND':
            if (count($args) != 0) {
                fatal_exit(do_lang_tempcode('INTERNAL_ERROR'));
            }
            switch (get_db_type()) {
                case 'postgresql':
                case 'sqlite':
                    $function = 'RANDOM';
                    break;
            }
            break;

        case 'COALESCE':
            if (count($args) != 2) {
                fatal_exit(do_lang_tempcode('INTERNAL_ERROR'));
            }
            switch (get_db_type()) {
                case 'access':
                    $function = 'IIF';
                    $args[0] .= ' IS NULL';
                    break;
                default:
                    $all_null = true;
                    foreach ($args as $arg) {
                        if ($arg != 'NULL') {
                            $all_null = false;
                            break;
                        }
                    }
                    if ($all_null) {
                        return 'NULL';
                    }
            }
            break;

        case 'LEAST':
            switch (get_db_type()) {
                case 'sqlite':
                    $function = 'MIN';
                    break;
                case 'sqlserver':
                case 'sqlserver_odbc':
                case 'access':
                    $ret = '(SELECT MIN(X) FROM (';
                    foreach ($args as $i => $arg) {
                        if ($i != 0) {
                            $ret .= ' UNION ALL ';
                        }
                        $ret .= 'SELECT ' . $arg . ' AS X';
                    }
                    $ret .= ') ' . 'x' . md5(uniqid('', true)) . ')';
                    return $ret;
            }
            break;

        case 'GREATEST':
            switch (get_db_type()) {
                case 'sqlite':
                    $function = 'MAX';
                    break;
                case 'sqlserver':
                case 'sqlserver_odbc':
                case 'access':
                    $ret = '(SELECT MAX(X) FROM (';
                    foreach ($args as $i => $arg) {
                        if ($i != 0) {
                            $ret .= ' UNION ALL ';
                        }
                        $ret .= 'SELECT ' . $arg . ' AS X';
                    }
                    $ret .= ') ' . 'x' . md5(uniqid('', true)) . ')';
                    return $ret;
            }
            break;

        case 'MOD':
            if (count($args) != 2) {
                fatal_exit(do_lang_tempcode('INTERNAL_ERROR'));
            }
            switch (get_db_type()) {
                case 'access':
                    return $args[0] . ' MOD ' . $args[1];
                case 'postgresql':
                case 'sqlserver':
                case 'sqlserver_odbc':
                case 'sqlite':
                    return $args[0] . ' % ' . $args[1];
            }
            break;

        // This may not be fully supported on all database systems
        case 'GROUP_CONCAT':
            if (count($args) != 2) {
                fatal_exit(do_lang_tempcode('INTERNAL_ERROR'));
            }
            switch (get_db_type()) {
                case 'oracle':
                    return '(SELECT LISTAGG(' . $args[0] . ', \',\') WITHIN GROUP (ORDER BY ' . $args[0] . ') FROM ' . $args[1] . ')';
                case 'postgresql':
                    return '(SELECT array_to_string(array_agg(' . $args[0] . '), \',\') FROM ' . $args[1] . ')';
                case 'sqlserver':
                case 'sqlserver_odbc':
                    return 'STUFF((SELECT \',\'+' . $args[0] . ' FROM ' . $args[1] . ' FOR XML PATH(\'\')), 1, 1, \'\')';
                case 'access': // Not fully supported
                    return '(SELECT TOP 1 ' . $args[0] . ' FROM ' . $args[1] . ')';
                case 'ibm': // Not fully supported
                    return '(SELECT ' . $args[0] . ' FROM ' . $args[1] . ' fetch first 1 rows only)';
                case 'xml':
                    return '(SELECT X_GROUP_CONCAT(' . $args[0] . ') FROM ' . $args[1] . ')';
                case 'mysql':
                case 'mysqli':
                case 'mysql_pdo':
                case 'mysql_dbx':
                case 'sqlite':
                default:
                    return '(SELECT GROUP_CONCAT(' . $args[0] . ') FROM ' . $args[1] . ')';
            }
            break;

        case 'X_ORDER_BY_BOOLEAN':
            if (count($args) != 1) {
                fatal_exit(do_lang_tempcode('INTERNAL_ERROR'));
            }
            switch (get_db_type()) {
                case 'sqlserver':
                case 'sqlserver_odbc':
                    return '(CASE WHEN ' . $args[0] . ' THEN 0 ELSE 1 END)';

                default:
                    return $args[0];
            }
            break;
    }

    if (get_db_type() == 'xml') {
        $function = 'X_' . $function;
    }

    // Default handling
    return $function . '(' . implode(',', $args) . ')';
}

/**
 * Get the number of rows in a table, with approximation support for performance (if necessary on the particular database backend).
 *
 * @param string $table The table name
 * @param  ?array $where WHERE clauses if it will help get a more reliable number when we're not approximating in map form (null: none)
 * @param  ?string $where_clause WHERE clauses if it will help get a more reliable number when we're not approximating in SQL form (null: none)
 * @param  ?object $db The DB connection to check against (null: site's main active forum database)
 * @return ?integer The count (null: do it normally)
 */
function get_table_count_approx($table, $where = null, $where_clause = null, $db = null)
{
    if ($db === null) {
        $db = $GLOBALS['SITE_DB'];
    }

    if ($where === null) {
        $where = array();
    }

    if (method_exists($GLOBALS['DB_STATIC_OBJECT'], 'get_table_count_approx')) {
        $test = $GLOBALS['DB_STATIC_OBJECT']->get_table_count_approx($table, $where, $where_clause, $db);
        if ($test !== null) {
            return $test;
        }
    }

    return $db->query_select_value($table, 'COUNT(*)', $where, ($where_clause === null) ? '' : (' AND ' . $where_clause));
}

/**
 * Create an SQL cast.
 * Note if using this in a SELECT clause you should use 'AS', as you can't predict what the CAST is going to set the field name as.
 *
 * @param string $field The field identifier
 * @param string $type The type wanted
 * @set CHAR INT FLOAT
 * @return string The database type
 */
function db_cast($field, $type)
{
    if (method_exists($GLOBALS['DB_STATIC_OBJECT'], 'db_cast')) {
        return $GLOBALS['DB_STATIC_OBJECT']->db_cast($field, $type);
    }

    switch ($type) {
        case 'CHAR':
        case 'INT':
        case 'FLOAT':
            $_type = $type;
            break;

        default:
            fatal_exit(do_lang_tempcode('INTERNAL_ERROR'));
    }

    return 'CAST(' . $field . ' AS ' . $_type . ')';
}

/**
 * Get the type of database installed, such as MySQL, or Oracle.
 *
 * @return string The database type
 */
function get_db_type()
{
    global $SITE_INFO;
    if (!isset($SITE_INFO['db_type'])) {
        return is_dir(get_custom_file_base() . '/uploads/website_specific/' . get_db_site()) ? 'xml' : 'mysql';
    }
    $ret = $SITE_INFO['db_type'];
    if ($ret === 'mysql' && !function_exists('mysql_connect')) {
        $ret = 'mysqli';
    }
    return $ret;
}

/**
 * Find Composr was installed to use persistent database connections or not.
 *
 * @return boolean Whether to use persistent database connections
 */
function get_use_persistent()
{
    global $SITE_INFO;
    return (!empty($SITE_INFO['use_persistent'])) ? ($SITE_INFO['use_persistent'] == '1') : false;
}

/**
 * Get the table prefixes used for all Composr tables, commonly used when you are installing Composr in the same database as your forums. The default table prefix is 'cms_'. Note that anything that might write to an arbitrary db, must ask that db for it's table prefix (if it needs it of course... the db abstracts away most needs for it)
 *
 * @return string The table prefix
 */
function get_table_prefix()
{
    global $SITE_INFO;
    if (!isset($SITE_INFO['table_prefix'])) {
        return 'cms' . strval(cms_version()) . '_';
    }
    return $SITE_INFO['table_prefix'];
}

/**
 * Get the host of the database ('localhost', for example).
 *
 * @return string The database host
 */
function get_db_site_host()
{
    global $SITE_INFO;
    return (!empty($SITE_INFO['db_site_host'])) ? $SITE_INFO['db_site_host'] : 'localhost';
}

/**
 * Get the name of the database.
 *
 * @return string The database site
 */
function get_db_site()
{
    global $SITE_INFO;
    if (empty($SITE_INFO['db_site'])) {
        return basename(get_file_base());
    }
    return $SITE_INFO['db_site'] . (($GLOBALS['CURRENT_SHARE_USER'] === null) ? '' : ('_' . $GLOBALS['CURRENT_SHARE_USER']));
}

/**
 * Get the database username.
 *
 * @return string The database username
 */
function get_db_site_user()
{
    global $SITE_INFO;
    if ($GLOBALS['CURRENT_SHARE_USER'] !== null) {
        return substr(md5($SITE_INFO['db_site_user'] . '_' . $GLOBALS['CURRENT_SHARE_USER']), 0, 16);
    }
    return (!empty($SITE_INFO['db_site_user'])) ? $SITE_INFO['db_site_user'] : 'root';
}

/**
 * Get the database password.
 *
 * @return string The database password
 */
function get_db_site_password()
{
    global $SITE_INFO;
    return array_key_exists('db_site_password', $SITE_INFO) ? $SITE_INFO['db_site_password'] : '';
}

/**
 * Get the host of the forum database ('localhost', for example).
 *
 * @return string The database host
 */
function get_db_forums_host()
{
    global $SITE_INFO;
    return (!empty($SITE_INFO['db_forums_host'])) ? $SITE_INFO['db_forums_host'] : (!empty($SITE_INFO['db_site_host']) ? $SITE_INFO['db_site_host'] : 'localhost');
}

/**
 * Get the name of the forum database.
 *
 * @return string The forum database site
 */
function get_db_forums()
{
    global $SITE_INFO;
    if (empty($SITE_INFO['db_forums'])) {
        return get_db_site();
    }
    return $SITE_INFO['db_forums'] . (($GLOBALS['CURRENT_SHARE_USER'] === null) ? '' : ('_' . $GLOBALS['CURRENT_SHARE_USER']));
}

/**
 * Get the forum database username.
 *
 * @return string The forum database username
 */
function get_db_forums_user()
{
    global $SITE_INFO;
    if (empty($SITE_INFO['db_forums_user'])) {
        return get_db_site_user();
    }
    if ($GLOBALS['CURRENT_SHARE_USER'] !== null) {
        return substr(md5($SITE_INFO['db_forums_user'] . '_' . $GLOBALS['CURRENT_SHARE_USER']), 0, 16);
    }
    return $SITE_INFO['db_forums_user'];
}

/**
 * Get the forum database password.
 *
 * @return string The forum database password
 */
function get_db_forums_password()
{
    global $SITE_INFO;
    if (!array_key_exists('db_forums_password', $SITE_INFO)) {
        return get_db_site_password();
    }
    return $SITE_INFO['db_forums_password'];
}

/**
 * Find whether we are on a multi-site-network.
 *
 * @param  ?object $db The DB connection to check against (null: site's main active forum database)
 * @return boolean Whether we are
 */
function is_on_multi_site_network($db = null)
{
    static $cache = null;
    if ($db === null && $cache !== null) {
        return $cache;
    }

    if (get_forum_type() == 'none') {
        $cache = false;
        return false;
    }

    if ($db !== null) {
        $ret = !is_forum_db($db); // If passed connection is not the same as the forum connection, then it must be a multi-site-network
        return $ret;
    }
    $cache = ((get_db_site_host() != get_db_forums_host()) || (get_db_site() != get_db_forums()) || (isset($GLOBALS['FORUM_DRIVER'])) && ($GLOBALS['FORUM_DRIVER']->get_drivered_table_prefix() != get_table_prefix()));
    return $cache;
}

/**
 * Find whether a database connection is to the forum database.
 *
 * @param  object $db The DB connection to check against
 * @return boolean Whether we are
 */
function is_forum_db($db)
{
    if (isset($db->is_forum_db)) {
        return $db->is_forum_db;
    }

    if (!is_on_multi_site_network()) {
        // Not on a multi-site-network
        return false;
    }

    $ret = ((isset($GLOBALS['FORUM_DB'])) && ($db->connection_write == $GLOBALS['FORUM_DB']->connection_write) && ($db->connection_write != $GLOBALS['SITE_DB']->connection_write));
    $db->is_forum_db = $ret;
    return $ret;
}

/**
 * Database handling.
 *
 * @package    core
 */
class DatabaseConnector
{
    public $table_prefix;
    public $connection_read, $connection_write;

    public $text_lookup_original_cache;
    public $text_lookup_cache;

    public $table_exists_cache;

    public $static_ob;

    public $dedupe_mode = false;

    /**
     * Construct a database driver from connection parameters.
     *
     * @param string $db_name The database name
     * @param string $db_host The database server
     * @param string $db_user The connection username
     * @param string $db_password The connection password
     * @param string $table_prefix The table prefix
     * @param boolean $fail_ok Whether to on error echo an error and return with a null, rather than giving a critical error
     * @param ?object         $static Static call object (null: use global static call object)
     */
    public function __construct($db_name, $db_host, $db_user, $db_password, $table_prefix, $fail_ok = false, $static = null)
    {
        $this->text_lookup_original_cache = array();
        $this->text_lookup_cache = array();
        $this->table_exists_cache = array();

        $servers = explode(',', $db_host);
        if (count($servers) == 1) {
            $this->connection_write = array(get_use_persistent(), $db_name, $db_host, $db_user, $db_password, $fail_ok);
            $this->connection_read = $this->connection_write;
        } else {
            $this->connection_write = array(get_use_persistent(), $db_name, $servers[0], $db_user, $db_password, $fail_ok);
            $min = (count($servers) == 2) ? 0 : 1;
            $this->connection_read = array(get_use_persistent(), $db_name, $servers[mt_rand($min, count($servers) - 1)], $db_user, $db_password, $fail_ok);
        }
        $this->table_prefix = $table_prefix;

        if ($static !== null) {
            $this->static_ob = $static;
        } else {
            $this->static_ob = $GLOBALS['DB_STATIC_OBJECT'];
        }
    }

    /**
     * Get the table prefixes used for all Composr tables, commonly used when you are installing Composr in the same database as your forums. The default table prefix is 'cms_'.
     *
     * @return string The table prefix
     */
    public function get_table_prefix()
    {
        return $this->table_prefix;
    }

    /**
     * Check if a table exists.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  boolean $really Check direct, not using meta-table (if possible)
     * @return boolean Whether it exists
     */
    public function table_exists($table_name, $really = false)
    {
        if ($really && strpos(get_db_type(), 'mysql') !== false) {
            // Just works with MySQL (too complex to do for all SQL's http://forums.whirlpool.net.au/forum-replies-archive.cfm/523219.html)
            $full_table_name = $this->get_table_prefix() . $table_name;
            $rows = $this->query("SHOW TABLES LIKE '" . $full_table_name . "'");
            foreach ($rows as $row) {
                foreach ($row as $field) {
                    if ($field == $full_table_name) {
                        return true;
                    }
                }
            }
            return false;
        }

        if (array_key_exists($table_name, $this->table_exists_cache)) {
            return $this->table_exists_cache[$table_name];
        }

        $test = $this->query_select_value_if_there('db_meta', 'm_name', array('m_table' => $table_name));
        $this->table_exists_cache[$table_name] = ($test !== null);
        return $this->table_exists_cache[$table_name];
    }

    /**
     * Create a table with the given name and the given array of field name to type mappings.
     * If a field type starts '*', then it is part of that field's key. If it starts '?', then it is an optional field.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  array $fields The fields
     * @param  boolean $skip_size_check Whether to skip the size check for the table (only do this for addon modules that don't need to support anything other than MySQL)
     * @param  boolean $skip_null_check Whether to skip the check for null string fields
     * @param  ?boolean $save_bytes Whether to use lower-byte table storage, with tradeoffs of not being able to support all unicode characters; use this if key length is an issue (null: auto-detect if needed). Pass as true/false for normal install code to make intentions explicit, maintenance code may use auto-detect.
     */
    public function create_table($table_name, $fields, $skip_size_check = false, $skip_null_check = false, $save_bytes = false)
    {
        require_code('database_helper');
        _helper_create_table($this, $table_name, $fields, $skip_size_check, $skip_null_check, $save_bytes);
    }

    /**
     * Add an index to a table without disturbing the contents, after the table has been created.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  ID_TEXT $index_name The index name
     * @param  array $fields The fields
     * @param  ?string $unique_key_fields Comma-separated names of the unique key field for the table (null: lookup)
     */
    public function create_index($table_name, $index_name, $fields, $unique_key_fields = null)
    {
        require_code('database_helper');
        _helper_create_index($this, $table_name, $index_name, $fields, $unique_key_fields);
    }

    /**
     * Turn a list of maps into the bulk-insert format used by query_insert.
     *
     * @param  array $maps List of maps
     * @return array The row format for bulk-inserts.
     */
    public function bulk_insert_flip($maps)
    {
        $data = array();
        foreach ($maps as $map) {
            foreach ($map as $key => $val) {
                if (!isset($data[$key])) {
                    $data[$key] = array();
                }
                $data[$key][] = $val;
            }
        }
        return $data;
    }

    /**
     * Insert a row.
     *
     * @param  string $table The table name
     * @param  array $map The insertion map. Each map may point to an array of values for bulk-inserts, as long as the size of each array is the same
     * @param  boolean $ret Whether to return the auto-insert-id
     * @param  boolean $fail_ok Whether to allow failure (outputting a message instead of exiting completely)
     * @param  boolean $save_as_volatile Whether we are saving as a 'volatile' file extension (used in the XML DB driver, to mark things as being non-syndicated to git)
     * @return mixed The ID of the new row
     */
    public function query_insert($table, $map, $ret = false, $fail_ok = false, $save_as_volatile = false)
    {
        $keys = '';
        $all_values = array(); // will usually only have a single entry; for bulk-inserts it will have as many as there are inserts

        $eis = $this->static_ob->db_empty_is_null();

        foreach ($map as $key => $value) {
            if ($keys !== '') {
                $keys .= ', ';
            }
            $keys .= $key;

            $_value = (!is_array($value)) ? array($value) : $value;

            $v = mixed();
            foreach ($_value as $i => $v) {
                if (!isset($all_values[$i])) {
                    $all_values[$i] = '';
                }
                $values = $all_values[$i];

                if ($values !== '') {
                    $values .= ', ';
                }

                if ($value === null) {
                    if (($eis) && ($v === '')) {
                        $values .= '\' \'';
                    } else {
                        $values .= 'NULL';
                    }
                } else {
                    if (($eis) && ($v === '')) {
                        $v = ' ';
                    }
                    if (is_integer($v)) {
                        $values .= strval($v);
                    } elseif (is_float($v)) {
                        $values .= number_format($v, 10, '.', '');
                    } elseif (($key === 'begin_num') || ($key === 'end_num')) {
                        $values .= $v; // FUDGE: for all our known large unsigned integers
                    } else {
                        $values .= '\'' . $this->static_ob->db_escape_string($v) . '\'';
                    }
                }

                $all_values[$i] = $values; // essentially appends, as $values was loaded from former $all_values[$i] value
            }
        }

        if (count($all_values) === 1) { // usually $all_values only has length of 1
            if ((function_exists('get_value')) && (get_value('enable_delayed_inserts') === '1') && (in_array($table, array('stats', 'banner_clicks', 'member_tracking', 'usersonline_track', 'download_logging'/*Ideally we would define this list via database_relations.php, but performance matters*/))) && (substr(get_db_type(), 0, 5) === 'mysql')) {
                $query = 'INSERT DELAYED INTO ' . $this->table_prefix . $table . ' (' . $keys . ') VALUES (' . $all_values[0] . ')';
            } else {
                $query = 'INSERT INTO ' . $this->table_prefix . $table . ' (' . $keys . ') VALUES (' . $all_values[0] . ')';
            }
        } else {
            if (count($all_values) === 0) {
                return null;
            }

            if ((get_db_type() == 'oracle') || (get_db_type() == 'access')) {
                // These databases are not supporting batch syntax
                // TODO: Improve logic in v11

                foreach ($all_values as $v) {
                    $query = 'INSERT INTO ' . $this->table_prefix . $table . ' (' . $keys . ') VALUES (' . $v . ')';
                    $this->_query($query, null, null, $fail_ok, $ret, null, '', $save_as_volatile);
                }

                return null;
            }

            // So we can do batch inserts...
            $all_v = '';
            foreach ($all_values as $v) {
                if ($all_v !== '') {
                    $all_v .= ', ';
                }
                $all_v .= '(' . $v . ')';
            }

            $query = 'INSERT INTO ' . $this->table_prefix . $table . ' (' . $keys . ') VALUES ' . $all_v;
        }

        return $this->_query($query, null, null, $fail_ok, $ret, null, '', $save_as_volatile);
    }

    /**
     * Create a SELECT query from some abstract data.
     *
     * @param  string $table The table to select from
     * @param  ?array $select_map List of field selections (null: all fields)
     * @param  ?array $where_map Map of conditions to enforce (null: no conditions)
     * @param  string $end Additional stuff to tack onto the query
     * @return string SQL query
     */
    protected function _get_where_expand($table, $select_map = null, $where_map = null, $end = '')
    {
        global $DEV_MODE;

        if ($select_map === null) {
            $select_map = array('*');
        }

        $select = '';
        foreach ($select_map as $key) {
            //if (!is_string($key)) $key = strval($key);   Should not happen, but won't cause a problem if does. Don't do this check for performance reasons.

            if ($select !== '') {
                $select .= ',';
            }

            $select .= $key;
        }

        $where = '';
        if (($where_map !== null) && ($where_map != array())) {
            foreach ($where_map as $key => $value) {
                if ($DEV_MODE) {
                    if (!is_string($key)) {
                        fatal_exit('Parameters to the database API given in the wrong order. Please check the function call.');
                    }
                }

                if ($where !== '') {
                    $where .= ' AND ';
                }

                if (is_float($value)) {
                    $where .= $key . '=' . number_format($value, 10, '.', '');
                } elseif (is_integer($value)) {
                    $where .= $key . '=' . strval($value);
                } elseif (($key === 'begin_num') || ($key === 'end_num')) {
                    $where .= $key . '=' . $value; // FUDGE: for all our known large unsigned integers
                } else {
                    if ($value === null) {
                        $where .= $key . ' IS NULL';
                    } else {
                        if (($value === '') && ($this->static_ob->db_empty_is_null())) {
                            $value = ' ';
                        }

                        $where .= db_string_equal_to($key, $value);
                    }
                }
            }

            return 'SELECT ' . $select . ' FROM ' . $table . ' WHERE (' . $where . ') ' . $end;
        }
        if (substr(ltrim($end), 0, 6) !== 'WHERE ') {
            $end = 'WHERE 1=1 ' . $end; // We force a WHERE so that code of ours that alters queries can work robustly
        }
        return 'SELECT ' . $select . ' FROM ' . $table . ' ' . $end;
    }

    /**
     * Get the specified value from the database. This is the specified value of the first row returned. A fatal error is produced if there is no matching row.
     *
     * @param  string $table The table name
     * @param  string $selected_value The field to select
     * @param  ?array $where_map The WHERE map [will all be AND'd together] (null: no where conditions)
     * @param  string $end Something to tack onto the end
     * @param  boolean $fail_ok Whether to allow failure (outputting a message instead of exiting completely)
     * @param  ?array $lang_fields Extra language fields to join in for cache-prefilling. You only need to send this if you are doing a JOIN and carefully craft your query so table field names won't conflict (null: auto-detect, if not a join)
     * @return mixed The first value of the first row returned
     */
    public function query_select_value($table, $selected_value, $where_map = null, $end = '', $fail_ok = false, $lang_fields = null)
    {
        $values = $this->query_select($table, array($selected_value), $where_map, $end, 1, null, $fail_ok, $lang_fields);
        if ($values === null) {
            return null; // error
        }
        if (!array_key_exists(0, $values)) {
            fatal_exit(do_lang_tempcode('QUERY_NULL', escape_html($this->_get_where_expand($this->table_prefix . $table, array($selected_value), $where_map, $end)))); // No result found
        }
        return $this->_query_select_value($values);
    }

    /**
     * Extract the first of the first of the list of maps.
     *
     * @param  array $values The list of maps
     * @return mixed The first value of the first row in the list
     */
    protected function _query_select_value($values)
    {
        if (!array_key_exists(0, $values)) {
            return null; // No result found
        }
        $first = $values[0];
        $v = current($first); // Result found. Maybe a value of 'null'
        return $v;
    }

    /**
     * Get the specified value from the database, or null if there is no matching row (or if the value itself is null). This is good for detection existence of records, or for use if they might may or may not be present.
     *
     * @param  string $table The table name
     * @param  string $select The field to select
     * @param  ?array $where_map The WHERE map [will all be AND'd together] (null: no where conditions)
     * @param  string $end Something to tack onto the end
     * @param  boolean $fail_ok Whether to allow failure (outputting a message instead of exiting completely)
     * @param  ?array $lang_fields Extra language fields to join in for cache-prefilling. You only need to send this if you are doing a JOIN and carefully craft your query so table field names won't conflict (null: auto-detect, if not a join)
     * @return ?mixed The first value of the first row returned (null: nothing found, or null value found)
     */
    public function query_select_value_if_there($table, $select, $where_map = null, $end = '', $fail_ok = false, $lang_fields = null)
    {
        $values = $this->query_select($table, array($select), $where_map, $end, 1, null, $fail_ok, $lang_fields);
        if ($values === null) {
            return null; // error
        }
        return $this->_query_select_value($values);
    }

    /**
     * This function is a variant of query_select_value_if_there, by the fact that it only accepts a complete (and perfect) SQL query, instead of assembling one itself from the specified parameters.
     *
     * @param  string $query The complete SQL query
     * @param  boolean $fail_ok Whether to allow failure (outputting a message instead of exiting completely)
     * @param  boolean $skip_safety_check Whether to skip the query safety check
     * @param  ?array $lang_fields Extra language fields to join in for cache-prefilling. You only need to send this if you are doing a JOIN and carefully craft your query so table field names won't conflict (null: auto-detect, if not a join)
     * @return ?mixed The first value of the first row returned (null: nothing found, or null value found)
     */
    public function query_value_if_there($query, $fail_ok = false, $skip_safety_check = false, $lang_fields = null)
    {
        global $DEV_MODE;

        if (($DEV_MODE) || (!has_solemnly_declared(I_UNDERSTAND_SQL_INJECTION))) {
            if (!is_bool($fail_ok)) {
                fatal_exit('You probably wanted to use query_select_value_if_there');
            }

            if (!$skip_safety_check) {
                require_code('database_security_filter');

                if (is_simple_query($query)) {
                    fatal_exit('It is highly recommended to use query_select/query_update/query_delete helper methods instead of the \'query\' method for this query -- in order to avoid having to write any SQL');
                }

                if (!has_escaped_dynamic_sql($query)) {
                    fatal_exit('Dynamic SQL has not been escaped properly in ' . $query);
                }
            }
        }

        $values = $this->query($query, 1, null, $fail_ok, $skip_safety_check, $lang_fields);
        if ($values === null) {
            return null; // error
        }
        return $this->_query_select_value($values);
    }

    /**
     * Get the database rows found matching the specified parameters. Unlike 'query', it doesn't take raw SQL -- it assembles SQL based the parameters requested.
     * Only use this if you're where condition is a series of AND clauses doing simple property comparisons.
     *
     * @param  string $table The table name
     * @param  ?array $select The SELECT map (null: all fields)
     * @param  ?array $where_map The WHERE map [will all be AND'd together] (null: no conditions)
     * @param  string $end Something to tack onto the end of the SQL query
     * @param  ?integer $max The maximum number of rows to select (null: get all)
     * @param  ?integer $start The starting row to select (null: start at first)
     * @param  boolean $fail_ok Whether to allow failure (outputting a message instead of exiting completely)
     * @param  ?array $lang_fields Extra language fields to join in for cache-prefilling. You only need to send this if you are doing a JOIN and carefully craft your query so table field names won't conflict (null: auto-detect, if not a join)
     * @return array The results (empty array: empty result set)
     */
    public function query_select($table, $select = null, $where_map = null, $end = '', $max = null, $start = null, $fail_ok = false, $lang_fields = null)
    {
        $full_table = $this->table_prefix . $table;

        $field_prefix = '';

        if ($select === null) {
            $select = array('*');
        }

        $this->_automatic_lang_fields($table, $full_table, $select, $where_map, $end, $lang_fields);

        return $this->_query($this->_get_where_expand($full_table, $select, $where_map, $end), $max, $start, $fail_ok, false, $lang_fields, $field_prefix);
    }

    /**
     * Work out $lang_fields from analysing the table, if needed.
     *
     * @param  string $table The table name
     * @param  string $full_table The table name, with prefix too
     * @param  array $select The SELECT map
     * @param  ?array $where_map The WHERE map [will all be AND'd together] (null: no conditions)
     * @param  string $end Something to tack onto the end of the SQL query
     * @param  ?array $lang_fields Extra language fields to join in for cache-prefilling. You only need to send this if you are doing a JOIN and carefully craft your query so table field names won't conflict (null: auto-detect, if not a join)
     */
    protected function _automatic_lang_fields(&$table, &$full_table, &$select, &$where_map, &$end, &$lang_fields)
    {
        // Optimisation for entirely automatic translate table linkage (only done on non-joins, as this removes a whole lot of potential complexities -- if people are doing joins they go a little further to do this manually anyway; also we make sure we're operating on our site's table prefix so we don't collect meta info for the wrong table set)
        if ($lang_fields === null) {
            if (
                ($table !== 'translate') &&
                (strpos($table, ' ') === false) &&
                (strpos($end, 'GROUP BY ') === false/*Can only SELECT what is also in GROUP BY*/) &&
                ((isset($GLOBALS['SITE_DB'])) &&
                ($this->table_prefix === $GLOBALS['SITE_DB']->table_prefix) || (get_forum_type() === 'cns'))
            ) {
                global $TABLE_LANG_FIELDS_CACHE;
                $lang_fields_provisional = isset($TABLE_LANG_FIELDS_CACHE[$table]) ? $TABLE_LANG_FIELDS_CACHE[$table] : array();
                $lang_fields = array();

                if ($lang_fields_provisional !== array()) {
                    $full_table .= ' main';

                    foreach ($select as $i => $s) {
                        if (!is_string($s)) {
                            $lang_fields_provisional = array();
                            break; // Bad API call, but we'll let it fail naturally
                        }

                        if (preg_match('#^[A-Za-z\_\*]+$#', $s) !== 0) {
                            $select[$i] = 'main.' . $s;
                        }
                    }
                    if ($where_map !== null) {
                        foreach ($where_map as $i => $s) {
                            if (!is_string($i)) {
                                $lang_fields_provisional = array();
                                break; // Bad API call, but we'll let it fail naturally
                            }

                            if (preg_match('#^[A-Za-z\_]+$#', $i) !== 0) {
                                unset($where_map[$i]);
                                $where_map['main.' . $i] = $s;
                            }
                        }
                    }
                    if ($end !== '') {
                        $end = cms_preg_replace_safe('#(^|,|\s)([a-z]+)($|,|\s)#', '${1}main.${2}${3}', $end);
                    }

                    $field_prefix = 'main.';

                    $select_inv = array_flip($select);

                    foreach ($lang_fields_provisional as $lang_field => $field_type) {
                        if (
                            (isset($select_inv[$field_prefix . '*'])) ||

                            (isset($select_inv[$field_prefix . $lang_field])) ||

                            (isset($select_inv['t_' . $lang_field . '.text_original'])) ||
                            ((!is_null($where_map)) && (isset($where_map['t_' . $lang_field . '.text_original']))) ||
                            (strpos($end, 't_' . $lang_field . '.text_original') !== false)
                        ) {
                            $lang_fields[$lang_field] = $field_type;
                        }
                    }
                }
            }
        }
    }

    /**
     * Do a named-parameter query, like "SELECT * FROM foo WHERE a='{value}';" with array('value' => 'b').
     * Assumes nothing looking like {example} is in the query already.
     * Also supports {prefix} for encoding the table prefix.
     * Lots of programmers like to do queries like this as it reduces the chance of accidentally forgetting to escape a parameter inserted directly/manually within a longer query.
     * Usually in Composr we use APIs like query_select, which avoids the need for SQL all-together, but this doesn't work for all patterns of query.
     *
     * @param  string $query The complete parameter-ready SQL query
     * @param  array $parameters The query parameters (a map)
     * @param  ?integer $max The maximum number of rows to affect (null: no limit)
     * @param  ?integer $start The start row to affect (null: no specification)
     * @param  boolean $fail_ok Whether to output an error on failure
     * @param  boolean $skip_safety_check Whether to skip the query safety check
     * @param  ?array $lang_fields Extra language fields to join in for cache-prefilling. You only need to send this if you are doing a JOIN and carefully craft your query so table field names won't conflict (null: auto-detect, if not a join)
     * @param  string $field_prefix All the core fields have a prefix of this on them, so when we fiddle with language lookup we need to use this (only consider this if you're setting $lang_fields)
     * @return ?mixed The results (null: no result set) (empty array: empty result set)
     */
    public function query_parameterised($query, $parameters, $max = null, $start = null, $fail_ok = false, $skip_safety_check = false, $lang_fields = null, $field_prefix = '')
    {
        $query = $this->_query_parameterised($query, $parameters);
        return $this->query($query, $max, $start, $fail_ok, $skip_safety_check, $lang_fields, $field_prefix);
    }

    /**
     * Helper for query_parameterised.
     *
     * @param  string $query The complete parameter-ready SQL query
     * @param  array $parameters The query parameters (a map)
     * @return string Parameterised query
     */
    public function _query_parameterised($query, $parameters)
    {
        if (isset($parameters['prefix'])) {
            warn_exit('prefix is a reserved parameter, you should not set it.');
        }

        $val = mixed();

        $len = strlen($query);
        $current_parameter = null;
        $query_new = '';
        for ($i = 0; $i < $len; $i++) {
            $c = $query[$i];
            if ($current_parameter === null) {
                if ($c == '{') {
                    $current_parameter = '';
                    $in_quotes_start = (($i > 0) && ($query[$i - 1] == "'"));
                } else {
                    $query_new .= $c;
                }
            } else {
                if ($c == '}') {
                    if ($current_parameter == 'prefix') {
                        $query_new .= $this->get_table_prefix();
                    } elseif (array_key_exists($current_parameter, $parameters)) {
                        $in_quotes_end = (($i < $len - 1) && ($query[$i + 1] == "'"));

                        $val = $parameters[$current_parameter];
                        if (is_integer($val)) {
                            $val = strval($val);

                            if ($in_quotes_start || $in_quotes_end) {
                                $val = db_escape_string($val);
                            }
                        } elseif (is_float($val)) {
                            $val = number_format($val, 10, '.', '');

                            if ($in_quotes_start || $in_quotes_end) {
                                $val = db_escape_string($val);
                            }
                        } elseif (is_bool($val)) {
                            $val = ($val ? '1' : '0');

                            if ($in_quotes_start || $in_quotes_end) {
                                $val = db_escape_string($val);
                            }
                        } elseif ($val === null) {
                            $val = 'NULL';

                            if ($in_quotes_start) {
                                $query_new = substr($query_new, 0, strlen($query_new) - 1);
                            }
                            if ($in_quotes_end) {
                                $i++;
                            }
                        } else {
                            if (!is_string($val)) {
                                $val = strval($val);
                            }

                            $val = db_escape_string($val);
                            if (!$in_quotes_start) {
                                $val = "'" . $val;
                            }
                            if (!$in_quotes_end) {
                                $val .= "'";
                            }
                        }

                        $query_new .= $val;
                    } else {
                        $query_new .= '{' . $current_parameter . '}'; // No match so leave alone
                    }
                    $current_parameter = null;
                } else {
                    $current_parameter .= $c;
                }
            }
        }

        return $query_new;
    }

    /**
     * This function is a raw query executor. It shouldn't usually be used unless you need to write SQL involving 'OR' statements or other complexities. There are abstracted versions available which you probably want instead (mainly, query_select).
     *
     * @param  string $query The complete SQL query
     * @param  ?integer $max The maximum number of rows to affect (null: no limit)
     * @param  ?integer $start The start row to affect (null: no specification)
     * @param  boolean $fail_ok Whether to output an error on failure
     * @param  boolean $skip_safety_check Whether to skip the query safety check
     * @param  ?array $lang_fields Extra language fields to join in for cache-prefilling. You only need to send this if you are doing a JOIN and carefully craft your query so table field names won't conflict (null: auto-detect, if not a join)
     * @param  string $field_prefix All the core fields have a prefix of this on them, so when we fiddle with language lookup we need to use this (only consider this if you're setting $lang_fields)
     * @return ?mixed The results (null: no result set) (empty array: empty result set)
     */
    public function query($query, $max = null, $start = null, $fail_ok = false, $skip_safety_check = false, $lang_fields = null, $field_prefix = '')
    {
        global $DEV_MODE;
        if (!$skip_safety_check && stripos($query, 'union') !== false && strpos(get_db_type(), 'mysql') !== false) {
            $_query = cms_preg_replace_safe('#\s#', ' ', strtolower($query));
            $queries = 1;//substr_count($_query,'insert into ')+substr_count($_query,'replace into ')+substr_count($_query,'update ')+substr_count($_query,'select ')+substr_count($_query,'delete from '); Not reliable
            if ((strpos(preg_replace('#\'[^\']*\'#', '\'\'', str_replace('\\\'', '', $_query)), ' union ') !== false) || ($queries > 1)) {
                log_hack_attack_and_exit('SQL_INJECTION_HACK', $query);
            }

            if (function_exists('has_solemnly_declared')) {
                if (($DEV_MODE) || (!has_solemnly_declared(I_UNDERSTAND_SQL_INJECTION))) {
                    require_code('database_security_filter');

                    if (is_simple_query($query)) {
                        fatal_exit('It is highly recommended to use query_select/query_update/query_delete method instead of the \'query\' method for this query');
                    }

                    if (!has_escaped_dynamic_sql($query)) {
                        fatal_exit('Dynamic SQL has not been escaped properly');
                    }
                }
            }
        }

        return $this->_query($query, $max, $start, $fail_ok, false, $lang_fields, $field_prefix);
    }

    /**
     * Convert a field name of type SHORT/LONG_TRANS[__COMCODE] into something we may use directly in our SQL.
     * Assumes the query has separately been informed of the $lang_fields parameter (which is automatic for query_select).
     *
     * @param  ID_TEXT $field_name Language field name
     * @return ID_TEXT SQL field name reference
     */
    public function translate_field_ref($field_name)
    {
        if (multi_lang_content()) {
            return 't_' . $field_name . '.text_original';
        }
        return $field_name;
    }

    /**
     * This function is a very basic query executor. It shouldn't usually be used by you, as there are specialised abstracted versions available.
     *
     * @param  string $query The complete SQL query
     * @param  ?integer $max The maximum number of rows to affect (null: no limit)
     * @param  ?integer $start The start row to affect (null: no specification)
     * @param  boolean $fail_ok Whether to output an error on failure
     * @param  boolean $get_insert_id Whether to get an insert ID
     * @param  ?array $lang_fields Extra language fields to join in for cache-prefilling. You only need to send this if you are doing a JOIN and carefully craft your query so table field names won't conflict (null: auto-detect, if not a join)
     * @param  string $field_prefix All the core fields have a prefix of this on them, so when we fiddle with language lookup we need to use this (only consider this if you're setting $lang_fields)
     * @param  boolean $save_as_volatile Whether we are saving as a 'volatile' file extension (used in the XML DB driver, to mark things as being non-syndicated to git)
     * @return ?mixed The results (null: no result set) (empty array: empty result set)
     */
    public function _query($query, $max = null, $start = null, $fail_ok = false, $get_insert_id = false, $lang_fields = null, $field_prefix = '', $save_as_volatile = false)
    {
        global $QUERY_COUNT, $NO_QUERY_LIMIT, $QUERY_LOG, $QUERY_LIST, $DEV_MODE, $IN_MINIKERNEL_VERSION, $QUERY_FILE_LOG, $UPON_QUERY_HOOKS_CACHE;

        if ($QUERY_FILE_LOG !== null) {
            flock($QUERY_FILE_LOG, LOCK_EX);
            fseek($QUERY_FILE_LOG, 0, SEEK_END);
            fwrite($QUERY_FILE_LOG, $query . ';' . "\n\n");
            flock($QUERY_FILE_LOG, LOCK_UN);
        }

        if ($DEV_MODE) {
            if (!$GLOBALS['NO_DB_SCOPE_CHECK']) {
                if ((!multi_lang_content()) && (strpos($query, $this->get_table_prefix() . 'translate') !== false) && (strpos($query, 'CHECK TABLE') === false) && (strpos($query, 'DROP TABLE') === false) && (strpos($query, 'DROP INDEX') === false) && (strpos($query, 'ALTER TABLE') === false) && (strpos($query, 'CREATE TABLE') === false)) {
                    fatal_exit('Assumption of multi-lang-content being on, and it\'s not');
                }

                if ((get_forum_type() != 'none') && (strpos($query, get_table_prefix() . 'f_') !== false) && (strpos($query, get_table_prefix() . 'f_') < 100) && (strpos($query, 'f_welcome_emails') === false) && (!is_forum_db($this)) && (is_cns_satellite_site())) {
                    fatal_exit('Using Conversr queries on the wrong driver');
                }
            }
        }

        if (!$NO_QUERY_LIMIT) {
            $QUERY_COUNT++;
            /*if ($QUERY_COUNT > 10) {
                cms_ob_end_clean();
            }
            @print('Query: ' . $query . "\n");*/
        }
        if ((!headers_sent()) && (function_exists('fb')) && (get_param_integer('keep_firephp_queries', 0) === 1)) {
            fb('Query: ' . $query);
        }

        if (($QUERY_COUNT === 250) && (get_param_integer('keep_no_query_limit', 0) === 0) && ($GLOBALS['RELATIVE_PATH'] !== '_tests') && (count($_POST) === 0) && (get_page_name() !== 'admin_importer') && (!$IN_MINIKERNEL_VERSION) && (get_param_string('special_page_type', '') !== 'query')) {
            cms_profile_start_for('_query:HIGH_VOLUME_ALERT');

            $NO_QUERY_LIMIT = true;
            $log_path = get_custom_file_base() . '/data_custom/big_query_screens.log';
            if (is_writable_wrap($log_path)) {
                $myfile = fopen($log_path, 'at');
                flock($myfile, LOCK_EX);
                fseek($myfile, 0, SEEK_END);
                fwrite($myfile, get_self_url_easy(true) . "\n");
                flock($myfile, LOCK_UN);
                fclose($myfile);
            }
            if ($DEV_MODE) {
                $QUERY_COUNT = 0;
                fatal_exit(do_lang_tempcode('TOO_MANY_QUERIES'));
            }

            cms_profile_end_for('_query:HIGH_VOLUME_ALERT');
        }

        // Optimisation: Load language fields in advance so we don't need to do additional details when calling get_translated_* functions
        $lang_strings_expecting = array();
        if (!empty($lang_fields)) {
            if (multi_lang_content()) {
                if ((strpos($query, 'text_original') !== false) || (function_exists('user_lang')) && ((is_null($start)) || ($start < 200))) {
                    if (function_exists('user_lang')) {
                        $lang = user_lang();
                    } else {
                        require_code('lang');
                        $lang = get_site_default_lang();
                    }

                    foreach ($lang_fields as $field => $field_type) {
                        $field_stripped = preg_replace('#.*\.#', '', $field);

                        $join = ' LEFT JOIN ' . $this->table_prefix . 'translate t_' . $field_stripped . ' ON t_' . $field_stripped . '.id=' . $field_prefix . $field;
                        $join .= ' AND ' . db_string_equal_to('t_' . $field_stripped . '.language', $lang);

                        $_query = strtoupper($query);
                        $from_pos = strpos($_query, ' FROM ');
                        $where_pos = strpos($_query, ' WHERE ');
                        $from_in_subquery = ($from_pos !== false) && (strpos(substr($_query, 0, $from_pos), '(SELECT') !== false); // FROM clause seems to be in a subquery, so it's mroe robust for us to work backwards
                        $where_in_subquery = ($where_pos !== false) && (strpos(substr($_query, 0, $where_pos), '(SELECT') !== false); // WHERE clause seems to be in a subquery, so it's mroe robust for us to work backwards
                        if ($from_in_subquery || $where_in_subquery) {
                            $from_pos = strrpos($_query, ' FROM ');
                            $where_pos = strrpos($_query, ' WHERE ');
                        }
                        if ($where_pos === false) {
                            $_where_pos = 0;
                            do {
                                $_where_pos = strpos($_query, ' GROUP BY ', $_where_pos + 1);
                                if ($_where_pos !== false) {
                                    $where_pos = $_where_pos;
                                }
                            } while ($_where_pos !== false);
                        }
                        if ($where_pos === false) {
                            $_where_pos = 0;
                            do {
                                $_where_pos = strpos($_query, ' ORDER BY ', $_where_pos + 1);
                                if ($_where_pos !== false) {
                                    $where_pos = $_where_pos;
                                }
                            } while ($_where_pos !== false);
                        }
                        if ($where_pos !== false) {
                            $query = substr($query, 0, $where_pos) . $join . substr($query, $where_pos);
                        } else {
                            $query .= $join;
                        }

                        $before_from = substr($query, 0, $from_pos);
                        if (preg_match('#(COUNT|SUM|AVG|MIN|MAX)\(#', $before_from) === 0) { // If we're returning full result sets (as opposed probably to just joining so we can use translate_field_ref)
                            $original = 't_' . $field_stripped . '.text_original AS t_' . $field_stripped . '__text_original';
                            $parsed = 't_' . $field_stripped . '.text_parsed AS t_' . $field_stripped . '__text_parsed';

                            $query = $before_from . ',' . $original . ',' . $parsed . substr($query, $from_pos);

                            $lang_strings_expecting[] = array($field, 't_' . $field_stripped . '__text_original', 't_' . $field_stripped . '__text_parsed');
                        }
                    }
                }
            } else {
                foreach ($lang_fields as $field => $field_type) {
                    if (strpos($field_type, '__COMCODE') !== false) {
                        $_query = strtoupper($query);
                        $from_pos = strpos($_query, ' FROM ');
                        if (($from_pos !== false) && (strpos(substr($_query, 0, $from_pos), '(SELECT') !== false)) {
                            $from_pos = strrpos($_query, ' FROM ');
                        }
                        $before_from = substr($query, 0, $from_pos);

                        if (preg_match('#(COUNT|SUM|AVG|MIN|MAX)\(#', $before_from) === 0) { // If we're returning full result sets (as opposed probably to just joining so we can use translate_field_ref)
                            $source_user = $field . '__source_user';
                            $parsed = $field . '__text_parsed';

                            if ($before_from != 'SELECT ' . $field_prefix . '*') {
                                $query = $before_from . ',' . $source_user . ',' . $parsed . substr($query, $from_pos);
                            }
                        }
                    }
                }
            }
        }

        if ($start < 0) {
            $start = 0;
        }
        if ($max < 0) {
            $max = 1;
        }

        if ($QUERY_LOG) {
            $before = microtime(true);
        }
        $sub = substr($query, 0, 6); // NB: We don't get 7, because it's time-consuming to check for space/tab/new-lines after 'SELECT', so we'll make the correct assumption SELECT is not a stem of any other keyword
        if ($sub === 'SELECT' || $sub === 'select' || $sub === '(SELEC' || $sub === '(selec') {
            $connection = &$this->connection_read;
        } else {
            $connection = &$this->connection_write;
        }
        if ((is_array($connection)) && (count($connection) > 4)) { // Okay, we can't be lazy anymore
            $connection = call_user_func_array(array($this->static_ob, 'db_get_connection'), $connection);
            _general_db_init();
        }

        // Special handling for searches, which are slow and specific - we want to recognise if previous active searches were the same and kill them (as this would have been a double form submit)
        if (($this->dedupe_mode) && (substr(get_db_type(), 0, 5) === 'mysql')) {
            $query .= '/* ' . get_session_id() . ' */'; // Identify query to session, for accurate de-duping

            $real_query = $query;
            if (($max !== null) && ($start !== null)) {
                $real_query .= ' LIMIT ' . strval($start) . ',' . strval($max);
            } elseif ($max !== null) {
                $real_query .= ' LIMIT ' . strval($max);
            } elseif ($start !== null) {
                $real_query .= ' LIMIT ' . strval($start) . ',30000000';
            }

            $ret = $this->static_ob->db_query('SHOW FULL PROCESSLIST', $connection, null, null, true);
            if (is_array($ret)) {
                foreach ($ret as $process) {
                    if ($process['Info'] === $real_query) {
                        $this->static_ob->db_query('KILL ' . strval($process['Id']), $connection, null, null, true);
                    }
                }
            }
        }

        // Run hooks, if any exist
        if ($UPON_QUERY_HOOKS_CACHE === null) {
            if ((!running_script('restore')) && (function_exists('find_all_hooks')) && (!isset($GLOBALS['DOING_USERS_INIT'])/*can't check for safe mode meaning can't get a full hook list yet*/)) {
                $UPON_QUERY_HOOKS_CACHE = array();
                $hooks = find_all_hooks('systems', 'upon_query');
                foreach ($hooks as $hook => $hook_dir) {
                    require_code('hooks/systems/upon_query/' . filter_naughty_harsh($hook), false, $hook_dir == 'sources_custom');
                    $UPON_QUERY_HOOKS_CACHE[$hook] = object_factory('Hook_upon_query_' . filter_naughty_harsh($hook), true);
                }
            }
        }
        if ($UPON_QUERY_HOOKS_CACHE !== null) {
            foreach ($UPON_QUERY_HOOKS_CACHE as $ob) {
                if (($ob !== null) && (method_exists($ob, 'run_pre'))) {
                    $ob->run_pre($this, $query, $max, $start, $fail_ok, $get_insert_id);
                }
            }
        }

        // Run/log query
        $ret = $this->static_ob->db_query($query, $connection, $max, $start, $fail_ok, $get_insert_id, false, $save_as_volatile);
        if ($QUERY_LOG) {
            $after = microtime(true);
            $text = (!is_null($max)) ? ($query . ' (' . (is_null($start) ? '0' : strval($start)) . '-' . strval((is_null($start) ? 0 : $start) + $max) . ')') : $query;
            $out = array('time' => ($after - $before), 'text' => $text, 'rows' => is_array($ret) ? count($ret) : null);
            $QUERY_LIST[] = $out;
        }
        /*  Generally one would use MySQL's own slow query log, which will impact Composr performance less
        if (microtime_diff($after, $before) > 1.0) {
            cms_profile_start_for('_query:SLOW_ALERT');
            cms_profile_end_for('_query:SLOW_ALERT', $query);
        }
        */

        // Run hooks, if any exist
        if ($UPON_QUERY_HOOKS_CACHE !== null) {
            foreach ($UPON_QUERY_HOOKS_CACHE as $ob) {
                if (($ob !== null) && (method_exists($ob, 'run_post'))) {
                    $ob->run_post($this, $query, $max, $start, $fail_ok, $get_insert_id, $ret);
                }
            }
        }

        if ($ret !== null) {
            foreach ($lang_strings_expecting as $bits) {
                list($field, $original, $parsed) = $bits;

                if ((isset($ret[300])) && (strpos($query, 'theme_images') === false) && (strpos($query, 'group_category_access') === false) && (strpos($query, 'group_privileges') === false) && (strpos($query, 'config') === false)) {
                    cms_profile_start_for('_query:MANY_RESULTS_ALERT');
                    cms_profile_end_for('_query:MANY_RESULTS_ALERT', $query);
                }

                if (multi_lang_content()) {
                    // Copy results to lang cache, but only if not null AND unset to avoid any confusion
                    foreach ($ret as $i => $row) {
                        if (!isset($row[$field])) {
                            continue; // Probably dereferenced to text_original in WHERE, but not selected
                        }

                        $entry = $row[preg_replace('#^.*\.#', '', $field)];

                        if (($row[$original] !== null) && (count($this->text_lookup_original_cache) <= 1000)) {
                            $this->text_lookup_original_cache[$entry] = $row[$original];
                        }
                        if (($row[$parsed] !== null) && (count($this->text_lookup_cache) <= 1000)) {
                            $this->text_lookup_cache[$entry] = $row[$parsed];
                        }

                        unset($ret[$i][$original]);
                        unset($ret[$i][$parsed]);
                    }
                }
            }
        }

        return $ret;
    }

    /**
     * Update (edit) a row in the database.
     *
     * @param  string $table The table name
     * @param  array $update_map The UPDATE map
     * @param  ?array $where_map The WHERE map [will all be AND'd together] (null: no conditions)
     * @param  string $end Something to tack onto the end of the statement
     * @param  ?integer $max The maximum number of rows to update (null: no limit)
     * @param  ?integer $start The starting row to update (null: no specific start)
     * @param  boolean $num_touched Whether to get the number of touched rows. WARNING: Do not use in core Composr code as it does not work on all database drivers
     * @param  boolean $fail_ok Whether to allow failure (outputting a message instead of exiting completely)
     * @return ?integer The number of touched records (null: hasn't been asked / error / not supported)
     */
    public function query_update($table, $update_map, $where_map = null, $end = '', $max = null, $start = null, $num_touched = false, $fail_ok = false)
    {
        $where = '';
        $update = '';

        $value = mixed();

        if ($where_map !== null) {
            foreach ($where_map as $key => $value) {
                if ($where !== '') {
                    $where .= ' AND ';
                }

                if (is_float($value)) {
                    $where .= $key . '=' . number_format($value, 10, '.', '');
                } elseif (is_integer($value)) {
                    $where .= $key . '=' . strval($value);
                } elseif (($key === 'begin_num') || ($key === 'end_num')) {
                    $where .= $key . '=' . $value; // FUDGE: for all our known large unsigned integers
                } else {
                    if ($value === null) {
                        $where .= $key . ' IS NULL';
                    } else {
                        if (($value === '') && ($this->static_ob->db_empty_is_null())) {
                            $value = ' ';
                        }
                        $where .= db_string_equal_to($key, $value);
                    }
                }
            }
        }

        foreach ($update_map as $key => $value) {
            if (($value === STRING_MAGIC_NULL) || ($value === INTEGER_MAGIC_NULL)) {
                continue;
            }
            if ($update !== '') {
                $update .= ', ';
            }

            if ($value === null) {
                $update .= $key . '=NULL';
            } else {
                if (is_float($value)) {
                    $update .= $key . '=' . number_format($value, 10, '.', '');
                } elseif (is_integer($value)) {
                    $update .= $key . '=' . strval($value);
                } elseif (($key === 'begin_num') || ($key === 'end_num')) {
                    $where .= $key . '=' . $value; // FUDGE: for all our known large unsigned integers
                } else {
                    $update .= $key . '=\'' . $this->static_ob->db_escape_string($value) . '\'';
                }
            }
        }
        if ($update === '') {
            return null;
        }

        if ($where === '') {
            return $this->_query('UPDATE ' . $this->table_prefix . $table . ' SET ' . $update . ' ' . $end, $max, $start, $fail_ok, $num_touched);
        } else {
            return $this->_query('UPDATE ' . $this->table_prefix . $table . ' SET ' . $update . ' WHERE (' . $where . ') ' . $end, $max, $start, $fail_ok, $num_touched);
        }
    }

    /**
     * Deletes rows from the specified table, that match the specified conditions (if any). It may be limited to a row range (it is likely, only a maximum, of 1, will be used, if any kind of range at all).
     *
     * @param  string $table The table name
     * @param  ?array $where_map The WHERE map [will all be AND'd together] (null: no conditions)
     * @param  string $end Something to tack onto the end of the statement
     * @param  ?integer $max The maximum number of rows to delete (null: no limit)
     * @param  ?integer $start The starting row to delete (null: no specific start)
     * @param  boolean $fail_ok Whether to allow failure (outputting a message instead of exiting completely)
     */
    public function query_delete($table, $where_map = null, $end = '', $max = null, $start = null, $fail_ok = false)
    {
        if ($where_map === null) {
            if (($end === '') && (is_null($max)) && (is_null($start)) && (db_supports_truncate_table($GLOBALS['SITE_DB']->connection_read))) {
                $this->_query('TRUNCATE ' . $this->table_prefix . $table, null, null, $fail_ok);
            } else {
                $this->_query('DELETE FROM ' . $this->table_prefix . $table . ' ' . $end, $max, $start, $fail_ok);
            }
            return;
        }

        $where = '';

        foreach ($where_map as $key => $value) {
            if ($where !== '') {
                $where .= ' AND ';
            }

            if (is_float($value)) {
                $where .= $key . '=' . number_format($value, 10, '.', '');
            } elseif (is_integer($value)) {
                $where .= $key . '=' . strval($value);
            } elseif (($key === 'begin_num') || ($key === 'end_num')) {
                $where .= $key . '=' . $value; // FUDGE: for all our known large unsigned integers
            } else {
                if ($value === null) {
                    $where .= $key . ' IS NULL';
                } else {
                    if (($value === '') && ($this->static_ob->db_empty_is_null())) {
                        $where .= $key . ' IS NULL'; // $value = ' ';
                    } else {
                        $where .= db_string_equal_to($key, $value);
                    }
                }
            }
        }

        $query = 'DELETE FROM ' . $this->table_prefix . $table . ' WHERE (' . $where . ') ' . $end;
        $this->_query($query, $max, $start, $fail_ok);
    }

    /**
     * Delete an index from a table.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  ID_TEXT $index_name The index name
     */
    public function delete_index_if_exists($table_name, $index_name)
    {
        require_code('database_helper');
        _helper_delete_index_if_exists($this, $table_name, $index_name);
    }

    /**
     * Drop the given table, or if it doesn't exist, silently return.
     *
     * @param  ID_TEXT $table The table name
     */
    public function drop_table_if_exists($table)
    {
        require_code('database_helper');
        _helper_drop_table_if_exists($this, $table);
    }

    /**
     * Rename the given table.
     *
     * @param  ID_TEXT $old The old table name
     * @param  ID_TEXT $new The new table name
     */
    public function rename_table($old, $new)
    {
        require_code('database_helper');
        _helper_rename_table($this, $old, $new);
    }

    /**
     * Adds a field to an existing table.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  ID_TEXT $name The field name
     * @param  ID_TEXT $_type The field type
     * @param  ?mixed $default The default value; for a translatable field should still be a string value (null: null default / default default)
     */
    public function add_table_field($table_name, $name, $_type, $default = null)
    {
        require_code('database_helper');
        _helper_add_table_field($this, $table_name, $name, $_type, $default);
    }

    /**
     * Change the type of a DB field in a table. Note: this function does not support ascession/decession of translatability
     *
     * @param  ID_TEXT $table_name The table name
     * @param  ID_TEXT $name The field name
     * @param  ID_TEXT $_type The new field type
     * @param  ?ID_TEXT $new_name The new field name (null: leave name)
     */
    public function alter_table_field($table_name, $name, $_type, $new_name = null)
    {
        require_code('database_helper');
        _helper_alter_table_field($this, $table_name, $name, $_type, $new_name);
    }

    /**
     * Change the primary key of a table.
     *
     * @param  ID_TEXT $table_name The name of the table to create the index on
     * @param  array $new_key A list of fields to put in the new key
     */
    public function change_primary_key($table_name, $new_key)
    {
        require_code('database_helper');
        _helper_change_primary_key($this, $table_name, $new_key);
    }

    /**
     * Use an *AUTO key for a table that had some other key before.
     *
     * @param  ID_TEXT $table_name Table name
     * @param  ID_TEXT $field_name Field name for new key
     *
     * @ignore
     */
    public function add_auto_key($table_name, $field_name = 'id')
    {
        require_code('database_helper');
        _helper_add_auto_key($this, $table_name, $field_name);
    }

    /**
     * If a text field has picked up Comcode support, we will need to run this.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  ID_TEXT $name The field name
     * @param  ID_TEXT $key The tables key field name
     * @param  integer $level The translation level to use
     * @set    1 2 3 4
     * @param  boolean $in_assembly Whether our data is already stored in Tempcode assembly format
     */
    public function promote_text_field_to_comcode($table_name, $name, $key = 'id', $level = 2, $in_assembly = false)
    {
        require_code('database_helper');
        _helper_promote_text_field_to_comcode($this, $table_name, $name, $key, $level, $in_assembly);
    }

    /**
     * Delete the specified field from the specified table.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  ID_TEXT $name The field name
     */
    public function delete_table_field($table_name, $name)
    {
        require_code('database_helper');
        _helper_delete_table_field($this, $table_name, $name);
    }

    /**
     * If we've changed what $type is stored as, this function will need to be called to change the typing in the DB.
     *
     * @param  ID_TEXT $type The field type
     */
    public function refresh_field_definition($type)
    {
        require_code('database_helper');
        _helper_refresh_field_definition($this, $type);
    }

    /**
     * Find if a table is locked for more than 5 seconds. Only works with MySQL.
     *
     * @param  ID_TEXT $table The table name
     * @return boolean Whether the table is locked
     */
    public function table_is_locked($table)
    {
        if (in_array($table, array('stats', 'banner_clicks', 'member_tracking', 'usersonline_track', 'download_logging'))) {
            return false; // Actually, we have delayed insert for these so locking is not an issue
        }

        if (substr(get_db_type(), 0, 5) != 'mysql' || get_value('innodb') === '1') {
            return false;
        }

        static $cache = array();
        if (isset($cache[$table])) {
            return $cache[$table];
        }

        $tries = 0;
        do {
            if ((substr($table, 0, 2) == 'f_') && ($table != 'f_welcome_emails')) {
                $db_name = get_db_forums();
                $db = $GLOBALS['FORUM_DB'];
            } else {
                $db_name = get_db_site();
                $db = $GLOBALS['SITE_DB'];
            }
            $locks = $db->query('SHOW OPEN TABLES FROM ' . $db_name . ' WHERE `Table`=\'' . db_escape_string($db->get_table_prefix() . $table) . '\' AND In_use>=1', null, null, true);
            if (is_null($locks)) {
                return false; // MySQL version older than 5.0 (e.g. 4.1.x)
            }
            $locked = count($locks) >= 1;
            $tries++;
            if ($locked) {
                if (php_function_allowed('usleep')) {
                    usleep(50000); // 50ms wait
                }
            }
        } while (($locked) && ($tries < 5));

        $cache[$table] = $locked;
        return $locked;
    }
}
