Re: [sqlite] Update SQLITE

[Keith Medcalf]

On Sunday, 5 January, 2020 16:39, gideo...@lutzvillevineyards.com wrote:

>I have the following SQLITE query :
>
>SELECT   BlokkeklaarAliasnaam,
  BlokkeklaarKultivar,
  sum(BlokkeklaarSkatting)
>FROM Blokkeklaar
>GROUP BY BlokkeklaarAliasnaam,
  BlokkeklaarKultivar;
>
>I cannot figure out how to update a column (i.e.
>BlokkeklaarSkattingKultAliasnaam) with the value of
>sum(BlokkeklaarSkatting)
>in the above statement.

>This refers to a single table.

First of all, you cannot perform updates with a SELECT statement -- you need an 
UPDATE statement for that ;)

UPDATE Blokkeklaar as O
   SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
 FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS 
O.BlokkeklaarAliasnaam
  AND BlokkeklaarKultivar IS 
O.BlokkeklaarKultivar);

You may use triggers so that Blokkeklaar.BlokkeklaarSkattingKultAliasnaam is 
always kept up-to-date (assuming that you start from an empty table or run the 
above update once to make it current before making further updates, after which 
you never need to run the above update again):

CREATE TRIGGER ins_Blokkeklaar AFTER INSERT ON Blokkeklaar BEGIN
UPDATE Blokkeklaar AS O
   SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
 FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS 
O.BlokkeklaarAliasnaam
  AND BlokkeklaarKultivar IS 
O.BlokkeklaarKultivar)
 WHERE BlokkeklaarAliasnaam IS NEW.BlokkeklaarAliasnaam
   AND BlokkeklaarKultivar IS NEW.BlokkeklaarKultivar;
END;

CREATE TRIGGER del_Blokkeklaar AFTER DELETE ON Blokkeklaar BEGIN
UPDATE Blokkeklaar AS O
   SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
 FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS 
O.BlokkeklaarAliasnaam
  AND BlokkeklaarKultivar IS 
O.BlokkeklaarKultivar)
 WHERE BlokkeklaarAliasnaam IS OLD.BlokkeklaarAliasnaam
   AND BlokkeklaarKultivar IS OLD.BlokkeklaarKultivar;
END;

CREATE TRIGGER upd_Blokkeklaar AFTER UPDATE OF BlokkeklaarSkatting, 
BlokkeklaarAliasnaam, BlokkeklaarKultivar ON Blokkeklaar BEGIN
UPDATE Blokkeklaar AS O
   SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
 FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS 
O.BlokkeklaarAliasnaam
  AND BlokkeklaarKultivar IS 
O.BlokkeklaarKultivar)
 WHERE BlokkeklaarAliasnaam IS OLD.BlokkeklaarAliasnaam
   AND BlokkeklaarKultivar IS OLD.BlokkeklaarKultivar
   AND (OLD.BlokkeklaarAliasnaam IS NOT NEW.BlokkeklaarAliasnaam
OR OLD.BlokkeklaarKultivar IS NOT NEW.BlokkeklaarKultivar);
UPDATE Blokkeklaar AS O
   SET BlokkeklaarSkattingKultAliasnaam = (SELECT sum(BlokkeklaarSkatting)
 FROM Blokkeklaar
WHERE BlokkeklaarAliasnaam IS 
O.BlokkeklaarAliasnaam
  AND BlokkeklaarKultivar IS 
O.BlokkeklaarKultivar)
 WHERE BlokkeklaarAliasnaam IS NEW.BlokkeklaarAliasnaam
   AND BlokkeklaarKultivar IS NEW.BlokkeklaarKultivar;
END;

You will, of course, need an index on Blokkeklaar (BlokkeklaarAliasnaam, 
BlokkeklaarKultivar) unless you cherish slowness due to table scans -- it will 
be even faster if this is a covering index such as Blokkeklaar 
(BlokkeklaarAliasnaam, BlokkeklaarKultivar, BlokkeklaarSkatting).

And you may, of course, use == and <> in place of IS and IS NOT if the 
correponding columns are constrained NOT NULL in the table definition.  
Otherwise, the above treats NULL as a distinct value.  If you want NULL to be 
indistinct you will need to make some changes.

Also, note that the sum(...) aggregate returns NULL if there is nothing to sum 
or all the values to sum are NULL.  If you want that to be 0.0 instead, then 
change all uses of sum(...) to total(...)

-- 
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] Update SQLITE

[gideon.e]

I have the following SQLITE query :

 

SELECT

   BlokkeklaarAliasnaam,

   BlokkeklaarKultivar,

   sum(BlokkeklaarSkatting)

FROM

   Blokkeklaar

GROUP BY

   BlokkeklaarAliasnaam,

   BlokkeklaarKultivar;

 

 

I cannot figure out how to update a column (i.e.
BlokkeklaarSkattingKultAliasnaam) with the value of sum(BlokkeklaarSkatting)
in the above statement.

This refers to a single table.

 

Regards

 

 

 

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

Re: [sqlite] UPDATE SET using column-name-list Syntax error

[javaj1...@elxala.com]

Richard Hipp wrote:

On 12/10/17, javaj1...@elxala.com  wrote:

According to the documentation UPDATE SET admits column-name-list as
argument
but I cannot get it working. Here some tries

DROP TABLE IF EXISTS test; CREATE TABLE test(a,b,c);

-- ok
UPDATE test SET a = "vala", b = "valb" ;
UPDATE test SET a = (SELECT "vala"), b = (SELECT "valb") ;

-- not ok
UPDATE test SET (a, b) = "vala", "valb" ;
Error: near "(": syntax error
UPDATE test SET (a, b) = (SELECT "vala", "valb") ;
Error: near "(": syntax error

What am I doing wrong ? or is this syntax really supported ?

It is supported beginning with SQLite 3.15.0 (2016-10-14).  What
version of SQLite are you running?
Certantly! my application uses sqlite 3.17 2017-02-08 which is pretty 
new and support it
but I did the check in the command line actually with an older version 
(3.8.8.1 2015-01-20)

I will update this executable as well to avoid such wrong checks in future.

thank you!

PD:

-- not ok anyway (bad syntax from me)

UPDATE test SET (a, b) = "vala", "valb" ;
Error: 2 columns assigned 1 values

-- both ok
UPDATE test SET (a, b) = ("vala", "valb") ;
UPDATE test SET (a, b) = (SELECT "vala", "valb") ;






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

Re: [sqlite] UPDATE SET using column-name-list Syntax error

[x]

>UPDATE test SET (a, b) = "vala", "valb" ;

Should that not be (a, b) = (‘vala’, ‘valb’);


>UPDATE test SET (a, b) = (SELECT "vala", "valb") ;

Should that not be (a, b) = ((SELECT ‘vala’), ‘valb’);


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

Re: [sqlite] UPDATE SET using column-name-list Syntax error

[Richard Hipp]

On 12/10/17, javaj1...@elxala.com  wrote:
>
> According to the documentation UPDATE SET admits column-name-list as
> argument
> but I cannot get it working. Here some tries
>
> DROP TABLE IF EXISTS test; CREATE TABLE test(a,b,c);
>
> -- ok
> UPDATE test SET a = "vala", b = "valb" ;
> UPDATE test SET a = (SELECT "vala"), b = (SELECT "valb") ;
>
> -- not ok
> UPDATE test SET (a, b) = "vala", "valb" ;
> Error: near "(": syntax error
> UPDATE test SET (a, b) = (SELECT "vala", "valb") ;
> Error: near "(": syntax error
>
> What am I doing wrong ? or is this syntax really supported ?

It is supported beginning with SQLite 3.15.0 (2016-10-14).  What
version of SQLite are you running?
-- 
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

[sqlite] UPDATE SET using column-name-list Syntax error

[javaj1...@elxala.com]

Hi,

According to the documentation UPDATE SET admits column-name-list as 
argument

but I cannot get it working. Here some tries

DROP TABLE IF EXISTS test; CREATE TABLE test(a,b,c);

-- ok
UPDATE test SET a = "vala", b = "valb" ;
UPDATE test SET a = (SELECT "vala"), b = (SELECT "valb") ;

-- not ok
UPDATE test SET (a, b) = "vala", "valb" ;
Error: near "(": syntax error
UPDATE test SET (a, b) = (SELECT "vala", "valb") ;
Error: near "(": syntax error

What am I doing wrong ? or is this syntax really supported ?

I am specially interested in the syntax using SELECT since it could 
optimize the update

reducing the number of needed SELECT's

thanks,
Alejandro

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

Re: [sqlite] Update the SQLite package in Tcl

[Keith Medcalf]

On Sunday, 19 November, 2017 20:46, Joseph R. Justice  
wrote:

>On Sun, Nov 19, 2017 at 4:49 PM, Keith Medcalf 
>wrote:

>> On 19 November, 2017 10:50, Joseph R. Justice 
>asked:


>> The same applies for SQLite and anything else that has a stable
>> interface.

>Fair enough, but then we're back to the problem the original poster
>raised, since that's what they're claiming to be doing, if I 
>understand what they wrote correctly.  And, just dropping in a newer 
>DLL with the same name doesn't seem to be working for them.

Just because the name of a file is the same does not mean that the *contents* 
of the file are the same.  For example, you can rename "notepad.exe" to 
"winword.exe".  Copying your renamed "notepad.exe" (which is now called 
"winword.exe") over top of the distribution version of winword.exe in the 
office directory does not magically turn "notepad" into "word".  It just means 
that now when you try and run "Word" that "notepad" runs instead.

Similarly, copying a DLL containing the "standard" sqlite3.dll code over top of 
the dll containing the extension for TCL does not magically convert the 
"standard" sqlite3.dll into a dll containing the Tcl extension.

Have you looked in the teapot to see if there is a teabag containing a later 
version of the TEA extension (the default is only a few versions behind)?

---
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] Update the SQLite package in Tcl

[Joseph R. Justice]

On Sat, Nov 18, 2017 at 1:06 PM, Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:

I have installed Tcl/Tk from a couple of places on the web (activetcl
> and magicsplat), and I find that neither of them has the latest version of
> sqlite3 as the standard sqlite3 package.  ActiveTcl seems to be linked to
> sqlite 3.13 while magicsplat's version comes with sqlite 3.20.
>
> What do I need to do to get them both up to sqlite 3.21?  I see that
> their installations include a lib folder with a sqlite dll in it.  Is it
> sufficient simply to replace that dll with the sqlite 3.21 dll?  I tried
> that but it gave me error messages ("invalid argument, couldn't load dll",
> etc.), so I am guessing there is more to it than that.
>
> Better yet, is there a version of tcl/tk that is considered "official"
> that is kept updated with the latest versions of all these packages so that
> I don't have to wonder what comes packaged with which version of tcl I
> download from where?
>

Have you considered asking the support communities for the ActiveTcl and
MagicSplat binary distributions of Tcl how to update the version of SQLite
each binary distribution uses?  It seems to me this would be a question
right up their alley.

I wish you well with this task.



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

Re: [sqlite] Update the SQLite package in Tcl

[Joseph R. Justice]

On Sun, Nov 19, 2017 at 4:49 PM, Keith Medcalf  wrote:

> On 19 November, 2017 10:50, Joseph R. Justice  asked:
>


> >I'd think that dropping a newer version of SQLite, compiled as a run-
> >time linkable library, into a pre-existing binary compiled to use a
> >previous version of SQLite would require the ABI for SQLite (compiled
> >for use >as a linkable library) to be identical between versions, or
> >at least backwards compatible (such that a newer version of the
> >library can be used with an application compiled for a previous
> >version of the library to provide the same functionality as the
> >previous version, tho not newer functionality first provided by
> >the newer version of the library).
>
> >But is this a reasonable thing to expect on any platform, and
> >specifically on the Windows platform?
>
> >I don't know that it is, at least in this case.
>
> On Windows at least, unless deliberate action has been taken to ensure
> incompatibility (such as changing the definition of an exported function)
> or using "ordinal" rather than "name" exports, then the answer is yes.  You
> can simply "replace" the DLL with a newer version.  This is why programs
> that were written for windows in the mid-90's still continue to work to
> this day.  The Win32 API has not changed since it was introduced many many
> many many many years ago, and there is no such thing as "Windows Version
> Obsolescence" except where it has been done deliberately and with
> pre-knowledge and malicious intent by the software author.
>
> The same applies for SQLite and anything else that has a stable interface.
>

Fair enough, but then we're back to the problem the original poster raised,
since that's what they're claiming to be doing, if I understand what they
wrote correctly.  And, just dropping in a newer DLL with the same name
doesn't seem to be working for them.



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

Re: [sqlite] Update the SQLite package in Tcl

[Keith Medcalf]

On 19 November, 2017 10:50, Joseph R. Justice  asked:

>I'd think that dropping a newer version of SQLite, compiled as a run-
>time linkable library, into a pre-existing binary compiled to use a
>previous version of SQLite would require the ABI for SQLite (compiled 
>for use >as a linkable library) to be identical between versions, or 
>at least backwards compatible (such that a newer version of the 
>library can be used with an application compiled for a previous 
>version of the library to provide the same functionality as the 
>previous version, tho not newer functionality first provided by 
>the newer version of the library).

>But is this a reasonable thing to expect on any platform, and
>specifically on the Windows platform?  

>I don't know that it is, at least in this case.

On Windows at least, unless deliberate action has been taken to ensure 
incompatibility (such as changing the definition of an exported function) or 
using "ordinal" rather than "name" exports, then the answer is yes.  You can 
simply "replace" the DLL with a newer version.  This is why programs that were 
written for windows in the mid-90's still continue to work to this day.  The 
Win32 API has not changed since it was introduced many many many many many 
years ago, and there is no such thing as "Windows Version Obsolescence" except 
where it has been done deliberately and with pre-knowledge and malicious intent 
by the software author.

The same applies for SQLite and anything else that has a stable interface.

---
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] Update the SQLite package in Tcl

[Joseph R. Justice]

On Nov 18, 2017 1:22 PM, "Richard Hipp"  wrote:

On 11/18/17, Balaji Ramanathan  wrote:


> Hi,
>
> I have installed Tcl/Tk from a couple of places on the web (activetcl
> and magicsplat), and I find that neither of them has the latest version of
> sqlite3 as the standard sqlite3 package.  ActiveTcl seems to be linked to
> sqlite 3.13 while magicsplat's version comes with sqlite 3.20.
>
> What do I need to do to get them both up to sqlite 3.21?

On the SQLite download page, you will find both a Pre-release Snapshot
and a "sqlite-autoconf" tarball.  Download either of these.  (I
suggest the Pre-release Snapshot so that you can help us beta test!)

Untar, and cd into the "tea" subdirectory.  Then type:  "./configure;
make install".  That is suppose to install the latest SQLite for TCL.
"TEA" is the "Tcl Extension Architecture".


Reading the original post, plus his response to your first answer, it
appears to me that he actually wants to update a binary package version of
TCL/TK, compiled for the Windows platform, to a newer version of SQLite
without (ideally) recompiling anything, or at least without recompiling the
binary TCL/TK package.

Your instructions are for recompiling TCL/TK with the newer version of
SQLite on a Unix/Linux type platform, AFAICT.

I'd think that dropping a newer version of SQLite, compiled as a run-time
linkable library, into a pre-existing binary compiled to use a previous
version of SQLite would require the ABI for SQLite (compiled for use as a
linkable library) to be identical between versions, or at least backwards
compatible (such that a newer version of the library can be used with an
application compiled for a previous version of the library to provide the
same functionality as the previous version, tho not newer functionality
first provided by the newer version of the library).

But is this a reasonable thing to expect on any platform, and specifically
on the Windows platform?  I don't know that it is, at least in this case.



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

Re: [sqlite] Update the SQLite package in Tcl

[Balaji Ramanathan]

Are there equivalent instructions for Windows?  Thank you.

Balaji Ramanathan

-- Forwarded message --
From: Richard Hipp <d...@sqlite.org>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Cc:
Bcc:
Date: Sat, 18 Nov 2017 13:22:45 -0500
Subject: Re: [sqlite] Update the SQLite package in Tcl
On 11/18/17, Balaji Ramanathan <balaji.ramanat...@gmail.com> wrote:
> Hi,
>
> I have installed Tcl/Tk from a couple of places on the web (activetcl
> and magicsplat), and I find that neither of them has the latest version of
> sqlite3 as the standard sqlite3 package.  ActiveTcl seems to be linked to
> sqlite 3.13 while magicsplat's version comes with sqlite 3.20.
>
> What do I need to do to get them both up to sqlite 3.21?

On the SQLite download page, you will find both a Pre-release Snapshot
and a "sqlite-autoconf" tarball.  Download either of these.  (I
suggest the Pre-release Snapshot so that you can help us beta test!)

Untar, and cd into the "tea" subdirectory.  Then type:  "./configure;
make install".  That is suppose to install the latest SQLite for TCL.
"TEA" is the "Tcl Extension Architecture".


--
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] Update the SQLite package in Tcl

[Richard Hipp]

On 11/18/17, Balaji Ramanathan  wrote:
> Hi,
>
> I have installed Tcl/Tk from a couple of places on the web (activetcl
> and magicsplat), and I find that neither of them has the latest version of
> sqlite3 as the standard sqlite3 package.  ActiveTcl seems to be linked to
> sqlite 3.13 while magicsplat's version comes with sqlite 3.20.
>
> What do I need to do to get them both up to sqlite 3.21?

On the SQLite download page, you will find both a Pre-release Snapshot
and a "sqlite-autoconf" tarball.  Download either of these.  (I
suggest the Pre-release Snapshot so that you can help us beta test!)

Untar, and cd into the "tea" subdirectory.  Then type:  "./configure;
make install".  That is suppose to install the latest SQLite for TCL.
"TEA" is the "Tcl Extension Architecture".


-- 
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

[sqlite] Update the SQLite package in Tcl

[Balaji Ramanathan]

Hi,

I have installed Tcl/Tk from a couple of places on the web (activetcl
and magicsplat), and I find that neither of them has the latest version of
sqlite3 as the standard sqlite3 package.  ActiveTcl seems to be linked to
sqlite 3.13 while magicsplat's version comes with sqlite 3.20.

What do I need to do to get them both up to sqlite 3.21?  I see that
their installations include a lib folder with a sqlite dll in it.  Is it
sufficient simply to replace that dll with the sqlite 3.21 dll?  I tried
that but it gave me error messages ("invalid argument, couldn't load dll",
etc.), so I am guessing there is more to it than that.

Better yet, is there a version of tcl/tk that is considered "official"
that is kept updated with the latest versions of all these packages so that
I don't have to wonder what comes packaged with which version of tcl I
download from where?

Thank you.

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

Re: [sqlite] SQLite Update With CTE

[John McMahon]

Thanks Keith


On 23/08/2017 00:06, Keith Medcalf wrote:


You could also -- if using a version of SQLite3 that supports row values 
(3.15.0 and later) -- do something like this:
SQLite version 3.15.1 2016-11-04 12:08:49, I usually update near the end 
of year unless I see something particularly interesting, eg. CTEs when 
they were introduced.




UPDATE CUSTOMERS
SET (cust1, cust2, street, town, postcode) = (SELECT customer, NULL, 
address, town, postcode
FROM test
   WHERE custnum = 
customers.custnum)
  WHERE custnum in (select custnum from test);

It will get all the updates in a single correlated subquery rather than four ...
Thank you, I just compared the drawings in "lang_update.html" for 
versions 3.10 and 3.15, I had missed that and it is functionality I was 
wishing for.




---
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 John McMahon
Sent: Monday, 21 August, 2017 22:25
To: SQLite Users
Subject: [sqlite] SQLite Update With CTE

Hi

I am rewriting an old Perl script that selectively updates data from
one
table to another using this statement:

UPDATE CUSTOMERS
SET
 cust1= ?,
 cust2= NULL,
 street   = ?,
 town = ?,
 postcode = ?
  WHERE custnum = ?

I am intending to replace it with something like this where 'test' is
the CTE:

UPDATE CUSTOMERS as c
SET
 cust1= (select customer from test where custnum =
c.custnum),
 cust2= NULL,
 street   = (select address  from test where custnum =
c.custnum),
 town = (select town from test where custnum =
c.custnum),
 postcode = (select postcode from test where custnum = c.custnum)
  WHERE custnum = (select custnum from test where custnum =
c.custnum)

My question is, do I need this part of the statement:
  WHERE custnum = (select custnum from test where custnum =
c.custnum)

when I have the other 'where custnum = c.custnum' clauses.

I came across some Web examples that suggest that I might not. I
haven't
tested yet and am a little unsure.

Any guidance would be appreciated.

John

--
Regards
John McMahon
   li...@jspect.fastmail.fm


___
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



--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

Re: [sqlite] SQLite Update With CTE

[Keith Medcalf]

You could also -- if using a version of SQLite3 that supports row values 
(3.15.0 and later) -- do something like this:

UPDATE CUSTOMERS
   SET (cust1, cust2, street, town, postcode) = (SELECT customer, NULL, 
address, town, postcode
   FROM test
  WHERE custnum = 
customers.custnum)
 WHERE custnum in (select custnum from test);

It will get all the updates in a single correlated subquery rather than four ...

---
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 John McMahon
>Sent: Monday, 21 August, 2017 22:25
>To: SQLite Users
>Subject: [sqlite] SQLite Update With CTE
>
>Hi
>
>I am rewriting an old Perl script that selectively updates data from
>one
>table to another using this statement:
>
>UPDATE CUSTOMERS
>SET
> cust1= ?,
> cust2= NULL,
> street   = ?,
> town = ?,
> postcode = ?
>  WHERE custnum = ?
>
>I am intending to replace it with something like this where 'test' is
>the CTE:
>
>UPDATE CUSTOMERS as c
>SET
> cust1= (select customer from test where custnum =
>c.custnum),
> cust2= NULL,
> street   = (select address  from test where custnum =
>c.custnum),
> town = (select town from test where custnum =
>c.custnum),
> postcode = (select postcode from test where custnum = c.custnum)
>  WHERE custnum = (select custnum from test where custnum =
>c.custnum)
>
>My question is, do I need this part of the statement:
>  WHERE custnum = (select custnum from test where custnum =
>c.custnum)
>
>when I have the other 'where custnum = c.custnum' clauses.
>
>I came across some Web examples that suggest that I might not. I
>haven't
>tested yet and am a little unsure.
>
>Any guidance would be appreciated.
>
>John
>
>--
>Regards
>John McMahon
>   li...@jspect.fastmail.fm
>
>
>___
>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] SQLite Update With CTE

[Clemens Ladisch]

John McMahon wrote:
> should be
> UPDATE CUSTOMERS -- remove 'as c'
> SET
> cust1= (select customer from test where custnum = CUSTOMERS.custnum),
>  WHERE custnum IN (SELECT custnum FROM test)

Yes.


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

Re: [sqlite] SQLite Update With CTE

[John McMahon]

On 22/08/2017 16:41, Clemens Ladisch wrote:

John McMahon wrote:

UPDATE CUSTOMERS as c
SET
 cust1= (select customer from test where custnum = c.custnum),
 cust2= NULL,
 street   = (select address  from test where custnum = c.custnum),
 town = (select town from test where custnum = c.custnum),
 postcode = (select postcode from test where custnum = c.custnum)
  WHERE custnum = (select custnum from test where custnum = c.custnum)

My question is, do I need this part of the statement:
  WHERE custnum = (select custnum from test where custnum = c.custnum)

when I have the other 'where custnum = c.custnum' clauses.


The WHERE clause on the UPDATE itself filters the rows that will be
updated.

If you know that "test" contains new values for all customers, you do
not need the WHERE. But if you (might) update only a subset of
customers, you need it.


it doesn't




And that last subquery is not used for assignment, so writing it in
a different form might be clearer:
   WHERE EXISTS (SELECT * FROM test WHERE custnum = c.custnum)
or
   WHERE custnum IN (SELECT custnum FROM test)


And UPDATE does not support AS.


So this
UPDATE CUSTOMERS as c
SET
cust1= (select customer from test where custnum = c.custnum),
cust2= NULL,
...
should be
UPDATE CUSTOMERS -- remove 'as c'
SET
cust1= (select customer from test where custnum = c.custnum),
change to
cust1= (select customer from test where custnum = 
CUSTOMERS.custnum), -- excuse the line wrap


or perhaps
cust1= (select customer from test as t where t.custnum = custnum),

cust2= NULL,
...

and end with
 WHERE custnum IN (SELECT custnum FROM test)

Thank you,
John




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



--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203

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

Re: [sqlite] SQLite Update With CTE

[Clemens Ladisch]

John McMahon wrote:
> UPDATE CUSTOMERS as c
> SET
> cust1= (select customer from test where custnum = c.custnum),
> cust2= NULL,
> street   = (select address  from test where custnum = c.custnum),
> town = (select town from test where custnum = c.custnum),
> postcode = (select postcode from test where custnum = c.custnum)
>  WHERE custnum = (select custnum from test where custnum = c.custnum)
>
> My question is, do I need this part of the statement:
>  WHERE custnum = (select custnum from test where custnum = c.custnum)
>
> when I have the other 'where custnum = c.custnum' clauses.

The WHERE clause on the UPDATE itself filters the rows that will be
updated.

If you know that "test" contains new values for all customers, you do
not need the WHERE. But if you (might) update only a subset of
customers, you need it.


And that last subquery is not used for assignment, so writing it in
a different form might be clearer:
  WHERE EXISTS (SELECT * FROM test WHERE custnum = c.custnum)
or
  WHERE custnum IN (SELECT custnum FROM test)


And UPDATE does not support AS.


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

[sqlite] SQLite Update With CTE

[John McMahon]

Hi

I am rewriting an old Perl script that selectively updates data from one 
table to another using this statement:


UPDATE CUSTOMERS
SET
cust1= ?,
cust2= NULL,
street   = ?,
town = ?,
postcode = ?
 WHERE custnum = ?

I am intending to replace it with something like this where 'test' is 
the CTE:


UPDATE CUSTOMERS as c
SET
cust1= (select customer from test where custnum = c.custnum),
cust2= NULL,
street   = (select address  from test where custnum = c.custnum),
town = (select town from test where custnum = c.custnum),
postcode = (select postcode from test where custnum = c.custnum)
 WHERE custnum = (select custnum from test where custnum = c.custnum)

My question is, do I need this part of the statement:
 WHERE custnum = (select custnum from test where custnum = c.custnum)

when I have the other 'where custnum = c.custnum' clauses.

I came across some Web examples that suggest that I might not. I haven't 
tested yet and am a little unsure.


Any guidance would be appreciated.

John

--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

Re: [sqlite] UPDATE database using parameters

[Peter Da Silva]

On 7/22/17, 1:46 AM, "sqlite-users on behalf of Keith Medcalf" 
 
wrote:
> Not very well.  How do you think "drive by downloads" work?  Javascript in 
> browsers is the most dangerous thing ever invented!

I think the caps-lock key gives it a run for its money.
 

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

Re: [sqlite] UPDATE database using parameters

[Keith Medcalf]

On Friday, 21 July, 2017 20:05, Jens Alfke  said:
>> On Jul 21, 2017, at 1:01 PM, Keith Medcalf 
>> wrote:

>> Just using a web browser has your machine executing god only knows
>> what code generated by god only knows who doing god only knows what
>> to your computer.  Unless you have disabled that, of course.  But
>> that makes the web almost completely unuseable

>Well, JavaScript is sandboxed. And I think most people would take
>issue with the assertion that the web is unusable.

Not very well.  How do you think "drive by downloads" work?  Javascript in 
browsers is the most dangerous thing ever invented!

So, if you have disabled Javascript entirely the Web is *not* almost completely 
unuseable?  You must only visit websites run by the competent, because 90% of 
the web sites visited will be completely blank if Javascript is disabled.  
Google even displays the message "Once you remove all the malware, nothing 
remains".

>> And people who use squirrily quotes should fix their email client …

>“These”?  They're true quotation marks. The straight kind was only
>invented later, for typewriters, just to save a key; they’ve never
>been acceptable in anything but typewritten documents. (The early
>typewriters also didn’t have a “1” because you could use a lowercase
>“l”, or a “0” because you could use an “O”. That got remedied later,
>but they kept the ugly quotes.)

Nonetheless.  If you permit your email client to use squirrily quotes then 
anything that you type in a message cannot be cut and pasted into any "normal" 
software, command prompt, client, or programming language source file without 
having to have the quotes all fixed up.  Same with replacing -- with an em-dash.

And no, the squirrily quotes are typeset quotes.  They are meant for typeset 
publications.  Computer code is not a typesetting application.




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

Re: [sqlite] UPDATE database using parameters

[Jens Alfke]

> On Jul 21, 2017, at 1:01 PM, Keith Medcalf  wrote:
> 
> Just using a web browser has your machine executing god only knows what code 
> generated by god only knows who doing god only knows what to your computer.  
> Unless you have disabled that, of course.  But that makes the web almost 
> completely unuseable

Well, JavaScript is sandboxed. And I think most people would take issue with 
the assertion that the web is unusable.

> And people who use squirrily quotes should fix their email client …

“These”?  They're true quotation marks. The straight kind was only invented 
later, for typewriters, just to save a key; they’ve never been acceptable in 
anything but typewritten documents. (The early typewriters also didn’t have a 
“1” because you could use a lowercase “l”, or a “0” because you could use an 
“O”. That got remedied later, but they kept the ugly quotes.)

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

Re: [sqlite] UPDATE database using parameters

[Keith Medcalf]

On Friday, 21 July, 2017 11:37, Jens Alfke  wrote:

> But anyone writing software that runs in a web server, 
> or that otherwise interacts with untrusted data, has to 
> pay attention to basic security practices. 

> And a fundamental one is that you don’t run code that 
> some untrusted person sent you. 

But most people do this all the time.  Just using a web browser has your 
machine executing god only knows what code generated by god only knows who 
doing god only knows what to your computer.  Unless you have disabled that, of 
course.  But that makes the web almost completely unuseable because it is full 
of stupid sluggard Johhny-cum-lately web designers who pull in third-party crap 
from god only knows where (since only their victims run it, they do not run it 
themselves).  There is a very small subset of people who take action against 
such stupidity.  I used to complain but these people are utter morons with 
abysmal IQs and do not grok the problem -- so there is not much point in that.  
Now I simply refuse to deal with companies that pull such shenanigans and tell 
them why I will never do business with them.

> Anyone who doesn’t hear alarm bells going off when 
> they see code like 
> “UPDATE students set name=$FORM_DATA …” 
> really shouldn’t be writing this sort of software. 

And people who use squirrily quotes should fix their email client ...

> (And it gets worse than this. A major attack on Wordpress 
> and other PHP apps about ten years ago, that caused a lot 
> of damage worldwide, was triggered by some bozo using PHP’s 
> “eval()” function inside an XMLRPC library.)

You don't need to look that far.  I am sure there was at least ten new 
vulnerabilities discovered yesterday that fall into this category.  And just 
for WordPress.

—Jens




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

Re: [sqlite] UPDATE database using parameters

[Jens Alfke]

> On Jul 21, 2017, at 9:48 AM, John McKown  wrote:
> 
> ​And, just to interject a politically incorrect statement, any "programmer"
> who does not use the safer interface is either __extremely__ ignorant,  or
> arrogantly stupid

I wouldn’t put it that harshly, but I agree in spirit. If you’re just learning 
an API and experimenting on your own, it’s understandable that you’ll overlook 
parts of it, or miss best practices, and make mistakes. If that causes crashes 
or problems on your computer, well, that’s part of learning. We all do that.

But anyone writing software that runs in a web server, or that otherwise 
interacts with untrusted data, has to pay attention to basic security 
practices. And a fundamental one is that you don’t run code that some untrusted 
person sent you. SQL statements are code. So incorporating user input into a 
string and then telling a SQL database to evaluate that string is incredibly 
unsafe. 

Anyone who doesn’t hear alarm bells going off when they see code like “UPDATE 
students set name=$FORM_DATA …” really shouldn’t be writing this sort of 
software. (And it gets worse than this. A major attack on Wordpress and other 
PHP apps about ten years ago, that caused a lot of damage worldwide, was 
triggered by some bozo using PHP’s “eval()” function inside an XMLRPC library.)

It’s like: if you want to fool around with gears and pulleys and learn 
mechanics in your home workshop, awesome! But if you’re going to build an 
elevator for my 20-story apartment building, I really want you to have a solid 
knowledge of structural engineering and safety practices, so you don’t do 
something ignorant and get people killed.

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

Re: [sqlite] UPDATE database using parameters

[Jens Alfke]

> On Jul 21, 2017, at 6:45 AM, Peter Da Silva  
> wrote:
> 
> Have a look at prepared statements and statement parameters.

Agreed.

PLEASE, PLEASE, do not try to splice parameters into SQL strings! Any mistakes 
in this code leave you vulnerable to SQL Injection Attacks, which are 
depressingly common and cause great damage in the real world. (Some of the 
examples already posted in this thread contain such mistakes…)

SQLite, like every other SQL database I know of, has APIs that let you safely 
plug runtime parameters into statements without having to do string 
concatenation or quoting or sanitization. The resulting code is safe, more 
readable, and as a bonus it’s faster because the database only has to compile 
and optimize the statement once, no matter how many times it runs.

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

Re: [sqlite] UPDATE database using parameters

[Peter Da Silva]

Using a straight PHP-level substitution like that performs the substitution 
before the SQL parser sees it. It’s also super dangerous if you’re not 
absolutely sure there’s no path for an untrusted agent to inject the name 
you’re selecting on.

https://xkcd.com/327/

On 7/21/17, 3:42 AM, "sqlite-users on behalf of Edmondo Borasio" 
 wrote:

Hi and thanks for your email.

I am using PHP with SQLite on an Apache server.
That statement was taken from some advice I got from a forum.  I wasn't
aware it was MySQL.
I am new to SQLite and this is my first database.

*"Table/column names cannot use parameters.  You have to put it directly*
*into the string:"*

I guess however there must be a way, because for example with SELECT it
works.
The query below works perfectly using variables:

$results = $db->query("SELECT \"$DbItemName\" FROM Anagrafica WHERE
hID=\"$hId\"")->fetchArray();

Cheers

Edmondo


On Fri, 21 Jul 2017 at 12:24, Clemens Ladisch  wrote:

> Edmondo Borasio wrote:
> > $stmt->bind_param($p_name,$bind_value);
>
> This looks like PHP's MySQL driver.  Which DB are you actually using?
>
> Anyway, I recommend you start with the examples from the manual, e.g.,
> :
>
>   $stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
>   $stmt->bindValue(':id', 1, SQLITE3_INTEGER);
>   $result = $stmt->execute();
>
> > I would also need to take the parameter "name" of "SET name" from a
> variable
>
> Table/column names cannot use parameters.  You have to put it directly
> into the string:
>
>   $sql = "UPDATE Anagrafica SET ".$col." = ..."
>
>
> Regards,
> Clemens
> ___
> 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] UPDATE database using parameters

[Peter Da Silva]

Have a look at prepared statements and statement parameters.

Also, if you’re quoting strings you should use single quotes rather than double 
quotes. Double quotes just override the tokenizer, the result is still untyped 
and technically an identifier (the tokenizer actually marks it as TK_ID) until 
the parser disambiguates it. This can lead to unexpected results.

On 7/21/17, 1:34 AM, "sqlite-users on behalf of Edmondo Borasio" 
 wrote:

I am updating a record of a SQLite database as follows:

$db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');

but instead of using name and ID I want to use some variables, $NewItemName
 and $hId.

Entering the variables as they are won't work. Neither using escape
characters like \"$DbItemName\" and \"$hId\".

Any idea of the right syntax pls?
-- 
Dr Edmondo Borasio, MedC BQ Ophth, FEBO
Consultant Ophthalmologist
Specialised in Cornea, Cataract & Laser Refractive Surgery

Head of Corneal and Refractive Surgery Department
Burjeel Hospital
Abu Dhabi, UAE
___
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] UPDATE database using parameters

[Tim Streater]

On 21 Jul 2017, at 11:14, Rowan Worth  wrote:

> On 21 July 2017 at 17:50, Tim Streater  wrote:
>
>>$sql = "UPDATE Movies SET name = '$newname' where id=$newid";
>>
>> Personally I don't like forcing PHP to scan strings so I tend to use
>> concatentation, rewriting the last of these as:
>>
>>$sql = 'UPDATE Movies SET name = '' . $newname . '' where id=' .
>> $newid;
>>
>> but that's just a personal style preference.
>>
>
> Either way, I hope for your sake no one releases a movie called:
> Avengers!'; Drop Table Movies; --

That’s going to be a problem anyway if your strings can legally contain 
single-quotes, never mind little Bobby Tables. Sanitisation is called for.

> As Simon says, better to use something like $sql = 'UPDATE Movies SET name
> = ? WHERE id = ?' and bind your parameters in.

Not disagreeing, but I was operating on the first-things-first principle.


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

Re: [sqlite] UPDATE database using parameters

[Rowan Worth]

On 21 July 2017 at 17:50, Tim Streater  wrote:

>$sql = "UPDATE Movies SET name = '$newname' where id=$newid";
>
> Personally I don't like forcing PHP to scan strings so I tend to use
> concatentation, rewriting the last of these as:
>
>$sql = 'UPDATE Movies SET name = \'' . $newname . '\' where id=' .
> $newid;
>
> but that's just a personal style preference.
>

Either way, I hope for your sake no one releases a movie called:
Avengers!'; Drop Table Movies; --

;)

As Simon says, better to use something like $sql = 'UPDATE Movies SET name
= ? WHERE id = ?' and bind your parameters in.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE database using parameters

[Tim Streater]

On 21 Jul 2017 at 10:04, Edmondo Borasio  wrote: 

> Hi Tim.
>
> It *almost* works..
>
>$DbItemNameTest = "new name";
>$hIdTest = "1";
>
>$db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest .
> '\' WHERE hospital_ID="1"'); //IT WORKS
>   $db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest . '\'
> WHERE hospital_ID=' . $hIdTest); //IT DOESN'T WORK
>
> The second one doesn't work even if I change hIdTest to integer.
>
>$hIdTest = 1;

Well, is hospital_ID stored in your database as a string or as an integer?

If it's an integer, then having $hIdTest as an integer should work. If it's a 
string you would leave $hIdTest as a string and change the sql to:

   ... WHERE hospital_ID=\'' . $hIdTest . '\'');

(I know SQLite can be cleverer than that in doing conversions but I've tried 
never to rely on that so am unfamiliar with it)

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

Re: [sqlite] UPDATE database using parameters

[Tim Streater]

On 21 Jul 2017 at 09:58, Simon Slavin  wrote: 

> On 21 Jul 2017, at 7:33am, Edmondo Borasio  wrote:
>
>> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
>
> Those are the wrong quotes.  SQLite requires single quotes around literal
> strings, and expects no quote around numbers.  And PHP doesn’t care which
> quotes you use as long as they match.

Actually PHP does care. It will not look inside single quoted strings for 
special escaped characters or variables for which to substitute values. It 
*will* do so for double-quoted strings.

So:

   echo 'Hello\n';

will not do the same thing as:

   echo "Hello\n";

The latter will put out a newline at the end of Hello whereas the former will 
put out two characters (\ and n).

Similarly, variable substitution will not happen here:

   $sql = 'UPDATE Movies SET name = $newname where id=$newid';

but will do here:

   $sql = "UPDATE Movies SET name = $newname where id=$newid";

So PHP is happy, but SQLite is not - you'd need:

   $sql = "UPDATE Movies SET name = '$newname' where id=$newid";

Personally I don't like forcing PHP to scan strings so I tend to use 
concatentation, rewriting the last of these as:

   $sql = 'UPDATE Movies SET name = \'' . $newname . '\' where id=' . $newid;

but that's just a personal style preference.

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

Re: [sqlite] UPDATE database using parameters

[Edmondo Borasio]

Hi Tim.

It *almost* works..

$DbItemNameTest = "new name";
$hIdTest = "1";

$db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest .
'\' WHERE hospital_ID="1"'); //IT WORKS
   $db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest . '\'
WHERE hospital_ID=' . $hIdTest); //IT DOESN'T WORK

The second one doesn't work even if I change hIdTest to integer.

$hIdTest = 1;

Any suggestions pls?

Cheers



On 21 July 2017 at 12:35, Tim Streater  wrote:

> On 21 Jul 2017 at 07:33, Edmondo Borasio  wrote:
>
> > I am updating a record of a SQLite database as follows:
> >
> > $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
> >
> > but instead of using name and ID I want to use some variables,
> $NewItemName
> > and $hId.
> >
> > Entering the variables as they are won't work. Neither using escape
> > characters like \"$DbItemName\" and \"$hId\".
>
> It would help if you said what language you are using.
>
> In PHP, one could do this:
>
>   $db->exec ('UPDATE Movies SET name = \'' . $DbItemName . '\' WHERE ID='
> . $hId);
>
> or perhaps:
>
>   $db->exec ("UPDATE Movies SET name = '$DbItemName' WHERE ID=$hId");
>
>
> --
> Cheers  --  Tim
> ___
> 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] UPDATE database using parameters

[Simon Slavin]

On 21 Jul 2017, at 7:33am, Edmondo Borasio  wrote:

> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');

Those are the wrong quotes.  SQLite requires single quotes around literal 
strings, and expects no quote around numbers.  And PHP doesn’t care which 
quotes you use as long as they match.  Correct line should be as follows:

$db->exec("UPDATE Movies SET name = 'new movie' WHERE ID=4");

>   $stmt = $conn->prepare('UPDATE Anagrafica SET name=? WHERE hID=?');

Use named parameters.  See the example on this page instead:



so something like

$stmt = $conn->prepare("UPDATE Anagrafica SET name=:name WHERE 
hID=:hid");
$stmt->bindValue(":name", $DbItemName, SQLITE3_TEXT);
$stmt->bindValue(":hid", $hId, SQLITE3_INTEGER);

If speed is an issue then you can use numbers for the first parameter, but that 
makes your code less easy to read.

Also, make sure you are using the SQLite3 driver, not the standard PDO one.  So 
you should be doing

$conn = new SQLite3("path goes here");

You can get away with using the wrong quotes in PHP in some circumstances, but 
not others, which makes debugging the wrong case extremely difficult.  Better 
to use the right quotes throughout your code even when the wrong ones work.

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

Re: [sqlite] UPDATE database using parameters

[Edmondo Borasio]

Hi and thanks for your email.

I am using PHP with SQLite on an Apache server.
That statement was taken from some advice I got from a forum.  I wasn't
aware it was MySQL.
I am new to SQLite and this is my first database.

*"Table/column names cannot use parameters.  You have to put it directly*
*into the string:"*

I guess however there must be a way, because for example with SELECT it
works.
The query below works perfectly using variables:

$results = $db->query("SELECT \"$DbItemName\" FROM Anagrafica WHERE
hID=\"$hId\"")->fetchArray();

Cheers

Edmondo


On Fri, 21 Jul 2017 at 12:24, Clemens Ladisch  wrote:

> Edmondo Borasio wrote:
> > $stmt->bind_param($p_name,$bind_value);
>
> This looks like PHP's MySQL driver.  Which DB are you actually using?
>
> Anyway, I recommend you start with the examples from the manual, e.g.,
> :
>
>   $stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
>   $stmt->bindValue(':id', 1, SQLITE3_INTEGER);
>   $result = $stmt->execute();
>
> > I would also need to take the parameter "name" of "SET name" from a
> variable
>
> Table/column names cannot use parameters.  You have to put it directly
> into the string:
>
>   $sql = "UPDATE Anagrafica SET ".$col." = ..."
>
>
> Regards,
> Clemens
> ___
> 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] UPDATE database using parameters

[Tim Streater]

On 21 Jul 2017 at 07:33, Edmondo Borasio  wrote: 

> I am updating a record of a SQLite database as follows:
>
> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
>
> but instead of using name and ID I want to use some variables, $NewItemName
> and $hId.
>
> Entering the variables as they are won't work. Neither using escape
> characters like \"$DbItemName\" and \"$hId\".

It would help if you said what language you are using.

In PHP, one could do this:

  $db->exec ('UPDATE Movies SET name = \'' . $DbItemName . '\' WHERE ID=' . 
$hId);

or perhaps:

  $db->exec ("UPDATE Movies SET name = '$DbItemName' WHERE ID=$hId");


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

Re: [sqlite] UPDATE database using parameters

[Clemens Ladisch]

Edmondo Borasio wrote:
> $stmt->bind_param($p_name,$bind_value);

This looks like PHP's MySQL driver.  Which DB are you actually using?

Anyway, I recommend you start with the examples from the manual, e.g.,
:

  $stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
  $stmt->bindValue(':id', 1, SQLITE3_INTEGER);
  $result = $stmt->execute();

> I would also need to take the parameter "name" of "SET name" from a variable

Table/column names cannot use parameters.  You have to put it directly
into the string:

  $sql = "UPDATE Anagrafica SET ".$col." = ..."


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

Re: [sqlite] UPDATE database using parameters

[Edmondo Borasio]

Hi Thanks but it doesn't work.

It's weird because the following works:

$db->exec('UPDATE Anagrafica SET name = "new name" WHERE hID="1"');
//WORKS

But this doesn't:

$p_name ="new name";
$bind_value = "1";
$stmt = $conn->prepare('UPDATE Anagrafica SET name=? WHERE hID=?');
$stmt->bind_param($p_name,$bind_value);
$stmt->exec();

To make things more complex, once this is working, then I would also need
to take the parameter "name" of "SET name" from a variable..

Thanks

On 21 July 2017 at 10:45,  wrote:

> On Fri Jul 21, 2017 at 06:33:55AM +, Edmondo Borasio wrote:
> > I am updating a record of a SQLite database as follows:
> >
> > $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
> >
> > but instead of using name and ID I want to use some variables,
> $NewItemName
> >  and $hId.
> >
> > Entering the variables as they are won't work. Neither using escape
> > characters like \"$DbItemName\" and \"$hId\".
> >
> > Any idea of the right syntax pls?
>
> It appears that you are using something like Perl or Ruby to access
> your database?  If that is the case, then you will find that the
> concept of "placeholders" is what you are looking for.
>
> Typically you specify a "?" (without the quotes) where you want your
> variables to be, and then "bind" the variables to those positions
> either before or during the exec call.
>
> Here is the Perl documentation on that topic:
>
> https://metacpan.org/pod/DBI#Placeholders-and-Bind-Values
>
> I suggest you look up the equivalent in whatever language you are
> using.
>
> --
> Mark Lawrence
> ___
> 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] UPDATE database using parameters

[nomad]

On Fri Jul 21, 2017 at 06:33:55AM +, Edmondo Borasio wrote:
> I am updating a record of a SQLite database as follows:
> 
> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
> 
> but instead of using name and ID I want to use some variables, $NewItemName
>  and $hId.
>
> Entering the variables as they are won't work. Neither using escape
> characters like \"$DbItemName\" and \"$hId\".
> 
> Any idea of the right syntax pls?

It appears that you are using something like Perl or Ruby to access
your database?  If that is the case, then you will find that the
concept of "placeholders" is what you are looking for.

Typically you specify a "?" (without the quotes) where you want your
variables to be, and then "bind" the variables to those positions
either before or during the exec call.

Here is the Perl documentation on that topic:

https://metacpan.org/pod/DBI#Placeholders-and-Bind-Values

I suggest you look up the equivalent in whatever language you are
using.

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

Re: [sqlite] update or replace ...

[R Smith]

On 2016/06/30 2:48 PM, Olivier Mascia wrote:



Le 30 juin 2016 à 13:34, R Smith  a écrit :

MERGE dbo.xxx AS T
  USING dbo.yyy AS S
 ON T.SomeID = S.SomeID
  WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever checking 
is relevant
THEN UPDATE SET T.ValueThatNeedsChanging = NewValue
  WHEN NOT MATCHED
THEN INSERT (SomeID, ValueThatNeedsChanging, AndAllOtherColumns)
VALUES () / SELECT clauses
;

I, hopefully, never used such a piece of coding in my whole life and I know, 
now, why all my human body cells refrained me to ever even approach MSSQL. :)


Ha, ok, but I wasn't really complaining about the way it is done in 
MSSQL, I was merely saying what is needed for it to work, and I see now 
you mean more of a INSERT OR UPDATE the way MySQL does it. Don't 
disregard the above MERGE statement though, it is a very powerful piece 
of SQL and I quite like to use it. Very often (if not mostly) you want 
to update and/or insert only in certain cases (WHERE modified = 1) - 
taking values from a table (rather than just VALUES like I did in my 
examples) and for that this MERGE mechanism is an amazing device. It's 
basically UPSERT with expressions, filtering and sub-queries allowed.


If we /are/ going to go full regalia in SQLite, I'd push for MERGE[1] 
actually rather than just INSERT - UPDATE, but the caveats I mentioned 
earlier remain.


[1] Perhaps borrowing from the PostGres MERGE implementation rather...


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

Re: [sqlite] update or replace ...

[Paul Egli]

On Thu, Jun 30, 2016 at 8:17 AM, Paul Egli wrote:

> No chance for race conditions if data changes between the operations.
>

I should say "if data changes *or a read happens* between the operations.
:-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Paul Egli]

On Thu, Jun 30, 2016 at 7:48 AM, Olivier Mascia wrote:

> > Le 30 juin 2016 à 13:34, R Smith a écrit :
> >
> > MERGE dbo.xxx AS T
> >  USING dbo.yyy AS S
> > ON T.SomeID = S.SomeID
> >  WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever
> checking is relevant
> > THEN UPDATE SET T.ValueThatNeedsChanging = NewValue
> >  WHEN NOT MATCHED
> >THEN INSERT (SomeID, ValueThatNeedsChanging, AndAllOtherColumns)
> > VALUES () / SELECT clauses
> > ;
>
> I, hopefully, never used such a piece of coding in my whole life and I
> know, now, why all my human body cells refrained me to ever even approach
> MSSQL. :)
>
> The - useful - "upsert" which I can make good use of is the simpler one
> you can find in FirebirdSQL, where it is called UPDATE OR INSERT with
> pretty much the same syntax as an insert.
>
> Easy to read, useful and effective for what use cases it is designed for.
>
>
I've used MERGE INTO a lot and sometimes wished that SQLite had it, but i
can understand why it's not a priority. To me, the real value of MERGE is
the atomicity of the operation. You can perform inserts, updates, and
deletes all in one statement--No chance for race conditions if data changes
between the operations.

In SQLite where only a single writer is allowed at a time and the only
isolation level available Serializable, all you need to do is BEGIN
IMMEDIATE and you get the atomicity you need, even if it seems unnatural
(to me) to use two or three different statements to do what is logically
one operation.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Olivier Mascia]

> Le 30 juin 2016 à 14:13, R Smith  a écrit :
> 
> There is no way to catch the outcome in the CLI that I know of, but that 
> doesn't matter. if you are writing scripts for the CLi and not programming 
> it, you can't possibly have SPEED as a paramount consideration, and if that's 
> the case, the simple usual upserts in the CLI would work just dandy (and the 
> order doesn't matter much for lookups, but it is very slightly more efficient 
> to do the Update first as sometimes there's nothing to update - if the update 
> is done second, there's always something to update.

Thanks Ryan,

Speed concern while using it in scripts is indeed not my concern.  I just don't 
like to have to repeat myself twice... to my computer, especially when I have 
to tell things first using either the insert or update syntax then say it again 
using the other syntax. :) And *that* is my concern when having to write a 
piece of script for some quick data fix. :)

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om



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

Re: [sqlite] update or replace ...

[Olivier Mascia]

> Le 30 juin 2016 à 13:34, R Smith  a écrit :
> 
> MERGE dbo.xxx AS T
>  USING dbo.yyy AS S
> ON T.SomeID = S.SomeID
>  WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever checking 
> is relevant
> THEN UPDATE SET T.ValueThatNeedsChanging = NewValue
>  WHEN NOT MATCHED
>THEN INSERT (SomeID, ValueThatNeedsChanging, AndAllOtherColumns)
> VALUES () / SELECT clauses
> ;

I, hopefully, never used such a piece of coding in my whole life and I know, 
now, why all my human body cells refrained me to ever even approach MSSQL. :)

The - useful - "upsert" which I can make good use of is the simpler one you can 
find in FirebirdSQL, where it is called UPDATE OR INSERT with pretty much the 
same syntax as an insert.

Easy to read, useful and effective for what use cases it is designed for.

-- 
Meilleures salutations, Met vriendelijke groeten,  Best Regards,
Olivier Mascia (from mobile device), integral.be/om



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

Re: [sqlite] update or replace ...

[R Smith]

On 2016/06/30 12:00 PM, Olivier Mascia wrote:


Besides, if you ever have to write a scripts for the command line tool, you're 
stuck trying to emulate that semantic. Or can we check by script the outcome of 
the UPDATE?



There is no way to catch the outcome in the CLI that I know of, but that 
doesn't matter. if you are writing scripts for the CLi and not 
programming it, you can't possibly have SPEED as a paramount 
consideration, and if that's the case, the simple usual upserts in the 
CLI would work just dandy (and the order doesn't matter much for 
lookups, but it is very slightly more efficient to do the Update first 
as sometimes there's nothing to update - if the update is done second, 
there's always something to update.


Here is a performance measurement of a typical script that would run 
just fine in the CLi merrily doing Upserts wihout needing to check any 
operation result (i.e the slowest possible way to do it).
It's run in SQLitespeed (but will work perfectly in the CLI too) so I 
can measure the Virtual Machine operations count and compare with the 
same script but without any of the unnecessary steps that makes upserts 
(this second one has half the SQL operations and will even be much more 
efficient than a MERGE statement).


Note the results in VM Steps measured in both cases. To my mind, the 
difference is not worth fretting over if you make scripts for the CLI.
(Note: The time improvement has more to do with caching than efficiency, 
but the VM steps don't lie).



  -- SQLite version 3.9.2  [ Release: 2015-11-02 ]  on SQLitespeed 
version 2.0.2.4.


  -- Script Items: 10 Parameter Count: 0
  -- 2016-06-30 13:47:04.423  |  [Info]   Script Initialized, 
Started executing...
  -- 



CREATE TABLE t(ID INTEGER PRIMARY KEY, A, B);

INSERT INTO t(A, B) VALUES
('John' , 'Smith'),
('Jerry' , 'Jones'),
('James' , 'Smith'),
('Jimmy' , 'Jones');

SELECT * FROM t;


  --  ID  | A   | B
  --  | --- | ---
  --   1  | John| Smith
  --   2  | Jerry   | Jones
  --   3  | James   | Smith
  --   4  | Jimmy   | Jones

UPDATE t SET A='Jenny', B='Smith' WHERE ID=2;  -- UPSERT Type 
1(best)

INSERT OR IGNORE INTO t(ID,A,B) VALUES (2,'Jenny','Smith');


INSERT OR IGNORE INTO t(ID,A,B) VALUES (3,'Jenna','Jones');-- UPSERT Type 2
UPDATE t SET A='Jenna', B='Jones' WHERE ID=2;


UPDATE t SET A='J.K.', B='Johnson' WHERE ID=99; -- UPSERT Type 1 again
INSERT OR IGNORE INTO t(ID,A,B) VALUES (99,'J.K.','Johnson');


SELECT * FROM t;


  --  ID  | A   | B
  --  | --- | -
  --   1  | John| Smith
  --   2  | Jenna   | Jones
  --   3  | James   | Smith
  --   4  | Jimmy   | Jones
  --  99  | J.K.| Johnson

  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.069s
  -- Total Script Query Time: 0d 00h 00m and 
00.037s

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






  -- SQLite version 3.9.2  [ Release: 2015-11-02 ]  on SQLitespeed 
version 2.0.2.4.


  -- Script Items: 7  Parameter Count: 0
  -- 2016-06-30 13:53:38.403  |  [Info]   Script Initialized, 
Started executing...
  -- 



CREATE TABLE t(ID INTEGER PRIMARY KEY, A, B);

INSERT INTO t(A, B) VALUES
('John' , 'Smith'),
('Jerry' , 'Jones'),
('James' , 'Smith'),
('Jimmy' , 'Jones');

SELECT * FROM t;


  --  ID  | A   | B
  --  | --- | ---
  --   1  | John| Smith
  --   2  | Jerry   | Jones
  --   3  | James   | Smith
  --   4  | Jimmy   | Jones

UPDATE t SET A='Jenny', B='Smith' WHERE ID=2;

UPDATE t SET A='Jenna', B='Jones' WHERE ID=2;

INSERT OR IGNORE INTO t(ID,A,B) VALUES (99,'J.K.','Johnson');

SELECT * FROM t;


  --  ID  | A   | B
  --  | --- | -
  --   1  | John| Smith
  --   2  | Jenna   | Jones
  --   3  | James   | Smith
  --   4  | Jimmy   | Jones
  --  99  | J.K.| Johnson

  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.031s
  -- Total Script Query Time: -- --- --- --- 
--.

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

Re: [sqlite] update or replace ...

[R Smith]

On 2016/06/30 10:54 AM, Olivier Mascia wrote:


Does the "INSERT OR REPLACE" syntax not provide what you are looking
for?

Absolutely not. "OR REPLACE", as Ryan and Simon pointed out clearly, is a 
conflict resolution mechanism.

1) The insert would have to trigger a constraint violation to execute the 
replace.
2) The replace *do* delete the row and insert a new one: this would have effect 
on related data (on delete cascade for instance).
3) The typical use case for some form of "UPSERT" (whatever the name and syntax 
it takes) is to generally update a row, assumed to be existing, inserting it at that 
occasion if needed.



This is true, but the UPSERT is a lofty beast. May I remind people of 
the intense bulk of SQL you have to type to make it happen in MSSQL and 
the like? here is a minimal set:


MERGE dbo.xxx AS T
  USING dbo.yyy AS S
 ON T.SomeID = S.SomeID
  WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever 
checking is relevant

THEN UPDATE SET T.ValueThatNeedsChanging = NewValue
  WHEN NOT MATCHED
THEN INSERT (SomeID, ValueThatNeedsChanging, AndAllOtherColumns)
VALUES () / SELECT clauses
;

That's twice the code needed to do the things others suggested (INSERT 
IGNORE followed by UPDATE) - and no, I'm not allergic to typing code, 
but can you imagine the SQL engine changes that would be required to 
parse and understand all that and describe it in the current 
prepared-statement structure? I believe that would take a good ol' bite 
out of the "Lite" claim.


This is not to say it shouldn't be done, nor an excuse for it not to be 
done (I'm merely pointing out why it mightn't have been done yet, as 
some asked that question), but I think the weight of the decision would 
be Gain vs. Effort / Size increase / Processing increase (in parser terms).


If it can help 1% of queries to run a little bit faster during a job 
which is already blisteringly fast - is it really worth the effort?. 
Granted, 1% of all SQlite queries in the World running more efficiently 
would probably amount to measurable global energy saving, but then 1% is 
probably overestimating by a lot.
Someone mentioned having to climb an Index twice - fair enough, but most 
queries do this a zillion times for look-ups, it's hardly a chore, 
that's why we have indices.


Again, no reason not to do it, but I would schedule the implementation 
of MERGE right after adding Checksums to pages. :)



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

Re: [sqlite] update or replace ...

[Simon Slavin]

On 30 Jun 2016, at 10:51am, Olivier Mascia  wrote:

> INSERT OR IGNORE ...
> followed by
> UPDATE ...

Of course.  That's what I actually did when I did it, but I'd forgotten the 
best way.  Your way means you don't have to check any error codes.  Thanks for 
reminding me.

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

Re: [sqlite] update or replace ...

[Paul]

Whatever the case, it will be, on average, 1.5x time less efficient than a 
'I-Hope-Would-Be' UPSERT. It all depends on the probability of INSERT to fail.  
In the scenario, where updates are frequent and inserts are rare, INSERT + 
UPDATE approach takes ~2x performance overhead. In most cases we iterate B-Tree 
2 times: once for INSERT, that will fail and second time for UPDATE.  
In reverse scenario, where inserts are frequent and updates are rare, INSERT + 
UPDATE approach will have almost no overhead. 
But how would you know which scenario would take place, to adjust the order of 
calls? Especially when you write a generic Insert-Or-Update method? So, 
whatever strategy you choose (either UPDATE or INSERT is first), statistically, 
on average, you have 50% of cases where UPDATE would be efficient if performed 
first and 50% of cases where the reverse is true. 

If implemented inside the SQLite engine, overhead can be removed. It is 
possible to descend B-Tree once, either to find the row to UPDATE or a hint 
where to INSERT new row. The only thing that puzzles me is why it wasn't 
implemented years ago. 

30 June 2016, 12:01:56, by "Simon Slavin" < slav...@bigfraud.org >: 

On 30 Jun 2016, at 8:24am, Olivier Mascia < o...@integral.be > wrote:

> Of course writing straight code in C/C++ it's rather simple to emulate 
> situations where you want to update some values in an existing row, creating 
> the row if needed.

The standard way of doing this is to do two commands:

1) INSERT without the REPLACE
2) UPDATE

When step 1 fails because the key values already exist you trap this and 
explicitly ignore it in your code (commented, because you're being nice to 
other programmers).  Then it's always the UPDATE which updates the fields you 
care about.

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] update or replace ...

[Olivier Mascia]

> Le 30 juin 2016 à 11:17, Clemens Ladisch  a écrit :
> 
> A better way is to try the UPDATE first, and if the data was not found
> (number of affected rows is zero), do the INSERT.  Doesn't even need
> a comment.

Indeed. That is precisely what we do. And what is not so easy in complex 
programming where you have thousands of more important things to care for, in 
addition to handle such double statement where they make sense:

>> Of course writing straight code in C/C++ it's rather simple to emulate 
>> situations where you want to update some values in an existing row, creating 
>> the row if needed. Though in larger programming tasks that ends up being 
>> rather tedious for business logic programmers. We hide this inside our C++ 
>> shell around SQLite C API and the solution is not bad but had some 
>> challenges for handling parameters to the statement(s) without risking them 
>> to be evaluated twice when re-using them for two distinct SQLite statements 
>> (while there is only one 'upsert' at the C++ logical level).


Besides, if you ever have to write a scripts for the command line tool, you're 
stuck trying to emulate that semantic. Or can we check by script the outcome of 
the UPDATE?

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om



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

Re: [sqlite] update or replace ...

[Olivier Mascia]

> Le 30 juin 2016 à 11:01, Simon Slavin  a écrit :
> 
>> Of course writing straight code in C/C++ it's rather simple to emulate 
>> situations where you want to update some values in an existing row, creating 
>> the row if needed.
> 
> The standard way of doing this is to do two commands:
> 
> 1) INSERT without the REPLACE
> 2) UPDATE
> 
> When step 1 fails because the key values already exist you trap this and 
> explicitly ignore it in your code (commented, because you're being nice to 
> other programmers).  Then it's always the UPDATE which updates the fields you 
> care about.

So
INSERT OR IGNORE ...
followed by
UPDATE ...

The (possibly) annoying thing behind this is that the most logical use case of 
an "UPDATE OR INSERT"/"UPSERT"/whatever-name is to update a row and in the rare 
case it might not already exist, to insert it.

I have not yet deep enough knowledge of inner details and optimizations inside 
sqlite.c but I tend to think it might be more costly to attempt insert first, 
having it fail (ignored) most of the times, and then only do the update.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om



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

Re: [sqlite] update or replace ...

[Clemens Ladisch]

Simon Slavin wrote:
> On 30 Jun 2016, at 8:24am, Olivier Mascia  wrote:
>> Of course writing straight code in C/C++ it's rather simple to emulate
>> situations where you want to update some values in an existing row,
>> creating the row if needed.
>
> The standard way of doing this is to do two commands:
>
> 1) INSERT without the REPLACE
> 2) UPDATE
>
> When step 1 fails because the key values already exist you trap this
> and explicitly ignore it in your code (commented, because you're being
> nice to other programmers).

Trapping only the conflict violation while properly handling any other
errors makes this even more complex.

A better way is to try the UPDATE first, and if the data was not found
(number of affected rows is zero), do the INSERT.  Doesn't even need
a comment.


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

Re: [sqlite] update or replace ...

[Simon Slavin]

On 30 Jun 2016, at 8:24am, Olivier Mascia  wrote:

> Of course writing straight code in C/C++ it's rather simple to emulate 
> situations where you want to update some values in an existing row, creating 
> the row if needed.

The standard way of doing this is to do two commands:

1) INSERT without the REPLACE
2) UPDATE

When step 1 fails because the key values already exist you trap this and 
explicitly ignore it in your code (commented, because you're being nice to 
other programmers).  Then it's always the UPDATE which updates the fields you 
care about.

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

Re: [sqlite] update or replace ...

[Olivier Mascia]

> Le 30 juin 2016 à 10:06, no...@null.net a écrit :
> 
>> I'd love to have some equivalent to the UPDATE OR INSERT statement
>> (or variation on it) that some other engines expose.  But clearly
> 
> Does the "INSERT OR REPLACE" syntax not provide what you are looking
> for?

Absolutely not. "OR REPLACE", as Ryan and Simon pointed out clearly, is a 
conflict resolution mechanism.

1) The insert would have to trigger a constraint violation to execute the 
replace.
2) The replace *do* delete the row and insert a new one: this would have effect 
on related data (on delete cascade for instance).
3) The typical use case for some form of "UPSERT" (whatever the name and syntax 
it takes) is to generally update a row, assumed to be existing, inserting it at 
that occasion if needed.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om


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

Re: [sqlite] update or replace ...

[nomad]

On Thu Jun 30, 2016 at 09:24:36AM +0200, Olivier Mascia wrote:

> I'd love to have some equivalent to the UPDATE OR INSERT statement
> (or variation on it) that some other engines expose.  But clearly

Does the "INSERT OR REPLACE" syntax not provide what you are looking
for?

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

Re: [sqlite] update or replace ...

[Olivier Mascia]

> Le 29 juin 2016 à 18:56, Olivier Mascia  a écrit :
> 
> Clearly it does nothing.
> Does this fit the intended behavior?
> Would that mean 'or replace' in the context of an update statement is a void 
> operation?

Thanks Ryan and Simon for your answers.  Indeed I forgot the OR 
REPLACE|FAIL|IGNORE|... in SQLite is related to the ON CONFLICT clause.

I'd love to have some equivalent to the UPDATE OR INSERT statement (or 
variation on it) that some other engines expose.  But clearly building it on 
top of a syntax using OR wouldn't be a nice idea: it would bring multiple 
interpretations to the 'OR' in this context, sometimes ON CONFLICT resolution 
and sometimes 'ON NOTHING DONE' resolution.

Of course writing straight code in C/C++ it's rather simple to emulate 
situations where you want to update some values in an existing row, creating 
the row if needed. Though in larger programming tasks that ends up being rather 
tedious for business logic programmers. We hide this inside our C++ shell 
around SQLite C API and the solution is not bad but had some challenges for 
handling parameters to the statement(s) without risking them to be evaluated 
twice when re-using them for two distinct SQLite statements (while there is 
only one 'upsert' at the C++ logical level).

All in all, I hope SQLite could bring up some sort of such UPDATE OR INSERT 
(which would be more easily named UPSERT in SQLite existing syntax), one day.  
Does adding such a new statement would fit the 'small' within "Small. Fast. 
Reliable. Choose any three." I'm not sure, I don't yet have enough background 
with it.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om



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

Re: [sqlite] update or replace ...

[Simon Slavin]

On 29 Jun 2016, at 5:56pm, Olivier Mascia  wrote:

> What's the expected behavior of statement "update or replace ..."? 
> (http://sqlite.org/lang_update.html)
> 
> create table T(K integer primary key, V text);
> update or replace T set V='data' where K=1;
> 
> Clearly it does nothing.
> Does this fit the intended behavior?
> Would that mean 'or replace' in the context of an update statement is a void 
> operation?

For the UPDATE OR REPLACE command, the REPLACE part comes into play only if an 
UPDATE would cause some sort of conflict.  Since the UPDATE command does 
nothing in this case, there will be no conflict, so there will be no REPLACE.

This command will return SQLITE_OK.  It is an entirely legal command which does 
nothing in this situation.

Under different circumstances (i.e. if the table already held data) the same 
command might result in a REPLACE.

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

Re: [sqlite] update or replace ...

[R Smith]

On 2016/06/29 6:56 PM, Olivier Mascia wrote:

Dear all,

What's the expected behavior of statement "update or replace ..."? 
(http://sqlite.org/lang_update.html)

create table T(K integer primary key, V text);
update or replace T set V='data' where K=1;

Clearly it does nothing.
Does this fit the intended behavior?
Would that mean 'or replace' in the context of an update statement is a void 
operation?


...OR REPLACE here indicates a Conflict-resolution algorithm. Anything 
after the OR in an UPDATE OR xxx  statement relates to the conflict 
resolution algorithm - i.e. What must be done if this change causes a 
constraint to fail?


In all the next examples, assume two rows with Primary key IDs 1 and 2 
already exist in the Database:


UPDATE OR FAIL   SET ID = 1 WHERE ID = 2;
 - indicates that the executions should stop immediately and produce a 
Constraint-failed error without touching any transaction mechanics.


UPDATE OR ROLLBACK   SET ID = 1 WHERE ID = 2;
 - indicates that the executions should stop immediately and roll back 
the entire active transaction.


UPDATE OR ABORT   SET ID = 1 WHERE ID = 2;
 - indicates that the executions should stop immediately and roll back 
the current statement, but leave prior changes in the transaction as is.


UPDATE OR IGNORE   SET ID = 1 WHERE ID = 2;
 - indicates that the the update should be skipped. It's the "Do 
nothing" check.


UPDATE OR REPLACE   SET ID = 1 WHERE ID = 2;
 - indicates that the Row with ID: 1 which already exists must be 
deleted and then this row's ID should be set to 1. Dangerous!



Hope that clears it up,
Ryan

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

[sqlite] update or replace ...

[Olivier Mascia]

Dear all,

What's the expected behavior of statement "update or replace ..."? 
(http://sqlite.org/lang_update.html)

create table T(K integer primary key, V text);
update or replace T set V='data' where K=1;

Clearly it does nothing.
Does this fit the intended behavior?
Would that mean 'or replace' in the context of an update statement is a void 
operation?

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om


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

Re: [sqlite] Update DataGrid and Save to database

[R.A. Nagy]

Most people seem to prefer black these days, but white is cooler. Reflects,
rather than absorbs, heat.

Of course, if you live up north that might be a bad thing. Here in the
south, all is well...



On Mon, Jun 13, 2016 at 9:31 AM, Chris Locke 
wrote:

> Great analogy.  PS: What colour seat covers should I be using if I have a
> Ford?
>
> On Mon, Jun 13, 2016 at 2:11 PM, jumper  wrote:
>
> > Thank you for the advice/information. I just solved the issue about a
> > minute ago. How can I stop getting new replies?
> >
> >
> > On 6/13/2016 8:08 AM, R Smith wrote:
> >
> >>
> >>
> >> On 2016/06/13 2:48 PM, jumper wrote:
> >>
> >>> New to SQLite and DataGrids. I need to know how to get an adapter,
> table
> >>> and dataset when app first comes up.
> >>> Then when someone updates the DataGrid I need to save the changes to
> the
> >>> database.
> >>>
> >>> I've been working on this for days and can't do it. Could someone
> please
> >>> post the code for how to do it?
> >>> Thank you so much,
> >>>
> >>
> >> Hi John,
> >>
> >> These questions you ask are about creating user-interfaces for databases
> >> and adapters in some GUI systems - none of which is remotely related to
> the
> >> inner workings of the SQLite DB engine or answerable on this forum. It's
> >> like you want to know where the gas pedal and steering wheel is in your
> >> car, and now contacting the Engine manufacturer to ask. We don't really
> >> know.
> >>
> >> I'm assuming the platforms you use may be MSVC, C++ or Delphi or some
> >> other GUI type creator - try their forum or community, or perhaps even a
> >> google search for a tutorial stating exactly the tools you use.
> >>
> >>
> >> Good luck!
> >> Ryan
> >>
> >>
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update DataGrid and Save to database

[Chris Locke]

Great analogy.  PS: What colour seat covers should I be using if I have a
Ford?

On Mon, Jun 13, 2016 at 2:11 PM, jumper  wrote:

> Thank you for the advice/information. I just solved the issue about a
> minute ago. How can I stop getting new replies?
>
>
> On 6/13/2016 8:08 AM, R Smith wrote:
>
>>
>>
>> On 2016/06/13 2:48 PM, jumper wrote:
>>
>>> New to SQLite and DataGrids. I need to know how to get an adapter, table
>>> and dataset when app first comes up.
>>> Then when someone updates the DataGrid I need to save the changes to the
>>> database.
>>>
>>> I've been working on this for days and can't do it. Could someone please
>>> post the code for how to do it?
>>> Thank you so much,
>>>
>>
>> Hi John,
>>
>> These questions you ask are about creating user-interfaces for databases
>> and adapters in some GUI systems - none of which is remotely related to the
>> inner workings of the SQLite DB engine or answerable on this forum. It's
>> like you want to know where the gas pedal and steering wheel is in your
>> car, and now contacting the Engine manufacturer to ask. We don't really
>> know.
>>
>> I'm assuming the platforms you use may be MSVC, C++ or Delphi or some
>> other GUI type creator - try their forum or community, or perhaps even a
>> google search for a tutorial stating exactly the tools you use.
>>
>>
>> Good luck!
>> Ryan
>>
>>
>> ___
>> 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] Update DataGrid and Save to database

[R Smith]

On 2016/06/13 3:09 PM, jumper wrote:

I just solved it only seconds ago. Can't believe it after all this time.
How do I stop getting messages now that I have it working?


Have you read this e-mail all the way to the bottom?
:)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update DataGrid and Save to database

[jumper]

Thank you for the advice/information. I just solved the issue about a 
minute ago. How can I stop getting new replies?



On 6/13/2016 8:08 AM, R Smith wrote:



On 2016/06/13 2:48 PM, jumper wrote:
New to SQLite and DataGrids. I need to know how to get an adapter, 
table and dataset when app first comes up.
Then when someone updates the DataGrid I need to save the changes to 
the database.


I've been working on this for days and can't do it. Could someone 
please post the code for how to do it?

Thank you so much,


Hi John,

These questions you ask are about creating user-interfaces for 
databases and adapters in some GUI systems - none of which is remotely 
related to the inner workings of the SQLite DB engine or answerable on 
this forum. It's like you want to know where the gas pedal and 
steering wheel is in your car, and now contacting the Engine 
manufacturer to ask. We don't really know.


I'm assuming the platforms you use may be MSVC, C++ or Delphi or some 
other GUI type creator - try their forum or community, or perhaps even 
a google search for a tutorial stating exactly the tools you use.



Good luck!
Ryan


___
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] Update DataGrid and Save to database

[jumper]

I just solved it only seconds ago. Can't believe it after all this time.
How do I stop getting messages now that I have it working?

thank you so much.

On 6/13/2016 8:04 AM, Chris Brody wrote:

I wonder if the answers following link could help you:
http://stackoverflow.com/questions/19617368/sqlite-database-and-datagrid

I found this by a quick Google search. For the future please explain
the context, show that you have done some basic research, show what
you have found, and show specifically where you are getting stuck. (I
am guessing this is DataGrid for .NET but have no way to know for sure
what you are asking.)

If you get stuck somewhere you can also ask on Stack Overflow but
please explain these things and also double-check their rules.

On Mon, Jun 13, 2016 at 2:48 PM, jumper  wrote:

New to SQLite and DataGrids. I need to know how to get an adapter, table and
dataset when app first comes up.
Then when someone updates the DataGrid I need to save the changes to the
database.

I've been working on this for days and can't do it. Could someone please
post the code for how to do it?
Thank you so much,

john
___
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] Update DataGrid and Save to database

[R Smith]

On 2016/06/13 2:48 PM, jumper wrote:
New to SQLite and DataGrids. I need to know how to get an adapter, 
table and dataset when app first comes up.
Then when someone updates the DataGrid I need to save the changes to 
the database.


I've been working on this for days and can't do it. Could someone 
please post the code for how to do it?

Thank you so much,


Hi John,

These questions you ask are about creating user-interfaces for databases 
and adapters in some GUI systems - none of which is remotely related to 
the inner workings of the SQLite DB engine or answerable on this forum. 
It's like you want to know where the gas pedal and steering wheel is in 
your car, and now contacting the Engine manufacturer to ask. We don't 
really know.


I'm assuming the platforms you use may be MSVC, C++ or Delphi or some 
other GUI type creator - try their forum or community, or perhaps even a 
google search for a tutorial stating exactly the tools you use.



Good luck!
Ryan


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

Re: [sqlite] Update DataGrid and Save to database

[Chris Brody]

I wonder if the answers following link could help you:
http://stackoverflow.com/questions/19617368/sqlite-database-and-datagrid

I found this by a quick Google search. For the future please explain
the context, show that you have done some basic research, show what
you have found, and show specifically where you are getting stuck. (I
am guessing this is DataGrid for .NET but have no way to know for sure
what you are asking.)

If you get stuck somewhere you can also ask on Stack Overflow but
please explain these things and also double-check their rules.

On Mon, Jun 13, 2016 at 2:48 PM, jumper  wrote:
> New to SQLite and DataGrids. I need to know how to get an adapter, table and
> dataset when app first comes up.
> Then when someone updates the DataGrid I need to save the changes to the
> database.
>
> I've been working on this for days and can't do it. Could someone please
> post the code for how to do it?
> Thank you so much,
>
> john
> ___
> 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] Update DataGrid and Save to database

[jumper]

New to SQLite and DataGrids. I need to know how to get an adapter, table 
and dataset when app first comes up.
Then when someone updates the DataGrid I need to save the changes to the 
database.


I've been working on this for days and can't do it. Could someone please 
post the code for how to do it?

Thank you so much,

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

Re: [sqlite] UPDATE statement without FROM clause

[skywind mailing lists]

Hi,

of course there is in general a difference between syntax complexity and 
performance but unfortunately not in this case. And the „just“ is very often 
the most difficult part.

Regards,
Hartwig

> Am 2016-06-07 um 07:39 schrieb James K. Lowden :
> 
> On Sat, 4 Jun 2016 18:18:36 +0200
> skywind mailing lists  wrote:
> 
>> At the moment I have to run something like:
>> 
>> UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),...
>> itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);
>> 
>> Using a FROM clause I just need one scan through B (at least in
>> principle). Now, I need N+1 scans.
> 
> Nonsense.  SQL provides no instruction to the implementation on how to
> organize or traverse the data.  SQLite is free to scan B once, twice,
> or not at all.  
> 
> Syntax has nothing to do with performance.  A correlated subquery is an
> expression of logic; it's not meant to be taken literally, and often
> isn't.  This particular form "just" needs to be recognized by the
> optimizer.  
> 
> --jkl
> ___
> 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] UPDATE statement without FROM clause

[James K. Lowden]

On Sat, 4 Jun 2016 18:18:36 +0200
skywind mailing lists  wrote:

> At the moment I have to run something like:
> 
> UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),...
> itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);
> 
> Using a FROM clause I just need one scan through B (at least in
> principle). Now, I need N+1 scans.

Nonsense.  SQL provides no instruction to the implementation on how to
organize or traverse the data.  SQLite is free to scan B once, twice,
or not at all.  

Syntax has nothing to do with performance.  A correlated subquery is an
expression of logic; it's not meant to be taken literally, and often
isn't.  This particular form "just" needs to be recognized by the
optimizer.  

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

Re: [sqlite] UPDATE statement without FROM clause

[Graham Holden]

One idea I came up with a while ago is to use a trigger on the "FROM"
table to cause updates to the main table:

CREATE TRIGGER UpdateTrigger AFTER UPDATE OF TriggerField ON Updates
BEGIN
UPDATE Master SET
Field1 = OLD.Field1,
Field2 = OLD.Field2,
...
WHERE Master.Key = OLD.Key
END;

and trigger the update with:

UPDATE Updates SET TriggerField = NULL ;

It seems to run (in my very simple test) at the same speed as REPLACE
INTO but has the advantages that (a) it doesn't replace rows (possibly
affecting rowids) and (b) not having to specify unchanged fields.

See http://stackoverflow.com/a/22481731/2096401 for more details.

Graham Holden
sql...@aldurslair.com


Monday, June 06, 2016, 9:11:14 AM, Hick Gunter <h...@scigames.at> wrote:

> Would not

> Replace into A () select ,,
>  from A [cross] join B on( );

> do the trick? If a.rowid has an alias (i.e. integer primary key)
> then the modified rows would be deleted, but reinserted with their
> respective previous rowids. This may required switching foreign  
> keys off fort he duration of the update.

> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@mailinglists.sqlite.org 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von skywind 
> mailing lists
> Gesendet: Samstag, 04. Juni 2016 18:19
> An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Betreff: Re: [sqlite] UPDATE statement without FROM clause

> Hi,

> why? At the moment I have to run something like:

> UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE 
> EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);

> Using a FROM clause I just need one scan through B (at least in principle). 
> Now, I need N+1 scans.

> Regards,
> Hartwig

>> Am 2016-06-04 um 15:33 schrieb Gerry Snyder <mesmerizer...@gmail.com>:
>>
>> If SQLite implemented the FROM it would just be a translation into the
>> complex and slow statements you want to avoid.
>>
>> Gerry Snyder
>> On Jun 4, 2016 9:19 AM, "skywind mailing lists"
>> <mailingli...@skywind.eu>
>> wrote:
>>
>>> Hi,
>>>
>>> I am using quite often SQL statements that update the data of one
>>> table with data from another table. This leads to some quite complex
>>> (and slow) statements because SQLite3 is not supporting a FROM clause
>>> in update statements. I am just wondering why the FROM clause is not
>>> supported by SQLite3?! Is this too complex to implement or is there
>>> simply no demand for these type of statements?
>>>
>>> Regards,
>>> Hartwig
>>>
>>>



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

Re: [sqlite] UPDATE statement without FROM clause

[Hick Gunter]

Would not

Replace into A () select ,, 
 from A [cross] join B on( );

do the trick? If a.rowid has an alias (i.e. integer primary key) then the 
modified rows would be deleted, but reinserted with their respective previous 
rowids. This may required switching foreign keys off fort he duration of the 
update.

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von skywind 
mailing lists
Gesendet: Samstag, 04. Juni 2016 18:19
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] UPDATE statement without FROM clause

Hi,

why? At the moment I have to run something like:

UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE 
EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);

Using a FROM clause I just need one scan through B (at least in principle). 
Now, I need N+1 scans.

Regards,
Hartwig

> Am 2016-06-04 um 15:33 schrieb Gerry Snyder <mesmerizer...@gmail.com>:
>
> If SQLite implemented the FROM it would just be a translation into the
> complex and slow statements you want to avoid.
>
> Gerry Snyder
> On Jun 4, 2016 9:19 AM, "skywind mailing lists"
> <mailingli...@skywind.eu>
> wrote:
>
>> Hi,
>>
>> I am using quite often SQL statements that update the data of one
>> table with data from another table. This leads to some quite complex
>> (and slow) statements because SQLite3 is not supporting a FROM clause
>> in update statements. I am just wondering why the FROM clause is not
>> supported by SQLite3?! Is this too complex to implement or is there
>> simply no demand for these type of statements?
>>
>> Regards,
>> Hartwig
>>
>>
>>
>> ___
>> 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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] UPDATE statement without FROM clause

[Keith Medcalf]

You can simulate either a two-pass or one-pass UPDATE  SET ... FROM 
,  WHERE 

By doing one or the other of the following (depending on whether you want 
one-pass or two-pass).

for a one-pass update:

BEGIN IMMEDIATE;
SELECT .rowid,  FROM  WHERE 
fetch a row
  UPDATE  SET x=?, ...  WHERE rowid=? -- bind the result set to the 
parameters and execute
when you run out of rows,
COMMIT;

For a two pass update BEGIN IMMEDIATE then either (a) SELECT into a temp table 
and do the update above from that table, or (b) store the rows in memory and 
then do the update afterwards.  If you used a temp table, drop it before 
committing.

> I am using quite often SQL statements that update the data of one table
> with data from another table. This leads to some quite complex (and slow)
> statements because SQLite3 is not supporting a FROM clause in update
> statements. I am just wondering why the FROM clause is not supported by
> SQLite3?! Is this too complex to implement or is there simply no demand
> for these type of statements?




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

Re: [sqlite] UPDATE statement without FROM clause

[Jean-Christophe Deschamps]

At 23:34 04/06/2016, you wrote:

On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps 
 wrote:


> Can't the same update be done more efficiently with a CTE?

The command inside the WITH has to be a SELECT command.


Definitely not as Ryan pointed out, and as the help file clearly states 
otherwise:


"All common table expressions (ordinary and recursive) are created by
 prepending a WITH clause in front of a 
SELECT, 
INSERT, 
DELETE, or 
UPDATE

 statement."

I agree that in that simple example, using a CTE is just syntactic sugar:

CREATE TABLE A (
  Id INTEGER NOT NULL PRIMARY KEY,
  Item1 INT,
  Item2 CHAR,
  item3 CHAR);
with ints (n) as (select -5000 union all select n+1 from ints limit 1)
INSERT INTO A (id) select n from ints;

CREATE TABLE B (
  Id INTEGER NOT NULL PRIMARY KEY,
  Item1 INT,
  Item2 CHAR,
  item3 CHAR);
INSERT INTO B (item1) VALUES 
(83),(81),(76),(105),(116),(101),(32),(114),(111),(99),(107),(115),(33),(0);

-- make table B bigger (IDs in A and B only partl overlap)
with ints (n) as (select (select count(*) from B)+1 union all select 
n+1 from ints limit 1)

INSERT INTO B (id) select n from ints;

with C as (select id, item1, item2, item3 from B where item1 not null)
update A set
   item1 = (select C.item1 from C where C.id = A.id),
   item2 = (select char(C.item1) from C where C.id = A.id),
   item3 = (select group_concat(item2, '') from A AA where AA.id 
<= A.id)

where id in (select id from C);

select * from A where item2 not null;

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

Re: [sqlite] UPDATE statement without FROM clause

[R Smith]

On 2016/06/04 11:34 PM, Simon Slavin wrote:

On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps  wrote:


Can't the same update be done more efficiently with a CTE?

The command inside the WITH has to be a SELECT command.

I wonder if there's a good reason for that.  If the command inside WITH could 
make changes to the database the result might be ambiguous, and very sensitive 
to how the SQL engine works.


Well yes, WITH is a form of SELECT, so the function clause inside the 
WITH has to be SELECT, in the same way that the function clause inside 
an UPDATE statement has to be SET. However, the result of the WITH can 
be applied to any Insert or Update or such (wherever a SELECT can be 
used) - to demonstrate:



  -- SQLite version 3.9.2  [ Release: 2015-11-02 ]  on  SQLitespeed
   version 2.0.2.4.

  -- Script Items: 5  Parameter Count: 0
  -- 2016-06-05 02:26:52.452  |  [Info] Script Initialized, Started
   executing...
  --
   


   CREATE TABLE Testy(
  a INT,
  b INT
   );

   WITH CTE(x) AS (
SELECT 1
  UNION ALL
SELECT x+1 FROM CTE WHERE x<10
   )
   INSERT INTO Testy(a,b)
   SELECT x,x*2 FROM CTE;


   SELECT * FROM Testy;


  --   a  |   b
  --  | 
  --   1  |   2
  --   2  |   4
  --   3  |   6
  --   4  |   8
  --   5  |  10
  --   6  |  12
  --   7  |  14
  --   8  |  16
  --   9  |  18
  --  10  |  20

   WITH CTE(x,y) AS (
SELECT 1,100
  UNION ALL
SELECT x+1,y+(x*5) FROM CTE WHERE x<10
   )
   UPDATE Testy SET b=(SELECT CTE.y FROM CTE WHERE CTE.x = Testy.a
   LIMIT 1) WHERE Testy.a < 8


   SELECT * FROM Testy;


  --   a  |   b
  --  | -
  --   1  |  100
  --   2  |  105
  --   3  |  115
  --   4  |  130
  --   5  |  150
  --   6  |  175
  --   7  |  205
  --   8  |   16
  --   9  |   18
  --  10  |   20


As to the OP's question of whether it can be done in a CTE (I assume he 
means /using/ a CTE, and not necessarily having the working bits 
/inside/ the CTE), there still is no way to use the UPDATE-FROM, even 
from a CTE.


I might be wrong, but I think there is a problem with the UPDATE FROM 
implementation; it has to require a scan one way or the other. Whether 
you look up the value in the referenced table according to the key in 
the updated table, or vice versa, there is no single-pass way of 
avoiding the per-item lookup (unless somebody has done this - I'd be 
very interested how). There is no way even for the QP to be sure a Key 
column from both (or even one) of the tables will be involved in the 
WHERE clause.
Perhaps if you have two tables that are exactly in sync (same Key-list, 
same length), but that must be a serious edge-case.


The only advantage using the UPDATE-FROM syntax, which I can see, is 
human legibility in SQL terms - which is not nothing btw.



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

Re: [sqlite] UPDATE statement without FROM clause

[Simon Slavin]

On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps  wrote:

> Can't the same update be done more efficiently with a CTE?

The command inside the WITH has to be a SELECT command.

I wonder if there's a good reason for that.  If the command inside WITH could 
make changes to the database the result might be ambiguous, and very sensitive 
to how the SQL engine works.

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

Re: [sqlite] UPDATE statement without FROM clause

[Jean-Christophe Deschamps]

At 18:18 04/06/2016, you wrote:

Hi,

why? At the moment I have to run something like:

UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... 
itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);


Using a FROM clause I just need one scan through B (at least in 
principle). Now, I need N+1 scans.


Regards,
Hartwig


Can't the same update be done more efficiently with a CTE?

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

Re: [sqlite] UPDATE statement without FROM clause

[Brad Stiles]

Is there some absolute requirement that it all be done in SQL?  Depending on 
the number of "items", it'd probably be faster in a loop in code. 

Even in MSSQL Server using TSQL, you're better off using a cursor for that sort 
of thing. I only use UPDATE FROM when I need a join to formulate the WHERE 
clause. 

> On Jun 4, 2016, at 12:18, skywind mailing lists  
> wrote:
> 
> Hi,
> 
> why? At the moment I have to run something like:
> 
> UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE 
> EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);
> 
> Using a FROM clause I just need one scan through B (at least in principle). 
> Now, I need N+1 scans.
> 
> Regards,
> Hartwig
> 
>> Am 2016-06-04 um 15:33 schrieb Gerry Snyder :
>> 
>> If SQLite implemented the FROM it would just be a translation into the
>> complex and slow statements you want to avoid.
>> 
>> Gerry Snyder
>> On Jun 4, 2016 9:19 AM, "skywind mailing lists" 
>> wrote:
>> 
>>> Hi,
>>> 
>>> I am using quite often SQL statements that update the data of one table
>>> with data from another table. This leads to some quite complex (and slow)
>>> statements because SQLite3 is not supporting a FROM clause in update
>>> statements. I am just wondering why the FROM clause is not supported by
>>> SQLite3?! Is this too complex to implement or is there simply no demand for
>>> these type of statements?
>>> 
>>> Regards,
>>> Hartwig
>>> 
>>> 
>>> 
>>> ___
>>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE statement without FROM clause

[skywind mailing lists]

Hi,

why? At the moment I have to run something like:

UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE 
EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);

Using a FROM clause I just need one scan through B (at least in principle). 
Now, I need N+1 scans.

Regards,
Hartwig

> Am 2016-06-04 um 15:33 schrieb Gerry Snyder :
> 
> If SQLite implemented the FROM it would just be a translation into the
> complex and slow statements you want to avoid.
> 
> Gerry Snyder
> On Jun 4, 2016 9:19 AM, "skywind mailing lists" 
> wrote:
> 
>> Hi,
>> 
>> I am using quite often SQL statements that update the data of one table
>> with data from another table. This leads to some quite complex (and slow)
>> statements because SQLite3 is not supporting a FROM clause in update
>> statements. I am just wondering why the FROM clause is not supported by
>> SQLite3?! Is this too complex to implement or is there simply no demand for
>> these type of statements?
>> 
>> Regards,
>> Hartwig
>> 
>> 
>> 
>> ___
>> 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] UPDATE statement without FROM clause

[Gerry Snyder]

If SQLite implemented the FROM it would just be a translation into the
complex and slow statements you want to avoid.

Gerry Snyder
On Jun 4, 2016 9:19 AM, "skywind mailing lists" 
wrote:

> Hi,
>
> I am using quite often SQL statements that update the data of one table
> with data from another table. This leads to some quite complex (and slow)
> statements because SQLite3 is not supporting a FROM clause in update
> statements. I am just wondering why the FROM clause is not supported by
> SQLite3?! Is this too complex to implement or is there simply no demand for
> these type of statements?
>
> Regards,
> Hartwig
>
>
>
> ___
> 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] UPDATE statement without FROM clause

[skywind mailing lists]

Hi,

I am using quite often SQL statements that update the data of one table with 
data from another table. This leads to some quite complex (and slow) statements 
because SQLite3 is not supporting a FROM clause in update statements. I am just 
wondering why the FROM clause is not supported by SQLite3?! Is this too complex 
to implement or is there simply no demand for these type of statements?

Regards,
Hartwig



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

[sqlite] UPDATE/open/close blocked while executing SELECT

[Keith Medcalf]

On Monday, 18 April, 2016 08:47 Olivier Mascia wrote:

> Just to clarify: you mean compiled using SQLITE_CONFIG_MULTITHREAD (and
> not SQLITE_CONFIG_SERIALIZED), is that it?

> Then, if using threads in the application, each thread has its own
> connection (or multiple connections) but no thread share any connection
> with another one? (This is required for SQLITE_CONFIG_MULTITHREAD mode,
> else you would need SQLITE_CONFIG_SERIALIZED, and that would add a lot of
> contention).

You do not have to restrict a connection to a thread, and you can use the same 
connection from multiple threads.  What you cannot do (and what SERIALIZED 
enforces) is that only one thread per connection can be executing inside 
(entered) the SQLite engine/library at a time.  That is, a connection (and 
anything derived from that connection) is fully serially entrant across 
threads.  It is not, however, multiply entrant.  The only difference between 
MULTITHREADED and SERIALIZED is that in the latter case SQLite enforces single 
entrance per connection -- in MULTITHREAD you have to enforce that yourself.

Dedicating a connection per thread and having a given thread only use its 
connection is one way of enforcing the single entrance requirement, but not the 
only way.

[sqlite] UPDATE/open/close blocked while executing SELECT

[Olivier Mascia]

> Le 18 avr. 2016 ? 15:10, Detlef Golze  a ?crit :
> 
> thank you for the hints. I think I do it correctly, I compiled with 
> SQLITE_CONFIG_MULTITHREAD and attach the database with the URI option 
> cache=shared.

Do you really *need* to use shared cache in favor of private cache?

There are more contentions (on the cache) implied by locks on table b-trees, 
when you use shared-cache. We quickly had to rule it out, for better 
concurrency (of readers). Also when using shared-cache, taking into account the 
large number of SQLITE_LOCKED_SHAREDCACHE extended error code you will get, it 
is probably better to use sqlite3_unlock_notify() properly in those cases, so 
that each thread is awaken properly as soon as it can retry and proceed. But 
that implies some more programming and is probably more fitted for an 
intermediate layer of code between you application code and sqlite library code 
(that's how we tested it).

Anyway, unless you have severe memory constraints, I would insist you try the 
private cache for each of your connections, you should see direct benefits.

> Also, each connection is used by one thread at a time. But another thread may 
> reuse a connection - I hope that is allowed.

I think so, because sqlite does not keep per-thread data but per connection 
data. So if you're absolutely sure this can't lead to situations of two threads 
sharing the connection, it should be okay, though I'd refrain to do that, it 
probably is useless.  Here our experience has been that "re-using" connections 
is useless, at least for us. Opening a connection when needed is very cheap 
with sqlite. So each worker thread in our app server, opens a new connection 
when the thread is scheduled fo working for a client and closes it when work 
done (client disconnect).

> Are you also opening/closing database connections and prepare statements 
> while another SELECT is running?

We can have any number of connections opened (generally less than 30), and any 
of them can run simultaneous select statements without issue, as far as our 
short experience shows.  You can even have one writer do a short-lived 
transaction while you have a number of readers right in the middle of 
select(s), that won't be a problem, wal is your friend for this.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om

[sqlite] UPDATE/open/close blocked while executing SELECT

[Clemens Ladisch]

Detlef Golze wrote:
> I attach the database with the URI option cache=shared.

This makes all threads share a single connection.  Don't do it.


Regards,
Clemens

[sqlite] UPDATE/open/close blocked while executing SELECT

[Simon Slavin]

On 18 Apr 2016, at 2:10pm, Detlef Golze  wrote:

> I compiled with SQLITE_CONFIG_MULTITHREAD

That bit's okay.

> and attach the database with the URI option cache=shared

Try turning that bit off and see if it results in an overall speed-up.

Simon.

[sqlite] UPDATE/open/close blocked while executing SELECT

[Olivier Mascia]

> Le 18 avr. 2016 ? 14:17, Detlef Golze  a ?crit :
> 
> Hi,
> 
> I am using SQLite C amalgamation version 3.08.11.01 compiled for Multi 
> Threading Mode and using WAL Journal Mode.
> 
> Sometimes I need to execute a SELECT statement which takes a very long time 
> to complete. I realized that during that time other functions are blocked for 
> a significant amount of time, eventually they get through after few minutes, 
> but then, another statement/function hangs. I have seen UPDATE statements 
> blocking and also functions like sqlite3_prepare16_v2(), open/close calls for 
> the same database.
> 
> It is usually blocking at btreeLockCarefully().
> 
> Is there something I can do to avoid such long starvations or is that by 
> design?
> 
> Thank you very much,
> Detlef.

I'm not an old-timer using SQLite and participating here, but I'm using it in 
WAL journal mode only, especially for its feature of 'multiple readers do not 
block writer and see a stable view of the db based on the instant they started 
their transaction'.

Just to clarify: you mean compiled using SQLITE_CONFIG_MULTITHREAD (and not 
SQLITE_CONFIG_SERIALIZED), is that it?

Then, if using threads in the application, each thread has its own connection 
(or multiple connections) but no thread share any connection with another one? 
(This is required for SQLITE_CONFIG_MULTITHREAD mode, else you would need 
SQLITE_CONFIG_SERIALIZED, and that would add a lot of contention).

Also, are your connections using private (SQLITE_OPEN_PRIVATECACHE) or shared 
cache (SQLITE_OPEN_SHAREDCACHE)?

When using SQLITE_CONFIG_MULTITHREAD, taking care no application thread share a 
connection with another, and using SQLITE_OPEN_PRIVATECACHE, along with WAL 
journal mode, I see no issue similar to what you report in our own programming.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om

[sqlite] UPDATE/open/close blocked while executing SELECT

[Detlef Golze]

> Detlef Golze wrote:
> > I attach the database with the URI option cache=shared.
> 
> This makes all threads share a single connection.  Don't do it.
> 
> 
> Regards,
> Clemens

That was indeed the problem. During my initial tests this option provided the 
best performance. I probably need to re-think that.

Thanks,
Detlef.

[sqlite] UPDATE/open/close blocked while executing SELECT

[Detlef Golze]

Hello Olivier,

thank you for the hints. I think I do it correctly, I compiled with 
SQLITE_CONFIG_MULTITHREAD and attach the database with the URI option 
cache=shared. Also, each connection is used by one thread at a time. But 
another thread may reuse a connection - I hope that is allowed.

Are you also opening/closing database connections and prepare statements while 
another SELECT is running?

Thanks,
Detlef.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Olivier 
Mascia
Sent: Monday, April 18, 2016 2:47 PM
To: SQLite mailing list
Subject: Re: [sqlite] UPDATE/open/close blocked while executing SELECT


> Le 18 avr. 2016 ? 14:17, Detlef Golze  a ?crit :
> 
> Hi,
> 
> I am using SQLite C amalgamation version 3.08.11.01 compiled for Multi 
> Threading Mode and using WAL Journal Mode.
> 
> Sometimes I need to execute a SELECT statement which takes a very long time 
> to complete. I realized that during that time other functions are blocked for 
> a significant amount of time, eventually they get through after few minutes, 
> but then, another statement/function hangs. I have seen UPDATE statements 
> blocking and also functions like sqlite3_prepare16_v2(), open/close calls for 
> the same database.
> 
> It is usually blocking at btreeLockCarefully().
> 
> Is there something I can do to avoid such long starvations or is that by 
> design?
> 
> Thank you very much,
> Detlef.

I'm not an old-timer using SQLite and participating here, but I'm using it in 
WAL journal mode only, especially for its feature of 'multiple readers do not 
block writer and see a stable view of the db based on the instant they started 
their transaction'.

Just to clarify: you mean compiled using SQLITE_CONFIG_MULTITHREAD (and not 
SQLITE_CONFIG_SERIALIZED), is that it?

Then, if using threads in the application, each thread has its own connection 
(or multiple connections) but no thread share any connection with another one? 
(This is required for SQLITE_CONFIG_MULTITHREAD mode, else you would need 
SQLITE_CONFIG_SERIALIZED, and that would add a lot of contention).

Also, are your connections using private (SQLITE_OPEN_PRIVATECACHE) or shared 
cache (SQLITE_OPEN_SHAREDCACHE)?

When using SQLITE_CONFIG_MULTITHREAD, taking care no application thread share a 
connection with another, and using SQLITE_OPEN_PRIVATECACHE, along with WAL 
journal mode, I see no issue similar to what you report in our own programming.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om


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

[sqlite] UPDATE/open/close blocked while executing SELECT

[Detlef Golze]

Hi,

I am using SQLite C amalgamation version 3.08.11.01 compiled for Multi 
Threading Mode and using WAL Journal Mode.

Sometimes I need to execute a SELECT statement which takes a very long time to 
complete. I realized that during that time other functions are blocked for a 
significant amount of time, eventually they get through after few minutes, but 
then, another statement/function hangs. I have seen UPDATE statements blocking 
and also functions like sqlite3_prepare16_v2(), open/close calls for the same 
database.

It is usually blocking at btreeLockCarefully().

Is there something I can do to avoid such long starvations or is that by design?

Thank you very much,
Detlef.

[sqlite] Awesome SQLite Update - New Book - Getting Started with SQL(lite and SQLiteStudio)

[Gerald Bauer]

Hello,
  For you enjoyment I have added three more entries to the Awesome
SQLite [1] collection:

  - sqlite-web (github: coleifer/sqlite-web) by Charles Leifer -- a
web-based SQLite database browser written in Python

 - sqliteweb (github: hypebeast/sqliteweb) by Sebastian Ruml -- a
web-based SQLite database browser written in Go

- sqleton (github: inukshuk/sqleton) by Sylvester Keil -- ['skel?t?n];
visualizes your SQLite database schema (requires graphviz)


 Anything missing? Contributions welcome. Cheers.

 [1] https://github.com/planetopendata/awesome-sqlite

[sqlite] Awesome SQLite Update - New Book - Getting Started with SQL(lite and SQLiteStudio)

[Gerald Bauer]

Hello,

  I've updated the Awesome SQLite [1] collection over at Planet Open
Data. What's news?

   Added a new beginner's book:

Getting Started with SQL - A Hands-On Approach for Beginners - by
Thomas Nield; 2016; O'Reilly; 134 pages -- learn SQL with SQLite and
SQLiteStudio


  Anything missing? Contributions welcome. Cheers.

 [1] https://github.com/planetopendata/awesome-sqlite

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Chris Prakoso]

Yes. Nothing complicated, fortunately.

On Tue, Feb 9, 2016 at 4:09 PM, Steven M. McNeese <
steven.mcneese at freedomparkdfw.com> wrote:

> So are you saying you are just reading data from your SQLite db using C#
> and
> just need to insert using the SQLite command?
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Chris
> Prakoso
> Sent: Tuesday, February 9, 2016 7:56 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] C# + SQLite - Update/Insert using Transaction is
> slower than without.
>
> Steven,
>
> I don't use any back-end, the code I pasted here IS my back-end.  I opted
> for direct SQLite connection.  So I don't use EF6 nor Linq.
>
> Chris
>
> On Tue, Feb 9, 2016 at 1:47 PM, Steven M. McNeese <
> steven.mcneese at freedomparkdfw.com> wrote:
>
> > Chris,
> >
> > What are you using in c# for SQLite back end?  Ado.net? Linq.  Let me
> > know and I can help you with bulk inserts.
> >
> > Sent from my iPhone
> >
> > > On Feb 9, 2016, at 6:13 AM, Chris Prakoso  wrote:
> > >
> > > Ok. Got it.
> > > Now, if only I can get that multiple rows update working on my code,
> > > it would be perfect.
> > >
> > > Thanks a lot,
> > > Chris
> > >
> > >> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch
> > >> 
> > wrote:
> > >>
> > >> Chris Prakoso wrote:
> > >>>public bool UpdateData(string tableName,
> > >> Dictionary<string,object> fields, List whereKeys)
> > >>>{
> > >>>...
> > >>>using (SQLiteTransaction transaction =
> > >> conn.BeginTransaction())
> > >>>{
> > >>>...
> > >>>rowsUpdated = cmd.ExecuteNonQuery();
> > >>>transaction.Commit();
> > >>
> > >> Using one transaction for each statement is slow.
> > >> (Re-opening the database doesn't help either.)
> > >>
> > >> You should use a single transaction around all update statements.
> > >>
> > >>
> > >> Regards,
> > >> Clemens
> > >> ___
> > >> sqlite-users mailing list
> > >> sqlite-users at mailinglists.sqlite.org
> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-user
> > >> s
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ---
> This email has been checked for viruses by Avast antivirus software.
> https://www.avast.com/antivirus
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Chris Prakoso]

Steven,

I don't use any back-end, the code I pasted here IS my back-end.  I opted
for direct SQLite connection.  So I don't use EF6 nor Linq.

Chris

On Tue, Feb 9, 2016 at 1:47 PM, Steven M. McNeese <
steven.mcneese at freedomparkdfw.com> wrote:

> Chris,
>
> What are you using in c# for SQLite back end?  Ado.net? Linq.  Let me know
> and I can help you with bulk inserts.
>
> Sent from my iPhone
>
> > On Feb 9, 2016, at 6:13 AM, Chris Prakoso  wrote:
> >
> > Ok. Got it.
> > Now, if only I can get that multiple rows update working on my code, it
> > would be perfect.
> >
> > Thanks a lot,
> > Chris
> >
> >> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch 
> wrote:
> >>
> >> Chris Prakoso wrote:
> >>>public bool UpdateData(string tableName,
> >> Dictionary fields, List whereKeys)
> >>>{
> >>>...
> >>>using (SQLiteTransaction transaction =
> >> conn.BeginTransaction())
> >>>{
> >>>...
> >>>rowsUpdated = cmd.ExecuteNonQuery();
> >>>transaction.Commit();
> >>
> >> Using one transaction for each statement is slow.
> >> (Re-opening the database doesn't help either.)
> >>
> >> You should use a single transaction around all update statements.
> >>
> >>
> >> Regards,
> >> Clemens
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Clemens Ladisch]

Chris Prakoso wrote:
> public bool UpdateData(string tableName, Dictionary 
> fields, List whereKeys)
> {
> ...
> using (SQLiteTransaction transaction = 
> conn.BeginTransaction())
> {
> ...
> rowsUpdated = cmd.ExecuteNonQuery();
> transaction.Commit();

Using one transaction for each statement is slow.
(Re-opening the database doesn't help either.)

You should use a single transaction around all update statements.


Regards,
Clemens

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Chris Prakoso]

Ok. Got it.
Now, if only I can get that multiple rows update working on my code, it
would be perfect.

Thanks a lot,
Chris

On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch  wrote:

> Chris Prakoso wrote:
> > public bool UpdateData(string tableName,
> Dictionary fields, List whereKeys)
> > {
> > ...
> > using (SQLiteTransaction transaction =
> conn.BeginTransaction())
> > {
> > ...
> > rowsUpdated = cmd.ExecuteNonQuery();
> > transaction.Commit();
>
> Using one transaction for each statement is slow.
> (Re-opening the database doesn't help either.)
>
> You should use a single transaction around all update statements.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Clemens Ladisch]

Chris Prakoso wrote:
> I've been testing the performance of my Insert/Update using
> Transaction and without, and I found that it is quicker when I don't
> use it.

Show the code.


Regards,
Clemens

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Chris Prakoso]

Here they are:

public bool UpdateData(string tableName, Dictionary
fields, List whereKeys)
{
bool result = false;
string sql = "";
List fieldList = new List();
List whereKeyList = new List();
int rowsUpdated = 0;

using (SQLiteConnection conn = new
SQLiteConnection(this.ConnectionString))
{
try
{
conn.Open();

SQLiteCommand cmd = new SQLiteCommand(conn);

using (SQLiteTransaction transaction =
conn.BeginTransaction())
{

// Build a list of fields need to be updated
if (fields.Count > 0)
{
foreach (KeyValuePair kvp in
fields)
{
cmd.Parameters.AddWithValue(kvp.Key,
kvp.Value);
fieldList.Add(kvp.Key);
}
}

sql = "update " + tableName + " set " +
this.BuildUpdateFieldList(fieldList) +
" where " + this.BuildWhereClause(whereKeys);

cmd.CommandText = sql;

rowsUpdated = cmd.ExecuteNonQuery();

transaction.Commit();
}


if (rowsUpdated > 0)
result = true;

}
catch (System.Exception ex)
{
this.UpdateStatusMessage(ex.Message);
}
}

return result;
}

On Tue, Feb 9, 2016 at 11:13 AM, Clemens Ladisch  wrote:

> Chris Prakoso wrote:
> > I've been testing the performance of my Insert/Update using
> > Transaction and without, and I found that it is quicker when I don't
> > use it.
>
> Show the code.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Chris Prakoso]

Hi all,

I've been testing the performance of my Insert/Update using
Transaction and without, and I found that it is quicker when I don't
use it.

Anybody has an insight on this?

Thanks a lot,
Chris

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Steven M. McNeese]

So are you saying you are just reading data from your SQLite db using C# and
just need to insert using the SQLite command?  

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Chris
Prakoso
Sent: Tuesday, February 9, 2016 7:56 AM
To: SQLite mailing list
Subject: Re: [sqlite] C# + SQLite - Update/Insert using Transaction is
slower than without.

Steven,

I don't use any back-end, the code I pasted here IS my back-end.  I opted
for direct SQLite connection.  So I don't use EF6 nor Linq.

Chris

On Tue, Feb 9, 2016 at 1:47 PM, Steven M. McNeese <
steven.mcneese at freedomparkdfw.com> wrote:

> Chris,
>
> What are you using in c# for SQLite back end?  Ado.net? Linq.  Let me 
> know and I can help you with bulk inserts.
>
> Sent from my iPhone
>
> > On Feb 9, 2016, at 6:13 AM, Chris Prakoso  wrote:
> >
> > Ok. Got it.
> > Now, if only I can get that multiple rows update working on my code, 
> > it would be perfect.
> >
> > Thanks a lot,
> > Chris
> >
> >> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch 
> >> 
> wrote:
> >>
> >> Chris Prakoso wrote:
> >>>public bool UpdateData(string tableName,
> >> Dictionary<string,object> fields, List whereKeys)
> >>>{
> >>>...
> >>>using (SQLiteTransaction transaction =
> >> conn.BeginTransaction())
> >>>{
> >>>...
> >>>rowsUpdated = cmd.ExecuteNonQuery();
> >>>transaction.Commit();
> >>
> >> Using one transaction for each statement is slow.
> >> (Re-opening the database doesn't help either.)
> >>
> >> You should use a single transaction around all update statements.
> >>
> >>
> >> Regards,
> >> Clemens
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-user
> >> s
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

[Steven M. McNeese]

Chris,

What are you using in c# for SQLite back end?  Ado.net? Linq.  Let me know and 
I can help you with bulk inserts. 

Sent from my iPhone

> On Feb 9, 2016, at 6:13 AM, Chris Prakoso  wrote:
> 
> Ok. Got it.
> Now, if only I can get that multiple rows update working on my code, it
> would be perfect.
> 
> Thanks a lot,
> Chris
> 
>> On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch  
>> wrote:
>> 
>> Chris Prakoso wrote:
>>>public bool UpdateData(string tableName,
>> Dictionary fields, List whereKeys)
>>>{
>>>...
>>>using (SQLiteTransaction transaction =
>> conn.BeginTransaction())
>>>{
>>>...
>>>rowsUpdated = cmd.ExecuteNonQuery();
>>>transaction.Commit();
>> 
>> Using one transaction for each statement is slow.
>> (Re-opening the database doesn't help either.)
>> 
>> You should use a single transaction around all update statements.
>> 
>> 
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPDATE silently failing

[Nelson, Erik - 2]

Hugues Bruant wrote on Wednesday, September 23, 2015 2:06 AM
> > > in some cases the SIndex captured inside the first lambda (UPDATE
> > > statement) appeared to be null even though it wasn't null in the
> > > enclosing scope (setVersion_)
> >
> > Interesting (and disturbing) result. Is this with Oracle's java
> compiler?
> >
> Compiler is Oracle JDK 8u40-b25 on OSX
> Runtime is OpenJDK 8u60-b04 built from source on OS X, Windows and
> Linux
> 
> It's pretty rare but damaging enough that I'm going to avoid capturing
> lambdas in critical code paths from now on.

Perhaps everyone is aware already, but it seems like at least sometimes lambdas 
expose underlying thread synchronization problems.

--
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] UPDATE silently failing

[Rowan Worth]

On 23 September 2015 at 12:32, Hugues Bruant  wrote:

> On Wed, Sep 23, 2015 at 12:00 AM, Rowan Worth  wrote:
>
> > Has many possible explanations.
>
> I can't think of any that is consistent with the Java specification.
>

Yeah fair enough, SIndex looks watertight. It's still possible for some
other code to change _i through reflection but (a) I presume you would know
if you were doing something like that and (b) it wouldn't explain the null
SIndex capture.

in some cases the SIndex captured inside the first lambda
> (UPDATE statement) appeared to be null even though it wasn't null in the
> enclosing scope (setVersion_)


Interesting (and disturbing) result. Is this with Oracle's java compiler?
-Rowan