def discover_functions(self,prod_cursor,make_observable=True): self.cursor.execute("SELECT pro_oid,func_name,id FROM function_name WHERE {0}={1} AND alive".format(self.sub_fk,self.id)) local_funcs=self.cursor.fetchall() # print local_funcs try: prod_cursor.execute("""SELECT p.oid AS pro_oid,p.proname AS funcname,p.proretset,t.typname,l.lanname FROM pg_proc p LEFT JOIN pg_namespace n ON n.oid = p.pronamespace JOIN pg_type t ON p.prorettype=t.oid JOIN pg_language l ON p.prolang=l.oid WHERE (p.prolang <> (12)::oid) AND n.oid={0}""".format(self.db_fields['obj_oid'])) except Exception as e: logger.error("Cannot execute function discovery query: {0}".format(e)) # prod_cursor.close() return prod_funcs=prod_cursor.fetchall() for l_func in local_funcs: for p_func in prod_funcs: if l_func[0]==p_func[0] and l_func[1]==p_func[1]: break else: logger.info("Retired function {0} in schema {1}".format(l_func[1],self.db_fields['sch_name'])) old_func=FunctionName(l_func[2]) old_func.retire() for p_func in prod_funcs: for l_func in local_funcs: if p_func[0]==l_func[0] and p_func[1]==l_func[1]: break else: logger.info("Created new function: {0} in schema {1}".format(p_func[1],self.db_fields['sch_name'])) new_func=FunctionName() new_func.set_fields(sn_id=self.id,pro_oid=p_func[0],func_name=p_func[1],proretset=p_func[2],prorettype=p_func[3],prolang=p_func[4],observable=make_observable) new_func.create() new_func.truncate()
def discover_functions(self): p_cur=self._get_p_cursor() if not p_cur: logger.error("Returning from SN.discover_functions no p_cur obtained") return False try: p_cur.execute("""SELECT p.oid AS pro_oid,p.proname AS funcname,p.proretset,t.typname,l.lanname FROM pg_proc p LEFT JOIN pg_namespace n ON n.oid = p.pronamespace JOIN pg_type t ON p.prorettype=t.oid JOIN pg_language l ON p.prolang=l.oid WHERE (p.prolang <> (12)::oid) AND n.oid={0}""".format(self.db_fields['obj_oid'])) except Exception as e: logger.error("Cannot execute functions discovery query on Prod: {0}".format(e.pgerror)) p_cur.close() return False prod_funcs=p_cur.fetchall() p_cur.close() cur=self._get_cursor() if not cur: logger.error("Returning from SN.discover_functions no cur obtained") return False try: cur.execute("SELECT pro_oid,func_name,id FROM function_name WHERE sn_id={0} AND alive".format(self.id)) except Exception as e: logger.error("Cannot execute functions discovery query on Local: {0}".format(e.pgerror)) cur.close() return False local_funcs=cur.fetchall() cur.close() for l_func in local_funcs: for p_func in prod_funcs: if l_func[0]==p_func[0] and l_func[1]==p_func[1]: break else: old_func=FunctionName(self.db_conn,self.prod_conn,l_func[2]) if not old_func.retire(): logger.error("SN.discover_functions() Cannot retire old") else: logger.info("Retired function {0} in schema {1}".format(l_func[1],self.db_fields['sch_name'])) for p_func in prod_funcs: for l_func in local_funcs: if p_func[0]==l_func[0] and p_func[1]==l_func[1]: break else: new_func=FunctionName(self.db_conn,self.prod_conn) new_func.set_fields(sn_id=self.id,pro_oid=p_func[0],func_name=p_func[1],proretset=p_func[2],prorettype=p_func[3],prolang=p_func[4]) if not new_func._create(): logger.error("SN.discover_functions() Cannot create new") else: logger.info("Created new function: {0} in schema {1}".format(p_func[1],self.db_fields['sch_name'])) return True