Project

General

Profile

bug #9785

Update script for missing unit_ids (matrix)

Added by Andreas Müller 28 days ago. Updated 6 days ago.

Status:
In Progress
Priority:
Highest
Category:
data
Target version:
Start date:
09/23/2021
Due date:
% Done:

30%

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 matrix Resolved 09/20/2021

History

#1 Updated by Andreas Müller 28 days ago

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

#2 Updated by Andreas Müller 28 days ago

  • Description updated (diff)

#3 Updated by Andreas Müller 6 days 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
)

#4 Updated by Andreas Müller 6 days 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

#5 Updated by Andreas Müller 6 days ago

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

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

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 40 MB)