Subversion Repositories eFlore/Applications.cel

Compare Revisions

Ignore whitespace Rev 2484 → Rev 2485

/branches/v2.8-houe/doc/bdd/2014-09_migration_optimisation/maj_optimisation.sql
11,7 → 11,7
ALTER TABLE `cel_images` ADD `transmission` TINYINT(1) NOT NULL DEFAULT '1' AFTER `meta_autres`;
 
-- Ajoute le champ «date_liaison» dans «cel_images»
ALTER TABLE `cel_images` ADD `date_liaison` DATETIME NOT NULL AFTER `date_creation`;
ALTER TABLE `cel_images` ADD `date_liaison` DATETIME NULL AFTER `date_creation`;
 
-- Ajoute le champ «date_transmission» dans «cel_images»
ALTER TABLE `cel_images` ADD `date_transmission` DATETIME NULL AFTER `date_liaison`;
52,7 → 52,8
ALTER TABLE cel_obs DROP INDEX id_obs;
 
-- Correction du champ ce_utilisateur pour la table cel_images
UPDATE cel_obs SET ce_utilisateur = NULL WHERE ce_utilisateur LIKE '%@%' OR ce_utilisateur = '';
UPDATE cel_obs SET ce_utilisateur = NULL
WHERE ce_utilisateur LIKE '%@%' OR ce_utilisateur = '';
 
-- Correction index id_obs
CREATE UNIQUE INDEX id_obs ON cel_obs (ordre, ce_utilisateur, courriel_utilisateur);
62,6 → 63,7
CREATE INDEX utilisateur_date_creation ON cel_obs (ce_utilisateur, date_creation, id_observation);
 
-- -----------------------------------------------------
-- CORRECTIONS des CHAMPS ce_utilisateur & INDEX
 
-- Correction données en erreurs (bloque la génération des index)
START TRANSACTION;
138,10 → 140,56
IFNULL(co.date_transmission, '0000-00-00 00:00:00'),
IFNULL(co.date_modification, '0000-00-00 00:00:00'),
IFNULL(ci.date_liaison, '0000-00-00 00:00:00')
)
)
WHERE co.id_observation = ci.ce_observation ;
COMMIT;
 
-- -----------------------------------------------------
-- CORRECTIONS des DATES
 
ALTER TABLE cel_obs CHANGE date_creation date_creation DATETIME NOT NULL;
 
ALTER TABLE cel_obs CHANGE date_modification date_modification DATETIME NOT NULL;
 
START TRANSACTION;
UPDATE cel_obs AS co, cel_images AS ci
SET co.date_creation = LEAST(
IFNULL(co.date_modification, '0000-00-00 00:00:00'),
IFNULL(co.date_transmission, '0000-00-00 00:00:00'),
IFNULL(ci.date_creation, '0000-00-00 00:00:00'),
IFNULL(ci.date_modification, '0000-00-00 00:00:00'),
IFNULL(ci.date_transmission, '0000-00-00 00:00:00'),
IFNULL(ci.date_liaison, '0000-00-00 00:00:00')
)
WHERE co.id_observation = ci.ce_observation
AND co.date_creation = '0000-00-00 00:00:00' OR co.date_creation = NULL;
COMMIT;
 
START TRANSACTION;
UPDATE cel_images
SET date_creation = LEAST(
IFNULL(date_modification, '0000-00-00 00:00:00'),
IFNULL(date_transmission, '0000-00-00 00:00:00'),
IFNULL(date_liaison, '0000-00-00 00:00:00')
)
WHERE date_creation = '0000-00-00 00:00:00' OR date_creation = NULL;
COMMIT;
 
START TRANSACTION;
UPDATE cel_images AS ci, cel_obs AS co
SET ci.date_creation = LEAST(
IFNULL(ci.date_modification, '0000-00-00 00:00:00'),
IFNULL(ci.date_transmission, '0000-00-00 00:00:00'),
IFNULL(ci.date_liaison, '0000-00-00 00:00:00'),
IFNULL(co.date_creation, '0000-00-00 00:00:00'),
IFNULL(co.date_modification, '0000-00-00 00:00:00'),
IFNULL(co.date_transmission, '0000-00-00 00:00:00')
)
WHERE ci.ce_observation = co.id_observation
AND ci.date_creation = '0000-00-00 00:00:00' OR ci.date_creation = NULL;
COMMIT;
 
-- -----------------------------------------------------
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;