Re: [sqlite] Create VIEW passing despite invalid column name specified
On Thursday, 26 Jul 2018 7:58 PM -0400, Richard Hipp wrote: > On 7/26/18, Tomasz Kot 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. And I will add that I've found this behaviour quite useful when upgrading to a new database schema. -- Will ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create VIEW passing despite invalid column name specified
There is very little pressure to keep the shell tool small. So the check for views which refer to non-existent tables could be put into there, as part of one of the dot-commands which do checking. As for the code, it doesn't require anything more than SQL commands. There's no need for access to any SQLite internals. The code posted by Ryan Smith upthread is a good demonstration of how to manage that check without using SQLite internals. But if you distrust your schema so much that you want to sanity-checking VIEWs, you want to sanity-check TRIGGERs too, since they also have deferred entity-checking. And that is far more difficult. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create VIEW passing despite invalid column name specified
On 2018/07/27 1:59 PM, Dominique Devienne wrote: Sigh. We already have 4 different check pragmas (cell-size, fk, integrity, quick). One more for views seems perfectly in line with those. Did anyone raise the same old light-code-bloat argument for those too, in the past? I for one cringe every time I read it nowadays. In any case, it's always entirely at the discretion of DRH, and although he of course considers the communities views (pun intended) I'm sure, at the end of the day he's the sole decider. Yeah, I meant Richard (or one of the devs at least) - apologies, I assumed that was obvious. 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. Really? I've never heard that preparing a statement depended on how large tables accessed are. Doesn't prepare only ever cares about data dictionaries (i.e. DDL stuff) and not actual rows (DML stuff)? Preparing is not only running the SQL parser and generating the VDBE program for that statement? If what you wrote was true, wouldn't that imply the result of prepare could be different depending if the prepare happens with a empty or "full" table? Or I completely misunderstood your "large table" comment? It's not the size of a direct table that matters, it's only when part of a sub-query that needs to be evaluated. At least, that's how I have it - I could be wrong. I'm hoping someone who understands the internal mechanism more fully can provide a clear example. It might also be that it is simply required before the first step() and not during the prepare per sé. Last discussion on this was many moons and many versions of sqlite ago, too long for me to trust my memory. So before propagating any misinformation, I'll appeal to the devs (or anyone who knows for sure), and ask: Is there ever a reason the "sqlite3_prepare()" and its ilk can take longer than a few milliseconds? If so, how? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create VIEW passing despite invalid column name specified
On Fri, Jul 27, 2018 at 11:33 AM R Smith wrote: > On 2018/07/27 10:40 AM, Dominique Devienne wrote: > > On Fri, Jul 27, 2018 at 1:58 AM Richard Hipp wrote: > >> On 7/26/18, Tomasz Kot wrote: > >>> 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. > > I'm sure it is possible, though you will probably receive the usual > "code bloat avoidance" reply (which I tend to agree with). > Sigh. We already have 4 different check pragmas (cell-size, fk, integrity, quick). One more for views seems perfectly in line with those. Did anyone raise the same old light-code-bloat argument for those too, in the past? I for one cringe every time I read it nowadays. In any case, it's always entirely at the discretion of DRH, and although he of course considers the communities views (pun intended) I'm sure, at the end of the day he's the sole decider. > 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): > Of course it is. But many many people use SQLite entirely via sqlite3[.exe] with no C code extensions of their own. > 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. > Some pragmas AFAIK, are already DB aware and can restrict themselves to a given attached DB. > 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. > Really? I've never heard that preparing a statement depended on how large tables accessed are. Doesn't prepare only ever cares about data dictionaries (i.e. DDL stuff) and not actual rows (DML stuff)? Preparing is not only running the SQL parser and generating the VDBE program for that statement? If what you wrote was true, wouldn't that imply the result of prepare could be different depending if the prepare happens with a empty or "full" table? Or I completely misunderstood your "large table" comment? Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create VIEW passing despite invalid column name specified
On 2018/07/27 10:40 AM, Dominique Devienne wrote:
On Fri, Jul 27, 2018 at 1:58 AM Richard Hipp wrote:
On 7/26/18, Tomasz Kot 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
Re: [sqlite] Create VIEW passing despite invalid column name specified
On Fri, Jul 27, 2018 at 1:58 AM Richard Hipp wrote: > On 7/26/18, Tomasz Kot 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, with -bail perhaps, to eagerly and easily validate the DDL script. My $0.02. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create VIEW passing despite invalid column name specified
On 7/26/18, Tomasz Kot 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. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
