Re: [sqlite] Columns involved in a WHERE clause

2012-09-22 Thread Simon Slavin 

On 21 Sep 2012, at 3:17pm, Marco Bambini  wrote:

> Can I parse the output of the EXPLAIN my_query statement in order to have an 
> indirect access to columns involved in the WHERE clause?

Well, take a look at it.  Do you think you can write a parser for it ?

However, the format of the output of that command is not frozen.  It's meant to 
be read by humans, not computers, and you may find it changes from time to 
time.  And also I believe there are compilation options which disable that 
command entirely, so you can't depend on it being present in all SQLite 
libraries, only if you controlled the compilation yourself.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Columns involved in a WHERE clause

2012-09-22 Thread Marco Bambini 
Can I parse the output of the EXPLAIN my_query statement in order to have an 
indirect access to columns involved in the WHERE clause?

--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs







On Sep 21, 2012, at 4:10 PM, Richard Hipp  wrote:

> On Fri, Sep 21, 2012 at 9:56 AM, Marco Bambini  wrote:
> 
>> Hello,
>> is there a way to extract column names involved in a WHERE clause of a
>> query without manually parse the select statement?
>> 
>> For example from a query like:
>> SELECT * FROM myTable WHERE col1=… AND col2=…;
>> I would need to extract both col1 and col2.
>> 
>> There are no APIs in SQLite to do that.  No.  I think you have to parse
> the SQL yourself.
> 
> 
> 
>> Thanks.
>> --
>> Marco Bambini
>> http://www.sqlabs.com
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Columns involved in a WHERE clause

2012-09-21 Thread Simon Slavin 

On 21 Sep 2012, at 3:22pm, Marco Bambini  wrote:

> Can I parse the output of the EXPLAIN my_query statement in order to have an 
> indirect access to columns involved in the WHERE clause?

Is it good enough just to list the columns ?  After all the WHERE clause can 
include any expression, and can be very complicated.  Suppose your WHERE clause 
was

SELECT col1 FROM myTable WHERE (col5 * col3) >= col4 AND col2 IN 
(20,'twenty',col6)

It would seem simpler just to parse the SQL expression yourself.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Columns involved in a WHERE clause

2012-09-21 Thread Richard Hipp 
On Fri, Sep 21, 2012 at 10:22 AM, Marco Bambini  wrote:

> Can I parse the output of the EXPLAIN my_query statement in order to have
> an indirect access to columns involved in the WHERE clause?
>

You could use EXPLAIN QUERY PLAN, but they would only tell you which terms
were used for indexing.  And furthermore, the output format of EXPLAIN
QUERY PLAN is not guaranteed to be stable.  We don't change it except for
good reason, but some times good reasons arise, and so you cannot depend on
the output format staying the same forever.

You could also use the EXPLAIN output to look at the raw VDBE code, and
especially in the comment field for OP_Column opcodes.  But, the comment
field is only filled in if you compile with SQLITE_DEBUG, and even then you
don't know if the column is used in the WHERE clause or perhaps somewhere
else in the statement.


>
> --
> Marco Bambini
> http://www.sqlabs.com
> http://twitter.com/sqlabs
>
>
>
> On Sep 21, 2012, at 4:10 PM, Richard Hipp  wrote:
>
> > On Fri, Sep 21, 2012 at 9:56 AM, Marco Bambini  wrote:
> >
> >> Hello,
> >> is there a way to extract column names involved in a WHERE clause of a
> >> query without manually parse the select statement?
> >>
> >> For example from a query like:
> >> SELECT * FROM myTable WHERE col1=… AND col2=…;
> >> I would need to extract both col1 and col2.
> >>
> >> There are no APIs in SQLite to do that.  No.  I think you have to parse
> > the SQL yourself.
> >
> >
> >
> >> Thanks.
> >> --
> >> Marco Bambini
> >> http://www.sqlabs.com
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Columns involved in a WHERE clause

2012-09-21 Thread Marco Bambini 
Can I parse the output of the EXPLAIN my_query statement in order to have an 
indirect access to columns involved in the WHERE clause?

--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs



On Sep 21, 2012, at 4:10 PM, Richard Hipp  wrote:

> On Fri, Sep 21, 2012 at 9:56 AM, Marco Bambini  wrote:
> 
>> Hello,
>> is there a way to extract column names involved in a WHERE clause of a
>> query without manually parse the select statement?
>> 
>> For example from a query like:
>> SELECT * FROM myTable WHERE col1=… AND col2=…;
>> I would need to extract both col1 and col2.
>> 
>> There are no APIs in SQLite to do that.  No.  I think you have to parse
> the SQL yourself.
> 
> 
> 
>> Thanks.
>> --
>> Marco Bambini
>> http://www.sqlabs.com
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Columns involved in a WHERE clause

2012-09-21 Thread Simon Slavin 

On 21 Sep 2012, at 3:09pm, Simon Slavin  wrote:

> On 21 Sep 2012, at 2:56pm, Marco Bambini  wrote:
> 
>> is there a way to extract column names involved in a WHERE clause

I am very sorry, I completely missed that part of your question.  Please ignore 
my previous answer.  No, I don't think you can see anything about the WHERE 
clause.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Columns involved in a WHERE clause

2012-09-21 Thread Richard Hipp 
On Fri, Sep 21, 2012 at 9:56 AM, Marco Bambini  wrote:

> Hello,
> is there a way to extract column names involved in a WHERE clause of a
> query without manually parse the select statement?
>
> For example from a query like:
> SELECT * FROM myTable WHERE col1=… AND col2=…;
> I would need to extract both col1 and col2.
>
> There are no APIs in SQLite to do that.  No.  I think you have to parse
the SQL yourself.



> Thanks.
> --
> Marco Bambini
> http://www.sqlabs.com
>
>
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Columns involved in a WHERE clause

2012-09-21 Thread Simon Slavin 

On 21 Sep 2012, at 2:56pm, Marco Bambini  wrote:

> is there a way to extract column names involved in a WHERE clause of a query 
> without manually parse the select statement?
> 
> For example from a query like:
> SELECT * FROM myTable WHERE col1=… AND col2=…;
> I would need to extract both col1 and col2.

If everything about your setup and query is simple, than you can use this 
function from the API



Things get hairy when your SELECT includes a JOIN or returns a calculation 
instead of just a straight copy of a table column.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Columns involved in a WHERE clause

2012-09-21 Thread Marco Bambini 
Hello,
is there a way to extract column names involved in a WHERE clause of a query 
without manually parse the select statement?

For example from a query like:
SELECT * FROM myTable WHERE col1=… AND col2=…;
I would need to extract both col1 and col2.

Thanks.
--
Marco Bambini
http://www.sqlabs.com








___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users