Re: [Bug-apl] SQL support

[Juergen Sauermann]

Hi Blake,

we have 2⎕TF to convert APL functions and APL variables to text vectors ("bytes") and back.
So all that is needed for a native APL SQL database given a generic SQL database is to declare
the objects properly (BLOB) and to call 2⎕TF before writing to, or after reading from, the database.

This could be done "under the hood" by using a new function number doing that, but it
seems so simple that I wonder if it is worth the effort. There are many combinations of functions in APL
that look like deserving their own name (or symbol, or function number), but I tend to prefer fewer functions
in total because there is also a downside (remembering them, portability, maintenance to name a few).

I guess a few examples would be beneficial.

/// Jürgen


On 04/11/2014 08:03 PM, Blake McBride wrote:

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