Mapping Oracle types to PostgreSQL types

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

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

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

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

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!

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

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

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, for
example (18,9) ... and not that much for other uses. That's my point of
view.�

I must admit I didnt test it from one year. So with the speed you PG
hackers work, offering each release a new *major* release (HISTORY items
growth 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

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

On Sat, 2003-10-18 at 09:11, Jean-Michel POURE wrote:

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...

by "community" do you means users or developers? there are certainly
enough users interested in a postgresql version that i think it would be
viable to fork it if the compiere developers aren't willing to
cooperate. certainly would be more work, but if you could get a stable
version I think it could be successful.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Le Lundi 20 Octobre 2003 14:52, Robert Treat a écrit :

by "community" do you means users or developers? there are certainly
enough users interested in a postgresql version that i think it would be
viable to fork it if the compiere developers aren't willing to
cooperate. certainly would be more work, but if you could get a stable
version I think it could be successful.  

Dear Robert,

At first, the project has limited goals: porting Compiere PL server-side code
to PLpgSQL, removing some non-SQL 99 stuff and submitting the code back and
convincing Compiere developer community to use PostgreSQL.

Then, we will see. The problem with forking is that you have to create a
developer community from scratch, which is highly difficult and most of times
impossible.

Best regards,
Jean-Michel

Robert Treat wrote:

On Sat, 2003-10-18 at 09:11, Jean-Michel POURE wrote:

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...

by "community" do you means users or developers? there are certainly
enough users interested in a postgresql version that i think it would be
viable to fork it if the compiere developers aren't willing to
cooperate. certainly would be more work, but if you could get a stable
version I think it could be successful.

Robert Treat

I'll be happy if I can help you. I test Compiere since june and I asked
compiere what to do in order to port compiere to postgres and never
receive answer. But I think they accept this solution because they have
an old version with postgres and they know somebody running Compiere
with another database (adabas ? DB2 ? I don't remember)

Bruno

--
Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque@net6d.com
http://www.net6d.com

Vincent,

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.

Please port it. Compiere's dependence on Oracle has prevented me from
evaluating it for my clients. I'll be happy to provide advice/patches down
the line; I have some experience with accounting databases.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Le Lundi 20 Octobre 2003 17:22, Bruno LEVEQUE a écrit :

I'll be happy if I can help you. I test Compiere since june and I asked
compiere what to do in order to port compiere to postgres and never
receive answer. But I think they accept this solution because they have
an old version with postgres and they know somebody running Compiere
with another database (adabas ? DB2 ? I  don't remember)

I received identical remarks from other people. The developer of Compiere
sells Oracle licenses. Maybe he lives on it... Let's do the port and then we
will see.

Bruno, I am adding you to the list of interested people and will publish a web
page today.

Cheers, Jean-Michel

Jean-Michel POURE wrote:

Le Lundi 20 Octobre 2003 17:22, Bruno LEVEQUE a ?crit :

I'll be happy if I can help you. I test Compiere since june and I asked
compiere what to do in order to port compiere to postgres and never
receive answer. But I think they accept this solution because they have
an old version with postgres and they know somebody running Compiere
with another database (adabas ? DB2 ? I ?don't remember)

I received identical remarks from other people. The developer of Compiere
sells Oracle licenses. Maybe he lives on it... Let's do the port and then we
will see.

Bruno, I am adding you to the list of interested people and will publish a web
page today.

My guess is that an appeal to the broader Compiere user community would
sway their thinking.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Hi guys,

Jorg Janke (the "main" Compiere person) and I had a brief dicussion last
year.

Pretty much he asked if PostgreSQL (either the company and/or the
community) would be interested in _making PostgreSQL compatible with
Compiere_ or if not, then how about we and _make and maintain_ a fork of
PostgreSQL that did the right kind of transaction support to work
Compiere. The return for doing that would have been for PostgreSQL to
the be Compiere "default" (recommended) database.

Pointed out to him that it was unlikely to happen from the Community's
point of view, but if there could be a good case made for it from a
profitability point of view then it wouldn't hurt to consider it as far
as a company-done-thing.

For this to occur, we needed statistics of how many end user
installations of Compiere there are, and then we'd try and use that to
get a realistic idea of market demand for PostgreSQL and PostgreSQL
services from that, etc. (i.e. decent common sense to start with)

Jorg absolutely refused to co-operate. The only information he was
willing to provide was to point at the number of downloads.

Suggested to him that it would likely be worth putting some kind of
"installation" counting measures in places so we could at least get an
idea of real world terms, but instead he just got offended that the
download counter wasn't enough. (and I was *Really* super nice in my
approach here, trying to suggest getting information that *any* place
would have found absolutely necessary at a very minimum in order to proceed)

No dice. Spoke to a few others, and they had the same opinion of that
situation - *not business practical* (especially with an unfriendly
requestor).

That being said, if this effort to get Compiere and PostgreSQL working
does happen successfully, then all the better.

:-)

Regards and best wishes,

Justin Clift

Bruce Momjian wrote:

Justin Clift wrote:

Hi guys,

Jorg Janke (the "main" Compiere person) and I had a brief dicussion last
year.

Pretty much he asked if PostgreSQL (either the company and/or the
community) would be interested in _making PostgreSQL compatible with
Compiere_ or if not, then how about we and _make and maintain_ a fork of
PostgreSQL that did the right kind of transaction support to work
Compiere. The return for doing that would have been for PostgreSQL to
the be Compiere "default" (recommended) database.

It is hard to understand that if he is willing to make PostgreSQL the
default for Compiere, why he would refuse to include any changes to
allow Oracle and PostgreSQL to both work.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Tue, 21 Oct 2003, Bruce Momjian wrote:

Justin Clift wrote:

Hi guys,

Jorg Janke (the "main" Compiere person) and I had a brief dicussion last
year.

Pretty much he asked if PostgreSQL (either the company and/or the
community) would be interested in _making PostgreSQL compatible with
Compiere_ or if not, then how about we and _make and maintain_ a fork of
PostgreSQL that did the right kind of transaction support to work
Compiere. The return for doing that would have been for PostgreSQL to
the be Compiere "default" (recommended) database.

It is hard to understand that if he is willing to make PostgreSQL the
default for Compiere, why he would refuse to include any changes to
allow Oracle and PostgreSQL to both work.

But if what he wants is nested transactions, then I could see him paying
for development providing better ROI than forking the code. Is that what
was meant by "the right kind of transaction support" ?

Scott,

It is hard to understand that if he is willing to make PostgreSQL the
default for Compiere, why he would refuse to include any changes to
allow Oracle and PostgreSQL to both work.

But if what he wants is nested transactions, then I could see him paying
for development providing better ROI than forking the code. Is that what
was meant by "the right kind of transaction support" ?

If he wanted nested transactions, and was willing to pay for development, I
can't imagine us turning him down. Nested xtn have been on the TODO list for
years.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

On Tue, 2003-10-21 at 17:00, Josh Berkus wrote:

Scott,

It is hard to understand that if he is willing to make PostgreSQL the
default for Compiere, why he would refuse to include any changes to
allow Oracle and PostgreSQL to both work.

But if what he wants is nested transactions, then I could see him paying
for development providing better ROI than forking the code. Is that what
was meant by "the right kind of transaction support" ?

If he wanted nested transactions, and was willing to pay for development, I
can't imagine us turning him down. Nested xtn have been on the TODO list for
years.

I think his (his being the compiere guy) point was that if someone in
the postgresql community wanted to implement some of the more difficult
features into postgresql that he would make it the default db for
compiere. however he tried himself to raise funds to make it happen and
he could not, so it's on the postgresql community to make it happen
now.

The big question as I see it is whether he is willing to make
incremental steps to make compiere more postgresql compatible if it
might hurt the oracle side (the nvarchar2 to varchar data type change is
a good example of this). If he is willing to make these changes then all
is good, if not then istm that a fork would be the likely outcome.

course, its a lot of speculation and discussion that should probably
happen on the compiere lists, they have discussed this issue before and
would do so again, and as bruce said, getting the compiere community
support is the first step.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

scott.marlowe wrote:

On Tue, 21 Oct 2003, Bruce Momjian wrote:

Justin Clift wrote:

Hi guys,

Jorg Janke (the "main" Compiere person) and I had a brief dicussion last
year.

Pretty much he asked if PostgreSQL (either the company and/or the
community) would be interested in _making PostgreSQL compatible with
Compiere_ or if not, then how about we and _make and maintain_ a fork of
PostgreSQL that did the right kind of transaction support to work
Compiere. The return for doing that would have been for PostgreSQL to
the be Compiere "default" (recommended) database.

It is hard to understand that if he is willing to make PostgreSQL the
default for Compiere, why he would refuse to include any changes to
allow Oracle and PostgreSQL to both work.

But if what he wants is nested transactions, then I could see him paying
for development providing better ROI than forking the code. Is that what
was meant by "the right kind of transaction support" ?

Oh, that makes complete sense. You can't really code around the lack of
nested transactions, at least not cleanly. Let's get nested
transactions and go back to them.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Hi Scott,

scott.marlowe wrote:
<snip>

But if what he wants is nested transactions, then I could see him paying
for development providing better ROI than forking the code. Is that what
was meant by "the right kind of transaction support" ?

Yep, that's what was needed. :-)

Regards and best wishes,

Justin Clift