I think native SQL support is a really great thing. I prefer PostgreSQL,
so I might jump in in that area.
I feel strongly, though, that a keyed file system that works more native to
APL would be an important thing. Perhaps it can be built on top of what
you are doing - perhaps along the lines of my previous post. Is there any
interest in that?
Thanks.
Blake
On Fri, Apr 11, 2014 at 5:11 AM, Elias Mårtenson <loke...@gmail.com> wrote:
> I'm following up to myself here.
>
> I would like to have some advice from you guys about the design of the API.
>
> Currently, the API for select and update is the following:
>
> *database_id* FN '*statement*' [*argument* ...]
>
>
> In other words, the first element of the ravel of the right-hand argument
> is the SQL statement itself, and the remaining elements are the positional
> arguments.
>
> Now, I was thinking of allowing a two-dimensional array for the arguments.
> This would effectively run the same update for each row (using a prepared
> statement). You could do some cool stuff like feeding the result of a query
> into another:
>
> *result ← db SQL[3] 'select a,b from foo'*
> *db SQL[4] 'insert into bar (a,b) values (?,?)' result*
>
>
> However, parsing the right-hand arguments here is a bit ugly, since the
> interpretation of the arguments are significantly different depending on
> whether the second value of the ravel is two-dimensional. I find that to be
> very ugly.
>
> Another alternative is to have a separate function number for this
> particular case. Yet another option would be to pass the db *and* the SQL
> statement on the left-hand side. This would be messy in the case where
> there are no positional parameters though (since the left hand argument
> can't be empty).
>
> What are your suggestions as to how to design this?
>
> Regards,
> Elias
>
>
> On 11 April 2014 16:48, Elias Mårtenson <loke...@gmail.com> wrote:
>
>> I've been working on an SQL interface, and right now I'm at the point
>> where basic SQLite support works. Here's an example:
>>
>> ⍝ Load the native library
>> * '/home/emartenson/prog/apl-sqlite/lib_sqlite.so' ⎕FX 'SQL'*
>> SQL
>>
>> ⍝ Open the SQLite database in the file /tmp/foo
>> *db ← SQL[1] '/tmp/foo'*
>>
>> ⍝ Run an SQL statement to create a new table
>> *db SQL[4] 'create table foo (id int primary key, value
>> varchar(10))'*
>>
>> ⍝ Insert 10 rows of data into the table
>> *{db SQL[4] 'insert into foo (id,value) values (?,?)' ⍵
>> ('foo:',⍕⍵)}¨ ⍳10*
>>
>> ⍝ Select a few rows of data from the table
>> *8⎕CR db SQL[3] 'select * from foo where id < 4'*
>> ┌→────────┐
>> ↓1 ┌→────┐│
>> │ │foo:1││
>> │ └─────┘│
>> │2 ┌→────┐│
>> │ │foo:2││
>> │ └─────┘│
>> │3 ┌→────┐│
>> │ │foo:3││
>> │ └─────┘│
>> └∊────────┘
>>
>> ⍝ Close the database
>> *SQL[2] db*
>>
>> The code is available here: https://github.com/lokedhs/apl-sqlite
>>
>> Contrary to the project name, it's written in a way so that support for
>> other SQL databases can be easily plugged in.
>>
>> Regards,
>> Elias
>>
>
>
