On Sunday, 26 November, 2017 18:00, petern <[email protected]> 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 <[email protected]>
>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-
>> >[email protected]] 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
><[email protected]>
>> >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
>> >> [email protected]
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> >users
>> >>
>> >_______________________________________________
>> >sqlite-users mailing list
>> >[email protected]
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
