2016-04-15 8:45 GMT+02:00 Dominique Devienne <ddevienne at gmail.com>:
> On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof <cldwesterhof at gmail.com>
> wrote:
>
> > I want to work with UUID's. (Version 4.)
>
>
> Honestly, your post is a little vague. But maybe the following will help.
>
?I am not always very good in asking questions. :'-(
?
> > What is the smartest way to put a check on this?
> >
>
> check what? CHECK constraint? SQLite is dynamically typed, and to enforce a
> column's
> type you can use check constraints.
>
> A blob UUID is 16 bytes, not 4.
?Eeeh. :-*
?
> So 36 vs 16 is a matter of choice.
> I prefer blob guids myself. See below for uid-related insert/select SQL.
>
?I myself also, but I am sometimes told I want to optimise to much. And
when using blobs the checks become much easier.
?
> PS: Note that these are random blobs, so the type-bits don't follow the
> UUID spec. I don't care personally. YMMV.
>
?I think I do and it looks like I got it working.
?
> sqlite> create table t (uid blob primary key CHECK(typeof(uid)='blob' and
> length(uid)=16));
>
?I will go for this. With checks it becomes:
?CREATE TABLE t (
UUID BLOB PRIMARY KEY CHECK(TYPEOF(UUID) = 'blob' AND LENGTH(UUID) = 16
AND substr(HEX(UUID), 13, 1) == '4' AND substr(HEX(UUID), 17, 1) IN ('8',
'9', 'A', 'B'))
)
?
> sqlite> select substr(u,1,8)||'-'||substr(u,9,4)||'-4'||substr(u,13,3)||
> ...> '-'||substr(u,17,3)||'-'||substr(u,21,12) from (
> ...> select hex(uid) as u from t
> ...> );
>
?And this is the way to display them.
?Is there a way to convert a hex string to a blob? I did not find it yet.?
Most are rejected with:
insert into t values (randomblob(16));
Accepted are:
8E51A309-BC11-47CD-88C6-3F428D559B89
6C66572C-3FA1-4BEF-90D1-97678C30CB1D
3A713997-4035-4EDD-8E93-F7E3F579EF1D
Thus it looks like it works.
--
Cecil Westerhof
