Apparently you would query pragma_table_info for "INTEGER" PK columns and then ask through a trivial extension function about the other column meta-data:
https://www.sqlite.org/c3ref/table_column_metadata.html The basic plot is illustrated below: sqlite> .load column-meta-data.so sqlite> SELECT *,isRowId(name) FROM pragma_table_info('the_table') WHERE pk AND type='INTEGER' cid,name,type,notnull,dflt_value,pk,"isRowId('the_table',name)" ...1 ...etc Your only serious problem, I think, would be to distribute the column-meta-data.c extension with your tool. On Fri, Nov 24, 2017 at 3:13 PM, Peter Halasz <pe...@becauseofgames.com> wrote: > Sorry to steer the conversation back to the topic. > > Looks like I do need to use AUTOINCREMENT after all, otherwise the > framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY > columns are allowed a NULL value on insert. That's a shame. > > Is there a way to query a schema to get a direct answer to whether table > has a column which acts as the ROW ID alias? I'd like to improve linq2db's > code generation if I can, but I can't see any straightforward way to do it > as the ROW ID information is hidden (in SQLite's API, not just its > documentation) > > I found someone asking the same question on this mailing list in 2010 who > was told to: [1] > > "look at column 'sql' of TABLE sqlite_master and parse the creation > statement" > > Which is frankly ridiculous. > > I'm hoping there's an actual answer 7 years later that doesn't involve > implementing a SQL parser? (* please *don't even make suggestions on how to > do this) > > I can't find anything in the pragma documentation. [2] The closest I could > see is "*PRAGMA **index_xinfo(**index-name**);*" which will give > information on whether an index references a rowid. I tried creating an > index for a column just to test if it was the ROW ID but it doesn't > actually work that way. > > On the page https://www.sqlite.org/rowidtable.html it states: > > - > > All of the complications above (and others not mentioned here) arise > from the need to preserve backwards compatibility to the tens of > billions > of SQLite database files in circulation. In a perfect world, there > would be > no such thing as a "rowid" and all tables would following the standard > semantics implemented as WITHOUT ROWID tables, only without the extra > "WITHOUT ROWID" keywords. Unfortunately, life is messy. The designer of > SQLite offers his sincere apology for the current mess. > > So it appears ROWID is sticking around. Why is it hidden in the > documentation (not listed on on pages like the datatype page), and hidden > in the pragma interface as well? > > Is there an API call or PRAGMA statement that gives this information > directly? > > Thanks. > > > [1] > http://sqlite.1065341.n5.nabble.com/Introspection-and- > RowID-INTEGER-PRIMARY-KEY-td60462.html > [2] https://sqlite.org/pragma.html > _______________________________________________ > 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
