diff options
author | DL6ER <DL6ER@users.noreply.github.com> | 2019-12-19 00:36:43 +0300 |
---|---|---|
committer | GitHub <noreply@github.com> | 2019-12-19 00:36:43 +0300 |
commit | e2de199f4743b99c6bbb0eecedb66282203be638 (patch) | |
tree | 87e37a10c6ef85fb217c350169e509aeb50d874c /advanced | |
parent | a1633123aa831b75bcb5b7deace45cf5a2b49d0f (diff) | |
parent | a720fe17893ffbf48714eb11132089a7e858971e (diff) |
Merge pull request #3037 from pi-hole/new/group_zero
Add special group zero to gravity database
Diffstat (limited to 'advanced')
-rw-r--r-- | advanced/Scripts/database_migration/gravity-db.sh | 8 | ||||
-rw-r--r-- | advanced/Scripts/database_migration/gravity/6_to_7.sql | 35 |
2 files changed, 43 insertions, 0 deletions
diff --git a/advanced/Scripts/database_migration/gravity-db.sh b/advanced/Scripts/database_migration/gravity-db.sh index 508d9c56..28054643 100644 --- a/advanced/Scripts/database_migration/gravity-db.sh +++ b/advanced/Scripts/database_migration/gravity-db.sh @@ -64,4 +64,12 @@ upgrade_gravityDB(){ sqlite3 "${database}" < "${scriptPath}/5_to_6.sql" version=6 fi + if [[ "$version" == "6" ]]; then + # This migration script adds a special group with ID 0 + # which is automatically associated to all clients not + # having their own group assignments + echo -e " ${INFO} Upgrading gravity database from version 6 to 7" + sqlite3 "${database}" < "${scriptPath}/6_to_7.sql" + version=7 + fi } diff --git a/advanced/Scripts/database_migration/gravity/6_to_7.sql b/advanced/Scripts/database_migration/gravity/6_to_7.sql new file mode 100644 index 00000000..22d9dfaf --- /dev/null +++ b/advanced/Scripts/database_migration/gravity/6_to_7.sql @@ -0,0 +1,35 @@ +.timeout 30000 + +PRAGMA FOREIGN_KEYS=OFF; + +BEGIN TRANSACTION; + +INSERT OR REPLACE INTO "group" (id,enabled,name) VALUES (0,1,'Unassociated'); + +INSERT INTO domainlist_by_group (domainlist_id, group_id) SELECT id, 0 FROM domainlist; +INSERT INTO client_by_group (client_id, group_id) SELECT id, 0 FROM client; +INSERT INTO adlist_by_group (adlist_id, group_id) SELECT id, 0 FROM adlist; + +CREATE TRIGGER tr_domainlist_add AFTER INSERT ON domainlist + BEGIN + INSERT INTO domainlist_by_group (domainlist_id, group_id) VALUES (NEW.id, 0); + END; + +CREATE TRIGGER tr_client_add AFTER INSERT ON client + BEGIN + INSERT INTO client_by_group (client_id, group_id) VALUES (NEW.id, 0); + END; + +CREATE TRIGGER tr_adlist_add AFTER INSERT ON adlist + BEGIN + INSERT INTO adlist_by_group (adlist_id, group_id) VALUES (NEW.id, 0); + END; + +CREATE TRIGGER tr_group_zero AFTER DELETE ON "group" + BEGIN + INSERT OR REPLACE INTO "group" (id,enabled,name) VALUES (0,1,'Unassociated'); + END; + +UPDATE info SET value = 7 WHERE property = 'version'; + +COMMIT; |