Re: [sqlite] BLOBs and NULLs

[Francisco Tapia]

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

Re: [sqlite] BLOBs and NULLs

[Keith Medcalf]

On Wed, 23 Apr 2014 17:51:17 +0200
 Stephan Beal  wrote:

>On Wed, Apr 23, 2014 at 6:56 AM, Keith Medcalf 
>wrote:

>> You don't ever really need a GUID at all.  Simply use an "integer
primary
>> key" (an integer starting at 1) and simply pretend that it is being
added
>> to the applicable base GUID of your random choosing.  Everything
will still
>> be unique and you will have saved yourself a crap load of storage
space,
>> index space, and conserved countless CPU cycles so that they can be
spent
>> on something more productive productive.

>> I have never seen a need to actually use a GUID for anything, it is
a
>> ridiculous concept.

>Until one implements a DCVS (i.e. Fossil), at which point sequential
>numbers become literally impossible to generate.

Ok.  I can see where it may be necessary in a fully distributed system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Gerry Snyder
> Sent: Wednesday, April 23, 2014 2:36 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] BLOBs and NULLs
>
> On 4/23/2014 10:21 AM, Drago, William @ MWG - NARDAEAST wrote:
> >  If I was sure I wouldn't be merging data I might use timer ticks
> as my ID, but I'm not sure and I can't take the chance.
> >
> > -Bill
>
> Would it be possible to use INTEGER PRIMARY KEY AUTOINCREMENT for the
> ID, and manually start each test station at an ID value a billion
> larger than the previous one? Or whatever delta makes sense? Then
> collision could never happen.

Sure, but why make it so complicated? I understand the desire to save space, 
but geez, we're talking about a few extra bytes per row in a database that will 
be used for low volume widget testing on PC's with terabyte size disk drives. 
GUID vs. Int is not going to make a difference in this case.

> And, as a possible bonus, the ID would
> indicate which station the row came from.

I'm storing computer name and hard drive serial number, so that's already taken 
care of.

>
> Gerry
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Gerry Snyder]

On 4/23/2014 10:21 AM, Drago, William @ MWG - NARDAEAST wrote:

 If I was sure I wouldn't be merging data I might use timer ticks as my ID, 
but I'm not sure and I can't take the chance.

-Bill


Would it be possible to use INTEGER PRIMARY KEY AUTOINCREMENT for the 
ID, and manually start each test station at an ID value a billion larger 
than the previous one? Or whatever delta makes sense? Then collision 
could never happen. And, as a possible bonus, the ID would indicate 
which station the row came from.



Gerry

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

Re: [sqlite] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

The possibility of having to merge data from several independent test stations 
is what made me think of using GUID in the first place. I don't think there's a 
better way.

As for wasted space, the few extra bytes needed by GUID is a drop in the bucket 
compared to the blobs I'm storing. If I was sure I wouldn't be merging data I 
might use timer ticks as my ID, but I'm not sure and I can't take the chance.

-Bill

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Stephan Beal
> Sent: Wednesday, April 23, 2014 11:51 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] BLOBs and NULLs
>
> On Wed, Apr 23, 2014 at 6:56 AM, Keith Medcalf <kmedc...@dessus.com>
> wrote:
>
> > You don't ever really need a GUID at all.  Simply use an "integer
> > primary key" (an integer starting at 1) and simply pretend that it is
> > being added to the applicable base GUID of your random choosing.
> > Everything will still be unique and you will have saved yourself a
> > crap load of storage space, index space, and conserved countless CPU
> > cycles so that they can be spent on something more productive
> productive.
> >
> > I have never seen a need to actually use a GUID for anything, it is a
> > ridiculous concept.
> >
>
> Until one implements a DCVS (i.e. Fossil), at which point sequential
> numbers become literally impossible to generate.
>
>
> --
> - 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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Stephan Beal]

On Wed, Apr 23, 2014 at 6:56 AM, Keith Medcalf  wrote:

> You don't ever really need a GUID at all.  Simply use an "integer primary
> key" (an integer starting at 1) and simply pretend that it is being added
> to the applicable base GUID of your random choosing.  Everything will still
> be unique and you will have saved yourself a crap load of storage space,
> index space, and conserved countless CPU cycles so that they can be spent
> on something more productive productive.
>
> I have never seen a need to actually use a GUID for anything, it is a
> ridiculous concept.
>

Until one implements a DCVS (i.e. Fossil), at which point sequential
numbers become literally impossible to generate.


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

Re: [sqlite] BLOBs and NULLs

[Keith Medcalf]

>> In summary: the context of a GUID defines its "scope of required
>> uniqueness," and a 16-byte GUID is essentially globally unique so long
>> as
>> it has no collisions within its context(s). (i.e. who cares if SHA1s
>> collide, so long as it's not in the same repo?)
>
>You might be interested in UUIDs, which define the scope and provide
>methods for presenting hashed, sequential and random GUIDs within that
>scope:
>
>
>
>Libraries are available for many languages to generate UUIDs under many
>of these schemes.
>
>
>On 22 Apr 2014, at 6:57pm, RSmith  wrote:
>
>> Just to add  - I cannot imagine why you would have dashes to start with
>
>Take, for example, this GUID:
>
>550e8400-e29b-41d4-a716-44de5544ac00
>
>A program (or a TABLE) frequently handles many GUIDs which differ only in
>one of the fields.  For instance a program may have to represent a GUID
>externally (on the screen and output files) with the complete GUID, but
>it could store items internally (perhaps in a database) using only the
>last 12 hex digits, or as an integer up to 2^48.  Since it know that all
>people GUIDs start
>
>550e8400-e29b-41d4-a716-
>
>whereas all vehicles GUIDs start
>
>550e8404-31e6-41d4-a716-

You don't ever really need a GUID at all.  Simply use an "integer primary key" 
(an integer starting at 1) and simply pretend that it is being added to the 
applicable base GUID of your random choosing.  Everything will still be unique 
and you will have saved yourself a crap load of storage space, index space, and 
conserved countless CPU cycles so that they can be spent on something more 
productive productive.

I have never seen a need to actually use a GUID for anything, it is a 
ridiculous concept.




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

Re: [sqlite] BLOBs and NULLs

[Peter Aronson]

SQLite seemed to provide good randomness in my (admittedly informal) tests.
 
Peter

From: jose isaias cabrera <cabr...@wrc.xerox.com>
>To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Database 
><sqlite-users@sqlite.org> 
>Sent: Tuesday, April 22, 2014 1:06 PM
>Subject: Re: [sqlite] BLOBs and NULLs
>
>
>
>"Peter Aronson" wrote...
>
>
>> If you want to use sqlite3_randomness to generate a Version 4 UUID 
>> according to RFC4122, the following code will can be used:
>>
>> unsigned char uuid_data[16];
>>
>> /* We'll generate a version 4 UUID as per RFC4122. Start by generating
>> 128 bits of randomness (we will use 122 of them). */
>> sqlite3_randomness (16,uuid_data);
>>
>> /* Set the two most significant bits (bits 6 and 7) of the
>> clock_seq_hi_and_reserved field to zero and one, respectively. */
>> uuid_data[8] &= 0x3f;
>> uuid_data[8] |= 0x80;
>> /* Set the four most significant bits (bits 12 through 15) of the
>> time_hi_and_version field to the 4-bit version number from
>> Section 4.1.3 (which is 4). */
>> uuid_data[6] &= 0x0f;
>> uuid_data[6] |= 0x40;
>>
>> This assumes that sqlite3_randomness generates sufficiently good random 
>> numbers, but it appears to in my tests.
>
>Are you saying that sqlite3 does not appear to provide "good random numbers" 
>in your tests, or that it appears to, in your tests?
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[jose isaias cabrera]

"Peter Aronson" wrote...


If you want to use sqlite3_randomness to generate a Version 4 UUID 
according to RFC4122, the following code will can be used:


unsigned char uuid_data[16];

/* We'll generate a version 4 UUID as per RFC4122. Start by generating
128 bits of randomness (we will use 122 of them). */
sqlite3_randomness (16,uuid_data);

/* Set the two most significant bits (bits 6 and 7) of the
clock_seq_hi_and_reserved field to zero and one, respectively. */
uuid_data[8] &= 0x3f;
uuid_data[8] |= 0x80;
/* Set the four most significant bits (bits 12 through 15) of the
time_hi_and_version field to the 4-bit version number from
Section 4.1.3 (which is 4). */
uuid_data[6] &= 0x0f;
uuid_data[6] |= 0x40;

This assumes that sqlite3_randomness generates sufficiently good random 
numbers, but it appears to in my tests.


Are you saying that sqlite3 does not appear to provide "good random numbers" 
in your tests, or that it appears to, in your tests?


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

Re: [sqlite] BLOBs and NULLs

[Peter Aronson]

If you want to use sqlite3_randomness to generate a Version 4 UUID according to 
RFC4122, the following code will can be used:
 
  unsigned char  uuid_data[16];

  /* We'll generate a version 4 UUID as per RFC4122.  Start by generating
 128 bits of randomness (we will use 122 of them). */
  sqlite3_randomness (16,uuid_data);
  
  /* Set the two most significant bits (bits 6 and 7) of the 
 clock_seq_hi_and_reserved field to zero and one, respectively. */
  uuid_data[8] &= 0x3f;
  uuid_data[8] |= 0x80;
  /* Set the four most significant bits (bits 12 through 15) of the
 time_hi_and_version field to the 4-bit version number from
 Section 4.1.3 (which is 4). */
  uuid_data[6] &= 0x0f;
  uuid_data[6] |= 0x40;

This assumes that sqlite3_randomness generates sufficiently good random 
numbers, but it appears to in my tests.
 
Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Dominique Devienne]

>> That's why I wrote "our galaxy", not the "whole universe" ;) --DD
>
>
> Hehe, my bad... but that only changes a few orders of magnitude, there's only 
> a few billion galaxies :D

OK, you got me! After reading
http://www.universetoday.com/36302/atoms-in-the-universe/, 1e38 is not
even enough for our Sun's atoms, so I was way wrong indeed.

I stand humbly corrected. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Stephan Beal]

On Tue, Apr 22, 2014 at 8:55 PM, Dominique Devienne wrote:

> > than using string-format data (be sure to use SQLITE_TRANSIENT when
> binding
> > the memory, too).
>

Sorry - i meant SQLITE_STATIC. If your memory will outlive the step() call
then use that, _NOT_ SQLITE_TRANSIENT, to avoid that sqlite makes a copy of
the memory.

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

Re: [sqlite] BLOBs and NULLs

[RSmith]

On 2014/04/22 20:52, Dominique Devienne wrote:

On Tue, Apr 22, 2014 at 8:46 PM, RSmith  wrote:

On 2014/04/22 20:06, Dominique Devienne wrote:

Regarding the uniqueness argument made by DRH, it's actually very hard
to generate 2 random-based GUIDS, given that a 128-bit is a very very
large number. It is said that 128-bit is large enough to store the
estimated number of atoms in our galaxy.//
current estimates

are between 78 and 82 orders of magnitude (that's 1.0E+82) of atoms in the
known universe in decimal, which would be around ~1.0 x 2^270 and obviously
require around 270 bits to store.

That's why I wrote "our galaxy", not the "whole universe" ;) --DD


Hehe, my bad... but that only changes a few orders of magnitude, there's only a 
few billion galaxies :D

You'd still need over 200 bits for just our galaxy!

Atoms are pretty small it seems...


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

Re: [sqlite] BLOBs and NULLs

[Dominique Devienne]

On Tue, Apr 22, 2014 at 8:47 PM, Stephan Beal  wrote:
> On Tue, Apr 22, 2014 at 8:25 PM, Dominique Devienne 
> wrote:
>
>> Yet I don't see the point of a BIGINT either. A blob can effectively
>> act as a arbitrary sized integer already, albeit one stored in base
>> 256 and on which you cannot do arithmetic, but that's OK and enough to
>> use it as a PK / FK.
>>
>
> A blob can store raw binary data, i.e. raw integers from memory. Just be
> sure to encode/decode them if you want their stored representations to be
> platform-portable (big vs little endian). You can bind a blob using
> (, sizeof(myInt)) if you really want to, it just won't be
> platform-portable without settling on an encoding. If the goal is only
> performance, though, it might (without encoding) be (marginally) faster
> than using string-format data (be sure to use SQLITE_TRANSIENT when binding
> the memory, too).

Thanks for the advise. No, I'm not binding endian-specific native C
integer variables, but a uchar[16], so there are no endianness issues.

I mentally assimilate GUIDs are integers, but the code deals with raw
memory. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Simon Slavin]

On 22 Apr 2014, at 4:55pm, Dominique Devienne  wrote:

> Simply because of the extra space needed to store it. 36 bytes vs 16
> bytes. That's 20 wasted bytes for the PK, and everytime that PK is
> references in other tables' FKs too. Times millions of rows, it adds
> up, for nothing. The GUID is no less "genuine" as you put it, just
> because it's stored as a 16-bytes blob rather than the canonical
> 36-char text preferred by humans.

And if you use GUIDs a lot, make two functions for SQLite which convert to and 
from text form.  Loading external functions is something SQLite really is 
excellent at.


On 22 Apr 2014, at 5:57pm, Stephan Beal  wrote:

> http://www.w3.org/DesignIssues/Axioms.html#nonunique
> 
> In summary: the context of a GUID defines its "scope of required
> uniqueness," and a 16-byte GUID is essentially globally unique so long as
> it has no collisions within its context(s). (i.e. who cares if SHA1s
> collide, so long as it's not in the same repo?)

You might be interested in UUIDs, which define the scope and provide methods 
for presenting hashed, sequential and random GUIDs within that scope:



Libraries are available for many languages to generate UUIDs under many of 
these schemes.


On 22 Apr 2014, at 6:57pm, RSmith  wrote:

> Just to add  - I cannot imagine why you would have dashes to start with

Take, for example, this GUID:

550e8400-e29b-41d4-a716-44de5544ac00

A program (or a TABLE) frequently handles many GUIDs which differ only in one 
of the fields.  For instance a program may have to represent a GUID externally 
(on the screen and output files) with the complete GUID, but it could store 
items internally (perhaps in a database) using only the last 12 hex digits, or 
as an integer up to 2^48.  Since it know that all people GUIDs start

550e8400-e29b-41d4-a716-

whereas all vehicles GUIDs start

550e8404-31e6-41d4-a716-

there is no need to store them all in a table where the column tells you 
definitively whether you're talking about a person or a vehicle.

When you're doing a lot of this sort of thing having the hyphens visible speeds 
up human time in figuring out what they're looking at.

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

Re: [sqlite] BLOBs and NULLs

[Dominique Devienne]

On Tue, Apr 22, 2014 at 8:46 PM, RSmith  wrote:
> On 2014/04/22 20:06, Dominique Devienne wrote:
>> Regarding the uniqueness argument made by DRH, it's actually very hard
>> to generate 2 random-based GUIDS, given that a 128-bit is a very very
>> large number. It is said that 128-bit is large enough to store the
>> estimated number of atoms in our galaxy.//
>
>> current estimates
> are between 78 and 82 orders of magnitude (that's 1.0E+82) of atoms in the
> known universe in decimal, which would be around ~1.0 x 2^270 and obviously
> require around 270 bits to store.

That's why I wrote "our galaxy", not the "whole universe" ;) --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Dominique Devienne]

On Tue, Apr 22, 2014 at 8:36 PM, Dominique Devienne  wrote:
> On Tue, Apr 22, 2014 at 8:16 PM, Richard Hipp  wrote:
>> On Tue, Apr 22, 2014 at 2:06 PM, Dominique Devienne 
>> wrote:
>>
>>> Regarding the uniqueness argument made by DRH, it's actually very hard
>>> to generate 2 random-based GUIDS [that collide], given that a 128-bit is a
>>> very very large number.
>>
>> This is called the "Birthday Paradox".  Ask Google for more information.
>
> Thanks for that Richard. Live and learn ;)

Actually, that Wikipedia article has the number of GUIDs necessary to
achieve a given probability of collisions in
http://en.wikipedia.org/wiki/Birthday_problem#Probability_table and
even goes to mention

"For comparison, 10e−18 to 10e−15 is the uncorrectable bit error rate
of a typical hard disk.[6] In theory, 128-bit hash functions, such as
MD5, should stay within that range until about 820 billion documents,
even if its possible outputs are many more"

So even generating a trillion 128-bit GUID, the probability of a
collision is still astonishingly small, in the same order as hard disk
error rates :) That's good enough for me! --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Stephan Beal]

On Tue, Apr 22, 2014 at 8:25 PM, Dominique Devienne wrote:

> Yet I don't see the point of a BIGINT either. A blob can effectively
> act as a arbitrary sized integer already, albeit one stored in base
> 256 and on which you cannot do arithmetic, but that's OK and enough to
> use it as a PK / FK.
>

A blob can store raw binary data, i.e. raw integers from memory. Just be
sure to encode/decode them if you want their stored representations to be
platform-portable (big vs little endian). You can bind a blob using
(, sizeof(myInt)) if you really want to, it just won't be
platform-portable without settling on an encoding. If the goal is only
performance, though, it might (without encoding) be (marginally) faster
than using string-format data (be sure to use SQLITE_TRANSIENT when binding
the memory, too).

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

Re: [sqlite] BLOBs and NULLs

[RSmith]

On 2014/04/22 20:06, Dominique Devienne wrote:


Regarding the uniqueness argument made by DRH, it's actually very hard
to generate 2 random-based GUIDS, given that a 128-bit is a very very
large number. It is said that 128-bit is large enough to store the
estimated number of atoms in our galaxy.//


I'm all with you on the idea and you can find the paradox described on the net (it's an actual thing), but my quick interlude here 
is specific to the statement above about being able to store the estimated number of atoms in the Universe. I think you might be 
thinking of SHA 256, because that's closer to how many will be needed to accurately store it - current estimates are between 78 and 
82 orders of magnitude (that's 1.0E+82) of atoms in the known universe in decimal, which would be around ~1.0 x 2^270 and obviously 
require around 270 bits to store. Of course Stars are manufacturing (and killing) atoms all the time, so an estimate will have to do!


in case anyone is interested in the actual math:
https://www.wolframalpha.com/input/?i=10^82+%3D+2^x

Or in the physics:
http://www.universetoday.com/36302/atoms-in-the-universe/



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

Re: [sqlite] BLOBs and NULLs

[Richard Hipp]

On Tue, Apr 22, 2014 at 2:36 PM, Dominique Devienne wrote:

> Said Google tells me 2^128 - 1 = 3.4028237e+38
>
> and that sqrt(2^128 - 1) = 1.8446744e+19
>
> You've confused a 128-bit with a 64-bit integer in your 4 billion
> approximation, no?
>

Yes.  For a moment there, I was taking 2^64 was 4 billion.  It's 2^32 that
is 4 billion.  2^64 is larger.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Dominique Devienne]

On Tue, Apr 22, 2014 at 8:16 PM, Richard Hipp  wrote:
> On Tue, Apr 22, 2014 at 2:06 PM, Dominique Devienne 
> wrote:
>
>> Regarding the uniqueness argument made by DRH, it's actually very hard
>> to generate 2 random-based GUIDS [that collide], given that a 128-bit is a
>> very very large number.
>
> This is called the "Birthday Paradox".  Ask Google for more information.

Thanks for that Richard. Live and learn ;)

> To a good approximation, if there are N possible values, you need to
> generate sqrt(N) of them before you have a 50/50 chance of getting a
> collision.  (Wikipedia has the exact formula, if you are interested, but
> the approximation is usually good enough.)
>
> So for a 128-bit GUID, you'd expect to get a collision after generating 4
> billion of them, or so.

Said Google tells me 2^128 - 1 = 3.4028237e+38

and that sqrt(2^128 - 1) = 1.8446744e+19

You've confused a 128-bit with a 64-bit integer in your 4 billion
approximation, no?

> The above assumes you have a good source of randomness.  The "rand()"
> function in your favorite programming language is often not quite that
> good.  But random() in SQLite does a decent job, at last on Unix where it
> can be seeded using /dev/random.

FWIW, I used the Boost's
http://www.boost.org/doc/libs/1_47_0/doc/html/boost/random/mt19937.html
for the RNG. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

I can generate the GUID as a 16 byte hex string, so the dashes are no problem. 
I'm working with VEE (similar to LabView) and .Net, so a lot of the mundane 
stuff is done for me with nifty little methods like ToArray(), ToString(), etc..


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of RSmith
Sent: Tuesday, April 22, 2014 1:57 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] BLOBs and NULLs


On 2014/04/22 19:12, Richard Hipp wrote:
> On Tue, Apr 22, 2014 at 1:07 PM, Drago, William @ MWG - NARDAEAST <
> william.dr...@l-3com.com> wrote:
>
>> Does blob ignore them if they are included?
>>
> No.  That would be a syntax error.  The dashes in (strict) GUIDs are
> an arbitrary construct (perhaps originally designed to promote
> readability - as if anybody ever actually reads a GUID).  If you want
> to store the GUID as a BLOB then you'll have to strip the dashes
> yourselve (in your
> application) and do the hex-to-binary conversion yourself.

Just to add  - I cannot imagine why you would have dashes to start with, do you 
have a GUID generator that pumps out TEXT or STRING values? Usually they should 
be accompanied (in most libraries) by a function that pops out the actual 
16/20/32-byte BLOB as a stream or array of bytes/integers/whatever, and it 
usually involves some cost to convert it to strings with dashes etc. - a nice 
optimisation if those are no longer needed.

If you DO only get them as string though, the web is full of little bits of 
conversion code, and we will be glad to supply you with some too if you say 
which environment, function, etc.










___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Dominique Devienne]

On Tue, Apr 22, 2014 at 6:48 PM, Richard Hipp  wrote:
> On Tue, Apr 22, 2014 at 12:37 PM, Neville Dastur
>> I would hazard a guess that most mobile apps that use an internal DB, use
>> sqlite. With inconsistent mobile network coverage, having pure client side
>> PK generation is a must and GUIDs solve that problem well.

Exactly.

>> Has the time not come to support a 128bit INT that can then be used for GUID?

Yet I don't see the point of a BIGINT either. A blob can effectively
act as a arbitrary sized integer already, albeit one stored in base
256 and on which you cannot do arithmetic, but that's OK and enough to
use it as a PK / FK.

> "GUID" means different things to different people.  There are some "GUID"
> standards out there that people use.  But I take a more flexible approach
> and say that a "GUID" is any "Globally Unique IDentifier".  This
> generalized definition of "GUID" is not necessarily 128 bits (though I
> would argue 128 bits should be the bare minimum.)

Many people, including me, do refer to that one standard Simon already
linked to. And it is 128-bit.

> Fossil generates some of its "GUID"s using the SHA1 hash algorithm.  Other
> GUIDs (for example for ticket IDs) are generated using:
>
> SELECT lower(hex(randomblob(20)));
>
> You can increase the 20 to make the GUIDs as "globally unique" as you
> want.  The GUIDs discussed previously in this thread seem use 16 instead of
> 20 and thus are less unique.

But again, random GUIDs and (secure) hashes like SHA1 are different beats.

To compute your SHA1, you take an arbitrary large "content", and
process it to generate a hopefully unique but definitely not random
"number". The same content must generate the same "number, every time.
And changing just one byte of the content must generate an entirely
different hash.

While a random generated GUID "only" needs to be unique, generated out
of thin air, and strive to never generate the same "number" twice.

> So a 128bit int really isn't going to help here because as soon as you have
> one, you'll need a 160bit int.  And so forth...

It is enough for all practical purposes, with a good RNG.

> Better to simply use a BLOB which can have arbitrary length.  You aren't
> going to be adding and subtracting the GUIDs, so no need to call them
> integers.

I'm with you there.

> The average length of the derived, locally-unique identifiers in Fossil is
> about two bytes.  That is more compact than 16 or 20 bytes, regardless of
> whether you call it a BLOB or an INT.  So having a BIGINT capability
> doesn't really help you there either.

Sure. As you say, different applications have different requirements.

We load entities from different completely unrelated datastores, and
by using GUID PKs, we never run in collisions, unlike locally derived
local ids.

We can even have cross-datastore FKs using GUIDs, albeit not enforced
like intra-datastore FKs can be of course.

The decentralized nature of GUIDs, and it's practical uniqueness, do
make it a good choice for PK/FK IMHO, while remaining short enough a
key. Sure, 2 bytes beat 16 bytes (although with 2 bytes you're likely
on the low end; we can have in the millions of rows in our DBs).
Again, my $0.02. I was more reacting to James' anti-GUID paragraph ;)
--DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Richard Hipp]

On Tue, Apr 22, 2014 at 2:06 PM, Dominique Devienne wrote:

> Regarding the uniqueness argument made by DRH, it's actually very hard
> to generate 2 random-based GUIDS [that collide], given that a 128-bit is a
> very very
> large number.
>

This is called the "Birthday Paradox".  Ask Google for more information.

To a good approximation, if there are N possible values, you need to
generate sqrt(N) of them before you have a 50/50 chance of getting a
collision.  (Wikipedia has the exact formula, if you are interested, but
the approximation is usually good enough.)

So for a 128-bit GUID, you'd expect to get a collision after generating 4
billion of them, or so.

The above assumes you have a good source of randomness.  The "rand()"
function in your favorite programming language is often not quite that
good.  But random() in SQLite does a decent job, at last on Unix where it
can be seeded using /dev/random.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Dominique Devienne]

On Tue, Apr 22, 2014 at 6:57 PM, Stephan Beal  wrote:
> On Tue, Apr 22, 2014 at 6:48 PM, Richard Hipp  wrote:
>> Fossil generates some of its "GUID"s using the SHA1 hash algorithm.  Other
>> GUIDs (for example for ticket IDs) are generated using:
>>
>> SELECT lower(hex(randomblob(20)));
>>
>> You can increase the 20 to make the GUIDs as "globally unique" as you
>> want.  The GUIDs discussed previously in this thread seem use 16 instead of
>> 20 and thus are less unique.
>>
>
> That reminds me of a specific snippet from this article:
>
> http://www.w3.org/DesignIssues/Axioms.html#nonunique
>
> In summary: the context of a GUID defines its "scope of required
> uniqueness," and a 16-byte GUID is essentially globally unique so long as
> it has no collisions within its context(s). (i.e. who cares if SHA1s
> collide, so long as it's not in the same repo?)

First, SHA1 hashes and GUID, although they look the same (size
notwithstanding), are not the same. Hashes like SHA1 derive their
value from actual content (at a point in time), so they are in fact
better than randomly generated GUIDs. But not every applications can
easily compute content hashes (using SHA1, SHA256, or whatever other
secure hashing algo) for their content. And for mutable entities,
content hashes would be definition also mutate (ignoring very unlikely
collisions), unlike GUIDs which are arbitrary and immutable "by
design", which makes them suitable as PKs of mutate entities.

Regarding the uniqueness argument made by DRH, it's actually very hard
to generate 2 random-based GUIDS, given that a 128-bit is a very very
large number. It is said that 128-bit is large enough to store the
estimated number of atoms in our galaxy. It's good enough for my own
uses. Being of the curious type, I wrote a little test to generate a
large number of GUIDs (using boost::uuid), then sort them, then look
for the longest prefix (byte-wise, not char wise). To keep things
simple, I did that in memory, so could only generate 1/2 a billion,
and the longest common prefix I found was 7 bytes, out of the 16
bytes. Intuitively, I suspect one must generate increasingly large
number of GUIDs to increase the common prefix length by 1 byte each
time, but I didn't verify this intuition.

So yes, in theory, one will eventually run out of bits using a 128-bit
(integer) GUID, but in practice I don't think it hardly matters.My
$0.02. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[RSmith]

On 2014/04/22 19:12, Richard Hipp wrote:

On Tue, Apr 22, 2014 at 1:07 PM, Drago, William @ MWG - NARDAEAST <
william.dr...@l-3com.com> wrote:


Does blob ignore them if they are included?


No.  That would be a syntax error.  The dashes in (strict) GUIDs are an
arbitrary construct (perhaps originally designed to promote readability -
as if anybody ever actually reads a GUID).  If you want to store the GUID
as a BLOB then you'll have to strip the dashes yourselve (in your
application) and do the hex-to-binary conversion yourself.


Just to add  - I cannot imagine why you would have dashes to start with, do you have a GUID generator that pumps out TEXT or STRING 
values? Usually they should be accompanied (in most libraries) by a function that pops out the actual 16/20/32-byte BLOB as a stream 
or array of bytes/integers/whatever, and it usually involves some cost to convert it to strings with dashes etc. - a nice 
optimisation if those are no longer needed.


If you DO only get them as string though, the web is full of little bits of conversion code, and we will be glad to supply you with 
some too if you say which environment, function, etc.











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

Re: [sqlite] BLOBs and NULLs

[Richard Hipp]

On Tue, Apr 22, 2014 at 1:07 PM, Drago, William @ MWG - NARDAEAST <
william.dr...@l-3com.com> wrote:

> Does blob ignore them if they are included?
>

No.  That would be a syntax error.  The dashes in (strict) GUIDs are an
arbitrary construct (perhaps originally designed to promote readability -
as if anybody ever actually reads a GUID).  If you want to store the GUID
as a BLOB then you'll have to strip the dashes yourselve (in your
application) and do the hex-to-binary conversion yourself.


>
> -Bill
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, April 22, 2014 12:56 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] BLOBs and NULLs
>
> On Tue, Apr 22, 2014 at 12:55 PM, Drago, William @ MWG - NARDAEAST <
> william.dr...@l-3com.com> wrote:
>
> > Cool. So it's treating each 2 digit pair as a single byte hex value,
> > but what does blob do with the dashes?
> >
>
> Since the dashes carry no information, you could leave them out.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
> attachments are solely for the use of the addressee and may contain
> information that is privileged or confidential. Any disclosure, use or
> distribution of the information contained herein is prohibited. In the
> event this e-mail contains technical data within the definition of the
> International Traffic in Arms Regulations or Export Administration
> Regulations, it is subject to the export control laws of the
> U.S.Government. The recipient should check this e-mail and any attachments
> for the presence of viruses as L-3 does not accept any liability associated
> with the transmission of this e-mail. If you have received this
> communication in error, please notify the sender by reply e-mail and
> immediately delete this message and any attachments.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

Does blob ignore them if they are included?

-Bill

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, April 22, 2014 12:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 12:55 PM, Drago, William @ MWG - NARDAEAST < 
william.dr...@l-3com.com> wrote:

> Cool. So it's treating each 2 digit pair as a single byte hex value,
> but what does blob do with the dashes?
>

Since the dashes carry no information, you could leave them out.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Richard Hipp]

On Tue, Apr 22, 2014 at 12:55 PM, Drago, William @ MWG - NARDAEAST <
william.dr...@l-3com.com> wrote:

> Cool. So it's treating each 2 digit pair as a single byte hex value, but
> what does blob do with the dashes?
>

Since the dashes carry no information, you could leave them out.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

Cool. So it's treating each 2 digit pair as a single byte hex value, but what 
does blob do with the dashes?


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Tuesday, April 22, 2014 11:55 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] BLOBs and NULLs

On Tue, Apr 22, 2014 at 5:35 PM, Drago, William @ MWG - NARDAEAST 
<william.dr...@l-3com.com> wrote:
>>I myself prefer create table foo (guid blob primary key [NOT NULL], ...).
>
> If a genuine GUID looks like this: 37af1247-2e77-4880-8f46-48803ae2cd0a, then 
> why blob and not text?

Simply because of the extra space needed to store it. 36 bytes vs 16 bytes. 
That's 20 wasted bytes for the PK, and everytime that PK is references in other 
tables' FKs too. Times millions of rows, it adds up, for nothing. The GUID is 
no less "genuine" as you put it, just because it's stored as a 16-bytes blob 
rather than the canonical 36-char text preferred by humans. The native code 
guids, e.g.
boost::uuid, also use 16 bytes, so conversions to/from the DB
(binding/defining) would required bytes-to-text, and text-to-byte conversions, 
again for no gain. If we ever show a GUID to the user, which is rather rare 
(and often a bug), sure, we pretty-print it as dash-separated hex, but 
otherwise the most compact "native"
representation is used everywhere else. For ad-hoc queries using the 
shell-tool, SQLite's x'ff' blob-literal comes in handy too, and
quote() [1] is used for blob-to-hex conversions in selects (nothing needed in 
blob-to-blob joins). --DD

[1] 
http://stackoverflow.com/questions/1039461/how-to-display-blob-value-using-xabc-binary-string-literal-syntax
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Richard Hipp]

On Tue, Apr 22, 2014 at 12:37 PM, Neville Dastur
wrote:

>
> On 22 Apr 2014, at 17:33, Richard Hipp  wrote:
>
> > The usual solution here is to have a table that maps GUIDs into small
> > locally-unique integers:
> >
> >CREATE TABLE guid_id(id INTEGER PRIMARY KEY, guid TEXT UNIQUE);
> >
> > Use the small integer "id" value for internal foreign keys and whatnot.
> > And use the guid_id table to map GUIDs to id when moving data in from and
> > out to the rest of the world.
>
> Sorry, but does this not just over complicate the problem.
>


I guess it depends on the problem.

The same idea is used in the schema for Fossil (http://www.fossil-scm.org/)
and it works quite well there.   But every problem is different.  It's an
engineering judgement.


> I would hazard a guess that most mobile apps that use an internal DB, use
> sqlite. With inconsistent mobile network coverage, having pure client side
> PK generation is a must and GUIDs solve that problem well. Has the time not
> come to support a 128bit INT that can then be used for GUID?
>

"GUID" means different things to different people.  There are some "GUID"
standards out there that people use.  But I take a more flexible approach
and say that a "GUID" is any "Globally Unique IDentifier".  This
generalized definition of "GUID" is not necessarily 128 bits (though I
would argue 128 bits should be the bare minimum.)

Fossil generates some of its "GUID"s using the SHA1 hash algorithm.  Other
GUIDs (for example for ticket IDs) are generated using:

SELECT lower(hex(randomblob(20)));

You can increase the 20 to make the GUIDs as "globally unique" as you
want.  The GUIDs discussed previously in this thread seem use 16 instead of
20 and thus are less unique.

So a 128bit int really isn't going to help here because as soon as you have
one, you'll need a 160bit int.  And so forth...

Better to simply use a BLOB which can have arbitrary length.  You aren't
going to be adding and subtracting the GUIDs, so no need to call them
integers.

The average length of the derived, locally-unique identifiers in Fossil is
about two bytes.  That is more compact than 16 or 20 bytes, regardless of
whether you call it a BLOB or an INT.  So having a BIGINT capability
doesn't really help you there either.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Neville Dastur]

On 22 Apr 2014, at 17:33, Richard Hipp  wrote:

> The usual solution here is to have a table that maps GUIDs into small
> locally-unique integers:
> 
>CREATE TABLE guid_id(id INTEGER PRIMARY KEY, guid TEXT UNIQUE);
> 
> Use the small integer "id" value for internal foreign keys and whatnot.
> And use the guid_id table to map GUIDs to id when moving data in from and
> out to the rest of the world.

Sorry, but does this not just over complicate the problem.

I would hazard a guess that most mobile apps that use an internal DB, use 
sqlite. With inconsistent mobile network coverage, having pure client side PK 
generation is a must and GUIDs solve that problem well. Has the time not come 
to support a 128bit INT that can then be used for GUID?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Richard Hipp]

The usual solution here is to have a table that maps GUIDs into small
locally-unique integers:

CREATE TABLE guid_id(id INTEGER PRIMARY KEY, guid TEXT UNIQUE);

Use the small integer "id" value for internal foreign keys and whatnot.
And use the guid_id table to map GUIDs to id when moving data in from and
out to the rest of the world.


On Tue, Apr 22, 2014 at 11:55 AM, Dominique Devienne wrote:

> On Tue, Apr 22, 2014 at 5:35 PM, Drago, William @ MWG - NARDAEAST
>  wrote:
> >>I myself prefer create table foo (guid blob primary key [NOT NULL], ...).
> >
> > If a genuine GUID looks like this: 37af1247-2e77-4880-8f46-48803ae2cd0a,
> then why blob and not text?
>
> Simply because of the extra space needed to store it. 36 bytes vs 16
> bytes. That's 20 wasted bytes for the PK, and everytime that PK is
> references in other tables' FKs too. Times millions of rows, it adds
> up, for nothing. The GUID is no less "genuine" as you put it, just
> because it's stored as a 16-bytes blob rather than the canonical
> 36-char text preferred by humans. The native code guids, e.g.
> boost::uuid, also use 16 bytes, so conversions to/from the DB
> (binding/defining) would required bytes-to-text, and text-to-byte
> conversions, again for no gain. If we ever show a GUID to the user,
> which is rather rare (and often a bug), sure, we pretty-print it as
> dash-separated hex, but otherwise the most compact "native"
> representation is used everywhere else. For ad-hoc queries using the
> shell-tool, SQLite's x'ff' blob-literal comes in handy too, and
> quote() [1] is used for blob-to-hex conversions in selects (nothing
> needed in blob-to-blob joins). --DD
>
> [1]
> http://stackoverflow.com/questions/1039461/how-to-display-blob-value-using-xabc-binary-string-literal-syntax
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Dominique Devienne]

On Tue, Apr 22, 2014 at 5:35 PM, Drago, William @ MWG - NARDAEAST
 wrote:
>>I myself prefer create table foo (guid blob primary key [NOT NULL], ...).
>
> If a genuine GUID looks like this: 37af1247-2e77-4880-8f46-48803ae2cd0a, then 
> why blob and not text?

Simply because of the extra space needed to store it. 36 bytes vs 16
bytes. That's 20 wasted bytes for the PK, and everytime that PK is
references in other tables' FKs too. Times millions of rows, it adds
up, for nothing. The GUID is no less "genuine" as you put it, just
because it's stored as a 16-bytes blob rather than the canonical
36-char text preferred by humans. The native code guids, e.g.
boost::uuid, also use 16 bytes, so conversions to/from the DB
(binding/defining) would required bytes-to-text, and text-to-byte
conversions, again for no gain. If we ever show a GUID to the user,
which is rather rare (and often a bug), sure, we pretty-print it as
dash-separated hex, but otherwise the most compact "native"
representation is used everywhere else. For ad-hoc queries using the
shell-tool, SQLite's x'ff' blob-literal comes in handy too, and
quote() [1] is used for blob-to-hex conversions in selects (nothing
needed in blob-to-blob joins). --DD

[1] 
http://stackoverflow.com/questions/1039461/how-to-display-blob-value-using-xabc-binary-string-literal-syntax
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

>I myself prefer create table foo (guid blob primary key [NOT NULL], ...).

If a genuine GUID looks like this: 37af1247-2e77-4880-8f46-48803ae2cd0a, then 
why blob and not text?

-Bill



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Tuesday, April 22, 2014 5:07 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] BLOBs and NULLs

On Mon, Apr 21, 2014 at 4:58 PM, James K. Lowden <jklow...@schemamania.org> 
wrote:
> On Mon, 21 Apr 2014 13:30:15 +
> "Drago, William @ MWG - NARDAEAST" <william.dr...@l-3com.com> wrote:
>
>> Should I split this table up into smaller tables to eliminate the
>> NULLs (e.g. use one table each for IL, Phase, RL, Isolation)?

> Your database design is a model of the real world.  The rules it
> enforces should reflect those of the world it models. The tuple (IL,
> Phase, RL, Isolation) doesn't have much meaning, does it, in the sense
> that *together* they say something special about whatever (GUID, Path)
> represent?  From your description, each individual tuple (e.g., (GUID,
> Path, IL)) is meaningful, but the presence of, say, IL without Phase
> is not.  Each is a separate, freestanding fact, justifying its own table.

Pushed to its extreme, this sounds like an EAV* model, which seems surprising 
coming from you James ;)

* http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

> The part I like least about your design (1) the use of a GUID and, in
> particular (2) the name "GUID" for the column.  If you are generating
> this GUID, don't; use an integer.  If you're not generating it -- if
> it comes to you from another source and therefore identifies something
> in "the real world" in some sense, OK.  Either way, use the name of
> the column to reflect the thing identified, not the datatype of the
> identifier.

First, about your GUID versus integer point (an often contentious debate), I'm 
on the (opposite) GUID side myself. And the reason is to increase concurrency 
and decrease contention. When you depend on a server-enforced AUTO-INCREMENTED 
key, you must go to the central server to get that unique id, which is a 
round-trip (in client-server scenarii), and sometimes you must also insert a 
row, before you potentially have all the facts (columns). A GUID on the other 
hand can be randomly generated client-side, completely independently of the 
server, and still be unique (since 128 bit, a very very large integer, and a 
good RNG). If you need to "merge" later tables or databases created 
independently, you won't have GUID conflicts, unlike AUTO-INCREMENTED integer 
PKs (the integers are used in FKs elsewhere, so any merge operation must map 
the old AUTO-INC PKs to the new AUTO-INC ones, and updates all FKs). So IMHO, a 
GUID PK is very appropropriate in many scenarii, as long as one remembers it i
 s only a Surrogate Key, i.e. a convenient fixed-sized, globally unique (and 
thus often immutable) single value to use in FKs (especially convenient in the 
face of complex composite natural keys), and not a Natural Key.

Second, the datatype is blob (or RAW(16) in Oracle), not GUID, and there's thus 
nothing wrong to call it what it is. Many people in the SQL community seem to 
like stuttering, and will call it foo_id or foo_uid or foo_guid for a foo 
table, but I myself prefer create table foo (guid blob primary key [NOT NULL], 
...).

It's mostly a matter of opinion and taste, but there are benefits to using 
GUIDs as SKs in some circumstances in my book. My $0.02. --DD

PS: For a long time, SQLite depended on that integer PK, whether one wanted it 
or not (and I wasn't aware saying PRIMARY KEY for a non-integer PK did not mean 
it's standard-required NOT NULL until reading a recent thread. I wish there was 
a single opt-in pragma to disable all non-standard behavior). Now there's 
WITOUT ROWID tables, but from reading this list, it seems like it forces you to 
a B-tree, instead of a B*-tree  (store rows in leafs only) like ROWID-based 
tables, which I believe means more expensive inserts via more B-tree 
rebalancing, which is especially bad with large (blob-using) rows, given SQLite 
lack of out-of-row blob storage.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the ex

Re: [sqlite] BLOBs and NULLs

[Dominique Devienne]

On Tue, Apr 22, 2014 at 12:05 PM, Simon Slavin  wrote:
> On 22 Apr 2014, at 10:07am, Dominique Devienne  wrote:
> Store them as 32 hex digits, or 32 hex digits with the minus signs in, or as 
> a 32-bit-length integer, I don't care, but have them conform to V1 or V4 
> Algorithm generation, or something else mentioned on that page.

There's really no point wasting space to store a stringified 36-char
GUID, rather than storing that same guid as 16-bytes. Which algo
generated the GUID is embedded in the GUID itself, if it's standard
conforming, and is irrelevant to relational integrity anyway. All
RDBMSs can blob-to-hex if you want to see a dash-less hex GUID stored
as a blob or RAW, and you can write something in whatever (C, PL/SQL,
TSQL, pure SQL etc...) to splice the dashes in if you really want to.
A guid is a guid, irrespective of its actual representation. And
there's nothing wrong calling it as such. Store them as hex-encoded
(or octal, or base-64, ...) text if that pleases you, but that doesn't
make it any more or any less a GUID. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Simon Slavin]

On 22 Apr 2014, at 10:07am, Dominique Devienne  wrote:

> using GUIDs

Don't particularly mind if anyone is using GUIDs, but if anyone is using 
calling something GUID can you please make sure it's a real GUID ?  They look 
like this:



Store them as 32 hex digits, or 32 hex digits with the minus signs in, or as a 
32-bit-length integer, I don't care, but have them conform to V1 or V4 
Algorithm generation, or something else mentioned on that page.

If you don't want to do that, that's fine: generate whatever keys you want.  
Just don't call them GUIDs.

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

Re: [sqlite] BLOBs and NULLs

[Dominique Devienne]

On Mon, Apr 21, 2014 at 4:58 PM, James K. Lowden
 wrote:
> On Mon, 21 Apr 2014 13:30:15 +
> "Drago, William @ MWG - NARDAEAST"  wrote:
>
>> Should I split this table up into smaller tables to eliminate the
>> NULLs (e.g. use one table each for IL, Phase, RL, Isolation)?

> Your database design is a model of the real world.  The rules it
> enforces should reflect those of the world it models. The tuple (IL,
> Phase, RL, Isolation) doesn't have much meaning, does it, in the sense
> that *together* they say something special about whatever (GUID, Path)
> represent?  From your description, each individual tuple (e.g., (GUID,
> Path, IL)) is meaningful, but the presence of, say, IL without Phase is
> not.  Each is a separate, freestanding fact, justifying its own table.

Pushed to its extreme, this sounds like an EAV* model, which seems
surprising coming from you James ;)

* http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

> The part I like least about your design (1) the use of a GUID and, in
> particular (2) the name "GUID" for the column.  If you are generating
> this GUID, don't; use an integer.  If you're not generating it -- if it
> comes to you from another source and therefore identifies something in
> "the real world" in some sense, OK.  Either way, use the name of the
> column to reflect the thing identified, not the datatype of the
> identifier.

First, about your GUID versus integer point (an often contentious
debate), I'm on the (opposite) GUID side myself. And the reason is to
increase concurrency and decrease contention. When you depend on a
server-enforced AUTO-INCREMENTED key, you must go to the central
server to get that unique id, which is a round-trip (in client-server
scenarii), and sometimes you must also insert a row, before you
potentially have all the facts (columns). A GUID on the other hand can
be randomly generated client-side, completely independently of the
server, and still be unique (since 128 bit, a very very large integer,
and a good RNG). If you need to "merge" later tables or databases
created independently, you won't have GUID conflicts, unlike
AUTO-INCREMENTED integer PKs (the integers are used in FKs elsewhere,
so any merge operation must map the old AUTO-INC PKs to the new
AUTO-INC ones, and updates all FKs). So IMHO, a GUID PK is very
appropropriate in many scenarii, as long as one remembers it is only a
Surrogate Key, i.e. a convenient fixed-sized, globally unique (and
thus often immutable) single value to use in FKs (especially
convenient in the face of complex composite natural keys), and not a
Natural Key.

Second, the datatype is blob (or RAW(16) in Oracle), not GUID, and
there's thus nothing wrong to call it what it is. Many people in the
SQL community seem to like stuttering, and will call it foo_id or
foo_uid or foo_guid for a foo table, but I myself prefer create table
foo (guid blob primary key [NOT NULL], ...).

It's mostly a matter of opinion and taste, but there are benefits to
using GUIDs as SKs in some circumstances in my book. My $0.02. --DD

PS: For a long time, SQLite depended on that integer PK, whether one
wanted it or not (and I wasn't aware saying PRIMARY KEY for a
non-integer PK did not mean it's standard-required NOT NULL until
reading a recent thread. I wish there was a single opt-in pragma to
disable all non-standard behavior). Now there's WITOUT ROWID tables,
but from reading this list, it seems like it forces you to a B-tree,
instead of a B*-tree  (store rows in leafs only) like ROWID-based
tables, which I believe means more expensive inserts via more B-tree
rebalancing, which is especially bad with large (blob-using) rows,
given SQLite lack of out-of-row blob storage.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

JKL,

>The part I like least about your design (1) the use of a GUID

I agree with everything you said about GUID. The GUID column will be named 
DataSetID and will be an integer.


>The tuple (IL, Phase, RL, Isolation) doesn't
>have much meaning, does it, in the sense that
>*together* they say something special about
>whatever (GUID, Path) represent?
>From your description, each individual tuple
> (e.g., (GUID, Path, IL)) is meaningful, but the
>presence of, say, IL without Phase is not.
>Each is a separate, freestanding fact,
>justifying its own table.

These parameters do mean more when taken together than separately. Much like a 
patient's height, weight, and blood pressure do give you some information, but 
knowing all 3 gives you significant information about the patient. If height, 
weight, and blood pressure should be in separate tables, then perhaps my 
measurements should be too.

This is a small, low volume database. I'm not sure the 4 tables vs. 1 table is 
going to make a big difference one way or another, but I do want the logic to 
be correct. If using 4 tables is the right way to do this, then that's what 
I'll do. If it's 6 of one, half dozen of the other, then  maybe I'll flip a 
coin.

BTW, this is going beyond SQLite and I don't want to upset the moderators. Feel 
free to contact me directly at my L3 email address.

-Bill

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of James K. Lowden
Sent: Monday, April 21, 2014 10:59 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] BLOBs and NULLs

On Mon, 21 Apr 2014 13:30:15 +
"Drago, William @ MWG - NARDAEAST" <william.dr...@l-3com.com> wrote:

> Should I split this table up into smaller tables to eliminate the
> NULLs (e.g. use one table each for IL, Phase, RL, Isolation)? I'm not
> sure what the best design choice would be.

While Dr. Hipp's answer focussed on correctness and performance.  From the 
logical side I suggest you consider four separate tables.

Your database design is a model of the real world.  The rules it enforces 
should reflect those of the world it models. The tuple (IL, Phase, RL, 
Isolation) doesn't have much meaning, does it, in the sense that *together* 
they say something special about whatever (GUID, Path) represent?  From your 
description, each individual tuple (e.g., (GUID, Path, IL)) is meaningful, but 
the presence of, say, IL without Phase is not.  Each is a separate, 
freestanding fact, justifying its own table.

The part I like least about your design (1) the use of a GUID and, in 
particular (2) the name "GUID" for the column.  If you are generating this 
GUID, don't; use an integer.  If you're not generating it -- if it comes to you 
from another source and therefore identifies something in "the real world" in 
some sense, OK.  Either way, use the name of the column to reflect the thing 
identified, not the datatype of the identifier.

HTH.

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOBs and NULLs

[James K. Lowden]

On Mon, 21 Apr 2014 13:30:15 +
"Drago, William @ MWG - NARDAEAST"  wrote:

> Should I split this table up into smaller tables to eliminate the
> NULLs (e.g. use one table each for IL, Phase, RL, Isolation)? I'm not
> sure what the best design choice would be.

While Dr. Hipp's answer focussed on correctness and performance.  From
the logical side I suggest you consider four separate tables. 

Your database design is a model of the real world.  The rules it
enforces should reflect those of the world it models. The tuple (IL,
Phase, RL, Isolation) doesn't have much meaning, does it, in the sense
that *together* they say something special about whatever (GUID, Path)
represent?  From your description, each individual tuple (e.g., (GUID,
Path, IL)) is meaningful, but the presence of, say, IL without Phase is
not.  Each is a separate, freestanding fact, justifying its own table.  

The part I like least about your design (1) the use of a GUID and, in
particular (2) the name "GUID" for the column.  If you are generating
this GUID, don't; use an integer.  If you're not generating it -- if it
comes to you from another source and therefore identifies something in
"the real world" in some sense, OK.  Either way, use the name of the
column to reflect the thing identified, not the datatype of the
identifier.  

HTH.  

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

Re: [sqlite] BLOBs and NULLs

[Kees Nuyt]

On Mon, 21 Apr 2014 13:30:15 +, "Drago, William @ MWG - NARDAEAST"
 wrote:

> Should I split this table up into smaller tables to
> eliminate the NULLs (e.g. use one table each for IL,
> Phase, RL, Isolation)?

Adding to what Richard said:

(3) NULLs are not a problem by themselves, they take hardly any storage
at all, ust the type indicator that every every row has for every
column.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] BLOBs and NULLs

[Richard Hipp]

On Mon, Apr 21, 2014 at 9:30 AM, Drago, William @ MWG - NARDAEAST <
william.dr...@l-3com.com> wrote:

> All,
>
> One of the tables in my database has 4 columns that will hold small (under
> 5K) BLOBs. In many cases there will be no data at all in one or more of
> these columns (see sample below). Does this present any kind of problem?


No.

Two things to be aware of:

(1) When reading a row, SQLite reads from beginning to end.  So if you have
some small integer or boolean fields, it is better to put them first in the
table. Otherwise, SQLite has to read past the big BLOBs in order to get to
the smaller fields, even if the BLOBs themselves are not used.

(2) When changing any column of a row, the entire row is rewritten,
including the unchanged columns.  So if you have some smaller fields
(integers and booleans) that change frequently and also some large BLOBs
that change infrequently, you might consider factoring the BLOBs out into a
separate table just so they don't have to be rewritten every time a boolean
in the same row changes.

Both points above a purely performance considerations.  You should always
get the correct answer either way.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BLOBs and NULLs

[Drago, William @ MWG - NARDAEAST]

All,

One of the tables in my database has 4 columns that will hold small (under 5K) 
BLOBs. In many cases there will be no data at all in one or more of these 
columns (see sample below). Does this present any kind of problem? Should I 
split this table up into smaller tables to eliminate the NULLs (e.g. use one 
table each for IL, Phase, RL, Isolation)? I'm not sure what the best design 
choice would be.

Here is a sample of what the data will look like:

GUIDPathIL  Phase   RL  Isolation
9a778c0e1   BLOBBLOBBLOBNULL
9a778c0e2   BLOBBLOBBLOBNULL
9a778c0e3   BLOBBLOBBLOBNULL
9a778c0e4   BLOBBLOBBLOBNULL
9a778c0e5   NULLNULLBLOBNULL
9a778c0e6   BLOBBLOBBLOBNULL
9a778c0e7   BLOBBLOBBLOBNULL
9a778c0e8   BLOBBLOBBLOBNULL
9a778c0e9   BLOBBLOBBLOBNULL
9a778c0e10  NULLNULLNULLBLOB
9a778c0e11  NULLNULLNULLBLOB
9a778c0e12  NULLNULLNULLBLOB
9a778c0e13  NULLNULLNULLBLOB
9a778c0e14  NULLNULLNULLBLOB
9a778c0e15  NULLNULLNULLBLOB
9a778c0e16  NULLNULLNULLBLOB
9a778c0e17  NULLNULLNULLBLOB
9a778c0e18  NULLNULLNULLBLOB
9a778c0e19  NULLNULLNULLBLOB
23239d6b1   BLOBBLOBBLOBNULL
23239d6b2   BLOBBLOBBLOBNULL
23239d6b3   BLOBBLOBBLOBNULL
23239d6b4   BLOBBLOBBLOBNULL
23239d6b5   NULLNULLBLOBNULL
23239d6b6   BLOBBLOBBLOBNULL
23239d6b7   BLOBBLOBBLOBNULL
23239d6b8   BLOBBLOBBLOBNULL
23239d6b9   BLOBBLOBBLOBNULL
23239d6b10  NULLNULLNULLBLOB
23239d6b11  NULLNULLNULLBLOB
23239d6b12  NULLNULLNULLBLOB
23239d6b13  NULLNULLNULLBLOB
23239d6b14  NULLNULLNULLBLOB
23239d6b15  NULLNULLNULLBLOB
23239d6b16  NULLNULLNULLBLOB
23239d6b17  NULLNULLNULLBLOB
23239d6b18  NULLNULLNULLBLOB
23239d6b19  NULLNULLNULLBLOB


Thanks for your help...
--
Bill Drago
Senior Engineer

L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users