OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



SQL for selecting articles

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
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

SQL for selecting articles

Postby piotreba » Tue Jul 08, 2014 11:29 am

Hello,

What is the SQL statement for selecting articles from particular issue?




Thanks,

Peter
piotreba
 
Posts: 157
Joined: Mon Dec 13, 2010 3:29 pm

Re: SQL for selecting articles

Postby asmecher » Tue Jul 08, 2014 11:39 am

Hi Peter,

Are you looking to modify an existing query or write a new one? To determine which articles are in an issue, join articles.article_id to published_articles.article_id and published_articles.issue_id to issues.issue_id.

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

Re: SQL for selecting articles

Postby piotreba » Tue Jul 08, 2014 11:50 am

Thank you, in fact I need to update copyright year for back issues for different journals because after updating to the latest OJS all copyright years became 2014.
piotreba
 
Posts: 157
Joined: Mon Dec 13, 2010 3:29 pm

Re: SQL for selecting articles

Postby asmecher » Tue Jul 08, 2014 11:56 am

Hi piotreba,

Yes, we're working out a better solution to this at http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=8578. The current approach to assigning copyrights after upgrade is flawed. Can you describe how you want to approach this? I can write up some specific SQL; it'll probably be useful for others.

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

Re: SQL for selecting articles

Postby piotreba » Tue Jul 08, 2014 12:05 pm

Alec,


I tried something like this:

Code: Select all
UPDATE article_settings SET setting_value = '2013' WHERE setting_name='copyrightYear' AND article_id IN (SELECT article_id FROM published_articles WHERE issue_id IN (SELECT issue_id FROM issues WHERE journal_id = 1 AND year = '2013'))


But I noticed that some papers published in the 2013 issues have still 2014 in copyright year.
piotreba
 
Posts: 157
Joined: Mon Dec 13, 2010 3:29 pm

Re: SQL for selecting articles

Postby asmecher » Tue Jul 08, 2014 12:08 pm

Hi piotreba,

The copyright dates are assigned when the article is viewed, so it's possible that you have some articles that haven't yet been viewed when you run the update. Do you want to assign a copyright year to all published articles? If so, what do you want to use for that year -- the article's date published, for example? Or date submitted?

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

Re: SQL for selecting articles

Postby piotreba » Tue Jul 08, 2014 12:19 pm

Alec, basically I want to assign the copyright year (for all published papers) based on published year. BUT, it is not the case for all papers, since for the older ones the updated licenses for displaying within OJS were achieved later (e.g. for papers from 1980 they were achieved in 2013), so it would be nice to update these dates based on, e.g. volume numbers.
piotreba
 
Posts: 157
Joined: Mon Dec 13, 2010 3:29 pm

Re: SQL for selecting articles

Postby asmecher » Tue Jul 08, 2014 12:30 pm

Hi Peter,

Backup your database before running any of these, please.

Use this to reset ALL copyright years to the dates the articles were published, when available:
Code: Select all
REPLACE INTO article_settings (article_id, setting_name, setting_value, setting_type) SELECT pa.article_id, 'copyrightYear', YEAR(pa.date_published), 'string' FROM published_articles pa WHERE pa.date_published IS NOT NULL;
To assign dates by issue (e.g. assigning "2014" to articles in issue_id 10):
Code: Select all
REPLACE INTO article_settings (article_id, setting_name, setting_value, setting_type) SELECT pa.article_id, 'copyrightYear', 2014, 'string' FROM published_articles pa WHERE pa.date_published IS NOT NULL AND pa.issue_id=10;
Note that you will also want to assign the copyright holder and license URL:
Code: Select all
REPLACE INTO article_settings (article_id, locale, setting_name, setting_value, setting_type) SELECT pa.article_id, 'en_US', 'copyrightHolder', 'Simon Fraser University', 'string' FROM published_articles pa WHERE pa.date_published IS NOT NULL;

REPLACE INTO article_settings (article_id, setting_name, setting_value, setting_type) SELECT pa.article_id, 'licenseUrl', 'http://creativecommons.org/licenses/by/4.0/', 'string' FROM published_articles pa WHERE pa.date_published IS NOT NULL;
In the above examples, all published content will get a copyright holder of "Simon Fraser University" and a license of CC-by-4.0.

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

Re: SQL for selecting articles

Postby piotreba » Tue Jul 08, 2014 12:35 pm

Alec, what if the copyright holders are the authors?

Will they be set automatically as authors when the paper is viewed?
piotreba
 
Posts: 157
Joined: Mon Dec 13, 2010 3:29 pm

Re: SQL for selecting articles

Postby piotreba » Tue Jul 08, 2014 12:50 pm

Ok Alec, I run just the first statement, but I had to run it twice, after first run I had still incorrect years.
piotreba
 
Posts: 157
Joined: Mon Dec 13, 2010 3:29 pm

Re: SQL for selecting articles

Postby piotreba » Tue Jul 08, 2014 12:57 pm

Hmm, still I find articles where the date was not updated. Is your statement (the first you provided) valid only for articles that were viewed?
piotreba
 
Posts: 157
Joined: Mon Dec 13, 2010 3:29 pm

Re: SQL for selecting articles

Postby asmecher » Tue Jul 08, 2014 1:06 pm

Hi Peter,

To give all copyrights to authors for published content, use this one:
Code: Select all
REPLACE INTO article_settings (article_id, locale, setting_name, setting_value, setting_type) SELECT pa.article_id, 'en_US', 'copyrightHolder', GROUP_CONCAT(CONCAT(a.last_name, ', ', a.first_name) SEPARATOR '; '), 'string' FROM authors a, published_articles pa, issues i WHERE a.submission_id = pa.article_id AND pa.issue_id = i.issue_id GROUP BY a.submission_id;
Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 8869
Joined: Wed Aug 10, 2005 12:56 pm

Re: SQL for selecting articles

Postby piotreba » Tue Jul 08, 2014 1:15 pm

Thank you Alec,


but the problem remains for papers not viewed. I hope it will be possible to set valid dates for those?




Peter
piotreba
 
Posts: 157
Joined: Mon Dec 13, 2010 3:29 pm

Re: SQL for selecting articles

Postby asmecher » Tue Jul 08, 2014 1:24 pm

Hi Peter,

Did you set the copyright date, holder and license URL using the queries above? If all three are set, it should even apply to articles that haven't been viewed.

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

Re: SQL for selecting articles

Postby piotreba » Tue Jul 08, 2014 1:43 pm

Yes Alec, I applied all of them and still some dates are not updated.

BTW, the author names pattern has changed compared to the original ones, which was First Name1 Last Name1, First Name2 Last Name2, while after update it is Last Name1, First Name1; Last Name2, First Name2.



EDIT: I noticed strange behaviour, once updated entry after viewing it once more time became updated again to the year 2014, with the earlier author names pattern (First Name1 Last Name1, First Name2 Last Name2).




Peter
piotreba
 
Posts: 157
Joined: Mon Dec 13, 2010 3:29 pm

Next

Return to OJS Technical Support

Who is online

Users browsing this forum: Bing [Bot] and 2 guests