Hi David, I don't really have an ERD available, although someone on our team might. I'll ask around. -Alexei
Director of Web Development - Farallon Geographics, Inc. - 971.227.3173 On Mon, Feb 24, 2020 at 1:21 AM Andrew Jones < [email protected]> wrote: > 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 > <https://groups.google.com/d/msgid/archesproject/3bcf5628-d30f-47f6-a825-9031fa7f6407%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- -- 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/CA%2BZLqy8WVLUKtHUXQ6C8WoquyvYmVTxyB0AsFTyC_jXjMR%2B9cA%40mail.gmail.com.
