Hi Guys,
I'm just wondering is there is a good Zend Framework-based methodology
for storing multi-lingual content in a database?
I know that there are several structures for doing this kind of thing,
including tables of strings that include language identifiers and such
like, but these can often be complex and hard to model.
What I've done in the past is create some custom UDF functions in MySQL
to pack multi-lingual content into a single field.
I would do something like:
INSERT INTO table SET field=LANG_PACK('en', 'Hello', 'fr', 'Bonjour');
I could then do:
SELECT LANG_EXTRACT(field, 'fr', 'en') FROM table;
which would give me back 'Bonjour'; or
SELECT LANG_EXTRACT(field, 'de', 'en', 'fr') FROM table;
which would give me back 'Hello' as no 'de' translation was found.
In the past, I've generally integrated the call to LANG_EXTRACT into an
API call in my db layer so when extracting the language for the current
locale the app is running in is trivial - so in ZF parlance something
similar to this:
$db->select()->from('table', $db->langField('field'));
(where $db->langField() would returns a Zend_Db_Expr).
This has the advantage that only the correct language comes out of the
database layer, and there is no logic required in the application.
As the MySQL functions are written in C and the packed language format
includes a header which contains exact byte locations of the substring
we want and thus can return the data quickly. It also handles "falling
back" to 2nd or thrid choice of language if the data is not present in
the preferred language.
Personally I really like the approach of dumping this logic into the db
layer. It keeps the application logic quite simple. Some other MySQL
functions allow for full extraction of the data in XML (LANG_XML())
format and thus allow for the creation of an editing interface that
shows all languages (e.g. as tabs). Other functions include
LANG_UPDATE() and LANG_PACK_XML() to perform what their name suggests.
Now all of this is very non-standard. It works for me and I find it
quite efficient and effective for my needs... there are however several
disadvantages:
1. Indexing: The db layer cannot index this content particurly well,
and comparing the result of a function is not that efficient as it needs
a full table scan.
2. It requires custom functions to be compiled and installed on the db
server. While this is quite simple, it's obviously an sysadmin overhead.
So my basic question is, is there a standard way to do this kind of
thing in ZF?
Perhaps there is a construct in the ZF db layer (or such a construct
could be create) to store the multilingual data in XML and extract out
the specific string from the XML semi-transparently?
This is kind of the opposite to Zend_Db_Expr where you define something
that should be passed to the DB directly. I'd propose a Zend_Db_Filter
which would have a way to know what expression to select ($f->getExpr())
and know that before the data is returned to the caller, it should be
filtered ($fiels = $f->filter($field)).
(This kind of filter could be useful for other things too, e.g. for
creating an image object from raw image data etc.)
Going back to my above example:
$db->select()->from('table', $db->langField('field'));
$db->langField() would return a Zend_Db_Filter object and the field
itself would just store e.g. XML.
I don't want to ramble on here and go off on too many tangents, so I'm
just really canvassing ideas/opinions as to how this kind of thing could
be done and what improvements can be made to ZF to support this.
Thoughts?
Col
--
Colin Guthrie
gmane(at)colin.guthr.ie
http://colin.guthr.ie/
Day Job:
Tribalogic Limited [http://www.tribalogic.net/]
Open Source:
Mandriva Linux Contributor [http://www.mandriva.com/]
PulseAudio Hacker [http://www.pulseaudio.org/]
Trac Hacker [http://trac.edgewall.org/]
