bradymiller wrote on Monday, March 23, 2009:
hey,
I’m bumping a discussion that began in the help forum to here regarding foreign language support in OpenEMR to the developer forum:
https://sourceforge.net/forum/forum.php?thread_id=3119442&forum_id=202505
We currently store our translation stuff in 3 tables (I dumped these tables at bottom of this message to give all the details), which are:
lang_constants (Holds the english constants)
lang_definitions (Contains the foreign word with references by id numbers to other two tables)
lang_languages (simply contains foreign languages names)
The xl() function performs the translation by basically using the id number from chosen language, the id number of the word (lang_constant) and sees if any entries in the lang_definitions table)
In OpenEMR’s admin->language menu, users can add languages, constants, and edit translations.
We have recently cleared out the constants and definitions tables secondary to some garbled stuff and to duplciate entries (there were about 2800 constants and 900 or so definintions (swedish some spanish)). So it seem like out current methodology has failed.
The question is what do we do now?
Pimm has expressed interest in cleaning up the old tables to get the previous 2800 constants back into the database along with the thousand or so swedish translations along with perhaps some other languages. In the interest of not wasting his time (ie needing to clean out the database again in several years) figured we may need to come up with another solution. My database planning skill are weak at best and hoping some others had some ideas.
My proposal is to actually make the current three tables static, so users can not modify these. We can keep track of all constants (basically string within the xl("???") functions in our code) and add during each release to the lang_constants tables. We can also continue to add "official" translations to the lang_definitions tables and "official" languages to the lang_languages table. This mechanism would then allow a stable ever increasing support of other languages. To allow users to customize we can make two more additional tabless (one for custom constants and one for definitions) that are basically checked by the xl() function before checking the static tables.
The goal is to get something figured out and implemented during the next development cycle.
-Brady
–
– Table structure for table `lang_constants`
–
DROP TABLE IF EXISTS `lang_constants`;
CREATE TABLE `lang_constants` (
`cons_id` int(11) NOT NULL auto_increment,
`constant_name` varchar(255) character set utf8 collate utf8_unicode_ci default NULL,
UNIQUE KEY `cons_id` (`cons_id`),
KEY `cons_name` (`constant_name`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
–
– Dumping data for table `lang_constants`
–
LOCK TABLES `lang_constants` WRITE;
/*!40000 ALTER TABLE `lang_constants` DISABLE KEYS */;
INSERT INTO `lang_constants` VALUES (1,‘he’),(2,‘brain’),(3,‘users’);
/*!40000 ALTER TABLE `lang_constants` ENABLE KEYS */;
UNLOCK TABLES;
–
– Table structure for table `lang_definitions`
–
DROP TABLE IF EXISTS `lang_definitions`;
CREATE TABLE `lang_definitions` (
`def_id` int(11) NOT NULL auto_increment,
`cons_id` int(11) NOT NULL default ‘0’,
`lang_id` int(11) NOT NULL default ‘0’,
`definition` mediumtext character set utf8 collate utf8_unicode_ci,
UNIQUE KEY `def_id` (`def_id`),
KEY `definition` (`definition`(100))
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
–
– Dumping data for table `lang_definitions`
–
LOCK TABLES `lang_definitions` WRITE;
/*!40000 ALTER TABLE `lang_definitions` DISABLE KEYS */;
INSERT INTO `lang_definitions` VALUES (1,1,2,‘sweHe’),(2,1,3,‘SpaHe’),(3,1,4,‘GerHe’),(4,2,2,‘swebrain’),(5,2,3,‘SpaBrain’);
/*!40000 ALTER TABLE `lang_definitions` ENABLE KEYS */;
UNLOCK TABLES;
–
– Table structure for table `lang_languages`
–
DROP TABLE IF EXISTS `lang_languages`;
CREATE TABLE `lang_languages` (
`lang_id` int(11) NOT NULL auto_increment,
`lang_code` char(2) character set latin1 NOT NULL default ‘’,
`lang_description` varchar(100) character set utf8 collate utf8_unicode_ci default NULL,
UNIQUE KEY `lang_id` (`lang_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
–
– Dumping data for table `lang_languages`
–
LOCK TABLES `lang_languages` WRITE;
/*!40000 ALTER TABLE `lang_languages` DISABLE KEYS */;
INSERT INTO `lang_languages` VALUES (1,‘en’,‘English’),(2,‘se’,‘Swedish’),(3,‘es’,‘Spanish’),(4,‘de’,‘German’),(5,‘du’,‘Dutch’),(6,‘he’,‘Hebrew’);
/*!40000 ALTER TABLE `lang_languages` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
and a basic "ground rules", which already appears to be understood.