Devart Forums

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;

Code: Select all

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

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;