Information Schema enhancement request

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Information Schema enhancement request

Dominique Devienne
(Not sure it's strictly information schema related, but here goes anyway).

SQLite has grown in the recent months better support for extracting
an information schema, thanks to new pragmas and especially their
eponymous vtable versions, allowing them to be mixed in queries with

But one thing that's lacking IMHO, is the ability to get dependencies
between objects, to know e.g. that view depends on those table(s)/view(s).
Or that trigger depends on those other view(s)/table(s) it's selecting from
or inserting into (or deleting from).

There's the authorizer API, but it's not accessible from the CLI or queries
in general.
And even if it was, since triggers are "merged in" to SQL using them,
there'd be no
way to distinguish their own dependencies, from the SQL they'd be part of.

I'd therefore would like a new pragma that can list dependencies of
entries, in terms of other sqlite_master entries. For both tables and
indexes, it's trivial.
The info is already right there in sqlite_master. BUT for views and
triggers, not so at all.

Such a new pragma could reuse the authorizer machinery maybe, and allow an
official way to get dependencies, w/o abusing the authorizer API for that

Thank you for considering this proposed enhancement. Thanks, --DD

PS: For triggers, knowing what DML / CRUD the trigger does against the
dependency would be very valuable information too. Obviously for views,
that's SELECT only, while triggers can have all 4 SELECT, INSERT, UPDATE,
DELETE against any of their dependencies.
sqlite-users mailing list
[hidden email]