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
