diff options
author | DL6ER <DL6ER@users.noreply.github.com> | 2019-12-08 18:50:22 +0300 |
---|---|---|
committer | GitHub <noreply@github.com> | 2019-12-08 18:50:22 +0300 |
commit | 807a5cfb23f3b21484d7018401d3c590f6cdf499 (patch) | |
tree | 5f290f4c5a5891126b4c6e4d2a4a75a7892dc424 /advanced | |
parent | ca7a5bc0fe962284a8d0ba905b99bcdadc9afb6e (diff) | |
parent | b6cd7b8e3d36a5d7208136a671e65913076aef79 (diff) |
Merge pull request #3015 from pi-hole/tweak/domainlist_table
Unite four domain tables into a single domainlist table.
Diffstat (limited to 'advanced')
-rw-r--r-- | advanced/Scripts/database_migration/gravity-db.sh | 13 | ||||
-rw-r--r-- | advanced/Scripts/database_migration/gravity/3_to_4.sql | 96 | ||||
-rwxr-xr-x | advanced/Scripts/list.sh | 168 |
3 files changed, 191 insertions, 86 deletions
diff --git a/advanced/Scripts/database_migration/gravity-db.sh b/advanced/Scripts/database_migration/gravity-db.sh index 0fe90d8a..a12be864 100644 --- a/advanced/Scripts/database_migration/gravity-db.sh +++ b/advanced/Scripts/database_migration/gravity-db.sh @@ -10,6 +10,8 @@ # 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" + upgrade_gravityDB(){ local database piholeDir auditFile version database="${1}" @@ -23,7 +25,7 @@ upgrade_gravityDB(){ # This migration script upgrades the gravity.db file by # adding the domain_audit table echo -e " ${INFO} Upgrading gravity database from version 1 to 2" - sqlite3 "${database}" < "/etc/.pihole/advanced/Scripts/database_migration/gravity/1_to_2.sql" + sqlite3 "${database}" < "${scriptPath}/1_to_2.sql" version=2 # Store audit domains in database table @@ -38,7 +40,14 @@ upgrade_gravityDB(){ # renaming the regex table to regex_blacklist, and # creating a new regex_whitelist table + corresponding linking table and views echo -e " ${INFO} Upgrading gravity database from version 2 to 3" - sqlite3 "${database}" < "/etc/.pihole/advanced/Scripts/database_migration/gravity/2_to_3.sql" + sqlite3 "${database}" < "${scriptPath}/2_to_3.sql" version=3 fi + if [[ "$version" == "3" ]]; then + # This migration script unifies the formally separated domain + # 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=6 + fi } diff --git a/advanced/Scripts/database_migration/gravity/3_to_4.sql b/advanced/Scripts/database_migration/gravity/3_to_4.sql new file mode 100644 index 00000000..8d1c1d26 --- /dev/null +++ b/advanced/Scripts/database_migration/gravity/3_to_4.sql @@ -0,0 +1,96 @@ +.timeout 30000 + +PRAGMA FOREIGN_KEYS=OFF; + +BEGIN TRANSACTION; + +CREATE TABLE domainlist +( + id INTEGER PRIMARY KEY AUTOINCREMENT, + type INTEGER NOT NULL DEFAULT 0, + domain TEXT UNIQUE NOT NULL, + enabled BOOLEAN NOT NULL DEFAULT 1, + date_added INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int)), + date_modified INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int)), + comment TEXT +); + +ALTER TABLE whitelist ADD COLUMN type INTEGER; +UPDATE whitelist SET type = 0; +INSERT INTO domainlist (type,domain,enabled,date_added,date_modified,comment) + SELECT type,domain,enabled,date_added,date_modified,comment FROM whitelist; + +ALTER TABLE blacklist ADD COLUMN type INTEGER; +UPDATE blacklist SET type = 1; +INSERT INTO domainlist (type,domain,enabled,date_added,date_modified,comment) + SELECT type,domain,enabled,date_added,date_modified,comment FROM blacklist; + +ALTER TABLE regex_whitelist ADD COLUMN type INTEGER; +UPDATE regex_whitelist SET type = 2; +INSERT INTO domainlist (type,domain,enabled,date_added,date_modified,comment) + SELECT type,domain,enabled,date_added,date_modified,comment FROM regex_whitelist; + +ALTER TABLE regex_blacklist ADD COLUMN type INTEGER; +UPDATE regex_blacklist SET type = 3; +INSERT INTO domainlist (type,domain,enabled,date_added,date_modified,comment) + SELECT type,domain,enabled,date_added,date_modified,comment FROM regex_blacklist; + +DROP TABLE whitelist_by_group; +DROP TABLE blacklist_by_group; +DROP TABLE regex_whitelist_by_group; +DROP TABLE regex_blacklist_by_group; +CREATE TABLE domainlist_by_group +( + domainlist_id INTEGER NOT NULL REFERENCES domainlist (id), + group_id INTEGER NOT NULL REFERENCES "group" (id), + PRIMARY KEY (domainlist_id, group_id) +); + +DROP TRIGGER tr_whitelist_update; +DROP TRIGGER tr_blacklist_update; +DROP TRIGGER tr_regex_whitelist_update; +DROP TRIGGER tr_regex_blacklist_update; +CREATE TRIGGER tr_domainlist_update AFTER UPDATE ON domainlist + BEGIN + UPDATE domainlist SET date_modified = (cast(strftime('%s', 'now') as int)) WHERE domain = NEW.domain; + END; + +DROP VIEW vw_whitelist; +CREATE VIEW vw_whitelist AS SELECT domain, domainlist.id AS id, domainlist_by_group.group_id AS group_id + FROM domainlist + LEFT JOIN domainlist_by_group ON domainlist_by_group.domainlist_id = domainlist.id + LEFT JOIN "group" ON "group".id = domainlist_by_group.group_id + WHERE domainlist.enabled = 1 AND (domainlist_by_group.group_id IS NULL OR "group".enabled = 1) + AND domainlist.type = 0 + ORDER BY domainlist.id; + +DROP VIEW vw_blacklist; +CREATE VIEW vw_blacklist AS SELECT domain, domainlist.id AS id, domainlist_by_group.group_id AS group_id + FROM domainlist + LEFT JOIN domainlist_by_group ON domainlist_by_group.domainlist_id = domainlist.id + LEFT JOIN "group" ON "group".id = domainlist_by_group.group_id + WHERE domainlist.enabled = 1 AND (domainlist_by_group.group_id IS NULL OR "group".enabled = 1) + AND domainlist.type = 1 + ORDER BY domainlist.id; + +DROP VIEW vw_regex_whitelist; +CREATE VIEW vw_regex_whitelist AS SELECT domain, domainlist.id AS id, domainlist_by_group.group_id AS group_id + FROM domainlist + LEFT JOIN domainlist_by_group ON domainlist_by_group.domainlist_id = domainlist.id + LEFT JOIN "group" ON "group".id = domainlist_by_group.group_id + WHERE domainlist.enabled = 1 AND (domainlist_by_group.group_id IS NULL OR "group".enabled = 1) + AND domainlist.type = 2 + ORDER BY domainlist.id; + +DROP VIEW vw_regex_blacklist; +CREATE VIEW vw_regex_blacklist AS SELECT domain, domainlist.id AS id, domainlist_by_group.group_id AS group_id + FROM domainlist + LEFT JOIN domainlist_by_group ON domainlist_by_group.domainlist_id = domainlist.id + LEFT JOIN "group" ON "group".id = domainlist_by_group.group_id + WHERE domainlist.enabled = 1 AND (domainlist_by_group.group_id IS NULL OR "group".enabled = 1) + AND domainlist.type = 3 + ORDER BY domainlist.id; + +UPDATE info SET value = 6 WHERE property = 'version'; + +COMMIT; diff --git a/advanced/Scripts/list.sh b/advanced/Scripts/list.sh index 6a606665..c5bf5b2a 100755 --- a/advanced/Scripts/list.sh +++ b/advanced/Scripts/list.sh @@ -21,68 +21,78 @@ web=false domList=() -listType="" -listname="" +typeId="" colfile="/opt/pihole/COL_TABLE" source ${colfile} +# IDs are hard-wired to domain interpretation in the gravity database scheme +# Clients (including FTL) will read them through the corresponding views +readonly whitelist="0" +readonly blacklist="1" +readonly regex_whitelist="2" +readonly regex_blacklist="3" + +GetListnameFromTypeId() { + if [[ "$1" == "${whitelist}" ]]; then + echo "whitelist" + elif [[ "$1" == "${blacklist}" ]]; then + echo "blacklist" + elif [[ "$1" == "${regex_whitelist}" ]]; then + echo "regex whitelist" + elif [[ "$1" == "${regex_blacklist}" ]]; then + echo "regex blacklist" + fi +} -helpFunc() { - if [[ "${listType}" == "whitelist" ]]; then - param="w" - type="whitelist" - elif [[ "${listType}" == "regex_blacklist" && "${wildcard}" == true ]]; then - param="-wild" - type="wildcard blacklist" - elif [[ "${listType}" == "regex_blacklist" ]]; then - param="-regex" - type="regex blacklist filter" - elif [[ "${listType}" == "regex_whitelist" && "${wildcard}" == true ]]; then - param="-white-wild" - type="wildcard whitelist" - elif [[ "${listType}" == "regex_whitelist" ]]; then - param="-white-regex" - type="regex whitelist filter" - else - param="b" - type="blacklist" +GetListParamFromTypeId() { + if [[ "${typeId}" == "${whitelist}" ]]; then + echo "w" + elif [[ "${typeId}" == "${blacklist}" ]]; then + echo "b" + elif [[ "${typeId}" == "${regex_whitelist}" && "${wildcard}" == true ]]; then + echo "-white-wild" + elif [[ "${typeId}" == "${regex_whitelist}" ]]; then + echo "-white-regex" + elif [[ "${typeId}" == "${regex_blacklist}" && "${wildcard}" == true ]]; then + echo "-wild" + elif [[ "${typeId}" == "${regex_blacklist}" ]]; then + echo "-regex" fi +} + +helpFunc() { + local listname param + + listname="$(GetListnameFromTypeId "${typeId}")" + param="$(GetListParamFromTypeId)" echo "Usage: pihole -${param} [options] <domain> <domain2 ...> Example: 'pihole -${param} site.com', or 'pihole -${param} site1.com site2.com' -${type^} one or more domains +${listname^} one or more domains Options: - -d, --delmode Remove domain(s) from the ${type} - -nr, --noreload Update ${type} without reloading the DNS server + -d, --delmode Remove domain(s) from the ${listname} + -nr, --noreload Update ${listname} without reloading the DNS server -q, --quiet Make output less verbose -h, --help Show this help dialog - -l, --list Display all your ${type}listed domains + -l, --list Display all your ${listname}listed domains --nuke Removes all entries in a list" exit 0 } -EscapeRegexp() { - # This way we may safely insert an arbitrary - # string in our regular expressions - # This sed is intentionally executed in three steps to ease maintainability - # The first sed removes any amount of leading dots - echo $* | sed 's/^\.*//' | sed "s/[]\.|$(){}?+*^]/\\\\&/g" | sed "s/\\//\\\\\//g" -} - -HandleOther() { +ValidateDomain() { # Convert to lowercase domain="${1,,}" # Check validity of domain (don't check for regex entries) if [[ "${#domain}" -le 253 ]]; then - if [[ ( "${listType}" == "regex_blacklist" || "${listType}" == "regex_whitelist" ) && "${wildcard}" == false ]]; then + if [[ ( "${typeId}" == "${regex_blacklist}" || "${typeId}" == "${regex_whitelist}" ) && "${wildcard}" == false ]]; then validDomain="${domain}" else - validDomain=$(grep -P "^((-|_)*[a-z\\d]((-|_)*[a-z\\d])*(-|_)*)(\\.(-|_)*([a-z\\d]((-|_)*[a-z\\d])*))*$" <<< "${domain}") # Valid chars check - validDomain=$(grep -P "^[^\\.]{1,63}(\\.[^\\.]{1,63})*$" <<< "${validDomain}") # Length of each label + # Use regex to check the validity of the passed domain. see https://regexr.com/3abjr + validDomain=$(grep -P "^((?!-))(xn--)?[a-z0-9][a-z0-9-_]{0,61}[a-z0-9]{0,1}\.(xn--)?([a-z0-9\-]{1,61}|[a-z0-9-]{1,30}\.[a-z]{2,})$" <<< "${domain}") fi fi @@ -94,21 +104,6 @@ HandleOther() { } ProcessDomainList() { - local is_regexlist - if [[ "${listType}" == "regex_blacklist" ]]; then - # Regex black filter list - listname="regex blacklist filters" - is_regexlist=true - elif [[ "${listType}" == "regex_whitelist" ]]; then - # Regex white filter list - listname="regex whitelist filters" - is_regexlist=true - else - # Whitelist / Blacklist - listname="${listType}" - is_regexlist=false - fi - for dom in "${domList[@]}"; do # Format domain into regex filter if requested if [[ "${wildcard}" == true ]]; then @@ -118,77 +113,82 @@ ProcessDomainList() { # Logic: If addmode then add to desired list and remove from the other; # if delmode then remove from desired list but do not add to the other if ${addmode}; then - AddDomain "${dom}" "${listType}" - if ! ${is_regexlist}; then - RemoveDomain "${dom}" "${listAlt}" - fi + AddDomain "${dom}" else - RemoveDomain "${dom}" "${listType}" + RemoveDomain "${dom}" fi done } AddDomain() { - local domain list num - # Use printf to escape domain. %q prints the argument in a form that can be reused as shell input + local domain num requestedListname existingTypeId existingListname domain="$1" - list="$2" # Is the domain in the list we want to add it to? - num="$(sqlite3 "${gravityDBfile}" "SELECT COUNT(*) FROM ${list} WHERE domain = '${domain}';")" + num="$(sqlite3 "${gravityDBfile}" "SELECT COUNT(*) FROM domainlist WHERE domain = '${domain}';")" + requestedListname="$(GetListnameFromTypeId "${typeId}")" if [[ "${num}" -ne 0 ]]; then - if [[ "${verbose}" == true ]]; then - echo -e " ${INFO} ${1} already exists in ${listname}, no need to add!" + existingTypeId="$(sqlite3 "${gravityDBfile}" "SELECT type FROM domainlist WHERE domain = '${domain}';")" + if [[ "${existingTypeId}" == "${typeId}" ]]; then + if [[ "${verbose}" == true ]]; then + echo -e " ${INFO} ${1} already exists in ${requestedListname}, no need to add!" + fi + else + existingListname="$(GetListnameFromTypeId "${existingTypeId}")" + sqlite3 "${gravityDBfile}" "UPDATE domainlist SET type = ${typeId} WHERE domain='${domain}';" + if [[ "${verbose}" == true ]]; then + echo -e " ${INFO} ${1} already exists in ${existingListname}, it has been moved to ${requestedListname}!" + fi fi return fi # Domain not found in the table, add it! if [[ "${verbose}" == true ]]; then - echo -e " ${INFO} Adding ${1} to the ${listname}..." + echo -e " ${INFO} Adding ${domain} to the ${requestedListname}..." fi reload=true # Insert only the domain here. The enabled and date_added fields will be filled # with their default values (enabled = true, date_added = current timestamp) - sqlite3 "${gravityDBfile}" "INSERT INTO ${list} (domain) VALUES ('${domain}');" + sqlite3 "${gravityDBfile}" "INSERT INTO domainlist (domain,type) VALUES ('${domain}',${typeId});" } RemoveDomain() { - local domain list num - # Use printf to escape domain. %q prints the argument in a form that can be reused as shell input + local domain num requestedListname domain="$1" - list="$2" # Is the domain in the list we want to remove it from? - num="$(sqlite3 "${gravityDBfile}" "SELECT COUNT(*) FROM ${list} WHERE domain = '${domain}';")" + num="$(sqlite3 "${gravityDBfile}" "SELECT COUNT(*) FROM domainlist WHERE domain = '${domain}' AND type = ${typeId};")" + + requestedListname="$(GetListnameFromTypeId "${typeId}")" if [[ "${num}" -eq 0 ]]; then if [[ "${verbose}" == true ]]; then - echo -e " ${INFO} ${1} does not exist in ${list}, no need to remove!" + echo -e " ${INFO} ${domain} does not exist in ${requestedListname}, no need to remove!" fi return fi # Domain found in the table, remove it! if [[ "${verbose}" == true ]]; then - echo -e " ${INFO} Removing ${1} from the ${listname}..." + echo -e " ${INFO} Removing ${domain} from the ${requestedListname}..." fi reload=true # Remove it from the current list - sqlite3 "${gravityDBfile}" "DELETE FROM ${list} WHERE domain = '${domain}';" + sqlite3 "${gravityDBfile}" "DELETE FROM domainlist WHERE domain = '${domain}' AND type = ${typeId};" } Displaylist() { - local list listname count num_pipes domain enabled status nicedate + local count num_pipes domain enabled status nicedate requestedListname - listname="${listType}" - data="$(sqlite3 "${gravityDBfile}" "SELECT domain,enabled,date_modified FROM ${listType};" 2> /dev/null)" + requestedListname="$(GetListnameFromTypeId "${typeId}")" + data="$(sqlite3 "${gravityDBfile}" "SELECT domain,enabled,date_modified FROM domainlist WHERE type = ${typeId};" 2> /dev/null)" if [[ -z $data ]]; then echo -e "Not showing empty list" else - echo -e "Displaying ${listname}:" + echo -e "Displaying ${requestedListname}:" count=1 while IFS= read -r line do @@ -221,17 +221,17 @@ Displaylist() { } NukeList() { - sqlite3 "${gravityDBfile}" "DELETE FROM ${listType};" + sqlite3 "${gravityDBfile}" "DELETE FROM domainlist WHERE type = ${typeId};" } for var in "$@"; do case "${var}" in - "-w" | "whitelist" ) listType="whitelist"; listAlt="blacklist";; - "-b" | "blacklist" ) listType="blacklist"; listAlt="whitelist";; - "--wild" | "wildcard" ) listType="regex_blacklist"; wildcard=true;; - "--regex" | "regex" ) listType="regex_blacklist";; - "--white-regex" | "white-regex" ) listType="regex_whitelist";; - "--white-wild" | "white-wild" ) listType="regex_whitelist"; wildcard=true;; + "-w" | "whitelist" ) typeId=0;; + "-b" | "blacklist" ) typeId=1;; + "--white-regex" | "white-regex" ) typeId=2;; + "--white-wild" | "white-wild" ) typeId=2; wildcard=true;; + "--wild" | "wildcard" ) typeId=3; wildcard=true;; + "--regex" | "regex" ) typeId=3;; "-nr"| "--noreload" ) reload=false;; "-d" | "--delmode" ) addmode=false;; "-q" | "--quiet" ) verbose=false;; @@ -239,7 +239,7 @@ for var in "$@"; do "-l" | "--list" ) Displaylist;; "--nuke" ) NukeList;; "--web" ) web=true;; - * ) HandleOther "${var}";; + * ) ValidateDomain "${var}";; esac done |