|
| From: | Blake McBride |
| Subject: | Re: [Bug-apl] ISO Component File API preview |
| Date: | Thu, 10 Jul 2014 10:09:54 -0500 |
David's implementation opens a new connection (in SQLite, creates a new database file) for each CF "file".This is not a particularly good idea, and even using SQLite is extremely wasteful in terms of resources. You really only want a single database connection.Even if your'e using a single database, creating a separate database table for each "file" is also not good. Databases are not designed to have tables created and removed all the time. What you should do is to create the necessary tables once, and then use those to store all of the CF "files".Regards,EliasOn 10 July 2014 22:53, Blake McBride <address@hidden> wrote:
I am not sure where you are going with this, but I thought it worked like Elias said - multiple files per database. This means that once you form the connection with the database - a one time event - no component file operation has to worry about any database or SQL related stuff. It is all automatically associated to the database you opened in the beginning. Your functions should all be totally compliant.In the rare case where you wanted to use multiple databases, you can have a function that switches relative databases, and then all subsequent conforming calls will be relative to the newly selected (underlying) database.Thanks.BlakeOn Thu, Jul 10, 2014 at 1:25 AM, David Lamkins <address@hidden> wrote:
The short answer is that I'd like to retain compliance with the ISO spec. That means either coming up with conforming extensions (e.g. parsing the "filename" to infer database type) or adding new functions (as has already been done for transaction support).
Mixing API's to explicitly accept a database handle seems like a reasonable extension.
I don't think it'd be a problem to require an update of the component file implementation each time lib_sql adds support for a new database. In fact, given the vagaries of SQL implementations, I can imagine the forced update of the component file code being a near-certainty.
I do like the notion of allowing multiple component files in a single database.
On Jul 9, 2014 10:38 PM, "Elias Mårtenson" <address@hidden> wrote:Thank you for looking into this.Since the SQL API is database-agnostic, it would make sense to make your library the same. Instead of trying to make sense of the file specification, why don't you just pass it along to the SQL API? In other words, just pass the database type (currently only "sqlite" or "postgresql", but who knows what other databases we might want to support in the future?) and the connect parameters to the SQL library without looking at them?Or, even easier, you might not want to call SQL∆Connect at all, and simply accept an already-opened database handle?As for the "files" concept, wouldn't it make more sense to allow multiple files in a single database. The files index would then be stored in a separate table, which would be linked to a column in the datastore. Listing the files would then be a simple SELECT from the file-list-table.Regards,EliasOn 10 July 2014 13:30, David B. Lamkins <address@hidden> wrote:
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/
> >
> >
>
>
>
>
>
>
>
>
| [Prev in Thread] | Current Thread | [Next in Thread] |