Change the database design to support later additions

[ajnyga]

The database should be changed to a four table combination. This change will make the plugin more combatible with later modifications like OpenAIRE and Datacite support even if we do not support those with the first release.

This may need more consideration, but:

funder

funder_id [int]
funder_identifier [string, doi or something else]*
submission_id
context_id

funder_settings

funder_id
locale
setting_name
setting_value
setting_type

funder_award

funder_award_id [int]
funder_id [int]
funder_award_number [string]

funder_award_settings**

funder_award_id
locale
setting_name
setting_value
setting_type

* this is probably not needed. We just need some checks for the OpenAIRE data for showing it in OAI, basically when fundingProgramme exists, we are dealing with OpenAIRE data. For Datacite/Crossref combatibility, we need to make sure if Datacite is ok with receiving
** or add awardTitle and awardURI fields to funder_award table, but this would prevent localisation etc. I any case adding the awardTitle and awardURI option to the form will involve chancing the way that data is entered because we need to pair up the awardNumber and the awardTitle.

Example of saved CrossRef funding data:
funder: '125', 'crossref', 'http://dx.doi.org/10.13039/501100000780'
funder_settings: '125', 'en_US', 'funderName', 'European Commission', 'string'
funder_award: '241', '125', '456566'

Example of saved OpenAIRE funding data:
funder: '126', 'openaire', 'http://dx.doi.org/10.13039/501100000780'
funder_settings: '126', 'en_US', 'funderName', 'European Commission', 'string'
funder_settings: '126', 'en_US', 'fundingProgramme', 'FP7', 'string'
funder_award: '254', '126', 'DE53522'
funder_award_settings: '254', 'en_US', 'awardTitle', 'My fabulous research project', 'string'

Example of saved Datacite funding data:
funder: '127', 'datacite', 'http://dx.doi.org/10.13039/501100000780'
funder_settings: '127', 'en_US', 'funderName', 'European Commission', 'string'
funder_award: '322', '127', 'DE53522'
funder_award_settings: '322', 'en_US', 'awardTitle', 'My fabulous research project', 'string'
funder_award_settings: '322', '', 'awardURI', 'http://www.awardinfo.com/is/here', 'string'

CrossRef		Datacite		OpenAIRE
funder_identifier	=	funderIdentifier	=	missing
funder_name	=	funderName	=	Funder*
award_number	=	awardNumber	=	projectID**
missing	=	awardTitle	=	ProjectName
missing	=	awardURI	=	missing
missing	=	missing	=	ProjectAcronym
missing	=	missing	=	FundingProgramme***

* Only two options: European Commission (EC) or Wellcome Trust (WT)? For OAI the acronyms are used.
** Not sure if the projectID is/can be the exact same thing?
*** only two options: FP7 and H2020?

Sources:
https://support.crossref.org/hc/en-us/articles/214360746-Funding-data-overview
https://guidelines.openaire.eu/en/latest/literature/field_projectid.html
https://schema.datacite.org/meta/kernel-4.0/

[bozana]

Hmmm... I almost tend to defer this change till we are 100% sure what all organizations need i.e. till we implement all the other solutions:
Maybe the funder_settings table is not needed:
-- we do not know the language of the funder name -- the Crossref funders API response do not contain this information, thus for now the funder name
-- the FP7 would be a sub-organization for Crossref and thus it should only be saved and deposited
funder_award and funder_award_settings seem to be needed, but in order to support them fully, a funder award grid has to be implemented. Once we know what exactly OpenAIRE needs there could be some further changes needed maybe.
Thus either to leave everything as is till all the other work can be done and because it should not be a problem to move the existing awards in the new table, or to just consider one more DB table funder_award for now (because it fits the current situation)?

[ajnyga]

I am thinking of the people who start to use this plugin when it is released.

If we will need changes to the database later on, how do we convert the one table data to a more complex scheme? I have not seen a plugin in OJS that has an upgrade/update function?

With the four table configuration we would be prepared to basically any additions later on. Of course with just CrossRef onboard at the moment, we would not even need the funder_award_settings table, but with OpenAIRE it is a different story.

For now, I would stick with the current form, We would just have a possibility for more complex solution in the backend.

Edit: I do not think that we need the funder identifier. The whole point is after all to create an uniform way of saving funding data.

[ajnyga]

-- the FP7 would be a sub-organization for Crossref and thus it should only be saved and deposited
funder_award and funder_award_settings seem to be needed, but in order to support them fully, a funder award grid has to be implemented. Once we know what exactly OpenAIRE needs there could be some further changes needed maybe.

Yes, FP7 is probably an suborg to EC. But to my knowledge the Wellcome Trust is not.

[ajnyga]

@bozana "If we will need changes to the database later on, how do we convert the one table data to a more complex scheme? I have not seen a plugin in OJS that has an upgrade/update function?"

If there is a positive answer to that question, then we can go with the current db schema, but if not, then I still suggest that we should prepare for different kinds of funding data with the schema already now - even if we do not actually use it's capabilites yet.

[bozana]

Currently there is no good function of plugins upgrade -- you are right. Thus, yes, maybe it would be better to separate them already... :-)

[ajnyga]

Do you think the four table setup is ok? It would be the most flexible solution even if we will never have support for multilingual funder names etc. The two most important things are still getting saved to the two main tables: that is the funder_identifier and the grant_id.

If the four tables is ok, and you have a lot of other things to do, I could merge the pr you made earlier and the add the support for the new database scheme. After that you could still tackle the Datacite support, if you think that there is enough time for.

[asmecher]

We're having a bigger discussion about upgrade toolsets on this issue: pkp/pkp-lib#2493

Long story short, "migrations" are the new standard for database version management. I believe the Laravel query builder we're recently started using includes a migration toolset (or it could be easily added, as it's part of the same suite). Since we've never had a good solution for plugin-based database management, it might make sense to pilot an ADODB replacement by providing migration tools for plugins.

[bozana]

@ajnyga, yes 4 tables would be OK. If we realize that the _settings table are not used at all, it's OK -- the we will find a way to remove them then one day... :-)
I think I can finish DataCite integration and if you find some time to work on this it will be great! Else, the plugin can also be added to the Plugin Gallery slightly after the release -- so not such a hurry :-) -- the priority is to finish all needed changes in the core... :-)

[ajnyga]

Great, so I will merge your pr and then add support for the four table db scheme. After that I will let you know, but it should be tomorrow already.

[ajnyga]

@bozana
I added a new branch with support for the four table schema: https://github.com/ajnyga/funding/tree/change-database-scheme

However, for some reason I can not get updateDataObjectSettings to work meaning that the funder_settings table is not getting updated: https://github.com/ajnyga/funding/blob/change-database-scheme/classes/FunderDAO.inc.php#L164

The object should be ok (I left a debug print_r there) but nothing happens. Maybe this is something very apparent, but I could not figure it out yet. You probably can?

I added FunderAward object and DAO, but these are not getting used that much yet. Also, feel free to fix any solutions I made that do not fit the PKP coding standards.

[ajnyga]

ah, I think I got it, forgot to add getLocaleFieldNames

[ajnyga]

Yes, getAdditionalFieldNames fixed the problem.

I noticed that the CrossRef API does return the locale of the primary funder name and also secondary funder names for other locales.

What do you think, should we add support for those now or later?

Of course the most important thing is to have the DOI, other data is just descriptive.

[ajnyga]

@bozana if you can that branch for comments and changes. If everything is ok, then you can proceed with Datacite support.

[bozana]

@ajnyga, I'll take a look...
Where did you see that Crossref API supports it -- could you post the link -- in the search/query API we are using?

[bozana]

That looks good! 👍 I had just a few comments... :-) Thanks a lot!!!

[ajnyga]

ah, I got confused. The API does not return a locale, you can just see it if you look at a specific DOI, like http://data.crossref.org/fundingdata/funder/10.13039/501100004756

[bozana]

Yes, hmmm... maybe then to leave the title as an unlocalized setting? What do you think?

[ajnyga]

@bozana If everythning is ok now, I will merge the branch to master.

[bozana]

👍 Yes, please do :-)

[ajnyga]

Merged to master (after fixing yet another typo)