1. sfMessageSource_MySQL.class.php
  2. /**
 * sfMessageSource_MySQL class.
 * 
 * Retrieve the message translation from a MySQL database.
 *
 * See the MessageSource::factory() method to instantiate this class.
 *
 * MySQL schema:
 *
 * CREATE TABLE `catalogue` (
 *   `cat_id` int(11) NOT NULL auto_increment,
 *   `name` varchar(100) NOT NULL default '',
 *   `source_lang` varchar(100) NOT NULL default '',
 *   `target_lang` varchar(100) NOT NULL default '',
 *   `date_created` int(11) NOT NULL default '0',
 *   `date_modified` int(11) NOT NULL default '0',
 *   `author` varchar(255) NOT NULL default '',
 *   PRIMARY KEY  (`cat_id`)
 * ) TYPE=InnoDB;
 *
 * CREATE TABLE `trans_unit` (
 *   `msg_id` int(11) NOT NULL auto_increment,
 *   `cat_id` int(11) NOT NULL default '1',
 *   `id` varchar(255) NOT NULL default '',
 *   `source` text NOT NULL,
 *   `target` text NOT NULL,
 *   `comments` text NOT NULL,
 *   `date_added` int(11) NOT NULL default '0',
 *   `date_modified` int(11) NOT NULL default '0',
 *   `author` varchar(255) NOT NULL default '',
 *   `translated` tinyint(1) NOT NULL default '0',
 *   PRIMARY KEY  (`msg_id`)
 * ) TYPE=InnoDB;
 *
 * Propel schema (in .xml format):
 *
 *  
 *    ...
 *    
 *     
 *     
 *     
 *     
 *     
 *     
 *     
 *    
    
 *
 *    
 *     
 *     
 *       
 *         
 *       
 *     
 *     
 *     
 *     
 *     
 *     
 *     
 *     
 *    
    
 *    ...
 *      
 *
 * @author Xiang Wei Zhuo 
 * @version v1.0, last update on Fri Dec 24 16:58:58 EST 2004
 * @package    symfony
 * @subpackage i18n
 */
  3. class sfMessageSource_MySQL extends sfMessageSource_Database
  4. {
  5.   /**
  6.    * The datasource string, full DSN to the database.
  7.    * @var string 
  8.    */
  9.   protected $source;
  11.   /**
  12.    * The DSN array property, parsed by PEAR's DB DSN parser.
  13.    * @var array 
  14.    */
  15.   protected $dsn;
  17.   /**
  18.    * A resource link to the database
  19.    * @var db 
  20.    */
  21.   protected $db;
  23.   /**
  24.    * Constructor.
  25.    * Creates a new message source using MySQL.
  26.    *
  27.    * @param string $source  MySQL datasource, in PEAR's DB DSN format.
  28.    * @see MessageSource::factory();
  29.    */
  30.   function __construct($source)
  31.   {
  32.     $this->source = (string) $source;
  33.     $this->dsn = $this->parseDSN($this->source);
  34.     $this->db = $this->connect();
  35.   }
  37.   /**
  38.    * Destructor, closes the database connection.
  39.    */
  40.   function __destruct()
  41.   {
  42.     @mysql_close($this->db);
  43.   }
  45.   /**
  46.    * Connects to the MySQL datasource
  47.    *
  48.    * @return resource MySQL connection.
  49.    * @throws sfException, connection and database errors.
  50.    */
  51.   protected function connect()
  52.   {
  53.     $dsninfo = $this->dsn;
  55.     if (isset($dsninfo['protocol']) && $dsninfo['protocol'] == 'unix')
  56.     {
  57.       $dbhost = ':'.$dsninfo['socket'];
  58.     }
  59.     else
  60.     {
  61.       $dbhost = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
  62.       if (!empty($dsninfo['port']))
  63.       {
  64.         $dbhost .= ':'.$dsninfo['port'];
  65.       }
  66.     }
  67.     $user = $dsninfo['username'];
  68.     $pw = $dsninfo['password'];
  70.     $connect_function = 'mysql_connect';
  72.     if (!function_exists($connect_function))
  73.     {
  74.       throw new RuntimeException('The function mysql_connect() does not exist. Please confirm MySQL is enabled in php.ini');
  75.     }
  77.     if ($dbhost && $user && $pw)
  78.     {
  79.       $conn = @$connect_function($dbhost, $user, $pw);
  80.     }
  81.     elseif ($dbhost && $user)
  82.     {
  83.       $conn = @$connect_function($dbhost, $user);
  84.     }
  85.     elseif ($dbhost)
  86.     {
  87.       $conn = @$connect_function($dbhost);
  88.     }
  89.     else
  90.     {
  91.       $conn = false;
  92.     }
  94.     if (empty($conn))
  95.     {
  96.       throw new sfException(sprintf('Error in connecting to %s.', $dsninfo));
  97.     }
  99.     if ($dsninfo['database'])
  100.     {
  101.       if (!@mysql_select_db($dsninfo['database'], $conn))
  102.       {
  103.         throw new sfException(sprintf('Error in connecting database, dsn: %s.', $dsninfo));
  104.       }
  105.     }
  106.     else
  107.     {
  108.       throw new sfException('Please provide a database for message translation.');
  109.     }
  111.     return $conn;
  112.   }
  114.   /**
  115.    * Gets the database connection.
  116.    *
  117.    * @return db database connection. 
  118.    */
  119.   public function connection()
  120.   {
  121.     return $this->db;
  122.   }
  124.   /**
  125.    * Gets an array of messages for a particular catalogue and cultural variant.
  126.    *
  127.    * @param string $variant the catalogue name + variant
  128.    * @return array translation messages.
  129.    */
  130.   public function &loadData($variant)
  131.   {
  132.     $variant = mysql_real_escape_string($variant, $this->db);
  134.     $statement =
  135.       "SELECT t.id, t.source, t.target, t.comments
  136.         FROM trans_unit t, catalogue c
  137.         WHERE c.cat_id =  t.cat_id
  138.           AND c.name = '{$variant}'
  139.         ORDER BY id ASC";
  141.     $rs = mysql_query($statement, $this->db);
  143.     $result = array();
  145.     while ($row = mysql_fetch_array($rs, MYSQL_NUM))
  146.     {
  147.       $source = $row[1];
  148.       $result[$source][] = $row[2]; //target
  149.       $result[$source][] = $row[0]; //id
  150.       $result[$source][] = $row[3]; //comments
  151.     }
  153.     return $result;
  154.   }
  156.   /**
  157.    * Gets the last modified unix-time for this particular catalogue+variant.
  158.    * We need to query the database to get the date_modified.
  159.    *
  160.    * @param string $source catalogue+variant
  161.    * @return int last modified in unix-time format.
  162.    */
  163.   protected function getLastModified($source)
  164.   {
  165.     $source = mysql_real_escape_string($source, $this->db);
  167.     $rs = mysql_query("SELECT date_modified FROM catalogue WHERE name = '{$source}'", $this->db);
  169.     $result = $rs ? intval(mysql_result($rs, 0)) : 0;
  171.     return $result;
  172.   }
  174.   /**
  175.    * Checks if a particular catalogue+variant exists in the database.
  176.    *
  177.    * @param string $variant catalogue+variant
  178.    * @return boolean true if the catalogue+variant is in the database, false otherwise.
  179.    */ 
  180.   public function isValidSource($variant)
  181.   {
  182.     $variant = mysql_real_escape_string ($variant, $this->db);
  184.     $rs = mysql_query("SELECT COUNT(*) FROM catalogue WHERE name = '{$variant}'", $this->db);
  186.     $row = mysql_fetch_array($rs, MYSQL_NUM);
  188.     $result = $row && $row[0] == '1';
  190.     return $result;
  191.   }
  193.   /**
  194.    * Retrieves catalogue details, array($cat_id, $variant, $count).
  195.    *
  196.    * @param string $catalogue 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.     }
  206.     $variant = $catalogue.'.'.$this->culture;
  208.     $name = mysql_real_escape_string($this->getSource($variant), $this->db);
  210.     $rs = mysql_query("SELECT cat_id FROM catalogue WHERE name = '{$name}'", $this->db);
  212.     if (mysql_num_rows($rs) != 1)
  213.     {
  214.       return false;
  215.     }
  217.     $cat_id = intval(mysql_result($rs, 0));
  219.     // first get the catalogue ID
  220.     $rs = mysql_query("SELECT COUNT(*) FROM trans_unit WHERE cat_id = {$cat_id}", $this->db);
  222.     $count = intval(mysql_result($rs, 0));
  224.     return array($cat_id, $variant, $count);
  225.   }
  227.   /**
  228.    * Updates the catalogue last modified time.
  229.    *
  230.    * @return boolean true if updated, false otherwise. 
  231.    */
  232.   protected function updateCatalogueTime($cat_id, $variant)
  233.   {
  234.     $time = time();
  236.     $result = mysql_query("UPDATE catalogue SET date_modified = {$time} WHERE cat_id = {$cat_id}", $this->db);
  238.     if ($this->cache)
  239.     {
  240.       $this->cache->remove($variant.':'.$this->culture);
  241.     }
  243.     return $result;
  244.   }
  246.   /**
  247.    * Saves the list of untranslated blocks to the translation source. 
  248.    * If the translation was not found, you should add those
  249.    * strings to the translation source via the <b>append()</b> method.
  250.    *
  251.    * @param string $catalogue the catalogue to add to
  252.    * @return boolean true if saved successfuly, false otherwise.
  253.    */
  254.   function save($catalogue = 'messages')
  255.   {
  256.     $messages = $this->untranslated;
  258.     if (count($messages) <= 0)
  259.     {
  260.       return false;
  261.     }
  263.     $details = $this->getCatalogueDetails($catalogue);
  265.     if ($details)
  266.     {
  267.       list($cat_id, $variant, $count) = $details;
  268.     }
  269.     else
  270.     {
  271.       return false;
  272.     }
  274.     if ($cat_id <= 0)
  275.     {
  276.       return false;
  277.     }
  278.     $inserted = 0;
  280.     $time = time();
  282.     foreach ($messages as $message)
  283.     {
  284.       $count++;
  285.       $inserted++;
  286.       $message = mysql_real_escape_string($message, $this->db);
  287.       $statement = "INSERT INTO trans_unit
  288.         (cat_id,id,source,date_added) VALUES
  289.         ({$cat_id}, {$count},'{$message}',$time)";
  290.       mysql_query($statement, $this->db);
  291.     }
  292.     if ($inserted > 0)
  293.     {
  294.       $this->updateCatalogueTime($cat_id, $variant);
  295.     }
  297.     return $inserted > 0;
  298.   }
  300.   /**
  301.    * Deletes a particular message from the specified catalogue.
  302.    *
  303.    * @param string $message   the source message to delete.
  304.    * @param string $catalogue the catalogue to delete from.
  305.    * @return boolean true if deleted, false otherwise. 
  306.    */
  307.   function delete($message, $catalogue = 'messages')
  308.   {
  309.     $details = $this->getCatalogueDetails($catalogue);
  310.     if ($details)
  311.     {
  312.       list($cat_id, $variant, $count) = $details;
  313.     }
  314.     else
  315.     {
  316.       return false;
  317.     }
  319.     $text = mysql_real_escape_string($message, $this->db);
  321.     $statement = "DELETE FROM trans_unit WHERE cat_id = {$cat_id} AND source = '{$message}'";
  322.     $deleted = false;
  324.     mysql_query($statement, $this->db);
  326.     if (mysql_affected_rows($this->db) == 1)
  327.     {
  328.       $deleted = $this->updateCatalogueTime($cat_id, $variant);
  329.     }
  331.     return $deleted;
  332.   }
  334.   /**
  335.    * Updates the translation.
  336.    *
  337.    * @param string $text      the source string.
  338.    * @param string $target    the new translation string.
  339.    * @param string $comments  comments
  340.    * @param string $catalogue the catalogue of the translation.
  341.    * @return boolean true if translation was updated, false otherwise. 
  342.    */
  343.   function update($text, $target, $comments, $catalogue = 'messages')
  344.   {
  345.     $details = $this->getCatalogueDetails($catalogue);
  346.     if ($details)
  347.     {
  348.       list($cat_id, $variant, $count) = $details;
  349.     }
  350.     else
  351.     {
  352.       return false;
  353.     }
  355.     $comments = mysql_real_escape_string($comments, $this->db);
  356.     $target = mysql_real_escape_string($target, $this->db);
  357.     $text = mysql_real_escape_string($text, $this->db);
  359.     $time = time();
  361.     $statement = "UPDATE trans_unit SET target = '{$target}', comments = '{$comments}', date_modified = '{$time}' WHERE cat_id = {$cat_id} AND source = '{$text}'";
  363.     $updated = false;
  365.     mysql_query($statement, $this->db);
  366.     if (mysql_affected_rows($this->db) == 1)
  367.     {
  368.       $updated = $this->updateCatalogueTime($cat_id, $variant);
  369.     }
  371.     return $updated;
  372.   }
  374.   /**
  375.    * Returns a list of catalogue as key and all it variants as value.
  376.    *
  377.    * @return array list of catalogues 
  378.    */
  379.   function catalogues()
  380.   {
  381.     $statement = 'SELECT name FROM catalogue ORDER BY name';
  382.     $rs = mysql_query($statement, $this->db);
  383.     $result = array();
  384.     while($row = mysql_fetch_array($rs, MYSQL_NUM))
  385.     {
  386.       $details = explode('.', $row[0]);
  387.       if (!isset($details[1]))
  388.       {
  389.         $details[1] = null;
  390.       }
  392.       $result[] = $details;
  393.     }
  395.     return $result;
  396.   }
  397. }
Debug toolbar