def __set_updatable_results_attrs(self, sql_path, table_oid, conn): # Set template path for sql scripts and the table object id self.sql_path = sql_path self.obj_id = table_oid 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 ExecuteError(result) self.nsp_name = result['rows'][0]['nspname'] self.object_name = result['rows'][0]['relname'] else: raise InternalServerError(SERVER_CONNECTION_CLOSED)
def get_all_columns_with_order(self, default_conn): """ Responsible for fetching columns from given object Args: default_conn: Connection object Returns: all_sorted_columns: Columns which are already sorted which will be used to populate the Grid in the dialog all_columns: List of all the column for given object which will be used to fill columns options """ driver = get_driver(PG_DEFAULT_DRIVER) if default_conn is None: manager = driver.connection_manager(self.sid) conn = manager.connection(did=self.did, conn_id=self.conn_id) else: conn = default_conn all_sorted_columns = [] data_sorting = self.get_data_sorting() all_columns = [] if conn.connected(): # Fetch the rest of the column names query = render_template( "/".join([self.sql_path, 'get_columns.sql']), obj_id=self.obj_id ) status, result = conn.execute_dict(query) if not status: raise ExecuteError(result) for row in result['rows']: all_columns.append(row['attname']) else: raise InternalServerError(SERVER_CONNECTION_CLOSED) # If user has custom data sorting then pass as it as it is if data_sorting and len(data_sorting) > 0: all_sorted_columns = data_sorting return all_sorted_columns, all_columns
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 ExecuteError(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_columns_types(is_query_tool, columns_info, table_oid, conn, has_oids): nodes_sqlpath = 'columns/sql/#{0}#'.format(conn.manager.version) query = render_template( "/".join([nodes_sqlpath, 'nodes.sql']), tid=table_oid, has_oids=has_oids ) colst, rset = conn.execute_2darray(query) if not colst: raise ExecuteError(rset) column_types = dict() for key, col in enumerate(columns_info): col_type = dict() col_type['type_code'] = col['type_code'] col_type['type_name'] = None col_type['internal_size'] = col['internal_size'] column_types[col['name']] = col_type if not is_query_tool: col_type['not_null'] = col['not_null'] = \ rset['rows'][key]['not_null'] col_type['has_default_val'] = \ col['has_default_val'] = \ rset['rows'][key]['has_default_val'] else: for row in rset['rows']: if row['oid'] == col['table_column']: col_type['not_null'] = col['not_null'] = row['not_null'] col_type['has_default_val'] = \ col['has_default_val'] = row['has_default_val'] break else: col_type['not_null'] = col['not_null'] = None col_type['has_default_val'] = col['has_default_val'] = None return column_types
def _get_primary_keys(sql_path, table_oid, conn): query = render_template("/".join([sql_path, 'primary_keys.sql']), obj_id=table_oid) status, result = conn.execute_dict(query) if not status: raise ExecuteError(result) primary_keys_columns = [] primary_keys = OrderedDict() pk_names = [] for row in result['rows']: primary_keys[row['attname']] = row['typname'] primary_keys_columns.append({ 'name': row['attname'], 'column_number': row['attnum'] }) pk_names.append(row['attname']) return primary_keys, primary_keys_columns, pk_names
def get_parent(conn, tid, template_path=None): """ This function will return the parent of the given table. :param conn: Connection Object :param tid: Table oid :param template_path: :return: """ SQL = render_template("/".join([template_path, 'get_parent.sql']), tid=tid) status, rset = conn.execute_2darray(SQL) if not status: raise ExecuteError(rset) schema = '' table = '' if 'rows' in rset and len(rset['rows']) > 0: schema = rset['rows'][0]['schema'] table = rset['rows'][0]['table'] return schema, table
def _check_oids(conn, sql_path, table_oid, columns_info): # Remove the special behavior of OID columns from # PostgreSQL 12 onwards, so returning False. if conn.manager.sversion >= 120000: return False # Check that the table has oids query = render_template("/".join([sql_path, 'has_oids.sql']), obj_id=table_oid) status, has_oids = conn.execute_scalar(query) if not status: raise ExecuteError(has_oids) # Check that the oid column is selected in results columns oid_column_selected = False for col in columns_info: if col['table_column'] is None and col['display_name'] == 'oid': oid_column_selected = True break return has_oids and oid_column_selected
def get_primary_keys(self, default_conn=None): """ This function is used to fetch the primary key columns. """ driver = get_driver(PG_DEFAULT_DRIVER) if default_conn is None: manager = driver.connection_manager(self.sid) conn = manager.connection(did=self.did, conn_id=self.conn_id) else: conn = default_conn pk_names = '' primary_keys = OrderedDict() if conn.connected(): # Fetch the primary key column names query = render_template( "/".join([self.sql_path, 'primary_keys.sql']), obj_id=self.obj_id ) status, result = conn.execute_dict(query) if not status: raise ExecuteError(result) for row in result['rows']: pk_names += driver.qtIdent(conn, row['attname']) + ',' primary_keys[row['attname']] = row['typname'] if pk_names != '': # Remove last character from the string pk_names = pk_names[:-1] else: raise InternalServerError( gettext('Not connected to server or connection with the ' 'server has been closed.') ) return pk_names, primary_keys
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, **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_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) if_exists_flag = kwargs.get('add_not_exists_clause', False) 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_exists_flag=if_exists_flag) if with_header: sql_header = "-- 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