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