Bug 6451 - Check and purge incorrect citation_date dates
Check and purge incorrect citation_date dates
Status: RESOLVED FIXED
Product: OJS
Classification: Unclassified
Component: Submissions and Publishing
2.3.5
All All
: P3 normal
Assigned To: PKP Support
Depends on: 6428
Blocks: 6452
  Show dependency treegraph
 
Reported: 2011-02-27 18:50 PST by James MacGregor
Modified: 2011-03-08 15:36 PST (History)
3 users (show)

See Also:
Version Reported In:
Also Affects:


Attachments
Patch against OJS 2.3.4 (2.11 KB, patch)
2011-03-08 10:44 PST, Alec Smecher
Details | Diff
Patch against OJS 2.3.4 (2.59 KB, patch)
2011-03-08 15:03 PST, Alec Smecher
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
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)