Re: [sqlite] Checking differences in tables

[Keith Medcalf]

insert into t (a, b, c, d, e, idate) 
 values ('p001', 1, 2, 
  (SELECT d FROM t
WHERE a = 'p001'
 ORDER BY idate desc
limit 1
  ),
  4, '2019-02-12');


---
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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Kees Nuyt
>Sent: Tuesday, 12 February, 2019 17:55
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Checking differences in tables
>
>On Tue, 12 Feb 2019 21:03:47 +, you wrote:
>
>>
>>
>>
>> David Raymond, on Tuesday, February 12, 2019 03:48 PM, wrote...
>>> Not sure if this will fix your specific issue, but if you're using
>a query as a single
>>> value it needs to be in parenthesis, so something like
>>>
>>> insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT
>d FROM t WHERE a = 'p006'
>>> AND max(idate)), 4, '2019-02-12');
>>
>> I get,
>> sqlite> insert into t (a, b, c, d, e, idate)
>> values ('p001', 1, 2,
>>  (SELECT d FROM t WHERE a = 'p001' AND max(idate)),
>> 4, '2019-02-12');
>> Error: misuse of aggregate function max()
>
>Try:
>insert into t (a, b, c, d, e, idate)
> values ('p001', 1, 2,
>  (SELECT d FROM t
>WHERE a = 'p001'
>  AND idate = (SELECT max(idate) FROM t WHERE a = 'p001')
>  ),
>  4, '2019-02-12');
>
>--
>Regards,
>Kees Nuyt
>
>
>___
>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] Building Amalgamation

[D Burgess]

Thanks Simon. I had previously read that.
From that link it says:
" The set of compile-time options that must be passed into the code
generators can vary from one release of SQLite to the next, but at the time
of this writing (circa SQLite 3.6.20, 2009-11-04) the set of options that
must be known by the code generators includes:"

3.6.20 is a loong time ago. Which led me to think that maybe that list is
no longer be accurate.
Is there an easy way to extract the available options for the latest
release?

On Wed, Feb 13, 2019 at 5:02 PM Simon Slavin  wrote:

> On 13 Feb 2019, at 5:56am, D Burgess  wrote:
>
> > Is there a definitive list of "defines" for building the amalgamation.
>
> You might mean this:
>
> 
>
> If it's something else you want, post again and we'll try to help.
>
> Simon.
> ___
> 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] Building Amalgamation

[Simon Slavin]

On 13 Feb 2019, at 5:56am, D Burgess  wrote:

> Is there a definitive list of "defines" for building the amalgamation.

You might mean this:



If it's something else you want, post again and we'll try to help.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Building Amalgamation

[D Burgess]

Is there a definitive list of "defines" for building the amalgamation.
i.e. for the following two steps

$ ./configure
$ ./make sqlite3.c

I build the Amalgamation because I need ENABLE_UPDATE_DELETE_LIMIT.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Checking differences in tables

[Jose Isaias Cabrera]

Kees Nuyt, on Tuesday, February 12, 2019 07:55 PM, wrote...
>On Tue, 12 Feb 2019 21:03:47 +, you wrote:
>> David Raymond, on Tuesday, February 12, 2019 03:48 PM, wrote...
>>> Not sure if this will fix your specific issue, but if you're using a query 
>>> as a single
>>> value it needs to be in parenthesis, so something like
>>>
>>> insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT d FROM t 
>>> WHERE a = 'p006'
>>> AND max(idate)), 4, '2019-02-12');
>>
>> I get,
>> sqlite> insert into t (a, b, c, d, e, idate)
>> values ('p001', 1, 2,
>>  (SELECT d FROM t WHERE a = 'p001' AND max(idate)),
>> 4, '2019-02-12');
>> Error: misuse of aggregate function max()
>
>Try:
>insert into t (a, b, c, d, e, idate)
> values ('p001', 1, 2,
>  (SELECT d FROM t
>WHERE a = 'p001'
>  AND idate = (SELECT max(idate) FROM t WHERE a = 'p001')
>  ),
>  4, '2019-02-12');

Man, you guys are good.  Thanks.  Yep, that works.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem to understand "Persistent Loadable Extensions"

[Ricardo Torquato]

Thanks!

I’m trying to transform the extension-functions.c in a persistent extension.

The original entry function is that:


int sqlite3_extension_init(
sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){
  SQLITE_EXTENSION_INIT2(pApi);
  RegisterExtensionFunctions(db);
  return 0;
}

After reading this append you sent I’m trying something like this:

int sqlite3_extension_init(
sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  rc = RegisterExtensionFunctions(db, pzErrMsg, pApi);
  if (rc == SQLITE_OK) {
rc = sqlite3_auto_extension(RegisterExtensionFunctions);
  }
  
  if (rc == SQLITE_OK) rc = SQLITE_OK_LOAD_PERMANENTLY;

  return rc;
}

And I’ve changed the signature of the RegisterExtensionFunctions to receive the 
pzErrMsg and pApi.

It’s not working yet, but I’m trying here. Am I on the right way?

> Em 12 de fev de 2019, à(s) 10:28, Richard Hipp  escreveu:
> 
> On 2/11/19, Ricardo Torquato  wrote:
>> Hey guys!
>> 
>> According to "Persistent Loadable Extensions” topic on
>> https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sqlite.org%2Floadext.html&data=02%7C01%7C%7Cdbb778fd875048b029e308d690e5a6b9%7C84df9e7fe9f640afb435%7C1%7C0%7C636855713347310297&sdata=u5oqlokW%2FAnemdWqoO23z3aKCRq9bx0oQ8hfekDUvos%3D&reserved=0
>>  if the initialization procedure returns
>> SQLITE_OK_LOAD_PERMANENTLY (256) the extension should persist on the
>> database file instead of just belong to the current connection.
>> 
>> So I’ve downloaded the extension-functions.c and before compile that I’ve
>> changed the the return of sqlite3_extension_init from 0 to 256. But still,
>> my extension just exists on the current connection.
>> 
>> Did I misunderstand the concept? Am I doing something wrong?
> 
> New text has been added to 
> https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sqlite.org%2Floadext.html%23persist&data=02%7C01%7C%7Cdbb778fd875048b029e308d690e5a6b9%7C84df9e7fe9f640afb435%7C1%7C0%7C636855713347310297&sdata=lwQuWj2qS%2FU8nY9eswtHETiz2FojeeNp3zB7ke14RiY%3D&reserved=0
> to hopefully clarify the situation.
> -- 
> D. Richard Hipp
> d...@sqlite.org

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Checking differences in tables

[Kees Nuyt]

On Tue, 12 Feb 2019 21:03:47 +, you wrote:

>
>
>
> David Raymond, on Tuesday, February 12, 2019 03:48 PM, wrote...
>> Not sure if this will fix your specific issue, but if you're using a query 
>> as a single
>> value it needs to be in parenthesis, so something like
>>
>> insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT d FROM t 
>> WHERE a = 'p006'
>> AND max(idate)), 4, '2019-02-12');
>
> I get,
> sqlite> insert into t (a, b, c, d, e, idate) 
> values ('p001', 1, 2, 
>  (SELECT d FROM t WHERE a = 'p001' AND max(idate)),
> 4, '2019-02-12');
> Error: misuse of aggregate function max()

Try:
insert into t (a, b, c, d, e, idate) 
 values ('p001', 1, 2, 
  (SELECT d FROM t
WHERE a = 'p001'
  AND idate = (SELECT max(idate) FROM t WHERE a = 'p001')
  ),
  4, '2019-02-12');

-- 
Regards,
Kees Nuyt


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Checking differences in tables

[Jose Isaias Cabrera]

David Raymond, on Tuesday, February 12, 2019 03:48 PM, wrote...
>Not sure if this will fix your specific issue, but if you're using a query as 
>a single
>value it needs to be in parenthesis, so something like
>
> insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT d FROM t 
> WHERE a = 'p006'
>AND max(idate)), 4, '2019-02-12');

I get,
sqlite> insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, (SELECT d 
FROM t WHERE a = 'p001' AND max(idate)), 4, '2019-02-12');
Error: misuse of aggregate function max()


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jose Isaias Cabrera
Sent: Tuesday, February 12, 2019 3:42 PM
To: James K. Lowden; SQLite mailing list
Subject: Re: [sqlite] Checking differences in tables


>James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...
>>On Tue, 12 Feb 2019 15:05:29 +
>>Jose Isaias Cabrera  wrote:
>>
>>> >SELECT * From t WHERE datestamp = "20190208"
>>>
>>> Ok, Simon, I'll bite; :-) Imagine this table:
>>>
>>> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)
>>
>>That's better.
>>
>>> how would I find the differences in the fields based on the different
>>> idate?
>>
>>select ...
>>from t as now join t as then
>>on now.idate = '20190208'
>>and then.idate = '20190207' -- note single quotes
>>and ... -- other things that match
>>where ... --- things that don't match
>>
>>Can't be more specific than that when the question is "find the
>>differences".
>
>No, this is great.  This is great, thanks.

One last question on this...  Imagine this scenario...

create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-11');

if I am inserting new records into the table, and there is a field, say "d", I 
want to keep the last value of that field for the last inserted record, how can 
I do this?  I am trying this,

insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, SELECT d FROM t 
WHERE a = 'p001' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 1, 2, SELECT d FROM t 
WHERE a = 'p002' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 1, 2, SELECT d FROM t 
WHERE a = 'p003' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 1, 2, SELECT d FROM t 
WHERE a = 'p004' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 1, 2, SELECT d FROM t 
WHERE a = 'p005' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, SELECT d FROM t 
WHERE a = 'p006' AND max(idate), 4, '2019-02-12');  -- new

But, of course, it's not working. I get,

Error: near "SELECT": syntax error

Thougths?  thanks.




___
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] Checking differences in tables

[David Raymond]

Not sure if this will fix your specific issue, but if you're using a query as a 
single value it needs to be in parenthesis, so something like

insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT d FROM t 
WHERE a = 'p006' AND max(idate)), 4, '2019-02-12');



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jose Isaias Cabrera
Sent: Tuesday, February 12, 2019 3:42 PM
To: James K. Lowden; SQLite mailing list
Subject: Re: [sqlite] Checking differences in tables


>James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...
>>On Tue, 12 Feb 2019 15:05:29 +
>>Jose Isaias Cabrera  wrote:
>>
>>> >SELECT * From t WHERE datestamp = "20190208"
>>>
>>> Ok, Simon, I'll bite; :-) Imagine this table:
>>>
>>> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)
>>
>>That's better.
>>
>>> how would I find the differences in the fields based on the different
>>> idate?
>>
>>select ...
>>from t as now join t as then
>>on now.idate = '20190208'
>>and then.idate = '20190207' -- note single quotes
>>and ... -- other things that match
>>where ... --- things that don't match
>>
>>Can't be more specific than that when the question is "find the
>>differences".
>
>No, this is great.  This is great, thanks.

One last question on this...  Imagine this scenario...

create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-11');

if I am inserting new records into the table, and there is a field, say "d", I 
want to keep the last value of that field for the last inserted record, how can 
I do this?  I am trying this,

insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, SELECT d FROM t 
WHERE a = 'p001' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 1, 2, SELECT d FROM t 
WHERE a = 'p002' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 1, 2, SELECT d FROM t 
WHERE a = 'p003' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 1, 2, SELECT d FROM t 
WHERE a = 'p004' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 1, 2, SELECT d FROM t 
WHERE a = 'p005' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, SELECT d FROM t 
WHERE a = 'p006' AND max(idate), 4, '2019-02-12');  -- new

But, of course, it's not working. I get,

Error: near "SELECT": syntax error

Thougths?  thanks.




___
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] Checking differences in tables

[Jose Isaias Cabrera]

>James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...
>>On Tue, 12 Feb 2019 15:05:29 +
>>Jose Isaias Cabrera  wrote:
>>
>>> >SELECT * From t WHERE datestamp = "20190208"
>>>
>>> Ok, Simon, I'll bite; :-) Imagine this table:
>>>
>>> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)
>>
>>That's better.
>>
>>> how would I find the differences in the fields based on the different
>>> idate?
>>
>>select ...
>>from t as now join t as then
>>on now.idate = '20190208'
>>and then.idate = '20190207' -- note single quotes
>>and ... -- other things that match
>>where ... --- things that don't match
>>
>>Can't be more specific than that when the question is "find the
>>differences".
>
>No, this is great.  This is great, thanks.

One last question on this...  Imagine this scenario...

create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-11');

if I am inserting new records into the table, and there is a field, say "d", I 
want to keep the last value of that field for the last inserted record, how can 
I do this?  I am trying this,

insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, SELECT d FROM t 
WHERE a = 'p001' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 1, 2, SELECT d FROM t 
WHERE a = 'p002' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 1, 2, SELECT d FROM t 
WHERE a = 'p003' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 1, 2, SELECT d FROM t 
WHERE a = 'p004' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 1, 2, SELECT d FROM t 
WHERE a = 'p005' AND max(idate), 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, SELECT d FROM t 
WHERE a = 'p006' AND max(idate), 4, '2019-02-12');  -- new

But, of course, it's not working. I get,

Error: near "SELECT": syntax error

Thougths?  thanks.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve tables accessed by query

[Nelson, Erik - 2]

That could be a useful approach, and automatable.  It might get complicated in 
the presence of table aliases, like

sqlite> select a.test from b as a;
Error: no such table: b

sqlite> create table b(hello);

sqlite> select a.test from b as a;
Error: no such column: a.test

In the end, the query engine needs to resolve to 'real' tables?

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Raymond
Sent: Tuesday, February 12, 2019 3:01 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Retrieve tables accessed by query

If you want the bare minimum of what will satisfy a statement I suppose there's 
always the iterative approach of:
Start with empty database
try your statement -> fix the error message -> repeat until it doesn't give an 
error


SQLite version 3.27.1 2019-02-08 13:17:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such table: tbl1

sqlite> create table tbl1 (rowid integer primary key);

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such table: tbl2

sqlite> create table tbl2 (rowid integer primary key);

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl1.field1

sqlite> alter table tbl1 add column field1;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl2.field3

sqlite> alter table tbl2 add column field3;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl1.field4

sqlite> alter table tbl1 add column field4;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl2.field2

sqlite> alter table tbl2 add column field2;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
QUERY PLAN
|--SCAN TABLE tbl1
`--SEARCH TABLE tbl2 USING AUTOMATIC COVERING INDEX (field2=?)

sqlite> .schema
CREATE TABLE tbl1 (rowid integer primary key, field1, field4);
CREATE TABLE tbl2 (rowid integer primary key, field3, field2);

sqlite>


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Tuesday, February 12, 2019 2:29 PM
To: SQLite mailing list
Subject: Re: [sqlite] Retrieve tables accessed by query

For

>Could you expound on that a bit more? If the tables don't exist then it 
>becomes a moot point.

The question I'm hoping to answer is "what tables do I need to make in order to 
fulfil this query?".

For

>select * from foo;

I was wondering if I could get a call for 'foo' on the authorizer API, even 
though this query may not be able to be compiled to completion.

Regarding the other complexities you mentioned, point taken.  At the end of the 
day, though, the query results be based on zero or more actual tables- I was 
hoping there might be some robust way to detect what those are.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Raymond
Sent: Tuesday, February 12, 2019 2:12 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Retrieve tables accessed by query

Could you expound on that a bit more? If the tables don't exist then it becomes 
a moot point.

If you have the extremely simple

select * from foo;

Then with no context "foo" is what's accessed.

But inside an actual database, "foo" could be a view involving other tables or 
views. And the query planner might use covering indexes that don't touch base 
tables at all, etc etc. And if it's a modifying statement there might be 
triggers that access their own things, etc. So are you looking for "parse this 
SQL", or "what specific bits of this specific database would get accessed were 
I to run this SQL"?

As a side point, if I recall, "the parsed AST" is something you can't get, 
specifically so that they're free to completely change it in any way they want 
at any time without the issue of "oops, this might break someone else's code." 
(Or I may be thinking of something else here)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Tuesday, February 12, 2019 1:00 PM
To: SQLite mailing list
Subject: Re: [sqlite] Retrieve tables accessed by query

Clemens Ladisch Sent: Tuesday, February 12, 2019 12:38 PM
>Zach Wasserman wrote:
>> Is anyone aware of an API I can use to determine which tables are accessed
>> by a given query?

Both Clemens and Warren had good comments.  I'm curious if there's a way to do 
it in a database where the tables *don't exist*.  For example, I have a q

Re: [sqlite] Retrieve tables accessed by query

[David Raymond]

If you want the bare minimum of what will satisfy a statement I suppose there's 
always the iterative approach of:
Start with empty database
try your statement -> fix the error message -> repeat until it doesn't give an 
error


SQLite version 3.27.1 2019-02-08 13:17:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such table: tbl1

sqlite> create table tbl1 (rowid integer primary key);

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such table: tbl2

sqlite> create table tbl2 (rowid integer primary key);

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl1.field1

sqlite> alter table tbl1 add column field1;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl2.field3

sqlite> alter table tbl2 add column field3;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl1.field4

sqlite> alter table tbl1 add column field4;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
Error: no such column: tbl2.field2

sqlite> alter table tbl2 add column field2;

sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on 
tbl1.field4 = tbl2.field2;
QUERY PLAN
|--SCAN TABLE tbl1
`--SEARCH TABLE tbl2 USING AUTOMATIC COVERING INDEX (field2=?)

sqlite> .schema
CREATE TABLE tbl1 (rowid integer primary key, field1, field4);
CREATE TABLE tbl2 (rowid integer primary key, field3, field2);

sqlite>


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Tuesday, February 12, 2019 2:29 PM
To: SQLite mailing list
Subject: Re: [sqlite] Retrieve tables accessed by query

For

>Could you expound on that a bit more? If the tables don't exist then it 
>becomes a moot point.

The question I'm hoping to answer is "what tables do I need to make in order to 
fulfil this query?".

For

>select * from foo;

I was wondering if I could get a call for 'foo' on the authorizer API, even 
though this query may not be able to be compiled to completion.

Regarding the other complexities you mentioned, point taken.  At the end of the 
day, though, the query results be based on zero or more actual tables- I was 
hoping there might be some robust way to detect what those are.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Raymond
Sent: Tuesday, February 12, 2019 2:12 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Retrieve tables accessed by query

Could you expound on that a bit more? If the tables don't exist then it becomes 
a moot point.

If you have the extremely simple

select * from foo;

Then with no context "foo" is what's accessed.

But inside an actual database, "foo" could be a view involving other tables or 
views. And the query planner might use covering indexes that don't touch base 
tables at all, etc etc. And if it's a modifying statement there might be 
triggers that access their own things, etc. So are you looking for "parse this 
SQL", or "what specific bits of this specific database would get accessed were 
I to run this SQL"?

As a side point, if I recall, "the parsed AST" is something you can't get, 
specifically so that they're free to completely change it in any way they want 
at any time without the issue of "oops, this might break someone else's code." 
(Or I may be thinking of something else here)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Tuesday, February 12, 2019 1:00 PM
To: SQLite mailing list
Subject: Re: [sqlite] Retrieve tables accessed by query

Clemens Ladisch Sent: Tuesday, February 12, 2019 12:38 PM
>Zach Wasserman wrote:
>> Is anyone aware of an API I can use to determine which tables are accessed
>> by a given query?

Both Clemens and Warren had good comments.  I'm curious if there's a way to do 
it in a database where the tables *don't exist*.  For example, I have a query 
and I want to find out which tables the query makes so I can create only those. 
 This query is obviously not valid in the current database... will it still 
(for example) call the authorizer interface?

Thanks

Erik

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipie

Re: [sqlite] Retrieve tables accessed by query

[Nelson, Erik - 2]

For

>Could you expound on that a bit more? If the tables don't exist then it 
>becomes a moot point.

The question I'm hoping to answer is "what tables do I need to make in order to 
fulfil this query?".

For

>select * from foo;

I was wondering if I could get a call for 'foo' on the authorizer API, even 
though this query may not be able to be compiled to completion.

Regarding the other complexities you mentioned, point taken.  At the end of the 
day, though, the query results be based on zero or more actual tables- I was 
hoping there might be some robust way to detect what those are.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Raymond
Sent: Tuesday, February 12, 2019 2:12 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Retrieve tables accessed by query

Could you expound on that a bit more? If the tables don't exist then it becomes 
a moot point.

If you have the extremely simple

select * from foo;

Then with no context "foo" is what's accessed.

But inside an actual database, "foo" could be a view involving other tables or 
views. And the query planner might use covering indexes that don't touch base 
tables at all, etc etc. And if it's a modifying statement there might be 
triggers that access their own things, etc. So are you looking for "parse this 
SQL", or "what specific bits of this specific database would get accessed were 
I to run this SQL"?

As a side point, if I recall, "the parsed AST" is something you can't get, 
specifically so that they're free to completely change it in any way they want 
at any time without the issue of "oops, this might break someone else's code." 
(Or I may be thinking of something else here)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Tuesday, February 12, 2019 1:00 PM
To: SQLite mailing list
Subject: Re: [sqlite] Retrieve tables accessed by query

Clemens Ladisch Sent: Tuesday, February 12, 2019 12:38 PM
>Zach Wasserman wrote:
>> Is anyone aware of an API I can use to determine which tables are accessed
>> by a given query?

Both Clemens and Warren had good comments.  I'm curious if there's a way to do 
it in a database where the tables *don't exist*.  For example, I have a query 
and I want to find out which tables the query makes so I can create only those. 
 This query is obviously not valid in the current database... will it still 
(for example) call the authorizer interface?

Thanks

Erik

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY&m=z_60ozV957S1aN7o8jya-G09AGlze-BReDPKaQJHJqM&s=Laupns_-TUEj7GOdta_BY0AA-BpZ-yEuUfwRLHz-Z_I&e=
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY&m=z_60ozV957S1aN7o8jya-G09AGlze-BReDPKaQJHJqM&s=Laupns_-TUEj7GOdta_BY0AA-BpZ-yEuUfwRLHz-Z_I&e=

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] why no unique columns on alter table

[Amit Yaron]

Probably because altering a table is done by changing the value of 
column SQL in table 'sqlite_master'.

Adding a unique constraint requires value checks.

On 12.2.2019 6:15, Mark Wagner wrote:

This is mainly for my curiosity.   Is there any particular reason that one
can't add a unique column on an alter table?  With a default value of null
they would all have unique values by default.

Any insight into this would be great.  Perhaps there' something obvious I'm
missing.

-- Mark
___
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] Choosing one value from the group with priority

[R Smith]

On 2019/02/12 7:29 PM, Ivan Krylov wrote:
I can supply a list of source_ids in order of decreasing priority from 
an array in my application, though I lose cross-database portability 
(is there a portable way to pass an array to a parameterized query, 
though?)... 


There is no fully portable way to pass a parameterized array to a query, 
though nearly every RDBMS engine has /some/ way of doing it.


You could however construct query text to achieve the same, for example: 
Imagine a query that lists some values from some tables, and needs to 
match against the first bit of the Fibonacci sequence, which is simply 
an array of say [1,1,2,3,5,8,13,21], so if you join a subquery using a 
value constructed from the array of the form:

"JOIN (SELECT n1 AS y [UNION ALL SELECT n2] ... [UNION ALL SELECT nf]) AS s"
where s is the alias for the joined query and y is the alias for the 
array value.


SQL example:

SELECT x, ...
  FROM ...
  JOIN (SELECT 1 AS Par UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 
SELECT 3 UNION ALL SELECT 5 UNION ALL SELECT 8 UNION ALL SELECT 13 UNION 
ALL SELECT 21) AS Fib

 WHERE x = Fib.Par


Note that there are far more efficient ways of doing this in most 
engines, such as using the VALUES () construct, but they all differ from 
engine to engine, whereas the above way is fully portable between all 
engines.


Note also that another fully portable and better method would be to 
construct first a TEMP table populated with the array values, and then 
simply join against that - but it does require an extra query, however, 
if you have any chance of re-using the query or using the same array 
more than once in other queries, then the TEMP table becomes exceedingly 
more efficient.



Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve tables accessed by query

[David Raymond]

Could you expound on that a bit more? If the tables don't exist then it becomes 
a moot point.

If you have the extremely simple

select * from foo;

Then with no context "foo" is what's accessed.

But inside an actual database, "foo" could be a view involving other tables or 
views. And the query planner might use covering indexes that don't touch base 
tables at all, etc etc. And if it's a modifying statement there might be 
triggers that access their own things, etc. So are you looking for "parse this 
SQL", or "what specific bits of this specific database would get accessed were 
I to run this SQL"?

As a side point, if I recall, "the parsed AST" is something you can't get, 
specifically so that they're free to completely change it in any way they want 
at any time without the issue of "oops, this might break someone else's code." 
(Or I may be thinking of something else here)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Tuesday, February 12, 2019 1:00 PM
To: SQLite mailing list
Subject: Re: [sqlite] Retrieve tables accessed by query

Clemens Ladisch Sent: Tuesday, February 12, 2019 12:38 PM
>Zach Wasserman wrote:
>> Is anyone aware of an API I can use to determine which tables are accessed
>> by a given query?

Both Clemens and Warren had good comments.  I'm curious if there's a way to do 
it in a database where the tables *don't exist*.  For example, I have a query 
and I want to find out which tables the query makes so I can create only those. 
 This query is obviously not valid in the current database... will it still 
(for example) call the authorizer interface?

Thanks

Erik

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
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] Checking differences in tables

[Jose Isaias Cabrera]

James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...
>On Tue, 12 Feb 2019 15:05:29 +
>Jose Isaias Cabrera  wrote:
>
>> >SELECT * From t WHERE datestamp = "20190208"
>>
>> Ok, Simon, I'll bite; :-) Imagine this table:
>>
>> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)
>
>That's better.
>
>> how would I find the differences in the fields based on the different
>> idate?
>
>select ...
>from t as now join t as then
>on now.idate = '20190208'
>and then.idate = '20190207' -- note single quotes
>and ... -- other things that match
>where ... --- things that don't match
>
>Can't be more specific than that when the question is "find the
>differences".

No, this is great.  This is great, thanks.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve tables accessed by query

[Nelson, Erik - 2]

Clemens Ladisch Sent: Tuesday, February 12, 2019 12:38 PM
>Zach Wasserman wrote:
>> Is anyone aware of an API I can use to determine which tables are accessed
>> by a given query?

Both Clemens and Warren had good comments.  I'm curious if there's a way to do 
it in a database where the tables *don't exist*.  For example, I have a query 
and I want to find out which tables the query makes so I can create only those. 
 This query is obviously not valid in the current database... will it still 
(for example) call the authorizer interface?

Thanks

Erik

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve tables accessed by query

[Clemens Ladisch]

Zach Wasserman wrote:
> Is anyone aware of an API I can use to determine which tables are accessed
> by a given query?

https://www.sqlite.org/c3ref/set_authorizer.html


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve tables accessed by query

[J. King]

On February 12, 2019 12:30:17 PM EST, Zach Wasserman  
wrote:
>Hello SQLite Users,
>
>Is anyone aware of an API I can use to determine which tables are
>accessed
>by a given query? Or, is there a way to retrieve the parsed AST of a
>query?
>If this is possible directly through the SQLite shell that would be
>great,
>but C API would work as well.
>
>Thank you,
>Zach Wasserman
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Authorizer callbacks could be (ab)used for this purpose, I would think. 



I'm not very familiar with SQLite's C interface, though, so I may be dead 
wrong. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve tables accessed by query

[Warren Young]

On Feb 12, 2019, at 10:30 AM, Zach Wasserman  wrote:
> 
> Is anyone aware of an API I can use to determine which tables are accessed
> by a given query? Or, is there a way to retrieve the parsed AST of a query?
> If this is possible directly through the SQLite shell that would be great,
> but C API would work as well.

EXPLAIN QUERY PLAN?   https://www.sqlite.org/lang_explain.html

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Choosing one value from the group with priority

[Ivan Krylov]

On Tue, 12 Feb 2019 13:31:59 +0100
Clemens Ladisch  wrote:

> SQLite allows to SELECT columns that are not mentioned in the GROUP
> BY clause, but they get their values from a random row in the group.

Thank you for pointing this out! I was aware of the row being
random when selected this way, but I didn't realize that it wasn't
standard SQL. One more reason to avoid SELECTs like this, then.

> There is another SQLite extension which allows to select a row in the
> group by using MAX() or MIN():
> 
>   select *, min(abs(source_id - 3)) from test where id in (1,2) group
> by id;

I'm afraid to use this because the queries I'm considering now may be
reused later in a different SQL engine, and a shortcut like this might
be easy to look over. Maybe I should just select min(abs(source_id-?))
because it is so much simpler, leave a comment and worry about it
and the more general cases later (if ever).

-- 
Best regards,
Ivan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Retrieve tables accessed by query

[Zach Wasserman]

Hello SQLite Users,

Is anyone aware of an API I can use to determine which tables are accessed
by a given query? Or, is there a way to retrieve the parsed AST of a query?
If this is possible directly through the SQLite shell that would be great,
but C API would work as well.

Thank you,
Zach Wasserman
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Choosing one value from the group with priority

[Ivan Krylov]

On Tue, 12 Feb 2019 14:08:20 +0200
R Smith  wrote:

> The semantics are made more legible using a CTE here, but can be
> done without it. The essential difference is that it uses a sub-query
> to obtain the minimum priority as opposed to introducing a sub-table
> sort.

Thank you for your reply!

The CTE example has been a learning experience. I have played around
some more and produced a solution for the general "every source_id has
its own priority" case, which I'm including in case someone else finds
this thread:

WITH
RANKED(id, value, rk) AS (
SELECT id, value, CASE source_id
-- source_id =then=> rank
when 3 then 1
when 1 then 2
when 2 then 3
END
FROM test
)
SELECT id, value
FROM RANKED AS test1
WHERE
test1.id IN (1,2)
AND test1.rk = (
SELECT MIN(rk)
FROM RANKED AS test2
WHERE test2.id = test1.id
)
;

This produces the same query plan as your query. Omitting a WHEN allows
me to completely ignore a source_id, should I want that. With

RANKED(id, value, rk) AS (
SELECT id, test.value, RANKING.rowid
FROM test
JOIN carray($ptr,$n,$type) AS RANKING
ON test.source_id = RANKING.value
)

I can supply a list of source_ids in order of decreasing priority from
an array in my application, though I lose cross-database portability
(is there a portable way to pass an array to a parameterized query,
though?) and may lose some of the performance to the JOIN (didn't
build a sample application to run EXPLAIN QUERY PLAN, sorry).

-- 
Best regards,
Ivan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] lost errors on writeback/fsync/close - does SQLite need to be fixed similary to PostgreSQL?

[Jens Alfke]

> On Feb 11, 2019, at 11:07 PM, Rowan Worth  wrote:
> 
> if process A is updating the DB via write() calls, the locking protocol
> ensures that no other process will call write() or fsync(), and in fact not
> even read() (because the DB might be in a partially-updated inconsistent
> state) before process A has finalised the transaction and called fsync().

WAL mode allows reads while a writer is in a transaction. The readers will 
‘see' the state of the database prior to the start of the transaction, until it 
commits. This is a key reason to use WAL if you have multiple connections to 
your database — much greater availability.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Checking differences in tables

[James K. Lowden]

On Tue, 12 Feb 2019 15:05:29 +
Jose Isaias Cabrera  wrote:

> >SELECT * From t WHERE datestamp = "20190208"
> 
> Ok, Simon, I'll bite; :-) Imagine this table:
> 
> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)

That's better.  
 
> how would I find the differences in the fields based on the different
> idate?

select ...
from t as now join t as then
on now.idate = '20190208' 
and then.idate = '20190207' -- note single quotes 
and ... -- other things that match
where ... --- things that don't match

Can't be more specific than that when the question is "find the
differences". 

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Checking differences in tables

[Jose Isaias Cabrera]

Simon Slavin, on Saturday, February 9, 2019 11:02 AM, wrote...
>On 9 Feb 2019, at 3:49pm, Jose Isaias Cabrera wrote:
>
>> Imagine this scenario:I have this table,
>>
>> t (a PRIMARY KEY, b, c, d, e)
>>
>> that contains yesterday's data. Today, I rename that table to,
>>
>> t_20190208 (a PRIMARY KEY, b, c, d, e).
>
>That's not how you use tables.  Your software shouldn't be automatically
> creating or dropping tables during the life of your program.
>
>Have one table for your data for all days.
>Add an extra column to the table for the datestamp.
>Then to select all data for a particular day you do
>
>SELECT * From t WHERE datestamp = "20190208"

Ok, Simon, I'll bite; :-) Imagine this table:

t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)

how would I find the differences in the fields based on the different idate?  a 
will never change because it is the only unique unchangeable input from date to 
date.  But, everything else can change from date to date.  Thanks.

josé




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] lost errors on writeback/fsync/close - does SQLite need to be fixed similary to PostgreSQL?

[Richard Hipp]

On 2/10/19, Edwin Török  wrote:
> I was wondering what changes SQLite3 would need in light of the fsync
> problems discovered by the PostgreSQL community

We've been looking into this, even before your message arrived.

When fsync() returns non-zero, that is basically the operating system
telling you that it has already corrupted the database file.  Recovery
is not feasible in the general case.  An SQLITE_IOERR_FSYNC error is
nearly the same thing as SQLITE_CORRUPT.

Though complete recovery is not possible in general, we are looking at
proposals for making recovery more likely and making permanent
database damage less likely.

Do not panic.  Fsync() failures are exceedingly rare, especially if
you avoid the use of network filesystems.  Though we are working to
mitigate the adverse effects of fsync() failures, we believe you are
much more likely to get corruption due to hardware failures, cosmic
rays, or rogue applications scribbling into your database files using
fopen().

BACKGROUND

For readers who are new to this problem, the following is a quick summary:

All modern operating systems cache disk content in kernel memory.
When you do a read(), the OS first checks to see if it already has the
content you are trying to read in cache, and if it does it merely does
something like an memcpy() to make that content available to your
process.  If the content is not already in cache, it is loaded into
cache and then the same memcpy()-like operation occurs.  When you
write(), the new content overwrites the in-cache copy of the content
and the cache line is marked "dirty", but the new content is not
normally written back to persistent storage, at least not immediately.
The OS might chose to write back content to storage at any time, but
the application does not know when that might be.  The fsync() call is
suppose to force content to be written back to persistent storage.
When fsync() returns successfully, that means that there are no
"dirty" cache lines for the file in question and whatever content is
currently in cache exactly matches the content that is on persistent
storage.

So when fsync() works, that means that the cache and the disk agree
and are consistent.  But when fsync() fails, the situation is muddled.
The state of the system after a failed fsync() depends on the
operating system, the specific version of the operating system, and
the filesystem on which the file resides.

When an fsync() fails, the system might purge all cache lines
associated with that file.  This causes some or all of the write()
requests since the previous fsync() to roll back.  This is
non-compliant behavior according to POSIX, but we are told that this
is what the XFS and BTRFS filesystems on Linux do.  This behavior is
non-compliant, but it does seem to make the most sense, and if all
systems worked this way we could perhaps engineer SQLite so that
recovery would always be possible.  Unfortunately, not all systems
work this way.

The POSIX-compliant behavior after an fsync() fails is that the cache
lines are all marked "clean" and are retained.  The EXT4 filesystem on
Linux works this way, I am told.  This means that subsequent reads
will see the new content for a while, but when the cache lines are
eventually ejected due to memory pressure, or when the system reboots,
some of the content will silently revert to its old value.  Some of
the cache lines might have successfully been written to persistent
storage prior to the fsync() failure, and the content in those lines
will not revert.  But content that did not get back to persistent
storage will eventually revert to its original value.  So, after an
fsync() failure, database file content could change out from under the
application, but the application has no way of knowing if or when this
will happen.  This is an unrecoverable situation.

If process X is writing to a file and encounters an fsync() error and
hence shuts down, this leave the filesystem cache in an inconsistent
state - the content in cache is "clean" but does not reflect what is
actually in persistent storage.  If a second process Y comes along and
starts reading the same file, the Y process has no good way of knowing
that the file is in an inconsistent state.  The file content might
change out from under process Y as cache lines are ejected, and
process Y is never informed of this.  The fsync() error was delivered
to process X, so process Y has no way of knowing that anything ever
went amiss.

Direct I/O has been proposed as a way to work around these problems.
But direct I/O has substantial performance penalties.  Also, according
to the man-page: "The O_DIRECT flag may impose alignment restrictions
on the length and address of user-space buffers and the file offset of
I/Os. Linux alignment restrictions vary by filesystem and kernel
version [T]here is currently no filesystem-independent interrface
for an application to discover these restrictions for a given file or
filesystem."  And that is just for Linux. 

Re: [sqlite] why no unique columns on alter table

[David Raymond]

I think it's because you can just use CREATE UNIQUE INDEX, which conveniently 
also lets you DROP INDEX on it if you want to get rid of the unique constraint 
later.

When you put UNIQUE in the original table description it gets a 
sqlite_autoindex_blah name, and you can't ALTER or DROP it from there as you 
mentioned.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Mark Wagner
Sent: Monday, February 11, 2019 11:16 PM
To: SQLite mailing list
Subject: [sqlite] why no unique columns on alter table

This is mainly for my curiosity.   Is there any particular reason that one
can't add a unique column on an alter table?  With a default value of null
they would all have unique values by default.

Any insight into this would be great.  Perhaps there' something obvious I'm
missing.

-- Mark
___
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] Choosing one value from the group with priority

[Clemens Ladisch]

Ivan Krylov wrote:
> select * from test where id in (1,2) group by id;

Please note that this is not standard SQL; SQLite allows to SELECT
columns that are not mentioned in the GROUP BY clause, but they get
their values from a random row in the group.


> but then I don't get to control which source I'm obtaining the values
> from (when there is more than one). Let's assume for now that I prefer
> to choose values with a particular source_id, but if those are not
> present, I would take what's available.

There is another SQLite extension which allows to select a row in the
group by using MAX() or MIN():

  select *, min(abs(source_id - 3)) from test where id in (1,2) group by id;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem to understand "Persistent Loadable Extensions"

[Richard Hipp]

On 2/11/19, Ricardo Torquato  wrote:
> Hey guys!
>
> According to "Persistent Loadable Extensions” topic on
> https://www.sqlite.org/loadext.html if the initialization procedure returns
> SQLITE_OK_LOAD_PERMANENTLY (256) the extension should persist on the
> database file instead of just belong to the current connection.
>
> So I’ve downloaded the extension-functions.c and before compile that I’ve
> changed the the return of sqlite3_extension_init from 0 to 256. But still,
> my extension just exists on the current connection.
>
> Did I misunderstand the concept? Am I doing something wrong?

New text has been added to https://www.sqlite.org/loadext.html#persist
to hopefully clarify the situation.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Choosing one value from the group with priority

[R Smith]

On 2019/02/12 1:13 PM, Ivan Krylov wrote:

Hi!

I have a table of some values obtained from different sources:

create table test (
id, source_id, value,
primary key(id, source_id)
);
insert into test values
(1, 1, 11), (1, 2, 12), (1, 3, 13),
(2, 1, 21),
(3, 2, 32);

//...//
select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;

which results in the following query plan:

QUERY PLAN
|--CO-ROUTINE 1
|  |--CO-ROUTINE 3
|  |  |--SEARCH TABLE test USING INDEX sqlite_autoindex_test_1 (id=?)
|  |  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
|  `--SCAN SUBQUERY 3
`--SCAN SUBQUERY 1

Is this the most effective way to express my query? Can the more general
problem of assigning a priority to all sources (e.g. "I want records
from source_id 3, otherwise 1, otherwise 2") be solved in a similar way?



The Window function solution is good, but it can be done easily without 
it, resulting in a bit more efficient query.
Here is the query set recreated showing first your previous stated query 
and then an alternate. The semantics are made more legible using a CTE 
here, but can be done without it. The essential difference is that it 
uses a sub-query to obtain the minimum priority as opposed to 
introducing a sub-table sort.


  -- SQLite version 3.25.1  [ Release: 2018-09-18 ]  on SQLitespeed 
version 2.1.1.37.
  -- 



create table test (
id, source_id, value,
primary key(id, source_id)
);

insert into test values
(1, 1, 11), (1, 2, 12), (1, 3, 13),
(2, 1, 21),
(3, 2, 32);

select * from test;

  --  id | source_id | value
  -- --- | - | 
  --  1  | 1 |  11
  --  1  | 2 |  12
  --  1  | 3 |  13
  --  2  | 1 |  21
  --  3  | 2 |  32


-- Original proposed Query:
select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;

  --  id  | value
  --  | 
  --   1  |  13
  --   2  |  21

EXPLAIN QUERY PLAN
select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;

  --  id  | parent | notused | detail
  --  | -- | --- | 


  --   2  |0   |0| CO-ROUTINE 1
  --   6  |2   |0| CO-ROUTINE 3
  --  10  |6   |0| SEARCH TABLE test USING INDEX 
sqlite_autoindex_test_1 (id=?)
  --  42  |6   |0| USE TEMP B-TREE FOR RIGHT PART 
OF ORDER BY

  --  58  |2   |0| SCAN SUBQUERY 3
  --  95  |0   |0| SCAN SUBQUERY 1



-- New proposed Query
WITH AV(id, value, priority) AS (
SELECT id, value, abs(source_id-3)
  FROM test
)
SELECT id, value
  FROM AV AS AV1
 WHERE AV1.id IN (1,2) AND AV1.priority = (SELECT MIN(priority) FROM AV 
AS AV2 WHERE AV2.id = AV1.id)

;

  --  id  | value
  --  | 
  --   1  |  13
  --   2  |  21


EXPLAIN QUERY PLAN
WITH AV(id, value, priority) AS (
SELECT id, value, abs(source_id-3)
  FROM test
)
SELECT id, value
  FROM AV AS AV1
 WHERE AV1.id IN (1,2) AND AV1.priority = (SELECT MIN(priority) FROM AV 
AS AV2 WHERE AV2.id = AV1.id)

;

  --  id  | parent | notused | detail
  --  | -- | --- | 

  --   3  |0   |0| SEARCH TABLE test USING INDEX 
sqlite_autoindex_test_1 (id=?)

  --  21  |0   |0| CORRELATED SCALAR SUBQUERY
  --  27  |   21   |0| SEARCH TABLE test USING INDEX 
sqlite_autoindex_test_1 (id=?)




  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.078s
  -- Total Script Query Time: 0d 00h 00m and 
00.016s

  -- Total Database Rows Changed: 5
  -- Total Virtual-Machine Steps: 600
  -- Last executed Item Index:7
  -- Last Script Error:
  -- 



  -- 2019-02-12 13:48:56.260  |  [Success]Script Success.
  -- 2019-02-12 13:48:56.260  |  [Success]Transaction Rolled back.
  -- ---  DB-Engine

[sqlite] Choosing one value from the group with priority

[Ivan Krylov]

Hi!

I have a table of some values obtained from different sources:

create table test (
id, source_id, value,
primary key(id, source_id)
);
insert into test values
(1, 1, 11), (1, 2, 12), (1, 3, 13),
(2, 1, 21),
(3, 2, 32);

When SELECTing the values, I would like to filter by id and get only
one value per id. This part is misleadingly easy:

select * from test where id in (1,2) group by id;

but then I don't get to control which source I'm obtaining the values
from (when there is more than one). Let's assume for now that I prefer
to choose values with a particular source_id, but if those are not
present, I would take what's available. I managed to invent a query
which would assign a priority to each value using window functions:

select
*, row_number() over win
from test
where
id in (1,2)
window win as (
partition by id
order by abs(source_id-3)
);

By subtracting a different value in the ORDER BY ABS(...) clause, I can
force a value with a different source_id to come up first.

And since SQLite doesn't currently allow putting window functions in the
WHERE clause, I'm using a nested query to actually get the values with
the highest priority:

select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;

which results in the following query plan:

QUERY PLAN
|--CO-ROUTINE 1
|  |--CO-ROUTINE 3
|  |  |--SEARCH TABLE test USING INDEX sqlite_autoindex_test_1 (id=?)
|  |  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
|  `--SCAN SUBQUERY 3
`--SCAN SUBQUERY 1

Is this the most effective way to express my query? Can the more general
problem of assigning a priority to all sources (e.g. "I want records
from source_id 3, otherwise 1, otherwise 2") be solved in a similar way?

-- 
Best regards,
Ivan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] lost errors on writeback/fsync/close - does SQLite need to be fixed similary to PostgreSQL?

[Rowan Worth]

On Tue, 12 Feb 2019 at 15:07, Rowan Worth  wrote:

> Huh, fascinating stuff. I'm not an sqlite developer but I can shed light
> on some of your questions.
>
> On Tue, 12 Feb 2019 at 09:54, Edwin Török  wrote:
>
>> A very conservative interpretation of various fsync bugs in various OS
>> kernels [2][5] would suggest that:
>>
>> #1. the list of known OS issues [3] should be updated with an entry
>> similar to: "Linux kernels <4.13 do not guarantee to report errors
>> encountered during writeback on next fsync, therefore data corruption
>> can occur without SQLite knowing about it.", see [4]:
>>
>
> I haven't read all the links yet so forgive me if this is answered there,
> but when you refer to versions <4.13 is that referring to eg. the ext3/ext4
> implementation in those versions, or is it a wider problem which affects
> _all_ filesystems? (I'm particularly interested in whether lustre is
> affected)
>

OK having now watched the talk and read up a bit, I don't know that sqlite
needs any attention. The main problem, it seems, is that postgres relied on
certain semantics from fsync. I've extracted the expectations from the
presentation:

> Expectation #1
>
> If there's an error during the fsync, the next fsync call will try to
flush the data from page cache again.
>
>
> Expectation #2
>
> There may be multiple file descriptors per file, possibly from multiple
processes. If the fsync fails in one process, the failure will be reported
in other processes too.

Dr H and/or Dan Kennedy may want to correct me, but I'm pretty sure sqlite
doesn't hold either of these expectations. For one it's architecture is
distributed rather than client/server, which means it doesn't really have
the luxury of leaving the page cache dirty for extended periods of time.
I'm only familiar with rollback journal (not WAL), but in this mode
sqlite's write cycle goes:

1. Changes are made in RAM, and the original contents of any modified pages
are written to the rollback journal
2. Once the transaction is ready to COMMIT, the rollback journal is fsync()d
3. An EXCLUSIVE lock is acquired on the database (which blocks waiting for
active readers complete)
4. The changes held in RAM are written to the main database (via write())
5. The main database is fsync()d
6. The rollback journal is deleted
7. The EXCLUSIVE lock is relinquished

Further details are here: https://www.sqlite.org/atomiccommit.html

An error from fsync() at at step (2) or step (5) will, I think, fail and
roll back the transaction. I don't know what happens if another error is
encountered during rollback though! It would make sense if
rollback-on-error was implemented using the same hot-journal recovery
mechanism used to recover from power loss, in which case it might be a
separate process which encounters the rollback error and the hot-journal
would remain there until someone finally succeeds in rolling it back. But I
don't know if that's what actually happens.

sqlite's expectations are also spelled out in section 2 of the atomic
commit doc. In particular:

> SQLite assumes that the operating system will buffer writes and that a
write request will return before data has actually been stored in the mass
storage device. SQLite further assumes that write operations will be
reordered by the operating system. For this reason, SQLite does a "flush"
or "fsync" operation at key points. SQLite assumes that the flush or fsync
will not return until all pending write operations for the file that is
being flushed have completed.

It goes on to say that "some fsync primitives are broken" which could cause
"database corruption following power loss." It doesn't currently
acknowledge the possibility of silent corruption, ie. there's an unspoken
expectation here from sqlite that flush/fsync _will_ return an error if the
data did not hit disk.

I haven't quite nailed down the circumstances in which linux <4.13 doesn't
report this. The lwn article (https://lwn.net/Articles/752613/) mentions
one scenario, which I understand as:

1. write(3, ...)  // update page cache for fd 3
2. // ... time passes. the writeback for step (1) fails with an error
3. // due to memory pressure, kernel structures for fd 3 are evicted
(swapped-out?) from RAM
4. fsync(3) // the kernel structures for fd 3 are paged back in, but the
error info is lost, and fsync() succeeds

If this is what we're looking at it seems pretty unlikely to happen in
sqlite's usage. The window for eviction small because fsync() comes
immediately after the write()s. ie. we're likely blocked in fsync() while
the write-backs are in progress, which seems an ideal scenario for the
kernel to pass on errors.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users