1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
CREATE EXTENSION "uuid-ossp";
SELECT uuid_nil();
SELECT uuid_ns_dns();
SELECT uuid_ns_url();
SELECT uuid_ns_oid();
SELECT uuid_ns_x500();
-- some quick and dirty field extraction functions
-- this is actually timestamp concatenated with clock sequence, per RFC 4122
CREATE FUNCTION uuid_timestamp_bits(uuid) RETURNS varbit AS
$$ SELECT ('x' || substr($1::text, 15, 4) || substr($1::text, 10, 4) ||
substr($1::text, 1, 8) || substr($1::text, 20, 4))::bit(80)
& x'0FFFFFFFFFFFFFFF3FFF' $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION uuid_version_bits(uuid) RETURNS varbit AS
$$ SELECT ('x' || substr($1::text, 15, 2))::bit(8) & '11110000' $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION uuid_reserved_bits(uuid) RETURNS varbit AS
$$ SELECT ('x' || substr($1::text, 20, 2))::bit(8) & '11000000' $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION uuid_multicast_bit(uuid) RETURNS bool AS
$$ SELECT (('x' || substr($1::text, 25, 2))::bit(8) & '00000001') != '00000000' $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION uuid_local_admin_bit(uuid) RETURNS bool AS
$$ SELECT (('x' || substr($1::text, 25, 2))::bit(8) & '00000010') != '00000000' $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION uuid_node(uuid) RETURNS text AS
$$ SELECT substr($1::text, 25) $$
LANGUAGE SQL STRICT IMMUTABLE;
-- Ideally, the multicast bit would never be set in V1 output, but the
-- UUID library may fall back to MC if it can't get the system MAC address.
-- Also, the local-admin bit might be set (if so, we're probably inside a VM).
-- So we can't test either bit here.
SELECT uuid_version_bits(uuid_generate_v1()),
uuid_reserved_bits(uuid_generate_v1());
-- Although RFC 4122 only requires the multicast bit to be set in V1MC style
-- UUIDs, our implementation always sets the local-admin bit as well.
SELECT uuid_version_bits(uuid_generate_v1mc()),
uuid_reserved_bits(uuid_generate_v1mc()),
uuid_multicast_bit(uuid_generate_v1mc()),
uuid_local_admin_bit(uuid_generate_v1mc());
-- timestamp+clock sequence should be monotonic increasing in v1
SELECT uuid_timestamp_bits(uuid_generate_v1()) < uuid_timestamp_bits(uuid_generate_v1());
SELECT uuid_timestamp_bits(uuid_generate_v1mc()) < uuid_timestamp_bits(uuid_generate_v1mc());
-- Ideally, the node value is stable in V1 addresses, but OSSP UUID
-- falls back to V1MC behavior if it can't get the system MAC address.
SELECT CASE WHEN uuid_multicast_bit(uuid_generate_v1()) AND
uuid_local_admin_bit(uuid_generate_v1()) THEN
true -- punt, no test
ELSE
uuid_node(uuid_generate_v1()) = uuid_node(uuid_generate_v1())
END;
-- In any case, V1MC node addresses should be random.
SELECT uuid_node(uuid_generate_v1()) <> uuid_node(uuid_generate_v1mc());
SELECT uuid_node(uuid_generate_v1mc()) <> uuid_node(uuid_generate_v1mc());
SELECT uuid_generate_v3(uuid_ns_dns(), 'www.widgets.com');
SELECT uuid_generate_v5(uuid_ns_dns(), 'www.widgets.com');
SELECT uuid_version_bits(uuid_generate_v4()),
uuid_reserved_bits(uuid_generate_v4());
SELECT uuid_generate_v4() <> uuid_generate_v4();
|
