Revision eec46cb0
Optimisation des requêtes pour le cas où le nombre de groupes de supitems est très élevé (#499).
git-svn-id: https://vigilo-dev.si.c-s.fr/svn@6193 b22e2e97-25c9-44ff-b637-2e5ceca36478
| vigiboard/controllers/root.py | ||
|---|---|---|
| 31 | 31 |
from pylons.i18n import ugettext as _, lazy_ugettext as l_ |
| 32 | 32 |
from sqlalchemy import asc |
| 33 | 33 |
from sqlalchemy.sql import func |
| 34 |
from sqlalchemy.orm import aliased |
|
| 34 | 35 |
from repoze.what.predicates import Any, All, in_group, \ |
| 35 | 36 |
has_permission, not_anonymous, \ |
| 36 | 37 |
NotAuthorizedError |
| ... | ... | |
| 40 | 41 |
from vigilo.models.session import DBSession |
| 41 | 42 |
from vigilo.models.tables import Event, EventHistory, CorrEvent, Host, \ |
| 42 | 43 |
SupItem, SupItemGroup, LowLevelService, \ |
| 43 |
StateName, State |
|
| 44 |
StateName, State, DataPermission
|
|
| 44 | 45 |
from vigilo.models.tables.grouphierarchy import GroupHierarchy |
| 45 | 46 |
from vigilo.models.functions import sql_escape_like |
| 46 |
from vigilo.models.tables.secondary_tables import EVENTSAGGREGATE_TABLE |
|
| 47 |
from vigilo.models.tables.secondary_tables import EVENTSAGGREGATE_TABLE, \ |
|
| 48 |
USER_GROUP_TABLE |
|
| 47 | 49 |
|
| 48 | 50 |
from vigilo.turbogears.controllers.autocomplete import AutoCompleteController |
| 49 | 51 |
from vigilo.turbogears.controllers.proxy import ProxyController |
| ... | ... | |
| 757 | 759 |
# retourne une liste vide dans le cas contraire |
| 758 | 760 |
is_manager = in_group('managers').is_met(request.environ)
|
| 759 | 761 |
if not is_manager: |
| 760 |
direct_access = False |
|
| 761 | 762 |
user = get_current_user() |
| 762 |
user_groups = dict(user.supitemgroups()) |
|
| 763 |
# On regarde d'abord si le groupe fait partie de ceux |
|
| 764 |
# auquels l'utilisateur a explicitement accès, ou s'il |
|
| 765 |
# est un parent des groupes auxquels l'utilisateur a accès |
|
| 766 |
if parent_id in user_groups.keys(): |
|
| 767 |
direct_access = user_groups[parent_id] |
|
| 768 |
# Dans le cas contraire, on vérifie si le groupe est un |
|
| 769 |
# sous-groupe des groupes auxquels l'utilisateur a accès |
|
| 770 |
else: |
|
| 771 |
id_list = [ug for ug in user_groups.keys() if user_groups[ug]] |
|
| 772 |
child_groups = DBSession.query(SupItemGroup.idgroup |
|
| 773 |
).distinct( |
|
| 774 |
).join( |
|
| 775 |
(GroupHierarchy, |
|
| 776 |
GroupHierarchy.idchild == SupItemGroup.idgroup), |
|
| 777 |
).filter(GroupHierarchy.idparent.in_(id_list) |
|
| 778 |
).filter(GroupHierarchy.hops > 0 |
|
| 779 |
).all() |
|
| 780 |
for ucg in child_groups: |
|
| 781 |
if ucg.idgroup == parent_id: |
|
| 782 |
direct_access = True |
|
| 783 |
break |
|
| 784 |
# Sinon, l'utilisateur n'a pas accès à ce groupe |
|
| 785 |
else: |
|
| 786 |
return dict(groups = [], leaves = []) |
|
| 787 |
|
|
| 788 |
# On récupère la liste des groupes dont |
|
| 789 |
# l'identifiant du parent est passé en paramètre |
|
| 790 |
db_groups = DBSession.query( |
|
| 791 |
SupItemGroup |
|
| 792 |
).join( |
|
| 793 |
(GroupHierarchy, GroupHierarchy.idchild == \ |
|
| 794 |
SupItemGroup.idgroup), |
|
| 795 |
).filter(GroupHierarchy.hops == 1 |
|
| 796 |
).filter(GroupHierarchy.idparent == parent_id |
|
| 797 |
).order_by(SupItemGroup.name.asc()) |
|
| 798 |
if not is_manager and not direct_access: |
|
| 799 |
id_list = [ug for ug in user_groups.keys()] |
|
| 800 |
db_groups = db_groups.filter( |
|
| 801 |
SupItemGroup.idgroup.in_(id_list)) |
|
| 763 |
GroupHierarchy_aliased = aliased(GroupHierarchy, |
|
| 764 |
name='GroupHierarchy_aliased') |
|
| 765 |
supitem_groups = DBSession.query( |
|
| 766 |
SupItemGroup.idgroup, |
|
| 767 |
SupItemGroup.name, |
|
| 768 |
).join( |
|
| 769 |
(GroupHierarchy, |
|
| 770 |
GroupHierarchy.idchild == SupItemGroup.idgroup), |
|
| 771 |
(DataPermission, |
|
| 772 |
DataPermission.idgroup == GroupHierarchy.idparent), |
|
| 773 |
(USER_GROUP_TABLE, USER_GROUP_TABLE.c.idgroup == \ |
|
| 774 |
DataPermission.idusergroup), |
|
| 775 |
).join( |
|
| 776 |
(GroupHierarchy_aliased, |
|
| 777 |
GroupHierarchy_aliased.idchild == SupItemGroup.idgroup), |
|
| 778 |
).filter(USER_GROUP_TABLE.c.username == user.user_name |
|
| 779 |
).filter(GroupHierarchy_aliased.idparent == parent_id |
|
| 780 |
).all() |
|
| 781 |
|
|
| 802 | 782 |
groups = [] |
| 803 |
for group in db_groups.all():
|
|
| 783 |
for group in supitem_groups:
|
|
| 804 | 784 |
groups.append({
|
| 805 | 785 |
'id' : group.idgroup, |
| 806 | 786 |
'name' : group.name, |
| ... | ... | |
| 838 | 818 |
user = get_current_user() |
| 839 | 819 |
is_manager = in_group('managers').is_met(request.environ)
|
| 840 | 820 |
if not is_manager: |
| 841 |
user_groups = [ug[0] for ug in user.supitemgroups()] |
|
| 842 |
root_groups = root_groups.filter( |
|
| 843 |
SupItemGroup.idgroup.in_(user_groups)) |
|
| 821 |
|
|
| 822 |
root_groups = root_groups.join( |
|
| 823 |
(GroupHierarchy, |
|
| 824 |
GroupHierarchy.idchild == SupItemGroup.idgroup), |
|
| 825 |
(DataPermission, |
|
| 826 |
DataPermission.idgroup == GroupHierarchy.idparent), |
|
| 827 |
(USER_GROUP_TABLE, USER_GROUP_TABLE.c.idgroup == \ |
|
| 828 |
DataPermission.idusergroup), |
|
| 829 |
).filter(USER_GROUP_TABLE.c.username == user.user_name) |
|
| 844 | 830 |
|
| 845 | 831 |
groups = [] |
| 846 | 832 |
for group in root_groups.all(): |
| vigiboard/controllers/vigiboardrequest.py | ||
|---|---|---|
| 33 | 33 |
|
| 34 | 34 |
from vigilo.models.session import DBSession |
| 35 | 35 |
from vigilo.models.tables import Event, CorrEvent, EventHistory, \ |
| 36 |
Host, LowLevelService, StateName |
|
| 37 |
from vigilo.models.tables.secondary_tables import SUPITEM_GROUP_TABLE |
|
| 36 |
Host, LowLevelService, StateName, DataPermission |
|
| 37 |
from vigilo.models.tables.grouphierarchy import GroupHierarchy |
|
| 38 |
from vigilo.models.tables.secondary_tables import SUPITEM_GROUP_TABLE, \ |
|
| 39 |
USER_GROUP_TABLE |
|
| 38 | 40 |
from vigiboard.widgets.edit_event import EditEventForm |
| 39 | 41 |
from vigiboard.controllers.plugins import VigiboardRequestPlugin |
| 40 | 42 |
|
| ... | ... | |
| 99 | 101 |
# Les managers ont accès à tout, les autres sont soumis |
| 100 | 102 |
# aux vérifications classiques d'accès aux données. |
| 101 | 103 |
if not is_manager: |
| 102 |
user_groups = [ug[0] for ug in user.supitemgroups() if ug[1]] |
|
| 103 |
lls_query = lls_query.filter( |
|
| 104 |
SUPITEM_GROUP_TABLE.c.idgroup.in_(user_groups) |
|
| 105 |
) |
|
| 106 |
host_query = host_query.filter( |
|
| 107 |
SUPITEM_GROUP_TABLE.c.idgroup.in_(user_groups) |
|
| 108 |
) |
|
| 109 |
|
|
| 104 |
# user_groups = [ug[0] for ug in user.supitemgroups() if ug[1]] |
|
| 105 |
# lls_query = lls_query.filter( |
|
| 106 |
# SUPITEM_GROUP_TABLE.c.idgroup.in_(user_groups) |
|
| 107 |
# ) |
|
| 108 |
# host_query = host_query.filter( |
|
| 109 |
# SUPITEM_GROUP_TABLE.c.idgroup.in_(user_groups) |
|
| 110 |
# ) |
|
| 111 |
|
|
| 112 |
lls_query = lls_query.join( |
|
| 113 |
(GroupHierarchy, |
|
| 114 |
GroupHierarchy.idchild == SUPITEM_GROUP_TABLE.c.idgroup), |
|
| 115 |
(DataPermission, |
|
| 116 |
DataPermission.idgroup == GroupHierarchy.idparent), |
|
| 117 |
(USER_GROUP_TABLE, USER_GROUP_TABLE.c.idgroup == \ |
|
| 118 |
DataPermission.idusergroup), |
|
| 119 |
).filter(USER_GROUP_TABLE.c.username == user.user_name) |
|
| 120 |
|
|
| 121 |
host_query = host_query.join( |
|
| 122 |
(GroupHierarchy, |
|
| 123 |
GroupHierarchy.idchild == SUPITEM_GROUP_TABLE.c.idgroup), |
|
| 124 |
(DataPermission, |
|
| 125 |
DataPermission.idgroup == GroupHierarchy.idparent), |
|
| 126 |
(USER_GROUP_TABLE, USER_GROUP_TABLE.c.idgroup == \ |
|
| 127 |
DataPermission.idusergroup), |
|
| 128 |
).filter(USER_GROUP_TABLE.c.username == user.user_name) |
|
| 110 | 129 |
|
| 111 | 130 |
# Objet Selectable renvoyant des informations sur un SupItem |
| 112 | 131 |
# concerné par une alerte, avec prise en compte des droits d'accès. |
Also available in: Unified diff