db consistency check

db_helpers.py

from invenio_db import db
from sqlalchemy import inspect
from sqlalchemy.exc import ProgrammingError


def check_col_seq_consistency(table, col):
    seq_id = get_seq_id(table, col)
    max_id = get_max_id(table, col)
    if max_id is not None and seq_id != max_id:
        print 'ERROR: {table}::{col} has a different seq={seq} than max={max}, please check.'.format(
            table=table,
            col=col,
            seq=seq_id,
            max=max_id,
        )
    else:
        print '{table}:{col} has seq={seq} and max={max}.'.format(table=table, col=col, seq=seq_id, max=max_id) 

def check_table_seq_consistency(engine, table):
    inspector = inspect(engine)
    for col in inspector.get_columns(table):
        if col.get('autoincrement'):
            check_col_seq_consistency(table, col.get('name'))
            

def get_seq_id(table, col):
    return db.engine.execute('select last_value from {table}_{col}_seq;'.format(table=table, col=col)).fetchone()[0]


def get_max_id(table, col):
    return db.engine.execute('select MAX({col}) from {table};'.format(col=col, table=table)).fetchone()[0]


def check_seq_id_consistency(engine):
    inspector = inspect(engine)
    for table in engine.table_names():
        check_table_seq_consistency(engine, table)