On 2017/03/22 12:37 PM, Richard Hipp wrote:
On 3/22/17, 邱朗 <qiulang2...@126.com> 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
