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_accesssubtree WHERE docid = v_docid;
37
                GET DIAGNOSTICS deleted_count = ROW_COUNT;
38
                RAISE NOTICE 'Deleted % rows from xml_accesssubtree', deleted_count;
39
	
40
		DELETE FROM xml_documents WHERE docid = v_docid AND rev = v_rev;
41
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
42
		RAISE NOTICE 'Deleted % rows from xml_documents', deleted_count;
43
		
44
		DELETE FROM xml_nodes WHERE docid = v_docid;
45
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
46
		RAISE NOTICE 'Deleted % rows from xml_nodes', deleted_count;
47
	
48
		-- revisions
49
		DELETE FROM xml_revisions WHERE docid = v_docid AND rev = v_rev;
50
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
51
		RAISE NOTICE 'Deleted % rows from xml_revisions', deleted_count;
52

    
53
		DELETE FROM xml_nodes_revisions WHERE docid = v_docid;
54
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
55
		RAISE NOTICE 'Deleted % rows from xml_nodes_revisions', deleted_count;
56

    
57
		-- event logs
58
		DELETE FROM access_log WHERE docid = v_docid||'.'||v_rev;
59
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
60
		RAISE NOTICE 'Deleted % rows from access_log', deleted_count;
61

    
62
		-- reminder to delete filesystem content
63
		IF v_doctype = 'BIN' THEN
64
			RAISE NOTICE 'Remember to delete DATA file from filesystem docid: %.%', v_docid, v_rev;
65
		ELSE
66
			RAISE NOTICE 'Remember to delete METADATA file from filesystem docid: %.%', v_docid, v_rev;
67
		END IF;
68
	END IF;
69

    
70
	-- do we want to remove the SM records too?
71
	IF deleteSystemMetadata THEN
72
		DELETE FROM smReplicationPolicy WHERE guid = pid;
73
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
74
		RAISE NOTICE 'Deleted % rows from smReplicationPolicy', deleted_count;
75
		
76
		DELETE FROM smReplicationStatus WHERE guid = pid;
77
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
78
		RAISE NOTICE 'Deleted % rows from smReplicationStatus', deleted_count;
79
		
80
		DELETE FROM systemMetadata WHERE guid = pid;
81
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
82
		RAISE NOTICE 'Deleted % rows from systemMetadata', deleted_count;
83
	
84
		DELETE FROM xml_access WHERE guid = pid;
85
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
86
		RAISE NOTICE 'Deleted % rows from xml_access', deleted_count;
87
	
88
		DELETE FROM identifier WHERE guid = pid;
89
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
90
		RAISE NOTICE 'Deleted % rows from identifier', deleted_count;
91
	END IF;
92
	
93
END;
94
$$ 
95
LANGUAGE plpgsql;
96
--BEGIN; select delete_all('bowles.55.4'); ROLLBACK;
97

    
98
/**
99
 * This part of the script collects PIDS to be deleted
100
 * and performs the deletion on that set of pids.
101
 * Currently we are targeting all ORNLDAAC objects.
102
 * Update this section as needed.
103
 */
104
-- Save the set of PIDs we are deleting
105
CREATE TABLE pids_to_delete AS
106
SELECT *, text(null) as docid, null::integer as rev
107
FROM systemMetadata
108
WHERE origin_member_node like 'urn:node:ORNLDAAC';
109

    
110
-- update with docid/rev if we have them
111
UPDATE pids_to_delete ptd
112
SET docid = id.docid, rev = id.rev
113
FROM identifier id
114
WHERE ptd.guid = id.guid;
115

    
116
-- Do the delete
117
BEGIN; 
118
SELECT delete_all(guid) FROM pids_to_delete;
119
--ROLLBACK;
120
COMMIT;
121
-- Clean up
122
DROP TABLE pids_to_delete;
(9-9/109)