Along with the table_info pragma you could also look at the output of an 
explain statement to see how it gets the value. When it's a rowid table it uses 
a "Rowid" opcode, vs using a "Column" opcode for a non-rowid table.

sqlite> create table rowidPK
   ...> (
   ...>   x integer,
   ...>   y text,
   ...>   primary key (x)
   ...> );

sqlite> create table separatePK
   ...> (
   ...>   x integer primary key desc,
   ...>   y text
   ...> );

sqlite> pragma table_info(rowidPK);
cid|name|type|notnull|dflt_value|pk
0|x|integer|0||1
1|y|text|0||0

sqlite> pragma table_info(separatePK);
cid|name|type|notnull|dflt_value|pk
0|x|integer|0||1
1|y|text|0||0

sqlite> explain select x from rowidPK not indexed;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     8     0                    00  Start at 8
1     OpenRead       0     3     0     0              00  root=3 iDb=0; rowidPK
2     Explain        0     0     0     SCAN TABLE rowidPK  00
3     Rewind         0     7     0                    00
4       Rowid          0     1     0                    00  r[1]=rowid
5       ResultRow      1     1     0                    00  output=r[1]
6     Next           0     4     0                    01
7     Halt           0     0     0                    00
8     Transaction    0     0     10    0              01  usesStmtJournal=0
9     Goto           0     1     0                    00

sqlite> explain select x from separatePK not indexed;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     8     0                    00  Start at 8
1     OpenRead       0     4     0     1              00  root=4 iDb=0; 
separatePK
2     Explain        0     0     0     SCAN TABLE separatePK  00
3     Rewind         0     7     0                    00
4       Column         0     0     1                    00  r[1]=separatePK.x
5       ResultRow      1     1     0                    00  output=r[1]
6     Next           0     4     0                    01
7     Halt           0     0     0                    00
8     Transaction    0     0     10    0              01  usesStmtJournal=0
9     Goto           0     1     0                    00

sqlite>

It's too bad explain can't be used as a sub-query though.

sqlite> select opcode from (explain select x from rowidPK not indexed);
Error: near "select": syntax error


-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Clemens Ladisch
Sent: Tuesday, November 28, 2017 3:39 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? 
Needed by many tools

Peter Halasz wrote:
> However, it's quite clear to me, as only a user of just a few of these
> tools and libraries, that they would be improved greatly if they had a
> direct way of querying which field in a SQLite table was acting as an alias
> of ROW ID.

The rowid column must have the type "integer", and must be the only
column in the primary key:

SELECT name
FROM pragma_table_info('MyTable')
WHERE type = 'integer' COLLATE NOCASE
GROUP BY pk != 0
HAVING max(pk) = 1;

(Before version 3.16.0, this was harder without pragma_table_info().)

But there is no reliable way to check for the PRIMARY KEY DESC exception,
or whether a table is a WITHOUT ROWID table.  You could try to read the
rowid/oid/_rowid_ columns, as long as the table does not use all three
of these names for other columns.


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

Reply via email to