On 2018/07/27 10:40 AM, Dominique Devienne wrote:
On Fri, Jul 27, 2018 at 1:58 AM Richard Hipp <d...@sqlite.org> wrote:
On 7/26/18, Tomasz Kot <tom...@gmail.com> wrote:
Hello,
Beneath sql shall throw an error on CREATE VIEW statement (as invalid
column is specified), but it passes (SQLite 3.23.1).
The error is deferred until you try to use the view. The reason for
deferring the error is that the problem might be corrected by
subsequent DDL statements prior to using the view.
Would it be possible to have a pragma that easily detects all invalid views?
Does an existing one already do that? That would be useful IMHO.
Someone writing a SQL script to instantiate a schema could run that pragma
at the end,
I'm sure it is possible, though you will probably receive the usual
"code bloat avoidance" reply (which I tend to agree with).
That said, it's easy to do in a bit of code which you can add to your
libraries as a standard function, which will go something like this (in
pseudo):
function check_views {
Read (SELECT name FROM sqlite_master WHERE type='view') into VList;
For each name in VList do
if sql_prepare('SELECT * FROM ' + name)<> SQL_OK
log( 'View ' + name + ' is not working.');
}
Which you can spruce up by adding the actual sqlite error return code or
description.
You can also UNION in the sqlite_temp_master to check temporary views if
needed.
One caution, some views that have ordered sub-selects linked to very
large tables (or other stuff I don't know about) might take a long time
to even simply prepare - so perhaps not a good idea to automatically add
this after every script.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
