Forum OpenACS Development: Use CREATE or REPLACE in your upgrade scripts

1: Use CREATE or REPLACE in your upgrade scripts

Posted by Dave Bauer on 10/04/02 07:31 PM

When writing your upgrade scripts for postgresql, go ahead and use
create or replace. OpenACS 4.6 requires Postgresql 7.2 which supports
this wonderful feature.

2: Response to Use CREATE or REPLACE in your upgrade scripts (response to 1)

Posted by Don Baccus on 10/05/02 02:39 AM

Good point, and we'll be wanting to switch the datamodel files to do the same in OpenACS 4.7.

3: Response to Use CREATE or REPLACE in your upgrade scripts (response to 1)

Posted by Roberto Mello on 10/07/02 02:01 AM

I'm hitting errors in some of the upgrade scripts while trying to upgrade from 4.5 to 4.6b.

The one I'm at right now is at acs-kernel, but I'm not sure why it's (the upgrade script) doing what it's doing.

In http://dev.openacs.org:8000/cvs/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.5-4.5.1.sql?rev=1.3&content-type=text/x-cvsweb-markup you can see the file in question.

It loads ../site-node-object-map-create.sql but why? (Vinod, you there?) PG barfs on that because the table is already created and so are all the functions. So why is that in the upgrade script at all?

Next, the functions in the upgrade file are not CREATE OR REPLACE so PG barfs on all of those.

4: Response to Use CREATE or REPLACE in your upgrade scripts (response to 1)

Posted by Vinod Kurup on 10/07/02 03:30 AM

Yup Roberto - I'm here 😊

the acs-kernel upgrade script for 4.5 -> 4.5.1 loads site-node-object-map-create.sql because that file and all its tables weren't present in acs-kernel 4.5. (i.e. the official 4.5 tarball). They made it into CVS sometime after the 4.5 release, so they're in the upgrade script. It sounds like you're already running acs-kernel 4.5.1. When did you install 4.5 (and did you do it from the tarball or CVS)? If you installed from CVS, then you probably installed 4.5 after the site-node map file had been added, thus you don't need the upgrade script. Also - none of the functions use CREATE or REPLACE because we hadn't made the decision to use PG7.2 when I wrote that script.

5: Response to Use CREATE or REPLACE in your upgrade scripts (response to 1)

Posted by Roberto Mello on 10/07/02 05:51 AM

Vinod,

Thanks for the prompt reply. I noticed the reason for your script shortly after my post.

The funny thing is that I had all the tables/functions in site-node-map-create (hope the name is right), but I had been running 4.5.