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/ >> > > >> > > >> > >> > >> > >> > >> > >> > >> > >> > >> >> >
