Re: [sqlite] sqlite3_column_name with alias

[J Decker]

There is sqlite3_column_origin_name( stmt, n ) ; is that also the alias?
instead of sqlite3_column_name(stmt,n );
or does there need to be sqlite3_column_original that really returns the
not alias?

On Mon, Jan 8, 2018 at 3:21 AM, Bart Smissaert 
wrote:

> > As you can see
>
> Should read:
> As you said
>
> RBS
>
> On Mon, Jan 8, 2018 at 11:17 AM, Bart Smissaert 
> wrote:
>
> > > Best is to define type on output
> >
> > Yes, looks that way. As you can see the alias can help, eg:
> > select max(integer_date_column) as integer_date_column
> >
> > what I also added is coding to pick up the intended formatting from the
> > alias, not from the column but by just specifying the output datatype:
> > select max(integer_date) as int_date
> >
> > All this is only needed if sqlite3_column_decltype produces null.
> > Looks kind of sorted now, thanks.
> >
> >
> > RBS
> >
> > On Mon, Jan 8, 2018 at 10:29 AM, R Smith  wrote:
> >
> >>
> >> On 2018/01/08 12:00 AM, Bart Smissaert wrote:
> >>
> >>> OK, I can see your point and I am sure you are right.
> >>> All this has to do with the question (discussed not long ago) how one
> >>> should know how the output from a statement should be handled by
> >>> an application receiving the data. In my case that application is
> Excel.
> >>> Take for example an integer. Excel need to know if this integer should
> >>> be formatted as a plain integer or as an Excel date. When the user
> moves
> >>> data to SQLite (from the sheet or from eg a text file) he/she will tell
> >>> how
> >>> this data should be stored. In this case the data type will be INTEGER
> or
> >>> INT_DATE. This information will be stored in SQLite_master. The
> question
> >>> is now how to match up the columns of the output with these data types
> in
> >>> SQLite_master. I have a simple system for this with sqlite3_column_type
> >>> and sqlite3_column_decltype etc. but this is just not fool proof. It
> >>> looks
> >>> I will need to think up some more elaborate system. One option is that
> >>> the
> >>> user
> >>> just tells (along with the statement) what the output formatting should
> >>> be,
> >>> but that would be cumbersome.
> >>>
> >>
> >> Yeah, that is a tough one.
> >> Even in other databases you won't be able to achieve this. "Type" is
> used
> >> mostly as an internal specification and sometimes output formatter.
> >> Preserving the original column type through query abstraction to the
> output
> >> is simply not possible, especially if that type is homemade.  You are
> >> interested in an output type, it should be specified within the output.
> >>
> >> Best is to define type on output. You might decrease cumbersomeness a
> lot
> >> if the list of types is small, using perhaps just a one or two character
> >> type identifier, like this:
> >>
> >> SELECT  foo AS Col1_i,   bar AS Col2_d etc.
> >> (Where i denotes INT and d is INT-DATE etc.)
> >>
> >> If you expect the list of types to grow in future, pick slightly longer
> >> identifiers.
> >>
> >> This of course would not be feasible if you (as in your collective devs)
> >> do not control the resulting queries, like if users can make queries on
> the
> >> fly
> >>
> >> Another method is keeping a table with names and types that the
> >> application can read. Add to it all the names used for output and the
> types
> >> implied. Say it contains the name Col1 with Type INT then whenever your
> >> application reads a query where the column name is Col1 (via origin or
> >> alias) then it knows that is an INT. This is the saddest method and
> should
> >> be avoided if possible.
> >>
> >> Good luck!
> >>
> >> Ryan
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_name with alias

[Bart Smissaert]

> As you can see

Should read:
As you said

RBS

On Mon, Jan 8, 2018 at 11:17 AM, Bart Smissaert 
wrote:

> > Best is to define type on output
>
> Yes, looks that way. As you can see the alias can help, eg:
> select max(integer_date_column) as integer_date_column
>
> what I also added is coding to pick up the intended formatting from the
> alias, not from the column but by just specifying the output datatype:
> select max(integer_date) as int_date
>
> All this is only needed if sqlite3_column_decltype produces null.
> Looks kind of sorted now, thanks.
>
>
> RBS
>
> On Mon, Jan 8, 2018 at 10:29 AM, R Smith  wrote:
>
>>
>> On 2018/01/08 12:00 AM, Bart Smissaert wrote:
>>
>>> OK, I can see your point and I am sure you are right.
>>> All this has to do with the question (discussed not long ago) how one
>>> should know how the output from a statement should be handled by
>>> an application receiving the data. In my case that application is Excel.
>>> Take for example an integer. Excel need to know if this integer should
>>> be formatted as a plain integer or as an Excel date. When the user moves
>>> data to SQLite (from the sheet or from eg a text file) he/she will tell
>>> how
>>> this data should be stored. In this case the data type will be INTEGER or
>>> INT_DATE. This information will be stored in SQLite_master. The question
>>> is now how to match up the columns of the output with these data types in
>>> SQLite_master. I have a simple system for this with sqlite3_column_type
>>> and sqlite3_column_decltype etc. but this is just not fool proof. It
>>> looks
>>> I will need to think up some more elaborate system. One option is that
>>> the
>>> user
>>> just tells (along with the statement) what the output formatting should
>>> be,
>>> but that would be cumbersome.
>>>
>>
>> Yeah, that is a tough one.
>> Even in other databases you won't be able to achieve this. "Type" is used
>> mostly as an internal specification and sometimes output formatter.
>> Preserving the original column type through query abstraction to the output
>> is simply not possible, especially if that type is homemade.  You are
>> interested in an output type, it should be specified within the output.
>>
>> Best is to define type on output. You might decrease cumbersomeness a lot
>> if the list of types is small, using perhaps just a one or two character
>> type identifier, like this:
>>
>> SELECT  foo AS Col1_i,   bar AS Col2_d etc.
>> (Where i denotes INT and d is INT-DATE etc.)
>>
>> If you expect the list of types to grow in future, pick slightly longer
>> identifiers.
>>
>> This of course would not be feasible if you (as in your collective devs)
>> do not control the resulting queries, like if users can make queries on the
>> fly
>>
>> Another method is keeping a table with names and types that the
>> application can read. Add to it all the names used for output and the types
>> implied. Say it contains the name Col1 with Type INT then whenever your
>> application reads a query where the column name is Col1 (via origin or
>> alias) then it knows that is an INT. This is the saddest method and should
>> be avoided if possible.
>>
>> Good luck!
>>
>> Ryan
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_name with alias

[Bart Smissaert]

> Best is to define type on output

Yes, looks that way. As you can see the alias can help, eg:
select max(integer_date_column) as integer_date_column

what I also added is coding to pick up the intended formatting from the
alias, not from the column but by just specifying the output datatype:
select max(integer_date) as int_date

All this is only needed if sqlite3_column_decltype produces null.
Looks kind of sorted now, thanks.


RBS

On Mon, Jan 8, 2018 at 10:29 AM, R Smith  wrote:

>
> On 2018/01/08 12:00 AM, Bart Smissaert wrote:
>
>> OK, I can see your point and I am sure you are right.
>> All this has to do with the question (discussed not long ago) how one
>> should know how the output from a statement should be handled by
>> an application receiving the data. In my case that application is Excel.
>> Take for example an integer. Excel need to know if this integer should
>> be formatted as a plain integer or as an Excel date. When the user moves
>> data to SQLite (from the sheet or from eg a text file) he/she will tell
>> how
>> this data should be stored. In this case the data type will be INTEGER or
>> INT_DATE. This information will be stored in SQLite_master. The question
>> is now how to match up the columns of the output with these data types in
>> SQLite_master. I have a simple system for this with sqlite3_column_type
>> and sqlite3_column_decltype etc. but this is just not fool proof. It looks
>> I will need to think up some more elaborate system. One option is that the
>> user
>> just tells (along with the statement) what the output formatting should
>> be,
>> but that would be cumbersome.
>>
>
> Yeah, that is a tough one.
> Even in other databases you won't be able to achieve this. "Type" is used
> mostly as an internal specification and sometimes output formatter.
> Preserving the original column type through query abstraction to the output
> is simply not possible, especially if that type is homemade.  You are
> interested in an output type, it should be specified within the output.
>
> Best is to define type on output. You might decrease cumbersomeness a lot
> if the list of types is small, using perhaps just a one or two character
> type identifier, like this:
>
> SELECT  foo AS Col1_i,   bar AS Col2_d etc.
> (Where i denotes INT and d is INT-DATE etc.)
>
> If you expect the list of types to grow in future, pick slightly longer
> identifiers.
>
> This of course would not be feasible if you (as in your collective devs)
> do not control the resulting queries, like if users can make queries on the
> fly
>
> Another method is keeping a table with names and types that the
> application can read. Add to it all the names used for output and the types
> implied. Say it contains the name Col1 with Type INT then whenever your
> application reads a query where the column name is Col1 (via origin or
> alias) then it knows that is an INT. This is the saddest method and should
> be avoided if possible.
>
> Good luck!
>
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_name with alias

[R Smith]

On 2018/01/08 12:00 AM, Bart Smissaert wrote:

OK, I can see your point and I am sure you are right.
All this has to do with the question (discussed not long ago) how one
should know how the output from a statement should be handled by
an application receiving the data. In my case that application is Excel.
Take for example an integer. Excel need to know if this integer should
be formatted as a plain integer or as an Excel date. When the user moves
data to SQLite (from the sheet or from eg a text file) he/she will tell how
this data should be stored. In this case the data type will be INTEGER or
INT_DATE. This information will be stored in SQLite_master. The question
is now how to match up the columns of the output with these data types in
SQLite_master. I have a simple system for this with sqlite3_column_type
and sqlite3_column_decltype etc. but this is just not fool proof. It looks
I will need to think up some more elaborate system. One option is that the
user
just tells (along with the statement) what the output formatting should be,
but that would be cumbersome.


Yeah, that is a tough one.
Even in other databases you won't be able to achieve this. "Type" is 
used mostly as an internal specification and sometimes output 
formatter.  Preserving the original column type through query 
abstraction to the output is simply not possible, especially if that 
type is homemade.  You are interested in an output type, it should be 
specified within the output.


Best is to define type on output. You might decrease cumbersomeness a 
lot if the list of types is small, using perhaps just a one or two 
character type identifier, like this:


SELECT  foo AS Col1_i,   bar AS Col2_d etc.
(Where i denotes INT and d is INT-DATE etc.)

If you expect the list of types to grow in future, pick slightly longer 
identifiers.


This of course would not be feasible if you (as in your collective devs) 
do not control the resulting queries, like if users can make queries on 
the fly


Another method is keeping a table with names and types that the 
application can read. Add to it all the names used for output and the 
types implied. Say it contains the name Col1 with Type INT then whenever 
your application reads a query where the column name is Col1 (via origin 
or alias) then it knows that is an INT. This is the saddest method and 
should be avoided if possible.


Good luck!
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_name with alias

[Bart Smissaert]

OK, I can see your point and I am sure you are right.
All this has to do with the question (discussed not long ago) how one
should know how the output from a statement should be handled by
an application receiving the data. In my case that application is Excel.
Take for example an integer. Excel need to know if this integer should
be formatted as a plain integer or as an Excel date. When the user moves
data to SQLite (from the sheet or from eg a text file) he/she will tell how
this data should be stored. In this case the data type will be INTEGER or
INT_DATE. This information will be stored in SQLite_master. The question
is now how to match up the columns of the output with these data types in
SQLite_master. I have a simple system for this with sqlite3_column_type
and sqlite3_column_decltype etc. but this is just not fool proof. It looks
I will need to think up some more elaborate system. One option is that the
user
just tells (along with the statement) what the output formatting should be,
but that would be cumbersome.

RBS

On Sun, Jan 7, 2018 at 9:10 PM, R Smith  wrote:

>
> On 2018/01/07 10:11 PM, Bart Smissaert wrote:
>
>> Sure, in that case there can be no sensible column name.
>> In the great majority of cases though the select field will be of a single
>> table column, with or without
>> an expression. In those cases it will be helpful to get the non alias
>> column name.
>>
>
> To repeat an answer by Richard given for a recent other question (if a
> little paraphrased):
>
> How much extra code, resources and memory are you willing to spend for ALL
> future queries so that you can know the sometimes available original column
> used in an alias?
>
> I would prefer that answer to be "Zero".
>
> Bart:
>
> "One would think that if sqlite3_column_name can get the alias name then
> somehow it must
> be possible to get the real column name."
>
> One would be wrong if one would think that. Inside the code/engine there
> are no column names, only pointers, (in fact I think the engine has to make
> special memory space for storing the output names - it is this memory that
> gets interrogated when you call sqlite3_column_name()),  the names only
> exist as a method for humans to state the intent of the question - it is
> forgotten at earliest convenience if not directly intended for output, and
> those that do get added to output has to be collected/deduced at a non-zero
> resource cost already. (At least this is my current understanding, I might
> have it wrong).
>
> Btw. - I am quite sure this holds for all DB engines, but if there is an
> engine out there that do let you trace back, I'd be interested to know.
>
> Also, the assumption about the "vast majority of cases" is simply wrong.
> The vast majority of returned column names in queries are not aliases,
> indeed aliases are mostly used when the column value is a result of a
> function or combination of columns (such as Simon's reply suggested) where
> you need a good resulting reference name in the output. Are you using
> aliases for columns where they are not needed?
>
> Further to this, there is nothing wrong with copying the name in the
> alias, such as: SELECT MAX(ColA) AS ColA FROM 
>
> Lastly, even if we involve zero column joining or functions, plain
> aliasing is a problem by itself. Consider this next query, what do you feel
> is the true original name of the output column sq1?
> CREATE VIEW v1 AS SELECT c1 AS vc1 FROM (SELECT 'X' AS c1);
> SELECT sq1 FROM (SELECT vc1 AS sq1 FROM v1);
> sq1
> -
>   X
>
> HTH, and I hope this adequately illustrates why your request is not
> possible currently, not trivial to implement and also not widely requested.
> Cheers!
> Ryan
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_name with alias

[R Smith]

On 2018/01/07 10:11 PM, Bart Smissaert wrote:

Sure, in that case there can be no sensible column name.
In the great majority of cases though the select field will be of a single
table column, with or without
an expression. In those cases it will be helpful to get the non alias
column name.


To repeat an answer by Richard given for a recent other question (if a 
little paraphrased):


How much extra code, resources and memory are you willing to spend for 
ALL future queries so that you can know the sometimes available original 
column used in an alias?


I would prefer that answer to be "Zero".

Bart:

"One would think that if sqlite3_column_name can get the alias name then
somehow it must
be possible to get the real column name."

One would be wrong if one would think that. Inside the code/engine there 
are no column names, only pointers, (in fact I think the engine has to 
make special memory space for storing the output names - it is this 
memory that gets interrogated when you call sqlite3_column_name()),  the 
names only exist as a method for humans to state the intent of the 
question - it is forgotten at earliest convenience if not directly 
intended for output, and those that do get added to output has to be 
collected/deduced at a non-zero resource cost already. (At least this is 
my current understanding, I might have it wrong).


Btw. - I am quite sure this holds for all DB engines, but if there is an 
engine out there that do let you trace back, I'd be interested to know.


Also, the assumption about the "vast majority of cases" is simply wrong. 
The vast majority of returned column names in queries are not aliases, 
indeed aliases are mostly used when the column value is a result of a 
function or combination of columns (such as Simon's reply suggested) 
where you need a good resulting reference name in the output. Are you 
using aliases for columns where they are not needed?


Further to this, there is nothing wrong with copying the name in the 
alias, such as: SELECT MAX(ColA) AS ColA FROM 


Lastly, even if we involve zero column joining or functions, plain 
aliasing is a problem by itself. Consider this next query, what do you 
feel is the true original name of the output column sq1?

CREATE VIEW v1 AS SELECT c1 AS vc1 FROM (SELECT 'X' AS c1);
SELECT sq1 FROM (SELECT vc1 AS sq1 FROM v1);
sq1
-
  X

HTH, and I hope this adequately illustrates why your request is not 
possible currently, not trivial to implement and also not widely requested.

Cheers!
Ryan





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_name with alias

[Bart Smissaert]

Sure, in that case there can be no sensible column name.
In the great majority of cases though the select field will be of a single
table column, with or without
an expression. In those cases it will be helpful to get the non alias
column name.

RBS



On Sun, Jan 7, 2018 at 7:59 PM, Simon Slavin  wrote:

>
>
> On 7 Jan 2018, at 6:16pm, Bart Smissaert  wrote:
>
> > Would it be possible somehow to get the column full name if there is a
> > column alias?
> >
> > For example if we have:
> >
> > select max(ID) as MaxID from table1
> >
> > then I need to get max(ID) from the statement pointer.
> >
> > One would think that if sqlite3_column_name can get the alias name then
> > somehow it must
> > be possible to get the real column name.
>
> Suppose you had this
>
> SELECT (a + b) AS theSum FROM myTable
>
> What would the "real column name" be ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_name with alias

[Simon Slavin]

On 7 Jan 2018, at 6:16pm, Bart Smissaert  wrote:

> Would it be possible somehow to get the column full name if there is a
> column alias?
> 
> For example if we have:
> 
> select max(ID) as MaxID from table1
> 
> then I need to get max(ID) from the statement pointer.
> 
> One would think that if sqlite3_column_name can get the alias name then
> somehow it must
> be possible to get the real column name.

Suppose you had this

SELECT (a + b) AS theSum FROM myTable

What would the "real column name" be ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users