Re: [Bug-apl] ISO Component File API preview

[David B. Lamkins]

Well, it seems simple enough, but I have zero experience with PostgreSQL
and it's going to take me a while to ramp up...

Therefore: Please apply the attached experimental patch and see whether
you can get something working.

Here's the gist of the change as implemented in this patch:

CF_OPEN takes either a file name (no path separator; .cf extension) or a
PostgreSQL URI. In the former case, you get a SQLite database file with
the given name. In the latter case, lib_sql passes the URI through to
the PostgreSQL server.

Once you have a database handle (and - in the case of the PostgreSQL
connection, I'd guess - a properly configured database) then everything
else should work exactly the same. (Or maybe not... While proofreading
this message, I realized that I depend upon SQLite's implict oid
support; this may not be the same on PostgreSQL. But that's a small
matter of either making a table definition that'll work in both cases,
or creating a separate case for each SQL variant.)

Elias, INSERT INTO should be valid in both SQLite and PostgreSQL. At
least, that's what my quick search of the documentation suggests.

Of course, the ISO component file APIs that are tied to *files* won't do
anything useful for a PostgreSQL connection. This includes CF_ERASE,
CF_RENAME and CF_CREATE; the first two for the obvious reasons and the
last because (for now, until I figure out or someone tells me otherwise)
I'm assuming that the PostgreSQL database passed to CF_OPEN will have
been created by a database administrator. If we can prove basic
operation, I'll take care of making the the file APIs do something
reasonable (probably just signal a domain error) when passed a
PostgreSQL URI.

The alternate (non-URI) form of PostgreSQL connection string is not
presently recognized. It may be tricky to distinguish between a
poorly-formed file name and a properly-formed non-URI connection string.

Guys: I'm willing to pursue this, but don't yet have the PostgreSQL
knowledge to enable me to proceed. If you want to provide patches,
that'd be great. If you're willing to talk me through setting up
PostgreSQL (It's installed; I just haven't figured out how to create
roles and databases yet...) that'd be even better.

I'll mention in passing that this seems like a bit of an abuse of the
component *file* concept since there's no readily-accessible (to the
application programmer, anyhow) file in the case of the PostgreSQL
database. 

On the other hand, the ability to host a component file abstraction on a
database server seems like an interesting and possibly useful
"conforming extension". I imagine that this could be a relatively easy
way to implement multiuser access, as well. (I'm assuming that
PostgreSQL handles multiple clients and can do The Right Thing w.r.t.
locking.) 

Let's see where this goes. I'm looking forward to your feedback and
help...


On Thu, 2014-07-10 at 12:56 +0800, Elias Mårtenson wrote:
> Yes, that's how I work too. My home server contains a Postgres
> instance that I use for pretty much everything. It's quite convenient.
> 
> 
> Regards,
> Elias
> 
> 
> On 10 July 2014 12:53, Blake McBride <address@hidden> wrote:
>         Greetings,
>         
>         
>         PostgreSQL is very important to, at least, me.  I do a lot of
>         production work in PostgreSQL.  I like SQLite too, but I would
>         only use it when the data didn't relate to anything but APL.
>          Here is what I propose.  Since your component file system
>         rides on top of SQL, and the standard doesn't know or care
>         about anything below the APL level, we should add a function
>         that allows the user to specify the database information
>         (dbname, user, password, etc.).  That call would be made as
>         sort of a setup step.  Once that setup step is specified, all
>         of the standard API should work as described.
>         
>         
>         This will give us a totally standard API.  If someone wants to
>         switch to GNU APL, all they have to do is add one function to
>         specify the database.  Not too much to ask.
>         
>         
>         We kind of have to do this.  Even with SQLite, you still have
>         to specify the database name (I presume one database contains
>         many component files).
>         
>         
>         Thanks.
>         
>         
>         Blake
>         
>         
>         
>         
>         On Wed, Jul 9, 2014 at 9:44 PM, David B. Lamkins
>         <address@hidden> wrote:
>                 I'm certainly willing to consider alternatives. IIUC,
>                 lib_sql also
>                 supports PostgreSQL. Anything else?
>                 
>                 How do I tell lib_sql to use a PostgreSQL server?
>                 
>                 The argument in favor of SQLite, of course, is that
>                 it's serverless. No
>                 additional setup (beyond the installation of the
>                 library) required.
>                 
>                 Would there be any additional benefits or concerns
>                 when connecting to a
>                 PostgreSQL server?
>                 
>                 As you've no doubt noticed, there's nothing in the
>                 code (or in the
>                 standard API) to acknowledge or support the notion of
>                 multiple users.
>                 Again: point in favor of SQLite...
>                 
>                 
>                 On Thu, 2014-07-10 at 10:25 +0800, Elias Mårtenson
>                 wrote:
>                 > I was looking at your code, and I noticed that it's
>                 SQLite-specific.
>                 > WOuldn't it make sense to make it
>                 SQL-implementation-agnostic?
>                 >
>                 >
>                 > Based on what I can see, the only SQLite-specific
>                 SQL you have in
>                 > there is "replace into" which I had never heard
>                 about before.
>                 >
>                 >
>                 > Regards,
>                 > Elias
>                 >
>                 >
>                 > On 9 July 2014 01:22, David Lamkins
>                 <address@hidden> wrote:
>                 >         I haven't yet written test scripts, but I've
>                 informally tested
>                 >         all of the functions and am reasonably
>                 confident that the
>                 >         component file API is complete and correct.
>                 >
>                 >
>                 >         If you'd like to try out the API while I'm
>                 working on scripted
>                 >         test cases, the repo is:
>                 >
>                 >         https://github.com/TieDyedDevil/iso-apl-cf
>                 >
>                 >
>                 >         You'll find documentation is in the comments
>                 and in Annex A of
>                 >         the ISO 13751 standard.
>                 >
>                 >
>                 >         The standard "specifies a minimal set of
>                 functions which a
>                 >         conforming implementation must provide";
>                 I've implemented all
>                 >         of these. I've also added several useful
>                 functions not
>                 >         mentioned in the standard, including
>                 component inquiry,
>                 >         component drop, and transaction support.
>                 >
>                 >
>                 >
>                 >         Note that the code is not packaged for my
>                 package manager; I
>                 >         assume that the component file
>                 implementation would become an
>                 >         L3 library in the event it's adopted for
>                 inclusion in GNU APL.
>                 >
>                 >
>                 >         Júergen, I've specified the GPLv3 license
>                 since that's what
>                 >         GNU APL uses. If there's a more appropriate
>                 choice of license
>                 >         for this library, please let me know.
>                 >
>                 >         --
>                 >         "The secret to creativity is knowing how to
>                 hide your
>                 >         sources."
>                 >            Albert Einstein
>                 >
>                 >
>                 >         http://soundcloud.com/davidlamkins
>                 >         http://reverbnation.com/lamkins
>                 >         http://reverbnation.com/lcw
>                 >         http://lamkins-guitar.com/
>                 >         http://lamkins.net/
>                 >         http://successful-lisp.com/
>                 >
>                 >
>                 
>                 
>                 
>                 
>         
>         
> 
>

postgres-experimental.patch
Description: Text Data