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, false)) {
  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                 $last_id = Config::get('system', 'dbclean-last-id-1', 0);
  44
  45                 logger("Deleting old global item entries from item table without user copy. Last ID: ".$last_id);
  46                 $r = dba::p("SELECT `id` FROM `item` WHERE `uid` = 0 AND
  47                                         NOT EXISTS (SELECT `guid` FROM `item` AS `i` WHERE `item`.`guid` = `i`.`guid` AND `i`.`uid` != 0) AND
  48                                         `received` < UTC_TIMESTAMP() - INTERVAL 90 DAY AND `id` >= ?
  49                                 ORDER BY `id` LIMIT ".intval($limit), $last_id);
  50                 $count = dba::num_rows($r);
  51                 if ($count > 0) {
  52                         logger("found global item orphans: ".$count);
  53                         while ($orphan = dba::fetch($r)) {
  54                                 $last_id = $orphan["id"];
  55                                 dba::delete('item', array('id' => $orphan["id"]));
  56                         }
  57                 } else {
  58                         logger("No global item orphans found");
  59                 }
  60                 dba::close($r);
  61                 logger("Done deleting ".$count." old global item entries from item table without user copy. Last ID: ".$last_id);
  62
  63                 Config::set('system', 'dbclean-last-id-1', $last_id);
  64
  65                 // We will eventually set this value when we found a good way to delete these items in another way.
  66                 // if ($count < $limit) {
  67                 //      Config::set('system', 'finished-dbclean-1', true);
  68                 // }
  69         } elseif ($stage == 2) {
  70                 $last_id = Config::get('system', 'dbclean-last-id-2', 0);
  71
  72                 logger("Deleting items without parents. Last ID: ".$last_id);
  73                 $r = dba::p("SELECT `id` FROM `item`
  74                                 WHERE NOT EXISTS (SELECT `id` FROM `item` AS `i` WHERE `item`.`parent` = `i`.`id`)
  75                                 AND `id` >= ? ORDER BY `id` LIMIT ".intval($limit), $last_id);
  76                 $count = dba::num_rows($r);
  77                 if ($count > 0) {
  78                         logger("found item orphans without parents: ".$count);
  79                         while ($orphan = dba::fetch($r)) {
  80                                 $last_id = $orphan["id"];
  81                                 dba::delete('item', array('id' => $orphan["id"]));
  82                         }
  83                 } else {
  84                         logger("No item orphans without parents found");
  85                 }
  86                 dba::close($r);
  87                 logger("Done deleting ".$count." items without parents. Last ID: ".$last_id);
  88
  89                 Config::set('system', 'dbclean-last-id-2', $last_id);
  90
  91                 if ($count < $limit) {
  92                         Config::set('system', 'finished-dbclean-2', true);
  93                 }
  94         } elseif ($stage == 3) {
  95                 $last_id = Config::get('system', 'dbclean-last-id-3', 0);
  96
  97                 logger("Deleting orphaned data from thread table. Last ID: ".$last_id);
  98                 $r = dba::p("SELECT `iid` FROM `thread`
  99                                 WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `thread`.`iid`) AND `iid` >= ?
 100                                 ORDER BY `iid` LIMIT ".intval($limit), $last_id);
 101                 $count = dba::num_rows($r);
 102                 if ($count > 0) {
 103                         logger("found thread orphans: ".$count);
 104                         while ($orphan = dba::fetch($r)) {
 105                                 $last_id = $orphan["iid"];
 106                                 dba::delete('thread', array('iid' => $orphan["iid"]));
 107                         }
 108                 } else {
 109                         logger("No thread orphans found");
 110                 }
 111                 dba::close($r);
 112                 logger("Done deleting ".$count." orphaned data from thread table. Last ID: ".$last_id);
 113
 114                 Config::set('system', 'dbclean-last-id-3', $last_id);
 115
 116                 if ($count < $limit) {
 117                         Config::set('system', 'finished-dbclean-3', true);
 118                 }
 119         } elseif ($stage == 4) {
 120                 $last_id = Config::get('system', 'dbclean-last-id-4', 0);
 121
 122                 logger("Deleting orphaned data from notify table. Last ID: ".$last_id);
 123                 $r = dba::p("SELECT `iid`, `id` FROM `notify`
 124                                 WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `notify`.`iid`) AND `id` >= ?
 125                                 ORDER BY `id` LIMIT ".intval($limit), $last_id);
 126                 $count = dba::num_rows($r);
 127                 if ($count > 0) {
 128                         logger("found notify orphans: ".$count);
 129                         while ($orphan = dba::fetch($r)) {
 130                                 $last_id = $orphan["id"];
 131                                 dba::delete('notify', array('iid' => $orphan["iid"]));
 132                         }
 133                 } else {
 134                         logger("No notify orphans found");
 135                 }
 136                 dba::close($r);
 137                 logger("Done deleting ".$count." orphaned data from notify table. Last ID: ".$last_id);
 138
 139                 Config::set('system', 'dbclean-last-id-4', $last_id);
 140
 141                 if ($count < $limit) {
 142                         Config::set('system', 'finished-dbclean-4', true);
 143                 }
 144         } elseif ($stage == 5) {
 145                 $last_id = Config::get('system', 'dbclean-last-id-5', 0);
 146
 147                 logger("Deleting orphaned data from notify-threads table. Last ID: ".$last_id);
 148                 $r = dba::p("SELECT `id` FROM `notify-threads`
 149                                 WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`parent` = `notify-threads`.`master-parent-item`) AND `id` >= ?
 150                                 ORDER BY `id` LIMIT ".intval($limit), $last_id);
 151                 $count = dba::num_rows($r);
 152                 if ($count > 0) {
 153                         logger("found notify-threads orphans: ".$count);
 154                         while ($orphan = dba::fetch($r)) {
 155                                 $last_id = $orphan["id"];
 156                                 dba::delete('notify-threads', array('id' => $orphan["id"]));
 157                         }
 158                 } else {
 159                         logger("No notify-threads orphans found");
 160                 }
 161                 dba::close($r);
 162                 logger("Done deleting ".$count." orphaned data from notify-threads table. Last ID: ".$last_id);
 163
 164                 Config::set('system', 'dbclean-last-id-5', $last_id);
 165
 166                 if ($count < $limit) {
 167                         Config::set('system', 'finished-dbclean-5', true);
 168                 }
 169         } elseif ($stage == 6) {
 170                 $last_id = Config::get('system', 'dbclean-last-id-6', 0);
 171
 172                 logger("Deleting orphaned data from sign table. Last ID: ".$last_id);
 173                 $r = dba::p("SELECT `iid`, `id` FROM `sign`
 174                                 WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `sign`.`iid`) AND `id` >= ?
 175                                 ORDER BY `id` LIMIT ".intval($limit), $last_id);
 176                 $count = dba::num_rows($r);
 177                 if ($count > 0) {
 178                         logger("found sign orphans: ".$count);
 179                         while ($orphan = dba::fetch($r)) {
 180                                 $last_id = $orphan["id"];
 181                                 dba::delete('sign', array('iid' => $orphan["iid"]));
 182                         }
 183                 } else {
 184                         logger("No sign orphans found");
 185                 }
 186                 dba::close($r);
 187                 logger("Done deleting ".$count." orphaned data from sign table. Last ID: ".$last_id);
 188
 189                 Config::set('system', 'dbclean-last-id-6', $last_id);
 190
 191                 if ($count < $limit) {
 192                         Config::set('system', 'finished-dbclean-6', true);
 193                 }
 194         } elseif ($stage == 7) {
 195                 $last_id = Config::get('system', 'dbclean-last-id-7', 0);
 196
 197                 logger("Deleting orphaned data from term table. Last ID: ".$last_id);
 198                 $r = dba::p("SELECT `oid`, `tid` FROM `term`
 199                                 WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `term`.`oid`) AND `tid` >= ?
 200                                 ORDER BY `tid` LIMIT ".intval($limit), $last_id);
 201                 $count = dba::num_rows($r);
 202                 if ($count > 0) {
 203                         logger("found term orphans: ".$count);
 204                         while ($orphan = dba::fetch($r)) {
 205                                 $last_id = $orphan["tid"];
 206                                 dba::delete('term', array('oid' => $orphan["oid"]));
 207                         }
 208                 } else {
 209                         logger("No term orphans found");
 210                 }
 211                 dba::close($r);
 212                 logger("Done deleting ".$count." orphaned data from term table. Last ID: ".$last_id);
 213
 214                 Config::set('system', 'dbclean-last-id-7', $last_id);
 215
 216                 if ($count < $limit) {
 217                         Config::set('system', 'finished-dbclean-7', true);
 218                 }
 219         }
 220
 221         // Call it again if not all entries were purged
 222         if (($stage != 0) AND ($count > 0)) {
 223                 proc_run(PRIORITY_MEDIUM, 'include/dbclean.php');
 224         }
 225
 226 }