create_orm_tables.py

#!/usr/bin/env python3
"""
create_orm_tables.py

Creates all ORM tables and validates their existence,
foreign key relationships, and expected indexes.
"""

from sqlalchemy import inspect
from rreme.models.base import engine, Base
import os
import sys
sys.path.insert(0, os.path.abspath(
    os.path.join(os.path.dirname(__file__), '..')))


# Register all models
import rreme.models.cv              # noqa: F401
import rreme.models.cvterm          # noqa: F401
import rreme.models.organism        # noqa: F401
import rreme.models.feature         # noqa: F401
import rreme.models.feature_loc     # noqa: F401
import rreme.models.feature_prop    # noqa: F401
import rreme.models.feature_relationship  # noqa: F401
import rreme.models.attract_motif   # noqa: F401
import rreme.models.rbp_pwm         # noqa: F401
import rreme.models.scan_job        # noqa: F401
import rreme.models.motif_mapping   # noqa: F401
import rreme.models.xref_models     # noqa: F401


def main():
    # 1) Create any tables not yet present
    Base.metadata.create_all(bind=engine)

    # 2) Inspect database schema
    inspector = inspect(engine)
    existing = set(inspector.get_table_names())

    # 3) Verify expected tables exist
    expected = {
        'cv', 'cvterm', 'organism', 'feature',
        'featureloc', 'featureprop', 'feature_relationship',
        'attract_motif', 'rbp_pwm', 'scan_job', 'motif_mapping',
        'xref_source', 'xref_entry', 'feature_xref'
    }
    missing = expected - existing
    if missing:
        print(
            f"[ERROR] Missing tables: {', '.join(sorted(missing))}", file=sys.stderr)
        sys.exit(1)

    # 4) Verify some key foreign‑keys:

    # cvterm.cv_id -> cv.cv_id
    fks = inspector.get_foreign_keys('cvterm')
    if not any(fk['referred_table'] == 'cv' and fk['constrained_columns'] == ['cv_id']
               for fk in fks):
        print("[ERROR] FK cvterm.cv_id -> cv.cv_id not found", file=sys.stderr)
        sys.exit(1)

    # feature.organism_id -> organism.organism_id
    fks = inspector.get_foreign_keys('feature')
    if not any(fk['referred_table'] == 'organism' and fk['constrained_columns'] == ['organism_id']
               for fk in fks):
        print(
            "[ERROR] FK feature.organism_id -> organism.organism_id not found", file=sys.stderr)
        sys.exit(1)

    # feature.type_id -> cvterm.cvterm_id
    if not any(fk['referred_table'] == 'cvterm' and fk['constrained_columns'] == ['type_id']
               for fk in fks):
        print("[ERROR] FK feature.type_id -> cvterm.cvterm_id not found",
              file=sys.stderr)
        sys.exit(1)

    # featureloc.feature_id, srcfeature_id -> feature.feature_id
    fks = inspector.get_foreign_keys('featureloc')
    if not any(fk['referred_table'] == 'feature' and fk['constrained_columns'] == ['feature_id']
               for fk in fks):
        print(
            "[ERROR] FK featureloc.feature_id -> feature.feature_id not found", file=sys.stderr)
        sys.exit(1)
    if not any(fk['referred_table'] == 'feature' and fk['constrained_columns'] == ['srcfeature_id']
               for fk in fks):
        print(
            "[ERROR] FK featureloc.srcfeature_id -> feature.feature_id not found", file=sys.stderr)
        sys.exit(1)

    # featureprop.feature_id -> feature.feature_id
    fks = inspector.get_foreign_keys('featureprop')
    if not any(fk['referred_table'] == 'feature' and fk['constrained_columns'] == ['feature_id']
               for fk in fks):
        print(
            "[ERROR] FK featureprop.feature_id -> feature.feature_id not found", file=sys.stderr)
        sys.exit(1)

    # feature_relationship.subject_id, object_id -> feature.feature_id
    fks = inspector.get_foreign_keys('feature_relationship')
    if not any(fk['referred_table'] == 'feature' and fk['constrained_columns'] == ['subject_id']
               for fk in fks):
        print("[ERROR] FK feature_relationship.subject_id -> feature.feature_id not found", file=sys.stderr)
        sys.exit(1)
    if not any(fk['referred_table'] == 'feature' and fk['constrained_columns'] == ['object_id']
               for fk in fks):
        print("[ERROR] FK feature_relationship.object_id -> feature.feature_id not found", file=sys.stderr)
        sys.exit(1)

    # attract_motif.feature_id -> feature.feature_id
    fks = inspector.get_foreign_keys('attract_motif')
    if not any(fk['referred_table'] == 'feature' and fk['constrained_columns'] == ['feature_id']
               for fk in fks):
        print(
            "[ERROR] FK attract_motif.feature_id -> feature.feature_id not found", file=sys.stderr)
        sys.exit(1)

    # scan_job has no FKs except in motif_mapping

    # motif_mapping.feature_id, job_id -> feature.feature_id, scan_job.job_id
    fks = inspector.get_foreign_keys('motif_mapping')
    if not any(fk['referred_table'] == 'feature' and fk['constrained_columns'] == ['feature_id']
               for fk in fks):
        print(
            "[ERROR] FK motif_mapping.feature_id -> feature.feature_id not found", file=sys.stderr)
        sys.exit(1)
    if not any(fk['referred_table'] == 'scan_job' and fk['constrained_columns'] == ['job_id']
               for fk in fks):
        print(
            "[ERROR] FK motif_mapping.job_id -> scan_job.job_id not found", file=sys.stderr)
        sys.exit(1)

    # feature_xref.feature_id -> feature.feature_id
    fks = inspector.get_foreign_keys('feature_xref')
    if not any(fk['referred_table'] == 'feature' and fk['constrained_columns'] == ['feature_id']
            for fk in fks):
        print("[ERROR] FK feature_xref.feature_id -> feature.feature_id not found", file=sys.stderr)
        sys.exit(1)

    # feature_xref.xref_id -> xref_entry.entry_id
    if not any(fk['referred_table'] == 'xref_entry' and fk['constrained_columns'] == ['xref_id']
            for fk in fks):
        print("[ERROR] FK feature_xref.xref_id -> xref_entry.entry_id not found", file=sys.stderr)
        sys.exit(1)

    # xref_entry.source_id -> xref_source.source_id
    fks = inspector.get_foreign_keys('xref_entry')
    if not any(fk['referred_table'] == 'xref_source' and fk['constrained_columns'] == ['source_id']
            for fk in fks):
        print("[ERROR] FK xref_entry.source_id -> xref_source.source_id not found", file=sys.stderr)
        sys.exit(1)


    # 5) Verify some key indexes exist
    def has_index(table, name):
        return name in {idx['name'] for idx in inspector.get_indexes(table)}

    # feature indexes
    for idx in ('idx_feature_uniquename', 'idx_feature_parent', 'idx_feature_type'):
        if not has_index('feature', idx):
            print(f"[ERROR] Missing index '{idx}' on feature", file=sys.stderr)
            sys.exit(1)

    # featureloc indexes
    for idx in ('idx_featureloc_feature', 'idx_featureloc_srcfeature'):
        if not has_index('featureloc', idx):
            print(
                f"[ERROR] Missing index '{idx}' on featureloc", file=sys.stderr)
            sys.exit(1)

    # featureprop indexes
    for idx in ('idx_featureprop_feature', 'idx_featureprop_type'):
        if not has_index('featureprop', idx):
            print(
                f"[ERROR] Missing index '{idx}' on featureprop", file=sys.stderr)
            sys.exit(1)

    # feature_relationship indexes
    for idx in ('idx_fr_subject', 'idx_fr_object', 'idx_fr_type'):
        if not has_index('feature_relationship', idx):
            print(
                f"[ERROR] Missing index '{idx}' on feature_relationship", file=sys.stderr)
            sys.exit(1)

    # attract_motif indexes
    for idx in ('idx_am_feature_id', 'idx_am_matrix_id', 'idx_am_gene_id'):
        if not has_index('attract_motif', idx):
            print(
                f"[ERROR] Missing index '{idx}' on attract_motif", file=sys.stderr)
            sys.exit(1)

    # rbp_pwm index
    if not has_index('rbp_pwm', 'idx_rbp_name'):
        print("[ERROR] Missing index 'idx_rbp_name' on rbp_pwm", file=sys.stderr)
        sys.exit(1)

    # scan_job indexes
    for idx in ('idx_scanjob_status', 'idx_scanjob_created'):
        if not has_index('scan_job', idx):
            print(
                f"[ERROR] Missing index '{idx}' on scan_job", file=sys.stderr)
            sys.exit(1)

    # motif_mapping indexes
    for idx in ('idx_mm_feature_id', 'idx_mm_matrix_id', 'idx_mm_job_id'):
        if not has_index('motif_mapping', idx):
            print(
                f"[ERROR] Missing index '{idx}' on motif_mapping", file=sys.stderr)
            sys.exit(1)


    # xref_entry indexes
    for idx in ('idx_xrefentry_source', 'idx_xrefentry_accession'):
        if not has_index('xref_entry', idx):
            print(f"[ERROR] Missing index '{idx}' on xref_entry", file=sys.stderr)
            sys.exit(1)

    # feature_xref indexes
    for idx in ('idx_featurexref_feature', 'idx_featurexref_xref'):
        if not has_index('feature_xref', idx):
            print(
                f"[ERROR] Missing index '{idx}' on feature_xref", file=sys.stderr)
            sys.exit(1)


    print("All ORM tables and relationships verified successfully.")


if __name__ == "__main__":
    main()