SQL-Sync generates SQL code out of database definition in json format. The generated code syncs the structure to database while preserving data. Preserving data is possible even when tables or columns get renamed.

[ { "name": "request", "id": "ED43F57B4F664CF19A3C270E35EDB455", "engine": "InnoDB", "columns": [ { "name": "id", "id": "601B5A7883D9490BA34CAFD96399E3E6", "type": "int unsigned", "auto": true }, { "name": "date", "id": "CE9917DAAB6943D8A9B1D461018DC90D", "type": "timestamp", "default": "CURRENT_TIMESTAMP" }, { "name": "path", "id": "F04C11D4003B446FA9CD93CD59A41E60", "type": "varchar(128)", "null": true }, { "name": "client", "id": "A1DD8DCBC9B74785B274F54E6FA96471", "type": "varchar(50)", "null": true } ], "keys": [ { "name": "PRIMARY", "type": "primary key", "columns": [ "id" ] } ] } ]

[ { "subject": "request", "operations": [ "INSERT" ] } ]

set @old_db = null; select `SCHEMA_NAME` into @old_db from `INFORMATION_SCHEMA`.`SCHEMATA` where `SCHEMA_NAME` = 'db-01-info'; set @qry = if (isnull(@old_db), 'CREATE DATABASE IF NOT EXISTS `db-01-info`;' , 'SET @r = \'Database "db-01-info" exists.\';' ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @all_tables = ''; set @all_views = ''; set @all_tables = concat(@all_tables, '{ED43F57B4F664CF19A3C270E35EDB455}'); set @old_table = null; select `TABLE_NAME` into @old_table from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_COMMENT` = 'ED43F57B4F664CF19A3C270E35EDB455' and `TABLE_SCHEMA` = 'db-01-info'; set @qry = if (isnull(@old_table), 'CREATE TABLE `db-01-info`.`_sql_request` (`_sql_` int UNSIGNED NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT \'ED43F57B4F664CF19A3C270E35EDB455\';' , 'SET @r = \'Table "request" exist.\';' ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @sub_query = null; select group_concat(concat('`db-01-info`.`', `TABLE_NAME`, '`') SEPARATOR ', ') into @sub_query from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'db-01-info' and `TABLE_TYPE` = 'VIEW' and instr(@all_views, concat('{', `TABLE_NAME`, '}')) = 0; set @qry = if (isnull(@sub_query), 'SET @r = \'No extra view.\';' , concat('DROP VIEW ', @sub_query, ';') ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @sub_query = null; select group_concat(concat('`db-01-info`.`', `TABLE_NAME`, '` to `db-01-info`.`_sql__drop_', `TABLE_NAME`, '`') SEPARATOR ', ') into @sub_query from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_NAME` not like '_sql__drop_%' and `TABLE_SCHEMA` = 'db-01-info' and `TABLE_TYPE` = 'BASE TABLE' and instr(@all_tables, concat('{', `TABLE_COMMENT`, '}')) = 0; set @qry = if (isnull(@sub_query), 'SET @r = \'No extra table.\';' , concat('RENAME TABLE ', @sub_query, ';') ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @ren_tables_prefix = ''; set @ren_tables_final = ''; set @old_table = null; select `TABLE_NAME` into @old_table from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_COMMENT` = 'ED43F57B4F664CF19A3C270E35EDB455' and `TABLE_SCHEMA` = 'db-01-info'; set @ren_tables_prefix = if (@old_table != 'request' && instr(@old_table, '_sql_') != 1, concat(@ren_tables_prefix, '`db-01-info`.`', @old_table, '` to `db-01-info`.`_sql_request`, ') , @ren_tables_prefix ); set @ren_tables_final = if (@old_table != 'request', concat(@ren_tables_final, '`db-01-info`.`_sql_request` to `db-01-info`.`request`, ') , @ren_tables_final ); set @qry = if (@ren_tables_final != '', if (@ren_tables_prefix != '', concat ('RENAME TABLE ', substr(@ren_tables_prefix, 1, length(@ren_tables_prefix) - 2), ';') , 'SET @r = \'All tables have prefix.\';' ), 'SET @r = \'No table needs prefix.\';' ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @qry = if (@ren_tables_final != '', concat ('RENAME TABLE ', substr(@ren_tables_final, 1, length(@ren_tables_final) - 2), ';') , 'SET @r = \'No table rename needed.\';'); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @old_engine = null; select `ENGINE` into @old_engine from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_NAME` = 'request' and `TABLE_SCHEMA` = 'db-01-info'; set @qry = if (@old_engine != 'InnoDB', 'ALTER TABLE `db-01-info`.`request` ENGINE=InnoDB;' , 'SET @r = \'Engine of "request" is ok.\';' ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @all_columns = ''; set @sub_query = ''; set @all_columns = concat(@all_columns, '{601B5A7883D9490BA34CAFD96399E3E6}'); set @old_column = null; select `COLUMN_NAME` into @old_column from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_COMMENT` = '601B5A7883D9490BA34CAFD96399E3E6' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @sub_query = if (isnull(@old_column), concat(@sub_query, 'ADD `_sql_id` int unsigned COMMENT \'601B5A7883D9490BA34CAFD96399E3E6\', ') , @sub_query ); set @all_columns = concat(@all_columns, '{CE9917DAAB6943D8A9B1D461018DC90D}'); set @old_column = null; select `COLUMN_NAME` into @old_column from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_COMMENT` = 'CE9917DAAB6943D8A9B1D461018DC90D' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @sub_query = if (isnull(@old_column), concat(@sub_query, 'ADD `_sql_date` int unsigned COMMENT \'CE9917DAAB6943D8A9B1D461018DC90D\', ') , @sub_query ); set @all_columns = concat(@all_columns, '{F04C11D4003B446FA9CD93CD59A41E60}'); set @old_column = null; select `COLUMN_NAME` into @old_column from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_COMMENT` = 'F04C11D4003B446FA9CD93CD59A41E60' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @sub_query = if (isnull(@old_column), concat(@sub_query, 'ADD `_sql_path` int unsigned COMMENT \'F04C11D4003B446FA9CD93CD59A41E60\', ') , @sub_query ); set @all_columns = concat(@all_columns, '{A1DD8DCBC9B74785B274F54E6FA96471}'); set @old_column = null; select `COLUMN_NAME` into @old_column from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_COMMENT` = 'A1DD8DCBC9B74785B274F54E6FA96471' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @sub_query = if (isnull(@old_column), concat(@sub_query, 'ADD `_sql_client` int unsigned COMMENT \'A1DD8DCBC9B74785B274F54E6FA96471\', ') , @sub_query ); set @qry = if (@sub_query != '', concat('ALTER TABLE `db-01-info`.`request` ', substr(@sub_query, 1, length(@sub_query) - 2), ';') , 'SET @r = \'No new column in "request" is needed.\';' ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @sub_query = null; select group_concat(concat('RENAME COLUMN `', `COLUMN_NAME`, '` to `_sql__drop_', `COLUMN_NAME`, '`') SEPARATOR ', ') into @sub_query from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_NAME` not like '_sql__drop_%' and `TABLE_SCHEMA` = 'db-01-info' and `TABLE_NAME` = 'request' and instr(@all_columns, concat('{', `COLUMN_COMMENT`, '}')) = 0; set @qry = if (isnull(@sub_query), 'SET @r = \'No extra column in "request".\';' , concat('ALTER TABLE `db-01-info`.`request` ', @sub_query, ';') ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @ren_columns_prefix = ''; set @ren_columns_final = ''; set @old_column = null; select `COLUMN_NAME` into @old_column from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_COMMENT` = '601B5A7883D9490BA34CAFD96399E3E6' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @ren_columns_prefix = if (@old_column != 'id' && instr(@old_column, '_sql_') != 1, concat(@ren_columns_prefix, 'RENAME COLUMN `', @old_column, '` to `_sql_id`, ') , @ren_columns_prefix ); set @ren_columns_final = if (@old_column != 'id', concat(@ren_columns_final, 'RENAME COLUMN `_sql_id` to `id`, ') , @ren_columns_final ); set @old_column = null; select `COLUMN_NAME` into @old_column from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_COMMENT` = 'CE9917DAAB6943D8A9B1D461018DC90D' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @ren_columns_prefix = if (@old_column != 'date' && instr(@old_column, '_sql_') != 1, concat(@ren_columns_prefix, 'RENAME COLUMN `', @old_column, '` to `_sql_date`, ') , @ren_columns_prefix ); set @ren_columns_final = if (@old_column != 'date', concat(@ren_columns_final, 'RENAME COLUMN `_sql_date` to `date`, ') , @ren_columns_final ); set @old_column = null; select `COLUMN_NAME` into @old_column from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_COMMENT` = 'F04C11D4003B446FA9CD93CD59A41E60' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @ren_columns_prefix = if (@old_column != 'path' && instr(@old_column, '_sql_') != 1, concat(@ren_columns_prefix, 'RENAME COLUMN `', @old_column, '` to `_sql_path`, ') , @ren_columns_prefix ); set @ren_columns_final = if (@old_column != 'path', concat(@ren_columns_final, 'RENAME COLUMN `_sql_path` to `path`, ') , @ren_columns_final ); set @old_column = null; select `COLUMN_NAME` into @old_column from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_COMMENT` = 'A1DD8DCBC9B74785B274F54E6FA96471' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @ren_columns_prefix = if (@old_column != 'client' && instr(@old_column, '_sql_') != 1, concat(@ren_columns_prefix, 'RENAME COLUMN `', @old_column, '` to `_sql_client`, ') , @ren_columns_prefix ); set @ren_columns_final = if (@old_column != 'client', concat(@ren_columns_final, 'RENAME COLUMN `_sql_client` to `client`, ') , @ren_columns_final ); set @qry = if (@ren_columns_final != '', if (@ren_columns_prefix != '', concat ('ALTER TABLE `db-01-info`.`request` ', substr(@ren_columns_prefix, 1, length(@ren_columns_prefix) - 2), ';') , 'SET @r = \'All columns in "request" have prefix.\';' ), 'SET @r = \'No column in "request" needs prefix.\';' ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @qry = if (@ren_columns_final != '', concat ('ALTER TABLE `db-01-info`.`request` ', substr(@ren_columns_final, 1, length(@ren_columns_final) - 2), ';') , 'SET @r = \'No column in "request" needs rename.\';'); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @all_foreign_keys = ''; set @sub_query = null; select group_concat(distinct concat('DROP FOREIGN KEY `', `CONSTRAINT_NAME`, '`') SEPARATOR ', ') into @sub_query from `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` where `REFERENCED_TABLE_NAME` is not null and `TABLE_SCHEMA` = 'db-01-info' and `TABLE_NAME` = 'request' and instr(@all_foreign_keys, `CONSTRAINT_NAME`) = 0; set @qry = if (isnull(@sub_query), 'SET @r = \'No extra foreign keys in "request".\';' , concat('ALTER TABLE `db-01-info`.`request` ', @sub_query, ';') ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @sub_query = ''; set @ordinal_change = false; set @old_type = null; set @old_default = null; set @old_null = null; set @old_auto = null; set @old_position = null; select `COLUMN_TYPE`, `COLUMN_DEFAULT`, `IS_NULLABLE`, `EXTRA` like '%auto_increment%' as AUTO, `ORDINAL_POSITION` into @old_type, @old_default, @old_null, @old_auto, @old_position from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_NAME` = 'id' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @ordinal_change = if (@old_position != 1, true, @ordinal_change); set @sub_query = if (@ordinal_change or @old_type != 'int unsigned' or @old_default != 'null' or @old_null != 'NO' or @old_auto != true, concat(@sub_query, 'MODIFY `id` int unsigned not null auto_increment COMMENT \'601B5A7883D9490BA34CAFD96399E3E6\' FIRST, ') , @sub_query ); set @old_type = null; set @old_default = null; set @old_null = null; set @old_auto = null; set @old_position = null; select `COLUMN_TYPE`, `COLUMN_DEFAULT`, `IS_NULLABLE`, `EXTRA` like '%auto_increment%' as AUTO, `ORDINAL_POSITION` into @old_type, @old_default, @old_null, @old_auto, @old_position from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_NAME` = 'date' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @ordinal_change = if (@old_position != 2, true, @ordinal_change); set @sub_query = if (@ordinal_change or @old_type != 'timestamp' or @old_default != 'CURRENT_TIMESTAMP' or @old_null != 'NO' or @old_auto != false, concat(@sub_query, 'MODIFY `date` timestamp DEFAULT CURRENT_TIMESTAMP not null COMMENT \'CE9917DAAB6943D8A9B1D461018DC90D\' AFTER `id`, ') , @sub_query ); set @old_type = null; set @old_default = null; set @old_null = null; set @old_auto = null; set @old_position = null; select `COLUMN_TYPE`, `COLUMN_DEFAULT`, `IS_NULLABLE`, `EXTRA` like '%auto_increment%' as AUTO, `ORDINAL_POSITION` into @old_type, @old_default, @old_null, @old_auto, @old_position from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_NAME` = 'path' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @ordinal_change = if (@old_position != 3, true, @ordinal_change); set @sub_query = if (@ordinal_change or @old_type != 'varchar(128)' or @old_default != 'null' or @old_null != 'YES' or @old_auto != false, concat(@sub_query, 'MODIFY `path` varchar(128) null COMMENT \'F04C11D4003B446FA9CD93CD59A41E60\' AFTER `date`, ') , @sub_query ); set @old_type = null; set @old_default = null; set @old_null = null; set @old_auto = null; set @old_position = null; select `COLUMN_TYPE`, `COLUMN_DEFAULT`, `IS_NULLABLE`, `EXTRA` like '%auto_increment%' as AUTO, `ORDINAL_POSITION` into @old_type, @old_default, @old_null, @old_auto, @old_position from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMN_NAME` = 'client' and `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info'; set @ordinal_change = if (@old_position != 4, true, @ordinal_change); set @sub_query = if (@ordinal_change or @old_type != 'varchar(50)' or @old_default != 'null' or @old_null != 'YES' or @old_auto != false, concat(@sub_query, 'MODIFY `client` varchar(50) null COMMENT \'A1DD8DCBC9B74785B274F54E6FA96471\' AFTER `path`, ') , @sub_query ); set @all_keys = ''; set @all_keys = concat(@all_keys, 'PRIMARY '); set @old_index = null; set @old_key_def = null; select `INDEX_NAME`, group_concat(concat('`', `COLUMN_NAME`, '`') ORDER BY `SEQ_IN_INDEX` SEPARATOR ', ') into @old_index, @old_key_def from `INFORMATION_SCHEMA`.`STATISTICS` where `TABLE_SCHEMA` = 'db-01-info' and `TABLE_NAME` = 'request' and `INDEX_NAME` = 'PRIMARY' group by `INDEX_NAME`; set @old_ok = @old_key_def = '`id`'; set @drop_query = if (@old_ok or isnull(@old_index), '', 'DROP INDEX `PRIMARY`, '); set @sub_query = concat(@sub_query, @drop_query); set @sub_query = if (@drop_query != '' or isnull(@old_index), concat(@sub_query, 'ADD primary key `PRIMARY` (`id`), ') , @sub_query); set @drop_query = null; select group_concat(distinct concat('DROP INDEX `', `INDEX_NAME`, '`') SEPARATOR ', ') into @drop_query from `INFORMATION_SCHEMA`.`STATISTICS` join `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` on `INFORMATION_SCHEMA`.`STATISTICS`.`INDEX_SCHEMA` = `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`.`CONSTRAINT_SCHEMA` and `INFORMATION_SCHEMA`.`STATISTICS`.`TABLE_NAME` = `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`.`TABLE_NAME` and `INFORMATION_SCHEMA`.`STATISTICS`.`INDEX_NAME` = `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`.`CONSTRAINT_NAME` where `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`.`REFERENCED_TABLE_NAME` is null and `INFORMATION_SCHEMA`.`STATISTICS`.`INDEX_SCHEMA` = 'db-01-info' and `INFORMATION_SCHEMA`.`STATISTICS`.`TABLE_NAME` = 'request' and instr(@all_keys, `INDEX_NAME`) = 0; set @sub_query = if (isnull(@drop_query), @sub_query, concat(@sub_query, @drop_query, ', ') ); set @drop_query = null; select group_concat(concat('DROP COLUMN `', `COLUMN_NAME`, '`') SEPARATOR ', ') into @drop_query from `INFORMATION_SCHEMA`.`COLUMNS` where `COLUMNS`.`TABLE_NAME` = 'request' and `COLUMNS`.`TABLE_SCHEMA` = 'db-01-info' and `COLUMN_NAME` like '_sql__drop_%'; set @sub_query = if (isnull(@drop_query), @sub_query, concat(@sub_query, @drop_query, ', ') ); set @qry = if (@sub_query != '', concat ('ALTER TABLE `db-01-info`.`request` ', substr(@sub_query, 1, length(@sub_query) - 2), ';') , 'SET @r = \'Table "request" is ok.\';' ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @sub_query = null; select group_concat(concat('`db-01-info`.`', `TABLE_NAME`, '`') SEPARATOR ', ') into @sub_query from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'db-01-info' and `TABLE_NAME` like '_sql__drop_%'; set @qry = if (isnull(@sub_query), 'SET @r = \'No extra table.\';', concat('DROP TABLE ', @sub_query, ';') ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @old_user = null; select `USER` into @old_user from `mysql`.`user` where `USER` = 'db-01-client'; set @qry = if (isnull(@old_user), concat('CREATE USER \'db-01-client\' IDENTIFIED BY \'', MD5(RAND()), '\';') , 'SET @r = \'User "db-01-client" exists.\';' ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @all_grants = ' request '; set @sub_query = null; select group_concat(concat('`', `table_name`, '`') separator ', ') into @sub_query from `mysql`.`tables_priv` where `Db` = 'db-01-info' and `user` = 'db-01-client' and instr(@all_grants, `table_name`) = 0; set @qry = if (isnull(@sub_query), 'SET @r = \'No extra permissions for "db-01-client".\';' , 'REVOKE ALL PRIVILEGES ON *.* FROM \'db-01-client\';' ); prepare stmt from @qry; execute stmt; deallocate prepare stmt; set @old_grant = null; select `table_priv` into @old_grant from `mysql`.`tables_priv` where `Db` = 'db-01-info' and `user` = 'db-01-client' and `table_name` = 'request'; set @qry = if (@old_grant = 'INSERT', 'SET @r = \'Permissions on "request" for "db-01-client" is ok.\';' , 'GRANT INSERT ON `db-01-info`.`request` TO \'db-01-client\';' ); prepare stmt from @qry; execute stmt; deallocate prepare stmt;