Thread: Analogue to SQL Server UniqueIdentifier?
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?
Thx.
Jerry.
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 thishave 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 functionto 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) ? Thisseems 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 aboutthis 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 implementedfunction 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) ? Thisseems 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 aboutthis 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 implementedfunction 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.