SQLite FTS4: Standard vs Enhanced Query Syntax

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 anything. You can check which version is supported by your server by querying the PRAGMA compile_options, e.g. for PHP:

try {
  $db = new PDO('sqlite:test.sqlite');
}
catch (PDOException $error) {
  echo $error->getMessage();
}

$data = $db->query("SELECT * FROM pragma_compile_options");
$row = $data->fetch(PDO::FETCH_ASSOC);
while($row) {
  echo $row['compile_options']."<br>";
  $row = $data->fetch(PDO::FETCH_ASSOC);
}

If your output contains SQLITE_ENABLE_FTS3_PARENTHESIS, then the Enhanced Query Syntax is available.

Using the console:

ssh user@myserver
user@myserver's password:
sqlite3 test.sqlite
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA compile_options
DISABLE_DIRSYNC
ENABLE_COLUMN_METADATA
ENABLE_FTS3
ENABLE_RTREE
ENABLE_UNLOCK_NOTIFY
SECURE_DELETE
TEMP_STORE=1
THREADSAFE=1
sqlite>
.quit

In my case, only standard query syntax is available. Hope this helps someone.

Leave a comment

Your email address will not be published. Required fields are marked *