Determining column collating functions

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

Determining column collating functions

Manuel Rigger
Hi everyone,

Is there a simple way to determine the collating function of a column?
PRAGMA table_info does not seem to provide this information. The
information could be extracted from sqlite_master, which contains the
SQL statements used to create the table or view. While parsing the SQL
string is rather straightforward for tables, it would involve more
effort to determine the collating functions for views, which can again
reference other views or tables.

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

Re: Determining column collating functions

J. King-3
On August 13, 2019 5:58:54 p.m. EDT, Manuel Rigger <[hidden email]> wrote:

>Hi everyone,
>
>Is there a simple way to determine the collating function of a column?
>PRAGMA table_info does not seem to provide this information. The
>information could be extracted from sqlite_master, which contains the
>SQL statements used to create the table or view. While parsing the SQL
>string is rather straightforward for tables, it would involve more
>effort to determine the collating functions for views, which can again
>reference other views or tables.
>
>Best,
>Manuel
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I'm not aware of any means beyong parsing the statement. This is doubly annoying because the index_xinfo pragma does list the collation sequence of index columns.
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Determining column collating functions

Keith Medcalf
In reply to this post by Manuel Rigger

On Tuesday, 13 August, 2019 15:59, Manuel Rigger <[hidden email]> wrote:

>Is there a simple way to determine the collating function of a
>column?

Presently, there is not.

>PRAGMA table_info does not seem to provide this information. The
>information could be extracted from sqlite_master, which contains the
>SQL statements used to create the table or view. While parsing the
>SQL string is rather straightforward for tables, it would involve
>more effort to determine the collating functions for views, which
>can again reference other views or tables.

The internal schema representation for the column (from which the table_info and table_xinfo draw their information) does contain the name of the collation being used if it is not the default (and also the actual affinity of the column), however the current table_info/table_xinfo does not return that information, although modifications to do so would be rather trivial.

Richard, would you like a patch for this (and a database_info pragma, which would allow all the introspection pragma tables to work across all attached databases more easily)?

As for views, they are a sort of dynamic thing so the collating sequence (nor the affinity) of a view column is not known until the statement using the view is prepared (a view is merely the storage of a definition and nothing is really known about it until it is used).

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.





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

Re: Determining column collating functions

Thomas Kurz
Would you consider implementing this not as a pragma, but as a real statement, like MySQL's SHOW COLUMNS (https://dev.mysql.com/doc/refman/5.5/en/show-columns.html)? Would be easier to memorize.

----- Original Message -----
From: Keith Medcalf <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Wednesday, August 14, 2019, 00:36:07
Subject: [sqlite] Determining column collating functions


On Tuesday, 13 August, 2019 15:59, Manuel Rigger <[hidden email]> wrote:

>Is there a simple way to determine the collating function of a
>column?

Presently, there is not.

>PRAGMA table_info does not seem to provide this information. The
>information could be extracted from sqlite_master, which contains the
>SQL statements used to create the table or view. While parsing the
>SQL string is rather straightforward for tables, it would involve
>more effort to determine the collating functions for views, which
>can again reference other views or tables.

The internal schema representation for the column (from which the table_info and table_xinfo draw their information) does contain the name of the collation being used if it is not the default (and also the actual affinity of the column), however the current table_info/table_xinfo does not return that information, although modifications to do so would be rather trivial.

Richard, would you like a patch for this (and a database_info pragma, which would allow all the introspection pragma tables to work across all attached databases more easily)?

As for views, they are a sort of dynamic thing so the collating sequence (nor the affinity) of a view column is not known until the statement using the view is prepared (a view is merely the storage of a definition and nothing is really known about it until it is used).

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users