Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Peter Halasz]

Hi Richard,

Thank you for replying personally. Apologies in advance if I use the wrong
terminology. I haven't looked at SQLite since the previous discussion in
this thread, as I've been waiting for this needed "feature".

I just have to say you treat ROWID like it's your quasimodo. A thing you
need to cover up and pretend doesn't exist.

Well, it's there. As you say, you're supporting it for at least three more
decades. Why not document it properly? It deserves more than a highly obtuse
mention on your Datatypes page. Why not make a PRAGMA statement to allow
tools to introspect the thing? Please. The code is already there for you.

The alternative to supporting table_ipk pragma is to support this
practically undocumented method, which I'll paste again in full. What you,
Richard Hipp, are saying to me, is that you propose to keep these opcodes
outputs from these EXPLAIN statements identical for 30 years:

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 p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 8 000  Start at 8
1 OpenRead   0 3 0 0  00  root=3 iDb=0; rowidPK
2 Explain0 0 0 SCAN TABLE rowidPK  00
3 Rewind 0 7 000
4   Rowid  0 1 000  r[1]=rowid
5   ResultRow  1 1 000  output=r[1]
6 Next   0 4 001
7 Halt   0 0 000
8 Transaction0 0 100  01  usesStmtJournal=0
9 Goto   0 1 000

sqlite> explain select x from separatePK not indexed;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 8 000  Start at 8
1 OpenRead   0 4 0 1  00  root=4
iDb=0; separatePK
2 Explain0 0 0 SCAN TABLE separatePK  00
3 Rewind 0 7 000
4   Column 0 0 100  r[1]=separatePK.x
5   ResultRow  1 1 000  output=r[1]
6 Next   0 4 001
7 Halt   0 0 000
8 Transaction0 0 100  01  usesStmtJournal=0
9 Goto   0 1 000

sqlite>


Because there is no other reliable way to access this information.

There's currently no way to reliably access IPK information. Tools that are
built around Sqlite need to have access to the specific information about
what its columns contain. Existing tools and libraries try very hard to
support SQLite but they are buggy and error prone. Why? Simply because of
the lack of any way to reasonably introspect.

The advice I was give in this thread was to write my own SQL parser -- I
was literally given that advice here. Yes. I know you've done that, but it
doesn't make sense for everyone else to too.

table_ipk pragma is not designed as some new fancy add-on feature like JSON
storage or 4D coordinate data; the feature is precisely to support the
database and its existing design choices. It's because you're supporting
the legacy of SQLite that tool developers need to be able to introspect
whether a table has an IPK column.

Parsing explain statements just doesn't make sense. You want to support the
exact output of those EXPLAIN statements for 30 years?

Not having a table_ipk pragma would only make sense if you were planning a
complete redesign which had different datatype behaviors. As you clearly
planning NOT to do that (and fairly so), it's frankly insane that you
wouldn't consider adding a way to introspect the existence of an IPK column.

All I'm asking is you embrace the decisions of the past and make them
visible to users and tool makers that need to introspect databases. (And
seriously, please including IPK as an actual type on
https://www.sqlite.org/datatype3.html. Embrace IPK. Let it out into the
light. Please. If you're going to have a fancy table showing how INT has an
affinity for INTEGER, state the giant hunchback of an exception somewhere
in the same table or at least directly under it.)

Please just make the datatypes of your fields programmatically accessible.
I want to keep us

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Richard Hipp]

On 3/5/18, Peter Halasz  wrote:
>  The "table_ipk" pragma statement didn't seem to make it into 3.22. Is
> there any reason it hasn't been merged?

Lack of perceived benefit.

Our goal is to support SQLite for 3 more decades.  Every new feature
that is added has to be supported, therefore, for 30 years.  This
encourages us to avoid adding new features whenever possible.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Peter Halasz]

 The "table_ipk" pragma statement didn't seem to make it into 3.22. Is
there any reason it hasn't been merged?

There is still no other reasonable way for third party tools to reliably
tell if a column is an "integer primary key" or just a primary key which
happens to be an integer.

https://www.sqlite.org/src/timeline?p=2494132a2b1221a4


On Wed, Nov 29, 2017 at 1:24 PM, Peter Halasz 
wrote:

> That's terrific! Looking forward to it being in a full release.
>
> Hopefully the next tool developer to come along won't need to write a
> custom SQL parser, get lost in irrelevant datatype documentation, sift
> through op codes from an unnestable "explain" statement, compile a custom
> SQLite C extension, re-implement SQLite's ROWID logic, or need to sign up
> to this mailing list--which has genuinely been very helpful and
> enlightening--because there will be a "table_ipk" pragma statement that
> does what they need.
>
> Cheers,
>
> Peter H.  (Pengo)
>
>
> On Wed, Nov 29, 2017 at 3:10 AM, petern 
> wrote:
>
>> Hey Peter.  Good on you.  Lobbying for sensible fixes to the public API
>> does pay off sometimes.
>>
>> There's a new branch in the timeline.  [Watch for a merge here:
>> https://www.sqlite.org/src/timeline?n=50 ]
>>
>> https://www.sqlite.org/src/info/2494132a2b1221a4
>>
>>   **   PRAGMA table_ipk()  **  ** If  has an INTEGER
>> PRIMARY KEY column that is an alias for  ** the ROWID, then return the
>> name of that column.  If  does not  ** have a ROWID alias, or
>> if it does not have a ROWID, or if  is  ** a view or virtual
>> table or if it does not exist, then return no rows.
>>
>> The lesson here is that one may safely ignore the sometimes authoritative
>> sounding status quo mafia who occupy this mailing list but have no source
>> check-in credentials whatsoever.
>>
>> On Mon, Nov 27, 2017 at 4:38 PM, Peter Halasz 
>> wrote:
>>
>> > Disclaimer: I am not a developer on linq2db. Nor am I a developer on "DB
>> > Browser for SQLite", nor am I a developer of any of the other tools
>> which
>> > help developers create SQLite databases and integrate them with their
>> > projects through UIs and APIs built on SQLite and its metadata.
>> >
>> > 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.
>> >
>> > linq2db, for example, has no simple way of definitely telling whether a
>> > field is a true INTEGER PRIMARY KEY, so its developers have left it as
>> an
>> > exercise for its library users to debug errors which come up as a
>> result,
>> > and to manually "correct column definitions". Something which can
>> result in
>> > much time wasted unnecessarily tracking down the bugs, finding special
>> API
>> > calls to work around them, and a less than favorable view of SQLite for
>> its
>> > documentation of these anomalies.
>> >
>> > linq2db could, in theory, fix this issue. But it would take a bunch of
>> > error-prone work to re-create SQLite's ROW ID rules, so they have chosen
>> > not to. They have simply chosen to ignore the weird behavior that comes
>> > form not differentiating an INTEGER PRIMARY KEY from an INT PRIMARY
>> KEY. I
>> > don't blame them for having other priorities. After all, linq2db
>> integrates
>> > with 12 other database engines. And this isn't about linq2db, it's about
>> > every tool that comes across the need for this metadata.
>> >
>> > Many other tools also have bugs or poor UX because they cannot access a
>> > simple bit of metadata. This could be solved quite easily if SQLite
>> simply
>> > exposed this data.
>> >
>> > I was honestly stunned when I discovered this basic information,
>> something
>> > which completely changes the behavior of a field, is not accessible to
>> > users.
>> >
>> > Please can SQLite developers make the ROW ID status of a field visible
>> in
>> > future versions?
>> >
>> > It could be done either directly through a PRAGMA statement, or slightly
>> > less directly by exposing "isRowId()" or "collseq()" style methods as
>> > posted by peter.nichvolodov in the previous thread.
>> >
>> > I really hope there is not resistance this idea. It's really amazing to
>> me
>> > that such a mature, battle-tested database engine does not fully expose
>> its
>> > metadata and field behavior.
>> >
>> > Cheers
>> >
>> > Peter H.
>> > ___
>> > 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/

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[sub sk79]

On Tue, Nov 28, 2017 at 9:33 PM, J. King  wrote:

> ...*that I never thought to ask*, many of which have been helpful in
> refining my application. ...
>

+1 that.
That is the main advantage of a mailing list over a forum with
filter-bubble or god forbid 'personalization'.
I even miss the keyword search engines of yesteryear for same reason. The
to-the-pointedness of google makes me dumb.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[J. King]

Please forgive the off-topicedness of this message, but I would like to second 
that this mailing list is incredibly edifying, and I would also like thank 
everyone who has both answered and asked questions in the time I've been 
subscribed. I've stumbled across answers here that I never thought to ask, many 
of which have been helpful in refining my application. My reading here has been 
quite invaluable. 

On November 28, 2017 9:24:57 PM EST, Peter Halasz  
wrote:
>That's terrific! Looking forward to it being in a full release.
>
>Hopefully the next tool developer to come along won't need to write a
>custom SQL parser, get lost in irrelevant datatype documentation, sift
>through op codes from an unnestable "explain" statement, compile a
>custom
>SQLite C extension, re-implement SQLite's ROWID logic, or need to sign
>up
>to this mailing list--which has genuinely been very helpful and
>enlightening--because there will be a "table_ipk" pragma statement that
>does what they need.
>
>Cheers,
>
>Peter H.  (Pengo)
>
>On Wed, Nov 29, 2017 at 3:10 AM, petern 
>wrote:
>
>> Hey Peter.  Good on you.  Lobbying for sensible fixes to the public
>API
>> does pay off sometimes.
>>
>> There's a new branch in the timeline.  [Watch for a merge here:
>> https://www.sqlite.org/src/timeline?n=50 ]
>>
>> https://www.sqlite.org/src/info/2494132a2b1221a4
>>
>>   **   PRAGMA table_ipk()  **  ** If  has an INTEGER
>> PRIMARY KEY column that is an alias for  ** the ROWID, then return
>the
>> name of that column.  If  does not  ** have a ROWID alias, or
>> if it does not have a ROWID, or if  is  ** a view or virtual
>> table or if it does not exist, then return no rows.
>>
>> The lesson here is that one may safely ignore the sometimes
>authoritative
>> sounding status quo mafia who occupy this mailing list but have no
>source
>> check-in credentials whatsoever.
>>
>> On Mon, Nov 27, 2017 at 4:38 PM, Peter Halasz
>
>> wrote:
>>
>> > Disclaimer: I am not a developer on linq2db. Nor am I a developer
>on "DB
>> > Browser for SQLite", nor am I a developer of any of the other tools
>which
>> > help developers create SQLite databases and integrate them with
>their
>> > projects through UIs and APIs built on SQLite and its metadata.
>> >
>> > 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.
>> >
>> > linq2db, for example, has no simple way of definitely telling
>whether a
>> > field is a true INTEGER PRIMARY KEY, so its developers have left it
>as an
>> > exercise for its library users to debug errors which come up as a
>result,
>> > and to manually "correct column definitions". Something which can
>result
>> in
>> > much time wasted unnecessarily tracking down the bugs, finding
>special
>> API
>> > calls to work around them, and a less than favorable view of SQLite
>for
>> its
>> > documentation of these anomalies.
>> >
>> > linq2db could, in theory, fix this issue. But it would take a bunch
>of
>> > error-prone work to re-create SQLite's ROW ID rules, so they have
>chosen
>> > not to. They have simply chosen to ignore the weird behavior that
>comes
>> > form not differentiating an INTEGER PRIMARY KEY from an INT PRIMARY
>KEY.
>> I
>> > don't blame them for having other priorities. After all, linq2db
>> integrates
>> > with 12 other database engines. And this isn't about linq2db, it's
>about
>> > every tool that comes across the need for this metadata.
>> >
>> > Many other tools also have bugs or poor UX because they cannot
>access a
>> > simple bit of metadata. This could be solved quite easily if SQLite
>> simply
>> > exposed this data.
>> >
>> > I was honestly stunned when I discovered this basic information,
>> something
>> > which completely changes the behavior of a field, is not accessible
>to
>> > users.
>> >
>> > Please can SQLite developers make the ROW ID status of a field
>visible in
>> > future versions?
>> >
>> > It could be done either directly through a PRAGMA statement, or
>slightly
>> > less directly by exposing "isRowId()" or "collseq()" style methods
>as
>> > posted by peter.nichvolodov in the previous thread.
>> >
>> > I really hope there is not resistance this idea. It's really
>amazing to
>> me
>> > that such a mature, battle-tested database engine does not fully
>expose
>> its
>> > metadata and field behavior.
>> >
>> > Cheers
>> >
>> > Peter H.
>> > ___
>> > 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
>sqlit

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Peter Halasz]

That's terrific! Looking forward to it being in a full release.

Hopefully the next tool developer to come along won't need to write a
custom SQL parser, get lost in irrelevant datatype documentation, sift
through op codes from an unnestable "explain" statement, compile a custom
SQLite C extension, re-implement SQLite's ROWID logic, or need to sign up
to this mailing list--which has genuinely been very helpful and
enlightening--because there will be a "table_ipk" pragma statement that
does what they need.

Cheers,

Peter H.  (Pengo)

On Wed, Nov 29, 2017 at 3:10 AM, petern  wrote:

> Hey Peter.  Good on you.  Lobbying for sensible fixes to the public API
> does pay off sometimes.
>
> There's a new branch in the timeline.  [Watch for a merge here:
> https://www.sqlite.org/src/timeline?n=50 ]
>
> https://www.sqlite.org/src/info/2494132a2b1221a4
>
>   **   PRAGMA table_ipk()  **  ** If  has an INTEGER
> PRIMARY KEY column that is an alias for  ** the ROWID, then return the
> name of that column.  If  does not  ** have a ROWID alias, or
> if it does not have a ROWID, or if  is  ** a view or virtual
> table or if it does not exist, then return no rows.
>
> The lesson here is that one may safely ignore the sometimes authoritative
> sounding status quo mafia who occupy this mailing list but have no source
> check-in credentials whatsoever.
>
> On Mon, Nov 27, 2017 at 4:38 PM, Peter Halasz 
> wrote:
>
> > Disclaimer: I am not a developer on linq2db. Nor am I a developer on "DB
> > Browser for SQLite", nor am I a developer of any of the other tools which
> > help developers create SQLite databases and integrate them with their
> > projects through UIs and APIs built on SQLite and its metadata.
> >
> > 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.
> >
> > linq2db, for example, has no simple way of definitely telling whether a
> > field is a true INTEGER PRIMARY KEY, so its developers have left it as an
> > exercise for its library users to debug errors which come up as a result,
> > and to manually "correct column definitions". Something which can result
> in
> > much time wasted unnecessarily tracking down the bugs, finding special
> API
> > calls to work around them, and a less than favorable view of SQLite for
> its
> > documentation of these anomalies.
> >
> > linq2db could, in theory, fix this issue. But it would take a bunch of
> > error-prone work to re-create SQLite's ROW ID rules, so they have chosen
> > not to. They have simply chosen to ignore the weird behavior that comes
> > form not differentiating an INTEGER PRIMARY KEY from an INT PRIMARY KEY.
> I
> > don't blame them for having other priorities. After all, linq2db
> integrates
> > with 12 other database engines. And this isn't about linq2db, it's about
> > every tool that comes across the need for this metadata.
> >
> > Many other tools also have bugs or poor UX because they cannot access a
> > simple bit of metadata. This could be solved quite easily if SQLite
> simply
> > exposed this data.
> >
> > I was honestly stunned when I discovered this basic information,
> something
> > which completely changes the behavior of a field, is not accessible to
> > users.
> >
> > Please can SQLite developers make the ROW ID status of a field visible in
> > future versions?
> >
> > It could be done either directly through a PRAGMA statement, or slightly
> > less directly by exposing "isRowId()" or "collseq()" style methods as
> > posted by peter.nichvolodov in the previous thread.
> >
> > I really hope there is not resistance this idea. It's really amazing to
> me
> > that such a mature, battle-tested database engine does not fully expose
> its
> > metadata and field behavior.
> >
> > Cheers
> >
> > Peter H.
> > ___
> > 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

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[petern]

Hey Peter.  Good on you.  Lobbying for sensible fixes to the public API
does pay off sometimes.

There's a new branch in the timeline.  [Watch for a merge here:
https://www.sqlite.org/src/timeline?n=50 ]

https://www.sqlite.org/src/info/2494132a2b1221a4

  **   PRAGMA table_ipk()  **  ** If  has an INTEGER
PRIMARY KEY column that is an alias for  ** the ROWID, then return the
name of that column.  If  does not  ** have a ROWID alias, or
if it does not have a ROWID, or if  is  ** a view or virtual
table or if it does not exist, then return no rows.

The lesson here is that one may safely ignore the sometimes authoritative
sounding status quo mafia who occupy this mailing list but have no source
check-in credentials whatsoever.

On Mon, Nov 27, 2017 at 4:38 PM, Peter Halasz 
wrote:

> Disclaimer: I am not a developer on linq2db. Nor am I a developer on "DB
> Browser for SQLite", nor am I a developer of any of the other tools which
> help developers create SQLite databases and integrate them with their
> projects through UIs and APIs built on SQLite and its metadata.
>
> 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.
>
> linq2db, for example, has no simple way of definitely telling whether a
> field is a true INTEGER PRIMARY KEY, so its developers have left it as an
> exercise for its library users to debug errors which come up as a result,
> and to manually "correct column definitions". Something which can result in
> much time wasted unnecessarily tracking down the bugs, finding special API
> calls to work around them, and a less than favorable view of SQLite for its
> documentation of these anomalies.
>
> linq2db could, in theory, fix this issue. But it would take a bunch of
> error-prone work to re-create SQLite's ROW ID rules, so they have chosen
> not to. They have simply chosen to ignore the weird behavior that comes
> form not differentiating an INTEGER PRIMARY KEY from an INT PRIMARY KEY. I
> don't blame them for having other priorities. After all, linq2db integrates
> with 12 other database engines. And this isn't about linq2db, it's about
> every tool that comes across the need for this metadata.
>
> Many other tools also have bugs or poor UX because they cannot access a
> simple bit of metadata. This could be solved quite easily if SQLite simply
> exposed this data.
>
> I was honestly stunned when I discovered this basic information, something
> which completely changes the behavior of a field, is not accessible to
> users.
>
> Please can SQLite developers make the ROW ID status of a field visible in
> future versions?
>
> It could be done either directly through a PRAGMA statement, or slightly
> less directly by exposing "isRowId()" or "collseq()" style methods as
> posted by peter.nichvolodov in the previous thread.
>
> I really hope there is not resistance this idea. It's really amazing to me
> that such a mature, battle-tested database engine does not fully expose its
> metadata and field behavior.
>
> Cheers
>
> Peter H.
> ___
> 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] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[David Raymond]

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 p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 8 000  Start at 8
1 OpenRead   0 3 0 0  00  root=3 iDb=0; rowidPK
2 Explain0 0 0 SCAN TABLE rowidPK  00
3 Rewind 0 7 000
4   Rowid  0 1 000  r[1]=rowid
5   ResultRow  1 1 000  output=r[1]
6 Next   0 4 001
7 Halt   0 0 000
8 Transaction0 0 100  01  usesStmtJournal=0
9 Goto   0 1 000

sqlite> explain select x from separatePK not indexed;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 8 000  Start at 8
1 OpenRead   0 4 0 1  00  root=4 iDb=0; 
separatePK
2 Explain0 0 0 SCAN TABLE separatePK  00
3 Rewind 0 7 000
4   Column 0 0 100  r[1]=separatePK.x
5   ResultRow  1 1 000  output=r[1]
6 Next   0 4 001
7 Halt   0 0 000
8 Transaction0 0 100  01  usesStmtJournal=0
9 Goto   0 1 000

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

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Clemens Ladisch]

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

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Keith Medcalf]

>Can you not deduce what you want using sqlite_sequence, as described
>in section 2.6.1 of

>

>?  This allows you to distinguish a primary key which happens to be
>an integer from an |NTEGER PRIMARY KEY.

No it does not.  It only identifies AUTOINCREMENT INTEGER PRIMARY KEY since 
only AUTOINCREMENT fields are in sqlite_sequence, only if a row has been 
inserted, and AUTOINCREMENT fields just happen to also have to be INTEGER 
PRIMARY KEY.

>Another useful thing might be to find the command used to create the
>table from the sqlite_master table and see whether it includes the
>text "INTEGER PRIMARY KEY".

Except that will not work.

create table x
(
  y INTEGER,
  z TEXT,
  PRIMARY KEY(y)
);

Also identifies y as the INTEGER PRIMARY KEY but does not contain the spelling 
"INTEGER PRIMARY KEY" anywhere within it.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Simon Slavin]

On 28 Nov 2017, at 12:38am, Peter Halasz  wrote:

> Please can SQLite developers make the ROW ID status of a field visible in
> future versions?

If it’s cone, the obvious place would be to add a column to



Can you not deduce what you want using sqlite_sequence, as described in section 
2.6.1 of



?  This allows you to distinguish a primary key which happens to be an integer 
from an |NTEGER PRIMARY KEY.

Another useful thing might be to find the command used to create the table from 
the sqlite_master table and see whether it includes the text "INTEGER PRIMARY 
KEY".

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Peter Halasz]

Disclaimer: I am not a developer on linq2db. Nor am I a developer on "DB
Browser for SQLite", nor am I a developer of any of the other tools which
help developers create SQLite databases and integrate them with their
projects through UIs and APIs built on SQLite and its metadata.

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.

linq2db, for example, has no simple way of definitely telling whether a
field is a true INTEGER PRIMARY KEY, so its developers have left it as an
exercise for its library users to debug errors which come up as a result,
and to manually "correct column definitions". Something which can result in
much time wasted unnecessarily tracking down the bugs, finding special API
calls to work around them, and a less than favorable view of SQLite for its
documentation of these anomalies.

linq2db could, in theory, fix this issue. But it would take a bunch of
error-prone work to re-create SQLite's ROW ID rules, so they have chosen
not to. They have simply chosen to ignore the weird behavior that comes
form not differentiating an INTEGER PRIMARY KEY from an INT PRIMARY KEY. I
don't blame them for having other priorities. After all, linq2db integrates
with 12 other database engines. And this isn't about linq2db, it's about
every tool that comes across the need for this metadata.

Many other tools also have bugs or poor UX because they cannot access a
simple bit of metadata. This could be solved quite easily if SQLite simply
exposed this data.

I was honestly stunned when I discovered this basic information, something
which completely changes the behavior of a field, is not accessible to
users.

Please can SQLite developers make the ROW ID status of a field visible in
future versions?

It could be done either directly through a PRAGMA statement, or slightly
less directly by exposing "isRowId()" or "collseq()" style methods as
posted by peter.nichvolodov in the previous thread.

I really hope there is not resistance this idea. It's really amazing to me
that such a mature, battle-tested database engine does not fully expose its
metadata and field behavior.

Cheers

Peter H.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users