SQL-Sync generates SQL code out of database definition in json format.

[ { "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;