You are viewing the PKP Support Forum | PKP Home Wiki

Authors metadata table in database

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
The Public Knowledge Project Support Forum is moving to http://forum.pkp.sfu.ca

This forum will be maintained permanently as an archived historical resource, but all new questions should be added to the new forum. Questions will no longer be monitored on this old forum after March 30, 2015.

Authors metadata table in database

Postby Almadl » Wed Sep 26, 2012 8:35 am

Hi all.
things are going good in my office: a lot of University of Bologna journals are asking us to move to OJS :-)
Although this is a good thing, there much work to do: for example, I succesfully imported a list of 25 issues with the new OJS of this journal (http://journals.unibo.it/riviste/index. ... tica/index),
but as you can see here the Authors list contains a lot of authors written with capital letters (http://journals.unibo.it/riviste/index. ... ch/authors).

We have tried to find the right table, in the db, which contains all the metadata for Authors:
it could be fairly easy to run a script to correct automatically the case in Name and Last name fields, and this could save us a lot of time
(as you know authors double easily in OJS for mismatched metadata, so there will be a lot of manual polishing anyway...).

Although, we *did* not find that table, we don't know where those authors metadata are in the db.

Could you help us in this? Do you think this approach is feasible?

Thank you,

Posts: 17
Joined: Tue Jan 24, 2012 6:08 am

Re: Authors metadata table in database

Postby asmecher » Wed Sep 26, 2012 8:53 am

Hi AlmaDL,

If you're using a recent version of OJS, the table you're looking for is "authors". Converting the names in the database is probably the easiest way, but beware of course of names that have more complicated capitalization, such as McSomething or O'Something.

Alec Smecher
Public Knowledge Project Team
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm

Re: Authors metadata table in database

Postby Almadl » Fri Oct 05, 2012 1:48 am

Hi, the Authors table is as follows

| author_id
| submission_id
| first_name
| middle_name
| last_name
| country
| email
| url
| primary_contact
| seq
| user_group_id

My issue is that I am looking for a relationship between Authors and Journals, because we have a multifarm installation: I need to know all the authors given a particular journal id, so that I can run a script on those authors and change the case, only in that journal.
Do you have an idea how I could do that?

Thank you very much for your help

Posts: 17
Joined: Tue Jan 24, 2012 6:08 am

Re: Authors metadata table in database

Postby JasonNugent » Fri Oct 05, 2012 4:52 am

Hi Andrea,

The authors table has a 'submission_id' column which is where the article id stored. You can do a query between authors and articles to get the authors for a particular article, or extend that SQL join to include the journals table and join on articles.journal_id and journals.journal_id. Doing that would give you the authors for a particular journal.

Site Admin
Posts: 910
Joined: Tue Jan 10, 2006 6:20 am

Re: Authors metadata table in database

Postby Almadl » Thu Jan 10, 2013 9:15 am

I forgot to say that I solved my issue, so thanks.

However, i would like to take the opportunity to discuss the general issue: authors metadata.
This must be addressed before (I didn't find anything relevant/up to date though), but managing authors metadata (especially from archived issues)
it is a real pain.
I work with 17 journals, and all the editorial teams don't use the whole online produre, but they only upload galleys for publication.
This means that everytime there are inconsistencies, and authors are often duplicated in the "Browse by author" list.
Is it planned to develop a sort of authority file/profile structure for authors?
Have you had other feedback on this particular topic?

One "patch" that could probably solve my main problem (duplication of authors)
could be to avoid using email as a "duplication parameter", which means to let same authors to have different emails but be the same author nonetheless.
Could it be feasible? Can I change this in my installation?

thank you for your work and availability (as usual).

Posts: 17
Joined: Tue Jan 24, 2012 6:08 am

Return to OJS Technical Support

Who is online

Users browsing this forum: Bing [Bot], Yahoo [Bot] and 1 guest