On Sun, Sep 27, 2009 at 5:19 PM, Darren Duncan <dar...@darrenduncan.net> wrote: > Following my previous message to the sqlite-users list, I've done a bit more > research and decided to escalate my reply to a formal feature request. > > I was initially going to file a ticket, but it seems that non-registered > SQLite > developers can't do that anymore, and we're supposed to do it on sqlite-users > where a non-registered developer would then distill list chatter to real > tickets. So here I go ... > > ----- > > I propose that SQLite (and other DBMSs, and the SQL standard) add support for > explicitly naming a list of result fields that they do *not* want, intended > for > use in situations where users do want most of the fields from a source but > don't > want a few. It would make for much more robust code if users can explicitly > encode their intentions, meaning say that they want all fields except field > foo, > and have them get exactly that (with the expectation that if non-foo fields > are > added or removed to the source, they get or don't get those automatically, > same > as if they did "SELECT *"). I have seen evidence from both first hand > experience and from many other developers, that this feature would be very > helpful to them. And arguably it shouldn't be too terribly complicated for a > DBMS to implement.
Its a nice idea, but I feel if you use some other interface to SQLite you can get the same effect. On the one hand, I think if you are writing a script/program to fetch the results, then your script can assemble the effective columns in the SELECT list (all columns minus the ones you want to leave out). Meaning you can write your own C function/Perl method or what-have-you to get that list, given the database connection, table name and the columns-to-leave-out. On the other hand I am not sure, but you seem to be interested in having this feature so that you get the functionality at the sqlite3 prompt. In which case I think you can use a script that has the columns you're interested in, and save typing that way (type script once, load many times / type script once, load, change script, load etc.) I feel there is great value in not changing software. Stephan > Now I know the SQL 2008 standard doesn't have the feature, at least in its > Foundation; I checked; see section "7.12 <query specification>" of the SQL > standard, which deals with the relevant area. Similarly, the SQLite SQL > grammar > doesn't include it, as seen at > http://www.sqlite.org/syntaxdiagrams.html#select-core . > > What I propose is extending the syntax of what the standard calls "<select > list>". The old SQL 2008 definition is: > > <select list> ::= > <asterisk> > | <select sublist> [ { <comma> <select sublist> }... ] > > ... and noting that the definition of "<select sublist>" is: > > <select sublist> ::= > <derived column> > | <qualified asterisk> > > ... so my proposed redefinition is: > > <select list> ::= > <select list minuend> [ EXCEPT <select list subtrahend> ] > > <select list minuend> ::= > <asterisk> > | <select sublist> [ { <comma> <select sublist> }... ] > > <select list subtrahend> ::= > <qualified asterisk> [ { <comma> <qualified asterisk> }... ] > > So my proposed "<select list minuend>" is identical to the old "<select > sublist>", and my addition is the optional EXCEPT plus list of not derived > columns. > > Note that I'm not stuck on the keyword EXCEPT, but it should be a word that > reads similarly. > > Examples of use: > > SELECT * EXCEPT col4 FROM tbl > > SELECT foo.*, bar.col6 EXCEPT foo.col3 FROM tbl1 INNER JOIN tbl2 USING (id) > > The semantics of the change are as if someone wrote the original "<select > sublist>" in normal SQL with extra detail that spelled out all the fields > individually, and excluded the ones after the EXCEPT. However, the semantics > are also that this interpretation is done in the context of when the SQL > statement is executed, not just when it is written; if the underlying database > schema changes meanwhile, the result's column list would be affected. > > I can also cite prior art in that Chris Date's and Hugh Darwen's Tutorial D > has > the feature I propose, where it is used to modify the relational projection > operation, and it is spelled with the "ALL BUT" prefix (example "tbl { ALL BUT > col4 }". Similarly, my Muldis D language has that feature, spelled with a "!" > prefix (example "$...@{!col4}"), or as the long-hand "complementary > projection" > operator separate to the "projection" operator. > > Note that especially if this proposal is treated favorably by SQLite, I'll go > on > and propose it to other DBMS groups too, starting with Postgres. > > Thank you in advance for the consideration. > > -- Darren Duncan > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Stephan Wehner -> http://stephan.sugarmotor.org (blog and homepage) -> http://www.thrackle.org -> http://www.buckmaster.ca -> http://www.trafficlife.com -> http://stephansmap.org -- http://blog.stephansmap.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
