CDR Tickets

BZISSUE::4655
BZDATETIME::2009-10-05 15:12:47
BZCREATOR::Volker Englisch
BZASSIGNEE::Volker Englisch
BZQACONTACT::William Osei-Poku

On Friday, Margaret and Anne identified a Summary document (CDR614165) on BACH that had been published to the vendors but did not appear on Cancer.gov.

After some investigation we identified that this summary - a new document - had been picked up for publishing on Aug 21st and got pushed to Gatekeeper.
However, the load of the document failed due to a 'pretty-URL unique value' failure. This failure was reported back to us.

At this point, the summary probably should have been corrected and manually republished.
Once the document was corrected the users expected the publishing job to pick up the summary for publishing because it had not been published yet. However, due to the earlier publishing event this document already existed in the table pub_proc_cg, but our queries of the publishing document identify a document as new (to be picked up as part of the nightly updates) if it doesn't exist in the table pub_proc_cg. Therefore, this document was not published as part of a nightly job but only as part of the weekly publishing job.

Obviously, this situation does not occur frequently but we could adjust the SQL queries for picking up new documents to adjust for this situation.

I've modified the original query (for summaries) from the following:
SELECT TOP ?NumDocsPerDocType? d.id, max(v.num)
FROM doc_version v
JOIN document d
ON v.id = d.id
JOIN doc_type t
ON d.doc_type = t.id
WHERE t.name = 'Summary'
AND d.active_status = 'A'
AND v.publishable = 'Y'
AND v.val_status = 'V'
AND v.updated_dt <= '?MaxDocUpdatedDate?'
AND v.id NOT IN (SELECT id FROM pub_proc_cg)
AND d.id NOT IN (
SELECT doc_id
FROM query_term_pub
WHERE path = '/Summary/WillReplace/@cdr:ref'
)
GROUP BY d.id
ORDER BY d.id

to this query, which would also pick up documents that do exist in the pub_proc_cg table but for which no successful push job to Cancer.gov exists.
SELECT d.id, max(v.num)
FROM doc_version v
JOIN document d
ON v.id = d.id
JOIN doc_type t
ON d.doc_type = t.id
WHERE t.name = 'Summary'
AND d.active_status = 'A'
AND v.publishable = 'Y'
AND v.val_status = 'V'

• • AND v.updated_dt <= '?MaxDocUpdatedDate?'
    AND d.id NOT IN (
    SELECT doc_id
    FROM query_term_pub
    WHERE path = '/Summary/WillReplace/@cdr:ref'
    )
    AND (v.id NOT IN (SELECT id FROM pub_proc_cg)
    OR
    v.id IN (SELECT DISTINCT ppc.id
    FROM pub_proc_cg ppc
    JOIN pub_proc_doc ppd
    ON ppc.id = ppd.doc_id
    JOIN pub_proc pp
    ON ppd.pub_proc = pp.id
    AND pp.pub_subset LIKE 'Push_%'
    WHERE ppd.failure = 'Y'
    AND NOT EXISTS (SELECT 'x'
    FROM pub_proc_doc i
    JOIN pub_proc ipp
    ON ipp.id = i.pub_proc
    AND ipp.pub_subset LIKE 'Push_%'
    WHERE i.doc_id = ppd.doc_id
    AND i.failure IS NULL
    )
    )
    )
    GROUP BY d.id
    ORDER BY d.id

The query adds about 2-5 minutes for each doc_type as it's written but it might be possible to speed things up a little more should we decide to modify the publishing queries.

I've tested this with the summaries only for now but more testing would be needed.