[Kde-pim] akonadi, server/storage and PostgreSQl

[Cédric Villemain]

> > Akonadi maps QString to BYTEA (PostgreSQL data type), I believe it
> > should map it to TEXT. 1. we do use toLatin1 to prepare data when
> > working with QString, so it should be safe for TEXT in PostgreSQL.
> > 2. escaping BYTEA and TEXT does not work the same, BYTEA are
> > escaped string *or* hex string. TEXT is plain, or escaped.
> 
> We would need to check what kind of data is stored in BYTEA, but
> QString itself is UTF8. Does TEXT support utf8 (sorry, no time to
> look this up now)?

Yes, it does.

> How do they perform performance wise (and size wise in the database)?

TEXT performs well , BYTEA too. BYTEA does not have the checks a TEXT 
has (about encoding).
I believe it changes nothing for akonadi (performance and size).

> > So, '\SEEN' is incorrect for BYTEA input but is correct for TEXT
> > input (with standard_conforming_string ON) '\\SEEN' is the correct
> > input for BYTEA. It can be E'\\\\SEEN' which is the same. Let's
> > see:
> > # select E'\\\\SEEN'::bytea;
> > bytea | \\SEEN
> > # select '\\SEEN'::bytea;
> > bytea | \\SEEN
> > 
> > BYTEA is by default output in hex format in recent versions of
> > PostgreSQL. A required fix in the akonadi-backend-postgresql is to
> > do:
> > # set bytea_output to escape;
> > to have a clean output (else you'll get a bunch of \x... like that
> > '\x455c5c5345454e' => '\\SEEN') I've a patch for that in akonadi,
> > however it should not be required: it is probably the job of QTSql
> > to handle that (IO of BYTEA)
> 
> When Akonadi sets up the database, it can set up as we want the
> bytea_output. If we use a shared server, indeed this could be more
> important, but the setting is per connection, no? Anyway, what is your
> suggestion, should we use "escape" or "hex" encoding?
> 
> In any case if the current code stores \SEEN incorrectly, that must be
> fixed.

It depends of QtSql.
It probably require 'escaped' output to get the data as words, I'm not 
absolutely sure the driver handle the hex format I/O as used by akonadi.

> > This is why I start to really wonder why you used BYTEA: the QT type
> > for BYTEA should be only QByteArray. QTsql do use PQunescapeString
> > (a function from libpq, the postgresql library). So it is safe. But
> > it is unsafe to do Qstring -> BYTEA and has been spoted by recent
> > problem with libqt-sql-psql 8.4.5: the change is that now there is
> > a test on qtdriver and double escaping is done only for Qstring
> > when standard_conforming_string is OFF (so the code on akonadi is
> > now really bugy in regard of this situation, because the double
> > escaping is not done anymore).
> 
> Indeed, there is a problem with the latest PSQL driver in Qt. But as I
> understood this is a problem in the driver, no (again, something I
> wanted to test for a while, but never had time to write a proper test
> app)?

Well, the QT patch is bad written, but it works. The problem is on 
akonadi which map qstring to bytea instead of text.

> > For memory, PostgreSQL provides functions to convert BYTEA to TEXT,
> > UTF8 in this example:
> > # select bytea_data, convert_from(bytea_data,'UTF8') as UTF8_data
> > FROM # (VALUES ('\\SEEN'::bytea)) t(bytea_data);
> > bytea_data | \\SEEN
> > utf8_data  | \SEEN
> > # select text_data, convert_to(text_data,'UTF8') as bytea_data FROM
> > # (VALUES ('\SEEN'::TEXT)) t(text_data);
> > text_data  | \SEEN
> > bytea_data | \\SEEN
> > 
> > So, what about mapping directly QString to TEXT and be safe ?
> 
> See above. If supports UTF8, speed is comparable and space in the
> database is not much bigger, it can be changed.

OK, perfect.

> > I also wonder why some SQL statements are constructed instead of
> > working with Qsql a bit more. I saw some
> > string.replace('\\','\\\\') in the akonadi code when I believe
> > qtsql.formatValue() should have been used.
> Could you point out an example? It can be that some statements come
> from a dynamic query builder, so they are not looking too optimized,
> but we would need to see the individual cases to see what can be
> fixed.

In the MySQL and PostgreSQL DbInitializer*::buildInsertValuesStatement, 
server/src/storage/dbinitializer_p.cpp

-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 490 bytes
Desc: This is a digitally signed message part.
URL: <http://mail.kde.org/pipermail/kde-pim/attachments/20130912/1988d6ab/attachment.sig>
-------------- next part --------------
_______________________________________________
KDE PIM mailing list kde-pim at kde.org
https://mail.kde.org/mailman/listinfo/kde-pim
KDE PIM home page at http://pim.kde.org/