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##########################################################################9import json10import re11from functools import wraps12 13from pgadmin.browser.server_groups import servers14from flask import render_template, make_response, request, jsonify, current_app15from flask_babel import gettext16from pgadmin.browser.collection import CollectionNodeModule17from pgadmin.browser.server_groups.servers.utils import parse_priv_from_db, \18 parse_priv_to_db19from pgadmin.browser.utils import PGChildNodeView20from pgadmin.utils.ajax import make_json_response, \21 make_response as ajax_response, internal_server_error, gone22from pgadmin.utils.ajax import precondition_required23from pgadmin.utils.driver import get_driver24from config import PG_DEFAULT_DRIVER25 26 27class TablespaceModule(CollectionNodeModule):28 _NODE_TYPE = 'tablespace'29 _COLLECTION_LABEL = gettext("Tablespaces")30 31 def __init__(self, import_name, **kwargs):32 super().__init__(import_name, **kwargs)33 34 def get_nodes(self, gid, sid):35 """36 Generate the collection node37 """38 yield self.generate_browser_collection_node(sid)39 40 @property41 def script_load(self):42 """43 Load the module script for server, when any of the server-group node is44 initialized.45 """46 return servers.ServerModule.node_type47 48 @property49 def module_use_template_javascript(self):50 """51 Returns whether Jinja2 template is used for generating the javascript52 module.53 """54 return False55 56 @property57 def node_inode(self):58 return False59 60 61blueprint = TablespaceModule(__name__)62 63 64class TablespaceView(PGChildNodeView):65 node_type = blueprint.node_type66 67 parent_ids = [68 {'type': 'int', 'id': 'gid'},69 {'type': 'int', 'id': 'sid'}70 ]71 ids = [72 {'type': 'int', 'id': 'tsid'}73 ]74 75 operations = dict({76 'obj': [77 {'get': 'properties', 'delete': 'delete', 'put': 'update'},78 {'get': 'list', 'post': 'create', 'delete': 'delete'}79 ],80 'nodes': [{'get': 'node'}, {'get': 'nodes'}],81 'children': [{'get': 'children'}],82 'sql': [{'get': 'sql'}],83 'msql': [{'get': 'msql'}, {'get': 'msql'}],84 'stats': [{'get': 'statistics'}, {'get': 'statistics'}],85 'dependency': [{'get': 'dependencies'}],86 'dependent': [{'get': 'dependents'}],87 'vopts': [{}, {'get': 'variable_options'}],88 'move_objects': [{'put': 'move_objects'}],89 'move_objects_sql': [{'get': 'move_objects_sql'}],90 })91 92 def check_precondition(f):93 """94 This function will behave as a decorator which will checks95 database connection before running view, it will also attaches96 manager,conn & template_path properties to self97 """98 99 @wraps(f)100 def wrap(*args, **kwargs):101 # Here args[0] will hold self & kwargs will hold gid,sid,tsid102 self = args[0]103 self.manager = get_driver(104 PG_DEFAULT_DRIVER105 ).connection_manager(106 kwargs['sid']107 )108 self.conn = self.manager.connection()109 self.datistemplate = False110 if (111 self.manager.db_info is not None and112 self.manager.did in self.manager.db_info and113 'datistemplate' in self.manager.db_info[self.manager.did]114 ):115 self.datistemplate = self.manager.db_info[116 self.manager.did]['datistemplate']117 118 # If DB not connected then return error to browser119 if not self.conn.connected():120 current_app.logger.warning(121 "Connection to the server has been lost."122 )123 return precondition_required(124 gettext(125 "Connection to the server has been lost."126 )127 )128 129 self.template_path = 'tablespaces/sql/#{0}#'.format(130 self.manager.version131 )132 current_app.logger.debug(133 "Using the template path: %s", self.template_path134 )135 # Allowed ACL on tablespace136 self.acl = ['C']137 138 return f(*args, **kwargs)139 140 return wrap141 142 @check_precondition143 def list(self, gid, sid):144 SQL = render_template(145 "/".join([self.template_path, self._PROPERTIES_SQL]),146 conn=self.conn147 )148 status, res = self.conn.execute_dict(SQL)149 150 if not status:151 return internal_server_error(errormsg=res)152 return ajax_response(153 response=res['rows'],154 status=200155 )156 157 @check_precondition158 def node(self, gid, sid, tsid):159 SQL = render_template(160 "/".join([self.template_path, self._NODES_SQL]),161 tsid=tsid, conn=self.conn162 )163 status, rset = self.conn.execute_2darray(SQL)164 if not status:165 return internal_server_error(errormsg=rset)166 167 if len(rset['rows']) == 0:168 return gone(gettext("""Could not find the tablespace."""))169 170 res = self.blueprint.generate_browser_node(171 rset['rows'][0]['oid'],172 sid,173 rset['rows'][0]['name'],174 icon="icon-tablespace"175 )176 177 return make_json_response(178 data=res,179 status=200180 )181 182 @check_precondition183 def nodes(self, gid, sid, tsid=None):184 res = []185 SQL = render_template(186 "/".join([self.template_path, self._NODES_SQL]),187 tsid=tsid, conn=self.conn188 )189 status, rset = self.conn.execute_2darray(SQL)190 if not status:191 return internal_server_error(errormsg=rset)192 193 for row in rset['rows']:194 res.append(195 self.blueprint.generate_browser_node(196 row['oid'],197 sid,198 row['name'],199 icon="icon-tablespace",200 description=row['description']201 ))202 203 return make_json_response(204 data=res,205 status=200206 )207 208 def _formatter(self, data, tsid=None):209 """210 Args:211 data: dict of query result212 tsid: tablespace oid213 214 Returns:215 It will return formatted output of collections216 """217 # We need to format variables according to client js collection218 if 'spcoptions' in data and data['spcoptions'] is not None:219 spcoptions = []220 for spcoption in data['spcoptions']:221 k, v = spcoption.split('=')222 spcoptions.append({'name': k, 'value': v})223 224 data['spcoptions'] = spcoptions225 226 # Need to format security labels according to client js collection227 if 'seclabels' in data and data['seclabels'] is not None:228 seclabels = []229 for seclbls in data['seclabels']:230 k, v = seclbls.split('=')231 seclabels.append({'provider': k, 'label': v})232 233 data['seclabels'] = seclabels234 235 # We need to parse & convert ACL coming from database to json format236 SQL = render_template(237 "/".join([self.template_path, self._ACL_SQL]),238 tsid=tsid, conn=self.conn239 )240 status, acl = self.conn.execute_dict(SQL)241 if not status:242 return internal_server_error(errormsg=acl)243 244 # We will set get privileges from acl sql so we don't need245 # it from properties sql246 data['spcacl'] = []247 248 for row in acl['rows']:249 priv = parse_priv_from_db(row)250 if row['deftype'] in data:251 data[row['deftype']].append(priv)252 else:253 data[row['deftype']] = [priv]254 255 return data256 257 @check_precondition258 def properties(self, gid, sid, tsid):259 SQL = render_template(260 "/".join([self.template_path, self._PROPERTIES_SQL]),261 tsid=tsid, conn=self.conn262 )263 status, res = self.conn.execute_dict(SQL)264 if not status:265 return internal_server_error(errormsg=res)266 267 if len(res['rows']) == 0:268 return gone(269 gettext("""Could not find the tablespace information.""")270 )271 272 # Making copy of output for future use273 copy_data = dict(res['rows'][0])274 copy_data['is_sys_obj'] = (275 copy_data['oid'] <= self._DATABASE_LAST_SYSTEM_OID or276 self.datistemplate)277 copy_data = self._formatter(copy_data, tsid)278 279 return ajax_response(280 response=copy_data,281 status=200282 )283 284 @check_precondition285 def create(self, gid, sid):286 """287 This function will creates new the tablespace object288 """289 290 required_args = {291 'name': 'Name',292 'spclocation': 'Location'293 }294 295 data = request.form if request.form else json.loads(296 request.data297 )298 299 for arg in required_args:300 if arg not in data:301 return make_json_response(302 status=410,303 success=0,304 errormsg=gettext(305 "Could not find the required parameter ({})."306 ).format(arg)307 )308 309 # To format privileges coming from client310 if 'spcacl' in data:311 data['spcacl'] = parse_priv_to_db(data['spcacl'], ['C'])312 313 try:314 SQL = render_template(315 "/".join([self.template_path, self._CREATE_SQL]),316 data=data, conn=self.conn317 )318 319 status, res = self.conn.execute_scalar(SQL)320 321 if not status:322 return internal_server_error(errormsg=res)323 324 # To fetch the oid of newly created tablespace325 SQL = render_template(326 "/".join([self.template_path, self._ALTER_SQL]),327 tablespace=data['name'], conn=self.conn328 )329 330 status, tsid = self.conn.execute_scalar(SQL)331 332 if not status:333 return internal_server_error(errormsg=tsid)334 335 SQL = render_template(336 "/".join([self.template_path, self._ALTER_SQL]),337 data=data, conn=self.conn338 )339 340 # Checking if we are not executing empty query341 if SQL and SQL.strip('\n') and SQL.strip(' '):342 status, res = self.conn.execute_scalar(SQL)343 if not status:344 return jsonify(345 node=self.blueprint.generate_browser_node(346 tsid,347 sid,348 data['name'],349 icon="icon-tablespace"350 ),351 success=0,352 errormsg=gettext(353 'Tablespace created successfully, '354 'Set parameter fail: {0}'.format(res)355 ),356 info=gettext(357 res358 )359 )360 361 other_node_info = {}362 if 'description' in data:363 other_node_info['description'] = data['description']364 365 return jsonify(366 node=self.blueprint.generate_browser_node(367 tsid,368 sid,369 data['name'],370 icon="icon-tablespace",371 **other_node_info372 )373 )374 except Exception as e:375 current_app.logger.exception(e)376 return internal_server_error(errormsg=str(e))377 378 @check_precondition379 def update(self, gid, sid, tsid):380 """381 This function will update tablespace object382 """383 data = request.form if request.form else json.loads(384 request.data385 )386 387 try:388 SQL, name = self.get_sql(gid, sid, data, tsid)389 # Most probably this is due to error390 if not isinstance(SQL, str):391 return SQL392 393 SQL = SQL.strip('\n').strip(' ')394 status, res = self.conn.execute_scalar(SQL)395 if not status:396 return internal_server_error(errormsg=res)397 398 other_node_info = {}399 if 'description' in data:400 other_node_info['description'] = data['description']401 402 return jsonify(403 node=self.blueprint.generate_browser_node(404 tsid,405 sid,406 name,407 icon="icon-%s" % self.node_type,408 **other_node_info409 )410 )411 except Exception as e:412 current_app.logger.exception(e)413 return internal_server_error(errormsg=str(e))414 415 @check_precondition416 def delete(self, gid, sid, tsid=None):417 """418 This function will drop the tablespace object419 """420 if tsid is None:421 data = request.form if request.form else json.loads(422 request.data423 )424 else:425 data = {'ids': [tsid]}426 427 try:428 for tsid in data['ids']:429 # Get name for tablespace from tsid430 status, rset = self.conn.execute_dict(431 render_template(432 "/".join([self.template_path, self._NODES_SQL]),433 tsid=tsid, conn=self.conn434 )435 )436 437 if not status:438 return internal_server_error(errormsg=rset)439 440 if not rset['rows']:441 return make_json_response(442 success=0,443 errormsg=gettext(444 'Error: Object not found.'445 ),446 info=gettext(447 'The specified tablespace could not be found.\n'448 )449 )450 451 # drop tablespace452 SQL = render_template(453 "/".join([self.template_path, self._DELETE_SQL]),454 tsname=(rset['rows'][0])['name'], conn=self.conn455 )456 457 status, res = self.conn.execute_scalar(SQL)458 if not status:459 return internal_server_error(errormsg=res)460 461 return make_json_response(462 success=1,463 info=gettext("Tablespace dropped")464 )465 466 except Exception as e:467 current_app.logger.exception(e)468 return internal_server_error(errormsg=str(e))469 470 @check_precondition471 def msql(self, gid, sid, tsid=None):472 """473 This function to return modified SQL474 """475 data = dict()476 for k, v in request.args.items():477 try:478 # comments should be taken as is because if user enters a479 # json comment it is parsed by loads which should not happen480 if k in ('description',):481 data[k] = v482 else:483 data[k] = json.loads(v)484 except ValueError as ve:485 current_app.logger.exception(ve)486 data[k] = v487 488 sql, _ = self.get_sql(gid, sid, data, tsid)489 # Most probably this is due to error490 if not isinstance(sql, str):491 return sql492 493 sql = sql.strip('\n').strip(' ')494 if sql == '':495 sql = "--modified SQL"496 return make_json_response(497 data=sql,498 status=200499 )500 501 def _format_privilege_data(self, data):502 for key in ['spcacl']:503 if key in data and data[key] is not None:504 if 'added' in data[key]:505 data[key]['added'] = parse_priv_to_db(506 data[key]['added'], self.acl507 )508 if 'changed' in data[key]:509 data[key]['changed'] = parse_priv_to_db(510 data[key]['changed'], self.acl511 )512 if 'deleted' in data[key]:513 data[key]['deleted'] = parse_priv_to_db(514 data[key]['deleted'], self.acl515 )516 517 def get_sql(self, gid, sid, data, tsid=None):518 """519 This function will genrate sql from model/properties data520 """521 required_args = [522 'name'523 ]524 525 if tsid is not None:526 SQL = render_template(527 "/".join([self.template_path, self._PROPERTIES_SQL]),528 tsid=tsid, conn=self.conn529 )530 status, res = self.conn.execute_dict(SQL)531 if not status:532 return internal_server_error(errormsg=res)533 534 if len(res['rows']) == 0:535 return gone(536 gettext("Could not find the tablespace on the server.")537 )538 539 # Making copy of output for further processing540 old_data = dict(res['rows'][0])541 old_data = self._formatter(old_data, tsid)542 543 # To format privileges data coming from client544 self._format_privilege_data(data)545 546 # If name is not present with in update data then copy it547 # from old data548 for arg in required_args:549 if arg not in data:550 data[arg] = old_data[arg]551 552 SQL = render_template(553 "/".join([self.template_path, self._UPDATE_SQL]),554 data=data, o_data=old_data, conn=self.conn555 )556 else:557 # To format privileges coming from client558 if 'spcacl' in data:559 data['spcacl'] = parse_priv_to_db(data['spcacl'], self.acl)560 # If the request for new object which do not have tsid561 SQL = render_template(562 "/".join([self.template_path, self._CREATE_SQL]),563 data=data, conn=self.conn564 )565 SQL += "\n"566 SQL += render_template(567 "/".join([self.template_path, self._ALTER_SQL]),568 data=data, conn=self.conn569 )570 SQL = re.sub('\n{2,}', '\n\n', SQL)571 return SQL, data['name'] if 'name' in data else old_data['name']572 573 @check_precondition574 def sql(self, gid, sid, tsid):575 """576 This function will generate sql for sql panel577 """578 SQL = render_template(579 "/".join([self.template_path, self._PROPERTIES_SQL]),580 tsid=tsid, conn=self.conn581 )582 status, res = self.conn.execute_dict(SQL)583 if not status:584 return internal_server_error(errormsg=res)585 586 if len(res['rows']) == 0:587 return gone(588 gettext("Could not find the tablespace on the server.")589 )590 # Making copy of output for future use591 old_data = dict(res['rows'][0])592 593 old_data = self._formatter(old_data, tsid)594 595 # To format privileges596 if 'spcacl' in old_data:597 old_data['spcacl'] = parse_priv_to_db(old_data['spcacl'], self.acl)598 599 SQL = ''600 # We are not showing create sql for system tablespace601 if not old_data['name'].startswith('pg_'):602 SQL = render_template(603 "/".join([self.template_path, self._CREATE_SQL]),604 data=old_data605 )606 SQL += "\n"607 SQL += render_template(608 "/".join([self.template_path, self._ALTER_SQL]),609 data=old_data, conn=self.conn610 )611 612 sql_header = """613-- Tablespace: {0}614 615-- DROP TABLESPACE IF EXISTS {0};616 617""".format(old_data['name'])618 619 SQL = sql_header + SQL620 SQL = re.sub('\n{2,}', '\n\n', SQL)621 return ajax_response(response=SQL.strip('\n'))622 623 @check_precondition624 def variable_options(self, gid, sid):625 """626 Args:627 gid:628 sid:629 630 Returns:631 This function will return list of variables available for632 table spaces.633 """634 ver = self.manager.version635 if ver >= 90600:636 SQL = render_template(637 "/".join(['tablespaces/sql/default', 'variables.sql'])638 )639 else:640 SQL = render_template(641 "/".join([self.template_path, 'variables.sql'])642 )643 status, rset = self.conn.execute_dict(SQL)644 645 if not status:646 return internal_server_error(errormsg=rset)647 648 return make_json_response(649 data=rset['rows'],650 status=200651 )652 653 @check_precondition654 def statistics(self, gid, sid, tsid=None):655 """656 This function will return data for statistics panel657 """658 SQL = render_template(659 "/".join([self.template_path, 'stats.sql']),660 tsid=tsid, conn=self.conn661 )662 status, res = self.conn.execute_dict(SQL)663 664 if not status:665 return internal_server_error(errormsg=res)666 667 return make_json_response(668 data=res,669 status=200670 )671 672 @check_precondition673 def dependencies(self, gid, sid, tsid):674 """675 This function gets the dependencies and returns an ajax response676 for the tablespace.677 678 Args:679 gid: Server Group ID680 sid: Server ID681 tsid: Tablespace ID682 """683 dependencies_result = self.get_dependencies(self.conn, tsid)684 return ajax_response(685 response=dependencies_result,686 status=200687 )688 689 @check_precondition690 def dependents(self, gid, sid, tsid):691 """692 This function gets the dependents and returns an ajax response693 for the tablespace.694 695 Args:696 gid: Server Group ID697 sid: Server ID698 tsid: Tablespace ID699 """700 dependents_result = self.get_dependents(self.conn, sid, tsid)701 return ajax_response(702 response=dependents_result,703 status=200704 )705 706 def _handel_dependents_type(self, types, type_str, row, rel_name):707 type_name = ''708 if types[type_str[0]] is None:709 if type_str[0] == 'i':710 type_name = 'index'711 rel_name = row['indname'] + ' ON ' + rel_name712 elif type_str[0] == 'o':713 type_name = 'operator'714 rel_name = row['relname']715 else:716 type_name = types[type_str[0]]717 return type_name, rel_name718 719 def _check_dependents_type(self, types, dependents, db_row, result):720 for row in result['rows']:721 rel_name = row['nspname']722 if rel_name is not None:723 rel_name += '.'724 725 if rel_name is None:726 rel_name = row['relname']727 else:728 rel_name += row['relname']729 730 type_str = row['relkind']731 # Fetch the type name from the dictionary732 # if type is not present in the types dictionary then733 # we will continue and not going to add it.734 if type_str[0] in types:735 # if type is present in the types dictionary, but it's736 # value is None then it requires special handling.737 type_name, rel_name = self._handel_dependents_type(types,738 type_str,739 row,740 rel_name)741 else:742 continue743 744 dependents.append(745 {746 'type': type_name,747 'name': rel_name,748 'field': db_row['datname']749 }750 )751 752 def _create_dependents_data(self, types, result, dependents, db_row,753 is_connected, manager):754 755 self._check_dependents_type(types, dependents, db_row, result)756 757 # Release only those connections which we have created above.758 if not is_connected:759 manager.release(db_row['datname'])760 761 def get_dependents(self, conn, sid, tsid):762 """763 This function is used to fetch the dependents for the selected node.764 765 Args:766 conn: Connection object767 sid: Server Id768 tsid: Tablespace ID769 770 Returns: Dictionary of dependents for the selected node.771 """772 # Dictionary for the object types773 types = {774 # None specified special handling for this type775 'r': 'table',776 'i': None,777 'S': 'sequence',778 'v': 'view',779 'x': 'external_table',780 'p': 'function',781 'n': 'schema',782 'y': 'type',783 'd': 'domain',784 'T': 'trigger_function',785 'C': 'conversion',786 'o': None787 }788 789 # Fetching databases with CONNECT privileges status.790 query = render_template(791 "/".join([self.template_path, 'dependents.sql']),792 fetch_database=True793 )794 status, db_result = self.conn.execute_dict(query)795 if not status:796 current_app.logger.error(db_result)797 798 dependents = list()799 800 # Get the server manager801 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)802 803 for db_row in db_result['rows']:804 oid = db_row['dattablespace']805 806 # Append all the databases to the dependents list if oid is same807 if tsid == oid:808 dependents.append({809 'type': 'database', 'name': '', 'field': db_row['datname']810 })811 812 # If connection to the database is not allowed then continue813 # with the next database814 if not db_row['datallowconn']:815 continue816 817 # Get the connection from the manager for the specified database.818 # Check the connect status and if it is not connected then create819 # a new connection to run the query and fetch the dependents.820 is_connected = True821 try:822 temp_conn = manager.connection(database=db_row['datname'])823 is_connected = temp_conn.connected()824 if not is_connected:825 temp_conn.connect()826 except Exception as e:827 current_app.logger.exception(e)828 829 if temp_conn.connected():830 query = render_template(831 "/".join([self.template_path, 'dependents.sql']),832 fetch_dependents=True, tsid=tsid833 )834 status, result = temp_conn.execute_dict(query)835 if not status:836 current_app.logger.error(result)837 838 self._create_dependents_data(types, result, dependents, db_row,839 is_connected, manager)840 841 return dependents842 843 844TablespaceView.register_node_view(blueprint)845