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 <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! http://www.postgresql.org/about/donate ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
