4.2.7 International Install

Preface

In general to have a fully localized OSCAR you will want to have a properties file that supplies translated labels for the interface AND the ability to insert data, anywhere in the program, in the language of your choice.  This document, used in conjunction with the deb installation, will yield the second part, typing in a non English language.  The first part, the translated interface, is available for English French and Spanish at this time.  Donations of translated interface files are welcome.

Document Version History

• v1.0 – initial public release to oscarmanual.org – April 26, 2013
  
• v1.1 – added hibernate settings –  April 27, 2013
• v1.2 – added instructions for conversion of existing OSCAR data –  May 5, 2013
• v1.3 – revised for MySQL 5.5 –  Sept 11, 2013

Internationalization of MySQL 5.0 Server

Full multinational language support requires the MySQL database to be in UTF-8 format

Unfortunately Linux installs of MySQL prior to 5.5 default to MYISAM storage engine and latin1 encoding.

On the other hand IF you are running 5.0 you MUST change this.

nano /etc/mysql/my.cnf

Add or uncomment the following lines.  NOTE if you are running 5.5 you likely fine with the defaults, but if not sure DO NOT supply the 5.0 flags as they are deprecated and your server will not start.  If you want to supply flags for 5.5 use character-set-server=UTF-8 and collation-server=utf8_unicode_ci instead.

[mysqld_safe]
default-storage-engine=innodb
[mysqld]
## for 5.0 ONLY use
default-character-set=UTF-8
default-collation=utf8_unicode_ci

Now restart MySQL to ensure that those settings take before you start creating databases.

sudo service mysql stop
sudo service mysql start

Please proceed with the deb install at this point which will now install the database with the correct encoding before returning here to finish the configuration in the next steps.

MySQL Connector and Settings

Before logging in you need to change a few things so that OSCAR can support the UTF-8 encoding on its end

First change the oscar properties file

sudo nano /usr/share/tomcat6/Oscar12_1.properties

Either comment out the hibernate line (its for deprecated MySQL connectors 4 and earlier) or set it to org.hibernate.dialect.MySQL5Dialect

and put some flags on the db_name to set UTF-8 encoding and ensure any "illegal" 0000-00-00 dates are handled

#hibernate.dialect=org.hibernate.dialect.MySQLDialect
db_name=oscar_12_1?characterEncoding=UTF-8&zeroDateTimeBehavior=round

Download the latest mysql connector from http://dev.mysql.com/downloads/connector/j/.

wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.24.tar.gz/from/http://cdn.mysql.com/ connector

Now extract the connector

tar -xvf connector

Now overwrite the somewhat tired OSCAR supplied connector with this latest one.

sudo cp mysql-connector-java-5.1.24/mysql-connector-java-5.1.24-bin.jar /var/lib/tomcat6/webapps/Oscar12_1/WEB-INF/lib/mysql-connector-java-3.0.11.jar

Now restart tomcat to ensure that those settings take effect.

sudo service tomcat6 stop
sudo service tomcat6 start

Now, if this is a NEW INSTALL you can Enjoy typing in whatever language you want in OSCAR.  

OR

If you already have data in OSCAR (ie you ran the deb BEFORE the first step) you will also need to convert your database in the next step.

 Conversion of Existing Latin1 OSCAR to UTF8

If you already have data in OSCAR the above two steps will not be enough as your database is likely Latin1.

If you are converting a oscar_12 or oscar_12_1 database you must first run update-2013-05-04.sql which you can get from https://source.oscartools.org:8080/cat/6037%2C1%2Cdatabase/mysql/updates/update-2013-05-04.sql%5E0

When you extract the file from the downloaded zip run it substituting your values for username, password and dbname.

mysql -uusername -ppassword dbname < update-2013-05-04.sql

username
    The username to access your database.   This is usually root
password
    The MySQL password for the above user. 

    The name of the OSCAR database to convert. This is usually in the format oscar_version eg oscar_12_1

After this preliminary step one approach is to run ALTER TABLE xxx CONVERT TO CHARACTER SET for each of OSCAR's 400 odd tables, but as the tables vary by OSCAR version locale CAISI BC and ON install, there is no one list.

Instead it is probably safer and more reliable to convert existing data from Latin1 to UTF-8 it by dumping the data into a text file.  This will serve both as a backup and as a template to do the required changes.

mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql

    Complete inserts for better compatibility.
-e
    Extended inserts for better performance.
--default-character-set=utf8
    To set the default character set.
--single-transaction
    To reduce  workload if anything goes wrong.
--skip-set-charset
    Not wanted or needed as we are changing it anyway.
--add-drop-database
    Required so we can restore over the top of our existing database.
-B
    We use this option so that our dump will contain drop table and create table syntax (which we will change the syntax for).

Now since we have a backup of the schema, lets stream over an altered dump.sql into a dump-fixed.sql one where any latin1's get converted to UTF-8

sed 's/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/' <dump.sql | sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/' >dump-fixed.sql

 Finally reload the database with the UTF-8 encoded one

mysql -uusername -ppassword < dump-fixed.sql

Result

Below is shown OSCAR 12_1 with English labels and Chinese Simplified characters.