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
   9 function dbclean_run(&$argv, &$argc) {
  10         if (!Config::get('system', 'dbclean', false)) {
  11                 return;
  12         }
  13
  14         if ($argc == 2) {
  15                 $stage = intval($argv[1]);
  16         } else {
  17                 $stage = 0;
  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                                 proc_run(PRIORITY_LOW, 'include/dbclean.php', $i);
  29                         }
  30                 }
  31         } else {
  32                 remove_orphans($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 function remove_orphans($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                 proc_run(PRIORITY_MEDIUM, 'include/dbclean.php');
 301         }
 302 }