<?php /*

 Composr
 Copyright (c) Christopher Graham, 2004-2024

 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
 */

/*EXTRA FUNCTIONS: mysql\_.+*/

/*
Only works with MySQL.
*/

/**
 * Run a MySQL database repair (inbuilt).
 *
 * @return Tempcode Results
 */
function database_repair_inbuilt() : object
{
    require_lang('upgrade');

    $out = new Tempcode();

    if (strpos(get_db_type(), 'mysql') === false) {
        return $out;
    }

    $tables = $GLOBALS['SITE_DB']->query_select('db_meta', ['DISTINCT m_table']);

    $GLOBALS['SITE_DB']->ensure_connected();
    $db = $GLOBALS['SITE_DB'];

    foreach ($tables as $table) {
        if ($table['m_table'] == 'sessions') {
            continue; // HEAP, so can't be repaired
        }

        $table = get_table_prefix() . $table['m_table'];

        // Check/Repair
        $result = $db->query('CHECK TABLE ' . $table . ' FAST');
        $status_row = end($result);
        if ($status_row['Msg_type'] != 'status') {
            $out->attach(paragraph(do_lang_tempcode('TABLE_ERROR', escape_html($table), escape_html($status_row['Msg_type']), [escape_html($status_row['Msg_text'])]), 'dfsdgdsgfgd'));
            $result2 = $db->query('REPAIR TABLE ' . $table);
            $status_row_2 = end($result2);
            $out->attach(paragraph(do_lang_tempcode('TABLE_FIXED', escape_html($table), escape_html($status_row_2['Msg_type']), [escape_html($status_row_2['Msg_text'])]), 'dfsdfgdst4'));
        }
    }

    if ($out->is_empty()) {
        $out = do_lang_tempcode('NO_ISSUES_FOUND');
    }

    return $out;
}

/**
 * Run a MySQL database repair (schema check).
 *
 * @return Tempcode Results
 */
function database_repair_wrap() : object
{
    require_lang('upgrade');

    $repair_ob = new DatabaseRepair();
    list($phase, $sql) = $repair_ob->search_for_database_issues();

    if ($sql != '') {
        return do_lang_tempcode('MYSQL_QUERY_CHANGES_MAKE_' . strval($phase), escape_html($sql));
    }

    return do_lang_tempcode('NO_MYSQL_QUERY_CHANGES_MAKE');
}

/**
 * Provide advice for repairing database issues.
 *
 * @package core
 */
class DatabaseRepair
{
    private $sql_fixup = [];

    private $deleting_tables = [];

    /**
     * Look for database issues.
     *
     * @return array A pair: Phase where errors happened (1 or 2), SQL
     */
    public function search_for_database_issues() : array
    {
        require_code('database_helper');
        require_code('database_relations', false, null, true);

        push_query_limiting(false);

        push_db_scope_check(false);

        $meta_tables = [];
        $field_details = $GLOBALS['SITE_DB']->query_select('db_meta', ['*']);
        foreach ($field_details as $field) {
            if (!isset($meta_tables[$field['m_table']])) {
                $meta_tables[$field['m_table']] = [];
            }
            $meta_tables[$field['m_table']][$field['m_name']] = $field['m_type'];
        }

        $_existent_tables = collapse_1d_complexity(null, $GLOBALS['SITE_DB']->query('SHOW TABLES'));
        $existent_tables = [];
        $existent_indices = [];
        foreach ($_existent_tables as $table_name) {
            if (substr($table_name, 0, strlen(get_table_prefix())) != get_table_prefix()) {
                continue;
            }

            $table_name = substr($table_name, strlen(get_table_prefix()));

            $column_details = $GLOBALS['SITE_DB']->query('SHOW COLUMNS FROM ' . get_table_prefix() . $table_name); // Field, Type, Null, Key, Default, Extra
            $_existent_table = [];
            foreach ($column_details as $column) {
                $_existent_table[$column['Field']] = [
                    'type' => $column['Type'],
                    'null_ok' => $column['Null'] == 'YES',
                    'is_primary' => in_array('PRI', array_map('trim', explode(',', $column['Key']))),
                    'default_value' => $column['Default'],
                    'is_auto_increment' => in_array('auto_increment', array_map('trim', explode(',', $column['Extra']))),
                ];
            }
            $existent_tables[$table_name] = $_existent_table;

            $index_details = $GLOBALS['SITE_DB']->query('SHOW INDEXES FROM ' . get_table_prefix() . $table_name); // Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type
            foreach ($index_details as $index) {
                $index_name = cms_strtolower_ascii($index['Key_name']);

                if ($index_name == 'primary') {
                    continue;
                }

                $is_full_text = ($index['Index_type'] == 'FULLTEXT');

                $universal_index_key = $table_name . '__' . ($is_full_text ? '#' : '') . $index_name;

                if (!isset($existent_indices[$universal_index_key])) {
                    $existent_indices[$universal_index_key] = [
                        'table' => $table_name,
                        'name' => $index_name,
                        'fields' => [],
                        'is_full_text' => $is_full_text,
                    ];
                }

                $existent_indices[$universal_index_key]['fields'][] = preg_replace('#\([^\)]*\)#', '', $index['Column_name']);
            }
        }

        $meta_indices = [];
        $index_details = $GLOBALS['SITE_DB']->query_select('db_meta_indices', ['*']);
        $indices = [];
        foreach ($index_details as $index) {
            $index_name = trim($index['i_name'], '#');
            $table_name = $index['i_table'];
            $universal_index_key = $table_name . '__' . $index['i_name'];

            $fields = explode(',', preg_replace('#\([^\)]*\)#', '', $index['i_fields']));

            $is_full_text = (strpos($index['i_name'], '#') !== false);

            $db_types = '';
            foreach ($fields as $field) {
                $db_type = $GLOBALS['SITE_DB']->query_select_value_if_there('db_meta', 'm_type', ['m_table' => $table_name, 'm_name' => $field]);
                if ($db_type !== null) {
                    if ($db_types != '') {
                        $db_types .= ',';
                    }
                    $db_types .= $db_type;
                }
            }

            if ((multi_lang_content()) && ($is_full_text) && ($table_name != 'translate') && (strpos($db_types, '_TRANS') !== false)) {
                continue;
            }

            $meta_indices[$universal_index_key] = [
                'table' => $table_name,
                'name' => $index_name,
                'fields' => $fields,
                'is_full_text' => $is_full_text,
            ];
        }

        $existent_privileges = [];
        $privilege_details = $GLOBALS['SITE_DB']->query_select('privilege_list', ['*']);
        foreach ($privilege_details as $privilege) {
            $existent_privileges[$privilege['the_name']] = [
                'section' => $privilege['p_section'],
                'default' => $privilege['the_default'],
            ];
        }

        $data = is_file(get_file_base() . '/data/db_meta.bin') ? unserialize(cms_file_get_contents_safe(get_file_base() . '/data/db_meta.bin', FILE_READ_LOCK)) : [];

        // Corrupt db_meta? Delete it and warn_exit.
        if (!is_array($data) || !array_key_exists('tables', $data) || !array_key_exists('indices', $data) || !array_key_exists('privileges', $data)) {
            @unlink(get_file_base() . '/data/db_meta.bin');
            warn_exit(do_lang_tempcode('DB_META_CORRUPT_MISSING'));
        }

        $expected_tables = [];
        foreach ($data['tables'] as $table_name => $table) {
            if (addon_installed($table['addon'], false, false, true, true)) {
                $expected_tables[$table_name] = $table['fields'];
            }
        }

        $expected_indices = [];
        foreach ($data['indices'] as $universal_index_key => $index) {
            if (addon_installed($index['addon'], false, false, true, true)) {
                unset($index['addon']);
                $expected_indices[$universal_index_key] = $index;
            }
        }

        $expected_privileges = [];
        foreach ($data['privileges'] as $privilege_name => $privilege) {
            if (addon_installed($privilege['addon'], false, false, true, true)) {
                unset($privilege['addon']);
                $expected_privileges[$privilege_name] = $privilege;
            }
        }

        $needs_changes = false;
        $needs_changes = $this->search_for_meta_table_issues($existent_tables, $meta_tables, $expected_tables) || $needs_changes;
        $needs_changes = $this->search_for_meta_index_issues($existent_indices, $meta_indices, $meta_tables) || $needs_changes;
        $phase = $needs_changes ? 1 : 2;
        if (!$needs_changes) {
            $needs_changes = $this->search_for_table_issues($existent_tables, $expected_tables, $meta_tables) || $needs_changes;
            $needs_changes = $this->search_for_index_issues($existent_indices, $expected_indices, $meta_indices, $meta_tables) || $needs_changes;
            $needs_changes = $this->search_for_privilege_issues($existent_privileges, $expected_privileges) || $needs_changes;
        }

        pop_db_scope_check();

        $sql = implode("\n\n", $this->sql_fixup);
        return [$phase, $sql];
    }

    /**
     * Search for issues between the meta tables and existent tables.
     *
     * @param  array $existent_tables Existent tables
     * @param  array $meta_tables Meta tables
     * @param  array $expected_tables Expected tables
     * @return boolean Whether there have been issues found
     */
    private function search_for_meta_table_issues(array $existent_tables, array $meta_tables, array $expected_tables) : bool
    {
        $needs_changes = false;

        $type_remap = $GLOBALS['DB_DRIVER']->get_type_remap(true);

        // Tables missing from DB -or- inconsistent in DB
        foreach ($meta_tables as $table_name => $table) {
            if (isset($existent_tables[$table_name])) {
                // Fields missing from DB?
                $meta_key_fields = [];
                $existent_key_fields = [];
                $needed = $meta_tables[$table_name];
                if (!multi_lang_content()) {
                    foreach ($needed as $field_name => $field_type) {
                        if (strpos($field_type, '_TRANS__COMCODE') !== false) {
                            $needed[$field_name . '__text_parsed'] = 'LONG_TEXT';
                            $needed[$field_name . '__source_user'] = 'MEMBER';
                        }
                    }
                }
                foreach ($needed as $field_name => $field_type) {
                    if (!isset($existent_tables[$table_name][$field_name])) {
                        $this->fix_table_inconsistent_in_db__create_field($table_name, $field_name, $field_type, false);
                        $needs_changes = true;
                    } else {
                        $existent_details = $existent_tables[$table_name][$field_name];
                        $existent_field_type = $existent_details['type'];

                        // Fields of inconsistent type?
                        $meta_field_type = trim($field_type, '*?');
                        $meta_field_type_raw = $type_remap[$meta_field_type];
                        $meta_is_primary = (strpos($field_type, '*') !== false);
                        $meta_null_ok = (strpos($field_type, '?') !== false) && (multi_lang_content() || strpos($field_type, '_TRANS') === false);
                        $meta_is_auto_increment = ($meta_field_type == 'AUTO');
                        $bad_type = ($meta_field_type_raw != $existent_details['type']);
                        $bad_null_ok = ($meta_null_ok != $existent_details['null_ok']);
                        $bad_is_auto_increment = ($meta_is_auto_increment != $existent_details['is_auto_increment']);
                        $bad_meta_type = (isset($expected_tables[$table_name][$field_name])) && (($field_type != $expected_tables[$table_name][$field_name]) && ($table_name != 'f_member_custom_fields'));
                        if (/*$bad_type || MySQL may report in different ways so cannot compare so instead we will compare meta-type and expected-type as a special case and later compare existent type to meta type*/$bad_null_ok || $bad_is_auto_increment || $bad_meta_type) {
                            $this->fix_table_inconsistent_in_db__bad_field_type($table_name, $field_name, isset($expected_tables[$table_name][$field_name]) ? $expected_tables[$table_name][$field_name] : $field_type, $bad_meta_type);
                            $needs_changes = true;
                        }
                        if ($meta_is_primary) {
                            $meta_key_fields[] = $field_name;
                        }
                        if ($existent_details['is_primary']) {
                            $existent_key_fields[] = $field_name;
                        }
                    }
                }

                // Bad primary key?
                sort($meta_key_fields);
                sort($existent_key_fields);
                if ($meta_key_fields != $existent_key_fields) {
                    $this->fix_table_inconsistent_in_db__bad_primary_key($table_name, $meta_key_fields, false);
                    $needs_changes = true;
                }

                // Fields alien in DB?
                foreach ($existent_tables[$table_name] as $field_name => $field) {
                    $matches = [];
                    if (
                        (!isset($meta_tables[$table_name][$field_name])) &&
                        (
                            (multi_lang_content()) ||
                            (preg_match('#^(.*)__(text_parsed|source_user)$#', $field_name, $matches) == 0) ||
                            (
                                (
                                    (!isset($expected_tables[$table_name][$matches[1]])) ||
                                    (strpos($expected_tables[$table_name][$matches[1]], '_TRANS__COMCODE') === false)
                                )
                                &&
                                (($table_name != 'f_member_custom_fields') || (preg_match('#^field_\d+#', $field_name) == 0))
                            )
                        )
                    ) {
                        if (isset($expected_tables[$table_name]['fields'][$field_name])) {
                            $field_type = $expected_tables[$table_name]['fields'][$field_name];
                        } else {
                            $field_type_raw = $field['type'];
                            $field_type = $this->db_type_to_cms_type($field_name, $field_type_raw, $field['is_auto_increment'], $field['is_primary'], $field['null_ok']);
                        }

                        if (preg_match('#^(.*)__(text_parsed|source_user)$#', $field_name, $matches) != 0) {
                            if (multi_lang_content()) {
                                // Should not be here with multi-lang-content
                                $this->fix_table_inconsistent_in_db__delete_field($table_name, $field_name, $field_type_raw, false);
                            } else {
                                // It's not actually a translatable field in meta-DB, but it should be
                                if ((isset($expected_tables[$table_name][$matches[1]])) && (strpos($expected_tables[$table_name][$matches[1]], '_TRANS') !== false)) {
                                    $expected_type = $expected_tables[$table_name][$matches[1]] . '__COMCODE';
                                } else {
                                    $expected_type = 'SHORT_TRANS__COMCODE'; // Assumption, might be LONG_TRANS
                                }
                                $query = 'UPDATE ' . get_table_prefix() . 'db_meta SET m_type=\'' . db_escape_string($expected_type) . '\' WHERE m_table=\'' . db_escape_string($table_name) . '\' AND m_name=\'' . db_escape_string($matches[1]) . '\'';
                                $this->add_fixup_query($query);
                            }

                            continue;
                        }

                        $this->fix_table_missing_in_meta__create_field($table_name, $field_name, $field_type);
                        $needs_changes = true;
                    }
                }
            } else {
                $this->create_table_missing_from_db($table_name, $table, false);
                $needs_changes = true;
            }
        }

        // Tables alien in DB
        foreach ($existent_tables as $table_name => $table) {
            if ($table_name == 'db_meta' || $table_name == 'db_meta_indices' || table_has_purpose_flag($table_name, TABLE_PURPOSE__NON_BUNDLED)) {
                continue;
            }

            if (!isset($meta_tables[$table_name])) {
                $table_cleaned = [];
                foreach ($table as $field_name => $field) {
                    if (isset($expected_tables[$table_name]['fields'][$field_name])) {
                        $table_cleaned[$field_name] = $expected_tables[$table_name]['fields'][$field_name];
                    } else {
                        $table_cleaned[$field_name] = $this->db_type_to_cms_type($field_name, $field['type'], $field['is_auto_increment'], $field['is_primary'], $field['null_ok']);
                    }
                }

                $this->create_table_missing_in_meta($table_name, $table_cleaned);
                $needs_changes = true;
            }
        }

        return $needs_changes;
    }

    /**
     * Search for issues between the meta indices and existent indices.
     *
     * @param  array $existent_indices Existent indices
     * @param  array $meta_indices Meta indices
     * @param  array $meta_tables Meta tables
     * @return boolean Whether there have been issues found
     */
    private function search_for_meta_index_issues(array $existent_indices, array $meta_indices, array $meta_tables) : bool
    {
        $needs_changes = false;

        // Indices missing from DB -or- inconsistent in DB
        foreach ($meta_indices as $universal_index_key => $index) {
            $meta_index_name = $index['name'];
            if (isset($existent_indices[$universal_index_key])) {
                $existent_fields = $existent_indices[$universal_index_key]['fields'];
                $meta_fields = $index['fields'];
                $existent_is_full_text = $existent_indices[$universal_index_key]['is_full_text'];
                $meta_is_full_text = $index['is_full_text'];
                sort($existent_fields);
                sort($meta_fields);
                if (($existent_fields != $meta_fields) || ($existent_is_full_text != $meta_is_full_text)) {
                    $this->fix_index_inconsistent_in_meta($meta_index_name, $existent_indices[$universal_index_key]);
                    $needs_changes = true;
                }
            } else {
                $this->create_index_missing_from_db($meta_index_name, $index, false, $meta_tables);
                $needs_changes = true;
            }
        }

        // Indices alien in DB
        foreach ($existent_indices as $universal_index_key => $index) {
            $table_name = $index['table'];

            if ($table_name == 'db_meta' || $table_name == 'db_meta_indices' || table_has_purpose_flag($table_name, TABLE_PURPOSE__NON_BUNDLED)) {
                continue;
            }

            if (!isset($meta_indices[$universal_index_key])) {
                $index_name = $index['name'];
                $this->create_index_missing_in_meta($index_name, $index);
                $needs_changes = true;
            }
        }

        return $needs_changes;
    }

    /**
     * Search for issues between the expected tables and existent tables.
     *
     * @param  array $existent_tables Existent tables
     * @param  array $expected_tables Expected tables
     * @param  array $meta_tables Meta tables
     * @return boolean Whether there have been issues found
     */
    private function search_for_table_issues(array $existent_tables, array $expected_tables, array $meta_tables) : bool
    {
        $needs_changes = false;

        $type_remap = $GLOBALS['DB_DRIVER']->get_type_remap(true);

        // Tables missing from DB -or- inconsistent in DB
        foreach ($expected_tables as $table_name => $table) {
            if ($table_name == 'f_member_custom_fields') {
                continue; // Can't check this, table is dynamic
            }

            if (isset($existent_tables[$table_name])) {
                // Fields missing from DB?
                $fields_added = 0;
                $expected_key_fields = [];
                $existent_key_fields = [];
                $needed = $expected_tables[$table_name];
                if (!multi_lang_content()) {
                    foreach ($needed as $field_name => $field_type) {
                        if (strpos($field_type, '_TRANS__COMCODE') !== false) {
                            $needed[$field_name . '__text_parsed'] = 'LONG_TEXT';
                            $needed[$field_name . '__source_user'] = 'MEMBER';
                        }
                    }
                }
                foreach ($needed as $field_name => $field_type) {
                    if (!isset($existent_tables[$table_name][$field_name])) {
                        $this->fix_table_inconsistent_in_db__create_field($table_name, $field_name, $field_type, (preg_match('#__(text_parsed|source_user)$#', $field_name) == 0) && (!isset($meta_tables[$table_name][$field_name])));
                        $needs_changes = true;
                        if (substr($field_type, 0, 1) == '*') {
                            $expected_key_fields[] = $field_name;
                        }
                        $fields_added++;
                    } else {
                        $existent_details = $existent_tables[$table_name][$field_name];

                        // Fields of inconsistent type?
                        $field_type_trimmed = trim($field_type, '*?');
                        $expected_field_type_raw = $type_remap[$field_type_trimmed];
                        $expected_is_primary = (strpos($field_type, '*') !== false);
                        $expected_null_ok = (strpos($field_type, '?') !== false) && (multi_lang_content() || strpos($field_type, '_TRANS') === false);
                        $expected_is_auto_increment = ($field_type_trimmed == 'AUTO');
                        $_expected = $this->cleanup_mysql_field_type($expected_field_type_raw);
                        $_existent = $this->cleanup_mysql_field_type($existent_details['type']);
                        $bad_type =
                            ($_expected != $_existent) &&
                            (($_expected != 'int') || ($_existent != 'longtext') || (strpos($field_type_trimmed, '_TRANS') === false))/*multi-lang-content difference*/ &&
                            (($table_name != 'f_member_custom_fields') || (preg_match('#^field_\d+#', $field_name) == 0));
                        $bad_null_ok = ($expected_null_ok != $existent_details['null_ok']);
                        $bad_is_auto_increment = ($expected_is_auto_increment != $existent_details['is_auto_increment']);
                        if ($bad_type || $bad_null_ok || $bad_is_auto_increment) {
                            $this->fix_table_inconsistent_in_db__bad_field_type($table_name, $field_name, $field_type, (preg_match('#__(text_parsed|source_user)$#', $field_name) == 0) && (isset($meta_tables[$table_name][$field_name])));
                            $needs_changes = true;
                        }
                        if ($expected_is_primary) {
                            $expected_key_fields[] = $field_name;
                        }
                        if ($existent_details['is_primary']) {
                            $existent_key_fields[] = $field_name;
                        }
                    }
                }

                // Bad primary key?
                sort($expected_key_fields);
                sort($existent_key_fields);
                if ($expected_key_fields != $existent_key_fields) {
                    $this->fix_table_inconsistent_in_db__bad_primary_key($table_name, $expected_key_fields, isset($meta_tables[$table_name][$field_name]), false);
                    $needs_changes = true;
                }

                // Fields alien in DB?
                foreach ($existent_tables[$table_name] as $field_name => $existent_details) {
                    $matches = [];
                    if (
                        (!isset($expected_tables[$table_name][$field_name])) &&
                        (
                            (multi_lang_content()) ||
                            (preg_match('#^(.*)__(text_parsed|source_user)$#', $field_name, $matches) == 0) ||
                            (
                                (
                                    (!isset($expected_tables[$table_name][$matches[1]])) ||
                                    (strpos($expected_tables[$table_name][$matches[1]], '_TRANS__COMCODE') === false)
                                )
                                &&
                                (($table_name != 'f_member_custom_fields') || (preg_match('#^field_\d+$#', $field_name) == 0))
                            )
                        )
                    ) {
                        $this->fix_table_inconsistent_in_db__delete_field($table_name, $field_name, $existent_details['type'], isset($meta_tables[$table_name][$field_name]));
                        $needs_changes = true;
                    }
                }
            } else {
                $this->create_table_missing_from_db($table_name, $table, true);
                $needs_changes = true;
            }
        }

        // Tables alien in DB
        foreach ($existent_tables as $table_name => $table) {
            if ($table_name == 'db_meta' || $table_name == 'db_meta_indices' || table_has_purpose_flag($table_name, TABLE_PURPOSE__NON_BUNDLED)) {
                continue;
            }
            if (!isset($expected_tables[$table_name])) {
                $this->delete_table_alien_in_db($table_name, isset($meta_tables[$table_name]));
                $needs_changes = true;
            }
        }

        return $needs_changes;
    }

    /**
     * Convert a MySQL field type to something we can compare against.
     *
     * @param  ID_TEXT $raw_type Field type
     * @return ID_TEXT Field type
     */
    private function cleanup_mysql_field_type(string $raw_type) : string
    {
        $raw_type = cms_strtolower_ascii($raw_type);
        $raw_type = preg_replace('#\(.*#', '', $raw_type);
        $raw_type = preg_replace('# .*#', '', $raw_type);
        $raw_type = str_replace('integer', 'int', $raw_type);
        $raw_type = str_replace('tinyint', 'int', $raw_type);
        $raw_type = str_replace('real', 'double', $raw_type);
        return $raw_type;
    }

    /**
     * Search for issues between the expected indices and existent indices.
     *
     * @param  array $existent_indices Existent indices
     * @param  array $expected_indices Expected indices
     * @param  array $meta_indices Meta indices
     * @param  array $meta_tables Meta tables
     * @return boolean Whether there have been issues found
     */
    private function search_for_index_issues(array $existent_indices, array $expected_indices, array $meta_indices, array $meta_tables) : bool
    {
        $needs_changes = false;

        // Indices missing from DB -or- inconsistent in DB
        foreach ($expected_indices as $universal_index_key => $index) {
            if ($index['table'] == 'f_member_custom_fields') {
                continue; // Can't check this, table is dynamic
            }

            $expected_index_name = $index['name'];
            $expected_fields = $index['fields'];
            $expected_is_full_text = $index['is_full_text'];
            if (isset($existent_indices[$universal_index_key])) {
                $existent_fields = $existent_indices[$universal_index_key]['fields'];
                $existent_is_full_text = $existent_indices[$universal_index_key]['is_full_text'];
                sort($existent_fields);
                sort($expected_fields);
                if ($existent_fields != $expected_fields || $existent_is_full_text != $expected_is_full_text) {
                    $this->fix_index_inconsistent_in_db($expected_index_name, $index, true, $meta_tables);
                    $needs_changes = true;
                }
            } else {
                $db_types = '';
                foreach ($expected_fields as $field) {
                    $db_type = isset($meta_tables[$index['table']][$field]) ? $meta_tables[$index['table']][$field] : null;
                    if ($db_type !== null) {
                        if ($db_types != '') {
                            $db_types .= ',';
                        }
                        $db_types .= $db_type;
                    }
                }

                if ((multi_lang_content()) && ($expected_is_full_text) && ($index['table'] != 'translate')) {
                    // Ignore, as it only existed to index a string column in non-multi-lang content mode
                } else {
                    $this->create_index_missing_from_db($expected_index_name, $index, true, $meta_tables);
                    $needs_changes = true;
                }
            }
        }

        // Indices alien in DB
        foreach ($existent_indices as $universal_index_key => $index) {
            $table_name = $index['table'];

            if ($table_name == 'f_member_custom_fields') {
                continue; // Can't check this, table is dynamic
            }

            if (($table_name == 'db_meta') || ($table_name == 'db_meta_indices') || (table_has_purpose_flag($table_name, TABLE_PURPOSE__NON_BUNDLED))) {
                continue;
            }

            if (!isset($expected_indices[$universal_index_key])) {
                $index_name = $index['name'];

                if ((!multi_lang_content()) && (count($index['fields']) == 1) && (strpos($meta_tables[$table_name][$index['fields'][0]], '_TRANS') !== false) && ($index_name == $index['fields'][0])) {
                    continue;
                }

                $this->delete_index_alien_in_db($index_name, $index, isset($meta_indices[$universal_index_key]));
                $needs_changes = true;
            }
        }

        return $needs_changes;
    }

    /**
     * Search for issues between the expected privileges and existent privileges.
     *
     * @param  array $existent_privileges Existent privileges
     * @param  array $expected_privileges Expected privileges
     * @return boolean Whether there have been issues found
     */
    private function search_for_privilege_issues(array $existent_privileges, array $expected_privileges) : bool
    {
        $needs_changes = false;

        // Privileges missing from DB -or- inconsistent in DB
        foreach ($expected_privileges as $privilege_name => $privilege) {
            if (isset($existent_privileges[$privilege_name])) {
                if ($existent_privileges[$privilege_name] != $privilege) {
                    $this->fix_privilege_inconsistent_in_db($privilege_name, $privilege);
                    $needs_changes = true;
                }
            } else {
                $this->create_privilege_missing_from_db($privilege_name, $privilege);
                $needs_changes = true;
            }
        }

        // Privileges alien in DB
        foreach ($existent_privileges as $privilege_name => $privilege) {
            if (!isset($expected_privileges[$privilege_name])) {
                $this->delete_privilege_alien_in_db($privilege_name);
                $needs_changes = true;
            }
        }

        return $needs_changes;
    }

    /**
     * Table field is existent but meta details missing.
     *
     * @param  string $table_name Table name
     * @param  string $field_name Field name
     * @param  string $field_type Field type
     */
    private function fix_table_missing_in_meta__create_field(string $table_name, string $field_name, string $field_type)
    {
        $query = 'INSERT INTO ' . get_table_prefix() . 'db_meta (m_table,m_name,m_type) VALUES (\'' . db_escape_string($table_name) . '\',\'' . db_escape_string($field_name) . '\',\'' . db_escape_string($field_type) . '\')';
        $this->add_fixup_query($query);
    }

    /**
     * Table is existent but meta details missing.
     *
     * @param  string $table_name Table name
     * @param  array $table Table details
     */
    private function create_table_missing_in_meta(string $table_name, array $table)
    {
        foreach ($table as $field_name => $field_type) {
            $this->fix_table_missing_in_meta__create_field($table_name, $field_name, $field_type);
        }
    }

    /**
     * Table is not there so create it.
     *
     * @param  string $table_name Table name
     * @param  array $table Table details
     * @param  boolean $include_meta Make meta changes too
     */
    private function create_table_missing_from_db(string $table_name, array $table, bool $include_meta)
    {
        if ($include_meta) {
            foreach ($table as $field_name => $field_type) {
                $this->fix_table_missing_in_meta__create_field($table_name, $field_name, $field_type);
            }
        }

        $table_copy = $table;
        foreach ($table_copy as $name => $type) {
            if (!multi_lang_content()) {
                if (strpos($type, '_TRANS') !== false) {
                    if (strpos($type, '__COMCODE') !== false) {
                        $table[$name . '__text_parsed'] = 'LONG_TEXT';
                        $table[$name . '__source_user'] = 'MEMBER';
                    }

                    $table[$name] = 'LONG_TEXT'; // In the DB layer, it must now save as such
                }
            }
        }

        $db = get_db_for($table_name);
        $queries = $GLOBALS['SITE_DB']->driver->create_table__sql(get_table_prefix() . $table_name, $table, $db->connection_write, $table_name);
        foreach ($queries as $sql) {
            $this->add_fixup_query($sql);
        }
    }

    /**
     * Table field is not there so create it.
     *
     * @param  string $table_name Table name
     * @param  string $field_name Field name
     * @param  string $field_type Field type
     * @param  boolean $include_meta Make meta changes too
     */
    private function fix_table_inconsistent_in_db__create_field(string $table_name, string $field_name, string $field_type, bool $include_meta)
    {
        if ($include_meta) {
            $this->fix_table_missing_in_meta__create_field($table_name, $field_name, $field_type);
        }

        $default = $GLOBALS['SITE_DB']->driver->get_implicit_field_default($field_type);

        $query = $GLOBALS['SITE_DB']->driver->add_table_field__sql(get_table_prefix() . $table_name, $field_name, $field_type, $default);
        $this->add_fixup_query($query);

        if ((!multi_lang_content()) && (strpos($field_type, '__COMCODE') !== false)) {
            $type_remap = $GLOBALS['DB_DRIVER']->get_type_remap(true);

            foreach (['text_parsed' => 'LONG_TEXT', 'source_user' => 'MEMBER'] as $sub_name => $sub_type) {
                $sub_name = $field_name . '__' . $sub_name;

                $sub_default = mixed();
                if ($sub_type == 'LONG_TEXT') {
                    $sub_default = '';
                } else {
                    $sub_default = db_get_first_id();
                }

                $query = $GLOBALS['SITE_DB']->driver->add_table_field__sql(get_table_prefix() . $table_name, $sub_name, $sub_type, $sub_default);
                $this->add_fixup_query($query);
            }
        }
    }

    /**
     * Table field should not exist so delete it.
     *
     * @param  string $table_name Table name
     * @param  string $field_name Field name
     * @param  string $field_type Field type
     * @param  boolean $include_meta Make meta changes too
     */
    private function fix_table_inconsistent_in_db__delete_field(string $table_name, string $field_name, string $field_type, bool $include_meta)
    {
        if ($include_meta) {
            $query = 'DELETE FROM ' . get_table_prefix() . 'db_meta WHERE m_table=\'' . db_escape_string($table_name) . '\' AND m_name=\'' . db_escape_string($field_name) . '\'';
            $this->add_fixup_query($query);
        }

        $fields_to_delete = [$field_name];

        if (strpos($field_type, '_TRANS__COMCODE') !== false) {
            if (!multi_lang_content()) {
                $fields_to_delete[] = $field_name . '__text_parsed';
                $fields_to_delete[] = $field_name . '__source_user';
            }
        }

        foreach ($fields_to_delete as $_field_name) {
            $query = $GLOBALS['SITE_DB']->driver->alter_delete_table_field__sql(get_table_prefix() . $table_name, $_field_name);
            $this->add_fixup_query($query);

            $query = 'DELETE FROM ' . get_table_prefix() . 'db_meta WHERE ' . db_string_equal_to('m_table', $table_name) . ' AND ' . db_string_equal_to('m_name', $field_name);
        }
    }

    /**
     * Table field is of wrong type so fix it.
     *
     * @param  string $table_name Table name
     * @param  string $field_name Field name
     * @param  string $field_type Field type
     * @param  boolean $include_meta Make meta changes too
     */
    private function fix_table_inconsistent_in_db__bad_field_type(string $table_name, string $field_name, string $field_type, bool $include_meta)
    {
        if ($include_meta) {
            $query = 'UPDATE ' . get_table_prefix() . 'db_meta SET m_type=\'' . db_escape_string($field_type) . '\' WHERE m_table=\'' . db_escape_string($table_name) . '\' AND m_name=\'' . db_escape_string($field_name) . '\'';
            $this->add_fixup_query($query);
        }

        $type_remap = $GLOBALS['SITE_DB']->driver->get_type_remap(true);
        $_field_type = str_replace(['*', '?'], ['', ''], $field_type);
        $db_type = $type_remap[$_field_type];

        $is_autoincrement = ($field_type == '*AUTO');
        $queries = $GLOBALS['SITE_DB']->driver->alter_table_field__sql(get_table_prefix() . $table_name, $field_name, $db_type, $field_type[0] == '?', $is_autoincrement, $field_name);
        foreach ($queries as $query) {
            $this->add_fixup_query($query);
        }
    }

    /**
     * Table has wrong key so fix that.
     *
     * @param  string $table_name Table name
     * @param  array $key_fields List of key fields
     * @param  boolean $include_meta Make meta changes too
     * @param  boolean $return_queries Whether to return the main queries instead of inserting them
     * @return ?array Special queries (null: $return_queries not set)
     */
    private function fix_table_inconsistent_in_db__bad_primary_key(string $table_name, array $key_fields, bool $include_meta, bool $return_queries = false) : ?array
    {
        if ($include_meta) {
            $query = 'UPDATE ' . get_table_prefix() . 'db_meta SET m_type=' . db_function('REPLACE', ['m_type', '\'*\'', '\'\'']) . ' WHERE m_table=\'' . db_escape_string($table_name) . '\'';
            $this->add_fixup_query($query);

            foreach ($key_fields as $key_field) {
                $query = 'UPDATE ' . get_table_prefix() . 'db_meta SET m_type=' . db_function('CONCAT', ['\'*\'', 'm_type']) . ' WHERE m_table=\'' . db_escape_string($table_name) . '\' AND m_name=\'' . db_escape_string($key_field) . '\'';
                $this->add_fixup_query($query);
            }
        }

        $create_key_query = $GLOBALS['SITE_DB']->driver->change_primary_key__sql(get_table_prefix(), $table_name, $key_fields);
        if (!$return_queries) {
            foreach ($create_key_query as $_query) {
                $this->add_fixup_query($_query);
            }
        }

        if ($return_queries) {
            return $create_key_query;
        }
        return null;
    }

    /**
     * Table should not be there so delete it.
     *
     * @param  string $table_name Table name
     * @param  boolean $include_meta Make meta changes too
     */
    private function delete_table_alien_in_db(string $table_name, bool $include_meta)
    {
        if ($include_meta) {
            $query = 'DELETE FROM ' . get_table_prefix() . 'db_meta_indices WHERE i_table=\'' . db_escape_string($table_name) . '\'';
            $this->add_fixup_query($query);

            $query = 'DELETE FROM ' . get_table_prefix() . 'db_meta WHERE m_table=\'' . db_escape_string($table_name) . '\'';
            $this->add_fixup_query($query);
        }

        $this->deleting_tables[] = $table_name;

        $query = 'DROP TABLE IF EXISTS ' . get_table_prefix() . $table_name;
        $this->add_fixup_query($query);

        $query = 'DELETE FROM ' . get_table_prefix() . 'db_meta WHERE ' . db_string_equal_to('m_table', $table_name);
        $this->add_fixup_query($query);

        $query = 'DELETE FROM ' . get_table_prefix() . 'db_meta_indices WHERE ' . db_string_equal_to('i_table', $table_name);
        $this->add_fixup_query($query);
    }

    /**
     * Fix inconsistent index meta details.
     * Considers real DB canonical over meta.
     *
     * @param  string $index_name Index name
     * @param  array $index Index details
     */
    private function fix_index_inconsistent_in_meta(string $index_name, array $index)
    {
        $query = 'UPDATE ' . get_table_prefix() . 'db_meta_indices SET i_fields=\'' . db_escape_string(implode(',', $index['fields'])) . '\',i_name=\'' . db_escape_string((($index['is_full_text']) ? '#' : '') . $index['name']) . '\' WHERE i_table=\'' . db_escape_string($index['table']) . '\' AND i_name=\'' . db_escape_string((($index['is_full_text']) ? '#' : '') . $index_name) . '\'';
        $this->add_fixup_query($query);
    }

    /**
     * Create missing meta index details.
     * Considers real DB canonical over meta.
     *
     * @param  string $index_name Index name
     * @param  array $index Index details
     */
    private function create_index_missing_in_meta(string $index_name, array $index)
    {
        $table_name = $index['table'];
        $fields = implode(',', $index['fields']);

        $query = 'INSERT INTO ' . get_table_prefix() . 'db_meta_indices (i_table,i_name,i_fields) VALUES (\'' . db_escape_string($table_name) . '\',\'' . db_escape_string((($index['is_full_text']) ? '#' : '') . $index_name) . '\',\'' . db_escape_string($fields) . '\')';
        $this->add_fixup_query($query);
    }

    /**
     * Create missing index.
     *
     * @param  string $index_name Index name
     * @param  array $index Index details
     * @param  boolean $include_meta Make meta changes too
     * @param  array $meta_tables Meta tables
     */
    private function create_index_missing_from_db(string $index_name, array $index, bool $include_meta, array $meta_tables)
    {
        if ($include_meta) {
            $this->create_index_missing_in_meta($index_name, $index);
        }

        $table_name = $index['table'];

        $is_full_text = $index['is_full_text'];
        $_index_name = ($is_full_text ? '#' : '') . $index_name;

        $fields = [];
        foreach ($index['fields'] as $field_name) {
            $db_type = isset($meta_tables[$table_name][$field_name]) ? $meta_tables[$table_name][$field_name] : null;
            $fields[$field_name] = $db_type;
        }

        $_fields = _helper_generate_index_fields($table_name, $fields, $is_full_text);

        if ($_fields !== null) {
            $unique_key_fields = implode(',', _helper_get_table_key_fields($table_name));

            $queries = $GLOBALS['DB_DRIVER']->create_index__sql(get_table_prefix() . $table_name, $_index_name, $_fields, $GLOBALS['SITE_DB']->connection_write, $table_name, $unique_key_fields, $GLOBALS['SITE_DB']->get_table_prefix());
            foreach ($queries as $sql) {
                $this->add_fixup_query($sql);
            }
        }
    }

    /**
     * Fix inconsistent index.
     *
     * @param  string $index_name Index name
     * @param  array $index Index details
     * @param  boolean $include_meta Make meta changes too
     * @param  array $meta_tables Meta tables
     */
    private function fix_index_inconsistent_in_db(string $index_name, array $index, bool $include_meta, array $meta_tables)
    {
        if ($include_meta) {
            $this->fix_index_inconsistent_in_meta($index_name, $index);
        }

        $this->delete_index_alien_in_db($index_name, $index, false);
        $this->create_index_missing_from_db($index_name, $index, false, $meta_tables);
    }

    /**
     * Delete index that should not be there.
     *
     * @param  string $index_name Index name
     * @param  array $index Index details
     * @param  boolean $include_meta Make meta changes too
     */
    private function delete_index_alien_in_db(string $index_name, array $index, bool $include_meta)
    {
        if ($include_meta) {
            $query = 'DELETE FROM ' . get_table_prefix() . 'db_meta_indices WHERE i_table=\'' . db_escape_string($index['table']) . '\' AND i_name=\'' . db_escape_string((($index['is_full_text']) ? '#' : '') . $index_name) . '\'';
            $this->add_fixup_query($query);
        }

        if (!in_array($index['table'], $this->deleting_tables)) {
            $query = $GLOBALS['SITE_DB']->driver->drop_index__sql(get_table_prefix() . $index['table'], $index_name);
            if ($query !== null) {
                $this->add_fixup_query($query);
            }

            $query = 'DELETE FROM ' . get_table_prefix() . 'db_meta_indices WHERE ' . db_string_equal_to('i_table', $index['table']);
            $this->add_fixup_query($query);
        }
    }

    /**
     * Create missing privilege.
     *
     * @param  string $privilege_name Privilege name
     * @param  array $privilege Privilege details
     */
    private function create_privilege_missing_from_db(string $privilege_name, array $privilege)
    {
        $section = $privilege['section'];
        $default = $privilege['default'];

        $query = 'INSERT INTO ' . get_table_prefix() . 'privilege_list (p_section, the_name, the_default) VALUES (\'' . db_escape_string($section) . '\', \'' . db_escape_string($privilege_name) . '\', ' . strval($default) . ')';
        $this->add_fixup_query($query);
    }

    /**
     * Fix inconsistent privilege.
     *
     * @param  string $privilege_name Privilege name
     * @param  array $privilege Privilege details
     */
    private function fix_privilege_inconsistent_in_db(string $privilege_name, array $privilege)
    {
        $section = $privilege['section'];
        $default = $privilege['default'];

        $query = 'UPDATE ' . get_table_prefix() . 'privilege_list SET p_section=\'' . db_escape_string($section) . '\', the_default=' . strval($default) . ' WHERE the_name=\'' . db_escape_string($privilege_name) . '\'';
        $this->add_fixup_query($query);
    }

    /**
     * Delete missing privilege.
     *
     * @param  string $privilege_name Privilege name
     */
    private function delete_privilege_alien_in_db(string $privilege_name)
    {
        $query = 'DELETE FROM ' . get_table_prefix() . 'privilege_list WHERE the_name=\'' . db_escape_string($privilege_name) . '\'';
        $this->add_fixup_query($query);
    }

    /**
     * Convert raw database field type to software field type.
     * Update _database_integrity automated test when changing this.
     *
     * @param  string $field_name Field name
     * @param  string $type_raw Field type (MySQL-style)
     * @param  boolean $is_auto_increment Auto-increment
     * @param  boolean $is_primary Primary key
     * @param  boolean $null_ok Null-acceptable
     * @return string Field type (software-style)
     */
    private function db_type_to_cms_type(string $field_name, string $type_raw, bool $is_auto_increment, bool $is_primary, bool $null_ok) : string
    {
        $type = (strpos($type_raw, 'int') !== false) ? 'INTEGER' : 'SHORT_TEXT';
        switch ($type_raw) {
            case 'varchar(5)':
                $type = 'LANGUAGE_NAME';
                break;
            case 'varchar(13)':
                $type = 'TOKEN';
                break;
            case 'varchar(40)':
                if (strpos($field_name, 'ip_address') !== false) {
                    $type = 'IP';
                } else {
                    $type = 'MINIID_TEXT';
                }
                break;
            case 'varchar(80)':
                $type = 'ID_TEXT';
                break;
            case 'varchar(255)':
                if (strpos($field_name, 'url') !== false) {
                    $type = 'URLPATH';
                } else {
                    $type = 'SHORT_TEXT';
                }
                break;
            case 'varchar(4000)':
                $type = 'TEXT';
                break;
            case 'tinyint(1)':
                $type = 'BINARY';
                break;
            case 'tinyint(4)':
                $type = 'SHORT_INTEGER';
                break;
            case 'int(10) unsigned':
                if ((strpos($field_name, 'count_') === 0) || (strpos($field_name, '_count') === (strlen($field_name) - 6)) || (strpos($field_name, '_count_') !== false)) { // Specific checks necessary to filter out fields like county and country
                    $type = 'UINTEGER'; // Count fields will never be LONG_TRANS / SHORT_TRANS / etc
                } else {
                    if ((strpos($field_name, 'date') !== false) || ((strpos($field_name, 'time') !== false) && (strpos($field_name, 'timeout') === false)) || (strpos($field_name, 'until') !== false)) {
                        $type = 'TIME';
                    } else {
                        $type = $is_auto_increment ? 'AUTO' : 'LONG_TRANS'; // Also could be... SHORT_TRANS or UINTEGER... but we can't tell this at all
                    }
                }
                break;
            case 'int(11)':
                if ($is_auto_increment) {
                    $type = 'AUTO';
                } elseif (strpos($field_name, 'count') === false) {
                    if ((strpos($field_name, 'group') !== false) && (strpos($field_name, 'grouping') === false)) {
                        $type = 'GROUP';
                    } elseif ((strpos($field_name, 'user') !== false) || (strpos($field_name, 'member') !== false) || (strpos($field_name, 'submitter') !== false) || (strpos($field_name, 'owner') !== false)) {
                        $type = 'MEMBER';
                    } elseif (strpos($field_name, '_id') !== false) {
                        $type = 'AUTO_LINK';
                    } else {
                        $type = 'INTEGER';
                    }
                } else {
                    $type = 'INTEGER';
                }
                break;
            case 'real':
            case 'double':
                $type = 'REAL';
                break;
            case 'longtext':
                $type = 'LONG_TEXT';
                break;
        }

        if ($is_primary) {
            $type = '*' . $type;
        } elseif ($null_ok) {
            $type = '?' . $type;
        }

        return $type;
    }

    /**
     * Add query to list of ones that might be run by user.
     *
     * @param  string $query Query
     */
    private function add_fixup_query(string $query)
    {
        $this->sql_fixup[md5($query)/*De-duplicates*/] = $query . ';';

        if (current_fatalistic() > 0) {
            fatal_exit($query);
        }
    }
}
