#1323 Cleanup report to find titles whose dates are before the first pub date

[Ahasuerus]

Create a cleanup report to find title records whose title dates are before the publication dates of the earliest publication record associated with the title. Allow "ignoring" titles.

Start with titles whose 4-digit year is before the earliest pub's 4-digit year. Implement the report one title type at a time to make it more manageable. Here is the breakdown of affected titles by title type as of 2019-11-16:

+--------------+--------------------+
| ANTHOLOGY | 287 |
| COLLECTION | 821 |
| COVERART | 4534 |
| INTERIORART | 22763 |
| EDITOR | 11 |
| ESSAY | 10717 |
| INTERVIEW | 455 |
| NOVEL | 6203 |
| NONFICTION | 327 |
| OMNIBUS | 171 |
| POEM | 6222 |
| REVIEW | 1896 |
| SERIAL | 73 |
| SHORTFICTION | 39750 |
| CHAPBOOK | 397 |
+--------------+--------------------+

The SQL query was:

select t1.title_ttype, count(t1.title_id) from titles t1
where YEAR(t1.title_copyright) <
(select YEAR(min(p.pub_year))
from pubs p, pub_content pc
where pc.pub_id = p.pub_id
and pc.title_id = t1.title_id)
group by t1.title_ttype;

The query logic may need to be enhanced. The current query's performance on the development server is not very good. It also locks all ISFDB title/publication pages while it runs.

[Ahasuerus]

Part 1 - COVERART titles implemented in:

edit/cleanup_lib.py
edit/cleanup_report.py
nightly/nightly_job.py

Installed in SVN 482 on 2019-12-06. Keeping the FR open since Part was limited to COVERART titles. Other title types will be added once COVERART titles have been cleaned up.

[Ahasuerus]

Part 2 - Limited the scope to variant titles for now since parent titles have trickier date logic:

edit/cleanup_report.py
nightly/nightly_job.py

Installed in SVN 484 on 2019-12-08. Keeping the FR open.

[Ahasuerus]

Part 3 - Added SERIAL titles:

edit/cleanup_report.py
nightly/nightly_job.py

Installed in SVN 490 on 2019-12-28. Keeping the FR open.

[Ahasuerus]

Updated counts for VTs only:

select t1.title_ttype, count(t1.title_id) from titles t1
where YEAR(t1.title_copyright) <
(select YEAR(min(p.pub_year))
from pubs p, pub_content pc
where pc.pub_id = p.pub_id
and pc.title_id = t1.title_id)
and t1.title_parent > 0
group by t1.title_ttype;

+--------------+--------------------+
| title_ttype  | count(t1.title_id) |
+--------------+--------------------+
| ANTHOLOGY    |                 91 |
| COLLECTION   |                248 |
| INTERIORART  |              10485 |
| EDITOR       |                  1 |
| ESSAY        |               1757 |
| INTERVIEW    |                 81 |
| NOVEL        |               2190 |
| NONFICTION   |                 73 |
| OMNIBUS      |                 81 |
| POEM         |                913 |
| REVIEW       |                125 |
| SERIAL       |                 73 |
| SHORTFICTION |              10302 |
| CHAPBOOK     |                 60 |
+--------------+--------------------+

[Ahasuerus]

Updated 2021-10-04 counts for VTs only:

 +--------------+--------------------+
 | ANTHOLOGY    |                 83 |
 | COLLECTION   |                224 |
 | COVERART     |                  5 |
 | INTERIORART  |               9467 |
 | EDITOR       |                  1 |
 | ESSAY        |               1796 |
 | INTERVIEW    |                 86 |
 | NOVEL        |               2106 |
 | NONFICTION   |                 69 |
 | OMNIBUS      |                 77 |
 | POEM         |                955 |
 | REVIEW       |                129 |
 | SERIAL       |                 16 |
 | SHORTFICTION |              10498 |
 | CHAPBOOK     |                 60 |
 +--------------+--------------------+

[Ahasuerus]

Part 4 - Added anthologies, chapbooks, omnibuses and collections:

edit/cleanup_report.py
nightly/nightly_job.py

Installed in SVN 772 on 202110-05.