Thanks, Martha, that will be useful! David
On Tuesday, 25 February 2020 19:07:01 UTC, Martha S wrote: > > I installed DBeaver Community Edition (https://dbeaver.io/) to generate > an ERD. Here is a copy I just exported to PNG and converted to PDF. I hope > it helps. > > Martha > > On Monday, February 24, 2020 at 10:03:43 AM UTC-8, Alexei Peters wrote: >> >> 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 < >> andre...@historicengland.org.uk> 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 arches...@googlegroups.com. To unsubscribe, >>> send email to arches...@googlegroups.com. 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 arches...@googlegroups.com. >>> 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 archesproject@googlegroups.com. To unsubscribe, send email to archesproject+unsubscr...@googlegroups.com. 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 archesproject+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/archesproject/8ba76a6c-23fe-420e-8dff-b0b250cefb42%40googlegroups.com.
