File size: 5,502 Bytes
74bf532 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 | """
Database Cleanup Script
Removes bad/misattributed data from the URAAS database.
Cleanup rules:
1. Remove items from institutions no longer in the registry
2. Remove items with no title, no DOI, no URL, and no authors
3. Remove exact DOI duplicates (keep first by id)
4. Remove items whose title is fewer than 10 chars
5. Flag (do not delete) items with institution mismatch in affiliation
6. Report before/after counts
"""
import logging
import os
import sys
from datetime import datetime
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger(__name__)
def run_cleanup(dry_run: bool = False):
from sqlalchemy import func
from uraas.config.institutions import get_registry
from uraas.database import Author, Collection, Community, Item, SessionLocal
registry = get_registry()
valid_inst_names = {c.name for c in registry.list_all()}
session = SessionLocal()
try:
total_before = session.query(Item).count()
log.info(f"Starting cleanup | Items before: {total_before}")
removed = 0
# ── Rule 1: Remove items from removed institutions ────────────────────
all_institutions_in_db = session.query(Item.institution).distinct().all()
stale_insts = [
r[0]
for r in all_institutions_in_db
if r[0] and r[0] not in valid_inst_names
]
if stale_insts:
log.info(f"Found stale institutions: {stale_insts}")
for stale in stale_insts:
stale_items = (
session.query(Item).filter(Item.institution == stale).all()
)
log.info(f" [{stale}] {len(stale_items)} items to remove")
if not dry_run:
for item in stale_items:
session.delete(item)
session.commit()
removed += len(stale_items)
# ── Rule 2: Remove items with no title ────────────────────────────────
no_title = (
session.query(Item)
.filter(
(Item.title == None) | (Item.title == "") | (Item.title == "Untitled")
)
.all()
)
log.info(f"Items with no/empty title: {len(no_title)}")
if not dry_run:
for item in no_title:
session.delete(item)
session.commit()
removed += len(no_title)
# ── Rule 3: Remove items with title < 10 chars and no DOI ────────────
all_short = session.query(Item).filter(Item.doi == None).all()
short_items = [i for i in all_short if i.title and len(i.title.strip()) < 10]
log.info(
f"Items with very short title (<10 chars) and no DOI: {len(short_items)}"
)
if not dry_run:
for item in short_items:
session.delete(item)
session.commit()
removed += len(short_items)
# ── Rule 4: Remove exact DOI duplicates (keep lowest id) ─────────────
doi_subq = (
session.query(Item.doi, func.min(Item.id).label("min_id"))
.filter(Item.doi != None)
.group_by(Item.doi)
.subquery()
)
dup_dois = (
session.query(Item)
.filter(Item.doi != None, Item.id.notin_(session.query(doi_subq.c.min_id)))
.all()
)
log.info(f"Duplicate DOI items to remove: {len(dup_dois)}")
if not dry_run:
for item in dup_dois:
session.delete(item)
session.commit()
removed += len(dup_dois)
# ── Rule 5: Remove items with no institution tag ──────────────────────
no_inst = (
session.query(Item)
.filter((Item.institution == None) | (Item.institution == ""))
.all()
)
# Only remove those that have no authors either
truly_orphan = [i for i in no_inst if not i.authors]
log.info(f"Items with no institution AND no authors: {len(truly_orphan)}")
if not dry_run:
for item in truly_orphan:
session.delete(item)
session.commit()
removed += len(truly_orphan)
total_after = session.query(Item).count()
log.info(f"\n{'='*50}")
log.info(f"CLEANUP COMPLETE {'(DRY RUN)' if dry_run else ''}")
log.info(f" Items before: {total_before}")
log.info(f" Items removed: {removed}")
log.info(f" Items after: {total_after}")
log.info(f"{'='*50}")
return {
"total_before": total_before,
"removed": removed,
"total_after": total_after,
"dry_run": dry_run,
}
except Exception as e:
log.error(f"Cleanup error: {e}")
session.rollback()
raise
finally:
session.close()
if __name__ == "__main__":
import argparse
parser = argparse.ArgumentParser(description="URAAS Database Cleanup")
parser.add_argument(
"--dry-run", action="store_true", help="Report without deleting"
)
args = parser.parse_args()
run_cleanup(dry_run=args.dry_run)
|