On November 6th, like a good project manager, I was cleaning up the enhancement tracker by removing choices from drop downs that aren't valid anymore (we renamed our 2.0.3 release 2.1, so 2.0.4 will now become 2.2), and accidentally deleted the entire enhancement request tracker. The error message doesn't tell you what you're doing (the cranky gforge admin didn't even blame me or say I fucked up), and I'm the fourth person to do this in as many years.
Our RFE tracker is 300+ items, collected over 4+ years, and includes notes, history, status, and all kinds of other information. This was a huge deal. Our system team, unwilling to restore a backup (which would punish everyone by obliterating their work for that day), restored a backup to our QA instance and told me to retype everything from there. I asked for access to the database and said I'd write the fix myself. I'm sure they were like 'whatever...project manager'.
My solution: archive tables and triggers on the 13 tables that hold tracker information (which I reverse engineered though my local backup/restore). Not only could I recover my shit this way, but the next fuckup won't be typing for days for a bad keystroke.
Unlike me -- who has used Oracle, SQL Server, Sybase, DB2, Informix, and MySQL -- gforge uses postgres. That meant I had to teach myself how to code for postgres (which, being based on Ingres, is 80% Oracle and 20% schizophrenic milk man). I could write 5 pages of the sillyness that is postgres (look: I'm Ada-like, but everything is a function, and triggers can't contain the trigger code, they must call a function that returns type trigger). Criminy.
I finally had a good backup and local copy of the db 11-NOV, had the archive table and trigger code written and debugged 12-NOV, and finished all the functions to restore and test by 13-NOV. I tested everything today, and all my codes without a single correction (and my test cases are tight as hell).
Now we just need to "delete" the data from QA, backup/restore the archive tables to production, and use my code to turn hamburger back into cow. I'm no expect, but I think I win.
-----------------------------------------------------------------
-- Table: 1.) deleted_artifact_history
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_history;
CREATE TABLE deleted_artifact_history(
like artifact_history
)
WITH OIDS;
ALTER TABLE deleted_artifact_history OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_history TO postgres;
ALTER TABLE deleted_artifact_history ADD PRIMARY KEY (id);
-- Function: fn_deleted_artifact_history
CREATE OR REPLACE FUNCTION fn_deleted_artifact_history()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_history SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_history on artifact_history
--DROP TRIGGER deleted_artifact_history ON artifact_history;
CREATE TRIGGER deleted_artifact_history
BEFORE DELETE
ON artifact_history
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_history();
-----------------------------------------------------------------
-- Table: 2.) deleted_artifact_file
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_file;
CREATE TABLE deleted_artifact_file(
like artifact_file
)
WITH OIDS;
ALTER TABLE deleted_artifact_file OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_file TO postgres;
ALTER TABLE deleted_artifact_file ADD PRIMARY KEY (id);
-- Function: fn_deleted_artifact_file
CREATE OR REPLACE FUNCTION fn_deleted_artifact_file()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_file SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_file on artifact_file
--DROP TRIGGER deleted_artifact_file ON artifact_file;
CREATE TRIGGER deleted_artifact_file
BEFORE DELETE
ON artifact_file
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_file();
-----------------------------------------------------------------
-- Table: 3.) deleted_artifact_group_list
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_group_list;
CREATE TABLE deleted_artifact_group_list(
like artifact_group_list
)
WITH OIDS;
ALTER TABLE deleted_artifact_group_list OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_group_list TO postgres;
ALTER TABLE deleted_artifact_group_list ADD PRIMARY KEY (group_artifact_id);
-- Function: fn_deleted_artifact_group_list
CREATE OR REPLACE FUNCTION fn_deleted_artifact_group_list()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_group_list SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_group_list on artifact_group_list
--DROP TRIGGER deleted_artifact_group_list ON artifact_group_list;
CREATE TRIGGER deleted_artifact_group_list
BEFORE DELETE
ON artifact_group_list
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_group_list();
-----------------------------------------------------------------
-- Table: 4.) deleted_artifact_extra_field_data
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_extra_field_data;
CREATE TABLE deleted_artifact_extra_field_data(
like artifact_extra_field_data
)
WITH OIDS;
ALTER TABLE deleted_artifact_extra_field_data OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_extra_field_data TO postgres;
ALTER TABLE deleted_artifact_extra_field_data ADD PRIMARY KEY (data_id);
-- Function: fn_deleted_artifact_extra_field_data
CREATE OR REPLACE FUNCTION fn_deleted_artifact_extra_field_data()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_extra_field_data SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_extra_field_data on artifact_extra_field_data
--DROP TRIGGER deleted_artifact_extra_field_data ON artifact_extra_field_data;
CREATE TRIGGER deleted_artifact_extra_field_data
BEFORE DELETE
ON artifact_extra_field_data
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_extra_field_data();
-----------------------------------------------------------------
-- Table: 5.) deleted_artifact_extra_field_list
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_extra_field_list;
CREATE TABLE deleted_artifact_extra_field_list(
like artifact_extra_field_list
)
WITH OIDS;
ALTER TABLE deleted_artifact_extra_field_list OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_extra_field_list TO postgres;
ALTER TABLE deleted_artifact_extra_field_list ADD PRIMARY KEY (extra_field_id);
-- Function: fn_deleted_artifact_extra_field_list
CREATE OR REPLACE FUNCTION fn_deleted_artifact_extra_field_list()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_extra_field_list SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_extra_field_list on artifact_extra_field_list
--DROP TRIGGER deleted_artifact_extra_field_list ON artifact_extra_field_list;
CREATE TRIGGER deleted_artifact_extra_field_list
BEFORE DELETE
ON artifact_extra_field_list
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_extra_field_list();
-----------------------------------------------------------------
-- Table: 6.) deleted_artifact_perm
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_perm;
CREATE TABLE deleted_artifact_perm(
like artifact_perm
)
WITH OIDS;
ALTER TABLE deleted_artifact_perm OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_perm TO postgres;
ALTER TABLE deleted_artifact_perm ADD PRIMARY KEY (id);
-- Function: fn_deleted_artifact_perm
CREATE OR REPLACE FUNCTION fn_deleted_artifact_perm()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_perm SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_perm on artifact_perm
--DROP TRIGGER deleted_artifact_perm ON artifact_perm;
CREATE TRIGGER deleted_artifact_perm
BEFORE DELETE
ON artifact_perm
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_perm();
-----------------------------------------------------------------
-- Table: 7.) deleted_artifact_type_monitor
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_type_monitor;
CREATE TABLE deleted_artifact_type_monitor(
like artifact_type_monitor
)
WITH OIDS;
ALTER TABLE deleted_artifact_type_monitor OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_type_monitor TO postgres;
ALTER TABLE deleted_artifact_type_monitor ADD PRIMARY KEY (group_artifact_id,user_id);
-- Function: fn_deleted_artifact
CREATE OR REPLACE FUNCTION fn_deleted_artifact_type_monitor()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_type_monitor SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_type_monitor on artifact_type_monitor
--DROP TRIGGER deleted_artifact_type_monitor ON artifact_type_monitor;
CREATE TRIGGER deleted_artifact_type_monitor
BEFORE DELETE
ON artifact_type_monitor
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_type_monitor();
-----------------------------------------------------------------
-- Table: 8.) deleted_artifact_query
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_query;
CREATE TABLE deleted_artifact_query(
like artifact_query
)
WITH OIDS;
ALTER TABLE deleted_artifact_query OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_query TO postgres;
ALTER TABLE deleted_artifact_query ADD PRIMARY KEY (artifact_query_id);
-- Function: fn_deleted_artifact_query
CREATE OR REPLACE FUNCTION fn_deleted_artifact_query()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_query SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_query on artifact_query
--DROP TRIGGER deleted_artifact_query ON artifact_query;
CREATE TRIGGER deleted_artifact_query
BEFORE DELETE
ON artifact_query
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_query();
-----------------------------------------------------------------
-- Table: 9.) deleted_artifact_query_fields
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_query_fields;
CREATE TABLE deleted_artifact_query_fields(
like artifact_query_fields
)
WITH OIDS;
ALTER TABLE deleted_artifact_query_fields OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_query_fields TO postgres;
ALTER TABLE deleted_artifact_query_fields ADD PRIMARY KEY (artifact_query_id, query_field_type, query_field_id);
-- Function: fn_deleted_artifact_query_fields
CREATE OR REPLACE FUNCTION fn_deleted_artifact_query_fields()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_query_fields SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_query_fields on artifact_query_fields
--DROP TRIGGER deleted_artifact_query_fields ON artifact_query_fields;
CREATE TRIGGER deleted_artifact_query_fields
BEFORE DELETE
ON artifact_query_fields
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_query_fields();
-----------------------------------------------------------------
-- Table: 10.) deleted_artifact_monitor
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_monitor;
CREATE TABLE deleted_artifact_monitor(
like artifact_monitor
)
WITH OIDS;
ALTER TABLE deleted_artifact_monitor OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_monitor TO postgres;
ALTER TABLE deleted_artifact_monitor ADD PRIMARY KEY (artifact_id, user_id);
-- Function: fn_deleted_artifact_monitor
CREATE OR REPLACE FUNCTION fn_deleted_artifact_monitor()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_monitor SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_monitor on artifact_monitor
--DROP TRIGGER deleted_artifact_monitor ON artifact_monitor;
CREATE TRIGGER deleted_artifact_monitor
BEFORE DELETE
ON artifact_monitor
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_monitor();
-----------------------------------------------------------------
-- Table: 11.) deleted_artifact_message
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_message;
CREATE TABLE deleted_artifact_message(
like artifact_message
)
WITH OIDS;
ALTER TABLE deleted_artifact_message OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_message TO postgres;
ALTER TABLE deleted_artifact_message ADD PRIMARY KEY (id);
-- Function: fn_deleted_artifact_message
CREATE OR REPLACE FUNCTION fn_deleted_artifact_message()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_message SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_message on artifact_message
--DROP TRIGGER deleted_artifact_message ON artifact_message;
CREATE TRIGGER deleted_artifact_message
BEFORE DELETE
ON artifact_message
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_message();
-----------------------------------------------------------------
-- Table: 12.) deleted_artifact_counts_agg
-----------------------------------------------------------------
--DROP TABLE deleted_artifact_counts_agg;
CREATE TABLE deleted_artifact_counts_agg(
like artifact_counts_agg
)
WITH OIDS;
ALTER TABLE deleted_artifact_counts_agg OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact_counts_agg TO postgres;
ALTER TABLE deleted_artifact_counts_agg ADD PRIMARY KEY (group_artifact_id);
-- Function: fn_deleted_artifact_counts_agg
CREATE OR REPLACE FUNCTION fn_deleted_artifact_counts_agg()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact_counts_agg SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact_counts_agg on artifact_counts_agg
--DROP TRIGGER deleted_artifact_counts_agg ON artifact_counts_agg;
CREATE TRIGGER deleted_artifact_counts_agg
BEFORE DELETE
ON artifact_counts_agg
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact_counts_agg();
-----------------------------------------------------------------
-- Table: 13.) deleted_artifact
-----------------------------------------------------------------
--DROP TABLE deleted_artifact;
CREATE TABLE deleted_artifact(
like artifact
)
WITH OIDS;
ALTER TABLE deleted_artifact OWNER TO postgres;
GRANT ALL ON TABLE deleted_artifact TO postgres;
ALTER TABLE deleted_artifact ADD PRIMARY KEY (artifact_id);
-- Trigger: deleted_artifact_update_last_modified_date on deleted_artifact
--DROP TRIGGER deleted_artifact_update_last_modified_date ON deleted_artifact;
CREATE TRIGGER deleted_artifact_update_last_modified_date
BEFORE INSERT OR UPDATE
ON deleted_artifact
FOR EACH ROW
EXECUTE PROCEDURE update_last_modified_date();
-- Function: fn_deleted_artifact
CREATE OR REPLACE FUNCTION fn_deleted_artifact()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO deleted_artifact SELECT OLD.*;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
-- Trigger: deleted_artifact on artifact
--DROP TRIGGER deleted_artifact ON artifact;
CREATE TRIGGER deleted_artifact
BEFORE DELETE
ON artifact
FOR EACH ROW
EXECUTE PROCEDURE fn_deleted_artifact();
and..
-----------------------------------------------------------------
-- Test Case: I. Archive Deleted Records
-- Precondition 1) Deleted tables have 0 records
-- 2) Artifact tables have N records
-- Postcondition 1) Deleted tables have N records
-- 2) Artifact tables reduced by N records
-- Execution 1) In isolation (from SQL prompt)
-- 2) Through gforge GUI
-----------------------------------------------------------------
-----------------------------------------------------------------
-- Test Case: II. Restore Deleted Records
-- Precondition: 1) Deleted tables have X records
-- 2) Artifact tables have Y records
-- Postcondition 1) Deleted tables have 0 records
-- 2) Artifact tables = X + Y records
-- Execution 1) In isolation (from SQL prompt)
-- 2) Through gforge GUI
-----------------------------------------------------------------
CREATE TABLE testRecordCount (
run_type varchar,
artifact integer NOT NULL,
d_artifact integer NOT NULL,
a_history integer NOT NULL,
d_a_history integer NOT NULL,
a_file integer NOT NULL,
d_a_file integer NOT NULL,
a_extra_field_data integer NOT NULL,
d_a_extra_field_data integer NOT NULL,
a_group_list integer NOT NULL,
d_a_group_list integer NOT NULL,
a_perm integer NOT NULL,
d_a_perm integer NOT NULL,
a_type_monitor integer NOT NULL,
d_a_type_monitor integer NOT NULL,
a_query integer NOT NULL,
d_a_query integer NOT NULL,
a_query_fields integer NOT NULL,
d_a_query_fields integer NOT NULL,
a_monitor integer NOT NULL,
d_a_monitor integer NOT NULL,
a_message integer NOT NULL,
d_a_message integer NOT NULL,
a_counts_agg integer NOT NULL,
d_a_counts_agg integer NOT NULL,
run_date date NOT NULL DEFAULT now()
);
CREATE OR REPLACE FUNCTION populateTestRecordCount (notes varchar, tracker_id int)
RETURNS VOID AS $$
DECLARE
v_artifact int;
v_d_artifact int;
v_a_history int;
v_d_a_history int;
v_a_file int;
v_d_a_file int;
v_a_extra_field_data int;
v_d_a_extra_field_data int;
v_a_group_list int;
v_d_a_group_list int;
v_a_perm int;
v_d_a_perm int;
v_a_type_monitor int;
v_d_a_type_monitor int;
v_a_query int;
v_d_a_query int;
v_a_query_fields int;
v_d_a_query_fields int;
v_a_monitor int;
v_d_a_monitor int;
v_a_message int;
v_d_a_message int;
v_a_counts_agg int;
v_d_a_counts_agg int;
BEGIN
-----------------------------------------------------------------
-- Artifact Tables
-----------------------------------------------------------------
select into v_artifact count(*) from artifact; --where group_artifact_id=tracker_id;
select into v_a_history count(*) from artifact_history;
--where artifact_id in (select artifact_id from artifact where group_artifact_id=tracker_id);
select into v_a_file count(*) from artifact_file;
--where artifact_id in (select artifact_id from artifact where group_artifact_id=tracker_id);
select into v_a_extra_field_data count(*) from artifact_extra_field_data;
--where artifact_id in (select artifact_id from artifact where group_artifact_id=tracker_id);
select into v_a_group_list count(*) from artifact_group_list; --where group_artifact_id=tracker_id;
select into v_a_perm count(*) from artifact_perm; --where group_artifact_id=tracker_id;
select into v_a_type_monitor count(*) from artifact_type_monitor; --where group_artifact_id=tracker_id;
select into v_a_query count(*) from artifact_query; --where group_artifact_id=tracker_id;
select into v_a_query_fields count(*) from artifact_query_fields;
--where artifact_query_id in (select artifact_query_id from artifact_query where group_artifact_id=tracker_id);
select into v_a_monitor count (*) from artifact_monitor;
select into v_a_message count (*) from artifact_message;
select into v_a_counts_agg count (*) from artifact_counts_agg;
-----------------------------------------------------------------
-- Deleted Tables
-----------------------------------------------------------------
select into v_d_artifact count(*) from deleted_artifact where group_artifact_id=tracker_id;
select into v_d_a_history count(*) from deleted_artifact_history where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
select into v_d_a_file count(*) from deleted_artifact_file where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
select into v_d_a_extra_field_data count(*) from deleted_artifact_extra_field_data where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
select into v_d_a_group_list count(*) from deleted_artifact_group_list where group_artifact_id=tracker_id;
select into v_d_a_perm count(*) from deleted_artifact_perm where group_artifact_id=tracker_id;
select into v_d_a_type_monitor count(*) from deleted_artifact_type_monitor where group_artifact_id=tracker_id;
select into v_d_a_query count(*) from deleted_artifact_query where group_artifact_id=tracker_id;
select into v_d_a_query_fields count(*) from deleted_artifact_query_fields where artifact_query_id in (
select artifact_query_id from deleted_artifact_query where group_artifact_id=tracker_id
);
select into v_d_a_monitor count (*) from deleted_artifact_monitor;
select into v_d_a_message count (*) from deleted_artifact_message;
select into v_d_a_counts_agg count (*) from deleted_artifact_counts_agg;
-----------------------------------------------------------------
-- Populate Test Table with Counts
-----------------------------------------------------------------
INSERT INTO testRecordCount (
run_type, artifact, d_artifact, a_history, d_a_history,
a_file, d_a_file, a_extra_field_data, d_a_extra_field_data,
a_group_list, d_a_group_list, a_perm, d_a_perm,
a_type_monitor, d_a_type_monitor, a_query, d_a_query,
a_query_fields, d_a_query_fields, a_monitor, d_a_monitor,
a_message, d_a_message, a_counts_agg, d_a_counts_agg
) VALUES (
notes, v_artifact, v_d_artifact, v_a_history, v_d_a_history,
v_a_file, v_d_a_file, v_a_extra_field_data, v_d_a_extra_field_data,
v_a_group_list, v_d_a_group_list, v_a_perm, v_d_a_perm,
v_a_type_monitor, v_d_a_type_monitor, v_a_query, v_d_a_query,
v_a_query_fields, v_d_a_query_fields, v_a_monitor, v_d_a_monitor,
v_a_message, v_d_a_message, v_a_counts_agg, v_d_a_counts_agg
);
END;
$$ LANGUAGE 'plpgsql';
-----------------------------------------------------------------
-- Test Deleting Records
-----------------------------------------------------------------
CREATE OR REPLACE FUNCTION testRecordDelete (tracker_id int)
RETURNS VOID AS $$
BEGIN
-----------------------------------------------------------------
-- artifact_history, file, and extra_field_data have fk's to artifact
-----------------------------------------------------------------
delete from artifact_history where artifact_id in (
select artifact_id from artifact where group_artifact_id=tracker_id
);
delete from artifact_file where artifact_id in (
select artifact_id from artifact where group_artifact_id=tracker_id
);
delete from artifact_extra_field_data where artifact_id in (
select artifact_id from artifact where group_artifact_id=tracker_id
);
delete from artifact_monitor where artifact_id in (
select artifact_id from artifact where group_artifact_id=tracker_id
);
delete from artifact_message where artifact_id in (
select artifact_id from artifact where group_artifact_id=tracker_id
);
delete from artifact where group_artifact_id=tracker_id;
-----------------------------------------------------------------
-- group_list, perm, type_monitor, counts_agg are straight forward
-----------------------------------------------------------------
delete from artifact_perm where group_artifact_id=tracker_id;
delete from artifact_group_list where group_artifact_id=tracker_id;
delete from artifact_type_monitor where group_artifact_id=tracker_id;
delete from artifact_counts_agg where group_artifact_id=tracker_id;
-----------------------------------------------------------------
-- query_history needs query to turn hamburger back into cow
-----------------------------------------------------------------
delete from artifact_query_fields where artifact_query_id in (
select artifact_query_id from artifact_query where group_artifact_id=tracker_id
);
delete from artifact_query where group_artifact_id=tracker_id;
END;
$$ LANGUAGE 'plpgsql';
-----------------------------------------------------------------
-- Restore Records
-----------------------------------------------------------------
CREATE OR REPLACE FUNCTION RecordRestore (tracker_id int)
RETURNS VOID AS $$
BEGIN
-----------------------------------------------------------------
-- artifact_group_list (disable trigger before restore)
-----------------------------------------------------------------
alter table artifact_group_list disable trigger artifactgrouplist_insert_trig;
insert into artifact_group_list select * from deleted_artifact_group_list where group_artifact_id=tracker_id;
alter table artifact_group_list enable trigger artifactgrouplist_insert_trig;
delete from deleted_artifact_group_list where group_artifact_id=tracker_id;
-----------------------------------------------------------------
-- artifact_query & artifact_query_fields
-----------------------------------------------------------------
insert into artifact_query select * from deleted_artifact_query where group_artifact_id=tracker_id;
insert into artifact_query_fields select * from deleted_artifact_query_fields where artifact_query_id in (
select artifact_query_id from deleted_artifact_query where group_artifact_id=tracker_id
);
delete from deleted_artifact_query_fields where artifact_query_id in (
select artifact_query_id from deleted_artifact_query where group_artifact_id=tracker_id
);
delete from deleted_artifact_query where group_artifact_id=tracker_id;
-----------------------------------------------------------------
-- artifact_perm & artifact_type_monitor
-----------------------------------------------------------------
insert into artifact_counts_agg select * from deleted_artifact_counts_agg where group_artifact_id=tracker_id;
insert into artifact_perm select * from deleted_artifact_perm where group_artifact_id=tracker_id;
insert into artifact_type_monitor select * from deleted_artifact_type_monitor where group_artifact_id=tracker_id;
delete from deleted_artifact_counts_agg where group_artifact_id=tracker_id;
delete from deleted_artifact_perm where group_artifact_id=tracker_id;
delete from deleted_artifact_type_monitor where group_artifact_id=tracker_id;
-----------------------------------------------------------------
-- fish desired records out of deleted_artifact, restore, clean up
-----------------------------------------------------------------
insert into artifact select * from deleted_artifact where group_artifact_id=tracker_id;
insert into artifact_history select * from deleted_artifact_history where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
insert into artifact_file select * from deleted_artifact_file where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
insert into artifact_extra_field_data select * from deleted_artifact_extra_field_data where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
insert into artifact_monitor select * from deleted_artifact_monitor where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
insert into artifact_message select * from deleted_artifact_message where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact_message where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact_monitor where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact_extra_field_data where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact_file where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact_history where artifact_id in (
select artifact_id from deleted_artifact where group_artifact_id=tracker_id
);
delete from deleted_artifact where group_artifact_id=tracker_id;
END;
$$ LANGUAGE 'plpgsql';
1 comment:
j-e-s-u-s.
Post a Comment