Re: [sqlite] SQLITE_MAX_COLUMN should be a runtime knob for Machine Learning

[Amirouche Boubekki]

Le lun. 17 juin 2019 à 14:36, Simon Slavin  a écrit :

> On 17 Jun 2019, at 9:14am, Dominique Devienne  wrote:
>
> > SQLite4's LSM backend is now an extension in SQLite3 called LSM1
> > https://www.sqlite.org/cgi/src/dir?ci=trunk&name=ext/lsm1&type=tree
> >
> > Which has been discussed in this list before.
> > Few people are using it it seems, given the low volume of questions.
>
> This may be because it's not documented.


Also it is difficult to compile.


> As far as I can see, the only way to discover it is to read the source
> code.  Thanks for the pointer.
>

The sqlite4 documentation is accurate.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] nested set tree: how to change order of one node?

[Sam Carleton]

The tree in question contains categories, subcategories and finally image
galleries.  It is common for the user to want to sort all the subordinates
of one level a different way, at times alphanumeric, other times simply to
their liking.  I have been reading through Joe Celko's Trees and
Hierarchies In Sql for Smarties book to refresh the old brain.  He never
talks about how to do any sorting, which tells me it is none trivial, but I
am sure it has been done.

My thought process is to do this:

   1. create a temp table to hold all the descendants of the parent
   2. copy the  subordinates (and descendants) into the temp table one at a
   time in the new order to get the lft/rgt values correct
   3. Once all the children and descendants are copied into the temp table,
   update the lft/rgt values of the source table to get the new order

Is this a valid approach?  Is there a better one?

Main Question: What is the best way to implement the insert in #2?
Ideally, I would like to do that in one SQL statement, do one insert select
that reorders things based on a where condition.  What is throwing me off
is the issue of the descents of what is being sorted, their order should
NOT be changing.  This would seem like a great place for a stored proc or
in the case of SQLite the WITH clause.  Can the WITH clause do what I am
trying to achieve?  An outer select controls the ordering of the
subordinates being sorted and the inner select does the actual gathering of
the data to insert into the temp table?  If this is possible, what might it
look like?

P.S.  In the book Joe talks about creating a view that shows subordinates,
for starts that can be used to sort alphanumeric:

select ChildOID, ChildName, lft, rgt
from EventNodeSubordinates
where ParentOID = '98f13b01-3936-44b0-84a4-56681320fb7d' and
  ChildOID <> '98f13b01-3936-44b0-84a4-56681320fb7d'
order by ChildName

Pax vobiscum,
Sam Carleton
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] CVE-2019-5018 patch

[Riccardo Schirone]

Hi,

What is exactly the patch for CVE-2019-5018? I could not see it anywhere in
the release changelog nor in the Cisco Talos report. It would be useful for
distributions that need to backport the fix.

Thanks,
-- 
Riccardo Schirone
Red Hat -- Product Security
Email: rschi...@redhat.com
PGP-Key ID: CF96E110


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

Re: [sqlite] CVE-2019-5018 patch

[Dan Kennedy]

On 18/6/62 19:48, Riccardo Schirone wrote:

Hi,

What is exactly the patch for CVE-2019-5018? I could not see it anywhere in
the release changelog nor in the Cisco Talos report. It would be useful for
distributions that need to backport the fix.


It was this one:

https://sqlite.org/src/info/1e16d3e8fc60d39c

Dan.

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

Re: [sqlite] Please explain multi-access behaviour to me

[Thomas Kurz]

This has been a very informative and helpful discussion. Thank you.

So have I understood correctly, that in an application, this kind of 
SQLITE_BUSY handling is sufficient:

BEGIN
UPDATE #1
SELECT #2
UPDATE #3
COMMIT <- check for busy here and retry only the commit on failure

And second, what is the best place for busy-handling when only reading? Examle:

BEGIN < sufficient here?
SELECT #1
SELECT #2
SELECT #3
ROLLBACK

Can I assume that if the "begin" succeeds, I have the right to perform all 
following selects without having to fear getting an SQLITE_BUSY? Or do I have 
to repeat the whole block?

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

Re: [sqlite] nested set tree: how to change order of one node?

[ingo]

Sam,

Can't answer your question directly, maybe the closure extension is
something for you. To read a bit about it:
http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/

ingo

On 18-6-2019 14:19, Sam Carleton wrote:
> My thought process is to do this:
> 
>1. create a temp table to hold all the descendants of the parent
>2. copy the  subordinates (and descendants) into the temp table one at a
>time in the new order to get the lft/rgt values correct
>3. Once all the children and descendants are copied into the temp table,
>update the lft/rgt values of the source table to get the new order
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CVE-2019-5018 patch

[Riccardo Schirone]

On 06/18, Dan Kennedy wrote:
> 
> On 18/6/62 19:48, Riccardo Schirone wrote:
> > Hi,
> > 
> > What is exactly the patch for CVE-2019-5018? I could not see it anywhere in
> > the release changelog nor in the Cisco Talos report. It would be useful for
> > distributions that need to backport the fix.
> 
> It was this one:
> 
> https://sqlite.org/src/info/1e16d3e8fc60d39c
> 

Thanks for your prompt reply.
-- 
Riccardo Schirone
Red Hat -- Product Security
Email: rschi...@redhat.com
PGP-Key ID: CF96E110


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

Re: [sqlite] Please explain multi-access behaviour to me

[Keith Medcalf]

On Tuesday, 18 June, 2019 07:12, Thomas Kurz  wrote:

>This has been a very informative and helpful discussion. Thank you.

>So have I understood correctly, that in an application, this kind of
>SQLITE_BUSY handling is sufficient:

>BEGIN
>UPDATE #1
>SELECT #2
>UPDATE #3
>COMMIT <- check for busy here and retry only the commit on failure

UPDATE #1 could fail with SQLITE_BUSY -- it may be unable to obtain a RESERVED 
lock.  Assuming that the UPDATE #1 completes then the transaction holds a 
RESERVED lock and the rest of the statements will not get an SQLITE_BUSY.  The 
COMMIT needs to obtain an EXCLUSIVE lock to write the changes, so it may return 
SQLITE_BUSY if there are other connections having SHARED locks which might need 
to clear before the transaction can commit.

Since you know that the transaction will require a RESERVED lock to complete, 
you should use BEGIN IMMEDIATE to acquire that lock immediately.  Then if the 
BEGIN IMMEDIATE succeeds you only need to worry about lock escalation again 
when you commit (and not on the interim statements which do not need to acquire 
any additional locks).


>And second, what is the best place for busy-handling when only
>reading? Examle:

>BEGIN < sufficient here?
>SELECT #1
>SELECT #2
>SELECT #3
>ROLLBACK
>
>Can I assume that if the "begin" succeeds, I have the right to
>perform all following selects without having to fear getting an
>SQLITE_BUSY? Or do I have to repeat the whole block?

Again, you need to check for SQLITE_BUSY on SELECT #1 since that is where the 
SHARED lock is obtained.  Once SELECT #1 has completed the transaction holds a 
shared lock and the rest of the statements will proceed without requiring 
additional locks.

---
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] Please explain multi-access behaviour to me

[Keith Medcalf]

"UPDATE #1 completes" and "SELECT #1 completes" should be read as the first 
sqlite3_step of that statement returns some sort of success indicator ...

Note that the sequence:

BEGIN;
SELECT #1;
UPDATE #1

may result in a deadlock in that although SELECT #1 may be able to obtain a 
SHARED lock, UPDATE #1 may not be able to upgrade to a RESERVED lock.  The only 
remedy is to abort the transaction entirely and start over again from the very 
beginning (this is because the principle that all required locks should be 
acquired at once and in the same order was violated).  Using BEGIN IMMEDIATE 
will eliminate this possibility since it will (correctly) acquire all required 
locks at once ... (though you still need to get an EXCLUSIVE lock for the 
commit, you may still have to wait for SHARED locks to clear before the 
transaction can commit, although you may starve if you designed your 
application badly, you cannot deadlock).  I suppose you could use BEGING 
EXCLUSIVE to make sure that you have the required exclusive lock as well, but 
this will lock all other readers out of the database and defeats concurrency.

Note that in WAL mode the deadlock condition may not be detected until commit 
time since the transaction (not being started with BEGIN IMMEDIATE) may not be 
the "top" snapshot and therefore cannot commit (ever).

Also note that it is possible for ANY STATEMENT which updates the database, 
even if a RESERVED lock is held by the transaction, to return SQLITE_BUSY.  For 
example an EXCLUSIVE lock is required to spill pages to disk from the cache and 
this may occur before COMMIT time.

So generally, you need to always check the return codes and do the needful ... 
(and what is needful depends on what you were doing and what the return code is 
and why you got that return code -- and the application programmer knows that 
with certainty, or at least should).

---
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 Keith Medcalf
>Sent: Tuesday, 18 June, 2019 08:25
>To: SQLite mailing list
>Subject: Re: [sqlite] Please explain multi-access behaviour to me
>
>
>On Tuesday, 18 June, 2019 07:12, Thomas Kurz 
>wrote:
>
>>This has been a very informative and helpful discussion. Thank you.
>
>>So have I understood correctly, that in an application, this kind of
>>SQLITE_BUSY handling is sufficient:
>
>>BEGIN
>>UPDATE #1
>>SELECT #2
>>UPDATE #3
>>COMMIT <- check for busy here and retry only the commit on
>failure
>
>UPDATE #1 could fail with SQLITE_BUSY -- it may be unable to obtain a
>RESERVED lock.  Assuming that the UPDATE #1 completes then the
>transaction holds a RESERVED lock and the rest of the statements will
>not get an SQLITE_BUSY.  The COMMIT needs to obtain an EXCLUSIVE lock
>to write the changes, so it may return SQLITE_BUSY if there are other
>connections having SHARED locks which might need to clear before the
>transaction can commit.
>
>Since you know that the transaction will require a RESERVED lock to
>complete, you should use BEGIN IMMEDIATE to acquire that lock
>immediately.  Then if the BEGIN IMMEDIATE succeeds you only need to
>worry about lock escalation again when you commit (and not on the
>interim statements which do not need to acquire any additional
>locks).
>
>
>>And second, what is the best place for busy-handling when only
>>reading? Examle:
>
>>BEGIN < sufficient here?
>>SELECT #1
>>SELECT #2
>>SELECT #3
>>ROLLBACK
>>
>>Can I assume that if the "begin" succeeds, I have the right to
>>perform all following selects without having to fear getting an
>>SQLITE_BUSY? Or do I have to repeat the whole block?
>
>Again, you need to check for SQLITE_BUSY on SELECT #1 since that is
>where the SHARED lock is obtained.  Once SELECT #1 has completed the
>transaction holds a shared lock and the rest of the statements will
>proceed without requiring additional locks.
>
>---
>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

Re: [sqlite] Please explain multi-access behaviour to me

[Simon Slavin]

On 18 Jun 2019, at 2:11pm, Thomas Kurz  wrote:

> So have I understood correctly, that in an application, this kind of 
> SQLITE_BUSY handling is sufficient:
> 
> BEGIN
> UPDATE #1
> SELECT #2
> UPDATE #3
> COMMIT <- check for busy here and retry only the commit on failure

I believe that the above is correct.  Though you should be checking the return 
code for every SQLite3 call, just because catching an unexpected code there can 
save you days of debugging time.

Actually, you're better off setting a timeout rather than handling retry in 
your own code:



(or use the PRAGMA).  People tend to set tiny timeouts and then waste 
programming time implementing their own retry code.  But what you really want 
is to set a timeout of a minute or more.  After all, which is better, to catch 
a working retry after 45 seconds or to present an error message to the user ?

The exception is when your SQLite operation is blocking the main thread of a 
real-time program.  But that's rare.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding the WITH clause

[James K. Lowden]

On Mon, 17 Jun 2019 20:46:41 -0400
Sam Carleton  wrote:

> It is my view that peace is not something that can be defined with
> some words, rather it is a universal experience.  

"But peace is not merely the absence of this tension, but the
presence of justice."
-- MLK, Letter from the Birmingham Jail

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

Re: [sqlite] Please explain multi-access behaviour to me

[Andy Bennett]

Hi,


Actually, you're better off setting a timeout rather than handling
retry in your own code:




As I understand it, SQLITE_BUSY can occur in two situations: one where 
busy looping will fix it and one where it won't. Using the busy_timeout 
handles the first case automatically and means that if you get 
SQLITE_BUSY in user code it's always the deadlocking variant.
To get around those deadlocks you have to abort the whole transaction 
and retry from the beginning. Keith explains some of these scenarios in 
more detail in a message a few moments before your own so I won't repeat 
them here.


Nevertheless, you need to retry because of concurrency contention, which 
can be handled automatically and reasonably efficiently, *AND* you need 
to retry at the business logic layer due to ACID correctness guarantees 
being provided and enforced.



Transactions can also fail for other reasons (such as a UNIQUE 
constraint violation or a read-modify-write hazard that happens when 
another transaction changes some data that you read and are manipulating 
and writing back) and you need to be able to recover from these 
scenarios.


Having said that, SQLITE is nice because its single writer model means 
that read-modify-write hazards are less of a problem than other RDBMSes, 
provided transactions that intend to write use BEGIN IMMEDIATE.




--
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
http://www.gonumber.com/andyjpb
0x7EBA75FF
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please explain multi-access behaviour to me

[Jose Isaias Cabrera]

Andy Bennett, on Tuesday, June 18, 2019 03:53 PM, wrote...
>
> > Actually, you're better off setting a timeout rather than handling
> > retry in your own code:
> >
> > 
>
> As I understand it, SQLITE_BUSY can occur in two situations: one where
> busy looping will fix it and one where it won't. Using the busy_timeout
> handles the first case automatically and means that if you get
> SQLITE_BUSY in user code it's always the deadlocking variant.
> To get around those deadlocks you have to abort the whole transaction
> and retry from the beginning. Keith explains some of these scenarios in
> more detail in a message a few moments before your own so I won't repeat
> them here.

This is very true.  It would be easier to have just two writers, and one hard 
drive. But in my case, I have 8 writers, and a shared network drive.  So, I had 
to go even farther than that, which was to have the software check if someone 
was writing to the DB by creating a small hidden, and it it, write the name of 
the PM, so when a PM wanted to write, The program would prompt the PM, "Please 
be patien, Joe is writing to the DB."  When the writer received an OK from the 
SQLite writing connection, I would delete that file, and the other writer would 
check if someone was writing to the DB, if there was no hidden file, then, a 
new one would be added with the second writer... etc. etc.  This took me a 
while to figure out with live data and in the end, everyone is happy and love 
it. Just my 98 Dominican cents. ;-) (US$0.01 = DO$0.49)

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

[sqlite] Is this a bug?

[Roman Fleysher]

Dear SQLiters,


I can not figure out what I am doing wrong. In testing, I simplified to the 
following:

CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);

SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY 
refVolume;

refVolume   CAST(10*max(cosSquared) AS INT)
--  ---
2   9
3   9
4   9
5   9
.
31  9
32  9
33  9

That is, we see that for refVolumes between 2 and 33, the value of the CAST() 
is always 9. Thus, I expect the following statement to output the same list of 
refVolumes. But it does not:

SELECT DISTINCT refVolume FROM cosSquared
 WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM 
cosSquared);

refVolume
--
2

What am I doing wrong? I am using version 3.16.

Thank you for your help,

Roman

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

Re: [sqlite] Is this a bug?

[Barry]

Your nested select statement wants to return many rows. Technically I think
this is illegal SQL (?), but it looks like SQLite is being lax about this
as usual and doing what it normally does in this sort of situation: picks a
row at random from the inner select.

Your statement is more or less equivalent to:
SELECT DISTINCT refVolume FROM cosSquered WHERE (refVolumn
CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared LIMIT 1)

The statement that will output the same same list of refVolumes is:
SELECT DISTINCT refVolume FROM cosSquared WHERE CAST(10 * cosSquared AS
INT) = 9

On Tue, 18 Jun 2019 at 16:44, Roman Fleysher 
wrote:

> Dear SQLiters,
>
>
> I can not figure out what I am doing wrong. In testing, I simplified to
> the following:
>
> CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);
>
> SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY
> refVolume;
>
> refVolume   CAST(10*max(cosSquared) AS INT)
> --  ---
> 2   9
> 3   9
> 4   9
> 5   9
> .
> 31  9
> 32  9
> 33  9
>
> That is, we see that for refVolumes between 2 and 33, the value of the
> CAST() is always 9. Thus, I expect the following statement to output the
> same list of refVolumes. But it does not:
>
> SELECT DISTINCT refVolume FROM cosSquared
>  WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM
> cosSquared);
>
> refVolume
> --
> 2
>
> What am I doing wrong? I am using version 3.16.
>
> Thank you for your help,
>
> Roman
>
> ___
> 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] Is this a bug?

[Roman Fleysher]

Oh, Thank you Barry.

I am glad it is not a bug. Bug is in my head.

Thank you,

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Barry [smith.bar...@gmail.com]
Sent: Tuesday, June 18, 2019 7:59 PM
To: SQLite mailing list
Subject: Re: [sqlite] Is this a bug?

Your nested select statement wants to return many rows. Technically I think
this is illegal SQL (?), but it looks like SQLite is being lax about this
as usual and doing what it normally does in this sort of situation: picks a
row at random from the inner select.

Your statement is more or less equivalent to:
SELECT DISTINCT refVolume FROM cosSquered WHERE (refVolumn
CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared LIMIT 1)

The statement that will output the same same list of refVolumes is:
SELECT DISTINCT refVolume FROM cosSquared WHERE CAST(10 * cosSquared AS
INT) = 9

On Tue, 18 Jun 2019 at 16:44, Roman Fleysher 
wrote:

> Dear SQLiters,
>
>
> I can not figure out what I am doing wrong. In testing, I simplified to
> the following:
>
> CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);
>
> SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY
> refVolume;
>
> refVolume   CAST(10*max(cosSquared) AS INT)
> --  ---
> 2   9
> 3   9
> 4   9
> 5   9
> .
> 31  9
> 32  9
> 33  9
>
> That is, we see that for refVolumes between 2 and 33, the value of the
> CAST() is always 9. Thus, I expect the following statement to output the
> same list of refVolumes. But it does not:
>
> SELECT DISTINCT refVolume FROM cosSquared
>  WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM
> cosSquared);
>
> refVolume
> --
> 2
>
> What am I doing wrong? I am using version 3.16.
>
> Thank you for your help,
>
> Roman
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228&sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3D&reserved=0
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228&sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3D&reserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users