cleaner.py

Go to the documentation of this file.

"""
Try to cleanup a DBI pair using DybDbi ...

    1) create CalibFeeSpecCleanup pair using DybDbi
    2) migrate useful entries from CalibFeeSpec into it
       (DBI will give them new SEQNO)
    3) drop CalibFeeSpec (or rename to CalibFeeSpecToBeDropped)
    4) rename CalibFeeSpecCleanup to CalibFeeSpec
    5) fixups in LOCALSEQNO to accommodate table renaming


mysql> select * from CalibFeeSpecCleanupVld order by VERSIONDATE limit 10 ;
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
| SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
|     1 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:37:16 | 2011-06-16 08:46:43 | 
|     2 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:38:16 | 2011-06-16 08:46:43 | 
|     3 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:39:16 | 2011-06-16 08:46:43 | 
|     4 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:40:16 | 2011-06-16 08:46:43 | 
|     5 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:41:16 | 2011-06-16 08:46:43 | 
|     6 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:42:16 | 2011-06-16 08:46:43 | 
|     7 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:43:16 | 2011-06-16 08:46:43 | 
|     8 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:44:16 | 2011-06-16 08:46:43 | 
|     9 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:45:16 | 2011-06-16 08:46:43 | 
|    10 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:46:16 | 2011-06-16 08:46:43 | 
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
10 rows in set (0.00 sec)

mysql> select * from CalibFeeSpecVld order by VERSIONDATE limit 10 ;
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
| SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
|     4 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:37:16 | 2010-03-10 17:36:31 | 
|     5 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:38:16 | 2010-03-10 17:36:31 | 
|     6 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:39:16 | 2010-03-10 17:36:31 | 
|     7 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:40:16 | 2010-03-10 17:36:31 | 
|     8 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:41:16 | 2010-03-10 17:36:31 | 
|     9 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:42:16 | 2010-03-10 17:36:31 | 
|    10 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:43:16 | 2010-03-10 17:36:31 | 
|    11 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:44:16 | 2010-03-10 17:36:31 | 
|    12 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:45:16 | 2010-03-10 17:36:31 | 
|    13 | 2010-01-07 05:37:16 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-01-07 13:46:16 | 2010-03-10 17:36:31 | 
+-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
10 rows in set (0.00 sec)


"""
import os, logging
log = logging.getLogger(__name__)

class DbiCleaner(dict):
    """
    Usage::

         db = DB("tmp_offline_db")
         assert db.sect == os.environ['DBCONF']
         dc = DbiCleaner( db , GCalibPmtSpec, GCalibPmtSpecCleaner )
         dc.reset_target()
         dc()

    Currently just duplicates for fidelity testing  

    """
    def __init__(self, db, src , tgt  ):
        """
        :param db: ``DybPython.DB`` instance
        :param src: source DybDbi class, eg GTableName
        :param tgt: target DybDbi class, eg GTableNameCleanup

        Target is just being used for table creation and table name

        """
        assert db.sect == os.environ['DBCONF']
        self.db = db
        self.src = src         
        self.tgt = tgt          
        self['tname'] = tgt.__name__[1:]   # G prefix convention 

    def reset_target(self):
        """
        Drop the target ``GTableNameCleanup`` tables and ``LOCALSEQNO`` refernence   
        harmless warning regarding non-existing tables on first run 
        """  
        assert self['tname'][-4:] == 'Fake' or self['tname'][-7:] == 'Cleanup' , "SAFETY MEASURE : target classname must end with 'Fake' or 'Cleanup' unlike %(tname)s  " % self
        self.db("drop table if exists %(tname)s, %(tname)sVld" % self )      
        self.db("delete from LOCALSEQNO where TABLENAME='%(tname)s'" % self )
        self.tgt().CreateDatabaseTables(0,  self['tname'] )

    def __call__(self, *seqnos ):
        """
        Traverse all validity records in source table, ordered by VERSIONDATE desc::

                mysql> select * from CalibFeeSpecVld order by VERSIONDATE desc ;
                +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
                | SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
                +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
                |   113 | 2010-06-18 02:18:24 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-06-18 10:23:24 | 2010-06-23 05:40:30 | 
                |   112 | 2010-06-18 02:18:24 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-06-18 10:22:24 | 2010-06-22 11:37:48 | 
                |   111 | 2010-06-18 02:18:24 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-06-18 10:21:24 | 2010-06-22 11:36:10 | 
                |   110 | 2010-06-18 02:18:24 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-06-18 10:20:24 | 2010-06-22 11:35:22 | 
                |   109 | 2010-06-18 02:18:24 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-06-18 10:19:24 | 2010-06-22 10:01:04 | 
                |   108 | 2010-06-18 02:18:24 | 2038-01-19 03:14:07 |       32 |       1 |       1 |    0 |          -1 | 2010-06-18 10:18:24 | 2010-06-22 10:00:47 | 
                |

        which means the created SEQNO will get changed unless can sort on SEQNO  ... but there are gaps so they must change anyhow

        Succeeds to do a full copy BUT differences:

        #. INSERTDATE not propagated : expected
        #. VERSIONDATE is 8hr shifted  ... some DBI TZ handling emerging ?
        #. SEQNO do not match : makes comparisons difficult 
        #. SEQNO mismatch because ordering is VERSIONDATE desc ... could sort the vrec by SEQNO but there are generally gaps anyhow
 
        """
        sqlcontext, task, subsite = "1=1", -1, -1   ## wideopen DBI querying 
        vrb = self.src.GetTableProxy().MakeValidityRecBuilder( sqlcontext, subsite, task )
        nvr = vrb.GetNumValidityRec()
        gap = vrb.GetValidityRec(0)
        assert gap.IsGap() 

        npr = 0
        for ivr in reversed(range(1,nvr)):   ## reverse for closer SEQNO correspondence 
            vrec = vrb.GetValidityRec(ivr)
            if len(seqnos) > 0 and vrec.seqno not in seqnos:continue

            npr += 1
            # writer for each vrec 
            wrt = self.src.Wrt().Clone() 
            wrt.ctx( contextrange=vrec.contextrange, subsite=vrec.subsite, task=vrec.task, versiondate=vrec.versiondate, tablename=self['tname'] ) ## make a fresh writer ... dont use vrec alone

            # wideopen reader for all payloads within the vrec (aka SEQNO)
            rpt = self.src.Rpt().Clone()
            rpt.ctx( validityrec=vrec )  ## vrec reader does " select * from T where SEQNO= 113 "   etc...
            n = len(rpt)
            log.debug("ivr %s vrec seqno %s n %s " % (ivr, vrec.seqno, n ))

            # write all source payloads to target 
            for row in rpt:
                wrt.Write(row)    
            assert wrt.Close()
            pass
        log.info("from %s attempted to propagate %s " % (nvr-1, npr) )   ## slot 0 is Gap 





if __name__ == '__main__':
    pass
    from DybPython import DB
    from DybDbi import GCalibFeeSpec as src, GCalibFeeSpecCleanup as tgt
    os.environ['DBCONF'] = "tmp_offline_db"
    db = DB( os.environ['DBCONF'] )
    dc = DbiCleaner( db, src, tgt )
    dc.reset_target()
    dc(113,3)