codekingpro/portable-devtools
115k
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"""Browser helper utilities"""11 12from abc import abstractmethod13 14import flask15from flask import render_template, current_app16from flask.views import View, MethodView17from flask_babel import gettext18 19from config import PG_DEFAULT_DRIVER20from pgadmin.utils.ajax import make_json_response, precondition_required,\21 internal_server_error22from pgadmin.utils.exception import ConnectionLost, SSHTunnelConnectionLost,\23 CryptKeyMissing24from pgadmin.utils.constants import DATABASE_LAST_SYSTEM_OID25 26 27def underscore_escape(text):28 """29 This function mimics the behaviour of underscore js escape function30 The html escaped by jinja is not compatible for underscore unescape31 function32 :param text: input html text33 :return: escaped text34 """35 html_map = {36 '&': "&",37 '<': "<",38 '>': ">",39 '"': """,40 "'": "'"41 }42 43 # always replace & first44 if text:45 for c, r in sorted(html_map.items(),46 key=lambda x: 0 if x[0] == '&' else 1):47 text = text.replace(c, r)48 49 return text50 51 52def underscore_unescape(text):53 """54 This function mimics the behaviour of underscore js unescape function55 The html unescape by jinja is not compatible for underscore escape56 function57 :param text: input html text58 :return: unescaped text59 """60 html_map = {61 "&": '&',62 "<": '<',63 ">": '>',64 """: '"',65 "'": "'"66 }67 68 # always replace & first69 if text:70 for c, r in html_map.items():71 text = text.replace(c, r)72 73 return text74 75 76def is_version_in_range(sversion, min_ver, max_ver):77 assert (max_ver is None or isinstance(max_ver, int))78 assert (min_ver is None or isinstance(min_ver, int))79 80 if min_ver is None and max_ver is None:81 return True82 83 if (min_ver is None or min_ver <= sversion) and \84 (max_ver is None or max_ver >= sversion):85 return True86 return False87 88 89class PGChildModule():90 """91 class PGChildModule92 93 This is a base class for children/grand-children of PostgreSQL, and94 all EDB Postgres Advanced Server version95 (i.e. EDB Postgres Advanced Server, Green Plum, etc).96 97 Method:98 ------99 * backend_supported(manager)100 - Return True when it supports certain version.101 Uses the psycopg server connection manager as input for checking the102 compatibility of the current module.103 """104 105 def __init__(self, *args, **kwargs):106 self.min_ver = 0107 self.max_ver = 1100000000108 self.min_ppasver = 0109 self.max_ppasver = 1100000000110 self.server_type = None111 112 super().__init__()113 114 def backend_supported(self, manager, **kwargs):115 if hasattr(self, 'show_node') and not self.show_node:116 return False117 118 sversion = getattr(manager, 'sversion', None)119 120 if sversion is None or not isinstance(sversion, int):121 return False122 123 assert (self.server_type is None or isinstance(self.server_type, list))124 125 if self.server_type is None or manager.server_type in self.server_type:126 min_server_version = self.min_ver127 max_server_version = self.max_ver128 if manager.server_type == 'ppas':129 min_server_version = self.min_ppasver130 max_server_version = self.max_ppasver131 return is_version_in_range(sversion, min_server_version,132 max_server_version)133 134 return False135 136 @abstractmethod137 def get_nodes(self, sid=None, **kwargs):138 pass139 140 141class NodeView(View, metaclass=type(MethodView)):142 """143 A PostgreSQL Object has so many operaions/functions apart from CRUD144 (Create, Read, Update, Delete):145 i.e.146 - Reversed Engineered SQL147 - Modified Query for parameter while editing object attributes148 i.e. ALTER TABLE ...149 - Statistics of the objects150 - List of dependents151 - List of dependencies152 - Listing of the children object types for the certain node153 It will used by the browser tree to get the children nodes154 155 This class can be inherited to achieve the diffrent routes for each of the156 object types/collections.157 158 OPERATION | URL | HTTP Method | Method159 ---------------+-----------------------------+-------------+--------------160 List | /obj/[Parent URL]/ | GET | list161 Properties | /obj/[Parent URL]/id | GET | properties162 Create | /obj/[Parent URL]/ | POST | create163 Delete | /obj/[Parent URL]/id | DELETE | delete164 Update | /obj/[Parent URL]/id | PUT | update165 166 SQL (Reversed | /sql/[Parent URL]/id | GET | sql167 Engineering) |168 SQL (Modified | /msql/[Parent URL]/id | GET | modified_sql169 Properties) |170 171 Statistics | /stats/[Parent URL]/id | GET | statistics172 Dependencies | /dependency/[Parent URL]/id | GET | dependencies173 Dependents | /dependent/[Parent URL]/id | GET | dependents174 175 Nodes | /nodes/[Parent URL]/ | GET | nodes176 Current Node | /nodes/[Parent URL]/id | GET | node177 178 Children | /children/[Parent URL]/id | GET | children179 180 NOTE:181 Parent URL can be seen as the path to identify the particular node.182 183 i.e.184 In order to identify the TABLE object, we need server -> database -> schema185 information.186 """187 operations = dict({188 'obj': [189 {'get': 'properties', 'delete': 'delete', 'put': 'update'},190 {'get': 'list', 'post': 'create'}191 ],192 'nodes': [{'get': 'node'}, {'get': 'nodes'}],193 'sql': [{'get': 'sql'}],194 'msql': [{'get': 'modified_sql'}],195 'stats': [{'get': 'statistics'}],196 'dependency': [{'get': 'dependencies'}],197 'dependent': [{'get': 'dependents'}],198 'children': [{'get': 'children'}]199 })200 201 @classmethod202 def generate_ops(cls):203 cmds = []204 for op in cls.operations:205 idx = 0206 for ops in cls.operations[op]:207 meths = []208 for meth in ops:209 meths.append(meth.upper())210 if len(meths) > 0:211 cmds.append({212 'cmd': op, 'req': (idx == 0),213 'with_id': (idx != 2), 'methods': meths214 })215 idx += 1216 return cmds217 218 # Inherited class needs to modify these parameters219 node_type = None220 # Inherited class needs to modify these parameters221 node_label = None222 # This must be an array object with attributes (type and id)223 parent_ids = []224 # This must be an array object with attributes (type and id)225 ids = []226 227 @classmethod228 def get_node_urls(cls):229 assert cls.node_type is not None, \230 "Please set the node_type for this class ({0})".format(231 str(cls.__class__.__name__))232 common_url = '/'233 for p in cls.parent_ids:234 common_url += '<{0}:{1}>/'.format(str(p['type']), str(p['id']))235 236 id_url = None237 for p in cls.ids:238 id_url = '{0}<{1}:{2}>'.format(239 common_url if not id_url else id_url,240 p['type'], p['id'])241 242 return id_url, common_url243 244 def __init__(self, **kwargs):245 self.cmd = kwargs['cmd']246 247 # Check the existance of all the required arguments from parent_ids248 # and return combination of has parent arguments, and has id arguments249 def check_args(self, **kwargs):250 has_id = has_args = True251 for p in self.parent_ids:252 if p['id'] not in kwargs:253 has_args = False254 break255 256 for p in self.ids:257 if p['id'] not in kwargs:258 has_id = False259 break260 261 return has_args, has_id and has_args262 263 def dispatch_request(self, *args, **kwargs):264 http_method = flask.request.method.lower()265 if http_method == 'head':266 http_method = 'get'267 268 assert self.cmd in self.operations, \269 'Unimplemented command ({0}) for {1}'.format(270 self.cmd,271 str(self.__class__.__name__)272 )273 274 has_args, has_id = self.check_args(**kwargs)275 276 assert (277 self.cmd in self.operations and278 (has_id and len(self.operations[self.cmd]) > 0 and279 http_method in self.operations[self.cmd][0]) or280 (not has_id and len(self.operations[self.cmd]) > 1 and281 http_method in self.operations[self.cmd][1]) or282 (len(self.operations[self.cmd]) > 2 and283 http_method in self.operations[self.cmd][2])284 ), \285 'Unimplemented method ({0}) for command ({1}), which {2} ' \286 'an id'.format(http_method,287 self.cmd,288 'requires' if has_id else 'does not require')289 meth = None290 if has_id:291 meth = self.operations[self.cmd][0][http_method]292 elif has_args and http_method in self.operations[self.cmd][1]:293 meth = self.operations[self.cmd][1][http_method]294 else:295 meth = self.operations[self.cmd][2][http_method]296 297 method = getattr(self, meth, None)298 299 if method is None:300 return make_json_response(301 status=406,302 success=0,303 errormsg=gettext(304 'Unimplemented method ({0}) for this url ({1})').format(305 meth, flask.request.path306 )307 )308 309 return method(*args, **kwargs)310 311 @classmethod312 def register_node_view(cls, blueprint):313 cls.blueprint = blueprint314 id_url, url = cls.get_node_urls()315 316 commands = cls.generate_ops()317 318 for c in commands:319 cmd = c['cmd'].replace('.', '-')320 if c['with_id']:321 blueprint.add_url_rule(322 '/{0}{1}'.format(323 c['cmd'], id_url if c['req'] else url324 ),325 view_func=cls.as_view(326 '{0}{1}'.format(327 cmd, '_id' if c['req'] else ''328 ),329 cmd=c['cmd']330 ),331 methods=c['methods']332 )333 else:334 blueprint.add_url_rule(335 '/{0}'.format(c['cmd']),336 view_func=cls.as_view(337 cmd, cmd=c['cmd']338 ),339 methods=c['methods']340 )341 342 def children(self, *args, **kwargs):343 """Build a list of treeview nodes from the child nodes."""344 children = self.get_children_nodes(*args, **kwargs)345 346 # Return sorted nodes based on label347 return make_json_response(348 data=sorted(349 children, key=lambda c: c['label']350 )351 )352 353 def get_children_nodes(self, *args, **kwargs):354 """355 Returns the list of children nodes for the current nodes. Override this356 function for special cases only.357 358 :param args:359 :param kwargs: Parameters to generate the correct set of tree node.360 :return: List of the children nodes361 """362 children = []363 364 for module in self.blueprint.submodules:365 children.extend(module.get_nodes(*args, **kwargs))366 367 return children368 369 370class PGChildNodeView(NodeView):371 372 _NODE_SQL = 'node.sql'373 _NODES_SQL = 'nodes.sql'374 _COUNT_SQL = 'count.sql'375 _CREATE_SQL = 'create.sql'376 _UPDATE_SQL = 'update.sql'377 _ALTER_SQL = 'alter.sql'378 _PROPERTIES_SQL = 'properties.sql'379 _DELETE_SQL = 'delete.sql'380 _GRANT_SQL = 'grant.sql'381 _SCHEMA_SQL = 'schema.sql'382 _ACL_SQL = 'acl.sql'383 _OID_SQL = 'get_oid.sql'384 _FUNCTIONS_SQL = 'functions.sql'385 _GET_CONSTRAINTS_SQL = 'get_constraints.sql'386 _GET_TABLES_SQL = 'get_tables.sql'387 _GET_DEFINITION_SQL = 'get_definition.sql'388 _GET_SCHEMA_OID_SQL = 'get_schema_oid.sql'389 _GET_COLUMNS_SQL = 'get_columns.sql'390 _GET_COLUMNS_FOR_TABLE_SQL = 'get_columns_for_table.sql'391 _GET_SUBTYPES_SQL = 'get_subtypes.sql'392 _GET_EXTERNAL_FUNCTIONS_SQL = 'get_external_functions.sql'393 _GET_TABLE_FOR_PUBLICATION = 'get_tables.sql'394 _DATABASE_LAST_SYSTEM_OID = DATABASE_LAST_SYSTEM_OID395 396 def get_children_nodes(self, manager, **kwargs):397 """398 Returns the list of children nodes for the current nodes.399 400 :param manager: Server Manager object401 :param kwargs: Parameters to generate the correct set of browser tree402 node403 :return:404 """405 nodes = []406 for module in self.blueprint.submodules:407 if isinstance(module, PGChildModule):408 if (409 manager is not None and410 module.backend_supported(manager, **kwargs)411 ):412 nodes.extend(module.get_nodes(**kwargs))413 else:414 nodes.extend(module.get_nodes(**kwargs))415 return nodes416 417 def children(self, **kwargs):418 """Build a list of treeview nodes from the child nodes."""419 420 if 'sid' not in kwargs:421 return precondition_required(422 gettext('Required properties are missing.')423 )424 425 from pgadmin.utils.driver import get_driver426 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(427 sid=kwargs['sid']428 )429 430 did = None431 if 'did' in kwargs:432 did = kwargs['did']433 434 try:435 conn = manager.connection(did=did)436 if not conn.connected():437 status, msg = conn.connect()438 if not status:439 return internal_server_error(errormsg=msg)440 except (ConnectionLost, SSHTunnelConnectionLost, CryptKeyMissing):441 raise442 except Exception:443 return precondition_required(444 gettext(445 "Connection to the server has been lost."446 )447 )448 449 # Return sorted nodes based on label450 return make_json_response(451 data=sorted(452 self.get_children_nodes(manager, **kwargs),453 key=lambda c: c['label']454 )455 )456 457 def get_dependencies(self, conn, object_id, where=None,458 show_system_objects=None, is_schema_diff=False):459 """460 This function is used to fetch the dependencies for the selected node.461 462 Args:463 conn: Connection object464 object_id: Object Id of the selected node.465 where: where clause for the sql query (optional)466 show_system_objects: System object status467 is_schema_diff: True when function gets called from schema diff.468 469 Returns: Dictionary of dependencies for the selected node.470 """471 472 # Set the sql_path473 sql_path = 'depends/{0}/#{1}#'.format(474 conn.manager.server_type, conn.manager.version)475 476 if where is None:477 where_clause = "WHERE dep.objid={0}::oid".format(object_id)478 else:479 where_clause = where480 481 query = render_template("/".join([sql_path, 'dependencies.sql']),482 where_clause=where_clause,483 object_id=object_id)484 # fetch the dependency for the selected object485 dependencies = self.__fetch_dependency(486 conn, query, show_system_objects, is_schema_diff)487 488 # fetch role dependencies489 if where_clause.find('subid') < 0:490 sql = render_template(491 "/".join([sql_path, 'role_dependencies.sql']),492 where_clause=where_clause, conn=conn)493 494 status, result = conn.execute_dict(sql)495 if not status:496 current_app.logger.error(result)497 498 for row in result['rows']:499 ref_name = row['refname']500 dep_str = row['deptype']501 dep_type = ''502 503 if dep_str == 'a':504 dep_type = 'ACL'505 elif dep_str == 'o':506 dep_type = 'Owner'507 508 if row['refclassid'] == 1260:509 dependencies.append(510 {'type': 'role',511 'name': ref_name,512 'field': dep_type}513 )514 515 return dependencies516 517 def get_dependents(self, conn, object_id, where=None):518 """519 This function is used to fetch the dependents for the selected node.520 521 Args:522 conn: Connection object523 object_id: Object Id of the selected node.524 where: where clause for the sql query (optional)525 526 Returns: Dictionary of dependents for the selected node.527 """528 # Set the sql_path529 sql_path = 'depends/{0}/#{1}#'.format(530 conn.manager.server_type, conn.manager.version)531 532 if where is None:533 where_clause = "WHERE dep.refobjid={0}::oid".format(object_id)534 else:535 where_clause = where536 537 query = render_template("/".join([sql_path, 'dependents.sql']),538 where_clause=where_clause)539 # fetch the dependency for the selected object540 dependents = self.__fetch_dependency(conn, query)541 542 return dependents543 544 def __fetch_dependency(self, conn, query, show_system_objects=None,545 is_schema_diff=False):546 """547 This function is used to fetch the dependency for the selected node.548 549 Args:550 conn: Connection object551 query: sql query to fetch dependencies/dependents552 show_system_objects: System object status553 is_schema_diff: True when function gets called from schema diff.554 555 Returns: Dictionary of dependency for the selected node.556 """557 558 standard_types = {559 'r': None,560 'i': 'index',561 'S': 'sequence',562 'v': 'view',563 'p': 'partition_table',564 'f': 'foreign_table',565 'm': 'materialized_view',566 't': 'toast_table',567 'I': 'partition_index'568 }569 570 # Dictionary for the object types571 custom_types = {572 'x': 'external_table', 'n': 'schema', 'd': 'domain',573 'l': 'language', 'Cc': 'check', 'Cd': 'domain_constraints',574 'Cf': 'foreign_key', 'Cp': 'primary_key', 'Co': 'collation',575 'Cu': 'unique_constraint', 'Cx': 'exclusion_constraint',576 'Fw': 'foreign_data_wrapper', 'Fs': 'foreign_server',577 'Fc': 'fts_configuration', 'Fp': 'fts_parser',578 'Fd': 'fts_dictionary', 'Ft': 'fts_template',579 'Ex': 'extension', 'Et': 'event_trigger', 'Pa': 'package',580 'Pf': 'function', 'Pt': 'trigger_function', 'Pp': 'procedure',581 'Rl': 'rule', 'Rs': 'row_security_policy', 'Sy': 'synonym',582 'Ty': 'type', 'Tr': 'trigger', 'Tc': 'compound_trigger',583 'c': 'type',584 # None specified special handling for this type585 'A': None586 }587 588 # Merging above two dictionaries589 types = {**standard_types, **custom_types}590 591 # Dictionary for the restrictions592 dep_types = {593 # None specified special handling for this type594 'n': 'normal',595 'a': 'auto',596 'i': None,597 'p': None598 }599 600 status, result = conn.execute_dict(query)601 if not status:602 current_app.logger.error(result)603 604 dependency = list()605 606 for row in result['rows']:607 _ref_name = row['refname']608 type_str = row['type']609 dep_str = row['deptype']610 nsp_name = row['nspname']611 object_id = None612 if 'refobjid' in row:613 object_id = row['refobjid']614 615 ref_name = ''616 if nsp_name is not None:617 ref_name = nsp_name + '.'618 619 type_name = ''620 icon = None621 622 # Fetch the type name from the dictionary623 # if type is not present in the types dictionary then624 # we will continue and not going to add it.625 if len(type_str) and type_str in types and \626 types[type_str] is not None:627 type_name = types[type_str]628 if type_str == 'Rl':629 ref_name = \630 _ref_name + ' ON ' + ref_name + row['ownertable']631 _ref_name = None632 elif type_str == 'Cf':633 ref_name += row['ownertable'] + '.'634 elif type_str == 'm':635 icon = 'icon-mview'636 elif len(type_str) and type_str[0] in types and \637 types[type_str[0]] is None:638 # if type is present in the types dictionary, but it's639 # value is None then it requires special handling.640 if type_str[0] == 'r':641 if (len(type_str) > 1 and type_str[1].isdigit() and642 int(type_str[1]) > 0) or \643 (len(type_str) > 2 and type_str[2].isdigit() and644 int(type_str[2]) > 0):645 type_name = 'column'646 else:647 type_name = 'table'648 if 'is_inherits' in row and row['is_inherits'] == '1':649 if 'is_inherited' in row and \650 row['is_inherited'] == '1':651 icon = 'icon-table-multi-inherit'652 # For tables under partitioned tables,653 # is_inherits will be true and dependency654 # will be auto as it inherits from parent655 # partitioned table656 elif ('is_inherited' in row and657 row['is_inherited'] == '0') and \658 dep_str == 'a':659 type_name = 'partition'660 else:661 icon = 'icon-table-inherits'662 elif 'is_inherited' in row and \663 row['is_inherited'] == '1':664 icon = 'icon-table-inherited'665 elif type_str[0] == 'A':666 # Include only functions667 if row['adbin'].startswith('{FUNCEXPR'):668 type_name = 'function'669 ref_name = row['adsrc']670 else:671 continue672 else:673 continue674 675 if _ref_name is not None:676 ref_name += _ref_name677 678 # If schema diff is set to True then we don't need to calculate679 # field and also no need to add icon and field in the list.680 if is_schema_diff and type_name != 'schema':681 dependency.append(682 {683 'type': type_name,684 'name': ref_name,685 'oid': object_id686 }687 )688 elif not is_schema_diff:689 dep_type = ''690 if show_system_objects is None:691 show_system_objects = self.blueprint.show_system_objects692 if dep_str[0] in dep_types:693 # if dep_type is present in the dep_types dictionary,694 # but it's value is None then it requires special695 # handling.696 if dep_types[dep_str[0]] is None:697 if dep_str[0] == 'i':698 if show_system_objects:699 dep_type = 'internal'700 else:701 continue702 elif dep_str[0] == 'p':703 dep_type = 'pin'704 type_name = ''705 else:706 dep_type = dep_types[dep_str[0]]707 708 dependency.append(709 {710 'type': type_name,711 'name': ref_name,712 'field': dep_type,713 'icon': icon,714 }715 )716 717 return dependency718 719 def _check_cascade_operation(self, only_sql=None):720 """721 Check cascade operation.722 :param only_sql:723 :return:724 """725 if self.cmd == 'delete' or only_sql:726 # This is a cascade operation727 cascade = True728 else:729 cascade = False730 return cascade731 732 def not_found_error_msg(self, custom_label=None):733 return gettext("Could not find the specified {}.".format(734 custom_label if custom_label else self.node_label).lower())735