I would recommend that you use the django model to delete the resources.
Like you, we were not confident that we knew enough about the database
structure to confidently execute SQL without leaving various elements all
over the place. You also need to update the index etc.
I would write a command that allows you to pass in a list of UUIDs (.json
UUID list would be easiest) and use the django model functions to delete
the Resource objects. *The following command is TOTALLY untested* but is
what I'm thinking about...
import os
import json
from django.core.management.base import BaseCommand, CommandError
from arches.app.models.resource import Resource
class Command(BaseCommand):
def add_arguments(self, parser):
parser.add_argument('operation', nargs='?',
choices=['delete_resources',],
help='Operation Type; ' +
'\'delete_resources\'=Deletes resources from the database')
parser.add_argument('-s', '--source', action='store',
dest='source', default='',
help='Json file containing Resource instance UUIDs in an array')
def handle(self, *args, **options):
if options['operation'] == 'delete_resources':
self.delete_resources(json_source=options['source'])
def load_json(self, json_source):
"""
json should be formatted ["",""]
"""
json_data = open(json_source)
return json.load(json_data)
def delete_resources(self,json_source)
uuids = load_json(json_source=source)
resources_to_delete = Resource.objects.filter(resourceinstanceid__in=uuids)
for r in resources_to_delete:
try:
r.delete()
except Exception as e:
print(str(e))
Have a look at django management commands to see where this needs to be
placed in your django project.
Andrew
On Sunday, February 23, 2020 at 5:37:36 PM UTC, David Osborne wrote:
>
> Hi Alexei
>
> Thank you for the details. I should have asked about doing this using SQL,
> which seems the more obvious way of doing it but I had looked at the
> database structure and couldn't see how it related to the data models. Is
> there anything for developers such as an entity relationship diagram for
> the database, to explain how to query it?
> I'll try your SQL and proceed with caution (and backups!) when I get to
> deletions.
>
> Cheers
> David
>
> On Tuesday, 18 February 2020 18:29:16 UTC, Alexei Peters wrote:
>>
>> Hi David,
>> There isn't a baked in "grep"-like command to do what you want but I've
>> crafted a bit of sql that you might be able to run to retrieve resource
>> instances based on node values.
>>
>> SELECT t.tileid,
>> t.resourceinstanceid,
>> t.tiledata,
>> n.nodeid
>> FROM tiles t
>> LEFT JOIN nodes n ON t.nodegroupid = n.nodegroupid
>> WHERE
>> (t.tiledata::json -> n.nodeid::text)::text = '12'
>>
>> In this case you'd replace "12" with the value you're interested in.
>> This won't work for geometry nodes but should work for the majority of
>> other datatypes.
>>
>> As for deleting the resources once identified, the surest way to do that
>> would be through the UI. That might be impractical if you have anything
>> more than 50 records (or less depending on how much perseverance you have).
>> You could always use SQL to delete resource instances based on the uuid,
>> which should cascade delete the tiles as well. This will still leave
>> traces of the resource instances in the edit log table (which you could
>> also delete if you wish). Resource-to-resrouce relationships could also be
>> deleted the same way.
>>
>> I will caveat all this by saying that I haven't tested any of this and
>> you should most definitely back up your database before you attempt to
>> delete anything via SQL.
>>
>> If you do delete resources via SQL, then you'll need to reindex the data
>> in Elasticsearch for which there is a command
>> python manange.py es index_database
>>
>> As for the issue with reports showing the uuid of the relationship rather
>> than the text, that might be something that could be fixed the default
>> report template.
>> Hope that helps!
>>
>> Cheers,
>> Alexei
>>
>> On Tuesday, February 11, 2020 at 3:12:54 PM UTC-8, David Osborne wrote:
>>>
>>> It seems I've inadvertently duplicated some resource records by
>>> accidentally assigning them new uuids before loading from CSVs, so instead
>>> of overwriting existing records, duplicates were created.
>>>
>>> I was wondering if anyone had developed any 'grep'-like command-line
>>> utilities that perhaps returned uuids for resources where, say, a node
>>> contains a particular value? If there were also a command to delete
>>> resources given their uuid, that would help to remove the duplicate
>>> records. If nothing like this exists, is there existing Python code in the
>>> codebase which would make a good starting point? We're still on Arches
>>> 4.4.2.
>>>
>>> A related question is whether it's possible to delete
>>> resource-to-resource relationship records? They can obviously be loaded
>>> from CSV but there's nothing documented which can remove them. What happens
>>> to these links when one or more of the associated resource records are
>>> deleted?
>>>
>>> Finally, I've always been puzzled that in our resource reports, related
>>> resources are followed by the uuid for the relationship
>>> (00000000-0000-0000-0000-000000000007), rather than the CIDOC-CRM property
>>> phrase ("is-related-to"or "refers-to", I forget which), although I see that
>>> Lincoln's Arcade has the almost equally obscure URL for CRM property P67.
>>>
>>> Cheers
>>> David
>>>
>>
--
-- To post, send email to [email protected]. To unsubscribe, send
email to [email protected]. For more information,
visit https://groups.google.com/d/forum/archesproject?hl=en
---
You received this message because you are subscribed to the Google Groups
"Arches Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/archesproject/3bcf5628-d30f-47f6-a825-9031fa7f6407%40googlegroups.com.
