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

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.


Peter H.
sqlite-users mailing list

Reply via email to