Unable to create virtual table in sqlite3 in electron app

Question

I am using my own sqlite3.dll in electron. So i need to register all the functions of sqlite3 with 'ffi' that I need to use. It was working pretty well till now. Ex for sqlite3_exec, i've registered this in ffi as follows

var lib = ffi.library('sqlite3',sqlite3_exec': ['int', [sqlite3Ptr, 'string', 'pointer', 'pointer', stringPtr]],)

and while using i use it as follows:

var res = lib.sqlite3_exec(dbHandle, query, null, null, null);

It works perfect.

Now I need to create virtual table. So when i try to create virtual table with the same code, I get error. Did a bit of research and got to know that I need to register a function with ffi that will allow me to create virtual table.

the function is as follows:

'sqlite3_create_module': ['int',[sqlite3Ptr, 'string', 'pointer', 'pointer']]

This is basically C syntax, I need to pass address of following module as the third parameter

static sqlite3_module module = { 
    .iVersion = 1, 
    .xCreate = test_CreateConnect, 
    .xConnect = test_CreateConnect, 
    .xBestIndex = test_BestIndex, 
    .xDisconnect = test_DisconnectDestroy, 
    .xDestroy = test_DisconnectDestroy, 
    .xOpen = test_Open, 
    .xClose = test_Close, 
    .xFilter = test_Filter, 
    .xNext = test_Next, 
    .xEof = test_Eof, 
    .xColumn = test_Column, 
    .xRowid = test_Rowid, 
    .xRename = test_Rename,

};

Its in C syntax, I've tried to change it to JS syntax as much as possible, but still nothing positive.

So if you've any solution for this please help.

score 1 · Accepted Answer · answered Mar 30 '17 at 10:07

If it's about creating a virtual table using fts3 or fts4 then you do not need to register the module with sqlite3 as FTS3 and FTS4 are included with the SQLite core source code,but they are not enabled by default. To enable them use the following two switches in the compiler command line :

-DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_FTS3_PARENTHESIS

Then you can use it as follows:

sqlite3 = ref.types.void; // we don't know what the layout of "sqlite3" looks like
sqlite3_stmt = ref.types.void;
sqlite3Ptr = ref.refType(sqlite3);
sqlite3PtrPtr = ref.refType(sqlite3Ptr);
stringPtr = ref.refType(ref.types.CString);
stmtPtr = ref.refType(sqlite3_stmt);
stmtPtrPtr = ref.refType(stmtPtr) ;

// binding to a few "libsqlite3" functions...
libsqlite3 = ffi.Library('sqlite3', {
    'sqlite3_open': ['int', ['string', sqlite3PtrPtr]],
    'sqlite3_close': ['int', [sqlite3Ptr]],
    'sqlite3_exec': ['int', [sqlite3Ptr, 'string', 'pointer', 'pointer', stringPtr]],
    'sqlite3_changes': ['int', [sqlite3Ptr]],
    'sqlite3_user_add': ['int', [sqlite3Ptr, 'string', 'string', 'int', 'int']],
    'sqlite3_user_authenticate': ['int', [sqlite3Ptr, 'string', 'string', 'int']],
    'sqlite3_prepare_v2': ['int',[sqlite3Ptr, 'string', 'int', stmtPtrPtr, stringPtr]],
    'sqlite3_bind_blob': [ 'int', [stmtPtr, 'int', 'string' , 'int', 'pointer'] ],
    'sqlite3_step':[ 'int', [stringPtr] ],
    'sqlite3_finalize': ['int', [stringPtr]],
    'sqlite3_column_bytes': ['string',[stringPtr,'int']],
    'sqlite3_column_blob': ['string',[stringPtr,'int']]        
});
 dbHandle = dbPtrPtr.deref(); 
 var queryvtab = "CREATE VIRTUAL TABLE virtual_table_name USING fts3()";
 var commr = libsqlite3.sqlite3_exec(dbHandle, queryvtab, null, null, null);

Unable to create virtual table in sqlite3 in electron app

1 Answers1