Dennis Cote wrote:
Noel Frankinet wrote:
Lawrence Chitty wrote:
You may be able to do this using the 'explain' statement
for example, I have a view called 'myview'. Wrapping this into an
sql select statement and proceeding this with explain e.g.
> explain select null from myview
gives a result set with the following:-
addr opcode p1 p2 p3
0 ColumnName 0 0 null
1 ColumnName 1 0 TEXT
2 Integer 0 0 <NULL>
3 OpenRead 1 228 mytable1
4 VerifyCookie 0 3016 <NULL>
5 Integer 0 0 <NULL>
6 OpenRead 2 17 mytable2
7 Rewind 1 13 <NULL>
8 Rewind 2 12 <NULL>
9 String 0 0 <NULL>
10 Callback 1 0 <NULL>
11 Next 2 9 <NULL>
12 Next 1 8 <NULL>
13 Close 1 0 <NULL>
14 Close 2 0 <NULL>
15 NullCallback 1 0 <NULL>
16 Halt 0 0 <NULL>
All you need to do now is search through the result set for any
opcode that is OpenRead, and the table name is in the p3 (last)
column. As you can see, myview consists of mytable1 and mytable2
Thank you that's what I was looking for.
Noel and Lawrence,
You should note that this will only work if your sqlite library is
compiled without NDEBUG defined (i.e. only in a debug build). In debug
builds, the table names in the explain output are generated by taking
the root page number that is actually stored in the compiled VM and
looking up the corresponding table name in the sqlite_master table.
Production builds (which are substantially faster) don't do this, they
simply display the root page number.
You can of course do the lookup in the sqlite_master table yourself,
but I don't know if there is a good way to tell if you are using a
debug or production build. You can't simply check if the table name is
numeric because someone could create a table with a numeric name. Some
of the pragmas, like parser_trace, don't work in production builds,
but don't generate any errors either.
If anyone knows how to tell if the sqlite library was built with
NDEBUG defined, this could be made to work well enough.
From what you explain above, would the following approach work to see
if NDEBUG defined.
> explain select null from sqlite_master.
Now look for the OpenRead and see if p3 is a number (NDEBUG not
defined) or 'sqlite_master' (NDEBUG defined). This is one table we
always know is always going to be present.
I only use the Windoze prebuilt tclsqlite.dll, which would appear to
have NDEBUG defined, so I can't really check this out any further.
Regards
Lawrence
