X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;ds=sidebyside;f=include%2Fdbstructure.php;h=f8d084437b16ed18342855b693a3dcc15573a585;hb=6dc07514db11dff20517dec9d385edf5eba3355f;hp=551f254a536beec833e3b9e0c15ec05663a226fb;hpb=e16afc0450c812b76a79c4fb71fcf8e5260bca0b;p=friendica.git

diff --git a/include/dbstructure.php b/include/dbstructure.php
index 551f254a53..f8d084437b 100644
--- a/include/dbstructure.php
+++ b/include/dbstructure.php
@@ -176,15 +176,6 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 		$definition = db_definition($charset);
 	}
 
-	// Ensure index conversion to unique removes duplicates
-	$sql_config = "SET session old_alter_table=1;";
-	if ($verbose) {
-		echo $sql_config."\n";
-	}
-	if ($action) {
-		$db->q($sql_config);
-	}
-
 	// 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
 		!(strpos($db->server_info(), 'MariaDB') !== false)) {
@@ -196,7 +187,8 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 	// Compare it
 	foreach ($definition AS $name => $structure) {
 		$is_new_table = False;
-		$sql3="";
+		$group_by = "";
+		$sql3 = "";
 		if (!isset($database[$name])) {
 			$r = db_create_table($name, $structure["fields"], $charset, $verbose, $action, $structure['indexes']);
 			if (!dbm::is_result($r)) {
@@ -204,6 +196,26 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 			}
 			$is_new_table = True;
 		} else {
+			$is_unique = false;
+			$temp_name = $name;
+
+			foreach ($structure["indexes"] AS $indexname => $fieldnames) {
+				if (isset($database[$name]["indexes"][$indexname])) {
+					$current_index_definition = implode(",",$database[$name]["indexes"][$indexname]);
+				} else {
+					$current_index_definition = "__NOT_SET__";
+				}
+				$new_index_definition = implode(",",$fieldnames);
+				if ($current_index_definition != $new_index_definition) {
+					if ($fieldnames[0] == "UNIQUE") {
+						$is_unique = true;
+						if ($ignore == "") {
+							$temp_name = "temp-".$name;
+						}
+					}
+				}
+			}
+
 			/*
 			 * Drop the index if it isn't present in the definition
 			 * or the definition differ from current status
@@ -219,7 +231,7 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 				if ($current_index_definition != $new_index_definition && substr($indexname, 0, 6) != 'local_') {
 					$sql2=db_drop_index($indexname);
 					if ($sql3 == "") {
-						$sql3 = "ALTER".$ignore." TABLE `".$name."` ".$sql2;
+						$sql3 = "ALTER".$ignore." TABLE `".$temp_name."` ".$sql2;
 					} else {
 						$sql3 .= ", ".$sql2;
 					}
@@ -230,7 +242,7 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 				if (!isset($database[$name]["fields"][$fieldname])) {
 					$sql2=db_add_table_field($fieldname, $parameters);
 					if ($sql3 == "") {
-						$sql3 = "ALTER TABLE `".$name."` ".$sql2;
+						$sql3 = "ALTER TABLE `".$temp_name."` ".$sql2;
 					} else {
 						$sql3 .= ", ".$sql2;
 					}
@@ -241,7 +253,7 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 					if ($current_field_definition != $new_field_definition) {
 						$sql2=db_modify_table_field($fieldname, $parameters);
 						if ($sql3 == "") {
-							$sql3 = "ALTER TABLE `".$name."` ".$sql2;
+							$sql3 = "ALTER TABLE `".$temp_name."` ".$sql2;
 						} else {
 							$sql3 .= ", ".$sql2;
 						}
@@ -265,10 +277,15 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 				}
 				$new_index_definition = implode(",",$fieldnames);
 				if ($current_index_definition != $new_index_definition) {
-					$sql2=db_create_index($indexname, $fieldnames);
+					$sql2 = db_create_index($indexname, $fieldnames);
+
+					// Fetch the "group by" fields for unique indexes
+					if ($fieldnames[0] == "UNIQUE") {
+						$group_by = db_group_by($indexname, $fieldnames);
+					}
 					if ($sql2 != "") {
 						if ($sql3 == "")
-							$sql3 = "ALTER" . $ignore . " TABLE `".$name."` ".$sql2;
+							$sql3 = "ALTER" . $ignore . " TABLE `".$temp_name."` ".$sql2;
 						else
 							$sql3 .= ", ".$sql2;
 					}
@@ -278,13 +295,68 @@ function update_structure($verbose, $action, $tables=null, $definition=null) {
 		if ($sql3 != "") {
 			$sql3 .= ";";
 
-			if ($verbose)
+			if ($verbose) {
+				// Ensure index conversion to unique removes duplicates
+				if ($is_unique) {
+					if ($ignore != "") {
+						echo "SET session old_alter_table=1;\n";
+					} else {
+						echo "CREATE TABLE `".$temp_name."` LIKE `".$name."`;\n";
+					}
+				}
+
 				echo $sql3."\n";
 
+				if ($is_unique) {
+					if ($ignore != "") {
+						echo "SET session old_alter_table=0;\n";
+					} else {
+						echo "INSERT INTO `".$temp_name."` SELECT * FROM `".$name."`".$group_by.";\n";
+						echo "DROP TABLE `".$name."`;\n";
+						echo "RENAME TABLE `".$temp_name."` TO `".$name."`;\n";
+					}
+				}
+			}
+
 			if ($action) {
+				// Ensure index conversion to unique removes duplicates
+				if ($is_unique) {
+					if ($ignore != "") {
+						$db->q("SET session old_alter_table=1;");
+					} else {
+						$r = $db->q("CREATE TABLE `".$temp_name."` LIKE `".$name."`;");
+						if (!dbm::is_result($r)) {
+							$errors .= t('Errors encountered performing database changes.').$sql3.EOL;
+							return $errors;
+						}
+					}
+				}
+
 				$r = @$db->q($sql3);
-				if (dbm::is_result($r))
+				if (!dbm::is_result($r))
 					$errors .= t('Errors encountered performing database changes.').$sql3.EOL;
+
+				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;
+							return $errors;
+						}
+						$r = $db->q("DROP TABLE `".$name."`;");
+						if (!dbm::is_result($r)) {
+							$errors .= t('Errors encountered performing database changes.').$sql3.EOL;
+							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;
+							return $errors;
+						}
+					}
+				}
 			}
 		}
 	}
@@ -401,6 +473,30 @@ function db_create_index($indexname, $fieldnames, $method="ADD") {
 	return($sql);
 }
 
+function db_group_by($indexname, $fieldnames) {
+
+	if ($fieldnames[0] != "UNIQUE") {
+		return "";
+	}
+
+	array_shift($fieldnames);
+
+	$names = "";
+	foreach ($fieldnames AS $fieldname) {
+		if ($names != "")
+			$names .= ",";
+
+		if (preg_match('|(.+)\((\d+)\)|', $fieldname, $matches)) {
+			$names .= "`".dbesc($matches[1])."`";
+		} else {
+			$names .= "`".dbesc($fieldname)."`";
+		}
+	}
+
+	$sql = sprintf(" GROUP BY %s", $names);
+	return $sql;
+}
+
 function db_index_suffix($charset, $reduce = 0) {
 	if ($charset != "utf8mb4") {
 		return "";
@@ -419,7 +515,7 @@ function db_definition($charset) {
 	$database["addon"] = array(
 			"fields" => array(
 					"id" => array("type" => "int(11)", "not null" => "1", "extra" => "auto_increment", "primary" => "1"),
-					"name" => array("type" => "varchar(255)", "not null" => "1", "default" => ""),
+					"name" => array("type" => "varchar(190)", "not null" => "1", "default" => ""),
 					"version" => array("type" => "varchar(255)", "not null" => "1", "default" => ""),
 					"installed" => array("type" => "tinyint(1)", "not null" => "1", "default" => "0"),
 					"hidden" => array("type" => "tinyint(1)", "not null" => "1", "default" => "0"),
@@ -428,6 +524,7 @@ function db_definition($charset) {
 					),
 			"indexes" => array(
 					"PRIMARY" => array("id"),
+					"name" => array("UNIQUE", "name"),
 					)
 			);
 	$database["attach"] = array(
@@ -441,10 +538,10 @@ function db_definition($charset) {
 					"data" => array("type" => "longblob", "not null" => "1"),
 					"created" => array("type" => "datetime", "not null" => "1", "default" => "0000-00-00 00:00:00"),
 					"edited" => array("type" => "datetime", "not null" => "1", "default" => "0000-00-00 00:00:00"),
-					"allow_cid" => array("type" => "text"),
-					"allow_gid" => array("type" => "text"),
-					"deny_cid" => array("type" => "text"),
-					"deny_gid" => array("type" => "text"),
+					"allow_cid" => array("type" => "mediumtext"),
+					"allow_gid" => array("type" => "mediumtext"),
+					"deny_cid" => array("type" => "mediumtext"),
+					"deny_gid" => array("type" => "mediumtext"),
 					),
 			"indexes" => array(
 					"PRIMARY" => array("id"),
@@ -645,10 +742,10 @@ function db_definition($charset) {
 					"nofinish" => array("type" => "tinyint(1)", "not null" => "1", "default" => "0"),
 					"adjust" => array("type" => "tinyint(1)", "not null" => "1", "default" => "1"),
 					"ignore" => array("type" => "tinyint(1) unsigned", "not null" => "1", "default" => "0"),
-					"allow_cid" => array("type" => "text"),
-					"allow_gid" => array("type" => "text"),
-					"deny_cid" => array("type" => "text"),
-					"deny_gid" => array("type" => "text"),
+					"allow_cid" => array("type" => "mediumtext"),
+					"allow_gid" => array("type" => "mediumtext"),
+					"deny_cid" => array("type" => "mediumtext"),
+					"deny_gid" => array("type" => "mediumtext"),
 					),
 			"indexes" => array(
 					"PRIMARY" => array("id"),
@@ -763,10 +860,10 @@ function db_definition($charset) {
 					),
 			"indexes" => array(
 					"PRIMARY" => array("id"),
-					"nurl" => array("nurl(32)"),
-					"name" => array("name(32)"),
+					"nurl" => array("nurl(64)"),
+					"name" => array("name(64)"),
 					"nick" => array("nick(32)"),
-					"addr" => array("addr(32)"),
+					"addr" => array("addr(64)"),
 					"hide_network_updated" => array("hide", "network", "updated"),
 					"updated" => array("updated"),
 					)
@@ -846,7 +943,7 @@ function db_definition($charset) {
 					),
 			"indexes" => array(
 					"PRIMARY" => array("id"),
-					"hook_file_function" => array("hook(30)","file(60)","function(30)"),
+					"hook_file_function" => array("UNIQUE", "hook(50)","file(80)","function(60)"),
 					)
 			);
 	$database["intro"] = array(
@@ -943,6 +1040,7 @@ function db_definition($charset) {
 					"parent-uri" => array("parent-uri"),
 					"extid" => array("extid"),
 					"uid_id" => array("uid","id"),
+					"uid_contactid_id" => array("uid","contact-id","id"),
 					"uid_created" => array("uid","created"),
 					"uid_unseen_contactid" => array("uid","unseen","contact-id"),
 					"uid_network_received" => array("uid","network","received"),
@@ -958,7 +1056,6 @@ function db_definition($charset) {
 					"contactid_verb" => array("contact-id","verb"),
 					"deleted_changed" => array("deleted","changed"),
 					"uid_wall_changed" => array("uid","wall","changed"),
-					"wall_uid_changed" => array("wall","uid","changed"),
 					"uid_eventid" => array("uid","event-id"),
 					"uid_authorlink" => array("uid","author-link"),
 					"uid_ownerlink" => array("uid","owner-link"),
@@ -1484,10 +1581,10 @@ function db_definition($charset) {
 					"expire_notification_sent" => array("type" => "datetime", "not null" => "1", "default" => "0000-00-00 00:00:00"),
 					"service_class" => array("type" => "varchar(32)", "not null" => "1", "default" => ""),
 					"def_gid" => array("type" => "int(11)", "not null" => "1", "default" => "0"),
-					"allow_cid" => array("type" => "text"),
-					"allow_gid" => array("type" => "text"),
-					"deny_cid" => array("type" => "text"),
-					"deny_gid" => array("type" => "text"),
+					"allow_cid" => array("type" => "mediumtext"),
+					"allow_gid" => array("type" => "mediumtext"),
+					"deny_cid" => array("type" => "mediumtext"),
+					"deny_gid" => array("type" => "mediumtext"),
 					"openidserver" => array("type" => "text"),
 					),
 			"indexes" => array(