Forum OpenACS Q&A: ns_ora to ns_pg migration

Request notifications

Posted by Jean-Fabrice RABAUTE on

I am trying to migrate a version old acs site (version 3.2) from oracle to postgresql.
Most of the work is ok (db migration, sql queries, etc...).

My only problem so far is to migrate all the ns_ora "blob_get_file", "write_blob", "blob_dml_file" ... to ns_pg related functions.

I naively tried ns_pb blob_get_file etc... but found that those functions cannot be used the same way the ns_ora functions are used.

Is there a way to replace the ns_ora function by ns_pg function ? My only need is to read/write blob date to/from the db.
It looks like the ns_pb is only working on binary data with table columns called "lob", and I have a lot of tables where the blob column is not called lob (users for instance !).

Can someone give me some tips, if possible, on how to migrate from ns_ora to ns_pg ?



Posted by Andrew Piskorski on
OpenACS has included database-independent LOB functions for a long time. e.g, db_blob_get_file, db_write_blob, and db_write_clob, most of which call db_exec_lob underneath to do the Oracle or PostgreSQL specific stuff. You should port your old Oracle specific code to use those, not the PostgreSQL specific APIs.

There's also no particular reason that most of this underlying functionality should be unique to each AOLserver database driver. It would be better to standardize the APIs for LOBs, bind variables, etc., and add that extended functionality to the ns_db API. That's been talked about on the AOLserver list, but AFAIK no one has actually done it yet. But for your purposes that doesn't matter, just use the OpenACS functions.

Posted by Andrew Piskorski on
Hm, I assumed above that you are upgrading your site from ACS 3.2 to OpenACS 5.x. That's what I'd probably recommend, even though it's likely to be a lot of work.

Are you in fact upgrading to OpenACS? Or are you staying on your (forked, presumably) ACS 3.2 codebase and converting it to use PostgreSQL? If the latter, well, that's basically what OpenACS 3.2 was, you should look at it. The more modern OpenACS 5.x db_* api is also still relevant, even if only as a source of examples.

Posted by Jean-Fabrice RABAUTE on

thank you for your feedback.
I am not upgrading to 5.x version, just trying to replace oracle by postgresql.

I checked the db_blob_get_file, db_write_blob, etc functions trying to get the basic calls to emulate them without success :-(
I will try to go deeper in the code to find what I need, or use them directly (but I like to understand what I am doing...).

Can you confirm that the openacs 5.x functions "db_blob_get_file, db_write_blob" do not need changes on the db (column name "lob" and triggers etc).
Because I read this page :

(search for "You must use the name")

And that's why I am a littke bit confused...

Thank you very much for your help.