Roger Binns wrote: >> intended for >> use in situations where users do want most of the fields from a source but >> don't >> want a few. > > What is the harm in the extra fields being returned? Sure they take up a > few bytes more space but that isn't a big deal. In tables where there may > be a large blob column it is mostly likely that the developer has already > "optimised" the situation (eg point to another table, use files, make sure > column isn't requested). Also if your table has so many columns that many > are unrelated then shouldn't you be doing some form of normalization rather > than this?
You can have a well optimized database and still want to exclude some fields from your result. The main rationale for the feature I mention is code brevity, in situations where you don't want to return all fields (if you do want to return them all, well the "*" is about as brief as you can get). For example, if you have a 8-field table and you want to return 6 fields, only having to spell out the 2 you don't want makes for shorter code. And the important thing is encoding what you want to happen, which is "everything except this". Robustness comes in several ways. One is that with less code mass their tends to be fewer places to have bugs, all other things equal; when you make developers write more, they are more likely to make mistakes. Another piece of robustness is resilience under change. If you periodically change what detail columns a table has, and you want your queries to return all the detail columns in most situations, then any queries not mentioning the detail columns in an "except" will automatically work following the updates, same as a "select *" does; you don't have to go back and update every instance to just keep up. One example is say you want to get a quick list of user details to display, and you're mostly liberal so you just work in terms of a blacklist for sensitivity: SELECT * EXCEPT password FROM users I think that fairly clearly explains your intent in few words, rather than people looking at this seeing a list of fields and noticing or not that one is missing and wondering whether or not it was just missed. Or say you're filtering on a simple field so it would be redundant to say separately for each result what is a constant within the result set: SELECT * EXCEPT status FROM projects WHERE status = 5 A very common use for the feature would be to help counteract a problem of duplicate column names, for example: SELECT * EXCEPT bar.id FROM foo LEFT OUTER JOIN bar ON bar.id = foo.id That is versus say: SELECT foo.*, bar.c1, bar.c2, ... FROM foo LEFT OUTER JOIN bar ON bar.id = foo.id ... as ways to ensure the result has no duplicate unqualified names. > A second note is that there is a very clear distinction between users and > developers (and administrators if using Oracle/postgres/mysql etc) although > developers using SQLite are SQLite users. In any event a user of a program > using SQLite should never encounter this issue. Nor should most developers > since they designed their schema around their uses. In this situation, by "user" I mean whoever is writing the SQL code. Or you can read the "user" as "programmer", that is the person using the programming language SQL to write things in. >> It would make for much more robust code if users can explicitly >> encode their intentions, > > What is testing for? What are comments for? Testing is for ensuring the program executes as expected, and especially that it doesn't regress in functionality when something is changed later. Comments are for either higher level overviews of a code block or for explaining tricky bits where the programmer is doing something unusual; otherwise code should be self-documenting where reasonably possible. One of the points of a declarative language like SQL is that it makes what result the code is expected to have more self-documenting. And my proposal only aids that. Ease of readability and maintenance of a codebase aids robustness, and some languages can aid this more than others. Which is a main reason why lots of people use languages like Perl/Python/Ruby or SQL rather than just C for many tasks. Expressiveness helps. My proposal adds useful expressiveness. >> I have seen evidence from both first hand >> experience and from many other developers, that this feature would be very >> helpful to them. > > I still don't see how? The only way is if they say "except" a certain field > that doesn't exist and so get a SQL error. I don't see how the proposal > saves people, money or time. I gave some examples of benefits above. But try doing a Google search sometime, or talking to colleagues about whether or how often they want to "select all except this" and want to know how to say that concisely in SQL. >> And arguably it shouldn't be too terribly complicated for a >> DBMS to implement. > > Or for you to implement it in your code :-) You could have a function that > takes a table name and a list of columns to exclude and returns a string to > insert into your query of the desired columns, comma separated. (pragma > table_info will get you the column names.) Yes, anyone certainly could implement this at the application level, and so what I propose isn't reasonably a top priority for a DBMS to do it. At the same time, I don't think it would hurt to do it in the DBMS either. That is, except that you raise a very good point about once you add it you're stuck with it until SQLite 4. > Implementing something like this in your codebase (plus perhaps making the > function available as open source) would give your argument substantiation. > For example you could point to how many bugs it helped catch, developer > productivity, adoption by other projects (this points to the need) etc. I am in the process of implementing this now, mainly in the form of implementing my Muldis D language (as open source) as an alternative query/every-task-SQL-is-for language over SQLite and other DBMSs. (I'm also implementing the language as a standalone reference implementation that works natively rather than over SQL.) So sure, we can see in the future from actual uptake how much people use that feature. When this is working, I will post to the list and it can be tried out. > ["D" related stuff elided] > > Unfortunately the various names and symbols aren't conducive to Google > searches (it keeps matching smileys!). Probably Muldis D having syntax that is more C/Perl/etc like, meaning lots of use of non-alpha characters, has something to do with searchability. On the other hand, Tutorial D is much closer to SQL syntax, most alphanumerics. But I just mentioned these for reference, and part of my inspiration; my proposal for SQL should stand considered just in the context of SQL. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
