Re: [sqlite] CLI dump command ignores view dependencies

[tonyp]

Exactly!

-Original Message- 
From: John Hascall

Sent: Wednesday, October 01, 2014 5:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] CLI dump command ignores view dependencies

I think his point was:

...

But, the existing situation where the first is​ 
illegal but the second is legal has

a certain illogic.

JohnOn Wed, Oct 1, 2014 at 9:05 AM, Stephan Beal <sgb...@googlemail.com> wrote:


On Wed, Oct 1, 2014 at 3:53 PM, <to...@acm.org> wrote:

> If it indeed does matter, then shouldn’t dropping view a (in the above
> example) also drop view b, automatically?
>

That assumes view 'a' somehow knows that it is the only consumer of 'b',
which it cannot know. Views from other db files, possibly not attached,
might be consumers of 'b'.


--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby
Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI dump command ignores view dependencies

[John Hascall]

I think his point was:

illegal to create a view referring to a non-existing table
AND
illegal to later create a situation where you have a view referring to a
non-existing table
is logical

legal to create a view referring to a non-existing table
AND
legal to later create a situation where you have a view referring to a
non-existing table
is logical
​
But, the existing situation where the first is​
​ illegal but the second is legal has
a certain illogic.


John​


On Wed, Oct 1, 2014 at 9:05 AM, Stephan Beal  wrote:

> On Wed, Oct 1, 2014 at 3:53 PM,  wrote:
>
> > If it indeed does matter, then shouldn’t dropping view a (in the above
> > example) also drop view b, automatically?
> >
>
> That assumes view 'a' somehow knows that it is the only consumer of 'b',
> which it cannot know. Views from other db files, possibly not attached,
> might be consumers of 'b'.
>
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby
> Wolf
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI dump command ignores view dependencies

[Stephan Beal]

On Wed, Oct 1, 2014 at 3:53 PM,  wrote:

> If it indeed does matter, then shouldn’t dropping view a (in the above
> example) also drop view b, automatically?
>

That assumes view 'a' somehow knows that it is the only consumer of 'b',
which it cannot know. Views from other db files, possibly not attached,
might be consumers of 'b'.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI dump command ignores view dependencies

[tonyp]

On the other hand, creation of a view could ignore the dependencies, and only 
check them at run time.

It's curious that the following sequence seems to behave this way:

create view a as select 1;
create view b as select * from a;
select * from b;
.d
drop view a;
.d
select * from b;--Error: no such table: main.a
create view a as select 3;
select * from b;

---

Apparently, a view can exist without its dependencies, so the order of 
definition should not have to matter.
If it indeed does matter, then shouldn’t dropping view a (in the above example) 
also drop view b, automatically?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI dump command ignores view dependencies

[Simon Slavin]

On 1 Oct 2014, at 7:25am, lchis...@paradise.net.nz wrote:

> Any database which has had any maintenance done on its views using any GUI or
> CLI tool could manifest this scenario, and I think it should explicitly
> documented as fixing it is non-trivial.

Nice catch.

It may be possible to fix the shell tool by having it construct a dependency 
graph of entities (tables, indexes, views, triggers) and use that to decide in 
what order to dump the entities.  Otherwise, yes, there's a problem with the 
.dump command and the problem should be documented.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] CLI dump command ignores view dependencies

[lchishol]

Hi all,

People on this list, and the SQLite documentation, say that the .dump command of
the CLI generates SQL which can be .read to rebuild a database.
What is omitted is the fact that the SQL .read may fail if you have created
views referring to other views and they are output in the SQL in the wrong 
order.

It seems that .dump emits SQL for tables, views, etc in the order they are 
defined.
However, if you have created view A, then view B which refers to A, then drop A
and recreate it, the dumped SQL contains the CREATE VIEW statement for B first,
which fails as A is not yet defined.

I have had this happen in the past, and verified it with a few lines of SQL in
the 3.8.6 CLI.

SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE x(a,b);
sqlite> CREATE VIEW va as select a from x;
sqlite> CREATE VIEW vb as select * from va;
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x(a,b);
CREATE VIEW va as select a from x;
CREATE VIEW vb as select * from va;
COMMIT;
sqlite> drop view va;
sqlite> CREATE VIEW va as select b from x;
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x(a,b);
CREATE VIEW vb as select * from va;
CREATE VIEW va as select b from x;
COMMIT;

It is possible to get around this by editing the SQL file (best), or by saving
the schema separately and reading it several times (with more and more 'table x
already exists' error lines) until everything has been created.

Any database which has had any maintenance done on its views using any GUI or
CLI tool could manifest this scenario, and I think it should explicitly
documented as fixing it is non-trivial.

Len Chisholm.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users