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"""Implements the pgAgent Jobs Node"""11from functools import wraps12import json13from datetime import datetime, time14 15from flask import render_template, request, jsonify16from flask_babel import gettext as _17 18from config import PG_DEFAULT_DRIVER19 20from pgadmin.browser.collection import CollectionNodeModule21from pgadmin.browser.utils import PGChildNodeView22from pgadmin.browser.server_groups import servers23from pgadmin.utils.ajax import make_json_response, internal_server_error, \24 make_response as ajax_response, gone, success_return25from pgadmin.utils.driver import get_driver26from pgadmin.utils.preferences import Preferences27from pgadmin.browser.server_groups.servers.pgagent.utils \28 import format_schedule_data, format_step_data29 30 31class JobModule(CollectionNodeModule):32 _NODE_TYPE = 'pga_job'33 _COLLECTION_LABEL = _("pgAgent Jobs")34 35 def get_nodes(self, gid, sid):36 """37 Generate the collection node38 """39 if self.show_node:40 yield self.generate_browser_collection_node(sid)41 42 @property43 def script_load(self):44 """45 Load the module script for server, when any of the server-group node is46 initialized.47 """48 return servers.ServerModule.node_type49 50 def backend_supported(self, manager, **kwargs):51 if hasattr(self, 'show_node') and not self.show_node:52 return False53 54 conn = manager.connection()55 56 status, res = conn.execute_scalar("""57SELECT58 has_table_privilege(59 'pgagent.pga_job', 'INSERT, SELECT, UPDATE'60 ) has_priviledge61WHERE EXISTS(62 SELECT has_schema_privilege('pgagent', 'USAGE')63 WHERE EXISTS(64 SELECT cl.oid FROM pg_catalog.pg_class cl65 LEFT JOIN pg_catalog.pg_namespace ns ON ns.oid=relnamespace66 WHERE relname='pga_job' AND nspname='pgagent'67 )68)69""")70 if status and res:71 status, res = conn.execute_dict("""72SELECT EXISTS(73 SELECT 1 FROM information_schema.columns74 WHERE75 table_schema='pgagent' AND table_name='pga_jobstep' AND76 column_name='jstconnstr'77 ) has_connstr""")78 79 manager.db_info['pgAgent'] = res['rows'][0]80 return True81 return False82 83 @property84 def csssnippets(self):85 """86 Returns a snippet of css to include in the page87 """88 snippets = [89 render_template(90 self._COLLECTION_CSS,91 node_type=self.node_type,92 _=_93 ),94 render_template(95 "pga_job/css/pga_job.css",96 node_type=self.node_type,97 _=_98 )99 ]100 101 for submodule in self.submodules:102 snippets.extend(submodule.csssnippets)103 104 return snippets105 106 @property107 def module_use_template_javascript(self):108 """109 Returns whether Jinja2 template is used for generating the javascript110 module.111 """112 return False113 114 def register(self, app, options):115 """116 Override the default register function to automagically register117 sub-modules at once.118 """119 from .schedules import blueprint as module120 self.submodules.append(module)121 122 from .steps import blueprint as module123 self.submodules.append(module)124 125 super().register(app, options)126 127 128blueprint = JobModule(__name__)129 130 131class JobView(PGChildNodeView):132 node_type = blueprint.node_type133 134 parent_ids = [135 {'type': 'int', 'id': 'gid'},136 {'type': 'int', 'id': 'sid'}137 ]138 ids = [139 {'type': 'int', 'id': 'jid'}140 ]141 142 operations = dict({143 'obj': [144 {'get': 'properties', 'delete': 'delete', 'put': 'update'},145 {'get': 'properties', 'post': 'create', 'delete': 'delete'}146 ],147 'nodes': [{'get': 'nodes'}, {'get': 'nodes'}],148 'sql': [{'get': 'sql'}],149 'msql': [{'get': 'msql'}, {'get': 'msql'}],150 'run_now': [{'put': 'run_now'}],151 'classes': [{}, {'get': 'job_classes'}],152 'children': [{'get': 'children'}],153 'stats': [{'get': 'statistics'}]154 })155 156 def check_precondition(f):157 """158 This function will behave as a decorator which will checks159 database connection before running view, it will also attaches160 manager,conn & template_path properties to self161 """162 163 @wraps(f)164 def wrap(self, *args, **kwargs):165 166 self.manager = get_driver(167 PG_DEFAULT_DRIVER168 ).connection_manager(169 kwargs['sid']170 )171 self.conn = self.manager.connection()172 173 # Set the template path for the sql scripts.174 self.template_path = 'pga_job/sql/pre3.4'175 176 if 'pgAgent'not in self.manager.db_info:177 _, res = self.conn.execute_dict("""178SELECT EXISTS(179 SELECT 1 FROM information_schema.columns180 WHERE181 table_schema='pgagent' AND table_name='pga_jobstep' AND182 column_name='jstconnstr'183 ) has_connstr""")184 185 self.manager.db_info['pgAgent'] = res['rows'][0]186 187 return f(self, *args, **kwargs)188 return wrap189 190 @check_precondition191 def nodes(self, gid, sid, jid=None):192 SQL = render_template(193 "/".join([self.template_path, self._NODES_SQL]),194 jid=jid, conn=self.conn195 )196 status, rset = self.conn.execute_dict(SQL)197 198 if not status:199 return internal_server_error(errormsg=rset)200 201 if jid is not None:202 if len(rset['rows']) != 1:203 return gone(204 errormsg=_("Could not find the pgAgent job on the server.")205 )206 return make_json_response(207 data=self.blueprint.generate_browser_node(208 rset['rows'][0]['jobid'],209 sid,210 rset['rows'][0]['jobname'],211 "icon-pga_job" if rset['rows'][0]['jobenabled'] else212 "icon-pga_job-disabled",213 description=rset['rows'][0]['jobdesc']214 ),215 status=200216 )217 218 res = []219 for row in rset['rows']:220 res.append(221 self.blueprint.generate_browser_node(222 row['jobid'],223 sid,224 row['jobname'],225 "icon-pga_job" if row['jobenabled'] else226 "icon-pga_job-disabled",227 description=row['jobdesc']228 )229 )230 231 return make_json_response(232 data=res,233 status=200234 )235 236 @check_precondition237 def properties(self, gid, sid, jid=None):238 SQL = render_template(239 "/".join([self.template_path, self._PROPERTIES_SQL]),240 jid=jid, conn=self.conn241 )242 status, rset = self.conn.execute_dict(SQL)243 244 if not status:245 return internal_server_error(errormsg=rset)246 247 if jid is not None:248 if len(rset['rows']) != 1:249 return gone(250 errormsg=_(251 "Could not find the pgAgent job on the server."252 )253 )254 res = rset['rows'][0]255 status, rset = self.conn.execute_dict(256 render_template(257 "/".join([self.template_path, 'steps.sql']),258 jid=jid, conn=self.conn,259 has_connstr=self.manager.db_info['pgAgent']['has_connstr']260 )261 )262 if not status:263 return internal_server_error(errormsg=rset)264 res['jsteps'] = rset['rows']265 status, rset = self.conn.execute_dict(266 render_template(267 "/".join([self.template_path, 'schedules.sql']),268 jid=jid, conn=self.conn269 )270 )271 if not status:272 return internal_server_error(errormsg=rset)273 274 # Create jscexceptions in the correct format that React control275 # required.276 for schedule in rset['rows']:277 if 'jexid' in schedule and schedule['jexid'] is not None \278 and len(schedule['jexid']) > 0:279 schedule['jscexceptions'] = []280 index = 0281 for exid in schedule['jexid']:282 schedule['jscexceptions'].append(283 {'jexid': exid,284 'jexdate': schedule['jexdate'][index],285 'jextime': schedule['jextime'][index]286 }287 )288 289 index += 1290 291 res['jschedules'] = rset['rows']292 else:293 res = rset['rows']294 295 return ajax_response(296 response=res,297 status=200298 )299 300 @check_precondition301 def create(self, gid, sid):302 """Create the pgAgent job."""303 required_args = [304 'jobname'305 ]306 307 data = request.form if request.form else json.loads(308 request.data.decode('utf-8')309 )310 311 for arg in required_args:312 if arg not in data:313 return make_json_response(314 status=410,315 success=0,316 errormsg=_(317 "Could not find the required parameter ({})."318 ).format(arg)319 )320 321 status, res = self.conn.execute_void('BEGIN')322 if not status:323 return internal_server_error(errormsg=res)324 325 status, res = self.conn.execute_scalar(326 render_template(327 "/".join([self.template_path, self._CREATE_SQL]),328 data=data, conn=self.conn, fetch_id=True,329 has_connstr=self.manager.db_info['pgAgent']['has_connstr']330 )331 )332 333 if not status:334 self.conn.execute_void('END')335 return internal_server_error(errormsg=res)336 337 # We need oid of newly created database338 status, res = self.conn.execute_dict(339 render_template(340 "/".join([self.template_path, self._NODES_SQL]),341 jid=res, conn=self.conn342 )343 )344 345 self.conn.execute_void('END')346 if not status:347 return internal_server_error(errormsg=res)348 349 row = res['rows'][0]350 351 return jsonify(352 node=self.blueprint.generate_browser_node(353 row['jobid'],354 sid,355 row['jobname'],356 icon="icon-pga_job" if row['jobenabled']357 else "icon-pga_job-disabled"358 )359 )360 361 @check_precondition362 def update(self, gid, sid, jid):363 """Update the pgAgent Job."""364 365 data = request.form if request.form else json.loads(366 request.data.decode('utf-8')367 )368 369 # Format the schedule and step data370 self.format_schedule_step_data(data)371 372 status, res = self.conn.execute_void(373 render_template(374 "/".join([self.template_path, self._UPDATE_SQL]),375 data=data, conn=self.conn, jid=jid,376 has_connstr=self.manager.db_info['pgAgent']['has_connstr']377 )378 )379 380 if not status:381 return internal_server_error(errormsg=res)382 383 # We need oid of newly created database384 status, res = self.conn.execute_dict(385 render_template(386 "/".join([self.template_path, self._NODES_SQL]),387 jid=jid, conn=self.conn388 )389 )390 391 if not status:392 return internal_server_error(errormsg=res)393 394 row = res['rows'][0]395 396 return jsonify(397 node=self.blueprint.generate_browser_node(398 jid,399 sid,400 row['jobname'],401 icon="icon-pga_job" if row['jobenabled']402 else "icon-pga_job-disabled",403 description=row['jobdesc']404 )405 )406 407 @check_precondition408 def delete(self, gid, sid, jid=None):409 """Delete the pgAgent Job."""410 411 if jid is None:412 data = request.form if request.form else json.loads(413 request.data414 )415 else:416 data = {'ids': [jid]}417 418 for jid in data['ids']:419 status, res = self.conn.execute_void(420 render_template(421 "/".join([self.template_path, self._DELETE_SQL]),422 jid=jid, conn=self.conn423 )424 )425 if not status:426 return internal_server_error(errormsg=res)427 428 return make_json_response(success=1)429 430 @check_precondition431 def msql(self, gid, sid, jid=None):432 """433 This function to return modified SQL.434 """435 data = {}436 for k, v in request.args.items():437 try:438 data[k] = json.loads(439 v.decode('utf-8') if hasattr(v, 'decode') else v440 )441 except ValueError:442 data[k] = v443 444 # Format the schedule and step data445 self.format_schedule_step_data(data)446 447 return make_json_response(448 data=render_template(449 "/".join([450 self.template_path,451 self._CREATE_SQL if jid is None else self._UPDATE_SQL452 ]),453 jid=jid, data=data, conn=self.conn, fetch_id=False,454 has_connstr=self.manager.db_info['pgAgent']['has_connstr']455 ),456 status=200457 )458 459 @check_precondition460 def statistics(self, gid, sid, jid):461 """462 statistics463 Returns the statistics for a particular database if jid is specified,464 otherwise it will return statistics for all the databases in that465 server.466 """467 pref = Preferences.module('browser')468 rows_threshold = pref.preference(469 'pgagent_row_threshold'470 )471 472 status, res = self.conn.execute_dict(473 render_template(474 "/".join([self.template_path, 'stats.sql']),475 jid=jid, conn=self.conn,476 rows_threshold=rows_threshold.get()477 )478 )479 480 if not status:481 return internal_server_error(errormsg=res)482 483 return make_json_response(484 data=res,485 status=200486 )487 488 @check_precondition489 def sql(self, gid, sid, jid):490 """491 This function will generate sql for sql panel492 """493 SQL = render_template(494 "/".join([self.template_path, self._PROPERTIES_SQL]),495 jid=jid, conn=self.conn, last_system_oid=0496 )497 status, res = self.conn.execute_dict(SQL)498 if not status:499 return internal_server_error(errormsg=res)500 501 if len(res['rows']) == 0:502 return gone(503 _("Could not find the object on the server.")504 )505 506 row = res['rows'][0]507 508 status, res = self.conn.execute_dict(509 render_template(510 "/".join([self.template_path, 'steps.sql']),511 jid=jid, conn=self.conn,512 has_connstr=self.manager.db_info['pgAgent']['has_connstr']513 )514 )515 if not status:516 return internal_server_error(errormsg=res)517 518 row['jsteps'] = res['rows']519 520 status, res = self.conn.execute_dict(521 render_template(522 "/".join([self.template_path, 'schedules.sql']),523 jid=jid, conn=self.conn524 )525 )526 if not status:527 return internal_server_error(errormsg=res)528 529 row['jschedules'] = res['rows']530 for schedule in row['jschedules']:531 schedule['jscexceptions'] = []532 if schedule['jexid']:533 idx = 0534 for exc in schedule['jexid']:535 # Convert datetime.time object to string536 if isinstance(schedule['jextime'][idx], time):537 schedule['jextime'][idx] = \538 schedule['jextime'][idx].strftime("%H:%M:%S")539 schedule['jscexceptions'].append({540 'jexid': exc,541 'jexdate': schedule['jexdate'][idx],542 'jextime': schedule['jextime'][idx]543 })544 idx += 1545 del schedule['jexid']546 del schedule['jexdate']547 del schedule['jextime']548 549 return ajax_response(550 response=render_template(551 "/".join([self.template_path, self._CREATE_SQL]),552 jid=jid, data=row, conn=self.conn, fetch_id=False,553 has_connstr=self.manager.db_info['pgAgent']['has_connstr']554 )555 )556 557 @check_precondition558 def run_now(self, gid, sid, jid):559 """560 This function will set the next run to now, to inform the pgAgent to561 run the job now.562 """563 status, res = self.conn.execute_void(564 render_template(565 "/".join([self.template_path, 'run_now.sql']),566 jid=jid, conn=self.conn567 )568 )569 if not status:570 return internal_server_error(errormsg=res)571 572 return success_return(573 message=_("Updated the next runtime to now.")574 )575 576 @check_precondition577 def job_classes(self, gid, sid):578 """579 This function will return the set of job classes.580 """581 status, res = self.conn.execute_dict(582 render_template("/".join([self.template_path, 'job_classes.sql']))583 )584 585 if not status:586 return internal_server_error(errormsg=res)587 588 return make_json_response(589 data=res['rows'],590 status=200591 )592 593 def format_schedule_step_data(self, data):594 """595 This function is used to format the schedule and step data.596 :param data:597 :return:598 """599 # Format the schedule data. Convert the boolean array600 jschedules = data.get('jschedules', {})601 if isinstance(jschedules, dict):602 for schedule in jschedules.get('added', []):603 format_schedule_data(schedule)604 for schedule in jschedules.get('changed', []):605 format_schedule_data(schedule)606 607 has_connection_str = self.manager.db_info['pgAgent']['has_connstr']608 jssteps = data.get('jsteps', {})609 if isinstance(jssteps, dict):610 for changed_step in jssteps.get('changed', []):611 status, res = format_step_data(612 data['jobid'], changed_step, has_connection_str,613 self.conn, self.template_path)614 if not status:615 internal_server_error(errormsg=res)616 617 618JobView.register_node_view(blueprint)619