diff options
author | Aleksander Machniak <alec@alec.pl> | 2021-10-03 13:10:14 +0300 |
---|---|---|
committer | Aleksander Machniak <alec@alec.pl> | 2021-10-03 13:10:14 +0300 |
commit | 48fbf110af3f76128de7bbe7f6ce0e48247513e4 (patch) | |
tree | f6a361b7b4dfe522f52f6091e7f7bb3a676610eb /program | |
parent | 626332d42a6c4d12389b40ae57113e95f3b2ce56 (diff) |
SQLite: Use foreign keys, require SQLite >= 3.6.19 (#8159)
Diffstat (limited to 'program')
-rw-r--r-- | program/include/rcmail_utils.php | 31 | ||||
-rw-r--r-- | program/lib/Roundcube/db/sqlite.php | 5 |
2 files changed, 7 insertions, 29 deletions
diff --git a/program/include/rcmail_utils.php b/program/include/rcmail_utils.php index 2de6009d3..9036cbcfb 100644 --- a/program/include/rcmail_utils.php +++ b/program/include/rcmail_utils.php @@ -277,43 +277,18 @@ class rcmail_utils */ public static function db_clean($days) { - $db = self::db(); - - // mapping for table name => primary key - $primary_keys = [ - 'contacts' => 'contact_id', - 'contactgroups' => 'contactgroup_id', - ]; - - $tables = [ + $db = self::db(); + $threshold = date('Y-m-d 00:00:00', time() - $days * 86400); + $tables = [ 'contacts', 'contactgroups', 'identities', 'responses', ]; - $threshold = date('Y-m-d 00:00:00', time() - $days * 86400); - foreach ($tables as $table) { $sqltable = $db->table_name($table, true); - // also delete linked records - // could be skipped for databases which respect foreign key constraints - if ($db->db_provider == 'sqlite' && ($table == 'contacts' || $table == 'contactgroups')) { - $pk = $primary_keys[$table]; - $memberstable = $db->table_name('contactgroupmembers'); - - $db->query( - "DELETE FROM " . $db->quote_identifier($memberstable) - . " WHERE `$pk` IN (" - . "SELECT `$pk` FROM $sqltable" - . " WHERE `del` = 1 AND `changed` < ?" - . ")", - $threshold); - - echo $db->affected_rows() . " records deleted from '$memberstable'\n"; - } - // delete outdated records $db->query("DELETE FROM $sqltable WHERE `del` = 1 AND `changed` < ?", $threshold); diff --git a/program/lib/Roundcube/db/sqlite.php b/program/lib/Roundcube/db/sqlite.php index 4182f7535..87e9c8384 100644 --- a/program/lib/Roundcube/db/sqlite.php +++ b/program/lib/Roundcube/db/sqlite.php @@ -76,7 +76,10 @@ class rcube_db_sqlite extends rcube_db } // Enable WAL mode to fix locking issues like #8035. - $dbh->query("PRAGMA journal_mode=WAL"); + $dbh->query("PRAGMA journal_mode = WAL"); + + // Enable foreign keys (requires sqlite 3.6.19 compiled with FK support) + $dbh->query("PRAGMA foreign_keys = ON"); } /** |