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

Tue Jul 21 18:55:26 PDT 2009

Ya'all are too kind.

Seriously. I am having a blast with this R&D. It is more fun than Disneyland (except for Haunted Mansion and Pirates of the Carribean).

The next logical step is to port the WinTel POC stuff on my workstation to a real platform: Linux. I used the more legacy SQLite3 calls (non-v2ish), and that should bode well with the default SQLite .so, circa 3.3.x. available on the Linux environment I have access too.

Since Parrot and Rakudo are still moving targets, hopefully this interim solution will be phased out in future releases. For the time being, we should be able to not worry about the low-level internals and perform CRUD through the DBDI layer of abstraction. (I'm still not over its JDBC-ness, though.)

But first must take a break Perl 6 and create a cartoon. :)

--- On Tue, 7/21/09, Tommy Stanton <tommystanton at gmail.com> wrote:

From: Tommy Stanton <tommystanton at gmail.com>
Subject: Re: [Thousand-oaks-pm] P6 Hackathon - Word from the Parrot SQLite3  Trenches...
To: "Todd Presta" <toddpresta at sbcglobal.net>
Cc: "perlmongers" <thousand-oaks-pm at mail.pm.org>
Date: Tuesday, July 21, 2009, 4:08 PM

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/thousand-oaks-pm/attachments/20090721/4cadd14c/attachment-0001.html>