3 * @file src/Worker/DBClean.php
4 * @brief The script is called from time to time to clean the database entries and remove orphaned data.
7 namespace Friendica\Worker;
9 use Friendica\Core\Config;
10 use Friendica\Core\Worker;
14 public static function execute($stage = 0) {
16 if (!Config::get('system', 'dbclean', false)) {
20 // Get the expire days for step 8 and 9
21 $days = Config::get('system', 'dbclean-expire-days', 0);
24 self::forkCleanProcess();
26 self::removeOrphans($stage);
31 * @brief Fork the different DBClean processes
33 private static function forkCleanProcess() {
34 for ($i = 1; $i <= 10; $i++) {
35 // Execute the background script for a step when it isn't finished.
36 // Execute step 8 and 9 only when $days is defined.
37 if (!Config::get('system', 'finished-dbclean-'.$i, false) && (($i < 8) || ($i > 9) || ($days > 0))) {
38 Worker::add(PRIORITY_LOW, 'DBClean', $i);
44 * @brief Remove orphaned database entries
45 * @param integer $stage What should be deleted?
49 * 1: Old global item entries from item table without user copy.
50 * 2: Items without parents.
51 * 3: Orphaned data from thread table.
52 * 4: Orphaned data from notify table.
53 * 5: Orphaned data from notify-threads table.
54 * 6: Orphaned data from sign table.
55 * 7: Orphaned data from term table.
57 * 9: Old global item entries from expired threads.
58 * 10: Old conversations.
60 private static function removeOrphans($stage) {
65 // We split the deletion in many small tasks
68 // Get the expire days for step 8 and 9
69 $days = Config::get('system', 'dbclean-expire-days', 0);
72 $last_id = Config::get('system', 'dbclean-last-id-1', 0);
74 logger("Deleting old global item entries from item table without user copy. Last ID: ".$last_id);
75 $r = dba::p("SELECT `id` FROM `item` WHERE `uid` = 0 AND
76 NOT EXISTS (SELECT `guid` FROM `item` AS `i` WHERE `item`.`guid` = `i`.`guid` AND `i`.`uid` != 0) AND
77 `received` < UTC_TIMESTAMP() - INTERVAL 90 DAY AND `id` >= ?
78 ORDER BY `id` LIMIT ".intval($limit), $last_id);
79 $count = dba::num_rows($r);
81 logger("found global item orphans: ".$count);
82 while ($orphan = dba::fetch($r)) {
83 $last_id = $orphan["id"];
84 dba::delete('item', array('id' => $orphan["id"]));
86 Worker::add(PRIORITY_MEDIUM, 'DBClean', 1, $last_id);
88 logger("No global item orphans found");
91 logger("Done deleting ".$count." old global item entries from item table without user copy. Last ID: ".$last_id);
93 Config::set('system', 'dbclean-last-id-1', $last_id);
94 } elseif ($stage == 2) {
95 $last_id = Config::get('system', 'dbclean-last-id-2', 0);
97 logger("Deleting items without parents. Last ID: ".$last_id);
98 $r = dba::p("SELECT `id` FROM `item`
99 WHERE NOT EXISTS (SELECT `id` FROM `item` AS `i` WHERE `item`.`parent` = `i`.`id`)
100 AND `id` >= ? ORDER BY `id` LIMIT ".intval($limit), $last_id);
101 $count = dba::num_rows($r);
103 logger("found item orphans without parents: ".$count);
104 while ($orphan = dba::fetch($r)) {
105 $last_id = $orphan["id"];
106 dba::delete('item', array('id' => $orphan["id"]));
108 Worker::add(PRIORITY_MEDIUM, 'DBClean', 2, $last_id);
110 logger("No item orphans without parents found");
113 logger("Done deleting ".$count." items without parents. Last ID: ".$last_id);
115 Config::set('system', 'dbclean-last-id-2', $last_id);
117 if ($count < $limit) {
118 Config::set('system', 'finished-dbclean-2', true);
120 } elseif ($stage == 3) {
121 $last_id = Config::get('system', 'dbclean-last-id-3', 0);
123 logger("Deleting orphaned data from thread table. Last ID: ".$last_id);
124 $r = dba::p("SELECT `iid` FROM `thread`
125 WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `thread`.`iid`) AND `iid` >= ?
126 ORDER BY `iid` LIMIT ".intval($limit), $last_id);
127 $count = dba::num_rows($r);
129 logger("found thread orphans: ".$count);
130 while ($orphan = dba::fetch($r)) {
131 $last_id = $orphan["iid"];
132 dba::delete('thread', array('iid' => $orphan["iid"]));
134 Worker::add(PRIORITY_MEDIUM, 'DBClean', 3, $last_id);
136 logger("No thread orphans found");
139 logger("Done deleting ".$count." orphaned data from thread table. Last ID: ".$last_id);
141 Config::set('system', 'dbclean-last-id-3', $last_id);
143 if ($count < $limit) {
144 Config::set('system', 'finished-dbclean-3', true);
146 } elseif ($stage == 4) {
147 $last_id = Config::get('system', 'dbclean-last-id-4', 0);
149 logger("Deleting orphaned data from notify table. Last ID: ".$last_id);
150 $r = dba::p("SELECT `iid`, `id` FROM `notify`
151 WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `notify`.`iid`) AND `id` >= ?
152 ORDER BY `id` LIMIT ".intval($limit), $last_id);
153 $count = dba::num_rows($r);
155 logger("found notify orphans: ".$count);
156 while ($orphan = dba::fetch($r)) {
157 $last_id = $orphan["id"];
158 dba::delete('notify', array('iid' => $orphan["iid"]));
160 Worker::add(PRIORITY_MEDIUM, 'DBClean', 4, $last_id);
162 logger("No notify orphans found");
165 logger("Done deleting ".$count." orphaned data from notify table. Last ID: ".$last_id);
167 Config::set('system', 'dbclean-last-id-4', $last_id);
169 if ($count < $limit) {
170 Config::set('system', 'finished-dbclean-4', true);
172 } elseif ($stage == 5) {
173 $last_id = Config::get('system', 'dbclean-last-id-5', 0);
175 logger("Deleting orphaned data from notify-threads table. Last ID: ".$last_id);
176 $r = dba::p("SELECT `id` FROM `notify-threads`
177 WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `notify-threads`.`master-parent-item`) AND `id` >= ?
178 ORDER BY `id` LIMIT ".intval($limit), $last_id);
179 $count = dba::num_rows($r);
181 logger("found notify-threads orphans: ".$count);
182 while ($orphan = dba::fetch($r)) {
183 $last_id = $orphan["id"];
184 dba::delete('notify-threads', array('id' => $orphan["id"]));
186 Worker::add(PRIORITY_MEDIUM, 'DBClean', 5, $last_id);
188 logger("No notify-threads orphans found");
191 logger("Done deleting ".$count." orphaned data from notify-threads table. Last ID: ".$last_id);
193 Config::set('system', 'dbclean-last-id-5', $last_id);
195 if ($count < $limit) {
196 Config::set('system', 'finished-dbclean-5', true);
198 } elseif ($stage == 6) {
199 $last_id = Config::get('system', 'dbclean-last-id-6', 0);
201 logger("Deleting orphaned data from sign table. Last ID: ".$last_id);
202 $r = dba::p("SELECT `iid`, `id` FROM `sign`
203 WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `sign`.`iid`) AND `id` >= ?
204 ORDER BY `id` LIMIT ".intval($limit), $last_id);
205 $count = dba::num_rows($r);
207 logger("found sign orphans: ".$count);
208 while ($orphan = dba::fetch($r)) {
209 $last_id = $orphan["id"];
210 dba::delete('sign', array('iid' => $orphan["iid"]));
212 Worker::add(PRIORITY_MEDIUM, 'DBClean', 6, $last_id);
214 logger("No sign orphans found");
217 logger("Done deleting ".$count." orphaned data from sign table. Last ID: ".$last_id);
219 Config::set('system', 'dbclean-last-id-6', $last_id);
221 if ($count < $limit) {
222 Config::set('system', 'finished-dbclean-6', true);
224 } elseif ($stage == 7) {
225 $last_id = Config::get('system', 'dbclean-last-id-7', 0);
227 logger("Deleting orphaned data from term table. Last ID: ".$last_id);
228 $r = dba::p("SELECT `oid`, `tid` FROM `term`
229 WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `term`.`oid`) AND `tid` >= ?
230 ORDER BY `tid` LIMIT ".intval($limit), $last_id);
231 $count = dba::num_rows($r);
233 logger("found term orphans: ".$count);
234 while ($orphan = dba::fetch($r)) {
235 $last_id = $orphan["tid"];
236 dba::delete('term', array('oid' => $orphan["oid"]));
238 Worker::add(PRIORITY_MEDIUM, 'DBClean', 7, $last_id);
240 logger("No term orphans found");
243 logger("Done deleting ".$count." orphaned data from term table. Last ID: ".$last_id);
245 Config::set('system', 'dbclean-last-id-7', $last_id);
247 if ($count < $limit) {
248 Config::set('system', 'finished-dbclean-7', true);
250 } elseif ($stage == 8) {
255 $last_id = Config::get('system', 'dbclean-last-id-8', 0);
257 logger("Deleting expired threads. Last ID: ".$last_id);
258 $r = dba::p("SELECT `thread`.`iid` FROM `thread`
259 INNER JOIN `contact` ON `thread`.`contact-id` = `contact`.`id` AND NOT `notify_new_posts`
260 WHERE `thread`.`received` < UTC_TIMESTAMP() - INTERVAL ? DAY
261 AND NOT `thread`.`mention` AND NOT `thread`.`starred`
262 AND NOT `thread`.`wall` AND NOT `thread`.`origin`
263 AND `thread`.`uid` != 0 AND `thread`.`iid` >= ?
264 AND NOT `thread`.`iid` IN (SELECT `parent` FROM `item`
265 WHERE (`item`.`starred` OR (`item`.`resource-id` != '')
266 OR (`item`.`file` != '') OR (`item`.`event-id` != '')
267 OR (`item`.`attach` != '') OR `item`.`wall` OR `item`.`origin`)
268 AND `item`.`parent` = `thread`.`iid`)
269 ORDER BY `thread`.`iid` LIMIT 1000", $days, $last_id);
270 $count = dba::num_rows($r);
272 logger("found expired threads: ".$count);
273 while ($thread = dba::fetch($r)) {
274 $last_id = $thread["iid"];
275 dba::delete('thread', array('iid' => $thread["iid"]));
277 Worker::add(PRIORITY_MEDIUM, 'DBClean', 8, $last_id);
279 logger("No expired threads found");
282 logger("Done deleting ".$count." expired threads. Last ID: ".$last_id);
284 Config::set('system', 'dbclean-last-id-8', $last_id);
285 } elseif ($stage == 9) {
290 $last_id = Config::get('system', 'dbclean-last-id-9', 0);
291 $till_id = Config::get('system', 'dbclean-last-id-8', 0);
293 logger("Deleting old global item entries from expired threads from ID ".$last_id." to ID ".$till_id);
294 $r = dba::p("SELECT `id` FROM `item` WHERE `uid` = 0 AND
295 NOT EXISTS (SELECT `guid` FROM `item` AS `i` WHERE `item`.`guid` = `i`.`guid` AND `i`.`uid` != 0) AND
296 `received` < UTC_TIMESTAMP() - INTERVAL 90 DAY AND `id` >= ? AND `id` <= ?
297 ORDER BY `id` LIMIT ".intval($limit), $last_id, $till_id);
298 $count = dba::num_rows($r);
300 logger("found global item entries from expired threads: ".$count);
301 while ($orphan = dba::fetch($r)) {
302 $last_id = $orphan["id"];
303 dba::delete('item', array('id' => $orphan["id"]));
305 Worker::add(PRIORITY_MEDIUM, 'DBClean', 9, $last_id);
307 logger("No global item entries from expired threads");
310 logger("Done deleting ".$count." old global item entries from expired threads. Last ID: ".$last_id);
312 Config::set('system', 'dbclean-last-id-9', $last_id);
313 } elseif ($stage == 10) {
314 $last_id = Config::get('system', 'dbclean-last-id-10', 0);
316 logger("Deleting old conversations. Last created: ".$last_id);
317 $r = dba::p("SELECT `received`, `item-uri` FROM `conversation`
318 WHERE `received` < UTC_TIMESTAMP() - INTERVAL 90 DAY
319 ORDER BY `received` LIMIT ".intval($limit));
320 $count = dba::num_rows($r);
322 logger("found old conversations: ".$count);
323 while ($orphan = dba::fetch($r)) {
324 $last_id = $orphan["received"];
325 dba::delete('conversation', array('item-uri' => $orphan["item-uri"]));
327 Worker::add(PRIORITY_MEDIUM, 'DBClean', 10, $last_id);
329 logger("No old conversations found");
332 logger("Done deleting ".$count." conversations. Last created: ".$last_id);
334 Config::set('system', 'dbclean-last-id-10', $last_id);