D. Richard Hipp wrote:
On Mon, 2005-02-28 at 11:54 -0700, Robert Simpson wrote:
Column Name - The name of the column as specified in the SELECT clause and what SQLite already generates Base Table - The base table the column came from or NULL if the column was computed Base Column - The base column of the table the column came from or NULL if the column was computed Catalog - The database the column came from or NULL if the column was computed.
OK. This is progress. Now I understand that people need the database, table, and column that resultset values originate from in order to automatically construct an appropriate UPDATE statement. That makes sense.
Wouldn't it be better to provide this information with a new API
rather than depend on a column naming convention? That would avoid
ambiguity in cases where users create dodgy column names that contain
characters like space and '.'
Yes, it sure would be better to use an API.
The second would naturally become sqlite3_column_table(), and the fourth sqlite3_column_database().
Unfortunately, the natural name for the third item, sqlite3_column_name(), is already used. However, rather than the column name, it returns the usual column heading. This is sometime the column name alone, and sometimes the table name and the column name separated by a period, depending upon the type of query, joined or not, and the column name pragma settings. It's too bad this wasn't called sqlite3_column_heading().
Anyway, to maintain backwards compatibility the new API should probably be sqlite3_column_short_name() to match the intent of the short_column_names pragma. It would always return the short name of the column.
I have a question about how column aliases will be handled. Given a table
create table t (a, b);
what is to be returned by
select a as b from t;
The column name is obviously 'b' because of the alias, but what should be returned as the base name, 'a' or 'b'? Or do all aliases effectively make their column a computed value so that it is read only, and therefore all the new API functions return a null string? I think this is the issue the proposed real_column_names pragma was intended to address.
With the column name and table name the user can get other useful information using pragma table_info(). Note, you need to use "database.table" as the table name for this pragma if the table is a duplicated name in an attached database. So perhaps we need to add an sqlite3_column_full_table_name() API to return this combination as well. ;-) just kidding...
If such a new API appears soon, would people (please!) stop using those short_column_names and long_column_names pragmas?For symmetry with the sqlite3_column_short_name() API, you could add an sqlite3_column_full_name() API that always returns the full name of the column including the table name (i.e. what was supposed to be returned as the column name with the full_column_names pragma set).
Even without the new API functions, the current sqlite3_column_name() API should probably be fixed so that it obeys the column name pragmas, as they are documented now, for backward compatibility.
Dennis Cote
