Forum OpenACS Development: Oracle and PG versions getting out of sync

We faced the same problem and we just gave up for all packages except core, though we try to be intelligent enough about it to make the use of DB specific code minimal.

After all, if someone decides to make an Oracle Version, it should be fairly simple for him/her to find -postgresql.xql files, rewrite them for Oracle and test the application.

I think a good idea would be to write a script which checks if there is always a -oracle.xql were there is a -postgres.xql. This won't help for change in the /tcl directory or changes in already ported Oracle files, but then again cvs.openacs.org and the cvs diffs would be your friend. Though again a view that shows changes on -postgresql.xql files after the last commit to the -oracle.xql version would be a tremendous help.

We have to also reconcile the fact that the majority of developers only work with PostgreSQL.

Besides this, in many cases there are ways to minimize the amount of database specific code that needs to be written.

Using the content repository Tcl API, which is already supported 100% on Oracle and PG is one step. Also using package_exec_plsql instead of putting plpgsql calls helps, although someone needs to write the pl/sql code to match the plpgsql code. This really isn't that hard, but can be time consuming if the PostgreSQL code has had major changes.

Obviously data models also need to be updated.

Overall can we expect that developers that use Oracle and want these packages to support Oracle, contribute to the work to make these packages Oracle compatible?

I guess the problem is that, the code has gone a long time without being maintained for Oracle. If a developer makes improvements to the PostgreSQL version, but does not have experience or access to Oracle to make the equivilant changes, what should they do? Is there a way to communicate the need for help with Oracle work, and are there volunteers who are willing to do this work?

I don't expect someone to get all the nuances of Oracle syntax correct if they aren't working on Oracle. But I'm dealing with things like new columns having been added to the data model for Postgres but not having been added to any of the Oracle files even where it would have been straightforward to do so. If the PG-only folks can do that, and perhaps indicate via comments where queries need changes that they don't know how to make, then those of us still using Oracle would have an easier time completing the work.

I also do think it would be wise to have maturity levels for each version, to at least warn people about the situation. I was foolish enough to see that there were *-oracle.xql files for this package and tell my client that it was already ported, so now I'm going to have to go back to him and explain why it's taking me days instead of hours to get this installed for him.

This is not the first time it has been pointed out that very few are using Oracle these days. That, combined with the fact that part of my current slog is due to someone having copied the *-postgresql.xql files to *-oracle.xql and then just leaving any queries that worked for both in place instead of moving them to a common file, has me thinking about the whole way we are handling this. Given that very few are using Oracle anymore, and that it's unlikely that the community is ever going to bother supporting a third database, is it really necessary to pull all queries out into xql files? It is looking to me like it would improve usability for all, especially PG people, if the PG queries were in the Tcl scripts like they used to be and only Oracle-specific queries were put into xql files.

This would be a huge departure and reversal of what we have been doing, but I think in the long run it would be an improvement. Not that anyone wants to go through the entire code base and move queries around; it would be something that people would do a bit at a time as they were working in that area anyway.

Just a thought, not even a suggestion at this point - was curious if anyone else thinks this might be a good idea.

So the real problem is not knowing the status of oracle support.

I suggest putting a text file in the package root called README.ORACLE or README.POSTGRESQL so we can communicate the status

Also adding RELEASE_NOTES or something similar would be good so folks could find out what is new in a package whether or not the Oracle support is done.

Let's be blunt: Oracle is, at this point, almost completely dispensable.

The reason that Postgres is being maintained and Oracle is not, is being Postgres is what is actually being used to develop new packages and then deliver those packages.

Those people still running on Oracle have already committed to OpenACS and are in maintenance mode with their code; so they would have to backport or otherwise modify code in any case.

ALL of the growth going forward, as the OpenACS and .LRN communities grow, will be on Postgres.

While I was at EduCause, not one IT person I spoke to was even interested in Oracle as a base for a web-facing application. While the brochure on .LRN mention both Postgres and Oracle support, the questions were more about features-and-benefits and support issues.

This is not an isolated, OpenACS-only situation, either. Sakai, Moodle etc. use different databases and only incidentally if at all, support Oracle (e.g. Sakai supports it because they have a JDBC-based layer and support for Oracle 10g and MySQL are what have been written so far).

The days of Oracle support are numbered, unless and until the folks actually using Oracle step up and do it themselves, just as the Postgres users have done.

I think with the migration that has happened at Galileo (from Oracle to PostgreSQL) and maybe one or two other institutions following this road, we would have a proven path for dropping Oracle Support altogether.

Though this might sound painful for the current users of Oracle, dropping support does not mean we are not supporting it in terms of you can't run Oracle even if you want to. It is more a "if you run Oracle, you are on your own (with all the other Oracle users)". Well, this is the current situation anyway, so...

I'd suggest to tag the packages once they run oracle with an "oracle-works" or something like this tag. This way we could get a state of a package where Oracle support has been working and if PostgreSQL goes further, then organizations running Oracle will at one stage be able to update the script to PostgreSQL and be happy with it.

Very good news that Jeff's cvs RSS feed is back up. Could someone post again the full syntax (or Jeff, could you quickly write up the possible parameters to show up at http://xarg.net/tools/cvs/ ?).

I would like to generate a feed for 5.1, 5.2 and HEAD (all packages)

Nima,

Can hibernate translate plpgsql to pl/sql or the reverse? OpenACS definitely does not lend itself to a transparent peristence layer. You would not be happy with the performance compromise. It would really require a redesign of OpenACS.

Of course, if you minimize use of pl/sql and use more standard SQL newer versions of Oracle will have easier support.

I agree completely with Janine. There's something called "consistency", and it's valuable. As long as OpenACS officially claims to support Oracle, then it had damn well better actually support Oracle. And if that support has caveats or limitations, then those need to be clearly, correctly, and, uh, consistently stated.

IMNSHO, if any individual developer chooses not to even do the easy, 2% extra effort stuff to help maintain support for both databases, then whether he knows it or not, that developer has taken it upon himself to sabotage OpenACS's multi-db support, and therefore to make the OpenACS community look like a bunch of sloppy, inconsistent dilettantes. And that's a Bad Thing...

We could say we support Oracle 10g XE (http://www.oracle.com/technology/products/database/xe/index.html), which is free to use and might attract more developers to use it.

Thanks Torben.

I will add more information on porting PostgreSQL and Oracle code back and forth to that page soon.

Janine,

I'll bring it up at the OCT meeting. I think breaking Oracle is a bug, and it is not acceptable, but, personally I can't keep track on every change in every package.

So, a policy on supporting Oracle is one thing, education of developers is the second. A simple statement in the developer documentation and documentation on how to support both databases will help. We also will need volunteers to help developers out, and possibly even gently remind them if they forget.

I think we should clearly communicate how to support Oracle, and let developers know if they need help converting to Oracle, or help testing their Oracle code, that someone will be able to help out. This is where folks with Oracle installs can help.

I think in the past there has been discussion of a general Oracle install where folks could test their code. We also have the test servers https://openacs.org/wiki/Test%20Servers so folks who commit should check that their code works on these test servers if possible.

Just to be clear, I don't expect the OCT to police the codebase, but I do think a clear statement needs to be made - either we are still supporting Oracle or we are not. As this thread illustrates, there is a strong feeling out there that since Oracle is not in as widespread use among OpenACS installations as it once was, it's on the way out. That becomes an excuse for some to not bother updating Oracle queries at all. Clearly stating that Oracle is still supported and that maintaining both sets of queries is expected will not solve the problem, but it may help a little. At least it takes away the easy excuse.

OTOH, if it turns out that the current state of things continues, then I think it would be better to drop Oracle support than to have it be continually broken. I am not so much arguing for Oracle support here as I am for having code that works as advertised.

Could someone with Oracle expertise leave a comment on bug 2674 with the version of Oracle that should be represented in api-doc ?