diff options
| -rw-r--r-- | .gitignore | 4 | ||||
| -rw-r--r-- | archaeological_context_records/models.py | 114 | ||||
| -rw-r--r-- | archaeological_context_records/tests.py | 64 | ||||
| -rw-r--r-- | docs/fr/source/annexe-tech-5-manage-commands.rst | 19 | ||||
| -rw-r--r-- | ishtar_common/management/commands/relations_update_cache_tables.py | 83 | ||||
| -rw-r--r-- | ishtar_common/migrations/0215_ishtarsiteprofile_parent_relations_engine.py | 20 | ||||
| -rw-r--r-- | ishtar_common/models.py | 91 |
7 files changed, 390 insertions, 5 deletions
diff --git a/.gitignore b/.gitignore index a9368a7a7..904b731d7 100644 --- a/.gitignore +++ b/.gitignore @@ -40,3 +40,7 @@ drassm_app .code-workspace .vscode/* example_project/local_settings.py.old +scripts +.project +.pydevproject +.settings diff --git a/archaeological_context_records/models.py b/archaeological_context_records/models.py index d62dde4be..96ba16935 100644 --- a/archaeological_context_records/models.py +++ b/archaeological_context_records/models.py @@ -35,6 +35,7 @@ from ishtar_common.utils import ( cached_label_changed, m2m_historization_changed, post_save_geo, + task ) from ishtar_common.models import ( @@ -61,6 +62,7 @@ from ishtar_common.models import ( DocumentItem, MainItem, QuickAction, + RelationsViews ) from ishtar_common.models_common import HistoricalRecords from archaeological_operations.models import ( @@ -1282,7 +1284,7 @@ class RecordRelationView(models.Model): return '{} "{}"'.format(self.relation_type, self.right_record) -class ContextRecordTree(models.Model): +class ContextRecordTree(RelationsViews): CREATE_SQL = """ CREATE VIEW cr_parent_relation_id AS SELECT id @@ -1354,6 +1356,19 @@ class ContextRecordTree(models.Model): DROP VIEW IF EXISTS context_records_tree; DROP VIEW IF EXISTS cr_parent_relation_id; """ + + CREATE_TABLE_SQL = """ + CREATE TABLE {table} ( + key varchar(100) PRIMARY KEY, + cr_id integer NOT NULL, + cr_parent_id integer NOT NULL, + CONSTRAINT fk1_{table} FOREIGN KEY(cr_id) + REFERENCES {fk_table}(id), + CONSTRAINT fk2_{table} FOREIGN KEY(cr_parent_id) + REFERENCES {fk_table}(id) + );""".format(table="context_records_tree", + fk_table="archaeological_context_records_contextrecord") + key = models.TextField(primary_key=True) cr = models.ForeignKey( "archaeological_context_records.ContextRecord", @@ -1369,3 +1384,100 @@ class ContextRecordTree(models.Model): class Meta: managed = False db_table = "context_records_tree" + + @classmethod + def _save_tree(cls, tree): + keys = [] + print("tree", tree) + for idx, parent_id in enumerate(tree[:-1]): + for child_id in tree[idx:]: + if child_id != parent_id: + cls.objects.get_or_create( + key=f"{child_id}_{parent_id}", + cr_id=child_id, cr_parent_id=parent_id + ) + keys.append((child_id, parent_id)) + return keys + + @classmethod + def _update_child(cls, parent_id, tree, rel_types): + whole_tree = set() + childs = RecordRelations.objects.values_list( + "left_record_id", flat=True).filter( + right_record_id=parent_id, relation_type_id__in=rel_types) + for c in childs[:]: + if c in tree: # cyclic + childs.pop(c) + #print("childs", parent_id, childs) + if not childs: # last leaf in the tree + return cls._save_tree(tree) + for c in childs: + whole_tree.update(cls._update_child(c, tree[:] + [c], rel_types)) + return whole_tree + + @classmethod + def _get_parent_trees(cls, child_id, trees, rel_types): + parents = RecordRelations.objects.values_list( + "right_record_id", flat=True).filter( + left_record_id=child_id, relation_type_id__in=rel_types) + if not parents: + return trees + new_trees = [] + for p in parents: + if p == child_id or any(1 for tree in trees if p in tree): # cyclic + continue + c_trees = list(map(lambda x: x + [p], trees)) + new_trees += cls._get_parent_trees(p, c_trees, rel_types) + return new_trees + + @classmethod + def _update(cls, item_id, cascade=True): + # update the whole tree + rel_types = RelationType.objects.filter( + logical_relation__in=('included', 'equal')).values_list("id", flat=True) + + # get first parents + parent_ids = [ + tree[-1] for tree in cls._get_parent_trees(item_id, [[item_id]], rel_types)] + """ + parent_ids = [] + current_ids = [item_id] + while current_ids: + new_ids = [] + for current_id in current_ids: + parents = RecordRelations.objects.values_list( + "right_record_id", flat=True).filter( + left_record_id=current_id, relation_type_id__in=rel_types) + if not parents: + continue + for p in parents[:]: + if p == current_id or p in parent_ids: # cyclic + parents.pop(p) + parent_ids += parents + new_ids += parents + current_ids = new_ids + """ + def get_cr(idx): + return ContextRecord.objects.get(pk=idx) + print(get_cr(item_id)) + if not parent_ids: + parent_ids = [item_id] + print("parents", [get_cr(p) for p in parent_ids]) + + # get all child for parents and save trees + all_relations = set() + for parent_id in parent_ids: + tree = [parent_id] + all_relations.update(cls._update_child(parent_id, tree, rel_types)) + #print(all_relations) + + # delete old relations + for item_id in set([c for c, __ in all_relations] + + [p for p, __ in all_relations]): + for rel in cls.objects.filter(cr_id=item_id).all(): + if (rel.cr_id, rel.cr_parent_id) not in all_relations: + rel.delete() + for rel in cls.objects.filter(cr_parent_id=item_id).all(): + if (rel.cr_id, rel.cr_parent_id) not in all_relations: + rel.delete() + diff --git a/archaeological_context_records/tests.py b/archaeological_context_records/tests.py index 64950bd91..1f919b5d9 100644 --- a/archaeological_context_records/tests.py +++ b/archaeological_context_records/tests.py @@ -884,9 +884,8 @@ class RecordRelationsTest(ContextRecordInit, TestCase): model = models.ContextRecord def setUp(self): - # two different context records - self.create_context_record({"label": "CR 1"}) - self.create_context_record({"label": "CR 2"}) + for idx in range(1, 11): + self.create_context_record({"label": f"CR {idx}"}) def test_relations(self): sym_rel_type = models.RelationType.objects.create( @@ -933,7 +932,7 @@ class RecordRelationsTest(ContextRecordInit, TestCase): # for non-symmetrical relation, adding one relation automatically # adds the inverse - rel = models.RecordRelations.objects.create( + models.RecordRelations.objects.create( left_record=cr_1, right_record=cr_2, relation_type=rel_type_1 ) self.assertEqual( @@ -943,6 +942,63 @@ class RecordRelationsTest(ContextRecordInit, TestCase): 1, ) + def test_relation_view(self): + ## TODO : branches multiples + ## TODO : cyclique + profile = get_current_profile() + profile.parent_relations_engine = "V" + profile.save() + profile = get_current_profile(force=True) + models.ContextRecordTree.check_engine() + crs = self.context_records + rel_type_1 = models.RelationType.objects.create( + symmetrical=False, txt_idx="rel_1", + logical_relation='included' + ) + """ + 6 7 8 9 10 + | | | | | + ------- ----- + | | + 4 5 + | | + --------- + | + 3 + | + --------- + | | + 1 2 + """ + relations = ( + (1, 3), (2, 3), (3, 4), (3, 5), (4, 6), (4, 7), (4, 8), + (5, 9), (5, 10) + ) + for child_idx, parent_idx in relations: + models.RecordRelations.objects.create( + left_record=crs[child_idx - 1], + right_record=crs[parent_idx - 1], + relation_type=rel_type_1 + ) + q = models.ContextRecordTree.objects.filter( + cr_parent_id=crs[2].pk, cr_id=crs[0].pk) + self.assertGreaterEqual(q.count(), 1) + + self.assertIsNone(models.ContextRecordTree.check_engine()) # no change + profile.parent_relations_engine = "T" + profile.save() + profile = get_current_profile(force=True) + self.assertTrue(models.ContextRecordTree.check_engine()) # change to table + q = models.ContextRecordTree.objects.filter(cr=crs[0], cr_parent=crs[1]) + self.assertEqual(q.count(), 0) # empty table + print("~~~ CR1 - child of all") + models.ContextRecordTree.update(crs[0].id) + print("~~~ CR2") + models.ContextRecordTree.update(crs[1].id) + print("~~~ CR3 - parent of all") + models.ContextRecordTree.update(crs[2].id) + # vérifier cr1 -> cr3 + class ContextRecordWizardCreationTest(WizardTest, ContextRecordInit, TestCase): fixtures = OPERATION_TOWNS_FIXTURES diff --git a/docs/fr/source/annexe-tech-5-manage-commands.rst b/docs/fr/source/annexe-tech-5-manage-commands.rst new file mode 100644 index 000000000..463ae8bde --- /dev/null +++ b/docs/fr/source/annexe-tech-5-manage-commands.rst @@ -0,0 +1,19 @@ +.. -*- coding: utf-8 -*- + +.. _annexe-technique-5-manage-commands: + +================================================ +Annexe technique 5 - Commandes de gestion en CLI +================================================ + +:Auteurs: Étienne Loks +:Date: 2021-06-14 +:Copyright: CC-BY 3.0 +:Ishtar Version: v3.1.0 + +---------------------------------- + +relations_update_cache_tables +----------------------------- + + diff --git a/ishtar_common/management/commands/relations_update_cache_tables.py b/ishtar_common/management/commands/relations_update_cache_tables.py new file mode 100644 index 000000000..ab7f134ff --- /dev/null +++ b/ishtar_common/management/commands/relations_update_cache_tables.py @@ -0,0 +1,83 @@ +#!/usr/bin/env python +# -*- coding: utf-8 -*- +# Copyright (C) 2013-2018 Étienne Loks <etienne.loks_AT_peacefrogsDOTnet> + +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU Affero General Public License as +# published by the Free Software Foundation, either version 3 of the +# License, or (at your option) any later version. + +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU Affero General Public License for more details. + +# You should have received a copy of the GNU Affero General Public License +# along with this program. If not, see <http://www.gnu.org/licenses/>. + +# See the file COPYING for details. + +import sys + +from django.core.management.base import BaseCommand + +from django.apps import apps + + +APPS = ['ishtar_common', 'archaeological_operations', + 'archaeological_context_records', 'archaeological_finds', + 'archaeological_warehouse'] + + +class Command(BaseCommand): + args = '' + help = 'Regenerate geo, cached labels and search vectors' + + def add_arguments(self, parser): + parser.add_argument('app_name', nargs='?', default=None, + choices=APPS) + parser.add_argument('model_name', nargs='?', default=None) + parser.add_argument( + '--quiet', dest='quiet', action='store_true', + help='Quiet output') + + def handle(self, *args, **options): + quiet = options['quiet'] + limit = options['app_name'] + model_name = options['model_name'] + if model_name: + model_name = model_name.lower() + for app in APPS: + if limit and app != limit: + continue + if not quiet: + print("* app: {}".format(app)) + for model in apps.get_app_config(app).get_models(): + if model_name and model.__name__.lower() != model_name: + continue + if model.__name__.startswith('Historical'): + continue + if not bool( + [k for k in dir(model) + if k.startswith('_generate_') or + k == "search_vector"]): + continue + msg = "-> processing {}: ".format(model._meta.verbose_name) + ln = model.objects.count() + for idx, obj_id in enumerate(model.objects.values('pk').all()): + obj = model.objects.get(pk=obj_id['pk']) + obj.skip_history_when_saving = True + obj._no_move = True + if hasattr(obj, "point_source") and obj.point_source in ( + "M", "T"): + obj.point = None + obj.point_2d = None + obj.x = None + obj.y = None + cmsg = "\r{} {}/{}".format(msg, idx + 1, ln) + if not quiet: + sys.stdout.write(cmsg) + sys.stdout.flush() + obj.save() + if not quiet: + sys.stdout.write("\n") diff --git a/ishtar_common/migrations/0215_ishtarsiteprofile_parent_relations_engine.py b/ishtar_common/migrations/0215_ishtarsiteprofile_parent_relations_engine.py new file mode 100644 index 000000000..acf410837 --- /dev/null +++ b/ishtar_common/migrations/0215_ishtarsiteprofile_parent_relations_engine.py @@ -0,0 +1,20 @@ +# -*- coding: utf-8 -*- +# Generated by Django 1.11.28 on 2021-06-14 19:06 +from __future__ import unicode_literals + +from django.db import migrations, models + + +class Migration(migrations.Migration): + + dependencies = [ + ('ishtar_common', '0214_auto_20210308_1628'), + ] + + operations = [ + migrations.AddField( + model_name='ishtarsiteprofile', + name='parent_relations_engine', + field=models.CharField(choices=[('V', 'SQL views'), ('T', 'Cache tables')], default='V', help_text='If you experience performance problems with complex relations (for instance: complex statigraphic relations), set it to "Cache tables" in order to use static cache tables. Do not forget to update theses table with the "relations_update_cache_tables" manage.py command.', max_length=1, verbose_name='Parent relations engine'), + ), + ] diff --git a/ishtar_common/models.py b/ishtar_common/models.py index 94e02aafe..fdeba5f26 100644 --- a/ishtar_common/models.py +++ b/ishtar_common/models.py @@ -62,6 +62,7 @@ from django.core.exceptions import ( from django.core.files.base import ContentFile from django.core.files.uploadedfile import SimpleUploadedFile from django.core.urlresolvers import reverse +from django.db import connection from django.db.models import Q, Max, Count from django.db.models.signals import post_save, post_delete, m2m_changed from django.db.utils import DatabaseError @@ -76,6 +77,8 @@ from ishtar_common.utils import ( get_current_profile, duplicate_item, get_image_path, + serialize_args_for_tasks, + task, ) from ishtar_common.utils_secretary import IshtarSecretaryRenderer @@ -825,6 +828,80 @@ def post_delete_record_relation(sender, instance, **kwargs): q.delete() +@task() +def relation_view_update(sender, kwargs): + if isinstance(sender, (tuple, list)): + sender = apps.get_model(*sender) + sender._update(kwargs["item_id"]) + + +class RelationsViews(models.Model): + CREATE_SQL = "" # SQL view creation + DELETE_SQL = "" # SQL view deletion + CREATE_TABLE_SQL = "" # SQL table creation + + class Meta: + managed = False + abstract = True + + @classmethod + def _update(cls, item_id): + raise NotImplemented() + + @classmethod + def update(cls, item_id): + profile = get_current_profile() + if profile.parent_relations_engine == "V": + return + if not settings.USE_BACKGROUND_TASK: + return relation_view_update(cls, {"item_id": item_id}) + else: + sender, kwargs = serialize_args_for_tasks( + cls, None, {"item_id": item_id} + ) + return relation_view_update.delay(sender, kwargs) + + @classmethod + def create_table(cls): + raise NotImplemented() + + @classmethod + def check_engine(cls): + """ + Check view or table properly created with settings on the profile + :return: True if table or view updated + """ + assert cls.CREATE_SQL + assert cls.DELETE_SQL + assert cls.CREATE_TABLE_SQL + profile = get_current_profile(force=True) + table_type = '' + with connection.cursor() as cursor: + q = "select table_type from information_schema.tables WHERE " \ + "table_name=%s;" + cursor.execute(q, [cls._meta.db_table]) + q = cursor.fetchall() + if q: + table_type = q[0][0] + + if profile.parent_relations_engine == "V": + if table_type == 'VIEW': + return + elif 'TABLE' in table_type: + q = "DROP TABLE %s" + cursor.execute(q, [cls._meta.db_table]) + cursor.execute(cls.CREATE_SQL) + return True + + if profile.parent_relations_engine == "T": + if 'TABLE' in table_type: + return + elif table_type == 'VIEW': + cursor.execute(cls.DELETE_SQL) + cursor.execute(cls.CREATE_TABLE_SQL) + return True + + class SearchQuery(models.Model): label = models.TextField(_("Label"), blank=True, default="") query = models.TextField(_("Query"), blank=True, default="") @@ -918,6 +995,20 @@ class IshtarSiteProfile(models.Model, Cached): _("Container - calculate weight only when all find has a weight"), default=False, ) + parent_relations_engine = models.CharField( + _("Parent relations engine"), + choices=( + ("V", _("SQL views")), + ("T", _("Cache tables")), + ), + default="V", + max_length=1, + help_text=_("If you experience performance problems with complex relations " + "(for instance: complex statigraphic relations), set it to " + "\"Cache tables\" in order to use static cache tables. Do not " + "forget to update theses table with the " + "\"relations_update_cache_tables\" manage.py command.") + ) config = models.CharField( _("Alternate configuration"), max_length=200, |