Hi,
Please find attached patch for RM 1983.
This issue only occurs when database encoding is other than utf-8
Also other issue was when we use connection of database with encoding other
than utf-8 to retrieve data from cluster table/s which has encoding utf-8
(e.g. pg_database) then data was not decoded properly.
--
*Harshal Dhumal*
*Software Engineer*
EnterpriseDB India: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/web/pgadmin/browser/server_groups/servers/databases/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/__init__.py
index 88360dd..df325e1 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/__init__.py
@@ -9,6 +9,7 @@
"""Implements the Database Node"""
+import sys
import simplejson as json
import re
from functools import wraps
@@ -568,6 +569,24 @@ class DatabaseView(PGChildNodeView):
)
data['old_name'] = (rset['rows'][0])['name']
+
+ # pg_database is cluster level table (with encoding utf-8)
+ # which is common to all databases under that cluster.
+ # So if connection of database with encoding other than utf-8 is
+ # used to query data from pg_database then manually decode
+ # database name to get correct one.
+
+ if self.conn.py_encoding != 'utf_8':
+ if sys.version_info < (3,):
+ data['old_name'] = data['old_name']\
+ .decode('utf-8')\
+ .encode(self.conn.py_encoding)\
+ .decode('utf-8')
+ else:
+ data['old_name'] = bytes(
+ data['old_name'], self.conn.py_encoding
+ ).decode('utf-8')
+
if 'name' not in data:
data['name'] = data['old_name']
diff --git a/web/pgadmin/browser/utils.py b/web/pgadmin/browser/utils.py
index 0dc2d01..2224720 100644
--- a/web/pgadmin/browser/utils.py
+++ b/web/pgadmin/browser/utils.py
@@ -11,6 +11,7 @@
from abc import abstractmethod
+import sys
import flask
from flask import render_template, current_app
from flask_babel import gettext
@@ -368,7 +369,24 @@ class PGChildNodeView(NodeView):
current_app.logger.error(result)
for row in result['rows']:
+
+ # pg_database is cluster level table (with encoding utf-8)
+ # which is common to all databases under that cluster.
+ # So if connection of database with encoding other than utf-8 is
+ # used to query data from pg_database then manually decode
+ # dependencies name to get correct one.
ref_name = row['refname']
+ if conn.py_encoding != 'utf_8':
+ if sys.version_info < (3,):
+ ref_name = ref_name\
+ .decode('utf-8')\
+ .encode(conn.py_encoding)\
+ .decode('utf-8')
+ else:
+ ref_name = bytes(
+ ref_name, conn.py_encoding
+ ).decode('utf-8')
+
dep_str = row['deptype']
dep_type = ''
diff --git a/web/pgadmin/utils/driver/psycopg2/__init__.py b/web/pgadmin/utils/driver/psycopg2/__init__.py
index 4cafeb2..b442846 100644
--- a/web/pgadmin/utils/driver/psycopg2/__init__.py
+++ b/web/pgadmin/utils/driver/psycopg2/__init__.py
@@ -26,7 +26,7 @@ from flask import g, current_app, session
from flask_babel import gettext
from flask_security import current_user
from pgadmin.utils.crypto import decrypt
-from psycopg2.extensions import adapt
+from psycopg2.extensions import adapt, encodings
import config
from pgadmin.model import Server, User
@@ -74,6 +74,7 @@ psycopg2.extensions.register_type(
'NUMERIC_RANGE_TEXT', psycopg2.STRING)
)
+
def register_string_typecasters(connection):
"""
Casts various types to string, resolving issues with out of
@@ -94,6 +95,30 @@ def register_string_typecasters(connection):
new_type = psycopg2.extensions.new_type(oids, 'RETURN_STRING', return_as_string)
psycopg2.extensions.register_type(new_type)
+ # In python3 when database encoding is other than utf-8 and client encoding
+ # is set to UNICODE then we need to map data from database encoding
+ # to utf-8.
+ # This is required because when client encoding is set to UNICODE then
+ # psycopg assumes database encoding utf-8 and not the actual encoding.
+ # Not sure whether it's bug or feature in psycopg for python3.
+
+ if sys.version_info >= (3,) and connection.encoding != 'UTF8':
+ def return_as_unicode(value, cursor):
+ if value is None:
+ return None
+ # Treat value as byte sequence of database encoding and then decode
+ # it as utf-8 to get correct unicode value.
+ return bytes(
+ value, encodings[cursor.connection.encoding]
+ ).decode('utf-8')
+
+ unicode_type = psycopg2.extensions.new_type(
+ (19, 18, 25, 1042, 1043, 0),
+ 'UNICODE', return_as_unicode)
+
+ psycopg2.extensions.register_type(unicode_type)
+
+
class Connection(BaseConnection):
"""
class Connection(object)
@@ -183,6 +208,7 @@ class Connection(BaseConnection):
self.manager = manager
self.db = db if db is not None else manager.db
self.conn = None
+ self.py_encoding = None
self.auto_reconnect = auto_reconnect
self.async = async
self.__async_cursor = None
@@ -345,6 +371,10 @@ Failed to connect to the database server(#{server_id}) for connection ({conn_id}
self.conn_id.encode('utf-8')
), None)
+ # map postgres encoding string to python encoding string
+ # e.g UTF8 -> utf_8 similarly win1256 -> cp1256 etc.
+ self.py_encoding = encodings[self.conn.encoding]
+
status, cur = self.__cursor()
formatted_exception_msg = self._formatted_exception_msg
mgr = self.manager
@@ -435,6 +465,25 @@ WHERE db.datname = current_database()""")
res = cur.fetchmany(1)[0]
mgr.db_info[res['did']] = res.copy()
+ # pg_database is cluster level table (with encoding utf-8)
+ # which is common to all databases under that cluster.
+ # So if connection of database with encoding other than utf-8 is
+ # used to query data from pg_database then manually decode
+ # database name to get correct one.
+
+ if self.conn.encoding != 'UTF8':
+ if sys.version_info < (3,):
+ mgr.db_info[res['did']]['datname'] = mgr.db_info[
+ res['did']]['datname']\
+ .decode('utf-8')\
+ .encode(self.py_encoding)\
+ .decode('utf-8')
+ else:
+ mgr.db_info[res['did']]['datname'] = bytes(
+ mgr.db_info[res['did']]['datname'],
+ self.py_encoding
+ ).decode('utf-8')
+
# We do not have database oid for the maintenance database.
if len(mgr.db_info) == 1:
mgr.did = res['did']
@@ -568,6 +617,13 @@ WHERE
query: SQL query to run.
params: Extra parameters
"""
+
+ if sys.version_info < (3,):
+ if type(query) == unicode:
+ query = query.encode('utf-8')
+ else:
+ query = query.encode('utf-8')
+
cur.execute(query, params)
if self.async == 1:
self._wait(cur.connection)
@@ -585,7 +641,7 @@ WHERE
u"Execute (with server cursor) for server #{server_id} - {conn_id} (Query-id: {query_id}):\n{query}".format(
server_id=self.manager.sid,
conn_id=self.conn_id,
- query=query,
+ query=query.decode('utf-8') if sys.version_info < (3,) else query,
query_id=query_id
)
)
@@ -703,6 +759,13 @@ WHERE
params: extra parameters to the function
formatted_exception_msg: if True then function return the formatted exception message
"""
+
+ if sys.version_info < (3,):
+ if type(query) == unicode:
+ query = query.encode('utf-8')
+ else:
+ query = query.encode('utf-8')
+
self.__async_cursor = None
status, cur = self.__cursor()
@@ -715,7 +778,7 @@ WHERE
u"Execute (async) for server #{server_id} - {conn_id} (Query-id: {query_id}):\n{query}".format(
server_id=self.manager.sid,
conn_id=self.conn_id,
- query=query,
+ query=query.decode('utf-8'),
query_id=query_id
)
)
@@ -733,7 +796,7 @@ Failed to execute query (execute_async) for the server #{server_id} - {conn_id}
""".format(
server_id=self.manager.sid,
conn_id=self.conn_id,
- query=query,
+ query=query.decode('utf-8'),
errmsg=errmsg,
query_id=query_id
)
@@ -1421,7 +1484,8 @@ class ServerManager(object):
database = self.db
elif did in self.db_info:
database = self.db_info[did]['datname']
- if hasattr(str, 'decode'):
+ if hasattr(str, 'decode') and \
+ not isinstance(database, unicode):
database = database.decode('utf-8')
else:
maintenance_db_id = u'DB:{0}'.format(self.db)
@@ -1511,7 +1575,8 @@ WHERE db.oid = {0}""".format(did))
if did is not None:
if did in self.db_info and 'datname' in self.db_info[did]:
database = self.db_info[did]['datname']
- if hasattr(str, 'decode'):
+ if hasattr(str, 'decode') and \
+ not isinstance(database, unicode):
database = database.decode('utf-8')
if database is None:
return False
--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
