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 10import json11import re12from functools import wraps13 14import pgadmin.browser.server_groups.servers.databases as database15from flask import render_template, request, jsonify16from flask_babel import gettext17from pgadmin.browser.collection import CollectionNodeModule18from pgadmin.browser.utils import PGChildNodeView19from pgadmin.utils.ajax import make_json_response, internal_server_error, \20 make_response as ajax_response, gone21from pgadmin.utils.driver import get_driver22from config import PG_DEFAULT_DRIVER23from pgadmin.tools.schema_diff.node_registry import SchemaDiffRegistry24from pgadmin.tools.schema_diff.compare import SchemaDiffObjectCompare25 26 27class EventTriggerModule(CollectionNodeModule):28 """29 class EventTriggerModule(CollectionNodeModule)30 31 A module class for Event trigger node derived from32 CollectionNodeModule.33 34 Methods:35 -------36 * __init__(*args, **kwargs)37 - Method is used to initialize the EventTriggerModule and it's base38 module.39 40 * get_nodes(gid, sid, did)41 - Method is used to generate the browser collection node.42 43 * script_load()44 - Load the module script for Event trigger, when any of the database node45 is initialized.46 """47 48 _NODE_TYPE = 'event_trigger'49 _COLLECTION_LABEL = gettext("Event Triggers")50 51 def __init__(self, *args, **kwargs):52 """53 Method is used to initialize the EventTriggerModule and it's base54 module.55 56 Args:57 *args:58 **kwargs:59 """60 super().__init__(*args, **kwargs)61 self.min_ver = 9030062 self.max_ver = None63 64 def get_nodes(self, gid, sid, did):65 """66 Generate the event_trigger node67 """68 if self.has_nodes(69 sid, did,70 base_template_path=EventTriggerView.BASE_TEMPLATE_PATH):71 yield self.generate_browser_collection_node(sid)72 73 @property74 def node_inode(self):75 """76 Always returns false, it is a leaf node, and do not have children77 nodes.78 """79 return False80 81 @property82 def script_load(self):83 """84 Load the module script for event_trigger, when any of the database node85 is initialized.86 """87 return database.DatabaseModule.node_type88 89 @property90 def module_use_template_javascript(self):91 """92 Returns whether Jinja2 template is used for generating the javascript93 module.94 """95 return False96 97 98blueprint = EventTriggerModule(__name__)99 100 101class EventTriggerView(PGChildNodeView, SchemaDiffObjectCompare):102 """103 class EventTriggerView(PGChildNodeView)104 105 A view class for event trigger node derived from PGChildNodeView.106 This class is responsible for all the stuff related to view like107 updating event trigger node, showing properties, showing sql in sql108 pane.109 110 Methods:111 -------112 * __init__(**kwargs)113 - Method is used to initialize the EventTriggerView and it's base view.114 115 * check_precondition()116 - This function will behave as a decorator which will checks117 database connection before running view, it will also attaches118 manager,conn & template_path properties to self119 120 * list()121 - Lists proroperties of all the nodes of type - event trigger.122 123 * nodes()124 - Creates all the child nodes of type - event trigger.125 126 * properties(gid, sid, did, etid)127 - Returns the properties of the given event trigger node128 129 * update(gid, sid, did, etid)130 - Updates the data for the given event trigger node.131 132 * msql(gid, sid, did, etid)133 - Return modified SQL for the given event trigger node based on the134 request data.135 136 * get_sql(data, etid)137 - Generates the sql from model data138 139 * sql(gid, sid, did, etid):140 - Generates the reversed engineered query for the given event trigger141 node.142 143 * get_event_funcs(gid, sid, did, etid):144 - Returns the event functions available in that database.145 146 * dependents(gid, sid, did, etid):147 - Returns the dependents list for the given event trigger node.148 149 * dependencies(self, gid, sid, did, etid):150 - Returns the dependencies list for the given event trigger node.151 """152 153 node_type = blueprint.node_type154 node_icon = "icon-%s" % blueprint.node_type155 BASE_TEMPLATE_PATH = 'event_triggers/sql/#{0}#'156 157 parent_ids = [158 {'type': 'int', 'id': 'gid'},159 {'type': 'int', 'id': 'sid'},160 {'type': 'int', 'id': 'did'}161 ]162 ids = [163 {'type': 'int', 'id': 'etid'}164 ]165 166 operations = dict({167 'obj': [168 {'get': 'properties', 'delete': 'delete', 'put': 'update'},169 {'get': 'list', 'post': 'create', 'delete': 'delete'}170 ],171 'nodes': [{'get': 'node'}, {'get': 'nodes'}],172 'children': [{'get': 'children'}],173 'sql': [{'get': 'sql'}],174 'msql': [{'get': 'msql'}, {'get': 'msql'}],175 'stats': [{'get': 'statistics'}],176 'dependency': [{'get': 'dependencies'}],177 'dependent': [{'get': 'dependents'}],178 'fopts': [{'get': 'get_event_funcs'}, {'get': 'get_event_funcs'}]179 })180 181 keys_to_ignore = ['oid', 'xmin', 'oid-2', 'eventfuncoid', 'schemaoid',182 'source']183 184 def check_precondition(f):185 """186 This function will behave as a decorator which will checks187 database connection before running view, it will also attaches188 manager,conn & template_path properties to self189 """190 191 @wraps(f)192 def wrap(*args, **kwargs):193 194 # Here - args[0] will always hold self & kwargs will hold gid,195 # sid, did196 self = args[0]197 self.manager = get_driver(198 PG_DEFAULT_DRIVER199 ).connection_manager(kwargs['sid'])200 self.conn = self.manager.connection(did=kwargs['did'])201 self.template_path = self.BASE_TEMPLATE_PATH.format(202 self.manager.version)203 204 self.datistemplate = False205 if (206 self.manager.db_info is not None and207 kwargs['did'] in self.manager.db_info and208 'datistemplate' in self.manager.db_info[kwargs['did']]209 ):210 self.datistemplate = self.manager.db_info[211 kwargs['did']]['datistemplate']212 213 return f(*args, **kwargs)214 215 return wrap216 217 @check_precondition218 def list(self, gid, sid, did):219 """220 This function is used to list all the event trigger221 nodes within that collection.222 223 Args:224 gid: Server Group ID225 sid: Server ID226 did: Database ID227 etid: Event trigger ID228 229 Returns:230 231 """232 sql = render_template("/".join([self.template_path,233 self._PROPERTIES_SQL]))234 status, res = self.conn.execute_dict(sql)235 236 if not status:237 return internal_server_error(errormsg=res)238 239 return ajax_response(240 response=res['rows'],241 status=200242 )243 244 @check_precondition245 def nodes(self, gid, sid, did):246 """247 This function is used to create all the child nodes within the248 collection. Here it will create all the event trigger nodes.249 250 Args:251 gid: Server Group ID252 sid: Server ID253 did: Database ID254 etid: Event trigger ID255 256 Returns:257 258 """259 result = []260 sql = render_template("/".join([self.template_path, self._NODES_SQL]))261 status, res = self.conn.execute_2darray(sql)262 if not status:263 return internal_server_error(errormsg=res)264 265 for row in res['rows']:266 result.append(267 self.blueprint.generate_browser_node(268 row['oid'],269 did,270 row['name'],271 self.node_icon,272 description=row['comment']273 ))274 275 return make_json_response(276 data=result,277 status=200278 )279 280 @check_precondition281 def node(self, gid, sid, did, etid):282 """283 This function will fetch properties of trigger node.284 285 Args:286 gid: Server Group ID287 sid: Server ID288 did: Database ID289 etid: Event trigger ID290 291 Returns:292 Json object of trigger node293 """294 sql = render_template("/".join([self.template_path, self._NODES_SQL]),295 etid=etid)296 status, res = self.conn.execute_2darray(sql)297 if not status:298 return internal_server_error(errormsg=res)299 300 for row in res['rows']:301 return make_json_response(302 data=self.blueprint.generate_browser_node(303 row['oid'],304 did,305 row['name'],306 self.node_icon307 ),308 status=200309 )310 311 return gone(gettext("Could not find the specified event trigger."))312 313 def _formatter(self, result):314 """315 This function is ued to parse security lables316 """317 seclabels = []318 if 'seclabels' in result and result['seclabels'] is not None:319 for sec in result['seclabels']:320 sec = re.search(r'([^=]+)=(.*$)', sec)321 seclabels.append({322 'provider': sec.group(1),323 'label': sec.group(2)324 })325 326 result['seclabels'] = seclabels327 return result328 329 @check_precondition330 def properties(self, gid, sid, did, etid):331 """332 This function is used to list all the event trigger333 nodes within that collection.334 335 Args:336 gid: Server Group ID337 sid: Server ID338 did: Database ID339 etid: Event trigger ID340 341 Returns:342 343 """344 status, res = self._fetch_properties(did, etid)345 if not status:346 return res347 348 return ajax_response(349 response=res,350 status=200351 )352 353 def _fetch_properties(self, did, etid):354 """355 This function fetch the properties of the event trigger.356 :param did:357 :param etid:358 :return:359 """360 sql = render_template(361 "/".join([self.template_path, self._PROPERTIES_SQL]),362 etid=etid, conn=self.conn363 )364 status, res = self.conn.execute_dict(sql)365 if not status:366 return False, internal_server_error(errormsg=res)367 368 if len(res['rows']) == 0:369 return False, gone(370 gettext("Could not find the event trigger information."))371 372 result = res['rows'][0]373 result['is_sys_obj'] = (374 result['oid'] <= self._DATABASE_LAST_SYSTEM_OID or375 self.datistemplate)376 result = self._formatter(result)377 378 return True, result379 380 @check_precondition381 def create(self, gid, sid, did):382 """383 This function will create a event trigger object.384 385 Args:386 gid: Server Group ID387 sid: Server ID388 did: Database ID389 etid: Event trigger ID390 391 Returns:392 393 """394 data = request.form if request.form else json.loads(395 request.data396 )397 398 required_args = {399 'name': 'Name',400 'eventowner': 'Owner',401 'eventfunname': 'Trigger function',402 'enabled': 'Enabled status',403 'eventname': 'Events'404 }405 err = []406 for arg in required_args:407 if arg not in data:408 err.append(required_args.get(arg, arg))409 if err:410 return make_json_response(411 status=400,412 success=0,413 errormsg=gettext(414 "Could not find the required parameter ({}).").format(err)415 )416 try:417 sql = render_template(418 "/".join([self.template_path, self._CREATE_SQL]),419 data=data, conn=self.conn420 )421 status, res = self.conn.execute_scalar(sql)422 if not status:423 return internal_server_error(errormsg=res)424 sql = render_template(425 "/".join([self.template_path, self._GRANT_SQL]),426 data=data, conn=self.conn427 )428 sql = sql.strip('\n').strip(' ')429 430 status, res = self.conn.execute_scalar(sql)431 if not status:432 return internal_server_error(errormsg=res)433 434 sql = render_template(435 "/".join([self.template_path, self._OID_SQL]),436 data=data, conn=self.conn437 )438 status, etid = self.conn.execute_scalar(sql)439 if not status:440 return internal_server_error(errormsg=etid)441 442 return jsonify(443 node=self.blueprint.generate_browser_node(444 etid,445 did,446 data['name'],447 self.node_icon448 )449 )450 except Exception as e:451 return internal_server_error(errormsg=str(e))452 453 @check_precondition454 def update(self, gid, sid, did, etid):455 """456 This function will update the data for the selected457 event trigger node.458 459 Args:460 gid: Server Group ID461 sid: Server ID462 did: Database ID463 etid: Event trigger ID464 465 Returns:466 467 """468 data = request.form if request.form else json.loads(469 request.data470 )471 472 try:473 sql = self.get_sql(data, etid)474 # Most probably this is due to error475 if not isinstance(sql, str):476 return sql477 478 if sql != "":479 status, res = self.conn.execute_scalar(sql)480 if not status:481 return internal_server_error(errormsg=res)482 483 sql = render_template(484 "/".join([self.template_path, self._OID_SQL]),485 data=data, conn=self.conn486 )487 status, etid = self.conn.execute_scalar(sql)488 489 other_node_info = {}490 if 'comment' in data:491 other_node_info['description'] = data['comment']492 493 return jsonify(494 node=self.blueprint.generate_browser_node(495 etid,496 did,497 data['name'],498 self.node_icon,499 **other_node_info500 )501 )502 else:503 return make_json_response(504 success=1,505 info="Nothing to update",506 data={507 'id': etid,508 'sid': sid,509 'gid': gid,510 'did': did511 }512 )513 514 except Exception as e:515 return internal_server_error(errormsg=str(e))516 517 @staticmethod518 def get_delete_data(cmd, etid, request_object):519 """520 This function is used to get the data and cascade information.521 :param cmd: Command522 :param etid: Object ID523 :param request_object: request object524 :return:525 """526 cascade = False527 # Below will decide if it's simple drop or drop with cascade call528 if cmd == 'delete':529 # This is a cascade operation530 cascade = True531 532 if etid is None:533 data = request_object.form if request_object.form else \534 json.loads(request_object.data)535 else:536 data = {'ids': [etid]}537 538 return cascade, data539 540 @check_precondition541 def delete(self, gid, sid, did, etid=None, only_sql=False):542 """543 This function will delete an existing event trigger object.544 545 Args:546 gid: Server Group ID547 sid: Server ID548 did: Database ID549 etid: Event trigger ID550 only_sql:551 552 Returns:553 554 """555 # get the value of cascade and data556 cascade, data = self.get_delete_data(self.cmd, etid, request)557 558 try:559 for etid in data['ids']:560 sql = render_template(561 "/".join([self.template_path, self._DELETE_SQL]),562 etid=etid563 )564 status, name = self.conn.execute_scalar(sql)565 if not status:566 return internal_server_error(errormsg=name)567 568 if name is None:569 return make_json_response(570 status=410,571 success=0,572 errormsg=gettext(573 'Error: Object not found.'574 ),575 info=gettext(576 'The specified event trigger could not be found.\n'577 )578 )579 580 sql = render_template(581 "/".join([self.template_path, self._DELETE_SQL]),582 name=name, cascade=cascade583 )584 585 # Used for schema diff tool586 if only_sql:587 return sql588 589 status, res = self.conn.execute_scalar(sql)590 if not status:591 return internal_server_error(errormsg=res)592 593 return make_json_response(594 success=1,595 info=gettext("Event trigger dropped")596 )597 598 except Exception as e:599 return internal_server_error(errormsg=str(e))600 601 @check_precondition602 def msql(self, gid, sid, did, etid=None):603 """604 This function is used to return modified SQL for the selected605 event trigger node.606 607 Args:608 gid: Server Group ID609 sid: Server ID610 did: Database ID611 etid: Event trigger ID612 613 Returns:614 615 """616 data = {}617 for k, v in request.args.items():618 try:619 data[k] = json.loads(v)620 except ValueError:621 data[k] = v622 try:623 sql = self.get_sql(data, etid)624 # Most probably this is due to error625 if not isinstance(sql, str):626 return sql627 628 sql = re.sub('\n{2,}', '\n\n', sql)629 if sql == '':630 sql = "--modified SQL"631 return make_json_response(632 data=sql,633 status=200634 )635 636 except Exception as e:637 return internal_server_error(errormsg=str(e))638 639 def get_sql(self, data, etid=None):640 """641 This function will generate sql from model data.642 643 Args:644 data: Contains the data of the selected event trigger node.645 etid: Event trigger ID646 647 Returns:648 649 """650 required_args = [651 'name'652 ]653 654 if etid is not None:655 sql = render_template(656 "/".join([self.template_path, self._PROPERTIES_SQL]),657 etid=etid658 )659 status, res = self.conn.execute_dict(sql)660 if not status:661 return internal_server_error(errormsg=res)662 663 if len(res['rows']) == 0:664 return gone(665 gettext("Could not find the event trigger information.")666 )667 668 old_data = res['rows'][0]669 old_data = self._formatter(old_data)670 671 for arg in required_args:672 if arg not in data:673 data[arg] = old_data[arg]674 sql = render_template(675 "/".join([self.template_path, self._UPDATE_SQL]),676 data=data, o_data=old_data677 )678 else:679 sql = self._get_create_with_grant_sql(data)680 return sql.strip('\n')681 682 def _get_create_with_grant_sql(self, data):683 684 required_args = {685 'name': 'Name',686 'eventowner': 'Owner',687 'eventfunname': 'Trigger function',688 'enabled': 'Enabled status',689 'eventname': 'Events'690 }691 err = []692 for arg in required_args:693 if arg not in data:694 err.append(required_args.get(arg, arg))695 if err:696 return make_json_response(697 status=410,698 success=0,699 errormsg=gettext(700 "Could not find the required parameter ({})."701 ).format(arg)702 )703 sql = render_template(704 "/".join([self.template_path, self._CREATE_SQL]),705 data=data706 )707 sql += "\n"708 sql += render_template(709 "/".join([self.template_path, self._GRANT_SQL]),710 data=data711 )712 return sql.strip('\n').strip(' ')713 714 @check_precondition715 def sql(self, gid, sid, did, etid, json_resp=True):716 """717 This function will generate sql to show in the sql pane for the718 selected event trigger node.719 720 Args:721 gid: Server Group ID722 sid: Server ID723 did: Database ID724 etid: Event trigger ID725 json_resp:726 727 Returns:728 729 """730 sql = render_template(731 "/".join([self.template_path, self._PROPERTIES_SQL]),732 etid=etid733 )734 status, res = self.conn.execute_dict(sql)735 if not status:736 return internal_server_error(errormsg=res)737 738 if len(res['rows']) == 0:739 return gone(740 gettext(741 "Could not find the specified event trigger on the "742 "server.")743 )744 745 result = res['rows'][0]746 result = self._formatter(result)747 748 sql = render_template(749 "/".join([self.template_path, self._CREATE_SQL]),750 data=result, conn=self.conn751 )752 sql += "\n\n"753 sql += render_template(754 "/".join([self.template_path, self._GRANT_SQL]),755 data=result, conn=self.conn756 )757 758 db_sql = render_template(759 "/".join([self.template_path, 'get_db.sql']),760 did=did761 )762 status, db_name = self.conn.execute_scalar(db_sql)763 if not status:764 return internal_server_error(errormsg=db_name)765 766 sql_header = "-- Event Trigger: {0} on database {1}\n\n-- ".format(767 result['name'], db_name768 )769 770 sql_header += render_template(771 "/".join([self.template_path, self._DELETE_SQL]),772 name=result['name'], )773 sql_header += "\n"774 775 sql = sql_header + sql776 sql = re.sub('\n{2,}', '\n\n', sql)777 778 if not json_resp:779 return sql780 781 return ajax_response(response=sql)782 783 @check_precondition784 def get_event_funcs(self, gid, sid, did, etid=None):785 """786 This function gets the event functions and returns an ajax response787 for the event trigger node.788 789 Args:790 gid: Server Group ID791 sid: Server ID792 did: Database ID793 etid: Event trigger ID794 795 Returns:796 797 """798 res = [{'label': '', 'value': ''}]799 sql = render_template(800 "/".join([self.template_path, 'eventfunctions.sql'])801 )802 status, rest = self.conn.execute_2darray(sql)803 if not status:804 return internal_server_error(errormsg=rest)805 for row in rest['rows']:806 res.append(807 {'label': row['tfname'], 'value': row['tfname']}808 )809 return make_json_response(810 data=res,811 status=200812 )813 814 @check_precondition815 def dependents(self, gid, sid, did, etid=None):816 """817 This function gets the dependents and returns an ajax response818 for the event trigger node.819 820 Args:821 gid: Server Group ID822 sid: Server ID823 did: Database ID824 etid: Event trigger ID825 """826 dependents_result = self.get_dependents(self.conn, etid)827 return ajax_response(828 response=dependents_result,829 status=200830 )831 832 @check_precondition833 def dependencies(self, gid, sid, did, etid):834 """835 This function gets the dependencies and returns an ajax response836 for the event trigger node.837 838 Args:839 gid: Server Group ID840 sid: Server ID841 did: Database ID842 etid: Event trigger ID843 """844 dependencies_result = self.get_dependencies(self.conn, etid)845 return ajax_response(846 response=dependencies_result,847 status=200848 )849 850 @check_precondition851 def fetch_objects_to_compare(self, sid, did):852 """853 This function will fetch the list of all the event triggers for854 specified database id.855 856 :param sid: Server Id857 :param did: Database Id858 :return:859 """860 res = dict()861 862 sql = render_template(863 "/".join([self.template_path, self._NODES_SQL]),864 schema_diff=True865 )866 status, rset = self.conn.execute_2darray(sql)867 if not status:868 return internal_server_error(errormsg=rset)869 870 for row in rset['rows']:871 status, data = self._fetch_properties(did, row['oid'])872 if status:873 res[row['name']] = data874 875 return res876 877 def get_sql_from_diff(self, **kwargs):878 """879 This function is used to get the DDL/DML statements.880 :param kwargs:881 :return:882 """883 gid = kwargs.get('gid')884 sid = kwargs.get('sid')885 did = kwargs.get('did')886 oid = kwargs.get('oid')887 data = kwargs.get('data', None)888 drop_sql = kwargs.get('drop_sql', False)889 890 if data:891 sql = self.get_sql(data=data, etid=oid)892 else:893 if drop_sql:894 sql = self.delete(gid=gid, sid=sid, did=did,895 etid=oid, only_sql=True)896 else:897 sql = self.sql(gid=gid, sid=sid, did=did, etid=oid,898 json_resp=False)899 return sql900 901 902SchemaDiffRegistry(blueprint.node_type, EventTriggerView, 'Database')903EventTriggerView.register_node_view(blueprint)904