tab.py

Go to the documentation of this file.

import os, logging, pickle, datetime, hashlib
from pprint import pformat
log = logging.getLogger(__name__)

class DebugValidityOrdering(object):
    """

    .. warn:: **FOR DEBUGGING ONLY**  NB the change impacts the kls specified only 

    Context manger for non standard DBI Validity ordering queries, allowing what happens 
    within a tied VERSIONDATE occurs to be examined.
    Observation suggests MySQL implicity orders by the PK(SEQNO) asc 
    within tied VERSIONDATE sets.

    The extra ordering string is impinged between "order by VERSIONDATE desc" 
    and the terminating ";" with a "," prefix when the extra is non-blank. 

    Usage::
 
         from DybPython.tab import DebugValidityOrdering
         from DybDbi import GDemo

         with DebugValidityOrdering(GDemo,"SEQNO desc"):
             r = GDemo.Rpt()
             n = len(r)
             print n, r.vrec.seqno 

    """  
    def __init__(self, kls , tmp  ):
        self.tmp = tmp
        self.proxy = kls.GetTableProxy().GetDBProxy()
        self.prior = None
    def __enter__( self ):
        if not self.tmp:
            self.prior = None
            log.warn("extra ordering of None, leaving ordering as is")
        else: 
            self.prior = self.proxy.GetExtraOrdering()
            self.proxy.SetExtraOrdering( self.tmp )       
            log.info("changing extra ordering : %s -> %s " % ( self.prior, self.tmp ) )

    def __exit__(self, type, value, tb ):
        if not self.prior:
            log.warn("no prior set ")
            return None
        self.proxy.SetExtraOrdering(self.prior)
        log.info("reverting extra ordering : %s -> %s " % ( self.prior, self.extra) )


class Rollback(object):
    """
    INSERTDATE rollback context manager
    """
    def __init__(self, insertdate ):
        self.insertdate = insertdate

    def config(self, insertdate):
        from DybDbi import TimeStamp, gDbi
        delta = datetime.timedelta( seconds=1 )    ## is this really needed ? YES as rollback uses "INSERTDATE < t"
        ts = TimeStamp.fromAssumedUTCDatetime( insertdate + delta )
        gDbi.ConfigRollback("* = %s" % ts.AsString("s") )
        return ts

    def __enter__(self):
        if self.insertdate != None:
            self.config(self.insertdate) 
    def __exit__(self, type, value, tb ):
        from DybDbi import gDbi
        gDbi.ClearRollbackDates()


def t_(_):
    """
    Convert a datetime into a TimeStamp
    """
    from DybDbi import TimeStamp
    return TimeStamp.fromAssumedUTCDatetime(_)

def rst_title( msg , cha="~" ):
    return  "%s\n%s\n\n" % ( msg, cha * len(msg) )

class ctxdict(dict):
    """
    Specialization of `dict` to make hashable allowing to be used in `sets` and other dicts 

    .. warning:: do **not** mutate these dicts if you wish to maintain sanity 
 
    """
    def __hash__(self):
        return hash(tuple(sorted(self.items())))
    def __str__(self):
        return "_".join(map(lambda k:"%s%s" % (k,self[k]) , sorted(self) )) 


class Tab(object):
    """
    Marriage of raw DB access via MySQL-python and DybDbi access 
    """
    def __init__(self, name , db ):
        """
        :param name: name of DBI payload table
        :param db:  :py:class:`DybPython.DB` instance
        """
        self.name = name
        self.db   = db
    def q(self, sql):
        return self.db( sql % {'tab':self.name} )
    def column(self, n, sql):
        return [d[n] for d in self.db( sql % {'tab':self.name} )]
    def vdistinct(self, n ):
        return self.column(n, "select distinct("+n+") from %(tab)sVld")

    def fields(self, skips=[]):
        """
        Return list of fields in the this table, excluding the skipped fields.
        """ 
        return [_['Field'] for _ in self.db("describe %s" % self.name  ) if _['Field'] not in skips ]

    def _qminmaxavg(self, skips=('SEQNO',), group_by='SEQNO' ):
        """
        Return SQL query string that provides min, max and avg values of each field within `group_by` groups 
        """
        fields = self.fields(skips=skips) 
        mma = lambda f:"min(%(f)s) as mi%(f)s, max(%(f)s) as mx%(f)s, avg(%(f)s) as av%(f)s" % dict(f=f)
        sel = ",".join(map(mma,fields))
        return "select %(group_by)s,%(sel)s from %(tab)s group by %(group_by)s" % dict(tab=self.name, group_by=group_by, sel=sel)

    def qminmaxavg( self):
        return self.db( self._qminmaxavg() )

    def mma(self):
        """
        Return a string rst table showing min/max/avg values grouped by SEQNO.
        When the docs virtual python is used, a valid rst table is returned otherwise
        a pale imitation table is provided.

        Note that min/max/avg are consolidated into a single cell to avoid overlarge tables
        """
        flds = self.fields(skips=('SEQNO',))
        from DybPython.vlut import TabularData
        td = TabularData()
        for e in self.qminmaxavg():
            d = dict(SEQNO=e['SEQNO'])
            for f in flds:
                d[f] = "%s %s %s" % ( e['mi'+f], e['mx'+f], e['av'+f ] )
            td.append(d)
        return td.as_rst( ['SEQNO'] + flds )


    def dropzero(self):
        """
        DROPs the DBI payload and validity tables and sets the LASTUSEDSEQNO in LOCALSEQNO table to zero.
        Only works when the DBCONF section name and actual database name starts with 'tmp_' 
        """
        tab = self.name
        dbname = self.db.dbc['database']
        if dbname.startswith('tmp_') and self.db.sect.startswith('tmp_'):
            log.info("dropping DBI table pair for %(tab)s and zeroing LOCALSEQNO.LASTUSEDSEQNO "  % locals() ) 
            self.db("drop table if exists %(tab)s, %(tab)sVld" % locals() )
            self.db("update LOCALSEQNO set LASTUSEDSEQNO=0 where TABLENAME='%(tab)s'" % locals() )
        else:
            raise Exception("dropzero only permitted when DBCONF section name and actual database name starts with tmp_ %(tab)s" % locals() ) 

    def vld(self, *seqnos):
        seqs = "(%s)" % seqnos[0] if len(seqnos)==1 else str(tuple(seqnos))
        return self.q("select * from %(tab)sVld where SEQNO in " + seqs ) 

    def vpayloadless(self):
        """
        Return validity entries with no corresponding payload

        There are legitimate reasons to go payloadless, however it is also a likely outcome of
        some error conditions when writing to the DB.
        """
        tab = self.name
        return self.q("select * from  %(tab)sVld where SEQNO not in (select distinct(SEQNO) from %(tab)s) order by SEQNO " % locals() )

    def vorder(self):
         return 'SEQNO TIMESTART TIMEEND SITEMASK SIMMASK SUBSITE TASK AGGREGATENO VERSIONDATE INSERTDATE'.split()

    def vctxsql(self, ctx):
         """
         Provide context SQL query excluding time requirement for interactive mysql querying.  Usage::

              tab = db.tab('Demo')
              actx = tab.actual_ctxs()
              sql = tab.vctxsql(actx[0])
                  > 'select * from DemoVld where SiteMask & 127 and SimMask & 1 and  Task = 0 and SubSite = 0 order by VERSIONDATE desc'

         """
         d = dict(tab=self.name)
         d.update(ctx)
         return "select * from %(tab)sVld where SiteMask & %(site)s and SimMask & %(simflag)s and  Task = %(task)s and SubSite = %(subsite)s order by VERSIONDATE desc" % d 

    def vctx(self, ctx):
        return self.db( self.vctxsql( ctx)) 

    def dbictx_directive(self, ctx ):
        """
        Provides rst source like::

          select * from Demo ... 
          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          .. dbictx:: tmp_offline_db Demo
               :site:
               :simflag:
               :task:
               :subsite:

        Which directs Sphinx to perform a faux-DBI context query when the documentation is built and present the result
        as a table within the docs.

        """
        vl += rst_title(str(self.vctxsql(ctx)))
        vl += '.. dbictx:: %(dbconf)s %(tn)s\n   :site: %(site)s\n   :simflag: %(simflag)s\n   :task: %(task)s\n   :subsite: %(subsite)s\n' % dict(ctx, dbconf=self.db.sect ,tn=self.name)
        return vl

    def vlast(self, n , orderlimit="order by SEQNO desc limit 1" ):
        """
        :param n: name of row in validity table, eg `INSERTDATE`
        :param orderlimit: SQL ordering and limit clause 
        """
        vals = self.column(n, "select `"+n+"` from %(tab)sVld " + orderlimit )
        return vals[0] if len(vals) == 1 else None

    def vtimes(self):
        return sorted(list(set( self.vdistinct("TIMESTART") + self.vdistinct("TIMEEND") )))

    def vseqnos(self, *seqnos, **kwa):
        """
        Returns string presentation of a list of SEQNO in a DBI validity table

        :param seqnos:  one or more SEQNO 

        Usage::

                In [6]: p tab.vseqnos(10,1)
                +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
                | SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
                +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
                |     1 | 2010-01-01 01:00:00 | 2038-01-19 03:14:07 |      127 |       1 |       0 |    0 |          -1 | 2010-01-01 01:00:00 | 2011-08-22 12:14:21 | 
                |    10 | 2010-01-01 04:00:00 | 2038-01-19 03:14:07 |      127 |       1 |       0 |    0 |          -1 | 2010-01-01 04:02:00 | 2011-08-22 12:14:30 | 
                +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+

        """
        raw = kwa.pop('raw',False)
        if raw:
            seqtuple = "(%s)" % seqnos[0] if len(seqnos)==1 else str(tuple(seqnos))
            return self.db.mysql("select * from %(tab)sVld where SEQNO in %(seqtuple)s" % {'tab':self.name,'seqtuple':seqtuple} , opts="-t" )
        else:
            return self.vld(*seqnos)      



    def actual_ctxs(self):
        """
        Provide list of distinct context dicts actually present in the Vld table,
        ordered by the string representation.

        Masks and specials kAnyTask = -1, kAnySubSite = -1 are not yet handled differently 
        
        :return: list of dicts such as ``dict(site=127,simflag=1,subsite=1,task=0, aggno=-1)`` covering all contexts in  Vld table

        """
        actual = []
        for e in self.q("select * from %(tab)sVld"):
            ctx = ctxdict(task=int(e['TASK']),simflag=int(e['SIMMASK']),site=int(e['SITEMASK']),subsite=int(e['SUBSITE']),aggno=int(e['AGGREGATENO']))
            actual.append(ctx)
        return sorted(list(set(actual)),cmp=lambda a,b:cmp(str(a),str(b)))   

    def looped_ctxs(self):
        """
        Looped context dicts based on distinct values of the components.

        :return: list of dicts such as ``dict(site=127,simflag=1,subsite=1,task=0, aggno=-1)`` covering all relevant contexts suggested by Vld table

        ::
                +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
                | SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
                +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+

        """
        sites    = self.vdistinct("SITEMASK")  
        simflags = self.vdistinct("SIMMASK")  
        subsites = self.vdistinct("SUBSITE")  
        tasks    = self.vdistinct("TASK")  
        aggnos   = self.vdistinct("AGGREGATENO")  
        
        l = [] 
        for task in map(int,tasks):
            for simflag in map(int,simflags):  
                for site in map(int,sites):
                    for subsite in map(int,subsites):
                        for aggno in map(int,aggnos):
                            l.append(ctxdict(task=task,simflag=simflag,site=site,subsite=subsite,aggno=aggno))
        return l


    exists = property(lambda self:self.db.has_table(self.name), doc="True if DBI payload table exists")

    path = property(lambda self:"%s.txt" % self.name)
    def save(self, smth, dir=None ):
        """
        :param smth: some string related to the table to be written
        """
        path = os.path.join(dir, self.path) if dir else self.path
        f = open( path , "w")
        f.write( smth )
        f.close()
       
    def _kls(self):
        import DybDbi
        return getattr( DybDbi , 'G' + self.name )  
    kls = property( _kls )


    def tscan(self, times, ctx, opts):
        """
        :param times: list of datetimes at which to perform DBI queries 
        :param ctx: DybDbi ctx kwargs, excluding `timestamp` 
        :param opts: dict of extra options

        :return lod: list-of-dicts representing winning SEQNO vs T

        Also for each time an extra FindSeqNoWithVersionDate query is done 
        in order to explicity show where the ambiguous rot sets in on the VLUT.  
        The excludingSeqno is used to avoid finding itself

        BUT FindSeqNoWithVersionDate will return 0 if it finds more than 1 ...
        maybe need to allow to pass a string


        When using the default timeGate the view of collisions seen by FindSeqNoWithVersionDate
        should correspond to the standard DBI view 


        """
        kls = self.kls
        tsc = []

        dbno = 0         
        uniqueVersionDateSiteMask = 0 
        uniqueVersionDateSimMask = 0   ## 0 means use the vrec ones
        useTimeGate = 0                ## 0 means NO, 1 use default, >15 use value as non-standard gate 

        ordering = opts.get("ordering",None) 

        for t in times:
            ts = t_(t)
            log.debug("tscan t %s ts %s ordering %s " % ( t, ts.AsString("s"), ordering ) )
            kls.Cache().Purge()

            with DebugValidityOrdering(kls,ordering):

                r = kls.Rpt().Clone()
                r.ctx( timestamp=ts, **ctx )
                n = len(r)

                nvr = r.key.numvrecs
                seq = r.key.seqnolist
                dig = r.digest             ## untested with aggregates 

                log.debug("payload count %s  nvr %s seq %s dig %s  " % (n, nvr,seq,dig ) )
                
                d = dict(t=t,n=n,dig=dig)

                ##  aggregate Table queries yield r.vrec.seqno all over the shop depending on extra ordering, but it appears not to matter 
                ## ... to allow VLUT comparisons to work for aggregate tables exposed the `key.seqnolist`  
                ##
                if nvr > 1:     ## aggregate query       
                    seq = r.key.seqnolist
                    d.update( rvs=seq ) 
                    ## unclear how to do collision test in aggregate case
                else:
                    #assert r.vrec.seqno == r.key.seqnolist, (r.vrec.seqno , r.key.seqnolist )
                    rvs = r.vrec.seqno   
                    rvs_versiondate = r.vrec.versiondate  
                    excludingSeqno = rvs
                    count = True
                    ncollision = proxy.FindSeqNoWithVersionDate( rvs_versiondate , r.vrec , dbno ,uniqueVersionDateSiteMask, uniqueVersionDateSimMask, excludingSeqno, useTimeGate, count )
                    if ncollision==1:
                        count = False
                        collision = proxy.FindSeqNoWithVersionDate( rvs_versiondate , r.vrec , dbno ,uniqueVersionDateSiteMask, uniqueVersionDateSimMask, excludingSeqno, useTimeGate, count )
                    else:
                        collision = -1 
                    pass
                    d.update( rvs=rvs,collision=collision,ncollision=ncollision) 
                    pass
                tsc.append( d )

        return tsc 

    def vscan(self, digest=False, timestart=False):
        """
        Technical check ``(SEQNO vs SEQNO)`` 
        Uses genDbi class for the table to perform DBI query for all SEQNO
        :return lod: list-of-dicts 

        Formerly was doing ``rvs = r.vrec.seqno if n>0 else 0`` but 
        ``n=0`` will be OK once orphans are allowed again

        """
        kls = self.kls
        r = kls.Rpt()
        vsc = []
        for e in self.db("select * from %(tab)sVld order by SEQNO asc" % {'tab':self.name} ):
            ts = t_( e['TIMESTART'] )
            r.ctx( timestamp=ts, site=e['SITEMASK'], simflag=e['SIMMASK'], subsite=e['SUBSITE'], task=e['TASK'] )
            n = len(r)
            rvs = r.vrec.seqno 
            d = dict(seqno=e['SEQNO'], rvs=rvs, n=n )
            if digest:
                d['digest'] = r.digest 
            if timestart:
                d['timestart'] = e['TIMESTART'] 
            vsc.append( d )
        return vsc


    def fullscan(self, ctx, opts ):
        """
        Performs SEQNO vs T scans for full history of a table,
        via rollbacks to all distinct INSERTDATES (+1s)        

        NB raw DB selects are blind to the rollback 

        :param ctx: DybDbi ctx kwargs, excluding `timestamp`       
        :param opts: dict with any extra variations

        :return dld: INSERTDATE keyed dict-of-list-of-dicts 
        """ 
        times = self.vtimes()     ## raw DB select is blind to the DBI rollback
        times = times[:-1]        ## kludgy exclusion of EOT
        fscan = {}

        insertdate_aligned = opts['_aligned'] 
        insertdates_ = sorted(self.vdistinct("INSERTDATE")) 
        if insertdate_aligned: 
            insertdates = zip(insertdates_,insertdates_)
        else:
            insertdates =  zip(["latest"],[insertdates_[-1]])

        for insertlabel,insertdate in insertdates:
            with Rollback(insertdate):
                tsc = self.tscan(times,  ctx, opts )
                log.debug("fullscan %s" % pformat(tsc))
                fscan[insertlabel] = tsc
        return fscan    


def test_ctx():
    from db import DB
    db = DB("tmp_offline_db")
    for tab in db.showpaytables:
        ta = db.tab(tab)

        lastinsert = ta.vlast('INSERTDATE')
        lctxs = ta.looped_ctxs()
        actxs = ta.actual_ctxs()

        log.info("tab %-20s lastinsert %-20s :  %-4s(%-4s) looped(actual) ctx " % (tab, lastinsert, len(lctxs),len(actxs)) )
        for ctx in lctxs:
            log.debug("lctx %s " % ctx)
        for ctx in actxs:
            log.debug("actx %s " % ctx)
    

def test_scan():
    from db import DB
    db = DB("tmp_offline_db")
    for tab in db.showpaytables:
        ta = db.tab(tab)
        actxs = ta.actual_ctxs()
        for ctx in actxs:
            ta.fullscan(ctx)

def test_rollback():
    from db import DB
    ta = db.tab('CableMap')
    opts = {}
    actxs = ta.actual_ctxs()
    ctx = actxs[-1]
    print ctx
    insertdates = ta.vdistinct("INSERTDATE")
    times = ta.vtimes()

    with Rollback(insertdates[-1]):
        tsc = ta.tscan( times, ctx, opts ) 

def test_payloadless():
    from db import DB
    db = DB("tmp_offline_db")
    #ta = db.tab('CableMap')
    ta = db.tab('PhysAd')
    for e in ta.vpayloadless():
        print e


if __name__ == '__main__':
    logging.basicConfig(level=logging.DEBUG)

    os.environ.setdefault('DBCONF','tmp_offline_db')

    from db import DB
    db = DB()
    ta = db.tab('CalibPmtSpec')
    print ta.mma()