#
#-----[ OVERWRITE ]------------------------------------------
#

overwrite db_utils_optimize_body.tpl with templates/subSilver/admin/db_utils_optimize_body.tpl

# 
#-----[ COPY ]------------------------------------------ 
# 

copy optimize_database_cron.php to includes/optimize_database_cron.php

# 
#-----[ SQL ]------------------------------------------ 
# 
#   If you don't know how to execute sql queries, then copy
#   mysql_install.php into your phpbb directory and run it.
#   (assuming you use mysql)
#   After process delete it.
#
#   If you don't use mysql, you'll have to edit these queries accordingly
# 
#   If you have a different table prefix then change this command accordingly. 
#   I have used the default table prefix! 
# 

CREATE TABLE phpbb_optimize_db (
  cron_enable enum('0','1') NOT NULL default '0',
  cron_every int(7) NOT NULL default '86400',
  cron_next int(11) NOT NULL default '0',
  cron_count int(5) NOT NULL default '0',
  show_tables varchar(150) NOT NULL default ''
)

# 
#-----[ SQL ]------------------------------------------ 
# 
#   If you have a different table prefix then change this command accordingly. 
#   I have used the default table prefix! 
# 

INSERT INTO phpbb_optimize_db VALUES ('0', 86400, 0, 0, '');

# 
#-----[ OPEN ]------------------------------------------ 
# 

common.php

# 
#-----[ FIND ]------------------------------------------ 
# 

?>

# 
#-----[ BEFORE, ADD ]------------------------------------------ 
#

include($phpbb_root_path . 'includes/optimize_database_cron.'.$phpEx);

# 
#-----[ OPEN ]------------------------------------------ 
# 

includes/constants.php

# 
#-----[ FIND ]------------------------------------------ 
# 

?>

# 
#-----[ BEFORE, ADD ]------------------------------------------ 
#

define('OPTIMIZE_DB_TABLE', $table_prefix.'optimize_db');

# 
#-----[ OPEN ]------------------------------------------ 
# 

admin/admin_db_utilities.php

# 
#-----[ FIND ]------------------------------------------ 
# 

//
// Start Optimize Database 1.1.1 by Sko22 < sko22@quellicheilpc.com >
//
		case 'optimize':

			if(!isset($HTTP_POST_VARS['ottimizza']))
			{
				
				include('./page_header_admin.'.$phpEx);

$sql = "SHOW TABLE STATUS";

	$result = $db->sql_query($sql);
	if( !$result )
	{
		message_die(GENERAL_ERROR, "Couldn't obtain databases list", "", __LINE__, __FILE__, $sql);
	}

	$opt = $db->sql_fetchrowset($result);

	for($i = 0; $i < count($opt); $i++)
	{
		
		$row_class = ( !($i % 2) ) ? $theme['td_class1'] : $theme['td_class2'];

$dbsize = $opt[$i]['Data_length'] + $opt[$i]['Index_length']; 

		if( $dbsize >= 1048576 )
		{
			//$dbsize = sprintf("%.2f Mb", ( $dbsize / 1048576 ));
			$dbsize = round(($dbsize / 1048576 ),1)." Mb";
		}
		else if( $dbsize >= 1024 )
		{
			//$dbsize = sprintf("%.2f Kb", ( $dbsize / 1024 ));
			$dbsize = round(($dbsize / 1024 ),1)." Kb";
		}
		else
		{
			//$dbsize = sprintf("%.2f Bytes", $dbsize);
			$dbsize = round($dbsize,1)." Bytes";
		}

$opt[$i]['Data_free'] != 0 ? $data_free = "No OK" : $data_free = "OK";
$opt[$i]['Data_free'] != 0 ? $check = "checked" : $check = ""; 

		$template->assign_block_vars("optimize", array(
			"ROW_CLASS" => $row_class,
			"S_SELECT_TABLE" => "<input type=\"checkbox\" name=\"selected_tbl[]\" value=\"" . $opt[$i]['Name'] . "\"" . $check . ">",
			"TABLE" => $opt[$i]['Name'],
			"RECORD" => $opt[$i]['Rows'],
			"TYPE" => $opt[$i]['Type'],
			"SIZE" => $dbsize,
			"STATUS" => $data_free,
			"TOT_TABLE" => $i
			)
		);

$total_tab = $i +1;
$total_rec = $total_rec + $opt[$i]['Rows']; 
$total_size = $total_size + $opt[$i]['Data_length'] + $opt[$i]['Index_length']; 
if ($data_free == "No OK") $total_stat = "No OK";

	}

$total_size = round(($total_size / 1048576 ),1)." Mb";
$total_stat == "No OK" ? $total_stat = "No OK" : $total_stat = "OK";

			$template->set_filenames(array(
					"body" => "admin/db_utils_optimize_body.tpl")
				);

				$s_hidden_fields = "<input type=\"hidden\" name=\"perform\" value=\"$perform\" />";
				
if ( $i != 1 )

{
				
$select_scritp = "
<script language=\"JavaScript\">
// I have copied and modified a script of phpMyAdmin.net
<!--
function setCheckboxes(the_form, do_check)
{
var elts = (typeof(document.forms[the_form].elements['selected_tbl[]']) != 'undefined') 
? document.forms[the_form].elements['selected_tbl[]'] 
: document.forms[the_form].elements = '';

var elts_cnt  = (typeof(elts.length) != 'undefined') ? elts.length : 0;

if (elts_cnt) {
	for (var i = 0; i < elts_cnt; i++) {
		if (do_check == \"invert\"){
		elts[i].checked == true ? elts[i].checked = false : elts[i].checked = true;
		} else {
		elts[i].checked = do_check;
		}
		} // end for
    } else {
        elts.checked        = do_check;
    } // end if... else

return true;
}
//-->
</script>
";

}

else

{

$select_scritp = "
<script language=\"JavaScript\">
<!--
function setCheckboxes(the_form, do_check)
{

}
//-->
</script>";

}

				$template->assign_vars(array(
					"SELECT_SCRIPT" => $select_scritp,
					"L_DATABASE_OPTIMIZE" => $lang['Database_Utilities'] . " : " . $lang['Optimize'],
					"L_OPTIMIZE_EXPLAIN" => $lang['Optimize_explain'],
					"L_OPTIMIZE_TABLE" => $lang['Optimize_Table'],
					"L_OPTIMIZE_RECORD" => $lang['Optimize_Record'],
					"L_OPTIMIZE_TYPE" => $lang['Optimize_Type'],
					"L_OPTIMIZE_SIZE" => $lang['Optimize_Size'],
					"L_OPTIMIZE_STATUS" => $lang['Optimize_Status'],
					"TOT_TABLE" => $total_tab,
					"TOT_RECORD" => $total_rec,
					"TOT_SIZE" => $total_size,
					"TOT_STATUS" => $total_stat,
					"L_OPTIMIZE_CHECKALL" => $lang['Optimize_CheckAll'],
					"L_OPTIMIZE_UNCHECKALL" => $lang['Optimize_UncheckAll'],
					"L_OPTIMIZE_INVERTCHECKED" => $lang['Optimize_InvertChecked'],
					"L_START_OPTIMIZE" => $lang['Optimize'],
					"S_DBUTILS_ACTION" => append_sid("admin_db_utilities.$phpEx"),
					"S_HIDDEN_FIELDS" => $s_hidden_fields
					)
				);

				$template->pparse("body");

				break;

			}

			else
			
			{

include('./page_header_admin.'.$phpEx);

$sql = "OPTIMIZE TABLE ";

// make query optimize
if ($_POST["selected_tbl"] != ""){

$i=1;
foreach ($_POST["selected_tbl"] as $var => $value){
	if($i<count($_POST["selected_tbl"])){
		$sql .= "`$value`, ";
		} else {
		$sql .= "`$value`";
		}
 $i++;
} 

}

$sql .= " ;";

if (!$result = $db->sql_query($sql))

{
	$optimize_notablechecked = true;
}

				$template->set_filenames(array(
					"body" => "admin/admin_message_body.tpl")
				);

				$optimize_notablechecked == true ? $message = $lang['Optimize_NoTableChecked'] : $message = $lang['Optimize_success'];

				$template->assign_vars(array(
					"MESSAGE_TITLE" => $lang['Database_Utilities'] . " : " . $lang['Optimize'],
					"MESSAGE_TEXT" => $message)
				);

				$template->pparse("body");
				break;

			}

		break;
//
// End Optimize Database 1.1.1 by Sko22 < sko22@quellicheilpc.com >
//

# 
#-----[ REPLACE WITH ]------------------------------------------ 
# 

//
// Start Optimize Database 1.2.0 by Sko22 < sko22@quellicheilpc.com >
//
		case 'optimize':

			include('./page_header_admin.'.$phpEx);

			//
			// If has been clicked the button reset
			//
			if( isset( $HTTP_POST_VARS['reset'] ) )
			{
			$sql = "UPDATE " . OPTIMIZE_DB_TABLE . " SET cron_enable = '0', cron_every = '86400', cron_next ='0', cron_count='0', show_tables = 'xxx' LIMIT 1 ";
			
				if( !($result = $db->sql_query($sql)) )
				{
					message_die(GENERAL_ERROR, 'Could not reset optimize database configuration', '', __LINE__, __FILE__, $sql);
				}
			}
			
			//
			// If has been clicked the button configure
			//
			if( isset( $HTTP_POST_VARS['configure'] ) || isset( $HTTP_POST_VARS['only_tables'] ) )
			{

				$sql = "UPDATE " . OPTIMIZE_DB_TABLE . " SET show_tables = '" . $HTTP_POST_VARS['only_tables'] . "' ";
			
				if( !($result = $db->sql_query($sql)) )
				{
					message_die(GENERAL_ERROR, 'Could not configure show tables', '', __LINE__, __FILE__, $sql);
				}
			
				if ( isset( $HTTP_POST_VARS['configure'] ) )
				{

				//
				// Update optimize database cronfiguration
				//
				$sql = "UPDATE " . OPTIMIZE_DB_TABLE . " SET cron_every = " . $HTTP_POST_VARS['cron_every'] . ", cron_enable = '"  . $HTTP_POST_VARS['enable_optimize_cron'] . "', cron_next = " . ( time() + $HTTP_POST_VARS['cron_every'] ) . ", show_tables = '" . $HTTP_POST_VARS['only_tables'] . "' ";
			
				if( !($result = $db->sql_query($sql)) )
				{
					message_die(GENERAL_ERROR, 'Could not optimize database cronfiguration', '', __LINE__, __FILE__, $sql);
				}

				}

			}

			//
			// Optimize database configuration
			//
			$sql_opt = "SELECT * FROM " . OPTIMIZE_DB_TABLE . " ";

			$opt_conf_result = $db->sql_query($sql_opt);

			if( !( $opt_conf = $db->sql_fetchrow($opt_conf_result) ) )
			{
				message_die(GENERAL_ERROR, 'Could not obtain database optimize configuration', '', __LINE__, __FILE__, $sql);
			}

			//
			// If has been clicked the button optimize
			//
			if(!isset($HTTP_POST_VARS['optimize']))
			{

			$sql = "SHOW TABLE STATUS LIKE '" . $opt_conf['show_tables'] . "%' ";

			$result = $db->sql_query($sql);
			if( !$result )
			{
				message_die(GENERAL_ERROR, "Couldn't obtain databases list", "", __LINE__, __FILE__, $sql);
			}

			$opt = $db->sql_fetchrowset($result);

			for($i = 0; $i < count($opt); $i++)
			{
		
			$row_class = ( !($i % 2) ) ? $theme['td_class1'] : $theme['td_class2'];

			$dbsize = $opt[$i]['Data_length'] + $opt[$i]['Index_length']; 
			
			//
			// Exact weight of a table of a database
			//
			if( $dbsize >= 1048576 )
			{
				//$dbsize = sprintf("%.2f Mb", ( $dbsize / 1048576 ));
				$dbsize = round(($dbsize / 1048576 ),1)." Mb";
			}
			else if( $dbsize >= 1024 )
			{
				//$dbsize = sprintf("%.2f Kb", ( $dbsize / 1024 ));
				$dbsize = round(($dbsize / 1024 ),1)." Kb";
			}
			else
			{
				//$dbsize = sprintf("%.2f Bytes", $dbsize);
				$dbsize = round($dbsize,1)." Bytes";
			}

			$opt[$i]['Data_free'] != 0 ? $data_free = "No OK" : $data_free = "OK";
			$opt[$i]['Data_free'] != 0 ? $check = "checked" : $check = ""; 

		//
		// Make list tables of database
		//
		$template->assign_block_vars("optimize", array(
			"ROW_CLASS" => $row_class,
			"S_SELECT_TABLE" => "<input type=\"checkbox\" name=\"selected_tbl[]\" value=\"" . $opt[$i]['Name'] . "\"" . $check . ">",
			"TABLE" => $opt[$i]['Name'],
			"RECORD" => $opt[$i]['Rows'],
			"TYPE" => $opt[$i]['Type'],
			"SIZE" => $dbsize,
			"STATUS" => $data_free,
			"TOT_TABLE" => $i
			)
		);

		$total_tab = $i +1;
		$total_rec = $total_rec + $opt[$i]['Rows']; 
		$total_size = $total_size + $opt[$i]['Data_length'] + $opt[$i]['Index_length']; 
		if ($data_free == "No OK") $total_stat = "No OK";

	}

	$total_size = round(($total_size / 1048576 ),1)." Mb";
	$total_stat == "No OK" ? $total_stat = "No OK" : $total_stat = "OK";

	$template->set_filenames(array(
		"body" => "admin/db_utils_optimize_body.tpl")
	);

	$s_hidden_fields = "<input type=\"hidden\" name=\"perform\" value=\"$perform\" />";
	
	//
	// Enable the select tables script 
	//
	if ( $i != 1 )
	{
					
	$select_scritp = "
	<script language=\"JavaScript\">
	// I have copied and modified a script of phpMyAdmin.net
	<!--
	function setCheckboxes(the_form, do_check)
	{
	var elts = (typeof(document.forms[the_form].elements['selected_tbl[]']) != 'undefined') 
	? document.forms[the_form].elements['selected_tbl[]'] 
	: document.forms[the_form].elements = '';

	var elts_cnt  = (typeof(elts.length) != 'undefined') ? elts.length : 0;

	if (elts_cnt) {
		for (var i = 0; i < elts_cnt; i++) {
			if (do_check == \"invert\"){
			elts[i].checked == true ? elts[i].checked = false : elts[i].checked = true;
			} else {
			elts[i].checked = do_check;
			}
			} // end for
		} else {
			elts.checked = do_check;
		} // end if... else

	return true;
	}
	//-->
	</script>
	";

	}
	else
	{

	$select_scritp = "
	<script language=\"JavaScript\">
	<!--
	function setCheckboxes(the_form, do_check)
	{

	}
	//-->
	</script>";

	}

	switch ( $opt_conf['cron_every'] ) {
    case 2592000:
        $month = "selected";
        break;
    case 1296000:
        $weeks2 = "selected";
        break;
    case 604800:
        $week = "selected";
        break;
	case 259200:
        $days3 = "selected";
        break;
	case 86400:
        $day = "selected";
        break;
	case 21600:
        $hours6 = "selected";
        break;
	case 3600:
        $hour = "selected";
        break;
	case 1800:
        $minutes30 = "selected";
        break;
	/* for optimize database cron test
	case 20:
        $seconds20 = "selected";
        break;
	*/
	default : $day = "selected";
	}
		//
		// Select a cron every
		//
		$template->assign_block_vars("sel_cron_every", array(
			"MONTH" => $month,
			"2WEEKS" => $weeks2,
			"WEEK" => $week,
			"3DAYS" => $days3,
			"DAY" => $day,
			"6HOURS" => $hours6,
			"HOUR" => $hour,
			/* for optimize database cron test
			"20SECONDS" => $seconds20,
			*/
			"30MINUTES" => $minutes30
			)
		);

		$opt_conf['cron_enable'] != '1' ? $enable_cron_no = 'checked="checked"': $enable_cron_yes = 'checked="checked"';
		
		if ( $opt_conf['cron_enable'] != '1'  || $opt_conf['cron_next'] == 0 )
		{
			$next_cron = " - - ";
			$performed_cron = " - - ";
		}
		else 
		{
			$next_cron = create_date( $lang['DATE_FORMAT'] . ' g:i:s a' , $opt_conf['cron_next'], $board_config['board_timezone'] );
			$performed_cron = $opt_conf['cron_count'];

		}
			
			//
			// Make the template
			//
			$template->assign_vars(array(
					"SELECT_SCRIPT" => $select_scritp,
					"TOT_TABLE" => $total_tab,
					"TOT_RECORD" => $total_rec,
					"TOT_SIZE" => $total_size,
					"TOT_STATUS" => $total_stat,
					"NEXT_CRON" => $next_cron,
					"CURRENT_TIME" => create_date( $lang['DATE_FORMAT'] . ' g:i:s a' , time(), $board_config['board_timezone'] ),
					"PERFORMED_CRON" => $performed_cron,
					"L_ENABLE_CRON" => $lang['Optimize_Enable_cron'],
					"L_ENABLE_CRON_YES" => $lang['Yes'],
					"L_ENABLE_CRON_NO" => $lang['No'],
					"L_CRON_EVERY" => $lang['Optimize_Cron_every'],
					"L_CURRENT_TIME" => $lang['Optimize_Current_time'],
					"L_NEXT_CRON_ACTION" => $lang['Optimize_Next_cron_action'],
					"L_PERFORMED_CRON" => $lang['Optimize_Performed_Cron'],
					"L_DATABASE_OPTIMIZE" => $lang['Database_Utilities'] . " : " . $lang['Optimize'],
					"L_OPTIMIZE_EXPLAIN" => $lang['Optimize_explain'],
					"L_OPTIMIZE_DB" => $lang['Optimize_DB'],
					"L_CONFIGURATION" => $lang['Configuration'],
					"L_SHOW_TABLE" => $lang['Optimize_Show_Table'],
					"L_CONFIGURE" => $lang['Optimize_Configure'],
					"L_RESET" => $lang['Reset'],
					"L_TABLE" => $lang['Optimize_Table'],
					"L_RECORD" => $lang['Optimize_Record'],
					"L_TYPE" => $lang['Optimize_Type'],
					"L_SIZE" => $lang['Optimize_Size'],
					"L_STATUS" => $lang['Optimize_Status'],
					"L_CHECKALL" => $lang['Optimize_CheckAll'],
					"L_UNCHECKALL" => $lang['Optimize_UncheckAll'],
					"L_INVERTCHECKED" => $lang['Optimize_InvertChecked'],
					"L_START_OPTIMIZE" => $lang['Optimize'],
					"S_DBUTILS_ACTION" => append_sid("admin_db_utilities.$phpEx"),
					"S_ENABLE_CRON_YES" => $enable_cron_yes,
					"S_ENABLE_CRON_NO" => $enable_cron_no,
					"S_ONLY_TABLES_VALUE" => $opt_conf['show_tables'],
					"S_HIDDEN_FIELDS" => $s_hidden_fields
					)
				);

				$template->pparse("body");

				break;

			}

			else
			
			{

			$sql = "OPTIMIZE TABLE ";

			// Make optimize query
			if ($_POST["selected_tbl"] != ""){

			$i=1;
			foreach ($_POST["selected_tbl"] as $var => $value){
				if($i<count($_POST["selected_tbl"])){
					$sql .= "`$value`, ";
					} else {
					$sql .= "`$value`";
					}
			 $i++;
			} 

			}

			$sql .= " ;";

			if (!$result = $db->sql_query($sql))

			{
				$optimize_notablechecked = true;
			}

			//
			// Create information message 
			//
			if ( $optimize_notablechecked == true )
			{

			$message = $lang['Optimize_NoTableChecked']  . "." . 
			"<br /><br />" . sprintf($lang['Optimize_return'], "<a href=\"" . append_sid("admin_db_utilities.$phpEx?perform=optimize") . "\">", "</a>")	 . 
			"<br /><br />" . sprintf($lang['Click_return_admin_index'], "<a href=\"" . append_sid("index.$phpEx?pane=right") . "\">", "</a>")	;
			}

			else

			{

			$message = $message = $lang['Optimize_success']  . "." .
			"<br /><br />" . sprintf($lang['Optimize_return'], "<a href=\"" . append_sid("admin_db_utilities.$phpEx?perform=optimize") . "\">", "</a>")	 .
			"<br /><br />" . sprintf($lang['Click_return_admin_index'], "<a href=\"" . append_sid("index.$phpEx?pane=right") . "\">", "</a>")	;

			}

			message_die(GENERAL_MESSAGE, $message);


			}

		break;
//
// End Optimize Database 1.2.0 by Sko22 < sko22@quellicheilpc.com >
//

# 
#-----[ OPEN ]------------------------------------------ 
# 

language/lang_english/lang_admin.php

# 
#-----[ FIND ]------------------------------------------ 
# 

$lang['Optimize_DB'] = 'Optimize Database';

# 
#-----[ REPLACE WITH ]------------------------------------------ 
#

# 
#-----[ FIND ]------------------------------------------ 
# 

$lang['Optimize'] = 'Optimize';

# 
#-----[ REPLACE WITH ]------------------------------------------ 
#

# 
#-----[ FIND ]------------------------------------------ 
# 

$lang['Optimize_explain'] = 'The elimination of data leaves in the database of the empty spaces, to eliminate these empty spaces is necessary to optimize database. Here it is possible to optimize the data in the tables of the database.';

# 
#-----[ REPLACE WITH ]------------------------------------------ 
#

# 
#-----[ FIND ]------------------------------------------ 
# 

$lang['Optimize_Table'] = 'Table';
$lang['Optimize_Record'] = 'Record';
$lang['Optimize_Type'] = 'Type';
$lang['Optimize_Size'] = 'Size';
$lang['Optimize_Status'] = 'Status';
$lang['Optimize_CheckAll'] = 'Check All';
$lang['Optimize_UncheckAll'] = 'Uncheck All';
$lang['Optimize_InvertChecked'] = 'Invert Checked';
$lang['Optimize_success'] = 'The Database has been successfully optimized.';
$lang['Optimize_NoTableChecked'] = '<b>No</b> Table Checked';

# 
#-----[ REPLACE WITH ]------------------------------------------ 
#

// Start Optimize Database

$lang['Optimize'] = 'Optimize';
$lang['Optimize_explain'] = 'The elimination of data leaves in the database of the empty spaces, to eliminate these empty spaces is necessary to optimize database. Here it is possible to optimize the data in the tables of the database.<br /><br />You can use the utility of configuration to optimize the database in automatic.';
$lang['Optimize_DB'] = 'Optimize Database';
$lang['Optimize_Enable_cron'] = "Enable Cron";
$lang['Optimize_Cron_every'] = "Cron Every";
$lang['Optimize_Current_time'] = "Current Time";
$lang['Optimize_Next_cron_action'] = "Next Cron Action";
$lang['Optimize_Performed_Cron'] = "Performed Cron";
$lang['Optimize_Show_Table'] = 'Show tables that begin for';
$lang['Optimize_Configure'] = 'Configure';
$lang['Optimize_Table'] = 'Table';
$lang['Optimize_Record'] = 'Record';
$lang['Optimize_Type'] = 'Type';
$lang['Optimize_Size'] = 'Size';
$lang['Optimize_Status'] = 'Status';
$lang['Optimize_CheckAll'] = 'Check All';
$lang['Optimize_UncheckAll'] = 'Uncheck All';
$lang['Optimize_InvertChecked'] = 'Invert Checked';
$lang['Optimize_return'] = 'Click %sHere%s to return to the Optimize Database';
$lang['Optimize_success'] = 'The Database has been successfully optimized';
$lang['Optimize_NoTableChecked'] = '<b>No</b> Tables Checked';

// End Optimize Database 

# 
#-----[ SAVE/CLOSE ALL FILES ]------------------------------------------ 
# 
# EoM 