public inbox for [email protected]
help / color / mirror / Atom feedFrom: Aditya Toshniwal <[email protected]>
To: Dave Page <[email protected]>
Cc: Joao De Almeida Pereira <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4][RM#3289] Can't query SQL_ASCII database.
Date: Thu, 14 Jun 2018 11:35:35 +0530
Message-ID: <CAM9w-_=g0GJTyF8QegHJtKojcghcE+xzDPQUig_YzUnnH3nyWg@mail.gmail.com> (raw)
In-Reply-To: <CAM9w-_n6LtJTXYhmrcYqvyvzXkshDMM3Z0yyKUWU4Vdmg7tNvw@mail.gmail.com>
References: <CAM9w-_ntCUguZp2rVJXPsDvWJbWneAz4-AHJoYB+nUTSYMXc9g@mail.gmail.com>
<CA+OCxozRjJY6RWrXpMLD6-dWnm2LdPs6bS+VmJa403vxVoLwhg@mail.gmail.com>
<CAM9w-_m-cJax16zqBonx-d0-HSy5dNoOzB13HWAad5Xv0Wu=CA@mail.gmail.com>
<CANxYE3+qrGeZYzdYEtzxS8i2P+325V5E+n1xVpyzTwn7vRqRDg@mail.gmail.com>
<CAM9w-_mTe0gW+2cdhMutMWyBsRt7Om3G_Em126Ryntf0nOiHbA@mail.gmail.com>
<CAM9w-_mXdJhuRFn87yAuqWtfrhnRgw1KvU2PpthL-hbnOt2QpA@mail.gmail.com>
<CAE+jjampAPoDq7xJgeNf1L=i_1jf83PLr=dZYicfkEMp6_UBAg@mail.gmail.com>
<CAM9w-_k2Fo-qDy+qxM5i=Aba__TAWxrryR+m3izTXB=koToKcQ@mail.gmail.com>
<CA+OCxoz+Wo3pUuu8SMBzAsQvrOjYLPQHCfQMtO3X3HKezKcapw@mail.gmail.com>
<CAM9w-_nEDycbeEEeUhKwXjxmkNC39YzqhggJR3Frc4pq6WcJ0Q@mail.gmail.com>
<CAM9w-_kDQkAWRZrRRtmMDz8DZ5x2wGoUro6oQbQqTzLmZZGyBw@mail.gmail.com>
<CAE+jjametYGjStNFZFW544Jcm_by1OABtb7yFQWKGzyfk08QiA@mail.gmail.com>
<CAM9w-_=rsazJWCu5xRqLGkYX3FW22vVuyc0NcR5TPskO9PApUg@mail.gmail.com>
<CAE+jjakwbfsbwkvwa6N1QGjGeVb36sWnde+C6amnHQLHdTE3kQ@mail.gmail.com>
<CAM9w-_km5Zv=Gw6CUZF8m3zzacOKL-ir-QNw3xPjP2MJfLRg7w@mail.gmail.com>
<CAM9w-_=c=82+5ESAEMXUQf5NOrfouZ5JxJMfr+56vJAfGz2DrA@mail.gmail.com>
<CA+OCxoxAhWEF5tQQy84QoOYB=ed--o--ppBoXZ1dwvWNBoNMQw@mail.gmail.com>
<CAM9w-_nUfackUWYNZDRcJ43D6ftYWnWkRsxvcyZVDzABkqza8A@mail.gmail.com>
<CA+OCxoyRgrQ=83Wo1YwsR9oSHE87=-20uP7d7zmF9F685CW2sA@mail.gmail.com>
<CAM9w-_ngHm2XpfXeA+RtWhifsHmN06rOEYwg5UrZBc=ZOum1Ug@mail.gmail.com>
<CA+OCxoyn32gZu78cc-p7Cr3zdsyviBVYD+X59W0-u+DTVUOzJg@mail.gmail.com>
<CAM9w-_=xKk35uQBYM+yZGkttryodU1CPF4n=jYEezKDm4GD_qw@mail.gmail.com>
<CA+OCxoxuS2GXK=LxREpniK6hHQ=d2G2FBpjD7JSohxZJpQwxPA@mail.gmail.com>
<CAM9w-_kPsWEodXtznzA0ZwXSO3d8t2agg5sUzF=2EEsGfakuMw@mail.gmail.com>
<CA+OCxoz7fFQn5ADzLbohXknMNk-1kfeTjAgCO4NrsXZPRiFVNw@mail.gmail.com>
<CAM9w-_mk04Rz6KVvQtmwiHs4s+OFDsDgCKebUO-vT0W2DfGi4w@mail.gmail.com>
<CA+OCxoyQtqqWhfNY5Um907AocAt1GsLcWvFJGjr5FLLeoWDCkA@mail.gmail.com>
<CAM9w-_n6LtJTXYhmrcYqvyvzXkshDMM3Z0yyKUWU4Vdmg7tNvw@mail.gmail.com>
I am sorry I missed the attachment. :(
PFA.
On Thu, Jun 14, 2018 at 11:34 AM, Aditya Toshniwal <
[email protected]> wrote:
> Hi Hackers,
>
> PFA updated patch.
>
> On Thu, Jun 7, 2018 at 4:41 PM, Dave Page <[email protected]> wrote:
>
>> Hi
>>
>> On Thu, Jun 7, 2018 at 12:05 PM, Aditya Toshniwal <
>> [email protected]> wrote:
>>
>>> Hi Dave,
>>>
>>>
>>> On Thu, Jun 7, 2018 at 4:07 PM, Dave Page <[email protected]> wrote:
>>>
>>>> Hi
>>>>
>>>> On Wed, Jun 6, 2018 at 2:02 PM, Aditya Toshniwal <
>>>> [email protected]> wrote:
>>>>
>>>>> Hi Hackers,
>>>>>
>>>>> PFA updated patch as the previous one was not working as expected. I
>>>>> have tried to make it similar to that of pgAdmin3 and you do not need to
>>>>> change client_encoding as it is set now based on server encoding. It works
>>>>> fine with "view data" also.
>>>>>
>>>>
>>>> - In connection.py, at ~409, shouldn't we set the client_encoding to
>>>> SQL_ASCII? Otherwise it could be overridden with something unexpected if
>>>> the client has PGCLIENTENCODING set for example.
>>>>
>>> Yeah I agree, it would be better to add. Will add the change.
>>>
>>>>
>>>> - With or without that change, I get the following test failure on
>>>> macOS with Python 2.7.10:
>>>>
>>> It works fine on my machine with Python 2.7 and macOS. Could you please
>>> let me know the Postgres DB version also.
>>>
>>
>> PostgreSQL 9.4.10 on x86_64-apple-darwin, compiled by
>> i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build
>> 5658) (LLVM build 2336.11.00), 64-bit
>>
>>
>>> Will test on few more machines.
>>>
>>>>
>>>> ======================================================================
>>>> ERROR: runTest (pgadmin.tools.sqleditor.tests
>>>> .test_encoding_charset.TestEncodingCharset)
>>>> With Encoding SQL_ASCII
>>>> ----------------------------------------------------------------------
>>>> Traceback (most recent call last):
>>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/tests/test_encoding_charset.py",
>>>> line 86, in runTest
>>>> response = self.tester.get(url)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
>>>> line 830, in get
>>>> return self.open(*args, **kw)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/testing.py",
>>>> line 127, in open
>>>> follow_redirects=follow_redirects)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
>>>> line 803, in open
>>>> response = self.run_wsgi_app(environ, buffered=buffered)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
>>>> line 716, in run_wsgi_app
>>>> rv = run_wsgi_app(self.application, environ, buffered=buffered)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py",
>>>> line 923, in run_wsgi_app
>>>> app_rv = app(environ, start_response)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>>> line 1997, in __call__
>>>> return self.wsgi_app(environ, start_response)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>>> line 1985, in wsgi_app
>>>> response = self.handle_exception(e)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>>> line 1540, in handle_exception
>>>> reraise(exc_type, exc_value, tb)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>>> line 1982, in wsgi_app
>>>> response = self.full_dispatch_request()
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>>> line 1614, in full_dispatch_request
>>>> rv = self.handle_user_exception(e)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>>> line 1517, in handle_user_exception
>>>> reraise(exc_type, exc_value, tb)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>>> line 1612, in full_dispatch_request
>>>> rv = self.dispatch_request()
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
>>>> line 1598, in dispatch_request
>>>> return self.view_functions[rule.endpoint](**req.view_args)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask_login.py",
>>>> line 792, in decorated_view
>>>> return func(*args, **kwargs)
>>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/__init__.py",
>>>> line 576, in poll
>>>> 'oids': oids
>>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/ajax.py", line 61,
>>>> in make_json_response
>>>> separators=(',', ':')),
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/simplejson/__init__.py",
>>>> line 399, in dumps
>>>> **kw).encode(obj)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/simplejson/encoder.py",
>>>> line 291, in encode
>>>> chunks = self.iterencode(o, _one_shot=True)
>>>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/simplejson/encoder.py",
>>>> line 373, in iterencode
>>>> return _iterencode(o, 0)
>>>> UnicodeDecodeError: 'utf8' codec can't decode byte 0xad in position 0:
>>>> invalid start byte
>>>>
>>>> ----------------------------------------------------------------------
>>>> Ran 317 tests in 30.692s
>>>>
>>>> FAILED (errors=1, skipped=21)
>>>>
>>>
> This is fixed. There was a problem with json dumps. Now, json dumps will
> be done based on connection encoding.
>
>>
>>>>
>>>>
>>>>> The only problem is, I cannot find equivalent codec for wxConvLibc in
>>>>> python. The closest one I could find is raw_unicode_escape. So, in a
>>>>> SQL_ASCII database, non ASCII characters may differ in pgAdmin4 and
>>>>> pgAdmin3, but it will display results.
>>>>>
>>>>
>>>> Yeah, I think that's fine. For the small number of people with
>>>> SQL_ASCII databases, seeing escaped characters is better than nothing.
>>>>
>>>>
>>>>>
>>>>>
>>>>> Dave,
>>>>> You need to add "E" before the string to be inserted, otherwise \x
>>>>> will be considered as a plain string.
>>>>> INSERT INTO sql_ascii (data) VALUES (E'[Invalid UTF-8] Blob:
>>>>> \xf4\xa5\xa3\xa5');
>>>>>
>>>>
>>>> Yeah, sorry - I copied the wrong version of the query :-(
>>>>
>>>>
>>>>>
>>>>>
>>>>> Kindly review.
>>>>>
>>>>> Thanks and Regards,
>>>>> Aditya Toshniwal
>>>>> Software Engineer | EnterpriseDB Software Solutions | Pune
>>>>> "Don't Complain about Heat, Plant a tree"
>>>>>
>>>>> On Tue, Jun 5, 2018 at 6:42 PM, Dave Page <[email protected]> wrote:
>>>>>
>>>>>> Hi
>>>>>>
>>>>>> On Tue, Jun 5, 2018 at 2:03 PM, Aditya Toshniwal <
>>>>>> [email protected]> wrote:
>>>>>>
>>>>>>> Hi
>>>>>>>
>>>>>>> On Tue, Jun 5, 2018 at 6:25 PM, Dave Page <[email protected]> wrote:
>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Tue, Jun 5, 2018 at 1:49 PM, Aditya Toshniwal <
>>>>>>>> [email protected]> wrote:
>>>>>>>>
>>>>>>>>> Hi Dave,
>>>>>>>>>
>>>>>>>>> The problem of SQL ASCII is solved with the patch, and not related
>>>>>>>>> to setting the client encoding of the sql window.
>>>>>>>>>
>>>>>>>>
>>>>>>>> No it's not. It doesn't work for me as I said (and showed the
>>>>>>>> example of).
>>>>>>>>
>>>>>>>
>>>>>>> After setting the client_encoding to SQL_ASCII you got the output.
>>>>>>> Previously, it used to fail in the back end itself because python encoding
>>>>>>> failure. That is fixed.
>>>>>>> The error ERROR: invalid byte sequence for encoding "UTF8": 0x80 is
>>>>>>> thrown by postgres and not python or pgAdmin4. You will get the same error
>>>>>>> even if you
>>>>>>> connect from psql.
>>>>>>>
>>>>>>
>>>>>> Sure - but that is not a fix. You have no way of running the SET
>>>>>> command if you're using "view data" - and in the query tool, users just
>>>>>> expect it to work (as it did in pgAdmin 3).
>>>>>>
>>>>>>
>>>>>>>
>>>>>>>>
>>>>>>>>> I can see there is no SET call in pgAdmin3 for client_encoding. I
>>>>>>>>> can remove the SET client_encoding='UNICODE'; that will solve the
>>>>>>>>> problem. But, can you please let me know why that was added.
>>>>>>>>>
>>>>>>>>
>>>>>>>> There is, but it's inside an API call (PQsetClientEncoding):
>>>>>>>>
>>>>>>>> 300
>>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l300;
>>>>>>>> wxLogInfo(wxT("Setting client_encoding to '%s'")
>>>>>>>> , encoding.c_str());
>>>>>>>> 301
>>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l301;
>>>>>>>> if (PQsetClientEncoding(conn, encoding.ToAscii()))
>>>>>>>> 302
>>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l302;
>>>>>>>> {
>>>>>>>> 303
>>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l303;
>>>>>>>> wxLogError(wxT("%s"), GetLastError().c_str());
>>>>>>>> 304
>>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l304;
>>>>>>>> }
>>>>>>>> 305
>>>>>>>> <https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l305;
>>>>>>>>
>>>>>>>> Oops ! Missed that. Apologies.
>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>
>>>>>>>>> Will remove the set call and will send you the updated patch if
>>>>>>>>> everything works fine.
>>>>>>>>>
>>>>>>>>
>>>>>>>> No, we need to ensure the client encoding is set correctly. It just
>>>>>>>> needs to be set to SQL_ASCII if it's a SQL_ASCII database (I believe).
>>>>>>>>
>>>>>>>>
>>>>>>> Need to rework on the initialise method. Will come with an updated.
>>>>>>> patch. Sorry for trouble.
>>>>>>>
>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Tue, Jun 5, 2018 at 6:05 PM, Dave Page <[email protected]>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> Hi
>>>>>>>>>>
>>>>>>>>>> On Tue, Jun 5, 2018 at 1:21 PM, Aditya Toshniwal <
>>>>>>>>>> [email protected]> wrote:
>>>>>>>>>>
>>>>>>>>>>> Hi Dave,
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> On Tue, Jun 5, 2018 at 4:56 PM, Dave Page <[email protected]>
>>>>>>>>>>> wrote:
>>>>>>>>>>>
>>>>>>>>>>>> Hi
>>>>>>>>>>>>
>>>>>>>>>>>> On Tue, Jun 5, 2018 at 9:50 AM, Aditya Toshniwal <
>>>>>>>>>>>> [email protected]> wrote:
>>>>>>>>>>>>
>>>>>>>>>>>>> Hi Hackers,
>>>>>>>>>>>>>
>>>>>>>>>>>>> PFA updated patch. The sqleditor change is sent separately and
>>>>>>>>>>>>> removed from current patch as suggested.
>>>>>>>>>>>>> The test cases were running fine when the module was specified
>>>>>>>>>>>>> using --pkg but were failing in complete run. Fixed that.
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> I did a quick test by creating a SQL_ASCII database containing
>>>>>>>>>>>> a simple table:
>>>>>>>>>>>>
>>>>>>>>>>>> CREATE TABLE sql_ascii (id serial primary key, data text);
>>>>>>>>>>>>
>>>>>>>>>>>> And then populated it with data:
>>>>>>>>>>>>
>>>>>>>>>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c
>>>>>>>>>>>> "INSERT INTO sql_acsii (data) VALUES ('[Windows-1252] Euro: \x80 Double
>>>>>>>>>>>> dagger: \x87');"
>>>>>>>>>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c
>>>>>>>>>>>> "INSERT INTO sql_ascii (data) VALUES ('[Latin-1] Yen: \xa5 Half:
>>>>>>>>>>>> \xbd');"
>>>>>>>>>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c
>>>>>>>>>>>> "INSERT INTO sql_ascii (data) VALUES ('[Japanese] Ship: \xe8\x88\xb9');"
>>>>>>>>>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c
>>>>>>>>>>>> "INSERT INTO sql_ascii (data) VALUES ('[Invalid UTF-8] Blob:
>>>>>>>>>>>> \xf4\xa5\xa3\xa5');"
>>>>>>>>>>>>
>>>>>>>>>>>> I then right-clicked the table in the treeview, and selected
>>>>>>>>>>>> the option to view all rows, and immediately saw an error:
>>>>>>>>>>>>
>>>>>>>>>>>> 2018-06-05 12:23:27,319: SQL pgadmin: Execute (async) for
>>>>>>>>>>>> server #1 - CONN:1187535 (Query-id: 8522474):
>>>>>>>>>>>> SELECT * FROM public.sql_ascii
>>>>>>>>>>>> ORDER BY id ASC
>>>>>>>>>>>> 2018-06-05 12:23:27,320: ERROR pgadmin: Failed to execute
>>>>>>>>>>>> query (execute_async) for the server #1 - CONN:1187535(Query-id: 8522474):
>>>>>>>>>>>> Error Message:ERROR: invalid byte sequence for encoding
>>>>>>>>>>>> "UTF8": 0x80
>>>>>>>>>>>> SQL state: 22021
>>>>>>>>>>>>
>>>>>>>>>>>> Running "SELECT * FROM sql_ascii" in the query tool resulted in
>>>>>>>>>>>> the same error, however, if I ran "SET client_encoding = 'SQL_ASCII';"
>>>>>>>>>>>> first, I do see results.
>>>>>>>>>>>>
>>>>>>>>>>>> I have confirmed that I've restarted the server after applying
>>>>>>>>>>>> the patch.
>>>>>>>>>>>>
>>>>>>>>>>>> What am I missing? Why don't we just set the client_encoding to
>>>>>>>>>>>> SQL_ASCII if it's a SQL_ASCII database?
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> It is by default same as the server encoding. But, the following
>>>>>>>>>>> existing code in web/pgadmin/utils/driver/psycopg2/connection.py
>>>>>>>>>>> makes the client_encoding as UNICODE for every connection. I am
>>>>>>>>>>> not sure it should be removed.
>>>>>>>>>>>
>>>>>>>>>>> status = _execute(cur, "SET DateStyle=ISO;"
>>>>>>>>>>>
>>>>>>>>>>> "SET client_min_messages=notice;"
>>>>>>>>>>>
>>>>>>>>>>> "SET bytea_output=escape;"
>>>>>>>>>>>
>>>>>>>>>>> "SET client_encoding='UNICODE';")
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> It was probably before you joined, but I have said a number of
>>>>>>>>>> times that pgAdmin 3 handled this differently and that maybe we should do
>>>>>>>>>> it the same way here. See https://git.postgresql.org
>>>>>>>>>> /gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp, in the
>>>>>>>>>> pgConn::Initialize() function.
>>>>>>>>>>
>>>>>>>>>> Either way, your patch isn't working for me.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Note that this testing was on Python 2.7.10 on MacOS.
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> Kindly review.
>>>>>>>>>>>>>
>>>>>>>>>>>>> Thanks and Regards,
>>>>>>>>>>>>> Aditya Toshniwal
>>>>>>>>>>>>> Software Engineer | EnterpriseDB Software Solutions | Pune
>>>>>>>>>>>>> "Don't Complain about Heat, Plant a tree"
>>>>>>>>>>>>>
>>>>>>>>>>>>> On Tue, Jun 5, 2018 at 10:15 AM, Aditya Toshniwal <
>>>>>>>>>>>>> [email protected]> wrote:
>>>>>>>>>>>>>
>>>>>>>>>>>>>> Hi
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> On Tue, Jun 5, 2018 at 1:08 AM, Joao De Almeida Pereira <
>>>>>>>>>>>>>> [email protected]> wrote:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> Hello Aditya,
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> There is no change related to notifications in this patch.
>>>>>>>>>>>>>>>> The below code is minor fix related to connection status of
>>>>>>>>>>>>>>>> sql editor. Can you please share the code snippet if it is not the below.
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> - # Check for the asynchronous notifies statements.
>>>>>>>>>>>>>>>> - conn.check_notifies(True)
>>>>>>>>>>>>>>>> - notifies = conn.get_notifies()
>>>>>>>>>>>>>>>> + if status is not None:
>>>>>>>>>>>>>>>> + # Check for the asynchronous notifies
>>>>>>>>>>>>>>>> statements.
>>>>>>>>>>>>>>>> + conn.check_notifies(True)
>>>>>>>>>>>>>>>> + notifies = conn.get_notifies()
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> This is a minor fix, but is it related to querying SQL_ASCII
>>>>>>>>>>>>>>> database?
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>> No its not. It is something I found when I was working on
>>>>>>>>>>>>>> SQL_ASCII related changes.
>>>>>>>>>>>>>> Well then, will send a separate patch for it.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> Thanks
>>>>>>>>>>>>>>> Victoria && Joao
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> --
>>>>>>>>>>>> Dave Page
>>>>>>>>>>>> Blog: http://pgsnake.blogspot.com
>>>>>>>>>>>> Twitter: @pgsnake
>>>>>>>>>>>>
>>>>>>>>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>>>>>>>>> The Enterprise PostgreSQL Company
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> Dave Page
>>>>>>>>>> Blog: http://pgsnake.blogspot.com
>>>>>>>>>> Twitter: @pgsnake
>>>>>>>>>>
>>>>>>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>>>>>>> The Enterprise PostgreSQL Company
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Dave Page
>>>>>>>> Blog: http://pgsnake.blogspot.com
>>>>>>>> Twitter: @pgsnake
>>>>>>>>
>>>>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>>>>> The Enterprise PostgreSQL Company
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Dave Page
>>>>>> Blog: http://pgsnake.blogspot.com
>>>>>> Twitter: @pgsnake
>>>>>>
>>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>>> The Enterprise PostgreSQL Company
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Dave Page
>>>> Blog: http://pgsnake.blogspot.com
>>>> Twitter: @pgsnake
>>>>
>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>> The Enterprise PostgreSQL Company
>>>>
>>>
>>>
>>
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
>
Attachments:
[application/octet-stream] RM3289.patch (11.5K, 3-RM3289.patch)
download | inline diff:
diff --git a/web/pgadmin/tools/sqleditor/__init__.py b/web/pgadmin/tools/sqleditor/__init__.py
index a9460dd7..d8f2ce67 100644
--- a/web/pgadmin/tools/sqleditor/__init__.py
+++ b/web/pgadmin/tools/sqleditor/__init__.py
@@ -574,7 +574,8 @@ def poll(trans_id):
'client_primary_key': client_primary_key,
'has_oids': has_oids,
'oids': oids
- }
+ },
+ encoding=conn.python_encoding
)
@@ -646,7 +647,8 @@ def fetch(trans_id, fetch_all=None):
'has_more_rows': has_more_rows,
'rows_fetched_from': rows_fetched_from,
'rows_fetched_to': rows_fetched_to
- }
+ },
+ encoding=conn.python_encoding
)
diff --git a/web/pgadmin/tools/sqleditor/tests/test_encoding_charset.py b/web/pgadmin/tools/sqleditor/tests/test_encoding_charset.py
new file mode 100644
index 00000000..0826decb
--- /dev/null
+++ b/web/pgadmin/tools/sqleditor/tests/test_encoding_charset.py
@@ -0,0 +1,103 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2018, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+from pgadmin.utils.route import BaseTestGenerator
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from regression import parent_node_dict
+from regression.python_test_utils import test_utils
+import json
+
+
+class TestEncodingCharset(BaseTestGenerator):
+ """
+ This class validates character support in pgAdmin4 for
+ different PostgresDB encodings
+ """
+ scenarios = [
+ (
+ 'With Encoding UTF8',
+ dict(
+ db_encoding='UTF8',
+ lc_collate='C',
+ test_str='A'
+ )),
+ (
+ 'With Encoding WIN1252',
+ dict(
+ db_encoding='WIN1252',
+ lc_collate='C',
+ test_str='A'
+ )),
+ (
+ 'With Encoding EUC_CN',
+ dict(
+ db_encoding='EUC_CN',
+ lc_collate='C',
+ test_str='A'
+ )),
+ (
+ 'With Encoding SQL_ASCII',
+ dict(
+ db_encoding='SQL_ASCII',
+ lc_collate='C',
+ test_str='\\255'
+ )),
+ ]
+
+ def setUp(self):
+ self.encode_db_name = 'encoding_' + self.db_encoding
+ self.encode_sid = self.server_information['server_id']
+ self.encode_did = test_utils.create_database(
+ self.server, self.encode_db_name,
+ (self.db_encoding, self.lc_collate))
+
+ def runTest(self):
+
+ db_con = database_utils.connect_database(self,
+ test_utils.SERVER_GROUP,
+ self.encode_sid,
+ self.encode_did)
+ if not db_con["info"] == "Database connected.":
+ raise Exception("Could not connect to the database.")
+
+ # Initialize query tool
+ url = '/datagrid/initialize/query_tool/{0}/{1}/{2}'.format(
+ test_utils.SERVER_GROUP, self.encode_sid, self.encode_did)
+ response = self.tester.post(url)
+ self.assertEquals(response.status_code, 200)
+
+ response_data = json.loads(response.data.decode('utf-8'))
+ self.trans_id = response_data['data']['gridTransId']
+
+ # Check character
+ url = "/sqleditor/query_tool/start/{0}".format(self.trans_id)
+ sql = "select E'{0}';".format(self.test_str)
+ response = self.tester.post(url, data=json.dumps({"sql": sql}),
+ content_type='html/json')
+ self.assertEquals(response.status_code, 200)
+ url = '/sqleditor/poll/{0}'.format(self.trans_id)
+ response = self.tester.get(url)
+ self.assertEquals(response.status_code, 200)
+ response_data = json.loads(response.data.decode('utf-8'))
+ self.assertEquals(response_data['data']['rows_fetched_to'], 1)
+
+ database_utils.disconnect_database(self, self.encode_sid,
+ self.encode_did)
+
+ def tearDown(self):
+ main_conn = test_utils.get_db_connection(
+ self.server['db'],
+ self.server['username'],
+ self.server['db_password'],
+ self.server['host'],
+ self.server['port'],
+ self.server['sslmode']
+ )
+ test_utils.drop_database(main_conn, self.encode_db_name)
diff --git a/web/pgadmin/utils/ajax.py b/web/pgadmin/utils/ajax.py
index 9b44aa3e..c19f778a 100644
--- a/web/pgadmin/utils/ajax.py
+++ b/web/pgadmin/utils/ajax.py
@@ -45,7 +45,8 @@ def get_no_cache_header():
def make_json_response(
- success=1, errormsg='', info='', result=None, data=None, status=200
+ success=1, errormsg='', info='', result=None, data=None, status=200,
+ encoding='utf-8'
):
"""Create a HTML response document describing the results of a request and
containing the data."""
@@ -58,7 +59,7 @@ def make_json_response(
return Response(
response=json.dumps(doc, cls=DataTypeJSONEncoder,
- separators=(',', ':')),
+ separators=(',', ':'), encoding=encoding),
status=status,
mimetype="application/json",
headers=get_no_cache_header()
diff --git a/web/pgadmin/utils/driver/psycopg2/connection.py b/web/pgadmin/utils/driver/psycopg2/connection.py
index cfd161a0..33762558 100644
--- a/web/pgadmin/utils/driver/psycopg2/connection.py
+++ b/web/pgadmin/utils/driver/psycopg2/connection.py
@@ -33,7 +33,7 @@ from pgadmin.utils import get_complete_file_path
from ..abstract import BaseDriver, BaseConnection
from .cursor import DictCursor
from .typecast import register_global_typecasters, \
- register_string_typecasters, register_binary_typecasters, \
+ register_binary_typecasters, \
register_array_to_string_typecasters, ALL_JSON_TYPES
@@ -387,8 +387,6 @@ class Connection(BaseConnection):
else:
self.conn.autocommit = True
- register_string_typecasters(self.conn)
-
if self.array_to_string:
register_array_to_string_typecasters(self.conn)
@@ -397,11 +395,22 @@ class Connection(BaseConnection):
if self.use_binary_placeholder:
register_binary_typecasters(self.conn)
- status = _execute(cur, "SET DateStyle=ISO;"
- "SET client_min_messages=notice;"
- "SET bytea_output=escape;"
- "SET client_encoding='UNICODE';")
-
+ if self.conn.encoding not in ('SQL_ASCII', 'SQLASCII',
+ 'MULE_INTERNAL', 'MULEINTERNAL'):
+ status = _execute(cur, "SET DateStyle=ISO;"
+ "SET client_min_messages=notice;"
+ "SET bytea_output=escape;"
+ "SET client_encoding='UNICODE';")
+ self.python_encoding = 'utf-8'
+ encodings[self.conn.encoding] = self.python_encoding
+ else:
+ status = _execute(cur, "SET DateStyle=ISO;"
+ "SET client_min_messages=notice;"
+ "SET bytea_output=escape;"
+ "SET client_encoding='{0}';"
+ .format(self.conn.encoding))
+ self.python_encoding = 'raw_unicode_escape'
+ encodings[self.conn.encoding] = self.python_encoding
if status is not None:
self.conn.close()
self.conn = None
diff --git a/web/pgadmin/utils/driver/psycopg2/typecast.py b/web/pgadmin/utils/driver/psycopg2/typecast.py
index f1366049..a8f6c385 100644
--- a/web/pgadmin/utils/driver/psycopg2/typecast.py
+++ b/web/pgadmin/utils/driver/psycopg2/typecast.py
@@ -163,49 +163,6 @@ def register_global_typecasters():
psycopg2.extensions.register_type(pg_array_types_to_array_of_string_type)
-def register_string_typecasters(connection):
- if connection.encoding != 'UTF8':
- # 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,):
- 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(
- # "char", name, text, character, character varying
- (19, 18, 25, 1042, 1043, 0),
- 'UNICODE', return_as_unicode)
- else:
- def return_as_unicode(value, cursor):
- if value is None:
- return None
- # Decode it as utf-8 to get correct unicode value.
- return value.decode('utf-8')
-
- unicode_type = psycopg2.extensions.new_type(
- # "char", name, text, character, character varying
- (19, 18, 25, 1042, 1043, 0),
- 'UNICODE', return_as_unicode)
-
- unicode_array_type = psycopg2.extensions.new_array_type(
- # "char"[], name[], text[], character[], character varying[]
- (1002, 1003, 1009, 1014, 1015, 0
- ), 'UNICODEARRAY', unicode_type)
-
- psycopg2.extensions.register_type(unicode_type)
- psycopg2.extensions.register_type(unicode_array_type)
-
-
def register_binary_typecasters(connection):
psycopg2.extensions.register_type(
psycopg2.extensions.new_type(
diff --git a/web/regression/python_test_utils/test_utils.py b/web/regression/python_test_utils/test_utils.py
index 3e517b61..464a09e1 100644
--- a/web/regression/python_test_utils/test_utils.py
+++ b/web/regression/python_test_utils/test_utils.py
@@ -116,7 +116,7 @@ def clear_node_info_dict():
del node_info_dict[node][:]
-def create_database(server, db_name):
+def create_database(server, db_name, encoding=None):
"""This function used to create database and returns the database id"""
try:
connection = get_db_connection(
@@ -130,8 +130,14 @@ def create_database(server, db_name):
old_isolation_level = connection.isolation_level
connection.set_isolation_level(0)
pg_cursor = connection.cursor()
- pg_cursor.execute(
- '''CREATE DATABASE "%s" TEMPLATE template0''' % db_name)
+ if encoding is None:
+ pg_cursor.execute(
+ '''CREATE DATABASE "%s" TEMPLATE template0''' % db_name)
+ else:
+ pg_cursor.execute(
+ '''CREATE DATABASE "%s" TEMPLATE template0
+ ENCODING='%s' LC_COLLATE='%s' LC_CTYPE='%s' ''' %
+ (db_name, encoding[0], encoding[1], encoding[1]))
connection.set_isolation_level(old_isolation_level)
connection.commit()
view thread (38+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: [pgAdmin4][RM#3289] Can't query SQL_ASCII database.
In-Reply-To: <CAM9w-_=g0GJTyF8QegHJtKojcghcE+xzDPQUig_YzUnnH3nyWg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox