Re: creating tables needed for a query on-demand

Roger Binns wrote:
> The authorizer only works on valid statements.  You have various gnarly
> issues such as telling the difference between a typo and what someone
> really meant.
>
>   
that's fine, and in fact exactly what I want. if the statement isn't 
valid SQL, my authorizer shouldn't see it anyways. if it can't 
ultimately be executed, there's no point in me populating the tables.
> I think you have two ways out of this.  The first is to provide a
> graphical view of the attached databases which you populate using the
> various pragmas so your user can see what is present and play pointy
> clicky to add columns and tables as needed.
>
>   
nope. the majority of my users should not be concerned about how the 
backend is working. they want to click a button and see the results. 
making the users manually populate the tables is too complicated for 
something the computer can (and should) be able to take care of on it's own.

the other option I could provide is for the people defining the queries 
(who in many cases will be different from the people ultimately using 
them), they could list which tables the statement needs to execute 
correctly. but this seems redundant, and duplication of information that 
is already in the query anyways.
> The second is to take SQLite's parser and make it build a parse tree for
> you.  You'll need to understand lex/yacc principles to start with this
> (SQLite uses one named Lemon).  The parser definition is in parse.y.
> This is fairly hard to do.
>
>   
what I'm actually doing is executing the query possibly twice. the first 
time through, I execute the query, and the authorizer logs which tables 
were referenced. after the query finishes I then check this list, and 
see if any of those tables should be [re-]populated. if so, I populate 
them, and run the query again to get the final results. it seems like a 
bit of a waste to run the query twice, but for the queries I've tried, 
SQLite is pretty quick. besides, the cases where it IS executed twice 
are the cases when I'm repopulating the tables anyways, and that time 
dwarfs the execution time for the SELECT.

thanx,
ryan