/search.css" rel="stylesheet" type="text/css"/> /search.js">
| Classes | Job Modules | Data Objects | Services | Algorithms | Tools | Packages | Directories | Tracs |

In This Package:

cleaner.py
Go to the documentation of this file.
00001 """
00002 Try to cleanup a DBI pair using DybDbi ...
00003 
00004     1) create CalibFeeSpecCleanup pair using DybDbi
00005     2) migrate useful entries from CalibFeeSpec into it
00006        (DBI will give them new SEQNO)
00007     3) drop CalibFeeSpec (or rename to CalibFeeSpecToBeDropped)
00008     4) rename CalibFeeSpecCleanup to CalibFeeSpec
00009     5) fixups in LOCALSEQNO to accommodate table renaming
00010 
00011 
00012 mysql> select * from CalibFeeSpecCleanupVld order by VERSIONDATE limit 10 ;
00013 +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
00014 | SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
00015 +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
00016 |     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 | 
00017 |     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 | 
00018 |     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 | 
00019 |     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 | 
00020 |     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 | 
00021 |     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 | 
00022 |     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 | 
00023 |     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 | 
00024 |     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 | 
00025 |    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 | 
00026 +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
00027 10 rows in set (0.00 sec)
00028 
00029 mysql> select * from CalibFeeSpecVld order by VERSIONDATE limit 10 ;
00030 +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
00031 | SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
00032 +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
00033 |     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 | 
00034 |     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 | 
00035 |     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 | 
00036 |     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 | 
00037 |     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 | 
00038 |     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 | 
00039 |    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 | 
00040 |    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 | 
00041 |    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 | 
00042 |    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 | 
00043 +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
00044 10 rows in set (0.00 sec)
00045 
00046 
00047 """
00048 import os, logging
00049 log = logging.getLogger(__name__)
00050 
00051 class DbiCleaner(dict):
00052     """
00053     Usage::
00054 
00055          db = DB("tmp_offline_db")
00056          assert db.sect == os.environ['DBCONF']
00057          dc = DbiCleaner( db , GCalibPmtSpec, GCalibPmtSpecCleaner )
00058          dc.reset_target()
00059          dc()
00060 
00061     Currently just duplicates for fidelity testing  
00062 
00063     """
00064     def __init__(self, db, src , tgt  ):
00065         """
00066         :param db: ``DybPython.DB`` instance
00067         :param src: source DybDbi class, eg GTableName
00068         :param tgt: target DybDbi class, eg GTableNameCleanup
00069 
00070         Target is just being used for table creation and table name
00071 
00072         """
00073         assert db.sect == os.environ['DBCONF']
00074         self.db = db
00075         self.src = src         
00076         self.tgt = tgt          
00077         self['tname'] = tgt.__name__[1:]   # G prefix convention 
00078 
00079     def reset_target(self):
00080         """
00081         Drop the target ``GTableNameCleanup`` tables and ``LOCALSEQNO`` refernence   
00082         harmless warning regarding non-existing tables on first run 
00083         """  
00084         assert self['tname'][-4:] == 'Fake' or self['tname'][-7:] == 'Cleanup' , "SAFETY MEASURE : target classname must end with 'Fake' or 'Cleanup' unlike %(tname)s  " % self
00085         self.db("drop table if exists %(tname)s, %(tname)sVld" % self )      
00086         self.db("delete from LOCALSEQNO where TABLENAME='%(tname)s'" % self )
00087         self.tgt().CreateDatabaseTables(0,  self['tname'] )
00088 
00089     def __call__(self, *seqnos ):
00090         """
00091         Traverse all validity records in source table, ordered by VERSIONDATE desc::
00092 
00093                 mysql> select * from CalibFeeSpecVld order by VERSIONDATE desc ;
00094                 +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
00095                 | SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
00096                 +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
00097                 |   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 | 
00098                 |   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 | 
00099                 |   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 | 
00100                 |   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 | 
00101                 |   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 | 
00102                 |   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 | 
00103                 |
00104 
00105         which means the created SEQNO will get changed unless can sort on SEQNO  ... but there are gaps so they must change anyhow
00106 
00107         Succeeds to do a full copy BUT differences:
00108 
00109         #. INSERTDATE not propagated : expected
00110         #. VERSIONDATE is 8hr shifted  ... some DBI TZ handling emerging ?
00111         #. SEQNO do not match : makes comparisons difficult 
00112         #. SEQNO mismatch because ordering is VERSIONDATE desc ... could sort the vrec by SEQNO but there are generally gaps anyhow
00113  
00114         """
00115         sqlcontext, task, subsite = "1=1", -1, -1   ## wideopen DBI querying 
00116         vrb = self.src.GetTableProxy().MakeValidityRecBuilder( sqlcontext, subsite, task )
00117         nvr = vrb.GetNumValidityRec()
00118         gap = vrb.GetValidityRec(0)
00119         assert gap.IsGap() 
00120 
00121         npr = 0
00122         for ivr in reversed(range(1,nvr)):   ## reverse for closer SEQNO correspondence 
00123             vrec = vrb.GetValidityRec(ivr)
00124             if len(seqnos) > 0 and vrec.seqno not in seqnos:continue
00125 
00126             npr += 1
00127             # writer for each vrec 
00128             wrt = self.src.Wrt().Clone() 
00129             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
00130 
00131             # wideopen reader for all payloads within the vrec (aka SEQNO)
00132             rpt = self.src.Rpt().Clone()
00133             rpt.ctx( validityrec=vrec )  ## vrec reader does " select * from T where SEQNO= 113 "   etc...
00134             n = len(rpt)
00135             log.debug("ivr %s vrec seqno %s n %s " % (ivr, vrec.seqno, n ))
00136 
00137             # write all source payloads to target 
00138             for row in rpt:
00139                 wrt.Write(row)    
00140             assert wrt.Close()
00141             pass
00142         log.info("from %s attempted to propagate %s " % (nvr-1, npr) )   ## slot 0 is Gap 
00143 
00144 
00145 
00146 
00147 
00148 if __name__ == '__main__':
00149     pass
00150     from DybPython import DB
00151     from DybDbi import GCalibFeeSpec as src, GCalibFeeSpecCleanup as tgt
00152     os.environ['DBCONF'] = "tmp_offline_db"
00153     db = DB( os.environ['DBCONF'] )
00154     dc = DbiCleaner( db, src, tgt )
00155     dc.reset_target()
00156     dc(113,3)
00157 
00158 
| Classes | Job Modules | Data Objects | Services | Algorithms | Tools | Packages | Directories | Tracs |

Generated on Fri May 16 2014 09:57:24 for DybDbi by doxygen 1.7.4