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

[Tommy Stanton]

Yeah, nice job Todd!  You got some sweet SQLite action goin' there.
Is your brain still intact?

I'm diggin' the w00t...w00t!

-Tommy

On Tue, Jul 21, 2009 at 3:28 PM, Andrew
Grangaard<agrangaard at rubiconproject.com> wrote:
> 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
>
>
> _______________________________________________
> Thousand-oaks-pm mailing list
> Thousand-oaks-pm at pm.org
> http://mail.pm.org/mailman/listinfo/thousand-oaks-pm
>