def discover_tables(self,prod_cursor,make_observable=True):
self.cursor.execute("SELECT obj_oid,tbl_name,id FROM table_name WHERE {0}={1} AND alive".format(self.sub_fk,self.id))
local_tbls=self.cursor.fetchall()
try:
prod_cursor.execute("""SELECT r.oid,r.relname,
CASE WHEN h.inhrelid IS NULL THEN 'f'::boolean ELSE 't'::boolean END AS has_parent
FROM pg_class r
LEFT JOIN pg_inherits h ON r.oid=h.inhrelid
WHERE r.relkind='r'
AND r.relnamespace={0}""".format(self.db_fields['obj_oid']))
except Exception as e:
logger.error("Cannot execute tables discovery query: {0},{1}".format(e.pgcode,e.pgerror))
# prod_cursor.close()
return
prod_tbls=prod_cursor.fetchall()
for l_table in local_tbls:
for p_table in prod_tbls:
if l_table[0]==p_table[0] and l_table[1]==p_table[1]:
break
else:
logger.info("Retired table {0} in schema {1}".format(l_table[1],self.db_fields['sch_name']))
old_table=TableName(l_table[2])
old_table.retire()
for p_table in prod_tbls:
for l_table in local_tbls:
if p_table[0]==l_table[0] and p_table[1]==l_table[1]:
break
else:
logger.info("Created new table: {0} in schema {1}".format(p_table[1],self.db_fields['sch_name']))
new_table=TableName()
new_table.set_fields(sn_id=self.id,tbl_name=p_table[1],obj_oid=p_table[0],has_parent=p_table[2],observable=make_observable)
new_table.create()
new_table.truncate()
def discover_tables(self):
cur=self._get_p_cursor()
if not cur:
logger.error("Returning from SN.discover_tables no p_cur obtained")
return False
try:
cur.execute("""SELECT r.oid,r.relname,
CASE WHEN h.inhrelid IS NULL THEN 'f'::boolean ELSE 't'::boolean END AS has_parent
FROM pg_class r
LEFT JOIN pg_inherits h ON r.oid=h.inhrelid
WHERE r.relkind='r'
AND r.relnamespace={0}""".format(self.db_fields['obj_oid']))
except Exception as e:
logger.error("Cannot execute tables discovery query on Prod: {0}".format(e.pgerror))
cur.close()
return False
prod_tbls=cur.fetchall()
cur.close()
cur=self._get_cursor()
if not cur:
logger.error("Returning from SN.discover_tables no cur obtained")
return False
try:
cur.execute("SELECT obj_oid,tbl_name,id FROM table_name WHERE sn_id={0} AND alive".format(self.id))
except Exception as e:
logger.error("Cannot execute tables discovery query on Local: {0}".format(e.pgerror))
cur.close()
return False
local_tbls=cur.fetchall()
cur.close()
for l_table in local_tbls:
for p_table in prod_tbls:
if l_table[0] == p_table[0] and l_table[1] == p_table[1]:
old_table=TableName(self.db_conn,self.prod_conn,l_table[2])
if not old_table.discover_indexes():
logger.error("SN.discover_tables False from tn.discover_indexes for old")
if not old_table.discover_toast():
logger.error("SN.discover_tables False from tn.discover_toast for old")
break
else:
old_table=TableName(self.db_conn,self.prod_conn,l_table[2])
if not old_table.retire():
logger.error("SN.discover_tables() cannot retire old")
else:
logger.info("Retired table {0} in schema {1}".format(l_table[1],self.db_fields['sch_name']))
for p_table in prod_tbls:
for l_table in local_tbls:
if p_table[0]==l_table[0] and p_table[1]==l_table[1]:
break
else:
logger.info("Created new table: {0} in schema {1}".format(p_table[1],self.db_fields['sch_name']))
new_table=TableName(self.db_conn,self.prod_conn)
new_table.set_fields(sn_id=self.id,tbl_name=p_table[1],obj_oid=p_table[0],has_parent=p_table[2])
if not new_table._create():
logger.error("SN.discover_tables() cannot create new")
else:
logger.info("Create new table {0}".format(p_table[1]))
if not new_table.discover_indexes():
logger.error("SN.discover_tables False from tn.discover_indexes for new")
if not new_table.discover_toast():
logger.error("SN.discover_tables False from tn.discover_toast for new")
return True