Drop view “automatically” when its associated table is dropped?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
23 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Drop view “automatically” when its associated table is dropped?

邱朗
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 ?


Thanks


Qiulang
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Richard Hipp-3
On 3/22/17, 邱朗 <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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, 邱朗 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Dominique Devienne
On Wed, Mar 22, 2017 at 12:52 PM, R Smith <[hidden email]> wrote:

> On 2017/03/22 12:37 PM, Richard Hipp wrote:
>
>> On 3/22/17, 邱朗 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Dominique Devienne
On Wed, Mar 22, 2017 at 1:33 PM, Clemens Ladisch <[hidden email]> wrote:

> Dominique Devienne wrote:
> > On Wed, Mar 22, 2017 at 12:52 PM, R Smith <[hidden email]> 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@.../msg89912.html
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Simon Slavin-3
In reply to this post by R Smith

On 22 Mar 2017, at 11:52am, R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Jean-Luc Hainaut
In reply to this post by 邱朗
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 
<https://projects.info.unamur.be/%7Edbm/mediawiki/index.php/Accueil>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

邱朗
In reply to this post by R Smith
“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" <[hidden email]> wrote:

>
>
>On 2017/03/22 12:37 PM, Richard Hipp wrote:
>> On 3/22/17, 邱朗 <[hidden email]> 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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" <[hidden email]> wrote:
>>
>> On 2017/03/22 12:37 PM, Richard Hipp wrote:
>>> On 3/22/17, 邱朗 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Simon Slavin-3

On 23 Mar 2017, at 11:25am, Domingo Alvarez Duarte <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

James K. Lowden
In reply to this post by Simon Slavin-3
On Wed, 22 Mar 2017 15:07:41 +0000
Simon Slavin <[hidden email]> wrote:

> On 22 Mar 2017, at 11:52am, R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

James K. Lowden
In reply to this post by Simon Slavin-3
On Thu, 23 Mar 2017 11:56:00 +0000
Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Daniel Anderson
In reply to this post by Simon Slavin-3
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 <[hidden email]>:

>
> On 23 Mar 2017, at 11:25am, Domingo Alvarez Duarte <[hidden email]>
> 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
> [hidden email]
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Simon Slavin-3

On 23 Mar 2017, at 10:52pm, Daniel Anderson <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Simon Slavin-3

On 24 Mar 2017, at 12:45am, Domingo Alvarez Duarte <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]>:

>
> On 24 Mar 2017, at 12:45am, Domingo Alvarez Duarte <[hidden email]>
> 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
> [hidden email]
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

James K. Lowden
In reply to this post by Simon Slavin-3
On Fri, 24 Mar 2017 01:15:50 +0000
Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Richard Hipp-3
On 3/24/17, James K. Lowden <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12
Loading...