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