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 <pe...@becauseofgames.com>
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

Reply via email to