[sqlite] Working with UUID
2016-04-15 19:47 GMT+02:00 Dominique Devienne :
> On Fri, Apr 15, 2016 at 3:56 PM, Cecil Westerhof
> wrote:
>
> > 2016-04-15 8:45 GMT+02:00 Dominique Devienne :
> > > On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof <
> cldwesterhof at gmail.com>>
> > wrote:
> >
> > ?Is there a way to convert a hex string to a blob? I did not find it
> yet.?
> >
>
> I didn't find one either (see below), beside formatting a SQL statement
> with a blob literal,
> kinda like sprintf(..., "x'%s'", hex_string), which is hardly an option.
> I'm surprised there's no built-in function or SQL way to do it.
> I suspect there is, and I'm missing something. Hopefully someone will chime
> in.
>
> Perhaps a CTE could do it.
?I have to delve in that also. You are taking my sleep away. ;-)
?
> I'd reach for C/C++ and custom functions for
> such things.
>
?I am probably going to work with Java, but when exploring I am using Bash
and SQLite Browser.
In a program I have been testing with UUID, I had:
ps.setBytes(1, getRandomUUIDBlob());
and the function itself:
private static byte[] getRandomUUIDBlob() {
ByteBuffer bb;
byte[] bytes = new byte[16];
UUIDuuid = UUID.randomUUID();
bb = ByteBuffer.wrap(bytes);
bb.putLong(uuid.getMostSignificantBits());
bb.putLong(uuid.getLeastSignificantBits());
return bytes;
}
This worked. I have to clean it up to show some strange things I noticed.
(In my eyes.)
?
> (I'd use a UDF for the printing part as well, I suspect it's faster that
> way, but didn't measure it).
>
?And something else to keep me awake. ;-)?
> 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> select typeof(x'ab');
> blob
> sqlite> select typeof('ab');
> text
> sqlite> select typeof(cast('ab' as blob));
> blob
> sqlite> select hex(x'ab');
> AB
> sqlite> select hex(cast('ab' as blob));
> 6162
> sqlite>
>
?Here I get a blob:
sqlite> WITH UUIDTable AS (
...> SELECT '3DBA81DE-7AA7-412E-954F-5B2DA8D4AB6C' AS UUIDStr
...> )
...> SELECT TYPEOF(CAST(SUBSTR(UUIDStr, 1, 8) ||
...>SUBSTR(UUIDStr, 10, 4) ||
...>SUBSTR(UUIDStr, 15, 4) ||
...>SUBSTR(UUIDStr, 20, 4) ||
...>SUBSTR(UUIDStr, 25, 12) AS blob))
...> FROM UUIDTable
...> ;
blob
But when I do not use TYPEOF I get a string instead of a blob:
sqlite> WITH UUIDTable AS (
...> SELECT '3DBA81DE-7AA7-412E-954F-5B2DA8D4AB6C' AS UUIDStr
...> )
...> SELECT CAST(SUBSTR(UUIDStr, 1, 8) ||
...> SUBSTR(UUIDStr, 10, 4) ||
...> SUBSTR(UUIDStr, 15, 4) ||
...> SUBSTR(UUIDStr, 20, 4) ||
...> SUBSTR(UUIDStr, 25, 12) AS blob)
...> FROM UUIDTable
...> ;
3DBA81DE7AA7412E954F5B2DA8D4AB6C
What am I doing wrong?
--
Cecil Westerhof
[sqlite] Working with UUID
On Fri, Apr 15, 2016 at 3:56 PM, Cecil Westerhof
wrote:
> 2016-04-15 8:45 GMT+02:00 Dominique Devienne :
> > On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof > gmail.com>>
> wrote:
>
> ?Is there a way to convert a hex string to a blob? I did not find it yet.?
>
I didn't find one either (see below), beside formatting a SQL statement
with a blob literal,
kinda like sprintf(..., "x'%s'", hex_string), which is hardly an option.
I'm surprised there's no built-in function or SQL way to do it.
I suspect there is, and I'm missing something. Hopefully someone will chime
in.
Perhaps a CTE could do it. I'd reach for C/C++ and custom functions for
such things.
(I'd use a UDF for the printing part as well, I suspect it's faster that
way, but didn't measure it).
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> select typeof(x'ab');
blob
sqlite> select typeof('ab');
text
sqlite> select typeof(cast('ab' as blob));
blob
sqlite> select hex(x'ab');
AB
sqlite> select hex(cast('ab' as blob));
6162
sqlite>
[sqlite] Working with UUID
2016-04-15 8:45 GMT+02:00 Dominique Devienne :
> On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof
> 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
[sqlite] Working with UUID
On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof
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>
[sqlite] Working with UUID
I want to work with UUID's. (Version 4.) What is the smartest way to put a check on this? Also: when using text to save them, you need 36 bytes instead of four bytes. When using a lot, it is better to use blob. I have to post the peculiarities I found with that another time. (For example when writing a lot and crashing the database is changed instead off rolled back.) I am leaning to using blob's (or at least for join tables). Are there reasons to go for one or the another, or is it just individual taste and efficiency when needed? -- Cecil Westerhof
