====== MantisBT Schema Comparison and Update ======

This pages describes a process to compare a given MantisBT schema with a standard installation, 
and update it if necessary.

The SQL was written for a MySQL installation, and would not work on other RDBMS without adjustments due to use of metadata.

The script and process was tested using a dump from mantisbt.org bugs database taken on 13-Sep-2013.



===== Preparation =====

  * First thing, **backup** the schema to compare and update, e.g. <code>mysqldump bugtracker >backup.sql</code>
  * Perform a fresh install of MantisBT to use a reference, in a separate schema, e.g. //mantis_12x//
  * Copy the SQL script below to your favorite editor <code sql>
/*
 * Detect differences betweeen a reference MantisBT schema and a target schema
 * and generate a SQL script to fix the target schema so that it is identical
 * to the reference one.
 *
 * Usage
 * 1. Update the current and reference schemas
 * 2. Save the script
 * 3. Run it from command line:
 *    mysql -N < this_script.sql >update_script.sql
 * 4. Update the database
 *    mysql target_schema -v <update_script.sql
 *
 * Revision History
 * 2013-09-10  dregad  created
 * 2013-09-13  dregad  handle unique index creation
 * 2013-09-14  dregad  fix default value setting for ''
 * 2013-09-16  dregad  exclude mantis_upgrade_table from tables to drop
 */


-- Set current schema (to update)
use bugtracker

-- Define reference schema
SET @schema_reference = 'mantis_12x';

-- No update below this line
-- ---------------------------------------------------------------------

SET @schema_to_update = database();

-- Creating Helper views
select '-- Create Temporary Helper views';

-- Need functions as it's not possible to use variables within views
CREATE FUNCTION sch_12x() RETURNS VARCHAR(20) RETURN @schema_reference;
CREATE FUNCTION sch_upd() RETURNS VARCHAR(20) RETURN @schema_to_update;

CREATE OR REPLACE VIEW v_tables AS
    SELECT org.table_schema sch, org.table_name tbl, 12x.table_name std_12x
    FROM information_schema.tables org
    LEFT JOIN information_schema.tables 12x
        ON 12x.table_schema = sch_12x() AND 12x.table_name = org.table_name
    WHERE org.table_schema = sch_upd()
      AND org.table_type = 'BASE TABLE';

CREATE OR REPLACE VIEW v_indexes AS
    SELECT
        table_schema sch,
        table_name tbl,
        index_name idx,
        GROUP_CONCAT(column_name ORDER BY seq_in_index) cols,
        non_unique
    FROM information_schema.statistics
    GROUP BY 1,2,3;

-- ---------------------------------------------------------------------
-- Generate SQL to update the schema
-- ---------------------------------------------------------------------

SELECT '-- Update Column type and Nullable attribute';

SELECT
    CONCAT_WS(' ', 'ALTER TABLE', tbl, 'MODIFY', col, 12x_type,
        IF(12x_nullable = 'NO', 'NOT NULL', ''),
        IF(ISNULL(12x_default), '', CONCAT('DEFAULT ', IF(12x_default = '', '''''', 12x_default))),
        IF(12x_extra = 'auto_increment', 'AUTO_INCREMENT', ''),
        ';'
    ) "Update columns Type & Nullable"
FROM (

    SELECT
        org.table_schema sch,
        org.table_name tbl,
        org.column_name col,
        12x.column_type 12x_type,
        org.column_type curr_type,
        12x.is_nullable 12x_nullable,
        org.is_nullable curr_nullable,
        12x.column_default 12x_default,
        org.column_default org_default,
        12x.extra 12x_extra,
        org.extra curr_extra
    FROM information_schema.columns 12x
    JOIN information_schema.columns org
        ON 12x.table_schema = @schema_reference
       AND 12x.table_name = org.table_name
       AND 12x.column_name = org.column_name
    WHERE org.table_schema = @schema_to_update
      AND (
            (12x.column_type != org.column_type)
         OR (12x.is_nullable != org.is_nullable)
         OR (12x.extra != org.extra)
          )

) delta;


-- ---------------------------------------------------------------------
SELECT '-- Default value';

SELECT
    CONCAT_WS(' ', 'ALTER TABLE', tbl, 'ALTER COLUMN', col,
        IF( ISNULL(std_12x),
            'DROP DEFAULT',
            CONCAT( 'SET DEFAULT ', IF( std_12x = '', '''''', std_12x))
        ),
        ';'
    ) "Update columns default value"
FROM (

    SELECT
        org.table_schema sch,
        org.table_name tbl,
        org.column_name col,
        org.column_default curr_val,
        12x.column_default std_12x
    FROM information_schema.columns 12x
    JOIN information_schema.columns org
        ON 12x.table_schema = @schema_reference
       AND 12x.table_name = org.table_name
       AND 12x.column_name = org.column_name
    WHERE org.table_schema = @schema_to_update
    AND (
            (12x.column_default IS NULL AND org.column_default IS NOT NULL)
         OR (12x.column_default IS NOT NULL AND org.column_default IS NULL)
         OR (12x.column_default != org.column_default)
        )

) delta;


-- ---------------------------------------------------------------------
SELECT '-- Tables';

SELECT
    CONCAT_WS(' ', 'DROP TABLE', tbl, ';') "Drop non-standard tables"
FROM (

    SELECT *
    FROM v_tables
    WHERE std_12x IS NULL
      AND tbl NOT LIKE '%_plugin_%'
      AND tbl <> 'mantis_upgrade_table'

) delta;


-- ---------------------------------------------------------------------
SELECT '-- Indexes';
-- autoincrement ?

SELECT
    CASE
        WHEN ISNULL(std_12x) THEN
            -- Drop indexes not existing in standard 1.2.x
            CONCAT_WS(' ', 'ALTER TABLE', tbl, 'DROP INDEX', idx, ';')
        WHEN ISNULL(curr_val) THEN
            -- Create missing indexes existing in standard 1.2.x
            CONCAT_WS(' ',
                'CREATE', IF(non_unique = 0, 'UNIQUE', ''), 'INDEX',
                idx, 'ON', tbl, '(', std_12x, ')', ';'
            )
        ELSE CONCAT('-- @TODO: Index update ', idx)
        END "Update indexes"
FROM (

    SELECT
        org.sch, org.tbl, org.idx, org.cols curr_val,
        12x.cols std_12x, 12x.non_unique
    FROM (SELECT * FROM v_indexes WHERE sch = @schema_to_update) org
    LEFT JOIN (SELECT * FROM v_indexes WHERE sch = @schema_reference) 12x
        ON org.tbl = 12x.tbl AND org.idx = 12x.idx
    WHERE isnull(12x.cols) OR org.cols <> 12x.cols
    UNION -- Emulate full outer join
    SELECT
        12x.sch, 12x.tbl, 12x.idx, org.cols curr_val,
        12x.cols std_12x, 12x.non_unique
    FROM (SELECT * FROM v_indexes WHERE sch = @schema_to_update) org
    RIGHT JOIN (SELECT * FROM v_indexes WHERE sch = @schema_reference) 12x
        ON org.tbl = 12x.tbl AND org.idx = 12x.idx
    WHERE org.cols IS NULL
    ORDER BY 2,3

) delta
WHERE tbl IN (SELECT tbl FROM v_tables WHERE std_12x IS NOT NULL);


-- ---------------------------------------------------------------------
SELECT '-- Drop Temporary Helper views';

DROP VIEW v_tables;
DROP VIEW v_indexes;
DROP FUNCTION sch_12x;
DROP FUNCTION sch_upd;
</code>

  * Update the schema names as appropriate
  * Save the Generation Script on your computer

===== Process execution =====

  * Create the Update Script (using the Generation Script created in the previous section); this will generate a series of SQL commands to update the schema <code>
mysql -N </path/to/generate_script.sql >/path/to/update_script.sql
</code> Note: temporary functions and views are created by the script in the current schema, then deleted at the end of execution.

  * If you just want to compare the definitions of the schemas, you can use the sub-select statements at each step.

  * Align the schema to the reference using the Update Script <code>
mysql bugtracker -v </path/to/update_script.sql
</code>

  * Alternatively, you can combine the two steps <code>
mysql -N </path/to/generate_script.sql |mysql bugtracker -v
</code>

At this point, the two schemas should be identical. Double-check, test and restore from backup in case of issues.


===== Notes =====

==== Comparing Indexes ====

<code sql>
SELECT table_schema, table_name, index_name, non_unique, seq_in_index, column_name, sub_part, packed, nullable
FROM information_schema.statistics
order by 1,2,3,5
</code>


==== Updating mantisbt.org bugtracker ====

Excluding plugin-specific tables, the following differences were found

  * 2 extra tables (mantis_tasks_table, mantis_upgrade_table)
  * 49 differences in indexes (extra, missing, different name, different definition, e.g. unique)
  * 130 column type differences, 
  * 61 default values differences 
  * 1 column which should be "not null" but is not

Additionally, when attempting to create a missing unique index on username column, 2 cases of duplicate keys in mantis_user_table were discovered,  (users //updater//, 2 records, and //dmok//, 3 records). After carefully checking that these accounts were not referenced anywhere in the database, the extra records were deleted through Manage Users page (keeping only the one that was created first).

As of this writing, the official tracker has not yet been updated; a message was sent to the mantisbt-dev mailing list to get feedback on the process first.