Add database garbage collection

1 files changed, 73 insertions, 0 deletions

diff --git a/app/cron.php b/app/cron.php
new file mode 100644
index 0000000..abadeec
--- /dev/null
+++ b/app/cron.php
@@ -0,0 +1,73 @@
+<?php
+
+/*
+ * SCROTT IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+ * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
+ * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
+ * IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
+ * OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
+ * ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
+ * OTHER DEALINGS IN THE SOFTWARE.
+ *
+ * For more information, please refer to UNLICENSE
+ */
+
+require_once "class/database.class.php";
+
+/*
+ * This file is intended to be run periodically by the local cron daemon
+ * and will perform tasks such as reacting to incoming emails and running
+ * garbage collection on the object database.
+ */
+
+/*
+ * Remove all orphaned objects from Scrott's object database.  These are
+ * any object with either a parent or owner set, which doesn't exist.
+ * Metadata tables (members, views, etc.) are also updated to drop all rows
+ * related to purged objects.
+ */
+function gc() : void
+{
+    $query = "CREATE TEMPORARY TABLE IF NOT EXISTS objtmp AS (SELECT * FROM objects)";
+    database::query($query);
+
+    /* cleanup base objects table */
+    $query = "DELETE FROM objects WHERE parent != '' AND parent NOT IN (SELECT guid FROM objtmp)";
+    database::query($query);
+
+    $query = "DELETE FROM objects WHERE owner != '' AND owner NOT IN (SELECT guid FROM objtmp)";
+    database::query($query);
+
+    /* cleanup sub tables */
+    $query = "DELETE FROM users WHERE guid NOT IN (SELECT guid FROM objects)";
+    database::query($query);
+
+    $query = "DELETE FROM groups WHERE guid NOT IN (SELECT guid FROM objects)";
+    database::query($query);
+
+    $query = "DELETE FROM pads WHERE guid NOT IN (SELECT guid FROM objects)";
+    database::query($query);
+
+    $query = "DELETE FROM stages WHERE guid NOT IN (SELECT guid FROM objects)";
+    database::query($query);
+
+    $query = "DELETE FROM issues WHERE guid NOT IN (SELECT guid FROM objects)";
+    database::query($query);
+
+    $query = "DELETE FROM mesgs WHERE guid NOT IN (SELECT guid FROM objects)";
+    database::query($query);
+
+    /* cleanup metadata tables */
+    $query = "DELETE FROM members WHERE guid NOT IN (SELECT guid FROM objects) " .
+        "OR member NOT IN (SELECT guid FROM objects)";
+    database::query($query);
+
+    $query = "DELETE FROM views WHERE guid NOT IN (SELECT guid FROM objects) " .
+        "OR viewer NOT IN (SELECT guid FROM objects)";
+    database::query($query);
+}
+
+/* Run maintenance routines */
+gc();
+
+?>