Hi all,
Long post, jump to Questions below as this info might not be required.
Hardware and software
- Device = Raspberry Pi Zero (trying to get a Zero 2 but you know how hard they are to get at the moment)
- Pi-hole = v5.16.2
- FTL = v5.22
- Web Interface = v5.19
- SQLite = 3.34.1
- Python = 3.9.2
Background
I'm new to the Raspberry Pi world, Python and SQL, but i thought for my first project, i'd get PiHole set up but also write my own AdList updating script that (these are currently working);
- Downloads one of the curated lists from https://firebog.net
- Extracts the AdList from PiHole's gravity.db
- Compares both lists
- If there are any new AdLists that are in the Firebog list, perform an SQL INSERT into the gravity.db AdList
- If there are any AdLists that are in the PiHole AdList that are not in the Firebog list, an SQL DELETE is performed to remove them
- Email what's been changed
I've got a few other things to include but haven't started on these yet;
- Check if there is an update to PiHole/FTL/Web Interface and send email if there is (hopefully perform automatically)
- Send an email when there is a message in the PiHole diagnosis
I also don't plan on logging into PiHole or the Pi Zero unless it's necessary.
Example
When i'm performing the SQL INSERT into the gravity database, new entries are getting added, but their ID is not starting from the next free ID number. Instead, they are starting from the next last known highest number that was in the database previously. e.g.
Started by adding 6 lists manually in the database via the web portal, then deleted ID numbers 4-6 which leaves 1-3.
ID Address
1 https://raw.githubusercontent.com/chadmayfield/my-pihole-blocklists/master/lists/pi_blocklist_porn_top1m.list
2 https://v.firebog.net/hosts/Prigent-Adult.txt
3 https://raw.githubusercontent.com/anudeepND/blacklist/master/facebook.txt
Added 3 new lists via SQL INSERT but new entries start from 7 onwards and not from 4.
ID Address
1 https://raw.githubusercontent.com/chadmayfield/my-pihole-blocklists/master/lists/pi_blocklist_porn_top1m.list
2 https://v.firebog.net/hosts/Prigent-Adult.txt
3 https://raw.githubusercontent.com/anudeepND/blacklist/master/facebook.txt
7 https://www.test-domain1/file.txt
8 https://www.test-domain2/file.txt
9 https://www.test-domain3/file.txt
Questions
- Is it normal behaviour for new SQL entries to be added into a database with the next last known highest number rather than an unused ID number that's empty?
- Should i be checking for any unused ID numbers and insert new entries into those or let SQL choose the next highest ID number automatically?
- Is PiHole going to start to complain if there are these gaps of ID numbers in the SQL database as this will increase over time?
Thanks,
Ash :]