Re: [sqlite] Drop view ?automatically? when its associated table is dropped?

[Richard Hipp]

On 3/24/17, James K. Lowden  wrote:
>
> I probably shouldn't have referred specifically to the AST.  The output
> I think SQLite users could benefit from isn't the emitted bytecode.
> It's the parse tree: the affected objects and the operations on them.
>
> I don't see how the parse tree would be volatile or hard to
> understand.

The "Parse tree" is what I meant by AST.  The parse tree used by
SQLite is not the nice clean easy-to-follow parse tree that you saw
coming out of YACC in your compiler-construction class.  In SQLite, is
hacked and highly optimized to minimize the number of memory
allocations and number of CPU cycles.  It is not a pretty sight.  And
it changes frequently as we work on new feature and optimizations.

To put it another way, the parse tree does not reflect the syntax of
the input exactly.  Many transformations occur before generating the
parse tree, since it is faster and cheaper to generate a
semi-optimized parse tree than it is to generate a parse tree that
directly mirrors the input syntax, then transform it via a separate
optimizer pass.

You can actually get an ASCII-art representation of the parse tree out
of the CLI if you compile with -DSQLITE_DEBUG and
-DSQLITE_ENABLE_SELECTTRACE and -DSQLITE_ENABLE_WHERETRACE.  In the
shell, run the command ".selecttrace 0x" then enter a query of
some kind, and you will get to see the parse tree as in moves through
its various error checking, optimization, and code generation phases.
It is this parse tree that you want to see, right?  And that is the
same parse tree that is frequently modified in order to accomplish new
features and optimizations, and which we are therefore unwilling to
make available in a standard interface.
-- 
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

Re: [sqlite] Drop view ?automatically? when its associated table is dropped?

[James K. Lowden]

On Fri, 24 Mar 2017 11:03:10 -0400
Richard Hipp  wrote:

> On 3/24/17, James K. Lowden  wrote:
> >
> > We know SQLite parses SQL to produce byte-code for its virtual
> > machine.  And we know that's an abstract syntax tree.  And we know
> > trees can be represented as tables.  Therefore we know the AST
> > could be returned as tables.  That was my suggestion.
> 
> The AST used by SQLite changes.  Frequently.  If we provide an
> interface that returns the AST as a (virtual) table, that would make
> the AST an interface, and prevent us from enhancing it in the future,
> for new features or performance improvements.

I fully appreciate that concern, and the problem of documentation and
clarity.  

I probably shouldn't have referred specifically to the AST.  The output
I think SQLite users could benefit from isn't the emitted bytecode.
It's the parse tree: the affected objects and the operations on them.  

If that tree were returned as a table or tables, the user could query
it to discover, say, how many tables are used, or how many times a
particular table is used.  Before dropping a table, they could
interrogate the results for any views (and perhaps queries) to make
sure the table is no longer referenced.  Or, as this thread started, it
could be used (in application code) to drop dependent views when a
table is dropped.  

I don't see how the parse tree would be volatile or hard to
understand.  Naturally, it's subject to the same cost/benefit analysis
as any other feature.  

--jkl





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

Re: [sqlite] Drop view ?automatically? when its associated table is dropped?

[Dominique Devienne]

On Fri, Mar 24, 2017 at 4:03 PM, Richard Hipp  wrote:

> On 3/24/17, James K. Lowden  wrote:
> >
> > We know SQLite parses SQL to produce byte-code for its virtual
> > machine.  And we know that's an abstract syntax tree.  And we know
> > trees can be represented as tables.  Therefore we know the AST could be
> > returned as tables.  That was my suggestion.
> >
>
> The AST used by SQLite changes.  Frequently.  If we provide an
> interface that returns the AST as a (virtual) table, that would make
> the AST an interface, and prevent us from enhancing it in the future,
> for new features or performance improvements.
>

But short of the full AST, there are things which are "immutable",
like dependencies between "objects". Simply listing dependencies
on views, tables, virtual tables, and functions from a SQL expression
(and thus by extension a view or table SQL) would go a long way in
meeting many uses cases, include the one that started this thread.

Abusing the authorizer callback for that isn't ideal, doesn't report
function dependencies (I think), doesn't allow to temporarily replace
the authorizer and restore it afterwards, etc...

Oracle has the *_dependencies dictionary views for example.
And that's a very stable interface. 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] Drop view ?automatically? when its associated table is dropped?

[Richard Hipp]

On 3/24/17, James K. Lowden  wrote:
>
> We know SQLite parses SQL to produce byte-code for its virtual
> machine.  And we know that's an abstract syntax tree.  And we know
> trees can be represented as tables.  Therefore we know the AST could be
> returned as tables.  That was my suggestion.
>

The AST used by SQLite changes.  Frequently.  If we provide an
interface that returns the AST as a (virtual) table, that would make
the AST an interface, and prevent us from enhancing it in the future,
for new features or performance improvements.

Furthermore, the current AST for Fossil is rather non-intuitive.  It
is not a straight-forward translation of the SQL, but rather employs
many tricks and hacks to make it small and fast.  If it were available
to you, it would require a great deal of documentation to understand
and even then might not be helpful.

-- 
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

Re: [sqlite] Drop view ?automatically? when its associated table is dropped?

[James K. Lowden]

On Fri, 24 Mar 2017 01:15:50 +
Simon Slavin  wrote:

> SQLite does not seem to parse views or triggers for dependencies.  If
> it did it wouldn?t allow this view or these triggers.

I guess you mean SQLite doesn't parse a view when it processes CREATE
VIEW or, if it does, doesn't retain dependency information.  It surely
parses a view to execute it.  

We know SQLite parses SQL to produce byte-code for its virtual
machine.  And we know that's an abstract syntax tree.  And we know
trees can be represented as tables.  Therefore we know the AST could be
returned as tables.  That was my suggestion.  

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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Daniel Anderson]

Yep Sybase preparse it. When you create a view/sproc/triggers/function the
objects referenced must exist or your DDL will fail.

this create some complexity when we want to recreate all the objects, order
of creation is very important.



2017-03-23 21:15 GMT-04:00 Simon Slavin :

>
> On 24 Mar 2017, at 12:45am, Domingo Alvarez Duarte 
> wrote:
>
> > It's not that complicated, sqlite already do all work right now but it
> doesn't store that information in a structured way "data dictionary".
>
> SQLite does not seem to parse views or triggers for dependencies.  If it
> did it wouldn’t allow this view or these triggers.
>
> SQLite version 3.16.0 2016-11-04 19:09:39
> Enter ".help" for usage hints.
> sqlite> CREATE TABLE ta (c1 TEXT);
> sqlite> CREATE TABLE tb (c2 TEXT, c3 TEXT);
>
> sqlite> CREATE VIEW v1 AS SELECT i1 FROM ia;
> sqlite> PRAGMA table_info(v1);
> Error: no such table: main.ia
>
> sqlite> CREATE TRIGGER t22 BEFORE INSERT ON tb
>...> FOR EACH ROW BEGIN
>...> INSERT INTO ta (fred) VALUES (new.c2);
>...> END;
> sqlite> CREATE TRIGGER t23 BEFORE INSERT ON tb
>...> FOR EACH ROW BEGIN
>...> INSERT INTO gradsad VALUES (ajsdhasd);
>...> END;
> sqlite>
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Daniel
*L'action accède à la perfection quand, bien que vivant, vous êtes déjà
mort*
*Bunan*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Simon Slavin]

On 24 Mar 2017, at 12:45am, Domingo Alvarez Duarte  wrote:

> It's not that complicated, sqlite already do all work right now but it 
> doesn't store that information in a structured way "data dictionary".

SQLite does not seem to parse views or triggers for dependencies.  If it did it 
wouldn’t allow this view or these triggers.

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> CREATE TABLE ta (c1 TEXT);
sqlite> CREATE TABLE tb (c2 TEXT, c3 TEXT);

sqlite> CREATE VIEW v1 AS SELECT i1 FROM ia;
sqlite> PRAGMA table_info(v1);
Error: no such table: main.ia

sqlite> CREATE TRIGGER t22 BEFORE INSERT ON tb
   ...> FOR EACH ROW BEGIN
   ...> INSERT INTO ta (fred) VALUES (new.c2);
   ...> END;
sqlite> CREATE TRIGGER t23 BEFORE INSERT ON tb
   ...> FOR EACH ROW BEGIN
   ...> INSERT INTO gradsad VALUES (ajsdhasd);
   ...> END;
sqlite> 

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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Domingo Alvarez Duarte]

Hello !

It's not that complicated, sqlite already do all work right now but it 
doesn't store that information in a structured way "data dictionary".


Cheers !


On 23/03/17 21:17, Simon Slavin wrote:

On 23 Mar 2017, at 10:52pm, Daniel Anderson  wrote:


Sybase has one.

there is even a way to get objects dependencies

Okay, thanks for the answers.  The only way to do this involves quite a 
complicated set of dependencies.  Consider, for example, having to parse a 
TRIGGER to figure out all the TABLEs and VIEWs mentioned inside the stored 
procedure, and also all the columns of those tables and views mentioned in the 
TRIGGER.

The only way I can see it happening involved a significant modification of 
SQLite so that as it parses the schema stored in the database it constructs 
this table of dependencies.  Nothing simple.

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


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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Simon Slavin]

On 23 Mar 2017, at 10:52pm, Daniel Anderson  wrote:

> Sybase has one.
> 
> there is even a way to get objects dependencies

Okay, thanks for the answers.  The only way to do this involves quite a 
complicated set of dependencies.  Consider, for example, having to parse a 
TRIGGER to figure out all the TABLEs and VIEWs mentioned inside the stored 
procedure, and also all the columns of those tables and views mentioned in the 
TRIGGER.

The only way I can see it happening involved a significant modification of 
SQLite so that as it parses the schema stored in the database it constructs 
this table of dependencies.  Nothing simple.

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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Daniel Anderson]

Sybase has one.

there is even a way to get objects dependencies, I have been working on a
project for the last 5 years using sybase (ASE). very huge project and
whenever we must alter the schema we have tools to tell us which store
proc, triggers, views will be impacted. it allow us to reach the different
stake holders impacted so they can assess how the change will impact them.

also, because the way ASE works, they must provide some way to find
dependencies, because all objects which depends on table must be recreated
when that table is changed (according to doc) if not the
view/triggers/sproc might not do what you expect or fail.

ex:
create proc ert as select * from user_table where country = 'canada'

if a column is added at the end to user_table and the proc ert is not
recreated, it will not see the new column.
if a column is drop the proc will fail to execute
if a column is added in the middle then undefined behavior.

so with that kind of behavior it is good that sybase provide a way to find
the dependencies.



2017-03-23 7:56 GMT-04:00 Simon Slavin :

>
> On 23 Mar 2017, at 11:25am, Domingo Alvarez Duarte 
> wrote:
>
> > This problem and other related with the lack of a "data dictionary" in
> sqlite,
>
> Not sure what you expect here.  Can you point me at a "data dictionary"
> for some other implementation of SQL ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Daniel
*L'action accède à la perfection quand, bien que vivant, vous êtes déjà
mort*
*Bunan*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view ?automatically? when its associated table is dropped?

[James K. Lowden]

On Thu, 23 Mar 2017 11:56:00 +
Simon Slavin  wrote:

> > This problem and other related with the lack of a "data dictionary"
> > in sqlite, 
> 
> Not sure what you expect here.  Can you point me at a "data
> dictionary" for some other implementation of SQL ?

https://www.postgresql.org/docs/9.1/static/catalogs-overview.html

Every commonly used DBMS has some support for system tables.  ANSI
standardized them with INFORMATION_SCHEMA.  SQLite's support for them
is notably sparse.  

A data dictionary commonly means something about data semantics: some
description about where the data came from and what they mean.
Unfortunately most system table implementations cannot be extended in
that way by the user.  (Of course, it is possible to write queries that
refer to them.  It is not possible to create constraints on system
tables that e.g. require a semantic description or even just a primary
key).  

--jkl



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

Re: [sqlite] Drop view ?automatically? when its associated table is dropped?

[James K. Lowden]

On Wed, 22 Mar 2017 15:07:41 +
Simon Slavin  wrote:

> On 22 Mar 2017, at 11:52am, R Smith  wrote:
> > 
> > May I add further that views are tricky things. They can refer to
> > multiple tables, or other views which in turn refer to multiple
> > tables or yet other views (and have obscured the names of the
> > original tables via "AS" aliasing). There is no way to easily
> > implement an automatic view-of-table dropper. (I've tried to do
> > something like this for an sqlite tool long ago).
> 
> I think I?ve tried to do the equivalent thing for TRIGGERs and also
> failed.  And there?s also FOREIGN KEYs, though you can figure those
> out using PRAGMA calls.

It's only "tricky" because SQL is tricky to parse.  Given a parser,
collecting terminal nodes of a particuar type is simple.  

An interesting -- and innovative -- SQLite function would expose the
parser's result as ordinary data: 

sqlite3_parse_sql( sqlite3*, const char sql[] )

returning two tables representing the parse tree, along the lines of

Expressions:
expression_id
parent_expression_id
table_name
column_number
column_name
column_type
column_size 

and

Operations:
operation_id
left_expression_id
left_expression_column_number
right_expression_id
right_expression_column_number

That's just off the top of my head.  Doubtless the experience of
representing JSON trees as tables would provide insight, too.  

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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Simon Slavin]

On 23 Mar 2017, at 11:25am, Domingo Alvarez Duarte  wrote:

> This problem and other related with the lack of a "data dictionary" in 
> sqlite, 

Not sure what you expect here.  Can you point me at a "data dictionary" for 
some other implementation of SQL ?

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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Domingo Alvarez Duarte]

Hello !

This problem and other related with the lack of a "data dictionary" in 
sqlite, would be nice to have the sql parser exposed that way we could 
get the database structure on the fly and solve problems like the one 
discussed on this thread.


Cheers !

On 23/03/17 00:59, 邱朗 wrote:

“Much safer to have a habit to name views like the table names they derive from 
(when they derive from specific tables)”

That is what I am doing right now. So thanks for all the answers I got to 
confirm that there is no easy way to do it.
Qiulang


At 2017-03-22 19:52:59, "R Smith"  wrote:


On 2017/03/22 12:37 PM, Richard Hipp wrote:

On 3/22/17, 邱朗  wrote:

Hi,


Is there any way to drop view “automatically” when its associated table is
dropped?
It seems no way to do. Then if I drop a table, is there any (easy) way to
find views created based on it and I can drop view manually ?


There is no easy way to figure out what tables a view refers to.
Furthermore, some applications might want to drop a table and then
recreate that table (perhaps after adding a new constraint or
reordering the columns) and would like to keep using the VIEW.  Those
applications would not want the VIEW to be dropped automatically.


May I add further that views are tricky things. They can refer to
multiple tables, or other views which in turn refer to multiple tables
or yet other views (and have obscured the names of the original tables
via "AS" aliasing). There is no way to easily implement an automatic
view-of-table dropper. (I've tried to do something like this for an
sqlite tool long ago).

You could even look for the table name referenced in the view sql (by
simply doing something like:
SELECT name FROM sqlite_master WHERE type='view' AND sql LIKE
'%MyTableName%';
and drop all of those views that show up in the result - BUT, then you
have to be absolutely sure no View would ever use a field, refer to a
field, use another table or view, or have inferred / aliased fields or
use tables in a sub-query of any kind that will ever be the same (in
full or in part) as the table name you are trying to drop for.

It's a slippery slope. Much safer to have a habit to name views like the
table names they derive from (when they derive from specific tables),
such as
CREATE VIEW cities_view_someref AS SELECT ... FROM cities WHERE  ;
CREATE VIEW cities_view_someotherref AS SELECT ... FROM cities WHERE  ;

Then later when you drop everything, either in your code do:
DROP TABLE cities;
namelist = (SELECT name FROM sqlite_master WHERE type='view' AND name
LIKE 'cities_view_%';)
Then DROP every view in namelist via code.

--- or by creating an extended function in sqlite3, do this (pseudo coded):

FUNCTION  nmDROPTV(@arg1):
BEGIN
   DROP TABLE @arg1;
   For each name in (SELECT name FROM sqlite_master WHERE type='view'
AND name LIKE '[@arg1]_view_%';)
 do DROP VIEW name;
END;
Maybe also return the failure/success status of those functions, and
perhaps executing them in a transaction, etc.

So then executing:
DROPTV cities;
Should have the same effect as the first example.


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

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


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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[邱朗]

“Much safer to have a habit to name views like the table names they derive from 
(when they derive from specific tables)”

That is what I am doing right now. So thanks for all the answers I got to 
confirm that there is no easy way to do it.
Qiulang


At 2017-03-22 19:52:59, "R Smith"  wrote:
>
>
>On 2017/03/22 12:37 PM, Richard Hipp wrote:
>> On 3/22/17, 邱朗  wrote:
>>> Hi,
>>>
>>>
>>> Is there any way to drop view “automatically” when its associated table is
>>> dropped?
>>> It seems no way to do. Then if I drop a table, is there any (easy) way to
>>> find views created based on it and I can drop view manually ?
>>>
>> There is no easy way to figure out what tables a view refers to.
>> Furthermore, some applications might want to drop a table and then
>> recreate that table (perhaps after adding a new constraint or
>> reordering the columns) and would like to keep using the VIEW.  Those
>> applications would not want the VIEW to be dropped automatically.
>>
>
>May I add further that views are tricky things. They can refer to 
>multiple tables, or other views which in turn refer to multiple tables 
>or yet other views (and have obscured the names of the original tables 
>via "AS" aliasing). There is no way to easily implement an automatic 
>view-of-table dropper. (I've tried to do something like this for an 
>sqlite tool long ago).
>
>You could even look for the table name referenced in the view sql (by 
>simply doing something like:
>SELECT name FROM sqlite_master WHERE type='view' AND sql LIKE 
>'%MyTableName%';
>and drop all of those views that show up in the result - BUT, then you 
>have to be absolutely sure no View would ever use a field, refer to a 
>field, use another table or view, or have inferred / aliased fields or 
>use tables in a sub-query of any kind that will ever be the same (in 
>full or in part) as the table name you are trying to drop for.
>
>It's a slippery slope. Much safer to have a habit to name views like the 
>table names they derive from (when they derive from specific tables), 
>such as
>CREATE VIEW cities_view_someref AS SELECT ... FROM cities WHERE  ;
>CREATE VIEW cities_view_someotherref AS SELECT ... FROM cities WHERE  ;
>
>Then later when you drop everything, either in your code do:
>DROP TABLE cities;
>namelist = (SELECT name FROM sqlite_master WHERE type='view' AND name 
>LIKE 'cities_view_%';)
>Then DROP every view in namelist via code.
>
>--- or by creating an extended function in sqlite3, do this (pseudo coded):
>
>FUNCTION  nmDROPTV(@arg1):
>BEGIN
>   DROP TABLE @arg1;
>   For each name in (SELECT name FROM sqlite_master WHERE type='view' 
>AND name LIKE '[@arg1]_view_%';)
> do DROP VIEW name;
>END;
>Maybe also return the failure/success status of those functions, and 
>perhaps executing them in a transaction, etc.
>
>So then executing:
>DROPTV cities;
>Should have the same effect as the first example.
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Jean-Luc Hainaut]

On 22/03/2017 11:33, 邱朗 wrote:

Hi,


Is there any way to drop view “automatically” when its associated table is 
dropped?
It seems no way to do. Then if I drop a table, is there any (easy) way to find 
views created based on it and I can drop view manually ?


A quick and dirty procedure:

sqlite> create table T(A,B,C);
sqlite> create view TA as select A,B from T;
sqlite> create view TB as select B,C from T;
sqlite> create view TAB as select A,B,C from TA natural join TB;
sqlite> select * from TAB;
sqlite> drop table T;
sqlite> select * from TAB;
Error: no such table: main.T

Also works for "drop view". Quite easy to code in any host language like 
other exceptions.


J-L Hainaut
https://projects.info.unamur.be/~dbm/mediawiki/index.php/Accueil 


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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Simon Slavin]

On 22 Mar 2017, at 11:52am, R Smith  wrote:
> 
> May I add further that views are tricky things. They can refer to multiple 
> tables, or other views which in turn refer to multiple tables or yet other 
> views (and have obscured the names of the original tables via "AS" aliasing). 
> There is no way to easily implement an automatic view-of-table dropper. (I've 
> tried to do something like this for an sqlite tool long ago).

I think I’ve tried to do the equivalent thing for TRIGGERs and also failed.  
And there’s also FOREIGN KEYs, though you can figure those out using PRAGMA 
calls.

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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Dominique Devienne]

On Wed, Mar 22, 2017 at 1:33 PM, Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> > On Wed, Mar 22, 2017 at 12:52 PM, R Smith  wrote:
> >> There is no way to easily implement an automatic view-of-table
> >> dropper. (I've tried to do something like this for an sqlite tool long
> ago).
> >
> > Yes but... You can run a simple query on the view, like "select rowid
> from v" and EXPLAIN that,
> > and look for specific OpCodes [1] (like OpenRead), look at its P2 arg,
> and lookup that root page in
> > sqlite_master.
>
> sqlite3_set_authorizer() would be easier, and an official API.
>

yet the same issue I raised almost 2 years ago in [1] is still there though,
i.e. you cannot "stack" authorizers (which imply you cannot
compose/combine) them.

We are still missing a sqlite3_get_authorizer API IMHO. --DD

[1]
http://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg89912.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Clemens Ladisch]

Dominique Devienne wrote:
> On Wed, Mar 22, 2017 at 12:52 PM, R Smith  wrote:
>> There is no way to easily implement an automatic view-of-table
>> dropper. (I've tried to do something like this for an sqlite tool long ago).
>
> Yes but... You can run a simple query on the view, like "select rowid from v" 
> and EXPLAIN that,
> and look for specific OpCodes [1] (like OpenRead), look at its P2 arg, and 
> lookup that root page in
> sqlite_master.

sqlite3_set_authorizer() would be easier, and an official API.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Dominique Devienne]

On Wed, Mar 22, 2017 at 12:52 PM, R Smith  wrote:

> On 2017/03/22 12:37 PM, Richard Hipp wrote:
>
>> On 3/22/17, 邱朗  wrote:
>>
>>> Is there any way to drop view “automatically” when its associated table
>>> is dropped?
>>>
>>> There is no easy way to figure out what tables a view refers to.
>> Furthermore, some applications might want to drop a table and then
>> recreate that table (perhaps after adding a new constraint or
>> reordering the columns) and would like to keep using the VIEW.  Those
>> applications would not want the VIEW to be dropped automatically.
>>
>
> May I add further that views are tricky things. They can refer to multiple
> tables, or other views which in turn refer to multiple tables or yet other
> views (and have obscured the names of the original tables via "AS"
> aliasing). There is no way to easily implement an automatic view-of-table
> dropper. (I've tried to do something like this for an sqlite tool long ago).
>

Yes but... You can run a simple query on the view, like "select rowid from
v" and EXPLAIN that,
and look for specific OpCodes [1] (like OpenRead), look at its P2 arg, and
lookup that root page in
sqlite_master. And if there was a way to use EXPLAIN in a join-query like
is now possible with the
new pragma table-valued functions, that could be simplified.

Compiled statements "flatten" nested views I imagine, since at the end of
the day, really
IO on real tables must be performed to run the query. FWIW. --DD

[1] https://www.sqlite.org/opcode.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[R Smith]

On 2017/03/22 12:37 PM, Richard Hipp wrote:

On 3/22/17, 邱朗  wrote:

Hi,


Is there any way to drop view “automatically” when its associated table is
dropped?
It seems no way to do. Then if I drop a table, is there any (easy) way to
find views created based on it and I can drop view manually ?


There is no easy way to figure out what tables a view refers to.
Furthermore, some applications might want to drop a table and then
recreate that table (perhaps after adding a new constraint or
reordering the columns) and would like to keep using the VIEW.  Those
applications would not want the VIEW to be dropped automatically.



May I add further that views are tricky things. They can refer to 
multiple tables, or other views which in turn refer to multiple tables 
or yet other views (and have obscured the names of the original tables 
via "AS" aliasing). There is no way to easily implement an automatic 
view-of-table dropper. (I've tried to do something like this for an 
sqlite tool long ago).


You could even look for the table name referenced in the view sql (by 
simply doing something like:
SELECT name FROM sqlite_master WHERE type='view' AND sql LIKE 
'%MyTableName%';
and drop all of those views that show up in the result - BUT, then you 
have to be absolutely sure no View would ever use a field, refer to a 
field, use another table or view, or have inferred / aliased fields or 
use tables in a sub-query of any kind that will ever be the same (in 
full or in part) as the table name you are trying to drop for.


It's a slippery slope. Much safer to have a habit to name views like the 
table names they derive from (when they derive from specific tables), 
such as

CREATE VIEW cities_view_someref AS SELECT ... FROM cities WHERE  ;
CREATE VIEW cities_view_someotherref AS SELECT ... FROM cities WHERE  ;

Then later when you drop everything, either in your code do:
DROP TABLE cities;
namelist = (SELECT name FROM sqlite_master WHERE type='view' AND name 
LIKE 'cities_view_%';)

Then DROP every view in namelist via code.

--- or by creating an extended function in sqlite3, do this (pseudo coded):

FUNCTION  nmDROPTV(@arg1):
BEGIN
  DROP TABLE @arg1;
  For each name in (SELECT name FROM sqlite_master WHERE type='view' 
AND name LIKE '[@arg1]_view_%';)

do DROP VIEW name;
END;
Maybe also return the failure/success status of those functions, and 
perhaps executing them in a transaction, etc.


So then executing:
DROPTV cities;
Should have the same effect as the first example.


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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Richard Hipp]

On 3/22/17, 邱朗  wrote:
> Hi,
>
>
> Is there any way to drop view “automatically” when its associated table is
> dropped?
> It seems no way to do. Then if I drop a table, is there any (easy) way to
> find views created based on it and I can drop view manually ?
>

There is no easy way to figure out what tables a view refers to.
Furthermore, some applications might want to drop a table and then
recreate that table (perhaps after adding a new constraint or
reordering the columns) and would like to keep using the VIEW.  Those
applications would not want the VIEW to be dropped automatically.

-- 
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