germinate_template_4_0_0
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
export_dataset_attributes
Parameters
Name
Type
Mode
datasetIds
text
IN
Definition
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