Actions
bug #9785
closedUpdate script for missing measurment unit_ids (matrix)
Start date:
Due date:
% Done:
100%
Estimated time:
Severity:
normal
Found in Version:
Description
for details see #9772#note-24
The problem existed already befor so there are lots of data with missing unit_id (e.g. 745 records in greece_bupleurum).
As recommended measurement units is usually only 1 unit per character it should be possible to reconstruct them.
SELECT deb.unit_id, deb.* FROM DescriptionElementBase deb WHERE deb.DTYPE LIKE 'Quant%' AND deb.unit_id IS NULL ORDER BY id DESC
Related issues
Updated by Andreas Müller about 2 years ago
- Copied from bug #9772: Improve DTO usage for character matrix added
Updated by Andreas Müller about 2 years ago
- % Done changed from 0 to 30
An Update for MySQL:
UPDATE DescriptionElementBase deb LEFT OUTER JOIN DefinedTermBase fe ON fe.id = deb.feature_id
SET deb.unit_id = (
SELECT MN2.recommendedMeasurementUnits_id
FROM DefinedTermBase fe2 INNER JOIN DefinedTermBase_MeasurementUnit MN2 ON MN2.Feature_id = fe2.id
WHERE fe.id = fe2.id AND (fe2.DTYPE = 'Feature' OR fe2.DTYPE = 'Character')
GROUP BY fe2.id
HAVING COUNT(*) = 1
)
WHERE deb.DTYPE = 'QuantitativeData' AND deb.unit_id IS NULL
AND fe.id IN (
SELECT fe.id
FROM DefinedTermBase fe INNER JOIN DefinedTermBase_MeasurementUnit MN ON MN.Feature_id = fe.id INNER JOIN DefinedTermBase mu ON MN.recommendedMeasurementUnits_id = mu.id
WHERE fe.DTYPE = 'Feature' OR fe.DTYPE = 'Character'
GROUP BY fe.id, mu.id
HAVING COUNT(*) = 1
)
Updated by Andreas Müller about 2 years ago
Some search SQL:
SELECT deb.id, fe.id, fe.titleCache -- , COUNT(*) AS n
FROM DescriptionElementBase deb LEFT OUTER JOIN DefinedTermBase fe ON fe.id = deb.feature_id
WHERE deb.DTYPE = 'QuantitativeData' AND deb.unit_id IS NULL
AND fe.id IN (
SELECT fe.id
FROM DefinedTermBase fe INNER JOIN DefinedTermBase_MeasurementUnit MN ON MN.Feature_id = fe.id INNER JOIN DefinedTermBase mu ON MN.recommendedMeasurementUnits_id = mu.id
WHERE fe.DTYPE = 'Feature' OR fe.DTYPE = 'Character'
GROUP BY fe.id, mu.id
HAVING COUNT(*) = 1
)
-- GROUP BY deb.feature_id
;
SELECT fe.titleCache feature, fe.id, mu.titleCache, mu.id, COUNT(*) AS n
FROM DefinedTermBase fe INNER JOIN DefinedTermBase_MeasurementUnit MN ON MN.Feature_id = fe.id INNER JOIN DefinedTermBase mu ON MN.recommendedMeasurementUnits_id = mu.id
WHERE fe.DTYPE = 'Feature' OR fe.DTYPE = 'Character'
GROUP BY fe.id, mu.id
HAVING COUNT(*) > 1
Updated by Andreas Müller about 2 years ago
- Status changed from New to In Progress
- Target version changed from Release 5.27 to CDM UML 5.42
The above update should run with error recovery to avoid a break in update if the SQL fails (especially for non-MySQL)
Updated by Andreas Müller over 1 year ago
- Subject changed from Update script for missing unit_ids (matrix) to Update script for missing measurment unit_ids (matrix)
- Status changed from In Progress to Resolved
- % Done changed from 30 to 70
Update script written
Updated by Andreas Müller over 1 year ago
- Target version changed from CDM UML 5.42 to Release 5.32
Updated by Andreas Müller over 1 year ago
- Status changed from Resolved to Closed
- % Done changed from 70 to 100
I guess this worked. If we still find a database with non-updated data we should update manually.
Actions