X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;ds=sidebyside;f=include%2Fdbstructure.php;h=59f19e9bbc5be0e2c1c10764adb14938677c8651;hb=47fd9226c3f0b90f460fe2031fa46cbb49a87100;hp=406ad6c93da52140e2a6659850aa66f69eccb35e;hpb=879ebb1d7ff4646391875dc85781ffa1f8aafcea;p=friendica.git

diff --git a/include/dbstructure.php b/include/dbstructure.php
index 406ad6c93d..59f19e9bbc 100644
--- a/include/dbstructure.php
+++ b/include/dbstructure.php
@@ -78,8 +78,18 @@ function update_fail($update_id, $error_message){
 function table_structure($table) {
 	$structures = q("DESCRIBE `%s`", $table);
 
+	$full_columns = q("SHOW FULL COLUMNS FROM `%s`", $table);
+
 	$indexes = q("SHOW INDEX FROM `%s`", $table);
 
+	$table_status = q("SHOW TABLE STATUS WHERE `name` = '%s'", $table);
+
+	if (dbm::is_result($table_status)) {
+		$table_status = $table_status[0];
+	} else {
+		$table_status = array();
+	}
+
 	$fielddata = array();
 	$indexdata = array();
 
@@ -104,7 +114,6 @@ function table_structure($table) {
 
 			$indexdata[$index["Key_name"]][] = $column;
 		}
-
 	if (dbm::is_result($structures)) {
 		foreach ($structures AS $field) {
 			$fielddata[$field["Field"]]["type"] = $field["Type"];
@@ -125,10 +134,16 @@ function table_structure($table) {
 			}
 		}
 	}
-	return(array("fields"=>$fielddata, "indexes"=>$indexdata));
+	if (dbm::is_result($full_columns)) {
+		foreach ($full_columns AS $column) {
+			$fielddata[$column["Field"]]["Collation"] = $column["Collation"];
+		}
+	}
+
+	return array("fields" => $fielddata, "indexes" => $indexdata, "table_status" => $table_status);
 }
 
-function print_structure($database, $charset) {
+function print_structure($database) {
 	echo "-- ------------------------------------------\n";
 	echo "-- ".FRIENDICA_PLATFORM." ".FRIENDICA_VERSION." (".FRIENDICA_CODENAME,")\n";
 	echo "-- DB_UPDATE_VERSION ".DB_UPDATE_VERSION."\n";
@@ -137,24 +152,33 @@ function print_structure($database, $charset) {
 		echo "--\n";
 		echo "-- TABLE $name\n";
 		echo "--\n";
-		db_create_table($name, $structure['fields'], $charset, true, false, $structure["indexes"]);
+		db_create_table($name, $structure['fields'], true, false, $structure["indexes"]);
 
 		echo "\n";
 	}
 }
 
+/**
+ * @brief Print out database error messages
+ *
+ * @param object $db Database object
+ * @param string $message Message to be added to the error message
+ *
+ * @return string Error message
+ */
+function print_update_error($db, $message) {
+	echo sprintf(t("\nError %d occured during database update:\n%s\n"),
+		$db->errorno, $db->error);
+
+	return t('Errors encountered performing database changes: ').$message.EOL;
+}
+
 function update_structure($verbose, $action, $tables=null, $definition=null) {
 	global $a, $db;
 
 	if ($action) {
 		Config::set('system', 'maintenance', 1);
-		Config::set('system', 'maintenance_reason', 'Database update');
-	}
-
-	if (isset($a->config["system"]["db_charset"])) {
-		$charset = $a->config["system"]["db_charset"];
-	} else {
-		$charset = "utf8";
+		Config::set('system', 'maintenance_reason', sprintf(t(': Database update'), dbm::date().' '.date('e')));
 	}
 
 	$errors = false;
@@ -168,16 +192,18 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 		$tables = q("SHOW TABLES");
 	}
 
-	foreach ($tables AS $table) {
-		$table = current($table);
+	if (dbm::is_result($tables)) {
+		foreach ($tables AS $table) {
+			$table = current($table);
 
-		logger(sprintf('updating structure for table %s ...', $table), LOGGER_DEBUG);
-		$database[$table] = table_structure($table);
+			logger(sprintf('updating structure for table %s ...', $table), LOGGER_DEBUG);
+			$database[$table] = table_structure($table);
+		}
 	}
 
 	// Get the definition
 	if (is_null($definition)) {
-		$definition = db_definition($charset);
+		$definition = db_definition();
 	}
 
 	// MySQL >= 5.7.4 doesn't support the IGNORE keyword in ALTER TABLE statements
@@ -194,9 +220,9 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 		$group_by = "";
 		$sql3 = "";
 		if (!isset($database[$name])) {
-			$r = db_create_table($name, $structure["fields"], $charset, $verbose, $action, $structure['indexes']);
+			$r = db_create_table($name, $structure["fields"], $verbose, $action, $structure['indexes']);
 			if (!dbm::is_result($r)) {
-				$errors .=  t('Errors encountered creating database tables.').$name.EOL;
+				$errors .= print_update_error($db, $name);
 			}
 			$is_new_table = True;
 		} else {
@@ -252,17 +278,25 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 					}
 				} else {
 					// Compare the field definition
-					$current_field_definition = implode(",",$database[$name]["fields"][$fieldname]);
-					$new_field_definition = implode(",",$parameters);
+					$field_definition = $database[$name]["fields"][$fieldname];
+
+					// Define the default collation if not given
+					if (!isset($parameters['Collation']) AND !is_null($field_definition['Collation'])) {
+						$parameters['Collation'] = 'utf8mb4_general_ci';
+					} else {
+						$parameters['Collation'] = null;
+					}
+
+					$current_field_definition = implode(",", $field_definition);
+					$new_field_definition = implode(",", $parameters);
 					if ($current_field_definition != $new_field_definition) {
-						$sql2=db_modify_table_field($fieldname, $parameters);
+						$sql2 = db_modify_table_field($fieldname, $parameters);
 						if ($sql3 == "") {
 							$sql3 = "ALTER" . $ignore . " TABLE `".$temp_name."` ".$sql2;
 						} else {
 							$sql3 .= ", ".$sql2;
 						}
 					}
-
 				}
 			}
 		}
@@ -288,10 +322,23 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 						$group_by = db_group_by($indexname, $fieldnames);
 					}
 					if ($sql2 != "") {
-						if ($sql3 == "")
+						if ($sql3 == "") {
 							$sql3 = "ALTER" . $ignore . " TABLE `".$temp_name."` ".$sql2;
-						else
+						} else {
 							$sql3 .= ", ".$sql2;
+						}
+					}
+				}
+			}
+
+			if (isset($database[$name]["table_status"]["Collation"])) {
+				if ($database[$name]["table_status"]["Collation"] != 'utf8mb4_general_ci') {
+					$sql2 = "DEFAULT COLLATE utf8mb4_general_ci";
+
+					if ($sql3 == "") {
+						$sql3 = "ALTER" . $ignore . " TABLE `".$temp_name."` ".$sql2;
+					} else {
+						$sql3 .= ", ".$sql2;
 					}
 				}
 			}
@@ -323,6 +370,8 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 			}
 
 			if ($action) {
+				Config::set('system', 'maintenance_reason', sprintf(t('%s: updating %s table.'), dbm::date().' '.date('e'), $name));
+
 				// Ensure index conversion to unique removes duplicates
 				if ($is_unique) {
 					if ($ignore != "") {
@@ -330,33 +379,33 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 					} else {
 						$r = $db->q("CREATE TABLE `".$temp_name."` LIKE `".$name."`;");
 						if (!dbm::is_result($r)) {
-							$errors .= t('Errors encountered performing database changes.').$sql3.EOL;
+							$errors .= print_update_error($db, $sql3);
 							return $errors;
 						}
 					}
 				}
 
 				$r = @$db->q($sql3);
-				if (!dbm::is_result($r))
-					$errors .= t('Errors encountered performing database changes.').$sql3.EOL;
-
+				if (!dbm::is_result($r)) {
+					$errors .= print_update_error($db, $sql3);
+				}
 				if ($is_unique) {
 					if ($ignore != "") {
 						$db->q("SET session old_alter_table=0;");
 					} else {
 						$r = $db->q("INSERT INTO `".$temp_name."` SELECT * FROM `".$name."`".$group_by.";");
 						if (!dbm::is_result($r)) {
-							$errors .= t('Errors encountered performing database changes.').$sql3.EOL;
+							$errors .= print_update_error($db, $sql3);
 							return $errors;
 						}
 						$r = $db->q("DROP TABLE `".$name."`;");
 						if (!dbm::is_result($r)) {
-							$errors .= t('Errors encountered performing database changes.').$sql3.EOL;
+							$errors .= print_update_error($db, $sql3);
 							return $errors;
 						}
 						$r = $db->q("RENAME TABLE `".$temp_name."` TO `".$name."`;");
 						if (!dbm::is_result($r)) {
-							$errors .= t('Errors encountered performing database changes.').$sql3.EOL;
+							$errors .= print_update_error($db, $sql3);
 							return $errors;
 						}
 					}
@@ -376,6 +425,10 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 function db_field_command($parameters, $create = true) {
 	$fieldstruct = $parameters["type"];
 
+	if (!is_null($parameters["Collation"])) {
+		$fieldstruct .= " COLLATE ".$parameters["Collation"];
+	}
+
 	if ($parameters["not null"])
 		$fieldstruct .= " NOT NULL";
 
@@ -395,7 +448,7 @@ function db_field_command($parameters, $create = true) {
 	return($fieldstruct);
 }
 
-function db_create_table($name, $fields, $charset, $verbose, $action, $indexes=null) {
+function db_create_table($name, $fields, $verbose, $action, $indexes=null) {
 	global $a, $db;
 
 	$r = true;
@@ -420,7 +473,7 @@ function db_create_table($name, $fields, $charset, $verbose, $action, $indexes=n
 
 	$sql = implode(",\n\t", $sql_rows);
 
-	$sql = sprintf("CREATE TABLE IF NOT EXISTS `%s` (\n\t", dbesc($name)).$sql."\n) DEFAULT CHARSET=".$charset;
+	$sql = sprintf("CREATE TABLE IF NOT EXISTS `%s` (\n\t", dbesc($name)).$sql."\n) DEFAULT COLLATE utf8mb4_general_ci";
 	if ($verbose)
 		echo $sql.";\n";
 
@@ -503,18 +556,7 @@ function db_group_by($indexname, $fieldnames) {
 	return $sql;
 }
 
-function db_index_suffix($charset, $reduce = 0) {
-	if ($charset != "utf8mb4") {
-		return "";
-	}
-
-	// On utf8mb4 indexes can only have a length of 191
-	$indexlength = 191 - $reduce;
-
-	return "(".$indexlength.")";
-}
-
-function db_definition($charset) {
+function db_definition() {
 
 	$database = array();
 
@@ -681,7 +723,7 @@ function db_definition($charset) {
 					"info" => array("type" => "mediumtext"),
 					"profile-id" => array("type" => "int(11)", "not null" => "1", "default" => "0"),
 					"bdyear" => array("type" => "varchar(4)", "not null" => "1", "default" => ""),
-					"bd" => array("type" => "date", "not null" => "1", "default" => "0000-00-00"),
+					"bd" => array("type" => "date", "not null" => "1", "default" => "0001-01-01"),
 					"notify_new_posts" => array("type" => "tinyint(1)", "not null" => "1", "default" => "0"),
 					"fetch_further_information" => array("type" => "tinyint(1)", "not null" => "1", "default" => "0"),
 					"ffi_keyword_blacklist" => array("type" => "text"),
@@ -840,7 +882,7 @@ function db_definition($charset) {
 					"about" => array("type" => "text"),
 					"keywords" => array("type" => "text"),
 					"gender" => array("type" => "varchar(32)", "not null" => "1", "default" => ""),
-					"birthday" => array("type" => "varchar(32)", "not null" => "1", "default" => "0000-00-00"),
+					"birthday" => array("type" => "varchar(32)", "not null" => "1", "default" => "0001-01-01"),
 					"community" => array("type" => "tinyint(1)", "not null" => "1", "default" => "0"),
 					"contact-type" => array("type" => "tinyint(1)", "not null" => "1", "default" => "-1"),
 					"hide" => array("type" => "tinyint(1)", "not null" => "1", "default" => "0"),
@@ -1304,7 +1346,7 @@ function db_definition($charset) {
 					"hide-friends" => array("type" => "tinyint(1)", "not null" => "1", "default" => "0"),
 					"name" => array("type" => "varchar(255)", "not null" => "1", "default" => ""),
 					"pdesc" => array("type" => "varchar(255)", "not null" => "1", "default" => ""),
-					"dob" => array("type" => "varchar(32)", "not null" => "1", "default" => "0000-00-00"),
+					"dob" => array("type" => "varchar(32)", "not null" => "1", "default" => "0001-01-01"),
 					"address" => array("type" => "varchar(255)", "not null" => "1", "default" => ""),
 					"locality" => array("type" => "varchar(255)", "not null" => "1", "default" => ""),
 					"region" => array("type" => "varchar(255)", "not null" => "1", "default" => ""),
@@ -1658,9 +1700,7 @@ function dbstructure_run(&$argv, &$argc) {
 				set_config('system','build',DB_UPDATE_VERSION);
 				return;
 			case "dumpsql":
-				// For the dump that is used to create the database.sql we always assume utfmb4
-				$charset = "utf8mb4";
-				print_structure(db_definition($charset), $charset);
+				print_structure(db_definition());
 				return;
 		}
 	}