How to determine the SQLite filename from an open connection?

If the PHP SQLite3 class doesn’t provide a property or a method to return this information, SQLite engine has a PRAGMA statement to get or set internal data or modify the library behavior.

It will returns a row with seq, name, file fields respectively containing a sequence id, the internal name of the database and the path to the file:

Some details are interesting to note.

  • A SQLite library could use several files.
  • The path will be canonical, and so follows symbolic links.

Unit testing case sample:

To test if the current $client connection file matches $config->databaseFilename:

To test if a query returns the expected result, an efficient method is to compare two arrays, one with expected result, one with the row returned by fetchArray.

By default, fetchArray stores twice each field value, one with a numeric index, one with an associative key. Here we focus on the fields containing the right information, so we use SQLITE3_ASSOC parameter to get an associative content only. If you wish to test the order, use fetchArray(SQLITE3_NUM):

The realpath function is used to get a canonical path.

References:

This post were initially published on Stack Overflow.