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"""11Typecast various data types so that they can be compatible with Javascript12data types.13"""14import psycopg15from psycopg.types.string import TextLoader16from psycopg.types.json import JsonDumper, _JsonDumper, _JsonLoader17from psycopg._encodings import py_codecs as encodings18from .encoding import get_encoding, configure_driver_encodings19from psycopg.types.net import InetLoader20from psycopg.adapt import Loader21from ipaddress import ip_address, ip_interface22from psycopg._encodings import py_codecs as encodings23 24configure_driver_encodings(encodings)25 26# OIDs of data types which need to typecast as string to avoid JavaScript27# compatibility issues.28# e.g JavaScript does not support 64 bit integers. It has 64-bit double29# giving only 53 bits of integer range (IEEE 754)30# So to avoid loss of remaining 11 bits (64-53) we need to typecast bigint to31# string.32 33TO_STRING_DATATYPES = (34    # To cast bytea, interval type35    17, 1186,36 37    # date, timestamp, timestamp with zone, time without time zone38    1082, 1114, 1184, 108339)40 41TO_STRING_NUMERIC_DATATYPES = (42    # Real, double precision, numeric, bigint43    700, 701, 1700, 2044)45 46# OIDs of array data types which need to typecast to array of string.47# This list may contain:48# OIDs of data types from PSYCOPG_SUPPORTED_ARRAY_DATATYPES as they need to be49# typecast to array of string.50# Also OIDs of data types which psycopg does not typecast array of that51# data type. e.g: uuid, bit, varbit, etc.52 53TO_ARRAY_OF_STRING_DATATYPES = (54    # To cast bytea[] type55    1001,56 57    # bigint[]58    1016,59 60    # double precision[], real[]61    1022, 1021,62 63    # bit[], varbit[]64    1561, 1563,65)66 67# OID of record array data type68RECORD_ARRAY = (2287,)69 70# OIDs of builtin array datatypes supported by psycopg71# OID reference psycopg/psycopg/typecast_builtins.c72#73# For these array data types psycopg returns result in list.74# For all other array data types psycopg returns result as string (string75# representing array literal)76# e.g:77#78# For below two sql psycopg returns result in different formats.79#   SELECT '{foo,bar}'::text[];80#   print('type of {} ==> {}'.format(res[0], type(res[0])))81#   SELECT '{<a>foo</a>,<b>bar</b>}'::xml[];82#   print('type of {} ==> {}'.format(res[0], type(res[0])))83#84# Output:85#   type of ['foo', 'bar'] ==> <type 'list'>86#   type of {<a>foo</a>,<b>bar</b>} ==> <type 'str'>87 88PSYCOPG_SUPPORTED_BUILTIN_ARRAY_DATATYPES = (89    1016, 1005, 1006, 1007, 1021, 1022, 1231,90    1002, 1003, 1009, 1014, 1015, 1014, 1015,91    1000, 1115, 1185, 1183, 1270, 1182, 1187,92    1001, 1028, 1013, 1041, 651, 104093)94 95# json, jsonb96# OID reference psycopg/lib/_json.py97PSYCOPG_SUPPORTED_JSON_TYPES = (114, 3802)98 99# json[], jsonb[]100PSYCOPG_SUPPORTED_JSON_ARRAY_TYPES = (199, 3807)101 102ALL_JSON_TYPES = PSYCOPG_SUPPORTED_JSON_TYPES +\103    PSYCOPG_SUPPORTED_JSON_ARRAY_TYPES104 105# INET[], CIDR[]106# OID reference psycopg/lib/_ipaddress.py107PSYCOPG_SUPPORTED_IPADDRESS_ARRAY_TYPES = (1041, 651)108 109# uuid[], uuid110# OID reference psycopg/lib/extras.py111PSYCOPG_SUPPORTED_IPADDRESS_ARRAY_TYPES = (2951, 2950)112 113# int4range, int8range, numrange, daterange tsrange, tstzrange114# OID reference psycopg/lib/_range.py115PSYCOPG_SUPPORTED_RANGE_TYPES = (3904, 3926, 3906, 3912, 3908, 3910)116 117# int4multirange, int8multirange, nummultirange, datemultirange tsmultirange,118# tstzmultirange[]119PSYCOPG_SUPPORTED_MULTIRANGE_TYPES = (4535, 4451, 4536, 4532, 4533, 4534)120 121# int4range[], int8range[], numrange[], daterange[] tsrange[], tstzrange[]122# OID reference psycopg/lib/_range.py123PSYCOPG_SUPPORTED_RANGE_ARRAY_TYPES = (3905, 3927, 3907, 3913, 3909, 3911)124 125# int4multirange[], int8multirange[], nummultirange[],126# datemultirange[] tsmultirange[], tstzmultirange[]127PSYCOPG_SUPPORTED_MULTIRANGE_ARRAY_TYPES = (6155, 6150, 6157, 6151, 6152, 6153)128 129 130def register_global_typecasters():131    # This registers a unicode type caster for datatype 'RECORD'.132    psycopg.adapters.register_loader(133        2249, TextLoaderpgAdmin)134    # This registers a unicode type caster for datatype 'RECORD_ARRAY'.135    psycopg.adapters.register_loader(136        2287, TextLoaderpgAdmin)137 138    for typ in TO_STRING_DATATYPES + TO_STRING_NUMERIC_DATATYPES +\139            PSYCOPG_SUPPORTED_RANGE_TYPES + PSYCOPG_SUPPORTED_MULTIRANGE_TYPES:140        psycopg.adapters.register_loader(typ,141                                         TextLoaderpgAdmin)142 143    # Define type caster to convert pg array types of above types into144    # array of string type145    for typ in TO_ARRAY_OF_STRING_DATATYPES:146        psycopg.adapters.register_loader(typ, TextLoaderpgAdmin)147 148    psycopg.adapters.register_loader("json",149                                     TextLoaderpgAdmin)150    psycopg.adapters.register_loader("jsonb",151                                     TextLoaderpgAdmin)152 153    # psycopg.types.json.set_json_loads(loads=lambda x: x)154 155    class JsonDumperpgAdmin(_JsonDumper):156 157        def dump(self, obj):158            return self.dumps(obj).encode()159 160    psycopg.adapters.register_dumper(dict, JsonDumperpgAdmin)161 162 163def register_string_typecasters(connection):164    # raw_unicode_escape used for SQL ASCII will escape the165    # characters. Here we unescape them using unicode_escape166    # and send ahead. When insert update is done, the characters167    # are escaped again and sent to the DB.168    for typ in (19, 18, 25, 1042, 1043, 0):169        if connection:170            connection.adapters.register_loader(typ, TextLoaderpgAdmin)171 172 173def register_binary_typecasters(connection):174    # The new classes can be registered globally, on a connection, on a cursor175 176    connection.adapters.register_loader(17,177                                        pgAdminByteaLoader)178 179    connection.adapters.register_loader(1001,180                                        pgAdminByteaLoader)181 182 183def register_array_to_string_typecasters(connection=None):184    type_array = PSYCOPG_SUPPORTED_BUILTIN_ARRAY_DATATYPES +\185        PSYCOPG_SUPPORTED_JSON_ARRAY_TYPES +\186        PSYCOPG_SUPPORTED_IPADDRESS_ARRAY_TYPES +\187        PSYCOPG_SUPPORTED_RANGE_ARRAY_TYPES + \188        PSYCOPG_SUPPORTED_MULTIRANGE_ARRAY_TYPES + \189        TO_ARRAY_OF_STRING_DATATYPES190 191    for typ in type_array:192        if connection:193            connection.adapters.register_loader(typ,194                                                TextLoaderpgAdmin)195 196 197class pgAdminInetLoader(InetLoader):198    def load(self, data):199        if isinstance(data, memoryview):200            data = bytes(data)201 202        if b"/" in data:203            return str(ip_interface(data.decode()))204        else:205            return str(ip_address(data.decode()))206 207 208# The new classes can be registered globally, on a connection, on a cursor209psycopg.adapters.register_loader("inet", pgAdminInetLoader)210psycopg.adapters.register_loader("cidr", pgAdminInetLoader)211 212 213class pgAdminByteaLoader(Loader):214    def load(self, data):215        return 'binary data' if data is not None else None216 217 218class TextLoaderpgAdmin(TextLoader):219    def load(self, data):220        postgres_encoding, python_encoding = get_encoding(221            self.connection.info.encoding)222        if postgres_encoding not in ['SQLASCII', 'SQL_ASCII']:223            # In case of errors while decoding data, instead of raising error224            # replace errors with empty space.225            # Error - utf-8 code'c can not decode byte 0x7f:226            # invalid continuation byte227            if isinstance(data, memoryview):228                return bytes(data).decode(self._encoding, errors='replace')229            else:230                return data.decode(self._encoding, errors='replace')231        else:232            # SQL_ASCII Database233            try:234                if isinstance(data, memoryview):235                    return bytes(data).decode(python_encoding)236                return data.decode(python_encoding)237            except Exception:238                if isinstance(data, memoryview):239                    return bytes(data).decode('UTF-8')240                return data.decode('UTF-8')241            else:242                if isinstance(data, memoryview):243                    return bytes(data).decode('ascii', errors='replace')244                return data.decode('ascii', errors='replace')245