<?php

/**
 * MessageSource_MySQL 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 Wei Zhuo <weizhuo[at]gmail[dot]com>
 * @version $Revision: 1.4 $  $Date: 2005/02/25 09:59:40 $
 * @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_MySQL class.
 * 
 * Retrive the message translation from a MySQL database.
 *
 * See the MessageSource::factory() method to instantiate this class.
 *
 * @author Xiang Wei Zhuo <weizhuo[at]gmail[dot]com>
 * @version v1.0, last update on Fri Dec 24 16:58:58 EST 2004
 * @package System.I18N.core
 */
class MessageSource_MySQL 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 MySQL.
	 * @param string MySQL 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() 
	{
		@mysql_close($this->db);
	}
	
	/**
	 * Connect to the MySQL datasource
	 * @return resource MySQL connection. 
	 * @throws Exception, connection and database errors.
	 */
	protected function connect()
	{
		/*static $conn;
		
		if(!is_null($conn))
			return $conn;
		*/
		$dsninfo = $this->dns;
		
     	if (isset($dsninfo['protocol']) && $dsninfo['protocol'] == 'unix') 
            $dbhost = ':' . $dsninfo['socket'];
        else 
        {
			$dbhost = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
            if (!empty($dsninfo['port'])) 
                $dbhost .= ':' . $dsninfo['port'];
        }
        $user = $dsninfo['username'];
        $pw = $dsninfo['password'];		
        
        $connect_function = 'mysql_connect';

        if ($dbhost && $user && $pw)
            $conn = @$connect_function($dbhost, $user, $pw);
        elseif ($dbhost && $user)
            $conn = @$connect_function($dbhost, $user);
        elseif ($dbhost)
            $conn = @$connect_function($dbhost);
        else
            $conn = false;        
                    
        if (empty($conn)) 
        {
        	throw new Exception('Error in connecting to '.$dsninfo);
        }
        
        if ($dsninfo['database']) 
        {
        	if (!@mysql_select_db($dsninfo['database'], $conn)) 
        		throw new Exception('Error in connecting database, dns:'.
        							$dsninfo);
        }
        else
        	throw new Exception('Please provide a database for message'.
        						' translation.');        					        
       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)
	{		
		$variant = mysql_escape_string($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 = '{$variant}' 
				ORDER BY id ASC";
			
		$rs = mysql_query($statement,$this->db);
			
		$result = array();
		
		while($row = mysql_fetch_array($rs,MYSQL_NUM))
		{
			$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)
	{
		$source = mysql_escape_string($source);

		$rs = mysql_query(
			"SELECT date_modified FROM catalogue WHERE name = '{$source}'",
			$this->db);
			
		$result = $rs ? intval(mysql_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)
	{
		$variant = mysql_escape_string ($variant);

		$rs = mysql_query( 
			"SELECT COUNT(*) FROM catalogue WHERE name = '{$variant}'",
			$this->db);
			
		$row = mysql_fetch_array($rs,MYSQL_NUM);
		
		$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 = mysql_escape_string($this->getSource($variant));	
				
		$rs = mysql_query("SELECT cat_id
					FROM catalogue WHERE name = '{$name}'", $this->db);
		
		if(mysql_num_rows($rs) != 1)
			return false;
		
		$cat_id = intval(mysql_result($rs,0));
		
		//first get the catalogue ID
		$rs = mysql_query(
			"SELECT count(msg_id)
				FROM trans_unit
				WHERE cat_id = {$cat_id}", $this->db);

		$count = intval(mysql_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 = mysql_query("UPDATE catalogue 
							SET date_modified = {$time}
							WHERE cat_id = {$cat_id}", $this->db);
			
		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++;
			$message = mysql_escape_string($message);
			$statement = "INSERT INTO trans_unit
				(cat_id,id,source,date_added) VALUES
				({$cat_id}, {$count},'{$message}',$time)";
			mysql_query($statement, $this->db);
		}
		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;
			
		$text = mysql_escape_string($message);
		
		$statement = "DELETE FROM trans_unit WHERE
						cat_id = {$cat_id} AND source = '{$message}'";
		$deleted = false;
				
		mysql_query($statement, $this->db);

		if(mysql_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;
		
		$comments = mysql_escape_string($comments);
		$target = mysql_escape_string($target);
		$text = mysql_escape_string($text);
		
		$time = time();
				
		$statement = "UPDATE trans_unit SET
						target = '{$target}',
						comments = '{$comments}',
						date_modified = '{$time}'
					WHERE cat_id = {$cat_id} 
						AND source = '{$text}'";
		
		$updated = false;
		
		mysql_query($statement, $this->db);
		if(mysql_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 = mysql_query($statement, $this->db);
		$result = array();
		while($row = mysql_fetch_array($rs,MYSQL_NUM))
		{
			$details = explode('.',$row[0]);
			if(!isset($details[1])) $details[1] = null;
			
			$result[] = $details;
		}
		return $result;
	}
	
}

?>