def db_select_sensor(sort='DESC',num=100,offset=0,id=None,deviceId=None):
myconn = db_getConn()
cursor = myconn.cursor()
num = int(num)
offset = int(offset)
isEOF = False
paras = []
condition = []
#build sql
sqlstr = "SELECT * FROM SENSOR"
#set condition
if id is not None:
condition.append(" ID=?")
paras.append(id)
if deviceId is not None:
condition.append(" DEVICE_ID=?")
paras.append(deviceId)
#build condition
if len(condition)!=0:
sqlstr = sqlstr + " WHERE "
for con in condition:
sqlstr = sqlstr + con + " AND"
#remove the last "AND"
sqlstr = sqlstr[:-3]
sqlstr = sqlstr + " ORDER BY ID "+sort+" LIMIT ? OFFSET ?"
paras.append(num)
paras.append(offset)
#print sqlstr
try:
cursor.execute(sqlstr,paras)
except Exception,e:
log.logError("db_select_sensor fail,exception: "+e.message)
def db_update_sensor(id,deviceId,name=None,description=None,userDefArea=None):
myconn = db_getConn()
sqlstr = "UPDATE SENSOR SET "
para = []
if (name is None)\
and (description is None)\
and (userDefArea is None):
return error.ERR_CODE_OK_ #nothing to update
if name is not None:
sqlstr = sqlstr + "NAME=?, "
para.append(name)
if description is not None:
sqlstr = sqlstr + "DESCRIPTION=?, "
para.append(description)
if userDefArea is not None:
sqlstr = sqlstr + "USER_DEF_AREA=?, "
para.append(userDefArea)
sqlstr = sqlstr[:-2]
sqlstr = sqlstr+" WHERE ID=? AND DEVICE_ID=?"
para.append(id)
para.append(deviceId)
try:
myconn.execute(sqlstr,para)
myconn.commit()
except Exception,e:
log.logError("db_update_sensor fail,exception: "+e.message)
return error.ERR_CODE_ERR_
def db_select_device(sort='DESC',num=100,offset=0,id=None):
myconn = db_getConn()
rows = ()
cursor = myconn.cursor()
isEOF = True
num = int(num)
offset = int(offset)
try:
if id is None:
sqlstr = "SELECT * FROM DEVICE ORDER BY ID "+sort+" LIMIT ? OFFSET ?"
cursor.execute(sqlstr,(num,offset))
rows = cursor.fetchall()
if len(rows)<num:
isEOF = True
else:
isEOF = False
else:
if num<=0:
cursor.close()
return rows,True
sqlstr = "SELECT * FROM DEVICE WHERE ID=?"
cursor.execute(sqlstr,(id,))
rows = cursor.fetchall()
isEOF = True
except Exception,e:
log.logError("db_select_device fail,exception: "+e.message)
def db_delete_privilege(master, masterId, obj, objId):
myconn = db_getConn()
sqlstr = "DELETE FROM PRIVILEGE "
paras = []
condition = []
#set condition
if master is not None:
condition.append(" PRIVILEGE_MASTER=?")
paras.append(master)
if masterId is not None:
condition.append(" PRIVILEGE_MASTER_ID=?")
paras.append(masterId)
if obj is not None:
condition.append(" OBJ_TYPE=?")
paras.append(obj)
if objId is not None:
condition.append(" OBJ_ID=?")
paras.append(objId)
#build condition
if len(condition)!=0:
sqlstr = sqlstr + " WHERE "
for con in condition:
sqlstr = sqlstr + con + " AND"
#remove the last "AND"
sqlstr = sqlstr[:-3]
#print sqlstr
try:
myconn.execute(sqlstr,paras)
myconn.commit()
except Exception,e:
log.logError("db_delete_privilege fail,exception:"+e.message)
return error.ERR_CODE_ERR_
def db_update_user(id, pwd=None, userDefArea=None, state=None):
myconn = db_getConn()
sqlstr = "UPDATE USER SET "
para = []
if (pwd is None)\
and (userDefArea is None)\
and (state is None):
return error.ERR_CODE_OK_ #nothing to update
if pwd is not None:
sqlstr = sqlstr + "PWD=?, "
para.append(pwd)
if userDefArea is not None:
sqlstr = sqlstr + "USER_DEF_AREA=?, "
para.append(userDefArea)
if state is not None:
sqlstr = sqlstr + "STATE=?, "
para.append(state)
sqlstr = sqlstr[:-2]
sqlstr = sqlstr+" WHERE ID=?"
para.append(id)
try:
myconn.execute(sqlstr,para)
myconn.commit()
except Exception,e:
log.logError("db_update_user fail,exception: "+e.message)
return error.ERR_CODE_ERR_
def stop():
if ServerClass.server:
ServerClass.server.stop()
ServerClass.server = None
ioloop.IOLoop.instance().stop()
ServerClass._lock = False
log.logError("server stopped by user")
def db_update_dataset(deviceId,sensorId,value,key,createTime=None):
myconn = db_getConn()
sqlstr = "UPDATE DATASET SET VALUE=?, LAST_UPDATE_TIME=? WHERE KEY=? AND DEVICE_ID=? AND SENSOR_ID=?"
import time
timeNow = time.strftime("%Y-%m-%d %H:%M:%S ", time.localtime())
para = []
para.append(value)
para.append(timeNow)
para.append(key)
para.append(deviceId)
para.append(sensorId)
#conditon
if createTime is not None:
sqlstr = sqlstr + " AND CREATE_TIME=?"
para.append(createTime)
# print sqlstr
try:
myconn.execute(sqlstr,para)
myconn.commit()
except Exception,e:
log.logError("db_update_dataset fail,exception: "+e.message)
return error.ERR_CODE_ERR_
def db_delete_dataset(deviceId,sensorId,createTimeStart=None,createTimeEnd=None,key=None):
myconn = db_getConn()
sqlstr = "DELETE FROM DATASET"
paras = [deviceId,sensorId]
condition = [" DEVICE_ID=?"," SENSOR_ID=?"]
#set condition
if createTimeStart is not None:
condition.append(" CREATE_TIME>=?")
paras.append(createTimeStart)
if createTimeEnd is not None:
condition.append(" CREATE_TIME<=?")
paras.append(createTimeEnd)
if key is not None:
condition.append(" KEY=?")
paras.append(key)
#build condition
if len(condition)!=0:
sqlstr = sqlstr + " WHERE "
for con in condition:
sqlstr = sqlstr + con + " AND"
#remove the last "AND"
sqlstr = sqlstr[:-3]
#print sqlstr
try:
myconn.execute(sqlstr,paras)
myconn.commit()
except Exception,e:
log.logError("db_delete_dataset fail,exception:"+e.message)
return error.ERR_CODE_ERR_
def send_error_msg(self,err,body={}):
"""
err: self.__ERRCODE__['']
"""
log.logError("send_error_msg: status="+str(err['status'])+", retcode="+err['code'])
self.set_header('retcode',err['code'])
self.set_status(err['status'])
self.write(escape.json_encode(body))
self.finish()
def db_delete_deviceauth(userId, deviceId):
myconn = db_getConn()
sqlstr = "DELETE FROM DEVICEAUTH WHERE USER_ID=? AND DEVICE_ID=?"
try:
myconn.execute(sqlstr,(userId,deviceId))
myconn.commit()
except Exception,e:
log.logError("db_delete_deviceauth fail,exception:"+e.message)
return error.ERR_CODE_ERR_
def db_delete_sensor(deviceId,id):
myconn = db_getConn()
sqlstr = "DELETE FROM SENSOR WHERE ID=? AND DEVICE_ID=?"
try:
myconn.execute(sqlstr,(id,deviceId))
myconn.commit()
except Exception,e:
log.logError("db_delete_sensor fail,exception:"+e.message)
return error.ERR_CODE_ERR_
def db_delete_device(id):
myconn = db_getConn()
sqlstr = "DELETE FROM DEVICE WHERE ID=?"
try:
myconn.execute(sqlstr,(id,))
myconn.commit()
except Exception,e:
log.logError("db_delete_device fail,exception:"+e.message)
return error.ERR_CODE_ERR_
def db_update_accessKey(key,accessDevices):
myconn = db_getConn()
sqlstr = "UPDATE ACCESSKEY SET ACCESS_DEVICES=? WHERE KEY=? "
try:
myconn.execute(sqlstr,(accessDevices,key))
myconn.commit()
except Exception,e:
log.logError("db_update_accessKey fail,exception: "+e.message)
return error.ERR_CODE_ERR_
def db_insert_deviceauth(userId, deviceId):
myconn = db_getConn()
sqlstr = "INSERT INTO DEVICEAUTH (USER_ID, DEVICE_ID, IS_OWNER) "\
"VALUES (?,?,?)"
try:
myconn.execute(sqlstr,(userId,deviceId,True))
myconn.commit()
except Exception,e:
log.logError("db_insert_deviceauth fail,exception:"+e.message)
return error.ERR_CODE_ERR_
def db_select_userlist_by_deviceid(id):
myconn = db_getConn()
rows = []
sqlstr = "SELECT * FROM DEVICEAUTH WHERE DEVICE_ID=?"
cursor = myconn.cursor()
try:
cursor.execute(sqlstr,(id,))
except Exception,e:
log.logError("db_select_userlist_by_deviceid fail,exception:"+e.message)
return []
def db_select_owner_devicelist_by_userid(id):
myconn = db_getConn()
rows = []
sqlstr = "SELECT DEVICE_ID FROM DEVICEAUTH WHERE IS_OWNER=TRUE AND USER_ID=?"
cursor = myconn.cursor()
try:
cursor.execute(sqlstr,(id,))
except Exception,e:
log.logError("db_select_owner_devicelist_by_userid fail,exception:"+e.message)
return []
def _getArgumentFromBody(self,key,default=None):
"""
get argument from body. body should be json format
"""
#print self.request.body
try:
bodyDecode = json.loads(self.request.body)
#print bodyDecode
except Exception,e:
log.logError("_getArgumentFromBody load body to json fail,exception: "+e.message)
return default
def db_select_user_by_name(name):
#print name
myconn = db_getConn()
cursor = myconn.cursor()
sqlstr = "SELECT * FROM USER WHERE NAME=?"
try:
cursor.execute(sqlstr,(name,))
row = cursor.fetchone()
except Exception,e:
log.logError("db_select_user_by_name fail,exception:"+e.message)
cursor.close()
return []
def db_select_accessKey(key):
myconn = db_getConn()
rows = ()
cursor = myconn.cursor()
if key is None:
return rows
try:
sqlstr = """SELECT * FROM ACCESSKEY WHERE KEY=?"""
cursor.execute(sqlstr,(key,))
rows = cursor.fetchall()
except Exception,e:
log.logError("db_select_accessKey fail,exception: "+e.message)
def db_insert_device(name,description,local,latitude,longitude,userDefArea,key):
myconn = db_getConn()
sqlstr = "INSERT INTO DEVICE (ID, NAME, DESCRIPTION, REG_TIME, LOCAL, LATITUDE, LONGITUDE, USER_DEF_AREA, KEY) "\
"VALUES (NULL,?,?,?,?,?,?,?,?)"
import time
timeNow = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
try:
myconn.execute(sqlstr,(name,description,timeNow,local,latitude,longitude,userDefArea,key))
myconn.commit()
except Exception,e:
log.logError("db_insert_device fail,exception:"+e.message)
return None
def db_insert_sensor(name,description,deviceId,userDefArea):
myconn = db_getConn()
sqlstr = "INSERT INTO SENSOR (ID, NAME, DESCRIPTION, REG_TIME, DEVICE_ID, USER_DEF_AREA) "\
"VALUES (NULL,?,?,?,?,?)"
import time
timeNow = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
try:
myconn.execute(sqlstr,(name,description,timeNow,deviceId,userDefArea))
myconn.commit()
except Exception,e:
log.logError("db_insert_sensor fail,exception:"+e.message)
return None
def db_insert_dataset(deviceId,sensorId,key,value):
myconn = db_getConn()
sqlstr = "INSERT INTO DATASET (DEVICE_ID, SENSOR_ID, CREATE_TIME, LAST_UPDATE_TIME, KEY, VALUE) "\
"VALUES (?,?,?,?,?,?)"
import time
timeNow = time.strftime("%Y-%m-%d %H:%M:%S ", time.localtime())
try:
myconn.execute(sqlstr,(deviceId,sensorId,timeNow,timeNow,key,value))
myconn.commit()
except Exception,e:
log.logError("db_insert_dataset fail,exception:"+e.message)
return None
def db_update_commandset(deviceId,sensorId,command,value):
myconn = db_getConn()
sqlstr = "UPDATE COMMANDSET SET VALUE=?,LAST_UPDATE_TIME=? WHERE DEVICE_ID=? AND SENSOR_ID=? AND COMMAND=?"
import time
timeNow = time.strftime("%Y-%m-%d %H:%M:%S ", time.localtime())
try:
myconn.execute(sqlstr,(value,timeNow,deviceId,sensorId,command))
myconn.commit()
except Exception,e:
log.logError("db_update_commandset fail,exception: "+e.message)
return error.ERR_CODE_ERR_
def run():
#加锁,只能启动一个服务
if ServerClass._lock:
log.logError("server is already running..")
return
ServerClass._lock = True
#初始化数据库
database.db_init()
#添加超级用户
privilegeM.priv_add_superuser()
#初始化权限管理模块
privilegeM.priv_init()
#解析命令行, 有了这行，还可以看到日志...
#options.parse_command_line()
settings = {
'debug': False,
'gzip': True,
'autoescape': None,
'xsrf_cookies': False,
'cookie_secret':"61oETzKXQAGaYdkL5gEmGeJJFuYh7EQnp2XdTP1o/Vo=",
'template_path':os.path.join(os.path.dirname(__file__), "playground"),
'static_path':os.path.join(os.path.dirname(__file__), "playground/main/static"),
'login_url':'/playground/login'
}
application = web.Application(
[
(HTTP_RES['root'], handlers.rootHandle),
(HTTP_RES['user'], handlers.usersHandle),
(HTTP_RES['userOne'], handlers.userOneHandle),
(HTTP_RES['device'], handlers.devicesHandle),
(HTTP_RES['deviceOne'], handlers.deviceOneHandle),
(HTTP_RES['sensor'], handlers.sensorsHandle),
(HTTP_RES['sensorOne'], handlers.sensorOneHandle),
(HTTP_RES['dataSet'], handlers.datasetHandle),
(HTTP_RES['commandSet'], handlers.commandsetHandle),
(HTTP_RES['commandSetOne'], handlers.commandsetOneHandle),
(HTTP_RES['deviceAuth'], handlers.deviceauthHandle),
(HTTP_RES['accessKey'], handlers.accessKeyHandle),
(HTTP_RES['userLogin'], handlers.userLoginHandle),
(HTTP_RES['userLogout'], handlers.userLogoutHandle),
##playgruod below
(r'/playground', playground.rootHandle),
(r'/playground/login', playground.loginHandle),
], **settings)
ServerClass.server = HTTPServer(application)
ServerClass.server.listen(configure.server_port,configure.server_ip)
try:
ioloop.IOLoop.instance().start()
except Exception,e:
log.logFatal("start server fail, exception:"+e.message)
ServerClass._lock = False
def db_select_dataset(deviceId,sensorId,sort='DESC',num=100,offset=0,orderBy='CREATE_TIME',
createTimeStart=None, createTimeEnd=None,lastUpdateTimeStart=None,
lastUpdateTimeEnd=None,key=None,valueMin=None,valueMax=None):
myconn = db_getConn()
cursor = myconn.cursor()
isEOF = True
num = int(num)
offset = int(offset)
paras = []
condition = []
#build sql
sqlstr = "SELECT * FROM DATASET "
#set condition
if createTimeStart is not None:
condition.append(" CREATE_TIME>=?")
paras.append(createTimeStart)
if createTimeEnd is not None:
condition.append(" CREATE_TIME<=?")
paras.append(createTimeEnd)
if lastUpdateTimeStart is not None:
condition.append(" LAST_UPDATE_TIME>=?")
paras.append(lastUpdateTimeStart)
if lastUpdateTimeEnd is not None:
condition.append(" LAST_UPDATE_TIME<=?")
paras.append(lastUpdateTimeEnd)
if key is not None:
condition.append(" KEY=?")
paras.append(key)
if valueMin is not None:
condition.append(" VALUE>=?")
paras.append(valueMin)
if valueMax is not None:
condition.append(" VALUE<=?")
paras.append(valueMax)
#build condition
if len(condition)!=0:
sqlstr = sqlstr + " WHERE "
for con in condition:
sqlstr = sqlstr + con + " AND"
#remove the last "AND"
sqlstr = sqlstr[:-3]
sqlstr = sqlstr + " ORDER BY "+orderBy+" "+sort+" LIMIT ? OFFSET ?"
paras.append(num)
paras.append(offset)
#print sqlstr
#print paras
try:
cursor.execute(sqlstr,paras)
except Exception,e:
log.logError("db_select_sendataset fail,exception: "+e.message)
def db_delete_commandset(deviceId,sensorId,command=None):
myconn = db_getConn()
sqlstr = "DELETE FROM COMMANDSET WHERE SENSOR_ID=? AND DEVICE_ID=?"
listParas = [sensorId,deviceId]
if command is not None:
listParas.append(command)
sqlstr = sqlstr + " AND COMMAND=?"
try:
myconn.execute(sqlstr,listParas)
myconn.commit()
except Exception,e:
log.logError("db_delete_commandset fail,exception:"+e.message)
return error.ERR_CODE_ERR_
def db_select_privilege(master, masterId, obj, objId):
myconn = db_getConn()
rows = ()
cursor = myconn.cursor()
try:
sqlstr = """SELECT * FROM PRIVILEGE WHERE
(PRIVILEGE_MASTER=? OR PRIVILEGE_MASTER="all")
AND (PRIVILEGE_MASTER_ID=? OR PRIVILEGE_MASTER_ID="all")
AND (OBJ_TYPE=? OR OBJ_TYPE="all")
AND (OBJ_ID=? OR OBJ_ID="all")"""
cursor.execute(sqlstr,(master, masterId, obj, objId))
rows = cursor.fetchall()
except Exception,e:
log.logError("db_select_privilege fail,exception: "+e.message)
def db_insert_commandset(deviceId,sensorId,command,value):
myconn = db_getConn()
sqlstr = "INSERT INTO COMMANDSET (DEVICE_ID, SENSOR_ID, COMMAND, VALUE, CREATE_TIME, LAST_UPDATE_TIME) "\
"VALUES (?,?,?,?,?,?)"
import time
timeNow = time.strftime("%Y-%m-%d %H:%M:%S ", time.localtime())
if isinstance(value,dict):
value = json.dumps(value)
try:
myconn.execute(sqlstr,(deviceId,sensorId,command,value,timeNow,timeNow))
myconn.commit()
except Exception,e:
log.logError("db_insert_commandset fail,exception:"+e.message)
return error.ERR_CODE_KEY_CONFLICT
def db_insert_user(name,pwd,userDefArea):
"""
:param name:
:param pwd:
:param userDefArea:
:return:userid
"""
#print name
myconn = db_getConn()
sqlstr = "INSERT INTO USER (ID, NAME, PWD, REG_TIME, LAST_LOGIN_TIME, STATE, USER_DEF_AREA) "\
"VALUES (NULL,?,?,?,?,?,?)"
import time
timeNow = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
try:
myconn.execute(sqlstr,(name,pwd,timeNow,'','offline',userDefArea))
myconn.commit()
except Exception,e:
log.logError("db_insert_user fail,exception:"+e.message)
return None
def db_update_device(id,name=None,description=None,local=None,latitude=None,longitude=None,userDefArea=None):
myconn = db_getConn()
sqlstr = "UPDATE DEVICE SET "
para = []
if (name is None)\
and (description is None)\
and (local is None)\
and (latitude is None)\
and (longitude is None)\
and (userDefArea is None):
return error.ERR_CODE_OK_ #nothing to update
if name is not None:
sqlstr = sqlstr + "NAME=?, "
para.append(name)
if description is not None:
sqlstr = sqlstr + "DESCRIPTION=?, "
para.append(description)
if local is not None:
sqlstr = sqlstr + "LOCAL=?, "
para.append(local)
if latitude is not None:
sqlstr = sqlstr + "LATITUDE=?, "
para.append(latitude)
if longitude is not None:
sqlstr = sqlstr + "LONGITUDE=?, "
para.append(longitude)
if userDefArea is not None:
sqlstr = sqlstr + "USER_DEF_AREA=?, "
para.append(userDefArea)
sqlstr = sqlstr[:-2]
sqlstr = sqlstr+" WHERE ID=?"
para.append(id)
try:
myconn.execute(sqlstr,para)
myconn.commit()
except Exception,e:
log.logError("db_update_device fail,exception: "+e.message)
return error.ERR_CODE_ERR_
