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

classic Classic list List threaded Threaded
12 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
Reply | Threaded
Open this post in threaded view
|

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

Peter Halasz
In reply to this post by 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 <[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
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 3/5/18, Peter Halasz <[hidden email]> 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
[hidden email]
_______________________________________________
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
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         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>


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 using SQLite. I understand it has a large legacy to support.
But you can do that without fucking over tool developers and all the users
who use or wish to use those tools such as myself.

Thanks for listening.

Peter Halasz.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users