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 <peter.nichvolo...@gmail.com> 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 <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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users