PHP and SQLite FTS4: How to process the matchinfo function part I

Reading the binary output from the matchinfo() function

SQLite’s MATCHINFO() function provides metrics that are useful for filtering or sorting results of a query according to relevance. The function returns a binary string representing a variable number of 32-bit unsigned integers in machine byte-order. It can be read into an array of integers using the PHP unpack() function passing 'L*' as the format parameter.

The code below re-creates the example and output from the FTS3/FTS4 documentation. First of all, we construct the sample.sqlite database file and create the virtual FTS4 table t1 with the two columns a and b. Secondly, we populate the table with data from the example:

// create the SQLite database example.sqlite
try {
    $db = new PDO('sqlite:example.sqlite');
} catch (PDOException $error) {
    echo $error->getMessage();
}

// populate the database with data
try {
    $db->exec("CREATE VIRTUAL TABLE t1 USING fts4(a, b);
        INSERT INTO t1 VALUES('transaction default models default', 'Non transaction reads');
        INSERT INTO t1 VALUES('the default transaction', 'these semantics present');
        INSERT INTO t1 VALUES('single request', 'default data');");

} catch (PDOException $error) {
    echo $error->getMessage().'
';
}

Next, we query the data in the table t1 using the matchinfo(t1, 'pcx') function with the default parameters 'pcx' for the second function argument (shown for clarity, can be omitted). Then we print the result of the query in a while loop using unpack('L*', $row['info1']) to convert the binary blob into an array of integers $arrInt32. Finally, we format the array of integers with our function formatOutput($arrInt32) in groups of three using the modulo %:

// query the database using the matchinfo() function
$data = $db->query("SELECT MATCHINFO(t1, 'pcx') info1 FROM t1 WHERE t1 MATCH 'default transaction \"these semantics\"'");

// convert, format and print the output of the query
while ($row = $data->fetch(PDO::FETCH_ASSOC)) {
    // matchinfo returns a blob of 32-bit unsigned integers in machine byte-order
    // note: returned array starts with index 1 and not 0
    $arrInt32 = unpack('L*', $row['info1']);
    echo formatOutput($arrInt32)."
";
}

/**
 * Formats the array of integers with spaces.
 * Formats the output with spaces according to the example in the SQLite FTS documentation.
 * @param array $arrInt32
 */
function formatOutput($arrInt32)
{
    foreach ($arrInt32 as $i => $int) {
        if ($i % 3 === 0) {
            echo ' ';
        }
        echo $int;
    }
}

Printed output: 32 132 011 122 011 000 111

Find out what these integers represent in Part II: Understanding the output from the matchinfo() function