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         if ($stage == 0) {
  21                 for ($i = 1; $i <= 7; $i++) {
  22                         if (!Config::get('system', 'finished-dbclean-'.$i)) {
  23                                 proc_run(PRIORITY_LOW, 'include/dbclean.php', $i);
  24                         }
  25                 }
  26         } else {
  27                 remove_orphans($stage);
  28         }
  29 }
  30
  31 /**
  32  * @brief Remove orphaned database entries
  33  */
  34 function remove_orphans($stage = 0) {
  35         global $db;
  36
  37         $count = 0;
  38
  39         // We split the deletion in many small tasks
  40         $limit = 1000;
  41
  42         if ($stage == 1) {
  43                 logger("Deleting old global item entries from item table without user copy");
  44                 $r = dba::p("SELECT `id` FROM `item` WHERE `uid` = 0
  45                                 AND NOT EXISTS (SELECT `guid` FROM `item` AS `i` WHERE `item`.`guid` = `i`.`guid` AND `i`.`uid` != 0)
  46                                 AND `received` < UTC_TIMESTAMP() - INTERVAL 90 DAY LIMIT ".intval($limit));
  47                 $count = dba::num_rows($r);
  48                 if ($count > 0) {
  49                         logger("found global item orphans: ".$count);
  50                         while ($orphan = dba::fetch($r)) {
  51                                 dba::delete('item', array('id' => $orphan["id"]));
  52                         }
  53                 } else {
  54                         logger("No global item orphans found");
  55
  56                 }
  57                 dba::close($r);
  58                 logger("Done deleting ".$count." old global item entries from item table without user copy");
  59
  60                 // We will eventually set this value when we found a good way to delete these items in another way.
  61                 // if ($count < $limit) {
  62                 //      Config::set('system', 'finished-dbclean-1', true);
  63                 // }
  64         } elseif ($stage == 2) {
  65                 logger("Deleting items without parents");
  66                 $r = dba::p("SELECT `id` FROM `item` WHERE NOT EXISTS (SELECT `id` FROM `item` AS `i` WHERE `item`.`parent` = `i`.`id`) LIMIT ".intval($limit));
  67                 $count = dba::num_rows($r);
  68                 if ($count > 0) {
  69                         logger("found item orphans without parents: ".$count);
  70                         while ($orphan = dba::fetch($r)) {
  71                                 dba::delete('item', array('id' => $orphan["id"]));
  72                         }
  73                 } else {
  74                         logger("No item orphans without parents found");
  75                 }
  76                 dba::close($r);
  77                 logger("Done deleting ".$count." items without parents");
  78
  79                 if ($count < $limit) {
  80                         Config::set('system', 'finished-dbclean-2', true);
  81                 }
  82         } elseif ($stage == 3) {
  83                 logger("Deleting orphaned data from thread table");
  84                 $r = dba::p("SELECT `iid` FROM `thread` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `thread`.`iid`) LIMIT ".intval($limit));
  85                 $count = dba::num_rows($r);
  86                 if ($count > 0) {
  87                         logger("found thread orphans: ".$count);
  88                         while ($orphan = dba::fetch($r)) {
  89                                 dba::delete('thread', array('iid' => $orphan["iid"]));
  90                         }
  91                 } else {
  92                         logger("No thread orphans found");
  93                 }
  94                 dba::close($r);
  95                 logger("Done deleting ".$count." orphaned data from thread table");
  96
  97                 if ($count < $limit) {
  98                         Config::set('system', 'finished-dbclean-3', true);
  99                 }
 100         } elseif ($stage == 4) {
 101                 logger("Deleting orphaned data from notify table");
 102                 $r = dba::p("SELECT `iid` FROM `notify` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `notify`.`iid`) LIMIT ".intval($limit));
 103                 $count = dba::num_rows($r);
 104                 if ($count > 0) {
 105                         logger("found notify orphans: ".$count);
 106                         while ($orphan = dba::fetch($r)) {
 107                                 dba::delete('notify', array('iid' => $orphan["iid"]));
 108                         }
 109                 } else {
 110                         logger("No notify orphans found");
 111                 }
 112                 dba::close($r);
 113                 logger("Done deleting ".$count." orphaned data from notify table");
 114
 115                 if ($count < $limit) {
 116                         Config::set('system', 'finished-dbclean-4', true);
 117                 }
 118         } elseif ($stage == 5) {
 119                 logger("Deleting orphaned data from notify-threads table");
 120                 $r = dba::p("SELECT `id` FROM `notify-threads` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `notify-threads`.`master-parent-item`) LIMIT ".intval($limit));
 121                 $count = dba::num_rows($r);
 122                 if ($count > 0) {
 123                         logger("found notify-threads orphans: ".$count);
 124                         while ($orphan = dba::fetch($r)) {
 125                                 dba::delete('notify-threads', array('id' => $orphan["id"]));
 126                         }
 127                 } else {
 128                         logger("No notify-threads orphans found");
 129                 }
 130                 dba::close($r);
 131                 logger("Done deleting ".$count." orphaned data from notify-threads table");
 132
 133                 if ($count < $limit) {
 134                         Config::set('system', 'finished-dbclean-5', true);
 135                 }
 136         } elseif ($stage == 6) {
 137                 logger("Deleting orphaned data from sign table");
 138                 $r = dba::p("SELECT `iid` FROM `sign` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `sign`.`iid`) LIMIT ".intval($limit));
 139                 $count = dba::num_rows($r);
 140                 if ($count > 0) {
 141                         logger("found sign orphans: ".$count);
 142                         while ($orphan = dba::fetch($r)) {
 143                                 dba::delete('sign', array('iid' => $orphan["iid"]));
 144                         }
 145                 } else {
 146                         logger("No sign orphans found");
 147                 }
 148                 dba::close($r);
 149                 logger("Done deleting ".$count." orphaned data from sign table");
 150
 151                 if ($count < $limit) {
 152                         Config::set('system', 'finished-dbclean-6', true);
 153                 }
 154         } elseif ($stage == 7) {
 155                 logger("Deleting orphaned data from term table");
 156                 $r = dba::p("SELECT `oid` FROM `term` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `term`.`oid`) LIMIT ".intval($limit));
 157                 $count = dba::num_rows($r);
 158                 if ($count > 0) {
 159                         logger("found term orphans: ".$count);
 160                         while ($orphan = dba::fetch($r)) {
 161                                 dba::delete('term', array('oid' => $orphan["oid"]));
 162                         }
 163                 } else {
 164                         logger("No term orphans found");
 165                 }
 166                 dba::close($r);
 167                 logger("Done deleting ".$count." orphaned data from term table");
 168
 169                 if ($count < $limit) {
 170                         Config::set('system', 'finished-dbclean-7', true);
 171                 }
 172         }
 173
 174         // Call it again if not all entries were purged
 175         if (($stage != 0) AND ($count > 0)) {
 176                 proc_run(PRIORITY_MEDIUM, 'include/dbclean.php');
 177         }
 178
 179 }