/lib/vendor/symfony/lib/i18n/sfMessageSource_SQLite.class.php

  1<?php
  2
  3/**
  4 * sfMessageSource_SQLite 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_SQLite.class.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 17 * @package    symfony
 18 * @subpackage i18n
 19 */
 20
 21/**
 22 * sfMessageSource_SQLite class.
 23 * 
 24 * Retrieve the message translation from a SQLite database.
 25 *
 26 * See the MessageSource::factory() method to instantiate this class.
 27 *
 28 * SQLite schema:
 29 *
 30 * CREATE TABLE catalogue (
 31 *   cat_id INTEGER PRIMARY KEY,
 32 *   name VARCHAR NOT NULL,
 33 *   source_lang VARCHAR,
 34 *   target_lang VARCHAR,
 35 *   date_created INT,
 36 *   date_modified INT,
 37 *   author VARCHAR);
 38 *
 39 * CREATE TABLE trans_unit (
 40 *   msg_id INTEGER PRIMARY KEY,
 41 *   cat_id INTEGER NOT NULL DEFAULT '1',
 42 *   id VARCHAR,
 43 *   source TEXT,
 44 *   target TEXT,
 45 *   comments TEXT,
 46 *   date_added INT,
 47 *   date_modified INT,
 48 *   author VARCHAR,
 49 *   translated INT(1) NOT NULL DEFAULT '0');
 50 *
 51 * Propel schema (in .xml format):
 52 *
 53 *  <database ...>
 54 *    ...
 55 *    <table name="catalogue">
 56 *     <column name="cat_id" type="integer" required="true" primaryKey="true" autoincrement="true" />
 57 *     <column name="name" type="varchar" size="100" />
 58 *     <column name="source_lang" type="varchar" size="100" />
 59 *     <column name="target_lang" type="varchar" size="100" />
 60 *     <column name="date_created" type="timestamp" />
 61 *     <column name="date_modified" type="timestamp" />
 62 *     <column name="author" type="varchar" size="255" />
 63 *    </table>
 64 *
 65 *    <table name="trans_unit">
 66 *     <column name="msg_id" type="integer" required="true" primaryKey="true" autoincrement="true" />
 67 *     <column name="cat_id" type="integer" />
 68 *       <foreign-key foreignTable="catalogue" onDelete="cascade">
 69 *         <reference local="cat_id" foreign="cat_id"/>
 70 *       </foreign-key>
 71 *     <column name="id" type="varchar" size="255" />
 72 *     <column name="source" type="longvarchar" />
 73 *     <column name="target" type="longvarchar" />
 74 *     <column name="comments" type="longvarchar" />
 75 *     <column name="date_created" type="timestamp" />
 76 *     <column name="date_modified" type="timestamp" />
 77 *     <column name="author" type="varchar" size="255" />
 78 *     <column name="translated" type="integer" />
 79 *    </table>
 80 *    ...
 81 *  </database>
 82 *
 83 * @author Xiang Wei Zhuo <weizhuo[at]gmail[dot]com>
 84 * @version v1.0, last update on Fri Dec 24 16:58:58 EST 2004
 85 * @package    symfony
 86 * @subpackage i18n
 87 */
 88class sfMessageSource_SQLite extends sfMessageSource_Database
 89{
 90  /**
 91   * The SQLite datasource, the filename of the database.
 92   * @var string 
 93   */
 94  protected $source;
 95
 96  /**
 97   * Constructor.
 98   * Creates a new message source using SQLite.
 99   * @see MessageSource::factory();
100   * @param string $source SQLite datasource, in PEAR's DB DSN format.
101   */
102  function __construct($source)
103  {
104    $dsn = $this->parseDSN((string) $source);
105    $this->source = $dsn['database'];
106  }
107
108  /**
109   * Gets an array of messages for a particular catalogue and cultural variant.
110   *
111   * @param string $variant the catalogue name + variant
112   * @return array translation messages.
113   */
114  public function &loadData($variant)
115  {
116    $variant = sqlite_escape_string($variant);
117
118    $statement =
119      "SELECT t.id, t.source, t.target, t.comments
120        FROM trans_unit t, catalogue c
121        WHERE c.cat_id =  t.cat_id
122          AND c.name = '{$variant}'
123        ORDER BY id ASC";
124
125    $db = sqlite_open($this->source);
126    $rs = sqlite_query($statement, $db);
127
128    $result = array();
129
130    while($row = sqlite_fetch_array($rs, SQLITE_NUM))
131    {
132      $source = $row[1];
133      $result[$source][] = $row[2]; //target
134      $result[$source][] = $row[0]; //id
135      $result[$source][] = $row[3]; //comments
136    }
137
138    sqlite_close($db);
139
140    return $result;
141  }
142
143  /**
144   * Gets the last modified unix-time for this particular catalogue+variant.
145   * We need to query the database to get the date_modified.
146   *
147   * @param string $source catalogue+variant
148   * @return int last modified in unix-time format.
149   */
150  protected function getLastModified($source)
151  {
152    $source = sqlite_escape_string($source);
153
154    $db = sqlite_open($this->source);
155
156    $rs = sqlite_query("SELECT date_modified FROM catalogue WHERE name = '{$source}'", $db);
157
158    $result = $rs ? intval(sqlite_fetch_single($rs)) : 0;
159
160    sqlite_close($db);
161
162    return $result;
163  }
164
165  /**
166   * Checks if a particular catalogue+variant exists in the database.
167   *
168   * @param string $variant catalogue+variant
169   * @return boolean true if the catalogue+variant is in the database, false otherwise.
170   */
171  public function isValidSource($variant)
172  {
173    $variant = sqlite_escape_string($variant);
174    $db = sqlite_open($this->source);
175    $rs = sqlite_query("SELECT COUNT(*) FROM catalogue WHERE name = '{$variant}'", $db);
176    $result = $rs && intval(sqlite_fetch_single($rs));
177    sqlite_close($db);
178
179    return $result;
180  }
181
182  /**
183   * Retrieves catalogue details, array($cat_id, $variant, $count).
184   *
185   * @param string $catalogue catalogue
186   * @return array catalogue details, array($cat_id, $variant, $count).
187   */
188  protected function getCatalogueDetails($catalogue = 'messages')
189  {
190    if (empty($catalogue))
191    {
192      $catalogue = 'messages';
193    }
194
195    $variant = $catalogue.'.'.$this->culture;
196
197    $name = sqlite_escape_string($this->getSource($variant));
198
199    $db = sqlite_open($this->source);
200
201    $rs = sqlite_query("SELECT cat_id FROM catalogue WHERE name = '{$name}'", $db);
202
203    if (sqlite_num_rows($rs) != 1)
204    {
205      return false;
206    }
207
208    $cat_id = intval(sqlite_fetch_single($rs));
209
210    // first get the catalogue ID
211    $rs = sqlite_query("SELECT count(msg_id) FROM trans_unit WHERE cat_id = {$cat_id}", $db);
212
213    $count = intval(sqlite_fetch_single($rs));
214
215    sqlite_close($db);
216
217    return array($cat_id, $variant, $count);
218  }
219
220  /**
221   * Updates the catalogue last modified time.
222   *
223   * @return boolean true if updated, false otherwise. 
224   */
225  protected function updateCatalogueTime($cat_id, $variant, $db)
226  {
227    $time = time();
228
229    $result = sqlite_query("UPDATE catalogue SET date_modified = {$time} WHERE cat_id = {$cat_id}", $db);
230
231    if ($this->cache)
232    {
233      $this->cache->remove($variant.':'.$this->culture);
234    }
235
236    return $result;
237  }
238
239  /**
240   * Saves the list of untranslated blocks to the translation source. 
241   * If the translation was not found, you should add those
242   * strings to the translation source via the <b>append()</b> method.
243   *
244   * @param string $catalogue the catalogue to add to
245   * @return boolean true if saved successfuly, false otherwise.
246   */
247  function save($catalogue = 'messages')
248  {
249    $messages = $this->untranslated;
250
251    if (count($messages) <= 0)
252    {
253      return false;
254    }
255
256    $details = $this->getCatalogueDetails($catalogue);
257
258    if ($details)
259    {
260      list($cat_id, $variant, $count) = $details;
261    }
262    else
263    {
264      return false;
265    }
266
267    if ($cat_id <= 0)
268    {
269      return false;
270    }
271    $inserted = 0;
272
273    $db = sqlite_open($this->source);
274    $time = time();
275
276    foreach ($messages as $message)
277    {
278      $message = sqlite_escape_string($message);
279      if (sqlite_query("INSERT INTO trans_unit (cat_id, id, source, date_added) VALUES ({$cat_id}, {$count}, '{$message}', $time)", $db))
280      {
281        $count++;
282        $inserted++;
283      }
284    }
285    if ($inserted > 0)
286    {
287      $this->updateCatalogueTime($cat_id, $variant, $db);
288    }
289
290    sqlite_close($db);
291
292    return $inserted > 0;
293  }
294
295  /**
296   * Updates the translation.
297   *
298   * @param string $text      the source string.
299   * @param string $target    the new translation string.
300   * @param string $comments  comments
301   * @param string $catalogue the catalogue of the translation.
302   * @return boolean true if translation was updated, false otherwise. 
303   */
304  function update($text, $target, $comments, $catalogue = 'messages')
305  {
306    $details = $this->getCatalogueDetails($catalogue);
307    if ($details)
308    {
309      list($cat_id, $variant, $count) = $details;
310    }
311    else
312    {
313      return false;
314    }
315
316    $comments = sqlite_escape_string($comments);
317    $target = sqlite_escape_string($target);
318    $text = sqlite_escape_string($text);
319
320    $time = time();
321
322    $db = sqlite_open($this->source);
323
324    sqlite_query("UPDATE trans_unit SET target = '{$target}', comments = '{$comments}', date_modified = '{$time}' WHERE cat_id = {$cat_id} AND source = '{$text}'", $db);
325
326    if (sqlite_changes($db))
327    {
328      $this->updateCatalogueTime($cat_id, $variant, $db);
329      $updated = true;
330    }
331    else
332    {
333      $updated = false;
334    }
335
336    sqlite_close($db);
337
338    return $updated;
339  }
340
341  /**
342   * Deletes a particular message from the specified catalogue.
343   *
344   * @param string  $message    the source message to delete.
345   * @param string  $catalogue  the catalogue to delete from.
346   * @return boolean true if deleted, false otherwise. 
347   */
348  function delete($message, $catalogue = 'messages')
349  {
350    $details = $this->getCatalogueDetails($catalogue);
351    if ($details)
352    {
353      list($cat_id, $variant, $count) = $details;
354    }
355    else
356    {
357      return false;
358    }
359
360    $db = sqlite_open($this->source);
361    $text = sqlite_escape_string($message);
362
363    sqlite_query("DELETE FROM trans_unit WHERE cat_id = {$cat_id} AND source = '{$message}'", $db);
364
365    if (sqlite_changes($db))
366    {
367      $this->updateCatalogueTime($cat_id, $variant, $db);
368      $deleted = true;
369    }
370    else
371    {
372      $deleted = false;
373    }
374
375    sqlite_close($db);
376
377    return $deleted;
378  }
379
380  /**
381   * Returns a list of catalogue as key and all it variants as value.
382   *
383   * @return array list of catalogues 
384   */
385  function catalogues()
386  {
387    $db = sqlite_open($this->source);
388    $statement = 'SELECT name FROM catalogue ORDER BY name';
389    $rs = sqlite_query($statement, $db);
390    $result = array();
391    while ($row = sqlite_fetch_array($rs, SQLITE_NUM))
392    {
393      $details = explode('.', $row[0]);
394      if (!isset($details[1]))
395      {
396        $details[1] = null;
397      }
398
399      $result[] = $details;
400    }
401    sqlite_close($db);
402
403    return $result;
404  }
405}