-- 
-- packages/notifications/sql/postgresql/upgrade/upgrade-5.1.0d2-5.1.0d3.sql
-- 
-- @author Stan Kaufman (skaufman@epimetrics.com)
-- @creation-date 2004-07-08
-- @cvs-id $Id: upgrade-5.1.0d2-5.1.0d3.sql,v 1.3 2013/03/30 18:50:28 gustafn Exp $
--
-- based on Peter's upgrade script:
-- Add on delete cascade foreign key constraints
-- see Bug http://openacs.org/bugtracker/openacs/bug?filter%2estatus=resolved&filter%2eactionby=6815&bug%5fnumber=260
-- @author Peter Marklund



-- added
select define_function_args('safe_drop_cosntraint','table_name,constraint_name');

--
-- procedure safe_drop_cosntraint/2
--
CREATE OR REPLACE FUNCTION safe_drop_cosntraint(
   p_table_name name,
   p_constraint_name name
) RETURNS integer AS $$
DECLARE
    v_constraint_p        integer;
BEGIN
    select count(*)
    into   v_constraint_p
    from   pg_constraint con, pg_class c
    where  con.conname = p_constraint_name
    and    c.oid = con.conrelid
    and    c.relname = p_table_name;

    if v_constraint_p > 0 then
        execute 'alter table ' || p_table_name || ' drop constraint ' || p_constraint_name;
    end if;

    return 0;
END;
$$ LANGUAGE plpgsql;

-- Add on delete cascade to notifications.notif_notif_id_fk foreign key constraint

select safe_drop_cosntraint('notifications', 'notif_notif_id_fk');

alter table notifications add constraint notif_notif_id_fk
                              foreign key (object_id)
                              references acs_objects (object_id)
                              on delete cascade;

-- Add on delete cascade to notification_requests.notif_request_id_fk foreign key constraint

select safe_drop_cosntraint('notification_requests', 'notif_request_id_fk');

alter table notification_requests add constraint notif_request_id_fk
                              foreign key (object_id)
                              references acs_objects (object_id)
                              on delete cascade;

drop function safe_drop_cosntraint(name, name);