def _get_properties_for_fk_const(tid, fkid, data, template_path, conn): """ Get property data for fk constraint. tid: table Id fkid: Foreign key constraint ID. data: Data. template_path: template path for get sql. conn: Connection. """ name = data['name'] if 'name' in data else None sql = render_template("/".join([template_path, FKEY_PROPERTIES_SQL]), tid=tid, cid=fkid) status, res = conn.execute_dict(sql) if not status: raise ExecuteError(res) if len(res['rows']) == 0: raise ObjectGone( _('Could not find the foreign key constraint in the table.')) old_data = res['rows'][0] if 'name' not in data: name = data['name'] = old_data['name'] return old_data, name
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 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, did, scid, tid, plid, datlastsysoid, schema, table, template_path=None): """ This function will generate sql from model data """ if plid is not None: sql = render_template("/".join([template_path, 'properties.sql']), schema=schema, plid=plid, scid=scid) status, res = conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: raise ObjectGone(_('Could not find the policy in the table.')) old_data = dict(res['rows'][0]) old_data['schema'] = schema old_data['table'] = table sql = render_template( "/".join([template_path, 'update.sql']), data=data, o_data=old_data ) else: data['schema'] = schema data['table'] = table sql = render_template("/".join( [template_path, 'create.sql']), data=data) return sql, data['name'] if 'name' in data else old_data['name']
def get_reverse_engineered_sql(conn, schema, table, did, tid, idx, datlastsysoid, 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 idx: Index ID :param datlastsysoid: :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']), did=did, tid=tid, idx=idx, 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 index 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 # Add column details for current index data = get_column_details(conn, idx, data, 'create') # Add Include details of the index if conn.manager.version >= 110000: data = get_include_details(conn, idx, data) SQL, name = get_sql(conn, data, did, tid, None, datlastsysoid) if with_header: sql_header = u"-- Index: {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 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 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') did = kwargs.get('did') tid = kwargs.get('tid') idx = kwargs.get('idx') datlastsysoid = kwargs.get('datlastsysoid') template_path = kwargs.get('template_path', None) with_header = kwargs.get('with_header', True) SQL = render_template("/".join([template_path, 'properties.sql']), did=did, tid=tid, idx=idx, 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 index 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 # Add column details for current index data = get_column_details(conn, idx, data, 'create') # Add Include details of the index if conn.manager.version >= 110000: data = get_include_details(conn, idx, data) SQL, name = get_sql(conn, data=data, did=did, tid=tid, idx=None, datlastsysoid=datlastsysoid) if with_header: sql_header = u"-- Index: {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 return SQL
def get_sql(conn, **kwargs): """ This function will generate sql from model data. :param conn: Connection Object :param kwargs: :return: """ data = kwargs.get('data') did = kwargs.get('did') tid = kwargs.get('tid') idx = kwargs.get('idx') datlastsysoid = kwargs.get('datlastsysoid') mode = kwargs.get('mode', None) template_path = kwargs.get('template_path', None) if_exists_flag = kwargs.get('if_exists_flag', False) name = data['name'] if 'name' in data else None if idx is not None: sql = render_template("/".join([template_path, 'properties.sql']), did=did, tid=tid, idx=idx, datlastsysoid=datlastsysoid) status, res = conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: raise ObjectGone(_('Could not find the index in the table.')) old_data = dict(res['rows'][0]) # Remove opening and closing bracket as we already have in jinja # template. if 'using' in old_data and old_data['using'] is not None and \ old_data['using'].startswith('(') and \ old_data['using'].endswith(')'): old_data['using'] = old_data['using'][1:-1] if 'withcheck' in old_data and old_data['withcheck'] is not None and \ old_data['withcheck'].startswith('(') and \ old_data['withcheck'].endswith(')'): old_data['withcheck'] = old_data['withcheck'][1:-1] # 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'] sql = render_template( "/".join([template_path, 'update.sql']), data=data, o_data=old_data, conn=conn ) else: sql = _get_create_sql(data, template_path, conn, mode, name, if_exists_flag=if_exists_flag) return sql, name
def get_reverse_engineered_sql(conn, **kwargs): """ This function will return reverse engineered sql for specified trigger. :param conn: :param kwargs: :return: """ schema = kwargs.get('schema') table = kwargs.get('table') scid = kwargs.get('scid') plid = kwargs.get('plid') policy_table_id = kwargs.get('policy_table_id') datlastsysoid = kwargs.get('datlastsysoid') template_path = kwargs.get('template_path', None) with_header = kwargs.get('with_header', True) SQL = render_template("/".join([template_path, 'properties.sql']), plid=plid, scid=scid, policy_table_id=policy_table_id) status, res = conn.execute_dict(SQL) if not status: raise ExecuteError(res) if len(res['rows']) == 0: raise ObjectGone(_('Could not find the policy 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 SQL, name = get_sql(conn, data=data, scid=scid, plid=None, policy_table_id=policy_table_id, datlastsysoid=datlastsysoid, schema=schema, table=table) if with_header: sql_header = "-- POLICY: {0}\n\n-- ".format(data['name']) sql_header += render_template("/".join([template_path, 'delete.sql']), policy_name=data['name'], result=data) SQL = sql_header + '\n\n' + SQL return SQL
def __init__(self, **kwargs): """ This method is used to initialize the class and create a proper object name which will be used to fetch the data using namespace name and object name. Args: **kwargs : N number of parameters """ # Save the server id and database id, namespace id, object id assert ('sid' in kwargs) assert ('did' in kwargs) assert ('obj_id' in kwargs) self.sid = kwargs['sid'] self.did = kwargs['did'] self.obj_id = kwargs['obj_id'] sql_filter = kwargs.get('sql_filter', None) self._row_filter = sql_filter if type(sql_filter) is str else None self._data_sorting = kwargs.get('data_sorting', None) self._set_sorting_from_filter_dialog = False manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(self.sid) conn = manager.connection(did=self.did) # we will set template path for sql scripts self.sql_path = 'sqleditor/sql/#{0}#'.format(manager.version) if conn.connected(): # Fetch the Namespace Name and object Name query = render_template( "/".join([self.sql_path, 'objectname.sql']), obj_id=self.obj_id ) status, result = conn.execute_dict(query) if not status: raise Exception(result) if len(result['rows']) == 0: raise ObjectGone( gettext("The specified object could not be found.")) self.nsp_name = result['rows'][0]['nspname'] self.object_name = result['rows'][0]['relname'] else: raise Exception(gettext( 'Not connected to server or connection with the server ' 'has been closed.') )
def get_reverse_engineered_sql(conn, schema, table, did, scid, tid, plid, datlastsysoid, 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 did: DB ID :param tid: Table ID :param plid: Policy ID :param datlastsysoid: :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']), plid=plid, scid=scid) status, res = conn.execute_dict(SQL) if not status: raise Exception(res) if len(res['rows']) == 0: raise ObjectGone(_('Could not find the policy 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 SQL, name = get_sql(conn, data, did, scid, tid, None, datlastsysoid, schema, table) if with_header: sql_header = u"-- POLICY: {0}\n\n-- ".format(data['name']) sql_header += render_template("/".join([template_path, 'delete.sql']), policy_name=data['name'], result=data ) SQL = sql_header + '\n\n' + SQL return SQL
def get_sql(conn, data, did, tid, ctype, cid=None, template_path=None): """ This function will generate sql from model data. :param conn: Connection Object :param data: data :param did: Database ID :param tid: Table id :param ctype: Constraint Type :param cid: index Constraint ID :param template_path: Template Path :return: """ name = data['name'] if 'name' in data else None sql = None if cid is not None: sql = render_template("/".join([template_path, 'properties.sql']), did=did, tid=tid, cid=cid, constraint_type=ctype) status, res = conn.execute_dict(sql) if not status: raise ExecuteError(res) elif len(res['rows']) == 0: raise ObjectGone(_('Could not find the constraint in the table.')) old_data = res['rows'][0] if 'name' not in data: name = data['name'] = old_data['name'] sql = render_template("/".join([template_path, 'update.sql']), data=data, o_data=old_data) else: is_error, errmsg, name = _check_required_args(data, name) if is_error: return _(errmsg), name sql = render_template( "/".join([template_path, 'create.sql']), data=data, conn=conn, constraint_name='PRIMARY KEY' if ctype == 'p' else 'UNIQUE') return sql, name
def _get_domain(self, doid): """ Returns Domain and Schema name. Args: doid: Domain Id """ SQL = render_template("/".join([self.template_path, 'get_domain.sql']), doid=doid) status, res = self.conn.execute_2darray(SQL) if not status: return False, internal_server_error(errormsg=res) if len(res['rows']) == 0: raise ObjectGone(self.not_found_error_msg('Domain')) return res['rows'][0]['schema'], res['rows'][0]['domain']
def get_sql(conn, data, did, tid, exid=None, template_path=None): """ This function will generate sql from model data. :param conn: Connection Object :param data: data :param did: Database ID :param tid: Table id :param exid: Exclusion Constraint ID :param template_path: Template Path :return: """ name = data['name'] if 'name' in data else None if exid is not None: sql = render_template("/".join([template_path, 'properties.sql']), did=did, tid=tid, cid=exid) status, res = conn.execute_dict(sql) if not status: raise Exception(res) if len(res['rows']) == 0: raise ObjectGone( _('Could not find the exclusion constraint in the table.')) old_data = res['rows'][0] if 'name' not in data: name = data['name'] = old_data['name'] sql = render_template("/".join([template_path, 'update.sql']), data=data, o_data=old_data) else: if 'columns' not in data or \ (isinstance(data['columns'], list) and len(data['columns']) < 1): return _('-- definition incomplete'), name sql = render_template("/".join([template_path, 'create.sql']), data=data, conn=conn) return sql, name
def get_sql(conn, data, did, tid, ctype, cid=None, template_path=None): """ This function will generate sql from model data. :param conn: Connection Object :param data: data :param did: Database ID :param tid: Table id :param ctype: Constraint Type :param cid: index Constraint ID :param template_path: Template Path :return: """ name = data['name'] if 'name' in data else None sql = None if cid is not None: sql = render_template("/".join([template_path, 'properties.sql']), did=did, tid=tid, cid=cid, constraint_type=ctype) status, res = conn.execute_dict(sql) if not status: raise Exception(res) if len(res['rows']) == 0: raise ObjectGone(_('Could not find the constraint in the table.')) old_data = res['rows'][0] if 'name' not in data: name = data['name'] = old_data['name'] sql = render_template("/".join([template_path, 'update.sql']), data=data, o_data=old_data) else: required_args = [[u'columns', u'index'] # Either of one should be there. ] def is_key_str(key, data): return isinstance(data[key], (str, unicode)) and data[key] != "" def is_key_list(key, data): return isinstance(data[key], list) and len(data[param]) > 0 for arg in required_args: if isinstance(arg, list): for param in arg: if param in data: if is_key_str(param, data) or is_key_list(param, data): break else: return _('-- definition incomplete'), name elif arg not in data: return _('-- definition incomplete'), name sql = render_template( "/".join([template_path, 'create.sql']), data=data, conn=conn, constraint_name='PRIMARY KEY' if ctype == 'p' else 'UNIQUE') return sql, name
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, **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
def get_sql(conn, data, did, tid, idx, datlastsysoid, mode=None, template_path=None): """ This function will generate sql from model data. :param conn: Connection Object :param data: Data :param did: :param tid: Table ID :param idx: Index ID :param datlastsysoid: :param mode: :param template_path: Optional template path :return: """ name = data['name'] if 'name' in data else None if idx is not None: SQL = render_template("/".join([template_path, 'properties.sql']), did=did, tid=tid, idx=idx, datlastsysoid=datlastsysoid) status, res = conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: raise ObjectGone(_('Could not find the index in the table.')) old_data = dict(res['rows'][0]) # Remove opening and closing bracket as we already have in jinja # template. if 'using' in old_data and old_data['using'] is not None and \ old_data['using'].startswith('(') and \ old_data['using'].endswith(')'): old_data['using'] = old_data['using'][1:-1] if 'withcheck' in old_data and old_data['withcheck'] is not None and \ old_data['withcheck'].startswith('(') and \ old_data['withcheck'].endswith(')'): old_data['withcheck'] = old_data['withcheck'][1:-1] # 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'] SQL = render_template("/".join([template_path, 'update.sql']), data=data, o_data=old_data, conn=conn) else: required_args = {'name': 'Name', 'columns': 'Columns'} for arg in required_args: err = False if arg == 'columns' and len(data['columns']) < 1: err = True if arg not in data: err = True # Check if we have at least one column if err: return _('-- definition incomplete'), name # If the request for new object which do not have did SQL = render_template("/".join([template_path, 'create.sql']), data=data, conn=conn, mode=mode) SQL += "\n" SQL += render_template("/".join([template_path, 'alter.sql']), data=data, conn=conn) return SQL, name
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 connection(self, **kwargs): database = kwargs.get('database', None) conn_id = kwargs.get('conn_id', None) auto_reconnect = kwargs.get('auto_reconnect', True) did = kwargs.get('did', None) async_ = kwargs.get('async_', None) use_binary_placeholder = kwargs.get('use_binary_placeholder', False) array_to_string = kwargs.get('array_to_string', False) if database is not None: if did is not None and did in self.db_info: self.db_info[did]['datname'] = database else: if did is None: database = self.db elif did in self.db_info: database = self.db_info[did]['datname'] else: maintenance_db_id = 'DB:{0}'.format(self.db) if maintenance_db_id in self.connections: conn = self.connections[maintenance_db_id] # try to connect maintenance db if not connected if not conn.connected(): conn.connect() if conn.connected(): status, res = conn.execute_dict(""" SELECT db.oid as did, db.datname, db.datallowconn, pg_catalog.pg_encoding_to_char(db.encoding) AS serverencoding, pg_catalog.has_database_privilege(db.oid, 'CREATE') as cancreate, datlastsysoid, datistemplate FROM pg_catalog.pg_database db WHERE db.oid = {0}""".format(did)) if status and len(res['rows']) > 0: for row in res['rows']: self.db_info[did] = row database = self.db_info[did]['datname'] if did not in self.db_info: raise ObjectGone( gettext( "Could not find the specified database.")) if not get_crypt_key()[0]: # the reason its not connected might be missing key raise CryptKeyMissing() if database is None: # Check SSH Tunnel is alive or not. if self.use_ssh_tunnel == 1: self.check_ssh_tunnel_alive() else: raise ConnectionLost(self.sid, None, None) my_id = ('CONN:{0}'.format(conn_id)) if conn_id is not None else \ ('DB:{0}'.format(database)) self.pinged = datetime.datetime.now() if my_id in self.connections: return self.connections[my_id] else: if async_ is None: async_ = 1 if conn_id is not None else 0 else: async_ = 1 if async_ is True else 0 self.connections[my_id] = Connection( self, my_id, database, auto_reconnect=auto_reconnect, async_=async_, use_binary_placeholder=use_binary_placeholder, array_to_string=array_to_string) return self.connections[my_id]
def get_sql(conn, data, tid, fkid=None, template_path=None): """ This function will generate sql from model data. :param conn: Connection Object :param data: data :param tid: Table id :param fkid: Foreign Key :param template_path: Template Path :return: """ name = data['name'] if 'name' in data else None if fkid is not None: sql = render_template("/".join([template_path, 'properties.sql']), tid=tid, cid=fkid) status, res = conn.execute_dict(sql) if not status: raise Exception(res) if len(res['rows']) == 0: raise ObjectGone( _('Could not find the foreign key constraint in the table.')) old_data = res['rows'][0] if 'name' not in data: name = data['name'] = old_data['name'] sql = render_template("/".join([template_path, 'update.sql']), data=data, o_data=old_data) if 'autoindex' in data and data['autoindex'] and \ ('coveringindex' in data and data['coveringindex'] != ''): col_sql = render_template( "/".join([template_path, 'get_constraint_cols.sql']), tid=tid, keys=zip(old_data['confkey'], old_data['conkey']), confrelid=old_data['confrelid']) status, res = conn.execute_dict(col_sql) if not status: raise Exception(res) columns = [] for row in res['rows']: columns.append({ "local_column": row['conattname'], "references": old_data['confrelid'], "referenced": row['confattname'] }) data['columns'] = columns sql += render_template("/".join( [template_path, 'create_index.sql']), data=data, conn=conn) else: if 'columns' not in data: return _('-- definition incomplete'), name elif isinstance(data['columns'], list) and len(data['columns']) < 1: return _('-- definition incomplete'), name if data['autoindex'] and \ ('coveringindex' not in data or data['coveringindex'] == ''): return _('-- definition incomplete'), name # Get the parent schema and table. schema, table = get_parent(conn, data['columns'][0]['references']) data['remote_schema'] = schema data['remote_table'] = table sql = render_template("/".join([template_path, 'create.sql']), data=data, conn=conn) if data['autoindex']: sql += render_template("/".join( [template_path, 'create_index.sql']), data=data, conn=conn) return sql, name