{"id":381,"date":"2014-02-09T07:27:34","date_gmt":"2014-02-09T06:27:34","guid":{"rendered":"http:\/\/www.dereckson.be\/blog\/?p=381"},"modified":"2014-11-06T05:35:38","modified_gmt":"2014-11-06T04:35:38","slug":"how-to-determine-the-sqlite-filename-from-an-open-connection","status":"publish","type":"post","link":"https:\/\/www.dereckson.be\/blog\/2014\/02\/09\/how-to-determine-the-sqlite-filename-from-an-open-connection\/","title":{"rendered":"How to determine the SQLite filename from an open connection?"},"content":{"rendered":"<div class=\"post-text\">\n<p>If the PHP SQLite3 class doesn&#8217;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.<\/p>\n<pre class=\"default prettyprint prettyprinted\"><code><span class=\"pln\">PRAGMA database_list<\/span><span class=\"pun\">;<\/span><\/code><\/pre>\n<p>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:<\/p>\n<pre class=\"default prettyprint prettyprinted\"><code><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><\/code><\/pre>\n<p>Some details are interesting to note.<\/p>\n<ul>\n<li>A SQLite library could use several files.<\/li>\n<li>The path will be canonical, and so follows symbolic links.<\/li>\n<\/ul>\n<p><strong>Unit testing case sample:<\/strong><\/p>\n<p>To test if the current $client connection file matches $config-&gt;databaseFilename:<\/p>\n<pre class=\"default prettyprint prettyprinted\"><code><span class=\"com\">\/**\r\n * Tests the SQLite client connection\r\n *\/<\/span>\r\n<span class=\"kwd\">function<\/span><span class=\"pln\"> testClient <\/span><span class=\"pun\">()<\/span> <span class=\"pun\">{<\/span><span class=\"pln\">\r\n    $client <\/span><span class=\"pun\">=<\/span> <span class=\"pun\">...<\/span><span class=\"pln\">\r\n    $config <\/span><span class=\"pun\">=<\/span> <span class=\"pun\">...<\/span><span class=\"pln\">\r\n    $row <\/span><span class=\"pun\">=<\/span><span class=\"pln\"> $client<\/span><span class=\"pun\">-&gt;<\/span><span class=\"pln\">query<\/span><span class=\"pun\">(<\/span><span class=\"str\">\"PRAGMA database_list\"<\/span><span class=\"pun\">)-&gt;<\/span><span class=\"pln\">fetchArray<\/span><span class=\"pun\">(<\/span><span class=\"pln\">SQLITE3_ASSOC<\/span><span class=\"pun\">);<\/span><span class=\"pln\">\r\n    $this<\/span><span class=\"pun\">-&gt;<\/span><span class=\"pln\">assertEquals<\/span><span class=\"pun\">(<\/span>\r\n        <span class=\"pun\">[<\/span>\r\n            <span class=\"str\">'seq'<\/span> <span class=\"pun\">=&gt;<\/span> <span class=\"lit\">0<\/span><span class=\"pun\">,<\/span>\r\n            <span class=\"str\">'name'<\/span> <span class=\"pun\">=&gt;<\/span> <span class=\"str\">'main'<\/span><span class=\"pun\">,<\/span>\r\n            <span class=\"str\">'file'<\/span> <span class=\"pun\">=&gt;<\/span><span class=\"pln\"> realpath<\/span><span class=\"pun\">(<\/span><span class=\"pln\">$config<\/span><span class=\"pun\">-&gt;<\/span><span class=\"pln\">databaseFilename<\/span><span class=\"pun\">)<\/span>\r\n        <span class=\"pun\">],<\/span><span class=\"pln\">\r\n        $row<\/span><span class=\"pun\">,<\/span>\r\n        <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>\r\n    <span class=\"pun\">);<\/span>\r\n<span class=\"pun\">}<\/span><\/code><\/pre>\n<p>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.<\/p>\n<p>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):<\/p>\n<pre class=\"default prettyprint prettyprinted\"><code><span class=\"pln\">    $row <\/span><span class=\"pun\">=<\/span><span class=\"pln\"> $client<\/span><span class=\"pun\">-&gt;<\/span><span class=\"pln\">query<\/span><span class=\"pun\">(<\/span><span class=\"str\">\"PRAGMA database_list\"<\/span><span class=\"pun\">)-&gt;<\/span><span class=\"pln\">fetchArray<\/span><span class=\"pun\">(<\/span><span class=\"pln\">SQLITE3_NUM<\/span><span class=\"pun\">);<\/span><span class=\"pln\">\r\n    $this<\/span><span class=\"pun\">-&gt;<\/span><span class=\"pln\">assertEquals<\/span><span class=\"pun\">(<\/span>\r\n        <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\">-&gt;<\/span><span class=\"pln\">databaseFilename<\/span><span class=\"pun\">)]<\/span><span class=\"pln\">\r\n        $row<\/span><span class=\"pun\">,<\/span>\r\n        <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>\r\n    <span class=\"pun\">);<\/span><\/code><\/pre>\n<p>The <code>realpath<\/code> function is used to get a canonical path.<\/p>\n<p><strong>References:<\/strong><\/p>\n<ul>\n<li><a href=\"http:\/\/www.sqlite.org\/pragma.html\" rel=\"nofollow\">PRAGMA statements documentation<\/a><\/li>\n<li><a href=\"http:\/\/phpunit.de\/manual\/3.7\/en\/writing-tests-for-phpunit.html#writing-tests-for-phpunit.assertions.assertEquals\" rel=\"nofollow\">PHPUnit assertEquals test<\/a><\/li>\n<li><a href=\"http:\/\/www.php.net\/manual\/en\/function.realpath.php\" rel=\"nofollow\">PHP realpath($path) documentation<\/a><\/li>\n<li><a href=\"http:\/\/www.php.net\/manual\/en\/sqlite3result.fetcharray.php\" rel=\"nofollow\">PHP SQLite3Result::fetchArray($mode) documentation<\/a><\/li>\n<\/ul>\n<p><em>This post were <a href=\"http:\/\/stackoverflow.com\/a\/21656493\/1930997\">initially published<\/a> on Stack Overflow.<\/em><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>If the PHP SQLite3 class doesn&#8217;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. PRAGMA database_list; It will returns a row with seq, name, file fields respectively containing a sequence id, the internal name of the [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49],"tags":[210,211],"class_list":["post-381","post","type-post","status-publish","format-standard","hentry","category-dev","tag-sqlite","tag-unit-testing"],"_links":{"self":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts\/381","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/comments?post=381"}],"version-history":[{"count":3,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts\/381\/revisions"}],"predecessor-version":[{"id":383,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts\/381\/revisions\/383"}],"wp:attachment":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/media?parent=381"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/categories?post=381"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/tags?post=381"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}