Location: PHPKode > projects > phpBB Email Digests > install_digests.php
<?php
/***************************************************************************
 *                             install_digest_tables.php
 *                             -------------------------
 *   begin                : Friday, September 24, 2004
 *   copyright            : 
 *   email                : 
 *
 *   $Id:
 *
 ***************************************************************************/

/***************************************************************************
 *
 *   This program 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 2 of the License, or
 *   (at your option) any later version.
 *
 ***************************************************************************/

define('IN_PHPBB', 1);

//
// Change the next line to "= true" if you need to debug this installation
$verbose = FALSE;
$ignore_error_code = FALSE;

$phpbb_root_path = './';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);
$error_code == FALSE;

//
// Define the "old" tables
//
define('SUBSCRIPTIONS_TABLE', $table_prefix.'mod_subscriptions');
define('SUBSCRIBED_FORUMS_TABLE', $table_prefix.'mod_subscribed_forums');
define('OLD_DIGEST_TABLE', $table_prefix.'digest');
define('OLD_DIGEST_FORUMS_TABLE', $table_prefix.'digest_forums');

define('DIGEST_MOD_VERSION','1.3.4');
define('DIGEST_HTML', 1);
define('DIGEST_TEXT', 0);

$userdata = session_pagestart($user_ip, PAGE_INDEX);
init_userprefs($userdata);

//
// Check for previous version installed
//
$digest_version = 'New installation';

$sql = "SELECT * FROM " . SUBSCRIPTIONS_TABLE; 
if ($result = $db->sql_query($sql))
{
	$digest_version = '1.1.x';
}

$sql = "SELECT * FROM " . OLD_DIGEST_TABLE; 
if ($result = $db->sql_query($sql))
{
	$digest_version = '1.2.x';
}

$sql = "SELECT * FROM " . DIGEST_TABLE; 
if ($result = $db->sql_query($sql))
{
	$digest_version = DIGEST_MOD_VERSION;
}

$row = $db->sql_fetchrow($result);
$phpBB_version = $board_config['version'] ;
$sql = array();

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<title>Install File for Digest MOD</title>
<head>
<meta http-equiv="Content-Type" content="text/html;">
<meta http-equiv="Content-Style-Type" content="text/css">
<style type="text/css">
<!--

th,td,p,.body {font-family: "Trebuchet MS",Verdana, Arial, Helvetica, sans-serif; font-size: 11pt}
.info {font-size: 12pt}

.maintitle	{font-weight: bold; font-size: 16pt; line-height : 120%;}

.ok {font-size: 11pt; font-weight: bold; font-family: "Trebuchet MS",Verdana, Arial, Helvetica, sans-serif; color:green}

.error {font-size: 11pt; font-weight: bold; font-family: "Trebuchet MS",Verdana, Arial, Helvetica, sans-serif; color:red}

-->
</style>
</head>
<body bgcolor="#FFFFFF" text="#000000" link="#006699" vlink="#5584AA">

<table width="100%" border="0" cellspacing="0" cellpadding="5" align="center"> 
	<tr>
		<td align="center" width="100%" valign="middle" colspan="2"><span class="maintitle">Installing Digests</span></td>
	</tr>
	<tr>
		<td align="left" width="100%" valign="middle" colspan="2"><span class="maintitle">Information</span></td>
	</tr>
	<tr>
		<td align="right" width="6%" valign="middle"><span class="info">Database type  ::</span></td>
		<td align="left" width="20%" valign="middle"><span class="info"><? print SQL_LAYER ?></span></td>
	</tr>
	<tr>
		<td align="right" width="6%" valign="middle"><span class="info">phpBB version  ::</span></td>
		<td align="left" width="20%" valign="middle"><span class="info">2<? print $phpBB_version ?></span></td>
	</tr>
	<tr>
		<td align="right" width="6%" valign="middle"><span class="info">Digests MOD version  ::</span></td>
		<td align="left" width="20%" valign="middle"><span class="info"><? print DIGEST_MOD_VERSION ?></span></td>
	</tr>
	<tr>
		<td align="right" width="6%" valign="middle"><span class="info">Previous Digest version  ::</span></td>
		<td align="left" width="20%" valign="middle"><span class="info"><? print $digest_version ?></span></td>
	</tr>
	<tr>
		<td align="left" width="100%" valign="middle" colspan="2"><span class="maintitle">What are you going to do ?</span></td>
	</tr>
	<tr>
		<td align="left" width="100%" valign="middle" colspan="2"><span class="body">This file will make the necessary changes to your database (adding/modifying tables) to make the Digest MOD work. This may take several minutes depending on the number of digest subscriptions that you have in your database. If you have any problem during this part, you can contact me to get support.</span></td>
	</tr>
	<tr>
		<td align="left" width="100%" valign="middle" colspan="2"><span class="maintitle">Adding/modifying tables to your database</span></td>
	</tr>
</table>

<?php

//
// Create the new tables
//

//
// Create the Digest table
//
$sql = "CREATE TABLE " . DIGEST_TABLE . " (
		digest_id int(6) NOT NULL auto_increment,
		digest_name varchar(25) NULL,
  		user_id mediumint(8) NOT NULL default 0,
		digest_type tinyint(1) NOT NULL default 0,
		digest_activity tinyint(1) NOT NULL default 1,
  		digest_frequency  mediumint(8) NOT NULL default 0,
  		last_digest int(11) NOT NULL default 0,
  		digest_format smallint(4) NOT NULL default 0,
  		digest_show_text smallint(4) NOT NULL default 0,
  		digest_show_mine smallint(4) NOT NULL default 0,
  		digest_new_only smallint(4) NOT NULL default 0,
  		digest_send_on_no_messages smallint(4) NOT NULL default 1,
		digest_moderator tinyint(1) NOT NULL default 0,
		digest_include_forum tinyint(1) NOT NULL default 1,
  		PRIMARY KEY  (digest_id),
		KEY user_id (user_id)
		) TYPE=MyISAM AUTO_INCREMENT=1";

if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}

if (!($result = $db->sql_query($sql)))
{
	$error = $db->sql_error();
	$error_code = TRUE;
	print(" =>> <b><span class=\"error\">ERROR - QUERY FAILED</b> ----> " . $error['message'] . "</span><br /><br />");
}
else
{
	print(" =>> <b><span class=\"ok\">Digest table created</span></b><br /><br />");
}

//
// Create the Forums table
//
$sql = "CREATE TABLE " . DIGEST_FORUMS_TABLE . " (
  		user_id mediumint(8) NOT NULL default 0,
  		forum_id smallint(5) NOT NULL default 0,
		digest_id int(11) NOT NULL default 0,
  		KEY user_id (user_id),
		KEY forum_id (forum_id)
		) TYPE=MyISAM";

if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}

if (!($result = $db->sql_query($sql)))
{
	$error = $db->sql_error();
	$error_code = TRUE;
	print(" =>> <b><span class=\"error\">ERROR - QUERY FAILED</b> ----> " . $error['message'] . "</span><br /><br />");
}
else
{
	print(" =>> <b><span class=\"ok\">Digest Forums table created</span></b><br /><br />");
}

//
// Create the Log table
//
$sql = "CREATE TABLE " . DIGEST_LOG_TABLE . " (
		log_time int(11) NOT NULL default 0,
		run_type tinyint(1) NOT NULL default 0,
  		user_id mediumint(8) NOT NULL default 0,
		digest_frequency mediumint(8) NOT NULL default 0,
		digest_type tinyint(1) NOT NULL default 0,
		group_id mediumint(8) NOT NULL default 1,
		log_status mediumint(2) NOT NULL default 0,
		log_posts int(11) NULL default 0,
		KEY user_id (user_id)
		) TYPE=MyISAM";

if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}

if (!($result = $db->sql_query($sql)))
{
	$error = $db->sql_error();
	$error_code = TRUE;
	print(" =>> <b><span class=\"error\">ERROR - QUERY FAILED</b> ----> " . $error['message'] . "</span><br /><br />");
}
else
{
	print(" =>> <b><span class=\"ok\">Digest Log table created</span></b><br /><br />");
}

//
// Create the Config table
//
$sql = "CREATE TABLE " . DIGEST_CONFIG_TABLE . " (
		config_name varchar(255) NOT NULL default '',
		config_value varchar(255) NOT NULL default 0,
		PRIMARY KEY  (config_name)
		) TYPE=MyISAM";

if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}

if (!($result = $db->sql_query($sql)))
{
	$error = $db->sql_error();
	$error_code = TRUE;
	print(" =>> <b><span class=\"error\">ERROR - QUERY FAILED</b> ----> " . $error['message'] . "</span><br /><br />");
}
else
{
	print(" =>> <b><span class=\"ok\">Digest Config table created</span></b><br /><br />");
}

//
// Populate the config table
//
$sql = "INSERT INTO " . DIGEST_CONFIG_TABLE . " 
		(config_name, config_value) VALUES 
		('activity_threshold', '90'),
		('allow_direct_run', '0'),
		('allow_daily', '1'),
		('allow_exclude', '0'),
		('allow_hours1', '0'),
		('allow_hours2', '0'),
		('allow_hours4', '0'),
		('allow_hours6', '0'),
		('allow_hours8', '0'),
		('allow_hours12', '0'),
		('allow_monthly', '0'),
		('allow_urgent', '0'),
		('allow_weekly', '1'),
		('auto_subscribe', '0'),
		('auto_subscribe_group', '0'),
		('check_user_activity', '0'),
		('default_format', '1'),
		('default_frequency', '24'),
		('default_new_only', '1'),
		('default_send_on_no_messages', '0'),
		('default_show_mine', '0'),
		('default_show_text', '1'),
		('default_text_length_type', '1'),
		('digest_date_format', 'D d-M-Y \a\t H:i:s'),
		('digest_disable_group', '0'),
		('digest_disable_user', '0'),
		('digest_logging', '0'),
		('digest_subject', ''),
		('digest_theme', '1'),
		('digest_version', '1.3.4'),
		('direct_password', ''),
		('home_page', 'index'),
		('log_days', '8'),
		('monthly_day', '28'),
		('new_sign_up', '0'),
		('override_theme', '1'),
		('pm_notify', '0'),
		('pm_display', '0'),
		('run_urgent_only', '1'),
		('run_time', '18'),
		('short_text_length', '150'),
		('show_forum_description', '0'),
		('show_ip', '0'),
		('show_stats', '1'),
		('supress_cron_output', '0'),
		('test_mode', '1'),
		('theme_type', '0'),
		('urgent_run_required', '1'),
		('use_system_time', '1'),
		('weekly_day', '0')";

if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}

if (!($result = $db->sql_query($sql)))
{
	$error = $db->sql_error();
	$error_code = TRUE;
	print(" =>> <b><span class=\"error\">ERROR - QUERY FAILED</b> ----> " . $error['message'] . "</span><br /><br />");
}
else
{
	print(" =>> <b><span class=\"ok\">Digest Config table populated</span></b><br /><br />");
}

//
// Modify the Users table
//
$sql = "ALTER TABLE " . USERS_TABLE . " 
		ADD
		user_digest_status tinyint(1) NOT NULL default '0'";

if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}

if (!($result = $db->sql_query($sql)))
{
	$error = $db->sql_error();
	$error_code = TRUE;
	print(" =>> <b><span class=\"error\">ERROR - QUERY FAILED</b> ----> " . $error['message'] . "</span><br /><br />");
}
else
{
	print(" =>> <b><span class=\"ok\">User table updated</span></b><br /><br />");
}

//
// Modify the User Group table
//
$sql = "ALTER TABLE " . USER_GROUP_TABLE . " 
		ADD
		digest_confirm_date int(11) NOT NULL default '0'";

if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}

if (!($result = $db->sql_query($sql)))
{
	$error = $db->sql_error();
	$error_code = TRUE;
	print(" =>> <b><span class=\"error\">ERROR - QUERY FAILED</b> ----> " . $error['message'] . "</span><br /><br />");
}
else
{
	print(" =>> <b><span class=\"ok\">User group table updated</span></b><br /><br />");
}

//
// Modify the Forums table
//
$sql = "ALTER TABLE " . FORUMS_TABLE . " 
		ADD
		forum_digest tinyint(1) NOT NULL default '1'";

if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}

if (!($result = $db->sql_query($sql)))
{
	$error = $db->sql_error();
	$error_code = TRUE;
	print(" =>> <b><span class=\"error\">ERROR - QUERY FAILED</b> ----> " . $error['message'] . "</span><br /><br />");
}
else
{
	print(" =>> <b><span class=\"ok\">Forums table updated</span></b><br /><br />");
}

//
// Modify the Groups table
//
$sql = "ALTER TABLE " . GROUPS_TABLE . " 
		ADD
		group_digest tinyint(1) NOT NULL default '0'";

if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}

if (!($result = $db->sql_query($sql)))
{
	$error = $db->sql_error();
	$error_code = TRUE;
	print(" =>> <b><span class=\"error\">ERROR - QUERY FAILED</b> ----> " . $error['message'] . "</span><br /><br />");
}
else
{
	print(" =>> <b><span class=\"ok\">Groups table updated</span></b><br /><br />");
}

//
// Modify the Posts table
//
$sql = "ALTER TABLE " . POSTS_TABLE . " 
		ADD
		urgent_post tinyint(1) NOT NULL default '0'";

if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}

if (!($result = $db->sql_query($sql)))
{
	$error = $db->sql_error();
	$error_code = TRUE;
	print(" =>> <b><span class=\"error\">ERROR - QUERY FAILED</b> ----> " . $error['message'] . "</span><br /><br />");
}
else
{
	print(" =>> <b><span class=\"ok\">Posts table updated</span></b><br /><br />");
}

//
// Update from a previous version
//
if (($error_code == FALSE) || (($error_code == TRUE) && ($ignore_error_code == TRUE)))
{
	switch (substr($digest_version, 2, 1))
	{
		case 1: // Update from version 1.1.x
			$old_sql = "SELECT * FROM " . SUBSCRIPTIONS_TABLE;
			$result = $db->sql_query($old_sql);			
			
			while($row = $db->sql_fetchrow($result))
			{			
				$sql = "INSERT INTO " . DIGEST_TABLE . " (
					user_id, digest_type, digest_frequency, last_digest, digest_format, digest_show_text, digest_show_mine, digest_new_only, digest_send_on_no_messages, digest_text_length
					) VALUES (
					'" . $row['user_id'] . "',
					'" . 0 . "',
					'" . (($row['digest_type'] == 'DAY') ? 24 : 168) . "', 
					'" . time() . "', 
					'" . (($row['format'] == 'HTML') ? DIGEST_HTML : DIGEST_TEXT) . "', 
					'" . (($row['show_text'] == 'YES') ? true : false) . "', 
					'" . (($row['show_mine'] == 'YES') ? true : false) . "', 
					'" . (($row['new_only'] == 'TRUE') ? true : false) . "', 
					'" . (($row['send_on_no_messages'] == 'YES') ? true : false) . "', 
					'" . (($row['text_length'] == '32000') ? '-1' : $row['text_length']) . "')";

				if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}
				$result_2 = $db->sql_query($sql);
			}

			print(" =>> <b><span class=\"ok\">Digest table data transferred from your previous version</span></b><br /><br />");

			$old_sql = "SELECT s.*, d.digest_id, d.user_id
				FROM " . SUBSCRIBED_FORUMS_TABLE . " s, " . DIGEST_TABLE . " d
				WHERE d.user_id = s.user_id";
			
			$result = $db->sql_query($old_sql);
			
			while($row = $db->sql_fetchrow($result))
			{
				$sql = "INSERT INTO " . DIGEST_FORUMS_TABLE . " (
					user_id, forum_id, digest_id
					) VALUES (
					'" . $row['user_id'] . "', 
					'" . $row['forum_id'] . "',
					'" . $row['digest_id'] . "')";

				if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}
				$result_2 = $db->sql_query($sql);
			}

			print(" =>> <b><span class=\"ok\">Digest Forum table data transferred from your previous version</span></b><br /><br />");

			$sql = "ALTER TABLE " . SUBSCRIPTIONS_TABLE . " RENAME old_" . SUBSCRIPTIONS_TABLE;
			if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}
			$result = $db->sql_query($sql);

			$sql = "ALTER TABLE " . SUBSCRIBED_FORUMS_TABLE . " RENAME old_" . SUBSCRIBED_FORUMS_TABLE;
			if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}
			$result = $db->sql_query($sql);

			print(" =>> <b><span class=\"ok\">Renaming previous tables</span></b><br /><br />");

			$remove = "<br /><b>You may delete :: old_" . SUBSCRIPTIONS_TABLE . " and old_" . SUBSCRIBED_FORUMS_TABLE . " tables once you are satisfied that the update is OK.</b><br />";

			break;

		case 2: // Update from version 1.2.x
			$old_sql = "SELECT * FROM " . OLD_DIGEST_TABLE;
			$result = $db->sql_query($old_sql);			
			
			while($row = $db->sql_fetchrow($result))
			{			
				if ($row['text_length'] == -1)
				{
					$row['show_text'] == -1;
				}
				$sql = "INSERT INTO " . DIGEST_TABLE . " (
					user_id, digest_type, digest_frequency, last_digest, digest_format, digest_show_text, digest_show_mine, digest_new_only, digest_send_on_no_messages
					) VALUES (
					'" . $row['user_id'] . "', 
					'" . 0 . "',
					'" . $row['digest_frequency'] . "', 
					'" . $row['last_digest'] . "', 
					'" . $row['format'] . "', 
					'" . $row['show_text'] . "', 
					'" . $row['show_mine'] . "', 
					'" . $row['new_only'] . "', 
					'" . $row['send_on_no_messages'] . "')";

				if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}
				$result2 = $db->sql_query($sql);
			}

			print(" =>> <b><span class=\"ok\">Digest table data transferred from your previous version</span></b><br /><br />");

			$old_sql = "SELECT o.*, d.digest_id, d.user_id
				FROM " . OLD_DIGEST_FORUMS_TABLE . " o, " . DIGEST_TABLE . " d
				WHERE d.user_id = o.user_id";
			
			$result = $db->sql_query($old_sql);
			
			while($row = $db->sql_fetchrow($result))
			{
				$sql = "INSERT INTO " . DIGEST_FORUMS_TABLE . " (
					user_id, forum_id, digest_id
					) VALUES (
					'" . $row['user_id'] . "',
					'" . $row['forum_id'] . "',
					'" . $row['digest_id'] . "')";

				if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}
				$result2 = $db->sql_query($sql);
			}

			print(" =>> <b><span class=\"ok\">Digest Forum table data transferred from your previous version</span></b><br /><br />");

			$sql = "ALTER TABLE " . OLD_DIGEST_TABLE . " RENAME old_" . OLD_DIGEST_TABLE;
			if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}
			$result = $db->sql_query($sql);

			$sql = "ALTER TABLE " . OLD_DIGEST_FORUMS_TABLE . " RENAME old_" . OLD_DIGEST_FORUMS_TABLE;
			if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}
			$result = $db->sql_query($sql);

			print(" =>> <b><span class=\"ok\">Renaming previous tables</span></b><br /><br />");

			$remove = "<br /><b>You may delete :: old_" . OLD_DIGEST_TABLE . " and old_" . OLD_DIGEST_FORUMS_TABLE . " tables once you are satisfied that the update is OK.</b><br />";
			break;
	}

	print(" =>> <b><span class=\"ok\">Verifying the data</span></b><br /><br />");

	// Verify the digest data
	$sql = "SELECT *
		FROM  " . DIGEST_TABLE . " 
		WHERE digest_type = 0";

	if ($verbose) {print 'Running query :: ' . $sql .'<br /><br />';}
	$result = $db->sql_query($sql);

	while($row = $db->sql_fetchrow($result))
	{
		$user_id = $row['user_id'];

		$sql2 = "SELECT *
			FROM " . USERS_TABLE . "
			WHERE user_id = $user_id";

		if ($verbose) {print 'Running query :: ' . $sql2 .'<br /><br />';}
		$result2 = $db->sql_query($sql2);
	
		$row2 = $db->sql_fetchrow($result2);

		if ($row2 == '')
		{
			$sql3 = "DELETE FROM " . DIGEST_TABLE . "
				WHERE user_id = '$user_id'
				AND digest_type = 0";

			if ($verbose) {print 'Running query :: ' . $sql3 .'<br /><br />';}
			$result3 = $db->sql_query($sql3);

			$sql3 = "DELETE FROM " . DIGEST_FORUMS_TABLE . "
				WHERE user_id = '$user_id'";

			if ($verbose) {print 'Running query :: ' . $sql3 .'<br /><br />';}
			$result3 = $db->sql_query($sql3);
		}
	}

	print(" =>> <b><span class=\"ok\">Transferred data verified - any redundant users have been deleted from the digest tables</span></b><br /><br />");
}
print '<span class="body"> *******************************************************************************************************************<br />*******************************************************************************************************************<br /><br />';
if ($error_code)
{
	print '<b>At least one query failed : check the error message and contact me if you need help to resolve the problem.</b><br />';
}
else
{
	print '<b>COMPLETE - INSTALLATION SUCCESSFUL</b><br />';
	print $remove;
	print '<br /><b>NOW, DELETE THIS FILE FROM YOUR SERVER</b><br />';
}

print '<br />*******************************************************************************************************************<br />*******************************************************************************************************************<br /><br /></span>';

?>
</body>
</html>
Return current item: phpBB Email Digests