<?php /*

 The contents of this file are subject to the Common Public Attribution License Version 1.0 (the "License");
 you may not use this file except in compliance with the License.
 You may obtain a copy of the License at http://opensource.org/licenses/cpal_1.0.

 Software distributed under the License is distributed on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or implied.
 See the License for the specific language governing rights and limitations under the License.

 The Original Code is Composr CMS.

 The Original Developer is the Initial Developer.

 The Initial Developer of the Original Code is Chris Graham.
 All portions of the code written by Chris Graham are Copyright (c) Christopher Graham. All Rights Reserved.

 See docs/LICENSE.md for full licensing 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 ****
*/

/**
 * @license    http://opensource.org/licenses/cpal_1.0 Common Public Attribution License
 * @copyright  Christopher Graham
 * @package    core_database_drivers
 */

// Useful documentation: https://dev.mysql.com/doc/refman/8.0/en/extensions-to-ansi.html

/**
 * Base class for MySQL database drivers.
 *
 * @package core_database_drivers
 */
abstract class Source_database_super_mysql extends Source_database_driver
{
    protected $table_prefix;
    protected $version = null;

    /**
     * Set up the database driver.
     *
     * @param  string $table_prefix Table prefix
     */
    public function __construct(string $table_prefix)
    {
        $this->table_prefix = $table_prefix;
    }

    /**
     * Get the default user for making db connections (used by the installer as a default).
     *
     * @return string The default user for db connections
     */
    public function default_user() : string
    {
        return 'root';
    }

    /**
     * Get the default password for making db connections (used by the installer as a default).
     *
     * @return string The default password for db connections
     */
    public function default_password() : string
    {
        return '';
    }

    /**
     * Adjust an SQL query to apply offset/limit restriction.
     *
     * @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
     */
    public function apply_sql_limit_clause(string &$query, ?int $max = null, int $start = 0)
    {
        if (($max !== null) && ($start != 0)) {
            $query .= ' LIMIT ' . strval($start) . ',' . strval($max);
        } elseif ($max !== null) {
            $query .= ' LIMIT ' . strval($max);
        } elseif ($start != 0) {
            $query .= ' LIMIT ' . strval($start) . ',30000000';
        }
    }

    /**
     * Find whether expression ordering can happen using ALIASes from the SELECT clause.
     *
     * @return boolean Whether it is
     */
    public function has_expression_ordering_by_alias() : bool
    {
        return true;
    }

    /**
     * Find whether update queries may have joins.
     *
     * @return boolean Whether it is
     */
    public function has_update_joins() : bool
    {
        return true;
    }

    /**
     * Find whether text fields can/should be given default values when added as a new column to an existing table.
     *
     * @return boolean Whether they do
     */
    public function has_default_for_text_fields() : bool
    {
        return false; // https://bugs.mysql.com/bug.php?id=21532
    }

    /**
     * Get the character used to surround fields to protect from keyword status.
     * We generally only use it when renaming fields (renaming them on upgrade so that we don't get a conflict with a keyword).
     *
     * @param  boolean $end Whether to get end character
     * @return string Character (blank: has none defined)
     */
    public function get_delimited_identifier(bool $end = false) : string
    {
        return '`';
    }

    /**
     * Create an SQL cast.
     *
     * @param  string $field The field identifier
     * @param  string $type The type wanted
     * @set CHAR INT FLOAT
     * @return string The database type
     */
    public function cast(string $field, string $type) : string
    {
        switch ($type) {
            case 'CHAR':
                $_type = $type . '(20)';
                break;

            case 'INT':
                $_type = 'SIGNED INTEGER';
                break;

            case 'FLOAT':
                $_type = 'DECIMAL';
                break;

            default:
                fatal_exit(do_lang_tempcode('INTERNAL_ERROR', escape_html('0c65795617085f4d9ef7e55c6d100d8d')));
        }

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

    /**
     * Fix a query so it runs on MySQL 8, by adding extra token escaping as required.
     *
     * @param  string $query Query
     * @return string Fixed query
     */
    protected function fix_mysql8_query(string $query) : string
    {
        if (preg_match('#(descriptions|DESCRIPTIONS|groups|path)#i', $query) == 0) { // We define 'descriptions' as well as 'DESCRIPTIONS' due to Turkish issue
            return $query;
        }
        $new_query = '';
        $tokens_to_escape = array_flip(['description', 'groups', 'path']);
        $tokens = $this->tokenise_query($query);
        foreach ($tokens as $i => $token) {
            if (isset($tokens_to_escape[cms_strtolower_ascii($token)])) {
                $new_query .= '`' . $token . '`';
            } else {
                $new_query .= $token;
            }
        }
        return $new_query;
    }

    /**
     * Tokenise a MySQL query (assumes a basic syntax the software is using).
     *
     * @param  string $query Query
     * @return array The tokens
     */
    protected function tokenise_query(string $query) : array
    {
        static $symbolic_tokens = null;
        if ($symbolic_tokens === null) {
            $symbolic_tokens = array_flip(["\t", ' ', "\n", '+', '-', '*', '/', '<>', '>', '<', '>=', '<=', '=', '(', ')', ',']);
        }

        $i = 0;
        $query .= ' '; // Cheat so that we do not have to handle the end state differently
        $len = strlen($query);
        $tokens = [];
        $current_token = '';
        $doing_symbol_delimiter = true;
        while ($i < $len) {
            $next = $query[$i];

            if ($next == "'") {
                if ($current_token != '') {
                    $tokens[] = $current_token;
                }
                $current_token = '';

                $i++;
                $backslash_mode = false;
                while ($i < $len) {
                    $next = $query[$i];

                    if ($backslash_mode) {
                        $current_token .= $next;
                        $backslash_mode = false;
                    } else {
                        if ($next == '\\') {
                            $current_token .= $next;
                            $backslash_mode = true;
                        } elseif ($next == "'") {
                            $tokens[] = "'" . $current_token . "'";
                            break;
                        } else {
                            $current_token .= $next;
                        }
                    }

                    $i++;
                }
                $current_token = '';
            } elseif (($next == '/') && ($i + 1 < $len) && ($query[$i + 1] == '*')) {
                if ($current_token != '') {
                    $tokens[] = $current_token;
                }
                $current_token = '';

                $i += 2;
                while ($i < $len) {
                    $next = $query[$i];

                    if (($next == '*') && ($i + 1 < $len) && ($query[$i + 1] == '/')) {
                        $tokens[] = '/*' . $current_token . '*/';
                        $i++;
                        break;
                    } else {
                        $current_token .= $next;
                    }

                    $i++;
                }
                $current_token = '';
            } else {
                $symbol_delimiter_coming = ((isset($symbolic_tokens[$next])) && ((isset($symbolic_tokens[$next])) || (($i + 1 < $len) && (isset($symbolic_tokens[$next . $query[$i + 1]]))))); //  (NB: symbol delimiters are a maximum of two in length)
                if ($symbol_delimiter_coming || $doing_symbol_delimiter) {
                    if ($current_token != '') {
                        $tokens[] = $current_token;
                    }
                    $current_token = $next;
                    $doing_symbol_delimiter = (isset($symbolic_tokens[$next]));
                } else {
                    $current_token .= $next;
                    if ($doing_symbol_delimiter) {
                        $doing_symbol_delimiter = isset($symbolic_tokens[$next]);
                    }
                }
            }

            $i++;
        }

        return $tokens;
    }

    /**
     * Get queries needed to initialise the DB connection.
     *
     * @return array List of queries
     */
    protected function get_init_queries() : array
    {
        global $SITE_INFO;
        if (empty($SITE_INFO['database_charset'])) {
            $SITE_INFO['database_charset'] = (get_charset() == 'utf-8') ? 'utf8mb4' : 'latin1';
        }

        $queries = [];

        // We'll do all the SETs in a single query
        //  This is good for performance
        //  However, if any option doesn't exist it will give an error
        //  So it is possible in the future ones might need to be done individually

        $set = [];

        if (!empty($SITE_INFO['database_collation'])) {
            $set['collation_connection'] = $SITE_INFO['database_collation'];
        }

        $set['wait_timeout'] = 28800;
        $set['sql_big_selects'] = 1;
        //$set['max_allowed_packet'] = 104857600;   Can not be set for a session, only globally

        $set['sql_mode'] = $this->_strict_mode_query(true);

        $set_query = '';
        foreach ($set as $key => $value) {
            if ($set_query != '') {
                $set_query .= ', ';
            }
            if (is_string($value)) {
                $set_query .= $key . '=\'' . $value . '\'';
            } else {
                $set_query .= $key . '=' . strval($value);
            }
        }
        $queries[] = 'SET ' . $set_query;

        return $queries;
    }

    /**
     * Get a strict mode set value. Takes into account configuration also.
     *
     * @param  boolean $setting Whether it is on (may be overridden be configuration)
     * @return string The value
     */
    protected function _strict_mode_query(bool $setting) : string
    {
        if (($setting) && (get_forum_type() == 'cns') && (!$GLOBALS['IN_MINIKERNEL_VERSION'])) {
            $value = 'STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY,ANSI_QUOTES';
        } else {
            $value = 'MYSQL40';
        }

        return $value;
    }

    /**
     * Get a strict mode set query. Takes into account configuration also.
     *
     * @param  boolean $setting Whether it is on (may be overridden be configuration)
     * @return ?string The query (null: none)
     */
    public function strict_mode_query(bool $setting) : ?string
    {
        $value = $this->_strict_mode_query($setting);
        $query = 'SET sql_mode=\'' . $value . '\'';

        return $query;
    }

    /**
     * Find if a database query may run, showing errors if it cannot.
     *
     * @param  string $query The complete SQL query
     * @param  mixed $connection The DB connection
     * @param  boolean $get_insert_id Whether to get the autoincrement ID created for an insert query
     * @return boolean Whether it can
     */
    public function query_may_run(string $query, $connection, bool $get_insert_id) : bool
    {
        if (isset($query[500000])) { // Let's hope we can fail on this, because it's a huge query. We can only allow it if MySQL can.
            $test_result = $this->query('SHOW VARIABLES LIKE \'max_allowed_packet\'', $connection, null, 0, true);

            if (!is_array($test_result)) {
                return false;
            }
            if (intval($test_result[0]['Value']) < intval(strlen($query) * 1.2)) {
                /*@mysql_query('SET max_allowed_packet=' . strval(intval(strlen($query) * 1.3)), $connection); Does not work well, as MySQL server has gone away error will likely just happen instead */

                if ($get_insert_id) {
                    $this->failed_query_exit(do_lang_tempcode('QUERY_FAILED_TOO_BIG', escape_html($query), escape_html(integer_format(strlen($query))), escape_html(integer_format(intval($test_result[0]['Value'])))));
                } else {
                    $this->failed_query_message(do_lang_tempcode('QUERY_FAILED_TOO_BIG', escape_html(substr($query, 0, 300)) . '...', escape_html(integer_format(strlen($query))), escape_html(integer_format(intval($test_result[0]['Value'])))));
                }
                return false;
            }
        }

        return true;
    }

    /**
     * Set a time limit on future queries.
     * Not all database drivers support this.
     *
     * @param  integer $seconds The time limit in seconds
     * @param  mixed $connection The DB connection
     */
    public function set_query_time_limit(int $seconds, $connection)
    {
        $this->query('SET SESSION MAX_EXECUTION_TIME=' . strval($seconds * 1000), $connection, null, 0, true); // Only works in MySQL 5.7+
    }

    /**
     * Encode a LIKE string comparison fragment for the database system. The pattern is a mixture of characters and _ and % wildcard symbols.
     * Regular string escaping is also applied so that you can put the output directly between quotes.
     *
     * @param  string $pattern The pattern
     * @return string The encoded pattern
     */
    public function encode_like(string $pattern) : string
    {
        return str_replace('\\\\_'/*MySQL escaped underscores*/, '\\_', $this->escape_string($pattern));
    }

    /**
     * Encode a WHERE query part for performing a comparison on a BINARY type field.
     *
     * @param  ID_TEXT $column The column name being compared
     * @param  ID_TEXT $operator The operation to be performed
     * @set < > = <> <= >=
     * @param  string $value The value to compare, in binary string format
     * @return string The encoded WHERE part
     */
    public function encode_binary_compare(string $column, string $operator, string $value) : string
    {
        return 'BINARY ' . $column . ' ' . $operator . ' BINARY \'' . db_escape_string($value) . '\'';
    }

    /**
     * Handle messaging for a failed query.
     *
     * @param  string $query The complete SQL query
     * @param  string $err The error message
     * @param  mixed $connection The DB connection
     */
    protected function handle_failed_query(string $query, string $err, $connection)
    {
        if (function_exists('ocp_mark_as_escaped')) {
            ocp_mark_as_escaped($err);
        }
        if ((!running_script('upgrader')) && ((!get_mass_import_mode()) || (current_fatalistic() > 0)) && (strpos($err, 'Duplicate entry') === false)) {
            $matches = [];
            if (preg_match('#/(\w+)\' is marked as crashed and should be repaired#U', $err, $matches) !== 0) {
                $this->query('REPAIR TABLE ' . $matches[1], $connection);
            }

            if ((!function_exists('do_lang')) || (do_lang('QUERY_FAILED', null, null, null, null, false) === null)) {
                $this->failed_query_exit(htmlentities('Query failed: ' . $query . ' : ' . $err));
            }
            $this->failed_query_exit(do_lang_tempcode('QUERY_FAILED', escape_html($query), ($err)));
        } else {
            $this->failed_query_echo(htmlentities('Database query failed: ' . $query . ' [') . ($err) . htmlentities(']'));
        }
    }

    /**
     * Get a map of software field types, to actual database types.
     *
     * @param  boolean $for_alter Whether this is for adding a table field
     * @return array The map
     */
    public function get_type_remap(bool $for_alter = false) : array
    {
        $type_remap = [
            'AUTO' => $for_alter ? 'integer unsigned' : 'integer unsigned auto_increment',
            'AUTO_LINK' => 'integer', // TODO: must be unsigned when implementing foreign keys
            'INTEGER' => 'integer',
            'UINTEGER' => 'integer unsigned',
            'SHORT_INTEGER' => 'tinyint',
            'REAL' => 'real',
            'BINARY' => 'tinyint(1)',
            'MEMBER' => 'integer', // TODO: must be unsigned when implementing foreign keys
            'GROUP' => 'integer', // TODO: must be unsigned when implementing foreign keys
            'TIME' => 'integer unsigned',
            'LONG_TRANS' => 'integer unsigned',
            'SHORT_TRANS' => 'integer unsigned',
            'LONG_TRANS__COMCODE' => multi_lang_content() ? 'integer' : 'longtext',
            'SHORT_TRANS__COMCODE' => multi_lang_content() ? 'integer' : 'varchar(255)',
            'SHORT_TEXT' => 'varchar(255)',
            'TEXT' => 'varchar(4000)', // Set consistently as 4000 across all drivers due to SQL Server having the lowest limit ; this field type should only be used as an alternative to LONG_TEXT that can be defaulted to '' if not specified, necessary for adding fields to the table's of external systems
            'LONG_TEXT' => 'longtext',
            'ID_TEXT' => 'varchar(80)',
            'MINIID_TEXT' => 'varchar(40)',
            'IP' => 'varchar(40)', // 15 for ip4, but we now support ip6
            'LANGUAGE_NAME' => 'varchar(5)',
            'TOKEN' => 'varchar(15)',
            'SERIAL' => 'longtext',
            'URLPATH' => 'varchar(255) BINARY',
            'BGUID' => 'BINARY(16)',
        ];
        return $type_remap;
    }

    /**
     * Get SQL for creating a new table.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  array $fields A map of field names to software field types (with *#? encodings)
     * @param  mixed $connection The DB connection to make on
     * @param  ID_TEXT $raw_table_name The table name with no table prefix
     * @param  boolean $save_bytes Whether to use lower-byte table storage, with trade-offs of not being able to support all unicode characters; use this if key length is an issue
     * @return array List of SQL queries to run
     */
    public function create_table__sql(string $table_name, array $fields, $connection, string $raw_table_name, bool $save_bytes = false) : array
    {
        $type_remap = $this->get_type_remap();

        $_fields = '';
        $keys = '';
        foreach ($fields as $name => $type) {
            if ($type[0] == '*') { // Is a key
                $type = substr($type, 1);
                if ($keys !== '') {
                    $keys .= ', ';
                }
                $keys .= $name;
            }

            if ($type[0] == '?') { // Is perhaps null
                $type = substr($type, 1);
                $perhaps_null = 'NULL';
            } else {
                $perhaps_null = 'NOT NULL';
            }

            $type = isset($type_remap[$type]) ? $type_remap[$type] : $type;

            $_fields .= '    ' . $name . ' ' . $type;

            // We specify default values for special Comcode fields, so we don't need to worry about populating them when manually editing the database
            /*if (substr($name, -13) == '__text_parsed') {    "BLOB/TEXT column 'the_description__text_parsed' can't have a default value" due to has_default_for_text_fields
                $_fields .= ' DEFAULT \'\'';
            } else*/
            if (substr($name, -13) == '__source_user') {
                $_fields .= ' DEFAULT ' . strval(db_get_first_id());
            }

            $_fields .= ' ' . $perhaps_null . ',' . "\n";
        }

        // As of 11.beta9, Default to InnoDB
        $table_type = (db_is_innodb() ? 'INNODB' : 'MyISAM');
        $type_key = 'engine';
        /*if ($raw_table_name == 'sessions') {
            $table_type = 'HEAP';   Some MySQL servers are very regularly reset
        }*/

        $query = 'CREATE TABLE ' . $table_name . ' (' . "\n" . $_fields . '    PRIMARY KEY (' . $keys . ")\n)";

        // TODO: this block of code will not work when we implement foreign keys; fields must have matching collations
        global $SITE_INFO;
        if (empty($SITE_INFO['database_charset'])) {
            $SITE_INFO['database_charset'] = (get_charset() == 'utf-8') ? 'utf8mb4' : 'latin1';
        }
        $charset = $SITE_INFO['database_charset'];
        if ($charset == 'utf8mb4' && $save_bytes) {
            $charset = 'utf8';
        }

        $query .= ' CHARACTER SET=' . preg_replace('#_.*$#', '', $charset);

        $query .= ' ' . $type_key . '=' . $table_type;

        return [$this->fix_mysql8_query($query)];
    }

    /**
     * Get SQL for renaming a table.
     *
     * @param  ID_TEXT $old Old name
     * @param  ID_TEXT $new New name
     * @return string SQL query to run
     */
    public function rename_table__sql(string $old, string $new) : string
    {
        return 'RENAME TABLE ' . $old . ' TO ' . $new;
    }

    /**
     * Find whether drop table "if exists" is present.
     *
     * @return boolean Whether it is
     */
    public function has_drop_table_if_exists() : bool
    {
        return true;
    }

    /**
     * Get SQL for changing the primary key of a table.
     *
     * @param  string $table_prefix The table prefix
     * @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
     * @return array List of SQL queries to run
     */
    public function change_primary_key__sql(string $table_prefix, string $table_name, array $new_key) : array
    {
        $queries = [];

        $query = 'ALTER TABLE ' . $table_prefix . $table_name . ' DROP PRIMARY KEY';
        if (!empty($new_key)) {
            $query .= ', ADD PRIMARY KEY (' . implode(',', $new_key) . ')';
        }
        $queries[] = $query;

        return $queries;
    }

    /**
     * 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  mixed $connection The DB connection
     * @return ?integer The count (null: do it normally)
     */
    public function get_table_count_approx(string $table, $connection) : ?int
    {
        if ((function_exists('get_value') && (get_value('slow_counts') === '1')) || db_is_innodb()) {
            $sql = 'SELECT TABLE_ROWS FROM information_schema.tables WHERE table_schema=DATABASE() AND TABLE_NAME=\'' . $this->escape_string($table) . '\'';
            $values = $this->query($sql, $connection, null, 0, true);
            if (!isset($values[0])) {
                return null; // No result found
            }
            $first = $values[0];
            $v = current($first); // Result found
            return $v;
        }

        return null;
    }

    /**
     * Get minimum search length.
     * This is broadly MySQL-specific. For other databases we will usually return 4, although there may truly not be a limit on it.
     *
     * @param  mixed $connection The DB connection
     * @return integer Search length
     */
    public function get_minimum_search_length($connection) : int
    {
        static $min_word_length = null;
        if ($min_word_length === null) {
            $min_word_length = 4;
            $_min_word_length = $this->query('SHOW VARIABLES LIKE \'ft_min_word_len\'', $connection, null, 0, true);
            if (isset($_min_word_length[0])) {
                $min_word_length = intval($_min_word_length[0]['Value']);
            }
        }
        return $min_word_length;
    }

    /**
     * Get SQL for changing the type of a DB field in a table.
     *
     * @param  ID_TEXT $table_name The table name
     * @param  ID_TEXT $name The field name
     * @param  ID_TEXT $db_type The new field type
     * @param  boolean $may_be_null If the field may be null
     * @param  ?boolean $is_autoincrement Whether it is an autoincrement field (null: could not set it, returned by reference)
     * @param  ID_TEXT $new_name The new field name
     * @return array List of SQL queries to run
     */
    public function alter_table_field__sql(string $table_name, string $name, string $db_type, bool $may_be_null, ?bool &$is_autoincrement, string $new_name) : array
    {
        if ($is_autoincrement) {
            $db_type .= ' auto_increment';
        }

        $sql_type = $db_type . ' ' . ($may_be_null ? 'NULL' : 'NOT NULL');

        $delimiter_start = $this->get_delimited_identifier(false);
        $delimiter_end = $this->get_delimited_identifier(true);

        $query = 'ALTER TABLE ' . $table_name . ' CHANGE ' . $delimiter_start . $name . $delimiter_end . ' ' . $new_name . ' ' . $sql_type;

        return [$query];
    }

    /**
     * Get SQL for creating a table index.
     *
     * @param  ID_TEXT $table_name The name of the table to create the index on
     * @param  ID_TEXT $index_name The index name (not really important at all)
     * @param  string $_fields Part of the SQL query: a comma-separated list of fields to use on the index
     * @param  mixed $connection_read The DB connection, may be used for running checks
     * @param  ID_TEXT $raw_table_name The table name with no table prefix
     * @param  string $unique_key_fields The name of the unique key field for the table
     * @param  string $table_prefix The table prefix
     * @return array List of SQL queries to run
     */
    public function create_index__sql(string $table_name, string $index_name, string $_fields, $connection_read, string $raw_table_name, string $unique_key_fields, string $table_prefix) : array
    {
        if ($index_name[0] == '#') {
            $index_name = substr($index_name, 1);
            $type = 'FULLTEXT';
        } else {
            $type = 'INDEX';
        }

        $ret = 'ALTER TABLE ' . $table_name . ' ADD ' . $type . ' ' . $index_name . ' (' . $_fields . ')';
        if (running_script('install') && ((is_numeric($this->version) && $this->version >= 50600) || (is_string($this->version) && version_compare($this->version, '5.6', '>=')))) {
            $ret .= ', LOCK=EXCLUSIVE'; // Optimisation
        }

        return [$this->fix_mysql8_query($ret)];
    }

    /**
     * Get SQL for deleting a table index.
     *
     * @param  ID_TEXT $table_name The name of the table the index is on
     * @param  ID_TEXT $index_name The index name
     * @return ?string SQL query to run (null: not supported)
     */
    public function drop_index__sql(string $table_name, string $index_name) : ?string
    {
        $ret = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
        if (running_script('install') && ((is_numeric($this->version) && $this->version >= 50600) || (is_string($this->version) && version_compare($this->version, '5.6', '>=')))) {
            $ret .= ', ALGORITHM=INPLACE LOCK=NONE'; // Optimisation
        }
        return $ret;
    }

    /**
     * Get SQL for creating an InnoDB foreign key.
     * Note that this does not check if we are using InnoDB; it simply generates SQL.
     *
     * @param  ID_TEXT $from_table The table on which we are creating a foreign key
     * @param  ID_TEXT $from_field The table's field on which we are creating a foreign key
     * @param  ID_TEXT $to_table The table which is being referenced
     * @param  ID_TEXT $to_field The table's field which is being referenced
     * @return ?string The SQL (null: not supported)
     */
    public function create_foreign_key__sql(string $from_table, string $from_field, string $to_table, string $to_field) : ?string
    {
        // TODO: Not implemented yet as it would require a major re-structuring of the software
        return null;

        /*
        // InnoDB support only
        if (!db_is_innodb()) {
            return null;
        }

        // Compose deterministic, schema-unique constraint name
        $constraint_name = 'fk_' . md5(preg_replace('#[^\w]#', '_', $from_table . '__' . $from_field));

        $delimiter = $this->get_delimited_identifier(false);

        $sql = 'ALTER TABLE ' . $from_table;
        $sql .= ' ADD CONSTRAINT ' . $constraint_name;
        $sql .= ' FOREIGN KEY (' . $delimiter . $from_field . $delimiter . ')';
        $sql .= ' REFERENCES ' . $to_table . ' (' . $delimiter . $to_field . $delimiter . ')';

        return $this->fix_mysql8_query($sql);
        */
    }

    /**
     * Get SQL for deleting a foreign key.
     * Note that this does not check if we are using InnoDB; it simply generates SQL.
     *
     * @param  ID_TEXT $from_table The table on which we want to delete the foreign key
     * @param  ID_TEXT $from_field The field on which we want to delete a foreign key
     * @return ?string The SQL (null: not supported)
     */
    public function delete_foreign_key__sql(string $from_table, string $from_field) : ?string
    {
        // TODO: Not implemented yet as it would require a major re-structuring of the software
        return null;

        /*
        // InnoDB support only
        if (!db_is_innodb()) {
            return null;
        }

        $constraint_name = 'fk_' . md5(preg_replace('#[^\w]#', '_', $from_table . '__' . $from_field));

        $sql = 'ALTER TABLE ' . $from_table . ' DROP FOREIGN KEY ' . $constraint_name;

        return $this->fix_mysql8_query($sql);
        */
    }

    /**
     * Get SQL for deleting a table.
     * When running this SQL you must suppress errors.
     *
     * @param  mixed $table The table name(s)
     * @return array List of SQL queries to run
     */
    public function drop_table_if_exists__sql($table) : array
    {
        if (!is_array($table)) {
            $table = [$table];
        }

        $sql = 'DROP TABLE IF EXISTS ' . implode(', ', $table);

        return [$sql];
    }

    /**
     * Insert a update a row (depending on whether a row with the key exists already).
     *
     * @param  string $table The table name
     * @param  array $map The INSERT/UPDATE map, minus anything in the key
     * @param  array $key_map The WHERE map representing the key [will all be ANDed together]
     * @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 ?string SQL query (null: not supported)
     */
    public function query_insert_or_replace(string $table, array $map, array $key_map, bool $fail_ok = false, bool $save_as_volatile = false) : ?string
    {
        $keys = '';
        $values = '';

        $v = mixed();

        $eis = $this->empty_is_null();

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

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

            if ($v === null) {
                if (($eis) && (is_string($v)) && ($v == '')) {
                    $values .= '\' \'';
                } else {
                    $values .= 'NULL';
                }
            } else {
                if (($eis) && (is_string($v)) && ($v == '')) {
                    $v = ' ';
                }
                if (is_integer($v)) {
                    $values .= strval($v);
                } elseif (is_float($v)) {
                    $values .= float_to_raw_string($v, 10);
                } else {
                    $values .= '\'' . $this->escape_string($v) . '\'';
                }
            }
        }

        return 'REPLACE INTO ' . $table . ' (' . $keys . ') VALUES (' . $values . ')';
    }

    /**
     * Find whether full-text-search is present.
     *
     * @param  mixed $connection The DB connection
     * @return boolean Whether it is
     */
    public function has_full_text($connection) : bool
    {
        return true;
    }

    /**
     * Find whether full-text-boolean-search is present.
     *
     * @return boolean Whether it is
     */
    public function has_full_text_boolean() : bool
    {
        return true;
    }

    /**
     * Assemble part of a WHERE clause for doing full-text search.
     *
     * @param  string $content Our match string (assumes "?" has been stripped already)
     * @return string Part of a WHERE clause for doing full-text search
     */
    public function full_text_assemble(string $content) : string
    {
        static $stopwords = null;
        if ($stopwords === null) {
            require_code('database_search');
            $stopwords = get_stopwords_list();
        }

        $boolean = (preg_match('#[\-+"]#', $content) != 0);

        if (isset($stopwords[trim(cms_mb_strtolower($content), '"')])) {
            if (($GLOBALS['DEV_MODE']) || (!has_solemnly_declared(I_UNDERSTAND_SQL_INJECTION))) {
                require_code('database_security_filter');
                $GLOBALS['DB_ESCAPE_STRING_LIST'][$this->escape_string(trim($content))] = true;
            }

            $words = preg_split('#\s+#', cms_strtolower_ascii($content));
            $all_stopwords = true;
            foreach ($words as $word) {
                if (trim($word) != '') {
                    $word = trim($word, $boolean ? '"+-' : '"');
                    if (!isset($stopwords[$word])) {
                        $all_stopwords = false;
                    }
                }
            }
            if ($all_stopwords) {
                // This is an imperfect solution for searching for a stop-word
                // It will not cover the case where the stop-word is within the wider text. But we can't handle that case efficiently anyway
                $_content = trim($content, $boolean ? '"+-' : '"');
                if (cms_mb_strlen($_content) > 8) {
                    return '? LIKE \'' . db_encode_like($_content) . '%\'';
                }
                return db_string_equal_to('?', $_content);
            }
        }

        if (!$boolean) {
            // This just causes muddling during full-text natural search
            $content = str_replace('?', '', $content);
            db_escape_string($content); // Hack to so SQL injection detector doesn't get confused

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

            return 'MATCH (?) AGAINST (\'' . $this->escape_string($content) . '\')';
        }

        // These risk parse errors during full-text natural search and aren't supported for searching in the software
        $content = str_replace(['>', '<', '(', ')', '~', '?', '@'], ['', '', '', '', '', '', ''], $content); // Risks parse error and not supported
        $content = preg_replace('#([\-+*])[\-+*]+#', '$1', $content); // Parse error if repeated on some servers
        $content = cms_preg_replace_safe('#[\-+]($|\s)#', '$1', $content); // Parse error if on end on some servers
        $content = cms_preg_replace_safe('#(^|\s)\*#', '$1', $content); // Parse error if on start on some servers
        db_escape_string($content); // Hack to so SQL injection detector doesn't get confused

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

        return 'MATCH (?) AGAINST (\'' . $this->escape_string($content) . '\' IN BOOLEAN MODE)';
    }

    /**
     * Close the database connections. We don't really need to close them (will close at exit), just disassociate so we can refresh them.
     */
    public function close_connections()
    {
        $this->cache_db = [];
        $this->last_select_db = null;
    }
}