Thanks Simon.
From: sqlite-users on behalf of
Simon Slavin
Sent: Monday, January 20, 2020 4:49:04 PM
To: SQLite mailing list
Subject: Re: [sqlite] Find schema of a table in a query
On 20 Jan 2020, at 2:53pm, x wrote:
> Is this possible in sql?
In SQL
On 20 Jan 2020, at 2:53pm, x 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.
> 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 ??
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
Brilliant Keith. Many thanks.
From: sqlite-users on behalf of
Keith Medcalf
Sent: Monday, January 20, 2020 9:28:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] Find schema of a table in a query
You are correct that same table names in temp obscure
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 ==
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
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
On Sunday, 19 January, 2020 01:47, x 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
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.
On Jan 19, 2020, at 2:41 AM, x 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?
Thanks Dominique. I was aware of that route but I was hoping for something
simpler.
From: sqlite-users on behalf of
Dominique Devienne
Sent: Sunday, January 19, 2020 9:32:28 AM
To: SQLite mailing list
Subject: Re: [sqlite] Find schema of a table in a query
On Sun, Jan 19, 2020 at 9:47 AM x 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.
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
14 matches
Mail list logo
