include/dbclean.php

   1 <?php
   2 /**
   3  * @file include/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
  10 function dbclean_run(&$argv, &$argc) {
  11         if (!Config::get('system', 'dbclean', false)) {
  12                 return;
  13         }
  14
  15         if ($argc == 2) {
  16                 $stage = intval($argv[1]);
  17         } else {
  18                 $stage = 0;
  19         }
  20
  21         // Get the expire days for step 8 and 9
  22         $days = Config::get('system', 'dbclean-expire-days', 0);
  23
  24         if ($stage == 0) {
  25                 for ($i = 1; $i <= 9; $i++) {
  26                         // Execute the background script for a step when it isn't finished.
  27                         // Execute step 8 and 9 only when $days is defined.
  28                         if (!Config::get('system', 'finished-dbclean-'.$i, false) && (($i < 8) || ($days > 0))) {
  29                                 Worker::add(PRIORITY_LOW, 'dbclean', $i);
  30                         }
  31                 }
  32         } else {
  33                 remove_orphans($stage);
  34         }
  35 }
  36
  37 /**
  38  * @brief Remove orphaned database entries
  39  * @param integer $stage What should be deleted?
  40  *
  41  * Values for $stage:
  42  * ------------------
  43  * 1:   Old global item entries from item table without user copy.
  44  * 2:   Items without parents.
  45  * 3:   Orphaned data from thread table.
  46  * 4:   Orphaned data from notify table.
  47  * 5:   Orphaned data from notify-threads table.
  48  * 6:   Orphaned data from sign table.
  49  * 7:   Orphaned data from term table.
  50  * 8:   Expired threads.
  51  * 9:   Old global item entries from expired threads
  52  */
  53 function remove_orphans($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         }
 298
 299         // Call it again if not all entries were purged
 300         if (($stage != 0) && ($count > 0)) {
 301                 Worker::add(PRIORITY_MEDIUM, 'dbclean');
 302         }
 303 }