Author Topic: [Translation UI] Database connection and UFT8  (Read 5421 times)

nite_man

  • NEEDS to work for LinuxMCE
  • ***
  • Posts: 1019
  • Want to work with LinuxMCE
    • View Profile
    • Smart Home Blog
[Translation UI] Database connection and UFT8
« on: October 07, 2009, 05:53:57 pm »
Hi,

I'd like to translate LMCE UI to Russian. But as I know now it isn't just a matter of translation of all descriptions in the Text_LS. The main problem is to convert database to the UTF8 and then from the client tell to mysql that opened connection is in UTF8. I described all steps to have UTF8 data in the Translate the GUI into another language. But still have a troubles with sending UTF8 option before connection to the database.

As I found the part where actual connection is done is db_wrapper. All methods such db_init(), db_real_connect() etc are just map to the mysql C API in the db_wrapper_mysql.h. The central point where connection method is called is PlutoUtils/DBHelper.h. I added there that code:
Code: [Select]
db_wrapper_options(m_pDB, MYSQL_SET_CHARSET_NAME, "utf8");right after
Code: [Select]
m_pDB = db_wrapper_init(NULL);Then I rebuilt three packages - pluto-orbiter, pluto-orbitergen and pluto-plutoutils, installed them and regenerated Orbiter. But without luck. The Russian letters are displayed like question mark. I have a simple Perl script to check if Russian titles are in UTF8:
Code: [Select]
#!/usr/bin/perl -w

use strict;
use warnings;
use utf8;

binmode STDOUT, ":utf8";

use DBI;
use Data::Dumper;

my $db_host = 'localhost';
my $db_name = 'pluto_main';
my $db_user = 'root';
my $db_pass = '';

my $dbh = DBI->connect("dbi:mysql:$db_name;host=$db_host", $db_user, $db_pass) or die "Cannot connect to $db_name: $DBI::err!";

$dbh->{'mysql_enable_utf8'} = 1;
$dbh->do('SET NAMES utf8');

my $query = "SELECT FK_Text, Description FROM Text_LS WHERE FK_Language=5";
my $res = $dbh->prepare($query) or die "Cannot prepare query: $DBI::err!";
my $rv = $res->execute() or die "Cannot execute query: $DBI::err!";
my $result = $res->fetchall_arrayref({});

my $count = 0;
for my $row (@$result) {
    my $msg = $row->{Description};
    print "$row->{FK_Text}: " . $msg . "\n";
}

$dbh->disconnect();

When I run it on the 0810 test machine with Russian language in the Text_LS it gives a proper output. But if those two rows are commented:
Code: [Select]
$dbh->{'mysql_enable_utf8'} = 1;
$dbh->do('SET NAMES utf8');

it output Russian text as question marks (the same as Orbiter). So, maybe somebody have some idea what's wrong with database connection. Will be very appreciated for any help since I don't have a wide experience with C++.

TIA
Michael Stepanov,
My setup: http://wiki.linuxmce.org/index.php/User:Nite_man#New_setup
Russian LinuxMCE community: http://linuxmce.ru

sambuca

  • Guru
  • ****
  • Posts: 462
    • View Profile
Re: [Translation UI] Database connection and UFT8
« Reply #1 on: October 07, 2009, 07:48:26 pm »
Hi,

I can't give any details, just a general guideline: All parts of the chain from the DB to the UI need to support UTF8. (at least no parts should break the utf8 string)

In this case two things come to mind:
* Does the font used support UTF-8 ? And in your particular case, does it have support for Cyrillic characters? Not all UTF-8 fonts have support for every character set, some may only support a subset of UTF-8.
* Does the variables used in C++ (string, char etc) support UTF-8? I think the standard string does not, but if no string manipulation is done which depend on a character being only one byte, I think it will work.

I really hope we can get proper utf-8 support in LMCE, keep up the good work  :)

best regards,
sambuca

Zaerc

  • Alumni
  • LinuxMCE God
  • *
  • Posts: 2256
  • Department of Redundancy Department.
    • View Profile
Re: [Translation UI] Database connection and UFT8
« Reply #2 on: October 07, 2009, 09:28:28 pm »
Maybe try adding default-character-set=utf8 to several sections (such as [client] and [mysqld]) of your /etc/mysql/my.cnf, after doing so you probably have to restart (at least the mysql server) or reboot.
"Change is inevitable. Progress is optional."
-- Anonymous


nite_man

  • NEEDS to work for LinuxMCE
  • ***
  • Posts: 1019
  • Want to work with LinuxMCE
    • View Profile
    • Smart Home Blog
Re: [Translation UI] Database connection and UFT8
« Reply #3 on: October 07, 2009, 10:56:01 pm »
Thank you, sambuca and Zaerc, for your ideas. Zaerc, I set everywhere in the my.cnf default charset utf8. The problem is that it won't affect the client connection from some application (mysql shell is affected). sambuca, good point. I'll investigate how to uft8 support is activated in C++.
Michael Stepanov,
My setup: http://wiki.linuxmce.org/index.php/User:Nite_man#New_setup
Russian LinuxMCE community: http://linuxmce.ru

nite_man

  • NEEDS to work for LinuxMCE
  • ***
  • Posts: 1019
  • Want to work with LinuxMCE
    • View Profile
    • Smart Home Blog
Re: [Translation UI] Database connection and UFT8
« Reply #4 on: October 09, 2009, 11:08:24 am »
Finally I found the way to set UTF8 connection for the clients on the server side. Here is the important part from the my.cnf:
Code: [Select]
[mysqld]
init_connect='SET NAMES utf8; SET collation_connection = utf8_general_ci;' # Set UTF8 for connection
default-character-set=utf8                                                
character-set-server=utf8                                                
collation-server=utf8_general_ci                                          
skip-character-set-client-handshake  # Tells to server to ignore client's charset for connetion

I tested it and it works. So, no any changes should be done in the code :)
« Last Edit: October 09, 2009, 12:48:29 pm by nite_man »
Michael Stepanov,
My setup: http://wiki.linuxmce.org/index.php/User:Nite_man#New_setup
Russian LinuxMCE community: http://linuxmce.ru

felpouse

  • Veteran
  • ***
  • Posts: 99
    • View Profile
Re: [Translation UI] Database connection and UFT8
« Reply #5 on: October 09, 2009, 11:42:56 am »
Hi Michael,

you did a really great job.

Could you please confirm me in which part of my.cnf this string are inserted ? Client, server or other part of the file configuration.

Best regards and congratulation

Luke

felpouse

  • Veteran
  • ***
  • Posts: 99
    • View Profile
Re: [Translation UI] Database connection and UFT8
« Reply #6 on: October 09, 2009, 12:07:23 pm »
Hi Michael,

sorry, but only now I've saw your updates on the page of the wiki related to the translation with all the modification to mysql server.

Best regards

Luke

nite_man

  • NEEDS to work for LinuxMCE
  • ***
  • Posts: 1019
  • Want to work with LinuxMCE
    • View Profile
    • Smart Home Blog
Re: [Translation UI] Database connection and UFT8
« Reply #7 on: October 09, 2009, 12:50:14 pm »
I updated code in my last post. Sure it should be added to the mysqld section. client section is used just for the mysql shell if I understand correctly.
Michael Stepanov,
My setup: http://wiki.linuxmce.org/index.php/User:Nite_man#New_setup
Russian LinuxMCE community: http://linuxmce.ru

felpouse

  • Veteran
  • ***
  • Posts: 99
    • View Profile
Re: [Translation UI] Database connection and UFT8
« Reply #8 on: October 09, 2009, 12:59:13 pm »
Great Michael,

I'll try to use it with the italian translation. I'll follow what happens.

Regards

Luke