From f203084699f06e99c14c2387c3a33fa517a54bb0 Mon Sep 17 00:00:00 2001
From: Arash Shams <ara4sh@hotmail.com>
Date: Sat, 4 Feb 2017 14:35:15 +0330
Subject: [PATCH] Adding db_collation_convertor procedure

---
 procedures/db_collation_convertor.sql | 70 +++++++++++++++++++++++++++++++++++
 sys_56.sql                            |  1 +
 sys_57.sql                            |  1 +
 3 files changed, 72 insertions(+)
 create mode 100644 procedures/db_collation_convertor.sql

diff --git a/procedures/db_collation_convertor.sql b/procedures/db_collation_convertor.sql
new file mode 100644
index 0000000..34695a9
--- /dev/null
+++ b/procedures/db_collation_convertor.sql
@@ -0,0 +1,70 @@
+--  Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
+--
+--  This program is free software; you can redistribute it and/or modify
+--  it under the terms of the GNU General Public License as published by
+--  the Free Software Foundation; version 2 of the License.
+--
+--  This program is distributed in the hope that it will be useful,
+--  but WITHOUT ANY WARRANTY; without even the implied warranty of
+--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+--  GNU General Public License for more details.
+--
+--  You should have received a copy of the GNU General Public License
+--  along with this program; if not, write to the Free Software
+--  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+
+DROP PROCEDURE IF EXISTS db_collation_convertor;
+
+DELIMITER $$
+
+CREATE DEFINER='root'@'localhost' PROCEDURE db_collation_convertor (
+	in in_dbname varchar(100), in in_charset varchar(100) , in in_collation varchar(100)
+	)
+	COMMENT '
+		Description
+		-----------
+		Convert all tables in database to new character set and collation .
+
+
+		Parameters
+		-----------
+
+		in_dbname (varchar(100)):
+			The Database name
+		
+		in_charset (varchar(100)):
+			The Character Set name, Example utf8
+
+		in_collation (varchar(100)):
+			The Collation name, Example: ut8_persian_ci
+
+		Example
+		--------
+
+		mysql> CALL sys.db_collation_covertor(''dbname'',''utf8'',''utf8_persian_ci'');
+
+		Query OK, 0 rows affected (0.00 sec)
+            '
+BEGIN
+	DECLARE finish INT DEFAULT 0;
+	DECLARE tab varchar(100);
+	DECLARE db_tables CURSOR FOR select table_name from information_schema.tables WHERE table_schema = in_dbname and table_type = 'base table';
+	DECLARE continue HANDLER FOR NOT found SET finish = 1;
+	IF NOT EXISTS (select COLLATION_NAME , CHARACTER_SET_NAME from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY where COLLATION_NAME = in_collation and CHARACTER_SET_NAME = in_charset) THEN
+			SELECT CONCAT('Invalid collation "', in_collation ,'" with character set "', in_charset,'"') AS Summary;
+			SET finish = 1;
+	END IF;
+	OPEN db_tables;
+	dbcolconv: LOOP
+		FETCH db_tables INTO tab;
+		IF finish = 1 THEN
+			LEAVE dbcolconv;
+		END IF;
+		SET @sql = CONCAT('alter table ', tab,' convert to character set ',in_charset,' collate ', in_collation);
+		PREPARE stmt FROM @sql;
+		EXECUTE stmt;
+		DEALLOCATE PREPARE stmt;
+	END LOOP;
+		CLOSE db_tables;
+END; $$
+DELIMITER ;
diff --git a/sys_56.sql b/sys_56.sql
index 1de01d2..f53528b 100644
--- a/sys_56.sql
+++ b/sys_56.sql
@@ -175,5 +175,6 @@ SOURCE ./procedures/ps_truncate_all_tables.sql
 
 SOURCE ./procedures/statement_performance_analyzer.sql
 SOURCE ./procedures/table_exists.sql
+SOURCE ./procedures/db_collation_convertor.sql
 
 SOURCE ./after_setup.sql
diff --git a/sys_57.sql b/sys_57.sql
index 277cc62..ca7ac82 100644
--- a/sys_57.sql
+++ b/sys_57.sql
@@ -191,5 +191,6 @@ SOURCE ./procedures/ps_truncate_all_tables.sql
 
 SOURCE ./procedures/statement_performance_analyzer.sql
 SOURCE ./procedures/table_exists.sql
+SOURCE ./procedures/db_collation_convertor.sql
 
 SOURCE ./after_setup.sql