Forum OpenACS Q&A: ACS > OpenACS Migration

Posted by Kenny Chan on
Hi All,

I was a aD bootcamper so my knowledge on the ACS is based on Oracle.
Now that I want to start using OpenACS, I am wondering what is the
difference in programming with the classic ACS and the OpenACS. My
understanding is that programming in OpenACS only needs some minor
change in the SQL statements being used. Correct me if I am wrong.
Also, Regarding the SQL syntaxes, is it sufficient enough to read just
the Oracle SQL to PostgreSQL Porting Guide?

Thanks in advance for any input / guidelines.

Kenny Chan

Posted by Roberto Mello on
Reading the porting guide will help a lot, especially if you are just programming pages and things like that.

PostgreSQL is much simpler to install and administer than Oracle. Read the PG Install Guide (which is missing a statement on the installation part that I need to correct before 3.2.4 is released) because it has tips on maintaning, backing up and the use of "VACUUM ANALYZE" which is essential to performance.

I plan on expanding the porting guide with information on how to port PL/SQL Code to PL/PgSQL and PL/Tcl sometime next month or maybe end of this month.

Posted by Alex Sokoloff on
Just to expand a little on Roberto's response: The difficulty of porting depends (of course!) on which Oracle features are used in the code that you're porting. As mentioned in the porting guide, there is no completely straightforward way to port Oracle "connect by" statements, but the workarounds are not too painful.

I've been working on porting the bookmarks module, which uses a combination of row-level and statement-level triggers to do a second set of changes to a table once the first changes are complete. (I can't remember the correct term for this, but it has to do with the fact that you can't do certain things to a "mutating table".) Since postgres doesn't have statement-level triggers, nor, I think, a way to store an array of values for a later procedure to process, you can't do some of the things that you can with Oracle stored procedures. The workaround for the problem at hand seems to be to move some of the logic out of stored procedures and into the tcl program that calls the update. (If I'm wrong here, I'd love to know.) This is not beautiful, since you may be stuck writing the same workaround in many places.

Also, postgres seems to have bugs or idiosyncracies that make it finicky about syntax. I had some problems concatenating strings: It won't concatenate a null value to a string in a stored procedure, but it will in a regular sql statement. It wants the two strings to be cast as the same type. Syntax for stored procedures took a certain amount of trial and error. This will become easier, I suppose, when more documentation with detailed syntax examples appears.

All in all, it's a lot easier than figuring out how to install and administer a production Oracle server.  >:0


Posted by Kenny Chan on
Thank you very much for the responses.
I will try to actually start using OpenACS and see what pitfalls are there waiting for me.

Roberto: Can't wait to see the new / updated porting guide :>

Good luck to all