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.
> 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. So 36 vs 16 is a matter of choice.
I prefer blob guids myself. See below for uid-related insert/select SQL.
--DD
PS: Note that these are random blobs, so the type-bits don't follow the
UUID spec. I don't care personally. YMMV.
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\DDevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> create table t (uid blob primary key);
sqlite> insert into t values (1), ('foo'), (x'abcd');
sqlite> select uid, typeof(uid), length(uid) from t;
1|integer|1
foo|text|3
??|blob|2
sqlite> create table t (uid blob primary key CHECK(typeof(uid)='blob' and
length(uid)=16));
sqlite> select uid, typeof(uid), length(uid) from t;
sqlite> insert into t values (1), ('foo'), (x'abcd');
Error: CHECK constraint failed: t
sqlite> select uid, typeof(uid), length(uid) from t;
sqlite> insert into t values (randomblob(16));
sqlite> insert into t values (randomblob(16));
sqlite> insert into t values (randomblob(16));
sqlite> select * from t;
?2?!???
????o??????T?&
e?rBq.???H??f?
sqlite> select quote(uid) from t;
X'674ED1D53CABCA0D86329A219F0EE8A4'
X'11F4BBE18CF36FC2C8159D9CEB54F126'
X'65F37242712E89A2E894480107F466A6'
sqlite> select hex(uid) from t;
674ED1D53CABCA0D86329A219F0EE8A4
11F4BBE18CF36FC2C8159D9CEB54F126
65F37242712E89A2E894480107F466A6
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
...> );
674ED1D5-3CAB-4CA0-863-9A219F0EE8A4
11F4BBE1-8CF3-46FC-C81-9D9CEB54F126
65F37242-712E-489A-E89-480107F466A6
sqlite>
