SQLITE_ENABLE_COLUMN_METADATA question ...

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLITE_ENABLE_COLUMN_METADATA question ...

Rob Sciuk

I've just compiled the latest (3.6.19) with the

-DSQLITE_ENABLE_COLUMN_METADATA flag set.  The problem I'm seeing is that
when I use an aggregate function in a select, the table name is not being
returned from an sqlite3_column_table_name() is not returning the
tablename as expected from a prepared statement, whereas if I actually
request data from the table, it works fine:

eg:
If I prepare the statement "select * from q_user", q_user is returned as
expected from sqlite3_column_table_name().

If I prepare the statement "select count(*) from q_user",
sqlite3_column_table_name() returns NULL.

This is somewhat disconcerting, and seems repeatable.  Are aggregate
functions handled differently than tuple data?

Are there any other dependancies other than SQLITE_ENABLE_COLUMN_METADATA?

Is there anything I might have overlooked??

Any ideas??

Thanks in advance,
Rob Sciuk
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_COLUMN_METADATA question ...

Jay Kreibich
On Mon, Oct 26, 2009 at 05:28:50PM -0400, Rob Sciuk scratched on the wall:

>
> I've just compiled the latest (3.6.19) with the
>
> -DSQLITE_ENABLE_COLUMN_METADATA flag set.  The problem I'm seeing is that
> when I use an aggregate function in a select, the table name is not being
> returned from an sqlite3_column_table_name() is not returning the
> tablename as expected from a prepared statement, whereas if I actually
> request data from the table, it works fine:
>
> eg:
> If I prepare the statement "select * from q_user", q_user is returned as
> expected from sqlite3_column_table_name().
>
> If I prepare the statement "select count(*) from q_user",
> sqlite3_column_table_name() returns NULL.
>
> This is somewhat disconcerting, and seems repeatable.  Are aggregate
> functions handled differently than tuple data?

  The documentation on this is fairly clear:

      http://sqlite.org/c3ref/column_database_name.html

      If the Nth column returned by the statement is an expression or
      subquery and is not a column value, then all of these functions
      return NULL. These routine might also return NULL if a memory
      allocation error occurs. Otherwise, they return the name of the
      attached database, table and column that query result column was
      extracted from.

  Basically, in order for these functions to work, the returned column
  needs to be a raw, unaltered, column directly from a specific table.
  Any kind of expression (including aggregate functions) means the
  data values in that result did not come directly from a specific
  table column, and therefore these functions return NULL.

  If you need a more general way to get the name of any column in any
  query, you most likely want sqlite3_column_name().

   -j

--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_COLUMN_METADATA question ...

D. Richard Hipp
In reply to this post by Rob Sciuk

On Oct 26, 2009, at 5:28 PM, Rob Sciuk wrote:

>
> I've just compiled the latest (3.6.19) with the
>
> -DSQLITE_ENABLE_COLUMN_METADATA flag set.  The problem I'm seeing is  
> that
> when I use an aggregate function in a select, the table name is not  
> being
> returned from an sqlite3_column_table_name() is not returning the
> tablename as expected from a prepared statement, whereas if I actually
> request data from the table, it works fine:
>
> eg:
> If I prepare the statement "select * from q_user", q_user is  
> returned as
> expected from sqlite3_column_table_name().
>
> If I prepare the statement "select count(*) from q_user",
> sqlite3_column_table_name() returns NULL.

sqlite3_column_table_name() (and all of the other METADATA functions)  
only work on table columns, not on functions.  The documentation says  
as much, though perhaps it could be worded more directly.  I'll make a  
note to clarify the documentation.

>
> This is somewhat disconcerting, and seems repeatable.  Are aggregate
> functions handled differently than tuple data?
>
> Are there any other dependancies other than  
> SQLITE_ENABLE_COLUMN_METADATA?
>
> Is there anything I might have overlooked??
>
> Any ideas??
>
> Thanks in advance,
> Rob Sciuk
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
[hidden email]



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_COLUMN_METADATA question ...

Rob Sciuk
In reply to this post by Jay Kreibich
On Mon, 26 Oct 2009, Jay A. Kreibich wrote:

>  The documentation on this is fairly clear:
>
>      http://sqlite.org/c3ref/column_database_name.html
>
>      If the Nth column returned by the statement is an expression or
>      subquery and is not a column value, then all of these functions
>      return NULL. These routine might also return NULL if a memory
>      allocation error occurs. Otherwise, they return the name of the
>      attached database, table and column that query result column was
>      extracted from.

Hmm, fair enough.

>
>  Basically, in order for these functions to work, the returned column
>  needs to be a raw, unaltered, column directly from a specific table.
>  Any kind of expression (including aggregate functions) means the
>  data values in that result did not come directly from a specific
>  table column, and therefore these functions return NULL.
>
>  If you need a more general way to get the name of any column in any
>  query, you most likely want sqlite3_column_name().
>
>   -j

I actually need a general way to get the TABLE name from a query ... hmmm
... back to the documentation ...

Thanks for your very kind reply.

Cheers,
Rob.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_COLUMN_METADATA question ...

Rob Sciuk
In reply to this post by D. Richard Hipp
On Mon, 26 Oct 2009, D. Richard Hipp wrote:

> sqlite3_column_table_name() (and all of the other METADATA functions) only
> work on table columns, not on functions.  The documentation says as much,
> though perhaps it could be worded more directly.  I'll make a note to clarify
> the documentation.
>
>
> D. Richard Hipp
> [hidden email]

I understand, and I must have missed the relevant documentation.  I'm
wondering, though, there should be a reliable way to return a list of the
affected tables from a prepared statement, no?

Cheers,
Rob
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_COLUMN_METADATA question ...

BareFeetWare
In reply to this post by Jay Kreibich
Hi all,

On Mon, Oct 26, 2009 at 05:28:50PM -0400, Rob Sciuk scratched on the  
wall:
>
> I've just compiled the latest (3.6.19) with the
>
> -DSQLITE_ENABLE_COLUMN_METADATA flag set.


This intrigues me. The functions that this enables looks very useful.

Please forgive my obvious ignorance here, but I'd appreciate some  
insight:

1. Why aren't these functions enabled by default?

2. Thus far I've been linking my Objective-C (Mac OS X) project  
against the operating system's built in libsqlite3.0.dylib framework.  
After downloading the 3.6.19 source, what steps do I need to follow to  
enable DSQLITE_ENABLE_COLUMN_METADATA, compile and link my XCode  
project against it?

Thanks,
Tom
BareFeet

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_COLUMN_METADATA question ...

Keith Roberts-4
In reply to this post by Rob Sciuk
On Mon, 26 Oct 2009, Rob Sciuk wrote:

> To: SQLite Users Digest <[hidden email]>
> From: Rob Sciuk <[hidden email]>
> Subject: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...
>
>
> I've just compiled the latest (3.6.19) with the

I'm using a pre-compiled version shipped with Fedora.

As a matter of interest, how long does it take to
compile sqlite on your machine? Not long?

Kind Regards,

Keith Roberts

-----------------------------------------------------------------
Websites:
http://www.php-debuggers.net
http://www.karsites.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-----------------------------------------------------------------
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ENABLE_COLUMN_METADATA question ...

Pavel Ivanov-2
> As a matter of interest, how long does it take to
> compile sqlite on your machine? Not long?

About 5 seconds, though I didn't measure and this figure can be not
exact enough. ;-)

Pavel

On Tue, Oct 27, 2009 at 5:19 AM, Keith Roberts <[hidden email]> wrote:

> On Mon, 26 Oct 2009, Rob Sciuk wrote:
>
>> To: SQLite Users Digest <[hidden email]>
>> From: Rob Sciuk <[hidden email]>
>> Subject: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...
>>
>>
>> I've just compiled the latest (3.6.19) with the
>
> I'm using a pre-compiled version shipped with Fedora.
>
> As a matter of interest, how long does it take to
> compile sqlite on your machine? Not long?
>
> Kind Regards,
>
> Keith Roberts
>
> -----------------------------------------------------------------
> Websites:
> http://www.php-debuggers.net
> http://www.karsites.net
> http://www.raised-from-the-dead.org.uk
>
> All email addresses are challenge-response protected with
> TMDA [http://tmda.net]
> -----------------------------------------------------------------
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users