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 < >> [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/8ba76a6c-23fe-420e-8dff-b0b250cefb42%40googlegroups.com.
