Adding method/property to DB object class to translate enums to various languages

[sneakyimp]

I've concocted a script that auto-generates super-convenient DB object classes which allow me to interact with my db tables without writing any SQL at all. They're not perfect, but I like them very much and they save me soooooo much time. Here's an example useage:

// assume $this->db represents my persistence mechanism

// fetches db record with id=42 from table my_user and instantiates an instance of DB_my_user
$record = DB_my_user::fetch_by_pkey($this->db, 42);

// the db object has a property for each db column. These are public and can be changed
$record->first_name = "Herbert";

// updating a db record is this easy
if (!$record->update_db_record()) {
  // db update did not work
  throw new Exception("There was a problem updating the database!");
}

What I would like to add to these helpful objects is the ability to translate any ENUM values in the db table to multiple languages as conveniently as possible. For example, a db table may have several enum columns. Let's say my user table has a db column status which describes my users:

CREATE TABLE my_user (
    // blah blah blah
    status ENUM('single', 'married', 'divorced', 'widowed', 'hopeless', 'fugly')
    // blah blah blah
);

I would like, as conveniently and elegantly as possible, be able to fetch a user object and refer to its status property while having that status translated into some particular language indicated by the session or by a url parameter. I imagine something like this:

echo $record->status; // outputs the original db value as defined just above. E.g., "divorced"

$record->set_lang("it"); // tells the retrieved object we'd like to translate to some language, in this case Italian
echo $record->translate->status; // outputs "divorziato"

I realize there are several challenges here:
1) Elegant code design using some other class or appropriate magic method such that my IDE code hints a "translate" property to my DB objects. Ideally this is defined in the shared base class common to my DB objects
2) Need scheme that insures good performance. Would like to avoid huge load operation of dozens of language files by implementing a lazy loading procedure. It would be preferable to cache language values on a web server rather than fetching them from a db server every time we need them.
3) Need to determine organization and format of language asset files that map a particular table/field/value/language combination onto the correct value that is as easy to understand and maintain as possible. If the language file organization is awkward, this could introduce quite a burden because we are likely to have a lot of these.
4) Need to decide on dependency injection versus service locator approach for locating/specifying the language translations. If I use the file system to organize my translations, the translation mechanism must at least require a pathname in order to locate the directory where these translations are stored. It would be ideal if we didn't have to toss around some directory location all the time, but using a service locator within my DB class seems wrong because it limits the reusability of that class.

I was imagining a language file organization scheme something like this directory structure in my file system somewhere:

table_name_one
  column_one
    en.php // contains english value-translation mappings (probably unnecessary)
    es.php // contains spanish value-translation mappings
    it.php // contains Italian mappings
  column_two.php // etc.
    en.php
    es.php
    it.php
table_name_two
...etc...  

or something like that. This structure would make it easy to change things if a table name changed. Each DB_* class has its corresponding table name defined in a constant so it would know which top-level folder to look in. Each second-level folder, corresponding to the column name, could be mapped using a magic method or something, and the language would tell you which file to require/include in order to perform the translation of the enum values. One of these files might look something like this:

// my_user/status/it.php
$map = array(
  'single' => 'scapolo',
  'married' =>'sposato',
  'divorced' => 'divorziato',
  'widowed' => 'vedova',
  'hopeless' =>'disperato',
  'fugly' => 'orribile'
);

Any thoughts or input would be greatly appreciated.

[NogDog]

If you put the translations in a DB table, you don't have to query them separately, just make them part of the select with a join on the translation table.

SELECT t1.enum_field, t2.translation
FROM table_one t1
INNER JOIN table_two t2 USING(enum_field) -- maybe a LEFT JOIN ?
WHERE <main table clause> AND t2.language = :language

[sneakyimp]

Thanks for your input!

NogDog;11045073 wrote:

If you put the translations in a DB table, you don't have to query them separately, just make them part of the select with a join on the translation table.

While this is most certainly true, I can think of a few reasons why I wouldn't necessarily want my translations in the db:
1) It inflicts greater load on the db when we expect our db to eventually become the primary bottleneck in our system.
2) This greater db server load is for values that change very rarely. I.e., once you provide translations for these enum values, they don't change at all until you change your db structure really.

Still, I do think your idea has merit but I'm also having a little trouble picturing how to join another table with still retaining the great time-saving advantages of my auto-generated database classes. I suspect it wouldn't be too hard to construct alternate fetch methods which take care to join the tables containing the language translations, possibly even in the baseclass.

On the other hand, building a join onto some language table in my record-fetching techniques introduces some dependencies that are not currently a feature of my auto-generated classes. Right now, they are pretty awesome stand-alone classes and I sort of like their aloof purity.

One my great desires in trying to build this is to get some really tidy, succinct syntax.

I've considered a couple of approaches to fetching properties from my class and having them translated. Syntax like this would be pretty awesome:

$obj = DB_my_table::fetch_by_pkey($db, 42); // fetch the object
$obj->set_lang("it"); // set translations to use Italian
echo $obj->some_property_name->translate();

However, I cannot seem to concoct any way for my db classes to intercept a reference to an existing public property with some kind of function to translate that property.

I have also considered this syntax:

echo $obj->translate->some_property_name;

This appears feasible if I define an appropriate translator class with get magic method attached to my base class:

// the base class
class baseclass {
	public $translate;
	public function __construct() {
		$this->translate = new translator($this);
	}
}

// the translator object class
class translator {
	private $parent_db_object;
	public function __construct($pobj) {
		$this->parent_db_object = $pobj;
	}
	public function __get($name) {
		echo "translator has been asked to translate the property \$$name\n";
		return $this->some_translation_function($name);
	}
	public function some_translation_function($property_name) {
		return "this is the string returned by some_translation_function which is responsible for translating " . $property_name . " whose value is " . $this->parent_db_object->$property_name . "\n";
	}
}
// a sample db object
class dbobj extends baseclass {
  public $prop1 = "val1";
  public $prop2 = "val2";

}

var_dump($obj->translate->prop1);

The output of that script is:

translator has been asked to translate the property $prop1
string(120) "this is the string returned by some_translation_function which is responsible for translating prop1 whose value is val1
"

This approach breaks down if I happen to create conflicting $translate property in my dbobj class. PHP complains about trying to convert the $translate object into a string:

Catchable fatal error: Object of class translator could not be converted to string

I'm also thinking it might be cool to create some kind of translation wrapper class where syntax might work like this:

$obj = DB_my_table::fetch_by_pkey($db, 42); // fetch the object
$xlator = new MyTranslator($obj, "it"); // feed the object to a translator object with a __get method that performs translation to the specified language
echo $xlator->some_property;  // where $some_property belongs to $obj and gets translated to italian by the MyTranslator class.

[sneakyimp]

I'm also wondering about what might be the best type of file for specifying language translations. These files must be creatable/editable by users, ideally without assistive tools. I'm concerned about performance (i.e., how fast PHP can parse/eval the files), about how easy they are to maintain, and about support of foreign language chars and punctuation like quotes and equals signs and stuff. For example, specifying associative arrays in a PHP file might be parsed super fast, but one must also type in the opening <?php tag, all those dollar sign, bracke, and quote marks, etc. An INI file might be a bit easier to maintain but you might have to quote all your strings and backslash any special chars in the data...and what about UTF-8 chars possibly breaking the ini parsing?

Some possible formats
JSON
INI
PHP
CSV

It occurs to me that PHP scripts can be automatically cached with [man]APC[/man]. It further occurs to me that my other post on a Memcache registry relates to this question.

[NogDog]

I guess I come from a different angle at things: I actually prefer to write all my SQL and have full control over it, as opposed to figuring out how to get some sort of abstraction layer to do what I need. 🙂 At my current job, the app I work on regularly (like several requests per second during peak hours) does a join between about half a dozen tables, one of which has close to a million records, one with over 8 million, and the others around half a million; yet it performs just fine (for our particular performance needs, anyway), so I tend to take with a grain of salt worries that adding another table join will have some noticeable negative impact on performance (assuming proper indexing, EXPLAINing, etc. 😉 ).

[sneakyimp]

NogDog;11045087 wrote:

I guess I come from a different angle at things: I actually prefer to write all my SQL and have full control over it, as opposed to figuring out how to get some sort of abstraction layer to do what I need. 🙂 At my current job, the app I work on regularly (like several requests per second during peak hours) does a join between about half a dozen tables, one of which has close to a million records, one with over 8 million, and the others around half a million; yet it performs just fine (for our particular performance needs, anyway), so I tend to take with a grain of salt worries that adding another table join will have some noticeable negative impact on performance (assuming proper indexing, EXPLAINing, etc. 😉 ).

I agree in some respects. A well-crafted JOIN can eliminate ugly queries-in-a-loop situation and dramatically improve performance. I didn't mean to suggest that an additional join would inherently introduce performance issues a priori.

What I did mean to suggest is that there is no real reason to query for these language strings. Once set, they really don't change at all so it seems more efficient to me to store them as constants/vars/config in a PHP file or parsed from an INI file or JSON file or something. Our architecture is moving toward a multi-machine arrangement anyway: multiple web servers and a single database server. We tend to allocate a lot of virtual servers in the cloud to handle temporary computing loads (in particular a huge asset pipeline for large numbers of images). Because our db is a clearing house for up to a dozen machines all trying to share data, it gets quite a workout. If I can shave off some db duties and allocate them to the web server (which is barely lifting its cyber finger) then I consider that a tiny win.

As for writing SQL, I do like to have a clear idea what is happening in my DB and am not a huge fan of enormously complicated abstraction layers, but it's my experience that the vast majority of SQL that I used to write was not so much tasty JOINs to accomplish some magic data performance feat but rather constructing tedious INSERT/UPDATE/DELETE queries to interact with a single table. My auto-generated classes eliminate the need for that almost entirely and this has greatly speeded up software development without introducing any significant inefficiencies or bloat in our database interactions. If I need a JOIN, I can still write one into a model class somewhere.

EDIT: While I'm aware that the query cache might really help for these small queries, I would point out that in a multi-machine architecture, the query cache lives on the db server and not the web server. We would still introduce network traffic and data transfer (and latencies, etc.) because each web server would be repeatedly asking the db server for these tiny bits of data that pretty much never change.

[NogDog]

As far as formats, I like JSON, with the caveat that it does not have a handy built-in commenting mechanism -- not a huge issue if you use nicely descriptive keys and structures, though -- and it's dirt-simple to read into PHP via json_decode() (or for that matter to create via json_encode()), and I find it easier for me to read than XML. I don't know the inner workings, but we use memcache on production to cache the result of parsing our JSON config files (of which we have many) in order to streamline that aspect so that it's not done for each and every request (just have to remember to restart Apache if/when we make a change to any of them).

[sneakyimp]

NogDog;11045091 wrote:

As far as formats, I like JSON, with the caveat that it does not have a handy built-in commenting mechanism -- not a huge issue if you use nicely descriptive keys and structures, though -- and it's dirt-simple to read into PHP via json_decode() (or for that matter to create via json_encode()), and I find it easier for me to read than XML.

Agreed wholeheartedly! json_encode in newer versions of php understands flags like JSON_PRETTY_PRINT. Good point about no ability to write comments. Not something I had considered necessary.

NogDog;11045091 wrote:

I don't know the inner workings, but we use memcache on production to cache the result of parsing our JSON config files (of which we have many) in order to streamline that aspect so that it's not done for each and every request (just have to remember to restart Apache if/when we make a change to any of them).

Restarting Apache seems a bit drastic to me. I'd be psyched to hear any more details about how your PHP code might use memcache. I'm increasingly thinking I need to know more about it.

I concocted a script to compare the loading performance of language files encoded as PHP, JSON, and INI files. I like the INI file format the best because it's so clean and easily edited by hand. Performance is good too. JSON is the fastest through 10,000 iterations though. The script:

<?php
// measure performance of INI parsing versus PHP language files

// we assume we'll have one language file for each db table

$iterations = 10000; // number of iterations to run for performance profiling
$enum_column_count = 5; // the number of enum columns per db table 
$enum_values_per_field = 5; // number of possible values per enum column
$enum_name_length = 10; // number of chars used for the name of enum columns
$enum_value_length = 15; // number of chars used for the values assigned to an enum
$enum_translation_length = 20; // assume translations are longer in length due to multibyte chars 

// TODO - these are the usual chars I use to define database column names. Maybe expand to include all valid char names?
$valid_key_chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_";

// TODO - expand this to include a vast array of utf-8 chars?
$valid_value_chars = " \t\n\r";
for($i=32; $i<128; $i++) {
	$valid_value_chars .= chr($i);
}

/**
 * Creates a random string of length $len by taking chars at random from the supplied string or chars
 * @param int $len Length of random string to be generated
 * @param string $valid_chars Acceptable chars for the string
 */
function get_rand_string($len, $valid_chars) {
	$vclast = mb_strlen($valid_chars) - 1; // index of last char
	$result = "";
	while (mb_strlen($result) < $len) {
		$rnd = mt_rand(0, $vclast);
		$result .= mb_substr($valid_chars, $rnd, 1);
	}
	return $result;
}

/**
 * Builds a random 2d associative array that roughly corresponds to what we'd need to translate
 * all the enums in a single db table in a single language
 * @return multitype:array
 */
function build_rand_assoc_array(){
	global $valid_key_chars;
	global $valid_value_chars;

global $enum_column_count;
global $enum_values_per_field;
global $enum_name_length;
global $enum_value_length;
global $enum_translation_length;

$result = array();
// each array will have $enum_column_count enum columns
for($k=0; $k<$enum_column_count; $k++){
	// create a random enum column name
	$enum_name = "enm_" . get_rand_string($enum_name_length, $valid_key_chars);

	$result[$enum_name] = array();

	// each enum will have $enum_values_per_field possible distinct values
	for($j=0; $j< $enum_values_per_field; $j++) {
		// each value is $enum_value_length chars
		$enum_value = "vl_" . get_rand_string($enum_value_length, $valid_key_chars);
		// each translation is $enum_translation_length chars
		$enum_translation = "trns_" . get_rand_string($enum_translation_length, $valid_key_chars);

		$result[$enum_name][$enum_value] = $enum_translation;
	}

}

return $result;
}

// given an array, creates a php file that will define it
function get_php($arr) {
	if (!is_array($arr)) {
		die("get_php did not receive an array");
	}
	$output = "<?php\n";
	$output .= "\$arr = array(\n";
	foreach($arr as $key => $subarr) {
		if (!is_array($subarr)){
			die("get_php's sub element is not an array");
		}
		$output .= "\t\"" . addslashes($key) . "\" => array(\n";
		foreach($subarr as $subkey => $subval) {
			$output .= "\t\t\"" . addslashes($subkey) . "\" => \"" . addslashes($subval) . "\",\n";
		}
		$output = rtrim($output, ",\n") . "\n";
		$output .= "\t),\n";
	}
	$output = rtrim($output, ",\n") . "\n";
	$output .= ");\n";

return $output;
}

function get_json($arr) {
	if (!is_array($arr)) {
		die("get_json did not receive an array");
	}
	$output = "{\n";
	foreach($arr as $key => $subarr) {
		if (!is_array($subarr)){
			die("get_json's sub element is not an array");
		}
		$output .= "\"" . addslashes($key) . "\":{\n";
		foreach($subarr as $subkey => $subval) {
			$output .= "\t\"" . addslashes($subkey) . "\" : \"" . addslashes($subval) . "\",\n";
		}
		$output = rtrim($output, ",\n") . "\n";
		$output .= "},\n";
	}
	$output = rtrim($output, ",\n") . "\n";
	$output .= "}\n";

return $output;

}

function get_ini($arr) {
	if (!is_array($arr)) {
		die("get_ini did not receive an array");
	}
	$output = "";
	foreach($arr as $key => $subarr) {
		if (!is_array($subarr)){
			die("get_ini's sub element is not an array");
		}
		$output .= "[" . addslashes($key) . "]\n";
		foreach($subarr as $subkey => $subval) {
			$output .= "" . addslashes($subkey) . " = \"" . addslashes($subval) . "\"\n";
		}
		$output .= "\n";
	}

return $output;
}

$dir = "/tmp/lang";

if (!is_writable($dir)) {
	echo "making $dir\n";
	mkdir($dir)
		or die("Could not make tmp folder");
}

$prof = array(
	"php" => 0,
	"json" => 0,
	"ini" => 0
);

function read_php($file, $arr_to_compare) {
	$start = microtime(TRUE);
	require $file; // this should define an array in $arr
	$end = microtime(TRUE);

if ($arr !== $arr_to_compare) {
	echo "php file $file does not define equal array!\n";
}

return $end - $start;
}

function read_json($file, $arr_to_compare) {
	$start = microtime(TRUE);
	$arr = json_decode(file_get_contents($file), TRUE); // this should define an array in $arr
	$end = microtime(TRUE);

if ($arr !== $arr_to_compare) {
	echo "json file $file does not define equal array!\n";
	var_dump($arr);
}

return $end - $start;
}

function read_ini($file, $arr_to_compare) {
	$start = microtime(TRUE);
	$arr = parse_ini_file($file, TRUE); // this should define an array in $arr
	$end = microtime(TRUE);

if ($arr !== $arr_to_compare) {
	echo "ini file $file does not define equal array!\n";
}

return $end - $start;
}

for($i=0; $i<$iterations; $i++){
	$arr = build_rand_assoc_array();

// create data files from it in various formats
$php = get_php($arr);
$php_file = $dir . "/" . $i . ".php";
file_put_contents($php_file, $php);

$json = get_json($arr);
$json_file = $dir . "/" . $i . ".json";
file_put_contents($json_file, $json);

$ini = get_ini($arr);
$ini_file = $dir . "/" . $i . ".ini";
file_put_contents($ini_file, $ini);

// measure the time required to parse the data file
// take care to profile each type
$prof["php"] += read_php($php_file, $arr);
$prof["json"] += read_json($json_file, $arr);
$prof["ini"] += read_ini($ini_file, $arr);
}
echo "done\n";
print_r($prof);
echo "\n";

NOTE: I wrote a custom json encoder because my workstation's old php doesn't support JSON_PRETTY_PRINT. This should not affect profiling as it's the read/loading performance only that I'm profiling. The result:

Array
(
    [code=php] => 0.58638691902161
    [json] => 0.37500500679016
    [ini] => 0.3792290687561
)

[NogDog]

I think we actually have a mechanism to clear the cache without having to restart Apache, but it's easier to remember how to just do "service httpd restart", and we (I) don't do it all that often on production (and no one really notices the maybe 1-second delay 😉 ). I believe the general process is that for a given sub-domain, which drives the "identity" we use to determine what config files get read, ends up putting all that identity-specific info into a registry class of sorts, cleverly called Identity. That class gets passed into all the controllers automatically, and presumably the framework itself looks to see if the identity object for that domain already exists in cache, and if not, creates it -- otherwise passing the existing identity object on to the controller.

And yes, in a few cases where particular clients support other languages, we often put the translations into those identity files -- though I find it gets rather bloated, but it works. :bemused:

[Derokorian]

NogDog;11045091 wrote:

As far as formats, I like JSON, with the caveat that it does not have a handy built-in commenting mechanism -- not a huge issue if you use nicely descriptive keys and structures, though -- and it's dirt-simple to read into PHP via json_decode()

sneakyimp;11045097 wrote:

Good point about no ability to write comments. Not something I had considered necessary.

I'm not sure why the json standard doesn't define a format for comments. I do like to put comments in my config files, and json is such a simple way to store config, and its pretty simple to strip comments, so I've create a simple json_parse() function that I use. Supports single line comments starting with # or // as well as block comments in the form of / ... /

function json_parse($json, $assoc = false, $depth = 512, $options = 0) {
    $json = preg_replace('@/\*.*?\*/@m', null, $json);
    $json = preg_replace('@\s*(//|#).*$@', null, $json);
    return json_decode($json, $assoc, $depth, $options);
}

[laserlight]

Derokorian wrote:

I'm not sure why the json standard doesn't define a format for comments.

Probably because a "lightweight data-interchange format" does not need a comment syntax: the parties could reasonably be presumed to know how to interpret the data to be exchanged, and if there is to be elaboration of some kind, that could be sent as part of the data itself.