Fixing returned submissions

Fixing permissions and reindexing returned submissions

Overview

In some cases, returning submissions to the submitter results in the submitter unable to see the submission in their workspace (My Submissions). Until the underlying code is found and fixed, these can be corrected by generating the appropriate permissions in the resourcepolicy table, and rebuilding the solr index for these items.

Steps

In psql console on the server

1. Get the data package's item_id:

select item_id from metadatavalue where metadata_field_id = 17 and text_value = '<doi>';

1. Confirm the data package should appear in the user's workspace. The following SQL should return one row

select * from workspaceitem where item_id = <package item id>;

1. Get the submitter's eperson_id, and ids of all items, bundles, and bitstreams contained within the package. The below SQL scripts return them as comma-separated

select submitter_id from item where item_id =<package item id>;
select string_agg(part.item_id::VARCHAR, ',') as item_ids from metadatavalue part, metadatavalue package where part.metadata_field_id in (17,42) and package.metadata_field_id = 17 and part.text_value = package.text_value and package.item_id = <package item id>;
select string_agg(bundle_id::VARCHAR, ',') as bundle_ids from item2bundle where item_id in (select part.item_id from metadatavalue part, metadatavalue package where part.metadata_field_id in (17,42) and package.metadata_field_id = 17 and part.text_value = package.text_value and package.item_id = <package item id>);
select string_agg(bitstream_id::VARCHAR, ',') as bitstream_ids from bundle2bitstream where bundle_id in (select bundle_id from item2bundle where item_id in (select part.item_id from metadatavalue part, metadatavalue package where part.metadata_field_id in (17,42) and package.metadata_field_id = 17 and part.text_value = package.text_value and package.item_id = <package item id>));

On your local machine

Run the below ruby script, inputting the comma-separated ids from above. The script generates the database statements and reindex command:

#!/usr/bin/env ruby
BITSTREAM = 0
BUNDLE = 1
ITEM = 2

puts "EPERSON_ID: "
EPERSON_ID = gets
EPERSON_ID.chomp

puts "item_ids (comma-delim string):"
input = gets
input.chomp
item_ids = input.split(",")

puts "bundle_ids (comma-delim string):"
input = gets
input.chomp
bundle_ids = input.split(",")

puts "bitstream_ids (comma-delim string):"
input = gets
input.chomp
bitstream_ids = input.split(",")

resource_id_map = {
ITEM => item_ids,
BUNDLE => bundle_ids,
BITSTREAM => bitstream_ids
}

action_ids = [0,1,3,4]

def get_delete_line(resource_type_id, resource_id)
"DELETE FROM resourcepolicy where resource_type_id = #{resource_type_id} AND resource_id = #{resource_id};"
end

def get_insert_line(resource_type_id, resource_id, action_id, eperson_id)
"INSERT INTO resourcepolicy (policy_id, resource_type_id, resource_id, action_id, eperson_id) values (getnextid('resourcepolicy'), #{resource_type_id}, #{resource_id}, #{action_id}, #{eperson_id});"
end

resource_id_map.keys.each do |resource_type_id|
resource_ids = resource_id_map[resource_type_id]
resource_ids.each do |resource_id|
puts get_delete_line(resource_type_id, resource_id)
action_ids.each do |action_id|
puts get_insert_line(resource_type_id, resource_id, action_id, EPERSON_ID)
end
end
end

puts "\n"
item_ids.each{|item_id| puts "/opt/dryad/bin/dspace update-discovery-index -i #{item_id}"}

1. Run the ruby script. It produces SQL statements and commands to run:

DELETE FROM resourcepolicy where resource_type_id = 2 AND resource_id = xxx;
INSERT INTO resourcepolicy (policy_id, resource_type_id, resource_id, action_id, eperson_id) values (getnextid('resourcepolicy'), 2, xxx, 0, yyy);
INSERT INTO resourcepolicy (policy_id, resource_type_id, resource_id, action_id, eperson_id) values (getnextid('resourcepolicy'), 2, xxx, 1, yyy);
...
/opt/dryad/bin/dspace update-discovery-index -i xxx
/opt/dryad/bin/dspace update-discovery-index -i xxx

In psql console on the server

1. Paste the SQL statements into the postgres client on the server

In shell console on the server

1. Paste the update-discovery-index commands into the shell on the server.