[sqlite] Malformed databases and multithreading

[Paul Egli]

Main question/comment:
-

On the "how to corrupt" page ( http://sqlite.org/howtocorrupt.html ) i
do not see any mention of using SQLite in an incorrect way with
respect to thread safety. Is there really no way that, for example,
using the same connection on multiple threads at the same time could
either (a) corrupt the database, or (b) lead to a false reports of
corruption?

(Even if such a misuse of SQLite falls under one of the categories
already listed on the page, perhaps said category could be updated to
explicitly tell about the risk of misusing SQLite in this way...
perhaps a "Section 2.6"... or 5.1 or 7.1).

Thoughts?

-P



P.S./Appendix: Further background info (probably TMI)...
-

We are trying to help a client debug some issues in a Xamarin-based
app that is deployed to both Android and iOS. Some of the errors they
are seeing are "malformed database".

They are still building in a way that uses the platform-provided
SQLite on both platforms (no, this will not work on Android N) and no
additional SQLite library is linked in, so they should not be having
the "Multiple copies of SQLite linked into the same application"
problem.

They also routinely see "cannot start a transaction within a
transaction" even though there is no obvious place where or reason why
that would happen in their code. This leads us to believe they may be
using the same connection on multiple threads.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[Paul Egli]

On Tue, Dec 13, 2016 at 9:56 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 13 Dec 2016, at 3:53pm, Paul Egli <e...@sourcegear.com> wrote:
>
> > Well if i am missing something, i still cannot see it.
> >
> > Based on these quotes in the docs, i assume that a NULL in the child
> table
> > means that it does not have a parent.
>
> You are correct.  I missed that.
>
> So yes, the original poster was correct, and using an index which left out
> the NULL key values would lead to the right results.  Should be possible to
> use that for an optimization case.  I suppose it might lead to quite a
> saving in filespace for some tables.
>


Great. Just to clarify, SQLite will already use "alternateChildIndex1" from
the example? Or just that it would be possible as an enhancement request?

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

Re: [sqlite] Foreign key child index question

[Paul Egli]

On Tue, Dec 13, 2016 at 9:41 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 13 Dec 2016, at 3:20pm, Paul Egli <e...@sourcegear.com> wrote:
>
> > Why must SQLite find rows where the value is NULL?
>
> Because the related fields in the offspring row might have NULL in them,
> and SQLite needs to know how to find the parent row for that row.
>
>
Well if i am missing something, i still cannot see it.

Based on these quotes in the docs, i assume that a NULL in the child table
means that it does not have a parent.

"All foreign key constraints in SQLite are handled as if MATCH SIMPLE were
specified."

"If "MATCH SIMPLE" is specified, then a child key is not required to
correspond to any row of the parent table if one or more of the child key
values are NULL."

Therefore SQLite would *not *need to find a parent for that row.

So i guess i am still hoping that, if possible, SQLite could be enhanced as
David's question was getting at. :-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[Paul Egli]

On Wed, Dec 7, 2016 at 3:11 PM, Simon Slavin  wrote:

>
> On 7 Dec 2016, at 8:40pm, David Raymond  wrote:
>
> > Question on making indexes for the child fields of foreign keys. I have
> a child table with a number of foreign keys on fields which the majority of
> the time are null. I've currently got indexes on the child fields for the
> purposes of speeding up the foreign key checks, but what I'm wondering is
> if I used a conditional index which has "where fkField is not null", will
> that index be usable by the internal foreign key checker?
>
> Sorry, but the answer is no.  SQLite needs to be able to find the rows
> where the key-value is NULL.  It can’t do that from an index which doesn’t
> include those rows.
>
>
Why must SQLite find rows where the value is NULL? To me, this sounded like
a good enhancement request if it's not already the case. Am i missing
something?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing ID's to UUID

[Paul Egli]

On Wed, Nov 16, 2016 at 7:59 AM, Keith Medcalf  wrote:

> Using the systemid sequence and the recordid sequence directly however,
> has a 0% probability of collision, so any rational person would use that
> directly and forgo entirely the introduction of uncertainty and bugs using
> "UUID" type crappola will cause.
>

As Dominique said, the issue here is decentralization... and i would add,
particularly in a disconnected environment and/or one with no central
authority. The method you describe does not handle device rollbacks or
cloning.

For example, one of your systems is a mobile device with it's own unique
system id. Periodically, this device broadcasts its inserted data to other
devices. Also, the user backs up the device to their PC every now and then.
At some point the mobile device gets lost or damaged. When they restore
from backup, the last few sequential ids from that system id get reused and
collide. It is also possible to restore from backup to a different device,
even if the original is still alive and well, at which point you have two
different devices with the same system id broadcasting colliding keys.

Theoretically a new, unique system id should be generated any time a system
is backed up or copied anywhere. But when the backup/copying logic is
completely independent and unknowing of your systemid, you are left with
needing to detect if the physical device has changed. This may be
unreliable or impossible on some platforms. And i don't think it would be
possible to detect the case where a rollback happened on the same physical
device.
___
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