Re: UUIDs in MonetDB

1 Oct 2015

      I was able to get that to work using -fraw

sql>select sys.uuid();
% .L # table_name
% uuid # name
% uuid # type
% 0 # length
[ 6c0e6904-c3a7-44c4-ba73-4d798cc0357d    ]
sql>

but not with \fraw (no -fraw on the command line)

sql>\fraw select sys.uuid();
unsupported formatter
sql>\f raw select sys.uuid();
unsupported formatter
sql>

fraw looks like a good debugging tool.

I am in the process of changing our schema to use UUIDs for keys in places
that we need unique keys both inside and outside of the database. However,
I am having problems inserting tuples with uuid columns that refer to
existing uuid primary keys. In  the following transcript from mclient you
will see that the insert into the institution table succeeded using
sys.uuid()  to generate the primary key but the insert into the account
table failed using the same uuid as the foreign key. I suspect I am using
the wrong syntax for UUID constant, but I cannot find an example on the
monetdb website.

sql>CREATE TABLE institution(
more>    pk_uuid UUID NOT NULL,
more>    name VARCHAR(128) NOT NULL,
more>    CONSTRAINT institution_pk PRIMARY KEY (pk_uuid)
more>);
operation successful (35.688ms)
sql>INSERT INTO institution (pk_uuid, name) VALUES (sys.uuid(),
'TheInstitution');
1 affected row (30.064ms)
sql>SELECT * FROM institution;
+--------------------------------------------------------------------------------------+----------------+
|
pk_uuid
| name           |
+========================================+================+
| fdf1b0a7-9cd2-4bb6-868a-049a199ef278                             |
TheInstitution |
+--------------------------------------------------------------------------------------+----------------+
1 tuple (2.240ms)
sql>CREATE TABLE account(
more>    pk_uuid UUID NOT NULL,
more>    name VARCHAR(128) NOT NULL,
more>    fk_institution_uuid UUID NOT NULL,
more>    number VARCHAR(64) NOT NULL,
more>    CONSTRAINT account_pk PRIMARY KEY (pk_uuid),
more>    CONSTRAINT account_institution_fk FOREIGN KEY
(fk_institution_uuid) REFERENCES institution (pk_uuid)
more>);
operation successful (23.009ms)
sql>INSERT INTO account (pk_uuid, name, fk_institution_uuid, number) VALUES
more>    (sys.uuid(), 'Master Account',
'fdf1b0a7-9cd2-4bb6-868a-049a199ef278', 'FXXXXXXXXXX');
TypeException:user.s12_1[22]:'calc.uuid' undefined in:
calc.uuid(nil:uuid);
program contains errors
sql>

On Thu, Oct 1, 2015 at 7:16 AM, Sjoerd Mullender  wrote:
...
On 30/09/15 19:26, Niels Nes wrote:
...
On Wed, Sep 30, 2015 at 03:01:31PM +0000, Doug Service wrote:
...
Yes, that works fine. How is the type supported in mapi? Is it a
VarChar(36)?
Yes over the wire it should be varchar(36).
Actually, as you can see in the mclient output if you use the -fraw
option or the \fraw command, the type is specified as "uuid".  But
internally in the library there is no special treatment.
...
Niels
...
Installed version information is at the end.
Doug
sql>select sys.uuid();
+----------------------------------------------------------------------------------------------------------------------+
...
|
...
uuid
 |
+======================================================================
================================================+
...
| 772fd36b-f7d2-48cf-aaac-31261fd8ef46
 |
+----------------------------------------------------------------------------------------------------------------------+
...
1 tuple (0.726ms)
...
sql>select sys.uuid();
+----------------------------------------------------------------------------------------------------------------------+
...
|
...
uuid
 |
+======================================================================
================================================+
...
| 5e2ae37b-8bd8-48ac-b35b-fc96fe760931
 |
+----------------------------------------------------------------------------------------------------------------------+
...
1 tuple (0.774ms)
...
sql>
Versions Installed
ii  libmonetdb-client-dev 11.21.5                           amd64
MonetDB client/server interface library development files ii
libmonetdb-client7 11.21.5                           amd64
MonetDB client/server interface library ii  libmonetdb-stream6
 11.19.15-20150603                 amd64        MonetDB stream
library ii  libmonetdb-stream7 11.21.5
amd64        MonetDB stream library ii  libmonetdb11
 11.19.15-20150603                 amd64        MonetDB core
library ii  libmonetdb12 11.21.5                           amd64
MonetDB core library ii  monetdb-client 11.21.5
amd64        MonetDB database client ii  monetdb-client-tools
 11.21.5                           amd64        MonetDB database
client ii  monetdb5-server 11.21.5
amd64        MonetDB database server version 5 ii
monetdb5-server-hugeint 11.21.5                           amd64
MonetDB - 128-bit integer support for MonetDB5-server ii
monetdb5-sql 11.21.5                           amd64
MonetDB SQL support for monetdb5
On Wed, Sep 30, 2015 at 2:38 PM, Niels Nes 
wrote:
On Wed, Sep 30, 2015 at 02:26:40PM +0000, Doug Service wrote:
...
The documentation at https://www.monetdb.org/Documentation/
Manuals/
SQLreference/UUItype indicates that a UUID type is supported
in
MonetDB
and gives the example
select uuid(); +--------------------------------------+ | uuid
| +======================================+ |
65950c76-a2f6-4543-660a-b849cf5f2453 |
+--------------------------------------+
which appears to output from mclient. When I try the same with
mclient
I receive
auto commit mode: on sql>select uuid(); SELECT: no such
operator 'uuid'
This works fine in the default and stable versions. Maybe your
in the wrong schema, ie does select sys.uuid(); work?
Niels
...
mapi.h only shows support for the following types
#define MAPI_AUTO       0       /* automatic type detection */
#define MAPI_TINY       1 #define MAPI_UTINY      2 #define
MAPI_SHORT      3 #define MAPI_USHORT     4 #define MAPI_INT
5 #define MAPI_UINT       6 #define MAPI_LONG       7 #define
MAPI_ULONG      8 #define MAPI_LONGLONG   9 #define
MAPI_ULONGLONG  10 #define MAPI_CHAR       11 #define
MAPI_VARCHAR    12 #define MAPI_FLOAT      13 #define
MAPI_DOUBLE     14 #define MAPI_DATE       15 #define MAPI_TIME
16 #define MAPI_DATETIME   17 #define MAPI_NUMERIC    18
Is it possible to get MonetDB to generate UUIDs and how is the
type
...
supported in mapi.
Thanks
Doug Service
...
_______________________________________________ users-list
mailing list users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14,
phone ++31 20 592-4098     sip:4098@sip.cwi.nl url:
https://www.cwi.nl/people/niels    e-mail: Niels.Nes@cwi.nl
_______________________________________________ users-list
mailing list users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
...
_______________________________________________ users-list
mailing list users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
--
Sjoerd Mullender
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list