def sql(self, gid, sid, did, eid): """ This function will generate sql for the sql panel """ SQL = render_template("/".join( [self.template_path, 'properties.sql'] ), eid=eid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone( _("Could not find the extension on the server.") ) result = res['rows'][0] SQL = render_template("/".join( [self.template_path, 'create.sql'] ), data=result, conn=self.conn, display_comments=True ) return ajax_response(response=SQL)
def sql(self, gid, sid, did, scid, pkgid, varid=None): """ Returns the SQL for the Function object. Args: gid: Server Group Id sid: Server Id did: Database Id scid: Schema Id pkgid: Package Id varid: variable Id """ SQL = render_template( "/".join([self.sql_template_path, 'properties.sql']), varid=varid, pkgid=pkgid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone( errormsg=gettext("Could not find the variables") ) var = res['rows'][0] sql = u"-- Package Variable: {}".format(var['name']) sql += u"\n\n" sql += u"{} {};".format(var['name'], var['datatype']) return ajax_response(response=sql)
def nodes(self, gid=None): """Return a JSON document listing the server groups for the user""" nodes = [] if gid is None: groups = ServerGroup.query.filter_by(user_id=current_user.id) for group in groups: nodes.append( self.blueprint.generate_browser_node( "%d" % group.id, None, group.name, "icon-%s" % self.node_type, True, self.node_type ) ) else: group = ServerGroup.query.filter_by(user_id=current_user.id, id=gid).first() if not group: return gone( errormsg=gettext("Could not find the server group.") ) nodes = self.blueprint.generate_browser_node( "%d" % (group.id), None, group.name, "icon-%s" % self.node_type, True, self.node_type ) return make_json_response(data=nodes)
def properties(self, gid, sid, did, scid, coid): """ This function will show the properties of the selected catalog objects node. Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID scid: Schema ID coid: Catalog object ID Returns: JSON of selected catalog objects node """ SQL = render_template( "/".join([self.template_path, 'properties.sql']), scid=scid, coid=coid ) 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 specified catalog object.""")) return ajax_response( response=res['rows'][0], status=200 )
def getSQL(self, gid, sid, data, did, eid=None): """ This function will generate sql from model data """ required_args = [ 'name' ] if eid is not None: SQL = render_template("/".join( [self.template_path, 'properties.sql'] ), eid=eid) 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 extension information.") ) old_data = res['rows'][0] for arg in required_args: if arg not in data: data[arg] = old_data[arg] SQL = render_template("/".join( [self.template_path, 'update.sql'] ), data=data, o_data=old_data) return SQL, data['name'] if 'name' in data else old_data['name'] else: SQL = render_template("/".join( [self.template_path, 'create.sql'] ), data=data) return SQL, data['name']
def sql(self, gid, sid, did, scid, tid): """ This function will creates reverse engineered sql for the table object Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID tid: Table ID """ main_sql = [] SQL = render_template( "/".join([self.table_template_path, 'properties.sql']), did=did, scid=scid, tid=tid, 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("The specified table could not be found.")) data = res['rows'][0] return BaseTableView.get_reverse_engineered_sql( self, did, scid, tid, main_sql, data)
def node(self, gid, sid, did, scid, coid): """ This function will fetch properties of catalog objects node. Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID coid: Catalog object ID Returns: JSON of given catalog objects child node """ SQL = render_template( "/".join([self.template_path, 'nodes.sql']), coid=coid ) status, rset = self.conn.execute_2darray(SQL) if not status: return internal_server_error(errormsg=rset) for row in rset['rows']: return make_json_response( data=self.blueprint.generate_browser_node( row['oid'], scid, row['name'], icon="icon-catalog_object" ), status=200 ) return gone( errormsg=gettext("Could not find the specified catalog object."))
def node(self, gid, sid, did, lid): """ This function will fetch properties of the language nodes. Args: gid: Server Group ID sid: Server ID did: Database ID lid: Language ID """ sql = render_template("/".join([self.template_path, 'properties.sql']), lid=lid) status, result = self.conn.execute_2darray(sql) if not status: return internal_server_error(errormsg=result) for row in result['rows']: return make_json_response( data=self.blueprint.generate_browser_node( row['oid'], did, row['name'], icon="icon-language" ), status=200 ) return gone(gettext("Could not find the specified language."))
def node(self, gid, sid, did, scid, doid): """ This function will fetch the properties of the domain node. Args: gid: Server Group Id sid: Server Id did: Database Id scid: Schema Id doid: Domain Id """ SQL = render_template("/".join([self.template_path, 'node.sql']), doid=doid) status, rset = self.conn.execute_2darray(SQL) if not status: return internal_server_error(errormsg=rset) for row in rset['rows']: return make_json_response( data=self.blueprint.generate_browser_node( row['oid'], scid, row['name'], icon="icon-domain" ), status=200 ) return gone(gettext("Could not find the specified domain."))
def node(self, gid, sid, did): SQL = render_template( "/".join([self.template_path, 'nodes.sql']), did=did, conn=self.conn, last_system_oid=0 ) status, rset = self.conn.execute_2darray(SQL) if not status: return internal_server_error(errormsg=rset) for row in rset['rows']: db = row['name'] if self.manager.db == db: connected = True else: conn = self.manager.connection(row['name']) connected = conn.connected() icon_css_class = "pg-icon-database" if not connected: icon_css_class = "icon-database-not-connected" return make_json_response( data=self.blueprint.generate_browser_node( row['did'], sid, row['name'], icon=icon_css_class, connected=connected, spcname=row['spcname'], allowConn=row['datallowconn'], canCreate=row['cancreate'] ), status=200 ) return gone(errormsg=_("Could not find the database on the server."))
def get_sql(self, gid, sid, data, did=None): SQL = '' if did is not None: # Fetch the name of database for comparison conn = self.manager.connection() status, rset = conn.execute_dict( render_template( "/".join([self.template_path, 'nodes.sql']), did=did, conn=conn, last_system_oid=0 ) ) if not status: return False, internal_server_error(errormsg=rset) if len(rset['rows']) == 0: return gone( _("Could not find the database on the server.") ) data['old_name'] = (rset['rows'][0])['name'] if 'name' not in data: data['name'] = data['old_name'] SQL = '' for action in ["rename_database", "tablespace"]: SQL += self.get_offline_sql(gid, sid, data, did, action) SQL += self.get_online_sql(gid, sid, data, did) else: SQL += self.get_new_sql(gid, sid, data, did) return True, SQL
def sql(self, gid, sid, did, scid, syid): """ This function will generates reverse engineered sql for synonym object Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID syid: Synonym ID """ SQL = render_template("/".join([self.template_path, 'properties.sql']), scid=scid, syid=syid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) if len(res['rows']) > 0: data = res['rows'][0] else: return gone( gettext('The specified synonym could not be found.') ) SQL = render_template("/".join([self.template_path, 'create.sql']), data=data, conn=self.conn, comment=True) return ajax_response(response=SQL)
def wrapped(self, *args, **kwargs): self.manager = get_driver( PG_DEFAULT_DRIVER ).connection_manager( kwargs['sid'] ) if self.manager is None: return gone(errormsg="Could not find the server.") if action and action in ["drop"]: self.conn = self.manager.connection() elif 'did' in kwargs: self.conn = self.manager.connection(did=kwargs['did']) self.db_allow_connection = True # If connection to database is not allowed then # provide generic connection if kwargs['did'] in self.manager.db_info: self._db = self.manager.db_info[kwargs['did']] if self._db['datallowconn'] is False: self.conn = self.manager.connection() self.db_allow_connection = False else: self.conn = self.manager.connection() # set template path for sql scripts self.template_path = 'databases/sql/#{0}#'.format( self.manager.version ) return f(self, *args, **kwargs)
def node(self, gid, sid, did, scid, foid): """ Returns the Foreign Tables to generate the Nodes. Args: gid: Server Group Id sid: Server Id did: Database Id scid: Schema Id foid: Foreign Table Id """ SQL = render_template("/".join([self.template_path, 'node.sql']), foid=foid) status, rset = self.conn.execute_2darray(SQL) if not status: return internal_server_error(errormsg=rset) for row in rset['rows']: return make_json_response( data=self.blueprint.generate_browser_node( row['oid'], scid, row['name'], icon="icon-foreign_table" ), status=200 ) return gone(gettext( 'Could not find the specified foreign table.' ))
def sql(self, gid, sid, did, scid, tid): """ This function will reverse generate sql for sql panel :param gid: group id :param sid: server id :param did: database id :param scid: schema id :param tid: fts tempate id """ sql = render_template( "/".join([self.template_path, 'sql.sql']), tid=tid, scid=scid, conn=self.conn ) status, res = self.conn.execute_scalar(sql) if not status: return internal_server_error( gettext( "Could not generate reversed engineered query for the " "FTS Template.\n{0}").format(res) ) if res is None: return gone( gettext( "Could not generate reversed engineered query for " "FTS Template node.") ) return ajax_response(response=res)
def select_sql(self, gid, sid, did, scid, foid): """ SELECT script sql for the object Args: gid: Server Group Id sid: Server Id did: Database Id scid: Schema Id foid: Foreign Table Id Returns: SELECT Script sql for the object """ data = self._fetch_properties(gid, sid, did, scid, foid) if data is False: return gone( gettext("Could not find the foreign table on the server.") ) columns = [] for c in data['columns']: columns.append(self.qtIdent(self.conn, c['attname'])) if len(columns) > 0: columns = ", ".join(columns) else: columns = '*' sql = u"SELECT {0}\n\tFROM {1};".format( columns, self.qtIdent(self.conn, data['basensp'], data['name']) ) return ajax_response(response=sql)
def delete_sql(self, gid, sid, did, scid, foid): """ DELETE script sql for the object Args: gid: Server Group Id sid: Server Id did: Database Id scid: Schema Id foid: Foreign Table Id Returns: DELETE Script sql for the object """ data = self._fetch_properties(gid, sid, did, scid, foid) if data is False: return gone( gettext("Could not find the foreign table on the server.") ) sql = u"DELETE FROM {0}\n\tWHERE <condition>;".format( self.qtIdent(self.conn, data['basensp'], data['name']) ) return ajax_response(response=sql)
def properties(self, gid, sid, jid, jscid): """ This function will show the properties of the selected language node. Args: gid: Server Group ID sid: Server ID jid: Job ID jscid: JobSchedule ID """ sql = render_template( "/".join([self.template_path, 'properties.sql']), jscid=jscid, jid=jid ) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone( errormsg=gettext("Could not find the specified job step.") ) return ajax_response( response=res['rows'][0], status=200 )
def node(self, gid, sid, did, scid, cfgid): """ Return FTS Configuration node to generate node Args: gid: Server Group Id sid: Server Id did: Database Id scid: Schema Id cfgid: fts Configuration id """ sql = render_template( "/".join([self.template_path, 'nodes.sql']), cfgid=cfgid ) status, rset = self.conn.execute_2darray(sql) if not status: return internal_server_error(errormsg=rset) if len(rset['rows']) == 0: return gone(_(""" Could not find the FTS Configuration node. """)) for row in rset['rows']: return make_json_response( data=self.blueprint.generate_browser_node( row['oid'], did, row['name'], icon="icon-fts_configuration" ), status=200 )
def properties(self, gid, sid, did, scid, tid, ptid): """ This function will show the properties of the selected table node. Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID scid: Schema ID tid: Table ID ptid: Partition Table ID Returns: JSON of selected table node """ SQL = render_template("/".join([self.partition_template_path, 'properties.sql']), did=did, scid=scid, tid=tid, ptid=ptid, 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( "The specified partitioned table could not be found.")) return super(PartitionsView, self).properties( gid, sid, did, scid, ptid, res)
def node(self, gid, sid, did, fid): """ This function will fetch properties of foreign data wrapper node. Args: gid: Server Group ID sid: Server ID did: Database ID fid: Foreign data wrapper ID """ sql = render_template("/".join([self.template_path, 'properties.sql']), conn=self.conn, fid=fid) status, r_set = self.conn.execute_2darray(sql) if not status: return internal_server_error(errormsg=r_set) for row in r_set['rows']: return make_json_response( data=self.blueprint.generate_browser_node( row['fdwoid'], did, row['name'], icon="icon-foreign_data_wrapper" ), status=200 ) return gone(gettext("Could not find the specified" " foreign data wrapper."))
def properties(self, gid, sid, did, etid): """ This function is used to list all the event trigger nodes within that collection. Args: gid: Server Group ID sid: Server ID did: Database ID etid: Event trigger ID Returns: """ sql = render_template( "/".join([self.template_path, 'properties.sql']), etid=etid, conn=self.conn ) 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 event trigger information.") ) result = res['rows'][0] result = self._formatter(result) return ajax_response( response=result, status=200 )
def properties(self, gid, sid, did, scid, pkgid, edbfnid=None): """ Returns the Function properties. Args: gid: Server Group Id sid: Server Id did: Database Id scid: Schema Id pkgid: Package Id edbfnid: Function Id """ SQL = render_template("/".join([self.sql_template_path, 'properties.sql']), pkgid=pkgid, edbfnid=edbfnid) 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 function in the database.") ) resp_data = res['rows'][0] # Get formatted Arguments frmtd_params, frmtd_proargs = self._format_arguments_from_db(resp_data) resp_data.update(frmtd_params) resp_data.update(frmtd_proargs) return ajax_response( response=resp_data, status=200 )
def properties(self, gid, sid, did, cid): """ This function will show the properties of the selected cast node :param gid: group id :param sid: server id :param did: database id :param cid: cast id :return: """ last_system_oid = (self.manager.db_info[did])['datlastsysoid'] if \ self.manager.db_info is not None and \ did in self.manager.db_info else 0 sql = render_template( "/".join([self.template_path, 'properties.sql']), cid=cid, datlastsysoid=last_system_oid, showsysobj=self.blueprint.show_system_objects ) 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 cast information.") ) return ajax_response( response=res['rows'][0], status=200 )
def sql(self, gid, sid, did, cid): """ This function will generate sql for sql panel :param gid: group id :param sid: server id :param did: database id :param cid: cast id :return: """ try: sql = render_template( "/".join([self.template_path, 'sql.sql']), cid=cid, conn=self.conn ) status, res = self.conn.execute_scalar(sql) if not status: return internal_server_error( _("Could not generate reversed engineered SQL for the " "cast.\n\n{0}").format(res) ) if res is None: return gone( _("Could not generate reversed engineered SQL for the " "cast node.\n") ) return ajax_response(response=res) except Exception as e: return internal_server_error(errormsg=str(e))
def sql(self, gid, sid, did, scid, tid, trid): """ This function will generates reverse engineered sql for trigger object Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID tid: Table ID trid: Trigger ID """ 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 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 data = self.get_trigger_function_schema(data) if len(data['custom_tgargs']) > 1: # We know that trigger has more than 1 argument, let's join them data['tgargs'] = self._format_args(data['custom_tgargs']) if len(data['tgattr']) >= 1: columns = ', '.join(data['tgattr'].split(' ')) data['columns'] = self._column_details(tid, columns) data = self._trigger_definition(data) SQL, name = self.get_sql(scid, tid, None, data) sql_header = u"-- Trigger: {0}\n\n-- ".format(data['name']) sql_header += render_template("/".join([self.template_path, 'delete.sql']), data=data, conn=self.conn) SQL = sql_header + '\n\n' + SQL.strip('\n') # If 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 ajax_response(response=SQL)
def properties(self, gid, sid, did, scid, dcid): """ Show properties of FTS Dictionary node Args: gid: Server Group Id sid: Server Id did: Database Id scid: Schema Id dcid: fts dictionary id """ sql = render_template( "/".join([self.template_path, 'properties.sql']), scid=scid, dcid=dcid ) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone(_(""" Could not find the FTS Dictionary node. """)) if res['rows'][0]['options'] is not None: res['rows'][0]['options'] = self.tokenize_options(res['rows'][0]['options']) return ajax_response( response=res['rows'][0], status=200 )
def node(self, gid, sid, did, etid): """ This function will fetch properties of trigger node. Args: gid: Server Group ID sid: Server ID did: Database ID etid: Event trigger ID Returns: Json object of trigger node """ sql = render_template("/".join([self.template_path, 'nodes.sql']), etid=etid) status, res = self.conn.execute_2darray(sql) if not status: return internal_server_error(errormsg=res) for row in res['rows']: return make_json_response( data=self.blueprint.generate_browser_node( row['oid'], did, row['name'], icon="icon-%s" % self.node_type ), status=200 ) return gone(gettext("Could not find the specified event trigger."))
def properties(self, gid, sid, did, scid, doid, coid): """ Returns the Domain Constraints property. Args: gid: Server Group Id sid: Server Id did: Database Id scid: Schema Id doid: Domain Id coid: Domain Constraint Id """ SQL = render_template("/".join([self.template_path, 'properties.sql']), doid=doid, coid=coid) 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 specified domain constraint." ) ) data = res['rows'][0] return ajax_response( response=data, status=200 )
def node(self, gid, sid, rid): status, rset = self.conn.execute_2darray( render_template( self.sql_path + 'nodes.sql', rid=rid ) ) if not status: return internal_server_error( _( "Error fetching role information from the database server.\n{0}" ).format(rset) ) for row in rset['rows']: return make_json_response( data=self.blueprint.generate_browser_node( row['oid'], sid, row['rolname'], 'icon-role' if row['rolcanlogin'] else 'icon-group', can_login=row['rolcanlogin'], is_superuser=row['rolsuper'] ), status=200 ) return gone(_("Could not find the role information."))
def statistics(self, gid, sid, did, scid, tid, idx=None): """ Statistics Args: gid: Server Group Id sid: Server Id did: Database Id scid: Schema Id tid: Table Id idx: Index Id Returns the statistics for a particular object if idx is specified else return all indexes """ if idx is not None: # Individual index # Check if pgstattuple extension is already created? # if created then only add extended stats status, is_pgstattuple = self.conn.execute_scalar(""" SELECT (count(extname) > 0) AS is_pgstattuple FROM pg_extension WHERE extname='pgstattuple' """) if not status: return internal_server_error(errormsg=is_pgstattuple) if is_pgstattuple: # Fetch index details only if extended stats available SQL = render_template( "/".join([self.template_path, self._PROPERTIES_SQL]), did=did, tid=tid, idx=idx, 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 index in the table.""") ) data = dict(res['rows'][0]) index = data['name'] else: index = None status, res = self.conn.execute_dict( render_template( "/".join([self.template_path, 'stats.sql']), conn=self.conn, schema=self.schema, index=index, idx=idx, is_pgstattuple=is_pgstattuple ) ) else: status, res = self.conn.execute_dict( render_template( "/".join([self.template_path, 'coll_stats.sql']), conn=self.conn, schema=self.schema, table=self.table ) ) if not status: return internal_server_error(errormsg=res) return make_json_response( data=res, status=200 )
def sql(self, gid, sid, jid): """ This function will generate sql for sql panel """ SQL = render_template("/".join([self.template_path, 'properties.sql']), jid=jid, conn=self.conn, last_system_oid=0) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone(_("Could not find the object on the server.")) row = res['rows'][0] status, res = self.conn.execute_dict( render_template( "/".join([self.template_path, 'steps.sql']), jid=jid, conn=self.conn, has_connstr=self.manager.db_info['pgAgent']['has_connstr'])) if not status: return internal_server_error(errormsg=res) row['jsteps'] = res['rows'] status, res = self.conn.execute_dict( render_template("/".join([self.template_path, 'schedules.sql']), jid=jid, conn=self.conn)) if not status: return internal_server_error(errormsg=res) row['jschedules'] = res['rows'] for schedule in row['jschedules']: schedule['jscexceptions'] = [] if schedule['jexid']: idx = 0 for exc in schedule['jexid']: schedule['jscexceptions'].append({ 'jexid': exc, 'jexdate': schedule['jexdate'][idx], 'jextime': schedule['jextime'][idx] }) idx += 1 del schedule['jexid'] del schedule['jexdate'] del schedule['jextime'] return ajax_response(response=render_template( "/".join([self.template_path, 'create.sql']), jid=jid, data=row, conn=self.conn, fetch_id=False, has_connstr=self.manager.db_info['pgAgent']['has_connstr']))
def get_sql(self, gid, sid, did, scid, data, dcid=None): """ This function will return SQL for model data :param gid: group id :param sid: server id :param did: database id :param scid: schema id :param dcid: fts dictionary id """ # Fetch sql for update if dcid is not None: sql = render_template( "/".join([self.template_path, 'properties.sql']), dcid=dcid, scid=scid ) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone(_("Could not find the FTS Dictionary node.")) old_data = res['rows'][0] # Handle templates and its schema name properly if old_data['template_schema'] is not None: if old_data['template_schema'] != "pg_catalog": old_data['template'] = self.qtIdent( self.conn, old_data['template_schema'], old_data['template'] ) # If user has changed the schema then fetch new schema directly # using its oid otherwise fetch old schema name using its oid sql = render_template( "/".join([self.template_path, 'schema.sql']), data=data) status, new_schema = self.conn.execute_scalar(sql) if not status: return internal_server_error(errormsg=new_schema) # Replace schema oid with schema name new_data = data.copy() if 'schema' in new_data: new_data['schema'] = new_schema # Fetch old schema name using old schema oid sql = render_template( "/".join([self.template_path, 'schema.sql']), data=old_data) status, old_schema = self.conn.execute_scalar(sql) if not status: return internal_server_error(errormsg=old_schema) # Replace old schema oid with old schema name old_data['schema'] = old_schema sql = render_template( "/".join([self.template_path, 'update.sql']), data=new_data, o_data=old_data ) # Fetch sql query for modified data return sql.strip('\n'), data['name'] if 'name' in data else old_data['name'] else: # Fetch schema name from schema oid sql = render_template("/".join([self.template_path, 'schema.sql']), data=data) status, schema = self.conn.execute_scalar(sql) if not status: return internal_server_error(errormsg=schema) # Replace schema oid with schema name new_data = data.copy() new_data['schema'] = schema if 'template' in new_data and \ 'name' in new_data and \ 'schema' in new_data: sql = render_template("/".join([self.template_path, 'create.sql']), data=new_data, conn=self.conn ) else: sql = u"-- definition incomplete" return sql.strip('\n'), data['name']
def sql(self, gid, sid, did, fid, fsid, json_resp=True): """ This function will generate sql to show it in sql pane for the selected foreign server node. Args: gid: Server Group ID sid: Server ID did: Database ID fid: Foreign data wrapper ID fsid: Foreign server ID json_resp: """ sql = render_template("/".join( [self.template_path, self._PROPERTIES_SQL]), fsid=fsid, conn=self.conn) 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()) if fid is None and 'fdwid' in res['rows'][0]: fid = res['rows'][0]['fdwid'] is_valid_options = False if res['rows'][0]['fsrvoptions'] is not None: res['rows'][0]['fsrvoptions'] = tokenize_options( res['rows'][0]['fsrvoptions'], 'fsrvoption', 'fsrvvalue') if len(res['rows'][0]['fsrvoptions']) > 0: is_valid_options = True sql = render_template("/".join([self.template_path, self._ACL_SQL]), fsid=fsid) status, fs_rv_acl_res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=fs_rv_acl_res) for row in fs_rv_acl_res['rows']: privilege = parse_priv_from_db(row) if row['deftype'] in res['rows'][0]: res['rows'][0][row['deftype']].append(privilege) else: res['rows'][0][row['deftype']] = [privilege] # To format privileges if 'fsrvacl' in res['rows'][0]: res['rows'][0]['fsrvacl'] = parse_priv_to_db( res['rows'][0]['fsrvacl'], ['U']) sql = render_template("/".join( [self.template_path, self._PROPERTIES_SQL]), fdwid=fid, conn=self.conn) status, res1 = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res1) fdw_data = res1['rows'][0] sql = '' sql = render_template("/".join([self.template_path, self._CREATE_SQL]), data=res['rows'][0], fdwdata=fdw_data, is_valid_options=is_valid_options, conn=self.conn) sql += "\n" sql_header = u"""-- Foreign Server: {0} -- DROP SERVER {0} """.format(res['rows'][0]['name']) sql = sql_header + sql if not json_resp: return sql.strip('\n') return ajax_response(response=sql.strip('\n'))
def get_sql(self, gid, sid, data, did, fid, fsid=None): """ This function will generate sql from model data. Args: gid: Server Group ID sid: Server ID did: Database ID data: Contains the data of the selected foreign server node fid: foreign data wrapper ID fsid: foreign server ID """ if fsid is not None: sql = render_template("/".join( [self.template_path, self._PROPERTIES_SQL]), fsid=fsid, conn=self.conn) 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()) elif res['rows'][0]['fsrvoptions'] is not None: res['rows'][0]['fsrvoptions'] = tokenize_options( res['rows'][0]['fsrvoptions'], 'fsrvoption', 'fsrvvalue') ForeignServerView._get_fsvr_acl(data) old_data = res['rows'][0] is_valid_added_options, \ is_valid_changed_options = \ ForeignServerView._validate_fsvr_options(data, old_data) sql = render_template( "/".join([self.template_path, self._UPDATE_SQL]), data=data, o_data=old_data, is_valid_added_options=is_valid_added_options, is_valid_changed_options=is_valid_changed_options, conn=self.conn) return sql.strip('\n'), \ data['name'] if 'name' in data else old_data['name'] else: sql = render_template("/".join( [self.template_path, self._PROPERTIES_SQL]), fdwid=fid, conn=self.conn) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) fdw_data = res['rows'][0] ForeignServerView._parse_priv(data) is_valid_options = False if 'fsrvoptions' in data: is_valid_options, data['fsrvoptions'] = validate_options( data['fsrvoptions'], 'fsrvoption', 'fsrvvalue') sql = render_template("/".join( [self.template_path, self._CREATE_SQL]), data=data, fdwdata=fdw_data, is_valid_options=is_valid_options, conn=self.conn) sql += "\n" return sql, data['name']
def create(self, gid, sid, did, fid): """ This function will create the foreign server node. Args: gid: Server Group ID sid: Server ID did: Database ID fid: foreign data wrapper ID """ required_args = ['name'] data = request.form if request.form else json.loads(request.data, encoding='utf-8') for arg in required_args: if arg not in data: return make_json_response( status=410, success=0, errormsg=gettext( "Could not find the required parameter ({}).").format( arg)) try: if 'fsrvacl' in data: data['fsrvacl'] = parse_priv_to_db(data['fsrvacl'], ['U']) sql = render_template("/".join( [self.template_path, self._PROPERTIES_SQL]), fdwid=fid, conn=self.conn) status, res1 = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res1) if len(res1['rows']) == 0: return gone( gettext( "The specified foreign server could not be found.")) fdw_data = res1['rows'][0] is_valid_options = False if 'fsrvoptions' in data: is_valid_options, data['fsrvoptions'] = validate_options( data['fsrvoptions'], 'fsrvoption', 'fsrvvalue') sql = render_template("/".join( [self.template_path, self._CREATE_SQL]), data=data, fdwdata=fdw_data, is_valid_options=is_valid_options, conn=self.conn) status, res = self.conn.execute_scalar(sql) if not status: return internal_server_error(errormsg=res) sql = render_template("/".join( [self.template_path, self._PROPERTIES_SQL]), data=data, fdwdata=fdw_data, conn=self.conn) status, r_set = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=r_set) return jsonify(node=self.blueprint.generate_browser_node( r_set['rows'][0]['oid'], fid, r_set['rows'][0]['name'], icon="icon-foreign_server")) except Exception as e: return internal_server_error(errormsg=str(e))
def wrapped(self, **kwargs): self.manager = get_driver( PG_DEFAULT_DRIVER).connection_manager(kwargs['sid']) self.conn = self.manager.connection() driver = get_driver(PG_DEFAULT_DRIVER) self.qtIdent = driver.qtIdent if not self.conn.connected(): return precondition_required( _("Connection to the server has been lost.")) self.sql_path = 'roles/sql/#{0}#'.format(self.manager.version) self.alterKeys = [ u'rolcanlogin', u'rolsuper', u'rolcreatedb', u'rolcreaterole', u'rolinherit', u'rolreplication', u'rolconnlimit', u'rolvaliduntil', u'rolpassword' ] if self.manager.version >= 90200 else [ u'rolcanlogin', u'rolsuper', u'rolcreatedb', u'rolcreaterole', u'rolinherit', u'rolconnlimit', u'rolvaliduntil', u'rolpassword' ] check_permission = False fetch_name = False forbidden_msg = None if action in ['drop', 'update']: if 'rid' in kwargs: fetch_name = True check_permission = True if action == 'drop': forbidden_msg = _( "The current user does not have permission to drop" " the role.") else: forbidden_msg = _( "The current user does not have permission to " "update the role.") elif action == 'create': check_permission = True forbidden_msg = _( "The current user does not have permission to create " "the role.") elif action == 'msql' and 'rid' in kwargs: fetch_name = True if check_permission: user = self.manager.user_info if not user['is_superuser'] and \ not user['can_create_role']: if action != 'update' or 'rid' in kwargs: if kwargs['rid'] != -1: if user['id'] != kwargs['rid']: return forbidden(forbidden_msg) if fetch_name: status, res = self.conn.execute_dict( render_template(self.sql_path + 'permission.sql', rid=kwargs['rid'], conn=self.conn)) if not status: return internal_server_error( _("Error retrieving the role information.\n{0}"). format(res)) if len(res['rows']) == 0: return gone( _("Could not find the role on the database " "server.")) row = res['rows'][0] self.role = row['rolname'] self.rolCanLogin = row['rolcanlogin'] self.rolCatUpdate = row['rolcatupdate'] self.rolSuper = row['rolsuper'] return f(self, **kwargs)
def sql(self, gid, sid, did, scid, tid, fkid=None): """ This function generates sql to show in the sql pane for the selected foreign key. Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID tid: Table ID fkid: Foreign key constraint ID Returns: """ SQL = render_template("/".join([self.template_path, 'properties.sql']), tid=tid, conn=self.conn, cid=fkid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone(_("""Could not find the foreign key.""")) data = res['rows'][0] data['schema'] = self.schema data['table'] = self.table sql = render_template("/".join( [self.template_path, 'get_constraint_cols.sql']), tid=tid, keys=zip(data['confkey'], data['conkey']), confrelid=data['confrelid']) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) columns = [] for row in res['rows']: columns.append({ "local_column": row['conattname'], "references": data['confrelid'], "referenced": row['confattname'] }) data['columns'] = columns SQL = render_template("/".join([self.template_path, 'get_parent.sql']), tid=data['columns'][0]['references']) status, res = self.conn.execute_2darray(SQL) if not status: return internal_server_error(errormsg=res) data['remote_schema'] = res['rows'][0]['schema'] data['remote_table'] = res['rows'][0]['table'] SQL = render_template("/".join([self.template_path, 'create.sql']), data=data) sql_header = u"-- Constraint: {0}\n\n-- ".format(data['name']) sql_header += render_template("/".join( [self.template_path, 'delete.sql']), data=data) sql_header += "\n" SQL = sql_header + SQL return ajax_response(response=SQL)
def properties(self, gid, sid, did, scid, tid, fkid=None): """ This function is used to list all the foreign key nodes within that collection. Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID tid: Table ID fkid: Foreign key constraint ID Returns: """ sql = render_template("/".join([self.template_path, 'properties.sql']), tid=tid, cid=fkid) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone( _("""Could not find the foreign key constraint in the table.""" )) result = res['rows'][0] sql = render_template("/".join( [self.template_path, 'get_constraint_cols.sql']), tid=tid, keys=zip(result['confkey'], result['conkey']), confrelid=result['confrelid']) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) columns = [] cols = [] for row in res['rows']: columns.append({ "local_column": row['conattname'], "references": result['confrelid'], "referenced": row['confattname'] }) cols.append(row['conattname']) result['columns'] = columns if fkid: coveringindex = self.search_coveringindex(tid, cols) result['coveringindex'] = coveringindex if coveringindex: result['autoindex'] = True result['hasindex'] = True else: result['autoindex'] = False result['hasindex'] = False return ajax_response(response=result, status=200)
def create(self, gid, sid, did, scid, tid): """ This function will creates new the schema object Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID tid: Table ID """ data = request.form if request.form else json.loads( request.data, encoding='utf-8' ) for k, v in data.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, TypeError, KeyError): data[k] = v required_args = { 'name': 'Name', 'columns': 'Columns' } is_error, err_msg = IndexesView._check_for_error(required_args, data) if is_error: return make_json_response( status=410, success=0, errormsg=gettext(err_msg) ) # Adding parent into data dict, will be using it while creating sql data['schema'] = self.schema data['table'] = self.table if len(data['table']) == 0: return gone(gettext("The specified table could not be found.")) try: # Start transaction. self.conn.execute_scalar("BEGIN;") SQL = render_template( "/".join([self.template_path, self._CREATE_SQL]), data=data, conn=self.conn, mode='create' ) status, res = self.conn.execute_scalar(SQL) if not status: # End transaction. self.conn.execute_scalar("END;") return internal_server_error(errormsg=res) # If user chooses concurrent index then we cannot run it along # with other alter statements so we will separate alter index part SQL = render_template( "/".join([self.template_path, 'alter.sql']), data=data, conn=self.conn ) SQL = SQL.strip('\n').strip(' ') if SQL != '': status, res = self.conn.execute_scalar(SQL) if not status: # End transaction. self.conn.execute_scalar("END;") return internal_server_error(errormsg=res) # we need oid to to add object in tree at browser SQL = render_template( "/".join([self.template_path, self._OID_SQL]), tid=tid, data=data ) status, idx = self.conn.execute_scalar(SQL) if not status: # End transaction. self.conn.execute_scalar("END;") return internal_server_error(errormsg=tid) # End transaction. self.conn.execute_scalar("END;") return jsonify( node=self.blueprint.generate_browser_node( idx, tid, data['name'], icon="icon-index" ) ) except Exception as e: # End transaction. self.conn.execute_scalar("END;") return internal_server_error(errormsg=str(e))
def delete(self, gid, sid, did, scid, coid=None, only_sql=False): """ This function will delete existing the collation object Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID coid: Collation ID only_sql: Return only sql if True """ if coid is None: data = request.form if request.form else json.loads( request.data, encoding='utf-8') else: data = {'ids': [coid]} # Below will decide if it's simple drop or drop with cascade call if self.cmd == 'delete': # This is a cascade operation cascade = True else: cascade = False try: for coid in data['ids']: SQL = render_template("/".join( [self.template_path, 'get_name.sql']), scid=scid, coid=coid) 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 collation object in the database." )) data = res['rows'][0] SQL = render_template("/".join( [self.template_path, 'delete.sql']), name=data['name'], nspname=data['schema'], cascade=cascade, conn=self.conn) # Used for schema diff tool if only_sql: return SQL status, res = self.conn.execute_scalar(SQL) if not status: return internal_server_error(errormsg=res) return make_json_response(success=1, info=gettext("Collation dropped")) except Exception as e: return internal_server_error(errormsg=str(e))
def get_sql(self, data, tid, fkid=None): """ This function will generate sql from model data. Args: data: Contains the data of the selected foreign key constraint. tid: Table ID. fkid: Foreign key constraint ID Returns: """ if fkid is not None: sql = render_template("/".join( [self.template_path, 'properties.sql']), tid=tid, cid=fkid) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone(_("""Could not find the foreign key.""")) old_data = res['rows'][0] required_args = ['name'] for arg in required_args: if arg not in data: data[arg] = old_data[arg] sql = render_template("/".join([self.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([self.template_path, 'get_constraint_cols.sql']), tid=tid, keys=zip(old_data['confkey'], old_data['conkey']), confrelid=old_data['confrelid']) status, res = self.conn.execute_dict(col_sql) if not status: return internal_server_error(errormsg=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( [self.template_path, 'create_index.sql']), data=data, conn=self.conn) else: required_args = ['columns'] for arg in required_args: if arg not in data: return _('-- definition incomplete') elif isinstance(data[arg], list) and len(data[arg]) < 1: return _('-- definition incomplete') if data['autoindex'] and \ ('coveringindex' not in data or data['coveringindex'] == ''): return _('-- definition incomplete') SQL = render_template("/".join( [self.template_path, 'get_parent.sql']), tid=data['columns'][0]['references']) status, rset = self.conn.execute_2darray(SQL) if not status: return internal_server_error(errormsg=rset) data['remote_schema'] = rset['rows'][0]['schema'] data['remote_table'] = rset['rows'][0]['table'] sql = render_template("/".join([self.template_path, 'create.sql']), data=data, conn=self.conn) if data['autoindex']: sql += render_template("/".join( [self.template_path, 'create_index.sql']), data=data, conn=self.conn) return sql, data['name'] if 'name' in data else old_data['name']
def sql(self, gid, sid, did, lid, json_resp=True): """ This function will generate sql to show in the sql pane for the selected language node. Args: gid: Server Group ID sid: Server ID did: Database ID lid: Language ID json_resp: """ sql = render_template( "/".join([self.template_path, self._PROPERTIES_SQL]), lid=lid ) 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_LANG_INFORMATION) # Making copy of output for future use old_data = dict(res['rows'][0]) sql = render_template( "/".join([self.template_path, self._ACL_SQL]), lid=lid ) status, result = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=result) for row in result['rows']: priv = parse_priv_from_db(row) if row['deftype'] in old_data: old_data[row['deftype']].append(priv) else: old_data[row['deftype']] = [priv] # To format privileges if 'lanacl' in old_data: old_data['lanacl'] = parse_priv_to_db( old_data['lanacl'], ['U'] ) seclabels = [] if 'seclabels' in old_data and old_data['seclabels'] is not None: import re for sec in old_data['seclabels']: sec = re.search(r'([^=]+)=(.*$)', sec) seclabels.append({ 'provider': sec.group(1), 'label': sec.group(2) }) old_data['seclabels'] = seclabels sql = render_template( "/".join([self.template_path, 'sqlpane.sql']), data=old_data, conn=self.conn ) if not json_resp: return sql.strip('\n') return ajax_response(response=sql.strip('\n'))
def get_sql(self, gid, sid, did, scid, data, foid=None): """ Genrates the SQL statements to create/update the Foreign Table. Args: gid: Server Group Id sid: Server Id did: Database Id scid: Schema Id foid: Foreign Table Id """ try: if foid is not None: old_data = self._fetch_properties(gid, sid, did, scid, foid, inherits=True) if not old_data: return gone(gettext(""" Could not find the foreign table in the database. It may have been removed by another user or shifted to the another schema. """)) # Prepare dict of columns with key = column's attnum # Will use this in the update template when any column is # changed, to identify the columns. col_data = {} for c in old_data['columns']: col_data[c['attnum']] = c old_data['columns'] = col_data if 'columns' in data and 'added' in data['columns']: data['columns']['added'] = self._format_columns( data['columns']['added']) if 'columns' in data and 'changed' in data['columns']: data['columns']['changed'] = self._format_columns( data['columns']['changed']) # Parse Column Options for c in data['columns']['changed']: old_col_options = c['attfdwoptions'] if ('attfdwoptions' in c and c['attfdwoptions']) else [] old_col_frmt_options = {} for o in old_col_options: col_opt = o.split("=") old_col_frmt_options[col_opt[0]] = col_opt[1] c['coloptions_updated'] = {'added': [], 'changed': [], 'deleted': []} if 'coloptions' in c and len(c['coloptions']) > 0: for o in c['coloptions']: if o['option'] in old_col_frmt_options and o['value'] != old_col_frmt_options[ o['option']]: c['coloptions_updated']['changed'].append(o) elif o['option'] not in old_col_frmt_options: c['coloptions_updated']['added'].append(o) if o['option'] in old_col_frmt_options: del old_col_frmt_options[o['option']] for o in old_col_frmt_options: c['coloptions_updated']['deleted'].append({'option': o}) # Parse Privileges if 'acl' in data and 'added' in data['acl']: data['acl']['added'] = parse_priv_to_db(data['acl']['added'], ["a", "r", "w", "x"]) if 'acl' in data and 'changed' in data['acl']: data['acl']['changed'] = parse_priv_to_db( data['acl']['changed'], ["a", "r", "w", "x"]) if 'acl' in data and 'deleted' in data['acl']: data['acl']['deleted'] = parse_priv_to_db( data['acl']['deleted'], ["a", "r", "w", "x"]) SQL = render_template( "/".join([self.template_path, 'update.sql']), data=data, o_data=old_data ) else: data['columns'] = self._format_columns(data['columns']) # Parse Privileges if 'acl' in data: data['acl'] = parse_priv_to_db(data['acl'], ["a", "r", "w", "x"]) SQL = render_template("/".join([self.template_path, 'create.sql']), data=data) return True, SQL except Exception as e: return False, e
def sql(self, gid, sid, did, scid, tid, cid=None): """ This function generates sql to show in the sql pane for the selected primary key. Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID tid: Table ID cid: Primary key constraint ID Returns: """ SQL = render_template("/".join( [self.template_path, self._PROPERTIES_SQL]), did=did, tid=tid, conn=self.conn, cid=cid, constraint_type=self.constraint_type) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone( _("""Could not find the {} in the table.""").format( _("primary key") if self.constraint_type == "p" else _("unique key"))) data = res['rows'][0] data['schema'] = self.schema data['table'] = self.table sql = render_template("/".join( [self.template_path, 'get_constraint_cols.sql']), cid=cid, colcnt=data['col_count']) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) columns = [] for row in res['rows']: columns.append({"column": row['column'].strip('"')}) data['columns'] = columns # Add Include details of the index supported for PG-11+ if self.manager.version >= 110000: sql = render_template("/".join( [self.template_path, 'get_constraint_include.sql']), cid=cid) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) data['include'] = [col['colname'] for col in res['rows']] SQL = render_template("/".join([self.template_path, self._CREATE_SQL]), data=data, constraint_name=self.constraint_name) sql_header = u"-- Constraint: {0}\n\n-- ".format(data['name']) sql_header += render_template("/".join( [self.template_path, self._DELETE_SQL]), data=data) sql_header += "\n" SQL = sql_header + SQL return ajax_response(response=SQL)
def get_sql(self, gid, sid, did, scid, data, cfgid=None): """ This function will return SQL for model data :param gid: group id :param sid: server id :param did: database id :param scid: schema id :param cfgid: fts Configuration id """ # Fetch sql for update if cfgid is not None: sql = render_template("/".join( [self.template_path, 'properties.sql']), cfgid=cfgid, scid=scid) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) elif len(res['rows']) == 0: return gone(_("Could not find the FTS Configuration node.")) old_data = res['rows'][0] if 'schema' not in data: data['schema'] = old_data['schema'] # If user has changed the schema then fetch new schema directly # using its oid otherwise fetch old schema name using its oid sql = render_template("/".join([self.template_path, 'schema.sql']), data=data) status, new_schema = self.conn.execute_scalar(sql) if not status: return internal_server_error(errormsg=new_schema) new_data = data.copy() # Replace schema oid with schema name self._replace_schema_oid_with_schema_name(new_schema, new_data) # Fetch old schema name using old schema oid sql = render_template("/".join([self.template_path, 'schema.sql']), data=old_data) status, old_schema = self.conn.execute_scalar(sql) if not status: return internal_server_error(errormsg=old_schema) # Replace old schema oid with old schema name old_data['schema'] = old_schema sql = render_template("/".join([self.template_path, 'update.sql']), data=new_data, o_data=old_data) # Fetch sql query for modified data if 'name' in data: return sql.strip('\n'), data['name'] return sql.strip('\n'), old_data['name'] else: # Fetch schema name from schema oid sql = render_template("/".join([self.template_path, 'schema.sql']), data=data) status, schema = self.conn.execute_scalar(sql) if not status: return internal_server_error(errormsg=schema) sql = self._get_sql_for_create(data, schema) return sql.strip('\n'), data['name']
def create(self, gid, sid, did, scid, tid, cid=None): """ This function will create a primary key. Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID tid: Table ID cid: Check constraint ID Returns: """ required_args = ['consrc'] data = request.form if request.form else json.loads(request.data, encoding='utf-8') for k, v in data.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 ('comment', ): data[k] = v else: data[k] = json.loads(v, encoding='utf-8') except (ValueError, TypeError, KeyError): data[k] = v for arg in required_args: if arg not in data or data[arg] == '': return make_json_response( status=400, success=0, errormsg=_("Could not find the required parameter ({})." ).format(arg)) data['schema'] = self.schema data['table'] = self.table # Checking whether the table is deleted via query tool if len(data['table']) == 0: return gone(_("The specified table could not be found.")) try: if 'name' not in data or data['name'] == "": SQL = "BEGIN;" # Start transaction. status, res = self.conn.execute_scalar(SQL) if not status: self.end_transaction() return internal_server_error(errormsg=res) # The below SQL will execute CREATE DDL only SQL = render_template("/".join([self.template_path, 'create.sql']), data=data) status, msg = self.conn.execute_scalar(SQL) if not status: self.end_transaction() return internal_server_error(errormsg=msg) if 'name' not in data or data['name'] == "": sql = render_template("/".join( [self.template_path, 'get_oid_with_transaction.sql'], ), tid=tid) status, res = self.conn.execute_dict(sql) if not status: self.end_transaction() return internal_server_error(errormsg=res) self.end_transaction() data['name'] = res['rows'][0]['name'] else: sql = render_template("/".join( [self.template_path, 'get_oid.sql']), tid=tid, name=data['name']) status, res = self.conn.execute_dict(sql) if not status: self.end_transaction() return internal_server_error(errormsg=res) if "convalidated" in res['rows'][0] and \ res['rows'][0]["convalidated"]: icon = "icon-check_constraint_bad" valid = False else: icon = "icon-check_constraint" valid = True return jsonify(node=self.blueprint.generate_browser_node( res['rows'][0]['oid'], tid, data['name'], icon=icon, valid=valid)) except Exception as e: self.end_transaction() return make_json_response(status=400, success=0, errormsg=e)
def _fetch_properties(self, did, lid): """ This function fetch the properties of the extension. :param did: :param lid: :return: """ sql = render_template( "/".join([self.template_path, self._PROPERTIES_SQL]), lid=lid ) 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_LANG_INFORMATION) res['rows'][0]['is_sys_obj'] = ( res['rows'][0]['oid'] <= self.datlastsysoid) sql = render_template( "/".join([self.template_path, self._ACL_SQL]), lid=lid ) status, result = self.conn.execute_dict(sql) if not status: return False, internal_server_error(errormsg=result) # if no acl found then by default add public if res['rows'][0]['acl'] is None: res['rows'][0]['lanacl'] = dict() res['rows'][0]['lanacl']['grantee'] = 'PUBLIC' res['rows'][0]['lanacl']['grantor'] = res['rows'][0]['lanowner'] res['rows'][0]['lanacl']['privileges'] = [ { 'privilege_type': 'U', 'privilege': True, 'with_grant': False } ] else: for row in result['rows']: priv = parse_priv_from_db(row) if row['deftype'] in res['rows'][0]: res['rows'][0][row['deftype']].append(priv) else: res['rows'][0][row['deftype']] = [priv] seclabels = [] if 'seclabels' in res['rows'][0] and \ res['rows'][0]['seclabels'] is not None: import re for sec in res['rows'][0]['seclabels']: sec = re.search(r'([^=]+)=(.*$)', sec) seclabels.append({ 'provider': sec.group(1), 'label': sec.group(2) }) res['rows'][0]['seclabels'] = seclabels return True, res['rows'][0]
def getSQL(self, gid, sid, did, data, scid, pkgid=None, sqltab=False, diff_schema=None): """ This function will generate sql from model data. Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID pkgid: Package ID sqltab: True diff_schema: Target Schema """ required_args = [u'name'] if diff_schema: data['schema'] = diff_schema else: data['schema'] = self.schema if pkgid is not None and not sqltab: SQL = render_template("/".join( [self.template_path, 'properties.sql']), scid=scid, pkgid=pkgid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone( errormsg=_("Could not find the package in the database.")) res['rows'][0]['pkgheadsrc'] = self.get_inner( res['rows'][0]['pkgheadsrc']) res['rows'][0]['pkgbodysrc'] = self.get_inner( res['rows'][0]['pkgbodysrc']) SQL = render_template("/".join([self.template_path, 'acl.sql']), scid=scid, pkgid=pkgid) status, rset1 = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=rset1) for row in rset1['rows']: priv = parse_priv_from_db(row) res['rows'][0].setdefault(row['deftype'], []).append(priv) # Making copy of output for further processing old_data = dict(res['rows'][0]) # To format privileges data coming from client for key in ['pkgacl']: if key in data and data[key] is not None: if 'added' in data[key]: data[key]['added'] = parse_priv_to_db( data[key]['added'], self.acl) if 'changed' in data[key]: data[key]['changed'] = parse_priv_to_db( data[key]['changed'], self.acl) if 'deleted' in data[key]: data[key]['deleted'] = parse_priv_to_db( data[key]['deleted'], self.acl) # If name is not present with in update data then copy it # from old data for arg in required_args: if arg not in data: data[arg] = old_data[arg] SQL = render_template("/".join([self.template_path, 'update.sql']), data=data, o_data=old_data, conn=self.conn, is_schema_diff=diff_schema) return SQL, data['name'] if 'name' in data else old_data['name'] else: # To format privileges coming from client if 'pkgacl' in data: data['pkgacl'] = parse_priv_to_db(data['pkgacl'], self.acl) SQL = render_template("/".join([self.template_path, 'create.sql']), data=data, conn=self.conn) return SQL, data['name']
def properties(self, gid, sid, did, scid, tid): """ This function will show the properties of the selected table node. Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID scid: Schema ID tid: Table ID Returns: JSON of selected table node """ SQL = render_template( "/".join([self.table_template_path, 'properties.sql']), did=did, scid=scid, tid=tid, 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("The specified table could not be found.")) # We will check the threshold set by user before executing # the query because that can cause performance issues # with large result set pref = Preferences.module('browser') table_row_count_pref = pref.preference('table_row_count_threshold') table_row_count_threshold = table_row_count_pref.get() estimated_row_count = int(res['rows'][0].get('reltuples', 0)) # If estimated rows are greater than threshold then if estimated_row_count and \ estimated_row_count > table_row_count_threshold: res['rows'][0]['rows_cnt'] = str(table_row_count_threshold) + '+' # If estimated rows is lower than threshold then calculate the count elif estimated_row_count and \ table_row_count_threshold >= estimated_row_count: SQL = render_template( "/".join( [self.table_template_path, 'get_table_row_count.sql'] ), data=res['rows'][0] ) status, count = self.conn.execute_scalar(SQL) if not status: return internal_server_error(errormsg=count) res['rows'][0]['rows_cnt'] = count # If estimated_row_count is zero then set the row count with same elif not estimated_row_count: res['rows'][0]['rows_cnt'] = estimated_row_count return super(TableView, self).properties( gid, sid, did, scid, tid, res )
def sql(self, gid, sid, did, scid, tid, trid): """ This function will generates reverse engineered sql for trigger object Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID tid: Table ID trid: Trigger ID """ 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 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 data = self.get_trigger_function_schema(data) if len(data['custom_tgargs']) > 1: # We know that trigger has more than 1 argument, let's join them data['tgargs'] = self._format_args(data['custom_tgargs']) if len(data['tgattr']) >= 1: columns = ', '.join(data['tgattr'].split(' ')) data['columns'] = self._column_details(tid, columns) data = self._trigger_definition(data) SQL, name = self.get_sql(scid, tid, None, data) sql_header = u"-- Trigger: {0}\n\n-- ".format(data['name']) sql_header += render_template("/".join( [self.template_path, 'delete.sql']), data=data, conn=self.conn) SQL = sql_header + '\n\n' + SQL.strip('\n') # If 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 ajax_response(response=SQL)
def sql(self, gid, sid, did, scid, pkgid, diff_schema=None, json_resp=True): """ This function will generate sql for sql panel Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID pkgid: Package ID diff_schema: Schema diff target schema name json_resp: json response or plain text response """ try: SQL = render_template("/".join( [self.template_path, 'properties.sql']), scid=scid, pkgid=pkgid) status, res = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone( errormsg=_("Could not find the package in the database.")) res['rows'][0]['pkgheadsrc'] = self.get_inner( res['rows'][0]['pkgheadsrc']) res['rows'][0]['pkgbodysrc'] = self.get_inner( res['rows'][0]['pkgbodysrc']) SQL = render_template("/".join([self.template_path, 'acl.sql']), scid=scid, pkgid=pkgid) status, rset1 = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=rset1) for row in rset1['rows']: priv = parse_priv_from_db(row) res['rows'][0].setdefault(row['deftype'], []).append(priv) result = res['rows'][0] sql, name = self.getSQL(gid, sid, did, result, scid, pkgid, True, diff_schema) # Most probably this is due to error if not isinstance(sql, str): return sql sql = sql.strip('\n').strip(' ') # Return sql for schema diff if not json_resp: return sql sql_header = u"-- Package: {0}.{1}\n\n-- ".format( self.schema, result['name']) sql_header += render_template("/".join( [self.template_path, 'delete.sql']), data=result) sql_header += "\n\n" sql = sql_header + sql return ajax_response(response=sql) except Exception as e: return internal_server_error(errormsg=str(e))
def sql(self, gid, sid, did, scid, tid, exid=None): """ This function generates sql to show in the sql pane for the selected Exclusion constraint. Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID tid: Table ID exid: Exclusion constraint ID Returns: """ try: SQL = render_template("/".join( [self.template_path, 'properties.sql']), did=did, tid=tid, conn=self.conn, cid=exid) status, result = self.conn.execute_dict(SQL) if not status: return internal_server_error(errormsg=result) if len(result['rows']) == 0: return gone(_("Could not find the exclusion constraint.")) data = result['rows'][0] data['schema'] = self.schema data['table'] = self.table sql = render_template("/".join( [self.template_path, 'get_constraint_cols.sql']), cid=exid, colcnt=data['col_count']) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) columns = [] for row in res['rows']: if row['options'] & 1: order = False nulls_order = True if (row['options'] & 2) else False else: order = True nulls_order = True if (row['options'] & 2) else False columns.append({ "column": row['coldef'].strip('"'), "oper_class": row['opcname'], "order": order, "nulls_order": nulls_order, "operator": row['oprname'] }) data['columns'] = columns # Add Include details of the index supported for PG-11+ if self.manager.version >= 110000: sql = render_template("/".join( [self.template_path, 'get_constraint_include.sql']), cid=exid) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) data['include'] = [col['colname'] for col in res['rows']] if not data['amname'] or data['amname'] == '': data['amname'] = 'btree' SQL = render_template("/".join([self.template_path, 'create.sql']), data=data) sql_header = u"-- Constraint: {0}\n\n-- ".format(data['name']) sql_header += render_template("/".join( [self.template_path, 'delete.sql']), data=data) sql_header += "\n" SQL = sql_header + SQL return ajax_response(response=SQL) except Exception as e: return internal_server_error(errormsg=str(e))
def enable_disable_trigger(self, gid, sid, did, scid, tid, trid): """ This function will enable OR disable the current 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') # Convert str 'true' to boolean type is_enable_flag = json.loads(data['enable']) try: 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 trigger in the table.""")) o_data = dict(res['rows'][0]) # If enable is set to true means we need SQL to enable # current trigger which is disabled already so we need to # alter the 'is_enable_trigger' flag so that we can render # correct SQL for operation o_data['is_enable_trigger'] = is_enable_flag # Adding parent into data dict, will be using it while creating sql o_data['schema'] = self.schema o_data['table'] = self.table SQL = render_template("/".join( [self.template_path, 'enable_disable_trigger.sql']), data=o_data, conn=self.conn) status, res = self.conn.execute_scalar(SQL) if not status: return internal_server_error(errormsg=res) return make_json_response(success=1, info="Trigger updated", data={ 'id': trid, 'tid': tid, 'scid': scid }) except Exception as e: return internal_server_error(errormsg=str(e))
def sql(self, gid, sid, did, fid, json_resp=True): """ This function will generate sql to show it in sql pane for the selected foreign data wrapper node. Args: gid: Server Group ID sid: Server ID did: Database ID fid: Foreign data wrapper ID json_resp: """ sql = render_template("/".join( [self.template_path, self._PROPERTIES_SQL]), fid=fid, conn=self.conn) 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 foreign data wrapper on the server.")) is_valid_options = False if res['rows'][0]['fdwoptions'] is not None: res['rows'][0]['fdwoptions'] = tokenize_options( res['rows'][0]['fdwoptions'], 'fdwoption', 'fdwvalue') if len(res['rows'][0]['fdwoptions']) > 0: is_valid_options = True sql = render_template("/".join([self.template_path, self._ACL_SQL]), fid=fid) status, fdw_acl_res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=fdw_acl_res) for row in fdw_acl_res['rows']: privilege = parse_priv_from_db(row) if row['deftype'] in res['rows'][0]: res['rows'][0][row['deftype']].append(privilege) else: res['rows'][0][row['deftype']] = [privilege] # To format privileges if 'fdwacl' in res['rows'][0]: res['rows'][0]['fdwacl'] = parse_priv_to_db( res['rows'][0]['fdwacl'], ['U']) sql = '' sql = render_template("/".join([self.template_path, self._CREATE_SQL]), data=res['rows'][0], conn=self.conn, is_valid_options=is_valid_options) sql += "\n" sql_header = """-- Foreign Data Wrapper: {0}\n\n""".format( res['rows'][0]['name']) sql_header += """-- DROP FOREIGN DATA WRAPPER IF EXISTS {0} """.format(self.qtIdent(self.conn, res['rows'][0]['name'])) sql = sql_header + sql if not json_resp: return sql.strip('\n') return ajax_response(response=sql.strip('\n'))
def update(self, gid, sid, did, scid, tid, trid): """ This function will updates existing the 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 = self.get_sql(scid, tid, trid, data) if not isinstance(SQL, (str, unicode)): 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 trigger then new OID is getting generated # so we need to return new OID of trigger. SQL = render_template("/".join([self.template_path, 'get_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, '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( gettext("""Could not find the trigger in the table.""")) # 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" % self.node_type if data['is_enable_trigger'] else "icon-%s-bad" % self.node_type)) except Exception as e: return internal_server_error(errormsg=str(e))
def create(self, gid, sid, did, scid, tid): """ This function will creates new the trigger object Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID tid: Table ID """ data = request.form if request.form else json.loads(request.data, encoding='utf-8') for k, v in data.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, TypeError, KeyError): data[k] = v required_args = {'name': 'Name', 'tfunction': 'Trigger function'} for arg in required_args: if arg not in data: return make_json_response( status=410, success=0, errormsg=gettext( "Could not find the required parameter ({}).").format( required_args[arg])) # Adding parent into data dict, will be using it while creating sql data['schema'] = self.schema data['table'] = self.table if len(data['table']) == 0: return gone(self.not_found_error_msg()) try: SQL = render_template("/".join( [self.template_path, self._CREATE_SQL]), data=data, conn=self.conn) status, res = self.conn.execute_scalar(SQL) if not status: return internal_server_error(errormsg=res) # we need oid to add object in tree at browser SQL = render_template("/".join([self.template_path, self._OID_SQL]), tid=tid, data=data) status, trid = self.conn.execute_scalar(SQL) if not status: return internal_server_error(errormsg=tid) return jsonify(node=self.blueprint.generate_browser_node( trid, tid, data['name'], icon="icon-trigger")) except Exception as e: return internal_server_error(errormsg=str(e))
def statistics(self, gid, sid, did, scid, tid, cid): """ Statistics Args: gid: Server Group ID sid: Server ID did: Database ID scid: Schema ID tid: Table ID cid: Primary key/Unique constraint ID Returns the statistics for a particular object if cid is specified """ # Check if pgstattuple extension is already created? # if created then only add extended stats status, is_pgstattuple = self.conn.execute_scalar(""" SELECT (count(extname) > 0) AS is_pgstattuple FROM pg_extension WHERE extname='pgstattuple' """) if not status: return internal_server_error(errormsg=is_pgstattuple) if is_pgstattuple: # Fetch index details only if extended stats available sql = render_template( "/".join([self.template_path, 'properties.sql']), did=did, tid=tid, cid=cid, constraint_type=self.constraint_type ) status, res = self.conn.execute_dict(sql) if not status: return internal_server_error(errormsg=res) if len(res['rows']) == 0: return gone( _("""Could not find the {} in the table.""").format( _("primary key") if self.constraint_type == "p" else _("unique key") ) ) result = res['rows'][0] name = result['name'] else: name = None status, res = self.conn.execute_dict( render_template( "/".join([self.template_path, 'stats.sql']), conn=self.conn, schema=self.schema, name=name, cid=cid, is_pgstattuple=is_pgstattuple ) ) if not status: return internal_server_error(errormsg=res) return make_json_response( data=res, status=200 )
def get_children_nodes(self, manager, **kwargs): """ Function is used to get the child nodes. :param manager: :param kwargs: :return: """ nodes = [] scid = kwargs.get('scid') tid = kwargs.get('tid') trid = kwargs.get('trid') try: SQL = render_template("/".join( [self.template_path, 'get_function_oid.sql']), tid=tid, trid=trid) status, rset = self.conn.execute_2darray(SQL) if not status: return internal_server_error(errormsg=rset) if len(rset['rows']) == 0: return gone( gettext("Could not find the specified trigger function")) # For language EDB SPL we should not display any node. if rset['rows'][0]['lanname'] != 'edbspl': trigger_function_schema_oid = rset['rows'][0]['tfuncschoid'] sql = render_template("/".join( [self.trigger_function_template_path, self._NODE_SQL]), scid=trigger_function_schema_oid, fnid=rset['rows'][0]['tfuncoid']) status, res = self.conn.execute_2darray(sql) if not status: return internal_server_error(errormsg=rset) if len(res['rows']) == 0: return gone( gettext( "Could not find the specified trigger function")) row = res['rows'][0] func_name = row['name'] # If trigger function is from another schema then we should # display the name as schema qulified name. if scid != trigger_function_schema_oid: func_name = \ rset['rows'][0]['tfuncschema'] + '.' + row['name'] trigger_func = current_app.blueprints['NODE-trigger_function'] nodes.append( trigger_func.generate_browser_node( row['oid'], trigger_function_schema_oid, gettext(func_name), icon="icon-trigger_function", funcowner=row['funcowner'], language=row['lanname'], inode=False)) except Exception as e: return internal_server_error(errormsg=str(e)) return nodes
def get_sql(self, gid, sid, did, scid, data, tid=None): """ This function will return SQL for model data :param gid: group id :param sid: server id :param did: database id :param scid: schema id :param data: sql data :param tid: fts tempate id """ # Fetch sql for update if tid is not None: sql = render_template( "/".join([self.template_path, self._PROPERTIES_SQL]), tid=tid, scid=scid ) 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()) old_data = res['rows'][0] if 'schema' not in data: data['schema'] = old_data['schema'] # If user has changed the schema then fetch new schema directly # using its oid otherwise fetch old schema name using # fts template oid sql = render_template( "/".join([self.template_path, self._SCHEMA_SQL]), data=data) status, new_schema = self.conn.execute_scalar(sql) if not status: return internal_server_error(errormsg=new_schema) # Replace schema oid with schema name new_data = data.copy() error, errmsg = self._replace_schema_oid_with_name(new_schema, old_data, new_data) if error: print('ERROR INSIDE UPDATE:: {0}'.format(errmsg)) return internal_server_error(errormsg=errmsg) sql = render_template( "/".join([self.template_path, self._UPDATE_SQL]), data=new_data, o_data=old_data ) # Fetch sql query for modified data if 'name' in data: return sql.strip('\n'), data['name'] return sql.strip('\n'), old_data['name'] else: # Fetch schema name from schema oid sql = render_template("/".join([self.template_path, self._SCHEMA_SQL]), data=data) status, schema = self.conn.execute_scalar(sql) if not status: return internal_server_error(errormsg=schema) sql = self._get_sql_for_create(data, schema) return sql.strip('\n'), data['name']