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