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

[Andras Mantia]

Hi,

Cédric Villemain wrote:

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


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

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

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


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

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

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

Andras
_______________________________________________
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/