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 <sjoerd@acm.org> 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 <Niels.Nes@cwi.nl>
>> 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