Proyectos de Subversion Moodle

<?php

// This file is part of Moodle - http://moodle.org/

//

// Moodle is free software: you can redistribute it and/or modify

// it under the terms of the GNU General Public License as published by

// the Free Software Foundation, either version 3 of the License, or

// (at your option) any later version.

//

// Moodle is distributed in the hope that it will be useful,

// but WITHOUT ANY WARRANTY; without even the implied warranty of

// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

// GNU General Public License for more details.

//

// You should have received a copy of the GNU General Public License

// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.

/**

 * Abstract database driver class.

 *

 * @package    core_dml

 * @copyright  2008 Petr Skoda (http://skodak.org)

 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later

 */

defined('MOODLE_INTERNAL') || die();

require_once(__DIR__.'/database_column_info.php');

require_once(__DIR__.'/moodle_recordset.php');

require_once(__DIR__.'/moodle_transaction.php');

/** SQL_PARAMS_NAMED - Bitmask, indicates :name type parameters are supported by db backend. */

define('SQL_PARAMS_NAMED', 1);

/** SQL_PARAMS_QM - Bitmask, indicates ? type parameters are supported by db backend. */

define('SQL_PARAMS_QM', 2);

/** SQL_PARAMS_DOLLAR - Bitmask, indicates $1, $2, ... type parameters are supported by db backend. */

define('SQL_PARAMS_DOLLAR', 4);

/** SQL_INT_MAX - Size of a large integer with cross database platform support. */

define('SQL_INT_MAX', 9999999999);

/** SQL_QUERY_SELECT - Normal select query, reading only. */

define('SQL_QUERY_SELECT', 1);

/** SQL_QUERY_INSERT - Insert select query, writing. */

define('SQL_QUERY_INSERT', 2);

/** SQL_QUERY_UPDATE - Update select query, writing. */

define('SQL_QUERY_UPDATE', 3);

/** SQL_QUERY_STRUCTURE - Query changing db structure, writing. */

define('SQL_QUERY_STRUCTURE', 4);

/** SQL_QUERY_AUX - Auxiliary query done by driver, setting connection config, getting table info, etc. */

define('SQL_QUERY_AUX', 5);

/** SQL_QUERY_AUX_READONLY - Auxiliary query that can be done using the readonly connection:

 * database parameters, table/index/column lists, if not within transaction/ddl. */

define('SQL_QUERY_AUX_READONLY', 6);

/**

 * Abstract class representing moodle database interface.

 * @link https://moodledev.io/docs/apis/core/dml/ddl

 *

 * @package    core_dml

 * @copyright  2008 Petr Skoda (http://skodak.org)

 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later

 */

abstract class moodle_database {

    /** @var database_manager db manager which allows db structure modifications. */

    protected $database_manager;

    /** @var moodle_temptables temptables manager to provide cross-db support for temp tables. */

    protected $temptables;

    /** @var array Cache of table info. */

    protected $tables  = null;

    // db connection options

    /** @var string db host name. */

    protected $dbhost;

    /** @var string db host user. */

    protected $dbuser;

    /** @var string db host password. */

    protected $dbpass;

    /** @var string db name. */

    protected $dbname;

    /** @var string Prefix added to table names. */

    protected $prefix;

    /** @var array Database or driver specific options, such as sockets or TCP/IP db connections. */

    protected $dboptions;

    /** @var bool True means non-moodle external database used.*/

    protected $external;

    /** @var int The database reads (performance counter).*/

    protected $reads = 0;

    /** @var int The database writes (performance counter).*/

    protected $writes = 0;

    /** @var float Time queries took to finish, seconds with microseconds.*/

    protected $queriestime = 0;

    /** @var int Debug level. */

    protected $debug  = 0;

    /** @var string Last used query sql. */

    protected $last_sql;

    /** @var array Last query parameters. */

    protected $last_params;

    /** @var int Last query type. */

    protected $last_type;

    /** @var string Last extra info. */

    protected $last_extrainfo;

    /** @var float Last time in seconds with millisecond precision. */

    protected $last_time;

    /** @var bool Flag indicating logging of query in progress. This helps prevent infinite loops. */

    protected $loggingquery = false;

    /** @var bool True if the db is used for db sessions. */

    protected $used_for_db_sessions = false;

    /** @var array Array containing open transactions. */

    protected $transactions = array();

    /** @var bool Flag used to force rollback of all current transactions. */

    private $force_rollback = false;

    /** @var string MD5 of settings used for connection. Used by MUC as an identifier. */

    private $settingshash;

    /** @var cache_application for column info */

    protected $metacache;

    /** @var cache_application|cache_session|cache_store for column info on temp tables */

    protected $metacachetemp;

    /** @var bool flag marking database instance as disposed */

    protected $disposed;

    /**

     * @var int internal temporary variable used to fix params. Its used by {@link _fix_sql_params_dollar_callback()}.

     */

    private $fix_sql_params_i;

    /**

     * @var int internal temporary variable used to guarantee unique parameters in each request. Its used by {@link get_in_or_equal()}.

     */

    protected $inorequaluniqueindex = 1;

    /**

     * @var boolean variable use to temporarily disable logging.

     */

    protected $skiplogging = false;

    /**

     * Constructor - Instantiates the database, specifying if it's external (connect to other systems) or not (Moodle DB).

     *              Note that this affects the decision of whether prefix checks must be performed or not.

     * @param bool $external True means that an external database is used.

     */

    public function __construct($external=false) {

        $this->external  = $external;

    }

    /**

     * Destructor - cleans up and flushes everything needed.

     */

    public function __destruct() {

        $this->dispose();

    }

    /**

     * Detects if all needed PHP stuff are installed for DB connectivity.

     * Note: can be used before connect()

     * @return mixed True if requirements are met, otherwise a string if something isn't installed.

     */

    abstract public function driver_installed();

    /**

     * Returns database table prefix

     * Note: can be used before connect()

     * @return string The prefix used in the database.

     */

    public function get_prefix() {

        return $this->prefix;

    }

    /**

     * Loads and returns a database instance with the specified type and library.

     *

     * The loaded class is within lib/dml directory and of the form: $type.'_'.$library.'_moodle_database'

     *

     * @param string $type Database driver's type. (eg: mysqli, pgsql, mssql, sqldrv, etc.)

     * @param string $library Database driver's library (native, pdo, etc.)

     * @param bool $external True if this is an external database.

     * @return ?moodle_database driver object or null if error, for example of driver object see {@see mysqli_native_moodle_database}

     */

    public static function get_driver_instance($type, $library, $external = false) {

        global $CFG;

        $classname = $type.'_'.$library.'_moodle_database';

        $libfile   = "$CFG->libdir/dml/$classname.php";

        if (!file_exists($libfile)) {

            return null;

        }

        require_once($libfile);

        return new $classname($external);

    }

    /**

     * Returns the database vendor.

     * Note: can be used before connect()

     * @return string The db vendor name, usually the same as db family name.

     */

    public function get_dbvendor() {

        return $this->get_dbfamily();

    }

    /**

     * Returns the database family type. (This sort of describes the SQL 'dialect')

     * Note: can be used before connect()

     * @return string The db family name (mysql, postgres, mssql, etc.)

     */

    abstract public function get_dbfamily();

    /**

     * Returns a more specific database driver type

     * Note: can be used before connect()

     * @return string The db type mysqli, pgsql, mssql, sqlsrv

     */

    abstract protected function get_dbtype();

    /**

     * Returns the general database library name

     * Note: can be used before connect()

     * @return string The db library type -  pdo, native etc.

     */

    abstract protected function get_dblibrary();

    /**

     * Returns the localised database type name

     * Note: can be used before connect()

     * @return string

     */

    abstract public function get_name();

    /**

     * Returns the localised database configuration help.

     * Note: can be used before connect()

     * @return string

     */

    abstract public function get_configuration_help();

    /**

     * Returns the localised database description

     * Note: can be used before connect()

     * @deprecated since 2.6

     * @return string

     */

    public function get_configuration_hints() {

        debugging('$DB->get_configuration_hints() method is deprecated, use $DB->get_configuration_help() instead');

        return $this->get_configuration_help();

    }

    /**

     * Returns the db related part of config.php

     * @return stdClass

     */

    public function export_dbconfig() {

        $cfg = new stdClass();

        $cfg->dbtype    = $this->get_dbtype();

        $cfg->dblibrary = $this->get_dblibrary();

        $cfg->dbhost    = $this->dbhost;

        $cfg->dbname    = $this->dbname;

        $cfg->dbuser    = $this->dbuser;

        $cfg->dbpass    = $this->dbpass;

        $cfg->prefix    = $this->prefix;

        if ($this->dboptions) {

            $cfg->dboptions = $this->dboptions;

        }

        return $cfg;

    }

    /**

     * Diagnose database and tables, this function is used

     * to verify database and driver settings, db engine types, etc.

     *

     * @return string null means everything ok, string means problem found.

     */

    public function diagnose() {

        return null;

    }

    /**

     * Connects to the database.

     * Must be called before other methods.

     * @param string $dbhost The database host.

     * @param string $dbuser The database user to connect as.

     * @param string $dbpass The password to use when connecting to the database.

     * @param string $dbname The name of the database being connected to.

     * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used

     * @param array $dboptions driver specific options

     * @return bool true

     * @throws dml_connection_exception if error

     */

    abstract public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, ?array $dboptions=null);

    /**

     * Store various database settings

     * @param string $dbhost The database host.

     * @param string $dbuser The database user to connect as.

     * @param string $dbpass The password to use when connecting to the database.

     * @param string $dbname The name of the database being connected to.

     * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used

     * @param array $dboptions driver specific options

     * @return void

     */

    protected function store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, ?array $dboptions=null) {

        $this->dbhost    = $dbhost;

        $this->dbuser    = $dbuser;

        $this->dbpass    = $dbpass;

        $this->dbname    = $dbname;

        $this->prefix    = $prefix;

        $this->dboptions = (array)$dboptions;

    }

    /**

     * Returns a hash for the settings used during connection.

     *

     * If not already requested it is generated and stored in a private property.

     *

     * @return string

     */

    protected function get_settings_hash() {

        if (empty($this->settingshash)) {

            $this->settingshash = md5($this->dbhost . $this->dbuser . $this->dbname . $this->prefix);

        }

        return $this->settingshash;

    }

    /**

     * Handle the creation and caching of the databasemeta information for all databases.

     *

     * @return cache_application The databasemeta cachestore to complete operations on.

     */

    protected function get_metacache() {

        if (!isset($this->metacache)) {

            $properties = array('dbfamily' => $this->get_dbfamily(), 'settings' => $this->get_settings_hash());

            $this->metacache = cache::make('core', 'databasemeta', $properties);

        }

        return $this->metacache;

    }

    /**

     * Handle the creation and caching of the temporary tables.

     *

     * @return cache_application The temp_tables cachestore to complete operations on.

     */

    protected function get_temp_tables_cache() {

        if (!isset($this->metacachetemp)) {

            // Using connection data to prevent collisions when using the same temp table name with different db connections.

            $properties = array('dbfamily' => $this->get_dbfamily(), 'settings' => $this->get_settings_hash());

            $this->metacachetemp = cache::make('core', 'temp_tables', $properties);

        }

        return $this->metacachetemp;

    }

    /**

     * Attempt to create the database

     * @param string $dbhost The database host.

     * @param string $dbuser The database user to connect as.

     * @param string $dbpass The password to use when connecting to the database.

     * @param string $dbname The name of the database being connected to.

     * @param array $dboptions An array of optional database options (eg: dbport)

     *

     * @return bool success True for successful connection. False otherwise.

     */

    public function create_database($dbhost, $dbuser, $dbpass, $dbname, ?array $dboptions=null) {

        return false;

    }

    /**

     * Returns transaction trace for debugging purposes.

     * @private to be used by core only

     * @return ?array or null if not in transaction.

     */

    public function get_transaction_start_backtrace() {

        if (!$this->transactions) {

            return null;

        }

        $lowesttransaction = end($this->transactions);

        return $lowesttransaction->get_backtrace();

    }

    /**

     * Closes the database connection and releases all resources

     * and memory (especially circular memory references).

     * Do NOT use connect() again, create a new instance if needed.

     * @return void

     */

    public function dispose() {

        if ($this->disposed) {

            return;

        }

        $this->disposed = true;

        if ($this->transactions) {

            $this->force_transaction_rollback();

        }

        if ($this->temptables) {

            $this->temptables->dispose();

            $this->temptables = null;

        }

        if ($this->database_manager) {

            $this->database_manager->dispose();

            $this->database_manager = null;

        }

        $this->tables  = null;

    }

    /**

     * This should be called before each db query.

     *

     * @param string $sql The query string.

     * @param array|null $params An array of parameters.

     * @param int $type The type of query ( SQL_QUERY_SELECT | SQL_QUERY_AUX_READONLY | SQL_QUERY_AUX |

     *                  SQL_QUERY_INSERT | SQL_QUERY_UPDATE | SQL_QUERY_STRUCTURE ).

     * @param mixed $extrainfo This is here for any driver specific extra information.

     * @return void

     */

    protected function query_start($sql, ?array $params, $type, $extrainfo=null) {

        if ($this->loggingquery) {

            return;

        }

        $this->last_sql       = $sql;

        $this->last_params    = $params;

        $this->last_type      = $type;

        $this->last_extrainfo = $extrainfo;

        $this->last_time      = microtime(true);

        switch ($type) {

            case SQL_QUERY_SELECT:

            case SQL_QUERY_AUX:

            case SQL_QUERY_AUX_READONLY:

                $this->reads++;

                break;

            case SQL_QUERY_INSERT:

            case SQL_QUERY_UPDATE:

            case SQL_QUERY_STRUCTURE:

                $this->writes++;

            default:

                if ((PHPUNIT_TEST) || (defined('BEHAT_TEST') && BEHAT_TEST) ||

                    defined('BEHAT_SITE_RUNNING')) {

                    // Set list of tables that are updated.

                    require_once(__DIR__.'/../testing/classes/util.php');

                    testing_util::set_table_modified_by_sql($sql);

                }

        }

        $this->print_debug($sql, $params);

    }

    /**

     * This should be called immediately after each db query. It does a clean up of resources.

     * It also throws exceptions if the sql that ran produced errors.

     * @param mixed $result The db specific result obtained from running a query.

     * @throws dml_read_exception | dml_write_exception | ddl_change_structure_exception

     * @return void

     */

    protected function query_end($result) {

        if ($this->loggingquery) {

            return;

        }

        if ($result !== false) {

            $this->query_log();

            // free memory

            $this->last_sql    = null;

            $this->last_params = null;

            $this->print_debug_time();

            return;

        }

        // remember current info, log queries may alter it

        $type   = $this->last_type;

        $sql    = $this->last_sql;

        $params = $this->last_params;

        $error  = $this->get_last_error();

        $this->query_log($error);

        switch ($type) {

            case SQL_QUERY_SELECT:

            case SQL_QUERY_AUX:

            case SQL_QUERY_AUX_READONLY:

                throw new dml_read_exception($error, $sql, $params);

            case SQL_QUERY_INSERT:

            case SQL_QUERY_UPDATE:

                throw new dml_write_exception($error, $sql, $params);

            case SQL_QUERY_STRUCTURE:

                $this->get_manager(); // includes ddl exceptions classes ;-)

                throw new ddl_change_structure_exception($error, $sql);

        }

    }

    /**

     * This logs the last query based on 'logall', 'logslow' and 'logerrors' options configured via $CFG->dboptions .

     * @param string|bool $error or false if not error

     * @return void

     */

    public function query_log($error=false) {

        // Logging disabled by the driver.

        if ($this->skiplogging) {

            return;

        }

        $logall    = !empty($this->dboptions['logall']);

        $logslow   = !empty($this->dboptions['logslow']) ? $this->dboptions['logslow'] : false;

        $logerrors = !empty($this->dboptions['logerrors']);

        $iserror   = ($error !== false);

        $time = $this->query_time();

        // Will be shown or not depending on MDL_PERF values rather than in dboptions['log*].

        $this->queriestime = $this->queriestime + $time;

        if ($logall or ($logslow and ($logslow < ($time+0.00001))) or ($iserror and $logerrors)) {

            $this->loggingquery = true;

            try {

                $backtrace = debug_backtrace();

                if ($backtrace) {

                    //remove query_log()

                    array_shift($backtrace);

                }

                if ($backtrace) {

                    //remove query_end()

                    array_shift($backtrace);

                }

                $log = new stdClass();

                $log->qtype      = $this->last_type;

                $log->sqltext    = $this->last_sql;

                $log->sqlparams  = var_export((array)$this->last_params, true);

                $log->error      = (int)$iserror;

                $log->info       = $iserror ? $error : null;

                $log->backtrace  = format_backtrace($backtrace, true);

                $log->exectime   = $time;

                $log->timelogged = time();

                $this->insert_record('log_queries', $log);

            } catch (Exception $ignored) {

            }

            $this->loggingquery = false;

        }

    }

    /**

     * Disable logging temporarily.

     */

    protected function query_log_prevent() {

        $this->skiplogging = true;

    }

    /**

     * Restore old logging behavior.

     */

    protected function query_log_allow() {

        $this->skiplogging = false;

    }

    /**

     * Returns the time elapsed since the query started.

     * @return float Seconds with microseconds

     */

    protected function query_time() {

        return microtime(true) - $this->last_time;

    }

    /**

     * Returns database server info array

     * @return array Array containing 'description' and 'version' at least.

     */

    abstract public function get_server_info();

    /**

     * Returns supported query parameter types

     * @return int bitmask of accepted SQL_PARAMS_*

     */

    abstract protected function allowed_param_types();

    /**

     * Returns the last error reported by the database engine.

     * @return string The error message.

     */

    abstract public function get_last_error();

    /**

     * Prints sql debug info

     * @param string $sql The query which is being debugged.

     * @param array $params The query parameters. (optional)

     * @param mixed $obj The library specific object. (optional)

     * @return void

     */

    protected function print_debug($sql, ?array $params=null, $obj=null) {

        if (!$this->get_debug()) {

            return;

        }

        if (CLI_SCRIPT) {

            $separator = "--------------------------------\n";

            echo $separator;

            echo "{$sql}\n";

            if (!is_null($params)) {

                echo "[" . var_export($params, true) . "]\n";

            }

            echo $separator;

        } else if (AJAX_SCRIPT) {

            $separator = "--------------------------------";

            error_log($separator);

            error_log($sql);

            if (!is_null($params)) {

                error_log("[" . var_export($params, true) . "]");

            }

            error_log($separator);

        } else {

            $separator = "<hr />\n";

            echo $separator;

            echo s($sql) . "\n";

            if (!is_null($params)) {

                echo "[" . s(var_export($params, true)) . "]\n";

            }

            echo $separator;

        }

    }

    /**

     * Prints the time a query took to run.

     * @return void

     */

    protected function print_debug_time() {

        if (!$this->get_debug()) {

            return;

        }

        $time = $this->query_time();

        $message = "Query took: {$time} seconds.\n";

        if (CLI_SCRIPT) {

            echo $message;

            echo "--------------------------------\n";

        } else if (AJAX_SCRIPT) {

            error_log($message);

            error_log("--------------------------------");

        } else {

            echo s($message);

            echo "<hr />\n";

        }

    }

    /**

     * Returns the SQL WHERE conditions.

     *

     * @param string $table The table name that these conditions will be validated against.

     * @param array $conditions The conditions to build the where clause. (must not contain numeric indexes)

     * @return array An array list containing sql 'where' part and 'params'.

     * @throws dml_exception

     */

    protected function where_clause($table, ?array $conditions=null) {

        // We accept nulls in conditions

        $conditions = is_null($conditions) ? array() : $conditions;

        if (empty($conditions)) {

            return array('', array());

        }

        // Some checks performed under debugging only

        if (debugging()) {

            $columns = $this->get_columns($table);

            if (empty($columns)) {

                // no supported columns means most probably table does not exist

                throw new dml_exception('ddltablenotexist', $table);

            }

            foreach ($conditions as $key=>$value) {

                if (!isset($columns[$key])) {

                    $a = new stdClass();

                    $a->fieldname = $key;

                    $a->tablename = $table;

                    throw new dml_exception('ddlfieldnotexist', $a);

                }

                $column = $columns[$key];

                if ($column->meta_type == 'X') {

                    //ok so the column is a text column. sorry no text columns in the where clause conditions

                    throw new dml_exception('textconditionsnotallowed', $conditions);

                }

            }

        }

        $allowed_types = $this->allowed_param_types();

        $where = array();

        $params = array();

        foreach ($conditions as $key=>$value) {

            if (is_int($key)) {

                throw new dml_exception('invalidnumkey');

            }

            if (is_null($value)) {

                $where[] = "$key IS NULL";

            } else {

                if ($allowed_types & SQL_PARAMS_NAMED) {

                    // Need to verify key names because they can contain, originally,

                    // spaces and other forbidden chars when using sql_xxx() functions and friends.

                    $normkey = trim(preg_replace('/[^a-zA-Z0-9_-]/', '_', $key), '-_');

                    if ($normkey !== $key) {

                        debugging('Invalid key found in the conditions array.');

                    }

                    $where[] = "$key = :$normkey";

                    $params[$normkey] = $value;

                } else {

                    $where[] = "$key = ?";

                    $params[] = $value;

                }

            }

        }

        $where = implode(" AND ", $where);

        return array($where, $params);

    }

    /**

     * Returns SQL WHERE conditions for the ..._list group of methods.

     *

     * @param string $field the name of a field.

     * @param array $values the values field might take.

     * @return array An array containing sql 'where' part and 'params'

     */

    protected function where_clause_list($field, array $values) {

        if (empty($values)) {

            return array("1 = 2", array()); // Fake condition, won't return rows ever. MDL-17645

        }

        // Note: Do not use get_in_or_equal() because it can not deal with bools and nulls.

        $params = array();

        $select = "";

        $values = (array)$values;

        foreach ($values as $value) {

            if (is_bool($value)) {

                $value = (int)$value;

            }

            if (is_null($value)) {

                $select = "$field IS NULL";

            } else {

                $params[] = $value;

            }

        }

        if ($params) {

            if ($select !== "") {

                $select = "$select OR ";

            }

            $count = count($params);

            if ($count == 1) {

                $select = $select."$field = ?";

            } else {

                $qs = str_repeat(',?', $count);

                $qs = ltrim($qs, ',');

                $select = $select."$field IN ($qs)";

            }

        }

        return array($select, $params);

    }

    /**

     * Constructs 'IN()' or '=' sql fragment

     * @param mixed $items A single value or array of values for the expression.

     * @param int $type Parameter bounding type : SQL_PARAMS_QM or SQL_PARAMS_NAMED.

     * @param string $prefix Named parameter placeholder prefix (a unique counter value is appended to each parameter name).

     * @param bool $equal True means we want to equate to the constructed expression, false means we don't want to equate to it.

     * @param mixed $onemptyitems This defines the behavior when the array of items provided is empty. Defaults to false,

     *              meaning throw exceptions. Other values will become part of the returned SQL fragment.

     * @throws coding_exception | dml_exception

     * @return array A list containing the constructed sql fragment and an array of parameters.

     */

    public function get_in_or_equal($items, $type=SQL_PARAMS_QM, $prefix='param', $equal=true, $onemptyitems=false) {

        // default behavior, throw exception on empty array

        if (is_array($items) and empty($items) and $onemptyitems === false) {

            throw new coding_exception('moodle_database::get_in_or_equal() does not accept empty arrays');

        }

        // handle $onemptyitems on empty array of items

        if (is_array($items) and empty($items)) {

            if (is_null($onemptyitems)) {             // Special case, NULL value

                $sql = $equal ? ' IS NULL' : ' IS NOT NULL';

                return (array($sql, array()));

            } else {

                $items = array($onemptyitems);        // Rest of cases, prepare $items for std processing

            }

        }

        if ($type == SQL_PARAMS_QM) {

            if (!is_array($items) or count($items) == 1) {

                $sql = $equal ? '= ?' : '<> ?';

                $items = (array)$items;

                $params = array_values($items);

            } else {

                if ($equal) {

                    $sql = 'IN ('.implode(',', array_fill(0, count($items), '?')).')';

                } else {

                    $sql = 'NOT IN ('.implode(',', array_fill(0, count($items), '?')).')';

                }

                $params = array_values($items);

            }

        } else if ($type == SQL_PARAMS_NAMED) {

            if (empty($prefix)) {

                $prefix = 'param';

            }

            if (!is_array($items)){

                $param = $prefix.$this->inorequaluniqueindex++;

                $sql = $equal ? "= :$param" : "<> :$param";

                $params = array($param=>$items);

            } else if (count($items) == 1) {

                $param = $prefix.$this->inorequaluniqueindex++;

                $sql = $equal ? "= :$param" : "<> :$param";

                $item = reset($items);

                $params = array($param=>$item);

            } else {

                $params = array();

                $sql = array();

                foreach ($items as $item) {

                    $param = $prefix.$this->inorequaluniqueindex++;

                    $params[$param] = $item;

                    $sql[] = ':'.$param;

                }

                if ($equal) {

                    $sql = 'IN ('.implode(',', $sql).')';

                } else {

                    $sql = 'NOT IN ('.implode(',', $sql).')';

                }

            }

        } else {

            throw new dml_exception('typenotimplement');

        }

        return array($sql, $params);

    }

    /**

     * Converts short table name {tablename} to the real prefixed table name in given sql.

     * @param string $sql The sql to be operated on.

     * @return string The sql with tablenames being prefixed with $CFG->prefix

     */

    protected function fix_table_names($sql) {

        return preg_replace_callback(

            '/\{([a-z][a-z0-9_]*)\}/',

            function($matches) {

                return $this->fix_table_name($matches[1]);

            },

            $sql

        );

    }

    /**

     * Adds the prefix to the table name.

     *

     * @param string $tablename The table name

     * @return string The prefixed table name

     */

    protected function fix_table_name($tablename) {

        return $this->prefix . $tablename;

    }

    /**

     * Internal private utitlity function used to fix parameters.

     * Used with {@link preg_replace_callback()}

     * @param array $match Refer to preg_replace_callback usage for description.

     * @return string

     */

    private function _fix_sql_params_dollar_callback($match) {

        $this->fix_sql_params_i++;

        return "\$".$this->fix_sql_params_i;

    }

    /**

     * Detects object parameters and throws exception if found

     * @param mixed $value

     * @return void

     * @throws coding_exception if object detected

     */

    protected function detect_objects($value) {

        if (is_object($value)) {

            throw new coding_exception('Invalid database query parameter value', 'Objects are are not allowed: '.get_class($value));

        }

    }

    /**

     * Normalizes sql query parameters and verifies parameters.

     * @param string $sql The query or part of it.

     * @param array $params The query parameters.

     * @return array (sql, params, type of params)

     */

    public function fix_sql_params($sql, ?array $params=null) {

        global $CFG;

        require_once($CFG->libdir . '/ddllib.php');

        $params = (array)$params; // mke null array if needed

        $allowed_types = $this->allowed_param_types();

        // convert table names

        $sql = $this->fix_table_names($sql);

        // cast booleans to 1/0 int and detect forbidden objects

        foreach ($params as $key => $value) {

            $this->detect_objects($value);

            $params[$key] = is_bool($value) ? (int)$value : $value;

        }

        // NICOLAS C: Fixed regexp for negative backwards look-ahead of double colons. Thanks for Sam Marshall's help

        $named_count = preg_match_all('/(?<!:):[a-z][a-z0-9_]*/', $sql, $named_matches); // :: used in pgsql casts

        $dollar_count = preg_match_all('/\$[1-9][0-9]*/', $sql, $dollar_matches);

        $q_count     = substr_count($sql, '?');

        // Optionally add debug trace to sql as a comment.

        $sql = $this->add_sql_debugging($sql);

        $count = 0;

        if ($named_count) {

            $type = SQL_PARAMS_NAMED;

            $count = $named_count;

        }

        if ($dollar_count) {

            if ($count) {

                throw new dml_exception('mixedtypesqlparam');

            }

            $type = SQL_PARAMS_DOLLAR;

            $count = $dollar_count;

        }

        if ($q_count) {

            if ($count) {

                throw new dml_exception('mixedtypesqlparam');

            }

            $type = SQL_PARAMS_QM;

            $count = $q_count;

        }

        if (!$count) {

             // ignore params

            if ($allowed_types & SQL_PARAMS_NAMED) {

                return array($sql, array(), SQL_PARAMS_NAMED);

            } else if ($allowed_types & SQL_PARAMS_QM) {

                return array($sql, array(), SQL_PARAMS_QM);

            } else {

                return array($sql, array(), SQL_PARAMS_DOLLAR);

            }

        }

        if ($count > count($params)) {

            $a = new stdClass;

            $a->expected = $count;

            $a->actual = count($params);

            throw new dml_exception('invalidqueryparam', $a);

        }

        $target_type = $allowed_types;

        if ($type & $allowed_types) { // bitwise AND

            if ($count == count($params)) {

                if ($type == SQL_PARAMS_QM) {

                    return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based array required

                } else {

                    //better do the validation of names below

                }

            }

            // needs some fixing or validation - there might be more params than needed

            $target_type = $type;

        }

        if ($type == SQL_PARAMS_NAMED) {

            $finalparams = array();

            foreach ($named_matches[0] as $key) {

                $key = trim($key, ':');

                if (!array_key_exists($key, $params)) {

                    throw new dml_exception('missingkeyinsql', $key, '');

                }

                if (strlen($key) > xmldb_field::NAME_MAX_LENGTH) {

                    throw new coding_exception(

                            "Placeholder names must be " . xmldb_field::NAME_MAX_LENGTH . " characters or shorter. '" .

                            $key . "' is too long.", $sql);

                }

                $finalparams[$key] = $params[$key];

            }

            if ($count != count($finalparams)) {

                throw new dml_exception('duplicateparaminsql');

            }

            if ($target_type & SQL_PARAMS_QM) {

                $sql = preg_replace('/(?<!:):[a-z][a-z0-9_]*/', '?', $sql);

                return array($sql, array_values($finalparams), SQL_PARAMS_QM); // 0-based required

            } else if ($target_type & SQL_PARAMS_NAMED) {