From aaa558f7ea01bff46a686426c942fa4a2d2cdd69 Mon Sep 17 00:00:00 2001
From: Michael <heluecht@pirati.ca>
Date: Sat, 22 Apr 2017 21:36:01 +0000
Subject: [PATCH] New command in dbstructure.php to convert all tables from
 MyISAM to InnoDB

---
 include/dbstructure.php | 43 ++++++++++++++++++++++++++++++++---------
 mod/admin.php           |  2 +-
 2 files changed, 35 insertions(+), 10 deletions(-)

diff --git a/include/dbstructure.php b/include/dbstructure.php
index 0d5c10f98d..e5d29fa085 100644
--- a/include/dbstructure.php
+++ b/include/dbstructure.php
@@ -7,13 +7,38 @@ require_once("include/text.php");
 
 define('NEW_UPDATE_ROUTINE_VERSION', 1170);
 
+/*
+ * Converts all tables from MyISAM to InnoDB
+ */
+function convert_to_innodb() {
+	global $db;
+
+	$r = q("SELECT `TABLE_NAME` FROM `information_schema`.`tables` WHERE `engine` = 'MyISAM' AND `table_schema` = '%s'",
+		dbesc($db->database_name()));
+
+	if (!dbm::is_result($r)) {
+		echo t('There are no tables on MyISAM.')."\n";
+		return;
+	}
+
+	foreach ($r AS $table) {
+		$sql = sprintf("ALTER TABLE `%s` engine=InnoDB;", dbesc($table['TABLE_NAME']));
+		echo $sql."\n";
+
+		$result = @$db->q($sql);
+		if (!dbm::is_result($result)) {
+			print_update_error($db, $sql);
+		}
+	}
+}
+
 /*
  * send the email and do what is needed to do on update fails
  *
  * @param update_id		(int) number of failed update
  * @param error_message	(str) error message
  */
-function update_fail($update_id, $error_message){
+function update_fail($update_id, $error_message) {
 	//send the administrators an e-mail
 	$admin_mail_list = "'".implode("','", array_map(dbesc, explode(",", str_replace(" ", "", $a->config['admin_email']))))."'";
 	$adminlist = q("SELECT uid, language, email FROM user WHERE email IN (%s)",
@@ -95,10 +120,6 @@ function table_structure($table) {
 
 	if (dbm::is_result($indexes))
 		foreach ($indexes AS $index) {
-			if ($index["Index_type"] == "FULLTEXT") {
-				continue;
-			}
-
 			if ($index['Key_name'] != 'PRIMARY' && $index['Non_unique'] == '0' && !isset($indexdata[$index["Key_name"]])) {
 				$indexdata[$index["Key_name"]] = array('UNIQUE');
 			}
@@ -460,7 +481,7 @@ function db_field_command($parameters, $create = true) {
 	if ($parameters["not null"])
 		$fieldstruct .= " NOT NULL";
 
-	if (isset($parameters["default"])){
+	if (isset($parameters["default"])) {
 		if (strpos(strtolower($parameters["type"]),"int")!==false) {
 			$fieldstruct .= " DEFAULT ".$parameters["default"];
 		} else {
@@ -487,7 +508,7 @@ function db_create_table($name, $fields, $verbose, $action, $indexes=null) {
 	$primary_keys = array();
 	foreach ($fields AS $fieldname => $field) {
 		$sql_rows[] = "`".dbesc($fieldname)."` ".db_field_command($field);
-		if (x($field,'primary') and $field['primary']!=''){
+		if (x($field,'primary') and $field['primary']!='') {
 			$primary_keys[] = $fieldname;
 		}
 	}
@@ -1691,7 +1712,7 @@ function db_definition() {
 function dbstructure_run(&$argv, &$argc) {
 	global $a, $db;
 
-	if (is_null($a)){
+	if (is_null($a)) {
 		$a = new App;
 	}
 
@@ -1730,6 +1751,9 @@ function dbstructure_run(&$argv, &$argc) {
 			case "dumpsql":
 				print_structure(db_definition());
 				return;
+			case "innodb":
+				convert_to_innodb();
+				return;
 		}
 	}
 
@@ -1741,11 +1765,12 @@ function dbstructure_run(&$argv, &$argc) {
 	echo "dryrun		show database update schema queries without running them\n";
 	echo "update		update database schema\n";
 	echo "dumpsql		dump database schema\n";
+	echo "innodb		convert all tables from MyISAM to InnoDB\n";
 	return;
 
 }
 
-if (array_search(__file__,get_included_files())===0){
+if (array_search(__file__,get_included_files())===0) {
 	dbstructure_run($_SERVER["argv"],$_SERVER["argc"]);
 	killme();
 }
diff --git a/mod/admin.php b/mod/admin.php
index c57e7f3545..c86c0db91e 100644
--- a/mod/admin.php
+++ b/mod/admin.php
@@ -447,7 +447,7 @@ function admin_page_summary(App $a) {
 	$warningtext = array();
 	if (dbm::is_result($r)) {
 		$showwarning = true;
-		$warningtext[] = sprintf(t('Your DB still runs with MyISAM tables. You should change the engine type to InnoDB. As Friendica will use InnoDB only features in the future, you should change this! See <a href="%s">here</a> for a guide that may be helpful converting the table engines. You may also use the <tt>convert_innodb.sql</tt> in the <tt>/util</tt> directory of your Friendica installation.<br />'), 'https://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html');
+		$warningtext[] = sprintf(t('Your DB still runs with MyISAM tables. You should change the engine type to InnoDB. As Friendica will use InnoDB only features in the future, you should change this! See <a href="%s">here</a> for a guide that may be helpful converting the table engines. You may also use the command <tt>php include/dbstructure.php innodb</tt> of your Friendica installation for an automatic conversion.<br />'), 'https://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html');
 	}
 	// MySQL >= 5.7.4 doesn't support the IGNORE keyword in ALTER TABLE statements
 	if ((version_compare($db->server_info(), '5.7.4') >= 0) AND
-- 
2.39.5