[sqlite] Draft Documentation Change
https://www.sqlite.org/draft/c3ref/vtab_config.html should also reference SQLITE_VTAB_INNOCUOUS and SQLITE_VTAB_DIRECTONLY -- 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] Error? ext/misc series.c and spellfix.c
sqlite3_vtab_config(db, SQLITE_INNOCUOUS) should that not be
sqlite3_vtab_config(db, SQLITE_VTAB_INNOCUOUS)?
Which explains why my quick patch in the pragma.c xConnect code to make all
pragma vtabs innocuous didn't work (I copied from series.c) :)
rc = sqlite3_declare_vtab(db, zBuf);
if( rc==SQLITE_OK ){
pTab = (PragmaVtab*)sqlite3_malloc(sizeof(PragmaVtab));
if( pTab==0 ){
rc = SQLITE_NOMEM;
}else{
sqlite3_vtab_config(db, SQLITE_VTAB_INNOCUOUS); // Make the vtab
innocuous
memset(pTab, 0, sizeof(PragmaVtab));
pTab->pName = pPragma;
pTab->db = db;
pTab->iHidden = i;
pTab->nHidden = j;
}
}else{
*pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));
}
--
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] 3.31.0 Request TRUSTED_SCHEMA and pragma's
Richard, The TRUSTED_SCHEMA setting works really well but I have noticed one problem (there may be more, but I haven't run across any yet) with it that is perhaps easy to address, though it needs to be done properly. That is perhaps adding an innocuous flag to pragma definitions in mkpragmatab.tcl so that it can be carried though into the vtable code that handles pragma_ xConnect method. This would permit pragma's such as table_info (for example) to be marked as innocuous so that pragma_table_info could be used in a view even when the schema is untrusted. Whether a directonly flag is required I do not know but, for example, one might never want to have pragma_integrity_check used in a view, though I presently don't really see any need for that and the behaviour of those pragma vtabs might not need changing at all from the current behaviour. Just some idea's (and I don't know TCL that well, and it would require the addition of the flags in the C code, or I would submit some patch ideas myself). -- 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
Re: [sqlite] Can it (should it) be done in SQL?
On Monday, 20 January, 2020 12:42, David Bicking wrote:
> Thanks. I figured the solution would use CTE (this is a CTE, isn't it??)
>Unfortunately, they were neither in Sqlite, nor mentioned in any of the
>sql stuff I read when I taught myself to do SQL.so it took me a while to
>figure out how it works.
Yes, it is a recursive CTE. It calculates the UNK line for the current period,
then for each additional period for which there is a goal it calculates the
that periods UNK line. And adjustment only needs to be applied if the previous
UNK was less than 0.
>Unfortunately, I extend the goals to cover all 12 months, leavings
>actuals for just periods 1 to 3. The result has UNK lines for periods 4
>to 12, with a null for the aount. I am guessing that this is because the
>in the unks cte it is subtracting the sum from data, but there is nothing
>in data, so it is subtracting a null resulting in a null.
You can fix this by replacing all the sum(amount) with total(amount). sum
returns null if there is no sum, total returns 0.0. Then cast the result back
to an integer at the end.
Do that with this too
>select amount
> from goals
>where period == unks.period+1
to make sure the return value is 0.0 rather than null (even though there can
only be one record, it is the easiest way).
>I was able to put a coalesce around the data sum, and that does work.
>The line strftime('%m') seemed very strange. I guess without a time
>string, sqlite defaults the the current date and time. the sqlite docs
>don't mention this.
strftime('%m') is the same as strftime('%m', 'now'), the 'now' is the default
if no arguments are provided for the datetime part. However, what you probably
want is strftime('%m', 'now', 'localtime') which will return the current month
at your current timezone location, not at the prime meridian.
A CTE that will handle those cases might look like this (with all explicit
casts and allowing NULL amounts, and data with no goals, and goals with no
data):
with first (period)
as (
values (cast(strftime('%m', 'now', 'localtime') as integer))
),
last (period)
as (
select max((
select max(period)
from goals
), (
select max(period)
from data
))
),
unks (period, type, amount)
as (
select first.period,
'UNK',
(
select total(amount)
from goals
where period between 1 and first.period
) - (
select total(amount)
from data
where period between 1 and first.period
)
from first, last
where first.period <= last.period
union all
select unks.period + 1,
'UNK',
(
select total(amount)
from goals
where period == unks.period + 1
) - (
select total(amount)
from data
where period == unks.period + 1
) + min(unks.amount, 0)
from unks, last
where unks.period < last.period
)
select period,
type,
amount
from data
union all
select period,
type,
cast(max(0, amount) as integer)
from unks
order by 1, 2;
You do, of course, need an index on Data(period) to avoid all the table scans.
--
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
Re: [sqlite] Not Null Constraint Issue?
>Could you show us the constraint you feel disallows those things ? We might be able to find a loophole in the exact way you phrased it. Also, could someone explain to me what EMPTY means here ? I've seen '' called "empty string" but it doesn't seem to be normal SQL language. - Simon You are correct. When I say "empty" I am meaning "empty string". My apologies if my nomenclature is incorrect here in terms of SQL speak. Regarding the constraint, to column is set as INTEGER NOT NULL, but because of SQLite's dynamic typing system (something I forget about), empty strings can be passed into INTEGER columns as they are technically not null. Now if a non numeric text would have made it into an INTEGER field, we would have dissected the issue a lot faster (and there is almost no way that would have happened anyway). Because it was an empty string value that made it in there, it appeared as a NULL to us when we tried reading the database as our database reader expected an INTEGER. It took as a while to figure out that the NULL value wasn't really NULL, but an empty string value sitting in an INTEGER field. I definitely know to look out for this now. From: Simon Slavin To: SQLite mailing list Subject: Re: [sqlite] Not Null Constraint Issue? Message-ID: <46053091-b9ab-43c4-b7f9-c89d70986...@bigfraud.org> Content-Type: text/plain; charset=us-ascii On 17 Jan 2020, at 6:39pm, Justin Gielski wrote: > After looking into the issue a bit more, it appears the INTEGER value was actually saved into the DB as an EMPTY not a NULL, which is currently allowed by the database constraints. Could you show us the constraint you feel disallows those things ? We might be able to find a loophole in the exact way you phrased it. Also, could someone explain to me what EMPTY means here ? I've seen '' called "empty string" but it doesn't seem to be normal SQL language. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can it (should it) be done in SQL?
Thanks. I figured the solution would use CTE (this is a CTE, isn't it??)
Unfortunately, they were neither in Sqlite, nor mentioned in any of the sql
stuff I read when I taught myself to do SQL.so it took me a while to figure out
how it works.
Unfortunately, I extend the goals to cover all 12 months, leavings actuals for
just periods 1 to 3. The result has UNK lines for periods 4 to 12, with a null
for the aount. I am guessing that this is because the in the unks cte it is
subtracting the sum from data, but there is nothing in data, so it is
subtracting a null resulting in a null.
I was able to put a coalesce around the data sum, and that does work.
The line strftime('%m') seemed very strange. I guess without a time string,
sqlite defaults the the current date and time. the sqlite docs don't mention
this.
David
On Saturday, January 18, 2020, 04:32:52 PM EST, Keith Medcalf
wrote:
Ooops. Wrong query pasted, should be this one:
with p (period) as (
values (cast(strftime('%m') as integer))
),
unks (period, type, amount) as (
select p.period,
'UNK',
(
select sum(amount)
from goals
where period between 1 and p.period
) - (
select sum(amount)
from data
where period between 1 and p.period
)
from p
where p.period <= (select max(period) from goals)
union all
select period+1,
'UNK',
(
select amount
from goals
where period == unks.period+1
) - (
select sum(amount)
from data
where period == unks.period+1
) + case when unks.amount < 0 then unks.amount else 0 end
from unks
where period < (select max(period) from goals)
)
select period,
type,
amount
from data
union all
select period,
type,
max(0, amount)
from unks
order by 1, 2;
--
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 On
>Behalf Of David Bicking
>Sent: Saturday, 18 January, 2020 11:20
>To: SQLite Mailing List
>Subject: [sqlite] Can it (should it) be done in SQL?
>
>I suspect the answer is that it is best to do this in the application
>program. However, the platform I want to use is dumb as a brick.
>It basically can call sqlite3_get_table, mildly reformat the data
>and send it to the display.
>
>Anyway, there are two tables
>CREATE TABLE Goals (period integer primary key, amount int);
>CREATE TABLE Data (period int, type text, amount int);
>
>periods above are 1 to 12, and amounts are zero or positive.
>
>INSERT INTO Goals Values (1,10), (2,10), (3,10);
>INSERT INTO Data Values (1,'A',3), (2, 'A', 5) (2, 'B', 6), (3, 'A', 2);
>
>Okay, last time I tried to send a list to the group it came out
>unreadable. Crossing fingers:
>
>PERIOD | TYPE | AMOUNT
>1 | A | 3
>1 | UNK | 7 -- the goal of 10 - data of 3
>2 | A | 5
>2 | B | 6
>2 | UNK | 0 -- goals of 1&2 = 20 - previous lines of 21
> -- with the negative result changed to 0
>3 | A | 2
>3 | UNK | 7 -- goals 1-3 = 30 - previous lines of 23
>
>Hopefully that makes sense. I need to insert a row into the output
>that ends each period at or above the accumulated goals.
>
>A further complication. The "UNK" row only gets inserted for the
>current or future periods. (i.e in two weeks it will be February,
>so the January UNK will be removed and the Feb UNK would
>become 6 (20 - 3 - 11).
>
>The best I got is
>
>select period, type, amt from Data
>UNION
>select period, 'UNK', (select sum(amount) from Goals G
> where g.period <= goals.period)
> -(select sum(amount) from Data D
> where d.period <= goals.period)
>from Goals;
>
>But it doesn't pick up the calculated "UNK" values from the prior
>periods.
>nor does it deal with negatives or not calculating UNK from periods in
>the past.
>
>So can this report be done within a query?
>
>Thanks,
>David
>___
>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
Re: [sqlite] Find schema of a table in a query
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 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 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
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 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find schema of a table in a query
> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find schema of a table in a query
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find schema of a table in a query
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 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 On >Behalf Of Keith Medcalf >Sent: Monday, 20 January, 2020 02:18 >To: SQLite mailing list >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 On >>Behalf Of x >>Sent: Monday, 20 January, 2020 01:27 >>To: SQLite mailing list >>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)
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 On >Behalf Of Keith Medcalf >Sent: Monday, 20 January, 2020 02:18 >To: SQLite mailing list >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 On >>Behalf Of x >>Sent: Monday, 20 January, 2020 01:27 >>To: SQLite mailing list >>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
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 On >Behalf Of x >Sent: Monday, 20 January, 2020 01:27 >To: SQLite mailing list >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 on >behalf of Keith Medcalf >Sent: Sunday, January 19, 2020 8:32:06 PM >To: SQLite mailing list >Subject: Re: [sqlite] Find schema of a table in a query > > >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 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
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 on behalf of Keith Medcalf Sent: Sunday, January 19, 2020 8:32:06 PM To: SQLite mailing list Subject: Re: [sqlite] Find schema of a table in a query 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 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
