src/Worker/DBClean.php

   1 <?php
   2 /**
   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.
   5  */
   6
   7 namespace Friendica\Worker;
   8
   9 use Friendica\Core\Config;
  10 use Friendica\Core\Worker;
  11 use dba;
  12
  13 class DBClean {
  14         public static function execute($stage = 0) {
  15
  16                 if (!Config::get('system', 'dbclean', false)) {
  17                         return;
  18                 }
  19
  20                 // Get the expire days for step 8 and 9
  21                 $days = Config::get('system', 'dbclean-expire-days', 0);
  22
  23                 if ($stage == 0) {
  24                         for ($i = 1; $i <= 9; $i++) {
  25                                 // Execute the background script for a step when it isn't finished.
  26                                 // Execute step 8 and 9 only when $days is defined.
  27                                 if (!Config::get('system', 'finished-dbclean-'.$i, false) && (($i < 8) || ($days > 0))) {
  28                                         Worker::add(PRIORITY_LOW, 'DBClean', $i);
  29                                 }
  30                         }
  31                 } else {
  32                         self::removeOrphans($stage);
  33                 }
  34         }
  35
  36         /**
  37          * @brief Remove orphaned database entries
  38          * @param integer $stage What should be deleted?
  39          *
  40          * Values for $stage:
  41          * ------------------
  42          * 1:   Old global item entries from item table without user copy.
  43          * 2:   Items without parents.
  44          * 3:   Orphaned data from thread table.
  45          * 4:   Orphaned data from notify table.
  46          * 5:   Orphaned data from notify-threads table.
  47          * 6:   Orphaned data from sign table.
  48          * 7:   Orphaned data from term table.
  49          * 8:   Expired threads.
  50          * 9:   Old global item entries from expired threads
  51          */
  52         private static function removeOrphans($stage = 0) {
  53                 global $db;
  54
  55                 $count = 0;
  56
  57                 // We split the deletion in many small tasks
  58                 $limit = 1000;
  59
  60                 // Get the expire days for step 8 and 9
  61                 $days = Config::get('system', 'dbclean-expire-days', 0);
  62
  63                 if ($stage == 1) {
  64                         $last_id = Config::get('system', 'dbclean-last-id-1', 0);
  65
  66                         logger("Deleting old global item entries from item table without user copy. Last ID: ".$last_id);
  67                         $r = dba::p("SELECT `id` FROM `item` WHERE `uid` = 0 AND
  68                                                 NOT EXISTS (SELECT `guid` FROM `item` AS `i` WHERE `item`.`guid` = `i`.`guid` AND `i`.`uid` != 0) AND
  69                                                 `received` < UTC_TIMESTAMP() - INTERVAL 90 DAY AND `id` >= ?
  70                                         ORDER BY `id` LIMIT ".intval($limit), $last_id);
  71                         $count = dba::num_rows($r);
  72                         if ($count > 0) {
  73                                 logger("found global item orphans: ".$count);
  74                                 while ($orphan = dba::fetch($r)) {
  75                                         $last_id = $orphan["id"];
  76                                         dba::delete('item', array('id' => $orphan["id"]));
  77                                 }
  78                         } else {
  79                                 logger("No global item orphans found");
  80                         }
  81                         dba::close($r);
  82                         logger("Done deleting ".$count." old global item entries from item table without user copy. Last ID: ".$last_id);
  83
  84                         Config::set('system', 'dbclean-last-id-1', $last_id);
  85                 } elseif ($stage == 2) {
  86                         $last_id = Config::get('system', 'dbclean-last-id-2', 0);
  87
  88                         logger("Deleting items without parents. Last ID: ".$last_id);
  89                         $r = dba::p("SELECT `id` FROM `item`
  90                                         WHERE NOT EXISTS (SELECT `id` FROM `item` AS `i` WHERE `item`.`parent` = `i`.`id`)
  91                                         AND `id` >= ? ORDER BY `id` LIMIT ".intval($limit), $last_id);
  92                         $count = dba::num_rows($r);
  93                         if ($count > 0) {
  94                                 logger("found item orphans without parents: ".$count);
  95                                 while ($orphan = dba::fetch($r)) {
  96                                         $last_id = $orphan["id"];
  97                                         dba::delete('item', array('id' => $orphan["id"]));
  98                                 }
  99                         } else {
 100                                 logger("No item orphans without parents found");
 101                         }
 102                         dba::close($r);
 103                         logger("Done deleting ".$count." items without parents. Last ID: ".$last_id);
 104
 105                         Config::set('system', 'dbclean-last-id-2', $last_id);
 106
 107                         if ($count < $limit) {
 108                                 Config::set('system', 'finished-dbclean-2', true);
 109                         }
 110                 } elseif ($stage == 3) {
 111                         $last_id = Config::get('system', 'dbclean-last-id-3', 0);
 112
 113                         logger("Deleting orphaned data from thread table. Last ID: ".$last_id);
 114                         $r = dba::p("SELECT `iid` FROM `thread`
 115                                         WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `thread`.`iid`) AND `iid` >= ?
 116                                         ORDER BY `iid` LIMIT ".intval($limit), $last_id);
 117                         $count = dba::num_rows($r);
 118                         if ($count > 0) {
 119                                 logger("found thread orphans: ".$count);
 120                                 while ($orphan = dba::fetch($r)) {
 121                                         $last_id = $orphan["iid"];
 122                                         dba::delete('thread', array('iid' => $orphan["iid"]));
 123                                 }
 124                         } else {
 125                                 logger("No thread orphans found");
 126                         }
 127                         dba::close($r);
 128                         logger("Done deleting ".$count." orphaned data from thread table. Last ID: ".$last_id);
 129
 130                         Config::set('system', 'dbclean-last-id-3', $last_id);
 131
 132                         if ($count < $limit) {
 133                                 Config::set('system', 'finished-dbclean-3', true);
 134                         }
 135                 } elseif ($stage == 4) {
 136                         $last_id = Config::get('system', 'dbclean-last-id-4', 0);
 137
 138                         logger("Deleting orphaned data from notify table. Last ID: ".$last_id);
 139                         $r = dba::p("SELECT `iid`, `id` FROM `notify`
 140                                         WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `notify`.`iid`) AND `id` >= ?
 141                                         ORDER BY `id` LIMIT ".intval($limit), $last_id);
 142                         $count = dba::num_rows($r);
 143                         if ($count > 0) {
 144                                 logger("found notify orphans: ".$count);
 145                                 while ($orphan = dba::fetch($r)) {
 146                                         $last_id = $orphan["id"];
 147                                         dba::delete('notify', array('iid' => $orphan["iid"]));
 148                                 }
 149                         } else {
 150                                 logger("No notify orphans found");
 151                         }
 152                         dba::close($r);
 153                         logger("Done deleting ".$count." orphaned data from notify table. Last ID: ".$last_id);
 154
 155                         Config::set('system', 'dbclean-last-id-4', $last_id);
 156
 157                         if ($count < $limit) {
 158                                 Config::set('system', 'finished-dbclean-4', true);
 159                         }
 160                 } elseif ($stage == 5) {
 161                         $last_id = Config::get('system', 'dbclean-last-id-5', 0);
 162
 163                         logger("Deleting orphaned data from notify-threads table. Last ID: ".$last_id);
 164                         $r = dba::p("SELECT `id` FROM `notify-threads`
 165                                         WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `notify-threads`.`master-parent-item`) AND `id` >= ?
 166                                         ORDER BY `id` LIMIT ".intval($limit), $last_id);
 167                         $count = dba::num_rows($r);
 168                         if ($count > 0) {
 169                                 logger("found notify-threads orphans: ".$count);
 170                                 while ($orphan = dba::fetch($r)) {
 171                                         $last_id = $orphan["id"];
 172                                         dba::delete('notify-threads', array('id' => $orphan["id"]));
 173                                 }
 174                         } else {
 175                                 logger("No notify-threads orphans found");
 176                         }
 177                         dba::close($r);
 178                         logger("Done deleting ".$count." orphaned data from notify-threads table. Last ID: ".$last_id);
 179
 180                         Config::set('system', 'dbclean-last-id-5', $last_id);
 181
 182                         if ($count < $limit) {
 183                                 Config::set('system', 'finished-dbclean-5', true);
 184                         }
 185                 } elseif ($stage == 6) {
 186                         $last_id = Config::get('system', 'dbclean-last-id-6', 0);
 187
 188                         logger("Deleting orphaned data from sign table. Last ID: ".$last_id);
 189                         $r = dba::p("SELECT `iid`, `id` FROM `sign`
 190                                         WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `sign`.`iid`) AND `id` >= ?
 191                                         ORDER BY `id` LIMIT ".intval($limit), $last_id);
 192                         $count = dba::num_rows($r);
 193                         if ($count > 0) {
 194                                 logger("found sign orphans: ".$count);
 195                                 while ($orphan = dba::fetch($r)) {
 196                                         $last_id = $orphan["id"];
 197                                         dba::delete('sign', array('iid' => $orphan["iid"]));
 198                                 }
 199                         } else {
 200                                 logger("No sign orphans found");
 201                         }
 202                         dba::close($r);
 203                         logger("Done deleting ".$count." orphaned data from sign table. Last ID: ".$last_id);
 204
 205                         Config::set('system', 'dbclean-last-id-6', $last_id);
 206
 207                         if ($count < $limit) {
 208                                 Config::set('system', 'finished-dbclean-6', true);
 209                         }
 210                 } elseif ($stage == 7) {
 211                         $last_id = Config::get('system', 'dbclean-last-id-7', 0);
 212
 213                         logger("Deleting orphaned data from term table. Last ID: ".$last_id);
 214                         $r = dba::p("SELECT `oid`, `tid` FROM `term`
 215                                         WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `term`.`oid`) AND `tid` >= ?
 216                                         ORDER BY `tid` LIMIT ".intval($limit), $last_id);
 217                         $count = dba::num_rows($r);
 218                         if ($count > 0) {
 219                                 logger("found term orphans: ".$count);
 220                                 while ($orphan = dba::fetch($r)) {
 221                                         $last_id = $orphan["tid"];
 222                                         dba::delete('term', array('oid' => $orphan["oid"]));
 223                                 }
 224                         } else {
 225                                 logger("No term orphans found");
 226                         }
 227                         dba::close($r);
 228                         logger("Done deleting ".$count." orphaned data from term table. Last ID: ".$last_id);
 229
 230                         Config::set('system', 'dbclean-last-id-7', $last_id);
 231
 232                         if ($count < $limit) {
 233                                 Config::set('system', 'finished-dbclean-7', true);
 234                         }
 235                 } elseif ($stage == 8) {
 236                         if ($days <= 0) {
 237                                 return;
 238                         }
 239
 240                         $last_id = Config::get('system', 'dbclean-last-id-8', 0);
 241
 242                         logger("Deleting expired threads. Last ID: ".$last_id);
 243                         $r = dba::p("SELECT `thread`.`iid` FROM `thread`
 244                                         INNER JOIN `contact` ON `thread`.`contact-id` = `contact`.`id` AND NOT `notify_new_posts`
 245                                         WHERE `thread`.`received` < UTC_TIMESTAMP() - INTERVAL ? DAY
 246                                                 AND NOT `thread`.`mention` AND NOT `thread`.`starred`
 247                                                 AND NOT `thread`.`wall` AND NOT `thread`.`origin`
 248                                                 AND `thread`.`uid` != 0 AND `thread`.`iid` >= ?
 249                                                 AND NOT `thread`.`iid` IN (SELECT `parent` FROM `item`
 250                                                                 WHERE (`item`.`starred` OR (`item`.`resource-id` != '')
 251                                                                         OR (`item`.`file` != '') OR (`item`.`event-id` != '')
 252                                                                         OR (`item`.`attach` != '') OR `item`.`wall` OR `item`.`origin`)
 253                                                                         AND `item`.`parent` = `thread`.`iid`)
 254                                         ORDER BY `thread`.`iid` LIMIT 1000", $days, $last_id);
 255                         $count = dba::num_rows($r);
 256                         if ($count > 0) {
 257                                 logger("found expired threads: ".$count);
 258                                 while ($thread = dba::fetch($r)) {
 259                                         $last_id = $thread["iid"];
 260                                         dba::delete('thread', array('iid' => $thread["iid"]));
 261                                 }
 262                         } else {
 263                                 logger("No expired threads found");
 264                         }
 265                         dba::close($r);
 266                         logger("Done deleting ".$count." expired threads. Last ID: ".$last_id);
 267
 268                         Config::set('system', 'dbclean-last-id-8', $last_id);
 269                 } elseif ($stage == 9) {
 270                         if ($days <= 0) {
 271                                 return;
 272                         }
 273
 274                         $last_id = Config::get('system', 'dbclean-last-id-9', 0);
 275                         $till_id = Config::get('system', 'dbclean-last-id-8', 0);
 276
 277                         logger("Deleting old global item entries from expired threads from ID ".$last_id." to ID ".$till_id);
 278                         $r = dba::p("SELECT `id` FROM `item` WHERE `uid` = 0 AND
 279                                                 NOT EXISTS (SELECT `guid` FROM `item` AS `i` WHERE `item`.`guid` = `i`.`guid` AND `i`.`uid` != 0) AND
 280                                                 `received` < UTC_TIMESTAMP() - INTERVAL 90 DAY AND `id` >= ? AND `id` <= ?
 281                                         ORDER BY `id` LIMIT ".intval($limit), $last_id, $till_id);
 282                         $count = dba::num_rows($r);
 283                         if ($count > 0) {
 284                                 logger("found global item entries from expired threads: ".$count);
 285                                 while ($orphan = dba::fetch($r)) {
 286                                         $last_id = $orphan["id"];
 287                                         dba::delete('item', array('id' => $orphan["id"]));
 288                                 }
 289                         } else {
 290                                 logger("No global item entries from expired threads");
 291                         }
 292                         dba::close($r);
 293                         logger("Done deleting ".$count." old global item entries from expired threads. Last ID: ".$last_id);
 294
 295                         Config::set('system', 'dbclean-last-id-9', $last_id);
 296                 }
 297
 298                 // Call it again if not all entries were purged
 299                 if (($stage != 0) && ($count > 0)) {
 300                         Worker::add(PRIORITY_MEDIUM, 'dbclean');
 301                 }
 302         }
 303 }