Store database version of pihole-FTL in info table

New table in pihole-FTL.db, similar to gravity.db info table with database version field.

A lot of users are writing custom SQL scripts, to extract and display (or update) data from the database in a format, not provided in the web interface.

Given the new field in pihole-FTL.db, table queries, field additional_info, which can be used to store any kind of information, it would be useful to check the database version, before displaying or updating database entries.

I second this FR. But you would also need a documentation/table describing which modification was made in which version.

At the moment I wrap my script in

if sqlite3 ${PIHOLE_FTL} "PRAGMA table_info(queries);" |grep -q additional_info ;then

Nice, didn't know you can use github in that way. Thanks

I think jpgpi250 is asking for the equivalent in pihole-FTL.db

I'm not sure there's any value in versioning pihole-FTL.db, it's rather transient in nature and not really storing anything that would be version dependent.

I agree with you that versioning for pihole itself might not be of any value, because the script will (should) be always in concordance with the ability the database provides.
But I see the benefit for users attaching own scripts/code to the database. They would benefit from an easy version string which would let them know the capabilities of the current database they interact with.

Which database.


And what would you expect to be stored as the version in the database of queries?

As far as I know, the structure of pihole-FTL.db hasn't changed since it was first introduced.

In pihole v.5.1.2, a new field (additional_info) has been introduced, so I assume the version number would be 2.

This would be identical to the versioning of the gravity database, the developers need to change the static version number, when a change is made, see here.

Thanks for making me think twice... it's already there :see_no_evil:

It's in table ftl id=0

Bildschirmfoto zu 2020-08-12 08-47-50

I was just going to say exactly this. Looking into the file @yubiuser linked, there is an entire chain of updates to ensure you can update any version of Pi-hole to a newer one (not only 5.1 -> 5.2 but also 4.0 -> 5.2, etc.).

Actually a lot changed (like allowing multiple host names per client by moving them from per-MAC address to per-IP address basis, etc.)


The current version with v5.2 is 7

The version after having merged new/super-clients will be 9: