diff options
| author | DL6ER <dl6er@dl6er.de> | 2019-11-26 12:58:39 +0300 |
|---|---|---|
| committer | DL6ER <dl6er@dl6er.de> | 2019-11-26 12:58:39 +0300 |
| commit | 037d52104a84ac867842e017c30879d17ee79cfe (patch) | |
| tree | a6d79a4a28ec90ae26fb5a60f40bfb8586188f8d /advanced | |
| parent | d883854aadb5f56075a6fe51cbe3ec59daf6751c (diff) | |
New command "pihole -g -r" recreates gravity.db based on files backed up in /etc/pihole/migration_update. This is useful to restore a working version of the database when the user destroyed the original database. Also, update gravity.db to version 5 because of a fix we needed to implement.
Signed-off-by: DL6ER <dl6er@dl6er.de>
Diffstat (limited to 'advanced')
| -rw-r--r-- | advanced/Scripts/database_migration/gravity-db.sh | 12 | ||||
| -rw-r--r-- | advanced/Scripts/database_migration/gravity/4_to_5.sql | 17 |
2 files changed, 27 insertions, 2 deletions
diff --git a/advanced/Scripts/database_migration/gravity-db.sh b/advanced/Scripts/database_migration/gravity-db.sh index 773898b0..1fe6a61f 100644 --- a/advanced/Scripts/database_migration/gravity-db.sh +++ b/advanced/Scripts/database_migration/gravity-db.sh @@ -44,9 +44,17 @@ upgrade_gravityDB(){ version=3 fi if [[ "$version" == "3" ]]; then - # This migration script upgrades the gravity and adlist views + # This migration script upgrades the gravity and list views # implementing necessary changes for per-client blocking + echo -e " ${INFO} Upgrading gravity database from version 3 to 4" sqlite3 "${database}" < "${scriptPath}/3_to_4.sql" - version=3 + version=4 + fi + if [[ "$version" == "4" ]]; then + # This migration script upgrades the adlist view + # to return an ID used in gravity.sh + echo -e " ${INFO} Upgrading gravity database from version 4 to 5" + sqlite3 "${database}" < "${scriptPath}/4_to_5.sql" + version=5 fi } 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..22b75d58 --- /dev/null +++ b/advanced/Scripts/database_migration/gravity/4_to_5.sql @@ -0,0 +1,17 @@ +.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 = 5 WHERE property = 'version'; + +COMMIT; |