diff options
author | Dan Schaper <dan.schaper@pi-hole.net> | 2020-01-24 20:23:34 +0300 |
---|---|---|
committer | GitHub <noreply@github.com> | 2020-01-24 20:23:34 +0300 |
commit | 52e2a2610ea07e7fef3dd68f224c89077c52fa00 (patch) | |
tree | 12801c0409b06cd2498dc1f226996b591d6d3c5d | |
parent | 29f06a444434c48c4258aebfebc5bd4094f57628 (diff) | |
parent | 633e56e8a99f54970d8ed7f3bf3a25c53430003d (diff) |
Merge pull request #3089 from pi-hole/tweak/gravity_db_10
Add gravity database 9->10 update script
-rw-r--r-- | advanced/Scripts/database_migration/gravity-db.sh | 7 | ||||
-rw-r--r-- | advanced/Scripts/database_migration/gravity/9_to_10.sql | 29 |
2 files changed, 36 insertions, 0 deletions
diff --git a/advanced/Scripts/database_migration/gravity-db.sh b/advanced/Scripts/database_migration/gravity-db.sh index 184b3a4a..6a51e353 100644 --- a/advanced/Scripts/database_migration/gravity-db.sh +++ b/advanced/Scripts/database_migration/gravity-db.sh @@ -87,4 +87,11 @@ upgrade_gravityDB(){ sqlite3 "${database}" < "${scriptPath}/8_to_9.sql" version=9 fi + if [[ "$version" == "9" ]]; then + # This migration drops unused tables and creates triggers to remove + # obsolete groups assignments when the linked items are deleted + echo -e " ${INFO} Upgrading gravity database from version 9 to 10" + sqlite3 "${database}" < "${scriptPath}/9_to_10.sql" + version=10 + fi } diff --git a/advanced/Scripts/database_migration/gravity/9_to_10.sql b/advanced/Scripts/database_migration/gravity/9_to_10.sql new file mode 100644 index 00000000..a5636a23 --- /dev/null +++ b/advanced/Scripts/database_migration/gravity/9_to_10.sql @@ -0,0 +1,29 @@ +.timeout 30000 + +PRAGMA FOREIGN_KEYS=OFF; + +BEGIN TRANSACTION; + +DROP TABLE IF EXISTS whitelist; +DROP TABLE IF EXISTS blacklist; +DROP TABLE IF EXISTS regex_whitelist; +DROP TABLE IF EXISTS regex_blacklist; + +CREATE TRIGGER tr_domainlist_delete AFTER DELETE ON domainlist + BEGIN + DELETE FROM domainlist_by_group WHERE domainlist_id = OLD.id; + END; + +CREATE TRIGGER tr_adlist_delete AFTER DELETE ON adlist + BEGIN + DELETE FROM adlist_by_group WHERE adlist_id = OLD.id; + END; + +CREATE TRIGGER tr_client_delete AFTER DELETE ON client + BEGIN + DELETE FROM client_by_group WHERE client_id = OLD.id; + END; + +UPDATE info SET value = 10 WHERE property = 'version'; + +COMMIT; |