Development

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

You must first sign up to be able to contribute.

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

Revision 9128, 12.2 kB (checked in by Carl.Vondrick, 7 years ago)

1.1: fixed @param phpdoc to fit specs in i18n (refs #2991)

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