Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Darko Volaric]

What about invalid and reused MAC addresses and devices with no MAC address at 
all?
What about time resets to the epoch which are not restored, user time changes, 
daylight saving or leap seconds?

It sounds even more probabilistic than the probabilistic methods. Does anyone 
actually use it?


> On Nov 25, 2017, at 4:54 AM, J. King  wrote:
> 
> Version 1 UUIDs only use a random number (16 bits) in the case of an 
> uninitialized clock sequence (a case which, ideally, should only occur the 
> first time a device generates a UUID). Version 1 UUIDs especially avoid using 
> random numbers; they are also not a shortening of longer input. 
> 
> In short, version 1 UUIDs are not a PRNG scheme, nor are they the same as 
> hash functions. 
> 
> I'm not a mathematician, and it's been a while since I've read the relevant 
> RFC, but I believe collisions in a proper, strict implementation of V1 UUIDs 
> are impossible by design until either a) the 60-bit timestamp overflows, or 
> b) the MAC address namespace is exhausted. It's not a matter of probability, 
> and it's only "a certainty" after the end of their design lifetime. 
> 
> Of course, UUIDs being of finite size, they will eventually be exhausted, and 
> a single machine may only generate 65536 identifiers in a 100-nanosecond span 
> of time. They will not, however, collide. 
> 
> On November 24, 2017 8:58:02 PM EST, Jean-Christophe Deschamps 
>  wrote:
>> 
>> At 23:49 24/11/2017, you wrote:
>> 
>>> On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote:
>>> 
 At 22:38 24/11/2017, you wrote:
> One proof of the falsehood of your assertion is that we CAN fill a 
> database with some data using UIDs, and we will almost certainly not
>> 
> get a collision, while you assertion we will.
>>> 
 This is an attempt at "proof by example". Keith is perfectly right 
 --mathematically speaking-- and your "proof" doesn't hold water, I 
 mean as a formal proof.  The best proof that your "proof" isn't a 
 proof is that you feel obliged  to add "almost certainly".
>>> 
>>> DISproof by example is a perfectly valid method. If someone makes a 
>>> claim that something is ALWAYS true, ONE counter example IS a 
>>> disproof. I said almost certainly as the chance of a collision isn't 0
>> 
>>> (to be able to say with certainty) but is most defintely less than the
>> 
>>> 100% claimed.
>> 
>> You're confusing one mathematical theorem and one practical statement. 
>> The first is the _mathematical_ fact that any PRNG (using any fixed 
>> number of random bits, which is what xUIDs are) will provide an 
>> infinite number of collisions with probability 1. This is definitely 
>> true. Of course here, the number of samples is implicitely infinite.
>> 
>> Your practical statement is that you can "most certainly" ignore the 
>> possibility of collision when feeding 2^N xUIDs into a unique column 
>> without loosing sleep. That's good enough in practice. The issue with 
>> your "demonstration" is that 2^N is bounded, whatever finite N you 
>> choose. Hence you don't contradict what Keith said, you just say 
>> something different applying to restricted cases. You're speaking about
>> 
>> practice, while Keith told about math. You're both right, each from his
>> 
>> own point of view. But you can't claim to disproof a trivially true 
>> theorem this way, by changing its premices.
>> 
>> An event with probability 10^-10...000 (any finite number of 
>> zeroes) will occur at least once, provided you run enough tries. It'll 
>> occur an infinite number of times if you run an infinite number of 
>> tries. Else its probability would be zero.
>> Your "disproof" amounts to say that 10^-10...000 = 0
>> 
>> And neither Keith nor I ever said that an xUID collision will occur 
>> with probability 1 after 2^64 samples. That would be false and that's 
>> why people feel free to use xUIDs _AND_ sleep quietly.
>> 
>> JcD
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> -- 
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> ___
> 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] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Keith Medcalf]

Only if you assume a monotonic clock ...


---
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 J. King
>Sent: Friday, 24 November, 2017 20:54
>To: SQLite mailing list
>Subject: Re: [sqlite] "BIGINT" doesn't act like INTEGER on table
>creation [Bug]
>
>Version 1 UUIDs only use a random number (16 bits) in the case of an
>uninitialized clock sequence (a case which, ideally, should only
>occur the first time a device generates a UUID). Version 1 UUIDs
>especially avoid using random numbers; they are also not a shortening
>of longer input.
>
>In short, version 1 UUIDs are not a PRNG scheme, nor are they the
>same as hash functions.
>
>I'm not a mathematician, and it's been a while since I've read the
>relevant RFC, but I believe collisions in a proper, strict
>implementation of V1 UUIDs are impossible by design until either a)
>the 60-bit timestamp overflows, or b) the MAC address namespace is
>exhausted. It's not a matter of probability, and it's only "a
>certainty" after the end of their design lifetime.
>
>Of course, UUIDs being of finite size, they will eventually be
>exhausted, and a single machine may only generate 65536 identifiers
>in a 100-nanosecond span of time. They will not, however, collide.
>
>On November 24, 2017 8:58:02 PM EST, Jean-Christophe Deschamps
> wrote:
>>
>>At 23:49 24/11/2017, you wrote:
>>
>>>On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote:
>>>
At 22:38 24/11/2017, you wrote:
>One proof of the falsehood of your assertion is that we CAN fill
>a
>database with some data using UIDs, and we will almost certainly
>not
>>
>get a collision, while you assertion we will.
>>>
This is an attempt at "proof by example". Keith is perfectly right
--mathematically speaking-- and your "proof" doesn't hold water, I
mean as a formal proof.  The best proof that your "proof" isn't a
proof is that you feel obliged  to add "almost certainly".
>>>
>>>DISproof by example is a perfectly valid method. If someone makes a
>>>claim that something is ALWAYS true, ONE counter example IS a
>>>disproof. I said almost certainly as the chance of a collision
>isn't 0
>>
>>>(to be able to say with certainty) but is most defintely less than
>the
>>
>>>100% claimed.
>>
>>You're confusing one mathematical theorem and one practical
>statement.
>>The first is the _mathematical_ fact that any PRNG (using any fixed
>>number of random bits, which is what xUIDs are) will provide an
>>infinite number of collisions with probability 1. This is definitely
>>true. Of course here, the number of samples is implicitely infinite.
>>
>>Your practical statement is that you can "most certainly" ignore the
>>possibility of collision when feeding 2^N xUIDs into a unique column
>>without loosing sleep. That's good enough in practice. The issue
>with
>>your "demonstration" is that 2^N is bounded, whatever finite N you
>>choose. Hence you don't contradict what Keith said, you just say
>>something different applying to restricted cases. You're speaking
>about
>>
>>practice, while Keith told about math. You're both right, each from
>his
>>
>>own point of view. But you can't claim to disproof a trivially true
>>theorem this way, by changing its premices.
>>
>>An event with probability 10^-10...000 (any finite number of
>>zeroes) will occur at least once, provided you run enough tries.
>It'll
>>occur an infinite number of times if you run an infinite number of
>>tries. Else its probability would be zero.
>>Your "disproof" amounts to say that 10^-10...000 = 0
>>
>>And neither Keith nor I ever said that an xUID collision will occur
>>with probability 1 after 2^64 samples. That would be false and
>that's
>>why people feel free to use xUIDs _AND_ sleep quietly.
>>
>>JcD
>>
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>--
>Sent from my Android device with K-9 Mail. Please excuse my brevity.
>___
>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] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[J. King]

Peter (that is, the other Peter) is being more than a little flippant, yes, and 
at least a little obscure (I don't get the joke, either), but the substance 
appears quite serious. 

His prior message suggested using what I can only assume would be a trivial 
extension to SQLite to do what you want to do---this extension, however, does 
not (already) exist. 

His last message provided what I can only assume is an example implementation 
of such an extension. 

On November 24, 2017 11:20:33 PM EST, Peter Halasz  
wrote:
>Is this a joke?
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Peter Halasz]

Is this a joke?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[petern]

Well, first you imagine you're back at the office in Santa Clara or Redmond
in the early 1990's.
Then take a belt of whisky, cross your eyes, and paste from doc to
clipboard a few edits and voila:

#include "sqlite3ext.h"
#include 
SQLITE_EXTENSION_INIT1
static struct metadata {
  char const *zDataType; /* OUTPUT: Declared data type */
  char const *zCollSeq; /* OUTPUT: Collation sequence name */
  int NotNull; /* OUTPUT: True if NOT NULL constraint exists */
  int PrimaryKey; /* OUTPUT: True if column part of PK */
  int Autoinc;/* OUTPUT: True if column is auto-increment */
} md;
static void initmd(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  sqlite3_table_column_metadata(
sqlite3_context_db_handle(context), /* Connection handle */
0, /* Database name or NULL */
(char const*)sqlite3_value_text(argv[0]), /* Table name */
(char const*)sqlite3_value_text(argv[1]), /* Column name */
, /* OUTPUT: Declared data type */
, /* OUTPUT: Collation sequence name */
, /* OUTPUT: True if NOT NULL constraint exists */
, /* OUTPUT: True if column part of PK */
/* OUTPUT: True if column is auto-increment */
);
}
static void collseq(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  initmd(context,argc,argv);
  if (!md.zCollSeq) return;
  sqlite3_result_text(context, md.zCollSeq, strlen(md.zCollSeq),
SQLITE_TRANSIENT);
}
static void autoinc(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  initmd(context,argc,argv);
  sqlite3_result_int(context, md.Autoinc);
}
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_colmetadata_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  int rc = sqlite3_create_function(db, "collseq", 2, SQLITE_UTF8, 0,
collseq, 0, 0);
  if (SQLITE_OK == rc) sqlite3_create_function(db, "autoinc", 2,
SQLITE_UTF8, 0, autoinc, 0, 0);
  return rc;
}

Linux box compile is something like this:

gcc -I -fPIC -lm -shared colmetadata.c -o colmetadata.so

Then test it out:

sqlite> .load colmetadata.so
sqlite> SELECT
*,collseq('sqlite_master',name)collseq,autoinc('sqlite_master',name)autoinc
FROM pragma_table_info('sqlite_master');
cid,name,type,notnull,dflt_value,pk,collseq,autoinc
0,type,text,0,,0,BINARY,0
1,name,text,0,,0,BINARY,0
2,tbl_name,text,0,,0,BINARY,0
3,rootpage,integer,0,,0,BINARY,0
4,sql,text,0,,0,BINARY,0

https://www.sqlite.org/c3ref/table_column_metadata.html

So armed with the above document and newly exposed pk,collseq, and autoinc
info you can deduce the rowid aliases.
Feel free to add more functions and clean up the error handling to suit
your needs.










On Fri, Nov 24, 2017 at 5:57 PM, Peter Halasz 
wrote:

> > sqlite> .load column-meta-data.so
>
> Sorry I'm at a loss to find this extension? Google gives me nothing related
> to SQLite for "isRowId", "column-meta-data.so", "column-meta-data.c", etc.
> ___
> 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] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[J. King]

Version 1 UUIDs only use a random number (16 bits) in the case of an 
uninitialized clock sequence (a case which, ideally, should only occur the 
first time a device generates a UUID). Version 1 UUIDs especially avoid using 
random numbers; they are also not a shortening of longer input. 

In short, version 1 UUIDs are not a PRNG scheme, nor are they the same as hash 
functions. 

I'm not a mathematician, and it's been a while since I've read the relevant 
RFC, but I believe collisions in a proper, strict implementation of V1 UUIDs 
are impossible by design until either a) the 60-bit timestamp overflows, or b) 
the MAC address namespace is exhausted. It's not a matter of probability, and 
it's only "a certainty" after the end of their design lifetime. 

Of course, UUIDs being of finite size, they will eventually be exhausted, and a 
single machine may only generate 65536 identifiers in a 100-nanosecond span of 
time. They will not, however, collide. 

On November 24, 2017 8:58:02 PM EST, Jean-Christophe Deschamps 
 wrote:
>
>At 23:49 24/11/2017, you wrote:
>
>>On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote:
>>
>>>At 22:38 24/11/2017, you wrote:
One proof of the falsehood of your assertion is that we CAN fill a 
database with some data using UIDs, and we will almost certainly not
>
get a collision, while you assertion we will.
>>
>>>This is an attempt at "proof by example". Keith is perfectly right 
>>>--mathematically speaking-- and your "proof" doesn't hold water, I 
>>>mean as a formal proof.  The best proof that your "proof" isn't a 
>>>proof is that you feel obliged  to add "almost certainly".
>>
>>DISproof by example is a perfectly valid method. If someone makes a 
>>claim that something is ALWAYS true, ONE counter example IS a 
>>disproof. I said almost certainly as the chance of a collision isn't 0
>
>>(to be able to say with certainty) but is most defintely less than the
>
>>100% claimed.
>
>You're confusing one mathematical theorem and one practical statement. 
>The first is the _mathematical_ fact that any PRNG (using any fixed 
>number of random bits, which is what xUIDs are) will provide an 
>infinite number of collisions with probability 1. This is definitely 
>true. Of course here, the number of samples is implicitely infinite.
>
>Your practical statement is that you can "most certainly" ignore the 
>possibility of collision when feeding 2^N xUIDs into a unique column 
>without loosing sleep. That's good enough in practice. The issue with 
>your "demonstration" is that 2^N is bounded, whatever finite N you 
>choose. Hence you don't contradict what Keith said, you just say 
>something different applying to restricted cases. You're speaking about
>
>practice, while Keith told about math. You're both right, each from his
>
>own point of view. But you can't claim to disproof a trivially true 
>theorem this way, by changing its premices.
>
>An event with probability 10^-10...000 (any finite number of 
>zeroes) will occur at least once, provided you run enough tries. It'll 
>occur an infinite number of times if you run an infinite number of 
>tries. Else its probability would be zero.
>Your "disproof" amounts to say that 10^-10...000 = 0
>
>And neither Keith nor I ever said that an xUID collision will occur 
>with probability 1 after 2^64 samples. That would be false and that's 
>why people feel free to use xUIDs _AND_ sleep quietly.
>
>JcD
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Richard Damon]

On 11/24/17 8:58 PM, Jean-Christophe Deschamps wrote:


At 23:49 24/11/2017, you wrote:


On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote:


At 22:38 24/11/2017, you wrote:
One proof of the falsehood of your assertion is that we CAN fill a 
database with some data using UIDs, and we will almost certainly 
not get a collision, while you assertion we will.


This is an attempt at "proof by example". Keith is perfectly right 
--mathematically speaking-- and your "proof" doesn't hold water, I 
mean as a formal proof.  The best proof that your "proof" isn't a 
proof is that you feel obliged  to add "almost certainly".


DISproof by example is a perfectly valid method. If someone makes a 
claim that something is ALWAYS true, ONE counter example IS a 
disproof. I said almost certainly as the chance of a collision isn't 
0 (to be able to say with certainty) but is most defintely less than 
the 100% claimed.


You're confusing one mathematical theorem and one practical statement. 
The first is the _mathematical_ fact that any PRNG (using any fixed 
number of random bits, which is what xUIDs are) will provide an 
infinite number of collisions with probability 1. This is definitely 
true. Of course here, the number of samples is implicitely infinite.


Your practical statement is that you can "most certainly" ignore the 
possibility of collision when feeding 2^N xUIDs into a unique column 
without loosing sleep. That's good enough in practice. The issue with 
your "demonstration" is that 2^N is bounded, whatever finite N you 
choose. Hence you don't contradict what Keith said, you just say 
something different applying to restricted cases. You're speaking 
about practice, while Keith told about math. You're both right, each 
from his own point of view. But you can't claim to disproof a 
trivially true theorem this way, by changing its premices.


An event with probability 10^-10...000 (any finite number of 
zeroes) will occur at least once, provided you run enough tries. It'll 
occur an infinite number of times if you run an infinite number of 
tries. Else its probability would be zero.

Your "disproof" amounts to say that 10^-10...000 = 0

And neither Keith nor I ever said that an xUID collision will occur 
with probability 1 after 2^64 samples. That would be false and that's 
why people feel free to use xUIDs _AND_ sleep quietly.


JcD

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


The statement, and I quote, was

Actually a UUID or a GUID has a 100% certainty of a collision, not just 
a possibility of a collision. Just as all hash algorithms which take 
something and generate a shorter "hash" or "checksum" will always have 
collisions. Without exception and as an absolute 100% certainty. There 
is no way to avoid this mathematical certainty.


The claim makes a positive statement of a 100% chance of collision, and 
makes an argument about it being a hash, and that it isn't just a chance.


Something happening at least one in an extremely large number of trials 
was NEVER a 100% probability in any form of math I have heard of. The 
number of records needed to have a significant chance of the collision, 
is beyond any practical usage. If the claim was a non-zero chance, then 
it would be true. Probability has always been related to times happened 
/ times tried. So your claim is that 10^-1...  == 1, saying 
something will EVENTUAL happen is a claim on non-zero probability, not 
of 100% probability.


If you mean that a finite width field can't hold unique values for an 
infinite of records, well, duh, why all the irrelevant references to 
hashes or even that it is a UUID or a GUID.


If it was meant that EVENTUAL, after an impossibly long time, you will 
get a collision, that is being absurd. I can say with better certainty 
that long before that happens, the computers running this database are 
going to break, so we will never get to the point of the collision.


--
Richard Damon

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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Keith Medcalf]

Actually, it is entirely possible to generate two and only two random xUID side 
by each and have them be duplicates.  Such is the nature of randomness.  

The only way to ensure that there is no collisions is to check whether the xUID 
is already in use/seen within the domain where it is used.

A rowid generated by the ordinary method (if no records then 1 else 
max(rowid)+1) cannot have a key collision within its domain.

---
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 Richard Damon
>Sent: Friday, 24 November, 2017 14:39
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] "BIGINT" doesn't act like INTEGER on table
>creation [Bug]
>
>On 11/24/17 2:25 PM, Keith Medcalf wrote:
>> Actually a UUID or a GUID has a 100% certainty of a collision,  not
>just a possibility of a collision.  Just as all hash algorithms which
>take something and generate a shorter "hash" or "checksum" will
>always have collisions.  Without exception and as an absolute 100%
>certainty.  There is no way to avoid this mathematical certainty.
>>
>> However, whether the absolute and unmitigatable certainty of a
>collision is of any import or not is an entirely different matter.
>>
>Absolutely incorrect, for a UID. Perhaps if you are talking about
>actual
>hashes, you can say that there are always multiple (at least
>potential)
>messages that will generate the same hash value (but for a good hash,
>the likelihood that one of them is sensible or even actually
>generated
>is minuscule). for a UID, while they are typically created by a hash
>of
>various information, including something that varies each time a
>given
>generator is used, what those inputs actually are is generally
>unimportant, but are mostly provided to help make the 'randomness' of
>the choice more 'random'. We are never really concerned with
>'potential'
>collisions, only actual collisions.
>
>One proof of the falsehood of your assertion is that we CAN fill a
>database with some data using UIDs, and we will almost certainly not
>get
>a collision, while you assertion we will.
>
>Yes, there is a limit on how many entries we can generate before we
>will
>likely, or even certainly hit a collision, but if that number is
>significantly higher than the number of records we will generate (or
>even CAN generate), we can assume relative safety. With a 128 bit
>UID,
>the approximate point we need to worry about random collisions is the
>order of 2^64, I suspect that creating an SQLite database with 2^64
>non-trivial records in a single table is likely going to have other
>issues besides unique key collisions.
>
>--
>Richard Damon
>
>___
>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] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Jean-Christophe Deschamps]

At 00:13 25/11/2017, you wrote:

Looks like I do need to use AUTOINCREMENT after all, otherwise the
framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY
columns are allowed a NULL value on insert.


I can't answer about just INTEGER PRIMARY KEY columns, but any table 
with an AUTOINCREMENT column has an entry in table sqlite_sequence, 
something much easier to deal with than digging into sqlite_master.


JcD

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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Jean-Christophe Deschamps]

At 23:49 24/11/2017, you wrote:


On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote:


At 22:38 24/11/2017, you wrote:
One proof of the falsehood of your assertion is that we CAN fill a 
database with some data using UIDs, and we will almost certainly not 
get a collision, while you assertion we will.


This is an attempt at "proof by example". Keith is perfectly right 
--mathematically speaking-- and your "proof" doesn't hold water, I 
mean as a formal proof.  The best proof that your "proof" isn't a 
proof is that you feel obliged  to add "almost certainly".


DISproof by example is a perfectly valid method. If someone makes a 
claim that something is ALWAYS true, ONE counter example IS a 
disproof. I said almost certainly as the chance of a collision isn't 0 
(to be able to say with certainty) but is most defintely less than the 
100% claimed.


You're confusing one mathematical theorem and one practical statement. 
The first is the _mathematical_ fact that any PRNG (using any fixed 
number of random bits, which is what xUIDs are) will provide an 
infinite number of collisions with probability 1. This is definitely 
true. Of course here, the number of samples is implicitely infinite.


Your practical statement is that you can "most certainly" ignore the 
possibility of collision when feeding 2^N xUIDs into a unique column 
without loosing sleep. That's good enough in practice. The issue with 
your "demonstration" is that 2^N is bounded, whatever finite N you 
choose. Hence you don't contradict what Keith said, you just say 
something different applying to restricted cases. You're speaking about 
practice, while Keith told about math. You're both right, each from his 
own point of view. But you can't claim to disproof a trivially true 
theorem this way, by changing its premices.


An event with probability 10^-10...000 (any finite number of 
zeroes) will occur at least once, provided you run enough tries. It'll 
occur an infinite number of times if you run an infinite number of 
tries. Else its probability would be zero.

Your "disproof" amounts to say that 10^-10...000 = 0

And neither Keith nor I ever said that an xUID collision will occur 
with probability 1 after 2^64 samples. That would be false and that's 
why people feel free to use xUIDs _AND_ sleep quietly.


JcD

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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Peter Halasz]

> sqlite> .load column-meta-data.so

Sorry I'm at a loss to find this extension? Google gives me nothing related
to SQLite for "isRowId", "column-meta-data.so", "column-meta-data.c", etc.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[petern]

Apparently you would query pragma_table_info for "INTEGER" PK columns and
then ask through a trivial extension function about the other column
meta-data:

https://www.sqlite.org/c3ref/table_column_metadata.html

The basic plot is illustrated below:

sqlite> .load column-meta-data.so
sqlite> SELECT *,isRowId(name) FROM pragma_table_info('the_table') WHERE pk
AND type='INTEGER'

cid,name,type,notnull,dflt_value,pk,"isRowId('the_table',name)"
...1
...etc

Your only serious problem, I think, would be to distribute the
column-meta-data.c extension with your tool.



On Fri, Nov 24, 2017 at 3:13 PM, Peter Halasz 
wrote:

> Sorry to steer the conversation back to the topic.
>
> Looks like I do need to use AUTOINCREMENT after all, otherwise the
> framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY
> columns are allowed a NULL value on insert. That's a shame.
>
> Is there a way to query a schema to get a direct answer to whether table
> has a column which acts as the ROW ID alias? I'd like to improve linq2db's
> code generation if I can, but I can't see any straightforward way to do it
> as the ROW ID information is hidden (in SQLite's API, not just its
> documentation)
>
> I found someone asking the same question on this mailing list in 2010 who
> was told to: [1]
>
> "look at column 'sql' of TABLE sqlite_master and parse the creation
> statement"
>
> Which is frankly ridiculous.
>
> I'm hoping there's an actual answer 7 years later that doesn't involve
> implementing a SQL parser? (* please *don't even make suggestions on how to
> do this)
>
> I can't find anything in the pragma documentation. [2] The closest I could
> see is "*PRAGMA **index_xinfo(**index-name**);*" which will give
> information on whether an index references a rowid. I tried creating an
> index for a column just to test if it was the ROW ID but it doesn't
> actually work that way.
>
> On the page https://www.sqlite.org/rowidtable.html it states:
>
>-
>
>All of the complications above (and others not mentioned here) arise
>from the need to preserve backwards compatibility to the tens of
> billions
>of SQLite database files in circulation. In a perfect world, there
> would be
>no such thing as a "rowid" and all tables would following the standard
>semantics implemented as WITHOUT ROWID tables, only without the extra
>"WITHOUT ROWID" keywords. Unfortunately, life is messy. The designer of
>SQLite offers his sincere apology for the current mess.
>
> So it appears ROWID is sticking around. Why is it hidden in the
> documentation (not listed on on pages like the datatype page), and hidden
> in the pragma interface as well?
>
> Is there an API call or PRAGMA statement that gives this information
> directly?
>
> Thanks.
>
>
> [1]
> http://sqlite.1065341.n5.nabble.com/Introspection-and-
> RowID-INTEGER-PRIMARY-KEY-td60462.html
> [2] https://sqlite.org/pragma.html
> ___
> 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] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[J Decker]

On Fri, Nov 24, 2017 at 3:13 PM, Peter Halasz 
wrote:

> Sorry to steer the conversation back to the topic.
>
> Looks like I do need to use AUTOINCREMENT after all, otherwise the
> framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY
> columns are allowed a NULL value on insert. That's a shame.
>
> Is there a way to query a schema to get a direct answer to whether table
> has a column which acts as the ROW ID alias? I'd like to improve linq2db's
> code generation if I can, but I can't see any straightforward way to do it
> as the ROW ID information is hidden (in SQLite's API, not just its
> documentation)
>
> I found someone asking the same question on this mailing list in 2010 who
> was told to: [1]
>
> "look at column 'sql' of TABLE sqlite_master and parse the creation
> statement"
>
> Which is frankly ridiculous.
>

*shrug* it probably is ridiculous, but I did anyway  XDataTable extends
DataTable and adds foriegn key relations; otherwise you need the full
DataSet to deal with some table-oriented things (like foreign keys)
https://sourceforge.net/p/xperdex/mercurial/ci/default/tree/xperdex.classes.2/XDataTable.cs

This is a parser that can either generate a create table statement from a
datatable, or a datatable from a create table statement.  (Or merge a data
table filled with existing columns and merge it with what's already in a
database)

https://sourceforge.net/p/xperdex/mercurial/ci/default/tree/xperdex.classes.2/SQL_Utilities.cs

I use this to parse the create table statement into reasonable tokens.
https://sourceforge.net/p/xperdex/mercurial/ci/default/tree/xperdex.classes.2/Types/XString.cs

The above can probably be disected from the full surrounding library with a
little work... Some features of XDataTable should probably be removed...
the first version automatically synced to the database when rows were
added/deleted/modified I eventually moved that behind an option 'live'
that if 'live' do the inserts directly; otherwise wait, and then later, a
full dataset (XDataSet) can sync all changes in a dataset in appropriate
order so foriegn key parent keys are inserted first.

It has a personality selection also, based on information from
DsnConnection class, which tells it whether it is SQL Server, MySQL or
Sqlite, so generation of types for columns in the database form the types
in the DataTable change depending on the flavor of database, and
constraint/foriegn key generation changes also




> I'm hoping there's an actual answer 7 years later that doesn't involve
> implementing a SQL parser? (* please *don't even make suggestions on how to
> do this)
>
> I can't find anything in the pragma documentation. [2] The closest I could
> see is "*PRAGMA **index_xinfo(**index-name**);*" which will give
> information on whether an index references a rowid. I tried creating an
> index for a column just to test if it was the ROW ID but it doesn't
> actually work that way.
>
> On the page https://www.sqlite.org/rowidtable.html it states:
>
>-
>
>All of the complications above (and others not mentioned here) arise
>from the need to preserve backwards compatibility to the tens of
> billions
>of SQLite database files in circulation. In a perfect world, there
> would be
>no such thing as a "rowid" and all tables would following the standard
>semantics implemented as WITHOUT ROWID tables, only without the extra
>"WITHOUT ROWID" keywords. Unfortunately, life is messy. The designer of
>SQLite offers his sincere apology for the current mess.
>
> So it appears ROWID is sticking around. Why is it hidden in the
> documentation (not listed on on pages like the datatype page), and hidden
> in the pragma interface as well?
>
> Is there an API call or PRAGMA statement that gives this information
> directly?
>
> Thanks.
>
>
> [1]
> http://sqlite.1065341.n5.nabble.com/Introspection-and-
> RowID-INTEGER-PRIMARY-KEY-td60462.html
> [2] https://sqlite.org/pragma.html
> ___
> 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] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Peter Halasz]

Sorry to steer the conversation back to the topic.

Looks like I do need to use AUTOINCREMENT after all, otherwise the
framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY
columns are allowed a NULL value on insert. That's a shame.

Is there a way to query a schema to get a direct answer to whether table
has a column which acts as the ROW ID alias? I'd like to improve linq2db's
code generation if I can, but I can't see any straightforward way to do it
as the ROW ID information is hidden (in SQLite's API, not just its
documentation)

I found someone asking the same question on this mailing list in 2010 who
was told to: [1]

"look at column 'sql' of TABLE sqlite_master and parse the creation
statement"

Which is frankly ridiculous.

I'm hoping there's an actual answer 7 years later that doesn't involve
implementing a SQL parser? (* please *don't even make suggestions on how to
do this)

I can't find anything in the pragma documentation. [2] The closest I could
see is "*PRAGMA **index_xinfo(**index-name**);*" which will give
information on whether an index references a rowid. I tried creating an
index for a column just to test if it was the ROW ID but it doesn't
actually work that way.

On the page https://www.sqlite.org/rowidtable.html it states:

   -

   All of the complications above (and others not mentioned here) arise
   from the need to preserve backwards compatibility to the tens of billions
   of SQLite database files in circulation. In a perfect world, there would be
   no such thing as a "rowid" and all tables would following the standard
   semantics implemented as WITHOUT ROWID tables, only without the extra
   "WITHOUT ROWID" keywords. Unfortunately, life is messy. The designer of
   SQLite offers his sincere apology for the current mess.

So it appears ROWID is sticking around. Why is it hidden in the
documentation (not listed on on pages like the datatype page), and hidden
in the pragma interface as well?

Is there an API call or PRAGMA statement that gives this information
directly?

Thanks.


[1]
http://sqlite.1065341.n5.nabble.com/Introspection-and-RowID-INTEGER-PRIMARY-KEY-td60462.html
[2] https://sqlite.org/pragma.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite website A/B comparison

[Dan Mack]

Richard Hipp  writes:

> Which is the better "timeline" display for the SQLite project status,
> in your opinion?
>
>   A:  https://sqlite.org/src/timeline
>   B:  https://sqlite.org/b/timeline
>

I prefer A; having the "Older" link at the top doesn't seem right for some
reason.  Maybe because I don't know if I need older yet when I am on the
top of a page.

Dan

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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[J Decker]

On Fri, Nov 24, 2017 at 11:25 AM, Keith Medcalf  wrote:

>
> Actually a UUID or a GUID has a 100% certainty of a collision,  not just a
> possibility of a collision.  Just as all hash algorithms which take
> something and generate a shorter "hash" or "checksum" will always have
> collisions.  Without exception and as an absolute 100% certainty.  There is
> no way to avoid this mathematical certainty.
>
> However, whether the absolute and unmitigatable certainty of a collision
> is of any import or not is an entirely different matter.
>
>
*I*t's also 100% certainty that collision will occur with a 32 or 64 bit
row id before a UUID collision occurs.

For the truly paranoid UUID can be made CUID  (
https://github.com/ericelliott/cuid ) by using seconds for 32 of the 128
bits.

" 1 billion UUIDs per second for about 85 years, and a file containing this
many UUIDs, at 16 bytes per UUID, would be about 45 exabytes, many times
larger than the largest databases currently in existence, which are on the
order of hundreds of petabytes. "
85 years in seconds is 2 680 560 000, which is about 1/2 of a 32 bit
number, so you can add a factor of 170 to that ... or 14,450 years before
50% collision probability.  (generating a billion a second mind you for
14,000 years)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Richard Damon]

On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote:


At 22:38 24/11/2017, you wrote:
One proof of the falsehood of your assertion is that we CAN fill a 
database with some data using UIDs, and we will almost certainly not 
get a collision, while you assertion we will.


This is an attempt at "proof by example". Keith is perfectly right 
--mathematically speaking-- and your "proof" doesn't hold water, I 
mean as a formal proof.  The best proof that your "proof" isn't a 
proof is that you feel obliged  to add "almost certainly".


DISproof by example is a perfectly valid method. If someone makes a 
claim that something is ALWAYS true, ONE counter example IS a disproof. 
I said almost certainly as the chance of a collision isn't 0 (to be able 
to say with certainty) but is most defintely less than the 100% claimed.


If SQLite was coded so that "SELECT 3.1415926" would "almost 
certainly" return the expected value you just wouldn't use it. Same 
thing can be applied to, say, a the classical Hello world! program and 
a C compiler, but in this case experience teaches us to be even much 
more careful!


I agree that you or anyone else _may_ consider the odds of UUID 
collision(s) rare enough to ignore the issue and this is examplified 
in practice by a huge number of systems using UUIDs or similar things. 
But this doesn't make a proof of anything. That's the difference 
between theory and practice. Search for a good quote in this list 
about theory and practice ;-)
Considering that for a 'reasonable' number of records, (maybe even into 
billions), the odds of a collision are probably on the order of once in 
the life of the universe (I would need to run the math to get the exact 
number, but it is minuscule in the period of the lifetime of a typical 
computer), it seems to be a reasonable assumption. A big part might be 
the consequences of a collision.


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



--
Richard Damon

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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Jean-Christophe Deschamps]

At 22:38 24/11/2017, you wrote:
One proof of the falsehood of your assertion is that we CAN fill a 
database with some data using UIDs, and we will almost certainly not 
get a collision, while you assertion we will.


This is an attempt at "proof by example". Keith is perfectly right 
--mathematically speaking-- and your "proof" doesn't hold water, I mean 
as a formal proof.  The best proof that your "proof" isn't a proof is 
that you feel obliged  to add "almost certainly".


If SQLite was coded so that "SELECT 3.1415926" would "almost certainly" 
return the expected value you just wouldn't use it. Same thing can be 
applied to, say, a the classical Hello world! program and a C compiler, 
but in this case experience teaches us to be even much more careful!


I agree that you or anyone else _may_ consider the odds of UUID 
collision(s) rare enough to ignore the issue and this is examplified in 
practice by a huge number of systems using UUIDs or similar things. But 
this doesn't make a proof of anything. That's the difference between 
theory and practice. Search for a good quote in this list about theory 
and practice ;-)


JcD 


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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Richard Damon]

On 11/24/17 2:25 PM, Keith Medcalf wrote:

Actually a UUID or a GUID has a 100% certainty of a collision,  not just a possibility of a 
collision.  Just as all hash algorithms which take something and generate a shorter 
"hash" or "checksum" will always have collisions.  Without exception and as an 
absolute 100% certainty.  There is no way to avoid this mathematical certainty.

However, whether the absolute and unmitigatable certainty of a collision is of 
any import or not is an entirely different matter.

Absolutely incorrect, for a UID. Perhaps if you are talking about actual 
hashes, you can say that there are always multiple (at least potential) 
messages that will generate the same hash value (but for a good hash, 
the likelihood that one of them is sensible or even actually generated 
is minuscule). for a UID, while they are typically created by a hash of 
various information, including something that varies each time a given 
generator is used, what those inputs actually are is generally 
unimportant, but are mostly provided to help make the 'randomness' of 
the choice more 'random'. We are never really concerned with 'potential' 
collisions, only actual collisions.


One proof of the falsehood of your assertion is that we CAN fill a 
database with some data using UIDs, and we will almost certainly not get 
a collision, while you assertion we will.


Yes, there is a limit on how many entries we can generate before we will 
likely, or even certainly hit a collision, but if that number is 
significantly higher than the number of records we will generate (or 
even CAN generate), we can assume relative safety. With a 128 bit UID, 
the approximate point we need to worry about random collisions is the 
order of 2^64, I suspect that creating an SQLite database with 2^64 
non-trivial records in a single table is likely going to have other 
issues besides unique key collisions.


--
Richard Damon

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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Keith Medcalf]

Actually a UUID or a GUID has a 100% certainty of a collision,  not just a 
possibility of a collision.  Just as all hash algorithms which take something 
and generate a shorter "hash" or "checksum" will always have collisions.  
Without exception and as an absolute 100% certainty.  There is no way to avoid 
this mathematical certainty.  

However, whether the absolute and unmitigatable certainty of a collision is of 
any import or not is an entirely different matter.

---
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 Stephen Chrzanowski
>Sent: Friday, 24 November, 2017 11:21
>To: SQLite mailing list
>Subject: Re: [sqlite] "BIGINT" doesn't act like INTEGER on table
>creation [Bug]
>
>Obviously, this is a design time factor, but, in my applications, I
>always
>use integer IDs if I'm ever going to bring the info that row contains
>(And
>other relevant info) to the UI.  I have never had a solid reason to
>use
>GUIDs or UUIDs or whatever.
>
>Any time I'm adding something to a listbox, combo box, or whatever it
>is,
>that element of that lists Object (RE: tStringList) gets the ID that
>is in
>the database.  Using a UID would not work, unless I spend CPU cycles
>converting a 32 character string to bytes, then to 128-bit numbers.
>But
>then, my compiler is 32-bit only, but can emulate 64-bit numbers.
>
>I can see the reasoning why a UUID is appealing, but, an ID is an ID.
>It
>doesn't matter what it is.  When you use INTEGER PRIMARY KEY, you get
>from
>1 to 2^64-1 numbers to play with at LEAST.  I don't know if SQLite
>will go
>into 128 or 256bit integers.  With UUID, you're looking at a chance
>of
>collision.  Small, yes.  But its there.  With INTEGER PRIMARY KEY,
>you're
>going up by one each time.  Since I will never care what that ID is,
>as a
>developer or as a user, Integer IDs are perfect.
>
>
>On Fri, Nov 24, 2017 at 12:33 PM, Richard Damon family.org>
>wrote:
>
>> On 11/24/17 3:51 AM, R Smith wrote:
>>
>>>
>>> On 2017/11/24 5:23 AM, Peter Halasz wrote:
>>>
 As for whether I need to use AUTOINCREMENT, it seemed like a good
>idea to
 avoid rowid reuse, but I can avoid using it for the sake of
>optimization,
 so probably will.

>>>
>>> I agree with Keith and has many times mentioned this before
>(apologies to
>>> others for the déjà vu). You as the programmer / designer should
>always be
>>> in control of how and why a new ID is assigned.
>>>
>>> I would disagree here, in many cases the ID (rowid) is a purely
>internal
>> attribute with the purpose of accessing the data. It may have no
>problem
>> domain significance. If the primary key's purpose is purely data
>access,
>> then letting the data access layer handle it makes sense. The one
>case
>> where it makes sense for the programmer / designer to take control
>of the
>> PK is if the domain naturally has an identifier that would be
>suitable for
>> the key (an reasonable sized integer that is naturally unique), The
>> designer should also be fairly certain that it will remain so.
>>
>>> It feels like a saving to let the DB engine do it for you, but it
>isn't
>>> really. What you save in a bit of code that decides the new ID
>before-hand
>>> (which can be as simple as SELECT MAX(id)+1 FROM t) you typically
>have to
>>> catch up afterward in code to do the usual get-LastInsertedID and
>then pop
>>> that in wherever stuff wants to link to the new item. It's a zero-
>sum gain
>>> really.
>>>
>> The big issues with this method is if two processes both try to
>create a
>> new item at the same time, one of them is going to get an error and
>have to
>> redo its work. If you start by creating the record with an
>autoincrement
>> id, and then getting the ID used, then you remove the need to
>handle the
>> error on the simultaneous creation.
>>
>>>
>>> I'm not even a big fan of Integer IDs, I think codes / UUIDs are
>best,
>>> but obviously the speed/size gain with an integer key (especially
>INTEGER
>>> PRIMARY KEY row-id alias in SQLite) can't be ignored.
>>>
>>> Making a UUID or other 'big' key the primary access key will
>increase the
>> cost of looking up a record for ALL purposes. For small tables, it
>might
>> not be measurable, but small tables are less apt to need that sort
>of PK
>> either. It can make sense to use a key like that as an External Key
>to
>> describe the record to the outside world. Perhaps if the ONLY
>accesses to a
>> table are via this 'big' key, and very rarely by some other
>key/field,
>> making the big key the primary key would make sense.
>>
>>> Disclaimer: This is only my opinion, well, I'm not completely
>alone in
>>> it, but it is still an opinion and not a general SQL prescription.
>>>
>>
>> --
>> Richard Damon
>>
>>
>> ___
>> sqlite-users mailing list
>> 

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Keith Medcalf]

You are missing the point.  

The rowid is assigned automatically if it is not specified (that is, if it is 
null on insert).  This is the behaviour of the rowid.  In all databases and 
filesystems ever invented anywhere in the multiverse by any carbon (even 
non-carbon) based lifeform, whether an ugly bag of mostly water or not.

The AUTOINCREMENT keyword is an entirely different beast that I have never seen 
actually required for any useful purpose (except that there appears to be a 
number of folks who seem to think that the rowid is not automatically generated 
without that keyword because they never RTFM or have some form of cognitive 
disability).

It has nothing to do with choosing a primary key, a surrogate key, or an 
alternate key.

---
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 Richard Damon
>Sent: Friday, 24 November, 2017 10:33
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] "BIGINT" doesn't act like INTEGER on table
>creation [Bug]
>
>On 11/24/17 3:51 AM, R Smith wrote:
>>
>> On 2017/11/24 5:23 AM, Peter Halasz wrote:
>>> As for whether I need to use AUTOINCREMENT, it seemed like a good
>>> idea to
>>> avoid rowid reuse, but I can avoid using it for the sake of
>>> optimization,
>>> so probably will.
>>
>> I agree with Keith and has many times mentioned this before
>(apologies
>> to others for the déjà vu). You as the programmer / designer should
>> always be in control of how and why a new ID is assigned.
>>
>I would disagree here, in many cases the ID (rowid) is a purely
>internal
>attribute with the purpose of accessing the data. It may have no
>problem
>domain significance. If the primary key's purpose is purely data
>access,
>then letting the data access layer handle it makes sense. The one
>case
>where it makes sense for the programmer / designer to take control of
>the PK is if the domain naturally has an identifier that would be
>suitable for the key (an reasonable sized integer that is naturally
>unique), The designer should also be fairly certain that it will
>remain so.
>> It feels like a saving to let the DB engine do it for you, but it
>> isn't really. What you save in a bit of code that decides the new
>ID
>> before-hand (which can be as simple as SELECT MAX(id)+1 FROM t) you
>> typically have to catch up afterward in code to do the usual
>> get-LastInsertedID and then pop that in wherever stuff wants to
>link
>> to the new item. It's a zero-sum gain really.
>The big issues with this method is if two processes both try to
>create a
>new item at the same time, one of them is going to get an error and
>have
>to redo its work. If you start by creating the record with an
>autoincrement id, and then getting the ID used, then you remove the
>need
>to handle the error on the simultaneous creation.
>>
>> I'm not even a big fan of Integer IDs, I think codes / UUIDs are
>best,
>> but obviously the speed/size gain with an integer key (especially
>> INTEGER PRIMARY KEY row-id alias in SQLite) can't be ignored.
>>
>Making a UUID or other 'big' key the primary access key will increase
>the cost of looking up a record for ALL purposes. For small tables,
>it
>might not be measurable, but small tables are less apt to need that
>sort
>of PK either. It can make sense to use a key like that as an External
>Key to describe the record to the outside world. Perhaps if the ONLY
>accesses to a table are via this 'big' key, and very rarely by some
>other key/field, making the big key the primary key would make sense.
>> Disclaimer: This is only my opinion, well, I'm not completely alone
>in
>> it, but it is still an opinion and not a general SQL prescription.
>
>--
>Richard Damon
>
>___
>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] Need SQLite datafiles to be locked on OS level (Windows)

[Keith Medcalf]

No.  

The Windows Search Indexer or Windows Defender only processes files when they 
are "closed" (that is, the act of closing a file adds it to the queue of files 
to be processed).  As long as they are "open" they are not enqueued for 
processing.  There are a few exceptions, but they all require "application 
support" to behave in the non-default "do not fiddle-faddle with open files".

To do otherwise would be completely foolish (or more correctly, brain-dead).

Of course, if the software in question is a third-party thing such as from 
Slimeytech, McCrappy, or another brain dead vendor, then it is possible that it 
fiddle-faddles with files that are "in use" because it requires a certain 
minimum (but very low) wattage to realize what a really truly bad idea (ie, 
completely and utterly brain-dead) that is.

---
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 Nelson, Erik - 2
>Sent: Friday, 24 November, 2017 10:20
>To: SQLite mailing list
>Subject: Re: [sqlite] Need SQLite datafiles to be locked on OS level
>(Windows)
>
>Jirí Matejka Sent on Friday, November 24, 2017 3:45 AM
>
>>but this does not prevent reading and modifying the file from other
>>processes while they are open by SQLite in our process.
>
>Does antivirus or Windows randomly modify other applications' files?
>It seems like no program could reliably execute under such
>circumstances.
>
>
>-
>-
>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-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] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Stephen Chrzanowski]

Obviously, this is a design time factor, but, in my applications, I always
use integer IDs if I'm ever going to bring the info that row contains (And
other relevant info) to the UI.  I have never had a solid reason to use
GUIDs or UUIDs or whatever.

Any time I'm adding something to a listbox, combo box, or whatever it is,
that element of that lists Object (RE: tStringList) gets the ID that is in
the database.  Using a UID would not work, unless I spend CPU cycles
converting a 32 character string to bytes, then to 128-bit numbers.  But
then, my compiler is 32-bit only, but can emulate 64-bit numbers.

I can see the reasoning why a UUID is appealing, but, an ID is an ID.  It
doesn't matter what it is.  When you use INTEGER PRIMARY KEY, you get from
1 to 2^64-1 numbers to play with at LEAST.  I don't know if SQLite will go
into 128 or 256bit integers.  With UUID, you're looking at a chance of
collision.  Small, yes.  But its there.  With INTEGER PRIMARY KEY, you're
going up by one each time.  Since I will never care what that ID is, as a
developer or as a user, Integer IDs are perfect.


On Fri, Nov 24, 2017 at 12:33 PM, Richard Damon 
wrote:

> On 11/24/17 3:51 AM, R Smith wrote:
>
>>
>> On 2017/11/24 5:23 AM, Peter Halasz wrote:
>>
>>> As for whether I need to use AUTOINCREMENT, it seemed like a good idea to
>>> avoid rowid reuse, but I can avoid using it for the sake of optimization,
>>> so probably will.
>>>
>>
>> I agree with Keith and has many times mentioned this before (apologies to
>> others for the déjà vu). You as the programmer / designer should always be
>> in control of how and why a new ID is assigned.
>>
>> I would disagree here, in many cases the ID (rowid) is a purely internal
> attribute with the purpose of accessing the data. It may have no problem
> domain significance. If the primary key's purpose is purely data access,
> then letting the data access layer handle it makes sense. The one case
> where it makes sense for the programmer / designer to take control of the
> PK is if the domain naturally has an identifier that would be suitable for
> the key (an reasonable sized integer that is naturally unique), The
> designer should also be fairly certain that it will remain so.
>
>> It feels like a saving to let the DB engine do it for you, but it isn't
>> really. What you save in a bit of code that decides the new ID before-hand
>> (which can be as simple as SELECT MAX(id)+1 FROM t) you typically have to
>> catch up afterward in code to do the usual get-LastInsertedID and then pop
>> that in wherever stuff wants to link to the new item. It's a zero-sum gain
>> really.
>>
> The big issues with this method is if two processes both try to create a
> new item at the same time, one of them is going to get an error and have to
> redo its work. If you start by creating the record with an autoincrement
> id, and then getting the ID used, then you remove the need to handle the
> error on the simultaneous creation.
>
>>
>> I'm not even a big fan of Integer IDs, I think codes / UUIDs are best,
>> but obviously the speed/size gain with an integer key (especially INTEGER
>> PRIMARY KEY row-id alias in SQLite) can't be ignored.
>>
>> Making a UUID or other 'big' key the primary access key will increase the
> cost of looking up a record for ALL purposes. For small tables, it might
> not be measurable, but small tables are less apt to need that sort of PK
> either. It can make sense to use a key like that as an External Key to
> describe the record to the outside world. Perhaps if the ONLY accesses to a
> table are via this 'big' key, and very rarely by some other key/field,
> making the big key the primary key would make sense.
>
>> Disclaimer: This is only my opinion, well, I'm not completely alone in
>> it, but it is still an opinion and not a general SQL prescription.
>>
>
> --
> Richard Damon
>
>
> ___
> 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] Need SQLite datafiles to be locked on OS level(Windows)

[Joe Mistachkin]

Richard Damon wrote:
>
> A basic rule is that even if you don't think anyone else might be 
> accessing a file, you need to allow for a temporary failure to lock a 
> file for writting, and retry several times with a timeout.
> 

Yes, the Win32 VFS does this.  However, the original poster may wish to
increase the default retry values using the SQLITE_FCNTL_WIN32_AV_RETRY
file control with the sqlite3_file_control() function, see:

https://www.sqlite.org/c3ref/c_fcntl_begin_atomic_write.html

--
Joe Mistachkin @ https://urn.to/r/mistachkin


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

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

[Richard Damon]

On 11/24/17 12:20 PM, Nelson, Erik - 2 wrote:

Jirí Matejka Sent on Friday, November 24, 2017 3:45 AM


but this does not prevent reading and modifying the file from other
processes while they are open by SQLite in our process.

Does antivirus or Windows randomly modify other applications' files?  It seems 
like no program could reliably execute under such circumstances.

An antivirus program is unlikely to actually modify the files (unless it 
thinks it is virus infected and is quarantining the file). What can 
happen is the file gets a read lock on it and the application attempts 
to get a write lock, and THAT is refused due to the read locks by the 
scanner/indexer/backup program.


A basic rule is that even if you don't think anyone else might be 
accessing a file, you need to allow for a temporary failure to lock a 
file for writting, and retry several times with a timeout.


--
Richard Damon

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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Richard Damon]

On 11/24/17 3:51 AM, R Smith wrote:


On 2017/11/24 5:23 AM, Peter Halasz wrote:
As for whether I need to use AUTOINCREMENT, it seemed like a good 
idea to
avoid rowid reuse, but I can avoid using it for the sake of 
optimization,

so probably will.


I agree with Keith and has many times mentioned this before (apologies 
to others for the déjà vu). You as the programmer / designer should 
always be in control of how and why a new ID is assigned.


I would disagree here, in many cases the ID (rowid) is a purely internal 
attribute with the purpose of accessing the data. It may have no problem 
domain significance. If the primary key's purpose is purely data access, 
then letting the data access layer handle it makes sense. The one case 
where it makes sense for the programmer / designer to take control of 
the PK is if the domain naturally has an identifier that would be 
suitable for the key (an reasonable sized integer that is naturally 
unique), The designer should also be fairly certain that it will remain so.
It feels like a saving to let the DB engine do it for you, but it 
isn't really. What you save in a bit of code that decides the new ID 
before-hand (which can be as simple as SELECT MAX(id)+1 FROM t) you 
typically have to catch up afterward in code to do the usual 
get-LastInsertedID and then pop that in wherever stuff wants to link 
to the new item. It's a zero-sum gain really.
The big issues with this method is if two processes both try to create a 
new item at the same time, one of them is going to get an error and have 
to redo its work. If you start by creating the record with an 
autoincrement id, and then getting the ID used, then you remove the need 
to handle the error on the simultaneous creation.


I'm not even a big fan of Integer IDs, I think codes / UUIDs are best, 
but obviously the speed/size gain with an integer key (especially 
INTEGER PRIMARY KEY row-id alias in SQLite) can't be ignored.


Making a UUID or other 'big' key the primary access key will increase 
the cost of looking up a record for ALL purposes. For small tables, it 
might not be measurable, but small tables are less apt to need that sort 
of PK either. It can make sense to use a key like that as an External 
Key to describe the record to the outside world. Perhaps if the ONLY 
accesses to a table are via this 'big' key, and very rarely by some 
other key/field, making the big key the primary key would make sense.
Disclaimer: This is only my opinion, well, I'm not completely alone in 
it, but it is still an opinion and not a general SQL prescription.


--
Richard Damon

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[John G]

I agree - keep the list on email. Simple, convenient.

John Gillespie

On 22 November 2017 at 19:49, Niall O'Reilly  wrote:

> On 21 Nov 2017, at 16:27, Drago, William @ CSG - NARDA-MITEQ wrote:
>
> > Please, not a forum. The email list is instant, dynamic, and convenient.
> I don't think checking into a forum to stay current with the brisk activity
> here is very practical or appealing.
>
> I agree with Bill on this.
>
> It seems to me that the idea of re-architecting such a useful
> communications
> channel as this mailing list on account of a cluster of false positives
> raised
> by a single provider's triage system would best be characterized as an
> example
> of "the tail wagging the dog".
>
> I use this provider's service for the major bulk of my e-mail because the
> university where I used to work, which provides a continued e-mail service
> to retirees, long ago outsourced its previously in-house e-mail system,
> which I once had a hand in running, to Google.
>
> In my experience, this provider's triage system does a pretty good job,
> with very few false positives.  I see the current high incidence of
> mis-classification of messages received through the SQLite mailing list
> as an aberration.
>
> Since the triage system is open to tuning by each recipient for their own
> incoming mail, I suggest that all that is needed is for each subscriber to
> this list who depends (as I do) on GMail for their mail feed, to apply this
> tuning for themselves.
>
> I found instructions here: https://support.google.com/mail/answer/6579 and
> have now set up the following filter:
>
>   Matches: to:(sqlite-users@mailinglists.sqlite.org)
>   Do this: Never send it to Spam
>
>
> Best regards,
>
> Niall O'Reilly
>
> ___
> 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] Need SQLite datafiles to be locked on OS level (Windows)

[Nelson, Erik - 2]

Jirí Matejka Sent on Friday, November 24, 2017 3:45 AM

>but this does not prevent reading and modifying the file from other 
>processes while they are open by SQLite in our process.

Does antivirus or Windows randomly modify other applications' files?  It seems 
like no program could reliably execute under such circumstances.


--
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Getting an advance list of RowIDs for a query result set

[x]

For a complex query you can often get a list of the base table RowIDs very 
quickly with a simple query and then use an array of those values (along with 
the carray virtual table) to retrieve sections of data from the complex query 
almost instantly. I've been doing this for a while but would love to find a way 
of doing it automatically. The problem is as follows

Suppose I have the following query

SELECT ColA, ColB, ColC, 
FROM BaseTbl
jointype_1 Tbl1 on comparison_1
jointype_2 Tbl2 on comparison_2
.
.
jointype_n Tbln on comparison_n
WHERE 
ORDER BY 

and I want a list of the BaseTbl's rowid's for all records returned by the 
above query in
the order defined by ORDER BY. I also want it in the fastest possible way. In 
other words I
want

SELECT BaseTbl.RowID
FROM BaseTbl
jointype_1 Tbl1 on comparison_1
jointype_2 Tbl2 on comparison_2
.
.
jointype_n Tbln on comparison_n
WHERE 
ORDER BY 

with all redundant tables removed.

The question is, how to determine what tables are redundant?

Obviously any table involved in the WHERE or ORDER BY has to be retained aa 
does any table
acting as an intermediate to join those tables back to the BaseTbl.

Of the other tables I'm thinking I can discard table_i provided it's joined to 
the BaseTbl by a table_i unique index and comparison_i covers all columns of 
that index. It's fairly obvious if jointype_i == left join but I'm thinking it 
works also for inner join. Can anyone give me a counter example?

Anyone think of other ways?

I'm also wondering if the result produced by 'explain query plan' could 
simplify the coding although I know use of that isn't recommended as the 
structure of it is not guaranteed.

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

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

[Stephen Chrzanowski]

You might be able to set the timeout to something higher.

https://sqlite.org/pragma.html#pragma_busy_timeout
https://sqlite.org/c3ref/busy_timeout.html


On Fri, Nov 24, 2017 at 3:44 AM, Jiří Matějka  wrote:

> We need to lock our SQLite datafiles at the filesystem level, because our
> application frequently crashes with "database is locked" error, which is
> probably due to other processes opening our files. At least we identified
> Windows Search indexing service and McAffee antivirus.
>
> I have tried exclusive locking mode by
> PRAGMA locking_mode=EXCLUSIVE;
> but this does not prevent reading and modifying the file from other
> processes while they are open by SQLite in our process.
>
> After digging in SQLite sources it seems not possible. VFS for Windows
> always sends these share mode flags to CreateFile.
> dwShareMode = FILE_SHARE_READ | FILE_SHARE_WRITE;
>
> Is there any way to achieve this?
>
> We use C# and System.Data.SQLite but there is no problem to call the
> SQLite functions directly. Desktop application, datafiles are stored in
> %APP_DATA% subfolder.
> ___
> 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] SQLite website A/B comparison

[Richard Hipp]

Which is the better "timeline" display for the SQLite project status,
in your opinion?

  A:  https://sqlite.org/src/timeline
  B:  https://sqlite.org/b/timeline

Since this question is really more about the version-control system
than SQLite itself, please reply directly to me rather responding to
the mailing list.  Or, join the discussion of this question over on
the fossil-us...@lists.fossil-scm.org mailing list.

Further suggestions for the improvement of the timeline display (or
anything else on the website) are welcomed.

-- 
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] EXISTS optimisation?

[Clemens Ladisch]

Constantin Emil MARINA wrote:
> I am wondering if in SQLITE the EXISTS clause is expanded and optimized in 
> any way.

No.

> This is generated by the observation that 2 algebrically equivalent queries,
>   SELECT WHERE EXISTS ()
> and
>   SELECT WHERE id IN (SELECT ...)
> produce different execution plans and different performance, with WHERE id IN 
> (SELECT ) looking properly optimized.

All three ways of writing this query are executed in the same way.  In
particular, the IN and JOIN queries are rewritten to act like the EXISTS
query:

create table t(x);
create table lookup(x primary key);

select * from t where exists (select * from lookup where x = t.x);
--EQP-- 0,0,0,SCAN TABLE t
--EQP-- 0,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 1
--EQP-- 1,0,0,SEARCH TABLE lookup USING COVERING INDEX 
sqlite_autoindex_lookup_1 (x=?)

select * from t where x in (select x from lookup);
--EQP-- 0,0,0,SCAN TABLE t
--EQP-- 0,0,0,USING INDEX sqlite_autoindex_lookup_1 FOR IN-OPERATOR

select t.* from t join lookup using (x);
--EQP-- 0,0,0,SCAN TABLE t
--EQP-- 0,1,1,SEARCH TABLE lookup USING COVERING INDEX 
sqlite_autoindex_lookup_1 (x=?)


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

Re: [sqlite] Commit and rollback behaviour during power loss

[Rowan Worth]

There is one degenerate case, which has been discussed a few times on this
list. With PRAGMA journal_mode=DELETE (the default), the atomic signal that
marks a transaction being committed is the deletion of the rollback
journal. Deleting a file is a directory level operation, which means there
are three files being changed:

1. The main DB file
2. The rollback journal
3. The directory

By default sqlite fsyncs (1) and (2) as part of COMMIT, but not (3). This
makes it possible for your program's control flow to get an SQLITE_OK
result from "conn.commit()" but then lose power before the OS syncs the
directory change to disk. As a result the transaction will be rolled back
when the DB is next opened.

It's a pretty extreme scenario, but you can avoid the risk via PRAGMA
synchronous=EXTRA (to also sync the directory), or PRAGMA
journal_mode=truncate (avoiding the need to modify the directory).


I don't think there is any scenario in which power loss could result in
multiple transactions being rolled back (unless fsync lies of course, in
which case all bets are off).

-Rowan

On 24 November 2017 at 16:47, Blagovest Buyukliev <
blagovest.buyukl...@gmail.com> wrote:

> Let's say we have the following Python code:
>
> import sqlite3
> conn = sqlite3.connect('mydb.db')
> c = conn.cursor()
> c.execute("INSERT INTO ...")
> conn.commit()
> c.execute("INSERT INTO ...")
> conn.commit()
>
> Can it be assumed that after conn.commit() has returned, fsync() has
> been called on the file and (assuming that fsync doesn't lie and
> doesn't just act as a barrier), the last inserted row has made its way
> to disk?
>
> Also, say that power is lost during the *second* conn.commit(). Is
> there a possibility that the database will be rolled back to the state
> before the first INSERT, or is it guaranteed that it will be rolled
> back to the state before the second INSERT?
> ___
> 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] Need SQLite datafiles to be locked on OS level (Windows)

[Rowan Worth]

On 24 November 2017 at 16:44, Jiří Matějka  wrote:

> We need to lock our SQLite datafiles at the filesystem level, because our
> application frequently crashes with "database is locked" error, which is
> probably due to other processes opening our files. At least we identified
> Windows Search indexing service and McAffee antivirus.
>

sqlite does try to work around this kind of interference (see winLock()
"Try 3 times to get the pending lock" and winRetryIoerr() used all over the
place)


> I have tried exclusive locking mode by
> PRAGMA locking_mode=EXCLUSIVE;
> but this does not prevent reading and modifying the file from other
> processes while they are open by SQLite in our process.
>

Right, but it should still prevent OS level "database is locked" errors by
virtue of the fact that SQLite no longer relinquishes its locks in this
mode. So it will only call LockFile/LockFileEx a handful of times before
escalating to the highest possible lock (which _will_ prevent other SQLite
instances from operating on the database until your process closes it).

Do you have more than one connection to the database? Is this error coming
from sqlite3_close? Are you in WAL mode? These introduce other
opportunities for "database is locked" which have nothing to do with the
operating environment.

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

Re: [sqlite] Commit and rollback behaviour during power loss

[Blagovest Buyukliev]

> If Commit returns with SQLITE_OK, then YES, it is handed off to the disk
> with some caveats, namely:
>
> A - We are assuming the Python sqlite3 wrapper you use doesn't do
> obfuscation of any sort and directly calls the sqlite3 API and returns the
> direct result from those calls. If you haven't yet, consider using (or at
> least trying) the APSW wrapper.

Some inspection of the Python wrapper would reveal that. The important
thing you note is that this condition can be distinguished from the C
API.

> B - When SQLite reports back SQLITE_OK after a commit, it means that SQLite
> has handed off the fsync() to the OS. Some OSes may lie about this having
> been effected yet (in the interest of speed) and also, perhaps even
> unbeknown to the OS, the hardware itself may lie about having actually
> committed those bits to platters/NVRam/etc. Implementing a good database
> system is as much about making the hardware comply as making the software
> work.  That said, data failures/corruption due to this B caveat is extremely
> improbable and only ever happens in the weirdest of circumstances.

The hardware and the OS are pretty much a fixed combination, since
this will be used in an in-house hosted solution with Linux and some
chosen enterprise-level storage.

> [1] What I am not seeing in your code is conn.startTransaction() (or however
> that will be named for you). How does the connection know you have meant for
> a transaction to start so as to commit it later? I'm also not seeing you
> testing any return value from those conn.commit() calls, but that might just
> be for brevity of the post. They can however return a failure code, in which
> case the commit didn't happen.

Yes, the code is purely illustrative and lacks any error handling for brevity.

> Again, if the first Commit returned with SQLITE_OK (with noting the above
> caveats again), then no, that state is forever captured and part of the DB
> now, and any new transaction will have its own atomicity.

That's a very good thing which renders our potential solution viable.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Commit and rollback behaviour during power loss

[R Smith]

On 2017/11/24 10:47 AM, Blagovest Buyukliev wrote:

Let's say we have the following Python code:

import sqlite3
conn = sqlite3.connect('mydb.db')
c = conn.cursor()
c.execute("INSERT INTO ...")
conn.commit()
c.execute("INSERT INTO ...")
conn.commit()

Can it be assumed that after conn.commit() has returned, fsync() has
been called on the file and (assuming that fsync doesn't lie and
doesn't just act as a barrier), the last inserted row has made its way
to disk?


Probably not[1], but lets assume for a moment a transaction was started 
successfully, then:


If Commit returns with SQLITE_OK, then YES, it is handed off to the disk 
with some caveats, namely:


A - We are assuming the Python sqlite3 wrapper you use doesn't do 
obfuscation of any sort and directly calls the sqlite3 API and returns 
the direct result from those calls. If you haven't yet, consider using 
(or at least trying) the APSW wrapper.


B - When SQLite reports back SQLITE_OK after a commit, it means that 
SQLite has handed off the fsync() to the OS. Some OSes may lie about 
this having been effected yet (in the interest of speed) and also, 
perhaps even unbeknown to the OS, the hardware itself may lie about 
having actually committed those bits to platters/NVRam/etc. Implementing 
a good database system is as much about making the hardware comply as 
making the software work.  That said, data failures/corruption due to 
this B caveat is extremely improbable and only ever happens in the 
weirdest of circumstances.




Also, say that power is lost during the *second* conn.commit(). Is
there a possibility that the database will be rolled back to the state
before the first INSERT, or is it guaranteed that it will be rolled
back to the state before the second INSERT?


Again, if the first Commit returned with SQLITE_OK (with noting the 
above caveats again), then no, that state is forever captured and part 
of the DB now, and any new transaction will have its own atomicity.



[1] What I am not seeing in your code is conn.startTransaction() (or 
however that will be named for you). How does the connection know you 
have meant for a transaction to start so as to commit it later? I'm also 
not seeing you testing any return value from those conn.commit() calls, 
but that might just be for brevity of the post. They can however return 
a failure code, in which case the commit didn't happen.



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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Dominique Devienne]

On Fri, Nov 24, 2017 at 9:51 AM, R Smith  wrote:

> I'm not even a big fan of Integer IDs, I think codes / UUIDs are best, but
> obviously the speed/size gain with an integer key (especially INTEGER
> PRIMARY KEY row-id alias in SQLite) can't be ignored.
>
> Disclaimer: This is only my opinion, well, I'm not completely alone in it,
> but it is still an opinion and not a general SQL prescription.


Many opinions on this everywhere, and discussed a few times on this list as
well.
Here's one among a million:
https://blog.codinghorror.com/primary-keys-ids-versus-guids/

--DD

PS: I use guids are surrogate keys (always with a companion natural key,
aka unique index, often composite).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need SQLite datafiles to be locked on OS level (Windows)

[Jiří Matějka]

We need to lock our SQLite datafiles at the filesystem level, because 
our application frequently crashes with "database is locked" error, 
which is probably due to other processes opening our files. At least we 
identified Windows Search indexing service and McAffee antivirus.


I have tried exclusive locking mode by
PRAGMA locking_mode=EXCLUSIVE;
but this does not prevent reading and modifying the file from other 
processes while they are open by SQLite in our process.


After digging in SQLite sources it seems not possible. VFS for Windows 
always sends these share mode flags to CreateFile.

dwShareMode = FILE_SHARE_READ | FILE_SHARE_WRITE;

Is there any way to achieve this?

We use C# and System.Data.SQLite but there is no problem to call the 
SQLite functions directly. Desktop application, datafiles are stored in 
%APP_DATA% subfolder.

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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[R Smith]

On 2017/11/24 5:23 AM, Peter Halasz wrote:

As for whether I need to use AUTOINCREMENT, it seemed like a good idea to
avoid rowid reuse, but I can avoid using it for the sake of optimization,
so probably will.


I agree with Keith and has many times mentioned this before (apologies 
to others for the déjà vu). You as the programmer / designer should 
always be in control of how and why a new ID is assigned.


It feels like a saving to let the DB engine do it for you, but it isn't 
really. What you save in a bit of code that decides the new ID 
before-hand (which can be as simple as SELECT MAX(id)+1 FROM t) you 
typically have to catch up afterward in code to do the usual 
get-LastInsertedID and then pop that in wherever stuff wants to link to 
the new item. It's a zero-sum gain really.


I'm not even a big fan of Integer IDs, I think codes / UUIDs are best, 
but obviously the speed/size gain with an integer key (especially 
INTEGER PRIMARY KEY row-id alias in SQLite) can't be ignored.


Disclaimer: This is only my opinion, well, I'm not completely alone in 
it, but it is still an opinion and not a general SQL prescription.



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

[sqlite] Commit and rollback behaviour during power loss

[Blagovest Buyukliev]

Let's say we have the following Python code:

import sqlite3
conn = sqlite3.connect('mydb.db')
c = conn.cursor()
c.execute("INSERT INTO ...")
conn.commit()
c.execute("INSERT INTO ...")
conn.commit()

Can it be assumed that after conn.commit() has returned, fsync() has
been called on the file and (assuming that fsync doesn't lie and
doesn't just act as a barrier), the last inserted row has made its way
to disk?

Also, say that power is lost during the *second* conn.commit(). Is
there a possibility that the database will be rolled back to the state
before the first INSERT, or is it guaranteed that it will be rolled
back to the state before the second INSERT?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users