Converting some C for sqlite

[coffee]

hi,

i need a ranking function for sqlite's fts4 and found a code for C.
is there a translation for this to PB already that i couldn't find? if not, how is the
is the following C code translated, especially the matchinfo and apVal parts?
thank you for any help in advance.

Code: Select all

#include <math.h>
#include <sqlite3ext.h>
#include <assert.h>
SQLITE_EXTENSION_INIT1

static void okapi_bm25(sqlite3_context *pCtx, int nVal, sqlite3_value **apVal) {
    assert(sizeof(int) == 4);

    unsigned int *matchinfo = (unsigned int *)sqlite3_value_blob(apVal[0]);
    int searchTextCol = sqlite3_value_int(apVal[1]);

    double K1 = ((nVal >= 3) ? sqlite3_value_double(apVal[2]) : 1.2);
    double B = ((nVal >= 4) ? sqlite3_value_double(apVal[3]) : 0.75);

    int P_OFFSET = 0;
    int C_OFFSET = 1;
    int N_OFFSET = 2;
    int A_OFFSET = 3;

    int termCount = matchinfo[P_OFFSET];
    int colCount = matchinfo[C_OFFSET];

    int L_OFFSET = (A_OFFSET + colCount);
    int X_OFFSET = (L_OFFSET + colCount);

    double totalDocs = matchinfo[N_OFFSET];
    double avgLength = matchinfo[A_OFFSET + searchTextCol];
    double docLength = matchinfo[L_OFFSET + searchTextCol];

    double sum = 0.0;

    for (int i = 0; i < termCount; i++) {
        int currentX = X_OFFSET + (3 * searchTextCol * (i + 1));
        double termFrequency = matchinfo[currentX];
        double docsWithTerm = matchinfo[currentX + 2];
        double idf = log(
            (totalDocs - docsWithTerm + 0.5) /
            (docsWithTerm + 0.5)
        );

        double rightSide = (
            (termFrequency * (K1 + 1)) /
            (termFrequency + (K1 * (1 - B + (B * (docLength / avgLength)))))
        );
        sum += (idf * rightSide);
    }
    sqlite3_result_double(pCtx, sum);
}

int sqlite3_extension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
    SQLITE_EXTENSION_INIT2(pApi)
    sqlite3_create_function(db, "okapi_bm25", -1, SQLITE_ANY, 0, okapi_bm25, 0, 0);
    return 0;
}

coffee

[coffee]

I translated it to the below, but it doesn't work. can't figure out what goes wrong.

Code: Select all

ImportC "sqlite3.lib"         ; USE THIS FOR WINDOWS. The line below is for Linux
; ImportC "$PUREBASIC_HOME/purelibraries/linux/libraries/libpbsqlite3.a"
  sqlite3_enable_load_extension(*db, onoff.l)
  sqlite3_load_extension(*db, zFile.s, zProc.s, *pzErrMsg)
  sqlite3_create_function(*DB, zFunctionName.p-utf8, nArg.i, eTextRep.i, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_value_double(*double)
  sqlite3_result_double(*context, double.d)
  sqlite3_result_null(*context)
  sqlite3_value_blob(*sqlite3_value)
  sqlite3_value_int(*sqlite3_value)
  sqlite3_value_bytes(*sqlite3_value)
  sqlite3_result_error(*context, zErrText.p-utf8, num.l)
EndImport

#SQLITE_OK = 0
#SQLITE_UTF8 = 1          ; IMP: R-37514-35566
#SQLITE_UTF16LE = 2       ; IMP: R-03371-37637
#SQLITE_UTF16BE = 3       ; IMP: R-51971-34154
#SQLITE_UTF16 = 4         ; Use native byte order
#SQLITE_ANY = 5           ; Deprecated
#SQLITE_UTF16_ALIGNED = 8 ; sqlite3_create_collation only
#P_OFFSET = 0
#C_OFFSET = 4
#N_OFFSET = 8
#A_OFFSET = 12

ProcedureC okapi_bm25(*pCtx, nVal.l, *apVal)
  Protected.d sum = 0.0, K1, B, totalDocs, avgLength, docLength  
  Protected.l i, currentX, searchTextCol, termCount, colCount, L_OFFSET, X_OFFSET
  Protected.d termFrequency, docsWithTerm, idf, rightSide
  Protected *matchinfo
  ;;;;;; assert(SizeOf(int) == 4)
  *matchinfo    = sqlite3_value_blob(PeekI(*apVal+#P_OFFSET))
  searchTextCol = sqlite3_value_int(*apVal+#C_OFFSET)
  If searchTextCol > 0
    searchTextCol = searchTextCol
  EndIf
  If nVal >= 3
    K1 = sqlite3_value_double(*apVal+#N_OFFSET)
  Else
    K1 = 1.2
  EndIf
  If nVal >= 4
    B = sqlite3_value_double(*apVal+#A_OFFSET)
  Else
    B = 0.75
  EndIf
  termCount = PeekL(*matchinfo+#P_OFFSET)
  colCount  = PeekL(*matchinfo+#C_OFFSET)
  L_OFFSET  = (#A_OFFSET + colCount)
  X_OFFSET  = (L_OFFSET  + colCount)
  totalDocs = PeekL(*matchinfo + #N_OFFSET)
  avgLength = PeekL(*matchinfo + (#A_OFFSET + searchTextCol))
  docLength = PeekL(*matchinfo + (L_OFFSET + searchTextCol))
  For i = 0 To termCount-1
    currentX      = X_OFFSET + (3 * searchTextCol * (i + 1))
    termFrequency = PeekL(*matchinfo + currentX)
    docsWithTerm  = PeekL(*matchinfo + (currentX + 2))
    idf           = Log((totalDocs - docsWithTerm + 0.5) / (docsWithTerm + 0.5))
    rightSide     = ((termFrequency * (K1 + 1)) / (termFrequency + (K1 * (1 - B + (B * (docLength / avgLength))))))
    sum + (idf * rightSide)
  Next
  If sum > 0.0
    sum = sum
  EndIf
  sqlite3_result_double(*pCtx, sum)
EndProcedure

my sql is:

Code: Select all

"SELECT rowid, * FROM booksearch WHERE booksearch MATCH '"+search+"' ORDER BY okapi_bm25(matchinfo(booksearch, 'pcnalx'), 0) DESC"

an idea anyone?


coffee

[infratec]

Without data it's hard to test.

Is your general program like this:

Code: Select all

ImportC "sqlite3.lib"         ; USE THIS FOR WINDOWS. The line below is for Linux
                              ; ImportC "$PUREBASIC_HOME/purelibraries/linux/libraries/libpbsqlite3.a"
  sqlite3_create_function(*DB, zFunctionName.p-utf8, nArg.i, eTextRep.i, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_value_double(*double)
  sqlite3_result_double(*context, double.d)
  sqlite3_result_null(*context)
  sqlite3_value_blob(*sqlite3_value)
  sqlite3_value_int(*sqlite3_value)
  sqlite3_value_bytes(*sqlite3_value)
  sqlite3_result_error(*context, zErrText.p-utf8, num.l)
EndImport

#SQLITE_OK = 0
#SQLITE_UTF8 = 1          ; IMP: R-37514-35566
#SQLITE_UTF16LE = 2       ; IMP: R-03371-37637
#SQLITE_UTF16BE = 3       ; IMP: R-51971-34154
#SQLITE_UTF16 = 4         ; Use native byte order
#SQLITE_ANY = 5           ; Deprecated
#SQLITE_UTF16_ALIGNED = 8 ; sqlite3_create_collation only

#P_OFFSET = 0
#C_OFFSET = 1
#N_OFFSET = 2
#A_OFFSET = 3


Structure LongArrayStructure
  L.l[0]
EndStructure


ProcedureC okapi_bm25(*pCtx, nVal.i, *apVal.LongArrayStructure)
  
  Protected.d sum, K1, B, totalDocs, avgLength, docLength 
  Protected.l i, currentX, searchTextCol, termCount, colCount, L_OFFSET, X_OFFSET
  Protected.d termFrequency, docsWithTerm, idf, rightSide
  Protected *matchinfo.LongArrayStructure
  
  
  ;;;;;; assert(SizeOf(int) == 4)
  *matchinfo = sqlite3_value_blob(*apVal\L[0])
  
  searchTextCol = sqlite3_value_int(*apVal\L[1])
  
  If nVal >= 3
    K1 = sqlite3_value_double(*apVal + #N_OFFSET)
  Else
    K1 = 1.2
  EndIf
  
  If nVal >= 4
    B = sqlite3_value_double(*apVal + #A_OFFSET)
  Else
    B = 0.75
  EndIf
  
  termCount = *matchinfo\L[#P_OFFSET]
  colCount  = *matchinfo\L[#C_OFFSET]
  L_OFFSET  = #A_OFFSET + colCount
  X_OFFSET  = L_OFFSET  + colCount
  totalDocs = *matchinfo\L[#N_OFFSET]
  avgLength = *matchinfo\L[#A_OFFSET + searchTextCol]
  docLength = *matchinfo\L[L_OFFSET + searchTextCol]
  
  For i = 0 To termCount - 1
    currentX      = X_OFFSET + (3 * searchTextCol * (i + 1))
    termFrequency = *matchinfo\L[currentX]
    docsWithTerm  = *matchinfo\L[currentX + 2]
    idf           = Log((totalDocs - docsWithTerm + 0.5) / (docsWithTerm + 0.5))
    rightSide     = ((termFrequency * (K1 + 1)) / (termFrequency + (K1 * (1 - B + (B * (docLength / avgLength))))))
    sum + (idf * rightSide)
  Next
  
  sqlite3_result_double(*pCtx, sum)
  
EndProcedure



UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "")
If DB
  
  DatabaseUpdate(DB, "CREATE TABLE booksearch (booksearch TEXT, name CHAR(50), weight INT)")
  
  DatabaseUpdate(DB, "INSERT INTO booksearch VALUES ('bla', 'bla', 3)")
  DatabaseUpdate(DB, "INSERT INTO booksearch VALUES ('blu', 'blu', 4)")
  
  
  If sqlite3_create_function(DatabaseID(DB), "okapi_bm25", 2, #SQLITE_ANY, #Null, @okapi_bm25(), #Null, #Null) = #SQLITE_OK
    
    Debug "Created"
    
    SQL$ = "SELECT rowid, * FROM booksearch WHERE booksearch MATCH 'bla' ORDER BY okapi_bm25(matchinfo(booksearch, 'pcnalx'), 0) DESC"
    If DatabaseQuery(DB, SQL$)
      While NextDatabaseRow(DB)
        Debug GetDatabaseString(DB, 0)
        Debug GetDatabaseString(DB, 1)
      Wend
      FinishDatabaseQuery(DB)
    Else
      Debug DatabaseError()
    EndIf
    
  EndIf
  
EndIf

Bernd

[coffee]

hi bernd and thanks for the code.

unfortunately the program and the data are too big to give short examples.
your demo is great but, it fails in

searchTextCol = sqlite3_value_int(*apVal\L[1])

with an exception. i created the search table kind of the one below (short, edited) and search on it.

Code: Select all

  createstring = "CREATE VIRTUAL TABLE booksearch Using fts4("
  createstring = createstring + " TITLE,"
  createstring = createstring + " SUBTITLE,"
  createstring = createstring + " SYNOPSIS,"
  createstring = createstring + " JOURNAL,"
  createstring = createstring + " tokenize=porter);"

i must see what i can do with your demo, definitely a super idea with the struct. hope to get it to work.

coffee

[infratec]

Unfortunately I never reach the function.

But can you try this:

Code: Select all

Structure QuadArrayStructure
  Q.q[0]
EndStructure

Structure LongArrayStructure
  L.l[0]
EndStructure


ProcedureC okapi_bm25(*pCtx, nVal.i, *apVal.QuadArrayStructure)
 
  Protected.d sum, K1, B, totalDocs, avgLength, docLength
  Protected.l i, currentX, searchTextCol, termCount, colCount, L_OFFSET, X_OFFSET
  Protected.d termFrequency, docsWithTerm, idf, rightSide
  Protected *matchinfo.LongArrayStructure
 
 
  ;;;;;; assert(SizeOf(int) == 4)
  Debug *apVal\Q[0]
  *matchinfo = sqlite3_value_blob(*apVal\Q[0])
 
  searchTextCol = sqlite3_value_int(*apVal\Q[1])
 
  If nVal >= 3
    K1 = sqlite3_value_double(*apVal\Q[2])
  Else
    K1 = 1.2
  EndIf
 
  If nVal >= 4
    B = sqlite3_value_double(*apVal\Q[3])
  Else
    B = 0.75
  EndIf

Because in an older example from me, the offset was 8
http://www.purebasic.fr/english/viewtop ... 6&p=460633

Bernd

[coffee]

if modified:

Code: Select all

ImportC "sqlite3.lib"         ; USE THIS FOR WINDOWS. The line below is for Linux
                              ; ImportC "$PUREBASIC_HOME/purelibraries/linux/libraries/libpbsqlite3.a"
  sqlite3_create_function(*DB, zFunctionName.p-utf8, nArg.i, eTextRep.i, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_value_double(*double)
  sqlite3_result_double(*context, double.d)
  sqlite3_result_null(*context)
  sqlite3_value_blob(*sqlite3_value)
  sqlite3_value_int(*sqlite3_value)
  sqlite3_value_bytes(*sqlite3_value)
  sqlite3_result_error(*context, zErrText.p-utf8, num.l)
EndImport

#SQLITE_OK = 0
#SQLITE_UTF8 = 1          ; IMP: R-37514-35566
#SQLITE_UTF16LE = 2       ; IMP: R-03371-37637
#SQLITE_UTF16BE = 3       ; IMP: R-51971-34154
#SQLITE_UTF16 = 4         ; Use native byte order
#SQLITE_ANY = 5           ; Deprecated
#SQLITE_UTF16_ALIGNED = 8 ; sqlite3_create_collation only

#P_OFFSET = 0
#C_OFFSET = 1
#N_OFFSET = 2
#A_OFFSET = 3


Structure LongArrayStructure
  L.l[0]
EndStructure


ProcedureC okapi_bm25(*pCtx, nVal.i, *apVal.LongArrayStructure)
  Protected.d sum, K1, B, totalDocs, avgLength, docLength 
  Protected.l i, currentX, searchTextCol, termCount, colCount, L_OFFSET, X_OFFSET
  Protected.d termFrequency, docsWithTerm, idf, rightSide
  Protected *matchinfo.LongArrayStructure
  ;;;;;; assert(SizeOf(int) == 4)
  *matchinfo = sqlite3_value_blob(*apVal\L[0])
;  searchTextCol = sqlite3_value_int(*apVal\L[1])
  searchTextCol = (*apVal\L[1])
  If nVal >= 3
    K1 = sqlite3_value_double(*apVal + #N_OFFSET)
  Else
    K1 = 1.2
  EndIf
  If nVal >= 4
    B = sqlite3_value_double(*apVal + #A_OFFSET)
  Else
    B = 0.75
  EndIf
  termCount = *matchinfo\L[#P_OFFSET]
  colCount  = *matchinfo\L[#C_OFFSET]
  L_OFFSET  = #A_OFFSET + colCount
  X_OFFSET  = L_OFFSET  + colCount
  totalDocs = *matchinfo\L[#N_OFFSET]
  avgLength = *matchinfo\L[#A_OFFSET + searchTextCol]
  docLength = *matchinfo\L[L_OFFSET + searchTextCol]
  For i = 0 To termCount - 1
    currentX      = X_OFFSET + (3 * searchTextCol * (i + 1))
    termFrequency = *matchinfo\L[currentX]
    docsWithTerm  = *matchinfo\L[currentX + 2]
    idf           = Log((totalDocs - docsWithTerm + 0.5) / (docsWithTerm + 0.5))
    rightSide     = ((termFrequency * (K1 + 1)) / (termFrequency + (K1 * (1 - B + (B * (docLength / avgLength))))))
    sum + (idf * rightSide)
  Next
  sqlite3_result_double(*pCtx, sum)
EndProcedure

; createstring$ = "SELECT docid, * FROM booksearch JOIN (SELECT docid, okapi_bm25(matchinfo(booksearch, 'pcxnal'), 10, 1) AS rank"

UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "")
If DB
  x=DatabaseUpdate(DB, "CREATE VIRTUAL TABLE booksearch Using fts4(bookse TEXT, name CHAR(50), name1 CHAR(50), weight INT, tokenize=porter);")
  x=DatabaseUpdate(DB, "INSERT INTO booksearch VALUES ('bla', 'bla', 'blu', 3)")
  x=DatabaseUpdate(DB, "INSERT INTO booksearch VALUES ('blu', 'blu', 'blu', 4)")
  x=DatabaseUpdate(DB, "INSERT INTO booksearch VALUES ('bla', 'bla', 'bla', 5)")
  
  If sqlite3_create_function(DatabaseID(DB), "okapi_bm25", 2, #SQLITE_ANY, #Null, @okapi_bm25(), #Null, #Null) = #SQLITE_OK
    Debug "Created"
    
    SQL$ = "SELECT rowid, * FROM booksearch WHERE booksearch MATCH 'bla' ORDER BY okapi_bm25(matchinfo(booksearch, 'pcnalx'), 0) DESC"

    If DatabaseQuery(DB, SQL$)
      While NextDatabaseRow(DB)
        Debug GetDatabaseString(DB, 0)
        Debug GetDatabaseString(DB, 1)
        Debug GetDatabaseString(DB, 2)
        Debug GetDatabaseString(DB, 3)
      Wend
      FinishDatabaseQuery(DB)
    Else
      Debug DatabaseError()
    EndIf    
  EndIf
EndIf

one would expect, that it orders like
('bla', 'bla', 'bla', 5)")
('bla', 'bla', 'blu', 3)")

but it doesn't. it doesn't do so.

forgot, the original code is at:
https://github.com/neozenith/sqlite-okapi-bm25

[infratec]

Hi,

this works: (x86 and x64)

Code: Select all

ImportC "sqlite3.lib"         ; USE THIS FOR WINDOWS. The line below is for Linux
                              ; ImportC "$PUREBASIC_HOME/purelibraries/linux/libraries/libpbsqlite3.a"
  sqlite3_create_function(*DB, zFunctionName.p-utf8, nArg.i, eTextRep.i, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_value_bytes.a(*sqlite3_value)
  sqlite3_value_int.i(*sqlite3_value)
  sqlite3_value_double.d(*sqlite3_value)
  sqlite3_value_blob.i(*sqlite3_value)
  sqlite3_result_double(*context, double.d)
  sqlite3_result_null(*context)
  sqlite3_result_error(*context, zErrText.p-utf8, num.l)
EndImport

#SQLITE_OK = 0
#SQLITE_UTF8 = 1          ; IMP: R-37514-35566
#SQLITE_UTF16LE = 2       ; IMP: R-03371-37637
#SQLITE_UTF16BE = 3       ; IMP: R-51971-34154
#SQLITE_UTF16 = 4         ; Use native byte order
#SQLITE_ANY = 5           ; Deprecated
#SQLITE_UTF16_ALIGNED = 8 ; sqlite3_create_collation only


Structure LongArrayStructure
  ptr.l[0]
EndStructure


Structure IntegerArrayStructure
  ptr.i[0]
EndStructure


ProcedureC okapi_bm25(*pCtx, nVal.i, *apVal.IntegerArrayStructure)
 
  Protected.d sum, K1, B, totalDocs, avgLength, docLength
  Protected.i i, currentX, searchTextColumn, termCount, colCount
  Protected.d termFrequency, docsWithTerm, idf, rightSide
  Protected *matchinfo.LongArrayStructure
  Protected.i P_OFFSET, C_OFFSET, X_OFFSET, A_OFFSET, L_OFFSET, N_OFFSET
 
 
  ;;;;;; assert(SizeOf(int) == 4)
  ; Parameter 1 is the result of matchinfo()
  *matchinfo = sqlite3_value_blob(*apVal\ptr[0])
  
  ; Parameter 2 is the column in which is searched (starting by 0)
  searchTextColumn = sqlite3_value_int(*apVal\ptr[1])
  
  ; Parameter 3 is optional
  If nVal >= 3
    K1 = sqlite3_value_double(*apVal\ptr[2])
  Else
    K1 = 1.2
  EndIf
  
  ; Parameter 4 is optional
  If nVal >= 4
    B = sqlite3_value_double(*apVal\ptr[3])
  Else
    B = 0.75
  EndIf
  
  P_OFFSET = 0
  C_OFFSET = 1
  X_OFFSET = 2
  
  termCount = *matchinfo\ptr[P_OFFSET]
  colCount  = *matchinfo\ptr[C_OFFSET]
  
  N_OFFSET = X_OFFSET + 3 * termCount * colCount
  A_OFFSET = N_OFFSET + 1
  L_OFFSET = (A_OFFSET + colCount)
 
  totalDocs = *matchinfo\ptr[N_OFFSET]
  avgLength = *matchinfo\ptr[A_OFFSET + searchTextColumn]
  docLength = *matchinfo\ptr[L_OFFSET + searchTextColumn]
  
  For i = 0 To termCount - 1
    currentX      = X_OFFSET + (3 * searchTextCol * (i + 1))
    termFrequency = *matchinfo\ptr[currentX]
    docsWithTerm  = *matchinfo\ptr[currentX + 2]
    idf           = Log((totalDocs - docsWithTerm + 0.5) / (docsWithTerm + 0.5))
    rightSide     = ((termFrequency * (K1 + 1)) / (termFrequency + (K1 * (1 - B + (B * (docLength / avgLength))))))
    sum + (idf * rightSide)
  Next
  
  sqlite3_result_double(*pCtx, sum)
  
EndProcedure



UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "")
If DB
  DatabaseUpdate(DB, "CREATE VIRTUAL TABLE booksearch Using fts4(title TEXT, tokenize=porter)")
  DatabaseUpdate(DB, "INSERT INTO booksearch VALUES ('bla bla blu')")
  DatabaseUpdate(DB, "INSERT INTO booksearch VALUES ('blu blu bla')")
  DatabaseUpdate(DB, "INSERT INTO booksearch VALUES ('blu blu blu')")
  DatabaseUpdate(DB, "INSERT INTO booksearch VALUES ('bla bla bla')")
  
  If sqlite3_create_function(DatabaseID(DB), "okapi_bm25", -1, #SQLITE_ANY, #Null, @okapi_bm25(), #Null, #Null) = #SQLITE_OK
    
    SQL$ = "SELECT okapi_bm25(matchinfo(booksearch, 'pcxnal'), 0) as rank, rowid, title FROM booksearch WHERE booksearch MATCH 'bla' ORDER BY rank DESC"
    
    If DatabaseQuery(DB, SQL$)
      While NextDatabaseRow(DB)
        Debug GetDatabaseString(DB, 0) + " : " + GetDatabaseString(DB, 1) + " -> " + GetDatabaseString(DB, 2)
      Wend
      FinishDatabaseQuery(DB)
    Else
      Debug DatabaseError()
    EndIf   
  EndIf
  CloseDatabase(DB)
EndIf

I used the original sourcecode for this.
At the ImportC declaration I added the returntypes of the functions, else you get garbage.
The order of the matchinfo stuff is important !
I had to change it to 'pcxnal' else I get NaN as sum.
It has to corespond to the values of

pcxnal:

P_OFFSET = 0
C_OFFSET = 1
X_OFFSET = 2
...

Bernd

[coffee]

wow this is great bernd.
when i compile (x64) and run this, i always get an exception on

searchTextCol = sqlite3_value_int(*apVal\ptr[1]) ;;;;; *apVal\ptr[1] = 0

this kills me, i have copied your original - not changed anything.
can that have anything to do with 64 bit?

[infratec]

Hi,

try to change ptr.l to ptr.i
And yes, it's a 64bit thing, because with my PB 5.61 x86 it works.

(I did it now in the listing above)

[coffee]

yup, it works, - but

for some search word the

docLength = *matchinfo\ptr[L_OFFSET + searchTextCol]

[ERROR] Invalid memory access. (Read error at address 235380924)

fails.

it seems, that a change to

sqlite3_result_double(*pCtx, Abs(sum))

betters the search results.

coffee

[coffee]

and another problem arises

totalDocs = *matchinfo\ptr[N_OFFSET]
[ERROR] Invalid memory access. (Read error at address 1069617276)

it happens when there are searches in 20000 research papers. sometime it takes 5 searches and at other times it happens after 10 searches or even at the third. it happens sooner when one uses a lot of search terms such as:

t-rex AND skull AND lateral AND marrow

[Fangbeast]

Just a tiny thing..If you are using PB 5.5x, you can shorten:

Code: Select all

  createstring = "CREATE VIRTUAL TABLE booksearch Using fts4("
  createstring = createstring + " TITLE,"
  createstring = createstring + " SUBTITLE,"
  createstring = createstring + " SYNOPSIS,"
  createstring = createstring + " JOURNAL,"
  createstring = createstring + " tokenize=porter);"

To (Using line continuation character):

Code: Select all

createstring = "CREATE VIRTUAL TABLE booksearch Using fts4(" + 
" TITLE,"     + 
" SUBTITLE,"  + 
" SYNOPSIS,"  + 
" JOURNAL,"   + 
" tokenize=porter);"

Might be a little easier to read.

[infratec]

Hi,

updated my listing above. Works now in x64 and x86.
The matchinfo uses in both cases long values.
So only the parameter array has to change the size, which is automatically done with integers.

Bernd

[coffee]

hi,

@Fangbeast: thanks for the tip, i keep forgetting those things.

@infratec:
thanks that is great.
there seems to be a real problem so with that function so. i spend a long time testing an the above crashes come sporadically and unpredictably. the "Invalid memory access" persists and after a couple hours testing, i could not find a pattern. appending a space or a chr($0d) to search string seemed at first to better the situation, but it didn't.

for now, these crashes that occur sooner or later or directly the first search render the search function unusable.

Code: Select all

  createstring = "CREATE VIRTUAL TABLE booksearch Using fts4("
  createstring = createstring + " DOI,"
  createstring = createstring + " TITLE,"
  createstring = createstring + " SUBTITLE,"
  createstring = createstring + " COMMENTS,"
  createstring = createstring + " BOOKCONTENT,"
  createstring = createstring + " SYNOPSIS,"
  createstring = createstring + " JOURNAL,"
  createstring = createstring + " tokenize=porter);"
  ret = DatabaseUpdate(databasehandle1, createstring)

this is the table i want to search in - in every field. is the sql to this false?

Code: Select all

SELECT okapi_bm25(matchinfo(booksearch, 'pcnalx'), 0) as rank, rowid, * FROM booksearch WHERE booksearch MATCH '"+search+"' ORDER BY rank DESC

coffee

__________________________________________________
Code tags added
09.11.2017
RSBasic

[infratec]

Hi,

I think you missunderstood the procedure okapi_bm25()

The second parameter specifies the column in which you search.
In your case: ,0 checks only the column DOI of your table.

Bernd