dbcf.py

Go to the documentation of this file.

#!/usr/bin/env python
"""
Compares schema between common tables from multiple Databases. 
DB are specified via dbconf section names in ~/.my.cnf

See also :dybgaudi:`Database/DybDbi/tests/test_schema.py`

Usage::

   ./dbcf.py --help
   ./dbcf.py offline_db tmp_nodata tmp_offline_db 
   ./dbcf.py offline_db tmp_nodata -t CalibFeeSpecVld 
   ./dbcf.py offline_db tmp_nodata -t CalibFeeSpecVld -f SUBSITE 
   ./dbcf.py offline_db tmp_nodata -t CalibFeeSpecVld -f SUBSITE -a Type
   ./dbcf.py offline_db tmp_nodata -t CalibFeeSpecVld -f SUBSITE -a Type
   ./dbcf.py offline_db tmp_nodata -f SUBSITE :

"""
import logging, argparse
from pprint import pformat
from DybPython import DB
log = logging.getLogger(__name__)

class DBCF(list):
    """
    DB Schema comparison between tables in Databases identified
    by a list of dbconf sectnames.
    Table schema comparisons are made for tables that appear 
    in all specified Databases

    The basis for comparison is the tuple of fieldatt dicts returned by
    MySQL-python in response to "describe TableName", the fieldattr for each 
    Field has the form::

        {'Default': None,
         'Extra': '',
         'Field': 'SEQNO',
         'Key': 'PRI',
         'Null': 'NO',
         'Type': 'int(11)'}


    Suspect a MySQL version difference in 'Default' reporting 
    in offline_db getting '' but in belle7 getting None, resulting 
    in many  mismatches (despite same creation commands): 

    PhysAdVld                      TIMEEND              Default    {'tmp_nodata': None, 'offline_db': ''}  

    """
    def __init__(self, *dbconf, **kwa ):
         """
         Establish names of common set of tables that exist in all Databases
         """
         list.__init__(self, dbconf )

         dbs = map( DB, self )

         tc = set()    
         for db in dbs: 
             t = set(db.showtables)
             if len(tc) == 0:
                 tc = set(t)
             else:
                 tc.intersection_update(t)    ## collect intersected tables

         self.opts = kwa.get('opts', {})
         self.dbs = dbs
         self.tc = tc


    def table(self, t ):
        """
        :param t: table name

        Obtain the descriptions from each Database and rekey  
        to return a dict-of-dict-of-dict keyed by fieldname/fieldattr/dbconf, 
        The rearrangement puts the dbconf at the base of the structure 
        giving access to all values in all db in one place

        ddd is an intermediate dict-of-dict-of-dict keyed on dbconf/fieldname/fieldattr
        """
        assert t in self.tc, "table %s is not in common " % t     

        # convert per field tuple-of-dicts into dict-of-dicts keyed on fieldname/fieldattr
        rejig = lambda x:dict(map(lambda _:(_['Field'],_) , x ))
 
        dd = []      
        ddd = {}     
        for n,db in zip(self,self.dbs):
            d = db("describe " + t )
            dd.append(d)
            ddd[n] = rejig(d)
        assert len(set(map(len,dd))) == 1 , "mismatch in field length %s " % t  

        kk = None
        for n in ddd:
           ks = sorted(ddd[n].keys())
           if not kk:
              kk = ks
           else:
              assert ks == kk,  "field key mismatch %s \n%r \n%r " % ( t , kk, ks )  

        # rekey to put the sectname at base giving access 
        # to all values in all db in one place
        msm = {}  
        for n in ddd.keys():
            for fn in ddd[n]:
                if not fn in msm:msm[fn] = {}
                for fa in ddd[n][fn]:
                    if not fa in msm[fn]:msm[fn][fa] = {}

                    v = ddd[n][fn][fa] 
                    if fa == 'Default' and not self.opts.get('nofix'): 
                         if v == None:v = ''                        

                    msm[fn][fa][n] = v
                  
        # prune when have only one value in leaf dict  
        # NB must use longform .keys() as are mutating the dict 
        for fn in msm.keys():
            for fa in msm[fn].keys():
                 vs = set(msm[fn][fa].values())
                 if len(vs) == 1:         ## only one value ... so prune
                     del msm[fn][fa]
            if len(msm[fn]) == 0:         ## prune up here too
                del msm[fn] 

        return msm 


    def mismatch(self):
        """
        Returns dict-of-dict-of-dict-of-dict keyed by tablename/fieldname/fieldattr/dbconf, 
        for example from tablename level::
       
              'SimPmtSpecVld': {'INSERTDATE': {'Default': {'offline_db': '', 'tmp_nodata': None}},
                                'TIMEEND':    {'Default': {'offline_db': '', 'tmp_nodata': None},
                                                   'Key': {'offline_db': 'MUL', 'tmp_nodata': ''}},
                                'TIMESTART': {'Default': {'offline_db': '', 'tmp_nodata': None},
                                                  'Key': {'offline_db': 'MUL', 'tmp_nodata': ''}},
                              'VERSIONDATE': {'Default': {'offline_db': '', 'tmp_nodata': None}}}}

        The dddd is pruned to only contain "paths" with mismatches
        """
        msm = {}

        ot = self.opts.get('table',None)
        op = self.opts.get('skip',None)
        skip = op.split(",") if op else [] 

        for t in self.tc:
            if ot and t != ot:
                continue  
            if t in skip:
                log.warn("skipping %s as has fieldname mismatch needing correction before can apply other checks " % t )
            else:
                msm[t] =  self.table( t )
        return msm
    

    def present(self, msm):

        of = self.opts.get('field',None)
        oa = self.opts.get('attr', None)

        for tn in msm:
            for fn in msm[tn]:
                if of and fn != of:continue 
                for fa in msm[tn][fn]:
                    if oa and fa != oa:continue 
                    print "%-30s %-20s %-10s %r " % ( tn,fn,fa, msm[tn][fn][fa] )

    def __call__(self):
        msm = self.mismatch()
        #print pformat(msm)
        self.present(msm)
        return msm

    def args_(cls):
        ap = argparse.ArgumentParser( description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
        ap.add_argument('dbconf',   nargs="+", help='Two or more dbconf section names from ~/.my.cnf file')
        ap.add_argument('-l','--loglevel',     help='logging level INFO,WARN,DEBUG... Default %(default)s ')
        ap.add_argument('-t','--table',        help='Restrict comparison to just this table. Default %(default)s ')
        ap.add_argument('-f','--field',        help='Restrict comparison to just this field. Default %(default)s ')
        ap.add_argument('-a','--attr',         help='Restrict comparison to just this attribute. Default %(default)s ')
        ap.add_argument('-s','--skip',         help='Comma delimited list of tables to skip. Default %(default)s ')
        ap.add_argument('-N','--nofix', action="store_true"  ,  help='Dont fix "Default" reporting.  Default %(default)s ')
        ap.set_defaults(
             loglevel="INFO",
               table=None,
               field=None,
               attr=None,
               skip=None,
               nofix=False,
         )
        args = ap.parse_args()
        loglevel = getattr(logging,args.loglevel.upper())
        logging.basicConfig( level=loglevel )
        return args
    args_ = classmethod( args_ )


def main():
    args = DBCF.args_()
    log.debug("main %r" %  args )
    cf = DBCF(*args.dbconf, opts=vars(args))
    msm = cf()
    return msm


if __name__ == '__main__':
    main()