Hello. Just worked out for you another data base update which will made it without data duplication and also faster
So really on one table still you can have duplicated data, because of mysql unique limitations I didn't set it in wt_cache, but this table has less data than other updated tables.
Also collate was changed - believe it works faster when makes collation by binary values, that by some additional rules like in previous DB.
To have faster DB without data duplication You have to.
For new installations
Here is new DB:
Code:
CREATE TABLE wt_cache (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(65000),
translated TEXT,
INDEX(originaltext(323), tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_bin;
CREATE TABLE wt_cache_medium (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(255),
translated VARCHAR(1000),
UNIQUE (originaltext, tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_bin;
CREATE TABLE wt_cache_short (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(50),
translated VARCHAR(255),
UNIQUE (originaltext, tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_bin;
Also change code in translate.php
this one:
Code:
/* Check cache for translation */
if ($enablecache) {
$sql=null;
$length = strlen($text);
if ($length<=50) {
$sql = mysql_query("SELECT translated FROM wt_cache_short WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."' LIMIT 1");
} else if ($length > 255) {
$sql = mysql_query("SELECT translated FROM wt_cache WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."' LIMIT 1");
} else {
$sql = mysql_query("SELECT translated FROM wt_cache_medium WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."' LIMIT 1");
}
while($t = mysql_fetch_array($sql)) {
return $lsto.$t['translated'].$rsto;
}
}
/* -- if not found, proceed with Google Translate */
To this one:
Code:
/* Check cache for translation */
if ($enablecache) {
$sql=null;
$length = strlen($text);
if ($length<=50) {
$sql = mysql_query("SELECT translated FROM wt_cache_short WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."'") or die('Query to short cache failed: ' . mysql_error());
} else if ($length > 255) {
$sql = mysql_query("SELECT translated FROM wt_cache WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."' LIMIT 1") or die('Query to normal cache failed: ' . mysql_error());
} else {
$sql = mysql_query("SELECT translated FROM wt_cache_medium WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."'") or die('Query to medium cache failed: ' . mysql_error());
}
while($t = mysql_fetch_array($sql)) {
return $lsto.$t['translated'].$rsto;
}
}
/* -- if not found, proceed with Google Translate */
And change this one:
Code:
////////////////////////////////////////////////////
if ($enablecache) {
mysql_connect ($mysqlserver, $dbusername, $dbpassword);
mysql_select_db ($dbname);
mysql_set_charset('utf8');
}
////////////////////////////////////////////////////
To this one (less white screns):
Code:
////////////////////////////////////////////////////
if ($enablecache) {
establishConnection();
}
function establishConnection() {
global $dbusername, $dbpassword, $mysqlserver, $dbname;
mysql_connect ($mysqlserver, $dbusername, $dbpassword) or die('Could not connect: ' . mysql_error());
mysql_select_db ($dbname) or die('Could not select database');
mysql_set_charset('utf8');
}
////////////////////////////////////////////////////
And last change. This one:
Code:
/* Save to cache */
if ($enablecache && (strlen($ttext)>0)) {
$length = strlen($text);
if ($length<=50) {
mysql_query("INSERT INTO wt_cache_short SET tl='".addslashes($tl)."', originaltext='".addslashes($text)."', translated='".addslashes(trim($ttext))."'");
} else if ($length > 255) {
mysql_query("INSERT INTO wt_cache SET tl='".addslashes($tl)."', originaltext='".addslashes($text)."', translated='".addslashes(trim($ttext))."'");
} else {
mysql_query("INSERT INTO wt_cache_medium SET tl='".addslashes($tl)."', originaltext='".addslashes($text)."', translated='".addslashes(trim($ttext))."'");
}
}
/* -- */
To this one:
Code:
/* Save to cache */
if ($enablecache && (strlen($ttext)>0)) {
$length = strlen($text);
if (!mysql_ping()) {
mysql_close();
establishConnection();
}
if ($length<=50) {
mysql_query("INSERT IGNORE INTO wt_cache_short SET tl='".addslashes($tl)."', originaltext='".addslashes($text)."', translated='".addslashes(trim($ttext))."'") or die('Insert to short cache failed: ' . mysql_error());
} else if ($length > 255) {
mysql_query("INSERT INTO wt_cache SET tl='".addslashes($tl)."', originaltext='".addslashes($text)."', translated='".addslashes(trim($ttext))."'") or die('Insert to normal cache failed: ' . mysql_error());
} else {
mysql_query("INSERT IGNORE INTO wt_cache_medium SET tl='".addslashes($tl)."', originaltext='".addslashes($text)."', translated='".addslashes(trim($ttext))."'") or die('Insert to medium cache failed: ' . mysql_error());
}
}
/* -- */
So whole new translate.php is:
Code:
<?php
////////////////////////////////////////////////////
// Global Translator API
////////////////////////////////////////////////////
global $enablesession, $enablecache, $originalEncoding, $fl;
global $dbusername, $dbpassword, $mysqlserver, $dbname;
////////////////////////////////////////////////////
// SETTINGS
////////////////////////////////////////////////////
$enablesession = false; //ignore
$enablecache = true; //true - enable cache setting, false - disable cache setting
$originalEncoding = 'iso-8859-1'; //refer to your forum source code for your base encoding
$fl = 'en'; //current language (original) - refer table for language variables
$dbusername = "SET_IT";
$dbpassword = "SET_IT";
$mysqlserver = "localhost";
$dbname = "SET_IT";
////////////////////////////////////////////////////
if ($enablesession) {
@session_name("iPWTLang");
@session_start();
}
set_time_limit(0);
////////////////////////////////////////////////////
if ($enablecache) {
establishConnection();
}
function establishConnection() {
global $dbusername, $dbpassword, $mysqlserver, $dbname;
mysql_connect ($mysqlserver, $dbusername, $dbpassword) or die('Could not connect: ' . mysql_error());
mysql_select_db ($dbname) or die('Could not select database');
mysql_set_charset('utf8');
}
////////////////////////////////////////////////////
function api_strip ($dt) { $dt = str_replace("\\\\", '\\', $dt); return ($dt); }
function emodfix ($dt) { $dt = str_replace('\"', '"', $dt); return ($dt); }
function remove_tags ($html) {
/* Remove reserved triple tags in html */
return preg_replace ("|<<<([^><]*)>>>|e", "emodfix('\\1')", $html);
}
function add_tags ($buffer) {
/* Inject triple tags to html to preserve html content from translation using ob_start */
return preg_replace("/(^|>\/?)([^><]*)($|\/?<)/e",
"emodfix('\\1').'<<<'.emodfix('\\2').'>>>'.emodfix('\\3')",
$buffer);
}
function getServerLoad($windows=false)
{
//MY CODE TO DISABLE SERVER LOAD CHEcKING
return 1;
//END
$os = strtolower(PHP_OS);
if (strpos($os, "win") === false) {
if (file_exists("/proc/loadavg")) {
$data = file_get_contents("/proc/loadavg");
$load = explode(' ', $data);
return $load[0];
} elseif (function_exists("shell_exec")) {
$load = explode(' ', `uptime`);
return $load[count($load)-1];
} else {
return false;
}
} elseif($windows) {
if(class_exists("COM")) {
$wmi = new COM("WinMgmts:\\\\.");
$cpus = $wmi->InstancesOf("Win32_Processor");
$cpuload = 0;
$i = 0;
if(version_compare('4.50.0', PHP_VERSION) == 1) {
// PHP 4
while ($cpu = $cpus->Next()) {
$cpuload += $cpu->LoadPercentage;
$i++;
}
} else {
// PHP 5
foreach ( $cpus as $cpu ) {
$cpuload += $cpu->LoadPercentage;
$i++;
}
}
$cpuload = round($cpuload / $i, 2);
return "$cpuload%";
} else {
return false;
}
}
}
function translate($text, $fl, $tl){
if (trim($text) == null) return $text; //skip translation if string empty
/* Retain left and right spaces after translation */
$lsto = substr($text, 0, strlen($text) - strlen(ltrim($text)));
$rsto = substr($text, strlen(rtrim($text)), strlen($text) - strlen(rtrim($text)));
/* -- */
/* Declare global */
global $enablecache,$overloadval,$disinterval;
if(floatval(getServerLoad()) >= 1.5) // Numeral = server load on which to crash
{
// Write current time to file.
$fp = fopen("overloadcache.txt","w");
fwrite($fp, time() . "");
fclose($fp);
if($nfh= @fopen("overloadcache.txt","r"))
{
$overloadtime = fgets($nfh);
if($overloadtime > time()-30) // Numeral = # seconds after overload not to cache!
{
$enablecache = false;
}
fclose($nfh);
}
}
/* Check for server overloads (modification by DeViAnThans3)*/
global $originalEncoding;
if ($originalEncoding != 'utf-8') {
$text = iconv($originalEncoding, 'utf-8', trim($text));
} else {
$text = trim($text);
}
/* Check cache for translation */
if ($enablecache) {
$sql=null;
$length = strlen($text);
/*
if (!mysql_ping()) {
mysql_close();
establishConnection();
}
*/
if ($length<=50) {
$sql = mysql_query("SELECT translated FROM wt_cache_short WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."'") or die('Query to short cache failed: ' . mysql_error());
} else if ($length > 255) {
$sql = mysql_query("SELECT translated FROM wt_cache WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."' LIMIT 1") or die('Query to normal cache failed: ' . mysql_error());
} else {
$sql = mysql_query("SELECT translated FROM wt_cache_medium WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."'") or die('Query to medium cache failed: ' . mysql_error());
}
while($t = mysql_fetch_array($sql)) {
return $lsto.$t['translated'].$rsto;
}
}
/* -- if not found, proceed with Google Translate */
/* -Establish cURL connection to Google Translate API server- */
$ch = @curl_init();
//@curl_setopt($ch, CURLOPT_URL, "http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&langpair=".urlencode($fl.'|'.$tl)."&q=".urlencode($text));
@curl_setopt($ch, CURLOPT_URL, "http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&langpair=".urlencode($fl.'|'.$tl)."&q=".urlencode($text));
//$postParams = "v=1.0&langpair=".$fl.'|'.$tl."&q=".iconv('iso-8859-2', 'utf-8', $text);
//@curl_setopt($ch, CURLOPT_URL, "http://ajax.googleapis.com/ajax/services/language/translate");
//@curl_setopt ($Curl_Session, CURLOPT_POST, 1);
//@curl_setopt ($Curl_Session, CURLOPT_POSTFIELDS, $postParams);
@curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax)");
@curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
@curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
$in = @curl_exec ($ch);
/* -End connection- */
preg_match('/{"translatedText":"(.*?)"}/', $in, $out);
//$ttext = conv(api_strip($out[1]));
$ttext = str_replace ('\u0026', '&' , api_strip($out[1]));
//$ttext = api_strip($out[1]);
//$ttext = unicode_encode(api_strip($out[1]), 'utf-8');
/* Save to cache */
if ($enablecache && (strlen($ttext)>0)) {
$length = strlen($text);
if (!mysql_ping()) {
mysql_close();
establishConnection();
}
if ($length<=50) {
mysql_query("INSERT IGNORE INTO wt_cache_short SET tl='".addslashes($tl)."', originaltext='".addslashes($text)."', translated='".addslashes(trim($ttext))."'") or die('Insert to short cache failed: ' . mysql_error());
} else if ($length > 255) {
mysql_query("INSERT INTO wt_cache SET tl='".addslashes($tl)."', originaltext='".addslashes($text)."', translated='".addslashes(trim($ttext))."'") or die('Insert to normal cache failed: ' . mysql_error());
} else {
mysql_query("INSERT IGNORE INTO wt_cache_medium SET tl='".addslashes($tl)."', originaltext='".addslashes($text)."', translated='".addslashes(trim($ttext))."'") or die('Insert to medium cache failed: ' . mysql_error());
}
}
/* -- */
return $lsto.$ttext.$rsto;
}
function callback($buffer){
global $fl;
$modifyhref = false;
//-------------------------------------------------------------------------------
global $enablesession;
// Get language setting from queries:
$lang = stripslashes(@$_GET['hl']);
if ($enablesession) {
// Save setting to server session [1]
// If language setting not found in queries, check session data: [2]
if (checklang($lang)) $_SESSION['lang'] = $lang; else $lang = @$_SESSION['lang'];
}
if (!checklang($lang) || $lang == $fl) { // If invalid language setting or language = original language, skip translation.
$tp = $buffer;
$tp = preg_replace("/<disp_lang>/is", checklang($fl), $tp);
return remove_tags($tp);
} else {
// Extract BUFFER
$tp = preg_replace("/(^|>\/?)([^><]*)($|\/?<)/e",
"emodfix('\\1').translate(emodfix('\\2'),'$fl','$lang').emodfix('\\3')",
$buffer);
if ($modifyhref) {
$tp = preg_replace('/(<a [^><]*href\=")([^"]*)("[^><]*>)/e',
"emodfix('\\1').langconv('$lang',emodfix('\\2')).emodfix('\\3')",
$tp);
$tp = preg_replace('/(<form [^><]*action\=")([^"]*)("[^><]*>)/e',
"emodfix('\\1').langconv('$lang',emodfix('\\2')).emodfix('\\3'))",
$tp);
}
$tp = preg_replace("/<disp_lang>/is", checklang($lang), $tp);
$tp = remove_tags($tp);
return $tp;
}
//---end function
}
function langconv($hl, $href){
if ($href && substr($href, 0, 7) != 'http://' && substr($href, 0, 11) != 'javascript:' && strpos($href, "#") === false) {
if(strpos($href, "?") === false) return $href.'?hl='.urlencode($hl); else return $href.'&hl='.urlencode($hl);
} else { return $href; }
}
function checklang($hl){
$langvar = array ("sq"=>"Albanian","ar"=>"Arabic","bg"=>"Bulgarian","ca"=>"Catalan","zh-CN"=>"Chinese","hr"=>"Croatian","cs"=>"Czech","da"=>"Danish","nl"=>"Dutch","en"=>"English","et"=>"Estonian","tl"=>"Filipino","fi"=>"Finnish","fr"=>"French","gl"=>"Galician","de"=>"German","el"=>"Greek","iw"=>"Hebrew","hi"=>"Hindi","hu"=>"Hungarian","id"=>"Indonesian","it"=>"Italian","ja"=>"Japanese","ko"=>"Korean","lv"=>"Latvian","lt"=>"Lithuanian","mt"=>"Maltese","no"=>"Norwegian","pl"=>"Polish","pt"=>"Portuguese","ro"=>"Romanian","ru"=>"Russian","sr"=>"Serbian","sk"=>"Slovak","sl"=>"Slovenian","es"=>"Spanish","sv"=>"Swedish","zh-TW"=>"Taiwanese","th"=>"Thai","tr"=>"Turkish","uk"=>"Ukrainian","vi"=>"Vietnamese");
foreach ($langvar as $i => $val){ if ($i == $hl) return $val; }
return false;
}
function clearlangsetting(){
global $enablesession;
if ($enablesession) $_SESSION['lang'] = null; /* -Clear lang session data on server- */
}
?>
Hope Dave will include it in next release.
Now - for already installed products
Here we have 2 ways. Easy one, and good one.
In easy one - just drop all tables from DB, set it from new script and change translate.php like described.
In good one - you want to keep already cached data, and this will need some changes.
1. change translate.php like described
2. dissable cache - set
$enablecache=false; in translate.php
3. change collate by executing:
Code:
Alter table wt_cache_short collate utf8_bin;
ALTER TABLE wt_cache_short CHANGE tl tl VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE wt_cache_short CHANGE originaltext originaltext VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE wt_cache_short CHANGE translated translated VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
Alter table wt_cache_medium collate utf8_bin;
ALTER TABLE wt_cache_medium CHANGE tl tl VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE wt_cache_medium CHANGE originaltext originaltext VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE wt_cache_medium CHANGE translated translated VARCHAR( 1000 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
Alter table wt_cache collate utf8_bin;
ALTER TABLE wt_cache CHANGE tl tl VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE wt_cache CHANGE translated translated TEXT CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE wt_cache CHANGE translated translated VARCHAR( 65000 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
4. Delete data duplication like described here (note this is very important - otherwise unique indexes will not be set):
https://vborg.vbsupport.ru/showpost....&postcount=282
Note that this step if time consuming and it is a chance that you will need to connect to DB with other client that WWW (just check it first). Remember - always you can use the easy way
5. Optimize tables:
Code:
OPTIMIZE TABLE wt_cache, wt_cache_medium, wt_cache_short;
6. Reindex to unique:
Code:
alter table wt_cache_short drop index originaltext;
create UNIQUE INDEX originaltext on wt_cache_short (originaltext, tl);
alter table wt_cache_medium drop index originaltext;
create UNIQUE INDEX originaltext on wt_cache_medium (originaltext, tl);
7. enable cache - set
$enablecache=true; in translate.php
And that is. As I wrote data duplication will disappear for ever from wt_cache_short and wt_cache_medium. Also it will be save in case of hazard described in my 2 previous posts. Also according to my automatic tests - page generation works faster.
So - enjoy
More Information