[Sommaire] [Imprimer]

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