<?php
/**
* Update script
* Test and version upgrade
* 
* @package VFront
* @author Mario Marcello Verona <marcelloverona@gmail.com>
* @copyright 2007-2010 Mario Marcello Verona
* @version 0.96d $Id: update.php 880 2010-12-14 12:43:47Z marciuz $
* @license http://www.gnu.org/licenses/gpl.html GNU Public License
* @todo Verificare con utility se se operazioni sono eseguibili dall'autore, in caso contrario segnalare le operazioni da compiere
*/

require_once("../inc/conn.php");
require_once("../inc/layouts.php");
require_once("../inc/func.comuni.php");

proteggi(3);


function check_privileges($priv){
	
	global  $vmsql, $vmreg, $db1;
	
	$GRANTEE=$vmsql->escape("'".$db1['user']."'@'".$db1['host']."'");
	
	if($db1['dbtype']=='mysql'){
		$sql="SELECT PRIVILEGE_TYPE FROM information_schema.SCHEMA_PRIVILEGES 
			  WHERE TABLE_SCHEMA='".$db1['frontend']."' AND GRANTEE='$GRANTEE'";
		
		$q=@$vmsql->query($sql);
		
		if(@$vmsql->num_rows($q)>0){
			return true;
		}
		else{
			return false;
		}
	}
	else{
		
		// No check for Postgres
		return false;
	}
	
}



##################################################
#
#	VERSION 0.95
#
#


// TEST VERSION < 0.95
function update_test_090(){
	
	global  $vmsql, $vmreg, $db1;
	
	$q_test=$vmsql->query("SELECT CHARACTER_MAXIMUM_LENGTH 
						FROM information_schema.COLUMNS
						WHERE TABLE_SCHEMA = '{$db1['frontend']}'
						AND TABLE_NAME = 'registro_tab'
						AND COLUMN_NAME = 'orderby'");
						
	if($vmsql->num_rows($q_test)==0){
		
		list($length)=$vmsql->fetch_row($q_test);
		
		if($length<255){
			return true;
		}
		else{
			return false;
		}
	}
	else return false;
	
}

// EXEC VERSION < 0.95
function update_exec_090(){
	
	global $db1;
	
	$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}log CHANGE op op ENUM( 'insert', 'update', 'delete', 'select', 'sconosciuta', 'ripristino', 'duplicazione', 'import' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ;";
	$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}registro_tab ADD in_import TINYINT( 1 ) UNSIGNED NULL AFTER in_export ;";
	$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}registro_tab CHANGE orderby orderby VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ;";
	$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}registro_tab CHANGE orderby_sort orderby_sort VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT 'ASC';";
	$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}stat ADD settings TEXT COMMENT 'Impostazioni avanzate del grafico';";
	
	return _update_exec('0.90',$sql_up);
}



// TEST VERSION 0.95
function update_test_095(){
	
	global $vmreg, $db1;

	$q_test=$vmreg->query("SELECT * FROM {$db1['frontend']}{$db1['sep']}variabili WHERE variabile='layout'");
	
	if($vmreg->num_rows($q_test)==0){
		return true;
	}
	else return false;
}


// EXEC VERSION 0.95
function update_exec_095(){
	
	global $db1;

	$sql_up[]="INSERT INTO {$db1['frontend']}{$db1['sep']}variabili (variabile, gid, valore, descrizione, tipo_var) VALUES ('layout',0,'default','Color theme','string');";

	return _update_exec('0.95',$sql_up);
}









##################################################
#
#	VERSION 0.95a
#
#


// TEST VERSION 0.95a
function update_test_095a(){
	
	global $db1, $vmsql, $vmreg;
	
	$q_test=$vmsql->query("SELECT *
						FROM information_schema.COLUMNS
						WHERE TABLE_SCHEMA = '{$db1['frontend']}'
						AND TABLE_NAME = 'registro_col'
						AND COLUMN_NAME = 'in_line'");
						
	if($vmsql->num_rows($q_test)==0){
		return true;
	}
	else return false;
}


// EXEC VERSION 0.95a
function update_exec_095a(){
	
	global $db1;

	
	if($db1['dbtype']=='mysql'){
	
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}allegato  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}gruppo  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}link  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}log  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}recordlock  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}registro_col  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}registro_submask  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}registro_submask_col  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}registro_tab  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}utente  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}variabili  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}xml_rules  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
	
		$sql_up[]="INSERT INTO {$db1['frontend']}{$db1['sep']}variabili (variabile, gid, valore, descrizione, tipo_var) VALUES ('alert_login_default',0,'1','Mostra agli utenti l\'avviso se sono presenti nel gruppo di default','bool');";
		$sql_up[]="INSERT INTO {$db1['frontend']}{$db1['sep']}variabili (variabile, gid, valore, descrizione, tipo_var) VALUES ('alert_config',0,'1','Mostra all\'admin l\'avviso in home page se è presente qualche errore nella configurazione','bool');";
		$sql_up[]="INSERT INTO {$db1['frontend']}{$db1['sep']} variabili (variabile, gid, valore, descrizione, tipo_var) VALUES ('show_comment_in_table', '0', '0', 'Mostra il commento della tabella nella maschera di inserimento dati', 'bool');";
	
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}registro_col ADD in_line TINYINT(1) NULL AFTER in_table;";
	
		
	}
	else{
		
		$sql_up[]="INSERT INTO {$db1['frontend']}{$db1['sep']}variabili (variabile, gid, valore, descrizione, tipo_var) VALUES ('alert_login_default',0,'1','Mostra agli utenti l\'avviso se sono presenti nel gruppo di default','bool');";
		$sql_up[]="INSERT INTO {$db1['frontend']}{$db1['sep']}variabili (variabile, gid, valore, descrizione, tipo_var) VALUES ('alert_config',0,'1','Mostra all\'admin l\'avviso in home page se è presente qualche errore nella configurazione','bool');";
		$sql_up[]="INSERT INTO {$db1['frontend']}{$db1['sep']}variabili (variabile, gid, valore, descrizione, tipo_var) VALUES ('show_comment_in_table', 0, '0', 'Mostra il commento della tabella nella maschera di inserimento dati', 'bool');";
	
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}registro_col ADD in_line smallint NULL ;";
	}
	
	
	return _update_exec('0.95a',$sql_up);
}




###########################################
#
#	VERSION 0.95b
#	// change in CONF
#




###########################################
#
#	VERSION 0.95c
#






// TEST VERSION 0.95c
function update_test_095c(){
	
	global $db1, $vmsql, $vmreg;
	
	$q_test=$vmsql->query("SELECT *
						FROM information_schema.COLUMNS
						WHERE TABLE_SCHEMA = '{$db1['frontend']}'
						AND TABLE_NAME = 'variabili'
						AND COLUMN_NAME = 'pubvar'");
						
	if($vmsql->num_rows($q_test)==0){
		return true;
	}
	else return false;
}



// EXEC VERSION 0.95a
function update_exec_095c(){
	
	global $db1;
	
	if($db1['dbtype']=='mysql'){
	
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}variabili ADD pubvar TINYINT(1) UNSIGNED NOT NULL DEFAULT '1';";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}variabili CHANGE valore valore TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;";
		$sql_up[]="UPDATE {$db1['frontend']}{$db1['sep']}variabili SET pubvar=0 WHERE variabile='layout';";
		
	}
	else{
		
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}variabili ADD pubvar smallint NOT NULL DEFAULT 1;";
		$sql_up[]="UPDATE {$db1['frontend']}{$db1['sep']}variabili SET pubvar=0 WHERE variabile='layout';";
	}
	
	return _update_exec('0.95c',$sql_up);
	
}







###########################################
#
#	VERSION 0.95d -- no changes in DB
#






###########################################
#
#	VERSION 0.95e
#




// TEST VERSION 0.95c
function update_test_095e(){
	
	global $db1, $vmsql, $vmreg;
	
	$q_test=$vmsql->query("SELECT *
						FROM information_schema.COLUMNS
						WHERE TABLE_SCHEMA = '{$db1['frontend']}'
						AND TABLE_NAME = 'utente'
						AND COLUMN_NAME = 'recover_passwd'");
						
	if($vmsql->num_rows($q_test)==0){
		return true;
	}
	else return false;
}







// EXEC VERSION 0.95e
function update_exec_095e(){
	
	global $db1;
	
	$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}utente ADD recover_passwd VARCHAR( 32 ) NULL ;";
	$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}utente ADD UNIQUE (email) ;";
	
	return _update_exec('0.95e',$sql_up);
	
}




###########################################
#
#	VERSION 0.95f
#




// TEST VERSION 0.95f
function update_test_095f(){
	
	global $db1, $vmsql, $vmreg;
	
	$q_test=$vmsql->query("SELECT * 
						 FROM information_schema.COLUMNS 
						 WHERE TABLE_SCHEMA='{$db1['frontend']}' AND TABLE_NAME='stat' AND COLUMN_NAME='published'");
						
						
	if($vmsql->num_rows($q_test)==0){
		return true;
	}
	else return false;
}







// EXEC VERSION 0.95f
function update_exec_095f(){
	
	global $db1;
	
	if($db1['dbtype']=='mysql'){
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}utente CHANGE nick nick VARCHAR(80) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}stat ADD published TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'published on home page';";
	}
	else if($db1['dbtype']=='postgres'){
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}utente ALTER nick TYPE character varying(80);";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}stat ADD COLUMN published smallint NOT NULL DEFAULT 0;";
		$sql_up[]="COMMENT ON COLUMN {$db1['frontend']}{$db1['sep']}stat.published IS 'published on home page';";
	}
	
	
	return _update_exec('0.95f',$sql_up);
	
}


###########################################
#
#	VERSION 0.95g
#




// TEST VERSION 0.95g
function update_test_095g(){
	
	global $db1, $vmsql, $vmreg;
	
	$q_test=$vmsql->query("SELECT * 
						 FROM information_schema.TABLES 
						 WHERE TABLE_SCHEMA='{$db1['frontend']}' AND TABLE_NAME='button' ");
						
						
	if($vmsql->num_rows($q_test)==0){
		return true;
	}
	else return false;
}







// EXEC VERSION 0.95g
function update_exec_095g(){
	
	global $db1;
	
	if($db1['dbtype']=='mysql'){
		
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}stat DROP INDEX `id_stat`;";
		$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}stat ADD INDEX ( `published` ) ;";
		
		$sql_up[]="CREATE TABLE {$db1['frontend']}{$db1['sep']}button (
		  id_button INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
		  id_table INT(10) NOT NULL,
		  definition TEXT COLLATE utf8_unicode_ci NOT NULL,
		  button_type VARCHAR(25) COLLATE utf8_unicode_ci NOT NULL,
		  background VARCHAR(7) COLLATE utf8_unicode_ci NOT NULL,
		  color VARCHAR(7) COLLATE utf8_unicode_ci NOT NULL,
		  button_name VARCHAR(50) COLLATE utf8_unicode_ci NOT NULL,
		  last_data TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
		  id_utente INT(11) UNSIGNED NOT NULL,
		  PRIMARY KEY  (id_button),
		  KEY id_table (id_table),
		  KEY id_utente (id_utente),
		  CONSTRAINT button_ibfk_1 FOREIGN KEY (id_table) REFERENCES {$db1['frontend']}{$db1['sep']}registro_tab (id_table) 
		  ON DELETE CASCADE ON UPDATE CASCADE
		) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
		";
		
		
		
		
		
		
	}
	else if($db1['dbtype']=='postgres'){
		
		$sql_up[]="CREATE INDEX i_stat_published ON {$db1['frontend']}{$db1['sep']}stat USING btree (published);";
		
		$sql_up[]="CREATE TABLE {$db1['frontend']}{$db1['sep']}button
		(
		  id_button serial NOT NULL,
		  id_table integer NOT NULL,
		  definition text NOT NULL,
		  button_type character varying(25) NOT NULL,
		  background character varying(7),
		  color character varying(7),
		  button_name character varying(50) NOT NULL,
		  last_data timestamp without time zone NOT NULL DEFAULT now(),
		  id_utente integer NOT NULL,
		  CONSTRAINT button_pkey PRIMARY KEY (id_button),
		  CONSTRAINT fk_button_id_table FOREIGN KEY (id_table)
		      REFERENCES {$db1['frontend']}{$db1['sep']}registro_tab (id_table) MATCH SIMPLE
		      ON UPDATE CASCADE ON DELETE CASCADE
		)
		WITHOUT OIDS;";
		
		$sql_up[]="CREATE INDEX i_button_id_table ON {$db1['frontend']}{$db1['sep']}button USING btree (id_table);";
		
	}
	
	
	return _update_exec('0.95g',$sql_up);
	
}



###########################################
#
#	VERSION 0.95h
#




// TEST VERSION 0.95h
function update_test_095h(){
	
	global $db1, $vmsql, $vmreg;
	
	$q_test=$vmsql->query("SELECT * 
						 FROM information_schema.COLUMNS 
						 WHERE TABLE_SCHEMA='{$db1['frontend']}' AND TABLE_NAME='button' AND COLUMN_NAME='settings'");
						
						
	if($vmsql->num_rows($q_test)==0){
		return true;
	}
	else return false;
}




// EXEC VERSION 0.95h
function update_exec_095h(){
	
	global $db1;

	$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}button ADD settings TEXT NULL ;";
	
	$sql_up[]="INSERT INTO ${db1['frontend']}.variabili (variabile, gid, valore, descrizione, tipo_var) VALUES ('search_limit_results',0,'1000','Max records in search results','int');";
		
	return _update_exec('0.95h',$sql_up);
	
}



// VERSION 0.95i
function update_test_095i(){
	
	
	global $db1, $vmsql, $vmreg;
	
	$q_test=$vmsql->query("SELECT * 
						 FROM {$db1['frontend']}{$db1['sep']}variabili 
						 WHERE variabile='lang' ");
						
						
	if($vmsql->num_rows($q_test)==0){
		return true;
	}
	else return false;
}



// EXEC VERSION 0.95i
function update_exec_095i(){
	
	global $db1;
	
	
	$sql_up[]="INSERT INTO ${db1['frontend']}.variabili (variabile, gid, valore, descrizione, tipo_var) VALUES ('lang',0,'','Overwrite the default language','string');";
		
	return _update_exec('0.95h',$sql_up);
}





// VERSION 0.95i
function update_test_095l(){
	
	
	global $db1, $vmsql, $vmreg;
	
	$q_test=$vmsql->query("SELECT * 
						 FROM {$db1['frontend']}{$db1['sep']}variabili 
						 WHERE variabile='show_updates' ");
						
						
	if($vmsql->num_rows($q_test)==0){
		return true;
	}
	else return false;
}



// EXEC VERSION 0.95i
function update_exec_095l(){
	
	global $db1;
	
	
	$sql_up[]="INSERT INTO ${db1['frontend']}.variabili (variabile, gid, valore, descrizione, tipo_var) VALUES ('show_updates',0,1,'Cerca update per VFront','bool');";
		
	return _update_exec('0.95l',$sql_up);
}






// VERSION 0.95m
function update_test_095m(){
	
	
	global $db1, $vmsql, $vmreg;
	
	$q_test=$vmsql->query("SELECT * 
						 FROM information_schema.COLUMNS 
						 WHERE TABLE_SCHEMA='{$db1['frontend']}' AND TABLE_NAME='registro_tab' AND COLUMN_NAME='table_alias'");
						
						
	if($vmsql->num_rows($q_test)==0){
		return true;
	}
	else return false;
}



// EXEC VERSION 0.95m
function update_exec_095m(){
	
	global $db1;
	
	
	$sql_up[]="ALTER TABLE {$db1['frontend']}{$db1['sep']}registro_tab ADD table_alias varchar(100) DEFAULT NULL ;";
		
	return _update_exec('0.95m',$sql_up);
}





function update_test_096(){

	global $db1, $vmsql, $vmreg;

	$q_test=$vmsql->query("SELECT *
						 FROM {$db1['frontend']}{$db1['sep']}variabili
						 WHERE variabile='enable_adminer' ");


	if($vmsql->num_rows($q_test)==0){
		return true;
	}
	else return false;
	
}

// EXEC VERSION 0.96
function update_exec_096(){

	global $db1;
	
	$sql_up[]="INSERT INTO {$db1['frontend']}{$db1['sep']}variabili (variabile,gid,valore,descrizione,tipo_var,pubvar) VALUES ( 'enable_adminer','0','0','Enable Adminer schema administrator','bool','1')";

	return _update_exec('0.96',$sql_up);
}




#######################################################################################à





function _update_exec($version,$sql_array){
	
	global  $vmsql, $vmreg, $db1;
	
	$string_out="\n--\n-- <strong>Update for $version</strong>\n--\n\n";
	
	if(!is_array($sql_array)) $sql_array=(array) $sql_array;
	
	$manual_make=array();
	
	$TEST_OK=0;
	$TEST_KO=0;
	
	foreach($sql_array as $sql){
		
		// GET SQL ACTION:
		$action=array();
		preg_match("|^ *([A-Z]+) |si",$sql,$action);
		
		if(count($action)>0){
			
			$sql_keyword=$action[1];
			$check=check_privileges($sql_keyword);
			
			$test=$vmsql->query_try($sql,false);
			
			if($test){
				$string_out.=$sql."\n-- <span class=\"verde\">OK</span>\n\n";
				$TEST_OK++;
			}
			else{
				$string_out.=$sql."\n-- <span class=\"rosso\">Error</span>\n\n";
				
				$manual_make[]=$sql;
				
				$TEST_KO++;
			}
		}
	}
	
	if(count($manual_make)>0){
		
		$string_out.="<h3>Please run this sql as root (or with user having the correct privileges on DB):</h3>\n";
		
		for($i=0;$i<count($manual_make);$i++){
			$string_out.=$manual_make[$i]."\n\n";
		}
	}
	
	return array($string_out,$TEST_OK,$TEST_KO);
}





if(USE_REG_SQLITE || $db1['dbtype']=='oracle' || $db1['dbtype']=='sqlite'){

	$TEST_UPDATE=false;

	$OUT='';

}
else{

	$TEST_UPDATE=true;

	// Find updates
	preg_match_all('|function (update_test_([\w]+))|im',join('',file(__FILE__)),$function);


	$OUT='';

	if(count($function[0])>0){

		$updates=0;

		if(isset($_GET['test'])){

			for($i=0;$i<count($function[1]);$i++){
				if($function[1][$i]()){

					$updates++;
				}
			}
		}
		else{

			$n_ok=0;
			$n_ko=0;

			// Esegui la funzione TEST
			for($i=0;$i<count($function[1]);$i++){
				if($function[1][$i]()){

					$updates++;


					$func_exec='update_exec_'.$function[2][$i];
					if(function_exists($func_exec)){

						$arr_out=$func_exec();
						$OUT.=$arr_out[0];
						$n_ok+=$arr_out[1];
						$n_ko+=$arr_out[2];
					}
				}
			}
		}
	}

}



echo openLayout1(_("VFront Update"),array("sty/admin.css"));

echo breadcrumbs(array("HOME","ADMIN",strtolower(_("VFront Update"))));

	
echo "<h1>"._("VFront Update")."</h1>";

if(!$TEST_UPDATE){

	echo "<p>"._('This test is not available for this VFront rule method')."</p>\n";
}
else if($updates==0){
	
	echo "<p>"._('No updates to be installed in DB VFront')."</p>\n";
}
else{
	
	if(isset($_GET['test'])){
		
		if($updates>0){
			
			echo "<p>".sprintf(_('There are %d updates to install.'),$updates)."</p>\n";
			echo "<p><a href=\"".$_SERVER['PHP_SELF']."\">"._("Proceed to VFront database update")."</a></p>\n";
		}
		else{
			echo "<p>"._('No updates to be installed.')."</p>\n";
		}
	}
	else{
		
		if($n_ko==0 && $n_ok>0){
			echo "<p>".sprintf(_('Founded and installed %d updates.'),$updates)."</p>\n";
		}
		else if($n_ko>0){
			echo "<p>".sprintf(_('Founded and installed %d updates. %d failed and should be installed manually.'),$n_ok,$n_ko)."</p>\n";
		}
		else{
			echo "<p>"._('No updates to be installed.')."</p>\n";
		}
	}
}

if($OUT!=''){
	
	echo "<div id=\"boxsql\"><code>\n";
		
	echo nl2br($OUT);
	
	echo "</code></div>\n";
}


echo closeLayout1();







?>