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

Request notifications

I'm currently working on a package (which will remain nameless as my intention is not to point fingers at anyone) which appears to have been ported to Oracle at one time, but then work has gone on in Postgres and no attempt has been made to keep the Oracle code up to date. I'm currently slogging through every file, cleaning up duplicate and missing queries from xql files and backporting code changes from the Postgres queries to the Oracle queries, with an ultimate goal of making it work for Oracle. It's not exactly my idea of a good time.

I have a few comments and questions for the community and the OCT:

1. Do we have, or can we have, any policies to prevent this from happening? I have no problem with packages that simply don't support Oracle, but this is more like a trampling of what was already in place.

2. I am working with the 5.1.5 version of the package. I have checked the 5.2 and HEAD versions and they have the same problems, so I have continued working with the version that matches my installation. I want to commit these changes when I'm done, but I think it requires more CVS mojo than I currently have to get these changes merged with the work that has been ongoing in this package. Is there documentation on this, or someone who can help?

There was a third one, but I've forgotten what it was. Hours of editing xql files will do that to you....

Collapse
Posted by Malte Sussdorff on
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.

Collapse
Posted by Alex Kroman on
It seems like there needs to be a way to connotate the stability of the postgres and oracle version in the .info file. In the past there have been stable releases of project manager (for postgres) which should have been considered 'developmental' for oracle.
Collapse
Posted by Dave Bauer on
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?

Collapse
Posted by Dave Bauer on
I want to say that, having broken code for Oracle really isn't acceptable. The other side is that almost all new development occurs on PostgreSQL systems. Most Oracle installs are more stable development systems that integrate new changes less often. This leads to a situation where we have many developers without access to Oracle, and very possibly without time to learn Oracle.

I don't know what the answer is. Is there a way to say "this version of this package doesn't work on Oracle"? How can we get better support for Oracle development?

Collapse
Posted by Janine Ohmer on
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.

Collapse
Posted by Andrew Piskorski on
So, the real problem you are having Janine, is that some OpenACS developer was sloppy, and knowingly so. Maybe he had a perfectly good excuse for that, maybe not, but in either case I think it entirely appropriate to point to the specific code in question. Problems not specifically identified are problems that don't get solved.
Collapse
Posted by Dave Bauer on
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.

Collapse
Posted by Janine Ohmer on
I think there are several real issues here.

One is the status of the Oracle support, and you are correct that a text file could solve that. Assuming people bother to update it.

I think a bigger problem (for the Oracle minority, anyway) is that Oracle support is seen as dispensable by a lot of people. These are the folks not bothering to make any effort at all to keep Oracle queries in sync with Postgres ones, even where it is easy to do so. I doubt those folks are going to bother to update the file, so it becomes almost worse than a moot point. If I commit this package I'm working on with a note saying that I have brought the Oracle queries up to date, and the same person or persons continue to work in it only on the PG side, my note quickly becomes a lie and worse than not having one at all.

I don't know the solution, and I don't know if there is a solution, but at least I have (hopefully) made everyone aware that there is a problem here.

Collapse
Posted by Patrick Giagnocavo on
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.

Collapse
Posted by Andrew Piskorski on
Much of this has all been discussed repeatedly before, back in March 2005.
Collapse
Posted by Malte Sussdorff on
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.

Collapse
Posted by Dave Bauer on
One thing everyone can do is to keep an eye on packages they are interested in, and watch the CVS commits.

There are multiple ways to keep up with changes in CVS such as http://cia.navi.cx/stats/project/OpenACS which has a mailing list, and an RSS feed, or Jeff's RSS feed at http://xarg.net/tools/cvs/rss/?days=7

Collapse
Posted by Malte Sussdorff on
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)

Collapse
Posted by Nima Mazloumi on
Too bad that we cannot make use of the transparent persistence layer hibernate which has support for all important databases and sql dialekts.
Collapse
Posted by Dave Bauer on
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.

Collapse
Posted by Janine Ohmer on
IMHO as long as the project says it supports Oracle then it is unacceptable to have people deciding to just work only in Postgres and leaving a mess for someone else to clean up. Making a sincere effort and goofing up some syntax is one thing, but not making any effort at all is another.

So I would like to ask the OCT to please discuss and clarify - are we still supporting Oracle? And if so, what is the level of effort expected from programmers who aren't working in Oracle themselves? If the current situation is acceptable to you that's fine, I just want an official statement of some sort so we all have the same expectations going forward.

Do I need to submit this request somewhere else, or is this post good enough?

Collapse
Posted by Andrew Piskorski on
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...

Collapse
Posted by Torben Brosten on
Draft guidelines for sql query changes expressed here are now summarized in the wiki:

http://openacs.org/wiki/Coding%20conventions

Collapse
Posted by Malte Sussdorff on
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.
Collapse
22: Oracle 10g XE (response to 19)
Posted by Andrew Piskorski on
Malte, the Oracle 10g XE thing is interesting. If it's also easy to install that might be an excellent way to at least get Oracle on every OpenACS developer's desktop for testing purposes.
Collapse
Posted by Dave Bauer on
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 http://openacs.org/wiki/Test%20Servers so folks who commit should check that their code works on these test servers if possible.

Collapse
Posted by Andrew Piskorski on
Incidentally, for at least some users, OpenACS dropping Oracle support would be very much not trivial. To give one example, at work I have extensive dependencies on Oracle which have nothing at all to do with OpenACS. Yet, we use OpenACS to provide Intranet access and web UIs to a lot of that non-OpenACS data which lives in Oracle. Presumably that would become much more difficult if OpenACS dropped Oracle support... I don't know how common that scenario is, but surely I'm not the only such user.

Does anyone know how feasible it is for PostgreSQL and/or Oracle to query each other? In other words, if I need to do a query using some data from Oracle and some data from PostgreSQL, how could I do that (other than the obvious thing of having the client query both separately)?

Oracle has database links, but so far I've only ever used them to connect to other Oracle instances (which works pretty well). Oracle has "Transparent Gateway" support for various databases (Informix, Sybase, SQL Server, etc.), but AFAIK not for PostgreSQL.

PostgreSQL apparently added Two-Phase Commit support to the CVS Head back in June (woohoo!), and may eventually support the XA interface to it as well. PostgreSQL also has contrib/dblink, Oralink, and DBI-Link, but I'm not sure how well those really work. (Do they use the new two-phase commit stuff?)

Anyone know how well those really work, or have any further info on PostgreSQL to Oracle and Oracle to PostgreSQL dblink stuff?

Collapse
Posted by Janine Ohmer on
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.

Collapse
Posted by Torben Brosten on
Regarding the premise "Oracle and PG versions getting out of sync", I would like to point out that ecommerce and I believe some other packages had incomplete PG support while the Oracle version worked. For example, ecommerce still has at least one gift certificate feature which is not implemented in PG.

I just wanted to balance the voices that this is not specifically Oracle version not keeping up with Postgresql implementations. Both cases are true.

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