/trunk/scripts/modules/cel/cel_references.sh |
---|
File deleted |
/trunk/scripts/modules/cel/Makefile |
---|
New file |
0,0 → 1,22 |
# echo $(egrep -l 'BASE(SOURCE|EDIT|ANNUAIRE)' *.sql) |
fichiers = cel_references.sql dedup-ordre-201307.sql fix-utilisateur-32.sql maj-cleanup-201307.sql maj-hash-id-obs-migr.sql maj-nom-ret.sql maj-referentiel-201307.sql maj-referentiel-und-201307.sql maj-struct-201307.sql redempteur.sql referonosaure.sql |
# la base de données à modifier |
alterdb = $(db) |
alterdb ?= tb_cel_test |
# pour bdtfxa, bdtxa, isfan, nvjfl, nva, baseflor, ... lecture seule; |
# utilisée pour actualiser les enregistrements de cel_obs dans referonosaure.sql |
sourcedb = $(sourcedb) |
sourcedb ?= tb_eflore |
# pour annuaire_tela, lecture seule; |
# utilisée pour initialiser cel_utilisateurs dans maj-struct-201307.sql |
annuairedb = $(annuairedb) |
annuairedb ?= tela_prod_v4 |
all: |
sed -i -e "s/`BASEEDIT`/`$(alterdb)`/g" \ |
-e "s/`BASEANNUAIRE`/`$(annuairedb)`/g" \ |
-e "s/`BASESOURCE`/`$(sourcedb)`/g" \ |
$(fichiers) |
Property changes: |
Added: svn:eol-style |
+native |
\ No newline at end of property |
/trunk/scripts/modules/cel/referonosaure.sql |
---|
9,7 → 9,7 |
/* |
-- SELECT id_observation, b.num_nom, CONCAT(b.nom_sci, ' ', b.auteur), b.num_taxonomique, b.famille |
SELECT id_observation, nom_ret, nom_ret_nn, nt, c.famille |
FROM tb_cel.cel_obs c, tb_eflore.bdtfx_v1_01 b |
FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`bdtfx_v1_01` b |
WHERE ( |
nom_sel_nn IS NOT NULL AND nom_sel_nn != 0 |
AND nom_referentiel like 'bdtfx%' |
19,7 → 19,7 |
*/ |
--- l'update --- |
UPDATE tb_cel.cel_obs c, tb_eflore.bdtfx_v1_01 b SET |
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`bdtfx_v1_01` b SET |
c.nom_ret = CONCAT(b.nom_sci, ' ', b.auteur), |
c.nom_ret_nn = b.num_nom, |
c.nt = b.num_taxonomique, |
35,7 → 35,7 |
-- 31524 avec les restrictions sur famille et SUBSTRING_INDEX() |
UPDATE tb_cel.cel_obs c, tb_eflore.bdtxa_v1_00 a SET |
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`bdtxa_v1_00` a SET |
c.nom_ret = CONCAT(a.nom_sci, ' ', a.auteur), |
c.nom_ret_nn = a.num_nom, |
c.nt = a.num_tax, |
50,7 → 50,7 |
-- 49 sans les restrictions sur famille et SUBSTRING_INDEX() |
-- 47 avec les restrictions sur famille et SUBSTRING_INDEX() |
UPDATE tb_cel.cel_obs c, tb_eflore.isfan_v2013 a SET |
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`isfan_v2013` a SET |
c.nom_ret = CONCAT(a.nom_sci, ' ', a.auteur), |
c.nom_ret_nn = a.num_nom, |
c.nt = a.num_taxonomique, |
/trunk/scripts/modules/cel/fix-utilisateur-32.sql |
---|
1,10 → 1,10 |
-- corriger les addresses erronées: |
-- SELECT distinct ce_utilisateur FROM tb_cel.cel_obs WHERE LENGTH(ce_utilisateur) = 32 AND ce_utilisateur LIKE '%@%' AND ce_utilisateur NOT REGEXP '\.(fr|com)$'; |
-- SELECT distinct ce_utilisateur FROM `BASEEDIT`.`cel_obs` WHERE LENGTH(ce_utilisateur) = 32 AND ce_utilisateur LIKE '%@%' AND ce_utilisateur NOT REGEXP '\.(fr|com)$'; |
ALTER TABLE tb_cel.cel_obs MODIFY ce_utilisateur VARCHAR(255) NOT NULL; |
ALTER TABLE `BASEEDIT`.`cel_obs` MODIFY ce_utilisateur VARCHAR(255) NOT NULL; |
UPDATE tb_cel.cel_obs SET ce_utilisateur = CONCAT(SUBSTRING_INDEX(ce_utilisateur,'@', 1), '@apprenti.isa-lille.fr') WHERE ce_utilisateur LIKE '%@apprenti.isa-%'; |
UPDATE tb_cel.cel_obs SET ce_utilisateur = 'amardeilh.michel@club-internet.fr' WHERE ce_utilisateur = 'amardeilh.michel@club-internet.f'; |
UPDATE tb_cel.cel_obs SET ce_utilisateur = 'claude.figureau.plantnet@gmail.com' WHERE ce_utilisateur = 'claude.figureau.plantnet@gmail.c'; |
UPDATE tb_cel.cel_obs SET ce_utilisateur = 'francoise.delachaussee@dbmail.com' WHERE ce_utilisateur = 'francoise.delachaussee@dbmail.co'; |
UPDATE tb_cel.cel_obs SET ce_utilisateur = 'lucie.boust@proxalys-environnement.com' WHERE ce_utilisateur = 'lucie.boust@proxalys-environneme'; |
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = CONCAT(SUBSTRING_INDEX(ce_utilisateur,'@', 1), '@apprenti.isa-lille.fr') WHERE ce_utilisateur LIKE '%@apprenti.isa-%'; |
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'amardeilh.michel@club-internet.fr' WHERE ce_utilisateur = 'amardeilh.michel@club-internet.f'; |
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'claude.figureau.plantnet@gmail.com' WHERE ce_utilisateur = 'claude.figureau.plantnet@gmail.c'; |
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'francoise.delachaussee@dbmail.com' WHERE ce_utilisateur = 'francoise.delachaussee@dbmail.co'; |
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'lucie.boust@proxalys-environnement.com' WHERE ce_utilisateur = 'lucie.boust@proxalys-environneme'; |
/trunk/scripts/modules/cel/cel.ini |
---|
5,7 → 5,7 |
[tables] |
obs = cel_inventory |
obsImages = cel_obs_images |
obsImages = `BASEEDIT`.`cel_obs`_images |
images = cel_images |
motsClesImages = cel_mots_cles_images |
motsClesObs = cel_mots_cles_obs |
/trunk/scripts/modules/cel/maj-referentiel-201307.sql |
---|
4,7 → 4,7 |
Les observations problématiques sont les suivantes: |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille |
FROM cel_obs |
FROM `BASEEDIT`.`cel_obs` |
WHERE nom_referentiel IS NULL AND nom_sel != '' AND nom_sel IS NOT NULL AND nom_ret_nn IS NOT NULL; |
Or maj-cleanup-201307.sql reset les valeurs des observations ayant un nom_sel NULL ou '', de plus la préférence de sélection est |
12,7 → 12,7 |
La requête est donc: |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille |
FROM cel_obs WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL; |
FROM `BASEEDIT`.`cel_obs` WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL; |
=> 76486 |
*/ |
21,20 → 21,20 |
DROP PROCEDURE IF EXISTS majreferentiel; |
CREATE TEMPORARY TABLE IF NOT EXISTS T_bis ( INDEX(`nom`(30))) AS \ |
SELECT "bdtfx" AS valid_ref, CONCAT(b.nom_sci, ' ', b.auteur) AS nom, b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_01 b UNION ALL \ |
SELECT "bdtxa" AS valid_ref, CONCAT(a.nom_sci, ' ', a.auteur) AS nom, a.num_nom, a.num_tax, a.famille FROM tb_eflore.bdtxa_v1_00 a; |
SELECT "bdtfx" AS valid_ref, CONCAT(b.nom_sci, ' ', b.auteur) AS nom, b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`bdtfx_v1_01` b UNION ALL \ |
SELECT "bdtxa" AS valid_ref, CONCAT(a.nom_sci, ' ', a.auteur) AS nom, a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`bdtxa_v1_00` a; |
/* Donc nous JOINons: |
-- INNER JOIN sur bdtfx: 69719 |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM cel_obs c INNER JOIN tb_eflore.bdtfx_v1_01 b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL; |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`bdtfx_v1_01` b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL; |
-- INNER JOIN sur bdtxa: 79471 dont 9752 en dup (bdtxa + bdtfx), eg: 1005047 |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM cel_obs c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel AND nom_sel_nn IS NOT NULL; |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel AND nom_sel_nn IS NOT NULL; |
*/ |
/* d'où GROUP BY id_observation HAVING count(id_observation) = 1 */ |
/* |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille |
FROM cel_obs c |
FROM `BASEEDIT`.`cel_obs` c |
INNER JOIN T_bis b |
ON (b.num_nom = c.nom_sel_nn) |
WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL GROUP BY id_observation HAVING count(id_observation) = 1; |
50,7 → 50,7 |
DECLARE subst INT DEFAULT 0; |
DECLARE _id_observation bigint(20) DEFAULT 0; |
DECLARE _valid_ref varchar(20) DEFAULT NULL; |
DECLARE cur1 CURSOR FOR SELECT id_observation, valid_ref FROM cel_obs c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn) |
DECLARE cur1 CURSOR FOR SELECT id_observation, valid_ref FROM `BASEEDIT`.`cel_obs` c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn) |
WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL group by id_observation HAVING count(id_observation) = 1; |
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; |
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; |
58,7 → 58,7 |
OPEN cur1; |
REPEAT |
FETCH cur1 INTO _id_observation, _valid_ref; |
UPDATE tb_cel.cel_obs c SET nom_referentiel = _valid_ref WHERE id_observation = _id_observation; |
UPDATE `BASEEDIT`.`cel_obs` c SET nom_referentiel = _valid_ref WHERE id_observation = _id_observation; |
SET subst = subst + 1; |
UNTIL done END REPEAT; |
select subst AS 'nombre de mises à jour de référentiel effectuées'; |
/trunk/scripts/modules/cel/maj-struct-201307.sql |
---|
1,17 → 1,17 |
-- dépot "cel", r1739 |
ALTER TABLE tb_cel.cel_obs MODIFY nom_sel VARCHAR(601) NULL DEFAULT NULL; |
ALTER TABLE tb_cel.cel_obs MODIFY nom_ret VARCHAR(601) NULL DEFAULT NULL; |
ALTER TABLE `BASEEDIT`.`cel_obs` MODIFY nom_sel VARCHAR(601) NULL DEFAULT NULL; |
ALTER TABLE `BASEEDIT`.`cel_obs` MODIFY nom_ret VARCHAR(601) NULL DEFAULT NULL; |
-- dépot "cel", r1739 |
CREATE INDEX nom_referentiel ON tb_cel.cel_obs (`nom_referentiel`(5)); |
CREATE INDEX nom_referentiel ON `BASEEDIT`.`cel_obs` (`nom_referentiel`(5)); |
-- depot "cel", r1809 |
ALTER TABLE tb_cel.cel_obs MODIFY altitude INTEGER(5) DEFAULT NULL; |
ALTER TABLE `BASEEDIT`.`cel_obs` MODIFY altitude INTEGER(5) DEFAULT NULL; |
-- depot "cel", r1811 |
CREATE OR REPLACE VIEW `cel_utilisateurs` AS |
CREATE OR REPLACE VIEW `BASEEDIT`.`cel_utilisateurs` AS |
SELECT at.U_ID AS id_utilisateur, at.U_SURNAME AS prenom, at.U_NAME AS nom, at.U_MAIL AS courriel, at.U_PASSWD AS mot_de_passe, |
ui.licence_acceptee, ui.admin, ui.preferences, ui.date_premiere_utilisation |
FROM tela_prod_v4.annuaire_tela AS at |
LEFT JOIN cel_utilisateurs_infos AS ui ON (ui.id_utilisateur = at.U_ID); |
FROM `BASEANNUAIRE`.`annuaire_tela` AS at |
LEFT JOIN `BASEEDIT`.`cel_utilisateurs_infos` AS ui ON (ui.id_utilisateur = at.U_ID); |
/trunk/scripts/modules/cel/cel_references.sql |
---|
New file |
0,0 → 1,124 |
/* |
TODO: |
* fix référentiel: suppression n° de version et uniformisation |
SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM `BASEEDIT`.`cel_obs` GROUP BY nom_referentiel ORDER BY count DESC; |
* ajout INDEX nom_referentiel(5) sur `BASEEDIT`.`cel_obs` |
* ajout INDEX catminat_code sur baseflor_v2012_12_31 |
* ajout INDEX num_taxon sur nva_v2013_06 |
* fix date: set NULL pour les dates dans le futur |
SELECT courriel_utilisateur, id_observation, date_observation FROM `BASEEDIT`.`cel_obs` WHERE date_observation > NOW(); |
* intégrer les noms non-associés à un taxon (bdtfx where num_taxonomique = '') |
* intégrer les noms non-associés à un taxon (bdtxa where num_tax = '' || num_tax IS NULL) |
CREATE INDEX i_nom_referentiel ON `BASEEDIT`.`cel_obs` (`nom_referentiel`(5)); |
CREATE INDEX i_catminat_code ON baseflor_v2012_12_31 (`catminat_code`); |
CREATE INDEX i_num_taxon ON nva_v2013_06 (`num_taxon`); |
*/ |
-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell-script |
-- SET @destdb = 'tb_cel'; |
-- SET @desttable = 'cel_references'; |
-- -- SET BASEEDIT = `tb_cel`; |
-- SET @dst = CONCAT('`',@destdb,'`','.`',@desttable,'`'); |
DROP TABLE IF EXISTS `BASEEDIT`.`cel_references`; |
CREATE TABLE IF NOT EXISTS `BASEEDIT`.`cel_references` ( |
`referentiel` CHAR(5) NOT NULL COMMENT 'eg: "bdtfx", "bdtfx", "bdtxa", ... No ENUM!', |
-- bdtfx |
`num_nom` INT(9) NOT NULL DEFAULT '0' COMMENT 'depuis bdtfx', |
`num_nom_retenu` VARCHAR(9) DEFAULT NULL COMMENT 'depuis bdtfx', |
-- bdtfx + nvjfl_v2007 + nva_v2013_06 |
`num_taxon` int(9) NOT NULL COMMENT "depuis bdtfx, nvjfl_v2007 et nva_v2013_06 (commun), les noms non-associés ne sont pas intégrés pour l\'instant", |
-- bdtfx |
`nom_sci` VARCHAR(500) NOT NULL COMMENT 'depuis bdtfx', |
`auteur` VARCHAR(100) DEFAULT NULL COMMENT 'depuis bdtfx', |
-- `BASEEDIT`.`cel_obs` |
-- `nom_ret_nn` DECIMAL(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.', |
-- `nom_ret` VARCHAR(255) DEFAULT NULL, |
-- nvjfl_v2007 (`nom_vernaculaire` text NOT NULL) |
-- mais NULL à cause de nva |
`nom_commun` VARCHAR(60) NULL COMMENT 'nom_vernaculaire pour nvjfl_v2007 et nva_v2013_06', |
-- baseflor_v2012_12_31 |
`catminat_code` varchar(18) DEFAULT 'inconnu' COMMENT 'depuis baseflor_v2012_12_31', |
`ve_lumiere` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_temperature` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_continentalite` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_humidite_atmos` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_humidite_edaph` int(2) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_reaction_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_nutriments_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_salinite` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_texture_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_mat_org_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
-- baseveg_v2013_01_09 |
`syntaxon` varchar(255) NULL COMMENT 'depuis baseveg_v2013_01_09', |
PRIMARY KEY (`referentiel`, `num_nom`), |
INDEX (`referentiel`(5)), |
INDEX (`num_nom`), |
INDEX (`num_taxon`) |
) ENGINE=MyISAM \ |
DEFAULT CHARSET=utf8 \ |
COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de bdtfx_v1_01, nvjfl_v2007 et bdtxa_v1_00'; |
-- tables temporaires |
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`; |
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \ |
-- ( SELECT n.num_taxon, n.nom_vernaculaire FROM `BASESOURCE`.`nvjfl_v2007` n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon, n.num_statut HAVING n.num_statut = MAX(n.num_statut) ); |
( SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut as void, MAX(n.num_statut) as void2 FROM `BASESOURCE`.`nvjfl_v2007` n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon HAVING n.num_statut = MAX(n.num_statut) ); |
-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop long |
CREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \ |
( SELECT n.num_taxon, n.nom_vernaculaire FROM `BASESOURCE`.`nva_v2013_06` n WHERE n.code_langue = 'fra' /* DB pb */ AND n.num_taxon IS NOT NULL /* /DB pb */ GROUP BY n.num_nom); -- aggrégat arbitraire car pas de num_statut |
-- JOIN ON num_taxon_originel car INDEX |
-- cf: eflore/projets/donnees/baseflor/2012-12-31/baseflor.sql |
CREATE TEMPORARY TABLE T_basevegflor ( INDEX(`num_nomen`), INDEX(`num_taxon`) ) AS \ |
SELECT f.num_nomen, f.num_taxon, f.catminat_code, f.ve_lumiere, f.ve_temperature, f.ve_continentalite, f.ve_humidite_atmos, f.ve_humidite_edaph, f.ve_reaction_sol, f.ve_nutriments_sol, f.ve_salinite, f.ve_texture_sol, f.ve_mat_org_sol, \ |
v.syntaxon \ |
FROM `BASESOURCE`.`baseflor_v2012_12_31` f LEFT JOIN `BASESOURCE`.`baseveg_v2013_01_09` v ON (f.catminat_code = v.code_catminat AND v.niveau = 'ALL' AND v.syntaxon IS NOT NULL) WHERE f.BDNT = "BDTFX" \ |
GROUP BY f.num_nomen, f.num_taxon; -- group by car plusieurs couple (f.num_nomen, f.num_taxon) peuvent exister dans baseveg_v2013_01_09 or num_nom est PRIMARY dans cel_references |
-- INSERTIONS |
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_taxonomique != '') |
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`, \ |
`catminat_code`, `ve_lumiere`, `ve_temperature`, `ve_continentalite`, `ve_humidite_atmos`, `ve_humidite_edaph`, \ |
`ve_reaction_sol`, `ve_nutriments_sol`, `ve_salinite`, `ve_texture_sol`, `ve_mat_org_sol`, `syntaxon`) \ |
SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire, \ |
bf.catminat_code, bf.ve_lumiere, bf.ve_temperature, bf.ve_continentalite, bf.ve_humidite_atmos, bf.ve_humidite_edaph, \ |
bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxon |
FROM `BASESOURCE`.`bdtfx_v1_01` b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \ |
LEFT JOIN T_basevegflor bf ON (b.num_taxonomique = bf.num_taxon AND b.num_nom = bf.num_nomen); |
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_tax IS NOT NULL AND b.num_tax != '') |
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \ |
SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.num_tax, b.nom_sci, b.auteur, n.nom_vernaculaire FROM `BASESOURCE`.`bdtxa_v1_00` b LEFT JOIN T_nva_v2013_06 n ON (b.num_tax = n.num_taxon); |
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`) \ |
SELECT "isfan", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur FROM `BASESOURCE`.`isfan_v2013` b; |
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`; |
SELECT SUM(theorie.a) AS théorie, pratique.a AS total FROM \ |
(SELECT COUNT(1) AS a FROM `BASESOURCE`.`bdtfx_v1_01` UNION ALL \ |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`bdtxa_v1_00` UNION ALL \ |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`isfan_v2013` \ |
) AS theorie, \ |
(SELECT COUNT(1) AS a FROM tb_cel.cel_references) AS pratique; |
-- bdtfx+bdtxa+isfan: 141181 (2013/07/23) |
/trunk/scripts/modules/cel/dedup-ordre-201307.sql |
---|
1,6 → 1,6 |
-- suppress dup: |
-- >= 115 au 22/07/2013 |
-- mysql -N <<<"SELECT distinct ce_utilisateur FROM cel_obs GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1;" > ordre-dup.txt |
-- mysql -N <<<"SELECT distinct ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1;" > ordre-dup.txt |
DROP FUNCTION IF EXISTS next_ordre; |
DROP PROCEDURE IF EXISTS ordre_need_update; |
14,7 → 14,7 |
NOT DETERMINISTIC |
BEGIN |
DECLARE c INT; |
SET c = (SELECT MAX(ordre) + 1 FROM tb_cel.cel_obs where ce_utilisateur = s1); |
SET c = (SELECT MAX(ordre) + 1 FROM `BASEEDIT`.`cel_obs` where ce_utilisateur = s1); |
RETURN c; |
END |
| |
22,7 → 22,7 |
CREATE PROCEDURE ordre_need_update(IN _s1 VARCHAR(255), OUT _ordre INT, OUT _c INT, OUT _min_obs INT) |
BEGIN |
SELECT ordre, count(ordre), MIN(id_observation) INTO _ordre, _c, _min_obs FROM tb_cel.cel_obs WHERE ce_utilisateur = _s1 GROUP BY ordre HAVING COUNT(ordre) > 1 LIMIT 1; |
SELECT ordre, count(ordre), MIN(id_observation) INTO _ordre, _c, _min_obs FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur = _s1 GROUP BY ordre HAVING COUNT(ordre) > 1 LIMIT 1; |
END |
| |
-- SELECT ordre_need_update("vincent.vuillermoz@apprenti.isa-lille.fr"); |
34,12 → 34,12 |
-- pour chaque ordre dupliqué |
WHILE @o IS NOT NULL DO |
SELECT CONCAT(" ", @o) as " ordre", @c as "(count/doublons)"; |
-- SELECT id_observation FROM tb_cel.cel_obs WHERE ce_utilisateur = _s1 AND ordre = @o AND id_observation != @minobs; |
-- SELECT id_observation FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur = _s1 AND ordre = @o AND id_observation != @minobs; |
-- pour chaque obs concernée, exceptée la première, on met à jour l'ordre, |
-- en utilisant next_ordre() |
WHILE obs_match != 0 DO |
-- SELECT CONCAT("== do update on", @o); |
UPDATE tb_cel.cel_obs SET ordre = next_ordre(_s1) |
UPDATE `BASEEDIT`.`cel_obs` SET ordre = next_ordre(_s1) |
WHERE ce_utilisateur = _s1 AND ordre = @o AND id_observation != @minobs LIMIT 1; |
SELECT ROW_COUNT() into obs_match; |
-- SELECT @o, obs_match; |
65,7 → 65,7 |
-- temp table |
CREATE TEMPORARY TABLE IF NOT EXISTS _temp_users (ce_utilisateur VARCHAR(255)) ENGINE=MEMORY AS \ |
( SELECT DISTINCT ce_utilisateur FROM cel_obs |
( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` |
GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1 ); |
SELECT COUNT(*) INTO done FROM _temp_users; |
96,4 → 96,4 |
-- clef unique sur (id_utilisateur, ordre) |
-- [mais seulement si on a dédupliqué TOUS les utilisateurs, y compris l'utilisateur '' |
-- à voir aussi: maj-hash-id-obs-migr.sql] |
CREATE UNIQUE INDEX couple_user_ordre ON tb_cel.cel_obs(ce_utilisateur, ordre); |
CREATE UNIQUE INDEX couple_user_ordre ON `BASEEDIT`.`cel_obs`(ce_utilisateur, ordre); |
/trunk/scripts/modules/cel/maj-cleanup-201307.sql |
---|
1,20 → 1,20 |
-- date d'observation dans le futur |
UPDATE cel_obs SET date_observation = NULL WHERE date_observation > now(); |
UPDATE `BASEEDIT`.`cel_obs` SET date_observation = NULL WHERE date_observation > now(); |
-- cleanup |
UPDATE cel_obs SET date_observation = NULL WHERE date_observation = '0000-00-00 00:00:00'; |
UPDATE `BASEEDIT`.`cel_obs` SET date_observation = NULL WHERE date_observation = '0000-00-00 00:00:00'; |
-- cleanup |
UPDATE cel_obs SET latitude = NULL, longitude = NULL WHERE longitude = 0 and latitude = 0; |
UPDATE `BASEEDIT`.`cel_obs` SET latitude = NULL, longitude = NULL WHERE longitude = 0 and latitude = 0; |
-- referentiels: 65800 NULL, 13000 '' |
UPDATE cel_obs SET nom_referentiel = SUBSTRING_INDEX(nom_referentiel, ':', 1); |
UPDATE cel_obs SET nom_referentiel = 'bdtfx' WHERE nom_referentiel IN ('bdtfx_v1','bdnff'); |
UPDATE `BASEEDIT`.`cel_obs` SET nom_referentiel = SUBSTRING_INDEX(nom_referentiel, ':', 1); |
UPDATE `BASEEDIT`.`cel_obs` SET nom_referentiel = 'bdtfx' WHERE nom_referentiel IN ('bdtfx_v1','bdnff'); |
-- pas de raison historique mémorisée à une différence '' vs NULL |
UPDATE cel_obs SET nom_referentiel = NULL where nom_referentiel = ''; |
UPDATE `BASEEDIT`.`cel_obs` SET nom_referentiel = NULL where nom_referentiel = ''; |
-- suppression des infos générées pour les observations dont le nom_sel à été supprimé par l'utilisateur |
-- 3172 |
UPDATE tb_cel.cel_obs c SET |
UPDATE `BASEEDIT`.`cel_obs` c SET |
c.nom_ret = '', |
c.nom_sel_nn = NULL, |
c.nom_ret = NULL, |
24,14 → 24,14 |
WHERE nom_sel = '' OR nom_sel IS NULL; |
-- problème n°1: mauvais référentiel (bdtfx) |
UPDATE tb_cel.cel_obs SET nom_referentiel = 'bdtxa' WHERE lieudit = 'Grenade'; |
UPDATE `BASEEDIT`.`cel_obs` SET nom_referentiel = 'bdtxa' WHERE lieudit = 'Grenade'; |
-- problème n°2: nom_sel NULL, nom_ret NOT NULL: 26 obs |
UPDATE cel_obs SET nom_sel = nom_ret, nom_sel_nn = nom_ret_nn WHERE nom_sel IS NULL AND nom_ret IS NOT NULL; |
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = nom_ret, nom_sel_nn = nom_ret_nn WHERE nom_sel IS NULL AND nom_ret IS NOT NULL; |
-- problème n°3: backslashes + newline: 90 + 217 obs |
UPDATE cel_obs SET commentaire = REPLACE(commentaire, "\n\\\'", "'"); |
UPDATE cel_obs SET commentaire = REPLACE(commentaire, "\\\'", "'"); |
UPDATE `BASEEDIT`.`cel_obs` SET commentaire = REPLACE(commentaire, "\n\\\'", "'"); |
UPDATE `BASEEDIT`.`cel_obs` SET commentaire = REPLACE(commentaire, "\\\'", "'"); |
-- problème n°4: ce_zone_geo inutile: 57802 obs |
UPDATE cel_obs SET ce_zone_geo = NULL WHERE ce_zone_geo = 'INSEE-C:'; |
UPDATE `BASEEDIT`.`cel_obs` SET ce_zone_geo = NULL WHERE ce_zone_geo = 'INSEE-C:'; |
/trunk/scripts/modules/cel/maj-nom-ret.sql |
---|
11,28 → 11,28 |
SELECT * FROM bdtfx_v1_01 WHERE num_nom_retenu = 0; # 3960 |
3) regarder les num_nom_ret orphelins de taxon en BDTFX: |
SELECT * FROM bdtxa_v1_00 WHERE num_nom_retenu = 0; # 0 |
4) regarder les orphelins équivalents dans cel_obs: |
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_01 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0; # 7740 |
4) regarder les orphelins équivalents dans `BASEEDIT`.`cel_obs`: |
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`bdtfx_v1_01` b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0; # 7740 |
Donc ceux dont le nom_ret à été POSTé manuellement et qui matchent le nom_sci de BDTFX : on les conserve. |
Mais les autres, qui ont un nom_ret probablement erroné et un nom_ret_nn à 0, on NULLify les données censées être (correctement) autogénérées ! |
Cela concerne: |
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_01 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0 |
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM cel_obs c, tb_eflore.bdtfx_v1_01 b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ); # 960 |
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`bdtfx_v1_01` b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0 |
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`bdtfx_v1_01` b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ); # 960 |
*/ |
-- D'où la requête : |
UPDATE cel_obs SET nom_sel_nn = NULL, nom_ret = NULL, nom_ret_nn = NULL, nt = NULL, famille = NULL WHERE id_observation IN |
( SELECT id_observation FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_01 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0 |
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM cel_obs c, tb_eflore.bdtfx_v1_01 b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ) ); |
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel_nn = NULL, nom_ret = NULL, nom_ret_nn = NULL, nt = NULL, famille = NULL WHERE id_observation IN |
( SELECT id_observation FROM `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`bdtfx_v1_01` b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0 |
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`bdtfx_v1_01` b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ) ); |
-- TODO |
-- UPDATE cel_obs SET nom_ret_nn = NULL WHERE nom_ret_nn = 0; |
-- UPDATE cel_obs SET nom_sel_nn = NULL WHERE nom_sel_nn = 0; |
-- UPDATE `BASEEDIT`.`cel_obs` SET nom_ret_nn = NULL WHERE nom_ret_nn = 0; |
-- UPDATE `BASEEDIT`.`cel_obs` SET nom_sel_nn = NULL WHERE nom_sel_nn = 0; |
/* |
UPDATE cel_obs SET nom_sel = NULL, nom_sel_nn = NULL, nom_ret = NULL, nom_ret_nn = NULL, nt = NULL, famille = NULL, |
FROM cel_obs |
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = NULL, nom_sel_nn = NULL, nom_ret = NULL, nom_ret_nn = NULL, nt = NULL, famille = NULL, |
FROM `BASEEDIT`.`cel_obs` |
WHERE (nom_sel = '' OR nom_sel IS NULL) AND |
( |
(nom_ret IS NOT NULL AND nom_ret != '') OR |
/trunk/scripts/modules/cel/maj-referentiel-und-201307.sql |
---|
14,7 → 14,7 |
-- obtient le nombre de matches sur nom_sel = nom_sci |
CREATE PROCEDURE getNomSciCount(IN _nom varchar(500), OUT param1 INT) |
BEGIN |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM tb_eflore.bdtfx_v1_01 b WHERE nom_sci = _nom UNION ALL SELECT count(1) FROM tb_eflore.bdtxa_v1_00 a WHERE nom_sci = _nom) AS req; |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM `BASESOURCE`.`bdtfx_v1_01` b WHERE nom_sci = _nom UNION ALL SELECT count(1) FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE nom_sci = _nom) AS req; |
END |
| |
-- retourne les paramètres d'une match |
21,9 → 21,9 |
CREATE PROCEDURE getNomSci(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255)) |
BEGIN |
SELECT * INTO param1, param2, param3, param4, param5 FROM |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_01 b WHERE nom_sci = _nom |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`bdtfx_v1_01` b WHERE nom_sci = _nom |
UNION ALL |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM tb_eflore.bdtxa_v1_00 a WHERE nom_sci = _nom) AS req; |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE nom_sci = _nom) AS req; |
END |
| |
31,7 → 31,7 |
-- quasiment identique à ci-dessus, sauf que nous excluons de la recherche de bdtfx et bdtxa les nom dont le nom d'auteur est '' |
CREATE PROCEDURE getNomSciAuteurCount(IN _nom varchar(500), OUT param1 INT) |
BEGIN |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM tb_eflore.bdtfx_v1_01 b WHERE CONCAT(nom_sci, ' ', auteur) = _nom UNION ALL SELECT count(1) FROM tb_eflore.bdtxa_v1_00 a WHERE CONCAT(nom_sci, ' ', auteur) = _nom) AS req; |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM `BASESOURCE`.`bdtfx_v1_01` b WHERE CONCAT(nom_sci, ' ', auteur) = _nom UNION ALL SELECT count(1) FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE CONCAT(nom_sci, ' ', auteur) = _nom) AS req; |
END |
| |
-- retourne les paramètres d'une match |
38,9 → 38,9 |
CREATE PROCEDURE getNomSciAuteur(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255)) |
BEGIN |
SELECT * INTO param1, param2, param3, param4, param5 FROM |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_01 b WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '' |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`bdtfx_v1_01` b WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '' |
UNION ALL |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM tb_eflore.bdtxa_v1_00 a WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '') AS req; |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '') AS req; |
END |
| |
52,7 → 52,7 |
DECLARE _nom varchar(255) DEFAULT NULL; |
-- la requête principale de sélection des observations à mettre à jour |
DECLARE cur1 CURSOR FOR SELECT id_observation, nom_sel FROM cel_obs WHERE nom_referentiel IS NULL AND nom_sel != '' AND nom_sel IS NOT NULL AND nom_ret_nn IS NULL; -- 78149 |
DECLARE cur1 CURSOR FOR SELECT id_observation, nom_sel FROM `BASEEDIT`.`cel_obs` WHERE nom_referentiel IS NULL AND nom_sel != '' AND nom_sel IS NOT NULL AND nom_ret_nn IS NULL; -- 78149 |
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; |
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; |
65,7 → 65,7 |
IF @a = 1 THEN |
CALL getNomSci(_nom, @_ref, @_nom, @_num_nom, @_num_tax, @_famille); |
SELECT "updb: getNomSci", _id_observation, _nom, '=', @_ref, @_nom, @_num_nom, @_num_tax, @_famille; |
UPDATE tb_cel.cel_obs c SET |
UPDATE `BASEEDIT`.`cel_obs` c SET |
c.nom_referentiel = @_ref, |
c.nom_ret = @_nom, |
c.nom_ret_nn = @_num_nom, |
78,7 → 78,7 |
IF @a = 1 THEN |
CALL getNomSciAuteur(_nom, @_ref, @_nom, @_num_nom, @_num_tax, @_famille); |
SELECT "updb: getNomSciAuteur", _id_observation, _nom, '=', @_ref, @_nom, @_num_nom, @_num_tax, @_famille; |
UPDATE tb_cel.cel_obs c SET |
UPDATE `BASEEDIT`.`cel_obs` c SET |
c.nom_referentiel = @_ref, |
c.nom_ret = @_nom, |
c.nom_ret_nn = @_num_nom, |
/trunk/scripts/modules/cel/redempteur.sql |
---|
4,7 → 4,7 |
Eg: |
SELECT id_observation, nom_sel |
FROM tb_cel.cel_obs |
FROM `BASEEDIT`.`cel_obs` |
WHERE ( |
nom_ret IS NULL or nom_ret = '' |
OR nom_ret_nn IS NULL or nom_ret_nn = 0 |
22,7 → 22,7 |
D'où, les 3621 observations suivantes (2206 nom_sel distincts) |
SELECT id_observation, nom_sel |
FROM tb_cel.cel_obs |
FROM `BASEEDIT`.`cel_obs` |
WHERE ( |
nom_sel IS NOT NULL AND nom_sel != '' |
AND ( |
42,7 → 42,7 |
Et effectuons une jointure sur bdtfx: |
SELECT id_observation, nom_sel, b.num_nom, b.famille |
FROM tb_cel.cel_obs c INNER JOIN tb_eflore.bdtfx_v1_01 b ON (b.nom_sci = c.nom_sel) |
FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`bdtfx_v1_01` b ON (b.nom_sci = c.nom_sel) |
WHERE ( |
nom_sel IS NOT NULL AND nom_sel != '' |
AND ( |
68,7 → 68,7 |
Nous obtenons donc ainsi les 69 observations à mettre à jour: |
SELECT id_observation, nom_sel, nom_ret, nom_ret_nn, nt, c.famille, b.num_nom, b.nom_sci, b.num_taxonomique, b.famille |
FROM tb_cel.cel_obs c INNER JOIN tb_eflore.bdtfx_v1_01 b ON (b.nom_sci = c.nom_sel) |
FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`bdtfx_v1_01` b ON (b.nom_sci = c.nom_sel) |
WHERE ( |
nom_sel IS NOT NULL AND nom_sel != '' |
AND ( |
91,7 → 91,7 |
CREATE TEMPORARY TABLE T_bis ( INDEX(`id_observation`)) AS |
SELECT id_observation, b.num_nom, CONCAT(b.nom_sci, ' ', b.auteur), b.num_taxonomique, b.famille |
FROM tb_cel.cel_obs c, tb_eflore.bdtfx_v1_01 b |
FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`bdtfx_v1_01` b |
WHERE ( |
b.nom_sci = c.nom_sel |
AND nom_sel IS NOT NULL AND nom_sel != '' |
105,7 → 105,7 |
) |
GROUP BY id_observation HAVING count(id_observation) = 1 |
UPDATE tb_cel.cel_obs c, T_bis t SET |
UPDATE `BASEEDIT`.`cel_obs` c, T_bis t SET |
c.nom_ret = t.nom_sci, |
c.nom_ret_nn = t.num_nom, |
c.nt = t.num_taxonomique, |