Réduire le nombre de requêtes pour la requête Sample_Set

Dans la même veine que #2439 il serait bien de réduire la charge de la BDD, et d'essayer de rendre l'application plus rapide.

Voici ce qu'il se passe du point de vue de la BDD pour une requête de 4 patients (découpée en plusieurs blocks à mesure que j'identifie les requêtes) :

SELECT 1
SET FOREIGN_KEY_CHECKS=1
SET sql_mode='NO_BACKSLASH_ESCAPES'
SELECT  auth_membership.id, auth_membership.user_id, auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT  auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 1)
SELECT  auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 2)
SELECT  auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 3)
commit

Ces requêtes sont de self.has_permission() dans vidjil_accessible_query(). Le but étant de vérifier si l'utilisateur a les accès "admin" pour cette table et cette action:

SELECT  auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT  auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'read') AND (auth_permission.table_name = 'patient')) AND (auth_permission.record_id = 0))

Je suis admin, donc on renvoi tous les patients:

SELECT  patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id > 0) ORDER BY patient.id DESC LIMIT 51 OFFSET 0

Ici c'est un appel à load_permissions(), qui fait appel à vidjil_accessible_query() deux fois (pour charger tous les patients/runs/sets en mémoire et stocker la permission):

SELECT  auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT  auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'read') AND (auth_permission.table_name = 'patient')) AND (auth_permission.record_id = 0))
SELECT  patient.id FROM patient WHERE (patient.id > 0)
SELECT  auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT  auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'admin') AND (auth_permission.table_name = 'patient')) AND (auth_permission.record_id = 0))
SELECT  patient.id FROM patient WHERE (patient.id > 0)

Encore un appel à load_permissions() cette fois-ci pour les permissions anon. On n'est pas "admin" car les admins doivent voir les vrais noms de leurs patients :) :

SELECT  auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT  auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'read') AND (auth_permission.table_name = 'patient')) AND (auth_permission.record_id = 0))
SELECT  patient.id FROM patient WHERE (patient.id > 0)
SELECT  auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT  auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'anon') AND (auth_permission.table_name = 'patient')) AND (auth_permission.record_id = 0))
SELECT  auth_membership.group_id FROM auth_permission, auth_membership WHERE ((((auth_membership.user_id = 1) AND (auth_membership.group_id = auth_permission.group_id)) AND (auth_permission.record_id = 0)) AND (auth_permission.name = 'anon'))
SELECT  patient.id FROM patient WHERE ((patient.id IN (SELECT  auth_permission.record_id FROM auth_permission, auth_membership WHERE (((((auth_membership.user_id = 1) AND (auth_membership.group_id IN (1))) AND (auth_membership.group_id = auth_permission.group_id)) AND (auth_permission.name = 'access')) AND (auth_permission.table_name = 'patient')))) OR (patient.id IN (SELECT  auth_permission.record_id FROM auth_permission, group_assoc, auth_membership WHERE ((((((auth_membership.user_id = 1) AND (auth_membership.group_id IN (1))) AND (auth_membership.group_id = group_assoc.second_group_id)) AND (group_assoc.first_group_id = auth_permission.group_id)) AND (auth_permission.name = 'access')) AND (auth_permission.table_name = 'patient')))))

Ici on a des vérifications d'existence effectuées dans les fonctions can_view_info():

SELECT  patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 11) LIMIT 1 OFFSET 0
SELECT  patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 10) LIMIT 1 OFFSET 0
SELECT  patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 9) LIMIT 1 OFFSET 0
SELECT  patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 8) LIMIT 1 OFFSET 0

Ces requêtes sont relativement raisonnables. Récupèrent les noms des créateurs et les tailles des sample sets:

SELECT  patient.id, auth_user.last_name FROM auth_user, patient WHERE ((patient.creator = auth_user.id) AND (patient.id IN (8,9,10,11)))
SELECT  patient.id, sequence_file.size_file FROM sample_set_membership, patient, sequence_file WHERE (((sample_set_membership.sample_set_id = patient.sample_set_id) AND (sequence_file.id = sample_set_membership.sequence_file_id)) AND (patient.id IN (8,9,10,11)))

load_config_information contient deux vidjil_accessible_query:

SELECT  auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT  auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'read') AND (auth_permission.table_name = 'config')) AND (auth_permission.record_id = 0))
SELECT  auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT  auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'admin') AND (auth_permission.table_name = 'config')) AND (auth_permission.record_id = 0))
SELECT  patient.id, config.name, config.id, fused_file.fused_file FROM patient, config, fused_file WHERE ((((patient.sample_set_id = fused_file.sample_set_id) AND (fused_file.config_id = config.id)) AND ((config.id > 
0) OR (config.id > 0))) AND (patient.id IN (8,9,10,11)))

load_permitted_groups():

SELECT  patient.id, auth_group.role FROM auth_group, auth_permission, patient WHERE ((((((patient.id = auth_permission.record_id) OR (auth_permission.record_id = 0)) AND (auth_permission.table_name = 'patient')) AND (auth_permission.name = 'access')) AND (auth_group.id = auth_permission.group_id)) AND (patient.id IN (8,9,10,11)))

load_anon_permissions()... On n'avait pas fait ça plus haut ??? :

SELECT  patient.id FROM auth_group, auth_permission, patient, auth_membership WHERE (((((((auth_permission.name = 'anon') AND (auth_permission.table_name = 'patient')) AND (patient.id = auth_permission.record_id)) AND (auth_group.id = auth_permission.group_id)) AND (auth_membership.user_id = 1)) AND (auth_membership.group_id = auth_group.id)) AND (patient.id IN (8,9,10,11)))

On charge tous les utilisateurs ? Pas trouvé dans le code... :

SELECT  auth_user.id, auth_user.first_name, auth_user.last_name, auth_user.email, auth_user.password, auth_user.registration_key, auth_user.reset_password_key, auth_user.registration_id FROM auth_user WHERE (auth_user.id > 1)

Encore des vérifications d'existence... Pas trouvé l'endroit dans le code:

SELECT  patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 11) LIMIT 1 OFFSET 0
SELECT  patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 10) LIMIT 1 OFFSET 0
SELECT  patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 9) LIMIT 1 OFFSET 0
SELECT  patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 8) LIMIT 1 OFFSET 0

Vérifie si l'utilisateur a le droit de créer des patients/runs/sets :

SELECT  auth_membership.group_id FROM auth_permission, auth_membership WHERE ((((auth_membership.user_id = 1) AND (auth_membership.group_id = auth_permission.group_id)) AND (auth_permission.record_id = 0)) AND (auth_permission.name = 'create'))
commit

A identifier:

SELECT 1
SET FOREIGN_KEY_CHECKS=1
SET sql_mode='NO_BACKSLASH_ESCAPES'
SELECT  auth_membership.id, auth_membership.user_id, auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT  auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 1)
SELECT  auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 2)
SELECT  auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 3)
commit