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.
|
1 |
<span class="pln">PRAGMA database_list</span><span class="pun">;</span> |
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:
|
1 |
<span class="lit">0</span><span class="pun">|</span><span class="pln">main</span><span class="pun">|</span><span class="str">/path/</span><span class="pln">to</span><span class="pun">/</span><span class="pln">yourdatabasefile</span><span class="pun">.</span><span class="pln">db</span> |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<span class="com">/** * Tests the SQLite client connection */</span> <span class="kwd">function</span><span class="pln"> testClient </span><span class="pun">()</span> <span class="pun">{</span><span class="pln"> $client </span><span class="pun">=</span> <span class="pun">...</span><span class="pln"> $config </span><span class="pun">=</span> <span class="pun">...</span><span class="pln"> $row </span><span class="pun">=</span><span class="pln"> $client</span><span class="pun">-></span><span class="pln">query</span><span class="pun">(</span><span class="str">"PRAGMA database_list"</span><span class="pun">)-></span><span class="pln">fetchArray</span><span class="pun">(</span><span class="pln">SQLITE3_ASSOC</span><span class="pun">);</span><span class="pln"> $this</span><span class="pun">-></span><span class="pln">assertEquals</span><span class="pun">(</span> <span class="pun">[</span> <span class="str">'seq'</span> <span class="pun">=></span> <span class="lit">0</span><span class="pun">,</span> <span class="str">'name'</span> <span class="pun">=></span> <span class="str">'main'</span><span class="pun">,</span> <span class="str">'file'</span> <span class="pun">=></span><span class="pln"> realpath</span><span class="pun">(</span><span class="pln">$config</span><span class="pun">-></span><span class="pln">databaseFilename</span><span class="pun">)</span> <span class="pun">],</span><span class="pln"> $row</span><span class="pun">,</span> <span class="str">"The query PRAGMA database_list didn't return what we expected: one database opened by the client, the file returned by the database matching our configuration file."</span> <span class="pun">);</span> <span class="pun">}</span> |
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):
|
1 2 3 4 5 6 |
<span class="pln"> $row </span><span class="pun">=</span><span class="pln"> $client</span><span class="pun">-></span><span class="pln">query</span><span class="pun">(</span><span class="str">"PRAGMA database_list"</span><span class="pun">)-></span><span class="pln">fetchArray</span><span class="pun">(</span><span class="pln">SQLITE3_NUM</span><span class="pun">);</span><span class="pln"> $this</span><span class="pun">-></span><span class="pln">assertEquals</span><span class="pun">(</span> <span class="pun">[</span><span class="lit">0</span><span class="pun">,</span> <span class="str">'main'</span><span class="pun">,</span><span class="pln"> realpath</span><span class="pun">(</span><span class="pln">$config</span><span class="pun">-></span><span class="pln">databaseFilename</span><span class="pun">)]</span><span class="pln"> $row</span><span class="pun">,</span> <span class="str">"The query PRAGMA database_list didn't return what we expected: one database opened by the client, the file returned by the database matching our configuration file."</span> <span class="pun">);</span> |
The realpath function is used to get a canonical path.
References:
- PRAGMA statements documentation
- PHPUnit assertEquals test
- PHP realpath($path) documentation
- PHP SQLite3Result::fetchArray($mode) documentation
This post were initially published on Stack Overflow.