edit
updated the sample scripts to correct my own stupidity (see below)... /edit
Again, I'm currently analyzing this (youtube ad blocking) script, the result is raising some questions.
This time, I'm looking at different ways to retrieve data.
Method 1: using sqlite3:
date="$(date --date="yesterday" '+%Y/%m/%d')"
time="$(date "+%R"; date "+%R" -d "1 hour ago")"
start=$( echo ${time} | cut --delimiter " " --fields 2)
begintm=$(TZ=CET date --date="$start" +"%s")
sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT domain FROM "queries" WHERE domain LIKE 'r_%---sn-%.googlevideo.com' AND timestamp > $begintm;"
It looks like the data is retrieved from the database on disk. Since the data is committed every minute, there appears to be a one minute delay. To confirm this, I've increased the setting from default DBINTERVAL=1.0 to DBINTERVAL=2. As expected the delay increases. There is a problem using this method, sometimes you get the message database is locked, thus no response, requiring additional logic to detect success.
The result apparently is the same. There is a delay, this delay increases when increasing the DBINTERVAL setting. This method never appears to fail, as opposed to the sqlite3 method.
My question(s):
which one is more efficient? The sqlite3 method or the telnet-api method. I think (but I'm wrong a lot of times) the telnet-api uses pihole-FTL as a man in the middle to get data from the database, thus sqlite3 being the better choice. I would like this to be confirmed.
given the delay (data only saved once a minute - default), is it possible to force a save to disk, prior to the data request, thus eliminating the delay? Is it safe to do that?
No. FTL has all the data in memory and can immediately respond to your request without the need for a single I/O operation. However, the data is limited to the most recent 24 hours (whereas you can request as much as you like from the database).
Dumping is only possible on restart. But you will not need this.
Not arguing here, as I said I configured DBINTERVAL=2. As soon as I load a youtube video, I can immediately see the request in the pihole log, I'm using this method to look at the logs, so it's practically real time on my (windows) screen. According to your explanation, if I than immediately execute either of the above scripts, I should see the result, however, It takes two minutes before I get a result. What am I missing?
FTL knows everything immediately as it processes your queries. Similarly, pihole.log sees everything immediately as it is updated without any delay.
The database, however, only gets updated every two minutes (with your setting) so there is a certain expected delay only in your first method.
I agree the DBINTERVAL=2 is responsible for the delay, using the sqlite3 method, however, I can't explain the delay with the telnet-api. Still wondering if I'm missing something...
This gives the most recent query ((1) means: Only one domain should be returned). As you can see, the difference to the current timestamp (second line) is only three seconds. Similar results appear when I query later. There is never a delay building up for me.
Found the problem, it's me (stupid).
query between begintime and endtime leads to the problem (copied from an other script to retrieve yesterdays queries)
example:
begintime 14:25
endtime 15:25
query at 15:25:01 will not show up until script is executed at 15:26
I've updated the sample scripts above to eliminate creating the same problem for future users.