def addHouse(hid,address,details,photofilepath=''):
unittype = 'household'
tab = unitTables[unittype]
name = None
parentdigest = None
cmd = "insert into %s (id,address,details) values('%s','%s','%s')"\
%(tab,hid,address,details)
res = sql.run_insert_cmd(cmd)
if res[0] == 1:
parentdigest = get_digest(hid=hid)
ctab = unitTables['user']
cmd = "insert into %s (id,parentdigest,name,details,photo) values('default','%s',"\
"'Default User','Default for House %s','%s')"%(ctab,parentdigest,hid,photofilepath)
res1 = sql.run_insert_cmd(cmd)
return res
def moveDevice(hid,ouid,nuid,did):
unittype = 'device'
utab = unitTables[unittype]
ctab = capTables[unittype]
if ouid == '':
ouid = default_user
if nuid == '':
nuid = default_user
if nuid == ouid:
return(1,"SUCCESS")
odigest = get_digest(hid,ouid,did)
ndigest = get_digest(hid,nuid,did)
npdigest = get_digest(hid,nuid)
#cmd1 = "update %s set parentdigest = '%s' where capped is True and cap != 'Infinity' and digest = '%s'"%(ctab,npdigest,odigest)
cmd1 = "update %s set parentdigest = '%s' where digest = '%s'"%(utab,npdigest,odigest)
cmd2 = "update %s set digest = '%s' where digest = '%s'"%(utab,ndigest,odigest)
prnt = 1
conn = sql.sqlconn()
sql.run_insert_cmd("begin",conn=conn,prnt=prnt)
for cmd in [cmd1,cmd2]:
res = sql.run_insert_cmd(cmd,conn=conn,prnt=prnt)
if res[0] == 0:
sql.run_insert_cmd("rollback",conn=conn,prnt=prnt)
return (0,"ERROR")
sql.run_insert_cmd("commit",conn=conn,prnt=prnt)
return (1,"SUCCESS")
def addUser(hid,uid,name,details,photofilepath=''):
unittype = 'user'
tab = unitTables[unittype]
address = None
parentdigest = get_digest(hid=hid)
cmd = "insert into %s (id,parentdigest,name,details,photo) values('%s','%s','%s','%s','%s')"\
%(tab,uid,parentdigest,name,details,photofilepath)
res = sql.run_insert_cmd(cmd)
return res
def addDevice(hid,uid,did,name,details,macaddr,photofilepath=''):
unittype = 'device'
tab = unitTables[unittype]
address = None
if uid == '':
uid = default_user
parentdigest = get_digest(hid=hid,uid=uid)
cmd = "insert into %s (id,parentdigest,name,details,photo,macid) values('%s','%s','%s','%s','%s',"\
%(tab,did,parentdigest,name,details,photofilepath)
mcmd = "'{"
for macid in macaddr:
mcmd = "%s%s,"%(mcmd,macid)
mcmd = "%s%s"%(mcmd[0:-1],"}')")
cmd = "%s%s"%(cmd,mcmd)
print cmd
res = sql.run_insert_cmd(cmd)
return res
def write_block_v1_0(data, tables, log, file):
if 'info' not in data:
log.write('Error: No info field in %s\n' % (file))
return
flag = 0
for tab in tables:
if tab in data:
flag = 1
break
if flag == 0:
log.write('Error: No known fields in %s\n' % (file))
return
#print data
print data
for tab in tables:
if tab in data:
numrec = len(data[tab])
for i in range(0, numrec):
table = tables[tab]
fids = []
vals = []
if tab != 'hop':
fids, vals = get_measurement_params(
fids, vals, data['info'][0])
else:
break
ttid = data[tab][i]['ttid']
data[tab][i].pop('ttid')
#did = data['info'][0]['deviceid'][-12:]
didTmp = data['info'][0]['deviceid'][-12:]
did = didTmp.replace(":", "")
ts = data['traceroute'][ttid]['timestamp']
srcip = data['traceroute'][ttid]['srcip']
dstip = data['traceroute'][ttid]['dstip']
tid = get_id_from_table(tables['traceroute'], did, srcip,
dstip, ts)
idtuple = {"tid": tid}
fids, vals = get_measurement_params(fids, vals, idtuple)
fids, vals = get_measurement_params(fids, vals, data[tab][i])
cmd = form_insert_cmd(table, fids, vals)
print cmd
res = sql.run_insert_cmd(cmd, conn=conn, prnt=1)
cnt = 0
def updateHouseMetaInfo(hid,address='',details='',photo=''):
unittype = 'household'
tab = unitTables[unittype]
digest = get_digest(hid=hid)
cmd = "update %s set "%(tab)
if address == '' and details == '' and photo == '':
return (1,("SUCCESS"))
if address != '':
cmd = "%s address='%s',"%(cmd,address)
if details != '':
cmd = "%s details='%s',"%(cmd,details)
if photo != '':
cmd = "%s photo='%s',"%(cmd,photo)
cmd = "%s where digest = '%s'"%(cmd[:-1],digest)
print cmd
res = sql.run_insert_cmd(cmd)
try:
return (1,("SUCCESS"))
except:
return (0,('ERROR: Could not update table'))
def write_block_v1_0(data,tables,log,file):
if 'info' not in data:
log.write('Error: No info field in %s\n'%(file))
return
flag = 0
for tab in tables:
if tab in data:
flag = 1
break
if flag == 0:
log.write('Error: No known fields in %s\n'%(file))
return
#print data
for tab in tables:
if tab in data:
numrec = len(data[tab])
for i in range(0,numrec):
table = tables[tab]
fids = []
vals = []
if tab != 'hop':
fids,vals = get_measurement_params(fids,vals,data['info'][0])
else:
ttid = data[tab][i]['ttid']
data[tab][i].pop('ttid')
did = data['info'][0]['deviceid'][-12:]
ts = data['traceroute'][ttid]['timestamp']
srcip = data['traceroute'][ttid]['srcip']
dstip = data['traceroute'][ttid]['dstip']
tid = get_id_from_table(tables['traceroute'],did,srcip,dstip,ts)
idtuple = {"tid":tid}
fids,vals = get_measurement_params(fids,vals,idtuple)
fids,vals = get_measurement_params(fids,vals,data[tab][i])
cmd = form_insert_cmd(table,fids,vals)
print cmd
res = sql.run_insert_cmd(cmd,conn=conn,prnt=1)
cnt = 0
def updateUserMetaInfo(hid,uid,name='',details='',photo='',did=None):
if did == None:
unittype = 'User'
digest = get_digest(hid=hid,uid=uid)
else:
unittype = 'Device'
digest = get_digest(hid=hid,uid=uid,did=did)
tab = unitTables[unittype]
cmd = "update %s set "%(tab)
if name == '' and details == '' and photo == '':
return (1,("SUCCESS"))
if name != '':
cmd = "%s name='%s',"%(cmd,name)
if details != '':
cmd = "%s details='%s',"%(cmd,details)
if photo != '':
cmd = "%s photo='%s',"%(cmd,photo)
cmd = "%s where digest = '%s'"%(cmd[:-1],digest)
print cmd
res = sql.run_insert_cmd(cmd)
try:
return (1,("SUCCESS"))
except:
return (0,('ERROR: Could not update table'))
def write_block_v1_0(data,tables,log,fname):
if 'info' not in data:
log.write('Error: No info field in %s\n'%(fname))
return False
flag = 0
for tab in tables:
if tab in data:
flag = 1
break
if flag == 0:
log.write('Error: No known fields in %s\n'%(fname))
return False
#print data
#postcmds = ['begin']
postcmds = []
global traceroutearr
invalid_data=False
for tab in tables:
if tab in data:
numrec = len(data[tab])
for i in range(0,numrec):
table = tables[tab]
fids = []
vals = []
if tab != 'hop':
fids,vals = get_measurement_params(fids,vals,data['info'][0])
fids,vals = get_measurement_params(fids,vals,data[tab][i])
cmd,cvals = form_insert_cmd(table,fids,vals)
else:
try:
ttid = data[tab][i]['ttid']
data[tab][i].pop('ttid')
did = data['info'][0]['deviceid'][-12:]
ts = data['traceroute'][ttid]['timestamp']
srcip = data['traceroute'][ttid]['srcip']
dstip = data['traceroute'][ttid]['dstip']
except:
#invalid data
invalid_data=True
postcmds = []
break
try:
toolid = data['traceroute'][ttid]['tool']
except:
try:
toolid = data['traceroute'][ttid]['toolid']
except:
toolid = 'traceroute'
tup = (did,ts,srcip,dstip,toolid)
#idtuple = {"tid":''}
#print tab
#fids,vals = get_measurement_params(fids,vals,idtuple)
fids,vals = get_measurement_params(fids,vals,data[tab][i])
try:
hopid = vals[fids.index('id')]
hopip = vals[fids.index('ip')]
hoprtt = vals[fids.index('rtt')]
hopval = (hopid,hopip,hoprtt)
except:
invalid_data=True
postcmds = []
break
try:
traceroutearr[tup].append(hopval)
except:
traceroutearr[tup] = [hopval]
#print cmd,table
#if tab == 'traceroute':
# cmd = "%s returning encode(id,'escape')"%(cmd)
# #res = sql.run_data_cmd(cmd,cvals,conn=conn,prnt=1) # to get return value
# did = data['info'][0]['deviceid'][-12:]
# ts = vals[fids.index('timestamp')]
# srcip = vals[fids.index('srcip')]
# dstip = vals[fids.index('dstip')]
# if 'tool' in fids:
# toolid = vals[fids.index('tool')]
# else:
# toolid = 'traceroute'
# tup = (did,ts,srcip,dstip,toolid)
# traceroutearr[tup] = []
if tab != 'hop' and tab != 'traceroute':
postcmds.append([cmd,cvals])
if invalid_data == True:
return False
if len(postcmds) > 1:
#postcmds.append('commit')
sql.run_insert_cmd(postcmds,conn=conn)
if len(traceroutearr) > 0:
bsdtr.write(traceroutearr)
traceroutearr = {}
return True