text: DROP VIEW IF EXISTS `data_overview`;
  
  DROP VIEW IF EXISTS `view_mcpd`;
  # CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_mcpd` AS select `g`.`id` AS `ID`,`g`.`puid` AS `PUID`,`institutions`.`code` AS `INSTCODE`,`g`.`general_identifier` AS `ACCENUMB`,`g`.`collnumb` AS `COLLNUMB`,`g`.`collcode` AS `COLLCODE`,`g`.`collname` AS `COLLNAME`,`institutions`.`address` AS `COLLINSTADDRESS`,`g`.`collmissid` AS `COLLMISSID`,`taxonomies`.`genus` AS `GENUS`,`taxonomies`.`species` AS `SPECIES`,`taxonomies`.`species_author` AS `SPAUTHOR`,`taxonomies`.`subtaxa` AS `SUBTAXA`,`taxonomies`.`subtaxa_author` AS `SUBTAUTHOR`,`taxonomies`.`cropname` AS `CROPNAME`,`g`.`number` AS `ACCENAME`,replace(`g`.`acqdate`,'-','') AS `ACQDATE`,`countries`.`country_code3` AS `ORIGCTY`,`locations`.`site_name` AS `COLLSITE`,`locations`.`latitude` AS `DECLATITUDE`,NULL AS `LATITUDE`,`locations`.`longitude` AS `DECLONGITUDE`,NULL AS `LONGITUDE`,`locations`.`coordinate_uncertainty` AS `COORDUNCERT`,`locations`.`coordinate_datum` AS `COORDDATUM`,`locations`.`georeferencing_method` AS `GEOREFMETH`,`locations`.`elevation` AS `ELEVATION`,replace(`g`.`colldate`,'-','') AS `COLLDATE`,`g`.`breeders_code` AS `BREDCODE`,`g`.`breeders_name` AS `BREDNAME`,`g`.`biologicalstatus_id` AS `SAMPSTAT`,`pedigreedefinitions`.`definition` AS `ANCEST`,`g`.`collsrc_id` AS `COLLSRC`,`g`.`donor_code` AS `DONORCODE`,`g`.`donor_name` AS `DONORNAME`,`g`.`donor_number` AS `DONORNUMB`,`g`.`othernumb` AS `OTHERNUMB`,`g`.`duplsite` AS `DUPLSITE`,`g`.`duplinstname` AS `DUPLINSTNAME`,group_concat(`storage`.`description` separator ',') AS `STORAGE`,`g`.`mlsstatus_id` AS `MLSSTAT`,(select `attributedata`.`value` from (`attributedata` left join `attributes` on((`attributes`.`id` = `attributedata`.`attribute_id`))) where ((`attributes`.`target_table` = 'germinatebase') and (`attributes`.`name` = 'Remarks') and (`attributedata`.`foreign_id` = `g`.`id`)) limit 1) AS `REMARKS`,`entitytypes`.`name` AS `ENTITYTYPE`,(select `p`.`id` from `germinatebase` `p` where (`p`.`id` = `g`.`entityparent_id`)) AS `ENTITYPARENTID`,(select `p`.`general_identifier` from `germinatebase` `p` where (`p`.`id` = `g`.`entityparent_id`)) AS `ENTITYPARENTACCENUMB` from (((((((((`germinatebase` `g` left join `taxonomies` on((`taxonomies`.`id` = `g`.`taxonomy_id`))) left join `locations` on((`locations`.`id` = `g`.`location_id`))) left join `countries` on((`countries`.`id` = `locations`.`country_id`))) left join `institutions` on((`institutions`.`id` = `g`.`institution_id`))) left join `pedigreedefinitions` on((`pedigreedefinitions`.`germinatebase_id` = `g`.`id`))) left join `storagedata` on((`storagedata`.`germinatebase_id` = `g`.`id`))) left join `storage` on((`storage`.`id` = `storagedata`.`storage_id`))) left join `attributedata` on((`attributedata`.`foreign_id` = `g`.`id`))) left join `entitytypes` on((`entitytypes`.`id` = `g`.`entitytype_id`))) group by `g`.`id`,`pedigreedefinitions`.`id`;
  
  DROP VIEW IF EXISTS `view_table_germplasm`;
  DROP VIEW IF EXISTS `view_table_germplasm_deprecated`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_germplasm_deprecated` AS select `germinatebase`.`name` AS `germplasm_name`,`germinatebase`.`id` AS `germplasm_id`,`germinatebase`.`general_identifier` AS `germplasm_gid`,`germinatebase`.`number` AS `germplasm_number`,`germinatebase`.`puid` AS `germplasm_puid`,`entitytypes`.`id` AS `entity_type_id`,`entitytypes`.`name` AS `entity_type_name`,`germinatebase`.`entityparent_id` AS `entity_parent_id`,`g`.`name` AS `entity_parent_name`,`g`.`general_identifier` AS `entity_parent_general_identifier`,`biologicalstatus`.`id` AS `biological_status_id`,`biologicalstatus`.`sampstat` AS `biological_status_name`,`synonyms`.`synonyms` AS `synonyms`,`germinatebase`.`collnumb` AS `collector_number`,`taxonomies`.`genus` AS `genus`,`taxonomies`.`species` AS `species`,`taxonomies`.`subtaxa` AS `subtaxa`,`institutions`.`id` AS `institution_id`,`institutions`.`name` AS `institution_name`,`locations`.`site_name` AS `location`,`locations`.`latitude` AS `latitude`,`locations`.`longitude` AS `longitude`,`locations`.`elevation` AS `elevation`,`countries`.`country_name` AS `country_name`,`countries`.`country_code2` AS `country_code`,`germinatebase`.`colldate` AS `coll_date`,`germinatebase`.`pdci` AS `pdci`,(select count(1) from (`images` left join `imagetypes` on((`imagetypes`.`id` = `images`.`imagetype_id`))) where ((`imagetypes`.`reference_table` = 'germinatebase') and (`images`.`foreign_id` = `germinatebase`.`id`))) AS `image_count`,(select `images`.`path` from (`images` left join `imagetypes` on((`imagetypes`.`id` = `images`.`imagetype_id`))) where ((`imagetypes`.`reference_table` = 'germinatebase') and (`images`.`foreign_id` = `germinatebase`.`id`)) limit 1) AS `first_image_path`,(select 1 from `phenotypedata` where (`phenotypedata`.`germinatebase_id` = `germinatebase`.`id`) limit 1) AS `has_trials_data`,(select 1 from (`datasetmembers` left join `datasets` on((`datasets`.`id` = `datasetmembers`.`dataset_id`))) where ((`datasetmembers`.`foreign_id` = `germinatebase`.`id`) and (`datasetmembers`.`datasetmembertype_id` = 2) and (`datasets`.`datasettype_id` = 1)) limit 1) AS `has_genotypic_data`,(select 1 from (`datasetmembers` left join `datasets` on((`datasets`.`id` = `datasetmembers`.`dataset_id`))) where ((`datasetmembers`.`foreign_id` = `germinatebase`.`id`) and (`datasetmembers`.`datasetmembertype_id` = 2) and (`datasets`.`datasettype_id` = 4)) limit 1) AS `has_allelefreq_data`,(select 1 from `compounddata` where (`compounddata`.`germinatebase_id` = `germinatebase`.`id`) limit 1) AS `has_compound_data` from ((((((((`germinatebase` left join `germinatebase` `g` on((`g`.`id` = `germinatebase`.`entityparent_id`))) left join `institutions` on((`institutions`.`id` = `germinatebase`.`institution_id`))) left join `entitytypes` on((`germinatebase`.`entitytype_id` = `entitytypes`.`id`))) left join `taxonomies` on((`germinatebase`.`taxonomy_id` = `taxonomies`.`id`))) left join `locations` on((`germinatebase`.`location_id` = `locations`.`id`))) left join `countries` on((`locations`.`country_id` = `countries`.`id`))) left join `biologicalstatus` on((`biologicalstatus`.`id` = `germinatebase`.`biologicalstatus_id`))) left join `synonyms` on(((`synonyms`.`foreign_id` = `germinatebase`.`id`) and (`synonyms`.`synonymtype_id` = 1))));
  
  DROP VIEW IF EXISTS `view_table_maps`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_maps` AS select `maps`.`id` AS `map_id`,`maps`.`name` AS `map_name`,`maps`.`description` AS `map_description`,`maps`.`user_id` AS `user_id`,`maps`.`visibility` AS `visibility`,count(1) AS `marker_count` from (`maps` left join `mapdefinitions` on((`maps`.`id` = `mapdefinitions`.`map_id`))) group by `maps`.`id`;
  
  DROP VIEW IF EXISTS `view_table_mapdefinitions`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_mapdefinitions` AS select `markers`.`id` AS `marker_id`,`markers`.`marker_name` AS `marker_name`,`synonyms`.`synonyms` AS `synonyms`,`mapfeaturetypes`.`description` AS `map_feature_type`,`maps`.`id` AS `map_id`,`maps`.`user_id` AS `user_id`,`maps`.`visibility` AS `visibility`,`maps`.`name` AS `map_name`,`mapdefinitions`.`chromosome` AS `chromosome`,`mapdefinitions`.`definition_start` AS `position` from ((((`markers` left join `mapdefinitions` on((`markers`.`id` = `mapdefinitions`.`marker_id`))) left join `mapfeaturetypes` on((`mapfeaturetypes`.`id` = `mapdefinitions`.`mapfeaturetype_id`))) left join `maps` on((`maps`.`id` = `mapdefinitions`.`map_id`))) left join `synonyms` on(((`synonyms`.`foreign_id` = `markers`.`id`) and (`synonyms`.`synonymtype_id` = 2))));
  
  DROP VIEW IF EXISTS `view_table_datasets`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_datasets` AS SELECT `datasets`.`id` AS `dataset_id`, `datasets`.`name` AS `dataset_name`, `datasets`.`description` AS `dataset_description`, `datasets`.`hyperlink` AS `hyperlink`, `datasets`.`source_file` AS `source_file`, `datasets`.`version` AS `version`, `datasettypes`.`description` AS `dataset_type`, `experiments`.`id` AS `experiment_id`, `experiments`.`experiment_name` AS `experiment_name`, `experiments`.`description` AS `experiment_description`, `datasets`.`datatype` AS `datatype`, `datasetstates`.`name` AS `dataset_state`,( SELECT json_arrayagg( json_object( 'locationId', `locations`.`id`, 'locationName', `locations`.`site_name`, 'locationLatitude', `locations`.`latitude`, 'locationLongitude', `locations`.`longitude`, 'countryId', `countries`.`id`, 'countryName', `countries`.`country_code2` )) FROM `datasetlocations` LEFT JOIN `locations` ON `locations`.`id` = `datasetlocations`.`location_id` LEFT JOIN `countries` ON `countries`.`id` = `locations`.`country_id` WHERE `datasetlocations`.`dataset_id` = `datasets`.`id` AND `locations`.`id` IS NOT NULL GROUP BY `datasets`.`id` ) AS `locations`, `licenses`.`id` AS `license_id`, `licenses`.`name` AS `license_name`, `datasets`.`contact` AS `contact`, `datasets`.`date_start` AS `start_date`, `datasets`.`date_end` AS `end_date`, `datasets`.`dublin_core` AS `dublin_core`, `datasets`.`created_on` AS `created_on`, `datasets`.`updated_on` AS `updated_on`, `datasetmeta`.`nr_of_data_objects` AS `data_object_count`, `datasetmeta`.`nr_of_data_points` AS `data_point_count`, `datasets`.`is_external` AS `is_external`,( SELECT count( 1 ) FROM `publicationdata` WHERE `publicationdata`.`foreign_id` = `datasets`.`id` AND `publicationdata`.`reference_type` = 'dataset' ) AS `publications`, ( SELECT count( 1 ) FROM `collaborators` LEFT JOIN `datasetcollaborators` ON `collaborators`.`id` = `datasetcollaborators`.`collaborator_id` WHERE `datasetcollaborators`.`dataset_id` = `datasets`.`id` ) AS `collaborators`,( SELECT count( 1 ) FROM `attributes` LEFT JOIN `attributedata` ON `attributedata`.`attribute_id` = `attributes`.`id` WHERE `attributes`.`target_table` = 'datasets' AND `attributedata`.`foreign_id` = `datasets`.`id` ) AS `attributes`, json_arrayagg( `licenselogs`.`user_id` ) AS `accepted_by` FROM `datasets` LEFT JOIN `experiments` ON `experiments`.`id` = `datasets`.`experiment_id` LEFT JOIN `datasettypes` ON `datasettypes`.`id` = `datasets`.`datasettype_id` LEFT JOIN `licenses` ON `licenses`.`id` = `datasets`.`license_id` LEFT JOIN `datasetmeta` ON `datasetmeta`.`dataset_id` = `datasets`.`id` LEFT JOIN `datasetstates` ON `datasetstates`.`id` = `datasets`.`dataset_state_id` LEFT JOIN `licenselogs` ON `licenselogs`.`license_id` = `licenses`.`id` GROUP BY `datasets`.`id`, `datasetmeta`.`id`;
  
  DROP VIEW IF EXISTS `view_table_experiments`;
  # CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_experiments` AS SELECT `experiments`.`id` AS `experiment_id`, `experiments`.`experiment_name` AS `experiment_name`, `experiments`.`description` AS `experiment_description`, `experiments`.`experiment_date` AS `experiment_date`, `experiments`.`created_on` AS `created_on`, (SELECT count( 1 ) FROM `datasets` WHERE `datasets`.`experiment_id` = `experiments`.`id` AND `datasets`.`datasettype_id` = 1 AND `datasets`.`is_external` = 0) AS `genotype_count`, (SELECT count( 1 ) FROM `datasets` WHERE `datasets`.`experiment_id` = `experiments`.`id` AND `datasets`.`datasettype_id` = 3 AND `datasets`.`is_external` = 0) AS `trials_count`, (SELECT count( 1 ) FROM `datasets` WHERE `datasets`.`experiment_id` = `experiments`.`id` AND `datasets`.`datasettype_id` = 4 AND `datasets`.`is_external` = 0) AS `allele_freq_count`, (SELECT count( 1 ) FROM `datasets` WHERE `datasets`.`experiment_id` = `experiments`.`id` AND `datasets`.`datasettype_id` = 5 AND `datasets`.`is_external` = 0) AS `climate_count`, (SELECT count( 1 ) FROM `datasets` WHERE `datasets`.`experiment_id` = `experiments`.`id` AND `datasets`.`datasettype_id` = 6 AND `datasets`.`is_external` = 0) AS `compound_count`, (SELECT count( 1 ) FROM `datasets` WHERE `datasets`.`experiment_id` = `experiments`.`id` AND `datasets`.`datasettype_id` = 7 AND `datasets`.`is_external` = 0) AS `pedigree_count` FROM `experiments`;
  
  DROP VIEW IF EXISTS `view_table_licenses`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_licenses` AS select `licenses`.`id` AS `license_id`,`licenses`.`name` AS `license_name`,`licenses`.`description` AS `license_description`,`licensedata`.`content` AS `license_content`,`locales`.`name` AS `locale_name`,`locales`.`description` AS `locale_description`,`datasets`.`id` AS `dataset_id`,JSON_ARRAYAGG(`licenselogs`.`user_id`) AS `accepted_by` from ((((`licenses` left join `licensedata` on((`licensedata`.`license_id` = `licenses`.`id`))) left join `locales` on((`locales`.`id` = `licensedata`.`locale_id`))) left join `datasets` on((`datasets`.`license_id` = `licenses`.`id`))) left join `licenselogs` on((`licenselogs`.`license_id` = `licenses`.`id`))) group by `licenses`.`id`,`locales`.`id`,`licensedata`.`id`,`datasets`.`id`;
  
  DROP VIEW IF EXISTS `view_stats_overview`;
  # CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_stats_overview` AS SELECT ( SELECT count( 1 ) FROM `germinatebase` ) AS `germplasm`, ( SELECT count( 1 ) FROM `markers` ) AS `markers`, ( SELECT count( 1 ) FROM `maps` WHERE maps.visibility = 1 ) AS `maps`, ( SELECT count( 1 ) FROM `phenotypes` ) AS `traits`, ( SELECT count( 1 ) FROM `climates` ) AS `climates`, ( SELECT count( 1 ) FROM `compounds` ) AS `compounds`, ( SELECT count( 1 ) FROM `locations` ) AS `locations`, ( SELECT count( 1 ) FROM `datasets` WHERE ( `datasets`.`dataset_state_id` = 1 )) AS `datasets`, ( SELECT count( 1 ) FROM `datasets` WHERE ( `datasets`.`dataset_state_id` = 1 AND `datasets`.`datasettype_id` = 1 )) AS `datasets_genotype`, ( SELECT count( 1 ) FROM `datasets` WHERE ( `datasets`.`dataset_state_id` = 1 AND `datasets`.`datasettype_id` = 3 )) AS `datasets_trials`, ( SELECT count( 1 ) FROM `datasets` WHERE ( `datasets`.`dataset_state_id` = 1 AND `datasets`.`datasettype_id` = 4 )) AS `datasets_allelefreq`, ( SELECT count( 1 ) FROM `datasets` WHERE ( `datasets`.`dataset_state_id` = 1 AND `datasets`.`datasettype_id` = 5 )) AS `datasets_climate`, ( SELECT count( 1 ) FROM `datasets` WHERE ( `datasets`.`dataset_state_id` = 1 AND `datasets`.`datasettype_id` = 6 )) AS `datasets_compound`, ( SELECT count( 1 ) FROM `experiments` ) AS `experiments`, ( SELECT count( 1 ) FROM `groups` WHERE ( `groups`.`visibility` = 1 )) AS `groups`, ( SELECT count( 1 ) FROM `images` ) AS `images`, ( SELECT count( 1 ) FROM `fileresources` ) AS `fileresources`;
  
  DROP VIEW IF EXISTS `view_stats_taxonomy`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_stats_taxonomy` AS SELECT * FROM ( SELECT `taxonomies`.`genus` AS `genus`, `taxonomies`.`species` AS `species`, `taxonomies`.`subtaxa` AS `subtaxa`, count( 1 ) AS `count` FROM ( `germinatebase` LEFT JOIN `taxonomies` ON (( `taxonomies`.`id` = `germinatebase`.`taxonomy_id` ))) WHERE NOT ISNULL( genus ) AND `germinatebase`.`entitytype_id` = 1 GROUP BY `taxonomies`.`id` UNION SELECT 'N/A' AS `genus`, NULL AS `species`, NULL AS `subtaxa`, count( 1 ) AS `count` FROM ( `germinatebase` LEFT JOIN `taxonomies` ON (( `taxonomies`.`id` = `germinatebase`.`taxonomy_id` ))) WHERE ISNULL( genus ) AND `germinatebase`.`entitytype_id` = 1 GROUP BY `taxonomies`.`id` ) t ORDER BY t.count DESC;
  
  DROP VIEW IF EXISTS `view_stats_biologicalstatus`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_stats_biologicalstatus` AS select substring_index(`biologicalstatus`.`sampstat`,' (',1) AS `biologicalstatus`,(select count(1) from `germinatebase` where (`germinatebase`.`biologicalstatus_id` = `biologicalstatus`.`id`)) AS `count` from `biologicalstatus` order by `count` desc;
  
  DROP VIEW IF EXISTS `view_stats_pdci`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_stats_pdci` AS select '0-1' AS `bin`,count(1) AS `count` from `germinatebase` where ((`germinatebase`.`pdci` >= 0) and (`germinatebase`.`pdci` < 1) and (`germinatebase`.`entitytype_id` = 1)) union select '1-2' AS `bin`,count(1) AS `count` from `germinatebase` where ((`germinatebase`.`pdci` >= 1) and (`germinatebase`.`pdci` < 2) and (`germinatebase`.`entitytype_id` = 1)) union select '2-3' AS `bin`,count(1) AS `count` from `germinatebase` where ((`germinatebase`.`pdci` >= 2) and (`germinatebase`.`pdci` < 3) and (`germinatebase`.`entitytype_id` = 1)) union select '3-4' AS `bin`,count(1) AS `count` from `germinatebase` where ((`germinatebase`.`pdci` >= 3) and (`germinatebase`.`pdci` < 4) and (`germinatebase`.`entitytype_id` = 1)) union select '4-5' AS `bin`,count(1) AS `count` from `germinatebase` where ((`germinatebase`.`pdci` >= 4) and (`germinatebase`.`pdci` < 5) and (`germinatebase`.`entitytype_id` = 1)) union select '5-6' AS `bin`,count(1) AS `count` from `germinatebase` where ((`germinatebase`.`pdci` >= 5) and (`germinatebase`.`pdci` < 6) and (`germinatebase`.`entitytype_id` = 1)) union select '6-7' AS `bin`,count(1) AS `count` from `germinatebase` where ((`germinatebase`.`pdci` >= 6) and (`germinatebase`.`pdci` < 7) and (`germinatebase`.`entitytype_id` = 1)) union select '7-8' AS `bin`,count(1) AS `count` from `germinatebase` where ((`germinatebase`.`pdci` >= 7) and (`germinatebase`.`pdci` < 8) and (`germinatebase`.`entitytype_id` = 1)) union select '8-9' AS `bin`,count(1) AS `count` from `germinatebase` where ((`germinatebase`.`pdci` >= 8) and (`germinatebase`.`pdci` < 9) and (`germinatebase`.`entitytype_id` = 1)) union select '9-10' AS `bin`,count(1) AS `count` from `germinatebase` where ((`germinatebase`.`pdci` >= 9) and (`germinatebase`.`pdci` <= 10) and (`germinatebase`.`entitytype_id` = 1));
  
  DROP VIEW IF EXISTS `view_stats_country`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_stats_country` AS select ifnull(`countries`.`country_name`,'UNKNOWN COUNTRY ORIGIN') AS `country`,ifnull(`countries`.`country_code3`,'UNK') AS `code`,count(1) AS `count` from ((`germinatebase` left join `locations` on((`germinatebase`.`location_id` = `locations`.`id`))) left join `countries` on((`countries`.`id` = `locations`.`country_id`))) where (`germinatebase`.`entitytype_id` = 1) group by `countries`.`id` order by count(1) desc;
  
  DROP VIEW IF EXISTS `view_table_usergroups`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_usergroups` AS select `usergroups`.`id` AS `user_group_id`,`usergroups`.`name` AS `user_group_name`,`usergroups`.`description` AS `user_group_description`,`usergroups`.`created_on` AS `created_on`,count(`usergroupmembers`.`id`) AS `count` from (`usergroups` left join `usergroupmembers` on((`usergroupmembers`.`usergroup_id` = `usergroups`.`id`))) group by `usergroups`.`id`;
  
  DROP VIEW IF EXISTS `view_table_groups`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_groups` AS select `groups`.`id` AS `group_id`,`groups`.`name` AS `group_name`,`groups`.`description` AS `group_description`,`grouptypes`.`id` AS `group_type_id`,`grouptypes`.`target_table` AS `group_type`,'' AS `user_name`,`groups`.`created_by` AS `user_id`,`groups`.`visibility` AS `group_visibility`,`groups`.`created_on` AS `created_on`,`groups`.`updated_on` AS `updated_on`,count(`groupmembers`.`id`) AS `count` from ((`groups` left join `grouptypes` on((`groups`.`grouptype_id` = `grouptypes`.`id`))) left join `groupmembers` on((`groupmembers`.`group_id` = `groups`.`id`))) group by `groups`.`id`;
  
  DROP VIEW IF EXISTS `view_table_markers`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_markers` AS select `markers`.`id` AS `marker_id`,`markers`.`marker_name` AS `marker_name`,`markertypes`.`description` AS `marker_type`,`synonyms`.`synonyms` AS `marker_synonyms`,`markers`.`created_on` AS `created_on`,`markers`.`updated_on` AS `updated_on` from ((`markers` left join `markertypes` on((`markertypes`.`id` = `markers`.`markertype_id`))) left join `synonyms` on(((`synonyms`.`foreign_id` = `markers`.`id`) and (`synonyms`.`synonymtype_id` = 2))));
  
  DROP VIEW IF EXISTS `view_table_locations`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_locations` AS select `locations`.`id` AS `location_id`,`locations`.`site_name` AS `location_name`,`locations`.`site_name_short` AS `location_name_short`,`locations`.`region` AS `location_region`,`locations`.`state` AS `location_state`,`locationtypes`.`name` AS `location_type`,`locations`.`latitude` AS `location_latitude`,`locations`.`longitude` AS `location_longitude`,`locations`.`elevation` AS `location_elevation`,`locations`.`coordinate_uncertainty` AS `location_coordinate_uncertainty`,`countries`.`id` AS `country_id`,`countries`.`country_name` AS `country_name`,`countries`.`country_code2` AS `country_code2`,`countries`.`country_code3` AS `country_code3` from ((`locations` left join `countries` on((`countries`.`id` = `locations`.`country_id`))) left join `locationtypes` on((`locationtypes`.`id` = `locations`.`locationtype_id`)));
  
  DROP VIEW IF EXISTS `view_table_images`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_images` AS select `images`.`image_id` AS `image_id`,`images`.`image_description` AS `image_description`,`images`.`image_foreign_id` AS `image_foreign_id`,`images`.`image_path` AS `image_path`,`images`.`image_type` AS `image_type`,`images`.`image_ref_table` AS `image_ref_table`,`images`.`reference_name` AS `reference_name`,`images`.`created_on` AS `created_on`,(select json_arrayagg(json_object('tagId', `imagetags`.`id`,'tagName', `imagetags`.`tag_name`)) from ( `image_to_tags` left join  `imagetags` on(( `imagetags`.`id` =  `image_to_tags`.`imagetag_id`))) where (( `image_to_tags`.`image_id` = `images`.`image_id`) and ( `image_to_tags`.`imagetag_id` is not null)) group by  `image_to_tags`.`image_id`) AS `tags` from (select  `images`.`id` AS `image_id`, `images`.`description` AS `image_description`, `images`.`foreign_id` AS `image_foreign_id`, `images`.`path` AS `image_path`, `imagetypes`.`description` AS `image_type`, `imagetypes`.`reference_table` AS `image_ref_table`, `germinatebase`.`name` AS `reference_name`, `images`.`created_on` AS `created_on` from (( `images` left join  `imagetypes` on(( `imagetypes`.`id` =  `images`.`imagetype_id`))) left join  `germinatebase` on(( `germinatebase`.`id` =  `images`.`foreign_id`))) where ( `imagetypes`.`reference_table` like 'germinatebase') union select  `images`.`id` AS `image_id`, `images`.`description` AS `image_description`, `images`.`foreign_id` AS `image_foreign_id`, `images`.`path` AS `image_path`, `imagetypes`.`description` AS `image_type`, `imagetypes`.`reference_table` AS `image_ref_table`, `compounds`.`name` AS `reference_name`, `images`.`created_on` AS `created_on` from (( `images` left join  `imagetypes` on(( `imagetypes`.`id` =  `images`.`imagetype_id`))) left join  `compounds` on(( `compounds`.`id` =  `images`.`foreign_id`))) where ( `imagetypes`.`reference_table` like 'compounds') union select  `images`.`id` AS `image_id`, `images`.`description` AS `image_description`, `images`.`foreign_id` AS `image_foreign_id`, `images`.`path` AS `image_path`, `imagetypes`.`description` AS `image_type`, `imagetypes`.`reference_table` AS `image_ref_table`, `phenotypes`.`name` AS `reference_name`, `images`.`created_on` AS `created_on` from (( `images` left join  `imagetypes` on(( `imagetypes`.`id` =  `images`.`imagetype_id`))) left join  `phenotypes` on(( `phenotypes`.`id` =  `images`.`foreign_id`))) where ( `imagetypes`.`reference_table` like 'phenotypes')) `images`;
  
  DROP VIEW IF EXISTS `view_table_traits`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_traits` AS select distinct `phenotypes`.`id` AS `trait_id`,`phenotypes`.`name` AS `trait_name`,`phenotypes`.`short_name` AS `trait_name_short`,`phenotypes`.`description` AS `trait_description`,`phenotypes`.`datatype` AS `data_type`,`phenotypes`.`restrictions` AS `trait_restrictions`,`units`.`id` AS `unit_id`,`units`.`unit_name` AS `unit_name`,`units`.`unit_description` AS `unit_description`,`units`.`unit_abbreviation` AS `unit_abbreviation`,`synonyms`.`synonyms` AS `synonyms`, (SELECT CAST(CONCAT('[', (SELECT GROUP_CONCAT(DISTINCT `dataset_id`) FROM `phenotypedata` WHERE `phenotypedata`.`phenotype_id` = `phenotypes`.`id`) ,']') as json)) AS `dataset_ids`, (select count(1) from `phenotypedata` where (`phenotypedata`.`phenotype_id` = `phenotypes`.`id`)) AS `count` from ((`phenotypes` left join `units` on((`units`.`id` = `phenotypes`.`unit_id`))) left join `synonyms` on(((`synonyms`.`foreign_id` = `phenotypes`.`id`) and (`synonyms`.`synonymtype_id` = 4)))) group by `phenotypes`.`id`,`synonyms`.`id`;
  
  DROP VIEW IF EXISTS `view_table_collaborators`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_collaborators` AS SELECT `collaborators`.`id` AS `collaborator_id`, `collaborators`.`first_name` AS `collaborator_first_name`, `collaborators`.`last_name` AS `collaborator_last_name`, `collaborators`.`external_id` AS `collaborator_external_id`, `collaborators`.`email` AS `collaborator_email`, `collaborators`.`phone` AS `collaborator_phone`, `datasetcollaborators`.`collaborator_roles` AS `collaborator_roles`, `institutions`.`id` AS `institution_id`, `institutions`.`name` AS `institution_name`, `institutions`.`address` AS `institution_address`, `datasets`.`id` AS `dataset_id`, `countries`.`id` AS `country_id`, `countries`.`country_name` AS `country_name`, `countries`.`country_code2` AS `country_code2`, `countries`.`country_code3` AS `country_code3` FROM `collaborators` LEFT JOIN `institutions` ON `institutions`.`id` = `collaborators`.`institution_id` LEFT JOIN `countries` ON `countries`.`id` = `institutions`.`country_id` LEFT JOIN `datasetcollaborators` ON `datasetcollaborators`.`collaborator_id` = `collaborators`.`id` LEFT JOIN `datasets` ON `datasets`.`id` = `datasetcollaborators`.`dataset_id`;
  
  DROP VIEW IF EXISTS `view_table_trials_data`;
  # CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_trials_data` AS select `germinatebase`.`id` AS `germplasm_id`,`germinatebase`.`general_identifier` AS `germplasm_gid`,`germinatebase`.`name` AS `germplasm_name`,`synonyms`.`synonyms` AS `germplasm_synonyms`,`g`.`name` AS `entity_parent_name`,`g`.`general_identifier` AS `entity_parent_general_identifier`,`entitytypes`.`name` AS `entity_type`,`datasets`.`id` AS `dataset_id`,`datasets`.`name` AS `dataset_name`,`datasets`.`description` AS `dataset_description`,`locations`.`site_name` AS `location_name`,`countries`.`country_name` AS `country_name`,`countries`.`country_code2` AS `country_code2`,`phenotypes`.`id` AS `trait_id`,`phenotypes`.`name` AS `trait_name`,`phenotypes`.`short_name` AS `trait_name_short`,`phenotypes`.`restrictions` AS `trait_restrictions`,`units`.`unit_name` AS `unit_name`,`treatments`.`name` AS `treatment`,`phenotypedata`.`rep` AS `rep`, `phenotypedata`.`recording_date` AS `recording_date`,`phenotypedata`.`phenotype_value` AS `trait_value` from `phenotypedata` left join `germinatebase` on `germinatebase`.`id` = `phenotypedata`.`germinatebase_id` left join `germinatebase` `g` on `germinatebase`.`entityparent_id` = `g`.`id` left join `synonyms` on (`synonyms`.`synonymtype_id` = 1 AND `synonyms`.`foreign_id` = `germinatebase`.`id`) left join `entitytypes` on `entitytypes`.`id` = `germinatebase`.`entitytype_id` left join `phenotypes` on `phenotypes`.`id` = `phenotypedata`.`phenotype_id` left join `units` on `units`.`id` = `phenotypes`.`unit_id` left join `datasets` on `datasets`.`id` = `phenotypedata`.`dataset_id` left join `locations` on `phenotypedata`.`location_id` = `locations`.`id` left join `countries` on `countries`.`id` = `locations`.`country_id` left join `treatments` on `phenotypedata`.`treatment_id` = `treatments`.`id`;
  
  DROP VIEW IF EXISTS `view_table_compound_data`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_compound_data` AS select `germinatebase`.`id` AS `germplasm_id`,`germinatebase`.`general_identifier` AS `germplasm_gid`,`germinatebase`.`name` AS `germplasm_name`,`synonyms`.`synonyms` AS `germplasm_synonyms`,`g`.`name` AS `entity_parent_name`,`g`.`general_identifier` AS `entity_parent_general_identifier`,`entitytypes`.`name` AS `entity_type`,`datasets`.`id` AS `dataset_id`,`datasets`.`name` AS `dataset_name`,`datasets`.`description` AS `dataset_description`,`compounds`.`id` AS `compound_id`,`compounds`.`name` AS `compound_name`,`units`.`unit_name` AS `unit_name`,`compounddata`.`recording_date` AS `recording_date`,`compounddata`.`compound_value` AS `compound_value` from `compounddata` left join `germinatebase` on `germinatebase`.`id` = `compounddata`.`germinatebase_id` left join `germinatebase` `g` on `germinatebase`.`entityparent_id` = `g`.`id` left join `synonyms` on (`synonyms`.`synonymtype_id` = 1 AND `synonyms`.`foreign_id` = `germinatebase`.`id`) left join `entitytypes` on `entitytypes`.`id` = `germinatebase`.`entitytype_id` left join `compounds` on `compounds`.`id` = `compounddata`.`compound_id` left join `units` on `units`.`id` = `compounds`.`unit_id` left join `datasets` on `datasets`.`id` = `compounddata`.`dataset_id`;
  
  DROP VIEW IF EXISTS `view_table_pedigrees`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_pedigrees` AS SELECT `parent`.`id` AS `parent_id`, `parent`.`general_identifier` AS `parent_gid`, `parent`.`name` AS `parent_name`, `child`.`id` AS `child_id`, `child`.`general_identifier` AS `child_gid`, `child`.`name` AS `child_name`, `datasets`.`id` AS `dataset_id`, `datasets`.`name` AS `dataset_name`, `pedigrees`.`relationship_type` AS `relationship_type`, `pedigrees`.`relationship_description` AS `relationship_description`, `pedigreedescriptions`.`name` AS `pedigree_description`, `pedigreedescriptions`.`author` AS `pedigree_author` FROM `pedigrees` LEFT JOIN `germinatebase` `parent` ON `parent`.`id` = `pedigrees`.`parent_id` LEFT JOIN `germinatebase` `child` ON `child`.`id` = `pedigrees`.`germinatebase_id` LEFT JOIN `pedigreedescriptions` ON `pedigreedescriptions`.`id` = `pedigrees`.`pedigreedescription_id` LEFT JOIN `datasets` ON `datasets`.`id` = `pedigrees`.`dataset_id`;
  
  DROP VIEW IF EXISTS `view_table_pedigreedefinitions`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_pedigreedefinitions` AS SELECT `germinatebase`.`id` AS `germplasm_id`, `germinatebase`.`name` AS `germplasm_name`, `pedigreenotations`.`name` AS `pedigree_notation_name`, `pedigreenotations`.`description` AS `pedigree_notation_description`, `pedigreenotations`.`reference_url` AS `pedigree_notation_url`, `datasets`.`id` AS `dataset_id`, `datasets`.`name` AS `dataset_name`, `pedigreedefinitions`.`id` AS `definition_id`, `pedigreedefinitions`.`definition` AS `definition`, `pedigreedescriptions`.`name` AS `pedigree_description_name`, `pedigreedescriptions`.`description` AS `pedigree_description_description`, `pedigreedescriptions`.`author` AS `pedigree_description_author`, `pedigreedefinitions`.`created_on` AS `created_on` FROM `pedigreedefinitions` LEFT JOIN `germinatebase` ON `germinatebase`.`id` = `pedigreedefinitions`.`germinatebase_id` LEFT JOIN `pedigreenotations` ON `pedigreenotations`.`id` = `pedigreedefinitions`.`pedigreenotation_id` LEFT JOIN `pedigreedescriptions` ON `pedigreedescriptions`.`id` = `pedigreedefinitions`.`pedigreedescription_id` LEFT JOIN `datasets` ON `datasets`.`id` = `pedigreedefinitions`.`dataset_id`;
  
  DROP VIEW IF EXISTS `view_table_entities`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_entities` AS select `parent`.`id` AS `entity_parent_id`,`parent`.`general_identifier` AS `entity_parent_gid`,`parent`.`name` AS `entity_parent_name`,`parententity`.`name` AS `entity_parent_type`,`child`.`id` AS `entity_child_id`,`child`.`general_identifier` AS `entity_child_gid`,`child`.`name` AS `entity_child_name`,`childentity`.`name` AS `entity_child_type` from (((`germinatebase` `parent` left join `germinatebase` `child` on((`parent`.`id` = `child`.`entityparent_id`))) left join `entitytypes` `parententity` on((`parententity`.`id` = `parent`.`entitytype_id`))) left join `entitytypes` `childentity` on((`childentity`.`id` = `child`.`entitytype_id`))) where ((`parent`.`id` is not null) and (`child`.`id` is not null));
  
  DROP VIEW IF EXISTS `view_table_germplasm_attributes`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_germplasm_attributes` AS select `attributedata`.`id` AS `attribute_value_id`,`germinatebase`.`id` AS `germplasm_id`,`germinatebase`.`general_identifier` AS `germplasm_gid`,`germinatebase`.`name` AS `germplasm_name`,`attributes`.`id` AS `attribute_id`,`attributes`.`name` AS `attribute_name`,`attributes`.`description` AS `attribute_description`,`attributes`.`datatype` AS `attribute_type`,`attributes`.`target_table` AS `target_table`,`attributedata`.`foreign_id` AS `foreign_id`,`attributedata`.`created_on` AS `created_on`,`attributedata`.`value` AS `attribute_value` from ((`attributedata` left join `germinatebase` on((`attributedata`.`foreign_id` = `germinatebase`.`id`))) left join `attributes` on((`attributes`.`id` = `attributedata`.`attribute_id`))) where (`attributes`.`target_table` = 'germinatebase');
  
  DROP VIEW IF EXISTS `view_table_dataset_attributes`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_dataset_attributes` AS select `datasets`.`id` AS `dataset_id`,`datasets`.`name` AS `dataset_name`,`datasets`.`description` AS `dataset_description`,`attributes`.`id` AS `attribute_id`,`attributes`.`name` AS `attribute_name`,`attributes`.`description` AS `attribute_description`,`attributes`.`datatype` AS `attribute_type`,`attributes`.`target_table` AS `target_table`,`attributedata`.`foreign_id` AS `foreign_id`,`attributedata`.`value` AS `attribute_value` from ((`attributedata` left join `datasets` on((`attributedata`.`foreign_id` = `datasets`.`id`))) left join `attributes` on((`attributes`.`id` = `attributedata`.`attribute_id`))) where (`attributes`.`target_table` = 'datasets');
  
  DROP VIEW IF EXISTS `view_table_institutions`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_institutions` AS select `institutions`.`id` AS `institution_id`,`institutions`.`name` AS `institution_name`,`institutions`.`code` AS `institution_code`,`institutions`.`acronym` AS `institution_acronym`,`institutions`.`address` AS `institution_address`,`institutions`.`email` AS `institution_email`,`institutions`.`contact` AS `institution_contact`,`institutions`.`phone` AS `institution_phone`,`countries`.`id` AS `country_id`,`countries`.`country_name` AS `country_name`,`countries`.`country_code2` AS `country_code` from (`institutions` left join `countries` on((`countries`.`id` = `institutions`.`country_id`)));
  
  DROP VIEW IF EXISTS `view_table_compounds`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_compounds` AS select `compounds`.`id` AS `compound_id`,`compounds`.`name` AS `compound_name`,`compounds`.`description` AS `compound_description`,`units`.`id` AS `unit_id`,`units`.`unit_name` AS `unit_name`,`units`.`unit_description` AS `unit_description`,`units`.`unit_abbreviation` AS `unit_abbreviation`,`synonyms`.`synonyms` AS `synonyms`,(select count(1) from `compounddata` where (`compounddata`.`compound_id` = `compounds`.`id`)) AS `count` from ((`compounds` left join `units` on((`units`.`id` = `compounds`.`unit_id`))) left join `synonyms` on(((`synonyms`.`foreign_id` = `compounds`.`id`) and (`synonyms`.`synonymtype_id` = 3)))) group by `compounds`.`id`,`synonyms`.`id`;
  
  DROP VIEW IF EXISTS `view_table_climates`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_climates` AS select `climates`.`id` AS `climate_id`,`climates`.`name` AS `climate_name`,`climates`.`short_name` AS `climate_name_short`,`climates`.`description` AS `climate_description`,`climates`.`datatype` AS `data_type`,`units`.`id` AS `unit_id`,`units`.`unit_name` AS `unit_name`,`units`.`unit_description` AS `unit_description`,(select count(1) from `climateoverlays` where (`climateoverlays`.`climate_id` = `climates`.`id`)) AS `overlays`,`units`.`unit_abbreviation` AS `unit_abbreviation`,(select count(1) from `climatedata` where (`climatedata`.`climate_id` = `climates`.`id`)) AS `count` from (`climates` left join `units` on((`units`.`id` = `climates`.`unit_id`))) group by `climates`.`id`;
  
  DROP VIEW IF EXISTS `view_table_climate_data`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_climate_data` AS select `locations`.`id` AS `location_id`,`locations`.`site_name` AS `location_name`,`locations`.`region` AS `location_region`,`locations`.`state` AS `location_state`,`locationtypes`.`name` AS `location_type`,`locations`.`latitude` AS `location_latitude`,`locations`.`longitude` AS `location_longitude`,`locations`.`elevation` AS `location_elevation`,`countries`.`country_name` AS `country_name`,`countries`.`country_code2` AS `country_code2`,`countries`.`country_code3` AS `country_code3`,`datasets`.`id` AS `dataset_id`,`datasets`.`name` AS `dataset_name`,`datasets`.`description` AS `dataset_description`,`climates`.`id` AS `climate_id`,`climates`.`name` AS `climate_name`,`climates`.`short_name` AS `climate_name_short`,`units`.`unit_name` AS `unit_name`,`climatedata`.`recording_date` AS `recording_date`,`climatedata`.`climate_value` AS `climate_value` from ((((((`climatedata` left join `locations` on((`locations`.`id` = `climatedata`.`location_id`))) left join `locationtypes` on((`locations`.`locationtype_id` = `locationtypes`.`id`))) left join `climates` on((`climates`.`id` = `climatedata`.`climate_id`))) left join `units` on((`units`.`id` = `climates`.`unit_id`))) left join `datasets` on((`datasets`.`id` = `climatedata`.`dataset_id`))) left join `countries` on((`countries`.`id` = `locations`.`country_id`)));
  
  DROP VIEW IF EXISTS `view_table_news`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_news` AS select `news`.`id` AS `news_id`,`news`.`title` AS `news_title`,`news`.`content` AS `news_content`,`news`.`hyperlink` AS `news_hyperlink`,`news`.`image` AS `news_image`,`newstypes`.`id` AS `newstype_id`,`newstypes`.`name` AS `newstype_name`,`newstypes`.`description` AS `newstype_description`,`news`.`created_on` AS `created_on`,`news`.`updated_on` AS `updated_on` from (`news` left join `newstypes` on((`newstypes`.`id` = `news`.`newstype_id`))) order by `news`.`created_on` desc;
  
  DROP VIEW IF EXISTS `view_table_comments`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_comments` AS select `comments`.`id` AS `comment_id`,`commenttypes`.`id` AS `comment_type_id`,`commenttypes`.`reference_table` AS `comment_type`,`comments`.`reference_id` AS `comment_foreign_id`,`comments`.`user_id` AS `user_id`,'' AS `user_name`,`comments`.`description` AS `comment_content`,`comments`.`updated_on` AS `updated_on` from (`comments` left join `commenttypes` on((`commenttypes`.`id` = `comments`.`commenttype_id`)));
  
  DROP VIEW IF EXISTS `view_table_climateoverlays`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_climateoverlays` AS select `climateoverlays`.`id` AS `climate_overlay_id`,`climates`.`id` AS `climate_id`,`climates`.`name` AS `climate_name`,`climateoverlays`.`is_legend` AS `is_legend`,`climateoverlays`.`bottom_left_latitude` AS `bottom_left_latitude`,`climateoverlays`.`bottom_left_longitude` AS `bottom_left_longitude`,`climateoverlays`.`top_right_latitude` AS `top_right_latitude`,`climateoverlays`.`top_right_longitude` AS `top_right_longitude`,`climateoverlays`.`path` AS `filename` from (`climateoverlays` left join `climates` on((`climates`.`id` = `climateoverlays`.`climate_id`)));
  
  DROP VIEW IF EXISTS `view_table_links`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_links` AS select `links`.`id` AS `link_id`,`links`.`description` AS `link_description`,`links`.`visibility` AS `link_visibility`,`linktypes`.`id` AS `linktype_id`,`linktypes`.`description` AS `linktype_description`,`linktypes`.`target_table` AS `linktype_target_table`,`linktypes`.`target_column` AS `linktype_target_column`,`links`.`foreign_id` AS `link_foreign_id`,`links`.`hyperlink` AS `hyperlink`,`linktypes`.`placeholder` AS `placeholder`,`links`.`updated_on` AS `updated_on` from (`links` left join `linktypes` on((`linktypes`.`id` = `links`.`linktype_id`)));
  
  DROP VIEW IF EXISTS `view_table_fileresources`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_fileresources` AS select `fileresources`.`id` AS `fileresource_id`,`fileresources`.`name` AS `fileresource_name`,`fileresources`.`path` AS `fileresource_path`,`fileresources`.`description` AS `fileresource_description`,`fileresources`.`filesize` AS `fileresource_size`,`fileresources`.`created_on` AS `fileresource_created_on`,`fileresources`.`updated_on` AS `fileresource_updated_on`,`fileresourcetypes`.`id` AS `fileresourcetype_id`,`fileresourcetypes`.`name` AS `fileresourcetype_name`,`fileresourcetypes`.`description` AS `fileresourcetype_description` from (`fileresources` left join `fileresourcetypes` on((`fileresources`.`fileresourcetype_id` = `fileresourcetypes`.`id`)));
  
  DROP VIEW IF EXISTS `view_table_fileresourcetypes`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_fileresourcetypes` AS select `fileresourcetypes`.`id` AS `id`,`fileresourcetypes`.`name` AS `name`,`fileresourcetypes`.`description` AS `description`,`fileresourcetypes`.`created_on` AS `created_on`,`fileresourcetypes`.`updated_on` AS `updated_on`,(select count(1) from `fileresources` where (`fileresources`.`fileresourcetype_id` = `fileresourcetypes`.`id`)) AS `count` from `fileresourcetypes`;
  
  DROP VIEW IF EXISTS `view_table_publications`;
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_table_publications` AS SELECT `publications`.`id` AS `publication_id`, `publications`.`doi` AS `publication_doi`, `publications`.`fallback_cache` AS `publication_fallback_cache`, `publicationdata`.`reference_type` AS `reference_type`, JSON_ARRAYAGG(`publicationdata`.`foreign_id`) AS `referencing_ids`, `publications`.`created_on` AS `created_on`, `publications`.`updated_on` AS `updated_on` FROM `publications` LEFT JOIN `publicationdata` ON `publicationdata`.`publication_id` = `publications`.`id` GROUP BY `publications`.`id`, `publicationdata`.`reference_type`;
  
  DROP PROCEDURE IF EXISTS `export_trials_data`;
  CREATE PROCEDURE `export_trials_data`(IN `groupIds` TEXT, IN `markedIds` TEXT, IN `datasetIds` TEXT, IN `phenotypeIds` TEXT) BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT(' SELECT GROUP_CONCAT(DISTINCT CONCAT("MAX(IF(`phenotype_id` = ", `phenotype_id`,",phenotype_value,NULL)) AS ", "`", CONCAT(phenotypes.`name`, IF(ISNULL(phenotypes.unit_id) OR ISNULL(units.unit_abbreviation), "", CONCAT(" [", units.unit_abbreviation, "]"))), "`")) INTO @SQL FROM phenotypedata LEFT JOIN phenotypes ON phenotypes.id = phenotypedata.phenotype_id LEFT JOIN germinatebase ON germinatebase.id = phenotypedata.germinatebase_id LEFT JOIN units ON units.id = phenotypes.unit_id WHERE phenotypedata.dataset_id IN (', datasetIds, ') AND ', IF(phenotypeIds IS NULL, '1=1', CONCAT('phenotype_id IN (', phenotypeIds, ')'))); PREPARE stmtone FROM @QRY; EXECUTE stmtone; DEALLOCATE PREPARE stmtone; IF @SQL IS NULL THEN SELECT NULL as ERROR; ELSE SET @SQL = CONCAT('SELECT germinatebase.name, germinatebase.puid, CONCAT_WS(" ", taxonomies.genus, taxonomies.species, taxonomies.subtaxa) AS `taxonomy`, `synonyms`.`synonyms` AS `germplasm_synonyms`, `g`.`name` AS `entity_parent_name`, `g`.`general_identifier` AS `entity_parent_general_identifier`, datasets.name AS dataset_name, datasets.description AS dataset_description, datasets.version AS dataset_version, licenses.name AS license_name, ( SELECT GROUP_CONCAT(site_name SEPARATOR ", ") FROM locations LEFT JOIN datasetlocations ON datasetlocations.location_id = locations.id WHERE datasetlocations.dataset_id = phenotypedata.dataset_id ) AS location_name, ( SELECT site_name FROM locations WHERE locations.id = phenotypedata.location_id ) AS trial_site, MAX(DATE_FORMAT(phenotypedata.recording_date, \'%Y-%m-%d\')) AS Date, phenotypedata.rep AS rep, phenotypedata.block AS block, AVG(phenotypedata.latitude) AS latitude, AVG(phenotypedata.longitude) AS longitude, AVG(phenotypedata.elevation) AS elevation, germinatebase.id AS dbId, germinatebase.general_identifier, treatments.description AS treatments_description, ', IF(groupIds IS NULL, '', CONCAT('(SELECT concat("[", group_concat(CONCAT("\\"", LEFT ( groups.NAME, 10 ), IF ( LENGTH( groups.NAME )> 10, "...\\"", "\\"" )) order by groups.name) , "]") FROM groupmembers LEFT JOIN groups ON groups.id = groupmembers.group_id WHERE groupmembers.group_id IN (', groupIds, ') AND groupmembers.foreign_id = germinatebase.id) AS group_ids, ')), @SQL,' FROM phenotypedata LEFT JOIN germinatebase ON germinatebase.id = phenotypedata.germinatebase_id LEFT JOIN `synonyms` on (`synonyms`.`synonymtype_id` = 1 AND `synonyms`.`foreign_id` = `germinatebase`.`id`) LEFT JOIN germinatebase g ON g.id = germinatebase.entityparent_id ', IF(groupIds IS NULL, '', 'LEFT JOIN groupmembers ON germinatebase.id = groupmembers.foreign_id') ,' LEFT JOIN datasets ON datasets.id = phenotypedata.dataset_id LEFT JOIN licenses ON licenses.id = datasets.license_id LEFT JOIN treatments ON treatments.id = phenotypedata.treatment_id LEFT JOIN taxonomies ON taxonomies.id = germinatebase.taxonomy_id WHERE (', IF(groupIds IS NULL AND markedIds IS NULL, '1=1', IF(groupIds IS NULL AND NOT(markedIds IS NULL), CONCAT('germinatebase.id IN (', markedIds, ')'), IF(markedIds IS NULL AND NOT(groupIds IS NULL), CONCAT('groupmembers.group_id IN (', groupIds, ')'), CONCAT('germinatebase.id IN (', markedIds, ') OR groupmembers.group_id IN (', groupIds, ')')))), ') AND datasets.id IN (', datasetIds, ') GROUP BY germinatebase.id, synonyms.synonyms, treatment_id, dataset_id, phenotypedata.location_id, phenotypedata.rep, phenotypedata.block'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END;
  
  DROP PROCEDURE IF EXISTS `export_compound_data`;
  CREATE PROCEDURE `export_compound_data` (IN `groupIds` TEXT, IN `markedIds` TEXT, IN `datasetIds` TEXT, IN `compoundIds` TEXT) BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT(' SELECT GROUP_CONCAT(DISTINCT CONCAT("MAX(IF(`compound_id` = ", `compound_id`, ",compound_value, NULL)) AS ", "`", CONCAT(compounds.`name`, IF(ISNULL(compounds.unit_id) OR ISNULL(units.unit_abbreviation), "", CONCAT(" [", units.unit_abbreviation, "]"))), "`")) INTO @SQL FROM compounddata LEFT JOIN compounds ON compounds.id = compounddata.compound_id LEFT JOIN germinatebase ON germinatebase.id = compounddata.germinatebase_id LEFT JOIN units ON units.id = compounds.unit_id WHERE compounddata.dataset_id IN (', datasetIds, ') AND ', IF(compoundIds IS NULL, '1=1', CONCAT('compound_id IN (', compoundIds, ')'))); PREPARE stmtone FROM @QRY; EXECUTE stmtone; DEALLOCATE PREPARE stmtone; IF @SQL IS NULL THEN SELECT NULL as ERROR; ELSE SET @SQL = CONCAT(' SELECT germinatebase.name, germinatebase.puid, CONCAT_WS(" ", taxonomies.genus, taxonomies.species, taxonomies.subtaxa) AS `taxonomy`, `synonyms`.`synonyms` AS `germplasm_synonyms`,	`g`.`name` AS `entity_parent_name`,	`g`.`general_identifier` AS `entity_parent_general_identifier`, datasets.name AS dataset_name, datasets.description AS dataset_description, datasets.version AS dataset_version, licenses.name AS license_name, DATE_FORMAT(compounddata.recording_date, \'%Y\') AS year, germinatebase.id AS dbId, germinatebase.general_identifier, ', IF(groupIds IS NULL, '', CONCAT('(SELECT concat("[", group_concat(CONCAT("\\"", LEFT ( groups.NAME, 10 ), IF ( LENGTH( groups.NAME )> 10, "...\\"", "\\"" )) order by groups.name) , "]") FROM groupmembers LEFT JOIN groups ON groups.id = groupmembers.group_id WHERE groupmembers.group_id IN (', groupIds, ') AND groupmembers.foreign_id = germinatebase.id) AS group_ids, ')), @SQL,' FROM compounddata LEFT JOIN germinatebase ON germinatebase.id = compounddata.germinatebase_id LEFT JOIN `synonyms` on (`synonyms`.`synonymtype_id` = 1 AND `synonyms`.`foreign_id` = `germinatebase`.`id`) LEFT JOIN germinatebase g ON g.id = germinatebase.entityparent_id ', IF (groupIds IS NULL, '', 'LEFT JOIN groupmembers ON germinatebase.id = groupmembers.foreign_id') ,' LEFT JOIN datasets ON datasets.id = compounddata.dataset_id LEFT JOIN licenses ON licenses.id = datasets.license_id LEFT JOIN taxonomies ON taxonomies.id = germinatebase.taxonomy_id WHERE datasets.id IN (', datasetIds, ') AND (', IF(groupIds IS NULL AND markedIds IS NULL, '1=1', IF(groupIds IS NULL AND NOT(markedIds IS NULL), CONCAT('germinatebase.id IN (', markedIds, ')'), IF(markedIds IS NULL AND NOT(groupIds IS NULL), CONCAT('groupmembers.group_id IN (', groupIds, ')'), CONCAT('germinatebase.id IN (', markedIds, ') OR groupmembers.group_id IN (', groupIds, ')')))), ') GROUP BY germinatebase.id, synonyms.synonyms, dataset_id, year'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END;
  
  DROP PROCEDURE IF EXISTS `export_climate_data`;
  CREATE PROCEDURE `export_climate_data`  (IN `groupIds` text,IN `markedIds` text,IN `datasetIds` text,IN `climateIds` text) BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT(' SELECT GROUP_CONCAT(DISTINCT CONCAT("MAX(IF(`climate_id` = ", `climate_id`,",climate_value,NULL)) AS ", "`", CONCAT(climates.`name`, IF(ISNULL(climates.unit_id) OR ISNULL(units.unit_abbreviation), "", CONCAT(" [", units.unit_abbreviation, "]"))), "`")) INTO @SQL FROM climatedata LEFT JOIN climates ON climates.id = climatedata.climate_id LEFT JOIN locations ON locations.id = climatedata.location_id LEFT JOIN units ON units.id = climates.unit_id WHERE climatedata.dataset_id IN (', datasetIds, ') AND ', IF(climateIds IS NULL, '1=1', CONCAT('climate_id IN (', climateIds, ')'))); PREPARE stmtone FROM @QRY; EXECUTE stmtone; DEALLOCATE PREPARE stmtone; IF @SQL IS NULL THEN SELECT NULL as ERROR; ELSE SET @SQL = CONCAT(' SELECT locations.site_name AS `name`, datasets.name AS dataset_name, datasets.description AS dataset_description, datasets.version AS dataset_version, licenses.name AS license_name, DATE_FORMAT(climatedata.recording_date, \'%Y\') AS year, MAX(DATE_FORMAT(climatedata.recording_date, \'%Y-%m-%d\')) AS Date, locations.site_name AS `Site`, locations.id AS dbId, ', IF(groupIds IS NULL, '', CONCAT('(SELECT concat("[", group_concat(CONCAT("\\"", LEFT ( groups.NAME, 10 ), IF ( LENGTH( groups.NAME )> 10, "...\\"", "\\"" )) order by groups.name) , "]") FROM groupmembers LEFT JOIN groups ON groups.id = groupmembers.group_id WHERE groupmembers.group_id IN (', groupIds, ') AND groupmembers.foreign_id = locations.id) AS group_ids, ')), @SQL,' FROM climatedata LEFT JOIN locations ON locations.id = climatedata.location_id ', IF (groupIds IS NULL, '', 'LEFT JOIN groupmembers ON locations.id = groupmembers.foreign_id') ,' LEFT JOIN datasets ON datasets.id = climatedata.dataset_id LEFT JOIN licenses ON licenses.id = datasets.license_id WHERE (', IF(groupIds IS NULL AND markedIds IS NULL, '1=1', IF(groupIds IS NULL AND NOT(markedIds IS NULL), CONCAT('locations.id IN (', markedIds, ')'), IF(markedIds IS NULL AND NOT(groupIds IS NULL), CONCAT('groupmembers.group_id IN (', groupIds, ')'), CONCAT('locations.id IN (', markedIds, ') OR groupmembers.group_id IN (', groupIds, ')')))), ') AND datasets.id IN (', datasetIds, ') GROUP BY locations.id, dataset_id, climatedata.recording_date'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END;
  
  DROP PROCEDURE IF EXISTS `dataset_meta`;
  CREATE PROCEDURE `dataset_meta` () BEGIN DELETE FROM datasetmeta WHERE NOT EXISTS ( SELECT 1 FROM datasets WHERE datasets.is_external = 1 AND datasets.id = datasetmeta.dataset_id ); SELECT @max := IFNULL(MAX(id) + 1, 1) FROM datasetmeta; SET @alter_statement = concat( 'ALTER TABLE `datasetmeta` AUTO_INCREMENT = ', @max ); PREPARE stmt FROM @alter_statement; EXECUTE stmt; DEALLOCATE PREPARE stmt; INSERT INTO datasetmeta (dataset_id, nr_of_data_points, nr_of_data_objects) SELECT datasets.id, ( COALESCE(ac.c, 0) + COALESCE (pc.c, 0) + COALESCE (cc.c, 0) + COALESCE(cmc.c, 0) + COALESCE(ped.c, 0) + COALESCE(pdd.c, 0) ) AS 'nr_of_data_points', ( COALESCE (ad.d, 0) + COALESCE (pd.d, 0) + COALESCE (cd.d, 0) + COALESCE(cmd.d, 0) + COALESCE(pedd.d, 0) + COALESCE(pddd.d, 0) ) AS 'nr_of_data_objects' FROM datasets LEFT JOIN datasetstates ON datasetstates.id = datasets.dataset_state_id LEFT JOIN experiments ON experiments.id = datasets.experiment_id LEFT JOIN datasettypes ON datasettypes.id = datasets.datasettype_id LEFT JOIN ( SELECT dataset_id, COUNT(1) AS c FROM pedigrees GROUP BY dataset_id ) ped ON ped.dataset_id = datasets.id LEFT JOIN ( SELECT dataset_id, COUNT(1) AS c FROM pedigreedefinitions GROUP BY dataset_id ) pdd ON pdd.dataset_id = datasets.id LEFT JOIN ( SELECT dataset_id, COUNT(1) AS d FROM datasetmembers WHERE datasetmembers.datasetmembertype_id = 2 GROUP BY dataset_id ) ad ON ad.dataset_id = datasets.id LEFT JOIN ( SELECT dataset_id, COUNT(1) AS c FROM phenotypedata GROUP BY dataset_id ) pc ON pc.dataset_id = datasets.id LEFT JOIN ( SELECT dataset_id, COUNT(1) AS c FROM climatedata GROUP BY dataset_id ) cc ON cc.dataset_id = datasets.id LEFT JOIN ( SELECT dataset_id, COUNT(1) AS c FROM compounddata GROUP BY dataset_id ) cmc ON cmc.dataset_id = datasets.id LEFT JOIN ( SELECT datasetmembers.dataset_id, a.count * m.count AS c FROM datasetmembers LEFT JOIN ( SELECT dataset_id, COUNT(1) AS count FROM datasetmembers WHERE datasetmembertype_id = 1 GROUP BY dataset_id ) a ON a.dataset_id = datasetmembers.dataset_id LEFT JOIN ( SELECT dataset_id, COUNT(1) AS count FROM datasetmembers WHERE datasetmembertype_id = 2 GROUP BY dataset_id ) m ON m.dataset_id = datasetmembers.dataset_id GROUP BY datasetmembers.dataset_id ) ac ON ac.dataset_id = datasets.id LEFT JOIN ( SELECT distinct_entries.dataset_id, count(1) AS d FROM ( SELECT DISTINCT dataset_id, germinatebase_id FROM phenotypedata ) AS distinct_entries GROUP BY distinct_entries.dataset_id ) pd ON pd.dataset_id = datasets.id LEFT JOIN ( SELECT distinct_entries.dataset_id, count(1) AS d FROM ( SELECT DISTINCT dataset_id, location_id FROM climatedata ) AS distinct_entries GROUP BY distinct_entries.dataset_id ) cd ON cd.dataset_id = datasets.id LEFT JOIN ( SELECT distinct_entries.dataset_id, count(1) AS d FROM ( SELECT DISTINCT dataset_id, germinatebase_id FROM compounddata ) AS distinct_entries GROUP BY distinct_entries.dataset_id ) cmd ON cmd.dataset_id = datasets.id LEFT JOIN ( SELECT distinct_entries.dataset_id, count(1) AS d FROM ( SELECT DISTINCT dataset_id, germinatebase_id FROM pedigrees ) AS distinct_entries GROUP BY distinct_entries.dataset_id ) pedd ON pedd.dataset_id = datasets.id LEFT JOIN ( SELECT distinct_entries.dataset_id, count(1) AS d FROM ( SELECT DISTINCT dataset_id, germinatebase_id FROM pedigreedefinitions ) AS distinct_entries GROUP BY distinct_entries.dataset_id ) pddd ON pddd.dataset_id = datasets.id WHERE is_external = 0; END;
  
  DROP PROCEDURE IF EXISTS `export_dataset_attributes`;
  CREATE PROCEDURE `export_dataset_attributes` (IN datasetIds TEXT) BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT('SELECT GROUP_CONCAT(DISTINCT CONCAT( "MAX(CASE WHEN attribute_id = ", `attributes`.`id`, " THEN attributedata.value END) AS ", "`", `attributes`.`name`, "`")) INTO @SQL FROM attributedata LEFT JOIN attributes ON attributes.id = attributedata.attribute_id WHERE attributes.target_table = "datasets" AND attributedata.foreign_id IN (', datasetIds,');'); PREPARE stmtone FROM @QRY; EXECUTE stmtone; DEALLOCATE PREPARE stmtone; IF @SQL IS NULL THEN SET @SQL = 'NULL'; END IF; SET @SQL = CONCAT('SELECT datasets.id AS dataset_id, datasets.name AS dataset_name, datasets.description AS dataset_description, datasets.version AS dataset_version, experiments.id AS experiment_id, experiments.experiment_name AS experiment_name, experiments.description AS experiment_description, licenses.name AS license_name, ', @SQL, ' FROM datasets LEFT JOIN experiments ON experiments.id = datasets.experiment_id LEFT JOIN licenses ON licenses.id = datasets.license_id LEFT JOIN attributedata ON datasets.id = attributedata.foreign_id LEFT JOIN attributes ON (attributes.id = attributedata.attribute_id AND attributes.target_table = "datasets") WHERE datasets.id IN (', datasetIds, ') GROUP BY datasets.id;'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
  
  DROP PROCEDURE IF EXISTS `export_passport_data`;
  CREATE PROCEDURE `export_passport_data` (IN `germplasmIds` LONGTEXT, IN `groupIds` TEXT) BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT(' SELECT GROUP_CONCAT(DISTINCT CONCAT("MAX(IF(`attribute_id` = ", `attribute_id`,",value,NULL)) AS ", "`", attributes.`name`, "`")) INTO @SQL FROM attributedata LEFT JOIN attributes ON attributes.id = attributedata.attribute_id WHERE `attributes`.`target_table` = "germinatebase"'); PREPARE stmtone FROM @QRY; EXECUTE stmtone; DEALLOCATE PREPARE stmtone; SET @SQL = CONCAT(' SELECT `germinatebase`.`name` AS `germplasm_name`,`germinatebase`.`id` AS `germplasm_id`,`germinatebase`.`general_identifier` AS `germplasm_gid`,`germinatebase`.`number` AS `germplasm_number`,`germinatebase`.`puid` AS `germplasm_puid`,`entitytypes`.`id` AS `entity_type_id`,`entitytypes`.`name` AS `entity_type_name`,`germinatebase`.`entityparent_id` AS `entity_parent_id`,`g`.`name` AS `entity_parent_name`,`g`.`general_identifier` AS `entity_parent_general_identifier`,`biologicalstatus`.`id` AS `biological_status_id`,`biologicalstatus`.`sampstat` AS `biological_status_name`,`synonyms`.`synonyms` AS `synonyms`,`germinatebase`.`collnumb` AS `collector_number`,`taxonomies`.`genus` AS `genus`,`taxonomies`.`species` AS `species`,`taxonomies`.`subtaxa` AS `subtaxa`,`institutions`.`id` AS `institution_id`,`institutions`.`name` AS `institution_name`,`locations`.`site_name` AS `location`,`locations`.`latitude` AS `latitude`,`locations`.`longitude` AS `longitude`,`locations`.`elevation` AS `elevation`,`countries`.`country_name` AS `country_name`,`countries`.`country_code2` AS `country_code`,`germinatebase`.`colldate` AS `coll_date`,`germinatebase`.`pdci` AS `pdci`,(select count(1) from (`images` left join `imagetypes` on((`imagetypes`.`id` = `images`.`imagetype_id`))) where ((`imagetypes`.`reference_table` = "germinatebase") and (`images`.`foreign_id` = `germinatebase`.`id`))) AS `image_count`,(select `images`.`path` from (`images` left join `imagetypes` on((`imagetypes`.`id` = `images`.`imagetype_id`))) where ((`imagetypes`.`reference_table` = "germinatebase") and (`images`.`foreign_id` = `germinatebase`.`id`)) limit 1) AS `first_image_path`,(select 1 from `phenotypedata` where (`phenotypedata`.`germinatebase_id` = `germinatebase`.`id`) limit 1) AS `has_trials_data`,(select 1 from (`datasetmembers` left join `datasets` on((`datasets`.`id` = `datasetmembers`.`dataset_id`))) where ((`datasetmembers`.`foreign_id` = `germinatebase`.`id`) and (`datasetmembers`.`datasetmembertype_id` = 2) and (`datasets`.`datasettype_id` = 1)) limit 1) AS `has_genotypic_data`,(select 1 from (`datasetmembers` left join `datasets` on((`datasets`.`id` = `datasetmembers`.`dataset_id`))) where ((`datasetmembers`.`foreign_id` = `germinatebase`.`id`) and (`datasetmembers`.`datasetmembertype_id` = 2) and (`datasets`.`datasettype_id` = 4)) limit 1) AS `has_allelefreq_data`,(select 1 from `compounddata` where (`compounddata`.`germinatebase_id` = `germinatebase`.`id`) limit 1) AS `has_compound_data`', IF(@SQL IS NULL, '', CONCAT(', ', @SQL)),' FROM `germinatebase` LEFT JOIN `germinatebase` `g` ON `g`.`id` = `germinatebase`.`entityparent_id` LEFT JOIN `institutions` ON `institutions`.`id` = `germinatebase`.`institution_id` LEFT JOIN `entitytypes` ON `germinatebase`.`entitytype_id` = `entitytypes`.`id` LEFT JOIN `taxonomies` ON `germinatebase`.`taxonomy_id` = `taxonomies`.`id` LEFT JOIN `locations` ON `germinatebase`.`location_id` = `locations`.`id` LEFT JOIN `countries` ON `locations`.`country_id` = `countries`.`id` LEFT JOIN `biologicalstatus` ON `biologicalstatus`.`id` = `germinatebase`.`biologicalstatus_id` LEFT JOIN `synonyms` ON (`synonyms`.`foreign_id` = `germinatebase`.`id` AND `synonyms`.`synonymtype_id` = 1) LEFT JOIN attributedata ON attributedata.foreign_id = germinatebase.id LEFT JOIN attributes ON (attributes.id = attributedata.attribute_id AND `attributes`.`target_table` = "germinatebase") ', IF (groupIds IS NULL, '', 'LEFT JOIN groupmembers ON germinatebase.id = groupmembers.foreign_id'), ' WHERE (', IF(groupIds IS NULL AND germplasmIds IS NULL, '1=1', IF(groupIds IS NULL AND NOT(germplasmIds IS NULL), CONCAT('germinatebase.id IN (', germplasmIds, ')'), IF(germplasmIds IS NULL AND NOT(groupIds IS NULL), CONCAT('groupmembers.group_id IN (', groupIds, ')'), CONCAT('germinatebase.id IN (', germplasmIds, ') OR groupmembers.group_id IN (', groupIds, ')')))), ') GROUP BY germinatebase.id, synonyms.synonyms'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
  
  DROP PROCEDURE IF EXISTS `export_trait_categorical`;
  CREATE PROCEDURE `export_trait_categorical` (IN `groupIds` TEXT, IN `markedIds` TEXT, IN `datasetIds` TEXT, IN `traitIds` TEXT) BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT(' SELECT GROUP_CONCAT(DISTINCT CONCAT("COUNT(IF(`dataset_id` = ", `dataset_id`,",phenotype_value,NULL)) AS ", "`", datasets.`name`, "`")) INTO @SQL FROM phenotypedata LEFT JOIN datasets ON datasets.id = phenotypedata.dataset_id ', IF(groupIds IS NULL, '', 'LEFT JOIN groupmembers ON phenotypedata.germinatebase_id = groupmembers.foreign_id '), 'WHERE ', IF(datasetIds IS NULL, '', CONCAT('phenotypedata.dataset_id IN (', datasetIds, ') AND ')), IF (markedIds IS NULL, '', CONCAT('phenotypedata.germinatebase_id IN (', markedIds,') AND ')), IF (groupIds IS NULL, '', CONCAT('groupmembers.group_id IN (', groupIds,') AND ')), 'phenotypedata.phenotype_id IN (', traitIds, ')'); PREPARE stmtone FROM @QRY; EXECUTE stmtone; DEALLOCATE PREPARE stmtone; IF @SQL IS NULL THEN SELECT NULL as ERROR; ELSE SET @SQL = CONCAT(' SELECT `phenotype_value`, ', @SQL, ' FROM phenotypedata LEFT JOIN datasets ON phenotypedata.dataset_id = datasets.id ', IF(groupIds IS NULL, '', 'LEFT JOIN groupmembers ON phenotypedata.germinatebase_id = groupmembers.foreign_id '), 'WHERE ', IF(datasetIds IS NULL, '', CONCAT('phenotypedata.dataset_id IN (', datasetIds, ') AND ')), IF (markedIds IS NULL, '', CONCAT('phenotypedata.germinatebase_id IN (', markedIds,') AND ')), IF (groupIds IS NULL, '', CONCAT('groupmembers.group_id IN (', groupIds,') AND ')), 'phenotypedata.phenotype_id IN (', traitIds, ') GROUP BY phenotypedata.phenotype_value'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END;