programming panda

My co-worker asked me how big is the mysql DB, I don't really know how so I did a little research and found this...

SELECT table_schema "Database", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ; 

And it works =D
It basically just go to the information_schema and sum up the size of the tables of each schema.

The output looks something like this

+--------------------+----------------------+
| Database           | Data Base Size in MB |
+--------------------+----------------------+
| dms                |           4.51943684 | 
| hrform             |           0.06588364 | 
| information_schema |           0.00390625 | 
| mysql              |           0.42193222 | 
| ptrng              |           0.05598068 | 
| securewiki         |           1.52050400 | 
| wiki               |         584.47095490 | 
+--------------------+----------------------+

I am working on a project which I have to write a help module and it has to be l10n (coz we have Japanese and Chinese customers). And because I want the content to be editable TTW, everything has to be in the DB. Another requirement is that if the content hasn't been translated, the default content (in English will be displayed).

The following is the DB schema (mysql dialect)

CREATE TABLE `help_manager`.`helps` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`topic` varchar(512) NOT NULL,
`content` varchar(16384) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `help_manager`.`helps_l10n` (
`id` INT NOT NULL ,
`locale_id` INT NOT NULL ,
`topic` VARCHAR(512) NOT NULL ,
`content` VARCHAR(16384) NULL ,
PRIMARY KEY (`id`, `locale_id`) ,
INDEX `helps_fk` (`id` ASC) ,
INDEX `locale_fk` (`locale_id` ASC) ,
CONSTRAINT `helps_fk`
FOREIGN KEY (`id` )
REFERENCES `help_manager`.`helps` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `locale_fk`
FOREIGN KEY (`locale_id` )
REFERENCES `help_manager`.`locale` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

CREATE TABLE IF NOT EXISTS `help_manager`.`locale` (
`id` INT NOT NULL AUTO_INCREMENT ,
`locale` VARCHAR(8) NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB

So here is how you select contents from the table with a specific locale

SELECT COALESCE (hl.topic,h.topic) , COALESCE(hl.content, h.content) FROM helps h LEFT JOIN (helps_l10n hl, `locale` l) ON (h.id=hl.id AND l.id=hl.locale_id) AND l.locale = 'ja_JP'

Side note
mysql UTF8 encoding:
utf8_bin: compare strings by the binary value of each character in the string

utf8_general_ci: compare strings using general language rules and using case-insensitive comparisons

utf8_general_cs: compare strings using general language rules and using case-sensitive comparisons

for example:
Ä == A => True
Ü == U => True