CVS API shows "uri" null

[MajaDolinar]

Our programmer that is trying to use the CVS API with Dataverse reported to me that the URI is still not displaying through the API. She has tested this yesterday after the new release. Please have a look at this and see what is causing the issue. It should be displayed now, since it is finally working on production. Not sure who to assign to this, so I am basically assigning you all.

[pakoselo]

What is the API endpoint she is trying, please? Just to check directly the requesting API.

On Thu, Mar 9, 2023 at 9:35 AM MajaDolinar @.***> wrote:

Our programmer that is trying to use the CVS API with Dataverse reported to me that the URI is still not displaying through the API. She has tested this yesterday after the new release. Please have a look at this and see what is causing the issue. It should be displayed now, since it is finally working on production. Not sure who to assign to this, so I am basically assigning you all.

— Reply to this email directly, view it on GitHub https://github.com/cessda/cessda.cvs.two/issues/559, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB23PTTLU46INU3YZDFJB7DW3GI33ANCNFSM6AAAAAAVUY7ZSI . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[john-shepherdson]

Just tried it via the Swagger docs (https://api.tech.cessda.eu/) and ran these queries, which returned results:

https://vocabularies.cessda.eu/v2/codes/TopicClassification/4.0/en

https://vocabularies.cessda.eu/v2/search/codes?agency=CESSDA&lang=en&query=Economics&size=20&vocab=TopicClassification

[pakoselo]

So we are talking about the URI to the individual codes?

On Thu, Mar 9, 2023 at 9:46 AM John Shepherdson @.***> wrote:

Just tried it via the Swagger docs (https://api.tech.cessda.eu/) and ran these queries, which returned results:

https://vocabularies.cessda.eu/v2/codes/TopicClassification/4.0/en

https://vocabularies.cessda.eu/v2/search/codes?agency=CESSDA&lang=en&query=Economics&size=20&vocab=TopicClassification

— Reply to this email directly, view it on GitHub https://github.com/cessda/cessda.cvs.two/issues/559#issuecomment-1461580623, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB23PTQ4FAYJ3PFBJTUEXOLW3GKGPANCNFSM6AAAAAAVUY7ZSI . You are receiving this because you commented.Message ID: <cessda/cessda. @.***>

[pakoselo]

Another thing

https://vocabularies-dev.cessda.eu/v2/search/codes?agency=CESSDA&lang=en&query=Economics&size=20&vocab=TopicClassification

https://vocabularies-dev.cessda.eu/v2/codes/TopicClassification/4.0/en

works well and shows the 3 digit versioning. Probably there were some tests on the dev, so the numbers differs, but this is not the issue.

So I am not sure about the latest code on the production (and also the DB, but if it shows on the info page, which shows right information) because there is no 3 digit versions in the URIs comparing with dev. @Joshua-cessda-admin can you please provide us (Stefan and me) latest production DB please?

[Joshocan]

@Stifo @pakoselo @john-shepherdson I think Her complaint was not because it does not return anything. The "uri" fields in the "Results" array show "null". See below { "@context": { "skos": "http://www.w3.org/2004/02/skos/core#", "owl": "http://www.w3.org/2002/07/owl#", "uri": "@id", "type": "@type", "onki": "http://schema.onki.fi/onki#", "results": { "@id": "onki:results", "@container": "@list" }, "versionInfo": "skos:versionInfo", "notation": "skos:notation", "prefLabel": "skos:prefLabel", "definition": "skos:definition", "@language": "en" }, "versionInfo": "4.2.1", "results": [ { "uri": null, "type": [ "skos:Concept" ], "notation": "Economics", "prefLabel": "ECONOMICS", "lang": "en", "vocab": "TopicClassification" }, { "uri": null, "type": [ "skos:Concept" ], "notation": "Economics.EconomicConditionsAndIndicators", "prefLabel": "Economic conditions and indicators", "definition": "Economic indicators indicate in which direction an economy is headed and include, for example, GDP growth rate, employment, inflation and interest rate, consumer and investor activity, household debt. Use note: This topic is also used for economic conditions of individuals and households which are surveyed or registered.", "lang": "en", "vocab": "TopicClassification" }, { "uri": null, "type": [ "skos:Concept" ], "notation": "Economics.EconomicSystemsAndDevelopment", "prefLabel": "Economic systems and development", "definition": "Data covering capitalist, socialist and other economic systems and data on, for example, international development programs.", "lang": "en", "vocab": "TopicClassification" }, { "uri": null, "type": [ "skos:Concept" ], "notation": "Economics.EconomicPolicyPublicExpenditureAndRevenue", "prefLabel": "Economic policy, public expenditure and revenue", "definition": "Data on economic policy, including budget and fiscal policy, public expenditure and public revenue.", "lang": "en", "vocab": "TopicClassification" }, { "uri": null, "type": [ "skos:Concept" ], "notation": "Economics.IncomePropertyAndInvestmentSaving", "prefLabel": "Income, property and investment/saving", "definition": "Data on the accumulation of personal assets, including income, property, investments and savings.", "lang": "en", "vocab": "TopicClassification" }, { "uri": null, "type": [ "skos:Concept" ], "notation": "Economics.ConsumptionAndConsumerBehaviour", "prefLabel": "Consumption and consumer behaviour", "definition": "Refers to the act of acquiring and/or using something to meet the needs and wants of a person, community or society. Data in this category include patterns of consumer behaviour, changes in them, and data on consumer ethics. For example, survey data on how often people buy certain items; when and where they shop; whether ethical considerations affect their consumer choices.", "lang": "en", "vocab": "TopicClassification" } ] }

I will send a copy of the production DB anyway.

[pakoselo]

@Joshua-cessda-admin I got it, but the question is why are there differences between staging and production?

[Joshocan]

Yes @pakoselo you are right. there are differences in the results. Let me know what we miss so we can correct it in the database. However, I think the way we apply changes to the DB is not sustainable. We need to codify the changes we want to see in the DB. We should talk more about this in the next sprint meeting. @MajaDolinar Please can you add this agenda?

[MajaDolinar]

@Joshocan added to the notes for the Sprint

[Stifo]

the problem with null uris is due to a bug fixed by #526 when uris were not generated. i'll prepare similar sql script as in #484 to fix it. basically, uris for the migrated codes need to be generated and it will be ok.

[Stifo]

@john-shepherdson @Joshua-cessda-admin

The following SQL script re-generates URIs for published concepts when null. It should be executed over the DB and when successfully finished, re-indexing of elasticsearch indexes should be invoked from the CVS -> Admin -> Maintenance site.

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;

update concept as act_concept
    left join concept as prev_concept on
        act_concept.previous_concept = prev_concept.id
    left join version as act_version on
        act_concept.version_id = act_version.id
    left join version as prev_version on
        prev_concept.version_id = prev_version.id
set
    act_concept.uri = case
        when act_concept.uri is null then 
            regexp_replace(
                prev_concept.uri,
                regexp_replace(
                    prev_version.number,
                    '([0-9]+)\.([0-9]+)(\.[0-9]+)?',
                    '$1\\\\.$2(\\\\.[0-9]+)?'
                ),
                act_version.number
            )
        else act_concept.uri
    end
where
    act_concept.uri is null
    and
    act_version.status = 'PUBLISHED'
    and
    prev_version.status = 'PUBLISHED'
    and
    prev_concept.uri is not null
;

SET SQL_SAFE_UPDATES=@SQL_SAFE_UPDATES;

[MajaDolinar]

This should be included in the next release. Please execute the prepared code - should not be pushed to 3.3.0. @john-shepherdson @Joshua-cessda-admin

[john-shepherdson]

@matthew-morris-cessda needs to be done as part of tomorrow's release

[MajaDolinar]

This I cannot test, so hopefully it will be working with the 3.1.0 release.

[matthew-morris-cessda]

DB fix applied

[MajaDolinar]

Apparently, the URI is still null. @matthew-morris-cessda have you run the fix? Please check this ASAP

[pakoselo]

Apparently, the URI is still null. @matthew-morris-cessda have you run the fix? Please check this ASAP

@MajaDolinar can you please provide as with CV, which URIs are nulls? I have tested few of them and they aren't nulls.

[matthew-morris-cessda]

I applied the DB fix yesterday when I closed the issue

[zibertg]

Hi!

For example, https://vocabularies.cessda.eu/v2/search/codes?agency=CESSDA&lang=en&query=Economics&size=20&vocab=TopicClassification shows uri: null

meanwhile, it's fine on dev: https://vocabularies-dev.cessda.eu/v2/search/codes?agency=CESSDA&lang=en&query=Economics&size=20&vocab=TopicClassification

[pakoselo]

I applied the DB fix yesterday when I closed the issue

@matthew-morris-cessda where there any errors during update?

[pakoselo]

@matthew-morris-cessda, @MajaDolinar @zibertg apparently there is probably another type of error. If you look into https://vocabularies.cessda.eu/vocabulary/TopicClassification you can see that the latest version is 4.2.2 and actually there is no version labeled 4.2.1 (this is what this query: https://vocabularies.cessda.eu/v2/search/codes?agency=CESSDA&lang=en&query=Economics&size=20&vocab=TopicClassification returns). So there is completely different problem with the database. @matthew-morris-cessda can you please provide us (me and @Stifo ) latest dump of the production database? I assume the source code is same on dev and production sites, the problem must be in the database.

[matthew-morris-cessda]

I've attached a copy of the latest production database

Vocabularies Service_CVS Database Export (Issue 559) - 2023-04-05 (13_24_43).zip

[Stifo]

@matthew-morris-cessda please, try running reindexing from the admin maintenance page. it seems, that the cause of the problem is an old index.

[Joshocan]

@Stifo Re-indexing is completed in CVS production.

[Stifo]

thanks @Joshocan. problem solved.