<?php /*

 Composr
 Copyright (c) ocProducts, 2004-2016

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


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

*/

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

/*
Only works with MySQL.
*/

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

    private $deleting_tables = array();

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

        $GLOBALS['NO_QUERY_LIMIT'] = true;

        $GLOBALS['NO_DB_SCOPE_CHECK'] = true;

        $meta_tables = array();
        $field_details = $GLOBALS['SITE_DB']->query_select('db_meta', array('*'));
        foreach ($field_details as $field) {
            if (!isset($meta_tables[$field['m_table']])) {
                $meta_tables[$field['m_table']] = array();
            }
            $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 = array();
        $existent_indices = array();
        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 = array();
            foreach ($column_details as $column) {
                $_existent_table[$column['Field']] = array(
                    '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 = strtolower($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] = array(
                        'table' => $table_name,
                        'name' => $index_name,
                        'fields' => array(),
                        'is_full_text' => $is_full_text,
                    );
                }

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

        $meta_indices = array();
        $index_details = $GLOBALS['SITE_DB']->query_select('db_meta_indices', array('*'));
        $indices = array();
        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', array('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] = array(
                'table' => $table_name,
                'name' => $index_name,
                'fields' => $fields,
                'is_full_text' => $is_full_text,
            );
        }

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

        $data = unserialize(file_get_contents(get_file_base() . '/data/db_meta.bin'));

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

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

        $expected_privileges = array();
        foreach ($data['privileges'] as $privilege_name => $privilege) {
            if (addon_installed($privilege['addon'])) {
                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;
        }

        $GLOBALS['NO_DB_SCOPE_CHECK'] = false;

        $sql = implode("\n\n", $this->sql_fixup);
        return array($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($existent_tables, $meta_tables, $expected_tables)
    {
        $needs_changes = false;

        $type_map = $GLOBALS['SITE_DB']->static_ob->db_get_type_remap();

        // 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 = array();
                $existent_key_fields = array();
                $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_map[$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 = array();
                    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_composr_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 = array();
                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_composr_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($existent_indices, $meta_indices, $meta_tables)
    {
        $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($existent_tables, $expected_tables, $meta_tables)
    {
        $needs_changes = false;

        $type_map = $GLOBALS['SITE_DB']->static_ob->db_get_type_remap();

        // 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 = array();
                $existent_key_fields = array();
                $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_map[$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) {
                    list($drop_key_query) = $this->fix_table_inconsistent_in_db__bad_primary_key($table_name, $expected_key_fields, isset($meta_tables[$table_name][$field_name]), true);
                    $this->sql_fixup = array_merge(
                        array_slice($this->sql_fixup, 0, count($this->sql_fixup) - $fields_added),
                        array($drop_key_query . ';'),
                        array_slice($this->sql_fixup, count($this->sql_fixup) - $fields_added)
                    );
                    $needs_changes = true;
                }

                // Fields alien in DB?
                foreach ($existent_tables[$table_name] as $field_name => $existent_details) {
                    $matches = array();
                    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 tpye
     */
    private function cleanup_mysql_field_type($raw_type)
    {
        $raw_type = strtolower($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($existent_indices, $expected_indices, $meta_indices, $meta_tables)
    {
        $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($existent_privileges, $expected_privileges)
    {
        $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($table_name, $field_name, $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 exixtent but meta details missing.
     *
     * @param  string $table_name Table name
     * @param  array $table Table details
     */
    private function create_table_missing_in_meta($table_name, $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($table_name, $table, $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
                }
            }
        }

        $queries = $GLOBALS['SITE_DB']->static_ob->db_create_table(get_table_prefix() . $table_name, $table, $table_name, null);
        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($table_name, $field_name, $field_type, $include_meta)
    {
        if ($include_meta) {
            $this->fix_table_missing_in_meta__create_field($table_name, $field_name, $field_type);
        }

        list($query, $default_st) = _helper_add_table_field_sql($GLOBALS['SITE_DB'], $table_name, $field_name, $field_type);
        $this->add_fixup_query($query);

        if ((!multi_lang_content()) && (strpos($field_type, '__COMCODE') !== false)) {
            $type_remap = $GLOBALS['SITE_DB']->static_ob->db_get_type_remap();

            foreach (array('text_parsed' => 'LONG_TEXT', 'source_user' => 'MEMBER') as $sub_name => $sub_type) {
                $sub_name = $field_name . '__' . $sub_name;
                $query = 'ALTER TABLE ' . get_table_prefix() . $table_name . ' ADD ' . $sub_name . ' ' . $type_remap[$sub_type];
                if ($sub_name == 'text_parsed') {
                    $query .= ' DEFAULT \'\'';
                } elseif ($sub_name == 'source_user') {
                    $query .= ' DEFAULT ' . strval(db_get_first_id());
                }
                $query .= ' NOT NULL';
                $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($table_name, $field_name, $field_type, $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);
        }

        $cols_to_delete = array($field_name);

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

        foreach ($cols_to_delete as $_field_name) {
            $query = 'ALTER TABLE ' . get_table_prefix() . $table_name . ' DROP COLUMN ' . $_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);
            $this->add_fixup_query($query);
        }
    }

    /**
     * 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($table_name, $field_name, $field_type, $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);
        }

        $query = _helper_alter_table_field_sql($GLOBALS['SITE_DB'], $table_name, $field_name, $field_type);
        $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($table_name, $key_fields, $include_meta, $return_queries = false)
    {
        if ($include_meta) {
            $query = 'UPDATE ' . get_table_prefix() . 'db_meta SET m_type=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', array('\'*\'', 'm_type')) . ' WHERE m_table=\'' . db_escape_string($table_name) . '\' AND m_name=\'' . db_escape_string($key_field) . '\'';
                $this->add_fixup_query($query);
            }
        }

        $_key_fields = implode(', ', $key_fields);
        $create_key_query = 'ALTER TABLE ' . get_table_prefix() . $table_name . ' DROP PRIMARY KEY';
        if ($_key_fields != '') {
            $create_key_query .= ', ADD PRIMARY KEY (' . $_key_fields . ')';
        }
        if (!$return_queries) {
            $this->add_fixup_query($create_key_query);
        }

        if ($return_queries) {
            return array($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($table_name, $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($index_name, $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($index_name, $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($index_name, $index, $include_meta, $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 = array();
        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['SITE_DB']->static_ob->db_create_index(get_table_prefix() . $table_name, $_index_name, $_fields, $GLOBALS['SITE_DB']->connection_write, $table_name, $unique_key_fields);
            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($index_name, $index, $include_meta, $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($index_name, $index, $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 = 'DROP INDEX ' . $index_name . ' ON ' . get_table_prefix() . $index['table'];
            $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($privilege_name, $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($privilege_name, $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($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 Composr field type.
     *
     * @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 (Composr-style)
     */
    private function db_type_to_composr_type($field_name, $type_raw, $is_auto_increment, $is_primary, $null_ok)
    {
        $type = (strpos($type_raw, 'int') !== false) ? 'INTEGER' : 'SHORT_TEXT';
        switch ($type_raw) {
            case 'varchar(5)':
                //$type = 'LANGUAGE_NAME';   Ideally, but we cannot assume
                $type = 'ID_TEXT';
                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 'tinyint(1)':
                $type = 'BINARY';
                break;
            case 'tinyint(4)':
                $type = 'SHORT_INTEGER';
                break;
            case 'int(10) unsigned':
                if ((strpos($field_name, 'date') !== false) || (strpos($field_name, 'time') !== 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';
                } else {
                    if (strpos($field_name, 'group') !== false) {
                        $type = 'GROUP';
                    } elseif ((strpos($field_name, 'user') !== false) || (strpos($field_name, 'member') !== false)) {
                        $type = 'MEMBER';
                    } elseif (strpos($field_name, '_id') !== false) {
                        $type = 'AUTO_LINK';
                    } 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($query)
    {
        $this->sql_fixup[md5($query)/*De-duplicates*/] = $query . ';';

        if (get_param_integer('keep_fatalistic', 0) == 1) {
            fatal_exit($query);
        }
    }
}
