Subversion Repositories eFlore/Applications.del

Compare Revisions

Ignore whitespace Rev 1959 → Rev 2094

/tags/v1.10-sodium/doc/bdd/migrations/2014-09_optimisation/maj_optimisation.sql
New file
0,0 → 1,64
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
 
-- -----------------------------------------------------
-- View `del_obs_images`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `del_obs_image` ;
DROP TABLE IF EXISTS `del_obs_image`;
 
-- View `v_del_image`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `v_del_image` ;
DROP TABLE IF EXISTS `v_del_image`;
 
-- -----------------------------------------------------
-- View `del_observations`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `del_observation` ;
DROP TABLE IF EXISTS `del_observation`;
CREATE OR REPLACE VIEW del_observation AS
SELECT id_observation,
if((char_length(o.ce_utilisateur) <> 32),cast(o.ce_utilisateur as unsigned),0) AS ce_utilisateur,
o.prenom_utilisateur, o.nom_utilisateur, o.courriel_utilisateur,
nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille,
ce_zone_geo, zone_geo, lieudit, station, milieu, nom_referentiel,
date_observation, o.mots_cles_texte, o.commentaire,
o.date_creation, o.date_modification, o.date_transmission, certitude
FROM tb_cel.cel_obs AS o INNER JOIN tb_cel.cel_images AS i ON (o.id_observation = i.ce_observation)
WHERE o.transmission = '1' ;
 
-- -----------------------------------------------------
-- View `del_images`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `del_image` ;
DROP TABLE IF EXISTS `del_image`;
CREATE OR REPLACE VIEW del_image AS
SELECT i.id_image, i.ce_observation,
if((char_length(i.ce_utilisateur) <> 32),cast(i.ce_utilisateur as unsigned),0) AS ce_utilisateur, i.prenom_utilisateur, i.nom_utilisateur, i.courriel_utilisateur,
i.hauteur, i.largeur, i.date_prise_de_vue, i.mots_cles_texte, i.commentaire, i.nom_original,
i.date_creation, i.date_modification, i.date_liaison, i.date_transmission
FROM tb_cel.cel_images AS i
WHERE i.transmission = '1';
 
-- -----------------------------------------------------
-- View `del_plantnet`
-- -----------------------------------------------------
## Il est nécessaire de garder une vue de ce type car l'optimisateur d'index de MYSQL fait n'importe quoi dans le cas du WS plantnet/changements...
DROP VIEW IF EXISTS `del_plantnet` ;
DROP TABLE IF EXISTS `del_plantnet`;
CREATE OR REPLACE VIEW del_plantnet AS
SELECT o.*,
i.id_image,
i.ce_utilisateur AS i_ce_utilisateur, i.prenom_utilisateur AS i_prenom_utilisateur, i.nom_utilisateur AS i_nom_utilisateur, i.courriel_utilisateur AS i_courriel_utilisateur,
hauteur, largeur, i.date_prise_de_vue, i.mots_cles_texte AS i_mots_cles_texte, i.commentaire AS i_commentaire, i.nom_original,
i.date_creation AS i_date_creation, i.date_modification AS i_date_modification, i.date_liaison AS i_date_liaison, i.date_transmission AS i_date_transmission
FROM tb_cel.cel_obs AS o
INNER JOIN tb_cel.cel_images AS i ON (o.id_observation = i.ce_observation )
WHERE o.transmission = '1';
 
-- -----------------------------------------------------
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
/tags/v1.10-sodium/doc/bdd/migrations/2014-10_validateurs/validateurs.sql
New file
0,0 → 1,72
Utilisateurs ayant proposé le plus grand nombre de propositions ayant été validées :
SELECT COUNT(id_commentaire) as total_propositions_validees, utilisateur_nom, utilisateur_prenom, id_utilisateur
FROM `del_commentaire` dc
INNER JOIN del_utilisateur du
ON du.id_utilisateur = dc.ce_utilisateur
WHERE proposition_retenue = 1
GROUP BY ce_utilisateur
ORDER BY total_propositions_validees DESC
 
 
Utilisateurs ayant proposé le plus grand nombre de propositions ayant été validées qui n'étaient pas la proposition initiale:
SELECT COUNT(id_commentaire) as total_propositions_validees, utilisateur_nom, utilisateur_prenom, id_utilisateur
FROM `del_commentaire` dc
INNER JOIN del_utilisateur du
ON du.id_utilisateur = dc.ce_utilisateur
WHERE proposition_retenue = 1
AND proposition_initiale = 0
GROUP BY ce_utilisateur
ORDER BY total_propositions_validees DESC
 
 
Utilisateurs ayant proposé le plus grand nombre de propositions ayant été validées qui étaient la proposition initiale:
SELECT COUNT(id_commentaire) as total_propositions_validees, utilisateur_nom, utilisateur_prenom, id_utilisateur
FROM `del_commentaire` dc
INNER JOIN del_utilisateur du
ON du.id_utilisateur = dc.ce_utilisateur
WHERE proposition_retenue = 1
AND proposition_initiale = 1
GROUP BY ce_utilisateur
ORDER BY total_propositions_validees DESC
 
Utilisateurs ayant déposé le plus grand nombre de commentaires:
SELECT COUNT(id_commentaire) as total_commentaires, utilisateur_nom, utilisateur_prenom, id_utilisateur
FROM `del_commentaire` dc
INNER JOIN del_utilisateur du
ON du.id_utilisateur = dc.ce_utilisateur
WHERE nom_sel IS NULL
GROUP BY ce_utilisateur
ORDER BY total_commentaires DESC
 
Utilisateurs ayant voté pour le plus grand nombre de propositions retenues:
SELECT COUNT(dcv.ce_proposition) as total_votes_propositions_validees, du.nom, du.prenom, dcv.ce_utilisateur
FROM del_commentaire_vote dcv
INNER JOIN del_commentaire dc
ON dcv.ce_proposition = dc.id_commentaire
AND valeur = 1
AND dc.proposition_retenue = 1
INNER JOIN del_utilisateur du
ON du.id_utilisateur = dcv.ce_utilisateur
GROUP BY dcv.ce_utilisateur
ORDER BY total_votes_propositions_validees DESC
 
 
Observations anonymes ayant des proposition votées positivement (donc potentiellement validables)
SELECT id_commentaire, ce_observation,
SUM(CASE
WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN 3
WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN -3
WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN 1
WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN -1
END
) as score
FROM del_commentaire dc
INNER JOIN del_commentaire_vote dcv
ON dc.id_commentaire = dcv.ce_proposition
WHERE dc.ce_observation NOT IN
(SELECT ce_observation FROM del_commentaire WHERE proposition_retenue = 1)
AND dc.ce_observation IN
(SELECT id_observation FROM del_observation WHERE ce_utilisateur = 0)
AND dc.nom_sel IS NOT NULL
GROUP BY dcv.ce_proposition
ORDER BY `score` DESC