Re: [Bug-apl] SQL support

[Elias Mårtenson]

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 <address@hidden> 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