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 
