Re: [sqlite] Readers Sharing Connection

[Igor Tandetnik]

On 6/30/2016 12:39 PM, Jarred Ford wrote:

How are multiple concurrent readers that share the same connection handled?


A connection has a mutex. Every API call locks it on entry and releases 
on exit.



Do I need to create a separate connection for each reader in order to keep 
these from running serially?


Not necessarily. Multiple readers on the same connection can interleave 
(if you have a SELECT statement, fetching each row and each value in a 
row is a separate API call, so multiple readers can take turns getting 
their rows). This may or may not be sufficient for your application. If 
you have a difficult query that takes a long time to produce a row, then 
all readers would be locked out while it runs. If you just scan tables 
with simple fast queries, then interleaving may be good enough.



Will writers on separate connections block the readers from running queries?


Yes in journal mode, no in WAL mode.


I'm using PRAGMA read_uncommitted = true


This is only meaningful when using shared cache mode ( 
https://www.sqlite.org/sharedcache.html ). It does nothing otherwise.

--
Igor Tandetnik

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

Re: [sqlite] SQLite policy on compile warnings

[Igor Korot]

Simon,

On Sun, Jun 26, 2016 at 12:16 AM, Igor Korot  wrote:
> Simon,
>
> On Sat, Jun 25, 2016 at 11:31 PM, Simon Slavin  wrote:
>>
>> On 26 Jun 2016, at 3:30am, Igor Korot  wrote:
>>
>>> ATM I'm using OSX 10.8 with Xcode 5.1.1. compiling everything for C++11
>>> and libc++.
>>
>> You should not be getting numerous warnings.  One or two, maybe.
>>
>> Make sure that Xcode knows that your .c file is C and not C++.  This is a 
>> big problem with Xcode for some reason.  It should know the difference 
>> between a .c and .cpp file but sometimes does not.  Check the build 
>> properties of the file inside Xcode.
>
> Since you have Mac I presume you know how to set the proper options
> inside the Xcode.
> Could you point them to me as I have no idea where they are and even
> less on how to set them.

Sorry for the long delay. I just got back to my Mac.

According to http://imgur.com/eWFzm9B the file type is set to "C".
Nevertheless, I have a number of warnings - see here: http://imgur.com/H8CQBX9

Am I doing something wrong?
I have Xcode 5.1.1 on OSX 10.8.

Let me know if you have a problem with the images.

Thank you.

>
> Thank you.
>
>>
>> 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] Sudden error 26 / 11

[Jim Borden]

The pragmas I am using are just journal mode WAL and the add on pragma from 
sqlcipher to set the key.  I am using threads fairly heavily (or rather C# is 
via Tasks and asynchronous ops), but here is the overall model:

Read connection (read only) shared between threads freely.  I can pump this 
through the single thread as well, but it will still suffer from an error in 
prepared statement handling (see below on write connection)
Write connection (also internal reads) pumped through a single thread for 
writes.  However, I realized that prepared statements were only being created 
on the special thread but being executed on others.

I read something that indicated that SQLite is never safe to be used from two 
threads at once, but it was labeled as outdated and seems to be contradicted by 
the newer information.

I am only using one process in the program.

Jim Borden
Software Engineer

jim.bor...@couchbase.com

On 6/30/16, 6:26 PM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
Simon Slavin"  wrote:

>
>On 30 Jun 2016, at 3:11pm, Jim Borden  wrote:
>
>> There were two instances of reported corruption today
>
>Are you using any pragmas ?  Are you using threads ?  Are you using more than 
>one process in one program ?
>
>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

[sqlite] Readers Sharing Connection

[Jarred Ford]

I'm using an in-memory sqlite db via c# with a shared cache , and I have a few 
questions about how certain operations are handled.


How are multiple concurrent readers that share the same connection handled?  Do 
I need to create a separate connection for each reader in order to keep these 
from running serially?


Will writers on separate connections block the readers from running queries?  
I'm using PRAGMA read_uncommitted = true, but it still seems that queries are 
getting blocked intermittently.


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

Re: [sqlite] Sudden error 26 / 11

[Simon Slavin]

On 30 Jun 2016, at 3:11pm, Jim Borden  wrote:

> There were two instances of reported corruption today

Are you using any pragmas ?  Are you using threads ?  Are you using more than 
one process in one program ?

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

Re: [sqlite] Sudden error 26 / 11

[Jim Borden]

Hello all,

Sorry for the delay.  It’s really hard to get this error to happen but I have 
some more relevant information.

I observed a read only connection (there are two connections open to the DB, 
one for reading only and one for writing / internal reading) suddenly start 
returning error code 26, and then error code 11 for a query so I pulled the 
database file.  It’s encrypted with SQLCipher, but I opened it with the 
sqlcipher command line and ran PRAGMA quick_check.  Normally when a corruption 
occurs this will show me what happened, but in this case it returned “ok.”  
However, the query results never ended.  I killed the process when it passed 2 
GB of output from a 6 MB database from a SELECT query.  This seems like 
corruption that the PRAGMA missed.  SELECT

One thing to note that I found was at one point during the testing today I 
found that sqlite3_prepare_v2 crashed while attempting to access invalid 
memory.  This led me to research prepared statements and find that operations 
on them are not actually thread safe.  So I will be changing the write 
connection mode to serialized and testing further but I wanted to report on the 
odd behavior I found above.  

I’m having trouble getting any output from the sqlite error log since this is a 
C# application.  I have written a P Invoke binding to the method but I’m unsure 
if it is succeeding or not.  How often should I expect output from it?  Is 
there a way I can force it to output something to test it?

As for the second suggestion, I am able to open the database with the sqlcipher 
command line so I assume that the database header is intact.  There were two 
instances of reported corruption today (perhaps three if I interpret the 
invalid memory access as the same problem showing a different result).  One 
showed “bTreeInitPage() returned error code 11” for some page in the 5000 range 
(this is usually the error I get, with various pages being listed as the bad 
ones between runs) and the other showed “ok” but had the never ending SELECT 
output on certain queries.

Please let me know if anything comes to mind.  

Jim Borden
Software Engineer

jim.bor...@couchbase.com

On 6/24/16, 4:25 AM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
Richard Hipp"  wrote:

>On Thu, Jun 23, 2016 at 6:13 PM, Jim Borden  wrote:
>> The library will be happily running along and then suddenly a SELECT 
>> statement will return error code 26 upon step.
>
>Error code 26 is SQLITE_NOTADB.  That only happens when SQLite is
>reading the 100-byte header at the beginning of the database file and
>finds that header is correct.  This is given a discinct error code
>because usually that finding means that you are trying to open
>something that is not really an SQLite database file.
>
>Debugging hints:
>
>(1) Set error logging on your application:  https://www.sqlite.org/errlog.html
>
>(2) Compile the "showdb" utility program (using "make showdb") and
>then run "showdb DATABASE dbheader" on a corrupt database file.  Send
>the results to this list.  (There are many other useful run-time
>options for "showdb" that you might also find useful.)
>
>
>>
>>
>> This connection is in “multi thread” mode I think (compiled with thread 
>> safety on but opened with NO_MUTEX).
>
>Does the problem go away if you se the threading mode to "serialized"?
>
>-- 
>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-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] Bad db feature request

[Eduardo Morras]

On Thu, 30 Jun 2016 10:12:05 +0100
Paul Sanderson  wrote:

> The OP wanted something to check his systems for corruption - if the
> SQLite dev team don't want to add checksums then the OP could possibly
> solve his own problem by adding them to his own internal version.
> 
> Extension may have been a bad choice of word - he can modify/compile
> his own version of SQLite add checksums - and also add a corruption
> engine if he wants.
> Paul

Your right

A simple trigger on insert/update a row to calculate a crc or hash of other 
columns content in the row and update a hidden column with the value can do the 
trick at row level. It will make performance lower, undesired but expected 
effect.

---   ---
Eduardo Morras 
___
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] Bad db feature request

[Paul Sanderson]

The OP wanted something to check his systems for corruption - if the
SQLite dev team don't want to add checksums then the OP could possibly
solve his own problem by adding them to his own internal version.

Extension may have been a bad choice of word - he can modify/compile
his own version of SQLite add checksums - and also add a corruption
engine if he wants.
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 29 June 2016 at 22:10, Dominique Devienne  wrote:
> On Wed, Jun 29, 2016 at 9:54 PM, Paul Sanderson <
> sandersonforens...@gmail.com> wrote:
>
>> As mentioned above there is (or can be) reserved space at the end of
>> each page (documented in the DB header) that can be used for checksums
>> - you just need to write your own extension :)
>>
>> https://www.sqlite.org/fileformat2.html
>
>
> Can you really? What kind of extension?
>
> The pager is not an extension point.
>
> The VFL is one, but https://www.sqlite.org/c3ref/io_methods.html is byte
> oriented, not page oriented.
> Plus there are different types of pages, with different format. Does the
> checksum go in the same place for all them?
> Official SQLite would ignore the extra info, and probably either not
> rewrite it, or keep it as-is, when modifying a page,
> making the page appear invalid. This is precisely the kind of things that
> if not built in is kinda useless I'm afraid.
>
> Plus w/o a format change, even if built-in to the latest SQLite, older
> SQLite apps wouldn't keep the checksums up-to-date too.
> This and index-based-row-counts and probably other things do require a
> backward-incompatible change IMHO. --DD
> ___
> 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 ...

[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