QGIS » QGIS Application

While investigating uppercase support issues on PostGIS layers, I noticed that international characters likely would not work for PostgreSQL object names, but would work for data values.

This may be a reason for some complaints about PostGIS layers I have seen in the various QGIS forums, something like: "I loaded a PostGIS layer and don't see any data."

I decided to attempt to fix this problem. I believe that I have done so and expect to submit a patch in a few days after some further testing. There is one more aspect of PostgreSQL object names I haven't tackled yet - the use of quotes (double and single) and backslash. This requires escaping the strings, plus the rules have changed over various versions.

I also fixed several other problems with PostGIS layer handling, some of which hindered testing
internationalization. See below for the details.

Also, I created a list of my expectations for PostGIS layers, which is attached.

I am using a PostgreSQL server with UTF8 as its database encoding, so all of these notes ares based on that. I have considered what happens if a database has some other encoding, but I haven't tested this very much. Once connected there shouldn't be a problem as long as UTF8 is used for client encoding.

Some obesrvations:

On looking at various source files concerning PostGIS layers I found that strings passed to libpq and returned were not of the proper character set. Qt uses Unicode in QStrings, which are 16-bit entities, while libpq takes C strings, which have 8-bit characters. Thus, there must be a character set conversion for communication with PostgreSQL databases.

From the Qt documentation I found that the default codec for C strings is Latin1.

The local 8 bit encoder in en_US locale appears to be UTF-8. This depends on the locale determined or selected in QGIS.

Various QGIS PostgreSQL modules set the client encoding on the database to UNICODE when they connect. Most string handling for calls to libpq/results from libpq were using default encoding. A few had specific encoding set, but these were inconsistent - some Local8Bit, some Utf8. This likely causes the wrong character set translation to be used, unless all strings passed to/from the database are UTF-8 encoded.

The solution:

I have tried to find all occurrences of the libpq calls and apply toUtf8() or fromUtf8() QString functions as appropriate to the strings (both QString and C strings), except for when connecting to a database where I used toLocal8Bit()/fromLocal8Bit. This results in uniform behavior for international characters when used in database objects and data. However, there is a potential for problems on database names if the locale uses a different encoding from the database default, if the database name contains non-ASCII characters.

I have tested fairly extensively with databases/schemas/tables/column names having spaces, uppercase, and international characters - load layer, save & load project, attribute table, identify.

I have not worked on the SPIT plug-in, two threaded modules (they appear to be no longer used), and ...postgisbox... files (no code is affected).

In the process of fixing and testing the string handling on libpq calls I discovered a number of little problems in the PostGIS layer modules:
+ that a test for a blank stored password used QString::null instead of QString::isEmpty() so a Password dialog was never raised. Fixed
+ a side of effect of the improper password test appears that the New Connection dialog was made to always store the password. Fixed so the Save Password checkbox actually does something.
+ type detection did not return the correct name for int2 (it was null) because the query used to retrieve type information needed a SELECT DISTINCT. The int2 type returns 2 rows, others don't (I don't know why), so the query fails. Fixed
+ QgsDbSourceSelect::getTableInfo() has a note about not being able to get a query to work on uppercase names. I provided a query that does work and removed some code farther down that was a work-around.
+ found that int8 data values were not handled correctly due to Int type being used to store integers, including int8. I added code to use LongLong on int8.
+ in QgsPgQueryBuilder the list of available data values would intermittently quote string data when inserted in the query. The mActualFieldIsChar member variable was being declared as a local variable in two functions, so it was not set properly when it was tested for character or number field type. Fixed
+ I have replaced in several places SQL query text of the form:
"... =(select oid from pg_class where relname='sometablename' and relnamespace=(select oid from pg_namespace where nspname='someschema'))"
with
...=regclass('"someschema"."sometablename"')::oid
The ::oid cast is necessary in some situations to force a return of oid rather than text. Also, the names need the double quotes for uppercase use.
(of course the quotes need to be properly escaped in the C string)
This isn't so much a problem, but it makes for cleaner code and is future-proof for when catalog support is available in PostgreSQL.