Create a function that changes an existing BASE database to use UTF-8
|Reported by:||Nicklas Nordborg||Owned by:||Nicklas Nordborg|
See #1168 for background info.
The script provided with the BASE installation creates a database that uses UTF-8, but the examples in the documentation doesn't do this. Since this will probably create the database with 'latin1' encoding instead we need to have a function that can convert an existing database to use UTF-8 instead.
In theory it is as simply as executing the SQL below for each table:
alter table <table-name> convert to character set utf8, default character set utf8;
and for the database:
alter database <db-name> default character set utf8;
We need to build a function in the installation/update program that finds all tables and generates a script file that changes the character set. If possible, only tables that has one or more string columns need the 'convert to character set utf8' part, but all tables must be changed to 'default character set utf8'.
For safety reasons I think the function should output a SQL script file that an admin executes manually on the database. There may be exotic server configurations that needs modifications to the generated script. Optionally, we may provide a flag to indicate that the script should be executed by the function.
PostgreSQL uses UTF-8 as the default character set so in most cases this should not be a problem. In PostgreSQL the character set is a database-wide setting that can't be changed later, so if there are PostgreSQL users with a BASE database that uses a different character set this will be a big problem for them. The only thing I can think of is to dump the entire database to some kind of SQL script for creating a new database that uses UTF-8 instead. Hopefully this can be done with the pg_dump tool as described here: http://www.postgresql.org/docs/8.1/static/backup.html