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) {
  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                                 dba::delete('item', array('id' => $orphan["id"]));
  54                         }
  55                 } else {
  56                         logger("No global item orphans found");
  57                 }
  58                 dba::close($r);
  59                 logger("Done deleting ".$count." old global item entries from item table without user copy");
  60         } elseif ($stage == 2) {
  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                                 dba::delete('item', array('id' => $orphan["id"]));
  68                         }
  69                 } else {
  70                         logger("No item orphans without parents found");
  71                 }
  72                 dba::close($r);
  73                 logger("Done deleting ".$count." items without parents");
  74         } elseif ($stage == 3) {
  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                                 dba::delete('thread', array('iid' => $orphan["iid"]));
  82                         }
  83                 } else {
  84                         logger("No thread orphans found");
  85                 }
  86
  87                 dba::close($r);
  88                 logger("Done deleting ".$count." orphaned data from thread table");
  89         } elseif ($stage == 4) {
  90                 logger("Deleting orphaned data from notify table");
  91                 $r = dba::p("SELECT `iid` FROM `notify` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `notify`.`iid`) LIMIT ".intval($limit));
  92                 $count = dba::num_rows($r);
  93                 if ($count > 0) {
  94                         logger("found notify orphans: ".$count);
  95                         while ($orphan = dba::fetch($r)) {
  96                                 dba::delete('notify', array('iid' => $orphan["iid"]));
  97                         }
  98                 } else {
  99                         logger("No notify orphans found");
 100                 }
 101                 dba::close($r);
 102                 logger("Done deleting ".$count." orphaned data from notify table");
 103         } elseif ($stage == 5) {
 104                 logger("Deleting orphaned data from notify-threads table");
 105                 $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));
 106                 $count = dba::num_rows($r);
 107                 if ($count > 0) {
 108                         logger("found notify-threads orphans: ".$count);
 109                         while ($orphan = dba::fetch($r)) {
 110                                 dba::delete('notify-threads', array('id' => $orphan["id"]));
 111                         }
 112                 } else {
 113                         logger("No notify-threads orphans found");
 114                 }
 115                 dba::close($r);
 116                 logger("Done deleting ".$count." orphaned data from notify-threads table");
 117         } elseif ($stage == 6) {
 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                                 dba::delete('sign', array('iid' => $orphan["iid"]));
 125                         }
 126                 } else {
 127                         logger("No sign orphans found");
 128                 }
 129                 dba::close($r);
 130                 logger("Done deleting ".$count." orphaned data from sign table");
 131         } elseif ($stage == 7) {
 132                 logger("Deleting orphaned data from term table");
 133                 $r = dba::p("SELECT `oid` FROM `term` WHERE NOT EXISTS (SELECT `id` FROM `item` WHERE `item`.`id` = `term`.`oid`) LIMIT ".intval($limit));
 134                 $count = dba::num_rows($r);
 135                 if ($count > 0) {
 136                         logger("found term orphans: ".$count);
 137                         while ($orphan = dba::fetch($r)) {
 138                                 dba::delete('term', array('oid' => $orphan["oid"]));
 139                         }
 140                 } else {
 141                         logger("No term orphans found");
 142                 }
 143                 dba::close($r);
 144                 logger("Done deleting ".$count." orphaned data from term table");
 145         }
 146
 147         // Call it again if not all entries were purged
 148         if (($stage != 0) AND ($count > 0)) {
 149                 proc_run(PRIORITY_MEDIUM, 'include/dbclean.php');
 150         }
 151
 152 }