“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" <rsm...@rsweb.co.za> wrote: > > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
