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. > *shrug* it probably is ridiculous, but I did anyway.... XDataTable extends DataTable and adds foriegn key relations; otherwise you need the full DataSet to deal with some table-oriented things (like foreign keys) https://sourceforge.net/p/xperdex/mercurial/ci/default/tree/xperdex.classes.2/XDataTable.cs This is a parser that can either generate a create table statement from a datatable, or a datatable from a create table statement. (Or merge a data table filled with existing columns and merge it with what's already in a database) https://sourceforge.net/p/xperdex/mercurial/ci/default/tree/xperdex.classes.2/SQL_Utilities.cs I use this to parse the create table statement into reasonable tokens. https://sourceforge.net/p/xperdex/mercurial/ci/default/tree/xperdex.classes.2/Types/XString.cs The above can probably be disected from the full surrounding library with a little work... Some features of XDataTable should probably be removed... the first version automatically synced to the database when rows were added/deleted/modified.... I eventually moved that behind an option 'live' that if 'live' do the inserts directly; otherwise wait, and then later, a full dataset (XDataSet) can sync all changes in a dataset in appropriate order so foriegn key parent keys are inserted first. It has a personality selection also, based on information from DsnConnection class, which tells it whether it is SQL Server, MySQL or Sqlite, so generation of types for columns in the database form the types in the DataTable change depending on the flavor of database, and constraint/foriegn key generation changes also.... > 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
