Index: openacs-4/packages/acs-content-repository/acs-content-repository.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/acs-content-repository.info,v
diff -u -r1.105 -r1.106
--- openacs-4/packages/acs-content-repository/acs-content-repository.info	27 Oct 2014 16:39:06 -0000	1.105
+++ openacs-4/packages/acs-content-repository/acs-content-repository.info	2 May 2015 17:23:21 -0000	1.106
@@ -7,7 +7,7 @@
     <initial-install-p>t</initial-install-p>
     <singleton-p>t</singleton-p>
 
-    <version name="5.9.0d1" url="http://openacs.org/repository/download/apm/acs-content-repository-5.9.0d1.apm">
+    <version name="5.9.0d2" url="http://openacs.org/repository/download/apm/acs-content-repository-5.9.0d2.apm">
         <owner url="http://openacs.org">OpenACS</owner>
         <summary>The canonical repository for OpenACS content.</summary>
         <release-date>2013-09-08</release-date>
@@ -19,7 +19,7 @@
         <license>GPL</license>
         <maturity>3</maturity>
 
-        <provides url="acs-content-repository" version="5.9.0d1"/>
+        <provides url="acs-content-repository" version="5.9.0d2"/>
         <requires url="acs-kernel" version="5.9.0d1"/>
         <requires url="acs-service-contract" version="5.9.0d1"/>
         <requires url="search" version="5.9.0d1"/>
Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql,v
diff -u -r1.61 -r1.62
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql	27 Oct 2014 16:39:06 -0000	1.61
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql	2 May 2015 17:23:21 -0000	1.62
@@ -506,195 +506,186 @@
   for multi-lingual searching in Intermedia.
 ';
 
--- postgresql RI bug causes multiple failures with regards to deletion of
--- content_revisions (DanW dcwickstrom@earthlink.net)
+alter table cr_items add constraint cr_items_live_fk 
+      foreign key (live_revision) references cr_revisions(revision_id);
 
--- alter table cr_items add constraint cr_items_live_fk 
---   foreign key (live_revision) references cr_revisions(revision_id);
+alter table cr_items add constraint cr_items_latest_fk 
+     foreign key (latest_revision) references cr_revisions(revision_id);
 
--- alter table cr_items add constraint cr_items_latest_fk 
---   foreign key (latest_revision) references cr_revisions(revision_id);
 
 
 
-
 --
 -- procedure cr_revision_del_ri_tr/0
 --
-CREATE OR REPLACE FUNCTION cr_revision_del_ri_tr(
+-- CREATE OR REPLACE FUNCTION cr_revision_del_ri_tr(
+-- ) RETURNS trigger AS $$
+-- DECLARE
+--         dummy           integer;
+--         v_latest        integer;
+--         v_live          integer;
+-- BEGIN
+--         select 1 into dummy
+--         from 
+--           cr_revisions           
+--         where 
+--           revision_id = old.live_revision;
+--         
+--         if FOUND then
+--           raise EXCEPTION 'Referential Integrity: live_revision still exists: %', old.live_revision;
+--         end if;
+--         
+--         select 1 into dummy
+--         from 
+--           cr_revisions 
+--         where 
+--           revision_id = old.latest_revision;
+--         
+--         if FOUND then
+--           raise EXCEPTION 'Referential Integrity: latest_revision still exists: %', old.latest_revision;
+--         end if;
+--         
+--         return old;
+-- END;
+-- $$ LANGUAGE plpgsql;
 
-) RETURNS trigger AS $$
-DECLARE
-        dummy           integer;
-        v_latest        integer;
-        v_live          integer;
-BEGIN
-        select 1 into dummy
-        from 
-          cr_revisions           
-        where 
-          revision_id = old.live_revision;
-        
-        if FOUND then
-          raise EXCEPTION 'Referential Integrity: live_revision still exists: %', old.live_revision;
-        end if;
-        
-        select 1 into dummy
-        from 
-          cr_revisions 
-        where 
-          revision_id = old.latest_revision;
-        
-        if FOUND then
-          raise EXCEPTION 'Referential Integrity: latest_revision still exists: %', old.latest_revision;
-        end if;
-        
-        return old;
-END;
-$$ LANGUAGE plpgsql;
 
 
-
 --
 -- procedure cr_revision_ins_ri_tr/0
 --
-CREATE OR REPLACE FUNCTION cr_revision_ins_ri_tr(
+-- CREATE OR REPLACE FUNCTION cr_revision_ins_ri_tr(
+-- ) RETURNS trigger AS $$
+-- DECLARE
+--         dummy           integer;
+--         v_latest        integer;
+--         v_live          integer;
+-- BEGIN
+--         select 1 into dummy
+--         from 
+--           cr_revisions           
+--         where 
+--           revision_id = new.live_revision;
+--         
+--         if NOT FOUND and new.live_revision is NOT NULL then
+--           raise EXCEPTION 'Referential Integrity: live_revision does not exist: %', new.live_revision;
+--         end if;
+--         
+--         select 1 into dummy
+--         from 
+--           cr_revisions 
+--         where 
+--           revision_id = new.latest_revision;
+--         
+--         if NOT FOUND and new.latest_revision is NOT NULL then
+--           raise EXCEPTION 'Referential Integrity: latest_revision does not exist: %', new.latest_revision;
+--         end if;
+-- 
+--         return new;
+-- END;
+-- $$ LANGUAGE plpgsql;
 
-) RETURNS trigger AS $$
-DECLARE
-        dummy           integer;
-        v_latest        integer;
-        v_live          integer;
-BEGIN
-        select 1 into dummy
-        from 
-          cr_revisions           
-        where 
-          revision_id = new.live_revision;
-        
-        if NOT FOUND and new.live_revision is NOT NULL then
-          raise EXCEPTION 'Referential Integrity: live_revision does not exist: %', new.live_revision;
-        end if;
-        
-        select 1 into dummy
-        from 
-          cr_revisions 
-        where 
-          revision_id = new.latest_revision;
-        
-        if NOT FOUND and new.latest_revision is NOT NULL then
-          raise EXCEPTION 'Referential Integrity: latest_revision does not exist: %', new.latest_revision;
-        end if;
 
-        return new;
-END;
-$$ LANGUAGE plpgsql;
 
-
-
 --
 -- procedure cr_revision_up_ri_tr/0
 --
-CREATE OR REPLACE FUNCTION cr_revision_up_ri_tr(
+-- CREATE OR REPLACE FUNCTION cr_revision_up_ri_tr(
+-- ) RETURNS trigger AS $$
+-- DECLARE
+--         dummy           integer;
+--         v_latest        integer;
+--         v_live          integer;
+-- BEGIN
+--         select 1 into dummy
+--         from 
+--           cr_revisions           
+--         where 
+--           revision_id = new.live_revision;
+--         
+--         if NOT FOUND and new.live_revision <> old.live_revision and new.live_revision is NOT NULL then
+--           raise EXCEPTION 'Referential Integrity: live_revision does not exist: %', new.live_revision;
+--         end if;
+--         
+--         select 1 into dummy
+--         from 
+--           cr_revisions 
+--         where 
+--           revision_id = new.latest_revision;
+--         
+--         if NOT FOUND and new.latest_revision <> old.latest_revision and new.latest_revision is NOT NULL then
+--           raise EXCEPTION 'Referential Integrity: latest_revision does not exist: %', new.latest_revision;
+--         end if;
+--         
+--         return new;
+-- END;
+-- $$ LANGUAGE plpgsql;
 
-) RETURNS trigger AS $$
-DECLARE
-        dummy           integer;
-        v_latest        integer;
-        v_live          integer;
-BEGIN
-        select 1 into dummy
-        from 
-          cr_revisions           
-        where 
-          revision_id = new.live_revision;
-        
-        if NOT FOUND and new.live_revision <> old.live_revision and new.live_revision is NOT NULL then
-          raise EXCEPTION 'Referential Integrity: live_revision does not exist: %', new.live_revision;
-        end if;
-        
-        select 1 into dummy
-        from 
-          cr_revisions 
-        where 
-          revision_id = new.latest_revision;
-        
-        if NOT FOUND and new.latest_revision <> old.latest_revision and new.latest_revision is NOT NULL then
-          raise EXCEPTION 'Referential Integrity: latest_revision does not exist: %', new.latest_revision;
-        end if;
-        
-        return new;
-END;
-$$ LANGUAGE plpgsql;
 
 
-
 --
 -- procedure cr_revision_del_rev_ri_tr/0
 --
-CREATE OR REPLACE FUNCTION cr_revision_del_rev_ri_tr(
+-- CREATE OR REPLACE FUNCTION cr_revision_del_rev_ri_tr(
+-- ) RETURNS trigger AS $$
+-- DECLARE
+--         dummy           integer;
+-- BEGIN
+--         select 1 into dummy
+--         from 
+--           cr_items
+--         where 
+--           item_id = old.item_id
+--         and
+--           live_revision = old.revision_id;
+--         
+--         if FOUND then
+--           raise EXCEPTION 'Referential Integrity: attempting to delete live_revision: %', old.revision_id;
+--         end if;
+--         
+--         select 1 into dummy
+--         from 
+--           cr_items
+--         where 
+--           item_id = old.item_id
+--         and
+--           latest_revision = old.revision_id;
+--         
+--         if FOUND then
+--           raise EXCEPTION 'Referential Integrity: attempting to delete latest_revision: %', old.revision_id;
+--         end if;
+--         
+--         return old;
+-- END;
+-- $$ LANGUAGE plpgsql;
 
-) RETURNS trigger AS $$
-DECLARE
-        dummy           integer;
-BEGIN
-        select 1 into dummy
-        from 
-          cr_items
-        where 
-          item_id = old.item_id
-        and
-          live_revision = old.revision_id;
-        
-        if FOUND then
-          raise EXCEPTION 'Referential Integrity: attempting to delete live_revision: %', old.revision_id;
-        end if;
-        
-        select 1 into dummy
-        from 
-          cr_items
-        where 
-          item_id = old.item_id
-        and
-          latest_revision = old.revision_id;
-        
-        if FOUND then
-          raise EXCEPTION 'Referential Integrity: attempting to delete latest_revision: %', old.revision_id;
-        end if;
-        
-        return old;
-END;
-$$ LANGUAGE plpgsql;
 
-
 -- reimplementation of RI triggers. (DanW dcwickstrom@earthlink.net)
 
-create trigger cr_revision_del_ri_tr 
-after delete on cr_items
-for each row execute procedure cr_revision_del_ri_tr();
+-- create trigger cr_revision_del_ri_tr 
+-- after delete on cr_items
+-- for each row execute procedure cr_revision_del_ri_tr();
 
-create trigger cr_revision_up_ri_tr 
-after update on cr_items
-for each row execute procedure cr_revision_up_ri_tr();
+-- create trigger cr_revision_up_ri_tr 
+-- after update on cr_items
+-- for each row execute procedure cr_revision_up_ri_tr();
 
-create trigger cr_revision_ins_ri_tr 
-after insert on cr_items
-for each row execute procedure cr_revision_ins_ri_tr();
+-- create trigger cr_revision_ins_ri_tr 
+-- after insert on cr_items
+-- for each row execute procedure cr_revision_ins_ri_tr();
 
-create trigger cr_revision_del_rev_ri_tr 
-after delete on cr_revisions
-for each row execute procedure cr_revision_del_rev_ri_tr();
+-- create trigger cr_revision_del_rev_ri_tr 
+-- after delete on cr_revisions
+-- for each row execute procedure cr_revision_del_rev_ri_tr();
 
--- (DanW - OpenACS) Added cleanup trigger to log file items that need 
--- to be cleaned up from the CR.
 
 
-
+-- (DanW - OpenACS) Added cleanup trigger to log file items that need 
+-- to be cleaned up from the CR.
 --
 -- procedure cr_cleanup_cr_files_del_tr/0
 --
 CREATE OR REPLACE FUNCTION cr_cleanup_cr_files_del_tr(
-
 ) RETURNS trigger AS $$
 DECLARE
         
@@ -730,10 +721,6 @@
 ';
 
 
--- create global temporary table cr_content_text (
---     revision_id        integer primary key,
---     content            CLOB
--- ) on commit delete rows;
 
 create table cr_content_text (
     revision_id        integer 
Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.0d1-5.9.0d2.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.0d1-5.9.0d2.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.0d1-5.9.0d2.sql	2 May 2015 17:23:21 -0000	1.1
@@ -0,0 +1,39 @@
+-- 
+-- Ancient version of PostgreSQL (most likely before pg8) had a
+-- bug in the handling of referential integrities (sometimes referred
+-- to as the RI bug) which made extra triggers necessary. AFIKT,
+-- this bug is gone and now the triggers should be removed as well
+-- and replaced by fk constraints (sometimes already done).
+-- 
+-- 
+-- Some old installations (like openacs.org) have still the following
+-- functions although the create script do not define this triggers.
+-- It seems that an update script was missing.
+-- 
+DROP TRIGGER IF EXISTS cr_folder_del_ri_trg ON cr_items;
+DROP FUNCTION IF EXISTS cr_folder_del_ri_trg();
+
+DROP TRIGGER IF EXISTS cr_folder_ins_up_ri_trg ON cr_folders;
+DROP FUNCTION IF EXISTS cr_folder_ins_up_ri_trg();
+
+-- 
+-- Handle latest_revision and live_revision via foreign keys
+-- 
+ALTER TABLE cr_items ADD CONSTRAINT cr_items_latest_fk
+FOREIGN KEY (latest_revision) REFERENCES cr_revisions(revision_id);
+
+ALTER TABLE cr_items ADD CONSTRAINT cr_items_live_fk
+FOREIGN KEY (live_revision) REFERENCES cr_revisions(revision_id);
+
+
+DROP TRIGGER IF EXISTS cr_revision_del_ri_tr on cr_items;
+DROP FUNCTION IF EXISTS cr_revision_del_ri_tr();
+
+DROP TRIGGER IF EXISTS cr_revision_ins_ri_tr on cr_items;
+DROP FUNCTION IF EXISTS cr_revision_ins_ri_tr();
+
+DROP TRIGGER IF EXISTS cr_revision_up_ri_tr on cr_items;
+DROP FUNCTION IF EXISTS cr_revision_up_ri_tr();
+
+DROP TRIGGER IF EXISTS cr_revision_del_rev_ri_tr on cr_revisions;
+DROP FUNCTION IF EXISTS cr_revision_del_rev_ri_tr();