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
>
