[sqlite] Does sqlite always allow creating own field named 'rowid'?
While implementing a table that intended for arbitrary table storage, I automatically named some field rowid not linking at the moment that it will have a name conflict with sqlite internal rowid name. Lately I discovered this, and (not a big deal) recreated table with a different name. But just wondering, was allowing to create such field intentional? As I suppose such field is complete phantom since most sql queries will interpret rowid internally and won't allow access this user field at all. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite always allow creating own field named 'rowid'?
While implementing a table that intended for arbitrary table storage, I automatically named some field rowid not linking at the moment that it will have a name conflict with sqlite internal rowid name. Lately I discovered this, and (not a big deal) recreated table with a different name. But just wondering, was allowing to create such field intentional? As I suppose such field is complete phantom since most sql queries will interpret rowid internally and won't allow access this user field at all. Sqlite works by giving priority to the column names defined in the table. So if you have a column named rowid then it will access this rather than the internal rowid. Therefore, there is more than one way of accessing this internal rowid: rowid, _rowid_ and oid. Of course if you define a column rowid integer primary key then the declared column and the internal rowid are one and the same... http://www.sqlite.org/lang_createtable.html#rowid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite always allow creating own field named 'rowid'?
On Wed, May 26, 2010 at 6:19 PM, Pavel Ivanov paiva...@gmail.com wrote: But just wondering, was allowing to create such field intentional? As I suppose such field is complete phantom since most sql queries will interpret rowid internally and won't allow access this user field at all. Situation is completely opposite. When you declare your field named rowid the real rowid becomes phantom and all queries will select your field instead. But real rowid will be still accessible via other names like _rowid_ or oid (is there a way to access real rowid when all three built-in names are declared by user in the table, I wonder?). And this behavior is documented (see http://www.sqlite.org/lang_createtable.html) and thus I suppose intended and introduced long ago. Ok, it makes sense. My only complain is that although rowid is a sqlite specific word, it became more of reserved words. One example (maybe too specific). One works with tables with a some sqlite admin and some imported tables from other database engine contains its own rowid field. As everyone knows every sqlite table has id regardless of the developer intention, so I assume that I can query SELECT rowid ... for almost every existing sqlite table in the world. But with this hypothetical case the results of such query even can be puzzling, for example, if the data don't declare uniqueness limitation on its own rowid field, we could see repeating values or even non numeric values. I think that using _rowid_ might be additional guarantee from such problems, but I suppose many developers still prefer rowid. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
