On Wed, Mar 22, 2017 at 12:52 PM, 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: >> >>> 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
