def get_reverse_engineered_sql(conn,
schema,
table,
tid,
trid,
datlastsysoid,
template_path=None):
"""
This function will return reverse engineered sql for trigger(s).
:param conn: Connection Object
:param schema: Schema
:param table: Table
:param tid: Table ID
:param trid: Trigger ID
:param datlastsysoid:
:param template_path: Optional template path
:return:
"""
SQL = render_template("/".join([template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=datlastsysoid)
status, res = conn.execute_dict(SQL)
if not status:
raise Exception(res)
if len(res['rows']) == 0:
raise ObjectGone(
_('Could not find the compound trigger in the table.'))
data = dict(res['rows'][0])
# Adding parent into data dict, will be using it while creating sql
data['schema'] = schema
data['table'] = table
if len(data['tgattr']) >= 1:
columns = ', '.join(data['tgattr'].split(' '))
data['columns'] = get_column_details(conn, tid, columns)
data = trigger_definition(data)
SQL, name = get_sql(conn, data, tid, None, datlastsysoid)
sql_header = u"-- Compound Trigger: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join([template_path, 'delete.sql']),
data=data,
conn=conn)
SQL = sql_header + '\n\n' + SQL.strip('\n')
# If trigger is disabled then add sql code for the same
if data['is_enable_trigger'] != 'O':
SQL += '\n\n'
SQL += render_template("/".join(
[template_path, 'enable_disable_trigger.sql']),
data=data,
conn=conn)
return SQL
def _fetch_properties(self, tid, trid):
SQL = render_template("/".join(
[self.template_path, self._PROPERTIES_SQL]),
tid=tid,
trid=trid,
datlastsysoid=self.datlastsysoid)
status, res = self.conn.execute_dict(SQL)
if not status:
return status, res
if len(res['rows']) == 0:
return True, res
# Making copy of output for future use
data = dict(res['rows'][0])
if len(data['tgattr']) >= 1:
columns = ', '.join(data['tgattr'].split(' '))
data['columns'] = compound_trigger_utils.get_column_details(
self.conn, tid, columns)
data = trigger_definition(data)
return True, data
def _fetch_properties(self, tid, trid):
"""
This function is used to fetch the properties of the specified object
:param tid:
:param trid:
:return:
"""
SQL = render_template("/".join([self.template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=self.datlastsysoid)
status, res = self.conn.execute_dict(SQL)
if not status:
return False, internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return False, gone(
gettext("""Could not find the trigger in the table."""))
# Making copy of output for future use
data = dict(res['rows'][0])
data = trigger_utils.get_trigger_function_and_columns(
self.conn, data, tid, self.blueprint.show_system_objects)
data = trigger_definition(data)
return True, data
def _fetch_properties(self, tid, trid):
"""
This function is used to fetch the properties of the specified object
:param tid:
:param trid:
:return:
"""
SQL = render_template("/".join(
[self.template_path, self._PROPERTIES_SQL]),
tid=tid,
trid=trid,
datlastsysoid=self._DATABASE_LAST_SYSTEM_OID)
status, res = self.conn.execute_dict(SQL)
if not status:
return False, internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return False, gone(self.not_found_error_msg())
# Making copy of output for future use
data = dict(res['rows'][0])
data = trigger_utils.get_trigger_function_and_columns(
self.conn, data, tid, self.blueprint.show_system_objects)
data = trigger_definition(data)
return True, data
def get_reverse_engineered_sql(conn, **kwargs):
"""
This function will return reverse engineered sql for specified trigger.
:param conn: Connection Object
:param kwargs:
:return:
"""
schema = kwargs.get('schema')
table = kwargs.get('table')
tid = kwargs.get('tid')
trid = kwargs.get('trid')
datlastsysoid = kwargs.get('datlastsysoid')
show_system_objects = kwargs.get('show_system_objects')
template_path = kwargs.get('template_path', None)
with_header = kwargs.get('with_header', True)
SQL = render_template("/".join([template_path, 'properties.sql']),
tid=tid, trid=trid,
datlastsysoid=datlastsysoid)
status, res = conn.execute_dict(SQL)
if not status:
raise ExecuteError(res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the trigger in the table.'))
data = dict(res['rows'][0])
# Adding parent into data dict, will be using it while creating sql
data['schema'] = schema
data['table'] = table
data = \
get_trigger_function_and_columns(conn, data, tid, show_system_objects)
data = trigger_definition(data)
SQL, name = get_sql(conn, data=data, tid=tid, trid=None,
datlastsysoid=datlastsysoid,
show_system_objects=show_system_objects)
if with_header:
sql_header = "-- Trigger: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join([template_path, 'delete.sql']),
data=data, conn=conn)
SQL = sql_header + '\n\n' + SQL.strip('\n')
else:
SQL = SQL.strip('\n')
# If trigger is disabled then add sql code for the same
if data['is_enable_trigger'] != 'O':
SQL += '\n\n'
SQL += render_template("/".join([template_path,
'enable_disable_trigger.sql']),
data=data, conn=conn)
return SQL
def get_sql(conn,
data,
tid,
trid,
datlastsysoid,
show_system_objects,
template_path=None):
"""
This function will generate sql from model data.
:param conn: Connection Object
:param data: Data
:param tid: Table ID
:param trid: Trigger ID
:param datlastsysoid:
:param show_system_objects: Show System Object value True or False
:param template_path: Optional template path
:return:
"""
name = data['name'] if 'name' in data else None
if trid is not None:
sql = render_template("/".join([template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=datlastsysoid)
status, res = conn.execute_dict(sql)
if not status:
raise Exception(res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the trigger in the table.'))
old_data = dict(res['rows'][0])
# If name is not present in data then
# we will fetch it from old data, we also need schema & table name
if 'name' not in data:
name = data['name'] = old_data['name']
old_data = get_trigger_function_and_columns(conn, old_data, tid,
show_system_objects)
old_data = trigger_definition(old_data)
SQL = render_template("/".join([template_path, 'update.sql']),
data=data,
o_data=old_data,
conn=conn)
else:
required_args = {'name': 'Name', 'tfunction': 'Trigger function'}
for arg in required_args:
if arg not in data:
return _('-- definition incomplete')
# If the request for new object which do not have did
SQL = render_template("/".join([template_path, 'create.sql']),
data=data,
conn=conn)
return SQL, name
def _fetch_properties(self, tid, trid):
SQL = render_template("/".join([self.template_path,
'properties.sql']),
tid=tid, trid=trid,
datlastsysoid=self.datlastsysoid)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(gettext(
"""Could not find the compound trigger in the table."""))
# Making copy of output for future use
data = dict(res['rows'][0])
if len(data['tgattr']) >= 1:
columns = ', '.join(data['tgattr'].split(' '))
data['columns'] = compound_trigger_utils.get_column_details(
self.conn, tid, columns)
data = trigger_definition(data)
return True, data
def get_sql_from_diff(self, **kwargs):
"""
This function is used to get the DDL/DML statements.
:param kwargs
:return:
"""
gid = kwargs.get('gid')
sid = kwargs.get('sid')
did = kwargs.get('did')
scid = kwargs.get('scid')
tid = kwargs.get('tid')
oid = kwargs.get('oid')
data = kwargs.get('data', None)
drop_sql = kwargs.get('drop_sql', False)
target_schema = kwargs.get('target_schema', None)
if data:
sql, name = compound_trigger_utils.get_sql(self.conn, data, tid,
oid, self.datlastsysoid)
if not isinstance(sql, str):
return sql
sql = sql.strip('\n').strip(' ')
else:
if drop_sql:
sql = self.delete(gid=gid,
sid=sid,
did=did,
scid=scid,
tid=tid,
trid=oid,
only_sql=True)
else:
sql = render_template("/".join(
[self.template_path, self._PROPERTIES_SQL]),
tid=tid,
trid=oid,
datlastsysoid=self.datlastsysoid)
status, res = self.conn.execute_dict(sql)
if not status:
return internal_server_error(errormsg=res)
elif len(res['rows']) == 0:
return gone(self.not_found_error_msg())
data = dict(res['rows'][0])
# Adding parent into data dict,
# will be using it while creating sql
data['schema'] = self.schema
data['table'] = self.table
if len(data['tgattr']) >= 1:
columns = ', '.join(data['tgattr'].split(' '))
data['columns'] = self._column_details(tid, columns)
data = trigger_definition(data)
sql = self._check_and_add_compound_trigger(
tid, data, target_schema)
return sql
def get_sql_from_diff(self, gid, sid, did, scid, tid, oid,
data=None, diff_schema=None, drop_sql=False):
if data:
sql, name = compound_trigger_utils.get_sql(self.conn,
data,
tid, oid,
self.datlastsysoid)
if not isinstance(sql, (str, unicode)):
return sql
sql = sql.strip('\n').strip(' ')
else:
if drop_sql:
SQL = self.delete(gid=gid, sid=sid, did=did,
scid=scid, tid=tid,
trid=oid, only_sql=True)
else:
SQL = render_template("/".join([self.template_path,
'properties.sql']),
tid=tid, trid=oid,
datlastsysoid=self.datlastsysoid)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(gettext("Could not find the compound "
"trigger in the table."))
data = dict(res['rows'][0])
# Adding parent into data dict,
# will be using it while creating sql
data['schema'] = self.schema
data['table'] = self.table
if len(data['tgattr']) >= 1:
columns = ', '.join(data['tgattr'].split(' '))
data['columns'] = self._column_details(tid, columns)
data = trigger_definition(data)
if diff_schema:
data['schema'] = diff_schema
SQL, name = compound_trigger_utils.get_sql(self.conn,
data,
tid,
None,
self.datlastsysoid)
# If compound trigger is disbaled then add sql
# code for the same
if not data['is_enable_trigger']:
SQL += '\n\n'
SQL += render_template("/".join([
self.template_path,
'enable_disable_trigger.sql']),
data=data, conn=self.conn)
return SQL
def properties(self, gid, sid, did, scid, tid, trid):
"""
This function will show the properties of the selected
compound trigger node.
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
scid: Schema ID
tid: Table ID
trid: Trigger ID
Returns:
JSON of selected compound trigger node
"""
SQL = render_template("/".join([self.template_path,
'properties.sql']),
tid=tid, trid=trid,
datlastsysoid=self.datlastsysoid)
status, res = self.conn.execute_dict(SQL)
if not status:
return internal_server_error(errormsg=res)
if len(res['rows']) == 0:
return gone(gettext(
"""Could not find the compound trigger in the table."""))
# Making copy of output for future use
data = dict(res['rows'][0])
if len(data['tgattr']) >= 1:
columns = ', '.join(data['tgattr'].split(' '))
data['columns'] = compound_trigger_utils.get_column_details(
self.conn, tid, columns)
data = trigger_definition(data)
return ajax_response(
response=data,
status=200
)
def get_reverse_engineered_sql(conn,
schema,
table,
tid,
trid,
datlastsysoid,
show_system_objects,
template_path=None,
with_header=True):
"""
This function will return reverse engineered sql for specified trigger.
:param conn: Connection Object
:param schema: Schema
:param table: Table
:param tid: Table ID
:param trid: Trigger ID
:param datlastsysoid:
:param show_system_objects: Show System Object value True or False
:param template_path: Optional template path
:param with_header: Optional parameter to decide whether the SQL will be
returned with header or not
:return:
"""
SQL = render_template("/".join([template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=datlastsysoid)
status, res = conn.execute_dict(SQL)
if not status:
raise Exception(res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the trigger in the table.'))
data = dict(res['rows'][0])
# Adding parent into data dict, will be using it while creating sql
data['schema'] = schema
data['table'] = table
data = \
get_trigger_function_and_columns(conn, data, tid, show_system_objects)
data = trigger_definition(data)
SQL, name = get_sql(conn, data, tid, None, datlastsysoid,
show_system_objects)
if with_header:
sql_header = u"-- Trigger: {0}\n\n-- ".format(data['name'])
sql_header += render_template("/".join([template_path, 'delete.sql']),
data=data,
conn=conn)
SQL = sql_header + '\n\n' + SQL.strip('\n')
else:
SQL = SQL.strip('\n')
# If trigger is disabled then add sql code for the same
if data['is_enable_trigger'] != 'O':
SQL += '\n\n'
SQL += render_template("/".join(
[template_path, 'enable_disable_trigger.sql']),
data=data,
conn=conn)
return SQL
def get_sql(conn,
data,
tid,
trid,
datlastsysoid,
show_system_objects,
is_schema_diff=False,
template_path=None):
"""
This function will generate sql from model data.
:param conn: Connection Object
:param data: Data
:param tid: Table ID
:param trid: Trigger ID
:param datlastsysoid:
:param show_system_objects: Show System Object value True or False
:param is_schema_diff:
:param template_path: Optional template path
:return:
"""
name = data['name'] if 'name' in data else None
if trid is not None:
sql = render_template("/".join([template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=datlastsysoid)
status, res = conn.execute_dict(sql)
if not status:
raise Exception(res)
if len(res['rows']) == 0:
raise ObjectGone(_('Could not find the trigger in the table.'))
old_data = dict(res['rows'][0])
# If name is not present in data then
# we will fetch it from old data, we also need schema & table name
if 'name' not in data:
name = data['name'] = old_data['name']
drop_sql = ''
if is_schema_diff:
if 'table' not in data:
data['table'] = old_data['relname']
if 'schema' not in data:
data['schema'] = old_data['nspname']
# If any of the below key is present in data then we need to drop
# trigger and re-create it.
key_array = [
'prosrc', 'is_row_trigger', 'evnt_insert', 'evnt_delete',
'evnt_update', 'fires', 'tgdeferrable', 'whenclause',
'tfunction', 'tgargs', 'columns', 'is_constraint_trigger',
'tginitdeferred'
]
is_drop_trigger = False
for key in key_array:
if key in data:
is_drop_trigger = True
break
if is_drop_trigger:
tmp_data = dict()
tmp_data['name'] = data['name']
tmp_data['nspname'] = old_data['nspname']
tmp_data['relname'] = old_data['relname']
drop_sql = render_template("/".join(
[template_path, 'delete.sql']),
data=tmp_data,
conn=conn)
old_data = get_trigger_function_and_columns(conn, old_data, tid,
show_system_objects)
old_data = trigger_definition(old_data)
SQL = render_template("/".join([template_path, 'update.sql']),
data=data,
o_data=old_data,
conn=conn)
if is_schema_diff:
SQL = drop_sql + '\n' + SQL
else:
required_args = {'name': 'Name', 'tfunction': 'Trigger function'}
for arg in required_args:
if arg not in data:
return _('-- definition incomplete')
# If the request for new object which do not have did
SQL = render_template("/".join([template_path, 'create.sql']),
data=data,
conn=conn)
return SQL, name
def get_sql(conn, **kwargs):
"""
This function will generate sql from model data.
:param conn: Connection Object
:param kwargs
:return:
"""
data = kwargs.get('data')
tid = kwargs.get('tid')
trid = kwargs.get('trid')
datlastsysoid = kwargs.get('datlastsysoid')
show_system_objects = kwargs.get('show_system_objects')
is_schema_diff = kwargs.get('is_schema_diff', False)
template_path = kwargs.get('template_path', None)
name = data['name'] if 'name' in data else None
if trid is not None:
sql = render_template("/".join([template_path, 'properties.sql']),
tid=tid,
trid=trid,
datlastsysoid=datlastsysoid)
status, res = conn.execute_dict(sql)
if not status:
raise ExecuteError(res)
elif len(res['rows']) == 0:
raise ObjectGone(_('Could not find the trigger in the table.'))
old_data = dict(res['rows'][0])
# If name is not present in data then
# we will fetch it from old data, we also need schema & table name
if 'name' not in data:
name = data['name'] = old_data['name']
drop_sql = _check_schema_diff_sql(is_schema_diff, data, old_data,
template_path, conn)
old_data = get_trigger_function_and_columns(conn, old_data, tid,
show_system_objects)
old_data = trigger_definition(old_data)
sql = render_template("/".join([template_path, 'update.sql']),
data=data,
o_data=old_data,
conn=conn)
if is_schema_diff:
sql = drop_sql + '\n' + sql
else:
required_args = {'name': 'Name', 'tfunction': 'Trigger function'}
for arg in required_args:
if arg not in data:
return _('-- definition incomplete')
# If the request for new object which do not have did
sql = render_template("/".join([template_path, 'create.sql']),
data=data,
conn=conn)
return sql, name