https://dev.e-taxonomy.eu/redmine/
https://dev.e-taxonomy.eu/redmine/redmine/favicon.ico?1469191485
2021-09-23T11:49:36Z
EDIT Project Management
EDIT - bug #9785: Update script for missing measurment unit_ids (matrix)
https://dev.e-taxonomy.eu/redmine/issues/9785?journal_id=62959
2021-09-23T11:49:36Z
Andreas Müller
<ul><li><strong>Copied from</strong> <i><a class="issue tracker-4 status-5 priority-11 priority-default closed" href="/redmine/issues/9772">bug #9772</a>: Improve DTO usage for character matrix</i> added</li></ul>
EDIT - bug #9785: Update script for missing measurment unit_ids (matrix)
https://dev.e-taxonomy.eu/redmine/issues/9785?journal_id=62960
2021-09-23T11:50:44Z
Andreas Müller
<ul><li><strong>Description</strong> updated (<a title="View differences" href="/redmine/journals/62960/diff?detail_id=78337">diff</a>)</li></ul>
EDIT - bug #9785: Update script for missing measurment unit_ids (matrix)
https://dev.e-taxonomy.eu/redmine/issues/9785?journal_id=63270
2021-10-15T15:52:09Z
Andreas Müller
<ul><li><strong>% Done</strong> changed from <i>0</i> to <i>30</i></li></ul><p>An Update for MySQL:</p>
<pre>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
)
</pre>
EDIT - bug #9785: Update script for missing measurment unit_ids (matrix)
https://dev.e-taxonomy.eu/redmine/issues/9785?journal_id=63271
2021-10-15T15:53:16Z
Andreas Müller
<ul></ul><p>Some search SQL:</p>
<pre><code class="sql syntaxhl">
<span class="k">SELECT</span> <span class="n">deb</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="n">fe</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="n">fe</span><span class="p">.</span><span class="n">titleCache</span> <span class="c1">-- , COUNT(*) AS n</span>
<span class="k">FROM</span> <span class="n">DescriptionElementBase</span> <span class="n">deb</span> <span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">DefinedTermBase</span> <span class="n">fe</span> <span class="k">ON</span> <span class="n">fe</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">deb</span><span class="p">.</span><span class="n">feature_id</span>
<span class="k">WHERE</span> <span class="n">deb</span><span class="p">.</span><span class="n">DTYPE</span> <span class="o">=</span> <span class="s1">'QuantitativeData'</span> <span class="k">AND</span> <span class="n">deb</span><span class="p">.</span><span class="n">unit_id</span> <span class="k">IS</span> <span class="k">NULL</span>
<span class="k">AND</span> <span class="n">fe</span><span class="p">.</span><span class="n">id</span> <span class="k">IN</span> <span class="p">(</span>
<span class="k">SELECT</span> <span class="n">fe</span><span class="p">.</span><span class="n">id</span>
<span class="k">FROM</span> <span class="n">DefinedTermBase</span> <span class="n">fe</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">DefinedTermBase_MeasurementUnit</span> <span class="n">MN</span> <span class="k">ON</span> <span class="n">MN</span><span class="p">.</span><span class="n">Feature_id</span> <span class="o">=</span> <span class="n">fe</span><span class="p">.</span><span class="n">id</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">DefinedTermBase</span> <span class="n">mu</span> <span class="k">ON</span> <span class="n">MN</span><span class="p">.</span><span class="n">recommendedMeasurementUnits_id</span> <span class="o">=</span> <span class="n">mu</span><span class="p">.</span><span class="n">id</span>
<span class="k">WHERE</span> <span class="n">fe</span><span class="p">.</span><span class="n">DTYPE</span> <span class="o">=</span> <span class="s1">'Feature'</span> <span class="k">OR</span> <span class="n">fe</span><span class="p">.</span><span class="n">DTYPE</span> <span class="o">=</span> <span class="s1">'Character'</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">fe</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="n">mu</span><span class="p">.</span><span class="n">id</span>
<span class="k">HAVING</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="o">=</span> <span class="mi">1</span>
<span class="p">)</span>
<span class="c1">-- GROUP BY deb.feature_id</span>
<span class="p">;</span>
<span class="k">SELECT</span> <span class="n">fe</span><span class="p">.</span><span class="n">titleCache</span> <span class="n">feature</span><span class="p">,</span> <span class="n">fe</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="n">mu</span><span class="p">.</span><span class="n">titleCache</span><span class="p">,</span> <span class="n">mu</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">n</span>
<span class="k">FROM</span> <span class="n">DefinedTermBase</span> <span class="n">fe</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">DefinedTermBase_MeasurementUnit</span> <span class="n">MN</span> <span class="k">ON</span> <span class="n">MN</span><span class="p">.</span><span class="n">Feature_id</span> <span class="o">=</span> <span class="n">fe</span><span class="p">.</span><span class="n">id</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">DefinedTermBase</span> <span class="n">mu</span> <span class="k">ON</span> <span class="n">MN</span><span class="p">.</span><span class="n">recommendedMeasurementUnits_id</span> <span class="o">=</span> <span class="n">mu</span><span class="p">.</span><span class="n">id</span>
<span class="k">WHERE</span> <span class="n">fe</span><span class="p">.</span><span class="n">DTYPE</span> <span class="o">=</span> <span class="s1">'Feature'</span> <span class="k">OR</span> <span class="n">fe</span><span class="p">.</span><span class="n">DTYPE</span> <span class="o">=</span> <span class="s1">'Character'</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">fe</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="n">mu</span><span class="p">.</span><span class="n">id</span>
<span class="k">HAVING</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="o">></span> <span class="mi">1</span>
</code></pre>
EDIT - bug #9785: Update script for missing measurment unit_ids (matrix)
https://dev.e-taxonomy.eu/redmine/issues/9785?journal_id=63272
2021-10-15T15:55:22Z
Andreas Müller
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>In Progress</i></li><li><strong>Target version</strong> changed from <i>Release 5.27</i> to <i>CDM UML 5.43</i></li></ul><p>The above update should run with error recovery to avoid a break in update if the SQL fails (especially for non-MySQL)</p>
EDIT - bug #9785: Update script for missing measurment unit_ids (matrix)
https://dev.e-taxonomy.eu/redmine/issues/9785?journal_id=66799
2022-07-22T15:45:02Z
Andreas Müller
<ul><li><strong>Subject</strong> changed from <i>Update script for missing unit_ids (matrix)</i> to <i>Update script for missing measurment unit_ids (matrix)</i></li><li><strong>Status</strong> changed from <i>In Progress</i> to <i>Resolved</i></li><li><strong>% Done</strong> changed from <i>30</i> to <i>70</i></li></ul><p>Update script written</p>
EDIT - bug #9785: Update script for missing measurment unit_ids (matrix)
https://dev.e-taxonomy.eu/redmine/issues/9785?journal_id=66800
2022-07-22T15:46:59Z
Andreas Müller
<ul><li><strong>Target version</strong> changed from <i>CDM UML 5.43</i> to <i>Release 5.32</i></li></ul>
EDIT - bug #9785: Update script for missing measurment unit_ids (matrix)
https://dev.e-taxonomy.eu/redmine/issues/9785?journal_id=66813
2022-07-22T22:49:57Z
Andreas Müller
<ul><li><strong>Status</strong> changed from <i>Resolved</i> to <i>Closed</i></li><li><strong>% Done</strong> changed from <i>70</i> to <i>100</i></li></ul><p>I guess this worked. If we still find a database with non-updated data we should update manually.</p>