[misc] add function for deleting entities faster (not released)

Delete entities faster using a minimal set of delete sql statements and without executing hooks. Benchmarks on some cases show that it's about 2x faster.

This has not been exhaustively tested but to avoid loosing this work put it in cubicweb/misc/scripts (not installed by python package).

authorPhilippe Pepiot <philippe.pepiot@logilab.fr>
changeset549dcb8ad7ec
branchdefault
phasedraft
hiddenno
parent revision#a4d465a3e77d fix(ci): manually remove the .tox/doc directory
child revision<not specified>
files modified by this revision
cubicweb/misc/scripts/fast_drop_entities.py
flake8-ok-files.txt
# HG changeset patch
# User Philippe Pepiot <philippe.pepiot@logilab.fr>
# Date 1490868095 -7200
# Thu Mar 30 12:01:35 2017 +0200
# Node ID 549dcb8ad7ecc60c22278b3eb349c76ff7c47849
# Parent a4d465a3e77d07cf6a79c121c10b2d6484cd7468
[misc] add function for deleting entities faster (not released)

Delete entities faster using a minimal set of delete sql statements and without
executing hooks.
Benchmarks on some cases show that it's about 2x faster.

This has not been exhaustively tested but to avoid loosing this work put it in
cubicweb/misc/scripts (not installed by python package).

diff --git a/cubicweb/misc/scripts/fast_drop_entities.py b/cubicweb/misc/scripts/fast_drop_entities.py
@@ -0,0 +1,98 @@
1 +# copyright 2017 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
2 +# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
3 +#
4 +# This file is part of CubicWeb.
5 +#
6 +# CubicWeb is free software: you can redistribute it and/or modify it under the
7 +# terms of the GNU Lesser General Public License as published by the Free
8 +# Software Foundation, either version 2.1 of the License, or (at your option)
9 +# any later version.
10 +#
11 +# CubicWeb is distributed in the hope that it will be useful, but WITHOUT
12 +# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
13 +# FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more
14 +# details.
15 +#
16 +# You should have received a copy of the GNU Lesser General Public License along
17 +# with CubicWeb.  If not, see <http://www.gnu.org/licenses/>.
18 +
19 +import collections
20 +
21 +
22 +def _fast_drop_entity(cnx, eid, etype, to_delete, rels_to_delete, to_update):
23 +    if eid in to_delete.get(etype, []):
24 +        # avoid loops
25 +        return
26 +    to_delete.setdefault(etype, set()).add(eid)
27 +
28 +    eschema = cnx.repo.schema[etype]
29 +
30 +    for rschema, related_etypes, role in eschema.relation_definitions():
31 +        if rschema.type == 'identity':
32 +            continue
33 +        composites = []
34 +        non_composites = []
35 +        for related_etype in related_etypes:
36 +            rdef = rschema.role_rdef(eschema, related_etype, role)
37 +            if role == rdef.composite:
38 +                composites.append(related_etype.type)
39 +            else:
40 +                non_composites.append(related_etype.type)
41 +
42 +        if composites and non_composites:
43 +            raise NotImplementedError
44 +
45 +        if non_composites and role == 'subject' and rschema.inlined:
46 +            continue
47 +
48 +        if non_composites and role == 'object' and rschema.inlined:
49 +            for related_etype in non_composites:
50 +                to_update.setdefault(
51 +                    (related_etype, rschema.type), set()).add(eid)
52 +        elif not rschema.inlined:
53 +            rels_to_delete.setdefault(
54 +                (rschema.type, role), set()).add(eid)
55 +
56 +        if composites:
57 +            rql = 'Any Y, E WHERE Y is ET, ET name E, X eid %(eid)s'
58 +            if role == 'subject':
59 +                rql = ','.join([rql, 'X {0} Y'.format(rschema.type)])
60 +            else:
61 +                rql = ','.join([rql, 'Y {0} X'.format(rschema.type)])
62 +            for related_eid, related_etype in cnx.execute(rql, {'eid': eid},
63 +                                                          build_descr=False):
64 +                _fast_drop_entity(cnx, related_eid, related_etype, to_delete,
65 +                                  rels_to_delete, to_update)
66 +
67 +
68 +def fast_drop_entities(rset):
69 +    """Delete entities faster using raw SQL and without executing hooks"""
70 +    cnx = rset.req
71 +    to_delete = collections.OrderedDict()
72 +    rels_to_delete = collections.OrderedDict()
73 +    to_update = collections.OrderedDict()
74 +    for entity in rset.entities():
75 +        _fast_drop_entity(cnx, entity.eid, entity.cw_etype, to_delete,
76 +                          rels_to_delete, to_update)
77 +
78 +    for (etype, rtype), eids in to_update.items():
79 +        cnx.system_sql(
80 +            'UPDATE cw_{0} SET cw_{1} = NULL WHERE cw_{1} IN ({2})'.format(
81 +                etype, rtype, ','.join(str(e) for e in eids)))
82 +
83 +    for (rtype, role), eids in rels_to_delete.items():
84 +        cnx.system_sql(
85 +            'DELETE FROM {0}_relation WHERE {1} IN ({2})'.format(
86 +                rtype, 'eid_from' if role == 'subject' else 'eid_to',
87 +                ','.join(str(e) for e in eids)))
88 +
89 +    deleted = set()
90 +    for etype, eids in to_delete.items():
91 +        deleted |= eids
92 +        cnx.system_sql(
93 +            'DELETE FROM cw_{0} WHERE cw_eid IN ({1})'.format(
94 +                etype, ','.join(str(e) for e in eids)))
95 +    if deleted:
96 +        eids = ','.join(str(e) for e in deleted)
97 +        cnx.system_sql('DELETE FROM appears WHERE uid IN ({0})'.format(eids))
98 +        cnx.system_sql('DELETE FROM entities WHERE eid IN ({0})'.format(eids))
diff --git a/flake8-ok-files.txt b/flake8-ok-files.txt
@@ -38,11 +38,15 @@
99  cubicweb/hooks/syncsources.py
100  cubicweb/hooks/test/data/hooks.py
101  cubicweb/hooks/test/unittest_notificationhooks.py
102  cubicweb/hooks/test/unittest_security.py
103  cubicweb/hooks/test/unittest_syncsession.py
104 +<<<<<<< dest
105  cubicweb/hooks/test/unittest_syncsources.py
106 +=======
107 +cubicweb/misc/scripts/fast_drop_entities.py
108 +>>>>>>> source
109  cubicweb/pylintext.py
110  cubicweb/pyramid/__init__.py
111  cubicweb/pyramid/auth.py
112  cubicweb/pyramid/bwcompat.py
113  cubicweb/pyramid/config.py