Project

General

Profile

Actions

bug #9785

closed

Update script for missing measurment unit_ids (matrix)

Added by Andreas Müller over 1 year ago. Updated 6 months ago.

Status:
Closed
Priority:
Highest
Category:
data
Target version:
Start date:
Due date:
% Done:

100%

Estimated time:
Severity:
normal
Found in Version:
Tags:

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

Copied from EDIT - bug #9772: Improve DTO usage for character matrixClosedKatja Luther

Actions
Actions #1

Updated by Andreas Müller over 1 year ago

  • Copied from bug #9772: Improve DTO usage for character matrix added
Actions #2

Updated by Andreas Müller over 1 year ago

  • Description updated (diff)
Actions #3

Updated by Andreas Müller over 1 year 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
)
Actions #4

Updated by Andreas Müller over 1 year 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
Actions #5

Updated by Andreas Müller over 1 year ago

  • Status changed from New to In Progress
  • Target version changed from Release 5.27 to CDM UML 5.36

The above update should run with error recovery to avoid a break in update if the SQL fails (especially for non-MySQL)

Actions #6

Updated by Andreas Müller 6 months 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

Actions #7

Updated by Andreas Müller 6 months ago

  • Target version changed from CDM UML 5.36 to Release 5.32
Actions #8

Updated by Andreas Müller 6 months 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

Also available in: Atom PDF