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"""Schema collection node helper class"""11 12import json13import copy14import re15 16from flask import render_template17 18from pgadmin.browser.collection import CollectionNodeModule19from pgadmin.utils.ajax import internal_server_error20from pgadmin.utils.driver import get_driver21from config import PG_DEFAULT_DRIVER22from pgadmin.utils.constants import DATATYPE_TIME_WITH_TIMEZONE,\23    DATATYPE_TIME_WITHOUT_TIMEZONE,\24    DATATYPE_TIMESTAMP_WITH_TIMEZONE,\25    DATATYPE_TIMESTAMP_WITHOUT_TIMEZONE26 27 28class SchemaChildModule(CollectionNodeModule):29    """30    Base class for the schema child node.31 32    Some of the node may be/may not be allowed in certain catalog nodes.33    i.e.34    Do not show the schema objects under pg_catalog, pgAgent, etc.35 36    Looks at two parameters CATALOG_DB_SUPPORTED, SUPPORTED_SCHEMAS.37 38    Schema child objects like catalog_objects are only supported for39    'pg_catalog', and objects like 'jobs' & 'schedules' are only supported for40    the 'pgagent' schema.41 42    For catalog_objects, we should set:43        CATALOG_DB_SUPPORTED = False44        SUPPORTED_SCHEMAS = ['pg_catalog']45 46    For jobs & schedules, we should set:47        CATALOG_DB_SUPPORTED = False48        SUPPORTED_SCHEMAS = ['pgagent']49    """50    CATALOG_DB_SUPPORTED = True51    SUPPORTED_SCHEMAS = None52 53    def backend_supported(self, manager, **kwargs):54        return (55            (56                (57                    kwargs['is_catalog'] and58                    (59                        (60                            self.CATALOG_DB_SUPPORTED and61                            kwargs['db_support']62                        ) or (63                            not self.CATALOG_DB_SUPPORTED and64                            not kwargs[65                                'db_support'] and66                            (67                                self.SUPPORTED_SCHEMAS is None or68                                kwargs[69                                    'schema_name'] in self.SUPPORTED_SCHEMAS70                            )71                        )72                    )73                ) or74                (75                    not kwargs['is_catalog'] and self.CATALOG_DB_SUPPORTED76                )77            ) and78            CollectionNodeModule.backend_supported(self, manager, **kwargs)79        )80 81    @property82    def module_use_template_javascript(self):83        """84        Returns whether Jinja2 template is used for generating the javascript85        module.86        """87        return False88 89 90class DataTypeReader:91    """92    DataTypeReader Class.93 94    This class includes common utilities for data-types.95 96    Methods:97    -------98    * get_types(conn, condition):99      - Returns data-types on the basis of the condition provided.100    """101 102    def _get_types_sql(self, conn, condition, add_serials, schema_oid):103        """104        Get sql for types.105        :param conn: connection106        :param condition: Condition for sql107        :param add_serials: add_serials flag108        :param schema_oid: schema iod.109        :return: sql for get type sql result, status and response.110        """111        # Check if template path is already set or not112        # if not then we will set the template path here113        manager = conn.manager if not hasattr(self, 'manager') \114            else self.manager115        if not hasattr(self, 'data_type_template_path'):116            self.data_type_template_path = 'datatype/sql/' + (117                '#{0}#'.format(manager.version)118            )119        sql = render_template(120            "/".join([self.data_type_template_path, 'get_types.sql']),121            condition=condition,122            add_serials=add_serials,123            schema_oid=schema_oid124        )125        status, rset = conn.execute_2darray(sql)126 127        return status, rset128 129    @staticmethod130    def _types_length_checks(length, typeval, precision):131        min_val = 0132        max_val = 0133        if length:134            min_val = 0 if typeval == 'D' else 1135            if precision:136                max_val = 1000137            elif min_val:138                # Max of integer value139                max_val = 2147483647140            else:141                # Max value is 6 for data type like142                # interval, timestamptz, etc..143                if typeval == 'D':144                    max_val = 6145                else:146                    max_val = 10147 148        return min_val, max_val149 150    def get_types(self, conn, condition, add_serials=False, schema_oid=''):151        """152        Returns data-types including calculation for Length and Precision.153 154        Args:155            conn: Connection Object156            condition: condition to restrict SQL statement157            add_serials: If you want to serials type158            schema_oid: If needed pass the schema OID to restrict the search159        """160        res = []161        try:162            status, rset = self._get_types_sql(conn, condition, add_serials,163                                               schema_oid)164            if not status:165                return status, rset166 167            for row in rset['rows']:168                # Attach properties for precision169                # & length validation for current type170                precision = False171                length = False172 173                # Check if the type will have length and precision or not174                if row['elemoid']:175                    length, precision, typeval = self.get_length_precision(176                        row['elemoid'])177 178                min_val, max_val = DataTypeReader._types_length_checks(179                    length, typeval, precision)180 181                res.append({182                    'label': row['typname'], 'value': row['typname'],183                    'typval': typeval, 'precision': precision,184                    'length': length, 'min_val': min_val, 'max_val': max_val,185                    'is_collatable': row['is_collatable'],186                    'oid': row['oid']187                })188 189        except Exception as e:190            return False, str(e)191 192        return True, res193 194    @staticmethod195    def get_length_precision(elemoid_or_name):196        precision = False197        length = False198        typeval = ''199 200        # Check against PGOID/typename for specific type201        if elemoid_or_name:202            if elemoid_or_name in (1560, 'bit',203                                   1561, 'bit[]',204                                   1562, 'varbit', 'bit varying',205                                   1563, 'varbit[]', 'bit varying[]',206                                   1042, 'bpchar', 'character',207                                   1043, 'varchar', 'character varying',208                                   1014, 'bpchar[]', 'character[]',209                                   1015, 'varchar[]', 'character varying[]'):210                typeval = 'L'211            elif elemoid_or_name in (1083, 'time',212                                     DATATYPE_TIME_WITHOUT_TIMEZONE,213                                     1114, 'timestamp',214                                     DATATYPE_TIMESTAMP_WITHOUT_TIMEZONE,215                                     1115, 'timestamp[]',216                                     'timestamp without time zone[]',217                                     1183, 'time[]',218                                     'time without time zone[]',219                                     1184, 'timestamptz',220                                     DATATYPE_TIMESTAMP_WITH_TIMEZONE,221                                     1185, 'timestamptz[]',222                                     'timestamp with time zone[]',223                                     1186, 'interval',224                                     1187, 'interval[]', 'interval[]',225                                     1266, 'timetz',226                                     DATATYPE_TIME_WITH_TIMEZONE,227                                     1270, 'timetz', 'time with time zone[]'):228                typeval = 'D'229            elif elemoid_or_name in (1231, 'numeric[]',230                                     1700, 'numeric'):231                typeval = 'P'232            else:233                typeval = ' '234 235        # Set precision & length/min/max values236        if typeval == 'P':237            precision = True238 239        if precision or typeval in ('L', 'D'):240            length = True241 242        return length, precision, typeval243 244    @staticmethod245    def _check_typmod(typmod, name):246        """247        Check type mode ad return length as per type.248        :param typmod:type mode.249        :param name: name of type.250        :return:251        """252        length = '('253        if name == 'numeric':254            _len = (typmod - 4) >> 16255            _prec = (typmod - 4) & 0xffff256            length += str(_len)257            if _prec is not None:258                length += ',' + str(_prec)259        elif (260            name == 'time' or261            name == 'timetz' or262            name == DATATYPE_TIME_WITHOUT_TIMEZONE or263            name == DATATYPE_TIME_WITH_TIMEZONE or264            name == 'timestamp' or265            name == 'timestamptz' or266            name == DATATYPE_TIMESTAMP_WITHOUT_TIMEZONE or267            name == DATATYPE_TIMESTAMP_WITH_TIMEZONE or268            name == 'bit' or269            name == 'bit varying' or270            name == 'varbit'271        ):272            _prec = 0273            _len = typmod274            length += str(_len)275        elif name == 'interval':276            _prec = 0277            _len = typmod & 0xffff278            # Max length for interval data type is 6279            # If length is greater then 6 then set length to None280            if _len > 6:281                _len = ''282            length += str(_len)283        elif name == 'date':284            # Clear length285            length = ''286        else:287            _len = typmod - 4288            _prec = 0289            length += str(_len)290 291        if len(length) > 0:292            length += ')'293 294        return length295 296    @staticmethod297    def _get_full_type_value(name, schema, length, array):298        """299        Generate full type value as per req.300        :param name: type name.301        :param schema: schema name.302        :param length: length.303        :param array: array of types304        :return: full type value305        """306        if name == 'char' and schema == 'pg_catalog':307            return '"char"' + array308        elif name == DATATYPE_TIME_WITH_TIMEZONE:309            return 'time' + length + ' with time zone' + array310        elif name == DATATYPE_TIME_WITHOUT_TIMEZONE:311            return 'time' + length + ' without time zone' + array312        elif name == DATATYPE_TIMESTAMP_WITH_TIMEZONE:313            return 'timestamp' + length + ' with time zone' + array314        elif name == DATATYPE_TIMESTAMP_WITHOUT_TIMEZONE:315            return 'timestamp' + length + ' without time zone' + array316        else:317            return name + length + array318 319    @staticmethod320    def _check_schema_in_name(typname, schema):321        """322        Above 7.4, format_type also sends the schema name if it's not323        included in the search_path, so we need to skip it in the typname324        :param typename: typename for check.325        :param schema: schema name for check.326        :return: name327        """328        if typname.find(schema + '".') >= 0:329            name = typname[len(schema) + 3]330        elif typname.find(schema + '.') >= 0:331            name = typname[len(schema) + 1]332        else:333            name = typname334 335        return name336 337    @staticmethod338    def get_full_type(nsp, typname, is_dup, numdims, typmod):339        """340        Returns full type name with Length and Precision.341 342        Args:343            conn: Connection Object344            condition: condition to restrict SQL statement345        """346        schema = nsp if nsp is not None else ''347        name = ''348        array = ''349        length = ''350 351        name = DataTypeReader._check_schema_in_name(typname, schema)352 353        if name.startswith('_'):354            if not numdims:355                numdims = 1356            name = name[1:]357 358        if name.endswith('[]'):359            if not numdims:360                numdims = 1361            name = name[:-2]362 363        if name.startswith('"') and name.endswith('"'):364            name = name[1:-1]365 366        if numdims > 0:367            while numdims:368                array += '[]'369                numdims -= 1370 371        if typmod != -1:372            length = DataTypeReader._check_typmod(typmod, name)373 374        type_value = DataTypeReader._get_full_type_value(name, schema, length,375                                                         array)376        return type_value377 378    @classmethod379    def parse_type_name(cls, type_name):380        """381        Returns prase type name without length and precision382        so that we can match the end result with types in the select2.383 384        Args:385            self: self386            type_name: Type name387        """388 389        # Manual Data type formatting390        # If data type has () with them then we need to remove them391        # eg bit(1) because we need to match the name with combobox392 393        is_array = False394        if type_name.endswith('[]'):395            is_array = True396            type_name = type_name.rstrip('[]')397 398        idx = type_name.find('(')399        if idx and type_name.endswith(')'):400            type_name = type_name[:idx]401        # We need special handling of timestamp types as402        # variable precision is between the type403        elif idx and type_name.startswith("time"):404            end_idx = type_name.find(')')405            # If we found the end then form the type string406            if end_idx != 1:407                from re import sub as sub_str408                pattern = r'(\(\d+\))'409                type_name = sub_str(pattern, '', type_name)410        # We need special handling for interval types like411        # interval hours to minute.412        elif type_name.startswith("interval"):413            type_name = 'interval'414 415        if is_array:416            type_name += "[]"417 418        return type_name419 420    @classmethod421    def parse_length_precision(cls, fulltype, is_tlength, is_precision):422        """423        Parse the type string and split length, precision.424        :param fulltype: type string425        :param is_tlength: is length type426        :param is_precision: is precision type427        :return: length, precision428        """429        t_len, t_prec = None, None430        if is_tlength and is_precision:431            match_obj = re.search(r'(\d+),(\d+)', fulltype)432            if match_obj:433                t_len = match_obj.group(1)434                t_prec = match_obj.group(2)435        elif is_tlength:436            # If we have length only437            match_obj = re.search(r'(\d+)', fulltype)438            if match_obj:439                t_len = match_obj.group(1)440                t_prec = None441 442        return t_len, t_prec443 444 445def trigger_definition(data):446    """447    This function will set the trigger definition details from the raw data448 449    Args:450        data: Properties data451 452    Returns:453        Updated properties data with trigger definition454    """455 456    # Here we are storing trigger definition457    # We will use it to check trigger type definition458    trigger_definition = {459        'TRIGGER_TYPE_ROW': (1 << 0),460        'TRIGGER_TYPE_BEFORE': (1 << 1),461        'TRIGGER_TYPE_INSERT': (1 << 2),462        'TRIGGER_TYPE_DELETE': (1 << 3),463        'TRIGGER_TYPE_UPDATE': (1 << 4),464        'TRIGGER_TYPE_TRUNCATE': (1 << 5),465        'TRIGGER_TYPE_INSTEAD': (1 << 6)466    }467 468    # Fires event definition469    if data['tgtype'] & trigger_definition['TRIGGER_TYPE_BEFORE']:470        data['fires'] = 'BEFORE'471    elif data['tgtype'] & trigger_definition['TRIGGER_TYPE_INSTEAD']:472        data['fires'] = 'INSTEAD OF'473    else:474        data['fires'] = 'AFTER'475 476    # Trigger of type definition477    if data['tgtype'] & trigger_definition['TRIGGER_TYPE_ROW']:478        data['is_row_trigger'] = True479    else:480        data['is_row_trigger'] = False481 482    # Event definition483    if data['tgtype'] & trigger_definition['TRIGGER_TYPE_INSERT']:484        data['evnt_insert'] = True485    else:486        data['evnt_insert'] = False487 488    if data['tgtype'] & trigger_definition['TRIGGER_TYPE_DELETE']:489        data['evnt_delete'] = True490    else:491        data['evnt_delete'] = False492 493    if data['tgtype'] & trigger_definition['TRIGGER_TYPE_UPDATE']:494        data['evnt_update'] = True495    else:496        data['evnt_update'] = False497 498    if data['tgtype'] & trigger_definition['TRIGGER_TYPE_TRUNCATE']:499        data['evnt_truncate'] = True500    else:501        data['evnt_truncate'] = False502 503    return data504 505 506def parse_rule_definition(res):507    """508    This function extracts:509    - events510    - do_instead511    - statements512    - condition513    from the defintion row, forms an array with fields and returns it.514    """515    res_data = []516    try:517        res_data = res['rows'][0]518        data_def = res_data['definition']519        import re520 521        # Parse data for condition522        condition = ''523        condition_part_match = re.search(524            r"((?:ON)\s+(?:[\s\S]+?)"525            r"(?:TO)\s+(?:[\s\S]+?)(?:DO))", data_def)526        if condition_part_match is not None:527            condition_part = condition_part_match.group(1)528 529            condition_match = re.search(530                r"(?:WHERE)\s+(\([\s\S]*\))\s+(?:DO)", condition_part)531 532            if condition_match is not None:533                condition = condition_match.group(1)534                # also remove enclosing brackets535                if condition.startswith('(') and condition.endswith(')'):536                    condition = condition[1:-1]537 538            # Parse data for statements539        statement_match = re.search(540            r"(?:DO\s+)(?:INSTEAD\s+)?([\s\S]*)(?:;)", data_def)541 542        statement = ''543        if statement_match is not None:544            statement = statement_match.group(1)545            # also remove enclosing brackets546            if statement.startswith('(') and statement.endswith(')'):547                statement = statement[1:-1]548 549        # set columns parse data550        res_data['event'] = {551            '1': 'SELECT',552            '2': 'UPDATE',553            '3': 'INSERT',554            '4': 'DELETE'555        }[res_data['ev_type']]556        res_data['do_instead'] = res_data['is_instead']557        res_data['statements'] = statement558        res_data['condition'] = condition559    except Exception as e:560        return internal_server_error(errormsg=str(e))561    return res_data562 563 564class VacuumSettings:565    """566    VacuumSettings Class.567 568    This class includes common utilities to fetch and parse569    vacuum defaults settings.570 571    Methods:572    -------573    * get_vacuum_table_settings(conn):574      - Returns vacuum table defaults settings.575 576    * get_vacuum_toast_settings(conn):577      - Returns vacuum toast defaults settings.578 579    * parse_vacuum_data(conn, result, type):580      - Returns result of an associated array581        of fields name, label, value and column_type.582        It adds name, label, column_type properties of table/toast583        vacuum into the array and returns it.584        args:585        * conn - It is db connection object586        * result - Resultset of vacuum data587        * type - table/toast vacuum type588 589    """590    vacuum_settings = dict()591 592    def fetch_default_vacuum_settings(self, conn, sid, setting_type):593        """594        This function is used to fetch and cached the default vacuum settings595        for specified server id.596        :param conn: Connection Object597        :param sid:  Server ID598        :param setting_type: Type (table or toast)599        :return:600        """601        if sid in VacuumSettings.vacuum_settings:602            if setting_type in VacuumSettings.vacuum_settings[sid]:603                return VacuumSettings.vacuum_settings[sid][setting_type]604        else:605            VacuumSettings.vacuum_settings[sid] = dict()606 607        # returns an array of name & label values608        vacuum_fields = render_template("vacuum_settings/vacuum_fields.json")609        vacuum_fields = json.loads(vacuum_fields)610 611        # returns an array of setting & name values612        vacuum_fields_keys = "'" + "','".join(613            vacuum_fields[setting_type].keys()) + "'"614        SQL = render_template('vacuum_settings/sql/vacuum_defaults.sql',615                              columns=vacuum_fields_keys)616 617        status, res = conn.execute_dict(SQL)618        if not status:619            return internal_server_error(errormsg=res)620 621        for row in res['rows']:622            row_name = row['name']623            row['name'] = vacuum_fields[setting_type][row_name][0]624            row['label'] = vacuum_fields[setting_type][row_name][1]625            row['column_type'] = vacuum_fields[setting_type][row_name][2]626 627        VacuumSettings.vacuum_settings[sid][setting_type] = res['rows']628        return VacuumSettings.vacuum_settings[sid][setting_type]629 630    def get_vacuum_table_settings(self, conn, sid):631        """632        Fetch the default values for autovacuum633        fields, return an array of634          - label635          - name636          - setting637        values638        """639        return self.fetch_default_vacuum_settings(conn, sid, 'table')640 641    def get_vacuum_toast_settings(self, conn, sid):642        """643        Fetch the default values for autovacuum644        fields, return an array of645          - label646          - name647          - setting648        values649        """650        return self.fetch_default_vacuum_settings(conn, sid, 'toast')651 652    def parse_vacuum_data(self, conn, result, type):653        """654        This function returns result of an associated array655        of fields name, label, value and column_type.656        It adds name, label, column_type properties of table/toast657        vacuum into the array and returns it.658        args:659        * conn - It is db connection object660        * result - Resultset of vacuum data661        * type - table/toast vacuum type662        """663 664        vacuum_settings_tmp = copy.deepcopy(self.fetch_default_vacuum_settings(665            conn, self.manager.sid, type))666 667        for row in vacuum_settings_tmp:668            row_name = row['name']669            if type == 'toast':670                row_name = 'toast_{0}'.format(row['name'])671            if result.get(row_name, None) is not None:672                value = float(result[row_name])673                row['value'] = int(value) if value % 1 == 0 else value674            else:675                row.pop('value', None)676 677        return vacuum_settings_tmp678 679 680def get_schema(sid, did, scid):681    """682    This function will return the schema name.683    """684 685    driver = get_driver(PG_DEFAULT_DRIVER)686    manager = driver.connection_manager(sid)687    conn = manager.connection(did=did)688 689    ver = manager.version690    server_type = manager.server_type691 692    # Fetch schema name693    status, schema_name = conn.execute_scalar(694        render_template("/".join(['schemas',695                                  '{0}/#{1}#'.format(server_type,696                                                     ver),697                                  'sql/get_name.sql']),698                        conn=conn, scid=scid699                        )700    )701 702    return status, schema_name703 704 705def get_schemas(conn, show_system_objects=False):706    """707    This function will return the schemas.708    """709 710    ver = conn.manager.version711    server_type = conn.manager.server_type712 713    SQL = render_template(714        "/".join(['schemas',715                  '{0}/#{1}#'.format(server_type, ver),716                  'sql/nodes.sql']),717        show_sysobj=show_system_objects,718        schema_restrictions=None719    )720 721    status, rset = conn.execute_2darray(SQL)722    return status, rset723