Thread: Mapping Oracle types to PostgreSQL types

Mapping Oracle types to PostgreSQL types

From
Jean-Michel POURE
Date:
Dear friends,

I would like to port Compiere CRM from Oracle to PostgreSQL (and release it
for free).

At first I would like to convert the data schema. This is not difficult as
Compiere is written using portable types like NUMBER (i,d) which can be
replaced by NUMERIC (i,d), etc... A series of Search/Replace is sufficiant.
There are other solutions in Contrib to connect to Oracle and export the data
(Bruce). Don't blame me to search in another (silly) direction...

The point here is that I would like to use the CREATE TYPE or CREATE DOMAIN
syntax to map Oracle types to PostgreSQL types. Therefore I can say "Guys,
Oracle is now mostly compatible with PostreSQL".

In PostgreSQL, I used CREATE TYPE syntax to map
Oracle nvarchar2 -> PostgreSQL varchar (see code #1).

The code seems to be the equivalent of "CREATE DOMAIN nvarchar2 as varchar;"

Now I can create tables with nvarchar2 but not nvarchar2(lenght).

Is there a way to map Oracle nvarchar2(lenght) to PostgreSQL varchar(lenght)
in PostgreSQL 7.3? Are there plans to allow such mapping in the future using
the CREATE DOMAIN syntax?

Best regards,
Jean-Michel Pouré

**********************************************************************
Code #1
--DROP TYPE nvarchar2 CASCADE;

CREATE OR REPLACE FUNCTION oracle_nvarchar2in(cstring, oid, int4)
RETURNS nvarchar2 AS
'varcharin'
LANGUAGE 'internal' IMMUTABLE STRICT;
COMMENT ON FUNCTION oracle_nvarchar2in(cstring, oid, int4) IS '(internal)';

CREATE OR REPLACE FUNCTION oracle_nvarchar2out(nvarchar2)
RETURNS cstring AS
'varcharout'
LANGUAGE 'internal' IMMUTABLE STRICT;

CREATE TYPE nvarchar2
(INPUT=oracle_nvarchar2in, OUTPUT=oracle_nvarchar2out, DEFAULT='',
INTERNALLENGTH=-1, ALIGNMENT=int4, STORAGE=EXTENDED);
COMMENT ON TYPE nvarchar2 IS 'Oracle type nvarchar2(length) mapped to
PostgreSQL type varchar(lenght)';



Re: Mapping Oracle types to PostgreSQL types

From
Peter Eisentraut
Date:
Jean-Michel POURE writes:

> Is there a way to map Oracle nvarchar2(lenght) to PostgreSQL varchar(lenght)
> in PostgreSQL 7.3? Are there plans to allow such mapping in the future using
> the CREATE DOMAIN syntax?

No to both.  Doing this would most likely require making the affected type
names be reserved words in the grammar or sacrifice some other
functionality, which seems a high price to pay for this cosmetic feature.

-- 
Peter Eisentraut   peter_e@gmx.net



Re: Mapping Oracle types to PostgreSQL types

From
Shridhar Daithankar
Date:
Jean-Michel POURE wrote:

> Dear friends,
> 
> I would like to port Compiere CRM from Oracle to PostgreSQL (and release it 
> for free).
> 
> At first I would like to convert the data schema. This is not difficult as 
> Compiere is written using portable types like NUMBER (i,d) which can be 
> replaced by NUMERIC (i,d), etc... A series of Search/Replace is sufficiant. 
> There are other solutions in Contrib to connect to Oracle and export the data 
> (Bruce). Don't blame me to search in another (silly) direction...

Rather than declaring numeric, create them as integer/float of appropriate size 
and add appropriate constraints. Numeric can be slower for large data load w.r.t 
native integers.

> 
> The point here is that I would like to use the CREATE TYPE or CREATE DOMAIN 
> syntax to map Oracle types to PostgreSQL types. Therefore I can say "Guys, 
> Oracle is now mostly compatible with PostreSQL".

You can create some sql scripts which can natively migrate from oracle to 
postgresql. Contrib could host them or gborg.

So what postgresql would say is, create a database and run the scripts and many 
of the oracle migration gotchas will be automatically taken care of.

Including such features in core postgresql is rather hard sell to postgresql 
developers. Especially when there is a rather simple workaround.
 HTH
 Shridhar



Re: Mapping Oracle types to PostgreSQL types

From
Andreas Pflug
Date:
Shridhar Daithankar wrote:

> Jean-Michel POURE wrote:
>
>> Dear friends,
>>
>> I would like to port Compiere CRM from Oracle to PostgreSQL (and 
>> release it for free).
>>
>> At first I would like to convert the data schema. This is not 
>> difficult as Compiere is written using portable types like NUMBER 
>> (i,d) which can be replaced by NUMERIC (i,d), etc... A series of 
>> Search/Replace is sufficiant. There are other solutions in Contrib to 
>> connect to Oracle and export the data (Bruce). Don't blame me to 
>> search in another (silly) direction...
>
>
> Rather than declaring numeric, create them as integer/float of 
> appropriate size and add appropriate constraints. Numeric can be 
> slower for large data load w.r.t native integers.


float is *not* an alternative to numeric. While integer or int8 should 
be usable for NUMBER(i), the non-rounding precision of numeric is vital 
for such an application.

Regards,
Andreas




Re: Mapping Oracle types to PostgreSQL types

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Jean-Michel POURE writes:
>> Is there a way to map Oracle nvarchar2(lenght) to PostgreSQL varchar(lenght)
>> in PostgreSQL 7.3? Are there plans to allow such mapping in the future using
>> the CREATE DOMAIN syntax?

> No to both.  Doing this would most likely require making the affected type
> names be reserved words in the grammar

Right.  At the moment, *all* the type names that support parenthesized
options are hard-wired into the grammar.  I think this is probably
unavoidable because otherwise there is a conflict between interpreting
"foo(3)" as a type name and interpreting it as a function call.  (But
if anyone can think of a way around that, I'm all ears.)

Since varchar(n) is SQL-standard syntax, can't you simply adopt the more
standard name for both databases?
        regards, tom lane


Re: Mapping Oracle types to PostgreSQL types

From
"Matthew T. O'Connor"
Date:
On Fri, 2003-10-17 at 04:10, Jean-Michel POURE wrote:
> Dear friends,
> 
> I would like to port Compiere CRM from Oracle to PostgreSQL (and release it 
> for free).

This would be wonderful.  However, I believe the guys at Compiere tried
to do this already and gave up on porting it to postgresql due too a
couple of PostgreSQL limitations.  I don't remember what they are
exactly, I think it had to do with nested transactions, maybe
savepoints, not sure exactly.  You should be able to find some mention
of this on their site.  It sounded to me like they use a lot of Oracle
features.

Good Luck!



Re: Mapping Oracle types to PostgreSQL types

From
Kris Jurka
Date:

On Fri, 17 Oct 2003, Tom Lane wrote:

> Since varchar(n) is SQL-standard syntax, can't you simply adopt the more
> standard name for both databases?
>

A long time ago Oracle made the varchar type equivalent to char and once
people complained about the excess space used by short entries they came
out with varchar2 which they've maintained every since valuing backwards
compatability more than the sql standard.

Kris Jurka




Re: Mapping Oracle types to PostgreSQL types

From
Jean-Michel POURE
Date:
Le Vendredi 17 Octobre 2003 16:32, Matthew T. O'Connor a écrit :
> This would be wonderful.  However, I believe the guys at Compiere tried
> to do this already and gave up on porting it to postgresql due too a
> couple of PostgreSQL limitations.  I don't remember what they are
> exactly, I think it had to do with nested transactions, maybe
> savepoints, not sure exactly.  You should be able to find some mention
> of this on their site.  It sounded to me like they use a lot of Oracle
> features.

There are only a few limitations in PostgreSQL like nested transaction,
updatable cursors and Oracle PL error handling. Can we call these
"limitations"? Most of us can live without them. These "limitations" are only
a small portion of the code (sometimes a few lines like updatable cursors).

Cheers, Jean-Michel



Re: Mapping Oracle types to PostgreSQL types

From
Jean-Paul ARGUDO
Date:
Le Friday Oct 17, 2003 at 10:10:26AM +0200, Jean-Michel POURE a écrit :
> Dear friends,
> 
> I would like to port Compiere CRM from Oracle to PostgreSQL (and release it 
> for free).

Hi Jean-Michel,


I did Red-Hat CCM Migration from Oracle to PostgreSQL a year ago when I
was the dba of IdealX, a french Open Source Services company in France,
you may know it. Since the job has been done for french public affairs,
I hope IdealX and this ministry did publish it GPL, I'm gonna get some
informations, I'll keep you on touch.

I do copy an Oracle production database everyday into a PostgreSQL one,
for web sites needs: production database is under Oracle, web reporting
database is on a LAPP server. (not LAMP ;-).. ).

I succeeded using oracle contrib (look at contrib/oracle or directly
to Gilles's DAROLD web at: http://www.samse.fr/GPL/).

Ora2pg is fast, simple and accurate, it handles PG schemas too, etc..

Beware of numeric(x,y) PG type, it's really slow, prefer integer
datatype. For example, translate a NUMERIC(3,2) into INTEGER. Just *100
the value, and modify client side (/100), it will be really faster (that was
the simple solution we found on another migration project at IdealX...).

[ As I told it here (http://www.geocrawler.com/archives/3/104/2002/5/0/8597590/)a year ago:
« Be aware that NUMERIC(x,y) in PG is very powerfull for atypic uses, forexample (18,9) ... and not that much for other
uses.That's my point ofview.»
 
I must admit I didnt test it from one year. So with the speed you PGhackers work, offering each release a new *major*
release(HISTORY itemsgrowth can show this: amazing..), I think maybe this is no more true...
 
]

I'd like to help you in your project, since I'm Oracle/PG experimented
DBA, and I need a good CRM solution for personal and profesional
purposes.

Please tell me if my experience can help you in any way, I'd be really
glad in participating your project. 

I can offer public CVS, web, etc.. for the project if you want.

Overall, I think we're both French ;-))

Cheers,

-- 
Jean-Paul ARGUDO
DBA Oracle, PostgreSQL, MySQL, Ingres
Membre de l'April            http://www.april.org


Re: Mapping Oracle types to PostgreSQL types

From
Jean-Michel POURE
Date:
Dear Jean-Paul,

> Please tell me if my experience can help you in any way, I'd be really
> glad in participating your project.

Thanks for your proposal, welcome in the team.

In short, we plan to port Compiere to PostgreSQL and submit the changes back
to Compiere team. There is no evidence so far that Compiere will accept the
changes as their main developer sells Oracle licenses.

At present, the team is:
- Vincent Harcq <vha@audaxis.com> for the Java part or Compiere.
- Jean-Paul ARGUDO <jpargudo@free.fr> and Jean-Michel Pouré <jm@poure.com> for
the database migration.

> I can offer public CVS, web, etc.. for the project if you want.

Thanks. During the week-end, I will set up a first web page and will contact
you back on Monday.

In a first time, our goal could be to describe the port in details, so to
convince Compiere community to migrate to PostgreSQL. Without support from
the Compiere community, there can be no port...

Best regards,
Jean-Michel