| 1 |
7292
|
leinfelder
|
/**
|
| 2 |
|
|
* This function removes all traces of an object identified by the given {pid}.
|
| 3 |
|
|
* NOTE: for complete removal, the object should also be deleted from the filesystem.
|
| 4 |
|
|
*/
|
| 5 |
7294
|
leinfelder
|
CREATE OR REPLACE FUNCTION delete_all(pid text, deleteSystemMetadata boolean default true) RETURNS VOID AS $$
|
| 6 |
7292
|
leinfelder
|
DECLARE
|
| 7 |
|
|
v_docid text;
|
| 8 |
|
|
v_rev integer;
|
| 9 |
|
|
v_doctype text;
|
| 10 |
|
|
deleted_count integer;
|
| 11 |
|
|
BEGIN
|
| 12 |
|
|
SELECT INTO v_docid docid FROM identifier WHERE guid = pid;
|
| 13 |
|
|
SELECT INTO v_rev rev FROM identifier WHERE guid = pid;
|
| 14 |
|
|
|
| 15 |
|
|
SELECT INTO v_doctype doctype FROM xml_documents WHERE docid = v_docid and rev = v_rev;
|
| 16 |
|
|
IF v_doctype IS NULL THEN
|
| 17 |
|
|
SELECT INTO v_doctype doctype FROM xml_revisions WHERE docid = v_docid and rev = v_rev;
|
| 18 |
|
|
END IF;
|
| 19 |
|
|
|
| 20 |
|
|
RAISE NOTICE 'Processing pid: %, which is docid: %.%, with doctype: %', pid, v_docid, v_rev, v_doctype;
|
| 21 |
|
|
|
| 22 |
|
|
IF v_docid IS NOT NULL THEN
|
| 23 |
|
|
-- current versions
|
| 24 |
|
|
DELETE FROM xml_queryresult WHERE docid = v_docid;
|
| 25 |
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 26 |
|
|
RAISE NOTICE 'Deleted % rows from xml_queryresult', deleted_count;
|
| 27 |
|
|
|
| 28 |
|
|
DELETE FROM xml_path_index WHERE docid = v_docid;
|
| 29 |
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 30 |
|
|
RAISE NOTICE 'Deleted % rows from xml_path_index', deleted_count;
|
| 31 |
|
|
|
| 32 |
|
|
DELETE FROM xml_index WHERE docid = v_docid;
|
| 33 |
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 34 |
|
|
RAISE NOTICE 'Deleted % rows from xml_index', deleted_count;
|
| 35 |
|
|
|
| 36 |
|
|
DELETE FROM xml_documents WHERE docid = v_docid AND rev = v_rev;
|
| 37 |
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 38 |
|
|
RAISE NOTICE 'Deleted % rows from xml_documents', deleted_count;
|
| 39 |
|
|
|
| 40 |
|
|
DELETE FROM xml_nodes WHERE docid = v_docid;
|
| 41 |
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 42 |
|
|
RAISE NOTICE 'Deleted % rows from xml_nodes', deleted_count;
|
| 43 |
|
|
|
| 44 |
|
|
-- revisions
|
| 45 |
|
|
DELETE FROM xml_revisions WHERE docid = v_docid AND rev = v_rev;
|
| 46 |
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 47 |
|
|
RAISE NOTICE 'Deleted % rows from xml_revisions', deleted_count;
|
| 48 |
|
|
|
| 49 |
|
|
DELETE FROM xml_nodes_revisions WHERE docid = v_docid;
|
| 50 |
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 51 |
|
|
RAISE NOTICE 'Deleted % rows from xml_nodes_revisions', deleted_count;
|
| 52 |
|
|
|
| 53 |
|
|
-- event logs
|
| 54 |
|
|
DELETE FROM access_log WHERE docid = v_docid||'.'||v_rev;
|
| 55 |
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 56 |
|
|
RAISE NOTICE 'Deleted % rows from access_log', deleted_count;
|
| 57 |
|
|
|
| 58 |
|
|
-- reminder to delete filesystem content
|
| 59 |
|
|
IF v_doctype = 'BIN' THEN
|
| 60 |
|
|
RAISE NOTICE 'Remember to delete DATA file from filesystem docid: %.%', v_docid, v_rev;
|
| 61 |
|
|
ELSE
|
| 62 |
|
|
RAISE NOTICE 'Remember to delete METADATA file from filesystem docid: %.%', v_docid, v_rev;
|
| 63 |
|
|
END IF;
|
| 64 |
|
|
END IF;
|
| 65 |
|
|
|
| 66 |
7294
|
leinfelder
|
-- do we want to remove the SM records too?
|
| 67 |
|
|
IF deleteSystemMetadata THEN
|
| 68 |
7392
|
leinfelder
|
DELETE FROM smReplicationPolicy WHERE guid = pid;
|
| 69 |
7294
|
leinfelder
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 70 |
7392
|
leinfelder
|
RAISE NOTICE 'Deleted % rows from smReplicationPolicy', deleted_count;
|
| 71 |
7294
|
leinfelder
|
|
| 72 |
7392
|
leinfelder
|
DELETE FROM smReplicationStatus WHERE guid = pid;
|
| 73 |
7294
|
leinfelder
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 74 |
7392
|
leinfelder
|
RAISE NOTICE 'Deleted % rows from smReplicationStatus', deleted_count;
|
| 75 |
7294
|
leinfelder
|
|
| 76 |
|
|
DELETE FROM systemMetadata WHERE guid = pid;
|
| 77 |
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 78 |
|
|
RAISE NOTICE 'Deleted % rows from systemMetadata', deleted_count;
|
| 79 |
7292
|
leinfelder
|
|
| 80 |
7294
|
leinfelder
|
DELETE FROM xml_access WHERE guid = pid;
|
| 81 |
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 82 |
|
|
RAISE NOTICE 'Deleted % rows from xml_access', deleted_count;
|
| 83 |
7292
|
leinfelder
|
|
| 84 |
7294
|
leinfelder
|
DELETE FROM identifier WHERE guid = pid;
|
| 85 |
|
|
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
| 86 |
|
|
RAISE NOTICE 'Deleted % rows from identifier', deleted_count;
|
| 87 |
|
|
END IF;
|
| 88 |
7292
|
leinfelder
|
|
| 89 |
|
|
END;
|
| 90 |
|
|
$$
|
| 91 |
|
|
LANGUAGE plpgsql;
|
| 92 |
|
|
--BEGIN; select delete_all('bowles.55.4'); ROLLBACK;
|
| 93 |
|
|
|
| 94 |
|
|
/**
|
| 95 |
|
|
* This part of the script collects PIDS to be deleted
|
| 96 |
|
|
* and performs the deletion on that set of pids.
|
| 97 |
|
|
* Currently we are targeting all ORNLDAAC objects.
|
| 98 |
|
|
* Update this section as needed.
|
| 99 |
|
|
*/
|
| 100 |
|
|
-- Save the set of PIDs we are deleting
|
| 101 |
|
|
CREATE TABLE pids_to_delete AS
|
| 102 |
|
|
SELECT *, text(null) as docid, null::integer as rev
|
| 103 |
|
|
FROM systemMetadata
|
| 104 |
|
|
WHERE origin_member_node like 'urn:node:ORNLDAAC';
|
| 105 |
|
|
|
| 106 |
|
|
-- update with docid/rev if we have them
|
| 107 |
|
|
UPDATE pids_to_delete ptd
|
| 108 |
|
|
SET docid = id.docid, rev = id.rev
|
| 109 |
|
|
FROM identifier id
|
| 110 |
|
|
WHERE ptd.guid = id.guid;
|
| 111 |
|
|
|
| 112 |
|
|
-- Do the delete
|
| 113 |
|
|
BEGIN;
|
| 114 |
|
|
SELECT delete_all(guid) FROM pids_to_delete;
|
| 115 |
|
|
--ROLLBACK;
|
| 116 |
|
|
COMMIT;
|
| 117 |
|
|
-- Clean up
|
| 118 |
|
|
DROP TABLE pids_to_delete;
|