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

[Cédric Villemain]

Hello dear kde developpers,

This mail to introduce myself.
I'm Cédric Villemain and I work mostly on/with/for PostgreSQL.
I'm also a long debian and kde user.

I would like to help in the areas (at least) of PostgreSQL and akonadi/server/storage (and eventualy qtsql...).
I already git clone akonadi and qt so the first step is done :)

I am unsure to understand well the decisions made with backend-postgresql so I would like to discuss them here, or please point me to the relevant threads in ML archive if any.

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.

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)

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).

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 ?

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.

Comments ?

-- 
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/20130911/9d81befd/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/