def insertNetInfo(waveCursor, netList):
try:
strLst = []
for netElm in netList:
if netElm['ntype'] == 'w':
strValue = "('%s', %s, '%s', '%s', '%s', %s, unix_timestamp())" % \
(netElm['playSessionID'], netElm['stTime'], netElm['ntype'], \
netElm['bssid'], netElm['ssid'], netElm['traffic'])
else:
strValue = "('%s', %s, '%s', NULL, NULL, %s, unix_timestamp())" % \
(netElm['playSessionID'], netElm['stTime'], netElm['ntype'], netElm['traffic'])
strLst.append(strValue)
if len(strLst) > 0:
sql = """insert into vidsession_net (playSessionID, stTime, ntype, bssid, ssid, traffic, lstuptmp)
values %s
on duplicate key update ntype = values(ntype), bssid = values(bssid), ssid = values(ssid), traffic = values(traffic), lstuptmp = unix_timestamp()
""" % ', '.join(strLst)
ret = waveCursor.execute(sql)
if ret == 0:
log.warn("insertNetInfo no record affected [%s]" % sql)
except Exception, e:
log.error("insertNetInfo error:%s" % e)
log.error(strLst)
raise e
def updateVidnet(waveCursor, vidDict):
if vidDict == None:
log.warn('updateVidnet : vidDict is null')
return False
playSessionID = vidDict.pop('playSessionID')
cols = vidDict.keys()
vals = vidDict.values()
try:
slist = []
for key in vidDict:
if type(vidDict[key]) == str or type(vidDict[key]) == unicode:
s = "%s = '%s'" % (key, vidDict[key].replace("'", "''"))
else:
s = "%s = %s" % (key, str(vidDict[key]))
slist.append(s)
slist.append("lstuptmp = unix_timestamp()")
#sql = "UPDATE vidsession SET %s WHERE playSessionID = '%s'" % (unicode(',', 'utf-8').join(map(lambda key:"%s='%s'" % (key, unicode(vidDict[key], 'utf-8').replace("'", "''")), vidDict)), playSessionID)
sql = "UPDATE vidsession SET %s WHERE playSessionID = '%s'" % (unicode(',', 'utf-8').join(slist), playSessionID)
waveCursor.execute(sql)
except Exception, e:
log.error("update vidsession ERROR:%s, playSessionID:%s" % (e, playSessionID))
log.error(vidDict)
raise e
def retrieve_category_info(lang: str,
category: str) -> Optional[List[Dict[str, str]]]:
html = http_get(f'{URL}/{lang}/Challenges/{category}/')
if html is None:
log.warn('category_not_found', category=category)
return None
log.msg(f"Fetched category page '{category}'")
return extract_category_info(html, category)
def process(self, params):
try:
self.rdbCur = None
self.rdbCur = worker.dbmanager.allocDictCursor('myapwave')
pnm = "p%x" % (int(random.random()*100) % 15 + 1)
while True:
self.rdbCur.execute("START TRANSACTION")
strSQL = "SELECT deviceID, lstuptmp from appssdist partition (%s) where isbusy = '0' and procTmp < %d order by lstuptmp limit 1 for update" % (pnm, params['tmp'])
ret = self.rdbCur.execute(strSQL)
if ret > 0 and ret < 10000: # Sometimes Mysql returns 18446744073709551615 instead of -1
row = self.rdbCur.fetchone()
devid = row['deviceID']
lstuptmp = row['lstuptmp']
strSQL = "UPDATE appssdist SET isbusy = '1' where deviceID = '%s'" % (devid)
ret = self.rdbCur.execute(strSQL)
self.rdbCur.execute("COMMIT")
log.info("process:appssdist '1' %s" % devid)
try:
procnt = self.passLog(devid)
except Exception, e:
log.error("Appsession.process in while [%s] %s" % (devid, e))
while True:
try:
rtcnt = 0
if procnt > 0:
strSQL = "UPDATE appssdist SET isbusy = '0', procTmp = %d, lstuptmp = unix_timestamp() where deviceID = '%s'" % (params['tmp'], devid)
else:
if lstuptmp < params['tmp'] - 600:
strSQL = "DELETE FROM appssdist where deviceID = '%s'" % (devid)
else:
strSQL = "UPDATE appssdist SET isbusy = '0', procTmp = %d where deviceID = '%s'" % (params['tmp'], devid)
ret = self.rdbCur.execute(strSQL)
log.info("process: appssdist '0' %s" % devid)
break
except Exception, e:
if str(e).find('restart') > 0:
log.warn("process: appssdist[%s] set to '0' retry: %d, %s" % (devid, rtcnt, e))
if rtcnt > 10:
raise e
time.sleep(rtcnt+1)
rtcnt += 1
else:
log.error("process: appssdist[%s] set to '0' %s" % (devid, e))
raise e
else:
def updateAppssLst(self, data, sessionID):
try:
strSQL = """REPLACE appsession_lst
(androidID, pkgnm, tTM, numTotalHits, sID,sessionID, oid)
VALUES ('%s', '%s', %.3f, %d, %d, '%s', '%s') """ % (data['deviceID'], data['pkgName'],
float(data['tTM']), int(data['numTotalHits']), int(data.get('sID', 0)), sessionID, data.get('objID', ''))
ret = self.rdbCur.execute(strSQL)
if ret == 0:
log.warn("UPDATE appsession_lst 0 row strSQL:%s" % strSQL)
except Exception, e:
log.error("update appsession_lst :%s" % e)
log.error(strSQL)
raise e
def insertVidnetLog(waveCursor, vidLogDict):
if vidLogDict == None:
log.warn('vidLogDict is null')
return False
cols = vidLogDict.keys()
vals = vidLogDict.values()
try:
sql = """insert into vidsession_log (%s, lstuptmp) values (%s, unix_timestamp())""" % (",".join(cols), ",".join(["'" + str(val).replace("'", "''") + "'" for val in vals]))
waveCursor.execute(sql)
except Exception, e:
log.error("INSERT vidsession_log ERROR:%s" % e)
log.error(vidLogDict)
raise e
def insertBBSQL(waveCursor, bbList):
try:
strLst = []
for bbElm in bbList:
if len(bbElm['bb']) == 8 and bbElm['bb'][7] == 'e':
strValue = "('%s', %s, %s, '%s', '%s', %s, %s, %s, unix_timestamp())" % (bbElm['psid'], bbElm['bb'][0], bbElm['bb'][2], \
'{0:02d}'.format(int(bbElm['bb'][1])), '{0:02d}'.format(int(bbElm['bb'][3])), bbElm['bb'][4], bbElm['bb'][5], bbElm['bb'][6])
elif len(bbElm['bb']) == 6:
strValue = "('%s', %s, %s, '%s', '%s', %s, %s, NULL, unix_timestamp())" % (bbElm['psid'], bbElm['bb'][0], bbElm['bb'][2], \
'{0:02d}'.format(int(bbElm['bb'][1])), '{0:02d}'.format(int(bbElm['bb'][3])), bbElm['bb'][4], bbElm['bb'][5])
else:
log.warn("BBList format error:")
log.warn(bbElm)
continue
strLst.append(strValue)
if len(strLst) > 0:
sql = """insert into vidsession_bb (playSessionID, stTime, endTime, stCode, endCode, trWF, trCell, stBBTime, lstuptmp)
values %s
on duplicate key update endTime = values(endTime), stCode = values(stCode), trWF = values(trWF), trCell = values(trCell), stBBTime = values(stBBTime), lstuptmp = unix_timestamp()
""" % ', '.join(strLst)
ret = waveCursor.execute(sql)
if ret == 0:
log.warn("insertBBSQL no record affected [%s]" % sql)
except Exception, e:
log.error("insertBBSQL error:%s" % e)
log.error(bbList)
raise e
async def set_all_challenges(lang: str) -> None:
html = http_get(f'{URL}/{lang}/Challenges/')
if html is None:
log.error('challenges_page_not_found')
return
categories = extract_categories(html)
if not categories:
log.warn('fetch_all_categories_failed', lang=lang)
return
log.debug('fetched_categories', categories=categories, lang=lang)
tp_func = partial(retrieve_category_info, lang)
with ThreadPool(len(categories)) as tp:
response = tp.map(tp_func, categories)
timestamp = datetime.now().isoformat()
await app.redis.set(
f'{lang}.challenges',
json.dumps({
'body': response,
'last_update': timestamp
}))
await app.redis.set(
f'{lang}.categories',
json.dumps({
'body': categories,
'last_update': timestamp
}))
for category_data in response:
await app.redis.set(
f'{lang}.categories.{category_data[0]["name"]}',
json.dumps({
'body': category_data,
'last_update': timestamp
}))
log.debug('set_all_challenges_success', lang=lang)
def insertVidnet(waveCursor, vidDict):
if vidDict == None:
log.warn('vidnetDict is null')
return False
cols = vidDict.keys()
vals = vidDict.values()
try:
slist = []
for v in vals:
if type(v) == str or type(v) == unicode:
slist.append("'" + v.replace("'", "''") + "'")
else:
slist.append(str(v))
sql = """insert into vidsession (%s, lstuptmp) values (%s, unix_timestamp())""" % (",".join(cols), unicode(",", "utf-8").join(slist))
waveCursor.execute(sql)
except Exception, e:
log.error("INSERT VIDNET ERROR:%s" % e)
log.error(vidDict)
raise e
def __new__(cls, state, bssid, ssid='', regdtm='19000101000000', rssi=-200, bregap=False, bmap=False, optrcom='none', geoloc=None, priority=Priority.NORMAL):
# Classify WiFi
try:
if ssid not in ('', None):
ssid = re.sub(r'^\s*"(.*)"\s*$', r'\1', unicode(ssid))
if ssid.find('"') >= 0:
log.error("!!! SSID - %s" % ssid)
if cls.isHotspot(ssid):
priority = Priority.LOW
else:
optrcom = cls.getWiFiOperator(ssid)
bregap = True if optrcom != 'none' else False
if not bregap:
bmap = cls.isMobile(ssid)
try:
ssid = MySQLdb.escape_string(unicode(ssid).encode('utf-8'))
except Exception, e:
# Non-ascii data.
log.warn("SSID MySQLdb.escape_string Error - %s, %s" % (ssid, e))
if not geoloc:
geoloc = GeoInfo()
def processNWcharac(self, alog):
try:
if alog.get('bssid', '') == '' or alog['bssid'] == None:
return
bssid = alog['bssid']
SumfrLst = [{'fq':0, 'avgrssi':0} for i in range(10)]
SumTpDict = {}
cocoaAvg = {'freq':0, 'estTP':0.0}
tpAvg = {'freq':0, 'avgTP':0.0}
#get saved nwcharac values from RDB
strSQL = "SELECT itype, lvl, freq, fvalue FROM apnwc WHERE bssid = '%s'" % bssid
ret = self.rdbCur.execute(strSQL)
if ret > 0:
rows = self.rdbCur.fetchall()
for row in rows:
if row['itype'] == '0':
SumfrLst[row['lvl']]['avgrssi'] = row['fvalue']
SumfrLst[row['lvl']]['fq'] = row['freq']
elif row['itype'] == '1':
SumTpDict[str(row['lvl'])] = row['freq']
elif row['itype'] == '2':
cocoaAvg = {'freq':row['freq'], 'estTP':row['fvalue']}
elif row['itype'] == '3':
tpAvg = {'freq':row['freq'], 'avgTP':row['fvalue']}
else:
log.warn("processNWcharac Unknow itype %s:%s" % (bssid, row['itype']))
continue
#parse aatlog's NWcharac string & store into temporary storage
if alog.get('NWcharac', None) <> None and alog['NWcharac'] > '':
nwstr = alog['NWcharac'].split('|')
if len(nwstr) == 2:
fqrssiLst = nwstr[0].split('.')
for i, fqrssi in enumerate(fqrssiLst):
fqlst = fqrssi.split(',')
if len(fqlst) == 2 and fqlst[0].isdigit() and fqlst[0] > 0 and fqlst[1].isdigit():
#RSSI AVG: frequency number * rssi / frequency number = (lastest) avg. rssi
#RSSI AVG Accumulated: (lastest avg. rssi * sum of frequency number + new freq * rssi) / (sum of freq number + new freq)
SumfrLst[i]['avgrssi'] = float(SumfrLst[i]['avgrssi']*SumfrLst[i]['fq'] + int(fqlst[0])*int(fqlst[1])) / float(SumfrLst[i]['fq']+int(fqlst[0]))
SumfrLst[i]['fq'] += int(fqlst[0])
varTPLst = nwstr[1].split('.')
for varTP in varTPLst:
TPLst = varTP.split(',')
if len(TPLst) == 2 and TPLst[0].isdigit():
if SumTpDict.has_key(TPLst[0]):
SumTpDict[TPLst[0]] += 1
else:
SumTpDict[TPLst[0]] = 1
if alog.has_key('estTP') and alog['estTP'] <> None:
if alog['estTP'] > 0:
cocoaAvg['freq'] += 1
cocoaAvg['estTP'] += int(alog['estTP'])
if alog.has_key('avgTP') and alog['avgTP'] <> None:
if alog['avgTP'] > 0:
tpAvg['freq'] += 1
tpAvg['avgTP'] += alog['avgTP']
#update RDB data
valuesLst = []
for i, fr in enumerate(SumfrLst):
strvalue = "('%s', '0', %d, %d, %f)" % (bssid, i, fr['fq'], fr['avgrssi'])
valuesLst.append(strvalue)
for key in SumTpDict:
strvalue = "('%s', '1', %d, %d, 0.0)" % (bssid, int(key), SumTpDict[key])
valuesLst.append(strvalue)
strvalue = "('%s', '2', 0, %d, %f)" % (bssid, cocoaAvg['freq'], cocoaAvg['estTP'])
valuesLst.append(strvalue)
strvalue = "('%s', '3', 0, %d, %f)" % (bssid, tpAvg['freq'], tpAvg['avgTP'])
valuesLst.append(strvalue)
strSQL = """INSERT INTO apnwc (bssid, itype, lvl, freq, fvalue)
VALUES %s
ON DUPLICATE KEY UPDATE
freq = VALUES(freq), fvalue = VALUES(fvalue)
""" % ', '.join(valuesLst)
ret = self.rdbCur.execute(strSQL)
except Exception, e:
log.error("processNWcharac: %s" % e)
log.error(alog)