I pasted that SQL into a SQLite shell and tried it out.

First off,  Affinity (aka pragma table_info.type) column is case collated,
so the LIKE operator should be used:

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?]

The main problem is still AUTOINCREMENT. You may not use AI, but a tool
that reads others' schemas has to deal with that possibility.  As well, the
system table sqlite_sequence will have no rows about an AI column when that
AI table has no rows.  Therefore, the only general way, without calling the
internal parser as I did in the extension, is to partly parse the SQL of
the CREATE TABLE statement.

Partial parsing isn't too hard I suppose.  There are a couple facts about
the well formed CREATE statement that help.

1. The keywords PRIMARY KEY must be two words.
2. The keyword "AUTOINCREMENT" must come after "KEY" and before the next
comma or closing bracket.

From those facts it should be feasible to deduce the AUTOINCREMENT status
by use of the instr() and substr() functions.

Or, one could simply use the native -std=c90 style SQLite extension I
posted for the other Peter...

My advise to the VB guy would be to load the extension instead of trying to
get VB to marshal pointers from the C API.  Though, I suppose that isn't
the worst idea from recent posts.  Reading all the rows of a table in
Python to get the count() has to be the best one.  In fact, that one is a
classic job interview question.






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

Reply via email to