[DBD-SQLite] Re: load_extension("perlvtab.so")

[Salvador Fandino]

Dami Laurent (PJ) wrote:
>> Also, currently DBD::SQLite doesn't fully expose the subset of 
>> sqlite3 C API required by SQLite::VirtualTable. I have been 
>> playing with DynaLoader::dl_find_symbol_anywhere() to get 
>> direct access to it but this approach doesn't seem to be 
>> portable... probably the easiest way would be to just 
>> integrate SQLite::VirtualTable into DBD::SQLite and maintain a 
>> different version for usage outside of perl.
>>
>> Any thoughts?
>>
>> Cheers,
>>
>> - Salva
> 
> That's exactly the way to go. So this means exposing the
> sqlite3_create_module() function in DBD::SQlite API, and creating a
> wrapper layer that can sit as a (struct sqlite3_module*) and will call
> Perl methods for every callback defined in that structure. Apparently
> part of that work is already implemented in SQLite::VirtualTable (which
> I didn't know so far) and can be copied from there.
> 
> Extending DBD::SQLite in that way could probably trigger many
> interesting new applications. 

I have been able to integrate the vtab perl backend from 
SQLite::VirtualTable into DBD::SQLite.

Some bugs remain and occasionally it SIGSEVS, but otherwise, it mostly 
works ;-)

The DBD::SQLite module with virtual table support is available from the 
vtab branch of this git repository:

   http://github.com/salva/my-p5-DBD-SQLite/


And this is a sample session with the debugger:

   DB<1> use DBI

   DB<2> x $dbh = DBI->connect('dbi:SQLite:dbname=/tmp/db.sqlite', '', '');
0  DBI::db=HASH(0x1116c98)
      empty hash
   DB<3> $dbh->sqlite_enable_vtab_extension

   DB<4> x $dbh->do(q|create virtual table maa using perl 
('SQLite::VirtualTable::CSV', 
'/home/salva/g/perl/p5-SQLite-VirtualTable/t/data.csv', 'columns=a,b,c')|)
0  '0E0'
   DB<5> x $dbh->selectall_arrayref('select * from maa');
0  ARRAY(0x1173198)
    0  ARRAY(0x1380600)
       0  1
       1  2
       2  3
    1  ARRAY(0x1380660)
       0  4
       1  5
       2  'hello'
    2  ARRAY(0x13806d8)
       0  6
       1  'by'
       2  'hello'
   DB<6>


Bye,

  - Salva