Ok, since this issue with column names has gone on long enough, and we seem to be getting nowhere, I would like to propose an API change.
Here is the rationale : Normally, a query does not need or can use "qualified" names (like something.something). An explicit, hard-coded query knows the structure of The result set, so it should/would not rely on column names as such. It is only generic code, and tool-level code that worries about these things. For example, an ODBC or OLEDB driver that uses sqlite underneath, needs to know the column *origin*. Also, the semantics and current buggy implementation of the pragmas relating to this issue, have been very frustrating to tool writers, I believe. So, what about this : A. Drop the pragmas (short_column and full_column) completely. They are stateful, which implies an extra headache for driver-level code. B. always name the columns using short names (ie just a column name, no qualification). If duplicate column names result in this way, either de-dupe them by adding a numeric suffix (ID, ID_1 etc) or just leave the duplicates alone. Higher-level code can handle this anyway. C. Add a new API: sqlite3_column_table(idx). It should return the table associated with a column, if it can be defined (and, by the way, it should probably NOT return the table ALIAS, as the current implementation of short_column_names does, but the real table name. who cares about the alias,anyway). If not known, return NIL. D. nice to have : this should work for views as well, either when a view is queried by itself, or if it is part of a join. In other words : If : Create table t1 (id,name) Create table t2(id,name) Create view v1 as select * from t1 Then: Select * from v1 Gives sqlite3_column_table(0)="t1", not "v1" (and perhaps a flag leting the user know that this came from a view) And, Select t2.*,v1.* from t2,v1 where t2.id=v1.id Also returns table name of "t1" for all columns coming from v1. I am not sure if this is doable, but it would be nice to have. Why ? Because a resolver engine, that generates sql, can effectively make views updateable. This proposed change would clean up the issue with the pragmas, give default column names that are compatible with all other sql engines I know of, *and* it would still give the ability to the interested user to find out the *origin* of a result set column. The idea can be expanded: we could also have this api function: Int sqlite3_column_origin(idx) Result: 0 = comes from table 1 = comes from view 2 = comes from expression Etc etc Why don't we vote on this issue, and if we can find enough support, try to convince DRH to implement it ? I, for one, am strongly in favor, since I am developing both an OLEDB driver (which I intend to make public), and a utility (SqliteExplorer) which would benefit a lot by such APIs, and I am sure I am not the only one.
