Imho, naming scheme should be like this: 1. column names as returned from sqlite3_column_name should always be a single word, NOT qualified by origin. So, all below should return "field1" as column name. If there is a column alias in the select, the *alias* should be returned as the single word. Case should be irrelevant for column names, as sql is supposed to be case-insensitive.
2. In case where an expression is given as column, say select field1+5 from..., it is Customary to return a pseudo name such as ExprN, where n=1..n. in any case, this is not Important, so use any scheme you find suitable. 3. there *should* be (nice to have) an sqlite3_column_origin function, which attempts to Return the column origin, in the form: Database.table.column In all cases, the *real* names should be returned, and not the aliases column_name column_origin ------------------------------------------------------------------------ ---------- > select field1 from test; field1 test.field1 > select "field1" from test; field1 '' > select FIELD1 from test; field1 (or FIELD1) '' > select "FIELD1" from test; field1 '' > select [field1] FROM test; field1 '' > select test.field1 from test; field1 '' > select a.field1 from test as a; field1 '' > select "a".field1 from test as a; field1 '' > select "a"."field1" from test as a; field1 '' > select main.test1.field1 from test; field1 main.test.field1 > select [main]."a".[field1] from test as a; field1 test.field1 select field1 as a from test a test.field1 etc > > This problem is related to the naming of columns in views. > Please tell me what the column names for the following views > should be: > > create view v1 as select field1 from test; > create view v2 as select FIELD1 from test; > create view v3 as select a.field1 from test as a; > create view v4 as select test1.field1 from test; > create view v5 as select "FIELD1" from test; > create view v6 as select "A"."FIELD1" from test as a; > create view v7 as select "a".field1 + 5 FROM test as a; > create view v8 as select "a".field1+5 from test as a; I believe in all cases, the names for sqlite3_column_name, and sqlite3_column_origin should be exactly the same as if the View was not present, ie as if the select was given directly. > Another place where naming is an issue is on CREATE TABLE AS > statements. What is the name of the column in each of the > following tables: > > create table t2 as select field1 from test; > create table t3 as select FIELD1 from test; > create table t4 as SELECT a.field1 from test as a; > -- and so forth Same comment as above, the column names should be the same for the equivalent SELECT (and data types as well) > For the above, what is the column name reported out when I do: > > select * from t2; > select * from t3; -- and so forth > > or > > select * from v1; > select * from v2; -- and so forth > > If someone will come up with a coherent set of column naming > rules - rules which other popular SQL database engines follow > - then I will be happy to implement them in SQLite. Doing so > will probably break a lot of existing code. But as the > column naming rules have never been specified or documented > before, I'd be willing to do it since any code that breaks > would have been depending on undefined behavior to begin with. > > Does the SQL standard have anything to say about this? > Does anybody know? I don't believe so. So, really, it is a matte of choice. However, let us examine the rationale of column naming requests you have been getting all this time. They come under two categories : A. people that do not like the current scheme / cannot make it compatible with some other database etc etc IMHO this is a lot of noise about a non-significant issue. Column names are retrieved from the sql statement, and people have the chance to map them to whatever they think is significant for the end-user. Programs can handle this easily, so I don't understand what it all the fuss about. My only comment here is that column names should be "short" (like pragma short_column_name), because this is what databases normally do. B. People who need to use column names to do generic processing, like machine-generated UPDATES, discovery of relations etc In this case, it is *ESSENTIAL* that : a. column names are consistent, and as informative as possible b. the origin of data can be retrieved as clearly as possible, since it is *this* which is needed by tools writers, and not any aliasing that the programmer has used in order to resolve ambiguities or for prettying up the sql statement. So, this is why I propose a second api, sqlite3_column_origin, which can return the real origin of the data, skipping aliases, views, etc. of course there are cases where the origin cannot be determined (or rather cannot be expressed in such terms, since sqlite always knows where the data comes from). This is not a problem, the api can choose some way to report this. For example, if you choose to add a single api , you can have the <database> part empty to signify the main database, the <table> part empty to show the data does not come from a table (select 16 from test), and the column part empty to show the data does not come from a column (select fiedl1+5 from test). Or, you can provide 3 api's , to be explicit : Sqlite3_column_database Sqlite3_column_table Sqlite3_column_column In any case, providing the extra apis will allow tool users to do things they now cannot do (for example write a proper OLEDB provider), so we have a definite increase in capabilities, and not just a cosmetic change. What you do with the original sqlite3_column_name api is imho irrelevant, but the scheme I proposed above is consistent, and also used by most known databases. Thanks, mike