Re: [sqlite] binary key, blob or text?

[Petite Abeille]

On Sep 30, 2011, at 2:34 PM, Igor Tandetnik wrote:

>> Any gotchas in using blob as keys (unique or otherwise)?
> 
> None that I know of.

Thanks Igor :)

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

Re: [sqlite] binary key, blob or text?

[Igor Tandetnik]

Petite Abeille  wrote:
> From an efficiency point of view, would one be better off storing that key as 
> a blob or text? In other words, store the raw byte
> sequence directly or use a hex text representation of it? 

Blob

> So, binary:
> 
>hashblob not null collate binary, -- as the raw byte sequence

Collation doesn't apply to blobs - they are always compared as binary.

> Any gotchas in using blob as keys (unique or otherwise)?

None that I know of.
-- 
Igor Tandetnik

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

[sqlite] binary key, blob or text?

[Petite Abeille]

Hello,

Say one has a hash value as an indexed key for a table.

>From an efficiency point of view, would one be better off storing that key as 
>a blob or text? In other words, store the raw byte sequence directly or use a 
>hex text representation of it?

So, binary:

hashblob not null collate binary, -- as the raw byte sequence

Or text:

hashtext not null collate binary, -- as hex encoded

And then:

  create index if not exists foo_hash on foo( hash )

>From a storage perspective, using the blob seems to be more straightforward 
>(no encoding overhead), so, say, given a SHA1 hash, 20 bytes raw vs. 40 bytes 
>hex encoded. 

Any gotchas in using blob as keys (unique or otherwise)?

Thanks in advance.

 





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