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