PostgreSQL MessageSource for TGlobalization

[gevik]

Please consider the following patch which implements MessageSource? for PostgreSQL. Regards, Gevik. gevik[at]xs4all[dot]nl

for testing: run the postgresql.sql schema script from I18N.schema first then add the following in application.xml:

<module id="globalization" class="TGlobalization" Culture="..." DefaultCulture="...">
    <translation type="PostgreSQL"
        source="pgsql://postgres:postgres@localhost/testdb"
        marker="@@"
        autosave="true" cache="false" />
</module> 

Patch:

Index: I18N/core/MessageSource.php
===================================================================
--- I18N/core/MessageSource.php	(revision 2464)
+++ I18N/core/MessageSource.php	(working copy)
@@ -125,7 +125,7 @@
 	 */
 	static function &factory($type, $source='.', $filename='')
 	{
-		$types = array('XLIFF', 'SQLite', 'MySQL', 'gettext');
+		$types = array('XLIFF', 'SQLite', 'MySQL', 'gettext','PostgreSQL');
 		
 		if(empty($filename) && in_array($type, $types) == false)
 			throw new Exception('Invalid type "'.$type.'", valid types are '.
Index: I18N/core/MessageSource_PostgreSQL.php
===================================================================
--- I18N/core/MessageSource_PostgreSQL.php	(revision 0)
+++ I18N/core/MessageSource_PostgreSQL.php	(revision 0)
@@ -0,0 +1,389 @@
+<?php
+
+/**
+ * MessageSource_PostgreSQL class file.
+ *
+ * This program is free software; you can redistribute it and/or modify
+ * it under the terms of the BSD License.
+ *
+ * Copyright(c) 2004 by Qiang Xue. All rights reserved.
+ *
+ * To contact the author write to {@link mailto:qiang.xue@gmail.com Qiang Xue}
+ * The latest version of PRADO can be obtained from:
+ * {@link http://prado.sourceforge.net/}
+ *
+ * @author Gevik babakhani <gevik[at]xs4all[dot]nl>
+ * @version $Revision: 1.0 $  $Date: 2008/06/01 12:00:00 $
+ * @package System.I18N.core
+ */
+
+/**
+ * Get the MessageSource class file.
+ */
+require_once(dirname(__FILE__).'/MessageSource.php');
+
+/**
+ * Get the I18N utility file, contains the DSN parser.
+ */
+require_once(dirname(__FILE__).'/util.php');
+
+/**
+ * MessageSource_PostgreSQL class.
+ *
+ * Retrive the message translation from a PostgreSQL database.
+ *
+ * See the MessageSource::factory() method to instantiate this class.
+ *
+ * @author Gevik babakhani <gevik[at]xs4all[dot]nl>
+ * @version $Revision: 1.0 $  $Date: 2008/06/01 12:00:00 $
+ * @package System.I18N.core
+ */
+class MessageSource_PostgreSQL extends MessageSource
+{
+	/**
+	 * The datasource string, full DSN to the database.
+	 * @var string
+	 */
+	protected $source;
+
+	/**
+	 * The DSN array property, parsed by PEAR's DB DSN parser.
+	 * @var array
+	 */
+	protected $dns;
+
+	/**
+	 * A resource link to the database
+	 * @var db
+	 */
+	protected $db;
+	/**
+	 * Constructor.
+	 * Create a new message source using PostgreSQL.
+	 * @param string PostgreSQL datasource, in PEAR's DB DSN format.
+	 * @see MessageSource::factory();
+	 */
+	function __construct($source)
+	{
+		$this->source = (string)$source;
+		$this->dns = parseDSN($this->source);
+		$this->db = $this->connect();
+	}
+
+	/**
+	 * Destructor, close the database connection.
+	 */
+	function __destruct()
+	{
+		@pg_close($this->db);
+	}
+
+	/**
+	 * Connect to the PostgreSQL datasource
+	 * @return resource PostgreSQL connection.
+	 * @throws Exception, connection and database errors.
+	 */
+	protected function connect()
+	{
+		
+		/* we are going to recreate a PostgreSQL connection string
+		 * from PEAR's format;
+		 */		 
+		$pg_conn = array();
+		
+		$dsninfo = $this->dns;
+
+		/* database host*/
+		$pg_conn['host'] = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
+		$pg_conn['port'] = empty($dsninfo['port']) ? "5432" : $dsninfo['port'];
+		$pg_conn['user'] = $dsninfo['username'];
+		$pg_conn['password'] = $dsninfo['password'];
+		$pg_conn['dbname'] = $dsninfo['database'];
+		
+		
+		/* creating a PostgreSQL connection string */
+		foreach($pg_conn as $key => $value)
+			$pg_conn_str .= "$key=$value "; /* dont forget the space here */	
+			
+        $connect_function = 'pg_connect';
+        
+        /* because there is equivalent mysql_select_db in PostgreSQL
+         * we try to connect without supressing the connection errors.
+         * If any errors happen during connection, Prado will throw an Exception
+         * automatically
+         */
+		$conn = $connect_function($pg_conn_str);
+		
+       return $conn;
+	}
+
+	/**
+	 * Get the database connection.
+	 * @return db database connection.
+	 */
+	public function connection()
+	{
+		return $this->db;
+	}
+
+	/**
+	 * Get an array of messages for a particular catalogue and cultural
+	 * variant.
+	 * @param string the catalogue name + variant
+	 * @return array translation messages.
+	 */
+	protected function &loadData($variant)
+	{
+		$statement =
+			"SELECT t.id, t.source, t.target, t.comments
+				FROM trans_unit t, catalogue c
+ 				WHERE c.cat_id =  t.cat_id
+					AND c.name = $1
+				ORDER BY id ASC";
+
+		$rs = pg_query_params($this->db,$statement,array($variant));
+
+		$result = array();
+
+		while($row = pg_fetch_array($rs))
+		{
+			$source = $row[1];
+			$result[$source][] = $row[2]; //target
+			$result[$source][] = $row[0]; //id
+			$result[$source][] = $row[3]; //comments
+		}
+		
+		return $result;
+	}
+
+	/**
+	 * Get the last modified unix-time for this particular catalogue+variant.
+	 * We need to query the database to get the date_modified.
+	 * @param string catalogue+variant
+	 * @return int last modified in unix-time format.
+	 */
+	protected function getLastModified($source)
+	{
+		$rs = pg_query_params($this->db,
+			"SELECT date_modified FROM catalogue WHERE name = $1",
+			array($source));
+
+		$result = $rs ? intval(pg_fetch_result($rs,0)) : 0;
+
+		return $result;
+	}
+
+	/**
+	 * Check if a particular catalogue+variant exists in the database.
+	 * @param string catalogue+variant
+	 * @return boolean true if the catalogue+variant is in the database,
+	 * false otherwise.
+	 */
+	protected function isValidSource($variant)
+	{
+		$rs = pg_query_params($this->db,
+			"SELECT COUNT(*) FROM catalogue WHERE name = $1",array($variant));
+
+		$row = pg_fetch_array($rs);
+
+		$result = $row && $row[0] == '1';
+
+		return $result;
+	}
+
+	/**
+	 * Get all the variants of a particular catalogue.
+	 * @param string catalogue name
+	 * @return array list of all variants for this catalogue.
+	 */
+	protected function getCatalogueList($catalogue)
+	{
+		$variants = explode('_',$this->culture);
+
+		$catalogues = array($catalogue);
+
+		$variant = null;
+
+		for($i = 0, $k = count($variants); $i < $k; ++$i)
+		{
+			if(isset($variants[$i]{0}))
+			{
+				$variant .= ($variant)?'_'.$variants[$i]:$variants[$i];
+				$catalogues[] = $catalogue.'.'.$variant;
+			}
+		}
+		return array_reverse($catalogues);
+	}
+
+	/**
+	 * Retrive catalogue details, array($cat_id, $variant, $count).
+	 * @param string catalogue
+	 * @return array catalogue details, array($cat_id, $variant, $count).
+	 */
+	private function getCatalogueDetails($catalogue='messages')
+	{
+		if(empty($catalogue))
+			$catalogue = 'messages';
+
+		$variant = $catalogue.'.'.$this->culture;
+
+		$name = $this->getSource($variant);
+
+		$rs = pg_query_params($this->db,"SELECT cat_id
+					FROM catalogue WHERE name = $1", array($name));
+
+		if(pg_num_rows($rs) != 1)
+			return false;
+
+		$cat_id = intval(pg_fetch_result($rs,0));
+
+		//first get the catalogue ID
+		$rs = pg_query_params($this->db,
+			"SELECT count(msg_id)
+				FROM trans_unit
+				WHERE cat_id = $1", array($cat_id));
+
+		$count = intval(pg_fetch_result($rs,0));
+
+		return array($cat_id, $variant, $count);
+	}
+
+	/**
+	 * Update the catalogue last modified time.
+	 * @return boolean true if updated, false otherwise.
+	 */
+	private function updateCatalogueTime($cat_id, $variant)
+	{
+		$time = time();
+
+		$result = pg_query_params($this->db,"UPDATE catalogue
+							SET date_modified = $1
+							WHERE cat_id = $2",array($time,$cat_id));
+
+		if(!empty($this->cache))
+			$this->cache->clean($variant, $this->culture);
+
+		return $result;
+	}
+
+	/**
+	 * Save the list of untranslated blocks to the translation source.
+	 * If the translation was not found, you should add those
+	 * strings to the translation source via the <b>append()</b> method.
+	 * @param string the catalogue to add to
+	 * @return boolean true if saved successfuly, false otherwise.
+	 */
+	function save($catalogue='messages')
+	{
+		$messages = $this->untranslated;
+
+		if(count($messages) <= 0) return false;
+
+		$details = $this->getCatalogueDetails($catalogue);
+
+		if($details)
+			list($cat_id, $variant, $count) = $details;
+		else
+			return false;
+
+		if($cat_id <= 0) return false;
+		$inserted = 0;
+
+		$time = time();
+		
+		foreach($messages as $message)
+		{
+			$count++; $inserted++;
+			$statement = "INSERT INTO trans_unit
+				(cat_id,id,source,date_added) VALUES
+				($1, $2,$3,$4)";
+			pg_query_params($this->db,$statement,array($cat_id,$count,$message,$time));
+		}
+		if($inserted > 0)
+			$this->updateCatalogueTime($cat_id, $variant);
+
+		return $inserted > 0;
+	}
+
+	/**
+	 * Delete a particular message from the specified catalogue.
+	 * @param string the source message to delete.
+	 * @param string the catalogue to delete from.
+	 * @return boolean true if deleted, false otherwise.
+	 */
+	function delete($message, $catalogue='messages')
+	{
+		$details = $this->getCatalogueDetails($catalogue);
+		if($details)
+			list($cat_id, $variant, $count) = $details;
+		else
+			return false;
+
+		$statement = "DELETE FROM trans_unit WHERE
+						cat_id = $1 AND source = $2";
+		$deleted = false;
+
+		pg_query_params($this->db,$statement, array($cat_id,$message));
+
+		if(pg_affected_rows($this->db) == 1)
+			$deleted = $this->updateCatalogueTime($cat_id, $variant);
+
+		return $deleted;
+	}
+
+	/**
+	 * Update the translation.
+	 * @param string the source string.
+	 * @param string the new translation string.
+	 * @param string comments
+	 * @param string the catalogue of the translation.
+	 * @return boolean true if translation was updated, false otherwise.
+	 */
+	function update($text, $target, $comments, $catalogue='messages')
+	{
+		$details = $this->getCatalogueDetails($catalogue);
+		if($details)
+			list($cat_id, $variant, $count) = $details;
+		else
+			return false;
+
+		$time = time();
+		
+		$statement = "UPDATE trans_unit SET
+						target = $1,
+						comments = $2,
+						date_modified = $3
+					WHERE cat_id = $4
+						AND source = $5";
+
+		$updated = false;
+
+		pg_query_params($this->db,$statement,array($target,$comments,$time,$cat_id,$text));
+		if(pg_affected_rows($this->db) == 1)
+			$updated = $this->updateCatalogueTime($cat_id, $variant);
+
+		return $updated;
+	}
+
+	/**
+	 * Returns a list of catalogue as key and all it variants as value.
+	 * @return array list of catalogues
+	 */
+	function catalogues()
+	{
+		$statement = 'SELECT name FROM catalogue ORDER BY name';
+		$rs = pg_query_params($this->db,$statement);
+		$result = array();
+		while($row = pg_fetch_array($rs))
+		{
+			$details = explode('.',$row[0]);
+			if(!isset($details[1])) $details[1] = null;
+
+			$result[] = $details;
+		}
+		return $result;
+	}
+
+}
+
+?>
\ No newline at end of file
Index: I18N/schema/postgresql.sql
===================================================================
--- I18N/schema/postgresql.sql	(revision 0)
+++ I18N/schema/postgresql.sql	(revision 0)
@@ -0,0 +1,33 @@
+
+-- Table structure for table catalogue
+
+CREATE TABLE catalogue (
+  cat_id serial NOT NULL primary key,
+  name varchar(100) NOT NULL default '',
+  source_lang varchar(100) NOT NULL default '',
+  target_lang varchar(100) NOT NULL default '',
+  date_created int NOT NULL default 0,
+  date_modified int NOT NULL default 0,
+  author varchar(255) NOT NULL default ''
+);
+
+-- Dumping data for table catalogue
+
+INSERT INTO catalogue VALUES (nextval('catalogue_cat_id_seq'), 'messages1', '', '', 0, 0, '');
+INSERT INTO catalogue VALUES (nextval('catalogue_cat_id_seq'), 'messages.en', '', '', 0, 0, '');
+INSERT INTO catalogue VALUES (nextval('catalogue_cat_id_seq'), 'messages.en_AU', '', '', 0, 0, '');
+
+-- Table structure for table trans_unit
+
+CREATE TABLE trans_unit (
+  msg_id serial NOT NULL primary key,
+  cat_id int NOT NULL default 1,
+  id varchar(255) NOT NULL default '',
+  source text NOT NULL,
+  target text NOT NULL default '',
+  comments text NOT NULL default '',
+  date_added int NOT NULL default 0,
+  date_modified int NOT NULL default 0,
+  author varchar(255) NOT NULL default '',
+  translated smallint NOT NULL default 0
+);
\ No newline at end of file

[gevik]

Please discard this ticket. A new development is sent to: http://www.pradosoft.com/forum/index.php/topic,10309.0.html

[gevik]

new patch version submited to forum. http://www.pradosoft.com/forum/index.php/topic,10311.0.html

[tof06]

MessageSource?_Database, which uses a TDbConnection, is preferred over specific MessageSource?_MySql & MessageSource?_PgSql.