Re: 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 <[email protected]> On
>Behalf Of Keith Medcalf
>Sent: Monday, 20 January, 2020 02:18
>To: SQLite mailing list <[email protected]>
>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 <[email protected]> On
>>Behalf Of x
>>Sent: Monday, 20 January, 2020 01:27
>>To: SQLite mailing list <[email protected]>
>>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 <[email protected]> on
>>behalf of Keith Medcalf <[email protected]>
>>Sent: Sunday, January 19, 2020 8:32:06 PM
>>To: SQLite mailing list <[email protected]>
>>Subject: Re: [sqlite] Find schema of a table in a query
>>
>>
>>On Sunday, 19 January, 2020 01:47, x <[email protected]> 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
>>[email protected]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>_______________________________________________
>>sqlite-users mailing list
>>[email protected]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users