Project

General

Profile

metacat / src / delete_all_by_pid.sql @ 8427

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 smReplicationPolicy WHERE guid = pid;
69
                GET DIAGNOSTICS deleted_count = ROW_COUNT;
70
                RAISE NOTICE 'Deleted % rows from smReplicationPolicy', deleted_count;
71
                
72
                DELETE FROM smReplicationStatus WHERE guid = pid;
73
                GET DIAGNOSTICS deleted_count = ROW_COUNT;
74
                RAISE NOTICE 'Deleted % rows from smReplicationStatus', 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;