To Support, or Not Support Oracle? 64
knuckles79 wonders: "The suggestion to drop Oracle support has divided the OpenACS community. OpenACS is a toolkit for building community websites. It was derived from Ars Digita's ACS code base which originally supported Oracle. When Ars Digita went bust after the tech crash, the ACS code was released as open source, and a community of developers continued to maintain and extend the code base. Up until now, OpenACS has supported both PostgreSQL and Oracle. However, the only active development within the project supports PostgreSQL. Now, those with an interest in Oracle support are threatening to divide the community, as they want the community to continue to support Oracle, even though they themselves aren't actively contributing financial or development support for their favoured database. They have essentially been given a 'free ride' all this time. Should OpenACS continue to support Oracle, or drop it in favour of a full open source stack?"
Oracle support isn't much different from Pgsql (Score:5, Informative)
Anyway, we have two query catalogs: "sql92" and "oracle". What's nice about the way the system is set up is that we put new SQL queries into the sql92 catalog unless we need to use something Oracle specific (very rare). In that rare case we write the sql92 query and then write an identically named query in the Oracle catalog with the Oracle syntax. When the system is configured to talk to Oracle, it looks into the oracle query catalog first and if it doesn't find the named query there it looks for it in the sql92 catalog. The result is that we can support both databases with minimal duplication. 99% of the queries are in the sql92 catalog. We've found that whenever you want to use an Oracle-ism then applying some thought will usually reveal another way to handle the problem that works in both databases. Example: using case statements [postgresql.org] instead of Oracle's decode [techonthenet.com].
Schemas are a different story. The datatypes and details are different enough that we have to keep two copies of the files to create the schema and the schema updates. However, these files aren't changed that much. Also, if you are familiar with Oracle's PL/SQL then Postgres's PL/pgSQL isn't much different.
Because of the way things are abstracted in our app it appears that it would be easy to add support for other databases as needed. It's sure been a breeze to support both Oracle and Postgres.
Re:Drop it (Score:2, Informative)
Do you know how expensive it is to abstract away differences in database syntax (especially when large companies like Oracle make significant ca$h intentionally making their syntax different/nonstandard)? If you think it's so easy, perhaps you should write it yourself for OpenACS instead of bitching about why it's not there...
Both Oracle and PostgreSQL put a lot of effort into standards compliance. Contrary to your unfounded claim Oracle has better standards compliance than pretty much any other database out there and has always been the benchmark against which other databases are judged, I will be very surprised if you can point to any reputable source that claims Oracle deliberately violates SQL standards.
In fact Oracles implementation of the SQL-92 standard is better than PostgreSQLs, better is probably the wrong word, more feature complete would be more accurate since both databases are highly compliant where they implement the standard. They do differ somewhat in the various implementation defined parts, but that is mostly to do with data definition and not the normal query/update syntax where both oracle and postgresql behave very closely.
Re:Drop it (Score:3, Informative)
My impression was that ACS was built using mostly stored procedures. Kind of hard to abstract away your execution environment...