On Sunday, 26 November, 2017 18:00, petern <peter.nichvolo...@gmail.com> wrote:
>First off, Affinity (aka pragma table_info.type) column is case >collated, so the LIKE operator should be used: Yes, but it is always lowercase. Just as the ObjectType (sqlite_master.type) is always lowercase. >select O.* > from SysColumns as O > where ObjectType == 'table' > and IsPrimaryKey == 1 > and Affinity LIKE 'INTEGER' > and not exists (select 1 > from SysColumns as I > where I.ObjectType == O.ObjectType > and I.ObjectName == O.ObjectName > and I.IsPrimaryKey > 1); > >[Side question. Do you find the '==' operator more >readable/informative than the plain '=' SQL convention?] Sometimes. Many languages require "==" for comparisons, so sometimes one gets it the habit. One can also use IS which will mean that NULL IS NULL returns True (1) whereas NULL == NULL (or NULL = NULL) will return False (0). In cases where only one of the operands is NULL there is no difference between =, ==, and IS ... The version below puts a collate nocase against each text column ... and each view should be flattened nicely to boot ... drop view if exists SysIndexColumns; drop view if exists SysIndexes; drop view if exists SysColumns; drop view if exists SysObjects; create view if not exists SysObjects as select ObjectType collate nocase, ObjectName collate nocase from ( select type as ObjectType, name as ObjectName from sqlite_master where type in ('table', 'view', 'index') ); create view if not exists SysColumns as select ObjectType collate nocase, ObjectName collate nocase, ColumnID collate nocase, ColumnName collate nocase, Affinity collate nocase, IsNotNull, DefaultValue, IsPrimaryKey from ( select ObjectType, ObjectName, cid as ColumnID, name as ColumnName, type as Affinity, "notnull" as IsNotNull, dflt_value as DefaultValue, pk as IsPrimaryKey from SysObjects join pragma_table_info(ObjectName) ); create view if not exists SysIndexes as select ObjectType collate nocase, ObjectName collate nocase, IndexName collate nocase, IndexID, IsUniqueIndex collate nocase, IndexOrigin collate nocase, IsPartialIndex from ( select ObjectType, ObjectName, name as IndexName, seq as IndexID, "unique" as IsUniqueIndex, origin as IndexOrigin, partial as IsPartialIndex from SysObjects join pragma_index_list(ObjectName) ); create view if not exists SysIndexColumns as select ObjectType collate nocase, ObjectName collate nocase, IndexName collate nocase, IndexColumnSequence, ColumnID, ColumnName collate nocase, IsDescendingOrder, Collation collate nocase, IsPartOfKey from ( select ObjectType, ObjectName, IndexName, seqno as IndexColumnSequence, cid as ColumnID, name as ColumnName, "desc" as IsDescendingOrder, coll as Collation, key as IsPartOfKey from SysIndexes join pragma_index_xinfo(IndexName) ); >On Sun, Nov 26, 2017 at 1:41 PM, Keith Medcalf <kmedc...@dessus.com> >wrote: > >> Not an answer to what is wrong with your call to get the metadata, >> however, the information is all available from SQL. >> >> Data Catalog Views: >> >> drop view SysIndexColumns; >> drop view SysIndexes; >> drop view SysColumns; >> drop view SysObjects; >> >> create view if not exists SysObjects >> as >> select type as ObjectType, >> name as ObjectName >> from sqlite_master >> where type in ('table', 'view', 'index'); >> >> create view if not exists SysColumns >> as >> select ObjectType, >> ObjectName, >> cid as ColumnID, >> name as ColumnName, >> type as Affinity, >> "notnull" as IsNotNull, >> dflt_value as DefaultValue, >> pk as IsPrimaryKey >> from SysObjects >> join pragma_table_info(ObjectName); >> >> create view if not exists SysIndexes >> as >> select ObjectType, >> ObjectName, >> name as IndexName, >> seq as IndexID, >> "unique" as IsUniqueIndex, >> origin as IndexOrigin, >> partial as IsPartialIndex >> from SysObjects >> join pragma_index_list(ObjectName); >> >> create view if not exists SysIndexColumns >> as >> select ObjectType, >> ObjectName, >> IndexName, >> seqno as IndexColumnSequence, >> cid as ColumnID, >> name as ColumnName, >> "desc" as IsDescendingOrder, >> coll as Collation, >> key as IsPartOfKey >> from SysIndexes >> join pragma_index_xinfo(IndexName); >> >> Then >> >> select O.* >> from SysColumns as O >> where ObjectType == 'table' >> and IsPrimaryKey == 1 >> and Affinity == 'integer' >> and not exists (select 1 >> from SysColumns as I >> where I.ObjectType == O.ObjectType >> and I.ObjectName == O.ObjectName >> and I.IsPrimaryKey > 1); >> >> will return all the 'INTEGER PRIMARY KEY' in the "main" database >... >> >> I do not know what it does for "without rowid" tables where there >is a >> single field declared as "integer primary key", if you have some of >those >> you will have to figure it out (and how to eliminate them if you >want to do >> so) yourself. >> >> --- >> The fact that there's a Highway to Hell but only a Stairway to >Heaven says >> a lot about anticipated traffic volume. >> >> >-----Original Message----- >> >From: sqlite-users [mailto:sqlite-users- >> >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert >> >Sent: Sunday, 26 November, 2017 13:14 >> >To: SQLite mailing list >> >Subject: Re: [sqlite] How to use sqlite3_table_column_metadata? >> > >> >I am passing a pointer to UTF encoded strings and the passed table >> >and >> >column do exist. >> >There must be something wrong in my code. >> > >> >RBS >> > >> > >> > >> > >> >On Sun, Nov 26, 2017 at 7:56 PM, Clemens Ladisch ><clem...@ladisch.de> >> >wrote: >> > >> >> Bart Smissaert wrote: >> >> > Having some difficulty using sqlite3_table_column_metadata, >used >> >from >> >> VB6. >> >> > >> >> > I keep getting a zero for the first output argument (should be >> >pointer >> >> to declared data type string) >> >> > and the return value of the function keeps giving 1. >> >> >> >> The documentation >> ><http://www.sqlite.org/c3ref/table_column_metadata.html> >> >> says: >> >> | The sqlite3_table_column_metadata() interface returns >> >SQLITE_ERROR and >> >> if the specified column does not exist. >> >> >> >> > Should the tbl_name and column_name also be encoded in UTF8? >> >> >> >> In SQLite, pretty much all strings are UTF-8. >> >> >> >> >> >> Regards, >> >> Clemens >> >> _______________________________________________ >> >> 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 >> >> >> >> _______________________________________________ >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users