<?php
ob_start();
//start the motor
$pdo = new PDO("sqlite:termsDatabase.sqlite");
setup(); //comment out this line when you're sure that the database is properly populated
/** helper functions **/
define("BASE_LANGUAGE", 'english'); //change if English is not the base language. NB must be lower case
/**
* function to create a new term. typically you will not need to call this directly
*
* @param $term
* @param $recID
* @param $definition
* @param $context
* @param $annotation
* @return id of newly created or existing term
*/
function addTerm($term, $recID=NULL, $definition='', $context='', $annotation=''){
$term = new term;
$term->loadfromterm($term);
if($term->getPrimary()):
return $term->getPrimary();
endif;
$term->term = $term;
$term->save();
addTranslation($term, BASE_LANGUAGE, $term);
return $term->getPrimary();
}
/**
* function to add a new translation of a term.
*
* @param $term - either the term in the base language OR the termID
* @param $language - either the language in plain text or the languageID of the TARGET language
* @param $translation - the translated term
* @return the id of the latest entry, or false if there was an error
*/
function addTranslation($term, $language, $translation){
if(!is_numeric($term)):
$termID = addTerm($term);
else:
//double check that the termID is valid
$term = new term;
$term->loadfromID($term);
if(empty($term->term)):
die('invalid termID');
endif;
endif;
return $term->addTranslation($language, $translation);
}
/**
* function to translate a given term
* @param $term - the term to translate (the termID or the term in the base language)
* @param $language - the language to translate TO (either the languageID or the language)
* @param $bypass - set to true to bypass the ID/text checks and pull the data straight from the translations table
* @return translation string or false if no translation was found.
*/
function translate($term, $language, $bypass = FALSE){
if($bypass):
global $pdo;
$sql = <<<SQL
SELECT translation
FROM translations
WHERE termID = ?
AND languageID = ?
SQL;
$s = $pdo->prepare($sql);
$s->execute(array($term, $language));
$row = $s->fetchObject();
return $row->translation;
endif;
$term = new term;
$term->loadfromID($term);
if(empty($term->term)):
die('invalid termID');
endif;
$translation = new translation;
return $translation->get($term, $language);
}
/**
* function to import a csv into the database
* ensure it is in proper csv format and the top row has the column names in it
*/
function import($fileName){
$fH = fopen($fileName, 'r') or die('cannot open file for processing');
//get headers
$headers = fgetcsv($fh);
$mainFields = array('recID', 'definition','context','annotation');
while(FALSE!== ($row = fgetcsv($fh))):
//first create the term
$language = array();
$termID = $recID = $definition = $context = $annotation = NULL;
foreach($headers as $colNum=>$field):
if(in_array(strtolower($field), $mainFields)):
$$field = $row[$colNum];
elseif(strtolower($field) == BASE_LANGUAGE):
$term = $row[$colNum];
else:
$language[$field] = $row[$colNum];
endif;
endforeach;
$termID = addTerm($term, $recID, $definition, $context, $annotation);
foreach($language as $key=>$val):
$result = addTranslation($termID, $key, $val);
if(!$result):
logError($termID, $key, $val);
endif;
endforeach;
endwhile;
fclose($fH);
}
/**
* small helper function to log errors to a file for later inspection
* @param $termID
* @param $key
* @param $val
* @return unknown_type
*/
function logError($termID, $key, $val){
file_put_contents('logfile.txt', "$termID\t$key\t$val\n", FILE_APPEND);
}
/**
* this creates the database tables
* @return unknown_type
*/
function setup(){
global $pdo;
$sql = array(
<<<SQL
PRAGMA encoding = "UTF-16le";
SQL
,
<<<SQL
CREATE TABLE IF NOT EXISTS languages
(
languageID INTEGER PRIMARY KEY AUTOINCREMENT,
language TEXT NOT NULL UNIQUE
)
SQL
,
<<<SQL
CREATE TABLE IF NOT EXISTS terms
(
termID INTEGER PRIMARY KEY AUTOINCREMENT,
term TEXT NOT NULL UNIQUE,
recID INT UNIQUE,
definition TEXT,
context TEXT,
annotation TEXT
)
SQL
,
<<<SQL
CREATE TABLE IF NOT EXISTS translations
(
termID INT ,
languageID INT,
translation TEXT
)
SQL
,
<<<SQL
CREATE UNIQUE INDEX IF NOT EXISTS tx ON translations
(
termID,
languageID
)
SQL
); //end array
foreach($sql as $query):
$r = $pdo->exec($query);
if($r === false):
print_r($pdo->errorInfo());
endif;
endforeach;
}
/**
* function to export the sqlite file to a flat file
* @param $format - either xls, csv, table, xml or sqlite
* @return unknown_type
*/
function export($format){
//get languages
global $pdo;
$s = $pdo->prepare("Select * from languages");
$s->execute();
$languages = array();
$_languages = array();
while($row = $s->fetchAssoc()):
if(strtolower($row['language']) == BASE_LANGUAGE):
array_unshift($languages, $row);
array_unshift($_languages, $row['language']);
else:
$languages[] = $row;
$_languages[] = $row['language'];
endif;
endwhile;
//now get all the terms
$s = $pdo->prepare("Select * from terms");
$s->execute();
$data = array();
while($row = $s->fetchObject()):
$term = array(
$row->termID,
$row->recID,
$row->term
);
foreach($languages as $language):
$tx = translate($row->termID, $language['languageID'], true);
array_push($term, $tx === false ? '' : $tx);
endforeach;
foreach(array('definition','context','annotation') as $item):
array_push($term, $row->$item);
endforeach;
$data[] = $term;
endwhile;
$header = array_merge(array(
'Term ID',
'Record ID',
'Term'),
$_languages,
array(
'definition',
'context',
'annotation'
));
ob_end_clean();
switch ($format):
case 'xls':
case 'excel':
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$rowNumber = 0;
// Creating a worksheet
$worksheet =& $workbook->addWorksheet("Translation Export");
$worksheet->setInputEncoding('UTF-16le');
foreach($header as $key=>$field ):
$worksheet->write($rowNumber, $key, $field );
endforeach;
foreach($data as $row):
$rowNumber++;
foreach($row as $key=>$val):
$worksheet->write($rowNumber, $key, $value);
endforeach;
endforeach;
$workbook->send('Terms Export.xls');
$workbook->close();
exit;
break;
case 'csv':
//nb may need a BOM here
$fH = new fopen('temp.txt','wb');
fwrite($fH,"\xFE\xFE"); //BOM
fputcsv($fH, $header);
foreach($data as $line):
fputcsv($fH, $line);
endforeach;
fclose($fH);
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=file.csv');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
readfile('temp.txt');
unlink('temp.txt');
exit();
break;
case 'table':
header('Content-Type: text/html; charset=UTF-16');
echo "\xFE\xFE";
echo <<<HTML
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-type" content="text/html;charset=UTF-16">
<meta charset="UTF-16">
<title>Terms</title>
</head>
<body>
HTML;
echo '<table>';
echo '<thead><tr>';
echo '<th>'. implode('</th><th>', $header) . '</th>';
echo '</tr></thead>';
echo '<tbody>';
foreach($data as $line):
echo '<tr><td>' . implode('</td><td>', array_map('htmlspecialchars', $line)) . '</td></td>';
endforeach;
echo '</tbody></table></body></html>';
exit;
case 'xml':
$fH = new fopen('temp.txt','wb');
fwrite($fH, '<?xml version="1.0" encoding="UTF-16le"?>');
fwrite($fH, "\n<terms>\n");
foreach($data as $row):
fwrite($fH, "\t" . '<term>'."\n");
foreach($row as $key=>$val):
fwrite($fH, "\t\t".'<' . $header[$key] . '>' . htmlspecialchars($val) . '</' . $header[$key] . '>' . "\n");
endforeach;
fwrite($fH, "\t" . '<\term>'."\n");
endforeach;
fwrite($fH, '<\terms>');
fclose($fH);
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=termsDatabase.xml');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
readfile('temp.txt');
unlink('temp.txt');
exit();
break;
case 'sqlite':
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=termsDatabase.sqlite');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
readfile('termsDatabase.sqlite');
exit;
break;
endswitch;
}
class base{
public function __construct(){
foreach($this->fields as $field=>$val):
$this->$field = $val;
endforeach;
}
public function save(){
$primary = $this->getPrimary();
if(!empty($primary)):
return $this->update();
else:
return $this->saveNew();
endif;
}
public function saveNew(){
global $pdo;
$sql = "insert into {$this->table} ";
$fields = $placeholders = $values = array();
foreach($this->fields as $field=>$default){
$fields[] = $field;
$placeholders[] = '?';
$values[] = $this->$field;
}
$sql .= '(' . implode(',',$fields).') VALUES (' . implode(',', $placeholders) . ')';
try{
$s = $pdo->prepare($sql);
$s->execute($values);
$id = $pdo->lastInsertId();
$this->setPrimary($id);
return true;
} catch (PDOException $e){
print_r($e);
return false;
}
}
public function update(){
global $pdo;
$sql = "update {$this->table} set ";
$fields = $placeholders = $values = array();
foreach($this->fields as $field=>$default):
$fields[] = "$field = ?";
$values[] = $this->$field;
endforeach;
$sql .= implode(',',$fields) . " where {$this->primary} = ?";
$values[] = $this->getPrimary();
try{
$s = $pdo->prepare($sql);
$s->execute($values);
return true;
} catch (PDOException $e){
print_r($e);
return false;
}
}
public function load($data){
if(is_object($data)) $data =get_object_vars($data);
foreach($this->fields as $field=>$val):
if(isset($data[$field])):
$this->$field = $data[$field];
endif;
endforeach;
}
public function loadFromPrimary($id){
global $pdo;
$sql= "Select * from {$this->table} where {$this->primary}=?";
try{
$s = $pdo->prepare($sql);
$s->execute(array($id));
$row = $s->fetchObject();
if($row) $this->load($row);
} catch (PDOException $e){
print_r($e);
}
}
public function loadFromID($id){
return $this->loadFromPrimary($id);
}
public function loadByID($id){
return $this->loadFromPrimary($id);
}
public function getPrimary(){
if(isset($this->{$this->primary})):
return $this->{$this->primary};
else:
return null;
endif;
}
public function setPrimary($val){
$this->{$this->primary} = $val;
}
public function delete(){
global $pdo;
$sql = "delete from {$this->table} where {$this->primary}=?";
$params = array($this->getPrimary());
try{
$s = $pdo->prepare($sql);
$s->execute($params);
} catch (PDOException $e) {
print_r($e);
}
}
public function bail($messages){
echo '<pre>';
print_r($messages);
echo '</pre>';
die;
}
}
class language extends base {
public $fields = array('languageID','language');
public $primary = 'languageID';
public $table = 'languages';
public function __construct(){
parent::__construct();
}
public function save(){
$this->language = ucwords(strtolower($this->language));
return parent::save();
}
public function loadFromLanguage($language){
global $pdo;
$sql = <<<SQL
SELECT *
FROM {$this->table}
WHERE language LIKE ?
SQL;
$s = $pdo->prepare($sql);
if($s === false) $this->bail($pdo->errorInfo());
$r = $s->execute (array(trim($language)));
if($r === false) $this->bail($s->errorInfo());
$this->load ($s->fetch(PDO::PDO_FETCH_ASSOC));
}
}
class term extends base {
public $fields = array('termID', 'term', 'recID','context','definition','annotation');
public $primary = 'termID';
public $table = 'terms';
public function __construct(){
parent::__construct();
}
public function addTranslation( $language, $translation){
$language = new language;
if(is_numeric($language)):
$language->loadFromID($language);
if(empty($language->language)):
$language->language = $language;
$language->save();
$languageID = $language->getPrimary();
else:
$languageID = $language;
endif;
else:
$language = new language;
$language->loadFromLanguage($language);
if($language->getPrimary()):
$languageID = $language->getPrimary();
else:
$language->language = $language;
$language->save();
$languageID = $language->getPrimary();
endif;
endif;
$translation = new translation;
return $translation->add($this->getPrimary(), $languageID, $translation);
}
public function translate ($term, $language){
if(is_numeric($term)):
$this->loadFromID($term);
else:
$this->loadFromTerm($term);
endif;
$translation = new translation;
return $translation->get($this->getPrimary(), $language);
}
public function loadFromTerm($term){
global $pdo;
$sql = <<<SQL
SELECT *
FROM {$this->table}
WHERE term LIKE ?
SQL;
$s = $pdo->prepare($sql);
if($s === false) $this->bail($pdo->errorInfo());
$r = $s->execute (array(trim($term)));
if($r === false) $this->bail($s->errorInfo());
$this->load ($s->fetch(PDO::PDO_FETCH_ASSOC));
}
}
class translation extends base {
public $fields = array('termID', 'languageID', 'translation');
public $table = 'translations';
public function __construct(){
parent::__construct();
}
public function save(){
$sql = <<<SQL
REPLACE INTO {$this->table}
(termID, langaugeID, translation)
VALUES
(?,?,?)
WHERE termID = ?
AND languageID = ?
SQL;
$params = array($this->termID, $this->languageID, $this->translation, $this->termID, $this->languageID);
$s = $pdo->prepare($sql);
if($s === false) $this->bail($pdo->errorInfo());
$r = $s->execute ( $params );
if($r === false) $this->bail($s->errorInfo());
return $true;
}
public function add($termID, $languageID, $translation){
$this->termID = $termID;
$this->languageID = $languageID;
$this->translation = $translation;
return $this->save();
}
public function get($term, $language){
if(is_numeric($language)):
$sql = <<<SQL
SELECT *
FROM {$this->table}
WHERE termID = ?
AND
languageID = ?
SQL;
else:
$sql = <<<SQL
SELECT a.*
FROM {$this->table} a
JOIN languages b
ON b.languageID = a.languageID
WHERE a.termID = ?
AND
b.language LIKE ?
SQL;
endif;
$params = array($term, trim($language));
$s = $pdo->prepare($sql);
if($s === false) $this->bail($pdo->errorInfo());
$r = $s->execute ( $params );
if($r === false) $this->bail($s->errorInfo());
$this->load($s->fetch(PDO::FETCH_ASSOC));
return empty($this->translation) ? false : $this->translation;
}
}
?>
![[reading]](/data/assets/smilies/reading.gif "[reading] [reading]")
![[blush]](/data/assets/smilies/blush.gif "[blush] [blush]")
![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
![[3eyes]](/data/assets/smilies/3eyes.gif "[3eyes] [3eyes]")
![[lightsaber]](/data/assets/smilies/lightsaber.gif "[lightsaber] [lightsaber]")
![[banghead]](/data/assets/smilies/banghead.gif "[banghead] [banghead]")
![[ponder]](/data/assets/smilies/ponder.gif "[ponder] [ponder]")
![[flush2]](/data/assets/smilies/flush2.gif "[flush2] [flush2]")