codekingpro/portable-devtools
114k
1##########################################################################2#3# pgAdmin 4 - PostgreSQL Tools4#5# Copyright (C) 2013 - 2024, The pgAdmin Development Team6# This software is released under the PostgreSQL Licence7#8##########################################################################9 10"""A blueprint module implementing the dashboard frame."""11import math12from functools import wraps13from flask import render_template, url_for, Response, g, request14from flask_babel import gettext15from pgadmin.user_login_check import pga_login_required16import json17from pgadmin.utils import PgAdminModule18from pgadmin.utils.ajax import make_response as ajax_response,\19 internal_server_error20from pgadmin.utils.ajax import precondition_required21from pgadmin.utils.driver import get_driver22from pgadmin.utils.menu import Panel23from pgadmin.utils.preferences import Preferences24from pgadmin.utils.constants import PREF_LABEL_DISPLAY, MIMETYPE_APP_JS, \25 PREF_LABEL_REFRESH_RATES26 27from config import PG_DEFAULT_DRIVER28 29MODULE_NAME = 'dashboard'30 31 32class DashboardModule(PgAdminModule):33 def __init__(self, *args, **kwargs):34 super().__init__(*args, **kwargs)35 36 def get_own_menuitems(self):37 return {}38 39 def register_preferences(self):40 """41 register_preferences42 Register preferences for this module.43 """44 help_string = gettext('The number of seconds between graph samples.')45 46 # Register options for Dashboards47 self.dashboard_preference = Preferences(48 'dashboards', gettext('Dashboards')49 )50 51 self.session_stats_refresh = self.dashboard_preference.register(52 'dashboards', 'session_stats_refresh',53 gettext("Session statistics refresh rate"), 'integer',54 5, min_val=1, max_val=999999,55 category_label=PREF_LABEL_REFRESH_RATES,56 help_str=help_string57 )58 59 self.tps_stats_refresh = self.dashboard_preference.register(60 'dashboards', 'tps_stats_refresh',61 gettext("Transaction throughput refresh rate"), 'integer',62 5, min_val=1, max_val=999999,63 category_label=PREF_LABEL_REFRESH_RATES,64 help_str=help_string65 )66 67 self.ti_stats_refresh = self.dashboard_preference.register(68 'dashboards', 'ti_stats_refresh',69 gettext("Tuples in refresh rate"), 'integer',70 5, min_val=1, max_val=999999,71 category_label=PREF_LABEL_REFRESH_RATES,72 help_str=help_string73 )74 75 self.to_stats_refresh = self.dashboard_preference.register(76 'dashboards', 'to_stats_refresh',77 gettext("Tuples out refresh rate"), 'integer',78 5, min_val=1, max_val=999999,79 category_label=PREF_LABEL_REFRESH_RATES,80 help_str=help_string81 )82 83 self.bio_stats_refresh = self.dashboard_preference.register(84 'dashboards', 'bio_stats_refresh',85 gettext("Block I/O statistics refresh rate"), 'integer',86 5, min_val=1, max_val=999999,87 category_label=PREF_LABEL_REFRESH_RATES,88 help_str=help_string89 )90 91 self.hpc_stats_refresh = self.dashboard_preference.register(92 'dashboards', 'hpc_stats_refresh',93 gettext("Handle & Process count statistics refresh rate"),94 'integer', 5, min_val=1, max_val=999999,95 category_label=PREF_LABEL_REFRESH_RATES,96 help_str=help_string97 )98 99 self.cpu_stats_refresh = self.dashboard_preference.register(100 'dashboards', 'cpu_stats_refresh',101 gettext(102 "Percentage of CPU time used by different process \103 modes statistics refresh rate"104 ), 'integer', 5, min_val=1, max_val=999999,105 category_label=PREF_LABEL_REFRESH_RATES,106 help_str=help_string107 )108 109 self.la_stats_refresh = self.dashboard_preference.register(110 'dashboards', 'la_stats_refresh',111 gettext("Average load statistics refresh rate"), 'integer',112 5, min_val=1, max_val=999999,113 category_label=PREF_LABEL_REFRESH_RATES,114 help_str=help_string115 )116 117 self.pcpu_stats_refresh = self.dashboard_preference.register(118 'dashboards', 'pcpu_stats_refresh',119 gettext("CPU usage per process statistics refresh rate"),120 'integer', 5, min_val=1, max_val=999999,121 category_label=PREF_LABEL_REFRESH_RATES,122 help_str=help_string123 )124 125 self.m_stats_refresh = self.dashboard_preference.register(126 'dashboards', 'm_stats_refresh',127 gettext("Memory usage statistics refresh rate"), 'integer',128 5, min_val=1, max_val=999999,129 category_label=PREF_LABEL_REFRESH_RATES,130 help_str=help_string131 )132 133 self.sm_stats_refresh = self.dashboard_preference.register(134 'dashboards', 'sm_stats_refresh',135 gettext("Swap memory usage statistics refresh rate"), 'integer',136 5, min_val=1, max_val=999999,137 category_label=PREF_LABEL_REFRESH_RATES,138 help_str=help_string139 )140 141 self.pmu_stats_refresh = self.dashboard_preference.register(142 'dashboards', 'pmu_stats_refresh',143 gettext("Memory usage per process statistics refresh rate"),144 'integer', 5, min_val=1, max_val=999999,145 category_label=PREF_LABEL_REFRESH_RATES,146 help_str=help_string147 )148 149 self.io_stats_refresh = self.dashboard_preference.register(150 'dashboards', 'io_stats_refresh',151 gettext("I/O analysis statistics refresh rate"), 'integer',152 5, min_val=1, max_val=999999,153 category_label=PREF_LABEL_REFRESH_RATES,154 help_str=help_string155 )156 157 self.display_graphs = self.dashboard_preference.register(158 'display', 'show_graphs',159 gettext("Show graphs?"), 'boolean', True,160 category_label=PREF_LABEL_DISPLAY,161 help_str=gettext('If set to True, graphs '162 'will be displayed on dashboards.')163 )164 165 self.display_server_activity = self.dashboard_preference.register(166 'display', 'show_activity',167 gettext("Show activity?"), 'boolean', True,168 category_label=PREF_LABEL_DISPLAY,169 help_str=gettext('If set to True, activity tables '170 'will be displayed on dashboards.')171 )172 173 self.long_running_query_threshold = self.dashboard_preference.register(174 'display', 'long_running_query_threshold',175 gettext('Long running query thresholds'), 'threshold',176 '2|5', category_label=PREF_LABEL_DISPLAY,177 help_str=gettext('Set the warning and alert threshold value to '178 'highlight the long-running queries on the '179 'dashboard.')180 )181 182 # Register options for Graphs183 self.graphs_preference = Preferences(184 'graphs', gettext('Graphs')185 )186 187 self.graph_data_points = self.graphs_preference.register(188 'graphs', 'graph_data_points',189 gettext("Show graph data points?"), 'boolean', False,190 category_label=PREF_LABEL_DISPLAY,191 help_str=gettext('If set to True, data points will be '192 'visible on graph lines.')193 )194 195 self.use_diff_point_style = self.graphs_preference.register(196 'graphs', 'use_diff_point_style',197 gettext("Use different data point styles?"), 'boolean', False,198 category_label=PREF_LABEL_DISPLAY,199 help_str=gettext('If set to True, data points will be visible '200 'in a different style on each graph lines.')201 )202 203 self.graph_mouse_track = self.graphs_preference.register(204 'graphs', 'graph_mouse_track',205 gettext("Show mouse hover tooltip?"), 'boolean', True,206 category_label=PREF_LABEL_DISPLAY,207 help_str=gettext('If set to True, tooltip will appear on mouse '208 'hover on the graph lines giving the data point '209 'details')210 )211 212 self.graph_line_border_width = self.graphs_preference.register(213 'graphs', 'graph_line_border_width',214 gettext("Chart line width"), 'integer',215 1, min_val=1, max_val=10,216 category_label=PREF_LABEL_DISPLAY,217 help_str=gettext('Set the width of the lines on the line chart.')218 )219 220 def get_exposed_url_endpoints(self):221 """222 Returns:223 list: a list of url endpoints exposed to the client.224 """225 return [226 'dashboard.index', 'dashboard.get_by_sever_id',227 'dashboard.get_by_database_id',228 'dashboard.dashboard_stats',229 'dashboard.dashboard_stats_sid',230 'dashboard.dashboard_stats_did',231 'dashboard.activity',232 'dashboard.get_activity_by_server_id',233 'dashboard.get_activity_by_database_id',234 'dashboard.locks',235 'dashboard.get_locks_by_server_id',236 'dashboard.get_locks_by_database_id',237 'dashboard.prepared',238 'dashboard.get_prepared_by_server_id',239 'dashboard.get_prepared_by_database_id',240 'dashboard.config',241 'dashboard.get_config_by_server_id',242 'dashboard.check_system_statistics',243 'dashboard.check_system_statistics_sid',244 'dashboard.check_system_statistics_did',245 'dashboard.system_statistics',246 'dashboard.system_statistics_sid',247 'dashboard.system_statistics_did',248 'dashboard.replication_slots',249 'dashboard.replication_stats',250 ]251 252 253blueprint = DashboardModule(MODULE_NAME, __name__)254 255 256def check_precondition(f):257 """258 This function will behave as a decorator which will check259 database connection before running view, it also adds260 manager, conn & template_path properties to self261 """262 263 @wraps(f)264 def wrap(*args, **kwargs):265 # Here args[0] will hold self & kwargs will hold gid,sid,did266 267 g.manager = get_driver(268 PG_DEFAULT_DRIVER).connection_manager(269 kwargs['sid']270 )271 272 def get_error(i_node_type):273 stats_type = ('activity', 'prepared', 'locks', 'config')274 if f.__name__ in stats_type:275 return precondition_required(276 gettext("Please connect to the selected {0}"277 " to view the table.".format(i_node_type))278 )279 else:280 return precondition_required(281 gettext("Please connect to the selected {0}"282 " to view the graph.".format(i_node_type))283 )284 285 # Below check handle the case where existing server is deleted286 # by user and python server will raise exception if this check287 # is not introduce.288 if g.manager is None:289 return get_error('server')290 291 if 'did' in kwargs:292 g.conn = g.manager.connection(did=kwargs['did'])293 node_type = 'database'294 else:295 g.conn = g.manager.connection()296 node_type = 'server'297 298 # If not connected then return error to browser299 if not g.conn.connected():300 return get_error(node_type)301 302 # Set template path for sql scripts303 g.server_type = g.manager.server_type304 g.version = g.manager.version305 306 # Include server_type in template_path307 g.template_path = 'dashboard/sql/' + (308 '#{0}#'.format(g.version)309 )310 311 return f(*args, **kwargs)312 313 return wrap314 315 316@blueprint.route("/dashboard.js")317@pga_login_required318def script():319 """render the required javascript"""320 return Response(321 response=render_template(322 "dashboard/js/dashboard.js",323 _=gettext324 ),325 status=200,326 mimetype=MIMETYPE_APP_JS327 )328 329 330@blueprint.route('/', endpoint='index')331@blueprint.route('/<int:sid>', endpoint='get_by_sever_id')332@blueprint.route('/<int:sid>/<int:did>', endpoint='get_by_database_id')333@pga_login_required334def index(sid=None, did=None):335 """336 Renders the welcome, server or database dashboard337 Args:338 sid: Server ID339 did: Database ID340 341 Returns: Welcome/Server/database dashboard342 343 """344 rates = {}345 346 # Get the server version347 if sid is not None:348 g.manager = get_driver(349 PG_DEFAULT_DRIVER).connection_manager(sid)350 g.conn = g.manager.connection()351 352 g.version = g.manager.version353 354 if not g.conn.connected():355 g.version = 0356 357 # Show the appropriate dashboard based on the identifiers passed to us358 if sid is None and did is None:359 return render_template('/dashboard/welcome_dashboard.html')360 if did is None:361 return render_template(362 '/dashboard/server_dashboard.html',363 sid=sid,364 rates=rates,365 version=g.version366 )367 else:368 return render_template(369 '/dashboard/database_dashboard.html',370 sid=sid,371 did=did,372 rates=rates,373 version=g.version374 )375 376 377def get_data(sid, did, template, check_long_running_query=False):378 """379 Generic function to get server stats based on an SQL template380 Args:381 sid: The server ID382 did: The database ID383 template: The SQL template name384 check_long_running_query:385 386 Returns:387 388 """389 # Allow no server ID to be specified (so we can generate a route in JS)390 # but throw an error if it's actually called.391 if not sid:392 return internal_server_error(errormsg='Server ID not specified.')393 394 sql = render_template(395 "/".join([g.template_path, template]), did=did396 )397 status, res = g.conn.execute_dict(sql)398 399 if not status:400 return internal_server_error(errormsg=res)401 402 # Check the long running query status and set the row type.403 if check_long_running_query:404 get_long_running_query_status(res['rows'])405 406 return ajax_response(407 response=res['rows'],408 status=200409 )410 411 412def get_long_running_query_status(activities):413 """414 This function is used to check the long running query and set the415 row type to highlight the row color accordingly416 """417 dash_preference = Preferences.module('dashboards')418 long_running_query_threshold = \419 dash_preference.preference('long_running_query_threshold').get()420 421 if long_running_query_threshold is not None:422 long_running_query_threshold = long_running_query_threshold.split('|')423 424 warning_value = float(long_running_query_threshold[0]) \425 if long_running_query_threshold[0] != '' else math.inf426 alert_value = float(long_running_query_threshold[1]) \427 if long_running_query_threshold[1] != '' else math.inf428 429 for row in activities:430 row['row_type'] = None431 432 # We care for only those queries which are in active state and433 # have active_since parameter and not None434 if row['state'] == 'active' and 'active_since' in row and \435 row['active_since'] is not None:436 active_since = float(row['active_since'])437 if active_since > warning_value:438 row['row_type'] = 'warning'439 if active_since > alert_value:440 row['row_type'] = 'alert'441 442 443@blueprint.route('/dashboard_stats',444 endpoint='dashboard_stats')445@blueprint.route('/dashboard_stats/<int:sid>',446 endpoint='dashboard_stats_sid')447@blueprint.route('/dashboard_stats/<int:sid>/<int:did>',448 endpoint='dashboard_stats_did')449@pga_login_required450@check_precondition451def dashboard_stats(sid=None, did=None):452 resp_data = {}453 454 if request.args['chart_names'] != '':455 chart_names = request.args['chart_names'].split(',')456 457 if not sid:458 return internal_server_error(errormsg='Server ID not specified.')459 460 sql = render_template(461 "/".join([g.template_path, 'dashboard_stats.sql']), did=did,462 chart_names=chart_names,463 )464 _, res = g.conn.execute_dict(sql)465 466 for chart_row in res['rows']:467 resp_data[chart_row['chart_name']] = json.loads(468 chart_row['chart_data'])469 470 return ajax_response(471 response=resp_data,472 status=200473 )474 475 476@blueprint.route('/activity/', endpoint='activity')477@blueprint.route('/activity/<int:sid>', endpoint='get_activity_by_server_id')478@blueprint.route(479 '/activity/<int:sid>/<int:did>', endpoint='get_activity_by_database_id'480)481@pga_login_required482@check_precondition483def activity(sid=None, did=None):484 """485 This function returns server activity information486 :param sid: server id487 :return:488 """489 return get_data(sid, did, 'activity.sql', True)490 491 492@blueprint.route('/locks/', endpoint='locks')493@blueprint.route('/locks/<int:sid>', endpoint='get_locks_by_server_id')494@blueprint.route(495 '/locks/<int:sid>/<int:did>', endpoint='get_locks_by_database_id'496)497@pga_login_required498@check_precondition499def locks(sid=None, did=None):500 """501 This function returns server lock information502 :param sid: server id503 :return:504 """505 return get_data(sid, did, 'locks.sql')506 507 508@blueprint.route('/prepared/', endpoint='prepared')509@blueprint.route('/prepared/<int:sid>', endpoint='get_prepared_by_server_id')510@blueprint.route(511 '/prepared/<int:sid>/<int:did>', endpoint='get_prepared_by_database_id'512)513@pga_login_required514@check_precondition515def prepared(sid=None, did=None):516 """517 This function returns prepared XACT information518 :param sid: server id519 :return:520 """521 return get_data(sid, did, 'prepared.sql')522 523 524@blueprint.route('/config/', endpoint='config')525@blueprint.route('/config/<int:sid>', endpoint='get_config_by_server_id')526@pga_login_required527@check_precondition528def config(sid=None):529 """530 This function returns server config information531 :param sid: server id532 :return:533 """534 return get_data(sid, None, 'config.sql')535 536 537@blueprint.route(538 '/cancel_query/<int:sid>/<int:pid>', methods=['DELETE']539)540@blueprint.route(541 '/cancel_query/<int:sid>/<int:did>/<int:pid>', methods=['DELETE']542)543@pga_login_required544@check_precondition545def cancel_query(sid=None, did=None, pid=None):546 """547 This function cancel the specific session548 :param sid: server id549 :param did: database id550 :param pid: session/process id551 :return: Response552 """553 sql = "SELECT pg_catalog.pg_cancel_backend({0});".format(pid)554 status, res = g.conn.execute_scalar(sql)555 if not status:556 return internal_server_error(errormsg=res)557 558 return ajax_response(559 response=gettext("Success") if res else gettext("Failed"),560 status=200561 )562 563 564@blueprint.route(565 '/terminate_session/<int:sid>/<int:pid>', methods=['DELETE']566)567@blueprint.route(568 '/terminate_session/<int:sid>/<int:did>/<int:pid>', methods=['DELETE']569)570@pga_login_required571@check_precondition572def terminate_session(sid=None, did=None, pid=None):573 """574 This function terminate the specific session575 :param sid: server id576 :param did: database id577 :param pid: session/process id578 :return: Response579 """580 sql = "SELECT pg_catalog.pg_terminate_backend({0});".format(pid)581 status, res = g.conn.execute_scalar(sql)582 if not status:583 return internal_server_error(errormsg=res)584 585 return ajax_response(586 response=gettext("Success") if res else gettext("Failed"),587 status=200588 )589 590 591# To check whether system stats extesion is present or not592@blueprint.route('check_extension/system_statistics',593 endpoint='check_system_statistics', methods=['GET'])594@blueprint.route('check_extension/system_statistics/<int:sid>',595 endpoint='check_system_statistics_sid', methods=['GET'])596@blueprint.route('check_extension/system_statistics/<int:sid>/<int:did>',597 endpoint='check_system_statistics_did', methods=['GET'])598@pga_login_required599@check_precondition600def check_system_statistics(sid=None, did=None):601 sql = "SELECT * FROM pg_extension WHERE extname = 'system_stats';"602 status, res = g.conn.execute_scalar(sql)603 if not status:604 return internal_server_error(errormsg=res)605 data = {}606 if res is not None:607 data['ss_present'] = True608 else:609 data['ss_present'] = False610 return ajax_response(611 response=data,612 status=200613 )614 615 616# System Statistics Backend617@blueprint.route('/system_statistics',618 endpoint='system_statistics', methods=['GET'])619@blueprint.route('/system_statistics/<int:sid>',620 endpoint='system_statistics_sid', methods=['GET'])621@blueprint.route('/system_statistics/<int:sid>/<int:did>',622 endpoint='system_statistics_did', methods=['GET'])623@pga_login_required624@check_precondition625def system_statistics(sid=None, did=None):626 resp_data = {}627 628 if request.args['chart_names'] != '':629 chart_names = request.args['chart_names'].split(',')630 631 if not sid:632 return internal_server_error(errormsg='Server ID not specified.')633 634 sql = render_template(635 "/".join([g.template_path, 'system_statistics.sql']), did=did,636 chart_names=chart_names,637 )638 status, res = g.conn.execute_dict(sql)639 640 if not status:641 return internal_server_error(errormsg=str(res))642 643 for chart_row in res['rows']:644 resp_data[chart_row['chart_name']] = json.loads(645 chart_row['chart_data'])646 647 return ajax_response(648 response=resp_data,649 status=200650 )651 652 653@blueprint.route('/replication_stats/<int:sid>',654 endpoint='replication_stats', methods=['GET'])655@pga_login_required656@check_precondition657def replication_stats(sid=None):658 """659 This function is used to list all the Replication slots of the cluster660 """661 662 if not sid:663 return internal_server_error(errormsg='Server ID not specified.')664 665 sql = render_template("/".join([g.template_path, 'replication_stats.sql']))666 status, res = g.conn.execute_dict(sql)667 668 if not status:669 return internal_server_error(errormsg=str(res))670 671 return ajax_response(672 response=res['rows'],673 status=200674 )675 676 677@blueprint.route('/replication_slots/<int:sid>',678 endpoint='replication_slots', methods=['GET'])679@pga_login_required680@check_precondition681def replication_slots(sid=None):682 """683 This function is used to list all the Replication slots of the cluster684 """685 686 if not sid:687 return internal_server_error(errormsg='Server ID not specified.')688 689 sql = render_template("/".join([g.template_path, 'replication_slots.sql']))690 status, res = g.conn.execute_dict(sql)691 692 if not status:693 return internal_server_error(errormsg=str(res))694 695 return ajax_response(696 response=res['rows'],697 status=200698 )699