--
-- MySQL database diff
-- -------------------
--
-- A set of SQL queries on the INFORMATION_SCHEMA to compare the structure
-- of two databases placed on the same MySQL server.
-- Works only with MySQL v5.0 and higher.
--
-- Created: muzso (muzso@muzso.hu), 2009.08.12
-- Version: 1.0
--

--
-- Parameters
--
-- addcollation: include the character set and collation in the generated column
-- changing SQL statements.
set @addcollation = 1;
-- sourcedb: the db with the "template" structure that we want to duplicate
set @sourcedb = 'sourcedb';
-- sourcedbexclude: a LIKE expression for source tables that we want to exclude
set @sourcedbexclude = 'SRC_%';
-- targetdb: the db that we want to update to the same structure as sourcedb
set @targetdb = 'targetdb';
-- sourcedbexclude: a LIKE expression for target tables that we want to exclude
set @targetdbexclude = 'TRG_%';

--
-- Queries
--

-- new tables to be created in targetdb
select src.`TABLE_NAME`
from `information_schema`.`tables` src
left outer join `information_schema`.`tables` dst on dst.`TABLE_SCHEMA` = @targetdb and dst.`TABLE_NAME` = src.`TABLE_NAME`
where 1 = 1
and src.`TABLE_SCHEMA` = @sourcedb
and src.`TABLE_NAME` not like @sourcedbexclude
and dst.`TABLE_NAME` is null
order by src.`TABLE_NAME`

-- old tables to be dropped in targetdb
select dst.`TABLE_NAME`, concat('drop table `', dst.`TABLE_NAME`, '`;') as stmt
from `information_schema`.`tables` dst
left outer join `information_schema`.`tables` src on src.`TABLE_SCHEMA` = @sourcedb and dst.`TABLE_NAME` = src.`TABLE_NAME`
where 1 = 1
and dst.`TABLE_SCHEMA` = @targetdb
and dst.`TABLE_NAME` not like @targetdbexclude
and src.`TABLE_NAME` is null
order by dst.`TABLE_NAME`

-- new columns to be created in existing tables of targetdb
select srcc.`TABLE_NAME`, srcc.`COLUMN_NAME`, concat('alter table `', srcc.`TABLE_NAME`, '` add column `', srcc.`COLUMN_NAME`, '` ', srcc.`COLUMN_TYPE`, if(@addcollation = 1 and srcc.`CHARACTER_SET_NAME` is not null, concat(' character set ', srcc.`CHARACTER_SET_NAME`, ' collate ', srcc.`COLLATION_NAME`), ''), if(srcc.`IS_NULLABLE` = 'NO', ' not', ''), ' null', if(srcc.`COLUMN_DEFAULT` is null, '', concat(' default ', if(srcc.`CHARACTER_SET_NAME` is not null, '\'', ''), srcc.`COLUMN_DEFAULT`, if(srcc.`CHARACTER_SET_NAME` is not null, '\'', ''))), if(length(srcc.`EXTRA`) > 0, concat(' ', srcc.`EXTRA`), ''), ';') as stmt
from `information_schema`.`columns` srcc inner join `information_schema`.`tables` dstt on dstt.`TABLE_SCHEMA` = @targetdb and dstt.`TABLE_NAME` = srcc.`TABLE_NAME`
where 1 = 1
and srcc.`TABLE_SCHEMA` = @sourcedb
and srcc.`TABLE_NAME` not like @sourcedbexclude
and not exists(
  select 1
  from `information_schema`.`columns` dstc
  where 1 = 1
  and dstc.`TABLE_SCHEMA` = @targetdb
  and dstc.`TABLE_NAME` = srcc.`TABLE_NAME`
  and dstc.`COLUMN_NAME` = srcc.`COLUMN_NAME`
)
order by srcc.`TABLE_NAME`, srcc.`COLUMN_NAME`

-- old columns to be dropped in existing tables of targetdb
select dstc.`TABLE_NAME`, dstc.`COLUMN_NAME`, concat('alter table `', dstc.`TABLE_NAME`, '` drop column `', dstc.`COLUMN_NAME`, '`;') as stmt
from `information_schema`.`columns` dstc inner join `information_schema`.`tables` srct on srct.`TABLE_SCHEMA` = @sourcedb and srct.`TABLE_NAME` = dstc.`TABLE_NAME`
where 1 = 1
and dstc.`TABLE_SCHEMA` = @targetdb
and dstc.`TABLE_NAME` not like @targetdbexclude
and not exists(
  select 1
  from `information_schema`.`columns` srcc
  where 1 = 1
  and srcc.`TABLE_SCHEMA` = @sourcedb
  and srcc.`TABLE_NAME` = dstc.`TABLE_NAME`
  and srcc.`COLUMN_NAME` = dstc.`COLUMN_NAME`
)
order by dstc.`TABLE_NAME`, dstc.`COLUMN_NAME`

-- existing columns with changed specification
select src.`TABLE_NAME`, src.`COLUMN_NAME`, concat('alter table `', src.`TABLE_NAME`, '` change column `', src.`COLUMN_NAME`, '` `', src.`COLUMN_NAME`, '` ', src.`COLUMN_TYPE`, if(@addcollation = 1 and src.`CHARACTER_SET_NAME` is not null, concat(' character set ', src.`CHARACTER_SET_NAME`, ' collate ', src.`COLLATION_NAME`), ''), if(src.`IS_NULLABLE` = 'NO', ' not', ''), ' null', if(src.`COLUMN_DEFAULT` is null, '', concat(' default ', if(src.`CHARACTER_SET_NAME` is not null, '\'', ''), src.`COLUMN_DEFAULT`, if(src.`CHARACTER_SET_NAME` is not null, '\'', ''))), if(length(src.`EXTRA`) > 0, concat(' ', src.`EXTRA`), ''), ';') as stmt
from `information_schema`.`columns` src
inner join `information_schema`.`columns` dst on src.`TABLE_NAME` = dst.`TABLE_NAME` and src.`COLUMN_NAME` = dst.`COLUMN_NAME`
where 1 = 1
and src.`TABLE_SCHEMA` = @sourcedb
and dst.`TABLE_SCHEMA` = @targetdb
and (
  src.`COLUMN_TYPE` != dst.`COLUMN_TYPE`
  or src.`IS_NULLABLE` != dst.`IS_NULLABLE`
  or coalesce(src.`COLUMN_DEFAULT`, 'NULL') != coalesce(dst.`COLUMN_DEFAULT`, 'NULL')
  or src.`EXTRA` != dst.`EXTRA`
)
order by src.`TABLE_NAME`, src.`COLUMN_NAME`

-- side-by-side comparison of old spec vs. new spec of the changed columns
select
  src.`TABLE_NAME`, src.`COLUMN_NAME`,
  concat('alter table `', dst.`TABLE_NAME`, '` add column `', dst.`COLUMN_NAME`, '` ', dst.`COLUMN_TYPE`, if(@addcollation = 1 and dst.`CHARACTER_SET_NAME` is not null, concat(' character set ', dst.`CHARACTER_SET_NAME`, ' collate ', dst.`COLLATION_NAME`), ''), if(dst.`IS_NULLABLE` = 'NO', ' not', ''), ' null', if(dst.`COLUMN_DEFAULT` is null, '', concat(' default ', if(dst.`CHARACTER_SET_NAME` is not null, '\'', ''), dst.`COLUMN_DEFAULT`, if(dst.`CHARACTER_SET_NAME` is not null, '\'', ''))), if(length(dst.`EXTRA`) > 0, concat(' ', dst.`EXTRA`), ''), ';') as old_stmt,
  concat('alter table `', src.`TABLE_NAME`, '` add column `', src.`COLUMN_NAME`, '` ', src.`COLUMN_TYPE`, if(@addcollation = 1 and src.`CHARACTER_SET_NAME` is not null, concat(' character set ', src.`CHARACTER_SET_NAME`, ' collate ', src.`COLLATION_NAME`), ''), if(src.`IS_NULLABLE` = 'NO', ' not', ''), ' null', if(src.`COLUMN_DEFAULT` is null, '', concat(' default ', if(src.`CHARACTER_SET_NAME` is not null, '\'', ''), src.`COLUMN_DEFAULT`, if(src.`CHARACTER_SET_NAME` is not null, '\'', ''))), if(length(src.`EXTRA`) > 0, concat(' ', src.`EXTRA`), ''), ';') as new_stmt
from `information_schema`.`columns` src
inner join `information_schema`.`columns` dst on src.`TABLE_NAME` = dst.`TABLE_NAME` and src.`COLUMN_NAME` = dst.`COLUMN_NAME`
where 1 = 1
and src.`TABLE_SCHEMA` = @sourcedb
and dst.`TABLE_SCHEMA` = @targetdb
and (
  src.`COLUMN_TYPE` != dst.`COLUMN_TYPE`
  or src.`IS_NULLABLE` != dst.`IS_NULLABLE`
  or coalesce(src.`COLUMN_DEFAULT`, 'NULL') != coalesce(dst.`COLUMN_DEFAULT`, 'NULL')
  or src.`EXTRA` != dst.`EXTRA`
)
order by src.`TABLE_NAME`, src.`COLUMN_NAME`