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
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.
piotreba
Posts: 163
Joined: Mon Dec 13, 2010 3:29 pm
Contact:

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

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

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

piotreba
Posts: 163
Joined: Mon Dec 13, 2010 3:29 pm
Contact:

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.

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

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

piotreba
Posts: 163
Joined: Mon Dec 13, 2010 3:29 pm
Contact:

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.

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

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

piotreba
Posts: 163
Joined: Mon Dec 13, 2010 3:29 pm
Contact:

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.

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

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

piotreba
Posts: 163
Joined: Mon Dec 13, 2010 3:29 pm
Contact:

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: 163
Joined: Mon Dec 13, 2010 3:29 pm
Contact:

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: 163
Joined: Mon Dec 13, 2010 3:29 pm
Contact:

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?

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

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

piotreba
Posts: 163
Joined: Mon Dec 13, 2010 3:29 pm
Contact:

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

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

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

piotreba
Posts: 163
Joined: Mon Dec 13, 2010 3:29 pm
Contact:

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


Return to “OJS Technical Support”

Who is online

Users browsing this forum: No registered users and 2 guests