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 Database Node"""11 12import re13from functools import wraps14 15import json16from flask import render_template, current_app, request, jsonify17from flask_babel import gettext as _18from flask_security import current_user19 20from pgadmin.browser.server_groups import servers21from config import PG_DEFAULT_DRIVER22from pgadmin.browser.collection import CollectionNodeModule23from pgadmin.browser.server_groups.servers.databases.utils import \24 parse_sec_labels_from_db, parse_variables_from_db, \25 get_attributes_from_db_info26from pgadmin.browser.server_groups.servers.utils import parse_priv_from_db, \27 parse_priv_to_db28from pgadmin.browser.utils import PGChildNodeView29from pgadmin.utils.ajax import gone30from pgadmin.utils.ajax import make_json_response, \31 make_response as ajax_response, internal_server_error, unauthorized32from pgadmin.utils.driver import get_driver33from pgadmin.tools.sqleditor.utils.query_history import QueryHistory34 35from pgadmin.tools.schema_diff.node_registry import SchemaDiffRegistry36from pgadmin.model import db, Server, Database37from pgadmin.browser.utils import underscore_escape38 39 40class DatabaseModule(CollectionNodeModule):41 _NODE_TYPE = 'database'42 _COLLECTION_LABEL = _("Databases")43 44 _DATABASE_CSS_PATH = 'databases/css'45 _DATABASE_CSS = "/".join([_DATABASE_CSS_PATH, 'database.css'])46 47 def __init__(self, *args, **kwargs):48 self.min_ver = None49 self.max_ver = None50 51 super().__init__(*args, **kwargs)52 53 def get_nodes(self, gid, sid):54 """55 Generate the collection node56 """57 if self.show_node:58 yield self.generate_browser_collection_node(sid)59 60 @property61 def script_load(self):62 """63 Load the module script for server, when any of the server-group node is64 initialized.65 """66 return servers.ServerModule.node_type67 68 @property69 def csssnippets(self):70 """71 Returns a snippet of css to include in the page72 """73 snippets = [74 render_template(75 self._COLLECTION_CSS,76 node_type=self.node_type,77 _=_78 ),79 render_template(80 self._DATABASE_CSS,81 node_type=self.node_type,82 _=_83 )84 ]85 86 for submodule in self.submodules:87 snippets.extend(submodule.csssnippets)88 89 return snippets90 91 @property92 def module_use_template_javascript(self):93 """94 Returns whether Jinja2 template is used for generating the javascript95 module.96 """97 return False98 99 def register(self, app, options):100 """101 Override the default register function to automagically register102 sub-modules at once.103 """104 from .casts import blueprint as module105 self.submodules.append(module)106 107 from .event_triggers import blueprint as module108 self.submodules.append(module)109 110 from .extensions import blueprint as module111 self.submodules.append(module)112 113 from .foreign_data_wrappers import blueprint as module114 self.submodules.append(module)115 116 from .languages import blueprint as module117 self.submodules.append(module)118 119 from .publications import blueprint as module120 self.submodules.append(module)121 122 from .schemas import schema_blueprint as module123 self.submodules.append(module)124 125 from .schemas import catalog_blueprint as module126 self.submodules.append(module)127 128 from .subscriptions import blueprint as module129 self.submodules.append(module)130 131 from .dbms_job_scheduler import blueprint as module132 self.submodules.append(module)133 134 super().register(app, options)135 136 137blueprint = DatabaseModule(__name__)138 139 140class DatabaseView(PGChildNodeView):141 node_type = blueprint.node_type142 node_label = "Database"143 144 parent_ids = [145 {'type': 'int', 'id': 'gid'},146 {'type': 'int', 'id': 'sid'}147 ]148 ids = [149 {'type': 'int', 'id': 'did'}150 ]151 152 operations = dict({153 'obj': [154 {'get': 'properties', 'delete': 'delete', 'put': 'update'},155 {'get': 'list', 'post': 'create', 'delete': 'delete'}156 ],157 'nodes': [158 {'get': 'node'},159 {'get': 'nodes'}160 ],161 'get_databases': [162 {'get': 'get_databases'},163 {'get': 'get_databases'}164 ],165 'sql': [166 {'get': 'sql'}167 ],168 'msql': [169 {'get': 'msql'},170 {'get': 'msql'}171 ],172 'stats': [173 {'get': 'statistics'},174 {'get': 'statistics'}175 ],176 'dependency': [177 {'get': 'dependencies'}178 ],179 'dependent': [180 {'get': 'dependents'}181 ],182 'children': [183 {'get': 'children'}184 ],185 'connect': [186 {187 'get': 'connect_status',188 'post': 'connect',189 'delete': 'disconnect'190 }191 ],192 'get_encodings': [193 {'get': 'get_encodings'},194 {'get': 'get_encodings'}195 ],196 'get_ctypes': [197 {'get': 'get_ctypes'},198 {'get': 'get_ctypes'}199 ],200 'get_icu_locale': [201 {'get': 'get_icu_locale'},202 {'get': 'get_icu_locale'}203 ],204 'vopts': [205 {}, {'get': 'variable_options'}206 ],207 'delete': [{'delete': 'delete'},208 {'delete': 'delete'}]209 })210 211 def check_precondition(action=None):212 """213 This function will behave as a decorator which will checks214 database connection before running view, it will also attaches215 manager,conn & template_path properties to self216 """217 218 def wrap(f):219 @wraps(f)220 def wrapped(self, *args, **kwargs):221 self.manager = get_driver(222 PG_DEFAULT_DRIVER223 ).connection_manager(224 kwargs['sid']225 )226 if self.manager is None:227 return gone(errormsg=_("Could not find the server."))228 229 self.datistemplate = False230 if action and action in ["drop"]:231 self.conn = self.manager.connection()232 elif 'did' in kwargs:233 self.conn = self.manager.connection(did=kwargs['did'])234 self.db_allow_connection = True235 # If connection to database is not allowed then236 # provide generic connection237 if kwargs['did'] in self.manager.db_info:238 self._db = self.manager.db_info[kwargs['did']]239 self.datistemplate, datallowconn = \240 get_attributes_from_db_info(self.manager, kwargs)241 242 if datallowconn is False:243 self.conn = self.manager.connection()244 self.db_allow_connection = False245 246 else:247 self.conn = self.manager.connection()248 249 # set template path for sql scripts250 self.template_path = 'databases/sql/#{0}#'.format(251 self.manager.version252 )253 254 return f(self, *args, **kwargs)255 256 return wrapped257 258 return wrap259 260 @check_precondition(action="list")261 def list(self, gid, sid):262 last_system_oid = self.retrieve_last_system_oid()263 264 db_disp_res = None265 params = None266 if self.manager and self.manager.db_res:267 db_disp_res = ", ".join(268 ['%s'] * len(self.manager.db_res.split(','))269 )270 params = tuple(self.manager.db_res.split(','))271 272 SQL = render_template(273 "/".join([self.template_path, self._PROPERTIES_SQL]),274 conn=self.conn,275 last_system_oid=last_system_oid,276 db_restrictions=db_disp_res,277 )278 status, res = self.conn.execute_dict(SQL, params)279 280 if not status:281 return internal_server_error(errormsg=res)282 283 result_set = []284 for row in res['rows']:285 row['is_sys_obj'] = (286 row['did'] <= self._DATABASE_LAST_SYSTEM_OID or287 self.datistemplate)288 if self.skip_db(row):289 continue290 291 if self.manager.db == row['name']:292 row['canDrop'] = False293 else:294 row['canDrop'] = True295 296 result_set.append(row)297 298 return ajax_response(299 response=result_set,300 status=200301 )302 303 def retrieve_last_system_oid(self):304 last_system_oid = 0305 306 if not self.blueprint.show_system_objects:307 last_system_oid = self._DATABASE_LAST_SYSTEM_OID308 309 return last_system_oid310 311 def get_icon(self, res, connected):312 if not connected and not res['is_template']:313 icon = "icon-database-not-connected"314 elif not connected and res['is_template']:315 icon = 'icon-database-template-not-connected'316 elif connected and res['is_template']:317 icon = 'icon-database-template-connected'318 else:319 icon = "pg-icon-database"320 321 return icon322 323 def skip_db(self, row):324 325 if not self.blueprint.show_system_objects \326 and row['is_sys_obj'] \327 and row['name'] not in ('postgres', 'edb') \328 or not self.blueprint.show_database_template \329 and row['is_sys_obj'] \330 and row['name'] not in ('postgres', 'edb'):331 return True332 333 if not self.blueprint.show_database_template \334 and row['is_template'] and \335 not row['is_sys_obj'] and \336 row['name'] not in ('postgres', 'edb'):337 return True338 return False339 340 def get_nodes(self, gid, sid, is_schema_diff=False):341 res = []342 last_system_oid = self.retrieve_last_system_oid()343 344 # if is_schema_diff then no need to show system templates.345 if is_schema_diff and self.manager.db_info is not None and \346 self.manager.did in self.manager.db_info:347 last_system_oid = self._DATABASE_LAST_SYSTEM_OID348 349 server_node_res = self.manager350 351 db_disp_res = None352 params = None353 if server_node_res and server_node_res.db_res:354 db_disp_res = ", ".join(355 ['%s'] * len(server_node_res.db_res.split(','))356 )357 params = tuple(server_node_res.db_res.split(','))358 SQL = render_template(359 "/".join([self.template_path, self._NODES_SQL]),360 last_system_oid=last_system_oid,361 db_restrictions=db_disp_res,362 )363 status, rset = self.conn.execute_dict(SQL, params)364 365 if not status:366 return internal_server_error(errormsg=rset)367 368 for row in rset['rows']:369 dbname = row['name']370 row['is_sys_obj'] = (371 row['did'] <= self._DATABASE_LAST_SYSTEM_OID or372 self.datistemplate)373 374 if self.skip_db(row):375 continue376 if self.manager.db == dbname:377 connected = True378 can_drop = can_dis_conn = False379 else:380 conn = self.manager.connection(database=dbname, did=row['did'])381 connected = conn.connected()382 can_drop = can_dis_conn = True383 384 icon = self.get_icon(row, connected)385 386 res.append(387 self.blueprint.generate_browser_node(388 row['did'],389 sid,390 row['name'],391 icon=icon,392 connected=connected,393 tablespace=row['spcname'],394 allowConn=row['datallowconn'],395 canCreate=row['cancreate'],396 canDisconn=can_dis_conn,397 canDrop=can_drop,398 isTemplate=row['is_template'],399 inode=True if row['datallowconn'] else False,400 description=row['description']401 )402 )403 404 return res405 406 @check_precondition(action="nodes")407 def nodes(self, gid, sid, is_schema_diff=False):408 res = self.get_nodes(gid, sid, is_schema_diff)409 410 return make_json_response(411 data=res,412 status=200413 )414 415 @check_precondition(action="get_databases")416 def get_databases(self, gid, sid):417 """418 This function is used to get all the databases irrespective of419 show_system_object flag for templates in create database dialog.420 :param gid:421 :param sid:422 :return:423 """424 res = []425 SQL = render_template(426 "/".join([self.template_path, self._NODES_SQL]),427 last_system_oid=0,428 show_system_objects=True,429 )430 status, rset = self.conn.execute_dict(SQL)431 432 if not status:433 return internal_server_error(errormsg=rset)434 435 for row in rset['rows']:436 res.append(row['name'])437 438 return make_json_response(439 data=res,440 status=200441 )442 443 @check_precondition(action="node")444 def node(self, gid, sid, did):445 SQL = render_template(446 "/".join([self.template_path, self._NODES_SQL]),447 did=did, conn=self.conn, last_system_oid=0,448 show_system_objects=self.blueprint.show_system_objects,449 )450 status, rset = self.conn.execute_2darray(SQL)451 452 if not status:453 return internal_server_error(errormsg=rset)454 455 for row in rset['rows']:456 db = row['name']457 if self.manager.db == db:458 connected = True459 else:460 conn = self.manager.connection(database=row['name'])461 connected = conn.connected()462 icon_css_class = "pg-icon-database"463 if not connected:464 icon_css_class = "icon-database-not-connected"465 return make_json_response(466 data=self.blueprint.generate_browser_node(467 row['did'],468 sid,469 row['name'],470 icon=icon_css_class,471 connected=connected,472 spcname=row['spcname'],473 allowConn=row['datallowconn'],474 canCreate=row['cancreate']475 ),476 status=200477 )478 479 return gone(errormsg=self.not_found_error_msg())480 481 @check_precondition(action="properties")482 def properties(self, gid, sid, did):483 484 SQL = render_template(485 "/".join([self.template_path, self._PROPERTIES_SQL]),486 did=did, conn=self.conn, last_system_oid=0,487 show_system_objects=self.blueprint.show_system_objects488 )489 status, res = self.conn.execute_dict(SQL)490 491 if not status:492 return internal_server_error(errormsg=res)493 494 if len(res['rows']) == 0:495 return gone(496 self.not_found_error_msg()497 )498 499 SQL = render_template(500 "/".join([self.template_path, self._ACL_SQL]),501 did=did, conn=self.conn502 )503 status, dataclres = self.conn.execute_dict(SQL)504 if not status:505 return internal_server_error(errormsg=res)506 507 res = self.formatdbacl(res, dataclres['rows'])508 509 SQL = render_template(510 "/".join([self.template_path, 'defacl.sql']),511 did=did, conn=self.conn, grant_reovke_sql=False512 )513 status, defaclres = self.conn.execute_dict(SQL)514 if not status:515 return internal_server_error(errormsg=res)516 517 res = self.formatdbacl(res, defaclres['rows'])518 519 result = res['rows'][0]520 result['is_sys_obj'] = (521 result['oid'] <= self._DATABASE_LAST_SYSTEM_OID)522 # Fetching variable for database523 SQL = render_template(524 "/".join([self.template_path, 'get_variables.sql']),525 did=did, conn=self.conn526 )527 528 status, res1 = self.conn.execute_dict(SQL)529 database = Database.query.filter_by(id=did, server=sid).first()530 531 if database:532 result['schema_res'] = database.schema_res.split(533 ',') if database.schema_res else []534 535 if not status:536 return internal_server_error(errormsg=res1)537 538 # Get Formatted Security Labels539 if 'seclabels' in result:540 # Security Labels is not available for PostgreSQL <= 9.1541 frmtd_sec_labels = parse_sec_labels_from_db(result['seclabels'])542 result.update(frmtd_sec_labels)543 544 # Get Formatted Variables545 frmtd_variables = parse_variables_from_db(res1['rows'])546 result.update(frmtd_variables)547 548 return ajax_response(549 response=result,550 status=200551 )552 553 @staticmethod554 def formatdbacl(res, dbacl):555 for row in dbacl:556 priv = parse_priv_from_db(row)557 res['rows'][0].setdefault(row['deftype'], []).append(priv)558 return res559 560 def connect(self, gid, sid, did):561 """Connect the Database."""562 from pgadmin.utils.driver import get_driver563 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)564 conn = manager.connection(did=did, auto_reconnect=True)565 already_connected = conn.connected()566 if not already_connected:567 status, errmsg = conn.connect()568 if not status:569 current_app.logger.error(570 "Could not connected to database(#{0}).\nError: {1}"571 .format(572 did, errmsg573 )574 )575 576 return internal_server_error(errmsg)577 else:578 current_app.logger.info(579 'Connection Established for Database Id: \580 %s' % (did)581 )582 return make_json_response(583 success=1,584 info=_("Database connected."),585 data={586 'icon': 'pg-icon-database',587 'already_connected': already_connected,588 'connected': True,589 'info_prefix': '{0}/{1}'.590 format(Server.query.filter_by(id=sid)[0].name, conn.db)591 }592 )593 594 def disconnect(self, gid, sid, did):595 """Disconnect the database."""596 597 # Release Connection598 from pgadmin.utils.driver import get_driver599 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)600 conn = manager.connection(did=did, auto_reconnect=True)601 status = manager.release(did=did)602 603 if not status:604 return unauthorized(_("Database could not be disconnected."))605 else:606 return make_json_response(607 success=1,608 info=_("Database disconnected."),609 data={610 'icon': 'icon-database-not-connected',611 'connected': False,612 'info_prefix': '{0}/{1}'.613 format(Server.query.filter_by(id=sid)[0].name, conn.db)614 }615 )616 617 @check_precondition(action="get_encodings")618 def get_encodings(self, gid, sid, did=None):619 """620 This function to return list of avialable encodings621 """622 res = []623 SQL = render_template(624 "/".join([self.template_path, 'get_encodings.sql'])625 )626 status, rset = self.conn.execute_dict(SQL)627 if not status:628 return internal_server_error(errormsg=rset)629 630 for row in rset['rows']:631 res.append(632 {'label': row['encoding'], 'value': row['encoding']}633 )634 635 return make_json_response(636 data=res,637 status=200638 )639 640 @check_precondition(action="get_ctypes")641 def get_ctypes(self, gid, sid, did=None):642 """643 This function to return list of available collation/character types644 """645 res = []646 default_list = ['C', 'POSIX']647 for val in default_list:648 res.append(649 {'label': val, 'value': val}650 )651 SQL = render_template(652 "/".join([self.template_path, 'get_ctypes.sql'])653 )654 status, rset = self.conn.execute_dict(SQL)655 if not status:656 return internal_server_error(errormsg=rset)657 658 for row in rset['rows']:659 if row['cname'] not in default_list:660 res.append({'label': row['cname'], 'value': row['cname']})661 662 return make_json_response(663 data=res,664 status=200665 )666 667 @check_precondition(action="get_icu_locale")668 def get_icu_locale(self, gid, sid, did=None):669 """670 This function is used to get the list of icu locale671 """672 res = []673 SQL = render_template(674 "/".join([self.template_path, 'get_icu_locale.sql'])675 )676 status, rset = self.conn.execute_dict(SQL)677 if not status:678 return internal_server_error(errormsg=rset)679 680 for row in rset['rows']:681 res.append(682 {'label': row['colliculocale'], 'value': row['colliculocale']})683 684 return make_json_response(685 data=res,686 status=200687 )688 689 @check_precondition(action="create")690 def create(self, gid, sid):691 """Create the database."""692 required_args = [693 'name'694 ]695 696 data = request.form if request.form else json.loads(697 request.data698 )699 700 for arg in required_args:701 if arg not in data:702 return make_json_response(703 status=410,704 success=0,705 errormsg=_(706 "Could not find the required parameter ({})."707 ).format(arg)708 )709 # The below SQL will execute CREATE DDL only710 SQL = render_template(711 "/".join([self.template_path, self._CREATE_SQL]),712 data=data, conn=self.conn713 )714 status, msg = self.conn.execute_scalar(SQL)715 if not status:716 return internal_server_error(errormsg=msg)717 718 if 'datacl' in data:719 data['datacl'] = parse_priv_to_db(data['datacl'], 'DATABASE')720 721 # The below SQL will execute rest DMLs because we cannot execute722 # CREATE with any other723 SQL = render_template(724 "/".join([self.template_path, self._GRANT_SQL]),725 data=data, conn=self.conn726 )727 SQL = SQL.strip('\n').strip(' ')728 if SQL and SQL != "":729 status, msg = self.conn.execute_scalar(SQL)730 if not status:731 return internal_server_error(errormsg=msg)732 733 # We need oid of newly created database734 SQL = render_template(735 "/".join([self.template_path, self._PROPERTIES_SQL]),736 name=data['name'], conn=self.conn, last_system_oid=0,737 show_system_objects=self.blueprint.show_system_objects,738 )739 SQL = SQL.strip('\n').strip(' ')740 if SQL and SQL != "":741 status, res = self.conn.execute_dict(SQL)742 if not status:743 return internal_server_error(errormsg=res)744 745 response = res['rows'][0]746 # Add database entry into database table with schema_restrictions.747 database = Database(id=response['did'], server=sid,748 schema_res=','.join(data['schema_res']))749 db.session.add(database)750 db.session.commit()751 752 return jsonify(753 node=self.blueprint.generate_browser_node(754 response['did'],755 sid,756 response['name'],757 icon="icon-database-not-connected",758 connected=False,759 tablespace=response['default_tablespace'],760 allowConn=True,761 canCreate=response['cancreate'],762 canDisconn=True,763 canDrop=True,764 isTemplate=response['is_template']765 )766 )767 768 @staticmethod769 def _update_db_schema_res(data, did, sid):770 database = Database.query.filter_by(id=did, server=sid).first()771 if 'schema_res' in data:772 if database:773 data['schema_res'] = ','.join(data['schema_res'])774 setattr(database, 'schema_res', data['schema_res'])775 else:776 database_obj = Database(id=did, server=sid,777 schema_res=','.join(778 data['schema_res']))779 db.session.add(database_obj)780 781 def _check_rename_db_or_change_table_space(self, data, conn, all_ids):782 783 for action in ["rename_database", "tablespace"]:784 sql = self.get_offline_sql(all_ids['gid'], all_ids['sid'], data,785 all_ids['did'], action)786 sql = sql.strip('\n').strip(' ')787 if sql and sql != "":788 status, msg = conn.execute_scalar(sql)789 if not status:790 # In case of error from server while rename it,791 # reconnect to the database with old name again.792 self.conn = self.manager.connection(793 database=data['old_name'], auto_reconnect=True794 )795 status, errmsg = self.conn.connect()796 if not status:797 current_app.logger.error(798 'Could not reconnected to database(#{0}).\n'799 'Error: {1}'.format(all_ids['did'], errmsg)800 )801 return True, msg802 803 QueryHistory.update_history_dbname(804 current_user.id, all_ids['sid'], data['old_name'],805 data['name'])806 return False, ''807 808 def _fetch_db_details(self, data, did):809 if did is not None:810 # Fetch the name of database for comparison811 status, rset = self.conn.execute_dict(812 render_template(813 "/".join([self.template_path, self._NODES_SQL]),814 did=did, conn=self.conn, last_system_oid=0,815 show_system_objects=self.blueprint.show_system_objects,816 )817 )818 if not status:819 return True, rset820 821 if len(rset['rows']) == 0:822 return gone(823 _('Could not find the database on the server.')824 )825 826 data['old_name'] = (rset['rows'][0])['name']827 if 'name' not in data:828 data['name'] = data['old_name']829 return False, ''830 831 def _reconnect_connect_db(self, data, did):832 if self._db['datallowconn']:833 self.conn = self.manager.connection(834 database=data['name'], auto_reconnect=True835 )836 status, errmsg = self.conn.connect()837 838 if not status:839 current_app.logger.error(840 'Could not connected to database(#{0}).\n'841 'Error: {1}'.format(did, errmsg)842 )843 return True, errmsg844 return False, ''845 846 def _commit_db_changes(self, res, can_drop):847 if self.manager.db == res['name']:848 can_drop = False849 850 try:851 db.session.commit()852 except Exception as e:853 current_app.logger.exception(e)854 return True, e.message, False855 return False, '', can_drop856 857 def _get_data_from_request(self):858 return request.form if request.form else json.loads(859 request.data860 )861 862 @check_precondition(action='update')863 def update(self, gid, sid, did):864 """Update the database."""865 866 data = self._get_data_from_request()867 # Update schema restriction in db object.868 DatabaseView._update_db_schema_res(data, did, sid)869 870 # Generic connection for offline updates871 conn = self.manager.connection(conn_id='db_offline_update')872 status, errmsg = conn.connect()873 if not status:874 current_app.logger.error(875 "Could not create database connection for offline updates\n"876 "Err: {0}".format(errmsg)877 )878 return internal_server_error(errmsg)879 880 fetching_error, err_msg = self._fetch_db_details(data, did)881 if fetching_error:882 return internal_server_error(errormsg=err_msg)883 884 # Release any existing connection from connection manager885 # to perform offline operation886 self.manager.release(did=did)887 all_ids = {888 'gid': gid,889 'sid': sid,890 'did': did891 }892 is_error, errmsg = self._check_rename_db_or_change_table_space(data,893 conn,894 all_ids)895 if is_error:896 return internal_server_error(errmsg)897 898 # Make connection for database again899 connection_error, errmsg = self._reconnect_connect_db(data, did)900 if connection_error:901 return internal_server_error(errmsg)902 903 sql = self.get_online_sql(gid, sid, data, did)904 sql = sql.strip('\n').strip(' ')905 if sql and sql != "":906 status, msg = self.conn.execute_scalar(sql)907 if not status:908 return internal_server_error(errormsg=msg)909 910 # Release any existing connection from connection manager911 # used for offline updates912 self.manager.release(conn_id="db_offline_update")913 914 # Fetch the new data again after update for proper node915 # generation916 status, rset = self.conn.execute_dict(917 render_template(918 "/".join([self.template_path, self._NODES_SQL]),919 did=did, conn=self.conn, last_system_oid=0,920 show_system_objects=self.blueprint.show_system_objects,921 )922 )923 if not status:924 return internal_server_error(errormsg=rset)925 926 if len(rset['rows']) == 0:927 return gone(928 self.not_found_error_msg()929 )930 931 res = rset['rows'][0]932 933 can_drop = True934 error, errmsg, is_can_drop = self._commit_db_changes(res, can_drop)935 if error:936 return make_json_response(937 success=0,938 errormsg=errmsg939 )940 941 can_drop = can_dis_conn = is_can_drop942 943 icon = self.get_icon(res,944 self.conn.connected()945 if self._db['datallowconn'] else False)946 947 return jsonify(948 node=self.blueprint.generate_browser_node(949 did,950 sid,951 res['name'],952 icon=icon,953 connected=self.conn.connected() if954 self._db['datallowconn'] else False,955 tablespace=res['spcname'],956 allowConn=res['datallowconn'],957 canCreate=res['cancreate'],958 canDisconn=can_dis_conn,959 canDrop=can_drop,960 inode=True if res['datallowconn'] else False,961 isTemplate=res['is_template'],962 )963 )964 965 def _release_conn_before_delete(self, sid, did):966 """967 Check connection and release it before deleting database.968 :param sid: Server Id.969 :param did: Database Id.970 :return: Return error if any.971 """972 if self.conn.connected():973 # Release the connection if it is connected974 from pgadmin.utils.driver import get_driver975 manager = \976 get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)977 manager.connection(did=did, auto_reconnect=True)978 status = manager.release(did=did)979 980 if not status:981 return True, unauthorized(982 _("Database could not be deleted."))983 984 return False, ''985 986 @staticmethod987 def _get_req_data(did):988 """989 Get data from request.990 :param did: Database Id.991 :return: Return Data get from request.992 """993 994 if did is None:995 data = request.form if request.form else json.loads(996 request.data997 )998 else:999 data = {'ids': [did]}1000 1001 return data1002 1003 @check_precondition(action="drop")1004 def delete(self, gid, sid, did=None):1005 """Delete the database."""1006 1007 data = DatabaseView._get_req_data(did)1008 1009 for did in data['ids']:1010 default_conn = self.manager.connection()1011 sql = render_template(1012 "/".join([self.template_path, self._DELETE_SQL]),1013 did=did, conn=self.conn1014 )1015 status, res = default_conn.execute_scalar(sql)1016 if not status:1017 return internal_server_error(errormsg=res)1018 1019 if res is None:1020 return make_json_response(1021 status=410,1022 success=0,1023 errormsg=_(1024 'Error: Object not found.'1025 ),1026 info=_(1027 'The specified database could not be found.\n'1028 )1029 )1030 else:1031 is_error, errmsg = self._release_conn_before_delete(sid, did)1032 if is_error:1033 return errmsg1034 1035 sql = render_template(1036 "/".join([self.template_path, self._DELETE_SQL]),1037 datname=res, conn=self.conn,1038 with_force=self.cmd == 'delete'1039 )1040 1041 status, msg = default_conn.execute_scalar(sql)1042 if not status:1043 # reconnect if database drop failed.1044 conn = self.manager.connection(did=did,1045 auto_reconnect=True)1046 status, errmsg = conn.connect()1047 1048 return internal_server_error(1049 errormsg=underscore_escape(msg))1050 1051 return make_json_response(success=1)1052 1053 @check_precondition(action="msql")1054 def msql(self, gid, sid, did=None):1055 """1056 This function to return modified SQL.1057 """1058 data = {}1059 for k, v in request.args.items():1060 try:1061 # comments should be taken as is because if user enters a1062 # json comment it is parsed by loads which should not happen1063 if k in ('comments',):1064 data[k] = v1065 else:1066 data[k] = json.loads(v)1067 except ValueError:1068 data[k] = v1069 status, res = self.get_sql(gid, sid, data, did)1070 1071 if not status:1072 return res1073 1074 res = re.sub('\n{2,}', '\n\n', res)1075 SQL = res.strip('\n').strip(' ')1076 1077 return make_json_response(1078 data=SQL,1079 status=2001080 )1081 1082 def get_sql(self, gid, sid, data, did=None):1083 SQL = ''1084 if did is not None:1085 # Fetch the name of database for comparison1086 conn = self.manager.connection()1087 status, rset = conn.execute_dict(1088 render_template(1089 "/".join([self.template_path, self._NODES_SQL]),1090 did=did, conn=conn, last_system_oid=0,1091 show_system_objects=self.blueprint.show_system_objects,1092 )1093 )1094 if not status:1095 return False, internal_server_error(errormsg=rset)1096 1097 if len(rset['rows']) == 0:1098 return gone(1099 self.not_found_error_msg()1100 )1101 1102 data['old_name'] = (rset['rows'][0])['name']1103 if 'name' not in data:1104 data['name'] = data['old_name']1105 1106 SQL = ''1107 for action in ["rename_database", "tablespace"]:1108 SQL += self.get_offline_sql(gid, sid, data, did, action)1109 1110 SQL += self.get_online_sql(gid, sid, data, did)1111 else:1112 SQL += self.get_new_sql(gid, sid, data, did)1113 1114 return True, SQL1115 1116 def get_new_sql(self, gid, sid, data, did=None):1117 """1118 Generates sql for creating new database.1119 """1120 required_args = [1121 'name'1122 ]1123 1124 for arg in required_args:1125 if arg not in data:1126 return _(" -- definition incomplete")1127 1128 acls = []1129 1130 try:1131 acls = render_template(1132 "/".join([self.template_path, 'allowed_privs.json'])1133 )1134 acls = json.loads(acls)1135 except Exception as e:1136 current_app.logger.exception(e)1137 1138 # Privileges1139 for aclcol in acls:1140 if aclcol in data:1141 allowedacl = acls[aclcol]1142 data[aclcol] = parse_priv_to_db(1143 data[aclcol], allowedacl['acl']1144 )1145 1146 sql_acl = render_template(1147 "/".join([self.template_path, self._GRANT_SQL]),1148 data=data,1149 conn=self.conn1150 )1151 1152 SQL = render_template(1153 "/".join([self.template_path, self._CREATE_SQL]),1154 data=data, conn=self.conn1155 )1156 SQL += "\n"1157 SQL += sql_acl1158 return SQL1159 1160 def get_online_sql(self, gid, sid, data, did=None):1161 """1162 Generates sql for altering database which don not require1163 database to be disconnected before applying.1164 """1165 acls = []1166 try:1167 acls = render_template(1168 "/".join([self.template_path, 'allowed_privs.json'])1169 )1170 acls = json.loads(acls)1171 except Exception as e:1172 current_app.logger.exception(e)1173 1174 # Privileges1175 for aclcol in acls:1176 if aclcol in data:1177 allowedacl = acls[aclcol]1178 1179 for key in ['added', 'changed', 'deleted']:1180 if key in data[aclcol]:1181 data[aclcol][key] = parse_priv_to_db(1182 data[aclcol][key], allowedacl['acl']1183 )1184 1185 return render_template(1186 "/".join([self.template_path, 'alter_online.sql']),1187 data=data, conn=self.conn1188 )1189 1190 def get_offline_sql(self, gid, sid, data, did=None, action=None):1191 """1192 Generates sql for altering database which require1193 database to be disconnected before applying.1194 """1195 1196 return render_template(1197 "/".join([self.template_path, 'alter_offline.sql']),1198 data=data, conn=self.conn, action=action1199 )1200