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
