3 * @file include/dbclean.php
4 * @brief The script is called from time to time to clean the database entries and remove orphaned data.
7 use \Friendica\Core\Config;
9 function dbclean_run(&$argv, &$argc) {
10 if (!Config::get('system', 'dbclean', false)) {
15 $stage = intval($argv[1]);
21 proc_run(PRIORITY_LOW, 'include/dbclean.php', 1);
22 proc_run(PRIORITY_LOW, 'include/dbclean.php', 2);
23 proc_run(PRIORITY_LOW, 'include/dbclean.php', 3);
24 proc_run(PRIORITY_LOW, 'include/dbclean.php', 4);
25 proc_run(PRIORITY_LOW, 'include/dbclean.php', 5);
26 proc_run(PRIORITY_LOW, 'include/dbclean.php', 6);
27 proc_run(PRIORITY_LOW, 'include/dbclean.php', 7);
29 remove_orphans($stage);
34 * @brief Remove orphaned database entries
36 function remove_orphans($stage = 0) {
41 // We split the deletion in many small tasks
44 if (($stage == 1) OR ($stage == 0)) {
45 logger("Deleting old global item entries from item table without user copy");
46 $r = dba::p("SELECT `id` FROM `item` WHERE `uid` = 0
47 AND NOT EXISTS (SELECT `guid` FROM `item` AS `i` WHERE `item`.`guid` = `i`.`guid` AND `i`.`uid` != 0)
48 AND `received` < UTC_TIMESTAMP() - INTERVAL 90 DAY LIMIT ".intval($limit));
49 $count = dba::num_rows($r);
51 logger("found global item orphans: ".$count);
52 while ($orphan = dba::fetch($r)) {
53 q("DELETE FROM `item` WHERE `id` = %d", intval($orphan["id"]));
57 logger("Done deleting old global item entries from item table without user copy");
60 if (($stage == 2) OR ($stage == 0)) {
61 logger("Deleting items without parents");
62 $r = dba::p("SELECT `id` FROM `item` WHERE NOT EXISTS (SELECT `id` FROM `item` AS `i` WHERE `item`.`parent` = `i`.`id`) LIMIT ".intval($limit));
63 $count = dba::num_rows($r);
65 logger("found item orphans without parents: ".$count);
66 while ($orphan = dba::fetch($r)) {
67 q("DELETE FROM `item` WHERE `id` = %d", intval($orphan["id"]));
71 logger("Done deleting items without parents");
74 if (($stage == 3) OR ($stage == 0)) {
75 logger("Deleting orphaned data from thread table");
76 $r = dba::p("SELECT `iid` FROM `thread` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `thread`.`iid`) LIMIT ".intval($limit));
77 $count = dba::num_rows($r);
79 logger("found thread orphans: ".$count);
80 while ($orphan = dba::fetch($r)) {
81 q("DELETE FROM `thread` WHERE `iid` = %d", intval($orphan["iid"]));
85 logger("Done deleting orphaned data from thread table");
88 if (($stage == 4) OR ($stage == 0)) {
89 logger("Deleting orphaned data from notify table");
90 $r = dba::p("SELECT `iid` FROM `notify` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `notify`.`iid`) LIMIT ".intval($limit));
91 $count = dba::num_rows($r);
93 logger("found notify orphans: ".$count);
94 while ($orphan = dba::fetch($r)) {
95 q("DELETE FROM `notify` WHERE `iid` = %d", intval($orphan["iid"]));
99 logger("Done deleting orphaned data from notify table");
102 if (($stage == 5) OR ($stage == 0)) {
103 logger("Deleting orphaned data from notify-threads table");
104 $r = dba::p("SELECT `id` FROM `notify-threads` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `notify-threads`.`master-parent-item`) LIMIT ".intval($limit));
105 $count = dba::num_rows($r);
107 logger("found notify-threads orphans: ".$count);
108 while ($orphan = dba::fetch($r)) {
109 q("DELETE FROM `notify-threads` WHERE `id` = %d", intval($orphan["id"]));
113 logger("Done deleting orphaned data from notify-threads table");
117 if (($stage == 6) OR ($stage == 0)) {
118 logger("Deleting orphaned data from sign table");
119 $r = dba::p("SELECT `iid` FROM `sign` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `sign`.`iid`) LIMIT ".intval($limit));
120 $count = dba::num_rows($r);
122 logger("found sign orphans: ".$count);
123 while ($orphan = dba::fetch($r)) {
124 q("DELETE FROM `sign` WHERE `iid` = %d", intval($orphan["iid"]));
128 logger("Done deleting orphaned data from sign table");
132 if (($stage == 7) OR ($stage == 0)) {
133 logger("Deleting orphaned data from term table");
134 $r = dba::p("SELECT `oid` FROM `term` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `term`.`oid`) LIMIT ".intval($limit));
135 $count = dba::num_rows($r);
137 logger("found term orphans: ".$count);
138 while ($orphan = dba::fetch($r)) {
139 q("DELETE FROM `term` WHERE `oid` = %d", intval($orphan["oid"]));
143 logger("Done deleting orphaned data from term table");
146 // Call it again if not all entries were purged
147 if (($stage != 0) AND ($count > 0)) {
148 proc_run(PRIORITY_MEDIUM, 'include/dbclean.php');