Deprecated code information: valid from

[cessda-bitbucket-importer]

Original report on BitBucket by Taina Jääskeläinen.

---

Deprecated code information:

Code value: code6.child2 Valid from 2015-09-22 to 2022-07-26

‌ URI: https://vocabularies.cessda.eu/vocabulary/TestingCV2_[CODE]?v=2.0

‌ Descriptive term: Code 6: Child 2

‌ Replaced by code: code16.child2 URI: https://vocabularies.cessda.eu/vocabulary/TestingCV2_[CODE]?v=2.0

‌

This is otherwise finalised in #421except for the ‘valid from’ information. Stefan said:

If ‘From’ is missing, it’s because there’s no information about the version, in which the particular code was introduced. This applies for all the codes added prior to the code deprecation feature. Since this feature, each added code has a new hidden attribute ‘introduced_in_version_id’ allowing to get the valid ‘From’ value. I think that it should be possible to create a script that discovers the versions in which the codes with missing ‘From’ were discovered.

‌

We put this on hold till the top priority issues for 3.0.0 were resolved. Not a major issue.

[cessda-bitbucket-importer]

Original comment by Taina Jääskeläinen.

---

Related to #425.

[Lorenz1983]

@matthew-morris-cessda What about the issue? Has it been resolved or can it be closed?

[pakoselo]

@Lorenz1983 I add also @Stifo to this issue, because he is the one who worked on it. There is some description in the first post by Taina from bitbucket system. So this is realy old issue. Maybe @stifo can help with it.

[Stifo]

The following script initializes introduced_in_version_id field in concept table with the version id, in which the particular concept was created. This results in valid from information to be present for all the concepts created before the deprecate code feature addition.

The script was tested in dev.

Background: The field introduced_in_version_id was added into the concept table with the code deprecation feature at the time, when different vocabularies, versions and concepts already existed. This field was set to NULL by default, therefore the valid from information was missing for the existing concepts. At the time, we have put this issue on hold as it had a low priority.

This script have to be executed over the production database to take effect. @matthew-morris-cessda could you please execute it? Thanks.

LOCK TABLES version concept WRITE;
START TRANSACTION;

update
  concept dst,
  (
    with t as (
      select 
        v.vocabulary_id as vocabulary_id,
        v.id as version_id,
        c.id as concept_id,
        c.notation as concept_notation,
        c.introduced_in_version_id as concept_initial_version
      from concept c
      left join version v on 
        c.version_id=v.id
      where
        c.introduced_in_version_id is null
      order by
        vocabulary_id,
        version_id,
        concept_id,
        c.notation
    )
    select
      min(version_id) as introduced_in_version,
      vocabulary_id,
      concept_notation
    from t
    group by 
      concept_notation,
      vocabulary_id
  ) src,
  version
set
  dst.introduced_in_version_id=src.introduced_in_version
where
  src.concept_notation=dst.notation
    and
    src.vocabulary_id=version.vocabulary_id
    and
    dst.version_id=version.id;

COMMIT;
UNLOCK TABLES;

Example of the result

[matthew-morris-cessda]

The SQL script has been run on production