00001
00002 """
00003 Performs context *group by* queries on DBI Validity tables and persists as list-of-dicts for
00004 subsequent interrogation, for example::
00005
00006 mysql> select SITEMASK,SUBSITE,count(*) as N,min(SEQNO) as minSEQNO,max(SEQNO) as maxSEQNO,min(TIMESTART) as minTIMESTART,max(TIMESTART) as maxTIMESTART,min(INSERTDATE) as minINSERTDATE,max(INSERTDATE) as maxINSERTDATE from DcsAdLidSensorVld group by SITEMASK,SUBSITE ;
00007
00008 +----------+---------+-------+----------+----------+---------------------+---------------------+---------------------+---------------------+
00009 | SITEMASK | SUBSITE | N | minSEQNO | maxSEQNO | minTIMESTART | maxTIMESTART | minINSERTDATE | maxINSERTDATE |
00010 +----------+---------+-------+----------+----------+---------------------+---------------------+---------------------+---------------------+
00011 | 1 | 1 | 14393 | 1 | 83951 | 2011-06-13 03:06:00 | 2013-03-05 18:43:59 | 2012-01-22 15:31:25 | 2013-03-06 03:37:56 |
00012 | 1 | 2 | 14528 | 2 | 83950 | 2011-06-13 03:06:00 | 2013-03-05 18:40:25 | 2012-01-22 15:31:25 | 2013-03-06 03:34:23 |
00013 | 2 | 1 | 12295 | 3 | 83949 | 2011-06-13 03:06:00 | 2013-03-05 18:36:13 | 2012-01-22 15:31:25 | 2013-03-06 03:30:16 |
00014 | 4 | 1 | 11341 | 4 | 83945 | 2011-06-13 03:06:00 | 2013-03-05 18:20:57 | 2012-01-22 15:31:25 | 2013-03-06 03:14:55 |
00015 | 4 | 2 | 11135 | 5 | 83946 | 2011-06-13 03:06:00 | 2013-03-05 18:21:40 | 2012-01-22 15:31:25 | 2013-03-06 03:15:41 |
00016 | 4 | 3 | 11107 | 6 | 83944 | 2011-06-13 03:06:00 | 2013-03-05 18:18:46 | 2012-01-22 15:31:25 | 2013-03-06 03:12:43 |
00017 | 32 | 1 | 4619 | 7 | 83947 | 2011-06-13 03:06:00 | 2013-03-05 18:22:31 | 2012-01-22 15:31:25 | 2013-03-06 03:16:27 |
00018 | 32 | 2 | 4533 | 8 | 83948 | 2011-06-13 03:06:00 | 2013-03-05 18:33:39 | 2012-01-22 15:31:25 | 2013-03-06 03:27:42 |
00019 +----------+---------+-------+----------+----------+---------------------+---------------------+---------------------+---------------------+
00020 8 rows in set (0.83 sec)
00021
00022
00023 ::
00024
00025 [blyth@cms01 vmap]$ ./vgroup.py vgroup_*.pk
00026 INFO:__main__:select SITEMASK,SUBSITE,count(*) as N,min(SEQNO) as minSEQNO,max(SEQNO) as maxSEQNO,min(TIMESTART) as minTIMESTART,max(TIMESTART) as maxTIMESTART,min(INSERTDATE) as minINSERTDATE,max(INSERTDATE) as maxINSERTDATE from %sVld group by SITEMASK,SUBSITE
00027 INFO:pickler:reading from vgroup_base.pk
00028 SITE SUBS N minSEQ maxSEQ minTIMESTART maxTIMESTART minINSERTDATE maxINSERTDATE
00029 1 1 14512 1 84903 2011-06-13 03:06:00 2013-03-10 18:43:31 2012-01-22 15:31:25 2013-03-11 03:37:27
00030 1 2 14647 2 84902 2011-06-13 03:06:00 2013-03-10 18:39:57 2012-01-22 15:31:25 2013-03-11 03:33:53
00031 2 1 12414 3 84901 2011-06-13 03:06:00 2013-03-10 18:36:18 2012-01-22 15:31:25 2013-03-11 03:30:20
00032 4 1 11460 4 84897 2011-06-13 03:06:00 2013-03-10 18:21:49 2012-01-22 15:31:25 2013-03-11 03:15:44
00033 4 2 11254 5 84898 2011-06-13 03:06:00 2013-03-10 18:22:48 2012-01-22 15:31:25 2013-03-11 03:16:44
00034 4 3 11226 6 84896 2011-06-13 03:06:00 2013-03-10 18:19:18 2012-01-22 15:31:25 2013-03-11 03:13:13
00035 32 1 4738 7 84899 2011-06-13 03:06:00 2013-03-10 18:23:24 2012-01-22 15:31:25 2013-03-11 03:17:19
00036 32 2 4652 8 84900 2011-06-13 03:06:00 2013-03-10 18:33:09 2012-01-22 15:31:25 2013-03-11 03:27:10
00037 INFO:pickler:reading from vgroup_post7.pk
00038 SITE SUBS N minSEQ maxSEQ minTIMESTART maxTIMESTART minINSERTDATE maxINSERTDATE
00039 1 1 14512 1 84903 2011-06-13 03:06:00 2013-03-10 18:43:31 2012-01-22 15:31:25 2013-03-11 03:37:27
00040 1 2 14647 2 84902 2011-06-13 03:06:00 2013-03-10 18:39:57 2012-01-22 15:31:25 2013-03-11 03:33:53
00041 2 1 12414 3 84901 2011-06-13 03:06:00 2013-03-10 18:36:18 2012-01-22 15:31:25 2013-03-11 03:30:20
00042 4 1 11460 4 84897 2011-06-13 03:06:00 2013-03-10 18:21:49 2012-01-22 15:31:25 2013-03-11 03:15:44
00043 4 2 11254 5 84898 2011-06-13 03:06:00 2013-03-10 18:22:48 2012-01-22 15:31:25 2013-03-11 03:16:44
00044 4 3 11226 6 84896 2011-06-13 03:06:00 2013-03-10 18:19:18 2012-01-22 15:31:25 2013-03-11 03:13:13
00045 4 4 4655 84904 89558 2012-07-30 00:37:02 2013-03-10 18:23:24 2013-03-11 07:24:17 2013-03-11 07:27:57
00046 32 1 4738 7 84899 2011-06-13 03:06:00 2013-03-10 18:23:24 2012-01-22 15:31:25 2013-03-11 03:17:19
00047 32 2 4652 8 84900 2011-06-13 03:06:00 2013-03-10 18:33:09 2012-01-22 15:31:25 2013-03-11 03:27:10
00048 INFO:pickler:reading from vgroup_post8.pk
00049 SITE SUBS N minSEQ maxSEQ minTIMESTART maxTIMESTART minINSERTDATE maxINSERTDATE
00050 1 1 14512 1 84903 2011-06-13 03:06:00 2013-03-10 18:43:31 2012-01-22 15:31:25 2013-03-11 03:37:27
00051 1 2 14647 2 84902 2011-06-13 03:06:00 2013-03-10 18:39:57 2012-01-22 15:31:25 2013-03-11 03:33:53
00052 2 1 12414 3 84901 2011-06-13 03:06:00 2013-03-10 18:36:18 2012-01-22 15:31:25 2013-03-11 03:30:20
00053 2 2 4472 89559 94030 2012-08-06 05:37:07 2013-03-10 18:33:09 2013-03-11 07:27:58 2013-03-11 07:31:47
00054 4 1 11460 4 84897 2011-06-13 03:06:00 2013-03-10 18:21:49 2012-01-22 15:31:25 2013-03-11 03:15:44
00055 4 2 11254 5 84898 2011-06-13 03:06:00 2013-03-10 18:22:48 2012-01-22 15:31:25 2013-03-11 03:16:44
00056 4 3 11226 6 84896 2011-06-13 03:06:00 2013-03-10 18:19:18 2012-01-22 15:31:25 2013-03-11 03:13:13
00057 4 4 4655 84904 89558 2012-07-30 00:37:02 2013-03-10 18:23:24 2013-03-11 07:24:17 2013-03-11 07:27:57
00058 32 1 4738 7 84899 2011-06-13 03:06:00 2013-03-10 18:23:24 2012-01-22 15:31:25 2013-03-11 03:17:19
00059 32 2 4652 8 84900 2011-06-13 03:06:00 2013-03-10 18:33:09 2012-01-22 15:31:25 2013-03-11 03:27:10
00060 INFO:pickler:reading from vgroup_post.pk
00061 SITE SUBS N minSEQ maxSEQ minTIMESTART maxTIMESTART minINSERTDATE maxINSERTDATE
00062 1 1 14512 1 84903 2011-06-13 03:06:00 2013-03-10 18:43:31 2012-01-22 15:31:25 2013-03-11 03:37:27
00063 1 2 14647 2 84902 2011-06-13 03:06:00 2013-03-10 18:39:57 2012-01-22 15:31:25 2013-03-11 03:33:53
00064 2 1 12414 3 84901 2011-06-13 03:06:00 2013-03-10 18:36:18 2012-01-22 15:31:25 2013-03-11 03:30:20
00065 2 2 4472 89559 94030 2012-08-06 05:37:07 2013-03-10 18:33:09 2013-03-11 07:27:58 2013-03-11 07:31:47
00066 4 1 11460 4 84897 2011-06-13 03:06:00 2013-03-10 18:21:49 2012-01-22 15:31:25 2013-03-11 03:15:44
00067 4 2 11254 5 84898 2011-06-13 03:06:00 2013-03-10 18:22:48 2012-01-22 15:31:25 2013-03-11 03:16:44
00068 4 3 11226 6 84896 2011-06-13 03:06:00 2013-03-10 18:19:18 2012-01-22 15:31:25 2013-03-11 03:13:13
00069 4 4 4655 84904 89558 2012-07-30 00:37:02 2013-03-10 18:23:24 2013-03-11 07:24:17 2013-03-11 07:27:57
00070 32 1 4738 7 84899 2011-06-13 03:06:00 2013-03-10 18:23:24 2012-01-22 15:31:25 2013-03-11 03:17:19
00071 32 2 4652 8 84900 2011-06-13 03:06:00 2013-03-10 18:33:09 2012-01-22 15:31:25 2013-03-11 03:27:10
00072 [blyth@cms01 vmap]$
00073
00074
00075 """
00076 import os, logging
00077 from pickler import Pickler
00078 log = logging.getLogger(__name__)
00079
00080 class VF(dict):
00081 """
00082 Validity field querying and presentation
00083 """
00084 xf = "SITEMASK SIMMASK SUBSITE TASK".split()
00085 qf = "SEQNO AGGREGATENO".split()
00086 tf = "TIMESTART TIMEEND INSERTDATE VERSIONDATE".split()
00087 af = xf + qf + tf
00088 fmt = {'x':4,'q':6,'t':20,'e':6}
00089
00090 def __init__(self, name, **kwa):
00091 dict.__init__(self, name=name)
00092 self.update(kwa)
00093 if name in self.xf:
00094 self.type = 'x'
00095 elif name in self.qf:
00096 self.type = 'q'
00097 elif name in self.tf:
00098 self.type = 't'
00099 else:
00100 self.type = 'e'
00101 assert self['as_'], "non standard expression field names require an 'as_' specifer "
00102 pass
00103
00104
00105 as_ = property(lambda _:'%(as_)s' % _ if _.has_key('as_') else _['name'])
00106 asmin = property(lambda _:"min%(name)s" % _)
00107 asmax = property(lambda _:"max%(name)s" % _)
00108 asmm = property(lambda _:[_.asmin,_.asmax])
00109
00110
00111 sqlmin = property(lambda _:"min(%(name)s) as min%(name)s" % _)
00112 sqlmax = property(lambda _:"max(%(name)s) as max%(name)s" % _)
00113 sqlcol = property(lambda _:'%(name)s as %(as_)s' % _ if _.has_key('as_') else _['name'])
00114 sqlmm = property(lambda _:[_.sqlmin,_.sqlmax])
00115
00116
00117 fwid = property(lambda _:_.fmt[_.type])
00118 ffmt = property(lambda _:"%("+_.as_+")-"+str(_.fwid)+"s")
00119 fmin = property(lambda _:"%(min"+_['name']+")-"+str(_.fwid)+"s")
00120 fmax = property(lambda _:"%(max"+_['name']+")-"+str(_.fwid)+"s")
00121 fmtmm = property(lambda _:[_.fmin,_.fmax])
00122
00123
00124 lfm = property(lambda _:"%-" + str(_.fwid) + "s" )
00125 lfmt = property(lambda _:_.lfm % _.as_[:_.fwid])
00126 lmin = property(lambda _:_.lfm % _.asmin[:_.fwid])
00127 lmax = property(lambda _:_.lfm % _.asmax[:_.fwid])
00128 lmtmm = property(lambda _:[_.lmin,_.lmax])
00129
00130
00131 class VGroup(dict):
00132
00133 def __init__(self, *args, **kwa):
00134 """
00135 :param xf: space delimited list of context fields to **group by** OR None for default "SITEMASK SIMMASK SUBSITE TASK"
00136 :param qf: space delimited list of other fields to report ranges for OR None for default "SEQNO AGGREGATENO"
00137 :param tf: space delimited list of date fields to report ranges for OR None for default "TIMESTART TIMEEND INSERTDATE VERSIONDATE"
00138 """
00139
00140 dict.__init__(self, *args, **kwa)
00141
00142 xf = self.get('xf',None)
00143 qf = self.get('qf',None)
00144 tf = self.get('tf',None)
00145
00146 xf = map(VF, xf.split() if xf else VF.xf)
00147 qf = map(VF, qf.split() if qf else VF.qf)
00148 tf = map(VF, tf.split() if tf else VF.tf)
00149
00150 cf = [VF("count(*)",as_="N")]
00151
00152 xcol = map(lambda _:_.sqlcol, xf )
00153 gcol = ",".join(xcol)
00154
00155 col = map(lambda _:[_.sqlcol], xf + cf )
00156 col += map(lambda _:_.sqlmm, qf + tf )
00157
00158 fld = map(lambda _:[_.as_], xf + cf )
00159 fld += map(lambda _:_.asmm, qf + tf )
00160
00161 fmt = map(lambda _:[_.ffmt] , xf + cf )
00162 fmt += map(lambda _:_.fmtmm, qf + tf )
00163
00164 lbl = map(lambda _:[_.lfmt] , xf + cf )
00165 lbl += map(lambda _:_.lmtmm, qf + tf )
00166
00167 scol = sum(col,[])
00168 sfld = sum(fld,[])
00169 sfmt = sum(fmt,[])
00170 slbl = sum(lbl,[])
00171
00172 col = ",".join(scol)
00173 table = "%s"
00174 sql = "select %(col)s from %(table)sVld group by %(gcol)s " % locals()
00175 log.info(sql)
00176
00177 fld = " ".join(sfld)
00178 fmt = " ".join(sfmt)
00179 lbl = " ".join(slbl)
00180
00181 self.sql = sql
00182 self.xcol = xcol
00183 self.fmt = fmt
00184 self.lbl = lbl
00185 self.fld = fld
00186
00187 def populate(self, arg):
00188 """
00189 Clear this dict and set contents from the `arg`
00190
00191 :param arg: either path to pickled obj OR pre-retrieved dict
00192 """
00193 self.clear()
00194 dod = arg if type(arg) == dict else Pickler.retrieve(arg)
00195 for k in dod:
00196 self[k] = dod[k]
00197
00198 def __call__(self, db, table ):
00199 """
00200 Perform validity context group query, retaining
00201 results within this list of dict
00202
00203 :param db: DB instance
00204 :param table: payload table name
00205 """
00206 for d in db(self.sql % table):
00207 key = tuple(map(lambda k:int(d[k]),self.xcol))
00208 self[key] = d
00209
00210 def persist(self, path):
00211 """
00212 :param path: for pickling
00213 """
00214 Pickler.persist(dict(self), path)
00215
00216 def tuple_keys(self):
00217 return sorted(filter(lambda _:type(_)==tuple,self.keys()))
00218
00219 def column(self, k):
00220 tks = self.tuple_keys()
00221 return map(lambda tk:self[tk][k], tks )
00222
00223 def __repr__(self):
00224 return "\n".join([self.lbl] + map(lambda k:self.fmt % self[k], self.tuple_keys() ) )
00225
00226
00227
00228
00229 if __name__ == '__main__':
00230 import sys
00231 logging.basicConfig(level=logging.INFO)
00232 from DybPython import DB
00233 db = DB("client")
00234
00235 vg = VGroup(xf="SITEMASK SUBSITE", qf="SEQNO", tf="TIMESTART INSERTDATE")
00236 if len(sys.argv)>1:
00237 for pk in sys.argv[1:]:
00238 if os.path.exists(pk):
00239 vg.populate(pk)
00240 print vg
00241
00242 if 0:
00243 vg(db,"DcsAdLidSensor")
00244 vg.persist(pc)
00245
