[Thousand-oaks-pm] P6 Hackathon - Word from the Parrot SQLite3 Trenches...

Tue Jul 21 13:39:58 PDT 2009

We have lift off!

I grafted the birdseed library into SQLite3.pir, played musical chairs
with the DBDI package, and connected to SQLite3 through Rakudo Perl 6.
Did run into some HLLCompiler errors when using thing .next() iterator,
though. That's why there is only a single $rs.next() call.
"while($rs.next())" triggered some statement parsing errors. Got research that...

Seems like DBDI eq JDBC though. :)

Here's some transcript:

C:\rakudo\20090721\rakudo\parrot\ext\SQLite3>sqlite3 test.db

SQLite version 3.5.4

Enter ".help" for instructions

sqlite> .sc

CREATE TABLE foo(bar,baz);

sqlite> select * from foo;

1|123

2|Thingy

sqlite> .q

C:\rakudo\20090721\rakudo\parrot\ext\SQLite3>type mytest.p6

use DBDI;

say 'Grabbing connection...';

my $conn  = DBDI::DriverManager.getConnection("dbdi:SQLite3:test.db", "", "");

say 'Creating a statement...';

my $stm = $conn.createStatement();

say 'Getting a resultset...';

my $rs  = $stm.executeQuery("select baz, bar from foo");

say 'Fast forward one...';

$rs.next();

say 'Get a value...';

my $data = $rs.getCol('baz');

print 'And the value is...';

say $data;

say 'w00t!';

C:\rakudo\20090721\rakudo\parrot\ext\SQLite3>perl6 mytest.p6

Grabbing connection...

get_db_handle: got here

Creating a statement...

Getting a resultset...

get_statement_handle: got here

Fast forward one...

Get a value...

And the value is...123

w00t!

T

-- Todd Presta

-- http://www.asciiville.com

--- On Tue, 7/21/09, Todd Presta <toddpresta at sbcglobal.net> wrote:

From: Todd Presta <toddpresta at sbcglobal.net>
Subject: [Thousand-oaks-pm] P6 Hackathon - Word from the Parrot SQLite3 Trenches...
To: "perlmongers" <thousand-oaks-pm at mail.pm.org>
Date: Tuesday, July 21, 2009, 11:16 AM

Ok. I might be able to sleep tonight.

Even with Parrot 1.4 out, still getting build errors when trying to use ncigen for the SQLite3 extension.

On another front, the NCI functionality within Parrot seems to choke whenever requesting SQLite database connection handle and prepared statement handle no matter what type of PMC is passed as a parameter during the SQLite dynamic library function calls from within Parrot PIR. Using either Pointer, CPointer, UnManagedStruct, ManagedStruct, or ResizablePMCArray either segfaults or returns an erroneous value.

I decided to go deep and write some C code, basically wrapping both the sqlite3_open and sqlite3_prepare functions in the SQLite3 dynamic library and returning the pointers as the function return values rather than as a reference parameters.

This following PIR snippet calls my dynamic
 library (birdseed), the SQLite3 dynamic library, and obtains the last column value of a row returned from a prepared statement.

.sub main :main
  .local pmc sqllib, bslib
  .local pmc fn_dbh, dbh, fn_sth, sth, fn_close, fn_step, fn_final, fn_col_t
  bslib  = loadlib "birdseed"
  sqllib = loadlib "sqlite3"

  $S0 = "select * from dummy"

  fn_dbh   = dlfunc bslib,  "get_db_handle",        "pt"
  fn_sth   = dlfunc bslib,  "get_statement_handle", "ppt"
  fn_step  = dlfunc sqllib, "sqlite3_step",         "ip"
  fn_final = dlfunc sqllib, "sqlite3_finalize",     "ip"
  fn_col_t = dlfunc sqllib, "sqlite3_column_text",  "tpi"
  fn_close = dlfunc sqllib, "sqlite3_close",        "ip"

  dbh = fn_dbh("dummy.db") 
  sth = fn_sth(dbh, "select * from dummy")

  $I1 = fn_step(sth)
  say $I1

  $S0 = fn_col_t(sth, 1)
  say $S0

  $I2 = fn_final(sth)
  say $I2
  $I3 = fn_close(dbh)
  say $I3

.end

Here is the resulting output from Parrot

C:\t\dv\parrot\PARROT~1\birdseed>parrot birdseed.pir
get_db_handle: got here
get_statement_handle: got here
100
This is tag 1
0
0

Basically, the line that reads "This is tag 1" is the second column of the first row returned from the table "dummy." The lines beginning with "get_" are being printf'd from my library. 100 is SQLITE_ROW (that is a row ready for plucking), and the 0's are SQLITE_OK.

So the trick now is to determine if the SQLite3.pir under <parrot src tree>/etc/SQLite3 can be retrofitted with the "potential" birdseed dynamic library and then be recognized by <parrot src tree>/etc/SQLite3/DBDI/Driver/SQLite3.pm.