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

Reply via email to