Noel Frankinet wrote:
Hello,
Is there a better way to list all table making a view than parsing
SQL. Is there an API ?
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
I would guess that this method could be expanded so that by looking for
the the OpenRead and OpenWrite opcodes, the tables accessed used could
be ascertained for any select, insert or update statement as well.
I am not particularly versed with the opcodes though, so there may be
something I have missed here, so maybe one of the experts on the list
could indicate if this is a worthwhile approach.
I was hoping that it might be possible to do something like "select p3
from (explain select null from myview) where opcode = 'OpenRead'", but
unfortunatley that approach does not work :(
Same question for the table schema,
Don't quite understand what you require here.
Regards
Lawrence
I do parse the SQL but its rather fragile.
I'm still in 2.8
Thank you.
