
12

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: sqliteusers [mailto:sqliteusers
> [hidden email]] On Behalf Of Richard Damon
>Sent: Friday, 24 November, 2017 14:39
>To: [hidden email]
>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
>nontrivial records in a single table is likely going to have other
>issues besides unique key collisions.
>
>
>Richard Damon
>
>_______________________________________________
>sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On 11/24/17 8:58 PM, JeanChristophe Deschamps wrote:
>
> At 23:49 24/11/2017, you wrote:
>
>> On 11/24/17 5:26 PM, JeanChristophe 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^100000...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^100000...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
>
> _______________________________________________
> sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusersThe 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 nonzero chance, then
it would be true. Probability has always been related to times happened
/ times tried. So your claim is that 10^10000... == 1, saying
something will EVENTUAL happen is a claim on nonzero 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
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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 60bit 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 100nanosecond span of time. They will not, however, collide.
On November 24, 2017 8:58:02 PM EST, JeanChristophe Deschamps < [hidden email]> wrote:
>
>At 23:49 24/11/2017, you wrote:
>
>>On 11/24/17 5:26 PM, JeanChristophe 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^100000...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^100000...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
>
>_______________________________________________
>sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers
Sent from my Android device with K9 Mail. Please excuse my brevity.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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 <string.h>
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 autoincrement */
} 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 */
&md.zDataType, /* OUTPUT: Declared data type */
&md.zCollSeq, /* OUTPUT: Collation sequence name */
&md.NotNull, /* OUTPUT: True if NOT NULL constraint exists */
&md.PrimaryKey, /* OUTPUT: True if column part of PK */
&md.Autoinc/* OUTPUT: True if column is autoincrement */
);
}
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<sqlite build dir> 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.htmlSo 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 < [hidden email]>
wrote:
> > sqlite> .load columnmetadata.so
>
> Sorry I'm at a loss to find this extension? Google gives me nothing related
> to SQLite for "isRowId", "columnmetadata.so", "columnmetadata.c", etc.
> _______________________________________________
> sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers>
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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 dothis 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 < [hidden email]> wrote:
>Is this a joke?
>_______________________________________________
>sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers
Sent from my Android device with K9 Mail. Please excuse my brevity.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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: sqliteusers [mailto:sqliteusers
> [hidden email]] 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 60bit 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 100nanosecond span of time. They will not, however, collide.
>
>On November 24, 2017 8:58:02 PM EST, JeanChristophe Deschamps
>< [hidden email]> wrote:
>>
>>At 23:49 24/11/2017, you wrote:
>>
>>>On 11/24/17 5:26 PM, JeanChristophe 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^100000...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^100000...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
>>
>>_______________________________________________
>>sqliteusers mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers>
>
>Sent from my Android device with K9 Mail. Please excuse my brevity.
>_______________________________________________
>sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


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 < [hidden email]> 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 60bit 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 100nanosecond span of time. They will not, however, collide.
>
> On November 24, 2017 8:58:02 PM EST, JeanChristophe Deschamps < [hidden email]> wrote:
>>
>> At 23:49 24/11/2017, you wrote:
>>
>>> On 11/24/17 5:26 PM, JeanChristophe 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^100000...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^100000...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
>>
>> _______________________________________________
>> sqliteusers mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers>
> 
> Sent from my Android device with K9 Mail. Please excuse my brevity.
> _______________________________________________
> sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


Are people here talking about UUIDs or things that just look like UUIDs? It sounds like the latter. UUIDs are actually structured objects, with embedded type bits. There are multiple UUID generation schemes, one of which is based on random numbers, others are based on hashes, and there is the common ugly workaround of generating a 128 bit hash and calling it a UUID.
If you use version 1 UUIDs you're mathematically guaranteed to avoid collisions. At least for the next 3000 years:
Version 1 UUIDs are based on a node address (MAC), a 60 bit clock, and a nodespecific sequence number. You can generate 163 billion version 1 UUIDs per second and they won't roll over until 5236 AD.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


Ah, I see someone else has already brought up version 1 UUIDs.
> What about invalid and reused MAC addresses and devices with no MAC address at all?
Not an issue with SQLite since that part of the UUID is a constant within a given database. It would be reasonable to simply pick "0" then set the low bit of the first octet to 1 in accordance with the standard for nonMAC node IDs. The standard suggests using a hash or a cryptographically secure random number generator, setting the
> What about time resets to the epoch which are not restored, user time changes,
I know some systems at least increment the node each time a time change is detected. It will take 2^47 time changes to roll over. Since the node part is not relevant to SQLite, this is perfectly safe.
> daylight saving or leap seconds?
Not relevant to the timestamp format, since it's an epoch time.
> It sounds even more probabilistic than the probabilistic methods. Does anyone actually use it?
It's Microsoft's standard GUID/UUID format and is also used by a lot of other systems for compatibility.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


>> What about time resets to the epoch which are not restored, user time changes,
>
> I know some systems at least increment the node each time a time change is detected. It will take 2^47 time changes to roll over. Since the node part is not relevant to SQLite, this is perfectly safe.
Also, the UUID clock doesn't need to be the system clock, so you can simply ignore backwards changes in the system clock (or maintain a common offset that gets updated whenever a backwards change is detected in the system clock). Over time this may trim a few decades off the 3000+ year life of the format.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


The one thing that saddens me is that the clock is not the full first
part of the UUID, so it's not a proxy for sorting by creation date. I
often wonder why they did that, they must have done it on purpose.
On 11/25/17, Peter Da Silva < [hidden email]> wrote:
>>> What about time resets to the epoch which are not restored, user time
>>> changes,
>>
>> I know some systems at least increment the node each time a time change is
>> detected. It will take 2^47 time changes to roll over. Since the node part
>> is not relevant to SQLite, this is perfectly safe.
>
> Also, the UUID clock doesn't need to be the system clock, so you can simply
> ignore backwards changes in the system clock (or maintain a common offset
> that gets updated whenever a backwards change is detected in the system
> clock). Over time this may trim a few decades off the 3000+ year life of the
> format.
> _______________________________________________
> sqliteusers mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers>
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


On Thu, 23 Nov 2017 17:35:08 0700
"Keith Medcalf" < [hidden email]> escribió:
>
> That is correct. You cannot spell "INTEGER PRIMARY KEY", which is an
> explicit alias for the rowid, as anything other than that one particular
> correct spelling.
>
> Personally, I have never found a use for the AUTOINCREMENT option. Why is
> it being used?
If you declare a INTEGER PRIMARY KEY alone, sqlite could reuse rowid number
from deleted rows. Using AUTOINCREMENT forces sqlite to use a rowid bigger than
the last one inserted/updated(1), so you can use it to know if one row is older
or newer than others, without autoincrement no. You can do similar behavior
with a trigger, for example for TEXT columns or automatically with DATE current
time.
(1) You can change the integer primary key to whatever value you want.
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a
> lot about anticipated traffic volume.
.... See you there then? ;)

Eduardo < [hidden email]>
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers


Sorry to rain on your parade, but " The sqlite_sequence table does not track ROWID changes associated with UPDATE statement, only INSERT statements.", so if you change the (autogenerated) rowid, your "row age to row id" relation no longer holds. Same goes for updating the sqlite_sequence entry associated with the table.
Indeed, if you immediately update the sqlite_sequence entry for your INTEGER PRIMARY KEY to MAXINT64, you will achieve "random" rowids.
BTW: Consider the following transaction to implement LRU via rowids:
BEGIN
 retrieve the rowid for the target row (store as rowid in calling program)
SELECT rowid,... FROM mytable WHERE ...;
 retrieve the sequence value for the table (store as seq in calling program)
SELECT seq+1 FROM SQLITE_SEQUENCE WHERE name='mytable';
 update values and set new rowid
UPDATE mytable SET rowid=?seq, ... WHERE rowid=?rowid;
 update sequence value
UPDATE SQLITE_SEQUENCE SET seq=?seq WHERE name='mytable';
COMMIT;
Yes, this will probably cause foreign keys to break or require execssive work (ON UPDATE CASCADE).
Ursprüngliche Nachricht
Von: sqliteusers [mailto: [hidden email]] Im Auftrag von Eduardo
Gesendet: Montag, 27. November 2017 10:28
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]
On Thu, 23 Nov 2017 17:35:08 0700
"Keith Medcalf" < [hidden email]> escribió:
>
> That is correct. You cannot spell "INTEGER PRIMARY KEY", which is an
> explicit alias for the rowid, as anything other than that one
> particular correct spelling.
>
> Personally, I have never found a use for the AUTOINCREMENT option.
> Why is it being used?
If you declare a INTEGER PRIMARY KEY alone, sqlite could reuse rowid number from deleted rows. Using AUTOINCREMENT forces sqlite to use a rowid bigger than the last one inserted/updated(1), so you can use it to know if one row is older or newer than others, without autoincrement no. You can do similar behavior with a trigger, for example for TEXT columns or automatically with DATE current time.
(1) You can change the integer primary key to whatever value you want.
> The fact that there's a Highway to Hell but only a Stairway to Heaven
> says a lot about anticipated traffic volume.
.... See you there then? ;)

Eduardo < [hidden email]>
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers___________________________________________
Gunter Hick  Software Engineer  Scientific Games International GmbH  Klitschgasse 24, A1130 Vienna  FN 157284 a, HG Wien, DVR: 0430013  (O) +43 1 80100  0
May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqliteusers mailing list
[hidden email]
http://mailinglists.sqlite.org/cgibin/mailman/listinfo/sqliteusers

12
