dbaux.py

Go to the documentation of this file.

#!/usr/bin/env python
"""
$Id: dbaux.py 17856 2012-08-22 11:40:42Z blyth $

Performs actions based on working copy at various revision points.

  ============  ===================================
    action         notes
  ============  ===================================
    ls             lists commit times/messages
    rcmpcat        compare ascii catalog with DB
    rloadcat       load ascii catalog into DB
  ============  ===================================

Usage examples::

   ./dbaux.py ls      4913
   ./dbaux.py ls      4913:4914
   ./dbaux.py ls      4913:4932    
   ./dbaux.py ls      4913:4914   --author bv 

   ./dbaux.py --workingcopy ~/mgr/tmp_offline_db --baseurl file:///tmp/repos/catalog ls 2:39 
        #
        # using non default workingcopy path and baseurl 
        #
        #      NB baseurl must be the base of the repository 
        #    TODO: avoid duplication by extracting baseurl from the working copy, or at least assert on consistency  
        #

   ./dbaux.py    rcmpcat 4913   
   ./dbaux.py    rcmpcat 4913:4932   
   ./dbaux.py -r rcmpcat 4913   

   ./dbaux.py         rloadcat 4913   
   ./dbaux.py --reset rloadcat 4913   ## -r/--reset deletes SVN working copy before `svn up` 


To select non-contiguous revisions use `-a/--author` to pick just that 
authors commits within the revision range.  Test with `ls`.

While testing in "tmp_offline_db" return to starting point with::

    ./db.py offline_db dump ~/offline_db.sql 
    ./db.py tmp_offline_db load ~/offline_db.sql 


While performing test loads into `tmp_offline_db`, multiple 
ascii catalog revisions can be loaded into DB with a single command::

    ./dbaux.py -c -r rloadcat 4913:4932      
           ## -c/--cachesvnlog   improves rerun speed while testing 
           ## -r/--reset         starts from a clean revision each time, 
                                 ignoring fastforward changes done by **rloadcat**

    ./dbaux.py -c -r rloadcat 4913:4932     
           ## a rerun will fail at the first revision and will do nothing 
           ## as the DB is detected to be ahead of the catalog

However when performing the real definitive updates into `offline_db` it is 
preferable to do things a bit differently::

    ./dbaux.py -c -r --dbconf offline_db  rloadcat 4913:4932 --logpath dbaux-rloadcat-4913-4932.log

           ## -s/--sleep 3 seconds sleep between revisions, avoid fastforward insert times with the same UTC second
           ##  --dbconf offline_db      target ~/.my.cnf section 


Checks after performing **rloadcat(s)** 
-----------------------------------------

Each **rloadcat** modifies the catalog inplace, changing the INSERTDATE times. 
However as are operating beneath the dybaux trunk it is not straightforward 
to commit these changes and record them as they are made.  Instead propagate 
them from the database into the catalog by an **rdumpcat** following updates.
This is also a further check of a sequence of **rloadcat**.

Dump the updated DB into the catalog with::

    db.py offline_db rdumpcat ~/dybaux/catalog/tmp_offline_db 
    db.py tmp_offline_db rdumpcat ~/dybaux/catalog/tmp_offline_db    ## when testing 

Then check the status of the catalog, only expected tables .csv should be changed::

    svn st  ~/dybaux/catalog/tmp_offline_db
 
    M       /home/blyth/dybaux/catalog/tmp_offline_db/CableMap/CableMapVld.csv     
    M       /home/blyth/dybaux/catalog/tmp_offline_db/HardwareID/HardwareIDVld.csv

               ## should only be INSERTDATE changes, 
               ## the new times should be UTC now times spread out over the 
               ## rloadcat operations    
             
    M       /home/blyth/dybaux/catalog/tmp_offline_db/tmp_offline_db.cat         

               ##  minor annoyance : changed order of entries in .cat 
               ##  ... to be fixed by standardizing order with sorted  TABLENAME 

Following a sequence of definitive commits into `offline_db` do an OVERRIDE commit 
into dybaux mentioning the revision range and author in the commit message. For example::

    svn ci -m "fastforward updates following offline_db rloadcat of bv r4913:r4932 OVERRIDE  " ~/dybaux/catalog/tmp_offline_db



Logfile Checks
---------------

Using the ``--logpath <path>`` option writes a log that is nearly the same as the console output.
Checks to make on the logfile:

Check all commits are covered::
 
   grep commit dbaux-rloadcat-4913-4932.log


Look at the `SEQNO` being loaded, verify no gaps and that the starting `SEQNO` is where expected::

   egrep "CableMap.*new SEQNO" dbaux-rloadcat-4913-4932.log
   egrep "HardwareID.*new SEQNO" dbaux-rloadcat-4913-4932.log

Examine fastforward times::

   grep fastforward dbaux-rloadcat-4913-4932.log


Manual Checks
--------------

Before loading a sequence of commits sample the ascii catalog at various revisions with eg::

    svn up -r <revision> ~/dybaux/catalog/tmp_offline_db
    cat ~/dybaux/catalog/tmp_offline_db/LOCALSQNO/LOCALSEQNO.csv

Verify that the ``LASTUSEDSEQNO`` value changes are as expected compared to:: 

        mysql> select * from LOCALSEQNO ; 
        +--------------+---------------+
        | TABLENAME    | LASTUSEDSEQNO |
        +--------------+---------------+
        | *            |             0 | 
        | CalibFeeSpec |           113 | 
        | CalibPmtSpec |            29 | 
        | FeeCableMap  |             3 | 
        | CableMap     |           440 |    
        | HardwareID   |           358 | 
        +--------------+---------------+
        6 rows in set (0.00 sec)

Expectations are:

#. incremental only ... no going back in `SEQNO`
#. no `SEQNO` gaps 




The tools perform many checks and comparisons, but manual checks are advisable also, eg::

        mysql> select distinct(INSERTDATE) from CableMapVld  ;
        mysql> select distinct(INSERTDATE) from HardwareIDVld  
        mysql> select distinct(SEQNO) from  CableMap ;
        mysql> select distinct(SEQNO) from  CableMapVld ;


rloadcat checks in various situations
---------------------------------------

Starting with r4913 and r4914 already loaded, try some operations.

a) rloadcat r4913 again::

    ./dbaux.py rloadcat 4913 
    ... 
    AssertionError: ('ERROR LASTUSEDSEQNO in target exceeds that in ascii cat HardwareID ', 42, 58)
       ## the DB is ahead of the catalog ... hence the error

b) rloadcat r4914 again::

    ./dbaux.py rloadcat 4913 
    ..
    WARNING:DybPython.db:no updates (new tables or new SEQNO) are detected  
        ## DB and catalog are level pegging ... hence "no updates" warning


AVOIDED ISSUES
---------------

#. same process rcmpcat checking following an rloadcat fails as has outdated idea of DB 
   content despite cache wiping on rloadcat. A subsequent rcmpcat in a new process succeeds. 
   .. was avoided by creating a fresh DB instance after loads, forcing re-accessing to Database 


"""
import os,sys,logging,argparse,shutil,time
sys.path.insert(0, os.path.expandvars("$SITEROOT/../installation/trunk/dybinst/scripts"))
from svnlog import SVNLog, Info, Status
log = logging.getLogger('DybPython')

pathx = lambda _:os.path.expanduser(os.path.expandvars(_))


def args_():
    ap = argparse.ArgumentParser( description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
    ap.add_argument("-l","--loglevel",   help="logging level INFO, DEBUG, WARN,... Default %(default)s ")
    ap.add_argument(     "--logformat",  help="Used by logger. Default %(default)s ") 
    ap.add_argument(     "--logpath",    help="Path to write log file to. Default %(default)s ") 
    ap.add_argument("-b","--baseurl",    help="base url of SVN repository. Default %(default)s ")
    ap.add_argument("-w","--workingcopy",help="working copy of SVN repository to svn up etc.. Default %(default)s ")
    ap.add_argument("-a","--author",     help="select revisions to be from this author. Default %(default)s ")
    ap.add_argument(     "--limit",      help="limit the maximum number of revisions to avoid slow queries. Default %(default)s ")
    ap.add_argument(     "--dbconf",     help="DB to compare against or load into. Default %(default)s ") 
    ap.add_argument(     "--sleep",      help="Seconds to sleep between revisions, eliminating the remote possibility of subsecond fastforwarding time clashes. Default %(default)s ") 
    #
    ap.add_argument( "action",           help="Action to perform eg ls, rcmpcat, rloadcat  ") 
    ap.add_argument( "revs",             help="String representation of revision or contiguous revision ranges, eg 4913:4932 or 4913") 
    # 
    ap.add_argument("-r","--reset",       action="store_true",  help="reset SVN working copy to precisely the revision by deletion of directory prior to update ")
    ap.add_argument("-c","--cachesvnlog", action="store_true",  help="cache the SVN log as an xml file for speed, when you know the log for a revision or revision range is unchanging ")

    # vestigial option was never acted upon, fast forward commands are done manually, as noted above
    #ap.add_argument("-f","--ffcommit",    action="store_true",  help="perform commits of the fastforward modified INSERTDATE catalogs. Default %(default)s ")

    ap.set_defaults( 
      loglevel="info",
      baseurl="http://dayabay.ihep.ac.cn/svn/dybaux" ,
      workingcopy="~/dybaux/catalog/tmp_offline_db",
      dbconf="tmp_offline_db",
      author=None,
      sleep=3, 
      limit=50,
      reset=False,
      cachesvnlog=False,
      logformat='%(asctime)s %(name)s %(levelname)-8s %(message)s',
      logpath=None,   
    )

    args = ap.parse_args()
    urlleaf = os.path.basename(args.baseurl)
    assert urlleaf == 'dybaux', "leaf of baseurl is now restricted to be dybaux for simplicity" 

    loglevel = getattr( logging , args.loglevel.upper() )
    log.setLevel(loglevel) 

    sh = logging.StreamHandler() 
    sh.setLevel(loglevel) 
    fmtr = logging.Formatter(args.logformat)
    sh.setFormatter(fmtr)
    log.addHandler(sh)        

    if args.logpath:
        fh = logging.FileHandler(args.logpath,mode="w")
        fh.setFormatter(fmtr)
        fh.setLevel(loglevel) 
        log.addHandler(fh)

    return args 

def db_(args):
    """
    programmatic use of db.py, to avoid logging confusion (double logging/missed logging/fmt differences):
       
    #.  `--nolog` avoids a clash of loggers
    #. logger name of `dbaux.py` is chosen to be 'DybPython' to hierachically contain 'DybPython.db'

    """
    from DybPython.db import main 
    dbconf = args.dbconf
    cmdline = 'db.py --noconfirm --nolog  %(dbconf)s noop' % locals()  
    sys.argv = cmdline.split()
    return main()                  

class Aux(object):

    def __init__(self, args ):
        log.info( "Aux %r" % args )
        self.args = args
        
        opts = {'limit':args.limit } 
        if args.cachesvnlog:
            opts['xmlcache'] = self.cachesvnlog

        if args.action == "rloadcat" and args.dbconf == 'offline_db':
            log.warn( "checking options for definitive rloadcat into offline_db"  )
            #if not args.ffcommit: 
            #    log.fatal( "definitive rloadcat requires the  -f/--ffcommit option ")
            #    sys.exit(1)
        else:
            pass

        slog = SVNLog( args.baseurl, args.revs, opts )
        self.slog = slog 
        log.info( "completed the slog " )
        pass 
        self.fresh_db()
        info = self.info 
        log.info("%r ... revision %s " % ( info, info.revision) ) 

    cachesvnlog = property(lambda self:"/tmp/dbaux-slog-%s-%s.xml" % (os.getlogin(), self.args.revs ))
    info = property(lambda self:Info(self.args.workingcopy), doc="parse/wrap output of `svn info --xml` ... caution rerun on each access" )  
    stat = property(lambda self:Status(self.args.workingcopy), doc="parse/wrap output of `svn status --xml` ... caution rerun on each access" )  

    def fresh_db(self):
        """
        Pull up a new DB instance
        """
        self.db = db_( self.args )

    def sslog(self):
        for e in self.slog:
            if self.args.author and e.author != self.args.author:
                continue 
            yield e 

    def ls_(self):
        """
        Lists the revisions, author, time, commit message
        """
        for i,e in enumerate(self.sslog()):
            log.info( "%-3s %6s %10s %20s %s " % (i,e.revision,e.author,e.t,e.msg) )   

    def svnup_(self, rev , reset=False, force=False ):
        """
        :param rev: revision number to bring working copy directory to 
        :param reset:  remove the directory first, wiping away uncommitted changes/conflicts 


        Aug 22, 2012 moved to checkout and revert rather than priot just update
        as this was  failing with ``--reset`` due to lack of the working copy directory,
        resulting in ``svn up`` skipping and subsequent assertions.  The idea is to step 
        thru pristine revisions, one by one::

            svn co -r 5292 http://dayabay.ihep.ac.cn/svn/dybaux/catalog/tmp_offline_db ~/dybaux/catalog/tmp_offline_db
            svn revert ~/dybaux/catalog/tmp_offline_db

        """ 
        info = self.info
        if info.revision == rev and not force:
            log.info(repr(info))
            log.info("svnup_ skip update as working copy is already at revision %(rev)s" % locals() )  
            return None

        wc = pathx(self.args.workingcopy)
        url = "%s/catalog/tmp_offline_db" % self.args.baseurl
        if reset:
            basename = os.path.basename(wc)
            assert os.path.isdir(wc) and len(wc) > 10, "bad wc %s " % wc 
            assert basename == 'tmp_offline_db'  
            log.info("removing working copy dir %s " % wc )
            shutil.rmtree(wc)
        else:
            stat = self.stat
            if len(stat) > 0:
                log.fatal("svnup_ into unclean working copy %r " % stat )
                log.fatal("\n".join([repr(e) for e in stat])) 
                log.fatal("consider using --reset option to blow away the working copy before update")
                sys.exit(1)

        svnco = "svn co -r %(rev)s %(url)s %(wc)s " % locals() 
        svnrv = "svn revert %(wc)s " % locals() 
        for svnxx in (svnco,svnrv):
            log.info(svnxx) 
            ret = os.popen(svnxx).read()
            log.info(ret)
 
    def rcmpcat_(self):
        """
        Loops over revisions:
        
        #. `svn up -r` the working copy  
        #. runs **rcmpcat**  comparing the ascii catalog with DB
 
        """
        stat = self.stat    
        if len(stat) > 0:
            log.warn("unclean working copy %r " % stat )
            log.warn("\n".join([repr(e) for e in stat])) 

        cat = self.args.workingcopy
        log.info("rcmpcat_ %r" % cat )
        for i,e in enumerate(self.sslog()):
            log.info("commit %-3s %6s %10s %20s %s " % (i,e.revision,e.author,e.t,e.msg) )   
            self.svnup_(e.revision, reset=self.args.reset )
            rcc = self.db.rcmpcat_(cat)   ## compare  
            log.info("rcmpcat %r" % rcc )

    def rloadcat_(self):
        """
        Loops over revisions
 
        #. `svn up -r` the working copy  
        #. runs **rcmpcat** to verify there are some updates to be loaded
        #. invokes **rloadcat** loading ascii catalog into DB
        #. runs **rcmpcat** agsin to verify load is complete 


        NB no confirmation is requested, thus before doing this perform an 
        **rcmpcat** to verify expected updates

        Rerunning an **rloadcat** ::

            ./dbaux.py rloadcat 4913   ## 1st time OK
            ./dbaux.py rloadcat 4913   ## 2nd time was giving conflicts ... now fails with unclean error
            ./dbaux.py --reset rloadcat 4913   ## blow away conflicts by deletion of working copy before "svn up"

        How to fix ?

        #.  When testing "svn revert" the changed validity tables throwing away
            the fastforward times ?  via parsing "svn status"  

        """
        cat = self.args.workingcopy
        sslog = [e for e in self.sslog()]
        log.info("rloadcat_ %r over %s revisions " % (cat, len(sslog)) )
        for i,e in enumerate(sslog):
            log.info( "commit %-3s %6s %10s %20s %s " % (i,e.revision,e.author,e.t,e.msg) )   
            self.svnup_(e.revision, reset=self.args.reset )
            rlc = self.db.rloadcat_(cat)                       ## compares and loads 
            if len(rlc) == 0:
                log.info("rloadcat_ didnt see any updates for revision %s " % e.revision )
                continue

            log.info("rloadcat %r" % rlc )
            self.fresh_db()
            rcc = self.db.rcmpcat_(cat)  
            log.info("after rloadcat rcmpcat %r" % rcc )

            assert len(rcc) == 0 , "should be no table/seqno differences after rloadcat " 
            if i + 1 < len(sslog):
                sleep = self.args.sleep
                log.info("sleeping for %s seconds" % sleep )
                time.sleep(sleep)  


    def __call__(self):
        cmd = self.args.action + "_"
        if hasattr( self , cmd ):
            getattr( self, cmd )() 
        else:
            log.warn("no method %s " % cmd ) 


def main():
    args = args_()
    aux = Aux(args)
    aux()

if __name__ == '__main__':
    main()