From: Alan Knowles <alan@roojs.com>
Date: Thu, 18 Jun 2015 06:01:38 +0000 (+0800)
Subject: mysql/mysql_change_engine.sql
X-Git-Url: http://git.roojs.org/?p=Pman.Core;a=commitdiff_plain;h=ede135aa29342579295ed1bb8676d6671024ba72

mysql/mysql_change_engine.sql
---

diff --git a/mysql/mysql_change_engine.sql b/mysql/mysql_change_engine.sql
index 76119685..57c9b5e4 100644
--- a/mysql/mysql_change_engine.sql
+++ b/mysql/mysql_change_engine.sql
@@ -2,6 +2,9 @@
 
 DROP PROCEDURE IF EXISTS mysql_change_engine;
 
+delimiter $$
+
+
 CREATE PROCEDURE mysql_change_engine(mytb TEXT)
     BEGIN
     DECLARE mydb TEXT;
@@ -10,42 +13,48 @@ CREATE PROCEDURE mysql_change_engine(mytb TEXT)
     SELECT database() INTO mydb;
     
     
-    SELECT IF(
-                dbtb2='.',
-                CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
-                CONCAT('SELECT ''',dbtb1,' is Already InnoDB'' as \"No Need to Convert\"')
-            )
-            INTO
-            ConvertEngineSQL
-            
-            FROM (
+    SELECT
+        IF(
+            dbtb2='.',
+            CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
+            CONCAT('SELECT ''',dbtb1,' is Already InnoDB'' as \"No Need to Convert\"')
+        )
+    INTO
+        ConvertEngineSQL
+        
+    FROM (
+        SELECT
+            CONCAT(A.db,'.',A.tb) dbtb1,
+            CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
+        FROM
+            (
+                SELECT
+                    table_schema db,table_name tb,engine
+                FROM
+                    information_schema.tables
+                WHERE
+                    table_schema=mydb and table_name=mytb
+            ) A
+        LEFT JOIN
+            (
                 SELECT
-                    CONCAT(A.db,'.',A.tb) dbtb1,
-                    CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
+                    table_schema db,table_name tb
                 FROM
-                    (
-                        SELECT
-                            table_schema db,table_name tb,engine
-                        FROM
-                            information_schema.tables
-                        WHERE
-                            table_schema=mydb and table_name=mytb
-                    ) A
-                LEFT JOIN
-                    (
-                        SELECT
-                            table_schema db,table_name tb
-                        FROM
-                            information_schema.tables
-                        WHERE
-                            table_schema=mydb and table_name=mytb AND engine='InnoDB'
-                    ) B
-                USING
-                    (db,tb)
-            ) AA;
+                    information_schema.tables
+                WHERE
+                    table_schema=mydb and table_name=mytb AND engine='InnoDB'
+            ) B
+        USING
+            (db,tb)
+    ) AA;
             
-            //SELECT ConvertEngineSQL; -- ???
-            PREPARE st FROM ConvertEngineSQL;
-            EXECUTE st;
-            DEALLOCATE PREPARE st;
-            
\ No newline at end of file
+    -- SELECT ConvertEngineSQL; -- ???
+    PREPARE st FROM ConvertEngineSQL;
+    EXECUTE st;
+    DEALLOCATE PREPARE st;
+
+END;
+
+$$
+
+DELIMITER ;