############################################################## 
## MOD Title: Optimize Database
## MOD Author: Sko22 < sko22@quellicheilpc.it > (N/A) http://www.quellicheilpc.it/
## MOD Description: It Checks and it Optimizes the Tables of the Database also in automatic.
## MOD Version: 1.2.2
## 
## Installation Level: Easy 
## Installation Time: 5 Minutes 
## Files To Edit: 
##		common.php
##		includes/constants.php
##		admin/admin_db_utilities.php
##             language/lang_english/lang_admin.php
## Included Files: 
##		includes/optimize_database_cron.php
##		templates/subSilver/admin/db_utils_optimize_body.tpl
############################################################## 
## For Security Purposes, Please Check: http://www.phpbb.com/mods/ for the 
## latest version of this MOD. Downloading this MOD from other sites could cause malicious code 
## to enter into your phpBB Forum. As such, phpBB will not offer support for MOD's not offered 
## in our MOD-Database, located at: http://www.phpbb.com/mods/ 
############################################################## 
## Author Notes: 
## This MOD is only for MySQL Database
## I have tested this MOD whit phpBB 2.0.4/5/6 and MySQL Database 3.23.56 and 4.0.15-nt
##
## Future versions can be found at http://www.quellicheilpc.it
## I've set up a support forum for my mods at http://www.quellicheilpc.it/forum/viewtopic.php?t=1769
##
## This MOD is released under the GPL License. 
## Intellectual Property is retained by the MOD Author(s) listed above 
## Copyright:      2003 Optimize Database 1.2.2 - Sko22
############################################################## 
## MOD History: 
## 
##   2003-10-06 - Version 1.2.2
##	- Fixed cron counter for contemporary accesses (Tanks to matrix)
##   2003-10-04 - Version 1.2.1
##	- Fixed bugs with automatic optimize whit mysql 4 (Tanks to a.banderas)
##	- Fixed little bugs with the language
##	- Added the translation in French (Tanks to Dr DLP)
##	- Added the function to Show only tables not optimized
##   2003-09-21 - Version 1.2.0
##	- Added a Configuration panel to optimize the database in automatic
##	- Added the function to show only some tables
##   2003-09-17 - Version 1.1.1
##	- Fixed bug with MySQL 4 and same little bugs (Tanks to a.banderas)
##   2003-09-03 - Version 1.1.0 
##	- Conformed to the phpBB coding standars ed fixed same bugs
##   2003-08-12 - Version 1.0.0
##	- Initial Release
##
############################################################## 
## Before Adding This MOD To Your Forum, You Should Back Up All Files Related To This MOD 
##############################################################  

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

copy optimize_database_cron.php to includes/optimize_database_cron.php
copy db_utils_optimize_body.tpl to templates/subSilver/admin/db_utils_optimize_body.tpl

# 
#-----[ 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',
  cron_lock enum('0','1') NOT NULL default '0',
  show_begin_for varchar(150) NOT NULL default '',
  show_not_optimized enum('0','1') NOT NULL default '0'
)

# 
#-----[ 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, '1', '', '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 ]------------------------------------------ 
# 

	if( (empty($file_uploads) || $file_uploads != 0) && (strtolower($file_uploads) != 'off') && (@phpversion() != '4.0.4pl1') )
	{
		$module['General']['Restore_DB'] = $filename . "?perform=restore";
	}

# 
#-----[ AFTER, ADD ]------------------------------------------ 
# 

	$module['General']['Optimize_DB'] = $filename . "?perform=optimize";

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

	switch($perform)
	{

# 
#-----[ AFTER, ADD ]------------------------------------------ 
# 

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

			include('./page_header_admin.'.$phpEx);
		
		$current_time = time();

			//
			// 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', cron_lock = '1', show_begin_for = '',  show_not_optimized = '0' 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['show_begin_for'] ) )
			{

				$sql = "UPDATE " . OPTIMIZE_DB_TABLE . " SET show_begin_for = '" . $HTTP_POST_VARS['show_begin_for'] . "' ";
			
				if( !($result = $db->sql_query($sql)) )
				{
					message_die(GENERAL_ERROR, 'Could not configure show begin for', '', __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 = " . ( $current_time + $HTTP_POST_VARS['cron_every'] ) . ", show_begin_for = '" . $HTTP_POST_VARS['show_begin_for'] . "', show_not_optimized = '" . $HTTP_POST_VARS['show_not_optimized'] . "' ";
			
				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_begin_for'] . "%' ";

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


		$i = 0;
		while ($opt = $db->sql_fetchrow($result) )
		{
	
			if ( $opt['Data_free'] != 0 || !$opt_conf['show_not_optimized'] )
			{
			
			$row_class = ( !($i % 2) ) ? $theme['td_class1'] : $theme['td_class2'];

			$dbsize = $opt['Data_length'] + $opt['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['Data_free'] != 0 ? $data_free = "No OK" : $data_free = "OK";
			$opt['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['Name'] . "\"" . $check . ">",
			"TABLE" => $opt['Name'],
			"RECORD" => $opt['Rows'],
			"TYPE" => $opt['Type'],
			"SIZE" => $dbsize,
			"STATUS" => $data_free,
			"TOT_TABLE" => $i
			)
		);

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

		} 
		
		else
		
		{
			$i--;
		}

$i++;

	}

	$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;
	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,
			"30MINUTES" => $minutes30,
			"20SECONDS" => $seconds20,
	
			"L_MONTH" => $lang['Optimize_month'],
			"L_2WEEKS" => $lang['Optimize_2weeks'],
			"L_WEEK" => $lang['Optimize_week'],
			"L_3DAYS" => $lang['Optimize_3days'],
			"L_DAY" => $lang['Optimize_day'],
			"L_6HOURS" => $lang['Optimize_6hours'],
			"L_HOUR" => $lang['Optimize_hour'],
			"L_30MINUTES" => $lang['Optimize_30minutes'],
			"L_20SECONDS" => $lang['Optimize_20seconds']
			)
		);

		$opt_conf['cron_enable'] != '1' ? $enable_cron_no = 'checked="checked"': $enable_cron_yes = 'checked="checked"';
		$opt_conf['show_not_optimized'] != '1' ? $enable_not_optimized_no = 'checked="checked"': $enable_not_optimized_yes = 'checked="checked"';
		
		if ( $opt_conf['cron_enable'] != '1'  || $opt_conf['cron_next'] == 0 )
		{
			$next_cron = " - - ";
			$performed_cron = " - - ";
		}
		else 
		{

		$next_cron = create_date( 'd M Y H:i:s', $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( 'd M Y H:i:s', $current_time, $board_config['board_timezone'] ),
					"PERFORMED_CRON" => $performed_cron,
					"L_ENABLE_CRON" => $lang['Optimize_Enable_cron'],
					"L_YES" => $lang['Yes'],
					"L_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_NOT_OPTIMIZED" => $lang['Optimize_Show_not_optimized'],
					"L_SHOW_BEGIN_FOR" => $lang['Optimize_Show_begin_for'],
					"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_SHOW_BEGIN_FOR" => $opt_conf['show_begin_for'],
					"S_ENABLE_NOT_OPTIMIZED_YES" => $enable_not_optimized_yes,
					"S_ENABLE_NOT_OPTIMIZED_NO" => $enable_not_optimized_no,
					"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.2 by Sko22 < sko22@quellicheilpc.it >
//

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

language/lang_english/lang_admin.php

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

$lang['Backup_options'] = 'Backup options';

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

// Start Optimize Database

$lang['Optimize'] = 'Optimize';
$lang['Optimize_explain'] = 'Here it\'s possible to optimize the contained data in the tables of the database. You can eliminate in this way the parts of the data that contain some empty spaces.<br />This operation must regularly be performs so that your database to make reliable and it has to maintain a speed of correct execution.';
$lang['Optimize_DB'] = 'Optimize Database';
$lang['Optimize_Enable_cron'] = "Enable Cron";
$lang['Optimize_Cron_every'] = "Cron Every";
$lang['Optimize_month'] = "Month";
$lang['Optimize_2weeks'] = "2 weeks";
$lang['Optimize_week'] = "Week";
$lang['Optimize_3days'] = "3 days";
$lang['Optimize_day'] = "Day";
$lang['Optimize_6hours'] = "6 hours";
$lang['Optimize_hour'] = "Hour";
$lang['Optimize_30minutes'] = "30 minutes";
$lang['Optimize_20seconds'] = "20 seconds (only for test)";
$lang['Optimize_Current_time'] = "Current Time";
$lang['Optimize_Next_cron_action'] = "Next Cron Action";
$lang['Optimize_Performed_Cron'] = "Performed Cron";
$lang['Optimize_Show_not_optimized'] = 'Show only tables not optimized';
$lang['Optimize_Show_begin_for'] = 'Show only 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 