germinate_template_4_0_0
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
dataset_meta
Parameters
Name
Type
Mode
Definition
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)) AS 'nr_of_data_points', ( COALESCE (ad.d, 0) + COALESCE (pd.d, 0) + COALESCE (cd.d, 0) + COALESCE(cmd.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 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 WHERE is_external = 0; END