Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
how to create a multilingual database table
Hello txp devs,
I am trying to create a database table that supports multiple languages when the MLP is installed. I have not found much information about ho to do this. After diving into the code of the MLP i have found a method that seems to work. But now, i am unsure if it is the right or at least a good way to achieve this.
I made a small test-plugin (based on code i found in smd_macro) that shows the soloution i found.
An installable version is here: abl_dbtest
The plugin requires the MLP.
The code is short, so i post it here too:
<?php
if (@txpinterface == 'admin') {
$plugin_name = 'abl_dbtest';
$adm_event = $plugin_name;
$adm_tab = 'DB Test';
// add tab under 'extensions'
add_privs($adm_event, '1,2');
register_tab('extensions', $adm_event, $adm_tab);
register_callback($plugin_name . '_dispatcher', $adm_event);
// plugin prefs
add_privs('plugin_lifecycle.' . $plugin_name, '1,2');
register_callback($plugin_name . '_lifecycle', 'plugin_lifecycle.' . $plugin_name);
}
if (!defined('ABL_DBTEST')) define('ABL_DBTEST', 'abl_dbtest');
function abl_dbtest_dispatcher($event, $step) {
if (!$step or
!in_array($step, array(
'abl_dbtest_table_install',
'abl_dbtest_table_remove',
))) {
abl_dbtest('');
} else $step();
}
function abl_dbtest_lifecycle($event, $step) {
$msg = '';
switch ($step) {
case 'installed':
abl_dbtest_table_install(0);
$msg = 'Done!';
break;
case 'deleted':
abl_dbtest_table_remove(0);
break;
}
return $msg;
}
function abl_dbtest($msg='') {
global $prefs;
pagetop('DB Test', $msg);
echo '<pre>' .n;
$res = safe_field('description', ABL_DBTEST, 'ID = 1');
var_dump($res) .n;
echo n;
$field = _l10n_rewrite_sql('description', $prefs['language'], 'description');
var_dump($field) .n;
$res = safe_field($field, ABL_DBTEST, 'ID = 1');
var_dump($res) .n;
echo n;
$sql = 'SELECT title, description FROM ' . safe_pfx(ABL_DBTEST) . ' WHERE ID = 1;';
$sql = _l10n_rewrite_sql('title', $prefs['language'], $sql);
$sql = _l10n_rewrite_sql('description', $prefs['language'], $sql);
$sql = trim($sql);
var_dump($sql) .n;
$res = safe_query($sql);
var_dump($res) .n;
while ($line = mysql_fetch_array($res, MYSQL_ASSOC)) {
var_dump($line) .n;
}
echo n;
echo 'title (de-de): ' . _l10n_make_field_name('title', 'de-de') .n;
echo 'title (en-gb): ' . _l10n_make_field_name('title', 'en-gb') .n;
echo n;
$sql = "`title` = 'test', `description` = 'This is a test.', " .n;
$sql .= '`' . _l10n_make_field_name('title', 'de-de') . "` = 'Test', " .n .
_l10n_make_field_name('description', 'de-de') . " = 'Das ist Test.', " .n;
$sql .= '`' . _l10n_make_field_name('title', 'en-gb') . "` = 'test', " .n .
_l10n_make_field_name('description', 'en-gb') . " = 'This is a test.'" .n;
$res = safe_insert(ABL_DBTEST, $sql);
echo 'insert result: ';
var_dump($res) .n;
echo n;
echo '</pre>' .n;
}
function abl_dbtest_table_install($showpane='1') {
$GLOBALS['txp_err_count'] = 0;
$l10n = false;
$l10n_languages = array();
$mapped_fields = '';
if (is_callable('l10n_installed')) {
$l10n = l10n_installed( true );
if ($l10n) {
@require_plugin('l10n');
// get instralled languages as array, e.g. array('de-de', 'en-gb');
$l10n_languages = MLPLanguageHandler::get_site_langs();
$map_fields = array(
'title' => "varchar(255) NULL DEFAULT ''",
'description' => "varchar(255) NULL DEFAULT ''",
);
foreach ($l10n_languages as $lang) {
foreach ($map_fields as $f => $sqldef) {
$mapped_fields .= '`' . _l10n_make_field_name($f, $lang) . '` ' .
$sqldef . ',' . n;
}
}
}
}
$ret = '';
$sql = array();
$sql[] = "CREATE TABLE IF NOT EXISTS `".safe_pfx(ABL_DBTEST)."` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NULL default '',
`description` varchar(255) NULL default ''," . n .
$mapped_fields .
"PRIMARY KEY (`ID`)
) ENGINE=MyISAM";
if(gps('debug')) {
dmp($sql);
}
foreach ($sql as $qry) {
$ret = safe_query($qry);
if ($ret===false) {
$GLOBALS['txp_err_count']++;
echo "<b>".$GLOBALS['txp_err_count'].".</b> ".mysql_error()."<br />\n";
}
echo "<!--\n $qry \n-->\n";
}
// Spit out results
if ($GLOBALS['txp_err_count'] == 0) {
if ($showpane) {
$msg = 'Table ' . safe_pfx(ABL_DBTEST) . ' installed.';
abl_dbtest($msg);
}
} else {
if ($showpane) {
$msg = 'Error! Table ' . safe_pfx(ABL_DBTEST) . ' not installed.';
abl_dbtest($msg);
}
}
}
function abl_dbtest_table_remove() {
$ret = '';
$sql = array();
$GLOBALS['txp_err_count'] = 0;
if (abl_dbtest_table_exist()) {
$sql[] = "DROP TABLE IF EXISTS " . safe_pfx(ABL_DBTEST) . "; ";
if(gps('debug')) {
dmp($sql);
}
foreach ($sql as $qry) {
$ret = safe_query($qry);
if ($ret===false) {
$GLOBALS['txp_err_count']++;
echo "<b>".$GLOBALS['txp_err_count'].".</b> ".mysql_error()."<br />\n";
echo "<!--\n $qry \n-->\n";
}
}
}
if ($GLOBALS['txp_err_count'] == 0) {
$msg = 'Table ' . safe_pfx(ABL_DBTEST) . ' removed.';
} else {
$msg = 'Error! Table ' . safe_pfx(ABL_DBTEST) . ' not removed.';
abl_dbtest($msg);
}
}
function abl_dbtest_table_exist($all='') {
if ($all) {
$tbls = array(ABL_DBTEST => 3);
$out = count($tbls);
foreach ($tbls as $tbl => $cols) {
if (gps('debug')) {
echo "++ TABLE ".$tbl." HAS ".count(@safe_show('columns', $tbl)).
" COLUMNS; REQUIRES ".$cols." ++".br;
}
if (count(@safe_show('columns', $tbl)) == $cols) {
$out--;
}
}
return ($out===0) ? 1 : 0;
} else {
if (gps('debug')) {
echo "++ TABLE ".ABL_DBTEST." HAS ".count(@safe_show('columns', ABL_DBTEST)).
" COLUMNS;";
}
return(@safe_show('columns', ABL_DBTEST));
}
}
?>Offline