Example showing how to work with matchinfo (SQLite3)

[RaelB]

Hi,

Is it possible to show an example of how to work with the blob data returned by SQLite matchinfo function (http://www.sqlite.org/fts3.html)?

I found this snippet from mORMot framework, but could not figure out how to use it with UniDAC user function signature.

Code: Select all

 // from SynSQLite3.pas 

  PFTSMatchInfo = ^TFTSMatchInfo;
  /// map the matchinfo function returned BLOB value
  // - i.e. the default 'pcx' layout, for both FTS3 and FTS4
  // - see http://www.sqlite.org/fts3.html#matchinfo
  // - used for the FTS3/FTS4 ranking of results by TSQLRest.FTSMatch method
  // and the internal RANK() function as proposed in
  // http://www.sqlite.org/fts3.html#appendix_a
  TFTSMatchInfo = record
    nPhrase: integer;
    nCol: integer;
    hits: array[1..9] of record
      this_row: integer;
      all_rows: integer;
      docs_with_hits: integer;
    end;
  end;

procedure InternalRank(Context: TSQLite3FunctionContext;
  argc: integer; var argv: TSQLite3ValueArray); {$ifndef SQLITE3_FASTCALL}cdecl;{$endif}
// supplies the same "RANK" internal function as proposed in
// http://www.sqlite.org/fts3.html#appendix_a
var MI: PFTSMatchInfo;
    p,c: integer;
    score: Double;
begin
  if argc>=1 then begin
    MI := sqlite3.value_blob(argv[0]);
    if argc=MI^.nCol+1 then begin
      score := 0;
      for p := 1 to MI^.nPhrase do
        for c := 1 to MI^.nCol do
        with MI^.hits[c] do 
          if this_row>0 then
            score := score+(this_row/all_rows)*sqlite3.value_double(argv[c]);
      sqlite3.result_double(Context,score);
      exit; // success: don't call sqlite3.result_error()
    end;
  end;
  ErrorWrongNumberOfArgs(Context);
end;

Essentially, assuming user is searching for one term in fts table, I would like to get how many occurrences there are for current row, e.g.:

Code: Select all

SELECT docid, rank(matchinfo(table_fts)) FROM table_fts WHERE table_fts MATCH 'myterm'

Can you provide some assistance?

Thank you
Rael

[AlexP]

Hello,

Direct doesn't support this functionality. If you are using a library with support for these functions, you can use them in UniDAC as well. You can also use third-party Extensions that support this functionality both in Direct mode and when working with the standard library.

[RaelB]

Hi,

Sorry, I think I was not so clear in my previous post. Let me try to be more clear.

I am using sqlite provider - not direct mode.

As you know you can create a user defined sqlite function using:
TLiteUtils.RegisterFunction

The function must have the following params:
function func_name(InValues: array of Variant): Variant;

If the query is passing the function a BLOB param, how does one go about using it? I.e get a pointer to the blob,know the blob size and use the data? Can you give an example of this?

Thanks
Rael

[AlexP]

The Variant array is suitable for a user function. To define the data type of each element, you can use the VarType method. The data type will depend on the contents of the BLOB field. Below is a sample of working with BLOB data in a user function.

Code: Select all

function AsString(InValues: array of Variant): Variant;
var
  vt: TVarType;
begin
  if VarIsArray(InValues[0]) then begin
    ShowMessage('VarIsArray') ;
  end
  else begin
    vt := VarType(InValues[0]);
    case vt of
      varNull: ShowMessage('varNull');
      varInt64: ShowMessage('varInt64');
      varDouble: ShowMessage('varDouble');
      varString: ShowMessage('varString');
    end;
  end;

end;

procedure TForm14.Button1Click(Sender: TObject);
begin
  UniConnection1.Connect;
  UniConnection1.ExecSQL('CREATE TABLE T_TEST(F_ID INTEGER PRYMARY KEY, F_BLOB BLOB)');
  UniQuery1.SQL.Text := 'INSERT INTO T_TEST VALUES(:F_ID, :F_BLOB)';
  UniQuery1.ParamByName('F_ID').AsInteger := 1;
  UniQuery1.ParamByName('F_BLOB').Clear;
  UniQuery1.Execute;
  UniQuery1.ParamByName('F_ID').AsInteger := 2;
  UniQuery1.ParamByName('F_BLOB').AsInteger := 1;
  UniQuery1.Execute;
  UniQuery1.ParamByName('F_ID').AsInteger := 3;
  UniQuery1.ParamByName('F_BLOB').AsFloat := 1.2;
  UniQuery1.Execute;
  UniQuery1.ParamByName('F_ID').AsInteger := 4;
  UniQuery1.ParamByName('F_BLOB').AsString := 'sdfasdfasdfasdf';
  UniQuery1.Execute;
  UniQuery1.ParamByName('F_ID').AsInteger := 5;
  UniQuery1.ParamByName('F_BLOB').LoadFromFile('d:\2.jpg', ftBlob);
  UniQuery1.Execute;

  TLiteUtils.RegisterFunction(UniConnection1, 'AsString', 1, AsString);

  UniQuery1.SQL.Text := 'SELECT F_ID, AsString(F_BLOB) FROM  T_TEST';

  UniQuery1.Open;
end;