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 stat(self,in_time_id):
tbls=self.get_tables()
if not tbls:
pass
else:
for tbl in tbls:
tn=TableName(self.db_conn,self.prod_conn,tbl)
if not tn.stat(in_time_id):
logger.warning("SN.stat False from tn.stat()")
if not tn.va_stat(in_time_id):
logger.warning("SN.stat False from tn.va_stat()")
funcs=self.get_functions()
if not funcs:
pass
else:
for fnc in funcs:
fn=FunctionName(self.db_conn,self.prod_conn,fnc)
if not fn.stat(in_time_id):
logger.warning("SN.stat False from fn.stat()")
return True
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
for sn_id in dn.get_dependants(obs=True): sn=SchemaName(sn_id) sn.discover_tables(db_cursor) sn.discover_functions(db_cursor) for tbl_id in sn.get_tables(): tn=TableName(tbl_id) tn.stat(lt.id,db_cursor) tn.va_stat(lt.id,db_cursor) tn.discover_indexes(db_cursor) for ind_id in tn.get_dependants(): ind=IndexName(ind_id) ind.stat(lt.id,db_cursor) tn.discover_toast(db_cursor) toast_id=tn.get_toast_id() if toast_id: ttn=TableToastName(toast_id) ttn.stat(lt.id,db_cursor) ttn.discover_index(db_cursor) tin=IndexToastName(ttn.get_tindex_id()) tin.stat(lt.id,db_cursor) if hc.get_track_function(): for fnc_id in sn.get_functions(): func=FunctionName(fnc_id) func.stat(lt.id,db_cursor) if not db_cursor.closed: db_cursor.close() work_cursor.close() database.db_conn.close()