Re: [sqlite] Determining column collating functions
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 To: SQLite mailing list Sent: Wednesday, August 14, 2019, 00:36:07 Subject: [sqlite] Determining column collating functions On Tuesday, 13 August, 2019 15:59, Manuel Rigger 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining column collating functions
On Tuesday, 13 August, 2019 15:59, Manuel Rigger 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining column collating functions
On August 13, 2019 5:58:54 p.m. EDT, Manuel Rigger 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 >sqlite-users@mailinglists.sqlite.org >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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Determining column collating functions
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
