CODEWITHSUNDEEP
sqlitefts3
user1228727
user1228727

Reputation: 31

SQLite3 FTS3/FTS4 matchinfo example

I am using the FTS4 module of SQLite3 and have a problem with the Example of the matchinfo() auxiliary function.

At the moment I am just trying to run the matchinfo() example from http://www.sqlite.org/fts3.html#matchinfo and it does not work.

I tried it on Windows 7 with SQLite 3.5.9 (precompiled version), on Ubuntu Linux OS 10.04.4 with SQLite 3.6.22 and on Mac OS 10.6.8 with SQLite 3.7.10 ( both manually compiled version). In all cases the FTS4 module is enabled and everything from the Documentation does work (the offsets and snippet function, full-text-search queries, etc.). On Ubuntu and Mac OS I do not get any result when using the matchinfo() example. On Windows the Command Line gives me strange characters. II tried it in the following ways: Using a PHP script, using NaviCat for SQLite3 and on the command line itself. In every cases it is the same.

This is the PHP code I am using for the example:

<?php

$db = new SQLite3("matchtest.db", SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);
$db->exec("CREATE VIRTUAL TABLE t1 USING fts4(a, b)");
$db->exec("INSERT INTO t1 VALUES('transaction default models default', 'Non transaction reads')");
$db->exec("INSERT INTO t1 VALUES('the default transaction', 'these semantics present')");
$db->exec("INSERT INTO t1 VALUES('single request', 'default data')");


//$result = $db->escapeString("'default transaction \"these semantics\"'");
$result = $db->query("SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'default transaction \"these semantics\"'");

while($row = $result->fetchArray()){
    var_dump($row);
}
?>

I tdidn't found anything regarding this problem on the web. Consequently I do not have any clue how to fix it. Maybe it is because PHP has problems in dealing with BLOBs?

I hope that you guys can help me. Thank you!

Upvotes: 3

Views: 3066

Answers (2)

Simon
Simon

Reputation: 354

The matchinfo() function returns a blob of 32-bit unsigned integers in machine byte-order. You have to convert the binary string with unpack():

$result = $db->query("SELECT MATCHINFO(t1) info1 FROM t1 WHERE t1 MATCH 'default transaction \"these semantics\"'");
while($row = $result->fetchArray(SQLITE3_ASSOC)) {
    // matchinfo returns 32-bit unsigned integers in machine byte-order
    $arrInt32 = unpack('L*', $row['info1']);
    echo implode(' ', $arrInt32)."<br>";
}

output: 3 2 1 3 2 0 1 1 1 2 2 0 1 1 0 0 0 1 1 1

Upvotes: 0

s-t
s-t

Reputation: 405

To see value as terminal output, use like:

select quote(matchinfo(t1)) from t1 where t1 match "default transaction";

This shows value like

X'0200000002000000020000000300000002000000000000000100000001000000010000000200000002000000010000000100000001000000'
X'0200000002000000010000000300000002000000000000000100000001000000010000000200000002000000000000000100000001000000'

Also...
This php function helped me understand the C function listed on SQLite3 documentation a bit better and you can see how the blob is casted to string in this, not array of int.

Upvotes: 1

Related Questions