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

[Andrew Grangaard]

Kickass!

inspiring, dude, inspiring.

--andrew

Todd Presta wrote:
> 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.
> 
>     More on this later ...
> 
>     "Er uh, we're still doing the project, right?" :)
> 
> 
> 
>     T
> 
> 
> 
>     -- Todd Presta
>     -- http://www.asciiville.com
> 
> 
>     -----Inline Attachment Follows-----
> 
>     _______________________________________________
>     Thousand-oaks-pm mailing list
>     Thousand-oaks-pm at pm.org </mc/compose?to=Thousand-oaks-pm at pm.org>
>     http://mail.pm.org/mailman/listinfo/thousand-oaks-pm
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Thousand-oaks-pm mailing list
> Thousand-oaks-pm at pm.org
> http://mail.pm.org/mailman/listinfo/thousand-oaks-pm