Forum OpenACS Development: Oracle and PG versions getting out of sync
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....
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.
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 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?
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.
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.
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.
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.
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.
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
I would like to generate a feed for 5.1, 5.2 and HEAD (all packages)
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.
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?
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...
I will add more information on porting PostgreSQL and Oracle code back and forth to that page soon.
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.
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?
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.
I just wanted to balance the voices that this is not specifically Oracle version not keeping up with Postgresql implementations. Both cases are true.