Store database version of pihole-FTL in info table

jpgpi250 · August 11, 2020, 6:26am

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.

yubiuser · August 11, 2020, 6:42am

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

yubiuser · August 11, 2020, 11:36am

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

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

DanSchaper · August 11, 2020, 5:48pm

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.

yubiuser · August 11, 2020, 7:49pm

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.

DanSchaper · August 11, 2020, 10:14pm

Which database.

yubiuser · August 12, 2020, 5:54am

pihole-FTL.db

DanSchaper · August 12, 2020, 6:41am

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

jpgpi250 · August 12, 2020, 6:50am

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.

yubiuser · August 12, 2020, 6:52am

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

github.com

pi-hole/FTL/blob/dbd4a696afd35efa5e6c4882c74d9102d9d7d6a2/src/database/common.h#L17

    
      
          *
          *  This file is copyright under the latest version of the EUPL.
          *  Please see LICENSE file for your rights under this license. */
          #ifndef DATABASE_COMMON_H
          #define DATABASE_COMMON_H
          
          
#include "sqlite3.h"
          
          
// Database table "ftl"
          enum ftl_table_props {
          	DB_VERSION,
          	DB_LASTTIMESTAMP,
          	DB_FIRSTCOUNTERTIMESTAMP
          } __attribute__ ((packed));
          
          
// Database table "counters"
          enum counters_table_props {
          	DB_TOTALQUERIES,
          	DB_BLOCKEDQUERIES
          } __attribute__ ((packed));

github.com

pi-hole/FTL/blob/dbd4a696afd35efa5e6c4882c74d9102d9d7d6a2/src/database/common.c#L265

    
      
          	// Try to open the database connection
          	rc = sqlite3_open_v2(FTLfiles.FTL_db, &FTL_db, SQLITE_OPEN_READWRITE, NULL);
          	if( rc != SQLITE_OK ){
          		logg("Cannot initialize (open) long-term database: %s", sqlite3_errstr(rc));
          		database = false;
          		return;
          	}
          
          
	// Test FTL_db version and see if we need to upgrade the database file
          	int dbversion = db_get_FTL_property(DB_VERSION);
          	if(dbversion < 1)
          	{
          		logg("Database not available, please ensure the database is unlocked when starting pihole-FTL !");
          		dbclose();
          
          
		database = false;
          		return;
          	}
          	else
          	{
          		logg("Database version is %i", dbversion);

It's in table ftl id=0

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

Coro · August 12, 2020, 7:10am

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

github.com

pi-hole/FTL/blob/dbd4a696afd35efa5e6c4882c74d9102d9d7d6a2/src/database/common.c#L368


      
          			return;
          		}
          		// Get updated version
          		dbversion = db_get_FTL_property(DB_VERSION);
          	}
          
          	// Update to version 7 if lower
          	if(dbversion < 7)
          	{
          		// Update to version 7: Create message table
          		logg("Updating long-term database to version 7");
          		if(dbquery("ALTER TABLE queries ADD COLUMN additional_info TEXT;") != SQLITE_OK ||
          		   !db_set_FTL_property(DB_VERSION, 7))
          		{
          			logg("Column additional_info not initialized, database not available");
          			dbclose();
          
          			database = false;
          			return;
          		}
          		// Get updated version

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

github.com

pi-hole/FTL/blob/afa0e4c3f05fc5958618c2ce5a745224fc7362e3/src/database/common.c#L412


      
          			return;
          		}
          		// Get updated version
          		dbversion = db_get_FTL_property(DB_VERSION);
          	}
          
          	// Update to version 9 if lower
          	if(dbversion < 9)
          	{
          		// Update to version 9: Add superclients table
          		logg("Updating long-term database to version 9");
          		if(!create_superclients_table())
          		{
          			logg("Superclients table not initialized, database not available");
          			dbclose();
          
          			database = false;
          			return;
          		}
          		// Get updated version
          		dbversion = db_get_FTL_property(DB_VERSION);