Analogue to SQL Server UniqueIdentifier?

In response to "jerry.evans@chordia" <jerry.evans@chordia.co.uk>:

Hi

My porting experiment has encountered the SQL Server UniqueIdentifier problem. I can see one or two suggestions about this have been made over the years but I'd like to try and stay close to the original. So:

I'm wondering if I can use a combination of a domain 'hack' for syntatic compatibillity and an externally implemented function to handle generation.

More specifically, given a table defined thus:

CREATE TABLE jazz(
UUID UniqueIdentifier DEFAULT newIdentifier(),
rootname VARCHAR(255),
data_source VARCHAR(1024),
date_created DATETIME DEFAULT GETDATE())

1. Can I handle the UniqueIdentifier datatype via a domain that aliases UniqueIdentifier to char(X) (for example) ? This seems to work fine for the DATETIME datatype.
2. Implement newIdentifier() in some extension DLL that simply calls CoCreateGUID() ?

or does uuid-ossp do this for me?

I'm no expert on this topic, but since nobody else has responded ...

I'm unsure why you would do anything other than install uuid-ossp.
Anything else is going to be a hack, and uuid-ossp was created specifically
to address this requirement.

Unless, of course, I've misunderstood your question.

--
Bill Moran
http://www.potentialtech.com

On Feb 18, 2008 4:52 PM, Bill Moran <wmoran@potentialtech.com> wrote:

In response to "jerry.evans@chordia" <jerry.evans@chordia.co.uk>:

Hi

My porting experiment has encountered the SQL Server UniqueIdentifier problem. I can see one or two suggestions about this have been made over the years but I'd like to try and stay close to the original. So:

I'm wondering if I can use a combination of a domain 'hack' for syntatic compatibillity and an externally implemented function to handle generation.

More specifically, given a table defined thus:

CREATE TABLE jazz(
UUID UniqueIdentifier DEFAULT newIdentifier(),
rootname VARCHAR(255),
data_source VARCHAR(1024),
date_created DATETIME DEFAULT GETDATE())

1. Can I handle the UniqueIdentifier datatype via a domain that aliases UniqueIdentifier to char(X) (for example) ? This seems to work fine for the DATETIME datatype.
2. Implement newIdentifier() in some extension DLL that simply calls CoCreateGUID() ?

or does uuid-ossp do this for me?

I'm no expert on this topic, but since nobody else has responded ...

I'm unsure why you would do anything other than install uuid-ossp.
Anything else is going to be a hack, and uuid-ossp was created specifically
to address this requirement.

Lack of support for Windows, which it sounds like the OP might be running?

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Oracle-compatible database company

Thanks Bill.

I discovered that 8.3 supports a UUID datatype. Thus a CREATE DOMAIN
uniqueidentifier AS uuid works fine for the aliasing. There are no SQL
Server style functions for UUID creation but I can handle this in the client
code. Works a treat.

Jerry

On Mon, Feb 18, 2008 at 05:01:22PM +0000, Dave Page wrote:

On Feb 18, 2008 4:52 PM, Bill Moran <wmoran@potentialtech.com> wrote:

In response to "jerry.evans@chordia" <jerry.evans@chordia.co.uk>:

Hi

My porting experiment has encountered the SQL Server UniqueIdentifier problem. I can see one or two suggestions about this have been made over the years but I'd like to try and stay close to the original. So:

I'm wondering if I can use a combination of a domain 'hack' for syntatic compatibillity and an externally implemented function to handle generation.

More specifically, given a table defined thus:

CREATE TABLE jazz(
UUID UniqueIdentifier DEFAULT newIdentifier(),
rootname VARCHAR(255),
data_source VARCHAR(1024),
date_created DATETIME DEFAULT GETDATE())

1. Can I handle the UniqueIdentifier datatype via a domain that aliases UniqueIdentifier to char(X) (for example) ? This seems to work fine for the DATETIME datatype.
2. Implement newIdentifier() in some extension DLL that simply calls CoCreateGUID() ?

or does uuid-ossp do this for me?

I'm no expert on this topic, but since nobody else has responded ...

I'm unsure why you would do anything other than install uuid-ossp.
Anything else is going to be a hack, and uuid-ossp was created specifically
to address this requirement.

Lack of support for Windows, which it sounds like the OP might be running?

That's something that's been on my agenda for a while. There are certainly
UUID generation functions available on Windows - at least for some of the
cases supported by uuid-ossp. If I were to write the same functions for
that one, where would people prefer that to go - in the uuid-ossp module
even though that's actually not correct (since it wouldn't be using ossp)
or a separate module uuid-win32?

//Magnus

Magnus Hagander <magnus@hagander.net> writes:

Lack of support for Windows, which it sounds like the OP might be running?

That's something that's been on my agenda for a while. There are certainly
UUID generation functions available on Windows - at least for some of the
cases supported by uuid-ossp. If I were to write the same functions for
that one, where would people prefer that to go - in the uuid-ossp module
even though that's actually not correct (since it wouldn't be using ossp)
or a separate module uuid-win32?

The latter is *completely* unacceptable. The entire point here is to
not expose any differences at the SQL level.

Why can't ossp be used --- is it impossible to port to Windows?

regards, tom lane

On Tue, 2008-02-19 at 11:07 -0500, Tom Lane wrote:

Magnus Hagander <magnus@hagander.net> writes:

Lack of support for Windows, which it sounds like the OP might be running?

That's something that's been on my agenda for a while. There are certainly
UUID generation functions available on Windows - at least for some of the
cases supported by uuid-ossp. If I were to write the same functions for
that one, where would people prefer that to go - in the uuid-ossp module
even though that's actually not correct (since it wouldn't be using ossp)
or a separate module uuid-win32?

The latter is *completely* unacceptable. The entire point here is to
not expose any differences at the SQL level.

Why can't ossp be used --- is it impossible to port to Windows?

I haven't looked into the details - it's possible that it could be
portable to Windows. But that would a Yet Another Dependency to be bale
to build and run pg... So I'd like to avoid it if possible.

//Magnus

Magnus Hagander wrote:

On Tue, 2008-02-19 at 11:07 -0500, Tom Lane wrote:

Why can't ossp be used --- is it impossible to port to Windows?

I haven't looked into the details - it's possible that it could be
portable to Windows. But that would a Yet Another Dependency to be bale
to build and run pg... So I'd like to avoid it if possible.

I think it's messy enough to port that it would make sense to create a
separate Windows library with the same interface.

The problem I see with porting it is that the author crammed too many
things in the same package, and we don't have any interest in porting
most of the stuff only to get something that we can get more easily by
hooking into Windows native calls.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.