<?php
/**
 * @version $Id: bfDBUtils.php 139 2010-01-03 20:45:41Z  $
 * @package Blue Flame Framework (bfFramework)
 * @copyright Copyright (C) 2003,2004,2005,2006,2007,2008,2009 Blue Flame IT Ltd. All rights reserved.
 * @license GNU General Public License
 * @link http://www.blueflameit.ltd.uk
 * @author Phil Taylor / Blue Flame IT Ltd.
 *
 * bfFramework is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * bfFramework is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this package.  If not, see http://www.gnu.org/licenses/
 */
defined ( '_JEXEC' ) or die ( 'Restricted access' );

define ( '_BF_MYSQL', '4.1' );

class bfDBUtils {
	
	/**
	 * The existing tables in the database
	 *
	 * @var array
	 */
	public $_existingTables = array ();
	
	/**
	 * The database prefix
	 *
	 * @var string
	 */
	public $_dbprefix = 'jos_';
	
	/**
	 * List of tables that we cant find and therefore need to install
	 *
	 * @var array
	 */
	public $_cantFindTables = array ();
	
	/**
	 * A list of tables that this component requires
	 *
	 * @var array
	 */
	public $_componentTables = array ();
	
	/**
	 * The array of sql queries that insert the default data for a newly created table
	 *
	 * @var array
	 */
	public $_componentDefaultData = array ();
	
	/**
	 * The new version numbers of the tables
	 *
	 * @var array
	 */
	public $_componentDBVersions = array ();
	
	/**
	 * PHP5 Constructor
	 *
	 */
	public function __construct() {
		global $mainframe;
		
		/* include xml2array */
		include_once (JPATH_ROOT . DS . 'administrator' . DS . 'components' . DS . $mainframe->get ( 'component' ) . DS . 'bfXML.php');
		
		/* get the dbo */
		$this->_db = bfCompat::getDBO ();
		
		/* get the database table prefix used on the joomla site */
		$this->_dbprefix = bfCompat::getdbPrefix ();
		
		/* get a list of the tables already in the mysql db */
		$this->_getCurrentTables ();
		
		/* get this components installation tables */
		$xml = new bfXml ( );
		$this->_componentTables = $xml->parse ( JPATH_ROOT . DS . 'components' . DS . $mainframe->get ( 'component' ) . DS . 'sql' . DS . 'install' . DS . 'tables.sql' );
		
		/* fix for if no file found */
		if (! is_array ( $this->_componentTables ))
			$this->_componentTables = array ();
			
		/* get version info */
		if (count ( $this->_componentTables )) {
			foreach ( $this->_componentTables as $k => $v ) {
				if (preg_match ( '/_version/', $k )) {
					/* remove version info from component tables array */
					unset ( $this->_componentTables [$k] );
					
					/* strip _version from the table name - only used in the XML */
					$k = str_replace ( '_version', '', $k );
					
					/* add table name and version number to array */
					$this->_componentDBVersions [$k] = $v;
				}
			}
		}
		/* load up the default data for tables into the array */
		$xml = new bfXml ( );
		$da = $xml->parse ( JPATH_ROOT . DS . 'components' . DS . $mainframe->get ( 'component' ) . DS . 'sql' . DS . 'install' . DS . 'data.sql' );
		$this->_componentDefaultData = $da;
	
	}
	
	/**
	 * I populate the class with a list of the current
	 * db tables
	 *
	 */
	public function _getCurrentTables() {
		$this->_db->setQuery ( 'SHOW TABLES' );
		$existingTables = $this->_db->LoadResultArray ();
		foreach ( $existingTables as $t ) {
			$this->_existingTables [$t] = $t;
		}
	}
	
	/**
	 * I check that all required tables are installed and if not I attempt to install them.
	 *
	 */
	public function checktables() {
		
		/* check for bfVersions db */
		if (! array_key_exists ( $this->_dbprefix . 'bfdbversions', $this->_existingTables )) {
			/* not found so install it */
			$this->_createbfDBVersionTable ();
		}
		
		$dberrors = 0;
		
		/**
		 * For every table we require:
		 *  1. Check its in the db already
		 *  2. If not try and install it (Which adds default data)
		 */
		foreach ( $this->_componentTables as $tableName => $sql ) {
			if (! array_key_exists ( $this->_dbprefix . $tableName, $this->_existingTables )) {
				$dberrors ++;
				$this->_cantFindTables [] = $tableName;
			}
		}
		
		if ($dberrors > 0) {
			$this->_installTables ();
		}
		
		$this->_checkForUpgradeToDBTables ();
	}
	
	function _checkForUpgradeToDBTables() {
		$needsUpgrade = false;
		
		/* for all our component tables, check db version and xml versioon */
		$currentTables = & $this->_componentTables;
		$requiredVersions = & $this->_componentDBVersions;
		foreach ( $currentTables as $tableName => $sql ) {
			$existingVer = $this->getTableVersionFromDB ( $tableName );
			$latestVer = $requiredVersions [$tableName];
			if ($latestVer > $existingVer) {
				$needsUpgrade = true;
			}
		}
		
		if ($needsUpgrade === true) {
			echo 'Processing Upgrade Tasks ... <br />';
			$this->_processUpgradeTasks ();
		}
	}
	
	function _processUpgradeTasks() {
		
		$needsUpgrade = array ();
		$db = & bfCompat::getDBO ();
		/* for all our component tables, check db version and xml version */
		$currentTables = & $this->_componentTables;
		$requiredVersions = & $this->_componentDBVersions;
		foreach ( $currentTables as $tableName => $sql ) {
			$existingVer = $this->getTableVersionFromDB ( $tableName );
			$latestVer = $requiredVersions [$tableName];
			
			if (! $existingVer) {
				$existingVer = $this->_populatedbversion ( $tableName );
			}
			
			if ($latestVer > $existingVer) {
				$needsUpgrade [] = array ('tablename' => $tableName, 'existingversion' => $existingVer );
			}
		
		}
		if (count ( $needsUpgrade )) {
			global $mainframe;
			/* get this components installation tables */
			$xml = new bfXml ( );
			$tasks = $xml->parse ( JPATH_ROOT . DS . 'components' . DS . $mainframe->get ( 'component' ) . DS . 'sql' . DS . 'upgrade' . DS . 'upgrade.sql' );
			foreach ( $needsUpgrade as $table ) {
				if (! is_array ( $tasks ['task'] ))
					continue;
				foreach ( $tasks ['task'] as $task ) {
					if ($task ['tablename'] == $table ['tablename']) {
						if ($task ['applytoversion'] == $table ['existingversion']) {
							$sqls = explode ( '; ', $task ['sql'] );
							foreach ( $sqls as $sql ) {
								$db->setQuery ( $sql );
								//																								echo $db->getQuery();
								$db->query ();
								//echo $db->getErrorMsg ();
							}
							
							$this->updateVerinDb ( $table ['tablename'], $task ['provides'] );
							echo '<br />' . $table ['tablename'] . ' upgraded!... ';
						
						}
					}
				}
			}
		}
		
		die ( bfText::_ ( 'Database tables have been auto upgraded - please refresh this page to continue' ) );
	}
	
	function getTableVersionFromDB($tableName) {
		global $mainframe;
		$db = bfCompat::getDBO ();
		$db->SetQuery ( 'SELECT version FROM #__bfdbversions WHERE component = \'' . $mainframe->get ( 'component' ) . '\' AND tablename = \'' . $tableName . '\'' );
		$ver = $db->LoadResult ();
		return $ver;
	}
	
	function checkComponentLink() {
		global $mainframe;
		$sn = str_replace ( 'com_', '', $mainframe->get ( 'component' ) );
		$db = bfCompat::getDBO ();
		$registry = bfRegistry::getInstance ( $mainframe->get ( 'component' ) );
		
		$db->setQuery ( "select count(*) FROM `#__components` WHERE `option` ='" . $mainframe->get ( 'component' ) . "'" );
		$coutn = $db->LoadResult ();
		if ($coutn > 1) {
			$db->setQuery ( "DELETE FROM `#__components` WHERE `option` ='" . $mainframe->get ( 'component' ) . "' LIMIT " . $coutn - 1 );
			$db->query ();
		}
		
		if (_BF_PLATFORM == 'JOOMLA1.5') {
			$sql = "
						update `#__components` set

			`name` = '" . $registry->getValue ( 'Component.Title' ) . ' v' . $registry->getValue ( 'Component.Version' ) . "',
			`link` = 'option=com_" . $sn . "',
			`menuid` = 0 ,
			`parent` = 0 ,
			`admin_menu_link` = 'option=com_" . $sn . "' ,
			`admin_menu_alt` = 'Configure " . $registry->getValue ( 'Component.Title' ) . " v" . $registry->getValue ( 'Component.Version' ) . rand ( 500, 800 ) . "',
			`option` =  'com_" . $sn . "',
			`ordering` = 0 ,
			`admin_menu_img` = '../" . bfCompat::mambotsfoldername () . "/system/blueflame/view/images/menulogo.gif',
			`iscore` =0 ,
			`enabled` =1
			WHERE `option` = 'com_" . $sn . "';
			";
		} else {
			$db->setQuery ( "DELETE FROM `#__components` WHERE `option` ='" . $mainframe->get ( 'component' ) . "'" );
			$db->query ();
			$sql = "INSERT INTO `#__components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`,
					`admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`) VALUES
					('', '" . $registry->getValue ( 'Component.Title' ) . ' v' . $registry->getValue ( 'Component.Version' ) . "', 'option=com_" . $sn . "', 0, 0, 'option=com_" . $sn . "', 'Configure " . $registry->getValue ( 'Component.Title' ) . ' v' . $registry->getValue ( 'Component.Version' ) . "', 'com_" . $sn . "', 0, '../" . bfCompat::mambotsfoldername () . "/system/blueflame/view/images/menulogo.gif', 0, '');
					";
		}
		$db->setQuery ( $sql );
		$db->query ();
	}
	
	function migrate() {
		global $mainframe;
		$db = bfCompat::getDBO ();
		$results = array ();
		$errors = array ();
		
		/* clean up first - we did warn them! */
		$results [] = '<h1>' . bfText::_ ( 'Clear Current Tables' ) . '</h1><ul class="bfsubmenu">';
		$xml = new bfXml ( );
		$tasks = $xml->parse ( JPATH_ROOT . DS . 'components' . DS . $mainframe->get ( 'component' ) . DS . 'sql' . DS . 'cleanup' . DS . 'removeall.sql' );
		
		foreach ( $tasks ['task'] as $task ) {
			$db->setQuery ( $task ['sql'] );
			$ret = $db->query ();
			if ($ret == 0) {
				/* error */
				$results [] = '<li class="error">' . bfText::_ ( 'ERROR!' ) . ' - ' . $task ['title'] . '<br />' . $db->getErrorMsg () . '</li>';
			} else {
				$results [] = '<li class="noerror">' . $task ['title'] . ' - ' . bfText::_ ( 'Done' ) . '!</li>';
			}
		}
		$results [] = '</ul>';
		/* run migration sql */
		
		/* migration */
		$results [] = '<h1>Run Migration Tasks</h1><ul class="bfsubmenu">';
		$xml = new bfXml ( );
		$tasks = $xml->parse ( JPATH_ROOT . DS . 'components' . DS . $mainframe->get ( 'component' ) . DS . 'sql' . DS . 'migrate' . DS . 'migrate.sql' );
		foreach ( $tasks ['task'] as $task ) {
			$db->setQuery ( $task ['sql'] );
			$ret = $db->query ();
			if ($ret == 0) {
				/* error */
				$results [] = '<li class="error">' . bfText::_ ( 'ERROR!' ) . ' - ' . $task ['title'] . '<br />' . $db->getErrorMsg () . '</li>';
			} else {
				$results [] = '<li class="noerror">' . $task ['title'] . ' - ' . bfText::_ ( 'Done!' ) . '</li>';
			}
		}
		$results [] = '</ul>';
		
		/* install any missing/new tables */
		$this->checktables ();
		
		return array ($results, $errors );
	}
	
	function updateVerinDb($tablename, $currentVer) {
		global $mainframe;
		$db = bfCompat::getDBO ();
		$db->setQuery ( "UPDATE #__bfdbversions SET version = '" . $currentVer . "' WHERE component = '" . $mainframe->get ( 'component' ) . "' AND tablename = '" . $tablename . "'" );
		$db->query ();
	}
	
	/**
	 * I install missing tables required by this component
	 *
	 */
	function _installTables() {
		foreach ( $this->_cantFindTables as $installThisTable ) {
			if (array_key_exists ( $installThisTable, $this->_componentTables )) {
				$sql = $this->_componentTables [$installThisTable];
				if (is_array ( $sql )) {
					$sql = implode ( '', $this->_componentTables [$installThisTable] );
				}
				
				if (_BF_MYSQL == '4.0') {
					$sql = str_replace ( 'collate utf8_bin', ' ', $sql );
					$sql = str_replace ( 'DEFAULT CHARSET=utf8 COLLATE=utf8_bin', ' ', $sql );
					$sql = str_replace ( 'character set utf8 collate utf8_bin', ' ', $sql );
					$sql = str_replace ( 'character set utf8', '', $sql );
					$sql = str_replace ( 'ENGINE=MyISAM', '', $sql );
				}
				
				$this->_db->setQuery ( $sql );
				$this->_db->query ();
				if ($this->_db->getErrorMsg ()) {
					die ( $this->_db->getErrorMsg () );
				}
				$this->_getCurrentTables ();
				$this->_addDefaultData ( $installThisTable );
				$this->_addtobfVersionsDB ( $installThisTable, $this->_componentDBVersions [$installThisTable] );
			
			}
		}
	}
	
	function _populatedbversion($table) {
		return $this->_addtobfVersionsDB ( $table, $this->_componentDBVersions [$table] );
	}
	
	function makeCompatible($sql) {
		if (_BF_MYSQL == '4.0') {
			$sql = str_replace ( 'collate utf8_bin', ' ', $sql );
			$sql = str_replace ( 'COLLATE utf8_bin', ' ', $sql );
			$sql = str_replace ( 'DEFAULT CHARSET=utf8 COLLATE=utf8_bin', ' ', $sql );
			$sql = str_replace ( 'default charset=utf8 collate=utf8_bin', ' ', $sql );
			$sql = str_replace ( 'character set utf8 collate utf8_bin', ' ', $sql );
			$sql = str_replace ( 'CHARACTER SET utf8 COLLATE utf8_bin', ' ', $sql );
			$sql = str_replace ( 'character set utf8', '', $sql );
			$sql = str_replace ( 'CHARACTER SET utf8', '', $sql );
			$sql = str_replace ( 'ENGINE=MyISAM', '', $sql );
		}
		return $sql;
	}
	
	/**
	 * I add an entry to the bfversiondb to keeptrack of database version numbers
	 *
	 * @param string $tablename The table name to add
	 * @param float $version The version number
	 */
	function _addtobfVersionsDB($tablename, $version) {
		global $mainframe;
		$sql = "DELETE FROM #__bfdbversions WHERE tablename = '" . $tablename . "'";
		$this->_db->setQuery ( $sql );
		$this->_db->query ();
		$sql = "INSERT INTO `#__bfdbversions` ( `tablename` , `version` , `component` )
				VALUES (
				'" . $tablename . "', '" . $version . "', '" . $mainframe->get ( 'component' ) . "'
				);";
		$this->_db->setQuery ( $sql );
		$this->_db->query ();
		return $version;
	}
	
	/**
	 * I install default data to a newly created table
	 *
	 * @param string $installThisTable The table to populate
	 */
	function _addDefaultData($installThisTable = null) {
		
		/* check database table exists */
		if (array_key_exists ( $installThisTable, $this->_componentTables )) {
			if (@array_key_exists ( $installThisTable, $this->_componentDefaultData )) {
				$sql = $this->_componentDefaultData [$installThisTable];
				if (is_array ( $sql )) {
					foreach ( $this->_componentDefaultData [$installThisTable] as $sql ) {
						$this->_db->setQuery ( $sql );
						$this->_db->query ();
						echo $this->_db->getErrorMsg ();
					}
				} else {
					$this->_db->setQuery ( $sql );
					$this->_db->query ();
					echo $this->_db->getErrorMsg ();
				}
			}
		}
	}
	
	/**
	 * I create the bfDBVersions database Table.
	 *
	 */
	function _createbfDBVersionTable() {
		$sql = 'CREATE TABLE `#__bfdbversions` (
				`tablename` VARCHAR( 255 ) NOT NULL ,
				`version` VARCHAR( 255 ) NOT NULL ,
				`component` VARCHAR( 255 ) NOT NULL
				)';
		$this->_db->setQuery ( $sql );
		$this->_db->query ();
	}
}