Great, was thinking you may have to loop it like
#!/bin/bash
while IFS= read -r list
do
echo "$list" | awk -F '|' '{print $3}' | tr -d " "
done <(sqlite3 /etc/pihole/gravity.db "SELECT * FROM domainlist WHERE type = 0;")
But if it works!
Edit, untested, not sure if you can just redirect < or if you need to herestring <<< it.
Edit2, forgot $() can be done with <() instead.
Yeah I can just redirect the output > to my git repo. Sloppy but it works.
After thinking about it i don't think grouping a whitelist would work, since you have to associate a group to individual clients, dangit.
Well, just the domains would be:
sqlite3 /etc/pihole/gravity.db "SELECT domain FROM domainlist WHERE type = 0;"
where you explicitly ask only for the (domain) column instead of all (*).
edit You could also just use pihole -w -l
Displaying whitelist:
1: sa.bbc.co.uk (enabled, last modified Fri, 21 Feb 2020 19:18:08 +0100)
On my todo, however, it will not land before next week. If someone wants to beat me to it, feel free to do so! An issue with this is that we will have to restrict the allowed characters in comments, I'm thinking about a-z A-Z 0-9 _ - . , - anything else needed? If we allow also stuff like ; or similar, it would be really hard to protect against exploits.
Yes, quotation marks will be necessary for spaces.
Maybe this for a more human-friendly output (I have written this code for the debugger)?
printf ".headers ON \\n.width 4 4 100 7 19 19 50\\n.mode column\\n SELECT id,type,domain,enabled,datetime(date_added,'unixepoch','localtime') date_added,datetime(date_modified,'unixepoch','localtime') date_modified,comment FROM domainlist" | sqlite3 /etc/pihole/gravity.db
Oh, duh, of course that works perfectly. Guess I should have just looked at the columns in the table lol. So I've got the following working:
This is the preferred way of interacting with the database. BEGIN a TRANSACTION, do all the INSERTs and then COMMIT in the end. The data is collected and written in one batch once you commit the changes.
If possible I would encourage / to allow for things like from bit.ly/piholestuff for those times where you pull in a suggestion from the web and want to document the source
Okay, so we're now at:
a-z A-Z 0-9 _ - . , # : /
(including whitespace).
I do not see a reason to omit dot or comma. . is also needed for domains (as mentioned by @Matt), commas , are integral parts of any sentences when you want to describe something.
Good catch on the :
Just FYI, here's the full list of potential allowable url characters:
"Safe" url characters:
$-_.+!*'(),
"Reserved" characters which are also allowed:
;/?:@=&
Finally, everything else is supposed to be encoded with %
This list obviously includes a few problem characters, and many of these characters only really show up in very long URLs which will likely break the character limit, so it may be easier to block them and suggest a URL shortener.
Personally I think the list you have is fine, just want you to be prepared for what may be entered into that field.
For anyone who was tracking/cares, here's the script that I ended up with:
#!/bin/bash
timestamp=`date '+%Y%m%d%H%M%S'`;
#Whitelist Maintenance
#Backup old whitelist
/usr/bin/sqlite3 /etc/pihole/gravity.db "SELECT domain FROM domainlist" > /tmp/whitelist.bak.$timestamp;
#Delete old whitelist table contents
/usr/bin/sqlite3 /etc/pihole/gravity.db "DELETE FROM domainlist";
#Import new whitelist domains
for domain in `curl -sS https://raw.githubusercontent.com/dmginc/pihole/master/whitelist.txt`
do
id=$((id+1))
/usr/bin/sqlite3 /etc/pihole/gravity.db "INSERT INTO domainlist VALUES ($id, 0, '$domain', 1, date('now'), date ('now'), '');"
done
#Output total in new whitelist
echo `sqlite3 /etc/pihole/gravity.db "SELECT domain FROM domainlist WHERE type = 0;" | wc -l` "domains in new whitelist"
#Adlist Maintenance
#Backup old adlist domains
/usr/bin/sqlite3 /etc/pihole/gravity.db "SELECT address FROM adlist" > /tmp/adlist.bak.$timestamp;
#Delete old adlist table contents
/usr/bin/sqlite3 /etc/pihole/gravity.db "DELETE FROM adlist;"
#Delete old list data
/bin/rm -rf /etc/pihole/list.*
#Import new adlists
for domain in `curl -sS https://raw.githubusercontent.com/dmginc/pihole/master/adlists.list`
do
id=$((id+1))
/usr/bin/sqlite3 /etc/pihole/gravity.db "INSERT OR IGNORE INTO adlist VALUES ($id, '$domain', 1, date('now'), date('now'), '');"
done
#Reload PiHole
/usr/local/bin/pihole -g
This pulls my own set of whitelisted domains and adlists, but can easily be changed to whatever source you want. Big thank you to @DL6ER & @DanSchaper for holding my hand through the new db structure and reminding how to use 'awk'!
Suggestion: Have sqlite3 do the counting for you. This is much much faster and less I/O intense!
sqlite3 /etc/pihole/gravity.db "SELECT COUNT(domain) FROM domainlist WHERE type = 0;"
Suggestion: Do not specify optional fields (default values will be used, e.g., unset instead of empty comment fields, status enabled and the current time as well):
sqlite3 /etc/pihole/gravity.db "INSERT INTO domainlist (id, type, domain) VALUES (1001, 0, '$domain');"
Similarly for the adlists, where the field is called address not domain.
Just a quick heads up: Still on my Todo list, I haven't forgotten about this one. Unforeseen things came in my way this week (+weekend), I will hopefully come to CLI comment next week.
Feel free to test it using
pihole checkout core new/CLI_domain_comments
You can use
pihole -b --comment "A B C" facebook.net
or even
pihole -b facebook.net --comment "A B C"
Multiple domain can still be added
pihole -b --comment "A B C" facebook.net otherbook.net twitter.com
When you specify the --comment option multiple times, we use the last comment, e.g.,
pihole -b --comment "A B C" facebook.net otherbook.net --comment "B C D" twitter.com
will add the three domains with the comment B C D:
Any reports for me whether you tested it and it worked or if something needs to be changed/improved?
I have tested this, it works as expected 
Small thing, curly brackets are unnecessary if it's a special variable with a single letter like "$1" according to google shellguide.
It finally happened. The CLI comment support is still awaiting review + approval.
CLI comment support has been added to the beta code. Please go back on track using
pihole checkout core release/v5.0