[Sommaire] [Imprimer]

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