Hi Elias,
not sure if I understood the problem correctly,
If the concern is that the first ravel element of the right arg is a
query string and the rest a some-how shaped
argument (and the total being ugly) then you could try the following
instead:
put the left arg (db) into the axis,
put the sql command as left arg, and
make the rest (data) the right arg.
For example instead of:
* db SQL[4] 'insert into bar (a,b) values (?,?)' result*
you would do:*
'insert into bar (a,b) values (?,?)' SQL[4, db] result*
That separates the SQL data better from the SQL commands.
/// Jürgen
On 04/11/2014 12:11 PM, Elias Mårtenson 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
<mailto: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
