{"id":1789,"date":"2020-07-03T15:57:45","date_gmt":"2020-07-03T13:57:45","guid":{"rendered":"https:\/\/www.speich.net\/articles\/?p=1789"},"modified":"2024-01-02T13:13:08","modified_gmt":"2024-01-02T12:13:08","slug":"sqlite-fts4-standard-vs-enhanced-query-syntax","status":"publish","type":"post","link":"https:\/\/www.speich.net\/articles\/en\/2020\/07\/03\/sqlite-fts4-standard-vs-enhanced-query-syntax\/","title":{"rendered":"SQLite FTS4: Standard vs Enhanced Query Syntax"},"content":{"rendered":"\n<p>The SQLite documentation talks about two ways to perform full text queries: standard or <a rel=\"noreferrer noopener\" href=\"https:\/\/sqlite.org\/fts3.html#_set_operations_using_the_enhanced_query_syntax\" target=\"_blank\">enhanced query syntax<\/a>. Be aware: which one is available depends on how SQLite was compiled. You might, like me, run into troubles when developing locally using one syntax and then publishing remotely only to find out your query does not return anything. You can check which version is supported by your server by querying the <a rel=\"noreferrer noopener\" href=\"https:\/\/sqlite.org\/pragma.html#pragma_compile_options\" target=\"_blank\">PRAGMA compile_options<\/a>, e.g. for PHP:<\/p>\n\n\n\n<!--more-->\n\n\n\n<pre class=\"wp-block-code language-php\"><code><pre><code class=\"language-php\">try {\n  $db = new PDO('sqlite:test.sqlite');\n}\ncatch (PDOException $error) {\n  echo $error-&gt;getMessage();\n}\n\n$data = $db-&gt;query(\"SELECT <em>*<\/em> FROM pragma_compile_options\");\n$row = $data-&gt;fetch(PDO::<em>FETCH_ASSOC<\/em>);\nwhile($row) {\n  echo $row&#91;'compile_options'].\"&lt;br&gt;\";\n  $row = $data-&gt;fetch(PDO::<em>FETCH_ASSOC<\/em>);\n}<\/code><\/pre><\/code><\/pre>\n\n\n\n<p>If your output contains <code>SQLITE_ENABLE_FTS3_PARENTHESIS<\/code>, then the Enhanced Query Syntax is available.<\/p>\n\n\n\n<p>Using the console:<\/p>\n\n\n\n<pre class=\"wp-block-code language-bash\"><code><pre class=\"command-line\" data-user=\"user\" data-host=\"myserver\" data-output=\"2, 4-17\"><code class=\"language-bash\">$ ssh user@myserver\nuser@myserver's password:\n$ sqlite3\nSQLite version 3.7.17 2013-05-20 00:56:22\nEnter \".help\" for instructions\nEnter SQL statements terminated with a \";\"\nsqlite> PRAGMA compile_options;\nDISABLE_DIRSYNC\nENABLE_COLUMN_METADATA\nENABLE_FTS3\nENABLE_RTREE\nENABLE_UNLOCK_NOTIFY\nSECURE_DELETE\nTEMP_STORE=1\nTHREADSAFE=1\nsqlite>\n.quit\n<\/code><\/pre><\/code><\/pre>\n\n\n\n<p>In my case, only standard query syntax is available. Hope this helps someone.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The SQLite documentation talks about two ways to perform full text queries: standard or enhanced query syntax. Be aware: which one is available depends on how SQLite was compiled. You might, like me, run into troubles when developing locally using one syntax and then publishing remotely only to find out your query does not return &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/www.speich.net\/articles\/en\/2020\/07\/03\/sqlite-fts4-standard-vs-enhanced-query-syntax\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;SQLite FTS4: Standard vs Enhanced Query Syntax&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[285,284,82],"class_list":["post-1789","post","type-post","status-publish","format-standard","hentry","category-php","tag-fts3","tag-fts4","tag-sqlite","entry"],"_links":{"self":[{"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/posts\/1789","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/comments?post=1789"}],"version-history":[{"count":19,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/posts\/1789\/revisions"}],"predecessor-version":[{"id":2407,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/posts\/1789\/revisions\/2407"}],"wp:attachment":[{"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/media?parent=1789"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/categories?post=1789"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.speich.net\/articles\/wp-json\/wp\/v2\/tags?post=1789"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}