197 lines
10 KiB
Python
197 lines
10 KiB
Python
"""Audit linked submission histories without inferring event chronology."""
|
|
import argparse
|
|
from collections import Counter
|
|
import json
|
|
from pathlib import Path
|
|
import sqlite3
|
|
|
|
from paper_atlas.db.backend import database_arguments, open_read_connection
|
|
from paper_atlas.db.submissions import timestamp
|
|
from paper_atlas.querying import LATEST_DECISION
|
|
|
|
|
|
ATTEMPTS = '''SELECT s.paper_id,s.id AS submission_id,e.venue_id,v.acronym AS venue,e.year,e.track,
|
|
count(d.id) AS decision_observations,
|
|
MAX(CASE WHEN d.status IN ('rejected','desk_rejected','withdrawn') THEN 1 ELSE 0 END)
|
|
AS historical_negative,
|
|
MAX(CASE WHEN d.status='accepted' THEN 1 ELSE 0 END) AS historical_acceptance,
|
|
COALESCE(MAX(CASE WHEN ''' + LATEST_DECISION + ''' THEN d.status END),'unknown')
|
|
AS latest_observed_status
|
|
FROM submissions s JOIN editions e ON e.id=s.edition_id JOIN venues v ON v.id=e.venue_id
|
|
LEFT JOIN decisions d ON d.submission_id=s.id
|
|
GROUP BY s.paper_id,s.id,e.venue_id,v.acronym,e.year,e.track
|
|
ORDER BY s.paper_id,e.venue_id,s.id'''
|
|
|
|
|
|
def _paper_counts(attempts):
|
|
negative = sum(row['historical_negative'] for row in attempts)
|
|
accepted = sum(row['latest_observed_status'] == 'accepted' for row in attempts)
|
|
overlap = sum(row['historical_negative'] and row['latest_observed_status'] == 'accepted'
|
|
for row in attempts)
|
|
venues = {}
|
|
for row in attempts:
|
|
counts = venues.setdefault(row['venue_id'], [0, 0])
|
|
counts[0] += row['historical_negative']
|
|
counts[1] += row['latest_observed_status'] == 'accepted'
|
|
return {
|
|
'papers_with_submissions': 1,
|
|
'papers_with_multiple_submissions': int(len(attempts) > 1),
|
|
'papers_with_historical_negative': int(negative > 0),
|
|
'papers_with_current_known_acceptance': int(accepted > 0),
|
|
'papers_with_negative_and_acceptance_in_distinct_attempts': int(negative * accepted > overlap),
|
|
'papers_with_negative_and_acceptance_in_distinct_venues':
|
|
int(negative * accepted > sum(n * a for n, a in venues.values())),
|
|
}
|
|
|
|
|
|
def _example(connection, attempts):
|
|
# Keep a negative/accepted pair visible even when a paper has many attempts.
|
|
accepted = [row for row in attempts if row['latest_observed_status'] == 'accepted']
|
|
first = accepted[0] if accepted else None
|
|
other_venue = next((row for row in accepted if row['venue_id'] != first['venue_id']), None)
|
|
pair = None
|
|
for negative in attempts:
|
|
if negative['historical_negative'] and first:
|
|
candidate = first if first['venue_id'] != negative['venue_id'] else other_venue
|
|
if candidate:
|
|
pair = (negative, candidate)
|
|
break
|
|
if pair is None:
|
|
for negative in attempts:
|
|
if negative['historical_negative'] and first:
|
|
candidate = first if first['submission_id'] != negative['submission_id'] else (
|
|
accepted[1] if len(accepted) > 1 else None)
|
|
if candidate:
|
|
pair = (negative, candidate)
|
|
break
|
|
selected = {}
|
|
for row in list(pair or ()) + attempts:
|
|
if len(selected) < 20:
|
|
selected.setdefault(row['submission_id'], dict(row))
|
|
for row in selected.values():
|
|
row['decisions'] = []
|
|
for decision in connection.execute('''SELECT id,status,raw_value,evidence_url,
|
|
decision_date,observed_at FROM decisions WHERE submission_id=?
|
|
ORDER BY observed_at DESC,COALESCE(decision_date,observed_at) DESC,id DESC LIMIT 50''',
|
|
(row['submission_id'],)):
|
|
entry = dict(decision)
|
|
sources = [dict(source) for source in connection.execute('''
|
|
SELECT so.name,r.external_id,ds.source_decision_id FROM decision_sources ds
|
|
JOIN source_records r ON r.id=ds.source_record_id JOIN sources so ON so.id=r.source_id
|
|
WHERE ds.decision_id=? ORDER BY so.name,r.external_id LIMIT 101''', (entry['id'],))]
|
|
entry['sources'] = sources[:100]
|
|
entry['sources_truncated'] = len(sources) > 100
|
|
row['decisions'].append(entry)
|
|
row['decisions_truncated'] = row['decision_observations'] > len(row['decisions'])
|
|
paper_id = attempts[0]['paper_id']
|
|
return {'paper_id': paper_id,
|
|
'title': connection.execute('SELECT title FROM papers WHERE id=?', (paper_id,)).fetchone()[0],
|
|
'counts': _paper_counts(attempts), 'attempt_count': len(attempts),
|
|
'attempts': list(selected.values()), 'attempts_truncated': len(attempts) > len(selected),
|
|
'chronology': 'Not inferred from observed_at, edition year, or row order.'}
|
|
|
|
|
|
def report(db=None, *, pg_service=None, pg_schema=None, examples=20):
|
|
if not 0 <= examples <= 100:
|
|
raise ValueError('Examples must be between 0 and 100')
|
|
started_at = timestamp()
|
|
connection = open_read_connection(db, pg_service=pg_service, pg_schema=pg_schema)
|
|
if not pg_service:
|
|
connection.row_factory = sqlite3.Row
|
|
try:
|
|
if pg_service:
|
|
identity = connection.execute('SELECT current_database(),current_schema()').fetchone()
|
|
database, schema = identity[0], identity[1]
|
|
else:
|
|
database, schema = str(Path(db).resolve()), None
|
|
names = ['papers_with_submissions', 'papers_with_multiple_submissions',
|
|
'papers_with_historical_negative', 'papers_with_current_known_acceptance',
|
|
'papers_with_negative_and_acceptance_in_distinct_attempts',
|
|
'papers_with_negative_and_acceptance_in_distinct_venues']
|
|
totals = Counter({key: 0 for key in names})
|
|
totals.update(submissions=0, decision_observations=0,
|
|
attempts_with_historical_negative=0,
|
|
attempts_with_historical_negative_and_acceptance=0)
|
|
latest = Counter()
|
|
sample_attempts = []
|
|
|
|
def consume(rows):
|
|
if not rows:
|
|
return
|
|
counts = _paper_counts(rows)
|
|
totals.update(counts)
|
|
if counts['papers_with_negative_and_acceptance_in_distinct_attempts'] and len(sample_attempts) < examples:
|
|
sample_attempts.append(rows)
|
|
|
|
current = []
|
|
for raw in connection.execute(ATTEMPTS):
|
|
row = dict(raw)
|
|
if current and row['paper_id'] != current[0]['paper_id']:
|
|
consume(current)
|
|
current = []
|
|
current.append(row)
|
|
totals['submissions'] += 1
|
|
totals['decision_observations'] += row['decision_observations']
|
|
totals['attempts_with_historical_negative'] += row['historical_negative']
|
|
totals['attempts_with_historical_negative_and_acceptance'] += int(
|
|
row['historical_negative'] and row['historical_acceptance'])
|
|
latest[row['latest_observed_status']] += 1
|
|
consume(current)
|
|
# These are relationship checks, not a substitute for the final entity/FK audit.
|
|
integrity = {}
|
|
checks = {
|
|
'submissions_without_source_links': '''SELECT count(*) FROM submissions s
|
|
WHERE NOT EXISTS(SELECT 1 FROM submission_sources ss WHERE ss.submission_id=s.id)''',
|
|
'decisions_without_source_links': '''SELECT count(*) FROM decisions d
|
|
WHERE NOT EXISTS(SELECT 1 FROM decision_sources ds WHERE ds.decision_id=d.id)''',
|
|
'submission_source_paper_mismatches': '''SELECT count(*) FROM submission_sources ss
|
|
JOIN submissions s ON s.id=ss.submission_id JOIN source_records r ON r.id=ss.source_record_id
|
|
WHERE s.paper_id<>r.paper_id''',
|
|
'decision_source_paper_mismatches': '''SELECT count(*) FROM decision_sources ds
|
|
JOIN decisions d ON d.id=ds.decision_id JOIN submissions s ON s.id=d.submission_id
|
|
JOIN source_records r ON r.id=ds.source_record_id WHERE s.paper_id<>r.paper_id''',
|
|
}
|
|
for name, query in checks.items():
|
|
integrity[name] = connection.execute(query).fetchone()[0]
|
|
return {'report_started_at': started_at, 'checked_at': timestamp(),
|
|
'engine': 'postgresql' if pg_service else 'sqlite', 'database': database,
|
|
'schema': schema, 'pg_service': pg_service,
|
|
'status': 'observed_history_audit_not_final_dedup', 'counts': dict(totals),
|
|
'latest_attempt_status_counts': dict(sorted(latest.items())),
|
|
'evidence_relationship_checks': integrity,
|
|
'examples': [_example(connection, rows) for rows in sample_attempts],
|
|
'example_limit': examples,
|
|
'interpretation': [
|
|
'Counts use existing canonical paper links, not independently verified real-world identity.',
|
|
'Negative includes rejected, desk_rejected and withdrawn observations; these remain distinct decisions.',
|
|
'Known acceptance follows the latest observed decision per attempt, matching query --outcome.',
|
|
'Distinct attempts/venues require separate submission/venue IDs; aliases do not create new venues.',
|
|
'Coexisting evidence does not prove rejection happened before acceptance. observed_at is collection time.',
|
|
'No known acceptance is not proof of never accepted. Source signatures and completeness are not established here.',
|
|
'Examples are bounded; counts include all attempts and observations in one read-only snapshot.',
|
|
'This does not perform entity merging, final deduplication, or label namespace/type validation.',
|
|
]}
|
|
finally:
|
|
connection.close()
|
|
|
|
|
|
def main(argv=None, *, prog=None):
|
|
parser = argparse.ArgumentParser(prog=prog)
|
|
database_arguments(parser)
|
|
parser.add_argument('--examples', type=int, default=20)
|
|
parser.add_argument('--out')
|
|
args = parser.parse_args(argv)
|
|
result = report(args.db, pg_service=args.pg_service, pg_schema=args.pg_schema, examples=args.examples)
|
|
text = json.dumps(result, ensure_ascii=False, indent=2)
|
|
if args.out:
|
|
path = Path(args.out)
|
|
stage = path.with_suffix('.part')
|
|
stage.write_text(text + '\n')
|
|
stage.replace(path)
|
|
else:
|
|
print(text)
|
|
|
|
|
|
if __name__ == '__main__':
|
|
main()
|