From 0f660cfd38dc6d175c5013594d9f195cd002617e Mon Sep 17 00:00:00 2001 From: "Jay Freeman (saurik)" Date: Fri, 25 Apr 2014 00:29:44 +0000 Subject: [PATCH 1/1] Initial version (seems to work, tested with 9.2). --- .gitignore | 2 + makefile | 8 +++ timeuuid--1.0.sql | 129 ++++++++++++++++++++++++++++++++++++++++++++++ timeuuid.c | 107 ++++++++++++++++++++++++++++++++++++++ timeuuid.control | 5 ++ 5 files changed, 251 insertions(+) create mode 100644 .gitignore create mode 100644 makefile create mode 100644 timeuuid--1.0.sql create mode 100644 timeuuid.c create mode 100644 timeuuid.control diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..157d706 --- /dev/null +++ b/.gitignore @@ -0,0 +1,2 @@ +timeuuid.o +timeuuid.so diff --git a/makefile b/makefile new file mode 100644 index 0000000..39bafad --- /dev/null +++ b/makefile @@ -0,0 +1,8 @@ +MODULE_big = timeuuid +EXTENSION = timeuuid +DATA = timeuuid--1.0.sql +OBJS = timeuuid.o + +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) diff --git a/timeuuid--1.0.sql b/timeuuid--1.0.sql new file mode 100644 index 0000000..119f771 --- /dev/null +++ b/timeuuid--1.0.sql @@ -0,0 +1,129 @@ +\echo Use "CREATE EXTENSION timeuuid" to load this file. \quit + +create type timeuuid; + +create or replace function timeuuid_in(cstring) returns timeuuid + as 'uuid_in' language internal immutable strict; +create or replace function timeuuid_out(timeuuid) returns cstring + as 'uuid_out' language internal immutable strict; + +create or replace function timeuuid_recv(internal) returns timeuuid + as 'uuid_recv' language internal immutable strict; +create or replace function timeuuid_send(timeuuid) returns bytea + as 'uuid_send' language internal immutable strict; + +create type timeuuid ( + input = timeuuid_in, + output = timeuuid_out, + receive = timeuuid_recv, + send = timeuuid_send, + like = uuid +); + +create or replace function timeuuid_eq(timeuuid, timeuuid) returns boolean + as 'uuid_eq' language internal immutable strict; +create or replace function timeuuid_neq(timeuuid, timeuuid) returns boolean + as 'uuid_ne' language internal immutable strict; + +create or replace function timeuuid_hash(timeuuid) returns int4 + as 'uuid_hash' language internal immutable strict; + +create function timeuuid_lt(timeuuid, timeuuid) returns boolean + as 'MODULE_PATHNAME' language c strict immutable; +create function timeuuid_gt(timeuuid, timeuuid) returns boolean + as 'MODULE_PATHNAME' language c strict immutable; +create function timeuuid_le(timeuuid, timeuuid) returns boolean + as 'MODULE_PATHNAME' language c strict immutable; +create function timeuuid_ge(timeuuid, timeuuid) returns boolean + as 'MODULE_PATHNAME' language c strict immutable; + +create function timeuuid_cmp(timeuuid, timeuuid) returns int4 + as 'MODULE_PATHNAME' language c strict immutable; + +create operator = ( + leftarg = timeuuid, + rightarg = timeuuid, + commutator = =, + negator = <>, + procedure = timeuuid_eq, + restrict = eqsel, + join = eqjoinsel, + hashes, + merges +); + +create operator <> ( + leftarg = timeuuid, + rightarg = timeuuid, + commutator = <>, + negator = =, + procedure = timeuuid_neq, + restrict = neqsel, + join = neqjoinsel +); + +create operator < ( + leftarg = timeuuid, + rightarg = timeuuid, + commutator = >, + negator = >=, + procedure = timeuuid_lt, + restrict = scalarltsel, + join = scalarltjoinsel +); + +create operator > ( + leftarg = timeuuid, + rightarg = timeuuid, + commutator = <, + negator = <=, + procedure = timeuuid_gt, + restrict = scalargtsel, + join = scalargtjoinsel +); + +create operator <= ( + leftarg = timeuuid, + rightarg = timeuuid, + commutator = >=, + negator = >, + procedure = timeuuid_le, + restrict = scalarltsel, + join = scalarltjoinsel +); + +create operator >= ( + leftarg = timeuuid, + rightarg = timeuuid, + commutator = <=, + negator = <, + procedure = timeuuid_ge, + restrict = scalargtsel, + join = scalargtjoinsel +); + +create operator class timeuuid_ops +default for type timeuuid using btree as + operator 1 <, + operator 2 <=, + operator 3 =, + operator 4 >=, + operator 5 >, + function 1 timeuuid_cmp(timeuuid, timeuuid); + +create cast (timeuuid as uuid) + without function as assignment; +create cast (uuid as timeuuid) + without function as assignment; + +create function timeuuid_to_timestamptz(uuid) returns timestamptz + as 'MODULE_PATHNAME' language c strict immutable; +create function timeuuid_to_timestamptz(timeuuid) returns timestamptz + as 'MODULE_PATHNAME' language c strict immutable; +create function timestamptz_to_timeuuid(timestamptz) returns timeuuid + as 'MODULE_PATHNAME' language c strict immutable; + +create cast (timeuuid as timestamptz) + with function timeuuid_to_timestamptz(timeuuid); +create cast (timestamptz as timeuuid) + with function timestamptz_to_timeuuid(timestamptz); diff --git a/timeuuid.c b/timeuuid.c new file mode 100644 index 0000000..8c78c6b --- /dev/null +++ b/timeuuid.c @@ -0,0 +1,107 @@ +#include "postgres.h" +#include "fmgr.h" + +#include "utils/timestamp.h" +#include "utils/uuid.h" + +PG_MODULE_MAGIC; + +#define UUID_EPOCH_JDATE 2299161 /* == date2j(1582, 10, 15) */ + +struct pg_uuid_t { + unsigned char data[UUID_LEN]; +}; + +__attribute__((__packed__)) +struct timeuuid_t { + uint32_t time_low; + uint16_t time_mid; + uint16_t time_hi_and_version; + uint8_t data[8]; +}; + +typedef struct timeuuid_t timeuuid_t; + +Datum timeuuid_lt(PG_FUNCTION_ARGS); +Datum timeuuid_gt(PG_FUNCTION_ARGS); +Datum timeuuid_le(PG_FUNCTION_ARGS); +Datum timeuuid_ge(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(timeuuid_lt); +PG_FUNCTION_INFO_V1(timeuuid_gt); +PG_FUNCTION_INFO_V1(timeuuid_le); +PG_FUNCTION_INFO_V1(timeuuid_ge); + +Datum timeuuid_cmp(PG_FUNCTION_ARGS); +PG_FUNCTION_INFO_V1(timeuuid_cmp); + +static int timeuuid_internal_cmp(const pg_uuid_t *arg1, const pg_uuid_t *arg2) { + int result; + if ((result = memcmp(&arg1->data[6], &arg2->data[6], 2)) != 0) + return result; + if ((result = memcmp(&arg1->data[4], &arg2->data[4], 2)) != 0) + return result; + if ((result = memcmp(&arg1->data[0], &arg2->data[0], 4)) != 0) + return result; + return memcmp(&arg1->data[8], &arg2->data[8], 8); +} + +Datum timeuuid_lt(PG_FUNCTION_ARGS) { + pg_uuid_t *arg1 = PG_GETARG_UUID_P(0); + pg_uuid_t *arg2 = PG_GETARG_UUID_P(1); + PG_RETURN_BOOL(timeuuid_internal_cmp(arg1, arg2) < 0); +} + +Datum timeuuid_gt(PG_FUNCTION_ARGS) { + pg_uuid_t *arg1 = PG_GETARG_UUID_P(0); + pg_uuid_t *arg2 = PG_GETARG_UUID_P(1); + PG_RETURN_BOOL(timeuuid_internal_cmp(arg1, arg2) > 0); +} + +Datum timeuuid_le(PG_FUNCTION_ARGS) { + pg_uuid_t *arg1 = PG_GETARG_UUID_P(0); + pg_uuid_t *arg2 = PG_GETARG_UUID_P(1); + PG_RETURN_BOOL(timeuuid_internal_cmp(arg1, arg2) <= 0); +} + +Datum timeuuid_ge(PG_FUNCTION_ARGS) { + pg_uuid_t *arg1 = PG_GETARG_UUID_P(0); + pg_uuid_t *arg2 = PG_GETARG_UUID_P(1); + PG_RETURN_BOOL(timeuuid_internal_cmp(arg1, arg2) >= 0); +} + +Datum timeuuid_cmp(PG_FUNCTION_ARGS) { + pg_uuid_t *arg1 = PG_GETARG_UUID_P(0); + pg_uuid_t *arg2 = PG_GETARG_UUID_P(1); + PG_RETURN_INT32(timeuuid_internal_cmp(arg1, arg2)); +} + +#ifdef HAVE_INT64_TIMESTAMP +Datum timeuuid_to_timestamptz(PG_FUNCTION_ARGS); +Datum timestamptz_to_timeuuid(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(timeuuid_to_timestamptz); +PG_FUNCTION_INFO_V1(timestamptz_to_timeuuid); + +static const int64_t uuid_diff = ((int64_t) POSTGRES_EPOCH_JDATE - (int64_t) UUID_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC; + +Datum timeuuid_to_timestamptz(PG_FUNCTION_ARGS) { + timeuuid_t *uuid = (timeuuid_t *) PG_GETARG_UUID_P(0); + TimestampTz ts = (((uint64_t) ntohs(uuid->time_hi_and_version) & 0xfff) << 48) + ((uint64_t) ntohs(uuid->time_mid) << 32) + (uint64_t) ntohl(uuid->time_low); + ts = (int64_t) (ts / 10) - uuid_diff; + PG_RETURN_TIMESTAMPTZ(ts); +} + +Datum timestamptz_to_timeuuid(PG_FUNCTION_ARGS) { + TimestampTz ts = PG_GETARG_TIMESTAMPTZ(0); + ts = (ts + uuid_diff) * 10; + timeuuid_t *uuid = (timeuuid_t *) palloc(sizeof(*uuid)); + uuid->time_hi_and_version = htons((ts >> 48) | (0x1 << 12)); + uuid->time_mid = htons(ts >> 32); + uuid->time_low = htonl(ts); + memset(uuid->data, 0, 8); + PG_RETURN_UUID_P(uuid); +} +#else +#error +#endif diff --git a/timeuuid.control b/timeuuid.control new file mode 100644 index 0000000..c12bf25 --- /dev/null +++ b/timeuuid.control @@ -0,0 +1,5 @@ +# seg extension +comment = 'data type for time-based v1 uuids: the perfect balance of bigserial and timestamptz' +default_version = '1.0' +module_pathname = '$libdir/timeuuid' +relocatable = true -- 2.45.2