New file |
0,0 → 1,206 |
<?php |
// declare(encoding='UTF-8'); |
/** |
* Migration des données CEL vers la base 2019 après avoir lancer le script nettoyage |
* |
* Utilisation : |
* - migrer les mots-clés obs : <code>/opt/lamp/bin/php cli.php migration_mots_cles -a obs</code> |
* - migrer les mots-clés images : <code>/opt/lamp/bin/php cli.php migration_mots_cles -a images</code> |
* |
* @category CEL |
* @package Scripts |
* @subpackage Migration : Mots-Clés |
* @author Mathias CHOUET <mathias@tela-botanica.org> |
* @author Jean-Pascal MILCENT <jpm@tela-botanica.org> |
* @author Aurelien PERONNET <aurelien@tela-botanica.org> |
* @license GPL v3 <http://www.gnu.org/licenses/gpl.txt> |
* @license CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt> |
* @copyright 1999-2014 Tela Botanica <accueil@tela-botanica.org> |
*/ |
|
class Migrat2019 extends Script { |
|
private $mode; |
private $bd_new_cel = "tb_new_cel"; |
|
public function __construct($script_nom, $parametres_cli) { |
parent::__construct($script_nom, $parametres_cli); |
$this->bdd = new Bdd(); |
$this->bd_new_cel = Config::get('nettoyage.cel_new'); |
$this->bd_cel = Config::get('nettoyage.cel_nettoye'); |
} |
|
public function executer() { |
$cmd = $this->getParametre('a'); |
$this->mode_verbeux = $this->getParametre('v'); |
|
switch($cmd) { |
case 'tout' : |
$this->migrerObservations(); |
$this->migrerProjet(); |
$this->migrerObsEtendusChampsUtilisateur(); |
$this->migrerObsEtendusUtilisateur(); |
$this->migrerObsEtendusProjet(); |
$this->migrerMotsClesObs(); |
$this->migrerMotsClesObsLiaison(); |
$this->migrerImages(); |
$this->migrerMotsClesImages(); |
$this->migrerMotsClesImagesLiaison(); |
$this->ajouterIdProjet(); |
break; |
case 'obs': |
$this->migrerObservations(); |
break; |
case 'projet': |
$this->migrerProjet(); |
break; |
case 'obs_etendus': |
$this->migrerObsEtendusChampsUtilisateur(); |
$this->migrerObsEtendusUtilisateur(); |
$this->migrerObsEtendusProjet(); |
break; |
case 'obs_mots_cles': |
$this->migrerMotsClesObs(); |
$this->migrerMotsClesObsLiaison(); |
break; |
case 'images': |
$this->migrerImages(); |
break; |
case 'images_tag': |
$this->migrerMotsClesImages(); |
$this->migrerMotsClesImagesLiaison(); |
break; |
case 'projet_id': |
$this->ajouterIdProjet(); |
break; |
default: |
echo 'Méthode inconnue, les méthodes possibles sont obs et images'."\n"; |
} |
} |
|
private function migrerObservations() { |
$requete = "ALTER TABLE ".$this->bd_new_cel.".`occurrence` ADD IF NOT EXISTS project varchar(50);"; |
$this->bdd->requeter($requete); |
|
$requete = "INSERT INTO ".$this->bd_new_cel.".`occurrence` |
(id, project, user_id, user_email, user_pseudo, date_observed, date_created, date_updated, date_published, user_sci_name, user_sci_name_id, accepted_sci_name, accepted_sci_name_id, family, certainty, annotation, coef, phenology, input_source, is_public, is_visible_in_cel, geometry, elevation, geodatum, locality, locality_insee_code, sublocality, environment, locality_consistency, station, published_location, osm_country, taxo_repo) |
SELECT id_observation, CASE WHEN `mots_cles_texte` like '%sauvages%' then 'sauvages' WHEN `mots_cles_texte` like '%missions-flore%' then 'missions-flore' WHEN `mots_cles_texte` like '%arbres-tetards%' then 'arbres-tetards' WHEN `mots_cles_texte` like '%arbres-remarquables%' then 'arbres-remarquables' WHEN `mots_cles_texte` like '%bellesdemarue%' then 'bellesdemarue' WHEN `mots_cles_texte` like '%biodiversite34%' then 'biodiversite34' WHEN `mots_cles_texte` like '%messicoles%' then 'messicoles' WHEN `mots_cles_texte` like '%florileges%' then 'florileges' END, |
ce_utilisateur, courriel_utilisateur, concat (prenom_utilisateur, ' ', nom_utilisateur), date_observation, date_creation, date_modification, date_transmission, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, famille, certitude, commentaire, abondance, phenologie, input_source, transmission, 1, |
CONCAT('{\"type\":\"Point\",\"coordinates\":[', longitude, ',', latitude,']}'), altitude, geodatum, zone_geo, ce_zone_geo, lieudit, milieu, locality_consistency, station, published_location, pays, nom_referentiel |
FROM ".$this->bd_cel.".`cel_obs`"; |
|
$this->bdd->requeter($requete); |
} |
|
private function migrerObsEtendusChampsUtilisateur() { |
$requete = "ALTER TABLE ".$this->bd_new_cel.".`user_custom_field` ADD IF NOT EXISTS `field_id` VARCHAR(500) NOT NULL AFTER `default_value`;"; |
$this->bdd->requeter($requete); |
$requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field` |
(name, data_type, default_value, field_id) |
SELECT label, 'Texte', '', cle |
FROM ".$this->bd_cel.".`cel_catalogue_champs_etendus` |
WHERE `cle` not in (SELECT `champ` FROM ".$this->bd_cel.".`cel_catalogue_champs_etendus_liaison`)"; |
$this->bdd->requeter($requete); |
} |
|
private function migrerObsEtendusUtilisateur() { |
$requete = "INSERT INTO ".$this->bd_new_cel.".`user_custom_field_occurrence` |
(`occurrence_id`, `user_custom_field_id`, `value`) |
SELECT `id_observation`, id, `valeur` |
FROM ".$this->bd_cel.".`cel_obs_etendues` |
RIGHT JOIN ".$this->bd_new_cel.".`user_custom_field` ON field_id = cle |
WHERE id_observation in (select id FROM ".$this->bd_new_cel.".`occurrence`)"; |
$this->bdd->requeter($requete); |
} |
|
|
private function migrerObsEtendusProjet() { |
$requete = "INSERT INTO ".$this->bd_new_cel.".`extended_field_occurrence` |
(`occurrence_id`, `extended_field_id`, `value`) |
SELECT `id_observation`, id, `valeur` |
FROM ".$this->bd_cel.".`cel_obs_etendues` |
RIGHT JOIN ".$this->bd_new_cel.".`extended_field` ON field_id = cle |
WHERE id_observation in (select id FROM ".$this->bd_new_cel.".`occurrence`)"; |
$this->bdd->requeter($requete); |
} |
|
private function migrerProjet() { |
$requete = "INSERT INTO ".$this->bd_new_cel.".`project_settings` (`id`, `project_id`, `project`, `language`, `title`, `logo`, `description`, `type`, `is_type`, `css_style`, `image_font`, `date_created`, `date_updated`, `taxo_restriction_type`, `taxo_restriction_value`, `location_type`, `location`, `published_location`, `environment`, `project_tag_name`, `info`) |
SELECT * FROM ".$this->bd_cel.".`project_settings`"; |
$this->bdd->requeter($requete); |
$requete = "INSERT INTO ".$this->bd_new_cel.".`extended_field` (`id`, `project_id`, `field_id`, `project`, `data_type`, `is_visible`, `is_mandatory`, `min_value`, `max_value`, `regexp`, `unit`) |
SELECT * FROM ".$this->bd_cel.".`extended_field`"; |
$this->bdd->requeter($requete); |
$requete = "INSERT INTO ".$this->bd_new_cel.".`extendedfield_translation`(`id`, `extended_field_id`, `project`, `label`, `description`, `default_value`, `error_message`, `language_iso_code`, `help`) |
SELECT * FROM ".$this->bd_cel.".`extendedfield_translation`"; |
$this->bdd->requeter($requete); |
} |
|
private function migrerMotsClesObs() { |
$requete = "INSERT INTO ".$this->bd_new_cel.".user_occurrence_tag |
(id, user_id, name, path) |
SELECT id_mot_cle, id_utilisateur, mot_cle, chemin |
FROM ".$this->bd_cel.".`cel_arbre_mots_cles_obs`"; |
$this->bdd->requeter($requete); |
} |
|
private function migrerMotsClesObsLiaison() { |
$requete = "INSERT INTO ".$this->bd_new_cel.".occurrence_user_occurrence_tag |
(occurrence_id, user_occurrence_tag_id) |
SELECT `id_element_lie`, `id_mot_cle` FROM ".$this->bd_cel.".`cel_mots_cles_obs_liaison`"; |
$this->bdd->requeter($requete); |
} |
|
private function migrerImages() { |
$requete = "INSERT INTO ".$this->bd_new_cel.".photo |
(id, occurrence_id, user_id, user_pseudo, user_email, date_shot, original_name, mime_type, |
date_updated, date_created, date_linked_to_occurrence, content_url, size, url) |
SELECT id_image, ce_observation, ce_utilisateur, prenom_utilisateur, |
courriel_utilisateur, date_prise_de_vue, nom_original,'' , date_modification, |
date_creation, date_liaison, '', '6444444',concat('https://api.tela-botanica.org/img:', |
lpad(`id_image`, 9, '0'), 'O') |
FROM ".$this->bd_cel.".cel_images"; |
$this->bdd->requeter($requete); |
$requete_jpg = 'UPDATE `photo` SET `mime_type` = "image/jpeg", `content_url`= concat('.Config::get('nettoyage.chemin_photo'). |
',substr(lpad(id, 9, "0"),1,3),"/",substr(lpad(id, 9, "0"),4,3),"/O/",substr(lpad(id, 9, "0"),1,3),"_",substr(lpad(id, 9, "0"),4,3),"_",substr(lpad(id, 9, "0"),7,3),"_O",substr(`original_name` , char_length(`original_name`) - locate('.', reverse(`original_name`)) +1, locate('.', reverse(`original_name`)))) |
WHERE `original_name` like "%.jp%" and content_url = ""'; |
$this->bdd->requeter($requete_jpg); |
$requete_png = 'UPDATE `photo` SET `mime_type` = "image/png", `content_url`= concat('.Config::get('nettoyage.chemin_photo'). |
',substr(lpad(id, 9, "0"),1,3),"/",substr(lpad(id, 9, "0"),4,3),"/O/",substr(lpad(id, 9, "0"),1,3),"_",substr(lpad(id, 9, "0"),4,3),"_",substr(lpad(id, 9, "0"),7,3),"_O",substr(`original_name` , char_length(`original_name`) - locate('.', reverse(`original_name`)) +1, locate('.', reverse(`original_name`)))) |
WHERE `original_name` like "%.png" and content_url = ""'; |
$this->bdd->requeter($requete_png); |
'UPDATE `photo` SET `mime_type` = "image/jpeg", `content_url`= concat('.Config::get('nettoyage.chemin_photo'). |
',substr(lpad(id, 9, "0"),1,3),"/",substr(lpad(id, 9, "0"),4,3),"/O/",substr(lpad(id, 9, "0"),1,3),"_",substr(lpad(id, 9, "0"),4,3),"_",substr(lpad(id, 9, "0"),7,3),"_O.jpg") |
WHERE content_url = "" and (`original_name` != "" or `original_name` is null)'; |
} |
|
private function migrerMotsClesImages() { |
$requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag |
(id, user_id, name, path) |
SELECT id_mot_cle, id_utilisateur, mot_cle, chemin |
FROM ".$this->bd_cel.".`cel_arbre_mots_cles_images`"; |
$this->bdd->requeter($requete); |
} |
|
private function migrerMotsClesImagesLiaison() { |
$requete = "INSERT INTO ".$this->bd_new_cel.".photo_tag_photo |
(photo_id, photo_tag_id) |
SELECT `id_element_lie`, `id_mot_cle` FROM ".$this->bd_cel.".`cel_mots_cles_images_liaison`"; |
$this->bdd->requeter($requete); |
} |
|
private function ajouterIdProjet() { |
$requete = "UPDATE ".$this->bd_new_cel.".`project_settings` SET `project_id` = `id`"; |
$this->bdd->requeter($requete); |
$requete = "INSERT INTO ".$this->bd_new_cel.".`tb_project`(`id`, `label`, `is_private`) SELECT `id`, `project`, '0' FROM ".$this->bd_new_cel.".`project_settings` "; |
$this->bdd->requeter($requete); |
$requete = "UPDATE ".$this->bd_new_cel.".`extended_field` f right join ".$this->bd_new_cel.".`project_settings` s on |
s.`project` = f.`project` SET f.`project_id` = s.`project_id`"; |
$this->bdd->requeter($requete); |
$requete = "UPDATE ".$this->bd_new_cel.".`occurrence` o right join ".$this->bd_new_cel.".`project_settings` s on |
s.`project` = o.`project` SET o.`project_id` = s.`project_id`"; |
$this->bdd->requeter($requete); |
} |
|
|
} |