codekingpro/portable-devtools

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 Cast Node"""11 12import json13from functools import wraps14 15from pgadmin.browser.server_groups.servers import databases16from flask import render_template, request, jsonify17from flask_babel import gettext18from pgadmin.browser.collection import CollectionNodeModule19from pgadmin.browser.utils import PGChildNodeView20from pgadmin.utils.ajax import make_json_response, internal_server_error, \21    make_response as ajax_response, gone22from pgadmin.utils.driver import get_driver23from config import PG_DEFAULT_DRIVER24from pgadmin.tools.schema_diff.node_registry import SchemaDiffRegistry25from pgadmin.tools.schema_diff.compare import SchemaDiffObjectCompare26 27 28class CastModule(CollectionNodeModule):29    """30     class CastModule(CollectionNodeModule)31 32        A module class for Cast node derived from CollectionNodeModule.33 34    Methods:35    -------36    * __init__(*args, **kwargs)37      - Method is used to initialize the CastModule and it's base module.38 39    * get_nodes(gid, sid, did)40      - Method is used to generate the browser collection node.41 42    * node_inode()43      - Method is overridden from its base class to make the node as leaf node.44 45    * script_load()46      - Load the module script for cast, when any of the database node is47        initialized.48    """49 50    _NODE_TYPE = 'cast'51    _COLLECTION_LABEL = gettext('Casts')52 53    def __init__(self, *args, **kwargs):54        super().__init__(*args, **kwargs)55 56    def get_nodes(self, gid, sid, did):57        """58        Generate the collection node59        :param gid: group id60        :param sid: server id61        :param did: database id62        """63        if self.has_nodes(sid, did,64                          base_template_path=CastView.BASE_TEMPLATE_PATH):65            yield self.generate_browser_collection_node(did)66 67    @property68    def node_inode(self):69        """70        Override the property to make the node as leaf node71        """72        return False73 74    @property75    def script_load(self):76        """77        Load the module script for cast, when any of the database node is78        initialized.79        """80        return databases.DatabaseModule.node_type81 82    @property83    def module_use_template_javascript(self):84        """85        Returns whether Jinja2 template is used for generating the javascript86        module.87        """88        return False89 90 91blueprint = CastModule(__name__)92 93 94class CastView(PGChildNodeView, SchemaDiffObjectCompare):95    """96    class CastView(PGChildNodeView)97 98        A view class for cast node derived from PGChildNodeView. This class is99        responsible for all the stuff related to view like100        create/update/delete cast, showing properties of cast node,101        showing sql in sql pane.102 103    Methods:104    -------105    * __init__(**kwargs)106      - Method is used to initialize the CastView and it's base view.107 108    * check_precondition()109      - This function will behave as a decorator which will checks110        database connection before running view, it will also attaches111        manager,conn & template_path properties to self112 113    * list()114      - This function is used to list all the cast nodes within that115      collection.116 117    * nodes()118      - This function will used to create all the child node within that119      collection. Here it will create all the cast nodes.120 121    * properties(gid, sid, did, rg_id)122      - This function will show the properties of the selected cast node123 124    * create(gid, sid, did, rg_id)125      - This function will create the new cast object126 127    * update(gid, sid, did, rg_id)128      - This function will update the data for the selected cast node129 130    * delete(self, gid, sid, rg_id):131      - This function will drop the cast object132 133    * msql(gid, sid, did, rg_id)134      - This function is used to return modified SQL for the selected cast node135 136    * get_sql(data, rg_id)137      - This function will generate sql from model data138 139    * sql(gid, sid, did, rg_id):140      - This function will generate sql to show in sql pane for the selected141      cast node.142 143    * get_type():144      - This function will fetch all the types for source and target types145      select control.146 147    * get_functions():148      - This function will fetch associated functions list depending on149      selected source and target types while creating a new cast node.150    """151 152    node_type = blueprint.node_type153    BASE_TEMPLATE_PATH = 'casts/sql/#{0}#'154 155    parent_ids = [156        {'type': 'int', 'id': 'gid'},157        {'type': 'int', 'id': 'sid'},158        {'type': 'int', 'id': 'did'}159    ]160    ids = [161        {'type': 'int', 'id': 'cid'}162    ]163 164    operations = dict({165        'obj': [166            {'get': 'properties', 'delete': 'delete', 'put': 'update'},167            {'get': 'list', 'post': 'create', 'delete': 'delete'}168        ],169        'children': [{170            'get': 'children'171        }],172        'delete': [{'delete': 'delete'}, {'delete': 'delete'}],173        'nodes': [{'get': 'node'}, {'get': 'nodes'}],174        'sql': [{'get': 'sql'}],175        'msql': [{'get': 'msql'}, {'get': 'msql'}],176        'stats': [{'get': 'statistics'}],177        'dependency': [{'get': 'dependencies'}],178        'dependent': [{'get': 'dependents'}],179        'get_type': [180            {'get': 'get_src_and_trg_type'},181            {'get': 'get_src_and_trg_type'}182        ],183        'get_functions': [184            {'post': 'get_functions'},185            {'post': 'get_functions'}186        ],187        'compare': [{'get': 'compare'}, {'get': 'compare'}]188    })189 190    keys_to_ignore = ['oid', 'id', 'oid-2', 'castfunc']191 192    def _init_(self, **kwargs):193        self.conn = None194        self.template_path = None195        self.manager = None196        super().__init__(**kwargs)197 198    def check_precondition(f):199        """200        This function will behave as a decorator which will check the201        database connection before running view. It will also attach202        manager, conn & template_path properties to self203        """204 205        @wraps(f)206        def wrap(*args, **kwargs):207            # Here args[0] will hold self & kwargs will hold gid,sid,did208            self = args[0]209            self.manager = get_driver(210                PG_DEFAULT_DRIVER211            ).connection_manager(kwargs['sid'])212            self.conn = self.manager.connection(did=kwargs['did'])213            # Set template path for the SQL scripts214            self.template_path = self.BASE_TEMPLATE_PATH.format(215                self.manager.version)216 217            self.datistemplate = False218            if (219                self.manager.db_info is not None and220                kwargs['did'] in self.manager.db_info and221                'datistemplate' in self.manager.db_info[kwargs['did']]222            ):223                self.datistemplate = self.manager.db_info[224                    kwargs['did']]['datistemplate']225 226            return f(*args, **kwargs)227 228        return wrap229 230    @check_precondition231    def list(self, gid, sid, did):232        """233        This function is used to list all the cast nodes within the collection.234        :param gid: group id235        :param sid: server id236        :param did: database id237        :return:238        """239        last_system_oid = 0 if self.blueprint.show_system_objects else \240            self._DATABASE_LAST_SYSTEM_OID241        sql = render_template(242            "/".join([self.template_path, self._PROPERTIES_SQL]),243            datlastsysoid=last_system_oid,244            showsysobj=self.blueprint.show_system_objects245        )246        status, res = self.conn.execute_dict(sql)247 248        if not status:249            return internal_server_error(errormsg=res)250 251        for row in res['rows']:252            row['castcontext'] = True if row['castcontext'] == 'IMPLICIT' \253                else False254 255        return ajax_response(256            response=res['rows'],257            status=200258        )259 260    @check_precondition261    def nodes(self, gid, sid, did):262        """263        This function will used to create all the child nodes within the264        collection. Here it will create all the cast nodes.265        :param gid: group id266        :param sid: server id267        :param did: database id268        :return:269        """270        res = []271        last_system_oid = 0 if self.blueprint.show_system_objects else \272            self._DATABASE_LAST_SYSTEM_OID273 274        sql = render_template(275            "/".join([self.template_path, self._NODES_SQL]),276            datlastsysoid=last_system_oid,277            showsysobj=self.blueprint.show_system_objects278        )279        status, rset = self.conn.execute_2darray(sql)280        if not status:281            return internal_server_error(errormsg=rset)282 283        for row in rset['rows']:284            res.append(285                self.blueprint.generate_browser_node(286                    row['oid'],287                    did,288                    row['name'],289                    icon="icon-cast",290                    description=row['description']291                ))292 293        return make_json_response(294            data=res,295            status=200296        )297 298    @check_precondition299    def node(self, gid, sid, did, cid):300        """301        This function will fetch properties of the cast node302        """303        sql = render_template(304            "/".join([self.template_path, self._NODES_SQL]),305            cid=cid306        )307        status, rset = self.conn.execute_2darray(sql)308        if not status:309            return internal_server_error(errormsg=rset)310 311        for row in rset['rows']:312            return make_json_response(313                data=self.blueprint.generate_browser_node(314                    row['oid'],315                    did,316                    row['name'],317                    icon="icon-cast"318                ),319                status=200320            )321 322        return gone(errormsg=gettext("Could not find the specified cast."))323 324    @check_precondition325    def properties(self, gid, sid, did, cid):326        """327        This function will show the properties of the selected cast node328        :param gid: group id329        :param sid: server id330        :param did: database id331        :param cid: cast id332        :return:333        """334        status, res = self._fetch_properties(did, cid)335        if not status:336            return res337 338        return ajax_response(339            response=res,340            status=200341        )342 343    def _fetch_properties(self, did, cid):344        """345        This function fetch the properties of the346        :param did:347        :param cid:348        :return:349        """350        last_system_oid = 0 if not self.blueprint.show_system_objects else \351            self._DATABASE_LAST_SYSTEM_OID352        sql = render_template(353            "/".join([self.template_path, self._PROPERTIES_SQL]),354            cid=cid,355            datlastsysoid=last_system_oid,356            showsysobj=self.blueprint.show_system_objects,357            conn=self.conn358        )359        status, res = self.conn.execute_dict(sql)360 361        if not status:362            return False, internal_server_error(errormsg=res)363 364        if len(res['rows']) == 0:365            return False, gone(366                gettext("Could not find the cast information.")367            )368 369        return True, res['rows'][0]370 371    @check_precondition372    def create(self, gid, sid, did):373        """374        This function will creates new the cast object375        :param did: database id376        :param sid: server id377        :param gid: group id378        :return:379        """380 381        required_args = [382            'srctyp',383            'trgtyp'384        ]385 386        data = request.form if request.form else json.loads(387            request.data388        )389        for arg in required_args:390            if arg not in data:391                return make_json_response(392                    status=410,393                    success=0,394                    errormsg=gettext(395                        "Could not find the required parameter ({})."396                    ).format(arg)397                )398        try:399            sql = render_template("/".join([self.template_path,400                                            self._CREATE_SQL]),401                                  data=data,402                                  conn=self.conn,403                                  )404            status, res = self.conn.execute_scalar(sql)405            if not status:406                return internal_server_error(errormsg=res)407 408            # we need oid to add object in tree at browser, below sql will409            # gives the same410            last_system_oid = 0 if self.blueprint.show_system_objects else \411                self._DATABASE_LAST_SYSTEM_OID412            sql = render_template(413                "/".join([self.template_path, self._PROPERTIES_SQL]),414                srctyp=data['srctyp'],415                trgtyp=data['trgtyp'],416                datlastsysoid=last_system_oid,417                showsysobj=self.blueprint.show_system_objects,418                conn=self.conn419            )420            status, cid = self.conn.execute_scalar(sql)421            if not status:422                return internal_server_error(errormsg=cid)423 424            return jsonify(425                node=self.blueprint.generate_browser_node(426                    cid,427                    did,428                    data['name'],429                    icon="icon-cast"430                )431            )432        except Exception as e:433            return internal_server_error(errormsg=str(e))434 435    @check_precondition436    def update(self, gid, sid, did, cid):437        """438        This function will update cast object439        :param cid: cast id440        :param did: database id441        :param sid: server id442        :param gid: group id443        :return:444        """445        data = request.form if request.form else json.loads(446            request.data447        )448        try:449            sql, name = self.get_sql(gid, sid, did, data, cid)450            # Most probably this is due to error451            if not isinstance(sql, str):452                return sql453            status, res = self.conn.execute_scalar(sql)454            if not status:455                return internal_server_error(errormsg=res)456 457            other_node_info = {}458            if 'description' in data:459                other_node_info['description'] = data['description']460 461            return jsonify(462                node=self.blueprint.generate_browser_node(463                    cid,464                    did,465                    name,466                    "icon-{0}".format(self.node_type),467                    **other_node_info468                )469            )470 471        except Exception as e:472            return internal_server_error(errormsg=str(e))473 474    @staticmethod475    def get_delete_data(cmd, cid, request_object):476        """477        This function is used to get the data and cascade information.478        :param cmd: Command479        :param cid: Object ID480        :param request_object: request object481        :return:482        """483        cascade = False484        # Below will decide if it's simple drop or drop with cascade call485        if cmd == 'delete':486            # This is a cascade operation487            cascade = True488 489        if cid is None:490            data = request_object.form if request_object.form else \491                json.loads(request_object.data)492        else:493            data = {'ids': [cid]}494 495        return cascade, data496 497    @check_precondition498    def delete(self, gid, sid, did, cid=None, only_sql=False):499        """500        This function will drop the cast object501        :param cid: cast id502        :param did: database id503        :param sid: server id504        :param gid: group id505        :param only_sql:506        :return:507        """508        # get the value of cascade and data509        cascade, data = self.get_delete_data(self.cmd, cid, request)510 511        for cid in data['ids']:512            try:513                # Get name for cast from cid514                sql = render_template("/".join([self.template_path,515                                                self._DELETE_SQL]),516                                      cid=cid)517                status, res = self.conn.execute_dict(sql)518                if not status:519                    return internal_server_error(errormsg=res)520 521                if not res['rows']:522                    return make_json_response(523                        status=410,524                        success=0,525                        errormsg=gettext(526                            'Error: Object not found.'527                        ),528                        info=gettext(529                            'The specified cast object could not be found.\n'530                        )531                    )532 533                # drop cast534                result = res['rows'][0]535                sql = render_template("/".join([self.template_path,536                                                self._DELETE_SQL]),537                                      castsource=result['castsource'],538                                      casttarget=result['casttarget'],539                                      cascade=cascade540                                      )541 542                # Used for schema diff tool543                if only_sql:544                    return sql545 546                status, res = self.conn.execute_scalar(sql)547                if not status:548                    return internal_server_error(errormsg=res)549 550            except Exception as e:551                return internal_server_error(errormsg=str(e))552 553        return make_json_response(554            success=1,555            info=gettext("Cast dropped")556        )557 558    @check_precondition559    def msql(self, gid, sid, did, cid=None):560        """561         This function returns modified SQL562         :param cid: cast id563         :param did: database id564         :param sid: server id565         :param gid: group id566         :return:567        """568        data = request.args569        sql, _ = self.get_sql(gid, sid, did, data, cid)570        # Most probably this is due to error571        if not isinstance(sql, str):572            return sql573        sql = sql.strip('\n').strip(' ')574        if sql == '':575            sql = "--modified SQL"576 577        return make_json_response(578            data=sql,579            status=200580        )581 582    def get_sql(self, gid, sid, did, data, cid=None):583        """584        This function will return sql for model data585        :param gid: group id586        :param sid: server id587        :param did: database id588        :param cid: cast id589        :param data: model data590        :return:591        """592        if cid is not None:593            last_system_oid = 0 if self.blueprint.show_system_objects else \594                self._DATABASE_LAST_SYSTEM_OID595            sql = render_template(596                "/".join([self.template_path, self._PROPERTIES_SQL]),597                cid=cid,598                datlastsysoid=last_system_oid,599                showsysobj=self.blueprint.show_system_objects,600                conn=self.conn601            )602            status, res = self.conn.execute_dict(sql)603 604            if not status:605                return internal_server_error(errormsg=res)606 607            if len(res['rows']) == 0:608                return gone(609                    gettext("Could not find the specified cast on the server.")610                )611 612            old_data = res['rows'][0]613            sql = render_template(614                "/".join([self.template_path, self._UPDATE_SQL]),615                data=data, o_data=old_data, conn=self.conn616            )617            return sql, data['name'] if 'name' in data else old_data['name']618        else:619            if 'srctyp' in data and 'trgtyp' in data:620                sql = render_template(621                    "/".join([self.template_path, self._CREATE_SQL]),622                    data=data, conn=self.conn623                )624            else:625                return gettext("-- definition incomplete"), None626            return sql, data['srctyp'] + "->" + data["trgtyp"]627 628    @check_precondition629    def get_functions(self, gid, sid, did, cid=None):630        """631        This function will return functions list associated with a cast632        :param gid: group id633        :param sid: server id634        :param did: database id635        :param cid: cast id636        :return:637        """638        res = []639        data = request.form if request.form else json.loads(640            request.data641        )642 643        sql = render_template("/".join([self.template_path,644                                        self._FUNCTIONS_SQL]),645                              srctyp=data['srctyp'],646                              trgtyp=data['trgtyp'],647                              conn=self.conn)648        status, rset = self.conn.execute_dict(sql)649 650        if not status:651            return internal_server_error(errormsg=rset)652        res.append({'label': '',653                    'value': ''})654 655        for row in rset['rows']:656            res.append({'label': row['proname'],657                        'value': row['proname']})658        return make_json_response(659            data=res,660            status=200661        )662 663    @check_precondition664    def get_src_and_trg_type(self, gid, sid, did, cid=None):665        """666        This function will return type list667        :param gid: group id668        :param sid: server id669        :param did: database id670        :param cid: cast id671        :return:672        """673        res = []674        sql = render_template(675            "/".join([self.template_path, 'getsrcandtrgttype.sql']),676            cid=cid677        )678        status, rset = self.conn.execute_dict(sql)679 680        if not status:681            return internal_server_error(errormsg=rset)682 683        res = [{'label': '', 'value': ''}]684        for row in rset['rows']:685            res.append({686                'label': row['typname'],687                'value': row['typname']688            })689 690        return make_json_response(691            data=res,692            status=200693        )694 695    @check_precondition696    def sql(self, gid, sid, did, cid, json_resp=True):697        """698        This function will generate sql for sql panel699        :param gid: group id700        :param sid: server id701        :param did: database id702        :param cid: cast id703        :param json_resp:704        :return:705        """706        try:707            sql = render_template(708                "/".join([self.template_path, 'sql.sql']),709                cid=cid,710                conn=self.conn711            )712 713            status, res = self.conn.execute_scalar(sql)714            if not status:715                return internal_server_error(gettext(716                    "Could not generate reversed engineered SQL for the cast."717                ) + "\n\n{0}".format(res))718 719            if res is None:720                return gone(gettext(721                    "Could not generate reversed engineered SQL for the "722                    "cast node."723                ))724 725            if not json_resp:726                return res727 728            return ajax_response(response=res)729 730        except Exception as e:731            return internal_server_error(errormsg=str(e))732 733    @check_precondition734    def dependents(self, gid, sid, did, cid):735        """736        This function gets the dependents and returns an ajax response737        for the cast node.738 739        Args:740            gid: Server Group ID741            sid: Server ID742            did: Database ID743            cid: Cast ID744        """745        dependents_result = self.get_dependents(self.conn, cid)746        return ajax_response(747            response=dependents_result,748            status=200749        )750 751    @check_precondition752    def dependencies(self, gid, sid, did, cid):753        """754        This function gets the dependencies and returns an ajax response755        for the cast node.756 757        Args:758            gid: Server Group ID759            sid: Server ID760            did: Database ID761            cid: Cast ID762        """763        dependencies_result = self.get_dependencies(self.conn, cid)764        return ajax_response(765            response=dependencies_result,766            status=200767        )768 769    @check_precondition770    def fetch_objects_to_compare(self, sid, did):771        """772        This function will fetch the list of all the casts for773        specified database id.774 775        :param sid: Server Id776        :param did: Database Id777        :return:778        """779        res = dict()780 781        sql = render_template(782            "/".join([self.template_path, self._NODES_SQL]),783            datlastsysoid=self._DATABASE_LAST_SYSTEM_OID,784            showsysobj=self.blueprint.show_system_objects,785            schema_diff=True786        )787        status, rset = self.conn.execute_2darray(sql)788        if not status:789            return internal_server_error(errormsg=rset)790 791        for row in rset['rows']:792            status, data = self._fetch_properties(did, row['oid'])793            if status:794                res[row['name']] = data795 796        return res797 798    def get_sql_from_diff(self, **kwargs):799        """800        This function is used to get the DDL/DML statements.801        :param kwargs:802        :return:803        """804        gid = kwargs.get('gid')805        sid = kwargs.get('sid')806        did = kwargs.get('did')807        oid = kwargs.get('oid')808        data = kwargs.get('data', None)809        drop_sql = kwargs.get('drop_sql', False)810 811        if data:812            sql, _ = self.get_sql(gid=gid, sid=sid, did=did, data=data,813                                  cid=oid)814        else:815            if drop_sql:816                sql = self.delete(gid=gid, sid=sid, did=did,817                                  cid=oid, only_sql=True)818            else:819                sql = self.sql(gid=gid, sid=sid, did=did, cid=oid,820                               json_resp=False)821        return sql822 823 824SchemaDiffRegistry(blueprint.node_type, CastView, 'Database')825CastView.register_node_view(blueprint)826