Okay I just needed the newline. Would you also please look at the issue below that I mentioned.
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 3:05 PM, Stefan Manegold Stefan.Manegold@cwi.nl wrote:
Hi Doug,
it's
sql>\fraw sql>select sys.uuid();
i.e., \f is a separate command rather than a prefix.
See also the welcome message of mclient, "\q" in mclient, `man mclient` and/or https://www.monetdb.org/Documentation/mclient-man-page
Best, Stefan
----- On Oct 1, 2015, at 5:00 PM, Doug Service n848dg@gmail.com wrote:
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
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list