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_