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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3


On 28 Nov 2017, at 12:38am, Peter Halasz <[hidden email]> 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

<https://sqlite.org/pragma.html#pragma_table_info>

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

<https://www.sqlite.org/fileformat2.html#internal_schema_objects>

?  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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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

><https://www.sqlite.org/fileformat2.html#internal_schema_objects>

>?  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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Clemens Ladisch
In reply to this post by Peter Halasz
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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         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:[hidden email]] On Behalf Of Clemens Ladisch
Sent: Tuesday, November 28, 2017 3:39 AM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

petern
In reply to this post by Peter Halasz
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(<table>)  **  ** If <table> has an INTEGER
PRIMARY KEY column that is an alias for  ** the ROWID, then return the
name of that column.  If <table> does not  ** have a ROWID alias, or
if it does not have a ROWID, or if <table> 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 <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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(<table>)  **  ** If <table> has an INTEGER
> PRIMARY KEY column that is an alias for  ** the ROWID, then return the
> name of that column.  If <table> does not  ** have a ROWID alias, or
> if it does not have a ROWID, or if <table> 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 <[hidden email]>
> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

J. King-3
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 <[hidden email]> 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 <[hidden email]>
>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(<table>)  **  ** If <table> has an INTEGER
>> PRIMARY KEY column that is an alias for  ** the ROWID, then return
>the
>> name of that column.  If <table> does not  ** have a ROWID alias, or
>> if it does not have a ROWID, or if <table> 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
><[hidden email]>
>> 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
>> > [hidden email]
>> >
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users