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 <= 10; $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) || ($i > 9) || ($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          * 10:  Old conversations.
  52          */
  53         private static function removeOrphans($stage = 0) {
  54                 global $db;
  55
  56                 $count = 0;
  57
  58                 // We split the deletion in many small tasks
  59                 $limit = 1000;
  60
  61                 // Get the expire days for step 8 and 9
  62                 $days = Config::get('system', 'dbclean-expire-days', 0);
  63
  64                 if ($stage == 1) {
  65                         $last_id = Config::get('system', 'dbclean-last-id-1', 0);
  66
  67                         logger("Deleting old global item entries from item table without user copy. Last ID: ".$last_id);
  68                         $r = dba::p("SELECT `id` FROM `item` WHERE `uid` = 0 AND
  69                                                 NOT EXISTS (SELECT `guid` FROM `item` AS `i` WHERE `item`.`guid` = `i`.`guid` AND `i`.`uid` != 0) AND
  70                                                 `received` < UTC_TIMESTAMP() - INTERVAL 90 DAY AND `id` >= ?
  71                                         ORDER BY `id` LIMIT ".intval($limit), $last_id);
  72                         $count = dba::num_rows($r);
  73                         if ($count > 0) {
  74                                 logger("found global item orphans: ".$count);
  75                                 while ($orphan = dba::fetch($r)) {
  76                                         $last_id = $orphan["id"];
  77                                         dba::delete('item', array('id' => $orphan["id"]));
  78                                 }
  79                         } else {
  80                                 logger("No global item orphans found");
  81                         }
  82                         dba::close($r);
  83                         logger("Done deleting ".$count." old global item entries from item table without user copy. Last ID: ".$last_id);
  84
  85                         Config::set('system', 'dbclean-last-id-1', $last_id);
  86                 } elseif ($stage == 2) {
  87                         $last_id = Config::get('system', 'dbclean-last-id-2', 0);
  88
  89                         logger("Deleting items without parents. Last ID: ".$last_id);
  90                         $r = dba::p("SELECT `id` FROM `item`
  91                                         WHERE NOT EXISTS (SELECT `id` FROM `item` AS `i` WHERE `item`.`parent` = `i`.`id`)
  92                                         AND `id` >= ? ORDER BY `id` LIMIT ".intval($limit), $last_id);
  93                         $count = dba::num_rows($r);
  94                         if ($count > 0) {
  95                                 logger("found item orphans without parents: ".$count);
  96                                 while ($orphan = dba::fetch($r)) {
  97                                         $last_id = $orphan["id"];
  98                                         dba::delete('item', array('id' => $orphan["id"]));
  99                                 }
 100                         } else {
 101                                 logger("No item orphans without parents found");
 102                         }
 103                         dba::close($r);
 104                         logger("Done deleting ".$count." items without parents. Last ID: ".$last_id);
 105
 106                         Config::set('system', 'dbclean-last-id-2', $last_id);
 107
 108                         if ($count < $limit) {
 109                                 Config::set('system', 'finished-dbclean-2', true);
 110                         }
 111                 } elseif ($stage == 3) {
 112                         $last_id = Config::get('system', 'dbclean-last-id-3', 0);
 113
 114                         logger("Deleting orphaned data from thread table. Last ID: ".$last_id);
 115                         $r = dba::p("SELECT `iid` FROM `thread`
 116                                         WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `thread`.`iid`) AND `iid` >= ?
 117                                         ORDER BY `iid` LIMIT ".intval($limit), $last_id);
 118                         $count = dba::num_rows($r);
 119                         if ($count > 0) {
 120                                 logger("found thread orphans: ".$count);
 121                                 while ($orphan = dba::fetch($r)) {
 122                                         $last_id = $orphan["iid"];
 123                                         dba::delete('thread', array('iid' => $orphan["iid"]));
 124                                 }
 125                         } else {
 126                                 logger("No thread orphans found");
 127                         }
 128                         dba::close($r);
 129                         logger("Done deleting ".$count." orphaned data from thread table. Last ID: ".$last_id);
 130
 131                         Config::set('system', 'dbclean-last-id-3', $last_id);
 132
 133                         if ($count < $limit) {
 134                                 Config::set('system', 'finished-dbclean-3', true);
 135                         }
 136                 } elseif ($stage == 4) {
 137                         $last_id = Config::get('system', 'dbclean-last-id-4', 0);
 138
 139                         logger("Deleting orphaned data from notify table. Last ID: ".$last_id);
 140                         $r = dba::p("SELECT `iid`, `id` FROM `notify`
 141                                         WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `notify`.`iid`) AND `id` >= ?
 142                                         ORDER BY `id` LIMIT ".intval($limit), $last_id);
 143                         $count = dba::num_rows($r);
 144                         if ($count > 0) {
 145                                 logger("found notify orphans: ".$count);
 146                                 while ($orphan = dba::fetch($r)) {
 147                                         $last_id = $orphan["id"];
 148                                         dba::delete('notify', array('iid' => $orphan["iid"]));
 149                                 }
 150                         } else {
 151                                 logger("No notify orphans found");
 152                         }
 153                         dba::close($r);
 154                         logger("Done deleting ".$count." orphaned data from notify table. Last ID: ".$last_id);
 155
 156                         Config::set('system', 'dbclean-last-id-4', $last_id);
 157
 158                         if ($count < $limit) {
 159                                 Config::set('system', 'finished-dbclean-4', true);
 160                         }
 161                 } elseif ($stage == 5) {
 162                         $last_id = Config::get('system', 'dbclean-last-id-5', 0);
 163
 164                         logger("Deleting orphaned data from notify-threads table. Last ID: ".$last_id);
 165                         $r = dba::p("SELECT `id` FROM `notify-threads`
 166                                         WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `notify-threads`.`master-parent-item`) AND `id` >= ?
 167                                         ORDER BY `id` LIMIT ".intval($limit), $last_id);
 168                         $count = dba::num_rows($r);
 169                         if ($count > 0) {
 170                                 logger("found notify-threads orphans: ".$count);
 171                                 while ($orphan = dba::fetch($r)) {
 172                                         $last_id = $orphan["id"];
 173                                         dba::delete('notify-threads', array('id' => $orphan["id"]));
 174                                 }
 175                         } else {
 176                                 logger("No notify-threads orphans found");
 177                         }
 178                         dba::close($r);
 179                         logger("Done deleting ".$count." orphaned data from notify-threads table. Last ID: ".$last_id);
 180
 181                         Config::set('system', 'dbclean-last-id-5', $last_id);
 182
 183                         if ($count < $limit) {
 184                                 Config::set('system', 'finished-dbclean-5', true);
 185                         }
 186                 } elseif ($stage == 6) {
 187                         $last_id = Config::get('system', 'dbclean-last-id-6', 0);
 188
 189                         logger("Deleting orphaned data from sign table. Last ID: ".$last_id);
 190                         $r = dba::p("SELECT `iid`, `id` FROM `sign`
 191                                         WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `sign`.`iid`) AND `id` >= ?
 192                                         ORDER BY `id` LIMIT ".intval($limit), $last_id);
 193                         $count = dba::num_rows($r);
 194                         if ($count > 0) {
 195                                 logger("found sign orphans: ".$count);
 196                                 while ($orphan = dba::fetch($r)) {
 197                                         $last_id = $orphan["id"];
 198                                         dba::delete('sign', array('iid' => $orphan["iid"]));
 199                                 }
 200                         } else {
 201                                 logger("No sign orphans found");
 202                         }
 203                         dba::close($r);
 204                         logger("Done deleting ".$count." orphaned data from sign table. Last ID: ".$last_id);
 205
 206                         Config::set('system', 'dbclean-last-id-6', $last_id);
 207
 208                         if ($count < $limit) {
 209                                 Config::set('system', 'finished-dbclean-6', true);
 210                         }
 211                 } elseif ($stage == 7) {
 212                         $last_id = Config::get('system', 'dbclean-last-id-7', 0);
 213
 214                         logger("Deleting orphaned data from term table. Last ID: ".$last_id);
 215                         $r = dba::p("SELECT `oid`, `tid` FROM `term`
 216                                         WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `term`.`oid`) AND `tid` >= ?
 217                                         ORDER BY `tid` LIMIT ".intval($limit), $last_id);
 218                         $count = dba::num_rows($r);
 219                         if ($count > 0) {
 220                                 logger("found term orphans: ".$count);
 221                                 while ($orphan = dba::fetch($r)) {
 222                                         $last_id = $orphan["tid"];
 223                                         dba::delete('term', array('oid' => $orphan["oid"]));
 224                                 }
 225                         } else {
 226                                 logger("No term orphans found");
 227                         }
 228                         dba::close($r);
 229                         logger("Done deleting ".$count." orphaned data from term table. Last ID: ".$last_id);
 230
 231                         Config::set('system', 'dbclean-last-id-7', $last_id);
 232
 233                         if ($count < $limit) {
 234                                 Config::set('system', 'finished-dbclean-7', true);
 235                         }
 236                 } elseif ($stage == 8) {
 237                         if ($days <= 0) {
 238                                 return;
 239                         }
 240
 241                         $last_id = Config::get('system', 'dbclean-last-id-8', 0);
 242
 243                         logger("Deleting expired threads. Last ID: ".$last_id);
 244                         $r = dba::p("SELECT `thread`.`iid` FROM `thread`
 245                                         INNER JOIN `contact` ON `thread`.`contact-id` = `contact`.`id` AND NOT `notify_new_posts`
 246                                         WHERE `thread`.`received` < UTC_TIMESTAMP() - INTERVAL ? DAY
 247                                                 AND NOT `thread`.`mention` AND NOT `thread`.`starred`
 248                                                 AND NOT `thread`.`wall` AND NOT `thread`.`origin`
 249                                                 AND `thread`.`uid` != 0 AND `thread`.`iid` >= ?
 250                                                 AND NOT `thread`.`iid` IN (SELECT `parent` FROM `item`
 251                                                                 WHERE (`item`.`starred` OR (`item`.`resource-id` != '')
 252                                                                         OR (`item`.`file` != '') OR (`item`.`event-id` != '')
 253                                                                         OR (`item`.`attach` != '') OR `item`.`wall` OR `item`.`origin`)
 254                                                                         AND `item`.`parent` = `thread`.`iid`)
 255                                         ORDER BY `thread`.`iid` LIMIT 1000", $days, $last_id);
 256                         $count = dba::num_rows($r);
 257                         if ($count > 0) {
 258                                 logger("found expired threads: ".$count);
 259                                 while ($thread = dba::fetch($r)) {
 260                                         $last_id = $thread["iid"];
 261                                         dba::delete('thread', array('iid' => $thread["iid"]));
 262                                 }
 263                         } else {
 264                                 logger("No expired threads found");
 265                         }
 266                         dba::close($r);
 267                         logger("Done deleting ".$count." expired threads. Last ID: ".$last_id);
 268
 269                         Config::set('system', 'dbclean-last-id-8', $last_id);
 270                 } elseif ($stage == 9) {
 271                         if ($days <= 0) {
 272                                 return;
 273                         }
 274
 275                         $last_id = Config::get('system', 'dbclean-last-id-9', 0);
 276                         $till_id = Config::get('system', 'dbclean-last-id-8', 0);
 277
 278                         logger("Deleting old global item entries from expired threads from ID ".$last_id." to ID ".$till_id);
 279                         $r = dba::p("SELECT `id` FROM `item` WHERE `uid` = 0 AND
 280                                                 NOT EXISTS (SELECT `guid` FROM `item` AS `i` WHERE `item`.`guid` = `i`.`guid` AND `i`.`uid` != 0) AND
 281                                                 `received` < UTC_TIMESTAMP() - INTERVAL 90 DAY AND `id` >= ? AND `id` <= ?
 282                                         ORDER BY `id` LIMIT ".intval($limit), $last_id, $till_id);
 283                         $count = dba::num_rows($r);
 284                         if ($count > 0) {
 285                                 logger("found global item entries from expired threads: ".$count);
 286                                 while ($orphan = dba::fetch($r)) {
 287                                         $last_id = $orphan["id"];
 288                                         dba::delete('item', array('id' => $orphan["id"]));
 289                                 }
 290                         } else {
 291                                 logger("No global item entries from expired threads");
 292                         }
 293                         dba::close($r);
 294                         logger("Done deleting ".$count." old global item entries from expired threads. Last ID: ".$last_id);
 295
 296                         Config::set('system', 'dbclean-last-id-9', $last_id);
 297                 } elseif ($stage == 10) {
 298                         $last_id = Config::get('system', 'dbclean-last-id-10', 0);
 299
 300                         logger("Deleting old conversations. Last created: ".$last_id);
 301                         $r = dba::p("SELECT `received`, `item-uri` FROM `conversation`
 302                                         WHERE `received` < UTC_TIMESTAMP() - INTERVAL 90 DAY
 303                                         ORDER BY `received` LIMIT ".intval($limit));
 304                         $count = dba::num_rows($r);
 305                         if ($count > 0) {
 306                                 logger("found old conversations: ".$count);
 307                                 while ($orphan = dba::fetch($r)) {
 308                                         $last_id = $orphan["received"];
 309                                         dba::delete('conversation', array('item-uri' => $orphan["item-uri"]));
 310                                 }
 311                         } else {
 312                                 logger("No old conversations found");
 313                         }
 314                         dba::close($r);
 315                         logger("Done deleting ".$count." conversations. Last created: ".$last_id);
 316
 317                         Config::set('system', 'dbclean-last-id-10', $last_id);
 318                 }
 319
 320                 // Call it again if not all entries were purged
 321                 if (($stage != 0) && ($count > 0)) {
 322                         Worker::add(PRIORITY_MEDIUM, 'dbclean');
 323                 }
 324         }
 325 }