def __init__(self, waEnviron): waResourceService.__init__(self, waEnviron) self.identifier = self.pathinfo[-1] if not self.pathinfo[-1]=="specimens" else None self.servicename = self.pathinfo[2] self.force = False if self.identifier is None: if (self.waEnviron['parameters'] and ('etime' in self.waEnviron['parameters']) and ('procedure' in self.waEnviron['parameters'])): self.etime = [ iso.parse_datetime(t) for t in self.waEnviron['parameters']['etime'][0].split("/") ] # self.etime = self.waEnviron['parameters']['etime'][0].split("/") self.procedure = self.waEnviron['parameters']['procedure'][0] else: self.etime = None self.procedure = None if (self.waEnviron['parameters'] and 'forceInsert' in self.waEnviron['parameters']): if self.waEnviron['parameters'] in ['true', 'True','TRUE']: self.force = True self.conn = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port'])
def executePut(self): """ Method for executing a POST requests that create a new SOS observed property """ if self.service == "default": raise Exception("observedproperties operation can not be done for default service instance.") elif self.uoms == None: raise Exception("destination observedproperty is not specified.") else: servicedb = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port']) sql = "SELECT id_uom FROM %s.uoms " % self.service sql += "WHERE name_uom = %s" par = (self.uoms,) row = servicedb.select(sql, par) if not row: raise Exception("Original Unit of measure '%s' does not exist." % self.uoms) oid = row[0]["id_uom"] sql = "UPDATE %s.uoms " % self.service sql += "SET name_uom = %s, desc_uom=%s" sql += " WHERE id_uom = %s" par = (str(self.json['name']),self.json['description'],oid) servicedb.execute(sql,par) self.setMessage("Update successfull")
def check_data(self, data): """ Method to check subscription Check if a user can receive notification via mail or twitter """ servicedb = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) sql = "SELECT * FROM wns.user WHERE id = %s" params = (self.user_id,) try: user = servicedb.execute(sql, params)[0] except psycopg2.Error as e: self.setException(e.pgerror) return False for tmp in data: if tmp == "mail" or tmp == "email": if not user['email']: self.setException("User can't receive notification via mail") return False if tmp == "twitter" and not user["twitter"]: self.setException("User cann't receive notification via twitter") return False return True
def executePost(self): """ Method for executing a POST requests that create a new SOS data quality .. note:: The POST must be in Json format with mandatory code, name and description keys >>> http://localhost/istsos/wa/istsos/services/demo/dataqualities >>> { "code": 100, "name": "raw", "description": "the format is correct" } """ if self.service == "default": raise Exception( "dataqualities operation can not be done for default service instance." ) else: servicedb = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port']) sql = "INSERT INTO %s.quality_index(id_qi, name_qi, desc_qi)" % self.service sql += " VALUES (%s, %s, %s);" par = (self.json['code'], self.json['name'], self.json['description']) servicedb.execute(sql, par) self.setMessage("")
def writeToDB(self): """ Write notification to database """ servicedb = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) if not self.__message['notification']: raise ValueError('missing notification name') # get notification_id sql = "SELECT id FROM wns.notification WHERE name=%s" params = (self.__message['notification'], ) not_id = servicedb.execute(sql, params)[0] not_id = not_id[0] print("Save " + self.__message['notification'] + " notification result") sql = "INSERT INTO wns.responses(not_id, date, notification, response)" sql += " VALUES (%s, %s, %s, %s)" params = (not_id, self.__message['date'], self.__message['notification'], json.dumps(self.__message['response'])) # write notification to db servicedb.execute(sql, params)
def executeDelete(self): """ DELETE subscription delete user from notification alert """ servicedb = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) if self.user_id and self.notification: sql = """DELETE FROM wns.registration WHERE user_id_fk = %s AND not_id_fk = %s;""" par = [self.user_id, self.notification] try: servicedb.execute(sql, par) except psycopg2.Error as e: self.setException(e.pgerror) return message = "User" + self.user_id message += ' unsubscribed from notification ' + self.notification self.setMessage(message) else: self.setException('Please define a user_id and a notification_id')
def executePut(self): """ Method for executing a PUT requests that update the status of a exception Update a exception status { "id" : 1, "newstatus" : "verified" } """ if self.service == "default": raise Exception( "Logs operation can not be done for default service instance.") if self.logs_id == None: raise Exception("Please select a valid logs id.") servicedb = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port']) if (self.json['newstatus'] is None): raise Exception("Not params.") sql = "UPDATE %s.cron_log SET" % self.service sql += " status_clo = %s WHERE id_clo = %s" par = (self.json['newstatus'], self.logs_id) servicedb.execute(sql, par) self.setMessage("Status changed")
def executeGet(self): """ GET users require user information return a list with all user """ import json servicedb = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) sql = "SELECT * FROM wns.user " params = None if self.userid: sql += " WHERE id=%s;" params = (self.userid, ) UsersList = servicedb.select(sql, params) users = [] for user in UsersList: tmp = dict(user) if user['ftp']: tmp['ftp'] = json.loads(tmp['ftp']) users.append(tmp) self.setMessage("found [" + str(len(users)) + "] users") self.setData(users)
def executeDelete(self): """DELETE notification delete notification request handler delete selected notification """ servicedb = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) sql = "DELETE FROM wns.notification WHERE id = %s RETURNING name;" par = [self.not_id] notname = None notname = servicedb.executeInTransaction(sql, par)[0][0] try: from wnslib import notificationManager as notManager if notname: notManager.delNotification(notname) servicedb.commitTransaction() self.setMessage('Notifcation ' + notname + ' deleted') else: servicedb.rollbackTransaction() self.setException("Canno't delete the notifcation") return except Exception as e: msg = "The following error occoured: " + str(e) msg += "\n\nPlease try again" servicedb.rollbackTransaction() self.setException(msg) return
def executeGet(self): """ GET request get notification request handler return list of notification """ servicedb = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) sql = "SELECT * FROM wns.notification " params = None if self.not_id: sql += " WHERE id=%s;" params = (self.not_id, ) NotificationList = servicedb.select(sql, params) Notification = [] for notif in NotificationList: Notification.append(dict(notif)) self.setData(Notification) self.setMessage("fount [" + str(len(Notification)) + "] notifications")
def executeGet(self): if self.servicename == "default": raise Exception("getlist operation can not be done for default service instance.") else: data = [] servicedb = databaseManager.PgDB(self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port'] ) proceduresList = utils.getProcedureNamesList(servicedb,self.servicename,observationType='virtual') for proc in proceduresList: elem = {} elem.update(proc) #elem["name"] = proc["name"] ops = utils.getObservedPropertiesFromProcedure(servicedb,self.servicename,proc["name"]) if ops != None: elem["observedproperties"] = [ {"name" : op["name"], "uom" : op["uom"] } for op in ops ] else: elem["observedproperties"] = [] offs = utils.getOfferingsFromProcedure(servicedb,self.servicename,proc["name"]) if offs != None: elem["offerings"] = [ off["name"] for off in offs ] else: elem["offerings"] = [] data.append(elem) self.setData(data) self.setMessage("Procedures of service <%s> successfully retrived" % self.servicename)
def validatedb(user, password, dbname, host, port=5432, service=None): """ Validate a service db connection parameters @param user: user name used to authenticate @param password: password used to authenticate @param dbname: the database name (only in dsn string) @param host: database host address @param port: onnection port number (optional - defaults to 5432) @param service: service name that correspond with associated database schema (optional) @return: L{True} if connection could be estabished and the schema found (only if schema is provided) """ from walib import databaseManager test_conn = databaseManager.PgDB(user, password, dbname, host, port) if not service is None or service == "default": sql = "SELECT count(*) from pg_namespace WHERE nspname = %s" par = (service, ) res = test_conn.select(sql, par) if len(res) == 1: pass else: raise Exception("CONNECTION ERROR: wrong schema %s" % service)
def __logToDB(self, old_json): # read old values from walib import databaseManager import datetime import copy # get new data new_json = copy.deepcopy(self.json) if old_json == new_json: return result = self.__check_changes(old_json, new_json) now = datetime.datetime.now() servicedb = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port']) for res in result: count = 0 sql = "INSERT INTO %s.tran_log(transaction_time_trl, " % self.service sql += "operation_trl, procedure_trl, begin_trl, end_trl, count) " sql += "VALUES (%s, %s, %s, %s, %s, %s)" params = (now, "RatingCurve", self.procedurename, res['from'],) params += (res['to'], count) servicedb.execute(sql, params)
def executePost(self): """ POST notification post notifation request handler create new notifcation, simple or complex """ servicedb = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) name = self.json["name"] description = self.json["description"] interval = self.json["interval"] not_id = None store = self.json.get("store", False) sql = """INSERT INTO wns.notification (name, description, interval, store) VALUES (%s,%s, %s, %s) RETURNING id;""" par = [name, description, interval, store] try: not_id = servicedb.executeInTransaction(sql, par)[0][0] except psycopg2.Error as e: self.setException(e.pgerror) servicedb.rollbackTransaction() return if not not_id: self.setException('Exception while creating a new notification') return try: from wnslib import notificationManager as notManager if "params" in self.json.keys(): params = self.json["params"] condition = self.json["condition"] service = self.json["service"] period = self.json.get("period", None) notManager.createSimpleNotification(name, service, params, condition, interval, period, store) else: print "Notification" funcFile = self.json["function"] msg = notManager.addNotification(name, funcFile, interval, store) if msg: self.setException(msg) servicedb.rollbackTransaction() return except Exception, e: msg = "The following error occoured: " + str(e) msg += "\n\nPlease try again" self.setException(msg) servicedb.rollbackTransaction() return
def executeGet(self): """ GET request """ params = self.wnsEnviron['parameters'] servicedb = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port']) sql = "SELECT * FROM wns.responses WHERE not_id=%s " par = (self.not_id,) limit = "LIMIT 1" if params is not None: keyList = list(params.keys()) if 'stime' in keyList: sql += " AND (date > %s::timestamptz)" par += (params['stime'][0],) if 'etime' in keyList: sql += " AND (date < %s::timestamptz)" par += (params['etime'][0],) if 'limit' in keyList: if params['limit'][0] != "all": limit = "LIMIT %s" % params['limit'][0] else: limit = "" sql += " ORDER BY date DESC " sql += limit try: result = servicedb.execute(sql, par) except psycopg2.Error as e: self.setException(e.pgerror) return response = [] for res in result: response.append( { "id": res['id'], "notification": res['notification'], "date": res['date'].strftime('%Y-%m-%dT%H:%M:%S%z'), "response": json.loads(res['response']) } ) self.setData(response) self.setMessage("Found [" + str(len(response)) + "] element")
def executePut(self): """ Method for executing a PUT requests that rename a SOS service @note: This method renames: 1. create a new service folder, 2. copy content from old to new service configuration file 3. rename the databse schema 4. delete old service files The POST must be in Json format with mandatory service key >>> { "service" : "service_name" } """ if self.service == "default": raise Exception("offerings PUT operation can not be done for 'default' service instance.") try: self.offering = self.pathinfo[(self.pathinfo.index("offerings")+1)] if self.offering == 'temporary' and self.offering != self.json["name"]: raise Exception("'temporary' offering name cannot be updated") servicedb = databaseManager.PgDB( self.serviceconf.connection["user"], self.serviceconf.connection["password"], self.serviceconf.connection["dbname"], self.serviceconf.connection["host"], self.serviceconf.connection["port"] ) sql = "UPDATE %s.offerings" % self.service sql += " SET name_off = %s, desc_off = %s, expiration_off = %s , active_off = %s " sql += " WHERE name_off = %s" name = self.json["name"] desc = self.json["description"] try: exp = isodate.parse_datetime(self.json["expiration"]) except: exp = None try: act = True if ("active" in self.json and self.json["active"]=='on') else False except: act = False pars = (name,desc,exp,act,self.offering) servicedb.execute(sql,pars) self.setMessage("offering successfully updated") except Exception as e: self.setException("Error in updating an offering: %s" % e)
def executePost(self): """ POST registrations subscribe a user to a notification Return confirm message if subscribed """ servicedb = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) not_list = self.json['data'] if len(not_list) == 0: self.setException('Please add a notification type') return if not self.check_data(not_list): return if self.user_id and self.notification: # Check notification type for noti in not_list: if noti == "mail" or noti == "email": if not self.serviceconf.mail['usermail']: self.setException("Cannot subscribe via email") return if noti == "twitter": if not self.serviceconf.twitter['consumer_key']: self.setException("Cannot subscribe via twitter") return sql = """INSERT INTO wns.registration (user_id_fk, not_id_fk, not_list) VALUES (%s,%s, %s);""" par = [self.user_id, self.notification, not_list] try: servicedb.execute(sql, par) except psycopg2.Error as e: self.setException(e.pgerror) return message = 'User ' + self.user_id + ' subscribed to notification ' message += str(self.notification) self.setMessage(message) else: self.setException("Please define user and notification")
def executeDelete(self): """ Method for executing a DELETE requests that remove an existing SOS observed property """ if self.service == "default": raise Exception( "observedproperties operation can not be done for default service instance." ) elif self.observedproperty == None: raise Exception( "destination observedproperty has to be specified.") else: servicedb = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port']) sql = """ SELECT id_opr, COALESCE(procCount,0) as counter FROM %s.observed_properties LEFT JOIN ( SELECT id_opr_fk, count(id_opr_fk) as procCount FROM %s.proc_obs GROUP BY id_opr_fk ) AS b ON id_opr = id_opr_fk """ % ((self.service, ) * 2) sql += "WHERE def_opr = %s" par = (self.observedproperty, ) row = servicedb.select(sql, par) if not row: raise Exception("'%s' observed property does not exist." % self.observedproperty) oid = row[0]["id_opr"] counter = row[0]["counter"] if counter > 0: if counter == 1: raise Exception( "There is %s procedure connected with '%s' observed property " % (counter, self.observedproperty)) else: raise Exception( "There are %s procedures connected with '%s' observed property " % (counter, self.observedproperty)) sql = "DELETE FROM %s.observed_properties " % self.service sql += " WHERE id_opr = %s;" par = (oid, ) servicedb.execute(sql, par) self.setMessage("Observed property is deleted")
def executePost(self,db=True): """ Method for executing a POST requests that initialize a new SOS service @note: This method creates a new istSOS offering The POST must be in Json format with mandatory offering key: >>> { "offering" : "meteorology", "description" : "meteo information" "expiration" : "2012-12-30T12:00" "active" : "sos_db" } """ servicedb = databaseManager.PgDB( self.serviceconf.connection["user"], self.serviceconf.connection["password"], self.serviceconf.connection["dbname"], self.serviceconf.connection["host"], self.serviceconf.connection["port"] ) #insert new offering in db try: sql = "INSERT INTO %s.offerings" % self.service sql += " (name_off,desc_off,expiration_off,active_off)" sql += " VALUES (%s, %s, %s, %s)" name = self.json["name"] try: desc = self.json["description"] except: desc = None try: exp = isodate.parse_datetime(self.json["expiration"]) except: exp = None try: act = self.json["active"] except: act = False pars = (name,desc,exp,act) servicedb.execute(sql,pars) self.setMessage("new offering successfully added") except Exception as e: self.setException("Error in adding new offering: %s" % e)
def executePut(self): """ Method for executing a POST requests that create a new SOS observed property """ if self.service == "default": raise Exception( "observedproperties operation can not be done for default service instance." ) elif self.observedproperty == None: raise Exception("destination observedproperty is not specified.") else: import json from walib import utils as ut servicedb = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port']) sql = "SELECT id_opr FROM %s.observed_properties " % self.service sql += "WHERE def_opr = %s" par = (self.observedproperty, ) row = servicedb.select(sql, par) if not row: raise Exception( "Original observed property '%s' does not exist." % self.observedproperty) oid = row[0]["id_opr"] sql = "UPDATE %s.observed_properties " % self.service if not self.json['constraint'] or self.json['constraint'] == {}: sql += "SET name_opr = %s, def_opr = %s, desc_opr=%s, constr_opr=NULL " par = (self.json['name'], self.json['definition'], self.json['description'], oid) else: try: ut.validateJsonConstraint(self.json['constraint']) except Exception as ex: raise Exception( "Constraint for observed property '%s' is not valid: %s" % (self.observedproperty, ex)) sql += "SET name_opr = %s, def_opr = %s, desc_opr=%s, constr_opr=%s " par = (self.json['name'], self.json['definition'], self.json['description'], json.dumps(self.json['constraint']), oid) sql += "WHERE id_opr = %s" servicedb.execute(sql, par) self.setMessage("Update successfull")
def executeGet(self): if self.servicename == "default": raise Exception("getlist operation can not be done for " "default service instance.") else: data = [] servicedb = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port']) if self.waEnviron['parameters'] and ( 'tzoffset' in self.waEnviron['parameters']): tzoffset = self.waEnviron['parameters']['tzoffset'][0] servicedb.setTimeTZ(tzoffset) proceduresList = utils.getProcedureNamesList( servicedb, self.servicename) for proc in proceduresList: elem = {} elem.update(proc) ops = utils.getObservedPropertiesFromProcedure( servicedb, self.servicename, proc["name"]) if ops is not None: elem["observedproperties"] = [{ "name": op["name"], "uom": op["uom"] } for op in ops] else: elem["observedproperties"] = [] offs = utils.getOfferingsFromProcedure(servicedb, self.servicename, proc["name"]) if offs is not None: elem["offerings"] = [off["name"] for off in offs] else: elem["offerings"] = [] data.append(elem) self.setData(data) self.setMessage( "Procedures of service <%s> successfully retrived" % (self.servicename))
def executeGet(self): """GET registration get user subscription to notifications Returns: A dict containing all user subscription to notification. Example: { user_id:[ { "not_id": 1, "not_list": [ "email", "twitter" ], "description": "notification description", "name": "notification name" } ] } """ servicedb = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) sql = "SELECT * FROM wns.registration r, wns.notification n " sql += "WHERE r.user_id_fk = %s AND r.not_id_fk=n.id" params = (self.user_id, ) if self.notification: sql += " AND r.not_id_fk= %s " params += (self.notification, ) NotificationList = servicedb.select(sql, params) RegistrationsList = [] for el in NotificationList: el = dict(el) del el['not_id_fk'] del el['user_id_fk'] RegistrationsList.append(dict(el)) self.setMessage("Notification subscription for user: " + self.user_id) self.setData(RegistrationsList)
def executePut(self): """ Method for executing a PUT requests that updates an existing SOS data quality .. note:: The PUT must be in Json format with mandatory name and description keys >>> http://localhost/istsos/wa/istsos/services/demo/dataqualities/1000 >>> { "name": "raw", "description": "the format is correct" } """ if self.service == "default": raise Exception( "observedproperties operation can not be done for default service instance." ) elif self.dataquality == None: raise Exception("destination quality index code is not specified.") else: #--check if quality index exists servicedb = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port']) sql = "SELECT id_qi FROM %s.quality_index" % self.service sql += " WHERE id_qi = %s" par = (self.dataquality, ) row = servicedb.select(sql, par) if not row: raise Exception("Quality index '%s' does not exist." % self.dataquality) oid = row[0]["id_qi"] sql = "UPDATE %s.quality_index " % self.service sql += "SET name_qi = %s, desc_qi=%s" sql += " WHERE id_qi = %s" par = (self.json['name'], self.json['description'], oid) servicedb.execute(sql, par) #--update quality index code if provided if self.json['code'] and not self.json['code'] == self.dataquality: sql = "UPDATE %s.quality_index" % self.service sql += " SET id_qi = %s" sql += " WHERE id_qi = %s" par = (self.json['code'], self.dataquality) servicedb.execute(sql, par) self.setMessage("Quality Index successfully updated")
def executeGet(self): if self.service == "default": raise Exception("offerings operation can not be done for 'default' service instance.") else: self.setData(utils.getOfferingNamesList( databaseManager.PgDB(self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port'] ),self.service)) if self.response['total']>0: self.setMessage("Offerings names of service \"%s\" successfully retrived" % self.service) else: self.setMessage("There aren't Offerings for service \"%s\"" % self.service)
def executePut(self): """ PUT user Update a existing user """ import json as jsonlib if not self.userid: self.setException("No user id defined") return servicedb = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) json_data = self.json sql = "UPDATE wns.user SET " params = () if "ftp" in json_data.keys(): sql += "" for key in json_data.keys(): if key != "ftp": sql += " " + key + "=%s," params += (json_data[key], ) else: sql += " " + key + "=%s," params += (jsonlib.dumps(json_data[key]), ) sql = sql[:-1] sql += " WHERE id=%s;" params += (self.userid, ) try: servicedb.execute(sql, params) except psycopg2.Error as e: self.setException(e.pgerror) return self.setMessage("Updated user info")
def executeGet(self): """ execute GET request create notification.aps file create database schema """ services_dir = config.wns_path aps_dir = path.join(services_dir, "notifications.aps") #print "Service path: ", services_dir import datetime now = datetime.datetime.now() startDate = now.strftime('%Y-%m-%d %H:%M:%S') aps = open(aps_dir, 'w') aps.write("### CREATED ON " + str(startDate) + " ###") aps.close() if not self.serviceconf.connectionWns['dbname']: #Copy connection params to connectionWns self.serviceconf.put('connectionWns', 'dbname', self.serviceconf.connection['dbname']) self.serviceconf.put('connectionWns', 'host', self.serviceconf.connection['host']) self.serviceconf.put('connectionWns', 'user', self.serviceconf.connection['user']) self.serviceconf.put('connectionWns', 'password', self.serviceconf.connection['password']) self.serviceconf.put('connectionWns', 'port', self.serviceconf.connection['port']) self.serviceconf.save() dbConnection = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) dbConnection.execute(sqlschema.wnsschema) msg = "Notification.aps file created in %s " % services_dir msg += "\nDatabase schema WNS correctly created" self.setMessage(msg)
def executePut(self): """ PUT registration Update user subscription to notification """ servicedb = databaseManager.PgDB( self.serviceconf.connectionWns['user'], self.serviceconf.connectionWns['password'], self.serviceconf.connectionWns['dbname'], self.serviceconf.connectionWns['host'], self.serviceconf.connectionWns['port']) not_list = self.json['data'] if len(not_list) == 0: self.setException('Please add a notification type') return if not self.check_data(not_list): return if self.user_id and self.notification: # Check notification type for noti in not_list: if noti == "mail" or noti == "email": if not self.serviceconf.mail['usermail']: self.setException("Cannot subscribe via email") return if noti == "twitter": if not self.serviceconf.twitter['consumer_key']: self.setException("Cannot subscribe via twitter") sql = """UPDATE wns.registration SET not_list=%s WHERE user_id_fk=%s AND not_id_fk=%s;""" par = [not_list, self.user_id, self.notification] try: servicedb.execute(sql, par) except psycopg2.Error as e: self.setException(e.pgerror) return self.setMessage('Update subscription') else: self.setException("Please defien user and notification")
def executeDelete(self): """ Method for executing a DELETE requests that remove an existing SOS data quality .. note:: The DELETE must include code in the URL >>> http://localhost/istsos/wa/istsos/services/demo/dataqualities/1000 """ if self.service == "default": raise Exception( "dataqualities operation can not be done for default service instance." ) elif self.dataquality == None: raise Exception( "destination quality index code has to be specified.") else: #--check if quality index exists servicedb = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port']) sql = "SELECT id_qi FROM %s.quality_index" % (self.service) sql += " WHERE id_qi = %s" par = (self.dataquality, ) row = servicedb.select(sql, par) if not row: raise Exception("'%s' Quality index code does not exist." % self.dataquality) oid = row[0]["id_qi"] #--check if quality index is in use sql = "SELECT id_msr FROM %s.measures" % (self.service) sql += " WHERE id_qi_fk=%s LIMIT 1" use = servicedb.select(sql, (oid, )) if use: raise Exception("'%s' Quality index code is in use." % self.dataquality) #--erase quality index code sql = "DELETE FROM %s.quality_index " % self.service sql += " WHERE id_qi = %s;" par = (oid, ) servicedb.execute(sql, par) self.setMessage("Quality index successfully deleted")
def __init__(self, conf=None): """ Initialize the MQTTMediator class conf """ self.lock = threading.Lock() self.conf = conf self.broker = {} self.services = {} defaultCfg = os.path.join(config.services_path, "default.cfg") instances = utils.getServiceList(config.services_path, listonly=False) for instance in instances: sc = configManager.waServiceConfig(defaultCfg, instance['path']) conn = databaseManager.PgDB(sc.connection["user"], sc.connection["password"], sc.connection["dbname"], sc.connection["host"], sc.connection["port"]) self.services[instance['service']] = {"config": sc, "conn": conn} # Get mqtt configurations rows = conn.select(""" SELECT id_prc, mqtt_prc, name_prc FROM %s.procedures""" % (instance['service'])) for row in rows: if row[1] is not None and row[1] != '': mqttConf = json.loads(row[1]) if 'broker_port' in mqttConf and ( mqttConf['broker_port'] is not None and mqttConf['broker_port'] != ''): broker_url = "%s:%s" % (mqttConf['broker_url'], mqttConf['broker_port']) else: broker_url = "%s:1883" % (mqttConf['broker_url']) topic = mqttConf['broker_topic'] if broker_url not in self.broker: self.broker[broker_url] = {} self.broker[broker_url][topic] = { "id": row[0], "name": row[2], "instance": instance['service'] }
def executePost(self): """ Method for executing a POST requests that create a new SOS observed property """ if self.service == "default": raise Exception("uoms operation can not be done for default service instance.") else: servicedb = databaseManager.PgDB( self.serviceconf.connection['user'], self.serviceconf.connection['password'], self.serviceconf.connection['dbname'], self.serviceconf.connection['host'], self.serviceconf.connection['port']) sql = "INSERT INTO %s.uoms(name_uom, desc_uom)" % self.service sql += " VALUES (%s, %s);" par = (self.json['name'],self.json['description']) servicedb.execute(sql,par) self.setMessage("")