Project

General

Profile

1
/**
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
CREATE OR REPLACE FUNCTION delete_all(pid text, deleteSystemMetadata boolean default true) RETURNS VOID AS $$ 
6
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
	-- do we want to remove the SM records too?
67
	IF deleteSystemMetadata THEN
68
		DELETE FROM systemMetadataReplicationPolicy WHERE guid = pid;
69
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
70
		RAISE NOTICE 'Deleted % rows from systemMetadataReplicationPolicy', deleted_count;
71
		
72
		DELETE FROM systemMetadataReplicationStatus WHERE guid = pid;
73
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
74
		RAISE NOTICE 'Deleted % rows from systemMetadataReplicationStatus', deleted_count;
75
		
76
		DELETE FROM systemMetadata WHERE guid = pid;
77
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
78
		RAISE NOTICE 'Deleted % rows from systemMetadata', deleted_count;
79
	
80
		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
	
84
		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
	
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;
(9-9/61)