AUTOINC vs. UUIDs

classic Classic list List threaded Threaded
43 messages Options
123
Reply | Threaded
Open this post in threaded view
|

AUTOINC vs. UUIDs

Simon Slavin-3
Posting this not because I agree with it but because the subject has come up here a couple of times.

<https://www.clever-cloud.com/blog/engineering/2015/05/20/Why-Auto-Increment-Is-A-Terrible-Idea/>

"Today, I’ll talk about why we stopped using serial integers for our primary keys, and why we’re now extensively using Universally Unique IDs (or UUIDs) almost everywhere."

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Petite Abeille-2

> On May 20, 2015, at 8:05 PM, Simon Slavin <[hidden email]> wrote:
>
> "Today, I’ll talk about why we stopped using serial integers for our primary keys, and why we’re now extensively using Universally Unique IDs (or UUIDs) almost everywhere.”

Argh… seriously?

tl;dr: don’t.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Stephan Beal-3
In reply to this post by Simon Slavin-3
On Wed, May 20, 2015 at 8:05 PM, Simon Slavin <[hidden email]> wrote:

> Posting this not because I agree with it but because the subject has come
> up here a couple of times.
>
> <
> https://www.clever-cloud.com/blog/engineering/2015/05/20/Why-Auto-Increment-Is-A-Terrible-Idea/
> >
>
> "Today, I’ll talk about why we stopped using serial integers for our
> primary keys, and why we’re now extensively using Universally Unique IDs
> (or UUIDs) almost everywhere."
>

As i recall (maybe incorrectly), one of sqlite4's problems is that auto-inc
is difficult with its storage model (don't remember why, just seem to
remember hearing/reading that). Perhaps this approach could be a viable
alternative?

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Scott Doctor
In reply to this post by Simon Slavin-3

Given a field that is a primary key with auto-increment, does sqlite
store an integer that gets incremented, or does it look at the last row
and increment its value?

------------
Scott Doctor
[hidden email]

On 5/20/2015 11:05 AM, Simon Slavin wrote:

> Posting this not because I agree with it but because the subject has come up here a couple of times.
>
> <https://www.clever-cloud.com/blog/engineering/2015/05/20/Why-Auto-Increment-Is-A-Terrible-Idea/>
>
> "Today, I’ll talk about why we stopped using serial integers for our primary keys, and why we’re now extensively using Universally Unique IDs (or UUIDs) almost everywhere."
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

R Smith

On 2015-05-20 09:36 PM, Scott Doctor wrote:
>
> Given a field that is a primary key with auto-increment, does sqlite
> store an integer that gets incremented, or does it look at the last
> row and increment its value?
>

SQLite stores an Integer that gets incremented if the PK is declared
specifically with "PRIMARY KEY AUTOINCREMENT", the place it stores the
integer is a table that you can edit but cannot create or drop, the
table is called "sqlite_sequence" and it contains an entry for each
table with an auto-incremented PK (and remain absent until such a table
is created).

More about that here:
https://www.sqlite.org/autoinc.html



> ------------
> Scott Doctor
> [hidden email]
>
> On 5/20/2015 11:05 AM, Simon Slavin wrote:
>> Posting this not because I agree with it but because the subject has
>> come up here a couple of times.
>>
>> <https://www.clever-cloud.com/blog/engineering/2015/05/20/Why-Auto-Increment-Is-A-Terrible-Idea/>
>>
>>
>> "Today, I’ll talk about why we stopped using serial integers for our
>> primary keys, and why we’re now extensively using Universally Unique
>> IDs (or UUIDs) almost everywhere."
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Richard Hipp-3
In reply to this post by Scott Doctor
On 5/20/15, Scott Doctor <[hidden email]> wrote:
>
> Given a field that is a primary key with auto-increment, does sqlite
> store an integer that gets incremented, or does it look at the last row
> and increment its value?

https://www.sqlite.org/autoinc.html
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Kees Nuyt
In reply to this post by Scott Doctor
On Wed, 20 May 2015 12:36:43 -0700, Scott Doctor
<[hidden email]> wrote:

> Given a field that is a primary key with auto-increment, does sqlite
> store an integer that gets incremented, or does it look at the last row
> and increment its value?

The autoincrement clause causes an entry in the sqlite_sequence
table.

$ sqlite3 t2.sqlite
SQLite version 3.8.11 2015-05-20 00:15:27
Enter ".help" for usage hints.
sqlite> create table t(id integer primary key autoincrement, tx
text);
sqlite> insert into t (tx) values ('one'),('two');
sqlite> select * from sqlite_sequence;
t|2
sqlite>

--
Regards,

Kees Nuyt

 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Simon Slavin-3

On 20 May 2015, at 8:52pm, Kees Nuyt <[hidden email]> wrote:

> The autoincrement clause causes an entry in the sqlite_sequence
> table.

It's interesting that SQLite uses tables inside the user database for private purposes like this.  A certain wall is broken when the designers choose this option.  SQLite does it for sqlite_master, sqlite_sequence, sqlite_stat*, and probably others I've forgotten.

SQLite is handicapped by having no permanent data storage location.  It has nowhere to store configuration information apart from inside the user's databases or as compilation settings.  This is very unusual but, I think, contributes a lot to how portable SQLite is: no need to understand folder structure or safe places to keep configuration information; increased startup-speed; reduced code size, fewer file handles, slightly reduced memory.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Mark Straver
Just to chime in here: I think using UUIDs for primary keys is an absolutely
terrible idea, instead. First off, how are you going to generate them? How
will you avoid collisions? Why should it be necessary to using that much
storage space for a primary key and what's the reason for not using
blazingly-fast integers?...

> This is very unusual but, I think, contributes a lot to how portable SQLite is: no need to understand folder structure or safe places to keep configuration information; increased startup-speed; reduced code size, fewer file handles, slightly reduced memory.

I think the fact that it's called "SQLite" is for a reason ;)


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Steven M. McNeese
Often times people will use GUIDs as primary keys when different systems need to generate rows and then merge together. Like an application that works with offline clients that push the data to a server when the connect. However there are other ways of accomplishing the same thing.

Sent from my iPhone

> On May 20, 2015, at 3:50 PM, Mark Straver <[hidden email]> wrote:
>
> Just to chime in here: I think using UUIDs for primary keys is an absolutely
> terrible idea, instead. First off, how are you going to generate them? How
> will you avoid collisions? Why should it be necessary to using that much
> storage space for a primary key and what's the reason for not using
> blazingly-fast integers?...
>
>> This is very unusual but, I think, contributes a lot to how portable SQLite is: no need to understand folder structure or safe places to keep configuration information; increased startup-speed; reduced code size, fewer file handles, slightly reduced memory.
>
> I think the fact that it's called "SQLite" is for a reason ;)
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Andreas Kupries-2
On Wed, May 20, 2015 at 2:20 PM, Steven M. McNeese
<[hidden email]> wrote:
> Often times people will use GUIDs as primary keys when different systems need to generate
> rows and then merge together. Like an application that works with offline clients that push the
> data to a server when the connect. However there are other ways of accomplishing the same thing.

For curiosity - Is there a site/blog post somewhere
enumerating/listing these other ways ?


>
> Sent from my iPhone
>
>> On May 20, 2015, at 3:50 PM, Mark Straver <[hidden email]> wrote:
>>
>> Just to chime in here: I think using UUIDs for primary keys is an absolutely
>> terrible idea, instead. First off, how are you going to generate them? How
>> will you avoid collisions? Why should it be necessary to using that much
>> storage space for a primary key and what's the reason for not using
>> blazingly-fast integers?...
>>
>>> This is very unusual but, I think, contributes a lot to how portable SQLite is: no need to understand folder structure or safe places to keep configuration information; increased startup-speed; reduced code size, fewer file handles, slightly reduced memory.
>>
>> I think the fact that it's called "SQLite" is for a reason ;)
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
% Tcl'2015 Oct 19-23 = http://www.tcl.tk/community/tcl2015/cfp.html
% EuroTcl'15 June 20-21 = http://www.eurotcl.tcl3d.org/
Andreas Kupries
Senior Tcl Developer
Code to Cloud: Smarter, Safer, Faster™
F: 778.786.1133
[hidden email], http://www.activestate.com
Learn about Stackato for Private PaaS: http://www.activestate.com/stackato
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Jean-Christophe Deschamps-3
In reply to this post by Mark Straver
At 22:50 20/05/2015, you wrote:

>Just to chime in here: I think using UUIDs for primary keys is an
>absolutely
>terrible idea, instead. First off, how are you going to generate them? How
>will you avoid collisions? Why should it be necessary to using that much
>storage space for a primary key and what's the reason for not using
>blazingly-fast integers?...

Agreed. Also the article denounces the "information leakage" introduced
by exposing rowids to applications, but keeps quiet about a significant
drawback: replacing those integers typically shown in the leftmost
column of a grid (which don't forcibly expose meaningful information)
by UUIDs is going to eat a very significant screen space if used instead.

Finaly I can see many people easily remembering 80486 as an identifier
of <something> but almost none able to do the same with their
c0b656b1-7351-4dc2-84c8-62a2afb41e66 example UUID.

Granted the risk of accidental collision is minimal but still
non-zero.  Yet their (careful) creation and, moreover, the repeated
conversions from 128-bit blob to split hex ASCII for human
"convenience" are a useless waste of cycles.

To those who say: "UUIDs don't have to be displayed to users", then why
complain that INTEGER PRIMARY KEY leak information if they are not
displayed either?

JcD

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Jean-Christophe Deschamps-3
In reply to this post by Andreas Kupries-2
At 23:24 20/05/2015, you wrote:

>On Wed, May 20, 2015 at 2:20 PM, Steven M. McNeese
><[hidden email]> wrote:
> > Often times people will use GUIDs as primary keys when different
> systems need to generate
> > rows and then merge together. Like an application that works with
> offline clients that push the
> > data to a server when the connect. However there are other ways of
> accomplishing the same thing.
>
>For curiosity - Is there a site/blog post somewhere
>enumerating/listing these other ways ?

I don't know, but let's say your rowids range from -9223372036854775807
to 9223372036854775807, that's 18446744073709551614 possible rowids.

Imagine that in 50 years, the total population on Earth will grow to
(say) 50 billion people (I hope it won't!).

18 446 744 073 709 551 614 / 50 000 000 000 = 368 934 881.474 191 032 28

That leaves you the possibility to assign a unique identifier to every
potential client on the planet (and a big one) in nearly 369 million
servers concurrently without the faintest risk of collision.

At this rate, you may limit rowids to only positive integers ... and
hire a large army of telemarketers.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Scott Doctor

Hmmm, 9,223,372,036,854,775,807. Disregarding the negative numbers
because,... well...

If your computer can process one billion completed, finalized,
transactions per second (I want one of those computers), the pool of
numbers will deplete in a mere:

(((( 9223372036854775807 / 1000000000 ) / 60 seconds) / 60 minutes) / 24
hours)/ 365 days) = 292 years.  rounded to the nearest year, but I think
your great, great, great, great, great, .... grand-kids will probably
not care, that assumes humans are not wiped out by by Skynet and eaten
by Aliens who harvest us as delicacies.


------------
Scott Doctor
[hidden email]

On 5/20/2015 2:38 PM, Jean-Christophe Deschamps wrote:

> At 23:24 20/05/2015, you wrote:
>
>> On Wed, May 20, 2015 at 2:20 PM, Steven M. McNeese
>> <[hidden email]> wrote:
>> > Often times people will use GUIDs as primary keys when different
>> systems need to generate
>> > rows and then merge together. Like an application that works with
>> offline clients that push the
>> > data to a server when the connect. However there are other ways of
>> accomplishing the same thing.
>>
>> For curiosity - Is there a site/blog post somewhere
>> enumerating/listing these other ways ?
>
> I don't know, but let's say your rowids range from
> -9223372036854775807 to 9223372036854775807, that's
> 18446744073709551614 possible rowids.
>
> Imagine that in 50 years, the total population on Earth will grow to
> (say) 50 billion people (I hope it won't!).
>
> 18 446 744 073 709 551 614 / 50 000 000 000 = 368 934 881.474 191 032 28
>
> That leaves you the possibility to assign a unique identifier to every
> potential client on the planet (and a big one) in nearly 369 million
> servers concurrently without the faintest risk of collision.
>
> At this rate, you may limit rowids to only positive integers ... and
> hire a large army of telemarketers.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Steven M. McNeese
In reply to this post by Jean-Christophe Deschamps-3
You misunderstood. If an application running on a tablet for 2 users add rows to a local database with an auto increment key, each would get a key based on their database an let's say for grins they both start with a new clean database. User 1 gets key 1 and user 2 gets key 1. Now they push their data to a server each with key 1 but different data. That doesn't work. Both records needs to be added to the server database - a collection of all the data collected on the client tablets.

Sent from my iPhone

> On May 20, 2015, at 4:38 PM, Jean-Christophe Deschamps <[hidden email]> wrote:
>
> At 23:24 20/05/2015, you wrote:
>
>> On Wed, May 20, 2015 at 2:20 PM, Steven M. McNeese
>> <[hidden email]> wrote:
>> > Often times people will use GUIDs as primary keys when different systems need to generate
>> > rows and then merge together. Like an application that works with offline clients that push the
>> > data to a server when the connect. However there are other ways of accomplishing the same thing.
>>
>> For curiosity - Is there a site/blog post somewhere
>> enumerating/listing these other ways ?
>
> I don't know, but let's say your rowids range from -9223372036854775807 to 9223372036854775807, that's 18446744073709551614 possible rowids.
>
> Imagine that in 50 years, the total population on Earth will grow to (say) 50 billion people (I hope it won't!).
>
> 18 446 744 073 709 551 614 / 50 000 000 000 = 368 934 881.474 191 032 28
>
> That leaves you the possibility to assign a unique identifier to every potential client on the planet (and a big one) in nearly 369 million servers concurrently without the faintest risk of collision.
>
> At this rate, you may limit rowids to only positive integers ... and hire a large army of telemarketers.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Scott Doctor

I can see the issue. For instance, an invoice ID related to inventory,
where the invoice ID is the PK number. Two salesmen on their iPads take
an order where their local database assigns the same PK number. When
merged you have two invoices with the same ID. Seems a classic problem
with non centralized input. A second field, such as salesman ID, would
need to squashed into that invoices PK number to guarantee a uniqueness
to the number, assuming every salesman has a unique ID.

------------
Scott Doctor
[hidden email]

On 5/20/2015 3:08 PM, Steven M. McNeese wrote:
> You misunderstood. If an application running on a tablet for 2 users add rows to a local database with an auto increment key, each would get a key based on their database an let's say for grins they both start with a new clean database. User 1 gets key 1 and user 2 gets key 1. Now they push their data to a server each with key 1 but different data. That doesn't work. Both records needs to be added to the server database - a collection of all the data collected on the client tablets.
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Keith Medcalf
In reply to this post by Simon Slavin-3

All relational database engines store configuration data within the users' database.  Many of them just hide it behind varying layers of logically imposed complication.  For example, you could simulate SQL Servers' obfuscation by simply changing the name of the primary database alias from "main" to "master" in the SQLite code, and then requiring that all "user" tables are stored in an attached database.

> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of Simon Slavin
> Sent: Wednesday, 20 May, 2015 14:38
> To: [hidden email]
> Subject: Re: [sqlite] AUTOINC vs. UUIDs
>
>
> On 20 May 2015, at 8:52pm, Kees Nuyt <[hidden email]> wrote:
>
> > The autoincrement clause causes an entry in the sqlite_sequence
> > table.
>
> It's interesting that SQLite uses tables inside the user database for
> private purposes like this.  A certain wall is broken when the designers
> choose this option.  SQLite does it for sqlite_master, sqlite_sequence,
> sqlite_stat*, and probably others I've forgotten.
>
> SQLite is handicapped by having no permanent data storage location.  It
> has nowhere to store configuration information apart from inside the
> user's databases or as compilation settings.  This is very unusual but, I
> think, contributes a lot to how portable SQLite is: no need to understand
> folder structure or safe places to keep configuration information;
> increased startup-speed; reduced code size, fewer file handles, slightly
> reduced memory.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Keith Medcalf
In reply to this post by Steven M. McNeese

These things should be called HUID's (Hopefully Unique ID's).  HUIDs violate determinism and are therefore a very bad thing.  Of course, many people do not require determinism (more often they think they do not require determinism -- support is after all the problem of some other guy) and having stuff hopefully work on a wing and a prayer is sufficiently adequate.

> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of Steven M. McNeese
> Sent: Wednesday, 20 May, 2015 15:20
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] AUTOINC vs. UUIDs
>
> Often times people will use GUIDs as primary keys when different systems
> need to generate rows and then merge together. Like an application that
> works with offline clients that push the data to a server when the
> connect. However there are other ways of accomplishing the same thing.
>
> Sent from my iPhone
>
> > On May 20, 2015, at 3:50 PM, Mark Straver <sqlite-users-
> [hidden email]> wrote:
> >
> > Just to chime in here: I think using UUIDs for primary keys is an
> absolutely
> > terrible idea, instead. First off, how are you going to generate them?
> How
> > will you avoid collisions? Why should it be necessary to using that much
> > storage space for a primary key and what's the reason for not using
> > blazingly-fast integers?...
> >
> >> This is very unusual but, I think, contributes a lot to how portable
> SQLite is: no need to understand folder structure or safe places to keep
> configuration information; increased startup-speed; reduced code size,
> fewer file handles, slightly reduced memory.
> >
> > I think the fact that it's called "SQLite" is for a reason ;)
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Keith Medcalf
In reply to this post by Jean-Christophe Deschamps-3

HUIDs have the birthday problem.  How many people do you need to have in a group before two of them will have the same birthday?  HUIDs can only be called LUID's (Locally Unique Identifiers) if you go to the trouble of ensuring uniqueness *before* using one.  Validating Global uniqueness is, quite obviously, a very difficult problem.

> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of Jean-Christophe Deschamps
> Sent: Wednesday, 20 May, 2015 15:38
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] AUTOINC vs. UUIDs
>
> At 23:24 20/05/2015, you wrote:
>
> >On Wed, May 20, 2015 at 2:20 PM, Steven M. McNeese
> ><[hidden email]> wrote:
> > > Often times people will use GUIDs as primary keys when different
> > systems need to generate
> > > rows and then merge together. Like an application that works with
> > offline clients that push the
> > > data to a server when the connect. However there are other ways of
> > accomplishing the same thing.
> >
> >For curiosity - Is there a site/blog post somewhere
> >enumerating/listing these other ways ?
>
> I don't know, but let's say your rowids range from -9223372036854775807
> to 9223372036854775807, that's 18446744073709551614 possible rowids.
>
> Imagine that in 50 years, the total population on Earth will grow to
> (say) 50 billion people (I hope it won't!).
>
> 18 446 744 073 709 551 614 / 50 000 000 000 = 368 934 881.474 191 032 28
>
> That leaves you the possibility to assign a unique identifier to every
> potential client on the planet (and a big one) in nearly 369 million
> servers concurrently without the faintest risk of collision.
>
> At this rate, you may limit rowids to only positive integers ... and
> hire a large army of telemarketers.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTOINC vs. UUIDs

Keith Medcalf
In reply to this post by Steven M. McNeese

On the other hand, perhaps both tablets implement the same PRNG with the same seed.  You will then have the same HUID's generated on both and have the exact same problem.  Addressing the problem using Hope and Pray is not a very robust solution.  It would be much better to solve the problem using a deterministic solution than a prayer based one.  Even though God hears all prayers, the answer might be no ...

> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of Steven M. McNeese
> Sent: Wednesday, 20 May, 2015 16:09
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] AUTOINC vs. UUIDs
>
> You misunderstood. If an application running on a tablet for 2 users add
> rows to a local database with an auto increment key, each would get a key
> based on their database an let's say for grins they both start with a new
> clean database. User 1 gets key 1 and user 2 gets key 1. Now they push
> their data to a server each with key 1 but different data. That doesn't
> work. Both records needs to be added to the server database - a collection
> of all the data collected on the client tablets.
>
> Sent from my iPhone
>
> > On May 20, 2015, at 4:38 PM, Jean-Christophe Deschamps
> <[hidden email]> wrote:
> >
> > At 23:24 20/05/2015, you wrote:
> >
> >> On Wed, May 20, 2015 at 2:20 PM, Steven M. McNeese
> >> <[hidden email]> wrote:
> >> > Often times people will use GUIDs as primary keys when different
> systems need to generate
> >> > rows and then merge together. Like an application that works with
> offline clients that push the
> >> > data to a server when the connect. However there are other ways of
> accomplishing the same thing.
> >>
> >> For curiosity - Is there a site/blog post somewhere
> >> enumerating/listing these other ways ?
> >
> > I don't know, but let's say your rowids range from -9223372036854775807
> to 9223372036854775807, that's 18446744073709551614 possible rowids.
> >
> > Imagine that in 50 years, the total population on Earth will grow to
> (say) 50 billion people (I hope it won't!).
> >
> > 18 446 744 073 709 551 614 / 50 000 000 000 = 368 934 881.474 191 032 28
> >
> > That leaves you the possibility to assign a unique identifier to every
> potential client on the planet (and a big one) in nearly 369 million
> servers concurrently without the faintest risk of collision.
> >
> > At this rate, you may limit rowids to only positive integers ... and
> hire a large army of telemarketers.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
123