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