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 _check_and_add_compound_trigger(self, tid, data, target_schema):
"""
This get compound trigger and check for disable.
:param tid: Table Id.
:param data: Data.
:param target_schema: schema diff check.
"""
if target_schema:
data['schema'] = target_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 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 msql(self, gid, sid, did, scid, tid, trid=None):
"""
This function will generates modified sql for compound trigger object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
trid: Trigger ID (When working with existing compound trigger)
"""
data = dict()
for k, v in request.args.items():
try:
# comments should be taken as is because if user enters a
# json comment it is parsed by loads which should not happen
if k in ('description',):
data[k] = v
else:
data[k] = json.loads(v, encoding='utf-8')
except ValueError:
data[k] = v
# Adding parent into data dict, will be using it while creating sql
data['schema'] = self.schema
data['table'] = self.table
try:
sql, name = compound_trigger_utils.get_sql(
self.conn, data, tid, trid, self.datlastsysoid)
if not isinstance(sql, str):
return sql
sql = sql.strip('\n').strip(' ')
if sql == '':
sql = "--modified SQL"
return make_json_response(
data=sql,
status=200
)
except Exception as e:
return internal_server_error(errormsg=str(e))
def update(self, gid, sid, did, scid, tid, trid):
"""
This function will updates existing the compound trigger object
Args:
gid: Server Group ID
sid: Server ID
did: Database ID
scid: Schema ID
tid: Table ID
trid: Trigger ID
"""
data = request.form if request.form else json.loads(request.data,
encoding='utf-8')
try:
data['schema'] = self.schema
data['table'] = self.table
SQL, name = compound_trigger_utils.get_sql(self.conn, data, tid,
trid,
self.datlastsysoid)
if not isinstance(SQL, str):
return SQL
SQL = SQL.strip('\n').strip(' ')
status, res = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=res)
# We need oid to add object in browser tree and if user
# update the compound trigger then new OID is getting generated
# so we need to return new OID of compound trigger.
SQL = render_template("/".join([self.template_path,
self._OID_SQL]),
tid=tid,
data=data)
status, new_trid = self.conn.execute_scalar(SQL)
if not status:
return internal_server_error(errormsg=new_trid)
# Fetch updated properties
SQL = render_template("/".join(
[self.template_path, self._PROPERTIES_SQL]),
tid=tid,
trid=new_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(self.not_found_error_msg())
# Making copy of output for future use
data = dict(res['rows'][0])
return jsonify(node=self.blueprint.generate_browser_node(
new_trid,
tid,
name,
icon="icon-%s-bad" %
self.node_type if data['is_enable_trigger'] ==
'D' else "icon-%s" % self.node_type))
except Exception as e:
return internal_server_error(errormsg=str(e))