Oh, and one more thing:

There is unfortunately no standard for getting the id of autoincrement
columns. For maximum flexibility, I'd suggest the standard approach of
having a separate table holding the "next" ID. Such table is usually
designed as a two-column "next_id" table, where one column holds the object
type (or table name) and the other column holds the next id.

Regards,
Elias


On 10 July 2014 13:38, Elias Mårtenson <loke...@gmail.com> 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,
> Elias
>
>
> On 10 July 2014 13:30, David B. Lamkins <dlamk...@gmail.com> 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 <blake1...@gmail.com> 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
>> >         <dlamk...@gmail.com> 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
>> >                 <da...@lamkins.net> 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/
>> >                 >
>> >                 >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>

Reply via email to