-- Drop selected patrons from Evergreen tables

--    Copyright (C) 2009, Dan Scott
--    Thanks to the Robertson Library, UPEI, for funding these scripts. 

--    This program is free software: you can redistribute it and/or modify
--    it under the terms of the GNU General Public License as published by
--    the Free Software Foundation, either version 3 of the License, or
--    (at your option) any later version.
--
--    This program is distributed in the hope that it will be useful,
--    but WITHOUT ANY WARRANTY; without even the implied warranty of
--    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--    GNU General Public License for more details.
--
--    You should have received a copy of the GNU General Public License
--    along with this program.  If not, see <http://www.gnu.org/licenses/>.

-- This example only deletes users with an ID greater than 19 from the system;
-- adjust according to your needs

-- Drop constraints and rules
ALTER TABLE actor.card DROP CONSTRAINT card_usr_fkey;
ALTER TABLE actor.usr_address DROP CONSTRAINT usr_address_usr_fkey;
DROP RULE protect_user_delete ON actor.usr;

-- Disable triggers
ALTER TABLE actor.usr DISABLE TRIGGER ALL;
ALTER TABLE actor.card DISABLE TRIGGER ALL;
ALTER TABLE actor.usr_address DISABLE TRIGGER ALL;

BEGIN;
-- Finally, delete the rows
DELETE FROM actor.card WHERE usr > 19;
DELETE FROM actor.usr_address WHERE usr > 19;
DELETE FROM actor.usr WHERE id > 19;
COMMIT;

-- Recreate constraints and rules
ALTER TABLE actor.card ADD CONSTRAINT card_usr_fkey FOREIGN KEY(usr)
  REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE actor.usr_address ADD CONSTRAINT usr_address_usr_fkey FOREIGN KEY(usr)
  REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED;

CREATE RULE protect_user_delete AS ON DELETE TO actor.usr DO INSTEAD
  UPDATE actor.usr SET deleted = TRUE WHERE OLD.id = actor.usr.id;

ALTER TABLE actor.usr ENABLE TRIGGER ALL;
ALTER TABLE actor.card ENABLE TRIGGER ALL;
ALTER TABLE actor.usr_address ENABLE TRIGGER ALL;


