On 27 Dec 2013, at 7:55am, David de Regt <dav...@mylollc.com> wrote:
> What I don't like is how often #3 requests gets shot down as being stupid. > Yes, often a feature request doesn't really fit with the general mantra of > SQLite, and it can be easily described as such. However, many things are in > a pretty grey area. For example, CTEs would fit fairly nicely with the > general mantra of SQLite, since it allows for making things > smaller/simpler/more explicit for the QO, but it's being shot down as a > non-useful feature that can be worked around. I'm not sure that's so. I think it's useful. It's being shot down as * providing only functionality that is already available * requiring lots of work on development and testing * increasing the size of the SQLite source code and compiled code The greatest of these is the third one. If you need caching, lots of concurrent multi-access, or a user/password/permission model, there are better SQL engines out there. But SQLite's major advantages over anything else are that it is tiny and runs on a huge number of platforms. Removing the 'lite' kills 50% of the reasons anyone has to use SQLite. Hence the resistance to adding things, and the eagerness to remove things. Remember, most of the billions of installations of SQLite are not on desktop computers which have plenty of space, they're on SatNav units or mobile phones where bytes are short and every milliwatt-hour counts. CTEs are useful for certain features of SQLite. They speed up stored procedures greatly and they're a good way of providing grouping by an aggregate function that depends on the order of rows. But SQLite doesn't implement stored functions, and doesn't have a concept of row order, so CTEs are useless for building on them. However, you mention is a case where CTEs would be useful: > Well, can it? Or does sqlite perform the subselect multiple times if you > mention the same query a couple different times in subtly different ways > (case sensitive, etc.)? This is, I think, the only advantage that CTEs would give. I suspect they'd provide optimised results in cases where the same temporary result was used in multiple ways within a command. Though the second part of your sentence would remove that advantage: if there are differences in result (case sensitive, etc.) then the 'WITH' would have to be executed multiple times, just like a sub-SELECT does. > There are important nuances here before completely dismissing something out > of hand. Not being dismissed out of hand. The only person who can do that here is Doctor Hipp, and that's because he's the boss of SQLite. Posts from anyone else are just personal opinions. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
