On 2017/07/09 4:50 AM, petern wrote:
The bug here is how the VALUES logic can't have it both ways. If double quotes are invalid for column value literals they should be rejected or at least ignored. They should not suddenly be injected into the column name(s) observed by the outer scope of the VALUES clause.
It's not a bug, but it is an irritation - and we've been petitioning since the dawn of time to have a "STRICT" mode in SQLite where these shenanigans are indeed not allowed. As I understood from previous dev-team remarks, is that it's relatively easy to change, but not easy to maintain "both ways" without bloating the code-base, and since this quirk existed from day 1, it is required to keep existing for backward compatibility. (And backward compatibility IS important, since it is the very thing you are asking future guarantees for now).
That being said, for most of us it is/was just a once-off irritation that one time when we used double-quotes in stead of single quotes and got weird results. We fixed it, and so life goes on. In your case you have arrived at some mechanism or exploit of the SQLite internals to achieve an interesting functionality, but the quotes are biting you.
As already stated, best use is to avoid the double quotes in the VALUES. You are however quite welcome to use them in the identifier section (in lieu of [ and ]), but you can use the MSSQL style square brackets too. As to the question of future-proofing this method, well, it's not documented and not set in stone, BUT, SQLite isn't in the habit of haphazardly changing these internals. You are very likely to have a stable future regime for it - just not a 100% guarantee.
My advice would be to include test code, or create a separate test system that tests all your syntax forms, which you verify with in future before incorporating any future SQLite upgrade in your projects. Also, try to make your code based on constants and things in a way that a change would be very easy should it ever be required. (That's 1st week Computer science 101) :)
One last option, since you seem skilled in the coding of things, SQLite /IS/ open source, you could maintain a fork which implements a few lines of code to ensure your method works, or even make it work better. The code is very well documented, and this would guarantee your future compatibility.
That is of course unless there is a software easter egg here that, depending on as yet undisclosed well crafted literal values, allows the column names to be elegantly specified in addition to the row values.
None that I know of, and even if there were, it's not documented and so would fall under the exact same problem as your current solutions: We are not 100% sure it will remain in future exactly as it is now.
</whitenoise> Best of luck! Ryan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
