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.
HTH
Dennis Cote
Thank you Dennis,
so I'm back to my original question, is parsing the DDL from
sqlite_master is a viable (good ?) option. Is there a way to do it with
salite itself (there surely is way but can hook into it). Or should I
rely on my homegrown half-backed SQL parser ?
Regards
--
Noël Frankinet
Gistek Software SA
http://www.gistek.net