David, I feel very strongly for many reasons that creating a new database for each component file is a big, big mistake. The overhead on this has got to be inordinately expensive. Did you see what I did with the keyed file system? It wouldn't be hard to convert that to a component file system at all. I think it is important that all component files be stored in a single database.
If you need any help with PostgreSQL, Elias and I have a lot of experience with it. Please feel free with any questions. I am very happy to help. --blake On Thu, Jul 10, 2014 at 10:02 AM, Elias Mårtenson <loke...@gmail.com> wrote: > 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, > Elias > > > On 10 July 2014 22:53, Blake McBride <blake1...@gmail.com> 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. >> >> Blake >> >> >> >> On Thu, Jul 10, 2014 at 1:25 AM, David Lamkins <da...@lamkins.net> 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" <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/ >>>>> > > >>>>> > > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> >>>>> >>>> >> >
