Adding many lists via Shell

e-renner · March 18, 2022, 3:02pm

Hi,
I automated the deployment of PiHole via a Shell script for me. It is, because I re-install PiHole quite some times. And I can administer it for some other family Households in this way too.
I moved to a different city and wanted to reinstall PiHole.
I took my Singleboard computer, installed "debian buster by ayufan" on a SD card, as I did the last time. Then I imported my script and run it as sudo. This script imports my blocklists, exakt blocking and whitelisting entries and the regEXes.

These are about 800 entries... So, I´ve done it directly via Database

I´ve done
sudo sqlite3 /etc/pihole/gravity.db "INSERT INTO domainlist (type, domain, enabled, comment) VALUES (0,'outlook.office365.com', 1,'Microsoft365Global')";

and got back
sudo: sqlite3: command not found

I got shocked, because I reinstalled my PiHole 3 different time with this script successfully.

I went to the docs and red, that it now have to be
sudo pihole-FTL sqlite3 /etc/pihole/gravity.db "INSERT INTO domainlist (type, domain, enabled, comment) VALUES (0,'outlook.office.com', 1,'Microsoft365Global')";

I replaced every sqlite3 to pihole-FTL sqlite3. I can import the Blocklists at least for now. But sometimes it´s still complaining about table can´t be found. What did you change at the database-structure?

jfb · March 18, 2022, 3:28pm

Have you considered using the teleporter function, which exports all your existing Pi-hole settings for later import into the new installation?

Yes.

github.com/pi-hole/FTL

Optimize queries table

pi-hole:development ← pi-hole:new/optimize-queries

opened 11:55AM - 16 Dec 21 UTC

DL6ER

+376 -52

**By submitting this pull request, I confirm the following:** - [X] I have re…ad and understood the [contributors guide](https://github.com/pi-hole/pi-hole/blob/master/CONTRIBUTING.md). - [X] I have checked that [another pull request](https://github.com/pi-hole/FTL/pulls) for this purpose does not exist. - [X] I have considered, and confirmed that this submission will be valuable to others. - [X] I accept that this submission may not be used, and the pull request closed at the will of the maintainer. - [X] I give this submission freely, and claim no ownership to its content. **How familiar are you with the codebase?:** ## 10 --- This PR optimizes the `queries` table by using link-tables with IDs instead of storing the `domain`, `client` and `forward` as strings over and over. This is *not* a breaking change to users as we provide a `VIEW queries` in place of the old `TABLE queries` that will give the same results for external queries as before: `VIEW queries` ``` $ sqlite3 /etc/pihole/pihole-FTL.db -header "SELECT * FROM queries ORDER BY id DESC LIMIT 1;" id|timestamp|type|status|domain|client|forward|additional_info 19391970|1639654453|2|2|forum.arduino.cc|192.168.2.206|127.0.0.1#5353| ``` `TABLE query_storage` (holding the real data) ``` $ sqlite3 /etc/pihole/pihole-FTL.db -header "SELECT * FROM query_storage ORDER BY id DESC LIMIT 1;" id|timestamp|type|status|domain|client|forward|additional_info 19391970|1639654453|2|2|114|2|1| ``` In the example above, storage required *per query* before: ``` 4 sizeof(id) + 4 sizeof(timestamp) + 1 sizeof(type) + 1 sizeof(status) + 16 strlen("forum.arduino.cc") + 1 + 14 strlen("192.168.2.206") + 1 + 15 strlen("127.0.0.1#5353") + 1 + 1 sizeof(NULL) = 56 bytes per record ``` Storage required *per query* with the optimization of this PR: ``` 4 sizeof(id) + 4 sizeof(timestamp) + 1 sizeof(type) + 1 sizeof(status) + 1 sizeof(domain) + 1 sizeof(client) + 1 sizeof(forward) + 1 sizeof(additional_info) = 14 bytes per record ``` This reduction by factor 4 easily outperforms the tiny extra amount of space we need for the newly introduced link-tables. We chose this hybrid approach through the `VIEW` because we cannot afford rewriting the entire `queries` table as this would literally take forever on low-end devices such as Raspberry Pis from the early days. This also lays the foundation for a requested feature that is availability of host names in the long-term database as the linked client ID are in fact a pair of `(ip,hostname)` in the database. This allows us to store both without any really noticeable overhead. This PR comes with a tiny drawback (that isn't really one, though): After the database has been optimized (upgraded to version 11), older versions of FTL will not be able to write new queries to the database. It is possible to revert the upgrade of the database without data-loss, however, it is a multiple-step task and should not be done copy-paste-like. That's why I'm not giving the precise instructions to do this here. **edit** We furthermore save some space on the `additional_information` field that con contain domains when blocking during deep CNAME inspection.

system · April 8, 2022, 3:28pm

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.