Thread: MS SQL Server compatibility functions

MS SQL Server compatibility functions

From
Fredrik Olsson
Date:
Hi.

In the course of porting a database from Microsoft SQL Server to 
PostgreSQL I have rewritten a few of the date and string functions in 
pl/pgSQL. Started with just datepart, datediff and soundex, but once 
started I continued and rewrote all date/time and string functions 
supported by MS SQL 2005. Leaving only compatibility with 
unicode-handling and binary objects (that MS SQL Server for some reason 
overloads string functions to work with).

I guess I am not the only one moving from MS SQL Server, so is there 
interest for others to use my work, as a contrib perhaps. And how should 
I continue from here in that case?

regards

-- 
//Fredrik Olsson Treyst AB +46-19-362182 fredrik.olsson@treyst.se



Re: MS SQL Server compatibility functions

From
"Bill Bartlett"
Date:
I'd be _very_ interested.  I'll also volunteer to help out on this if
you need assistance -- we have somewhat of a mixed environment here, so
I already have a few (simple) functions that allow some compatibility
between MS SQL Server and PostgreSQL (supporting "nextval" on SQL
Server, etc.), but it sounds like your work has gone far beyond my work.

- Bill

> 
> Hi.
> 
> In the course of porting a database from Microsoft SQL Server to 
> PostgreSQL I have rewritten a few of the date and string functions in 
> pl/pgSQL. Started with just datepart, datediff and soundex, but once 
> started I continued and rewrote all date/time and string functions 
> supported by MS SQL 2005. Leaving only compatibility with 
> unicode-handling and binary objects (that MS SQL Server for 
> some reason 
> overloads string functions to work with).
> 
> I guess I am not the only one moving from MS SQL Server, so is there 
> interest for others to use my work, as a contrib perhaps. And 
> how should 
> I continue from here in that case?
> 
> regards
> 
> -- 
> //Fredrik Olsson
>   Treyst AB
>   +46-19-362182
>   fredrik.olsson@treyst.se
> 



Re: MS SQL Server compatibility functions

From
"Pollard, Mike"
Date:
If this gets added as a contrib, here's a version of uniqueidentifier
and newid() I wrote that maintains the same format as the SQL Server
version:

CREATE SCHEMA sqlserver AUTHORIZATION postgres;
GRANT ALL ON SCHEMA sqlserver TO public;

CREATE SEQUENCE sqlserver.uniqueidentifier0 INCREMENT 1 MINVALUE 0 MAXVALUE 9223372036854775807 START 0 CACHE 1;
GRANT ALL ON TABLE sqlserver.uniqueidentifier0 TO public;

CREATE SEQUENCE sqlserver.uniqueidentifier1 INCREMENT 1 MINVALUE 0 MAXVALUE 9223372036854775807 START 0 CACHE 1;
GRANT ALL ON TABLE sqlserver.uniqueidentifier1 TO public;

--
-- use newid(n) to increment the sequences n times.  Useful
-- for testing and timing the newid() function.
--
CREATE OR REPLACE FUNCTION sqlserver.newid(l integer)
RETURNS CHAR(36) AS $$
DECLARE n CHAR(36); maxl INTEGER;
BEGIN maxl := l; WHILE (maxl > 0) LOOP   n := sqlserver.newid();   maxl := maxl - 1; END LOOP;
 RETURN(n);
END;$$ LANGUAGE plpgsql;

--
-- use newid() to acquire the next uniqueidentifier value.
-- This uses two sequences.  Since a sequence returns an
-- 8-byte number, we just convert those into two 16 character
-- hex strings.  Normally we just need to increment the second
-- sequence, but when that fills up, we increment the first
-- one and then reset the second one to 0.  To prevent concerns
-- over a race condition, we then get the nextval of the second
-- sequence.
--
-- Note that this algorithm assumes that int8 works properly.
-- If you are porting this to a platform without a working int8,
-- then you will need to use 4 4-byte sequences instead.
--
CREATE OR REPLACE FUNCTION sqlserver.newid()
RETURNS CHAR(36) AS $$
DECLARE numbers0 CHAR(16); numbers1 CHAR(16); formatted_id CHAR(36); sq0 INT8; sq1 INT8;
BEGIN   -- get the current sequence values SELECT INTO sq0 last_value FROM sqlserver.uniqueidentifier0; SELECT INTO sq1
last_valueFROM sqlserver.uniqueidentifier1;    -- if sq1 is wrapped, then increment sq0 and restart sq1 at 0 IF (sq1 =
9223372036854775807)THEN   sq0 := NEXTVAL('sqlserver.uniqueidentifier0');   sq1 :=
SETVAL('sqlserver.uniqueidentifier1',0);     -- get nextval; ensures no race condition   sq1 :=
NEXTVAL('sqlserver.uniqueidentifier1');ELSE   sq1 := NEXTVAL('sqlserver.uniqueidentifier1'); END IF; 
 numbers0 := UPPER(LPAD(TO_HEX(sq0), 16, '0')); numbers1 := UPPER(LPAD(TO_HEX(sq1), 16, '0'));formatted_id :=
SUBSTRING(numbers0,1, 8) || '-' || 
SUBSTRING(numbers0, 9, 4) || '-' || SUBSTRING(numbers0, 13, 4) ||                 '-' || SUBSTRING(numbers1, 1, 4) ||
'-'|| 
SUBSTRING(numbers1, 5, 12);
 return(formatted_id);
END;$$ LANGUAGE plpgsql;

CREATE DOMAIN sqlserver.uniqueidentifier AS char(36) DEFAULT sqlserver.newid();

Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc.
--------------------------------Better to remain silent and be thought a fool than to speak out and
remove all doubt.        Abraham Lincoln

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Bill Bartlett
Sent: Wednesday, November 23, 2005 10:01 AM
To: 'Fredrik Olsson'; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] MS SQL Server compatibility functions

I'd be _very_ interested.  I'll also volunteer to help out on this if
you need assistance -- we have somewhat of a mixed environment here, so
I already have a few (simple) functions that allow some compatibility
between MS SQL Server and PostgreSQL (supporting "nextval" on SQL
Server, etc.), but it sounds like your work has gone far beyond my work.

- Bill

>
> Hi.
>
> In the course of porting a database from Microsoft SQL Server to
> PostgreSQL I have rewritten a few of the date and string functions in
> pl/pgSQL. Started with just datepart, datediff and soundex, but once
> started I continued and rewrote all date/time and string functions
> supported by MS SQL 2005. Leaving only compatibility with
> unicode-handling and binary objects (that MS SQL Server for
> some reason
> overloads string functions to work with).
>
> I guess I am not the only one moving from MS SQL Server, so is there
> interest for others to use my work, as a contrib perhaps. And
> how should
> I continue from here in that case?
>
> regards
>
> --
> //Fredrik Olsson
>   Treyst AB
>   +46-19-362182
>   fredrik.olsson@treyst.se
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org


Re: MS SQL Server compatibility functions

From
Devrim GUNDUZ
Date:
Hi,

On Wed, 23 Nov 2005, Fredrik Olsson wrote:

<snip>
> I guess I am not the only one moving from MS SQL Server, so is there interest 
> for others to use my work, as a contrib perhaps. And how should I continue 
> from here in that case?

I'd start a new project at pgfoundry and then would begin talking about a 
contrib module.

Regards,
--
Devrim GUNDUZ
Kivi Bilişim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr                      http://www.gunduz.org

Re: MS SQL Server compatibility functions

From
Fredrik Olsson
Date:
Devrim GUNDUZ wrote:
>
> Hi,
>
> On Wed, 23 Nov 2005, Fredrik Olsson wrote:
>
> <snip>
>> I guess I am not the only one moving from MS SQL Server, so is there 
>> interest for others to use my work, as a contrib perhaps. And how 
>> should I continue from here in that case?
>
> I'd start a new project at pgfoundry and then would begin talking 
> about a contrib module.
>
Good idea. I have registered mssqlsupport as project name there, and 
will add all there is as soon as/if the project gets accepted.

Regards

-- 
//Fredrik Olsson Treyst AB +46-19-362182 fredrik.olsson@treyst.se



Re: MS SQL Server compatibility functions

From
Christopher Kings-Lynne
Date:
I just started a MySQL compatibility functions project on pgfoundry.org.  I suggest starting an MSSQL one as well.  I'd
beinterested if you 
 
could mail me your code for your functions so far because many of the 
MySQL functions are copied from MSSQL...

Chris

Fredrik Olsson wrote:
> Hi.
> 
> In the course of porting a database from Microsoft SQL Server to 
> PostgreSQL I have rewritten a few of the date and string functions in 
> pl/pgSQL. Started with just datepart, datediff and soundex, but once 
> started I continued and rewrote all date/time and string functions 
> supported by MS SQL 2005. Leaving only compatibility with 
> unicode-handling and binary objects (that MS SQL Server for some reason 
> overloads string functions to work with).
> 
> I guess I am not the only one moving from MS SQL Server, so is there 
> interest for others to use my work, as a contrib perhaps. And how should 
> I continue from here in that case?
> 
> regards
> 



Re: MS SQL Server compatibility functions

From
"Pavel Stehule"
Date:
Hello

DB2, MySQL and MsSQL has shared group of function (date, time, strings). You 
can do it a bit complex -  use variable which direct behavior, but there 
isn't bigger differences, I hope. Please (for start), use mycode, orafunc 
from pgfoundry.

Regards
Pavel Stehule





>From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
>To: Fredrik Olsson <fredrik.olsson@treyst.se>
>CC: pgsql-hackers@postgresql.org
>Subject: Re: [HACKERS] MS SQL Server compatibility functions
>Date: Thu, 24 Nov 2005 09:24:06 +0800
>
>I just started a MySQL compatibility functions project on pgfoundry.org.  I 
>suggest starting an MSSQL one as well.  I'd be interested if you could mail 
>me your code for your functions so far because many of the MySQL functions 
>are copied from MSSQL...
>
>Chris
>
>Fredrik Olsson wrote:
>>Hi.
>>
>>In the course of porting a database from Microsoft SQL Server to 
>>PostgreSQL I have rewritten a few of the date and string functions in 
>>pl/pgSQL. Started with just datepart, datediff and soundex, but once 
>>started I continued and rewrote all date/time and string functions 
>>supported by MS SQL 2005. Leaving only compatibility with unicode-handling 
>>and binary objects (that MS SQL Server for some reason overloads string 
>>functions to work with).
>>
>>I guess I am not the only one moving from MS SQL Server, so is there 
>>interest for others to use my work, as a contrib perhaps. And how should I 
>>continue from here in that case?
>>
>>regards
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster

_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/



Re: MS SQL Server compatibility functions

From
Fredrik Olsson
Date:
Fredrik Olsson wrote:
> Devrim GUNDUZ wrote:
>>
>> Hi,
>>
>> On Wed, 23 Nov 2005, Fredrik Olsson wrote:
>>
>> <snip>
>>> I guess I am not the only one moving from MS SQL Server, so is there 
>>> interest for others to use my work, as a contrib perhaps. And how 
>>> should I continue from here in that case?
>>
>> I'd start a new project at pgfoundry and then would begin talking 
>> about a contrib module.
>>
> Good idea. I have registered mssqlsupport as project name there, and 
> will add all there is as soon as/if the project gets accepted.
>
Said and done, project is up at http://pgfoundry.org/projects/mssqlsupport/

regards

-- 
//Fredrik Olsson Treyst AB +46-19-362182 fredrik.olsson@treyst.se



Re: MS SQL Server compatibility functions

From
Mario Weilguni
Date:
Am Mittwoch, 23. November 2005 16:32 schrieb Pollard, Mike:
> If this gets added as a contrib, here's a version of uniqueidentifier
> and newid() I wrote that maintains the same format as the SQL Server
> version:
>
> CREATE SCHEMA sqlserver
>   AUTHORIZATION postgres;
> GRANT ALL ON SCHEMA sqlserver TO public;
>
> CREATE SEQUENCE sqlserver.uniqueidentifier0
>   INCREMENT 1
>   MINVALUE 0
>   MAXVALUE 9223372036854775807
>   START 0
>   CACHE 1;
> GRANT ALL ON TABLE sqlserver.uniqueidentifier0 TO public;
>
> CREATE SEQUENCE sqlserver.uniqueidentifier1
>   INCREMENT 1
>   MINVALUE 0
>   MAXVALUE 9223372036854775807
>   START 0
>   CACHE 1;
> GRANT ALL ON TABLE sqlserver.uniqueidentifier1 TO public;

Why do you use "GRANT ALL" and not "GRANT SELECT, UPDATE"? All means everybody 
can do bad things with those sequences.

Regards,Mario Weilguni


Re: MS SQL Server compatibility functions

From
Christopher Kings-Lynne
Date:
> Why do you use "GRANT ALL" and not "GRANT SELECT, UPDATE"? All means everybody 
> can do bad things with those sequences.

GRANT ALL on a sequence IS GRANT SELECT & UPDATE.

Chris


Re: MS SQL Server compatibility functions

From
"Pollard, Mike"
Date:
If you're referring to my procedure for newid(), then it was just
because of pure laziness; it was an internal proof of concept project,
and I was still concentrating on getting it working.

Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc.
--------------------------------Better to remain silent and be thought a fool than to speak out and
remove all doubt.        Abraham Lincoln

-----Original Message-----
From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au]
Sent: Thursday, November 24, 2005 5:57 AM
To: Mario Weilguni
Cc: pgsql-hackers@postgresql.org; Pollard, Mike; Bill Bartlett; Fredrik
Olsson
Subject: Re: [HACKERS] MS SQL Server compatibility functions

> Why do you use "GRANT ALL" and not "GRANT SELECT, UPDATE"? All means
everybody
> can do bad things with those sequences.

GRANT ALL on a sequence IS GRANT SELECT & UPDATE.

Chris