Writing oracle/postgress generic SQL

On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote:

Anyone know of any guidelines for writing SQL which works under
Oracle witch will also work under postgress. This is to ensure that
SQL written for an Oracle database can be migrated to postgress
later.

You've just bumped into the problem that while standard SQL exists,
only Mimer and possibly DB2 implement it. The presentation below
outlines your main choices for supporting more than one DB back-end,
and they're all expensive and troublesome to maintain.

http://www.powerpostgresql.com/Downloads/database_depends_public.swf

The cheapest, highest-quality thing to do is to choose one DB back-end
and then use everything it has to offer.

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

"Ben Edwards" <funkytwig@gmail.com> writes:

Anyone know of any guidelines for writing SQL which works under Oracle
witch will also work under postgress.

The only thing that means anything is testing on both :-(. Yeah, there
is a SQL standard, but there is no DBMS anywhere in the world that
implements all and only what is in the spec. Exhibit A in this regard
is that the standard refuses to specify any user-visible index
manipulation; so the moment you write anything like CREATE INDEX you
are on implementation-dependent ground.

regards, tom lane

On Fri, 23 Feb 2007, David Fetter wrote:

On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote:

Anyone know of any guidelines for writing SQL which works under
Oracle witch will also work under postgress. This is to ensure that
SQL written for an Oracle database can be migrated to postgress
later.

You've just bumped into the problem that while standard SQL exists,
only Mimer and possibly DB2 implement it. The presentation below
outlines your main choices for supporting more than one DB back-end,
and they're all expensive and troublesome to maintain.

http://www.powerpostgresql.com/Downloads/database_depends_public.swf

With all due respect to Josh's presentation, there's a lot more to the
story than those couple of slides. (They were meant to be given, I'm sure,
along with a talk in which the speaker provided most of the value.) And I
don't think launching an attack on MySql is helpful to this dialogue,
though I do understand the point Josh is making...

There are other choices. For example, Science Tools, back in 1997, faced
with the similar but slightly different problem of being a vendor
supporting multiple RDBMSes for client data, could have taken the typical
choice of managing different code branches for each of the RDBMSes it
supports. Instead, we wrote an SQL dialect translator that presently
supports five (and soon six) RDBMS platforms - and could probably support
all the rest if only someone cared enough to configure them - and this
translator is available to customers, not just embeded for the exclusive
use of Science Tools' applications. You link your user-application code to
our library and you can send it any version of SQL, either statically or
dynamically, and it automatically translates into the correct dialect for
the database engine you're connected to. It does both DDL and DML and it
has command-line tools available, too, so you don't have to link your apps
if you don't want to. Presently supported are: Postgres (of course!),
Informix, DB2, Sybase, and also Oracle - yes, of course, them, too.
(OpenIngres is undergoing testing right now for certification sometime
this spring.)

Are there things it misses? Yes, but not much. I'll take the wild guess
that more than 80% of applications are completely and adequately served.
It has pass-through capability so you can still get at engine-specific
features, though it does completely side-step stored procedures as these
are vastly harder to automate conversion of - we just do the SQL. When
calling a DBMS from our library, we handle error recovery, database
reconnection, optional DBMS independent journaling and even important
aspects of security. When parsing DDL, it (optionally) throws warnings of
incompatability, though, as a practical matter, most engines have now
removed most of their older limitations that made this vital in their
earlier versions. (We support versions of all five since about 1997 and,
as there were so many small changes along the way, we provide a
configuration mechanism where you can tell it the limitations of your
version such as attribute length, maximum length of varchar, etc.)

Regards,
Richard

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy@ScienceTools.com, http://ScienceTools.com/

Ben Edwards wrote:

Anyone know of any guidelines for writing SQL which works under Oracle
witch will also work under postgress. This is to ensure that SQL
written for an Oracle database can be migrated to postgress later.

I converted a fairly complex data collection application from Oracle to
PG about 2 yrs ago. I was pleasantly surprised at how little DML I had
to change, and some of it had deeply nested subqueries. Here are the
snags I hit:

(1) Stored procedures had to be rewritten by hand. You might want to
look at EnterpriseDB, as they've added on to PG to enhance Oracle
compatibility.

(2) I had to change all the stored procedure invocations that used
Oracle's "call myproc()" syntax. If we had used JDBC standard calling
conventions, this would not have been necessary. (Just realized you
didn't say which language you are using.)

(3) Stay away for Oracle proprietary SQL features, like their use of (+)
for outer joins. This was a version 8 oddity, and they support standard
outer join syntax now.

(4) We had significant use of Oracle dblinks in our SQL, and of course
that doesn't translate. PG has a dblink capability in contrib, but it
is not as complete an implementation as Oracle's.

Hope that helps.

--
Guy Rouillier

I've converted stuff from PostgreSQL to Oracle before, and some of the
biggest pains were "OFFSET ... LIMIT ..." in PostgreSQL vs. ROWNUM or
ROW_NUMBER in Oracle (depending on version of Oracle, including having to
wrap the query with ROWNUM/ROW_NUMBER in a subselect - I greatly prefer
OFFSET and LIMIT, especially for web applications), and sequence NEXTVAL
syntax. There may be some date type conversion / formatting issues, too.
Temporary tables were somewhat different, too, as I recall.

You might be able to hide some of the internal differences by creating
database-specific views and functions, and using simpler queries from the
views at a higher level of the application. Of course, there is that
weird Oracle thing where you have to say "SELECT .... from DUAL" instead
of just "SELECT xxx" to get simple function return values.

Some of the Oracle stuff may differ, depending on version.

Susan Cassidy

"Ben Edwards" <funkytwig@gmail.com>
Sent by: pgsql-general-owner@postgresql.org
02/23/2007 01:27 AM

To
pgsql-general@postgresql.org
cc

Subject
[GENERAL] Writing oracle/postgress generic SQL

Anyone know of any guidelines for writing SQL which works under Oracle
witch will also work under postgress. This is to ensure that SQL
written for an Oracle database can be migrated to postgress later.

Ben
--
Ben Edwards - Brussels, Belgium & Bristol, UK
If you have a problem emailing me use
http://www.gurtlush.org.uk/profiles.php?uid=4
(email address this email is sent from may be defunct)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------

On Fri, Feb 23, 2007 at 08:28:06AM -0800, Richard Troy wrote:

On Fri, 23 Feb 2007, David Fetter wrote:

On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote:

Anyone know of any guidelines for writing SQL which works under
Oracle witch will also work under postgress. This is to ensure that
SQL written for an Oracle database can be migrated to postgress
later.

You've just bumped into the problem that while standard SQL exists,
only Mimer and possibly DB2 implement it. The presentation below
outlines your main choices for supporting more than one DB back-end,
and they're all expensive and troublesome to maintain.

http://www.powerpostgresql.com/Downloads/database_depends_public.swf

With all due respect to Josh's presentation, there's a lot more to
the story than those couple of slides.

With all due respect, the presentation was if anything an
understatement. Unless, as with rare beasties like Science Tools, the
major purpose of the application is to support multiple DBMS
back-ends, it's just too expensive. Even in those rare cases, it's
expensive.

[sales pitch elided ;)]

Are there things it misses? Yes, but not much. I'll take the wild
guess that more than 80% of applications are completely and
adequately served.

That says something about the applications you've seen, and not about
the adequacy of such a library. What point is there in using a
powerful tool like an RDBMS and then hobbling yourself by only using
10% of the available features? It's certainly a bad thing to do by
default.

It has pass-through capability so you can still get at engine-specific
features, though it does completely side-step stored procedures

Oops! There went 60% of the code in some of the databases I've seen
in production. 80% in at least one case I've seen in the past year.

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
http://www.postgresql.org/about/donate

On Fri, 23 Feb 2007, David Fetter wrote:

On Fri, Feb 23, 2007 at 08:28:06AM -0800, Richard Troy wrote:

On Fri, 23 Feb 2007, David Fetter wrote:

On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote:

Anyone know of any guidelines for writing SQL which works under
Oracle witch will also work under postgress. This is to ensure that
SQL written for an Oracle database can be migrated to postgress
later.

You've just bumped into the problem that while standard SQL exists,
only Mimer and possibly DB2 implement it. The presentation below
outlines your main choices for supporting more than one DB back-end,
and they're all expensive and troublesome to maintain.

http://www.powerpostgresql.com/Downloads/database_depends_public.swf

With all due respect to Josh's presentation, there's a lot more to
the story than those couple of slides.

With all due respect, the presentation was if anything an
understatement.

Yes; it didn't say very much. I'm sure Josh, as speaker, articulated what
wasn't in those slides, but we didn't get the benefit of that on the web.

Unless, as with rare beasties like Science Tools, the
major purpose of the application is to support multiple DBMS
back-ends, it's just too expensive. Even in those rare cases, it's
expensive.

I guess anything you have to pay for is too expensive. (Sounds like dogma
to me. And you know what dogma makes - just don't step in it.)

Are there things it misses? Yes, but not much. I'll take the wild
guess that more than 80% of applications are completely and
adequately served.

That says something about the applications you've seen, and not about
the adequacy of such a library.

That remark is uninformed and arrogantly presumptuous about both me and
the library, and uninsightful regarding the implementation of
applications. It's also needlessly offensive, if you'll forgive the pun.

What point is there in using a
powerful tool like an RDBMS and then hobbling yourself by only using
10% of the available features? It's certainly a bad thing to do by
default.

10%? Whatever. I never said anything of the kind - and I'm reminded that
an unsupported argument can be dismissed without support. But there ARE
good reasons. We read on this very list about two weeks ago a long
treatise on the subject by an obviously long-in-the-tooth DBA type who
articulately took at least four pages to tell us why it was his practice
and advice to always be able to move to another RDBMS. Perhaps read the
archives and become informed...

It has pass-through capability so you can still get at engine-specific
features, though it does completely side-step stored procedures

Oops! There went 60% of the code in some of the databases I've seen
in production. 80% in at least one case I've seen in the past year.

Lots of people use stored procedures and some people over-use them while
some others under-utilize them in their architectures. It should be no
surprise that some people follow dogma while others consider every arrow
in their quiver. Yet I detect a certain flippant bigottry in your response
- Oops! Perhaps a more considered argument would be effective than just an
attack - that is, presuming there's a considered argument to be made.

The short of it is that Science Tools is surely not alone in having
developed an SQL dialect translator, though we may be the only ones to
offer it to customers. Either way, automated dialect translation, whether
by us otherwise, is another useful choice whether _you_ like it or not.

Ciao,
Richard

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy@ScienceTools.com, http://ScienceTools.com/

Unless, as with rare beasties like Science Tools, the
major purpose of the application is to support multiple DBMS
back-ends, it's just too expensive. Even in those rare cases, it's
expensive.

I guess anything you have to pay for is too expensive. (Sounds like dogma
to me. And you know what dogma makes - just don't step in it.)

*cough* There really isn't a good argument in general for abstracting
out database access to support multiple platforms.

The only argument I ever see is:

We want our product to support as many databases as possible. Which is
certainly a valid business argument but certainly not a good technical
argument.

Are there things it misses? Yes, but not much. I'll take the wild
guess that more than 80% of applications are completely and
adequately served.

That says something about the applications you've seen, and not about
the adequacy of such a library.

That remark is uninformed and arrogantly presumptuous about both me and
the library, and uninsightful regarding the implementation of
applications. It's also needlessly offensive, if you'll forgive the pun.

I am not sure why you would be offended by another's experience. I am
offended that you are offended that he wasn't offended. Good lord, take
a breath.

I would agree that in my experience most applications that choose to
abstract their database usage generally make bad choices in how they do
it and thus have a negative impact on not only the survivability of
existing code but the maintainability of said code.

Are their apps out there that do it right? Oh probably, I have never
seen one though.

The short of it is that Science Tools is surely not alone in having
developed an SQL dialect translator, though we may be the only ones to
offer it to customers. Either way, automated dialect translation, whether
by us otherwise, is another useful choice whether _you_ like it or not.

useful not always == good.

Windows is useful.

Windows is not good.

Joshua D. Drake

Ciao,
Richard

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Mon, Feb 26, 2007 at 08:01:52AM -0800, Richard Troy wrote:

On Fri, 23 Feb 2007, David Fetter wrote:

On Fri, Feb 23, 2007 at 08:28:06AM -0800, Richard Troy wrote:

On Fri, 23 Feb 2007, David Fetter wrote:

On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote:

Anyone know of any guidelines for writing SQL which works under
Oracle witch will also work under postgress. This is to ensure that
SQL written for an Oracle database can be migrated to postgress
later.

You've just bumped into the problem that while standard SQL exists,
only Mimer and possibly DB2 implement it. The presentation below
outlines your main choices for supporting more than one DB back-end,
and they're all expensive and troublesome to maintain.

http://www.powerpostgresql.com/Downloads/database_depends_public.swf

With all due respect to Josh's presentation, there's a lot more
to the story than those couple of slides.

With all due respect, the presentation was if anything an
understatement.

Yes; it didn't say very much. I'm sure Josh, as speaker, articulated
what wasn't in those slides, but we didn't get the benefit of that
on the web.

The presentation understated the problems with trying to support more
than one DBMS back-end.

Unless, as with rare beasties like Science Tools, the major
purpose of the application is to support multiple DBMS back-ends,
it's just too expensive. Even in those rare cases, it's
expensive.

I guess anything you have to pay for is too expensive. (Sounds like
dogma to me. And you know what dogma makes - just don't step in it.)

If you're determined to take offense, especially after your phone call
wherein I thought we had discussed this rationally, I can't stop you.

I can tell you that I've tried many times over the years and seen
plenty of other efforts to make database-independent code, and in no
case was it cheap even to attempt. Either it pushes lots of work from
the database out into application land, or it's duplicating database
code that essentially does the same thing for each back-end RDBMS.

The first is expensive because the applications are now doing things
that the database is good at, and the second is expensive because
maintaining parallel code bases where the design criterion is that
they must behave identically is never going to be cheap. The first
piles on the second one's cost as soon as there is more than one
application.

None of this has anything to do with the business model. It has to do
with essential qualities of software development.

Are there things it misses? Yes, but not much. I'll take the
wild guess that more than 80% of applications are completely and
adequately served.

That says something about the applications you've seen, and not
about the adequacy of such a library.

That remark is uninformed and arrogantly presumptuous about both me
and the library, and uninsightful regarding the implementation of
applications. It's also needlessly offensive, if you'll forgive the
pun.

Since Science Tools is not in the business of selling SQL translators,
you'll of course be delighted to show just exactly how it works and
for what cases. The "treat the DBMS as a dumb data store" model is
one that's been widely tested and proven inadequate from the viewpoint
of the organization that has to maintain said data store. That model
can be quite lucrative for vendors, and more power to them.

What point is there in using a powerful tool like an RDBMS and
then hobbling yourself by only using 10% of the available
features? It's certainly a bad thing to do by default.

10%? Whatever. I never said anything of the kind - and I'm reminded
that an unsupported argument can be dismissed without support. But
there ARE good reasons. We read on this very list about two weeks
ago a long treatise on the subject by an obviously long-in-the-tooth
DBA type who articulately took at least four pages to tell us why it
was his practice and advice to always be able to move to another
RDBMS. Perhaps read the archives and become informed...

I'm informed. I am aware that some of the cute tricks DBMS vendors
used to play by making it expensive to switch back-ends weren't
terribly ethical, just as the cute tricks Unix vendors used to play
weren't. That was the late 1980s and early 1990s, and the situation
now is different. Without needing to introduce intentional
incompatibilities, RDBMSs are so different from one another that it's
just about impossible to make code that's exactly identical, one to
the other. I'd contend that it's impossible without pushing work out
into the application layers, which is that "dumb data store" model.

It has pass-through capability so you can still get at
engine-specific features, though it does completely side-step
stored procedures

Oops! There went 60% of the code in some of the databases I've
seen in production. 80% in at least one case I've seen in the
past year.

Lots of people use stored procedures and some people over-use them
while some others under-utilize them in their architectures. It
should be no surprise that some people follow dogma while others
consider every arrow in their quiver.

You keep saying this word, "dogma." I prefer the term, "best
practices," and since I'm in the business of helping people who have
to manage the data, my "best practice" is to put their interests ahead
of vendors'. Attempts at database independence really serve
vendors--free software or otherwise--first, and people managing
databases a distant second.

Yet I detect a certain flippant bigottry in your response - Oops!
Perhaps a more considered argument would be effective than just an
attack - that is, presuming there's a considered argument to be
made.

The short of it is that Science Tools is surely not alone in having
developed an SQL dialect translator, though we may be the only ones
to offer it to customers. Either way, automated dialect
translation, whether by us otherwise, is another useful choice
whether _you_ like it or not.

Some day, and maybe that day is today with Science Tools, SQL dialect
translators will be so good at what they do that we'll only write
DBMS-specific SQL for a subset of code that is to SQL what
hand-tooled machine-specific assembler code is to C. If that day has
arrived, great! Until it does, though, we're kinda stuck with what we
have, and should deal with it that way.

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
http://www.postgresql.org/about/donate