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
