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"""A blueprint module implementing the erd tool."""11import json12 13from flask import url_for, request, Response14from flask import render_template, current_app as app15from pgadmin.user_login_check import pga_login_required16from flask_babel import gettext17from werkzeug.user_agent import UserAgent18from pgadmin.utils import PgAdminModule, \19 SHORTCUT_FIELDS as shortcut_fields20from pgadmin.utils.ajax import make_json_response, bad_request, \21 internal_server_error22from pgadmin.model import Server23from config import PG_DEFAULT_DRIVER24from pgadmin.utils.driver import get_driver25from pgadmin.browser.utils import underscore_unescape26from pgadmin.browser.server_groups.servers.databases.schemas.utils \27 import get_schemas28from pgadmin.browser.server_groups.servers.databases.schemas.tables. \29 constraints.foreign_key import utils as fkey_utils30from pgadmin.utils.constants import PREF_LABEL_KEYBOARD_SHORTCUTS, \31 PREF_LABEL_DISPLAY, PREF_LABEL_OPTIONS32from .utils import ERDHelper33from pgadmin.utils.exception import ConnectionLost34from pgadmin.authenticate import socket_login_required35from ... import socketio36 37MODULE_NAME = 'erd'38SOCKETIO_NAMESPACE = '/{0}'.format(MODULE_NAME)39 40 41class ERDModule(PgAdminModule):42 """43 class ERDModule(PgAdminModule)44 45 A module class for ERD derived from PgAdminModule.46 """47 48 LABEL = gettext("ERD tool")49 50 def get_own_menuitems(self):51 return {}52 53 def get_exposed_url_endpoints(self):54 """55 Returns:56 list: URL endpoints57 """58 return [59 'erd.panel',60 'erd.initialize',61 'erd.prequisite',62 'erd.sql',63 'erd.close'64 ]65 66 def register_preferences(self):67 self.preference.register(68 'keyboard_shortcuts',69 'open_project',70 gettext('Open project'),71 'keyboardshortcut',72 {73 'alt': False,74 'shift': False,75 'control': True,76 'key': {77 'key_code': 79,78 'char': 'o'79 }80 },81 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,82 fields=shortcut_fields83 )84 85 self.preference.register(86 'keyboard_shortcuts',87 'save_project',88 gettext('Save project'),89 'keyboardshortcut',90 {91 'alt': False,92 'shift': False,93 'control': True,94 'key': {95 'key_code': 83,96 'char': 's'97 }98 },99 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,100 fields=shortcut_fields101 )102 103 self.preference.register(104 'keyboard_shortcuts',105 'save_project_as',106 gettext('Save project as'),107 'keyboardshortcut',108 {109 'alt': False,110 'shift': True,111 'control': True,112 'key': {113 'key_code': 83,114 'char': 's'115 }116 },117 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,118 fields=shortcut_fields119 )120 121 self.preference.register(122 'keyboard_shortcuts',123 'generate_sql',124 gettext('Generate SQL'),125 'keyboardshortcut',126 {127 'alt': True,128 'shift': False,129 'control': True,130 'key': {131 'key_code': 83,132 'char': 's'133 }134 },135 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,136 fields=shortcut_fields137 )138 139 self.preference.register(140 'keyboard_shortcuts',141 'download_image',142 gettext('Download image'),143 'keyboardshortcut',144 {145 'alt': True,146 'shift': False,147 'control': True,148 'key': {149 'key_code': 73,150 'char': 'i'151 }152 },153 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,154 fields=shortcut_fields155 )156 157 self.preference.register(158 'keyboard_shortcuts',159 'add_table',160 gettext('Add table'),161 'keyboardshortcut',162 {163 'alt': True,164 'shift': False,165 'control': True,166 'key': {167 'key_code': 65,168 'char': 'a'169 }170 },171 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,172 fields=shortcut_fields173 )174 175 self.preference.register(176 'keyboard_shortcuts',177 'edit_table',178 gettext('Edit table'),179 'keyboardshortcut',180 {181 'alt': True,182 'shift': False,183 'control': True,184 'key': {185 'key_code': 69,186 'char': 'e'187 }188 },189 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,190 fields=shortcut_fields191 )192 193 self.preference.register(194 'keyboard_shortcuts',195 'clone_table',196 gettext('Clone table'),197 'keyboardshortcut',198 {199 'alt': True,200 'shift': False,201 'control': True,202 'key': {203 'key_code': 67,204 'char': 'c'205 }206 },207 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,208 fields=shortcut_fields209 )210 211 self.preference.register(212 'keyboard_shortcuts',213 'drop_table',214 gettext('Drop table'),215 'keyboardshortcut',216 {217 'alt': True,218 'shift': False,219 'control': True,220 'key': {221 'key_code': 68,222 'char': 'd'223 }224 },225 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,226 fields=shortcut_fields227 )228 229 self.preference.register(230 'keyboard_shortcuts',231 'add_edit_note',232 gettext('Add/Edit note'),233 'keyboardshortcut',234 {235 'alt': True,236 'shift': False,237 'control': True,238 'key': {239 'key_code': 78,240 'char': 'n'241 }242 },243 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,244 fields=shortcut_fields245 )246 247 self.preference.register(248 'keyboard_shortcuts',249 'one_to_many',250 gettext('One to many link'),251 'keyboardshortcut',252 {253 'alt': True,254 'shift': False,255 'control': True,256 'key': {257 'key_code': 79,258 'char': 'o'259 }260 },261 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,262 fields=shortcut_fields263 )264 265 self.preference.register(266 'keyboard_shortcuts',267 'many_to_many',268 gettext('Many to many link'),269 'keyboardshortcut',270 {271 'alt': True,272 'shift': False,273 'control': True,274 'key': {275 'key_code': 77,276 'char': 'm'277 }278 },279 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,280 fields=shortcut_fields281 )282 283 self.preference.register(284 'keyboard_shortcuts',285 'auto_align',286 gettext('Auto align'),287 'keyboardshortcut',288 {289 'alt': True,290 'shift': False,291 'control': True,292 'key': {293 'key_code': 76,294 'char': 'l'295 }296 },297 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,298 fields=shortcut_fields299 )300 301 self.preference.register(302 'keyboard_shortcuts',303 'show_details',304 gettext('Show more/fewer details'),305 'keyboardshortcut',306 {307 'alt': True,308 'shift': False,309 'control': True,310 'key': {311 'key_code': 84,312 'char': 't'313 }314 },315 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,316 fields=shortcut_fields317 )318 319 self.preference.register(320 'keyboard_shortcuts',321 'zoom_to_fit',322 gettext('Zoom to fit'),323 'keyboardshortcut',324 {325 'alt': True,326 'shift': True,327 'control': False,328 'key': {329 'key_code': 70,330 'char': 'f'331 }332 },333 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,334 fields=shortcut_fields335 )336 337 self.preference.register(338 'keyboard_shortcuts',339 'zoom_in',340 gettext('Zoom in'),341 'keyboardshortcut',342 {343 'alt': True,344 'shift': True,345 'control': False,346 'key': {347 'key_code': 187,348 'char': '+'349 }350 },351 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,352 fields=shortcut_fields353 )354 355 self.preference.register(356 'keyboard_shortcuts',357 'zoom_out',358 gettext('Zoom out'),359 'keyboardshortcut',360 {361 'alt': True,362 'shift': True,363 'control': False,364 'key': {365 'key_code': 189,366 'char': '-'367 }368 },369 category_label=PREF_LABEL_KEYBOARD_SHORTCUTS,370 fields=shortcut_fields371 )372 373 self.preference.register(374 'options',375 'sql_with_drop',376 gettext('SQL With DROP Table'),377 'boolean',378 False,379 category_label=PREF_LABEL_OPTIONS,380 help_str=gettext(381 'If enabled, the SQL generated by the ERD Tool will add '382 'DROP table DDL before each CREATE table DDL.'383 )384 )385 386 self.preference.register(387 'options',388 'table_relation_depth',389 gettext('Table Relation Depth'),390 'integer',391 -1,392 category_label=PREF_LABEL_OPTIONS,393 help_str=gettext(394 'The maximum depth pgAdmin should traverse to find '395 'related tables when generating an ERD for a table. '396 'Use -1 for no limit.'397 )398 )399 400 self.preference.register(401 'options', 'cardinality_notation',402 gettext('Cardinality Notation'), 'radioModern', 'crows',403 category_label=PREF_LABEL_OPTIONS, options=[404 {'label': gettext('Crow\'s foot'), 'value': 'crows'},405 {'label': gettext('Chen'), 'value': 'chen'},406 ],407 help_str=gettext(408 'Notation to be used to present cardinality.'409 )410 )411 412 self.preference.register(413 'options',414 'sql_with_drop',415 gettext('SQL With DROP Table'),416 'boolean',417 False,418 category_label=PREF_LABEL_OPTIONS,419 help_str=gettext(420 'If enabled, the SQL generated by the ERD Tool will add '421 'DROP table DDL before each CREATE table DDL.'422 )423 )424 425 426blueprint = ERDModule(MODULE_NAME, __name__, static_url_path='/static')427 428 429@blueprint.route(430 '/panel/<int:trans_id>',431 methods=["POST"],432 endpoint='panel'433)434@pga_login_required435def panel(trans_id):436 """437 This method calls index.html to render the erd tool.438 439 Args:440 panel_title: Title of the panel441 """442 443 params = {444 'trans_id': trans_id,445 'title': request.form['title']446 }447 if request.args:448 params.update({k: v for k, v in request.args.items()})449 450 if 'gen' in params:451 params['gen'] = True if params['gen'] == 'true' else False452 453 # We need client OS information to render correct Keyboard shortcuts454 user_agent = UserAgent(request.headers.get('User-Agent'))455 456 """457 Animations and transitions are not automatically GPU accelerated and by458 default use browser's slow rendering engine. We need to set 'translate3d'459 value of '-webkit-transform' property in order to use GPU. After applying460 this property under linux, Webkit calculates wrong position of the461 elements so panel contents are not visible. To make it work, we need to462 explicitly set '-webkit-transform' property to 'none' for .ajs-notifier,463 .ajs-message, .ajs-modal classes.464 465 This issue is only with linux runtime application and observed in Query466 tool and debugger. When we open 'Open File' dialog then whole Query tool467 panel content is not visible though it contains HTML element in back end.468 469 The port number should have already been set by the runtime if we're470 running in desktop mode.471 """472 is_linux_platform = False473 474 from sys import platform as _platform475 if "linux" in _platform:476 is_linux_platform = True477 478 s = Server.query.filter_by(id=int(params['sid'])).first()479 480 params.update({481 'bgcolor': s.bgcolor,482 'fgcolor': s.fgcolor,483 'client_platform': user_agent.platform,484 'is_desktop_mode': app.PGADMIN_RUNTIME,485 'is_linux': is_linux_platform486 })487 488 return render_template(489 "erd/index.html",490 title=underscore_unescape(params['title']),491 params=json.dumps(params),492 )493 494 495@blueprint.route(496 '/initialize/<int:trans_id>/<int:sgid>/<int:sid>/<int:did>',497 methods=["POST"], endpoint='initialize'498)499@pga_login_required500def initialize_erd(trans_id, sgid, sid, did):501 """502 This method is responsible for instantiating and initializing503 the erd tool object. It will also create a unique504 transaction id and store the information into session variable.505 506 Args:507 sgid: Server group Id508 sid: Server Id509 did: Database Id510 """511 # Read the data if present. Skipping read may cause connection512 # reset error if data is sent from the client513 if request.data:514 _ = request.data515 516 conn = _get_connection(sid, did, trans_id)517 518 return make_json_response(519 data={520 'connId': str(trans_id),521 'database': conn.db,522 'serverVersion': conn.manager.version,523 }524 )525 526 527def _get_connection(sid, did, trans_id):528 """529 Get the connection object of ERD.530 :param sid:531 :param did:532 :param trans_id:533 :return:534 """535 manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)536 try:537 conn = manager.connection(did=did, conn_id=trans_id,538 auto_reconnect=True,539 use_binary_placeholder=True)540 status, msg = conn.connect()541 if not status:542 app.logger.error(msg)543 raise ConnectionLost(sid, conn.db, trans_id)544 545 return conn546 except Exception as e:547 app.logger.error(e)548 raise549 550 551@blueprint.route('/prequisite/<int:trans_id>/<int:sgid>/<int:sid>/<int:did>',552 methods=["GET"],553 endpoint='prequisite')554@pga_login_required555def prequisite(trans_id, sgid, sid, did):556 conn = _get_connection(sid, did, trans_id)557 helper = ERDHelper(trans_id, sid, did)558 status, col_types = helper.get_types()559 560 if not status:561 return internal_server_error(errormsg=col_types)562 563 status, schemas = get_schemas(conn, show_system_objects=False)564 565 if not status:566 return internal_server_error(errormsg=schemas)567 568 return make_json_response(569 data={570 'col_types': col_types,571 'schemas': schemas['rows']572 },573 status=200574 )575 576 577def translate_foreign_keys(tab_fks, tab_data, all_nodes):578 """579 This function will take the from table foreign keys and translate580 it into non oid based format. It will allow creating FK sql even581 if table is not already created.582 :param tab_fks: Table foreign keyss583 :param tab_data: Table data584 :param all_nodes: All the nodes info from ERD585 :return: Translated foreign key data586 """587 for tab_fk in tab_fks:588 if 'columns' not in tab_fk:589 continue590 try:591 remote_table = all_nodes[tab_fk['columns'][0]['references']]592 except KeyError:593 continue594 tab_fk['schema'] = tab_data['schema']595 tab_fk['table'] = tab_data['name']596 tab_fk['remote_schema'] = remote_table['schema']597 tab_fk['remote_table'] = remote_table['name']598 599 new_column = {600 'local_column': tab_fk['columns'][0]['local_column'],601 'referenced': tab_fk['columns'][0]['referenced']602 }603 tab_fk['columns'][0] = new_column604 605 return tab_fks606 607 608@blueprint.route('/sql/<int:trans_id>/<int:sgid>/<int:sid>/<int:did>',609 methods=["POST"],610 endpoint='sql')611@pga_login_required612def sql(trans_id, sgid, sid, did):613 data = json.loads(request.data)614 with_drop = False615 if request.args and 'with_drop' in request.args:616 with_drop = True if request.args.get('with_drop') == 'true' else False617 618 helper = ERDHelper(trans_id, sid, did)619 conn = _get_connection(sid, did, trans_id)620 621 sql = ''622 tab_foreign_keys = []623 all_nodes = data.get('nodes', {})624 625 table_sql = ''626 for tab_key, tab_data in all_nodes.items():627 tab_fks = tab_data.pop('foreign_key', [])628 tab_foreign_keys.extend(translate_foreign_keys(tab_fks, tab_data,629 all_nodes))630 table_sql += '\n\n' + helper.get_table_sql(tab_data,631 with_drop=with_drop)632 633 if with_drop:634 for tab_fk in tab_foreign_keys:635 fk_sql = fkey_utils.get_delete_sql(conn, tab_fk)636 sql += '\n\n' + fk_sql637 638 if sql != '':639 sql += '\n\n'640 641 sql += table_sql642 for tab_fk in tab_foreign_keys:643 fk_sql, _ = fkey_utils.get_sql(conn, tab_fk, None)644 sql += '\n\n' + fk_sql645 646 return make_json_response(647 data=sql,648 status=200649 )650 651 652@socketio.on('connect', namespace=SOCKETIO_NAMESPACE)653def connect():654 """655 Connect to the server through socket.656 :return:657 :rtype:658 """659 socketio.emit('connected', {'sid': request.sid},660 namespace=SOCKETIO_NAMESPACE,661 to=request.sid)662 663 664@socketio.on('tables', namespace=SOCKETIO_NAMESPACE)665@socket_login_required666def tables(params):667 try:668 helper = ERDHelper(params['trans_id'], params['sid'], params['did'])669 _get_connection(params['sid'], params['did'], params['trans_id'])670 671 status, tables = helper.get_all_tables(params.get('scid', None),672 params.get('tid', None))673 674 if not status:675 tables = tables.json if isinstance(tables, Response) else tables676 socketio.emit('tables_failed', tables,677 namespace=SOCKETIO_NAMESPACE,678 to=request.sid)679 return680 socketio.emit('tables_success', tables, namespace=SOCKETIO_NAMESPACE,681 to=request.sid)682 except Exception as e:683 socketio.emit('tables_failed', str(e), namespace=SOCKETIO_NAMESPACE,684 to=request.sid)685 686 687@blueprint.route('/close/<int:trans_id>/<int:sgid>/<int:sid>/<int:did>',688 methods=["DELETE"],689 endpoint='close')690@pga_login_required691def close(trans_id, sgid, sid, did):692 manager = get_driver(693 PG_DEFAULT_DRIVER).connection_manager(sid)694 if manager is not None:695 conn = manager.connection(did=did, conn_id=trans_id)696 697 # Release the connection698 if conn.connected():699 conn.cancel_transaction(trans_id, did=did)700 manager.release(did=did, conn_id=trans_id)701 return make_json_response(data={'status': True})702