Find schema of a table in a query

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Find schema of a table in a query

curmudgeon
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?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

Dominique Devienne
On Sun, Jan 19, 2020 at 9:47 AM x <[hidden email]> 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?


Set an authorizer. Requires to write code though, cannot be done in SQL.
https://www.sqlite.org/c3ref/set_authorizer.html
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

curmudgeon
Thanks Dominique. I was aware of that route but I was hoping for something simpler.



________________________________
From: sqlite-users <[hidden email]> on behalf of Dominique Devienne <[hidden email]>
Sent: Sunday, January 19, 2020 9:32:28 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Find schema of a table in a query

On Sun, Jan 19, 2020 at 9:47 AM x <[hidden email]> 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?


Set an authorizer. Requires to write code though, cannot be done in SQL.
https://www.sqlite.org/c3ref/set_authorizer.html
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

Warren Young
On Jan 19, 2020, at 2:41 AM, x <[hidden email]> wrote:
>
> I was hoping for something simpler.

Such as?

I mean, your question basically reduces to “I need to be inside the parse loop,” and SQLite has come along and said, “Hey, check this out, you can be inside the parse loop.”  I mean, how cool is that?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

curmudgeon
If I could answer the “such as” I wouldn’t have asked the question. The word “hoping” is the clue as in I was hoping there was some function I had overlooked. I’ve settled for using the sqlite3_table_column_metadata function in a loop using each db name in turn in the order sqlite3 does.



________________________________
From: sqlite-users <[hidden email]> on behalf of Warren Young <[hidden email]>
Sent: Sunday, January 19, 2020 4:02:40 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Find schema of a table in a query

On Jan 19, 2020, at 2:41 AM, x <[hidden email]> wrote:
>
> I was hoping for something simpler.

Such as?

I mean, your question basically reduces to “I need to be inside the parse loop,” and SQLite has come along and said, “Hey, check this out, you can be inside the parse loop.”  I mean, how cool is that?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

Keith Medcalf
In reply to this post by curmudgeon

On Sunday, 19 January, 2020 01:47, x <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

curmudgeon
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 <[hidden email]> on behalf of Keith Medcalf <[hidden email]>
Sent: Sunday, January 19, 2020 8:32:06 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Find schema of a table in a query


On Sunday, 19 January, 2020 01:47, x <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

Keith Medcalf

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

Keith Medcalf

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

curmudgeon
Brilliant Keith. Many thanks.



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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

Richard Damon
In reply to this post by Keith Medcalf
On 1/20/20 4:17 AM, Keith Medcalf wrote:

> 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.
>
One point, due to the nature of the fundamentals of SQL, the whole
concept of 'first' means you MUST have an ORDER BY to get the item you
want.  Yes, when you test it may seem that the records always come in
the order you want without it, but that is really happenstance, and
might change by any number of things.

If the order that the SQL engine happens to choose to fetch the data
does happen to be in the order specified by the ORDER BY, then a good
engine will optimize it out, so it is free.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

curmudgeon
In reply to this post by Keith Medcalf
> select name from pragma_database_list d
> where (select name from pragma_table_info(?1) where schema==d.name)
> order by seq!=1, seq limit 1;

Is this possible in sql?

Given a variable ?1 which contains a valid attached DB name is it possible to retrieve the contents of

?1.table_name  ??????

It’s not like ‘select ?1 as DB,* from ?1.table_name’ is valid sql. Am I just being flummoxed by the fact the top works because it’s metadata?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

Simon Slavin-3
On 20 Jan 2020, at 2:53pm, x <[hidden email]> wrote:

> Is this possible in sql?

In SQL schema names and table names are entities.  You cannot bind a parameter to a either of them.

This is why, instead of keeping data in many different databases, you make one big schema, and use that value as a column.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Find schema of a table in a query

curmudgeon
Thanks Simon.

________________________________
From: sqlite-users <[hidden email]> on behalf of Simon Slavin <[hidden email]>
Sent: Monday, January 20, 2020 4:49:04 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Find schema of a table in a query

On 20 Jan 2020, at 2:53pm, x <[hidden email]> wrote:

> Is this possible in sql?

In SQL schema names and table names are entities.  You cannot bind a parameter to a either of them.

This is why, instead of keeping data in many different databases, you make one big schema, and use that value as a column.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users