commit 0cfe068d65b1c1b92ac02993ac0d2a8fd2fc6591
parent b11559246f9aee3d5c91e04514908321548a90a5
Author: root <root>
Date: Sat, 7 Sep 2024 21:57:37 +0200
add few more queries
Diffstat:
6 files changed, 173 insertions(+), 0 deletions(-)
diff --git a/sql/snippets/change_collation_all_tables_and_columns.sql b/sql/snippets/change_collation_all_tables_and_columns.sql
@@ -0,0 +1 @@
+SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") AS executethis FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="some_schema" AND TABLE_TYPE="BASE TABLE";
diff --git a/sql/snippets/check_sp_prics.sql b/sql/snippets/check_sp_prics.sql
@@ -0,0 +1 @@
+SELECT CONCAT('GRANT EXECUTE ON PROCEDURE ', db, '.', routine_name, ' TO ', '\'', user '\'', '@', '\'', host '\'', ';') FROM mysql.procs_priv WHERE routine_name = 'some_stored_proc';
diff --git a/sql/snippets/compare_schema_struct.sql b/sql/snippets/compare_schema_struct.sql
@@ -0,0 +1,65 @@
+USE some_schema;
+
+DROP PROCEDURE IF EXISTS `compare_schemas`;
+
+DELIMITER $$
+
+CREATE PROCEDURE compare_schemas(IN source_db VARCHAR(64), IN target_db VARCHAR(64))
+BEGIN
+ SELECT
+ 'Only in source' AS exist_type,
+ c1.table_schema, c1.table_name, c1.column_name, c1.ordinal_position, c1.column_default, c1.is_nullable, c1.numeric_precision,
+ c1.numeric_scale, c1.character_set_name, c1.collation_name, c1.column_type, c1.column_key, c1.extra, c1.column_comment
+ FROM
+ (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = source_db) c1
+ LEFT JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = target_db) c2
+ ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name
+ WHERE c2.column_name IS NULL
+
+ UNION ALL
+
+ SELECT
+ 'Only in target' AS exist_type,
+ c2.table_schema, c2.table_name, c2.column_name, c2.ordinal_position, c2.column_default, c2.is_nullable, c2.numeric_precision,
+ c2.numeric_scale, c2.character_set_name, c2.collation_name, c2.column_type, c2.column_key, c2.extra, c2.column_comment
+ FROM
+ (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = source_db) c1
+ RIGHT JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = target_db) c2
+ ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name
+ WHERE c1.column_name IS NULL
+
+ UNION ALL
+
+ SELECT
+ 'In both schemas' AS exist_type,
+ CONCAT(c1.table_schema, '/', c2.table_schema),
+ c1.table_name, c1.column_name,
+ IF(c1.ordinal_position = c2.ordinal_position OR c1.ordinal_position IS NULL AND c2.ordinal_position IS NULL, NULL,
+ CONCAT_WS('/', IFNULL(c1.ordinal_position, ''), IFNULL(c2.ordinal_position, ''))),
+ IF(c1.column_default = c2.column_default OR c1.column_default IS NULL AND c2.column_default IS NULL, NULL,
+ CONCAT_WS('/', IFNULL(c1.column_default, ''), IFNULL(c2.column_default, ''))),
+ IF(c1.is_nullable = c2.is_nullable OR c1.is_nullable IS NULL AND c2.is_nullable IS NULL, NULL,
+ CONCAT_WS('/', IFNULL(c1.is_nullable, ''), IFNULL(c2.is_nullable, ''))),
+ IF(c1.numeric_precision = c2.numeric_precision OR c1.numeric_precision IS NULL AND c2.numeric_precision IS NULL, NULL,
+ CONCAT_WS('/', IFNULL(c1.numeric_precision, ''), IFNULL(c2.numeric_precision, ''))),
+ IF(c1.numeric_scale = c2.numeric_scale OR c1.numeric_scale IS NULL AND c2.numeric_scale IS NULL, NULL,
+ CONCAT_WS('/', IFNULL(c1.numeric_scale, ''), IFNULL(c2.numeric_scale, ''))),
+ IF(c1.character_set_name = c2.character_set_name OR c1.character_set_name IS NULL AND c2.character_set_name IS NULL, NULL,
+ CONCAT_WS('/', IFNULL(c1.character_set_name, ''), IFNULL(c2.character_set_name, ''))),
+ IF(c1.collation_name = c2.collation_name OR c1.collation_name IS NULL AND c2.collation_name IS NULL, NULL,
+ CONCAT_WS('/', IFNULL(c1.collation_name, ''), IFNULL(c2.collation_name, ''))),
+ IF(c1.column_type = c2.column_type OR c1.column_type IS NULL AND c2.column_type IS NULL, NULL,
+ CONCAT_WS('/', IFNULL(c1.column_type, ''), IFNULL(c2.column_type, ''))),
+ IF(c1.column_key = c2.column_key OR c1.column_key IS NULL AND c2.column_key IS NULL, NULL,
+ CONCAT_WS('/', IFNULL(c1.column_key, ''), IFNULL(c2.column_key, ''))),
+ IF(c1.extra = c2.extra OR c1.extra IS NULL AND c2.extra IS NULL, NULL,
+ CONCAT_WS('/', IFNULL(c1.extra, ''), IFNULL(c2.extra, ''))),
+ IF(c1.column_comment = c2.column_comment OR c1.column_comment IS NULL AND c2.column_comment IS NULL, NULL,
+ CONCAT_WS('/', IFNULL(c1.column_comment, ''), IFNULL(c2.column_comment, '')))
+ FROM
+ (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = source_db) c1
+ JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = target_db) c2
+ ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name;
+END $$
+
+DELIMITER ;
diff --git a/sql/snippets/copy_schema_struct.sql b/sql/snippets/copy_schema_struct.sql
@@ -0,0 +1,73 @@
+USE some_schema;
+
+-- May need to add DEFINER
+DROP PROCEDURE IF EXISTS `copy_schema_with_config`;
+
+CREATE PROCEDURE copy_schema_with_config(IN inOldDB VARCHAR(256), IN inNewDB VARCHAR(256), IN copyTables INT(1))
+ BEGIN
+
+ DECLARE v_finished INTEGER DEFAULT 0;
+ DECLARE v_tname varchar(100) DEFAULT "";
+
+ DECLARE currentTable CURSOR FOR
+ SELECT `table_name`
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = inOldDB
+ and TABLE_TYPE='BASE TABLE';
+
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
+ -- Should be able to delete tables even if they have foreign keys
+ SET FOREIGN_KEY_CHECKS = 0;
+ set @result=NULL;
+
+ -- Create new schema if it doesn't yet exist
+ SET @create = CONCAT('CREATE SCHEMA IF NOT EXISTS ',inNewDB,';');
+ PREPARE create_schema FROM @create;
+ EXECUTE create_schema;
+ DEALLOCATE PREPARE create_schema;
+
+ -- Loop over tables in the old schema
+ OPEN currentTable;
+ clone_tables: LOOP
+
+ -- Get the next table name
+ FETCH currentTable INTO v_tname;
+
+ -- Leave the Loop if we're done
+ IF v_finished = 1 THEN LEAVE clone_tables;
+ END IF;
+
+ -- Drop tables in target DB schema if they already exist
+ SET @drop = CONCAT("DROP TABLE IF EXISTS `" , inNewDB, "`.`", v_tname,"`;");
+ PREPARE stmt FROM @drop;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+
+ -- Clone the table from the origin
+ SET @sql = CONCAT("CREATE TABLE `", inNewDB, "`.`", v_tname, "` LIKE `", inOldDB, "`.`", v_tname, "`;");
+ PREPARE clone_table FROM @sql;
+ EXECUTE clone_table;
+ DEALLOCATE PREPARE clone_table;
+
+ -- Optionally clone some data
+ -- maybe change this to a for each in kind of thing besides config
+ IF (copyTables > 0 && v_tname = 'config') THEN
+ SET @data = CONCAT("INSERT INTO `", inNewDB, "`.`", v_tname, "` SELECT * FROM `", inOldDB, "`.`", v_tname, "`;");
+ PREPARE clone_data FROM @data;
+ EXECUTE clone_data;
+ DEALLOCATE PREPARE clone_data;
+ END IF;
+
+ -- Give the user some result message
+ SET @result = IFNULL(CONCAT(@result,',',v_tname),v_tname);
+
+ END LOOP clone_tables;
+
+ -- Close cursor
+
+ CLOSE currentTable;
+ SET FOREIGN_KEY_CHECKS = 1;
+ -- Print result message
+ SELECT CONCAT("Copied schema structure from ", inOldDB, " to ", inNewDB, ": ", @result);
+
+ END;
diff --git a/sql/snippets/db_logging.sql b/sql/snippets/db_logging.sql
@@ -0,0 +1,29 @@
+USE some_schema;
+
+CREATE TABLE some_schema.sp_logging (
+ `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
+ `runtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
+ `user` VARCHAR(255) NOT NULL,
+ `server_id` INT UNSIGNED NOT NULL,
+ `sp` VARCHAR(255) NOT NULL,
+ `db` VARCHAR(255) NOT NULL,
+ `comment` VARCHAR(512) NOT NULL,
+ `processlist` TEXT DEFAULT NULL,
+ PRIMARY KEY (`id`, server_id),
+ KEY sp_logging_idx1 (`sp`, `runtime`)
+) ;
+
+DELIMITER $$
+CREATE PROCEDURE some_schema.log(IN i_sp VARCHAR(255), IN i_db VARCHAR(255), i_comment VARCHAR(512))
+ MODIFIES SQL DATA
+BEGIN
+ INSERT INTO some_schema.sp_logging VALUES (null, null, CURRENT_USER(), (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'server_id'), i_sp, i_db, i_comment, (SELECT GROUP_CONCAT('[', CONCAT('USER: ', user, 'DB: ', db, 'TIME: ', NOW(), 'SQL: ', LEFT(info,512)), ']') FROM information_schema.processlist WHERE info IS NOT NULL AND id<>connection_id()));
+
+END $$
+
+DELIMITER ;
+
+-- THE DEFINER OF THIS SP SHOULD HAVE EXECUTE RIGHTS TO IT, AS WELL AS SELECT AND INSERT to this some_schema.sp_logging table!!!
+-- GIVE EXEC RIGHTS TO WHICHEVER USERS NEED TO LOG STUFF INTO THEIR SPS
+
+-- CALL some_schema.log('the sp', 'the schema', 'some message');
diff --git a/sql/snippets/table_sizes.sql b/sql/snippets/table_sizes.sql
@@ -0,0 +1,4 @@
+SELECT table_name AS `Table`, ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
+FROM information_schema.TABLES
+WHERE table_schema = "db_name"
+ORDER BY `Size in MB`;