Brilliant Keith. Many thanks.
________________________________
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of
Keith Medcalf <kmedc...@dessus.com>
Sent: Monday, January 20, 2020 9:28:50 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Find schema of a table in a query
You are correct that same table names in temp obscure those names from main and
other attached databases, so your order by addition is required.
select name
from pragma_database_list as d
where exists (select *
from pragma_table_info
where schema == d.name
and arg == ?)
order by seq != 1, seq
limit 1;
--
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 <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Keith Medcalf
>Sent: Monday, 20 January, 2020 02:18
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] Find schema of a table in a query
>
>
>The "main" database is always seq == 0, the "temp" database is always seq
>== 1, and other databases are seq == 2 and greater in the order they were
>attached. seq 2 -> whatever is always contiguous. The table search
>order for unqualified names (when a search is required) is always in the
>seq returned by pragma database_list, so
>
>select name
> from pragma_database_list as d
> where exists (select *
> from pragma_table_info
> where schema == d.name
> and arg == 'x')
> limit 1;
>
>will always return the correct schema name, no order by required. (and
>you can use table_info rather than table_xinfo because a table must
>always have one named column that is not hidden. If this were not the
>case, then you would have to use table_xinfo to ensure that something is
>returned for that pragma lookup.
>
>SQLite version 3.31.0 2020-01-20 03:22:36
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> attach database ':memory:' as two;
>sqlite> attach database ':memory:' as three;
>sqlite> attach database ':memory:' as four;
>sqlite> attach database ':memory:' as five;
>sqlite> create table main.x(x);
>sqlite> insert into main.x values (0);
>sqlite> create table temp.x(x);
>sqlite> insert into temp.x values (1);
>sqlite> create table two.x(x);
>sqlite> insert into two.x values (2);
>sqlite> create table three.x(x);
>sqlite> insert into three.x values (3);
>sqlite> create table four.x(x);
>sqlite> insert into four.x values (4);
>sqlite> create table five.x(x);
>sqlite> insert into five.x values (5);
>sqlite> pragma database_list;
>0|main|
>1|temp|
>2|two|
>3|three|
>4|four|
>5|five|
>sqlite> detach database three;
>sqlite> pragma database_list;
>0|main|
>1|temp|
>2|two|
>3|four|
>4|five|
>sqlite> select * from x;
>1
>sqlite> select name
> ...> from pragma_database_list as d
> ...> where exists (select *
> ...> from pragma_table_xinfo
> ...> where schema == d.name
> ...> and arg == 'x')
> ...> limit 1;
>main
>sqlite> drop table main.x;
>sqlite> select * from x;
>1
>sqlite> select name
> ...> from pragma_database_list as d
> ...> where exists (select *
> ...> from pragma_table_xinfo
> ...> where schema == d.name
> ...> and arg == 'x')
> ...> limit 1;
>temp
>sqlite> drop table four.x;
>sqlite> select * from x;
>1
>sqlite> select name
> ...> from pragma_database_list as d
> ...> where exists (select *
> ...> from pragma_table_xinfo
> ...> where schema == d.name
> ...> and arg == 'x')
> ...> limit 1;
>temp
>sqlite> drop table temp.x;
>sqlite> select * from x;
>2
>sqlite> select name
> ...> from pragma_database_list as d
> ...> where exists (select *
> ...> from pragma_table_xinfo
> ...> where schema == d.name
> ...> and arg == 'x')
> ...> limit 1;
>two
>sqlite> create table temp.x(x);
>sqlite> insert into temp.x values (1);
>sqlite> select * from x;
>1
>sqlite> select name
> ...> from pragma_database_list as d
> ...> where exists (select *
> ...> from pragma_table_xinfo
> ...> where schema == d.name
> ...> and arg == 'x')
> ...> limit 1;
>temp
>sqlite>
>
>--
>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 <sqlite-users-boun...@mailinglists.sqlite.org> On
>>Behalf Of x
>>Sent: Monday, 20 January, 2020 01:27
>>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>>Subject: Re: [sqlite] Find schema of a table in a query
>>
>>WOW Keith. That’s the sqlite coding equivalent of scoring a hat trick in
>>the world cup final. I’ve added an order by to get the solution
>>
>>select name from pragma_database_list d
>>where (select name from pragma_table_xinfo where schema==d.name and
>>arg==?1)
>>order by seq!=1, seq limit 1;
>>
>>I’m assuming the temp db is always 1 in the seq column. Can anyone
>>confirm that or should I change it to
>>
>>order by lower(name)!=temp, seq limit 1;
>>
>>Thanks. I also learned the parentheses are not required for pragma
>>functions when there’s no params and alternate syntax when they are.
>>
>>
>>________________________________
>>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on
>>behalf of Keith Medcalf <kmedc...@dessus.com>
>>Sent: Sunday, January 19, 2020 8:32:06 PM
>>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>>Subject: Re: [sqlite] Find schema of a table in a query
>>
>>
>>On Sunday, 19 January, 2020 01:47, x <tam118...@hotmail.com> wrote:
>>
>>>Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want
>>>to know the name of the schema that tbl belongs to. What’s the easiest
>>>way to do this?
>>
>>>I know sqlite will use temp.tbl if it exists else main.tbl if it exists
>>>else it will search for the earliest attached schema with a table
>called
>>>tbl. Finding that involves the use of PRAGMA database_list and then
>>>querying each of the associated sqlite_master tables in turn for the
>>>existence of tbl until you get a match. Is there an easier way?
>>
>>How about:
>>
>>select name
>> from pragma_database_list as d
>> where (select name
>> from pragma_table_xinfo
>> where schema == d.name
>> and arg == 'x') is not null;
>>
>>Where you set "arg == 'tablename'" which will return all the schema's in
>>which the specified tablename exists.
>>
>>--
>>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
>
>
>
>_______________________________________________
>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
