Regenerate citations and update version numbers inside

[MajaDolinar]

Citations currently do not display the proper version number. See image for details

[Stifo]

@john-shepherdson @Joshua-cessda-admin

The following SQL script updates version numbers in citation texts for all the published versions since the 3-digit migration. It should be executed over the DB.

The script relies on MySQL's regexp_replace function available from v8.0+.

use cvs;

SET @SQL_SAFE_UPDATES=@@SQL_SAFE_UPDATES;
SET SQL_SAFE_UPDATES=0;

SET @min_id = (select min(id) from version where regexp_like(version_notes, '(?mi)^.*?To align with a new versioning system.+$'));

update
    version
set
    citation = regexp_replace(
        regexp_replace(
            citation,
            'Version[:blank:]+([0-9]+\.[0-9]+(\.[0-9]+)?)',
            concat('Version ', number)
        ),
        '(urn:.+?)([0-9]+\.[0-9]+(\.[0-9]+)?)',
        concat('$1', number)
    )
where
    status = 'PUBLISHED'
    and
    id >= @min_id;
;

SET SQL_SAFE_UPDATES=@SQL_SAFE_UPDATES;

[MajaDolinar]

Have you executed this code on staging? It still does not correspond to version selected. Available from changes accordingly, whereas the Citation information does not change.

[john-shepherdson]

@matthew-morris-cessda can you take a look in Joshua's absence?

[MajaDolinar]

@matthew-morris-cessda and @john-shepherdson what is happening then with this part? I cannot close it and sign it for relelase if it is not done.

[matthew-morris-cessda]

@MajaDolinar I have run the script against CVS Staging, see attached screenshot

[MajaDolinar]

Thank you @matthew-morris-cessda I see the issue is resolved, so I am closing this issue. Ready for 3.1.0 release.