Development

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

You must first sign up to be able to contribute.

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

Revision 23810, 12.4 kB (checked in by Kris.Wallsmith, 5 years ago)

[1.3] set svn:eol-style property to native and svn:keywords property to Id on all .php files

  • Property svn:mime-type set to text/x-php
  • Property svn:eol-style set to native
  • Property svn:keywords set to Id
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 (!function_exists($connect_function))
162     {
163       throw new RuntimeException('The function mysql_connect() does not exist. Please confirm MySQL is enabled in php.ini');
164     }
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 $variant the catalogue name + variant
217    * @return array translation messages.
218    */
219   public 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 $source 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 $variant catalogue+variant
267    * @return boolean true if the catalogue+variant is in the database, false otherwise.
268    */
269   public 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    * Retrieves catalogue details, array($cat_id, $variant, $count).
284    *
285    * @param string $catalogue catalogue
286    * @return array catalogue details, array($cat_id, $variant, $count).
287    */
288   protected function getCatalogueDetails($catalogue = 'messages')
289   {
290     if (empty($catalogue))
291     {
292       $catalogue = 'messages';
293     }
294
295     $variant = $catalogue.'.'.$this->culture;
296
297     $name = mysql_real_escape_string($this->getSource($variant), $this->db);
298
299     $rs = mysql_query("SELECT cat_id FROM catalogue WHERE name = '{$name}'", $this->db);
300
301     if (mysql_num_rows($rs) != 1)
302     {
303       return false;
304     }
305
306     $cat_id = intval(mysql_result($rs, 0));
307
308     // first get the catalogue ID
309     $rs = mysql_query("SELECT COUNT(*) FROM trans_unit WHERE cat_id = {$cat_id}", $this->db);
310
311     $count = intval(mysql_result($rs, 0));
312
313     return array($cat_id, $variant, $count);
314   }
315
316   /**
317    * Updates the catalogue last modified time.
318    *
319    * @return boolean true if updated, false otherwise.
320    */
321   protected function updateCatalogueTime($cat_id, $variant)
322   {
323     $time = time();
324
325     $result = mysql_query("UPDATE catalogue SET date_modified = {$time} WHERE cat_id = {$cat_id}", $this->db);
326
327     if ($this->cache)
328     {
329       $this->cache->remove($variant.':'.$this->culture);
330     }
331
332     return $result;
333   }
334
335   /**
336    * Saves the list of untranslated blocks to the translation source.
337    * If the translation was not found, you should add those
338    * strings to the translation source via the <b>append()</b> method.
339    *
340    * @param string $catalogue the catalogue to add to
341    * @return boolean true if saved successfuly, false otherwise.
342    */
343   function save($catalogue = 'messages')
344   {
345     $messages = $this->untranslated;
346
347     if (count($messages) <= 0)
348     {
349       return false;
350     }
351
352     $details = $this->getCatalogueDetails($catalogue);
353
354     if ($details)
355     {
356       list($cat_id, $variant, $count) = $details;
357     }
358     else
359     {
360       return false;
361     }
362
363     if ($cat_id <= 0)
364     {
365       return false;
366     }
367     $inserted = 0;
368
369     $time = time();
370
371     foreach ($messages as $message)
372     {
373       $count++;
374       $inserted++;
375       $message = mysql_real_escape_string($message, $this->db);
376       $statement = "INSERT INTO trans_unit
377         (cat_id,id,source,date_added) VALUES
378         ({$cat_id}, {$count},'{$message}',$time)";
379       mysql_query($statement, $this->db);
380     }
381     if ($inserted > 0)
382     {
383       $this->updateCatalogueTime($cat_id, $variant);
384     }
385
386     return $inserted > 0;
387   }
388
389   /**
390    * Deletes a particular message from the specified catalogue.
391    *
392    * @param string $message   the source message to delete.
393    * @param string $catalogue the catalogue to delete from.
394    * @return boolean true if deleted, false otherwise.
395    */
396   function delete($message, $catalogue = 'messages')
397   {
398     $details = $this->getCatalogueDetails($catalogue);
399     if ($details)
400     {
401       list($cat_id, $variant, $count) = $details;
402     }
403     else
404     {
405       return false;
406     }
407
408     $text = mysql_real_escape_string($message, $this->db);
409
410     $statement = "DELETE FROM trans_unit WHERE cat_id = {$cat_id} AND source = '{$message}'";
411     $deleted = false;
412
413     mysql_query($statement, $this->db);
414
415     if (mysql_affected_rows($this->db) == 1)
416     {
417       $deleted = $this->updateCatalogueTime($cat_id, $variant);
418     }
419
420     return $deleted;
421   }
422
423   /**
424    * Updates the translation.
425    *
426    * @param string $text      the source string.
427    * @param string $target    the new translation string.
428    * @param string $comments  comments
429    * @param string $catalogue the catalogue of the translation.
430    * @return boolean true if translation was updated, false otherwise.
431    */
432   function update($text, $target, $comments, $catalogue = 'messages')
433   {
434     $details = $this->getCatalogueDetails($catalogue);
435     if ($details)
436     {
437       list($cat_id, $variant, $count) = $details;
438     }
439     else
440     {
441       return false;
442     }
443
444     $comments = mysql_real_escape_string($comments, $this->db);
445     $target = mysql_real_escape_string($target, $this->db);
446     $text = mysql_real_escape_string($text, $this->db);
447
448     $time = time();
449
450     $statement = "UPDATE trans_unit SET target = '{$target}', comments = '{$comments}', date_modified = '{$time}' WHERE cat_id = {$cat_id} AND source = '{$text}'";
451
452     $updated = false;
453
454     mysql_query($statement, $this->db);
455     if (mysql_affected_rows($this->db) == 1)
456     {
457       $updated = $this->updateCatalogueTime($cat_id, $variant);
458     }
459
460     return $updated;
461   }
462
463   /**
464    * Returns a list of catalogue as key and all it variants as value.
465    *
466    * @return array list of catalogues
467    */
468   function catalogues()
469   {
470     $statement = 'SELECT name FROM catalogue ORDER BY name';
471     $rs = mysql_query($statement, $this->db);
472     $result = array();
473     while($row = mysql_fetch_array($rs, MYSQL_NUM))
474     {
475       $details = explode('.', $row[0]);
476       if (!isset($details[1]))
477       {
478         $details[1] = null;
479       }
480
481       $result[] = $details;
482     }
483
484     return $result;
485   }
486 }
487
Note: See TracBrowser for help on using the browser.