Saving Article metadata changes empty field behaviour in database

Hi there,

as it is already pointed out in the old forum, author data imported via xml is modified when saving the article metadata in the backend, even if the author fields themselves have not been changed. I just stumbled across this situation on a current OJS release. This creates a problem when the author of one article has written more articles than that. In this case, his name is duplicated in ‘browse by author’.

Before I try to work out a fix for this (imho it would be about changing the behavior empty or nonexisting element’s data is saved at XML import), I’d like to ask whether there has been any other movement around this, other than what is available in the code.

Regards,
krollbsb

Hi @ojsbsb,

Can you compare the authors and author_settings tables for an article before and after saving the metadata? I wonder if something gets converted from SQL null to an empty string or similar.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi,

When importing from a XML file where there is no affiliation element, there will be no database entry. When then editing the article metadata and leaving the affiliation empty there will be an empty entry in the database. This leads to the difference of the authors in the appropriate SQL query i.e. result – the locale and primary_locale fields are different: for the imported author it is NULL, for the edited author it is the actual locale.

@asmecher, I am not sure how to solve it best:
a) either to treat the empty XML affiliation elements differently i.e. to insert the empty entries for all journal locales in the database, or
b) not to compare the locale and primary_locale i.e. to remove those columns/fields from the SQL query in the AuthorDAO::getAuthorsAlphabetizedByJournal?

Best wishes!
Bozana

The third possiblity would be for the users to always consider/insert XML affiliation elements for all journal languages with an empty space, e.g.:


@asmecher: do you think we should solve it with either a) or b) from above or shall we communicate this third solution to the users?

Hi @bozana,

I think the best solution would probably be to adjust the query that fetches all users and attempts to disambiguate them, so that it no longer distinguishes between NULL and ''. I think wrapping the column in the SELECT clause with a COALESCE(column_name, '') would probably be all that’s needed.

Regards,
Alec Smecher
Public Knowledge Project Team

The problem is not NULL and ’ ’ but the locale and primary_locale and NULL, s. for example the attached query result. Do you still think the same – I am not able to think at the moment, but wanted to ask however… Then I can try it tomorrow… :slight_smile:

Hi @bozana,

Ah, I see… I wonder if you could do:

CASE WHEN affiliiation_l IS NULL THEN NULL ELSE asl.locale END AS locale

…and…

CASE WHEN affiliation_pl IS NULL THEN NULL ELSE aspl.locale END AS primary_locale

Generally I’d like to keep this very targeted (rather than a big update to the forms or the database itself), as it’s only to support the current disambiguation scheme OJS 2.x uses, which is a known problem and should be replaced using something better like ORCiDs.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

The aliases affiliation_l and affiliation_pl in case statement didn’t work, but:
CASE WHEN asl.setting_value = ‘’ THEN NULL ELSE asl.locale END AS locale
and
CASE WHEN aspl.setting_value = ‘’ THEN NULL ELSE aspl.locale END AS primary_locale
S. authors disambiguation scheme · Issue #715 · pkp/pkp-lib · GitHub i.e. pkp/pkp-lib#715 fix authors disambiguation scheme in browse plugin by bozana · Pull Request #588 · pkp/ojs · GitHub

Best!
Bozana

Hi @bozana,

Thanks! Moving the discussion over to that pull request.

Regards,
Alec Smecher
Public Knowledge Project Team