diff options
author | DL6ER <DL6ER@users.noreply.github.com> | 2019-12-12 11:49:02 +0300 |
---|---|---|
committer | GitHub <noreply@github.com> | 2019-12-12 11:49:02 +0300 |
commit | 5457b2c6ea91bce9e100db16a5f1e5c30d1d3f33 (patch) | |
tree | d5e2e7a2722c0779a64f2aef43515d5a0ea87bb8 /advanced | |
parent | 02f33167105d0a7384e8df4f86ebedc4205b281a (diff) | |
parent | d29947ba32de5313ffbd9bedb76e0f8218812d05 (diff) |
Merge pull request #2935 from pi-hole/new/internal-blocking
Per-client blocking changes
Diffstat (limited to 'advanced')
4 files changed, 73 insertions, 3 deletions
diff --git a/advanced/Scripts/database_migration/gravity-db.sh b/advanced/Scripts/database_migration/gravity-db.sh index a12be864..508d9c56 100644 --- a/advanced/Scripts/database_migration/gravity-db.sh +++ b/advanced/Scripts/database_migration/gravity-db.sh @@ -10,7 +10,7 @@ # This file is copyright under the latest version of the EUPL. # Please see LICENSE file for your rights under this license. -scriptPath="/etc/.pihole/advanced/Scripts/database_migration/gravity" +readonly scriptPath="/etc/.pihole/advanced/Scripts/database_migration/gravity" upgrade_gravityDB(){ local database piholeDir auditFile version @@ -48,6 +48,20 @@ upgrade_gravityDB(){ # lists into a single table with a UNIQUE domain constraint echo -e " ${INFO} Upgrading gravity database from version 3 to 4" sqlite3 "${database}" < "${scriptPath}/3_to_4.sql" + version=4 + fi + if [[ "$version" == "4" ]]; then + # This migration script upgrades the gravity and list views + # implementing necessary changes for per-client blocking + echo -e " ${INFO} Upgrading gravity database from version 4 to 5" + sqlite3 "${database}" < "${scriptPath}/4_to_5.sql" + version=5 + fi + if [[ "$version" == "5" ]]; then + # This migration script upgrades the adlist view + # to return an ID used in gravity.sh + echo -e " ${INFO} Upgrading gravity database from version 5 to 6" + sqlite3 "${database}" < "${scriptPath}/5_to_6.sql" version=6 fi } diff --git a/advanced/Scripts/database_migration/gravity/3_to_4.sql b/advanced/Scripts/database_migration/gravity/3_to_4.sql index 8d1c1d26..352b1baa 100644 --- a/advanced/Scripts/database_migration/gravity/3_to_4.sql +++ b/advanced/Scripts/database_migration/gravity/3_to_4.sql @@ -91,6 +91,6 @@ CREATE VIEW vw_regex_blacklist AS SELECT domain, domainlist.id AS id, domainlist AND domainlist.type = 3 ORDER BY domainlist.id; -UPDATE info SET value = 6 WHERE property = 'version'; +UPDATE info SET value = 4 WHERE property = 'version'; -COMMIT; +COMMIT;
\ No newline at end of file diff --git a/advanced/Scripts/database_migration/gravity/4_to_5.sql b/advanced/Scripts/database_migration/gravity/4_to_5.sql new file mode 100644 index 00000000..2ad906fc --- /dev/null +++ b/advanced/Scripts/database_migration/gravity/4_to_5.sql @@ -0,0 +1,38 @@ +.timeout 30000 + +PRAGMA FOREIGN_KEYS=OFF; + +BEGIN TRANSACTION; + +DROP TABLE gravity; +CREATE TABLE gravity +( + domain TEXT NOT NULL, + adlist_id INTEGER NOT NULL REFERENCES adlist (id), + PRIMARY KEY(domain, adlist_id) +); + +DROP VIEW vw_gravity; +CREATE VIEW vw_gravity AS SELECT domain, adlist_by_group.group_id AS group_id + FROM gravity + LEFT JOIN adlist_by_group ON adlist_by_group.adlist_id = gravity.adlist_id + LEFT JOIN adlist ON adlist.id = gravity.adlist_id + LEFT JOIN "group" ON "group".id = adlist_by_group.group_id + WHERE adlist.enabled = 1 AND (adlist_by_group.group_id IS NULL OR "group".enabled = 1); + +CREATE TABLE client +( + id INTEGER PRIMARY KEY AUTOINCREMENT, + ip TEXT NOL NULL UNIQUE +); + +CREATE TABLE client_by_group +( + client_id INTEGER NOT NULL REFERENCES client (id), + group_id INTEGER NOT NULL REFERENCES "group" (id), + PRIMARY KEY (client_id, group_id) +); + +UPDATE info SET value = 5 WHERE property = 'version'; + +COMMIT;
\ No newline at end of file diff --git a/advanced/Scripts/database_migration/gravity/5_to_6.sql b/advanced/Scripts/database_migration/gravity/5_to_6.sql new file mode 100644 index 00000000..d2bb3145 --- /dev/null +++ b/advanced/Scripts/database_migration/gravity/5_to_6.sql @@ -0,0 +1,18 @@ +.timeout 30000 + +PRAGMA FOREIGN_KEYS=OFF; + +BEGIN TRANSACTION; + +DROP VIEW vw_adlist; +CREATE VIEW vw_adlist AS SELECT DISTINCT address, adlist.id AS id + FROM adlist + LEFT JOIN adlist_by_group ON adlist_by_group.adlist_id = adlist.id + LEFT JOIN "group" ON "group".id = adlist_by_group.group_id + WHERE adlist.enabled = 1 AND (adlist_by_group.group_id IS NULL OR "group".enabled = 1) + ORDER BY adlist.id; + +UPDATE info SET value = 6 WHERE property = 'version'; + +COMMIT; + |