From fd2d8cc180dba428170b51d46bd9d1d301e101f9 Mon Sep 17 00:00:00 2001
From: Michael Vogel <ike@pirati.ca>
Date: Thu, 20 Oct 2016 22:05:21 +0000
Subject: [PATCH] Orphans are removed without giant lock problems

---
 include/dba.php         | 19 +++++++++++++++++++
 include/dbclean.php     | 36 +++++++++++++++++++++++++++++++-----
 include/dbstructure.php |  1 +
 mod/display.php         |  2 +-
 4 files changed, 52 insertions(+), 6 deletions(-)

diff --git a/include/dba.php b/include/dba.php
index a9ed9e5a05..c7b598f2d6 100644
--- a/include/dba.php
+++ b/include/dba.php
@@ -108,6 +108,23 @@ class dba {
 		return $return;
 	}
 
+	/**
+	 * @brief Returns the number of rows
+	 *
+	 * @return string
+	 */
+	public function num_rows() {
+		if (!$this->result)
+			return 0;
+
+		if ($this->mysqli) {
+			$return = $this->result->num_rows;
+		} else {
+			$return = mysql_num_rows($this->result);
+		}
+		return $return;
+	}
+
 	public function q($sql, $onlyquery = false) {
 		global $a;
 
@@ -126,6 +143,8 @@ class dba {
 
 		$stamp1 = microtime(true);
 
+		$sql = "/*".$a->callstack()." */ ".$sql;
+
 		if($this->mysqli)
 			$result = @$this->db->query($sql);
 		else
diff --git a/include/dbclean.php b/include/dbclean.php
index 8ec9c1dc4a..860d0f2202 100644
--- a/include/dbclean.php
+++ b/include/dbclean.php
@@ -16,22 +16,48 @@ if(is_null($db)) {
 load_config('config');
 load_config('system');
 
-update_shadow_copy();
+remove_orphans();
 killme();
 
 function remove_orphans() {
+	 global $db;
 
 	logger("Deleting orphaned data from thread table");
-	q("DELETE FROM `thread` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `thread`.`iid`)");
+	if ($db->q("SELECT `iid` FROM `thread` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `thread`.`iid`)", true)) {
+		logger("found thread orphans: ".$db->num_rows());
+		while ($orphan = $db->qfetch())
+			q("DELETE FROM `thread` WHERE `iid` = %d", intval($orphan["iid"]));
+	}
+	$db->qclose();
+
 
 	logger("Deleting orphaned data from notify table");
-	q("DELETE FROM `notify` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `notify`.`iid`)");
+	if ($db->q("SELECT `iid` FROM `notify` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `notify`.`iid`)", true)) {
+		logger("found notify orphans: ".$db->num_rows());
+		while ($orphan = $db->qfetch())
+			q("DELETE FROM `notify` WHERE `iid` = %d", intval($orphan["iid"]));
+	}
+	$db->qclose();
+
 
 	logger("Deleting orphaned data from sign table");
-	q("DELETE FROM `sign` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `sign`.`iid`)");
+	if ($db->q("SELECT `iid` FROM `sign` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `sign`.`iid`)", true)) {
+		logger("found sign orphans: ".$db->num_rows());
+		while ($orphan = $db->qfetch())
+			q("DELETE FROM `sign` WHERE `iid` = %d", intval($orphan["iid"]));
+	}
+	$db->qclose();
+
 
 	logger("Deleting orphaned data from term table");
-	q("DELETE FROM `term` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `term`.`oid`)");
+	if ($db->q("SELECT `oid` FROM `term` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `term`.`oid`)", true)) {
+		logger("found term orphans: ".$db->num_rows());
+		while ($orphan = $db->qfetch())
+			q("DELETE FROM `term` WHERE `oid` = %d", intval($orphan["oid"]));
+	}
+	$db->qclose();
+
+// SELECT `id`, `received`, `created`, `guid` FROM `item` WHERE `uid` = 0 AND NOT EXISTS (SELECT `guid` FROM `item` AS `i` WHERE `item`.`guid` = `i`.`guid` AND `i`.`uid` != 0) LIMIT 1;
 
 	logger("Done deleting orphaned data from tables");
 }
diff --git a/include/dbstructure.php b/include/dbstructure.php
index 2aefdf45c1..f3c8947408 100644
--- a/include/dbstructure.php
+++ b/include/dbstructure.php
@@ -1121,6 +1121,7 @@ function db_definition($charset) {
 			"indexes" => array(
 					"PRIMARY" => array("id"),
 					"uid_contactid" => array("uid", "contact-id"),
+					"uid_album_created" => array("uid", "album", "created"),
 					"resource-id" => array("resource-id"),
 					"guid" => array("guid"),
 					)
diff --git a/mod/display.php b/mod/display.php
index b21c2810ca..0e0e7b3031 100644
--- a/mod/display.php
+++ b/mod/display.php
@@ -236,7 +236,7 @@ function display_content(&$a, $update = 0) {
 			}
 
 			if ($nick == "") {
-				$r = qu("SELECT `user`.`nickname`, `item`.`id` FROM `item` INNER JOIN `user` ON `user`.`uid` = `item`.`uid`
+				$r = qu("SELECT STRAIGHT_JOIN `user`.`nickname`, `item`.`id` FROM `item` INNER JOIN `user` ON `user`.`uid` = `item`.`uid`
 					WHERE `item`.`visible` AND NOT `item`.`deleted` AND NOT `item`.`moderated`
 						AND `item`.`allow_cid` = ''  AND `item`.`allow_gid` = ''
 						AND `item`.`deny_cid`  = '' AND `item`.`deny_gid`  = ''
-- 
2.39.5