| /trunk/tapirlink/bdd/legumino_vue.sql |
|---|
| New file |
| 0,0 → 1,24 |
| CREATE ALGORITHM=UNDEFINED DEFINER=`legumino`@`localhost` SQL SECURITY DEFINER VIEW `legumino_tapir` AS |
| SELECT |
| concat(_utf8'urn:lsid:tela-botanica.org:legumino:', c.id_contributions) AS guid, |
| c.date AS date_observation, |
| c.commentaire AS commentaire, |
| c.date_saisie AS date_modification, |
| SUBSTRING(cm.code_insee FROM 4) AS commune_code_insee, |
| cm.nom_communes AS commune_nom, |
| cm.latitude AS commune_latitude, |
| cm.longitude AS commune_longitude, |
| l.NumNomen AS num_nomenclatural, |
| l.NumTaxon AS num_taxonomique, |
| l.Famille AS nom_famille, |
| l.Genre AS nom_genre, |
| l.Espece AS nom_espece, |
| l.Auteur AS nom_auteur, |
| `i`.`num_nom_sel` AS `num_nom_sel`,`i`.`famille` AS `famille`,`l`.`code` AS `code`,`l`.`name` AS `name`,`i`.`date_observation` AS `date_observation`,`i`.`identifiant` AS `identifiant`,concat(`l`.`sector`,' ',`l`.`x_utm`,'m E ',`l`.`y_utm`,'m N') AS `coordonnees_bruts`,`l`.`sector` AS `sector`,`l`.`x_utm` AS `x_utm`,`l`.`y_utm` AS `y_utm`,`i`.`date_transmission` AS `date_transmission`,concat(_utf8'<?xml version="1.0" encoding="UTF-8"?>','<info>','<ordre>',`i`.`ordre`,'</ordre>','<num_nom_sel>',`i`.`num_nom_sel`,'</num_nom_sel>','<nom_ret>',`i`.`nom_ret`,'</nom_ret>','<num_nom_ret>',`i`.`num_nom_ret`,'</num_nom_ret>','<num_taxon>',`i`.`num_taxon`,'</num_taxon>','<lieudit>',`i`.`lieudit`,'</lieudit>','<station>',`i`.`station`,'</station>','<milieu>',`i`.`milieu`,'</milieu>','<commentaire>',`i`.`commentaire`,'</commentaire>','<transmission>',`i`.`transmission`,'</transmission>','<date_creation>',`i`.`date_creation`,'</date_creation>','<maj_name>',`l`.`maj_name`,'</maj_name>','<sector>',`l`.`sector`,'</sector>','<update_date>',`l`.`update_date`,'</update_date>','</info>') AS `related_information`,`i`.`num_taxon` AS `num_taxon` |
| FROM (contributions AS c |
| LEFT JOIN communes AS cm ON (c.code_insee = cm.code_insee) |
| LEFT JOIN legumino AS l (c.NumTaxon = l.NumTaxon) |
| LEFT JOIN contributeurs AS ct (c.id_contributeurs = ct.mail) |
| WHERE (`i`.`transmission` = 1); |
| /trunk/tapirlink/bdd/cel_vue.sql |
|---|
| New file |
| 0,0 → 1,22 |
| CREATE ALGORITHM=UNDEFINED DEFINER=`telabotap`@`localhost` SQL SECURITY DEFINER |
| VIEW `cel_tapir` AS |
| SELECT |
| concat(_utf8'urn:lsid:tela-botanica.org:cel:',i.id) AS guid, |
| i.date_modification AS date_modification, |
| i.id AS id, |
| i.nom_sel AS nom_sel, |
| i.num_nom_sel AS num_nom_sel, |
| i.famille AS famille, |
| l.code AS code, |
| l.name AS name, |
| i.date_observation AS date_observation, |
| i.identifiant AS identifiant, |
| concat(l.sector,' ',l.x_utm,'m E ',l.y_utm,'m N') AS coordonnees_bruts, |
| l.sector AS sector, |
| l.x_utm AS x_utm, |
| l.y_utm AS y_utm, |
| i.date_transmission AS date_transmission, |
| concat(_utf8'<?xml version="1.0" encoding="UTF-8"?>','<info>','<ordre>',i.ordre,'</ordre>','<num_nom_sel>',i.num_nom_sel,'</num_nom_sel>','<nom_ret>',i.nom_ret,'</nom_ret>','<num_nom_ret>',i.num_nom_ret,'</num_nom_ret>','<num_taxon>',i.num_taxon,'</num_taxon>','<lieudit>',i.lieudit,'</lieudit>','<station>',i.station,'</station>','<milieu>',i.milieu,'</milieu>','<commentaire>',i.commentaire,'</commentaire>','<transmission>',i.transmission,'</transmission>','<date_creation>',i.date_creation,'</date_creation>','<maj_name>',l.maj_name,'</maj_name>','<sector>',l.sector,'</sector>','<update_date>',l.update_date,'</update_date>','</info>') AS related_information, |
| i.num_taxon AS num_taxon |
| FROM (cel_inventory AS i LEFT JOIN locations AS l ON (((i.location = l.name) AND (i.id_location = l.code)))) |
| WHERE (i.transmission = 1); |