1. sfMessageSource_SQLite.class.php
  2. /**
 * sfMessageSource_SQLite class.
 * 
 * Retrieve the message translation from a SQLite database.
 *
 * See the MessageSource::factory() method to instantiate this class.
 *
 * SQLite schema:
 *
 * CREATE TABLE catalogue (
 *   cat_id INTEGER PRIMARY KEY,
 *   name VARCHAR NOT NULL,
 *   source_lang VARCHAR,
 *   target_lang VARCHAR,
 *   date_created INT,
 *   date_modified INT,
 *   author VARCHAR);
 *
 * CREATE TABLE trans_unit (
 *   msg_id INTEGER PRIMARY KEY,
 *   cat_id INTEGER NOT NULL DEFAULT '1',
 *   id VARCHAR,
 *   source TEXT,
 *   target TEXT,
 *   comments TEXT,
 *   date_added INT,
 *   date_modified INT,
 *   author VARCHAR,
 *   translated INT(1) NOT NULL DEFAULT '0');
 *
 * 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_SQLite extends sfMessageSource_Database
  4. {
  5.   /**
  6.    * The SQLite datasource, the filename of the database.
  7.    * @var string 
  8.    */
  9.   protected $source;
  11.   /**
  12.    * Constructor.
  13.    * Creates a new message source using SQLite.
  14.    * @see MessageSource::factory();
  15.    * @param string $source SQLite datasource, in PEAR's DB DSN format.
  16.    */
  17.   function __construct($source)
  18.   {
  19.     $dsn = $this->parseDSN((string) $source);
  20.     $this->source = $dsn['database'];
  21.   }
  23.   /**
  24.    * Gets an array of messages for a particular catalogue and cultural variant.
  25.    *
  26.    * @param string $variant the catalogue name + variant
  27.    * @return array translation messages.
  28.    */
  29.   public function &loadData($variant)
  30.   {
  31.     $variant = sqlite_escape_string($variant);
  33.     $statement =
  34.       "SELECT t.id, t.source, t.target, t.comments
  35.         FROM trans_unit t, catalogue c
  36.         WHERE c.cat_id =  t.cat_id
  37.           AND c.name = '{$variant}'
  38.         ORDER BY id ASC";
  40.     $db = sqlite_open($this->source);
  41.     $rs = sqlite_query($statement, $db);
  43.     $result = array();
  45.     while($row = sqlite_fetch_array($rs, SQLITE_NUM))
  46.     {
  47.       $source = $row[1];
  48.       $result[$source][] = $row[2]; //target
  49.       $result[$source][] = $row[0]; //id
  50.       $result[$source][] = $row[3]; //comments
  51.     }
  53.     sqlite_close($db);
  55.     return $result;
  56.   }
  58.   /**
  59.    * Gets the last modified unix-time for this particular catalogue+variant.
  60.    * We need to query the database to get the date_modified.
  61.    *
  62.    * @param string $source catalogue+variant
  63.    * @return int last modified in unix-time format.
  64.    */
  65.   protected function getLastModified($source)
  66.   {
  67.     $source = sqlite_escape_string($source);
  69.     $db = sqlite_open($this->source);
  71.     $rs = sqlite_query("SELECT date_modified FROM catalogue WHERE name = '{$source}'", $db);
  73.     $result = $rs ? intval(sqlite_fetch_single($rs)) : 0;
  75.     sqlite_close($db);
  77.     return $result;
  78.   }
  80.   /**
  81.    * Checks if a particular catalogue+variant exists in the database.
  82.    *
  83.    * @param string $variant catalogue+variant
  84.    * @return boolean true if the catalogue+variant is in the database, false otherwise.
  85.    */
  86.   public function isValidSource($variant)
  87.   {
  88.     $variant = sqlite_escape_string($variant);
  89.     $db = sqlite_open($this->source);
  90.     $rs = sqlite_query("SELECT COUNT(*) FROM catalogue WHERE name = '{$variant}'", $db);
  91.     $result = $rs && intval(sqlite_fetch_single($rs));
  92.     sqlite_close($db);
  94.     return $result;
  95.   }
  97.   /**
  98.    * Retrieves catalogue details, array($cat_id, $variant, $count).
  99.    *
  100.    * @param string $catalogue catalogue
  101.    * @return array catalogue details, array($cat_id, $variant, $count).
  102.    */
  103.   protected function getCatalogueDetails($catalogue = 'messages')
  104.   {
  105.     if (empty($catalogue))
  106.     {
  107.       $catalogue = 'messages';
  108.     }
  110.     $variant = $catalogue.'.'.$this->culture;
  112.     $name = sqlite_escape_string($this->getSource($variant));
  114.     $db = sqlite_open($this->source);
  116.     $rs = sqlite_query("SELECT cat_id FROM catalogue WHERE name = '{$name}'", $db);
  118.     if (sqlite_num_rows($rs) != 1)
  119.     {
  120.       return false;
  121.     }
  123.     $cat_id = intval(sqlite_fetch_single($rs));
  125.     // first get the catalogue ID
  126.     $rs = sqlite_query("SELECT count(msg_id) FROM trans_unit WHERE cat_id = {$cat_id}", $db);
  128.     $count = intval(sqlite_fetch_single($rs));
  130.     sqlite_close($db);
  132.     return array($cat_id, $variant, $count);
  133.   }
  135.   /**
  136.    * Updates the catalogue last modified time.
  137.    *
  138.    * @return boolean true if updated, false otherwise. 
  139.    */
  140.   protected function updateCatalogueTime($cat_id, $variant, $db)
  141.   {
  142.     $time = time();
  144.     $result = sqlite_query("UPDATE catalogue SET date_modified = {$time} WHERE cat_id = {$cat_id}", $db);
  146.     if ($this->cache)
  147.     {
  148.       $this->cache->remove($variant.':'.$this->culture);
  149.     }
  151.     return $result;
  152.   }
  154.   /**
  155.    * Saves the list of untranslated blocks to the translation source. 
  156.    * If the translation was not found, you should add those
  157.    * strings to the translation source via the <b>append()</b> method.
  158.    *
  159.    * @param string $catalogue the catalogue to add to
  160.    * @return boolean true if saved successfuly, false otherwise.
  161.    */
  162.   function save($catalogue = 'messages')
  163.   {
  164.     $messages = $this->untranslated;
  166.     if (count($messages) <= 0)
  167.     {
  168.       return false;
  169.     }
  171.     $details = $this->getCatalogueDetails($catalogue);
  173.     if ($details)
  174.     {
  175.       list($cat_id, $variant, $count) = $details;
  176.     }
  177.     else
  178.     {
  179.       return false;
  180.     }
  182.     if ($cat_id <= 0)
  183.     {
  184.       return false;
  185.     }
  186.     $inserted = 0;
  188.     $db = sqlite_open($this->source);
  189.     $time = time();
  191.     foreach ($messages as $message)
  192.     {
  193.       $message = sqlite_escape_string($message);
  194.       if (sqlite_query("INSERT INTO trans_unit (cat_id, id, source, date_added) VALUES ({$cat_id}, {$count}, '{$message}', $time)", $db))
  195.       {
  196.         $count++;
  197.         $inserted++;
  198.       }
  199.     }
  200.     if ($inserted > 0)
  201.     {
  202.       $this->updateCatalogueTime($cat_id, $variant, $db);
  203.     }
  205.     sqlite_close($db);
  207.     return $inserted > 0;
  208.   }
  210.   /**
  211.    * Updates the translation.
  212.    *
  213.    * @param string $text      the source string.
  214.    * @param string $target    the new translation string.
  215.    * @param string $comments  comments
  216.    * @param string $catalogue the catalogue of the translation.
  217.    * @return boolean true if translation was updated, false otherwise. 
  218.    */
  219.   function update($text, $target, $comments, $catalogue = 'messages')
  220.   {
  221.     $details = $this->getCatalogueDetails($catalogue);
  222.     if ($details)
  223.     {
  224.       list($cat_id, $variant, $count) = $details;
  225.     }
  226.     else
  227.     {
  228.       return false;
  229.     }
  231.     $comments = sqlite_escape_string($comments);
  232.     $target = sqlite_escape_string($target);
  233.     $text = sqlite_escape_string($text);
  235.     $time = time();
  237.     $db = sqlite_open($this->source);
  239.     sqlite_query("UPDATE trans_unit SET target = '{$target}', comments = '{$comments}', date_modified = '{$time}' WHERE cat_id = {$cat_id} AND source = '{$text}'", $db);
  241.     if (sqlite_changes($db))
  242.     {
  243.       $this->updateCatalogueTime($cat_id, $variant, $db);
  244.       $updated = true;
  245.     }
  246.     else
  247.     {
  248.       $updated = false;
  249.     }
  251.     sqlite_close($db);
  253.     return $updated;
  254.   }
  256.   /**
  257.    * Deletes a particular message from the specified catalogue.
  258.    *
  259.    * @param string  $message    the source message to delete.
  260.    * @param string  $catalogue  the catalogue to delete from.
  261.    * @return boolean true if deleted, false otherwise. 
  262.    */
  263.   function delete($message, $catalogue = 'messages')
  264.   {
  265.     $details = $this->getCatalogueDetails($catalogue);
  266.     if ($details)
  267.     {
  268.       list($cat_id, $variant, $count) = $details;
  269.     }
  270.     else
  271.     {
  272.       return false;
  273.     }
  275.     $db = sqlite_open($this->source);
  276.     $text = sqlite_escape_string($message);
  278.     sqlite_query("DELETE FROM trans_unit WHERE cat_id = {$cat_id} AND source = '{$message}'", $db);
  280.     if (sqlite_changes($db))
  281.     {
  282.       $this->updateCatalogueTime($cat_id, $variant, $db);
  283.       $deleted = true;
  284.     }
  285.     else
  286.     {
  287.       $deleted = false;
  288.     }
  290.     sqlite_close($db);
  292.     return $deleted;
  293.   }
  295.   /**
  296.    * Returns a list of catalogue as key and all it variants as value.
  297.    *
  298.    * @return array list of catalogues 
  299.    */
  300.   function catalogues()
  301.   {
  302.     $db = sqlite_open($this->source);
  303.     $statement = 'SELECT name FROM catalogue ORDER BY name';
  304.     $rs = sqlite_query($statement, $db);
  305.     $result = array();
  306.     while ($row = sqlite_fetch_array($rs, SQLITE_NUM))
  307.     {
  308.       $details = explode('.', $row[0]);
  309.       if (!isset($details[1]))
  310.       {
  311.         $details[1] = null;
  312.       }
  314.       $result[] = $details;
  315.     }
  316.     sqlite_close($db);
  318.     return $result;
  319.   }
  320. }
Debug toolbar