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
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
Comments (0)
Post a Comment