Metacat

/**

 * This function removes all traces of an object identified by the given {pid}.

 * NOTE: for complete removal, the object should also be deleted from the filesystem.

 */

CREATE OR REPLACE FUNCTION delete_all(pid text, deleteSystemMetadata boolean default true) RETURNS VOID AS $$ 

DECLARE 

	v_docid text; 

	v_rev integer; 

	v_doctype text;

	deleted_count integer;

BEGIN 

	SELECT INTO v_docid docid FROM identifier WHERE guid = pid;

	SELECT INTO v_rev rev FROM identifier WHERE guid = pid;

	SELECT INTO v_doctype doctype FROM xml_documents WHERE docid = v_docid and rev = v_rev;

	IF v_doctype IS NULL THEN

		SELECT INTO v_doctype doctype FROM xml_revisions WHERE docid = v_docid and rev = v_rev;

	END IF;

	RAISE NOTICE 'Processing pid: %, which is docid: %.%, with doctype: %', pid, v_docid, v_rev, v_doctype;

	IF v_docid IS NOT NULL THEN

		-- current versions

		DELETE FROM xml_queryresult WHERE docid = v_docid;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from xml_queryresult', deleted_count;

		DELETE FROM xml_path_index WHERE docid = v_docid;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from xml_path_index', deleted_count;

		DELETE FROM xml_index WHERE docid = v_docid;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from xml_index', deleted_count;

		DELETE FROM xml_documents WHERE docid = v_docid AND rev = v_rev;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from xml_documents', deleted_count;

		DELETE FROM xml_nodes WHERE docid = v_docid;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from xml_nodes', deleted_count;

		-- revisions

		DELETE FROM xml_revisions WHERE docid = v_docid AND rev = v_rev;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from xml_revisions', deleted_count;

		DELETE FROM xml_nodes_revisions WHERE docid = v_docid;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from xml_nodes_revisions', deleted_count;

		-- event logs

		DELETE FROM access_log WHERE docid = v_docid||'.'||v_rev;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from access_log', deleted_count;

		-- reminder to delete filesystem content

		IF v_doctype = 'BIN' THEN

			RAISE NOTICE 'Remember to delete DATA file from filesystem docid: %.%', v_docid, v_rev;

		ELSE

			RAISE NOTICE 'Remember to delete METADATA file from filesystem docid: %.%', v_docid, v_rev;

		END IF;

	END IF;

	-- do we want to remove the SM records too?

	IF deleteSystemMetadata THEN

		DELETE FROM systemMetadataReplicationPolicy WHERE guid = pid;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from systemMetadataReplicationPolicy', deleted_count;

		DELETE FROM systemMetadataReplicationStatus WHERE guid = pid;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from systemMetadataReplicationStatus', deleted_count;

		DELETE FROM systemMetadata WHERE guid = pid;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from systemMetadata', deleted_count;

		DELETE FROM xml_access WHERE guid = pid;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from xml_access', deleted_count;

		DELETE FROM identifier WHERE guid = pid;

		GET DIAGNOSTICS deleted_count = ROW_COUNT;

		RAISE NOTICE 'Deleted % rows from identifier', deleted_count;

	END IF;

END;

$$ 

LANGUAGE plpgsql;

--BEGIN; select delete_all('bowles.55.4'); ROLLBACK;

/**

 * This part of the script collects PIDS to be deleted

 * and performs the deletion on that set of pids.

 * Currently we are targeting all ORNLDAAC objects.

 * Update this section as needed.

 */

-- Save the set of PIDs we are deleting

CREATE TABLE pids_to_delete AS

SELECT *, text(null) as docid, null::integer as rev

FROM systemMetadata

WHERE origin_member_node like 'urn:node:ORNLDAAC';

-- update with docid/rev if we have them

UPDATE pids_to_delete ptd

SET docid = id.docid, rev = id.rev

FROM identifier id

WHERE ptd.guid = id.guid;

-- Do the delete

BEGIN; 

SELECT delete_all(guid) FROM pids_to_delete;

--ROLLBACK;

COMMIT;

-- Clean up

DROP TABLE pids_to_delete;