Development

/branches/1.0/lib/i18n/sfMessageSource_MySQL.class.php

You must first sign up to be able to contribute.

root/branches/1.0/lib/i18n/sfMessageSource_MySQL.class.php

Revision 6806, 12.8 kB (checked in by fabien, 6 years ago)

fixed phpdoc package/subpackage entries

  • Property svn:mime-type set to text/x-php
  • Property svn:eol-style set to native
  • Property svn:keywords set to Id Rev Date
Line 
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$
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  * MySQL schema:
34  *
35  * CREATE TABLE `catalogue` (
36  *   `cat_id` int(11) NOT NULL auto_increment,
37  *   `name` varchar(100) NOT NULL default '',
38  *   `source_lang` varchar(100) NOT NULL default '',
39  *   `target_lang` varchar(100) NOT NULL default '',
40  *   `date_created` int(11) NOT NULL default '0',
41  *   `date_modified` int(11) NOT NULL default '0',
42  *   `author` varchar(255) NOT NULL default '',
43  *   PRIMARY KEY  (`cat_id`)
44  * ) TYPE=InnoDB;
45  *
46  * CREATE TABLE `trans_unit` (
47  *   `msg_id` int(11) NOT NULL auto_increment,
48  *   `cat_id` int(11) NOT NULL default '1',
49  *   `id` varchar(255) NOT NULL default '',
50  *   `source` text NOT NULL,
51  *   `target` text NOT NULL,
52  *   `comments` text NOT NULL,
53  *   `date_added` int(11) NOT NULL default '0',
54  *   `date_modified` int(11) NOT NULL default '0',
55  *   `author` varchar(255) NOT NULL default '',
56  *   `translated` tinyint(1) NOT NULL default '0',
57  *   PRIMARY KEY  (`msg_id`)
58  * ) TYPE=InnoDB;
59  *
60  * Propel schema (in .xml format):
61  *
62  *  <database ...>
63  *    ...
64  *    <table name="catalogue">
65  *     <column name="cat_id" type="integer" required="true" primaryKey="true" autoincrement="true" />
66  *     <column name="name" type="varchar" size="100" />
67  *     <column name="source_lang" type="varchar" size="100" />
68  *     <column name="target_lang" type="varchar" size="100" />
69  *     <column name="date_created" type="timestamp" />
70  *     <column name="date_modified" type="timestamp" />
71  *     <column name="author" type="varchar" size="255" />
72  *    </table>
73  *
74  *    <table name="trans_unit">
75  *     <column name="msg_id" type="integer" required="true" primaryKey="true" autoincrement="true" />
76  *     <column name="cat_id" type="integer" />
77  *       <foreign-key foreignTable="catalogue" onDelete="cascade">
78  *         <reference local="cat_id" foreign="cat_id"/>
79  *       </foreign-key>
80  *     <column name="id" type="varchar" size="255" />
81  *     <column name="source" type="longvarchar" />
82  *     <column name="target" type="longvarchar" />
83  *     <column name="comments" type="longvarchar" />
84  *     <column name="date_created" type="timestamp" />
85  *     <column name="date_modified" type="timestamp" />
86  *     <column name="author" type="varchar" size="255" />
87  *     <column name="translated" type="integer" />
88  *    </table>
89  *    ...
90  *  </database>
91  *
92  * @author Xiang Wei Zhuo <weizhuo[at]gmail[dot]com>
93  * @version v1.0, last update on Fri Dec 24 16:58:58 EST 2004
94  * @package    symfony
95  * @subpackage i18n
96  */
97 class sfMessageSource_MySQL extends sfMessageSource
98 {
99   /**
100    * The datasource string, full DSN to the database.
101    * @var string
102    */
103   protected $source;
104
105   /**
106    * The DSN array property, parsed by PEAR's DB DSN parser.
107    * @var array
108    */
109   protected $dsn;
110
111   /**
112    * A resource link to the database
113    * @var db
114    */
115   protected $db;
116
117   /**
118    * Constructor.
119    * Creates a new message source using MySQL.
120    *
121    * @param string MySQL datasource, in PEAR's DB DSN format.
122    * @see MessageSource::factory();
123    */
124   function __construct($source)
125   {
126     $this->source = (string) $source;
127     $this->dsn = parseDSN($this->source);
128     $this->db = $this->connect();
129   }
130
131   /**
132    * Destructor, closes the database connection.
133    */
134   function __destruct()
135   {
136     @mysql_close($this->db);
137   }
138
139   /**
140    * Connects to the MySQL datasource
141    *
142    * @return resource MySQL connection.
143    * @throws sfException, connection and database errors.
144    */
145   protected function connect()
146   {
147     $dsninfo = $this->dsn;
148
149     if (isset($dsninfo['protocol']) && $dsninfo['protocol'] == 'unix')
150     {
151       $dbhost = ':'.$dsninfo['socket'];
152     }
153     else
154     {
155       $dbhost = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
156       if (!empty($dsninfo['port']))
157       {
158         $dbhost .= ':'.$dsninfo['port'];
159       }
160     }
161     $user = $dsninfo['username'];
162     $pw = $dsninfo['password'];
163
164     $connect_function = 'mysql_connect';
165
166     if ($dbhost && $user && $pw)
167     {
168       $conn = @$connect_function($dbhost, $user, $pw);
169     }
170     elseif ($dbhost && $user)
171     {
172       $conn = @$connect_function($dbhost, $user);
173     }
174     elseif ($dbhost)
175     {
176       $conn = @$connect_function($dbhost);
177     }
178     else
179     {
180       $conn = false;
181     }
182
183     if (empty($conn))
184     {
185       throw new sfException(sprintf('Error in connecting to %s.', $dsninfo));
186     }
187
188     if ($dsninfo['database'])
189     {
190       if (!@mysql_select_db($dsninfo['database'], $conn))
191       {
192         throw new sfException(sprintf('Error in connecting database, dsn: %s.', $dsninfo));
193       }
194     }
195     else
196     {
197       throw new sfException('Please provide a database for message translation.');
198     }
199
200     return $conn;
201   }
202
203   /**
204    * Gets the database connection.
205    *
206    * @return db database connection.
207    */
208   public function connection()
209   {
210     return $this->db;
211   }
212
213   /**
214    * Gets an array of messages for a particular catalogue and cultural variant.
215    *
216    * @param string the catalogue name + variant
217    * @return array translation messages.
218    */
219   protected function &loadData($variant)
220   {
221     $variant = mysql_real_escape_string($variant, $this->db);
222
223     $statement =
224       "SELECT t.id, t.source, t.target, t.comments
225         FROM trans_unit t, catalogue c
226         WHERE c.cat_id =  t.cat_id
227           AND c.name = '{$variant}'
228         ORDER BY id ASC";
229
230     $rs = mysql_query($statement, $this->db);
231
232     $result = array();
233
234     while ($row = mysql_fetch_array($rs, MYSQL_NUM))
235     {
236       $source = $row[1];
237       $result[$source][] = $row[2]; //target
238       $result[$source][] = $row[0]; //id
239       $result[$source][] = $row[3]; //comments
240     }
241
242     return $result;
243   }
244
245   /**
246    * Gets the last modified unix-time for this particular catalogue+variant.
247    * We need to query the database to get the date_modified.
248    *
249    * @param string catalogue+variant
250    * @return int last modified in unix-time format.
251    */
252   protected function getLastModified($source)
253   {
254     $source = mysql_real_escape_string($source, $this->db);
255
256     $rs = mysql_query("SELECT date_modified FROM catalogue WHERE name = '{$source}'", $this->db);
257
258     $result = $rs ? intval(mysql_result($rs, 0)) : 0;
259
260     return $result;
261   }
262
263   /**
264    * Checks if a particular catalogue+variant exists in the database.
265    *
266    * @param string catalogue+variant
267    * @return boolean true if the catalogue+variant is in the database, false otherwise.
268    */
269   protected function isValidSource($variant)
270   {
271     $variant = mysql_real_escape_string ($variant, $this->db);
272
273     $rs = mysql_query("SELECT COUNT(*) FROM catalogue WHERE name = '{$variant}'", $this->db);
274
275     $row = mysql_fetch_array($rs, MYSQL_NUM);
276
277     $result = $row && $row[0] == '1';
278
279     return $result;
280   }
281
282   /**
283    * Gets all the variants of a particular catalogue.
284    *
285    * @param string catalogue name
286    * @return array list of all variants for this catalogue.
287    */
288   protected function getCatalogueList($catalogue)
289   {
290     $variants = explode('_', $this->culture);
291
292     $catalogues = array($catalogue);
293
294     $variant = null;
295
296     for ($i = 0, $max = count($variants); $i < $max; $i++)
297     {
298       if (strlen($variants[$i]) > 0)
299       {
300         $variant .= $variant ? '_'.$variants[$i] : $variants[$i];
301         $catalogues[] = $catalogue.'.'.$variant;
302       }
303     }
304
305     return array_reverse($catalogues);
306   }
307
308   /**
309    * Retrieves catalogue details, array($cat_id, $variant, $count).
310    *
311    * @param string catalogue
312    * @return array catalogue details, array($cat_id, $variant, $count).
313    */
314   protected function getCatalogueDetails($catalogue = 'messages')
315   {
316     if (empty($catalogue))
317     {
318       $catalogue = 'messages';
319     }
320
321     $variant = $catalogue.'.'.$this->culture;
322
323     $name = mysql_real_escape_string($this->getSource($variant), $this->db);
324
325     $rs = mysql_query("SELECT cat_id FROM catalogue WHERE name = '{$name}'", $this->db);
326
327     if (mysql_num_rows($rs) != 1)
328     {
329       return false;
330     }
331
332     $cat_id = intval(mysql_result($rs, 0));
333
334     // first get the catalogue ID
335     $rs = mysql_query("SELECT COUNT(*) FROM trans_unit WHERE cat_id = {$cat_id}", $this->db);
336
337     $count = intval(mysql_result($rs, 0));
338
339     return array($cat_id, $variant, $count);
340   }
341
342   /**
343    * Updates the catalogue last modified time.
344    *
345    * @return boolean true if updated, false otherwise.
346    */
347   protected function updateCatalogueTime($cat_id, $variant)
348   {
349     $time = time();
350
351     $result = mysql_query("UPDATE catalogue SET date_modified = {$time} WHERE cat_id = {$cat_id}", $this->db);
352
353     if (!empty($this->cache))
354     {
355       $this->cache->clean($variant, $this->culture);
356     }
357
358     return $result;
359   }
360
361   /**
362    * Saves the list of untranslated blocks to the translation source.
363    * If the translation was not found, you should add those
364    * strings to the translation source via the <b>append()</b> method.
365    *
366    * @param string the catalogue to add to
367    * @return boolean true if saved successfuly, false otherwise.
368    */
369   function save($catalogue = 'messages')
370   {
371     $messages = $this->untranslated;
372
373     if (count($messages) <= 0)
374     {
375       return false;
376     }
377
378     $details = $this->getCatalogueDetails($catalogue);
379
380     if ($details)
381     {
382       list($cat_id, $variant, $count) = $details;
383     }
384     else
385     {
386       return false;
387     }
388
389     if ($cat_id <= 0)
390     {
391       return false;
392     }
393     $inserted = 0;
394
395     $time = time();
396
397     foreach ($messages as $message)
398     {
399       $count++;
400       $inserted++;
401       $message = mysql_real_escape_string($message, $this->db);
402       $statement = "INSERT INTO trans_unit
403         (cat_id,id,source,date_added) VALUES
404         ({$cat_id}, {$count},'{$message}',$time)";
405       mysql_query($statement, $this->db);
406     }
407     if ($inserted > 0)
408     {
409       $this->updateCatalogueTime($cat_id, $variant);
410     }
411
412     return $inserted > 0;
413   }
414
415   /**
416    * Deletes a particular message from the specified catalogue.
417    *
418    * @param string the source message to delete.
419    * @param string the catalogue to delete from.
420    * @return boolean true if deleted, false otherwise.
421    */
422   function delete($message, $catalogue = 'messages')
423   {
424     $details = $this->getCatalogueDetails($catalogue);
425     if ($details)
426     {
427       list($cat_id, $variant, $count) = $details;
428     }
429     else
430     {
431       return false;
432     }
433
434     $text = mysql_real_escape_string($message, $this->db);
435
436     $statement = "DELETE FROM trans_unit WHERE cat_id = {$cat_id} AND source = '{$message}'";
437     $deleted = false;
438
439     mysql_query($statement, $this->db);
440
441     if (mysql_affected_rows($this->db) == 1)
442     {
443       $deleted = $this->updateCatalogueTime($cat_id, $variant);
444     }
445
446     return $deleted;
447   }
448
449   /**
450    * Updates the translation.
451    *
452    * @param string the source string.
453    * @param string the new translation string.
454    * @param string comments
455    * @param string the catalogue of the translation.
456    * @return boolean true if translation was updated, false otherwise.
457    */
458   function update($text, $target, $comments, $catalogue = 'messages')
459   {
460     $details = $this->getCatalogueDetails($catalogue);
461     if ($details)
462     {
463       list($cat_id, $variant, $count) = $details;
464     }
465     else
466     {
467       return false;
468     }
469
470     $comments = mysql_real_escape_string($comments, $this->db);
471     $target = mysql_real_escape_string($target, $this->db);
472     $text = mysql_real_escape_string($text, $this->db);
473
474     $time = time();
475
476     $statement = "UPDATE trans_unit SET target = '{$target}', comments = '{$comments}', date_modified = '{$time}' WHERE cat_id = {$cat_id} AND source = '{$text}'";
477
478     $updated = false;
479
480     mysql_query($statement, $this->db);
481     if (mysql_affected_rows($this->db) == 1)
482     {
483       $updated = $this->updateCatalogueTime($cat_id, $variant);
484     }
485
486     return $updated;
487   }
488
489   /**
490    * Returns a list of catalogue as key and all it variants as value.
491    *
492    * @return array list of catalogues
493    */
494   function catalogues()
495   {
496     $statement = 'SELECT name FROM catalogue ORDER BY name';
497     $rs = mysql_query($statement, $this->db);
498     $result = array();
499     while($row = mysql_fetch_array($rs, MYSQL_NUM))
500     {
501       $details = explode('.', $row[0]);
502       if (!isset($details[1]))
503       {
504         $details[1] = null;
505       }
506
507       $result[] = $details;
508     }
509
510     return $result;
511   }
512 }
513
Note: See TracBrowser for help on using the browser.