Bug 6451

Summary: Check and purge incorrect citation_date dates
Product: OJS Reporter: James MacGregor <jmacgreg>
Component: Submissions and PublishingAssignee: PKP Support <pkp-support>
Status: RESOLVED FIXED    
Severity: normal CC: alec, jfitz049, vgabler
Priority: P3    
Version: 2.3.5   
Hardware: All   
OS: All   
Version Reported In: Also Affects:
Bug Depends on: 6428    
Bug Blocks: 6452    
Attachments: Patch against OJS 2.3.4
Patch against OJS 2.3.4

Description James MacGregor 2011-02-27 18:50:46 PST
Using Bug 6428, check any suspicious citation_date values and purge them if considered incorrect.
Comment 1 Alec Smecher 2011-03-08 10:44:53 PST
Created attachment 3450 [details]
Patch against OJS 2.3.4

Note that this patch does not actually do anything for existing users unless they run through the upgrade process. Instead, they should run:

For MySQL:
ALTER TABLE published_articles CHANGE COLUMN date_published date_published DATETIME;
UPDATE published_articles pa, edit_decisions e1, edit_decisions e2, articles a SET pa.date_published = NULL WHERE pa.article_id = a.article_id AND e1.article_id = a.article_id AND e2.article_id = a.article_id AND e1.edit_decision_id <> e2.edit_decision_id AND e1.date_decided = e2.date_decided AND pa.date_published >= a.date_submitted;

For PostgreSQL:
ALTER TABLE published_articles ALTER COLUMN date_published DROP NOT NULL;
UPDATE published_articles SET date_published = NULL WHERE article_id IN (SELECT DISTINCT a.article_id FROM articles a, published_articles pa, edit_decisions e1, edit_decisions e2 WHERE pa.article_id = a.article_id AND e1.article_id = a.article_id AND e2.article_id = a.article_id AND e1.edit_decision_id <> e2.edit_decision_id AND e1.date_decided = e2.date_decided AND pa.date_published >= a.date_submitted);

This will attempt to identify content that came in via the Expedited Submission or Quick Submit processes, and null out *all* date_published entries in published_articles for that content where the published_article.date_published is more recent than article.date_submitted. THIS MAY INCLUDE SOME GOOD DATA, e.g. if an Editor submits an Editorial and uses the Expedited Process to move it through workflow quickly.

See also bug #4718 and bug #6480.
Comment 2 Alec Smecher 2011-03-08 14:59:33 PST
Unfortunately, bug #6428 isn't a good match for this -- the dupe decision behavior appears to have disappeared in recent releases. Instead, we can look for submissions with a "date submitted" and "date published" less than 5 minutes apart and null those out. Will add updated patch shortly...
Comment 3 Alec Smecher 2011-03-08 15:03:13 PST
Created attachment 3452 [details]
Patch against OJS 2.3.4

For MySQL:
ALTER TABLE published_articles CHANGE COLUMN date_published date_published
DATETIME;
UPDATE published_articles pa, articles a SET pa.date_published = NULL WHERE a.article_id = pa.article_id AND UNIX_TIMESTAMP(pa.date_published) - UNIX_TIMESTAMP(a.date_submitted) < 300 AND UNIX_TIMESTAMP(pa.date_published) - UNIX_TIMESTAMP(a.date_submitted) >= 0

For PostgreSQL:
ALTER TABLE published_articles ALTER COLUMN date_published DROP NOT NULL;
UPDATE published_articles SET date_published = NULL WHERE article_id IN (SELECT a.article_id FROM published_articles pa, articles a WHERE a.article_id = pa.article_id AND EXTRACT(EPOCH FROM pa.date_published) - EXTRACT(EPOCH FROM a.date_submitted) < 300 AND EXTRACT(EPOCH FROM pa.date_published) - EXTRACT(EPOCH FROM a.date_submitted) >= 0
Comment 4 Alec Smecher 2011-03-08 15:36:14 PST
(Committed to ojs-stable-2_3)