Re: [sqlite] Readers Sharing Connection

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

Re: [sqlite] SQLite policy on compile warnings

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.

Re: [sqlite] Sudden error 26 / 11

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

[sqlite] Readers Sharing Connection

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

Re: [sqlite] Sudden error 26 / 11

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

Re: [sqlite] Sudden error 26 / 11

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

Re: [sqlite] update or replace ...

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

Re: [sqlite] update or replace ...

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

Re: [sqlite] update or replace ...

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

Re: [sqlite] update or replace ...

> 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

Re: [sqlite] update or replace ...

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

Re: [sqlite] update or replace ...

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

Re: [sqlite] update or replace ...

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

Re: [sqlite] update or replace ...

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.

Re: [sqlite] update or replace ...

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

Re: [sqlite] update or replace ...

> 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

Re: [sqlite] update or replace ...

> 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

Re: [sqlite] Bad db feature request

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

Re: [sqlite] update or replace ...

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

Re: [sqlite] Bad db feature request

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

Re: [sqlite] update or replace ...

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

Re: [sqlite] update or replace ...

> 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

Re: [sqlite] update or replace ...

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

Re: [sqlite] update or replace ...

> 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