Re: [sqlite] Request to get alias of table
On Sun, Aug 18, 2019 at 6:51 PM Keith Medcalf wrote:
>
> And what would you expect the column, alias, and table alias, and origin
> table to be for the following query:
>
> select * from (select * from ( select * from (select 1, 2, 3) ta) as tb)
> tc;
>
>
Wrong function... it's SQLColAttribute...
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcolattribute-function?view=sql-server-2017
'1' '2' '3' no column alias
'ta' is where the values came from but probably 'tc'
amusing though.
---
Added some logging to break it out, because there isn't any reason to have
any depth to that
SQL_DESC_TABLE_NAME(TN)
SQL_DESC_BASE_TABLE_NAME (BN)
SQL_DESC_NAME (CN)
select 1,2,3 = CN=['1','2','3'] TN=blank BN=blank
select * from( select 1,2,3 ) ta TN='ta' BN=blank
select * from (select * from( select 1,2,3 ) ta )tb TN='tb' BN='tb';
... etc for tc, td, ...
For MySQL ODBC.
but most importantly; 'select * from someTable ta'; table_name returns
'ta' not 'someTable' ... base name does return 'someTable'.
in current Sqlite API there is NO way to get 'ta' from the last query.
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of J Decker
> >Sent: Sunday, 18 August, 2019 18:14
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Request to get alias of table
> >
> >And sorry for the Spam. But, having had cake, I really do like cake,
> >and i
> >do beleive I have deliberatly forced a query to result with an object
> >of
> >group:{} and users: {} information in the group.
> >
> >But, having worked motstly with MySQL/MSSQL through ODBC, that's why
> >I
> >would expect
> >
> >select count(*) count from table alias
> >
> >to have a origin column name of 'count(*)' (not null), an alias of
> >'count'
> >, a table name of 'table' and a table alias of 'alias'.
> >
> >Although the ODBC stuff doesn't actually give you the choice to get
> >the
> >origin names of things.
> >
> >
> >
> >
> >On Sun, Aug 18, 2019 at 5:01 PM J Decker wrote:
> >
> >> Okay let's start with, I originally had an API compatible with
> >ODBC, which
> >> the third information parameter is 'The name of the table, view,
> >alias,
> >> or synonym.'
> >>
> >>
> >>
> >https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src
> >/tpc/db2z_fncolumns.html
> >>
> >>
> >>
> >> Column 1 TABLE_CAT (VARCHAR(128))The name of the catalog. The value
> >is
> >> NULL if this table does not have catalogs.Column 2 TABLE_SCHEM
> >> (VARCHAR(128))The name of the schema containing TABLE_NAME.Column 3
> >> TABLE_NAME (VARCHAR(128) not NULL)The name of the table, view,
> >alias, or
> >> synonym.Column 4 COLUMN_NAME (VARCHAR(128) not NULL)The column
> >> identifier. The name of the column of the specified table, view,
> >alias, or
> >> synonym.Column 5 DATA_TYPE (SMALLINT not NULL)The SQL data type of
> >the
> >> column that is identified by COLUMN_NAME. The DATA_TYPE is one of
> >the
> >> values in the Symbolic SQL Data Type column in the table of
> >symbolic and
> >> default data types for CLI.Column 6 TYPE_NAME (VARCHAR(128) not
> >NULL)A
> >> character string that represents the name of the data type that
> >corresponds
> >> to DATA_TYPE.Column 7 COLUMN_SIZE (INTEGER)
> >>
> >>
> >___
> >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] Request to get alias of table
And what would you expect the column, alias, and table alias, and origin table
to be for the following query:
select * from (select * from ( select * from (select 1, 2, 3) ta) as tb) tc;
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of J Decker
>Sent: Sunday, 18 August, 2019 18:14
>To: SQLite mailing list
>Subject: Re: [sqlite] Request to get alias of table
>
>And sorry for the Spam. But, having had cake, I really do like cake,
>and i
>do beleive I have deliberatly forced a query to result with an object
>of
>group:{} and users: {} information in the group.
>
>But, having worked motstly with MySQL/MSSQL through ODBC, that's why
>I
>would expect
>
>select count(*) count from table alias
>
>to have a origin column name of 'count(*)' (not null), an alias of
>'count'
>, a table name of 'table' and a table alias of 'alias'.
>
>Although the ODBC stuff doesn't actually give you the choice to get
>the
>origin names of things.
>
>
>
>
>On Sun, Aug 18, 2019 at 5:01 PM J Decker wrote:
>
>> Okay let's start with, I originally had an API compatible with
>ODBC, which
>> the third information parameter is 'The name of the table, view,
>alias,
>> or synonym.'
>>
>>
>>
>https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src
>/tpc/db2z_fncolumns.html
>>
>>
>>
>> Column 1 TABLE_CAT (VARCHAR(128))The name of the catalog. The value
>is
>> NULL if this table does not have catalogs.Column 2 TABLE_SCHEM
>> (VARCHAR(128))The name of the schema containing TABLE_NAME.Column 3
>> TABLE_NAME (VARCHAR(128) not NULL)The name of the table, view,
>alias, or
>> synonym.Column 4 COLUMN_NAME (VARCHAR(128) not NULL)The column
>> identifier. The name of the column of the specified table, view,
>alias, or
>> synonym.Column 5 DATA_TYPE (SMALLINT not NULL)The SQL data type of
>the
>> column that is identified by COLUMN_NAME. The DATA_TYPE is one of
>the
>> values in the Symbolic SQL Data Type column in the table of
>symbolic and
>> default data types for CLI.Column 6 TYPE_NAME (VARCHAR(128) not
>NULL)A
>> character string that represents the name of the data type that
>corresponds
>> to DATA_TYPE.Column 7 COLUMN_SIZE (INTEGER)
>>
>>
>___
>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] Request to get alias of table
And sorry for the Spam. But, having had cake, I really do like cake, and i
do beleive I have deliberatly forced a query to result with an object of
group:{} and users: {} information in the group.
But, having worked motstly with MySQL/MSSQL through ODBC, that's why I
would expect
select count(*) count from table alias
to have a origin column name of 'count(*)' (not null), an alias of 'count'
, a table name of 'table' and a table alias of 'alias'.
Although the ODBC stuff doesn't actually give you the choice to get the
origin names of things.
On Sun, Aug 18, 2019 at 5:01 PM J Decker wrote:
> Okay let's start with, I originally had an API compatible with ODBC, which
> the third information parameter is 'The name of the table, view, alias,
> or synonym.'
>
>
> https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_fncolumns.html
>
>
>
> Column 1 TABLE_CAT (VARCHAR(128))The name of the catalog. The value is
> NULL if this table does not have catalogs.Column 2 TABLE_SCHEM
> (VARCHAR(128))The name of the schema containing TABLE_NAME.Column 3
> TABLE_NAME (VARCHAR(128) not NULL)The name of the table, view, alias, or
> synonym.Column 4 COLUMN_NAME (VARCHAR(128) not NULL)The column
> identifier. The name of the column of the specified table, view, alias, or
> synonym.Column 5 DATA_TYPE (SMALLINT not NULL)The SQL data type of the
> column that is identified by COLUMN_NAME. The DATA_TYPE is one of the
> values in the Symbolic SQL Data Type column in the table of symbolic and
> default data types for CLI.Column 6 TYPE_NAME (VARCHAR(128) not NULL)A
> character string that represents the name of the data type that corresponds
> to DATA_TYPE.Column 7 COLUMN_SIZE (INTEGER)
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request to get alias of table
Okay let's start with, I originally had an API compatible with ODBC, which the third information parameter is 'The name of the table, view, alias, or synonym.' https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_fncolumns.html Column 1 TABLE_CAT (VARCHAR(128))The name of the catalog. The value is NULL if this table does not have catalogs.Column 2 TABLE_SCHEM (VARCHAR(128))The name of the schema containing TABLE_NAME.Column 3 TABLE_NAME (VARCHAR(128) not NULL)The name of the table, view, alias, or synonym.Column 4 COLUMN_NAME (VARCHAR(128) not NULL)The column identifier. The name of the column of the specified table, view, alias, or synonym.Column 5 DATA_TYPE (SMALLINT not NULL)The SQL data type of the column that is identified by COLUMN_NAME. The DATA_TYPE is one of the values in the Symbolic SQL Data Type column in the table of symbolic and default data types for CLI.Column 6 TYPE_NAME (VARCHAR(128) not NULL)A character string that represents the name of the data type that corresponds to DATA_TYPE.Column 7 COLUMN_SIZE (INTEGER) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request to get alias of table
On Thu, Aug 15, 2019 at 7:13 PM Keith Medcalf wrote: > > With long names turned on the columns are named differently when returned > though the aliased subquery. Since a dot (.) is the separator between the > schema.table or table.column and the column name contains an embedded dot, > you have to quote the name ... This is probably why long_column_names was > deprecated. > > select count(*)count,* from ( select count(*)count,* from addresses join > users on users.address_id=addresses.id ) address join pets pet on > pet.user_id=address.'users.id'; > > Row(count=1, address_count=1, address_addresses_id=1, > address_addresses_name='there', address_users_id=1, > address_users_address_id=1, address_users_name='bob', pet_id=1, > pet_user_id=1, pet_name='odif') > > select count(*)count,* from ( select count(*)count,* from addresses join > users on users.address_id=addresses.id ) address join pets pet on > pet.user_id=address.[users.id]; > > Row(count=1, address_count=1, address_addresses_id=1, > address_addresses_name='there', address_users_id=1, > address_users_address_id=1, address_users_name='bob', pet_id=1, > pet_user_id=1, pet_name='odif') > > select count(*)count,* from ( select count(*)count,* from addresses join > users on users.address_id=addresses.id ) address join pets pet on > pet.user_id=address."users.id"; > > Row(count=1, address_count=1, address_addresses_id=1, > address_addresses_name='there', address_users_id=1, > address_users_address_id=1, address_users_name='bob', pet_id=1, > pet_user_id=1, pet_name='odif') > > None of this removes the desire to get the alias of tables specified in queries. Even though queries eventually end up flat and column-aliased in the end, during development, it's still nice to get structured records from the database; and pragma long_column_names(whatever) changes the query behavior, so that's not an option to use without breaking old stuff. > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of J Decker > >Sent: Thursday, 15 August, 2019 19:19 > >To: SQLite mailing list > >Subject: Re: [sqlite] Request to get alias of table > > > >On Thu, Aug 15, 2019 at 4:52 PM Keith Medcalf > >wrote: > > > >> > >> The query does not work because it is defective. The column name > >pragma's > >> only affect the column names that your program sees, not the names > >used > >> internally in the SQL statement. It does not affect the identifier > >use > >> internally. If your internal identifier use is defective, then it > >is > >> defective and only fixing the defect will fix it. In this > >particular case > >> it can ONLY be fixed by specifying appropriate column names using > >AS. > >> > >> pragma full_column_names=1; > >pragma short_column_names=0; > >does change how SQL deals with it. > > > >Without this query works > >select count(*)count,* from ( select count(*)count,* from addresses > >join > >users on users.address_id=addresses.id ) address join pets pet on > >pet.user_id=address.'id:1'; > > > >and with, it doesn't seem to be able to be referenced... you're > >right, I > >did end up making it a string isntead of a colum name, quoting with > >`` > >didn't work either. > > > > > > > >> In other words DO NOT use * unless you are absolutely certain that > >the > >> column names are unique. This is not a "personal peeve", this is > >how SQL > >> works. Do not use *. Period. End of story. This is almost > >always one of > >> the very first things one learns when using SQL. This is because > >if > >> something gets changed then the meaning of * gets changed. If the > >query > >> optimizer decides to do things differently (in a different order), > >the > >> results will be different. > >> > >> Using * means that you are relying on happenstance rather than > >actually > >> writing what you mean. > >> > >> select * from ( select count(*)count,* from addresses join users > >> on users.address_id=addresses.id ) address join pets pet on > >pet.user_id= > >> address.users.id; > >> > >> The subquery "select count(*) count,* from addr
Re: [sqlite] Request to get alias of table
With long names turned on the columns are named differently when returned though the aliased subquery. Since a dot (.) is the separator between the schema.table or table.column and the column name contains an embedded dot, you have to quote the name ... This is probably why long_column_names was deprecated. select count(*)count,* from ( select count(*)count,* from addresses join users on users.address_id=addresses.id ) address join pets pet on pet.user_id=address.'users.id'; Row(count=1, address_count=1, address_addresses_id=1, address_addresses_name='there', address_users_id=1, address_users_address_id=1, address_users_name='bob', pet_id=1, pet_user_id=1, pet_name='odif') select count(*)count,* from ( select count(*)count,* from addresses join users on users.address_id=addresses.id ) address join pets pet on pet.user_id=address.[users.id]; Row(count=1, address_count=1, address_addresses_id=1, address_addresses_name='there', address_users_id=1, address_users_address_id=1, address_users_name='bob', pet_id=1, pet_user_id=1, pet_name='odif') select count(*)count,* from ( select count(*)count,* from addresses join users on users.address_id=addresses.id ) address join pets pet on pet.user_id=address."users.id"; Row(count=1, address_count=1, address_addresses_id=1, address_addresses_name='there', address_users_id=1, address_users_address_id=1, address_users_name='bob', pet_id=1, pet_user_id=1, pet_name='odif') -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of J Decker >Sent: Thursday, 15 August, 2019 19:19 >To: SQLite mailing list >Subject: Re: [sqlite] Request to get alias of table > >On Thu, Aug 15, 2019 at 4:52 PM Keith Medcalf >wrote: > >> >> The query does not work because it is defective. The column name >pragma's >> only affect the column names that your program sees, not the names >used >> internally in the SQL statement. It does not affect the identifier >use >> internally. If your internal identifier use is defective, then it >is >> defective and only fixing the defect will fix it. In this >particular case >> it can ONLY be fixed by specifying appropriate column names using >AS. >> >> pragma full_column_names=1; >pragma short_column_names=0; >does change how SQL deals with it. > >Without this query works >select count(*)count,* from ( select count(*)count,* from addresses >join >users on users.address_id=addresses.id ) address join pets pet on >pet.user_id=address.'id:1'; > >and with, it doesn't seem to be able to be referenced... you're >right, I >did end up making it a string isntead of a colum name, quoting with >`` >didn't work either. > > > >> In other words DO NOT use * unless you are absolutely certain that >the >> column names are unique. This is not a "personal peeve", this is >how SQL >> works. Do not use *. Period. End of story. This is almost >always one of >> the very first things one learns when using SQL. This is because >if >> something gets changed then the meaning of * gets changed. If the >query >> optimizer decides to do things differently (in a different order), >the >> results will be different. >> >> Using * means that you are relying on happenstance rather than >actually >> writing what you mean. >> >> select * from ( select count(*)count,* from addresses join users >> on users.address_id=addresses.id ) address join pets pet on >pet.user_id= >> address.users.id; >> >> The subquery "select count(*) count,* from addresses join users on >> users.address_id=addresses.id" has duplicated column names, and >there is >> nothing that you can do about this except use AS to give specific >distinct >> names to the columns. You then alias this as a tableform entity >called >> address. There are two id columns in address, and there is no way >that you >> can specify in the outer query which one you want other than by >giving them >> unique names in the inner query by listing explicit columns and >giving them >> unique aliases using AS. >> >> Like I said, if you care about column names then give the columns >names, >> and if you need to distinguish between two columns with the same >name, give >> them different aliases. >> > >they already have different aliases, based on where they come from. >For >instance the long name option kno
Re: [sqlite] Request to get alias of table
On Thu, Aug 15, 2019 at 4:52 PM Keith Medcalf wrote:
>
> The query does not work because it is defective. The column name pragma's
> only affect the column names that your program sees, not the names used
> internally in the SQL statement. It does not affect the identifier use
> internally. If your internal identifier use is defective, then it is
> defective and only fixing the defect will fix it. In this particular case
> it can ONLY be fixed by specifying appropriate column names using AS.
>
> pragma full_column_names=1;
pragma short_column_names=0;
does change how SQL deals with it.
Without this query works
select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.'id:1';
and with, it doesn't seem to be able to be referenced... you're right, I
did end up making it a string isntead of a colum name, quoting with ``
didn't work either.
> In other words DO NOT use * unless you are absolutely certain that the
> column names are unique. This is not a "personal peeve", this is how SQL
> works. Do not use *. Period. End of story. This is almost always one of
> the very first things one learns when using SQL. This is because if
> something gets changed then the meaning of * gets changed. If the query
> optimizer decides to do things differently (in a different order), the
> results will be different.
>
> Using * means that you are relying on happenstance rather than actually
> writing what you mean.
>
> select * from ( select count(*)count,* from addresses join users
> on users.address_id=addresses.id ) address join pets pet on pet.user_id=
> address.users.id;
>
> The subquery "select count(*) count,* from addresses join users on
> users.address_id=addresses.id" has duplicated column names, and there is
> nothing that you can do about this except use AS to give specific distinct
> names to the columns. You then alias this as a tableform entity called
> address. There are two id columns in address, and there is no way that you
> can specify in the outer query which one you want other than by giving them
> unique names in the inner query by listing explicit columns and giving them
> unique aliases using AS.
>
> Like I said, if you care about column names then give the columns names,
> and if you need to distinguish between two columns with the same name, give
> them different aliases.
>
they already have different aliases, based on where they come from. For
instance the long name option knows that count is not duplicated.
The query has lots of structure that keeps duplicated values separate...
{
count: 1,
'address.count': 1,
'address.addresses.id': 1,
'address.addresses.name': 'there',
'address.users.id': 1,
'address.users.address_id': 1,
'address.users.name': 'bob'
}
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of J Decker
> >Sent: Thursday, 15 August, 2019 14:37
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Request to get alias of table
> >
> >>
> >>
> >>
> >> sqlite> select * from ( select count(*)count,* from addresses join
> >users
> >> on users.address_id=addresses.id ) address join pets pet on
> >pet.user_id=
> >> address.users.id;
> >> Error: no such column: address.users.id
> >>
> >>
> >>> console.log( db.do( "select count(*)count,* from ( select
> >count(*)count,*
> >from addresses join users on users.address_id=addresses.id ) address
> >join
> >pets pet on pet.user_id='address.user.id';" ) );
> >
> >
> >13:34:27.325|21281160~sql_module.cc(571):Do
> >Command[04F1ECF4:test.db]:
> >select count(*)count,* from ( select count(*)count,* from addresses
> >join
> >users on users.address_id=addres
> >ses.id ) address join pets pet on pet.user_id='address.user.id';
> >13:34:27.326|21281160~sack.cc(97503):Sqlite3 Err: (284) automatic
> >index
> >on users(address_id)
> >13:34:27.326|21281160~sack.cc(104891):0
> >13:34:27.326|21281160~sack.cc(101458):Column 0 :
> >table_name:(null)
> >origin_name:(null) table_alias:(null)
> >13:34:27.327|21281160~sack.cc(101458):Column 1 :
> >table_name:(null)
> >origin_name:(null) table_alias:(null)
> >13:34:27.327|212800
Re: [sqlite] Request to get alias of table
The query does not work because it is defective. The column name pragma's only
affect the column names that your program sees, not the names used internally
in the SQL statement. It does not affect the identifier use internally. If
your internal identifier use is defective, then it is defective and only fixing
the defect will fix it. In this particular case it can ONLY be fixed by
specifying appropriate column names using AS.
In other words DO NOT use * unless you are absolutely certain that the column
names are unique. This is not a "personal peeve", this is how SQL works. Do
not use *. Period. End of story. This is almost always one of the very first
things one learns when using SQL. This is because if something gets changed
then the meaning of * gets changed. If the query optimizer decides to do
things differently (in a different order), the results will be different.
Using * means that you are relying on happenstance rather than actually writing
what you mean.
select * from ( select count(*)count,* from addresses join users
on users.address_id=addresses.id ) address join pets pet on pet.user_id=
address.users.id;
The subquery "select count(*) count,* from addresses join users on
users.address_id=addresses.id" has duplicated column names, and there is
nothing that you can do about this except use AS to give specific distinct
names to the columns. You then alias this as a tableform entity called
address. There are two id columns in address, and there is no way that you can
specify in the outer query which one you want other than by giving them unique
names in the inner query by listing explicit columns and giving them unique
aliases using AS.
Like I said, if you care about column names then give the columns names, and if
you need to distinguish between two columns with the same name, give them
different aliases.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of J Decker
>Sent: Thursday, 15 August, 2019 14:37
>To: SQLite mailing list
>Subject: Re: [sqlite] Request to get alias of table
>
>>
>>
>>
>> sqlite> select * from ( select count(*)count,* from addresses join
>users
>> on users.address_id=addresses.id ) address join pets pet on
>pet.user_id=
>> address.users.id;
>> Error: no such column: address.users.id
>>
>>
>>> console.log( db.do( "select count(*)count,* from ( select
>count(*)count,*
>from addresses join users on users.address_id=addresses.id ) address
>join
>pets pet on pet.user_id='address.user.id';" ) );
>
>
>13:34:27.325|21281160~sql_module.cc(571):Do
>Command[04F1ECF4:test.db]:
>select count(*)count,* from ( select count(*)count,* from addresses
>join
>users on users.address_id=addres
>ses.id ) address join pets pet on pet.user_id='address.user.id';
>13:34:27.326|21281160~sack.cc(97503):Sqlite3 Err: (284) automatic
>index
>on users(address_id)
>13:34:27.326|21281160~sack.cc(104891):0
>13:34:27.326|21281160~sack.cc(101458):Column 0 :
>table_name:(null)
>origin_name:(null) table_alias:(null)
>13:34:27.327|21281160~sack.cc(101458):Column 1 :
>table_name:(null)
>origin_name:(null) table_alias:(null)
>13:34:27.327|21281160~sack.cc(101458):Column 2 :
>table_name:addresses
>origin_name:id table_alias:addresses
>13:34:27.327|21281160~sack.cc(101458):Column 3 :
>table_name:addresses
>origin_name:name table_alias:addresses
>13:34:27.327|21281160~sack.cc(101458):Column 4 : table_name:users
>origin_name:id table_alias:users
>13:34:27.327|21281160~sack.cc(101458):Column 5 : table_name:users
>origin_name:address_id table_alias:users
>13:34:27.328|21281160~sack.cc(101458):Column 6 : table_name:users
>origin_name:name table_alias:users
>13:34:27.328|21281160~sack.cc(101458):Column 7 : table_name:pets
>origin_name:id table_alias:pet
>13:34:27.328|21281160~sack.cc(101458):Column 8 : table_name:pets
>origin_name:user_id table_alias:pet
>13:34:27.328|21281160~sack.cc(101458):Column 9 : table_name:pets
>origin_name:name table_alias:pet
>13:34:27.328|21281160~sack.cc(104418):no data
>[
> {
>count: 0,
>'address.count': null,
>'address.addresses.id': null,
>'address.addresses.name': null,
>'address.users.id': null,
>'address.users.address_id': null,
>'address.users.name': null,
>'pet.id': null,
>'pet.user_id': null,
>'pet.name': null
> }
>]
>
>Hmm... I don'
Re: [sqlite] Request to get alias of table
Aha! Found and fixed some bugs in my apsw wrapper for stuff that I do not do
often (such as using * to retrieve duplicate column names as I typically always
use AS to name columns explicitly, so the Row was being built with duplicate
names). Also, removed the code that disabled the long_column_names support as
it does not actually require changes to the APSW base, those only being
required if I want to fetch the underlying column metadata).
from __future__ import absolute_import, division, print_function,
unicode_literals
import newapsw
import apsw
db = apsw.Connection('')
db.execute('''
create table addresses( id, name );
create table users( id, address_id, name );
create table pets(id, user_id, name );
insert into addresses(id,name) values ( 1, 'there' );
insert into users(id,address_id,name) values ( 1, 1, 'bob' );
insert into pets(id,user_id,name) values ( 1,1, 'odif' );
''')
cr1 = db.execute('''
select *
from users user
join addresses address
on address.id=user.address_id
join pets pet
on pet.user_id=user.id;
''')
print(1, cr1.fetchone())
cr2 = db.execute('''
select address.id AS address_id,
address.nameAS address_name,
user.id AS user_id,
user.address_id AS user_address_id,
user.name AS user_name,
pet.id AS pet_id,
pet.user_id AS pet_user_id,
pet.nameAS pet_name
from users AS user
join addresses AS address
on address.id == user.address_id
join pets AS pet
on pet.user_id == user.id;
''')
print(2, cr2.fetchone())
db.execute('''
pragma full_column_names=1;
pragma short_column_names=0;
''')
cr3 = db.execute('''
select *
from users user
join addresses address
on address.id=user.address_id
join pets pet
on pet.user_id=user.id;
''')
print(3, cr3.fetchone())
>py -3 sample.py
1 Row(id=1, address_id=1, name='bob', id_1=1, name_1='there', id_2=1,
user_id=1, name_2='odif')
2 Row(address_id=1, address_name='there', user_id=1, user_address_id=1,
user_name='bob', pet_id=1, pet_user_id=1, pet_name='odif')
3 Row(user_id=1, user_address_id=1, user_name='bob', address_id=1,
address_name='there', pet_id=1, pet_user_id=1, pet_name='odif')
Note:
db.execute => db.cursor().execute
The Row object is just built by an exehook and a rowhook getting the column
names from the cursor.description and mangling them somewhat so that the Row
has distinct names in its list of column names so that the column name can be
accessed as an attribute of the Row ...
If you want to see how it all works get http://www.dessus.com/files/
where is one of apsw-monolith27-gcc32.zip, apsw-monolith36-gcc64.zip,
apsw-monolith37-gcc64.zip. The python code is the same in all of them, just
the compiled apsw pyd is different. import newapsw to activate all the stuff,
then import apsw will have it all in the normal apsw library (the newapsw
module is substituted for the apsw module name in sys.modules).
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request to get alias of table
On Thu, Aug 15, 2019 at 2:53 PM Keith Medcalf wrote: > > Using AS to name columns means NOT using *. One only uses * when one does > not care about column names (or the ordering of the result columns). > Sounds like a personal gospel. This is closer to the test case (yes it doesn't make a lot of sense without the additional wheres and other context, but the structure is closer to what I'd have) select count(*)count,* from ( select count(*)count,* from addresses join users on users.address_id=addresses.id ) address join pets pet on pet.user_id='address.user.id'; from a test driven development viewpoint, I want to see what's available Then I can later refine the query. Second though, Providing the ability to get table alias I can make queries that reference different tables in reality but appear to have the same results as other queries. The query itself does a pretty good job of defining the structure of the data itself even with *. > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > ___ > 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] Request to get alias of table
Using AS to name columns means NOT using *. One only uses * when one does not
care about column names (or the ordering of the result columns).
select address.id AS address_id,
address.nameAS address_name,
user.id AS user_id,
user.address_id AS user_address_id,
user.name AS user_name,
pet.id AS pet_id,
pet.user_id AS pet_user_id,
pet.nameAS pet_name
from users AS user
join addresses AS address
on address.id == user.address_id
join pets AS pet
on pet.user_id == user.id;
demonstrates how to specify column names.
import apsw
db = apsw.Connection('')
db.execute('''
create table addresses( id, name );
create table users( id, address_id, name );
create table pets(id, user_id, name );
insert into addresses(id,name) values ( 1, 'there' );
insert into users(id,address_id,name) values ( 1, 1, 'bob' );
insert into pets(id,user_id,name) values ( 1,1, 'odif' );
''')
cr1 = db.execute('''
select *
from users user
join addresses address
on address.id=user.address_id
join pets pet
on pet.user_id=user.id;')
cr2 = db.execute('''
select address.id AS address_id,
address.nameAS address_name,
user.id AS user_id,
user.address_id AS user_address_id,
user.name AS user_name,
pet.id AS pet_id,
pet.user_id AS pet_user_id,
pet.nameAS pet_name
from users AS user
join addresses AS address
on address.id == user.address_id
join pets AS pet
on pet.user_id == user.id;
''')
cr1.fetchone()
Row(id=1, address_id=1, name=u'bob', id_1=1, name_1=u'there', id_1=1,
user_id=1, name_1=u'odif')
cr2.fetchone()
Row(address_id=1, address_name=u'there', user_id=1, user_address_id=1,
user_name=u'bob', pet_id=1, pet_user_id=1, pet_name=u'odif')
*Note that I have modified apsw from the default by having execute() be a
method of the connection, and having data returned as a Row object, via a
python wrapper. It used to be able to handle the full_column_names but I got
rid of that since it required modifying the apsw code itself to return
additional column metadata.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request to get alias of table
>
>
>
> sqlite> select * from ( select count(*)count,* from addresses join users
> on users.address_id=addresses.id ) address join pets pet on pet.user_id=
> address.users.id;
> Error: no such column: address.users.id
>
>
>> console.log( db.do( "select count(*)count,* from ( select count(*)count,*
from addresses join users on users.address_id=addresses.id ) address join
pets pet on pet.user_id='address.user.id';" ) );
13:34:27.325|21281160~sql_module.cc(571):Do Command[04F1ECF4:test.db]:
select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addres
ses.id ) address join pets pet on pet.user_id='address.user.id';
13:34:27.326|21281160~sack.cc(97503):Sqlite3 Err: (284) automatic index
on users(address_id)
13:34:27.326|21281160~sack.cc(104891):0
13:34:27.326|21281160~sack.cc(101458):Column 0 : table_name:(null)
origin_name:(null) table_alias:(null)
13:34:27.327|21281160~sack.cc(101458):Column 1 : table_name:(null)
origin_name:(null) table_alias:(null)
13:34:27.327|21281160~sack.cc(101458):Column 2 : table_name:addresses
origin_name:id table_alias:addresses
13:34:27.327|21281160~sack.cc(101458):Column 3 : table_name:addresses
origin_name:name table_alias:addresses
13:34:27.327|21281160~sack.cc(101458):Column 4 : table_name:users
origin_name:id table_alias:users
13:34:27.327|21281160~sack.cc(101458):Column 5 : table_name:users
origin_name:address_id table_alias:users
13:34:27.328|21281160~sack.cc(101458):Column 6 : table_name:users
origin_name:name table_alias:users
13:34:27.328|21281160~sack.cc(101458):Column 7 : table_name:pets
origin_name:id table_alias:pet
13:34:27.328|21281160~sack.cc(101458):Column 8 : table_name:pets
origin_name:user_id table_alias:pet
13:34:27.328|21281160~sack.cc(101458):Column 9 : table_name:pets
origin_name:name table_alias:pet
13:34:27.328|21281160~sack.cc(104418):no data
[
{
count: 0,
'address.count': null,
'address.addresses.id': null,
'address.addresses.name': null,
'address.users.id': null,
'address.users.address_id': null,
'address.users.name': null,
'pet.id': null,
'pet.user_id': null,
'pet.name': null
}
]
Hmm... I don't get any values that way, I wonder what happens?
I suppose parsing that might be an option; not sure I can guarantee
databases will always pragma longname
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request to get alias of table
On Thu, Aug 15, 2019 at 12:33 PM J Decker wrote:
>
>
> On Thu, Aug 15, 2019 at 12:24 PM Keith Medcalf
> wrote:
>
>> After fixing the errors in the script (strings are quoted with single
>> quotes, not double quotes):
>>
> .headers on
> pragma full_column_names=1;
> pragma short_column_names=0;
> create table addresses( id, name );
> create table users( id, address_id, name );
> create table pets(id, user_id, name );
>
> insert into addresses(id,name) values ( 1, 'there' );
> insert into users(id,address_id,name) values ( 1, 1, 'bob' );
> insert into pets(id,user_id,name) values ( 1,1, 'odif' );
>
> select * from users user join addresses address on address.id=user.address_id
> join pets pet on pet.user_id=user.id;
>
>> id|address_id|name|id|name|id|user_id|name
>> 1|1|bob|1|there|1|1|odif
>> sqlite> pragma full_column_names=1;
>> sqlite> pragma short_column_names=0;
>> sqlite> select * from users user join addresses address on address.id
>> =user.address_id
>>...> join pets pet on pet.user_id=user.id;
>> user.id|user.address_id|user.name|address.id|address.name|pet.id
>> |pet.user_id|pet.name
>> 1|1|bob|1|there|1|1|odif
>> sqlite>
>>
>> Note that the short_column_names and full_column_names pragma's are
>> deprecated even though highly useful. You have to turn off
>> short_column_names (the default) in order for full_column_names to have any
>> effect.
>>
>> Note that you SHOULD be using AS to name your columns, if you care about
>> column names, and not relying on implementation details.
>>
>> Even if I do use AS, queries could be composed of expressions from other
queries, and eventually what someone makde 'unique' will be the same like
'count(*) AS count' , count, count, count
select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.'id:1';
1,1,1,there,1,1,bob,1,1,odif
Column 0 : table_name:(null) origin_name:(null) table_alias:(null)
-- I would have expected 'table_name' to be (select
count(*)count,*,fromaddresses.) and alias to be 'address'
-- I would have expected origin_name to be 'count(*)' and column_name to be
'count' (which it is)
Column 1 : table_name:(null) origin_name:(null) table_alias:(null)
-- I would have expected 'table_name' to be addresses and alias to be
'addresses' /* no alias, I'm not re-testing */
-- I would have expected origin_name to be 'count(*)' and column_name to be
'count' (which it is)
Column 2 : table_name:addresses origin_name:id table_alias:addresses
Column 3 : table_name:addresses origin_name:name table_alias:addresses
Column 4 : table_name:users origin_name:id table_alias:users
Column 5 : table_name:users origin_name:address_id table_alias:users
Column 6 : table_name:users origin_name:name table_alias:users
Column 7 : table_name:pets origin_name:id table_alias:pet
Column 8 : table_name:pets origin_name:user_id table_alias:pet
Column 9 : table_name:pets origin_name:name table_alias:pet
[
{
addresses: { id: 1, name: 'there' },
users: { 'id:1': 1, address_id: 1, 'name:1': 'bob' },
pet: { id: 1, user_id: 1, name: 'odif' },
/* I Have an error apparently (or feature), that if there isn't a
table/alias name, I don't split it...
I should at least promote it to an array... */
count: 1,
id: [ 1, 1, addresses: 1, pet: 1 ],
name: [ 'there', 'odif', addresses: 'there', pet: 'odif' ]
}
]
>
>
> --
>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>> says a lot about anticipated traffic volume.
>>
>>
>>
>>
>> ___
>> 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] Request to get alias of table
On Thu, Aug 15, 2019 at 12:24 PM Keith Medcalf wrote:
> After fixing the errors in the script (strings are quoted with single
> quotes, not double quotes):
>
sqlite> .headers on
> sqlite> create table addresses( id, name );
> sqlite> create table users( id, address_id, name );
> sqlite> create table pets(id, user_id, name );
> sqlite>
> sqlite> insert into addresses(id,name) values ( 1, 'there' );
> sqlite> insert into users(id,address_id,name) values ( 1, 1, 'bob' );
> sqlite> insert into pets(id,user_id,name) values ( 1,1, 'odif' );
> sqlite>
> sqlite> select * from users user join addresses address on address.id
> =user.address_id
>...> join pets pet on pet.user_id=user.id;
> id|address_id|name|id|name|id|user_id|name
> 1|1|bob|1|there|1|1|odif
> sqlite> pragma full_column_names=1;
> sqlite> pragma short_column_names=0;
> sqlite> select * from users user join addresses address on address.id
> =user.address_id
>...> join pets pet on pet.user_id=user.id;
> user.id|user.address_id|user.name|address.id|address.name|pet.id
> |pet.user_id|pet.name
> 1|1|bob|1|there|1|1|odif
> sqlite>
>
> Note that the short_column_names and full_column_names pragma's are
> deprecated even though highly useful. You have to turn off
> short_column_names (the default) in order for full_column_names to have any
> effect.
>
> Note that you SHOULD be using AS to name your columns, if you care about
> column names, and not relying on implementation details.
>
>
sqlite> select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address ;
address.count|address.addresses.id|address.addresses.name|address.users.id
|address.users.address_id|address.users.name
1|1|there|1|1|bob
sqlite> select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address join pets pet on pet.user_id=
address.users.id;
Error: no such column: address.users.id
How do I fix that query to work then?
Hmm, I don't really like this as the output though... I'd rather have the
individual names without parsing...
select * from users user join addresses address on address.id=user.address_id
join pets pet on pet.user_id=user.id;
[
{
'user.id': 1,
'user.address_id': 1,
'user.name': 'bob',
'address.id': 1,
'address.name': 'there',
'pet.id': 1,
'pet.user_id': 1,
'pet.name': 'odif'
}
]
select * from ( select * from addresses join users on users.address_id=
addresses.id ) address
[
{
'address.addresses.id': 1,
'address.addresses.name': 'there',
'address.users.id': 1,
'address.users.address_id': 1,
'address.users.name': 'bob'
}
]
Intersting, origin_name is still the short name
Column 0 : table_name:users origin_name:id table_alias:user
Column 1 : table_name:users origin_name:address_id table_alias:user
Column 2 : table_name:users origin_name:name table_alias:user
Column 3 : table_name:addresses origin_name:id table_alias:address
Column 4 : table_name:addresses origin_name:name table_alias:address
Column 5 : table_name:pets origin_name:id table_alias:pet
Column 6 : table_name:pets origin_name:user_id table_alias:pet
Column 7 : table_name:pets origin_name:name table_alias:pet
--
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> 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] Request to get alias of table
After fixing the errors in the script (strings are quoted with single quotes, not double quotes): sqlite> .headers on sqlite> create table addresses( id, name ); sqlite> create table users( id, address_id, name ); sqlite> create table pets(id, user_id, name ); sqlite> sqlite> insert into addresses(id,name) values ( 1, 'there' ); sqlite> insert into users(id,address_id,name) values ( 1, 1, 'bob' ); sqlite> insert into pets(id,user_id,name) values ( 1,1, 'odif' ); sqlite> sqlite> select * from users user join addresses address on address.id=user.address_id ...> join pets pet on pet.user_id=user.id; id|address_id|name|id|name|id|user_id|name 1|1|bob|1|there|1|1|odif sqlite> pragma full_column_names=1; sqlite> pragma short_column_names=0; sqlite> select * from users user join addresses address on address.id=user.address_id ...> join pets pet on pet.user_id=user.id; user.id|user.address_id|user.name|address.id|address.name|pet.id|pet.user_id|pet.name 1|1|bob|1|there|1|1|odif sqlite> Note that the short_column_names and full_column_names pragma's are deprecated even though highly useful. You have to turn off short_column_names (the default) in order for full_column_names to have any effect. Note that you SHOULD be using AS to name your columns, if you care about column names, and not relying on implementation details. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request to get alias of table
I was trying a more complex query where the table name isn't a simple table
name...
select * from ( select * from addresses join user on user.address_id=
addresses.id ) address;
but that gives like really strange column names...
# sqlite3 output
id|name|id:1|address_id|name:1
1|there|1|1|bob
# my output
[ { id: 1, name: 'there', 'id:1': 1, address_id: 1, 'name:1': 'bob' } ]
so if I were to try an do like select * from ( select * from addresses
join user on user.address_id=addresses.id ) address join pet on pet.user_id=
address.id:1;
is a syntax error ok...
select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.'id:1';
-- I guess that doesn't really make a 'table' to pull from
1,1,there,1,1,bob,1,1,odif
Column 0 : table_name:(null) origin_name:(null) table_alias:(null)
Column 1 : table_name:addresses origin_name:id table_alias:addresses
Column 2 : table_name:addresses origin_name:name table_alias:addresses
Column 3 : table_name:users origin_name:id table_alias:users
Column 4 : table_name:users origin_name:address_id table_alias:users
Column 5 : table_name:users origin_name:name table_alias:users
Column 6 : table_name:pets origin_name:id table_alias:pet
Column 7 : table_name:pets origin_name:user_id table_alias:pet
Column 8 : table_name:pets origin_name:name table_alias:pet
[
{
addresses: { id: 1, name: 'there' },
users: { 'id:1': 1, address_id: 1, 'name:1': 'bob' },
pet: { id: 1, user_id: 1, name: 'odif' },
count: 1,
id: [ 1, 1, addresses: 1, pet: 1 ],
name: [ 'there', 'odif', addresses: 'there', pet: 'odif' ]
}
]
On Thu, Aug 15, 2019 at 11:51 AM J Decker wrote:
> I would really like to get the table alias specified in the query, with an
> unpatched version of Sqlite.
>
>
> So here's a script...
>
> --
> .headers on
>
> create table addresses( id, name );
> create table users( id, address_id, name );
> create table pets(id, user_id, name );
>
> insert into addresses(id,name) values ( 1, "there" );
> insert into users(id,address_id,name) values ( 1, 1, "bob" );
> insert into pets(id,user_id,name) values ( 1,1, "odif" );
>
> select * from users user join addresses address on address.id=user.address_id
> join pets pet on pet.user_id=user.id;
>
> --
> This is the output, which you can see in the one case for suer multiple
> values come back that are 'id'
>
> id|address_id|name|id|name|id|user_id|name
> 1|1|bob|1|there|1|1|odif
>
> Sqlite command line tool doesn't really provide all of the information
> available though...
>
> ---
> This is the returns of
> sqlite3_column_table_name
> sqlite3_column_origin_name
> , and the function that doesn't exist yet : sqlite3_column_table_alias
>
> data : 1,1,bob,1,there,1,1,odif
> Column 0 : table_name:users origin_name:id table_alias:user
> Column 1 : table_name:users origin_name:address_id table_alias:user
> Column 2 : table_name:users origin_name:name table_alias:user
> Column 3 : table_name:addresses origin_name:id table_alias:address
> Column 4 : table_name:addresses origin_name:name table_alias:address
> Column 5 : table_name:pets origin_name:id table_alias:pet
> Column 6 : table_name:pets origin_name:user_id table_alias:pet
> Column 7 : table_name:pets origin_name:name table_alias:pet
>
>
> Desired output:
> [
> {
> user: { id: 1, address_id: 1, name: 'bob' },
> address: { id: 1, name: 'there' },
> pet: { id: 1, user_id: 1, name: 'odif' },
> id: [ 1, 1, 1, user: 1, address: 1, pet: 1 ],
> name: [
> 'bob',
> 'there',
> 'odif',
> user: 'bob',
> address: 'there',
> pet: 'odif'
> ]
> }
> ]
>
> // in the resulting object returned to JS, I keep column names that are
> duplicated as both an array...
> id[0], id[1], id[2] but also related to the table (alias) they come
> from. id.name, id.pets, id.address... but conversely by table (alias)
> name... name.id, address.id, pets.id
>
> ---
>
> I would really like to get the table alias specified in the query, with an
> unpatched version of Sqlite.
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Request to get alias of table
I would really like to get the table alias specified in the query, with an
unpatched version of Sqlite.
So here's a script...
--
.headers on
create table addresses( id, name );
create table users( id, address_id, name );
create table pets(id, user_id, name );
insert into addresses(id,name) values ( 1, "there" );
insert into users(id,address_id,name) values ( 1, 1, "bob" );
insert into pets(id,user_id,name) values ( 1,1, "odif" );
select * from users user join addresses address on address.id=user.address_id
join pets pet on pet.user_id=user.id;
--
This is the output, which you can see in the one case for suer multiple
values come back that are 'id'
id|address_id|name|id|name|id|user_id|name
1|1|bob|1|there|1|1|odif
Sqlite command line tool doesn't really provide all of the information
available though...
---
This is the returns of
sqlite3_column_table_name
sqlite3_column_origin_name
, and the function that doesn't exist yet : sqlite3_column_table_alias
data : 1,1,bob,1,there,1,1,odif
Column 0 : table_name:users origin_name:id table_alias:user
Column 1 : table_name:users origin_name:address_id table_alias:user
Column 2 : table_name:users origin_name:name table_alias:user
Column 3 : table_name:addresses origin_name:id table_alias:address
Column 4 : table_name:addresses origin_name:name table_alias:address
Column 5 : table_name:pets origin_name:id table_alias:pet
Column 6 : table_name:pets origin_name:user_id table_alias:pet
Column 7 : table_name:pets origin_name:name table_alias:pet
Desired output:
[
{
user: { id: 1, address_id: 1, name: 'bob' },
address: { id: 1, name: 'there' },
pet: { id: 1, user_id: 1, name: 'odif' },
id: [ 1, 1, 1, user: 1, address: 1, pet: 1 ],
name: [
'bob',
'there',
'odif',
user: 'bob',
address: 'there',
pet: 'odif'
]
}
]
// in the resulting object returned to JS, I keep column names that are
duplicated as both an array...
id[0], id[1], id[2] but also related to the table (alias) they come from.
id.name, id.pets, id.address... but conversely by table (alias) name...
name.id, address.id, pets.id
---
I would really like to get the table alias specified in the query, with an
unpatched version of Sqlite.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
