[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /**
   4   * MessageSource_MySQL class file.
   5   *
   6   * This program is free software; you can redistribute it and/or modify
   7   * it under the terms of the BSD License.
   8   *
   9   * Copyright(c) 2004 by Qiang Xue. All rights reserved.
  10   *
  11   * To contact the author write to {@link mailto:qiang.xue@gmail.com Qiang Xue}
  12   * The latest version of PRADO can be obtained from:
  13   * {@link http://prado.sourceforge.net/}
  14   *
  15   * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
  16   * @version $Revision: 1.4 $  $Date: 2005/02/25 09:59:40 $
  17   * @package System.I18N.core
  18   */
  19  
  20  /**
  21   * Get the MessageSource class file.
  22   */
  23  require_once(dirname(__FILE__).'/MessageSource.php');
  24  
  25  /**
  26   * Get the I18N utility file, contains the DSN parser.
  27   */
  28  require_once(dirname(__FILE__).'/util.php');
  29  
  30  /**
  31   * MessageSource_MySQL class.
  32   * 
  33   * Retrive the message translation from a MySQL database.
  34   *
  35   * See the MessageSource::factory() method to instantiate this class.
  36   *
  37   * @author Xiang Wei Zhuo <weizhuo[at]gmail[dot]com>
  38   * @version v1.0, last update on Fri Dec 24 16:58:58 EST 2004
  39   * @package System.I18N.core
  40   */
  41  class MessageSource_MySQL extends MessageSource
  42  {
  43      /**
  44       * The datasource string, full DSN to the database.
  45       * @var string 
  46       */
  47      protected $source;
  48          
  49      /**
  50       * The DSN array property, parsed by PEAR's DB DSN parser.
  51       * @var array 
  52       */
  53      protected $dns;
  54      
  55      /**
  56       * A resource link to the database
  57       * @var db 
  58       */
  59      protected $db;
  60      /**
  61       * Constructor.
  62       * Create a new message source using MySQL.
  63       * @param string MySQL datasource, in PEAR's DB DSN format.
  64       * @see MessageSource::factory();
  65       */
  66  	function __construct($source)
  67      {
  68          $this->source = (string)$source;        
  69          $this->dns = parseDSN($this->source);
  70          $this->db = $this->connect();
  71      }
  72      
  73      /**
  74       * Destructor, close the database connection.
  75       */
  76  	function __destruct() 
  77      {
  78          @mysql_close($this->db);
  79      }
  80      
  81      /**
  82       * Connect to the MySQL datasource
  83       * @return resource MySQL connection. 
  84       * @throws Exception, connection and database errors.
  85       */
  86  	protected function connect()
  87      {
  88          /*static $conn;
  89          
  90          if(!is_null($conn))
  91              return $conn;
  92          */
  93          $dsninfo = $this->dns;
  94          
  95           if (isset($dsninfo['protocol']) && $dsninfo['protocol'] == 'unix') 
  96              $dbhost = ':' . $dsninfo['socket'];
  97          else 
  98          {
  99              $dbhost = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
 100              if (!empty($dsninfo['port'])) 
 101                  $dbhost .= ':' . $dsninfo['port'];
 102          }
 103          $user = $dsninfo['username'];
 104          $pw = $dsninfo['password'];        
 105          
 106          $connect_function = 'mysql_connect';
 107  
 108          if ($dbhost && $user && $pw)
 109              $conn = @$connect_function($dbhost, $user, $pw);
 110          elseif ($dbhost && $user)
 111              $conn = @$connect_function($dbhost, $user);
 112          elseif ($dbhost)
 113              $conn = @$connect_function($dbhost);
 114          else
 115              $conn = false;        
 116                      
 117          if (empty($conn)) 
 118          {
 119              throw new Exception('Error in connecting to '.$dsninfo);
 120          }
 121          
 122          if ($dsninfo['database']) 
 123          {
 124              if (!@mysql_select_db($dsninfo['database'], $conn)) 
 125                  throw new Exception('Error in connecting database, dns:'.
 126                                      $dsninfo);
 127          }
 128          else
 129              throw new Exception('Please provide a database for message'.
 130                                  ' translation.');                                    
 131         return $conn;
 132      }
 133      
 134      /**
 135       * Get the database connection.
 136       * @return db database connection. 
 137       */
 138  	public function connection()
 139      {
 140          return $this->db;
 141      }
 142      
 143      /**
 144       * Get an array of messages for a particular catalogue and cultural 
 145       * variant.
 146       * @param string the catalogue name + variant
 147       * @return array translation messages.
 148       */        
 149      protected function &loadData($variant)
 150      {        
 151          $variant = mysql_escape_string($variant);
 152          
 153          $statement = 
 154              "SELECT t.id, t.source, t.target, t.comments
 155                  FROM trans_unit t, catalogue c
 156                   WHERE c.cat_id =  t.cat_id
 157                      AND c.name = '{$variant}' 
 158                  ORDER BY id ASC";
 159              
 160          $rs = mysql_query($statement,$this->db);
 161              
 162          $result = array();
 163          
 164          while($row = mysql_fetch_array($rs,MYSQL_NUM))
 165          {
 166              $source = $row[1];
 167              $result[$source][] = $row[2]; //target
 168              $result[$source][] = $row[0]; //id
 169              $result[$source][] = $row[3]; //comments
 170          }
 171          
 172          return $result;
 173      }
 174      
 175      /**
 176       * Get the last modified unix-time for this particular catalogue+variant.
 177       * We need to query the database to get the date_modified.
 178       * @param string catalogue+variant
 179       * @return int last modified in unix-time format.
 180       */    
 181  	protected function getLastModified($source)
 182      {
 183          $source = mysql_escape_string($source);
 184  
 185          $rs = mysql_query(
 186              "SELECT date_modified FROM catalogue WHERE name = '{$source}'",
 187              $this->db);
 188              
 189          $result = $rs ? intval(mysql_result($rs,0)) : 0;
 190              
 191          return $result;            
 192      }
 193          
 194      /**
 195       * Check if a particular catalogue+variant exists in the database.
 196       * @param string catalogue+variant
 197       * @return boolean true if the catalogue+variant is in the database, 
 198       * false otherwise.
 199       */    
 200  	protected function isValidSource($variant)
 201      {
 202          $variant = mysql_escape_string ($variant);
 203  
 204          $rs = mysql_query( 
 205              "SELECT COUNT(*) FROM catalogue WHERE name = '{$variant}'",
 206              $this->db);
 207              
 208          $row = mysql_fetch_array($rs,MYSQL_NUM);
 209          
 210          $result = $row && $row[0] == '1';
 211  
 212          return $result;
 213      }
 214      
 215      /**
 216       * Get all the variants of a particular catalogue.
 217       * @param string catalogue name
 218       * @return array list of all variants for this catalogue. 
 219       */     
 220  	protected function getCatalogueList($catalogue)
 221      {
 222          $variants = explode('_',$this->culture);
 223          
 224          $catalogues = array($catalogue);
 225  
 226          $variant = null;
 227                  
 228          for($i = 0, $k = count($variants); $i < $k; ++$i)
 229          {    
 230              if(isset($variants[$i]{0}))
 231              {
 232                  $variant .= ($variant)?'_'.$variants[$i]:$variants[$i];
 233                  $catalogues[] = $catalogue.'.'.$variant;
 234              }
 235          }
 236          return array_reverse($catalogues);    
 237      }    
 238      
 239      /**
 240       * Retrive catalogue details, array($cat_id, $variant, $count).
 241       * @param string catalogue
 242       * @return array catalogue details, array($cat_id, $variant, $count). 
 243       */
 244  	private function getCatalogueDetails($catalogue='messages')
 245      {
 246          if(empty($catalogue))
 247              $catalogue = 'messages';
 248  
 249          $variant = $catalogue.'.'.$this->culture;
 250          
 251          $name = mysql_escape_string($this->getSource($variant));    
 252                  
 253          $rs = mysql_query("SELECT cat_id
 254                      FROM catalogue WHERE name = '{$name}'", $this->db);
 255          
 256          if(mysql_num_rows($rs) != 1)
 257              return false;
 258          
 259          $cat_id = intval(mysql_result($rs,0));
 260          
 261          //first get the catalogue ID
 262          $rs = mysql_query(
 263              "SELECT count(msg_id)
 264                  FROM trans_unit
 265                  WHERE cat_id = {$cat_id}", $this->db);
 266  
 267          $count = intval(mysql_result($rs,0));
 268      
 269          return array($cat_id, $variant, $count);
 270      }    
 271      
 272      /**
 273       * Update the catalogue last modified time.
 274       * @return boolean true if updated, false otherwise. 
 275       */
 276  	private function updateCatalogueTime($cat_id, $variant)
 277      {
 278          $time = time();
 279          
 280          $result = mysql_query("UPDATE catalogue 
 281                              SET date_modified = {$time}
 282                              WHERE cat_id = {$cat_id}", $this->db);
 283              
 284          if(!empty($this->cache))
 285              $this->cache->clean($variant, $this->culture);    
 286          
 287          return $result;
 288      }    
 289      
 290      /**
 291       * Save the list of untranslated blocks to the translation source. 
 292       * If the translation was not found, you should add those
 293       * strings to the translation source via the <b>append()</b> method.
 294       * @param string the catalogue to add to
 295       * @return boolean true if saved successfuly, false otherwise.
 296       */
 297  	function save($catalogue='messages')
 298      {
 299          $messages = $this->untranslated;
 300          
 301          if(count($messages) <= 0) return false;        
 302  
 303          $details = $this->getCatalogueDetails($catalogue);    
 304          
 305          if($details)
 306              list($cat_id, $variant, $count) = $details;
 307          else
 308              return false;                    
 309          
 310          if($cat_id <= 0) return false;
 311          $inserted = 0;
 312  
 313          $time = time();
 314  
 315          foreach($messages as $message)
 316          {
 317              $count++; $inserted++;
 318              $message = mysql_escape_string($message);
 319              $statement = "INSERT INTO trans_unit
 320                  (cat_id,id,source,date_added) VALUES
 321                  ({$cat_id}, {$count},'{$message}',$time)";
 322              mysql_query($statement, $this->db);
 323          }
 324          if($inserted > 0)
 325              $this->updateCatalogueTime($cat_id, $variant);            
 326  
 327          return $inserted > 0;
 328      }    
 329      
 330      /**
 331       * Delete a particular message from the specified catalogue.
 332       * @param string the source message to delete.
 333       * @param string the catalogue to delete from.
 334       * @return boolean true if deleted, false otherwise. 
 335       */
 336  	function delete($message, $catalogue='messages')
 337      {
 338          $details = $this->getCatalogueDetails($catalogue);
 339          if($details)
 340              list($cat_id, $variant, $count) = $details;
 341          else
 342              return false;
 343              
 344          $text = mysql_escape_string($message);
 345          
 346          $statement = "DELETE FROM trans_unit WHERE
 347                          cat_id = {$cat_id} AND source = '{$message}'";
 348          $deleted = false;
 349                  
 350          mysql_query($statement, $this->db);
 351  
 352          if(mysql_affected_rows($this->db) == 1)
 353              $deleted = $this->updateCatalogueTime($cat_id, $variant);        
 354                              
 355          return $deleted;
 356  
 357      }
 358          
 359      /**
 360       * Update the translation.
 361       * @param string the source string.
 362       * @param string the new translation string.
 363       * @param string comments
 364       * @param string the catalogue of the translation.
 365       * @return boolean true if translation was updated, false otherwise. 
 366       */    
 367  	function update($text, $target, $comments, $catalogue='messages')
 368      {
 369          $details = $this->getCatalogueDetails($catalogue);
 370          if($details)
 371              list($cat_id, $variant, $count) = $details;
 372          else
 373              return false;
 374          
 375          $comments = mysql_escape_string($comments);
 376          $target = mysql_escape_string($target);
 377          $text = mysql_escape_string($text);
 378          
 379          $time = time();
 380                  
 381          $statement = "UPDATE trans_unit SET
 382                          target = '{$target}',
 383                          comments = '{$comments}',
 384                          date_modified = '{$time}'
 385                      WHERE cat_id = {$cat_id} 
 386                          AND source = '{$text}'";
 387          
 388          $updated = false;
 389          
 390          mysql_query($statement, $this->db);
 391          if(mysql_affected_rows($this->db) == 1)
 392              $updated = $this->updateCatalogueTime($cat_id, $variant);
 393                      
 394          return $updated;
 395      }
 396      
 397      /**
 398       * Returns a list of catalogue as key and all it variants as value.
 399       * @return array list of catalogues 
 400       */
 401  	function catalogues()
 402      {
 403          $statement = 'SELECT name FROM catalogue ORDER BY name';
 404          $rs = mysql_query($statement, $this->db);
 405          $result = array();
 406          while($row = mysql_fetch_array($rs,MYSQL_NUM))
 407          {
 408              $details = explode('.',$row[0]);
 409              if(!isset($details[1])) $details[1] = null;
 410              
 411              $result[] = $details;
 412          }
 413          return $result;
 414      }
 415      
 416  }
 417  
 418  ?>