Php BB-Upgrade

Home > HowTo > Php BB-Upgrade

Upgrade von phpBB-1.4.2 nach phpBB-2.0.14

mySQL-Upgrade

Die Tabellenstruktur hat sich von Version 1.4.2 nach 2.0.14 wesentlich weiterentwickelt.

Es gilt nun, die vorhandene Datenstruktur bei Erhalt der Daten auf den neusten Stand zu bringen. Zuvor sollte natürlich eine Datensicherung erfolgen.

Wenn auf dem Webserver keine Shell verfügbar ist, ist es sicher sinnvoll, die Daten zunächst zu sichern und die Umstellung auf einer lokalen Maschine vorzunehmen.

Im Beispiel seien alle Tabellen mit dem Prefix bb_ versehen.

Tabelle bb_users

Größte Schwierigkeit bei dieser Tabelle ist die Formatänderung des Feldes user_regdate, welches sich von einem String im Format “Dec 24, 2004″ in das UNIX_TIMESTAMP-Format 1103842800 geändert hat.

Am schnellsten schien hier die Zuhilfenahme eines kleines Bash-Scriptes, welches das Feld ausliest, in ein für mySQL greifbaren String umwandelt und dann in das (zunächst noch als varchar formatierte) Feld user_regdate zurückschreibt:

#!/bin/sh
#

# Scriptaufgabe:
#
# LUG-Forum mySQL-DB-Tabelle user, Feld user_regdate
#
# Typ in phpBB-1.4.2:  string('Oct 01, 2001')
# Typ in phpBB-2.0.14: integer(1123214565)
#
# (C) 2005 Thomas Kieschnik * Inspirit Systems

# Alle Datensaetze durchgehen, datumsstring umformatieren, und mittels mysql-Funktion UNIX_TIMESTAMP  ersetzen

# Die Vorgaben hier anpassen!!
DB="phpBB142lug"
TABLE="bb_users"
HOST="hostname"
USER="root"


TEMPFILE=`mktemp /tmp/$0.XXXXXX` || exit 1

echo -n "Bitte mySQL-Password fuer $USER: "
read PASSWD

myEXEC="mysql -u $USER -h $HOST -D $DB -p$PASSWD"

echo
echo "select user_id,';',user_regdate from $TABLE" | $myEXEC | grep -v user_id > $TEMPFILE

while read ID TRENNER DATUM_STR; do
    echo -n "$ID -- $DATUM_STR  --  "

    MONTH_STR=`echo $DATUM_STR | awk '{print $1}'`
    DAY=`echo $DATUM_STR | awk '{print $2}' | sed s/,//`
    YEAR=`echo $DATUM_STR | awk '{print $3}'`

    case $MONTH_STR in
	Jan) MONTH=01 ;;
	Feb) MONTH=02 ;;
	Mar) MONTH=03 ;;
	Apr) MONTH=04 ;;
	May) MONTH=05 ;;
	Jun) MONTH=06 ;;
	Jul) MONTH=07 ;;
	Aug) MONTH=08 ;;
	Sep) MONTH=09 ;;
	Oct) MONTH=10 ;;
	Nov) MONTH=11 ;;
	Dec) MONTH=12 ;;
	*)   MONTH=ERR ;;
    esac


    MYSQL_DATE="$YEAR-$MONTH-$DAY"
    echo $MYSQL_DATE

    echo "UPDATE $TABLE SET user_regdate = UNIX_TIMESTAMP('$MYSQL_DATE') WHERE user_id = $ID;" | $myEXEC

done < $TEMPFILE

rm $TEMPFILE
 

Wenn bei der Umwandlung keine Fehler aufgetreten sind, sollte es nun gelingen, mit folgenden SQL-Statements die Tabelle bei Erhalt aller Daten in die neue Form zu bekommen:

ALTER TABLE `bb_users` ADD `user_active` TINYINT DEFAULT '1' AFTER `user_id`
ALTER TABLE `bb_users` CHANGE `username` `username` VARCHAR( 25 ) NOT NULL
ALTER TABLE `bb_users` ADD `user_session_time` INT DEFAULT '0' NOT NULL AFTER `user_password` ;
ALTER TABLE `bb_users` ADD `user_session_page` SMALLINT DEFAULT '0' NOT NULL AFTER `user_session_time` ;
ALTER TABLE `bb_users` ADD `user_lastvisit` INT DEFAULT '0' NOT NULL AFTER `user_session_page` ;
ALTER TABLE `bb_users` CHANGE `user_level` `user_level` TINYINT( 10 ) DEFAULT '0' ;
ALTER TABLE `bb_users` CHANGE `user_posts` `user_posts` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ;
ALTER TABLE `bb_users` ADD `user_timezone` DECIMAL( 5, 2 ) DEFAULT '0.00' NOT NULL AFTER `user_posts` ;
ALTER TABLE `bb_users` ADD `user_style` TINYINT AFTER `user_timezone` ;
ALTER TABLE `bb_users` ADD `user_dateformat` VARCHAR( 14 ) DEFAULT 'd M Y H:i' NOT NULL AFTER `user_lang` ;
ALTER TABLE `bb_users` ADD `user_new_privmsg` SMALLINT UNSIGNED DEFAULT '0' NOT NULL AFTER `user_dateformat` ;
ALTER TABLE `bb_users` ADD `user_unread_privmsg` SMALLINT UNSIGNED DEFAULT '0' NOT NULL AFTER `user_new_privmsg` ,
ADD `user_last_privmsg` INT DEFAULT '0' NOT NULL AFTER `user_unread_privmsg` ;
ALTER TABLE `bb_users` ADD `user_emailtime` INT AFTER `user_email` ;
ALTER TABLE `bb_users` CHANGE `user_viewemail` `user_viewemail` TINYINT( 1 ) DEFAULT NULL ;
ALTER TABLE `bb_users` CHANGE `user_attachsig` `user_attachsig` TINYINT( 1 ) DEFAULT '0' ;
ALTER TABLE `bb_users` CHANGE `user_html` `user_allowhtml` TINYINT( 1 ) DEFAULT '1' ;
ALTER TABLE `bb_users` CHANGE `user_bbcode` `user_allowbbcode` TINYINT( 1 ) DEFAULT '1' ;
ALTER TABLE `bb_users` CHANGE `user_desmile` `user_allowsmile` TINYINT( 1 ) DEFAULT '1' ;
ALTER TABLE `bb_users` ADD `user_allowavatar` TINYINT( 1 ) DEFAULT '1' NOT NULL AFTER `user_allowbbcode` ,
ADD `user_allow_pm` TINYINT( 1 ) DEFAULT '1' NOT NULL AFTER `user_allowavatar` ;
ALTER TABLE `bb_users` ADD `user_notify` TINYINT( 1 ) DEFAULT '1' NOT NULL AFTER `user_allow_pm` ,
ADD `user_notify_pm` TINYINT( 1 ) DEFAULT '0' NOT NULL AFTER `user_notify` ,
ADD `user_popup_pm` TINYINT( 1 ) DEFAULT '0' NOT NULL AFTER `user_notify_pm` ,
ADD `user_allow_viewonline` TINYINT( 1 ) DEFAULT '1' NOT NULL AFTER `user_popup_pm` ;
ALTER TABLE `bb_users` CHANGE `user_rank` `user_rank` INT( 11 ) DEFAULT '0' ;
ALTER TABLE `bb_users` ADD `user_avatar` VARCHAR( 100 ) AFTER `user_rank` ,
ADD `user_avatar_type` TINYINT( 1 ) DEFAULT '0' NOT NULL AFTER `user_avatar` ;
ALTER TABLE `bb_users` CHANGE `user_email` `user_email` VARCHAR( 255 ) DEFAULT NULL ;
ALTER TABLE `bb_users` CHANGE `user_sig` `user_sig` TEXT DEFAULT NULL ;
ALTER TABLE `bb_users` ADD `user_sig_bbcode_uid` VARCHAR( 10 ) AFTER `user_sig` ;
ALTER TABLE `bb_users` CHANGE `user_aim` `user_aim` VARCHAR( 255 ) DEFAULT NULL ;
ALTER TABLE `bb_users` CHANGE `user_yim` `user_yim` VARCHAR( 255 ) DEFAULT NULL ;
ALTER TABLE `bb_users` CHANGE `user_msnm` `user_msnm` VARCHAR( 255 ) DEFAULT NULL ;
ALTER TABLE `bb_users` CHANGE `user_intrest` `user_interests` VARCHAR( 255 ) DEFAULT NULL ;
ALTER TABLE `bb_users` DROP `user_theme` ;
ALTER TABLE `bb_users` CHANGE `user_regdate` `user_regdate` INT( 11 ) DEFAULT NULL
ALTER TABLE `bb_users` ADD INDEX ( `user_session_time` ) ;

Ausserdem braucht es einen User mit “user_id = −1”, der - wenn nicht schon vorhanden - nun noch angelegt werden muss. Zudem ist ein wichtiger Parameter anzupassen:

#

#

INSERT INTO `bb_users` (`user_id`, `user_active`, `username`, `user_password`,
`user_session_time`, `user_session_page`, `user_lastvisit`, `user_regdate`,
`user_level`, `user_posts`, `user_timezone`, `user_style`, `user_lang`,
`user_dateformat`, `user_new_privmsg`, `user_unread_privmsg`,
`user_last_privmsg`, `user_emailtime`, `user_viewemail`, `user_attachsig`,
`user_allowhtml`, `user_allowbbcode`, `user_allowsmile`, `user_allowavatar`,
`user_allow_pm`, `user_allow_viewonline`, `user_notify`, `user_notify_pm`,
`user_popup_pm`, `user_rank`, `user_avatar`, `user_avatar_type`, `user_email`,
`user_icq`, `user_website`, `user_from`, `user_sig`, `user_sig_bbcode_uid`,
`user_aim`, `user_yim`, `user_msnm`, `user_occ`, `user_interests`,
`user_actkey`, `user_newpasswd`) VALUES (-1, 0, 'Anonymous', '', 0, 0, 0,
1115308471, 0, 0, '0.00', NULL, '', '', 0, 0, 0, NULL, 0, 0, 1, 1, 1, 1, 0, 1,
0, 1, 0, NULL, '', 0, '', '', '', '', '', NULL, '', '', '', '', '', '', '') ;

#
# Der User "Admin" - nur falls er noch nicht existiert! Ggf. ID korrigieren!
#
INSERT INTO `bb_users` (`user_id`, `user_active`, `username`, `user_password`,
`user_session_time`, `user_session_page`, `user_lastvisit`, `user_regdate`,
`user_level`, `user_posts`, `user_timezone`, `user_style`, `user_lang`,
`user_dateformat`, `user_new_privmsg`, `user_unread_privmsg`,
`user_last_privmsg`, `user_emailtime`, `user_viewemail`, `user_attachsig`,
`user_allowhtml`, `user_allowbbcode`, `user_allowsmile`, `user_allowavatar`,
`user_allow_pm`, `user_allow_viewonline`, `user_notify`, `user_notify_pm`,
`user_popup_pm`, `user_rank`, `user_avatar`, `user_avatar_type`, `user_email`,
`user_icq`, `user_website`, `user_from`, `user_sig`, `user_sig_bbcode_uid`,
`user_aim`, `user_yim`, `user_msnm`, `user_occ`, `user_interests`,
`user_actkey`, `user_newpasswd`) VALUES (2, 1, 'admin', '12121212121212', 0, 1,
0, 0, 1, 1, '0.00', 1, 'english', 'd M Y h:i a', 0, 0, 0, NULL, 1, 0, 0, 1, 1,
1, 1, 1, 0, 1, 1, 1, '', 0, 'admin@domain.tld', '', '', '', '', NULL, '', '',
'', '', '', '', '');


#
# Einige User-Einstellungen - ACHTUNG - richtiges Admin-Passwort einsetzen!
#
UPDATE `bb_users` SET `user_level` = '0' ;

UPDATE `bb_users` SET
`user_level` = '1' ,
`user_password` = MD5( 'neues_admin-passwort' ) ,
WHERE `username` = 'admin' ;


Tabelle bb_auth_access (vorher bb_access)

Die alte Tabelle bb_access hat folgende Struktur + Inahlt:

access_idaccess_title
−1Deleted
1User
2Moderator
3Super Moderator
4Administrator

Ob die Tabelle bb_auth_access der direkte Nachfolger ist, wird sich noch zeigen…

Die neue Tabelle wird zunächst einmal leer angelegt


#
# alte Tabelle erst einmal beiseite legen
#
ALTER TABLE `bb_access` RENAME `off_bb_access`

#

#

CREATE TABLE `bb_auth_access` (
`group_id` mediumint(8) NOT NULL default '0',
`forum_id` smallint(5) unsigned NOT NULL default '0',
`auth_view` tinyint(1) NOT NULL default '0',
`auth_read` tinyint(1) NOT NULL default '0',
`auth_post` tinyint(1) NOT NULL default '0',
`auth_reply` tinyint(1) NOT NULL default '0',
`auth_edit` tinyint(1) NOT NULL default '0',
`auth_delete` tinyint(1) NOT NULL default '0',
`auth_sticky` tinyint(1) NOT NULL default '0',
`auth_announce` tinyint(1) NOT NULL default '0',
`auth_vote` tinyint(1) NOT NULL default '0',
`auth_pollcreate` tinyint(1) NOT NULL default '0',
`auth_attachments` tinyint(1) NOT NULL default '0',
`auth_mod` tinyint(1) NOT NULL default '0',
KEY `group_id` (`group_id`),
KEY `forum_id` (`forum_id`)
) TYPE=MyISAM;

Tabelle bb_banlist

ALTER TABLE `bb_banlist` DROP `ban_start` ,
DROP `ban_end` ,
DROP `ban_time_type` ;
ALTER TABLE `bb_banlist` ADD `ban_email` VARCHAR( 255 ) DEFAULT NULL ;
ALTER TABLE `bb_banlist`
CHANGE `ban_userid` `ban_userid` MEDIUMINT( 8 ) DEFAULT '0' NOT NULL ,
CHANGE `ban_ip` `ban_ip` VARCHAR( 8 ) NOT NULL ;
ALTER TABLE `bb_banlist`
DROP INDEX `ban_id` ,
ADD INDEX `ban_ip_user_id` ( `ban_ip` , `ban_userid` ) ;

Tabelle bb_categories

ALTER TABLE `bb_categories`
CHANGE `cat_id` `cat_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
CHANGE `cat_order` `cat_order` MEDIUMINT( 8 ) UNSIGNED DEFAULT '0' NOT NULL ;
ALTER TABLE `bb_categories` ADD INDEX ( `cat_order` ) ;

Tabelle bb_config

Der Aufbau hat sich komplett geändert. Statt je einem Feld pro Parameter wird nun je ein Key-basierter Satz pro Parameter verwendet. Deshalb legen wir die alte Tabelle erst einmal beiseite und legen eine neue mit Standardvorgaben an. Eine Anpassung kann dann per Admin-Interface oder auch direkt bzw. per phpMyAdmin vorgenommen werden.

#
# alte Tabelle beiseite legen
#
ALTER TABLE `bb_config` RENAME `off_bb_config`;

#

#

CREATE TABLE `bb_config` (
`config_name` varchar(255) NOT NULL default '',
`config_value` varchar(255) NOT NULL default '',
PRIMARY KEY  (`config_name`)
) TYPE=MyISAM;


#

#

INSERT INTO `bb_config` VALUES ('config_id', '1');
INSERT INTO `bb_config` VALUES ('board_disable', '0');
INSERT INTO `bb_config` VALUES ('sitename', 'lugbz.de');
INSERT INTO `bb_config` VALUES ('site_desc', 'LUG-BZ Forum');
INSERT INTO `bb_config` VALUES ('cookie_name', 'phpbb2mysql');
INSERT INTO `bb_config` VALUES ('cookie_path', '/');
INSERT INTO `bb_config` VALUES ('cookie_domain', '');
INSERT INTO `bb_config` VALUES ('cookie_secure', '0');
INSERT INTO `bb_config` VALUES ('session_length', '3600');
INSERT INTO `bb_config` VALUES ('allow_html', '0');
INSERT INTO `bb_config` VALUES ('allow_html_tags', 'b,i,u,pre');
INSERT INTO `bb_config` VALUES ('allow_bbcode', '1');
INSERT INTO `bb_config` VALUES ('allow_smilies', '1');
INSERT INTO `bb_config` VALUES ('allow_sig', '1');
INSERT INTO `bb_config` VALUES ('allow_namechange', '0');
INSERT INTO `bb_config` VALUES ('allow_theme_create', '0');
INSERT INTO `bb_config` VALUES ('allow_avatar_local', '0');
INSERT INTO `bb_config` VALUES ('allow_avatar_remote', '0');
INSERT INTO `bb_config` VALUES ('allow_avatar_upload', '0');
INSERT INTO `bb_config` VALUES ('enable_confirm', '0');
INSERT INTO `bb_config` VALUES ('override_user_style', '0');
INSERT INTO `bb_config` VALUES ('posts_per_page', '15');
INSERT INTO `bb_config` VALUES ('topics_per_page', '50');
INSERT INTO `bb_config` VALUES ('hot_threshold', '25');
INSERT INTO `bb_config` VALUES ('max_poll_options', '10');
INSERT INTO `bb_config` VALUES ('max_sig_chars', '255');
INSERT INTO `bb_config` VALUES ('max_inbox_privmsgs', '50');
INSERT INTO `bb_config` VALUES ('max_sentbox_privmsgs', '25');
INSERT INTO `bb_config` VALUES ('max_savebox_privmsgs', '50');
INSERT INTO `bb_config` VALUES ('board_email_sig', 'Thanks, The Management');
INSERT INTO `bb_config` VALUES ('board_email', 'bb.admin@lugbz.de');
INSERT INTO `bb_config` VALUES ('smtp_delivery', '0');
INSERT INTO `bb_config` VALUES ('smtp_host', '');
INSERT INTO `bb_config` VALUES ('smtp_username', '');
INSERT INTO `bb_config` VALUES ('smtp_password', '');
INSERT INTO `bb_config` VALUES ('sendmail_fix', '0');
INSERT INTO `bb_config` VALUES ('require_activation', '0');
INSERT INTO `bb_config` VALUES ('flood_interval', '15');
INSERT INTO `bb_config` VALUES ('board_email_form', '0');
INSERT INTO `bb_config` VALUES ('avatar_filesize', '6144');
INSERT INTO `bb_config` VALUES ('avatar_max_width', '80');
INSERT INTO `bb_config` VALUES ('avatar_max_height', '80');
INSERT INTO `bb_config` VALUES ('avatar_path', 'images/avatars');
INSERT INTO `bb_config` VALUES ('avatar_gallery_path', 'images/avatars/gallery');
INSERT INTO `bb_config` VALUES ('smilies_path', 'images/smiles');
INSERT INTO `bb_config` VALUES ('default_style', '1');
INSERT INTO `bb_config` VALUES ('default_dateformat', 'D M d, Y g:i a');
INSERT INTO `bb_config` VALUES ('board_timezone', '2');
INSERT INTO `bb_config` VALUES ('prune_enable', '1');
INSERT INTO `bb_config` VALUES ('privmsg_disable', '0');
INSERT INTO `bb_config` VALUES ('gzip_compress', '0');
INSERT INTO `bb_config` VALUES ('coppa_fax', '');
INSERT INTO `bb_config` VALUES ('coppa_mail', '');
INSERT INTO `bb_config` VALUES ('record_online_users', '1');
INSERT INTO `bb_config` VALUES ('record_online_date', '1115308675');
INSERT INTO `bb_config` VALUES ('server_name', 'www.lugbz.de');
INSERT INTO `bb_config` VALUES ('server_port', '80');
INSERT INTO `bb_config` VALUES ('script_path', '/forum/');
INSERT INTO `bb_config` VALUES ('version', '.0.14');
INSERT INTO `bb_config` VALUES ('board_startdate', '1115308471');
INSERT INTO `bb_config` VALUES ('default_lang', 'german');

Tabelle bb_confirm

Diese Tabelle ist neu und braucht nur neu angelegt zu werden:

#

#

CREATE TABLE `bb_confirm` (
`confirm_id` char(32) NOT NULL default '',
`session_id` char(32) NOT NULL default '',
`code` char(6) NOT NULL default '',
PRIMARY KEY  (`session_id`,`confirm_id`)
) TYPE=MyISAM;

Tabelle bb_disallow

ALTER TABLE `bb_disallow`
CHANGE `disallow_id` `disallow_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
CHANGE `disallow_username` `disallow_username` VARCHAR( 25 ) NOT NULL

Tabelle bb_forum_access

kommt im neuen Forum nicht vor und wird erst einmal beiseite gelegt…

ALTER TABLE `bb_forum_access` RENAME `tmp_off_bb_forum_access`

Tabelle bb_forum_prune

ist neu und muss angelegt werden:

#

#
CREATE TABLE `bb_forum_prune` (
`prune_id` mediumint(8) unsigned NOT NULL auto_increment,
`forum_id` smallint(5) unsigned NOT NULL default '0',
`prune_days` smallint(5) unsigned NOT NULL default '0',
`prune_freq` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY  (`prune_id`),
KEY `forum_id` (`forum_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;


Tabelle bb_forum_mods

kommt nicht mehr vor und wird erst einmal beiseite gelegt…

ALTER TABLE `bb_forum_mods` RENAME `tmp_off_bb_forum_mods`

Tabelle bb_forums

ALTER TABLE `bb_forums`
DROP `forum_access` ,
DROP `forum_moderator` ,
DROP `forum_type` ;

ALTER TABLE `bb_forums`
CHANGE `forum_id` `forum_id` SMALLINT( 5 ) UNSIGNED DEFAULT '0' NOT NULL,
CHANGE `forum_topics` `forum_topics` MEDIUMINT( 8 ) UNSIGNED DEFAULT '0' NOT NULL,
CHANGE `forum_posts` `forum_posts` MEDIUMINT( 8 ) UNSIGNED DEFAULT '0' NOT NULL,
CHANGE `forum_last_post_id` `forum_last_post_id` MEDIUMINT( 8 ) UNSIGNED DEFAULT '0' NOT NULL ,
CHANGE `cat_id` `cat_id` MEDIUMINT( 8 ) UNSIGNED DEFAULT '0' NOT NULL
ALTER TABLE `bb_forums`
ADD `forum_status` TINYINT NOT NULL AFTER `forum_desc` ,
ADD `forum_order` MEDIUMINT UNSIGNED DEFAULT '1' NOT NULL AFTER `forum_status` ;
ALTER TABLE `bb_forums` ADD `prune_next` INT,
ADD `prune_enable` TINYINT( 1 ) DEFAULT '0' NOT NULL ,
ADD `auth_view` TINYINT( 2 ) DEFAULT '0' NOT NULL ,
ADD `auth_read` TINYINT( 2 ) DEFAULT '0' NOT NULL ,
ADD `auth_post` TINYINT( 2 ) DEFAULT '0' NOT NULL ,
ADD `auth_reply` TINYINT( 2 ) DEFAULT '0' NOT NULL ,
ADD `auth_edit` TINYINT( 2 ) DEFAULT '0' NOT NULL ,
ADD `auth_delete` TINYINT( 2 ) DEFAULT '0' NOT NULL ,
ADD `auth_sticky` TINYINT( 2 ) DEFAULT '0' NOT NULL ,
ADD `auth_announce` TINYINT( 2 ) DEFAULT '0' NOT NULL ,
ADD `auth_vote` TINYINT( 2 ) DEFAULT '0' NOT NULL ,
ADD `auth_pollcreate` TINYINT( 2 ) DEFAULT '0' NOT NULL ,
ADD `auth_attachments` TINYINT( 2 ) DEFAULT '0' NOT NULL ;
ALTER TABLE `bb_forums`
ADD INDEX ( `forum_order` ) ,
ADD INDEX ( `cat_id` ) ;

#

#
UPDATE `bb_forums` SET `auth_edit` = '1',
`auth_delete` = '1',
`auth_sticky` = '3',
`auth_announce` = '3',
`auth_vote` = '1',
`auth_pollcreate` = '1',
`auth_attachments` = '3';

Tabelle bb_headermetafooter

DROP TABLE `bb_headermetafooter`

Tabelle bb_groups

muss neu angelegt werden:

#

#

CREATE TABLE `bb_groups` (
`group_id` mediumint(8) NOT NULL auto_increment,
`group_type` tinyint(4) NOT NULL default '1',
`group_name` varchar(40) NOT NULL default '',
`group_description` varchar(255) NOT NULL default '',
`group_moderator` mediumint(8) NOT NULL default '0',
`group_single_user` tinyint(1) NOT NULL default '1',
PRIMARY KEY  (`group_id`),
KEY `group_single_user` (`group_single_user`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

#

#

INSERT INTO `lugbb_groups` VALUES (1, 1, 'Anonymous', 'Personal User', 0, 1);
INSERT INTO `lugbb_groups` VALUES (2, 1, 'Admin', 'Personal User', 0, 1);
INSERT INTO `lugbb_groups` VALUES (3, 1, '', 'Personal User', 0, 1);

Tabelle bb_posts

IP-Nummern werden im neuen Forum nicht mehr als String der Form 192.168.1.100 gespeichert sondern als Hexadezimalwert der Form C0A80164?. Damit reicht dann auch ein varchar(8) statt einem varchar(16) für das Feld poster_ip.

Da die mySQL-internen Funktionen uns wieder etwas die Hände binden, behelfen wir uns wieder mittels eines kleines Shellspriptes:

#!/bin/sh
#

# Scriptaufgabe:
#
# LUG-Forum mySQL-DB-Tabelle posts, Feld poster_ip
#
# Typ in phpBB-1.4.2:  varchar(16)
# Typ in phpBB-2.0.14: varchar(8)
#
# (C) 2005 Thomas Kieschnik * Inspirit Systems

# Alle Datensaetze durchgehen, IP-String umformatieren von "192.168.1.100" nach "`C0A80164"

DB="phpBB142lug"
TABLE="bb_posts"
HOST="hostname"
USER="root"
TEMPFILE=`mktemp /tmp/$0.XXXXXX` || exit 1

echo -n "Bitte mySQL-Password fuer $USER: "
read PASSWD

myEXEC="mysql -u $USER -h $HOST -D $DB -p$PASSWD"

echo
echo "select post_id,';',poster_ip from $TABLE" | $myEXEC | grep -v post_id > $TEMPFILE

while read ID TRENNER IP_STR; do
    echo -n "$ID -- $IP_STR  --  "

    IP_HEX=`echo $IP_STR | awk -F. '{ IPHEX = sprintf("%02x%02x%02x%02x",$1,$2,$3,$4); print toupper(IPHEX); }'`

    echo $IP_HEX

    echo "UPDATE $TABLE SET poster_ip = '$IP_HEX' WHERE post_id = '$ID';" | $myEXEC

done < $TEMPFILE

rm $TEMPFILE
 

Nun die restlichen Änderungen bzw. Ergänzungen in der Tabelle

#

#
ALTER TABLE `bb_posts` CHANGE `poster_ip` `poster_ip` VARCHAR( 8 ) NOT NULL ;

#
# Zeitformat von post_time an UNIX_TIMESTAMP anpassen
#
UPDATE `bb_posts` SET `post_time` = UNIX_TIMESTAMP( post_time ) ;
ALTER TABLE `bb_posts` CHANGE `post_time` `post_time` INT( 11 ) DEFAULT '0' NOT NULL ;

#

#
ALTER TABLE `bb_posts`
CHANGE `post_id` `post_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
CHANGE `topic_id` `topic_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
CHANGE `forum_id` `forum_id` SMALLINT UNSIGNED DEFAULT '0' NOT NULL ,
CHANGE `poster_id` `poster_id` MEDIUMINT DEFAULT '0' NOT NULL ;
ALTER TABLE `bb_posts`
ADD `post_username` VARCHAR( 25 ) ,
ADD `enable_bbcode` TINYINT( 1 ) DEFAULT '1' NOT NULL ,
ADD `enable_html` TINYINT( 1 ) DEFAULT '0' NOT NULL ,
ADD `enable_smilies` TINYINT( 1 ) DEFAULT '1' NOT NULL ,
ADD `enable_sig` TINYINT( 1 ) DEFAULT '1' NOT NULL ,
ADD `post_edit_time` INT,
ADD `post_edit_count` SMALLINT UNSIGNED DEFAULT '0' NOT NULL ;
ALTER TABLE `bb_posts`
DROP INDEX `post_id` ,
ADD INDEX ( `post_time` ) ;

Tabelle bb_posts_text

ALTER TABLE `bb_posts_text` CHANGE `post_id` `post_id` MEDIUMINT( 10 ) UNSIGNED DEFAULT '0' NOT NULL ;
ALTER TABLE `bb_posts_text` ADD `bbcode_uid` VARCHAR( 10 ) NOT NULL AFTER `post_id` ;
ALTER TABLE `bb_posts_text` ADD `post_subject` VARCHAR( 60 ) AFTER `bbcode_uid` ;

Tabellen bb_privmsgs und neu: bb_privmsgs_text

Privatmails verteilen sich nun auf zwei Tabellen; die bestehende muss also etwas aufgedröselt werden…

# Tabelle bb_priv_msgs mit Inhalt nach bb_privmsgs_text kopieren

CREATE TABLE `bb_privmsgs_text` (
`msg_id` int( 10 ) NOT NULL AUTO_INCREMENT ,
`from_userid` int( 10 ) NOT NULL default '0',
`to_userid` int( 10 ) NOT NULL default '0',
`msg_time` varchar( 20 ) default NULL ,
`poster_ip` varchar( 16 ) default NULL ,
`msg_status` int( 10 ) default '0',
`msg_text` text,
PRIMARY KEY ( `msg_id` ) ,
KEY `msg_id` ( `msg_id` ) ,
KEY `to_userid` ( `to_userid` )
) TYPE = MYISAM ;

INSERT INTO `bb_privmsgs_text` SELECT * FROM `bb_priv_msgs` ;

# Tabelle bb_priv_msgs_text schrumpfen und anpassen

ALTER TABLE `bb_privmsgs_text` DROP `from_userid` ,
DROP `to_userid` ,
DROP `msg_time` ,
DROP `poster_ip` ,
DROP `msg_status` ;
ALTER TABLE `bb_privmsgs_text`
CHANGE `msg_id` `privmsgs_text_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
CHANGE `msg_text` `privmsgs_text` TEXT DEFAULT NULL ;
ALTER TABLE `bb_privmsgs_text` ADD `privmsgs_bbcode_uid` VARCHAR( 10 ) DEFAULT '0' NOT NULL AFTER `privmsgs_text_id` ;
ALTER TABLE `bb_privmsgs_text` DROP INDEX `msg_id` ;

# Tabelle bb_priv_msgs umbenennen und anpassen

ALTER TABLE `bb_priv_msgs` RENAME `bb_privmsgs` ;
ALTER TABLE `bb_privmsgs` DROP `msg_text` ;
UPDATE `bb_privmsgs` SET `msg_time` = UNIX_TIMESTAMP( msg_time ) ;
ALTER TABLE `bb_privmsgs`
DROP INDEX `msg_id` ,
DROP INDEX `to_userid` ,
ADD INDEX ( `privmsgs_from_userid` ) ,
ADD INDEX ( `privmsgs_to_userid` ) ;

ALTER TABLE `bb_priv_msgs` CHANGE `msg_id` `privmsgs_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
CHANGE `from_userid` `privmsgs_from_userid` MEDIUMINT DEFAULT '0' NOT NULL ,
CHANGE `to_userid` `privmsgs_to_userid` MEDIUMINT DEFAULT '0' NOT NULL ,
CHANGE `msg_time` `privmsgs_date` INT DEFAULT '0' NOT NULL ,
CHANGE `poster_ip` `privmsgs_ip` VARCHAR( 8 ) NOT NULL

ALTER TABLE `bb_priv_msgs`
ADD `privmsgs_type` TINYINT( 4 ) DEFAULT '0' NOT NULL AFTER `privmsgs_id` ,
ADD `privmsgs_subject` VARCHAR( 255 ) DEFAULT '0' NOT NULL AFTER `privmsgs_type` ;
ALTER TABLE `bb_priv_msgs`
ADD `privmsgs_enable_bbcode` TINYINT( 1 ) DEFAULT '1' NOT NULL ,
ADD `privmsgs_enable_html` TINYINT( 1 ) DEFAULT '0' NOT NULL ,
ADD `privmsgs_enable_smilies` TINYINT( 1 ) DEFAULT '1' NOT NULL ,
ADD `privmsgs_attach_sig` TINYINT( 1 ) DEFAULT '1' NOT NULL ;

ALTER TABLE `bb_priv_msgs` DROP `msg_status` ;

Tabelle bb_ranks

ALTER TABLE `bb_ranks` DROP `rank_max` ;
ALTER TABLE `bb_ranks`
CHANGE `rank_id` `rank_id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
CHANGE `rank_min` `rank_min` MEDIUMINT DEFAULT '0' NOT NULL ,
CHANGE `rank_special` `rank_special` TINYINT( 1 ) DEFAULT '0' ;
ALTER TABLE `bb_ranks` DROP INDEX `rank_min` ;

#
# Daten
#
INSERT INTO `bb_ranks`
(`rank_id`, `rank_title`, `rank_min`, `rank_special`, `rank_image`)
VALUES (1, 'Site Admin', -1, 1, NULL);

Tabelle bb_sessions

Auf evtl bestehende Daten dieser Tabelle können wie verzichten, daher löschen wir die Tabelle und erzeugen sie gleich wieder mit der neuen Struktur:

DROP TABLE `bb_sessions` ;

CREATE TABLE `bb_sessions` (
`session_id` char(32) NOT NULL default '',
`session_user_id` mediumint(8) NOT NULL default '0',
`session_start` int(11) NOT NULL default '0',
`session_time` int(11) NOT NULL default '0',
`session_ip` char(8) NOT NULL default '0',
`session_page` int(11) NOT NULL default '0',
`session_logged_in` tinyint(1) NOT NULL default '0',
PRIMARY KEY  (`session_id`),
KEY `session_user_id` (`session_user_id`),
KEY `session_id_ip_user_id` (`session_id`,`session_ip`,`session_user_id`)
) TYPE=MyISAM;

Tabelle bb_smiles

Auch diese Tabelle wird neu erzeugt, die alte gelöscht…

DROP TABLE `bb_smiles` ;
CREATE TABLE `bb_smilies` (
`smilies_id` smallint(5) unsigned NOT NULL auto_increment,
`code` varchar(50) default NULL,
`smile_url` varchar(100) default NULL,
`emoticon` varchar(75) default NULL,
PRIMARY KEY  (`smilies_id`)
) TYPE=MyISAM AUTO_INCREMENT=43 ;


#

#

INSERT INTO `bb_smilies` VALUES (1, ':D', 'icon_biggrin.gif', 'Very Happy');
INSERT INTO `bb_smilies` VALUES (2, ':-D', 'icon_biggrin.gif', 'Very Happy');
INSERT INTO `bb_smilies` VALUES (3, ':grin:', 'icon_biggrin.gif', 'Very Happy');
INSERT INTO `bb_smilies` VALUES (4, ':)', 'icon_smile.gif', 'Smile');
INSERT INTO `bb_smilies` VALUES (5, ':-)', 'icon_smile.gif', 'Smile');
INSERT INTO `bb_smilies` VALUES (6, ':smile:', 'icon_smile.gif', 'Smile');
INSERT INTO `bb_smilies` VALUES (7, ':(', 'icon_sad.gif', 'Sad');
INSERT INTO `bb_smilies` VALUES (8, ':-(', 'icon_sad.gif', 'Sad');
INSERT INTO `bb_smilies` VALUES (9, ':sad:', 'icon_sad.gif', 'Sad');
INSERT INTO `bb_smilies` VALUES (10, ':o', 'icon_surprised.gif', 'Surprised');
INSERT INTO `bb_smilies` VALUES (11, ':-o', 'icon_surprised.gif', 'Surprised');
INSERT INTO `bb_smilies` VALUES (12, ':eek:', 'icon_surprised.gif', 'Surprised');
INSERT INTO `bb_smilies` VALUES (13, ':shock:', 'icon_eek.gif', 'Shocked');
INSERT INTO `bb_smilies` VALUES (14, ':?', 'icon_confused.gif', 'Confused');
INSERT INTO `bb_smilies` VALUES (15, ':-?', 'icon_confused.gif', 'Confused');
INSERT INTO `bb_smilies` VALUES (16, ':???:', 'icon_confused.gif', 'Confused');
INSERT INTO `bb_smilies` VALUES (17, '8)', 'icon_cool.gif', 'Cool');
INSERT INTO `bb_smilies` VALUES (18, '8-)', 'icon_cool.gif', 'Cool');
INSERT INTO `bb_smilies` VALUES (19, ':cool:', 'icon_cool.gif', 'Cool');
INSERT INTO `bb_smilies` VALUES (20, ':lol:', 'icon_lol.gif', 'Laughing');
INSERT INTO `bb_smilies` VALUES (21, ':x', 'icon_mad.gif', 'Mad');
INSERT INTO `bb_smilies` VALUES (22, ':-x', 'icon_mad.gif', 'Mad');
INSERT INTO `bb_smilies` VALUES (23, ':mad:', 'icon_mad.gif', 'Mad');
INSERT INTO `bb_smilies` VALUES (24, ':P', 'icon_razz.gif', 'Razz');
INSERT INTO `bb_smilies` VALUES (25, ':-P', 'icon_razz.gif', 'Razz');
INSERT INTO `bb_smilies` VALUES (26, ':razz:', 'icon_razz.gif', 'Razz');
INSERT INTO `bb_smilies` VALUES (27, ':oops:', 'icon_redface.gif', 'Embarassed');
INSERT INTO `bb_smilies` VALUES (28, ':cry:', 'icon_cry.gif', 'Crying or Very sad');
INSERT INTO `bb_smilies` VALUES (29, ':evil:', 'icon_evil.gif', 'Evil or Very Mad');
INSERT INTO `bb_smilies` VALUES (30, ':twisted:', 'icon_twisted.gif', 'Twisted Evil');
INSERT INTO `bb_smilies` VALUES (31, ':roll:', 'icon_rolleyes.gif', 'Rolling Eyes');
INSERT INTO `bb_smilies` VALUES (32, ':wink:', 'icon_wink.gif', 'Wink');
INSERT INTO `bb_smilies` VALUES (33, ';)', 'icon_wink.gif', 'Wink');
INSERT INTO `bb_smilies` VALUES (34, ';-)', 'icon_wink.gif', 'Wink');
INSERT INTO `bb_smilies` VALUES (35, ':!:', 'icon_exclaim.gif', 'Exclamation');
INSERT INTO `bb_smilies` VALUES (36, ':?:', 'icon_question.gif', 'Question');
INSERT INTO `bb_smilies` VALUES (37, ':idea:', 'icon_idea.gif', 'Idea');
INSERT INTO `bb_smilies` VALUES (38, ':arrow:', 'icon_arrow.gif', 'Arrow');
INSERT INTO `bb_smilies` VALUES (39, ':|', 'icon_neutral.gif', 'Neutral');
INSERT INTO `bb_smilies` VALUES (40, ':-|', 'icon_neutral.gif', 'Neutral');
INSERT INTO `bb_smilies` VALUES (41, ':neutral:', 'icon_neutral.gif', 'Neutral');
INSERT INTO `bb_smilies` VALUES (42, ':mrgreen:', 'icon_mrgreen.gif', 'Mr. Green');

Tabelle bb_themes und bb_themes_name

Auch hier: löschen (bb_themes) und neu anlegen


DROP TABLE `bb_themes` ;



CREATE TABLE `bb_themes` (
`themes_id` mediumint(8) unsigned NOT NULL auto_increment,
`template_name` varchar(30) NOT NULL default '',
`style_name` varchar(30) NOT NULL default '',
`head_stylesheet` varchar(100) default NULL,
`body_background` varchar(100) default NULL,
`body_bgcolor` varchar(6) default NULL,
`body_text` varchar(6) default NULL,
`body_link` varchar(6) default NULL,
`body_vlink` varchar(6) default NULL,
`body_alink` varchar(6) default NULL,
`body_hlink` varchar(6) default NULL,
`tr_color1` varchar(6) default NULL,
`tr_color2` varchar(6) default NULL,
`tr_color3` varchar(6) default NULL,
`tr_class1` varchar(25) default NULL,
`tr_class2` varchar(25) default NULL,
`tr_class3` varchar(25) default NULL,
`th_color1` varchar(6) default NULL,
`th_color2` varchar(6) default NULL,
`th_color3` varchar(6) default NULL,
`th_class1` varchar(25) default NULL,
`th_class2` varchar(25) default NULL,
`th_class3` varchar(25) default NULL,
`td_color1` varchar(6) default NULL,
`td_color2` varchar(6) default NULL,
`td_color3` varchar(6) default NULL,
`td_class1` varchar(25) default NULL,
`td_class2` varchar(25) default NULL,
`td_class3` varchar(25) default NULL,
`fontface1` varchar(50) default NULL,
`fontface2` varchar(50) default NULL,
`fontface3` varchar(50) default NULL,
`fontsize1` tinyint(4) default NULL,
`fontsize2` tinyint(4) default NULL,
`fontsize3` tinyint(4) default NULL,
`fontcolor1` varchar(6) default NULL,
`fontcolor2` varchar(6) default NULL,
`fontcolor3` varchar(6) default NULL,
`span_class1` varchar(25) default NULL,
`span_class2` varchar(25) default NULL,
`span_class3` varchar(25) default NULL,
`img_size_poll` smallint(5) unsigned default NULL,
`img_size_privmsg` smallint(5) unsigned default NULL,
PRIMARY KEY  (`themes_id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;



INSERT INTO `bb_themes` VALUES (1, 'subSilver', 'subSilver', 'subSilver.css', '', 'E5E5E5', '000000', '006699', '5493B4', '', 'DD6900', 'EFEFEF', 'DEE3E7', 'D1D7DC', '', '', '', '98AAB1', '006699', 'FFFFFF', 'cellpic1.gif', 'cellpic3.gif', 'cellpic2.jpg', 'FAFAFA', 'FFFFFF', '', 'row1', 'row2', '', 'Verdana, Arial, Helvetica, sans-serif', 'Trebuchet MS', 'Courier, \'Courier New\', sans-serif', 10, 11, 12, '444444', '006600', 'FFA34F', '', '', '', NULL, NULL);



CREATE TABLE `bb_themes_name` (
`themes_id` smallint(5) unsigned NOT NULL default '0',
`tr_color1_name` char(50) default NULL,
`tr_color2_name` char(50) default NULL,
`tr_color3_name` char(50) default NULL,
`tr_class1_name` char(50) default NULL,
`tr_class2_name` char(50) default NULL,
`tr_class3_name` char(50) default NULL,
`th_color1_name` char(50) default NULL,
`th_color2_name` char(50) default NULL,
`th_color3_name` char(50) default NULL,
`th_class1_name` char(50) default NULL,
`th_class2_name` char(50) default NULL,
`th_class3_name` char(50) default NULL,
`td_color1_name` char(50) default NULL,
`td_color2_name` char(50) default NULL,
`td_color3_name` char(50) default NULL,
`td_class1_name` char(50) default NULL,
`td_class2_name` char(50) default NULL,
`td_class3_name` char(50) default NULL,
`fontface1_name` char(50) default NULL,
`fontface2_name` char(50) default NULL,
`fontface3_name` char(50) default NULL,
`fontsize1_name` char(50) default NULL,
`fontsize2_name` char(50) default NULL,
`fontsize3_name` char(50) default NULL,
`fontcolor1_name` char(50) default NULL,
`fontcolor2_name` char(50) default NULL,
`fontcolor3_name` char(50) default NULL,
`span_class1_name` char(50) default NULL,
`span_class2_name` char(50) default NULL,
`span_class3_name` char(50) default NULL,
PRIMARY KEY  (`themes_id`)
) TYPE=MyISAM;




INSERT INTO `bb_themes_name` VALUES (1, 'The lightest row colour', 'The medium row color', 'The darkest row colour', '', '', '', 'Border round the whole page', 'Outer table border', 'Inner table border', 'Silver gradient picture', 'Blue gradient picture', 'Fade-out gradient on index', 'Background for quote boxes', 'All white areas', '', 'Background for topic posts', '2nd background for topic posts', '', 'Main fonts', 'Additional topic title font', 'Form fonts', 'Smallest font size', 'Medium font size', 'Normal font size (post body etc)', 'Quote & copyright text', 'Code text colour', 'Main table header text colour', '', '', '');

Tabelle bb_topics und bb_topics_watch

UPDATE `bb_topics` SET `topic_time` = UNIX_TIMESTAMP( topic_time ) ;

ALTER TABLE `bb_topics`
CHANGE `topic_id` `topic_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
CHANGE `topic_title` `topic_title` VARCHAR( 60 ) NOT NULL ,
CHANGE `topic_poster` `topic_poster` MEDIUMINT DEFAULT '0' NOT NULL ,
CHANGE `topic_time` `topic_time` INT DEFAULT '0' NOT NULL ,
CHANGE `topic_views` `topic_views` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
CHANGE `topic_replies` `topic_replies` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
CHANGE `topic_last_post_id` `topic_last_post_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
CHANGE `forum_id` `forum_id` SMALLINT( 10 ) UNSIGNED DEFAULT '0' NOT NULL ,
CHANGE `topic_status` `topic_status` TINYINT( 3 ) DEFAULT '0' NOT NULL ;

ALTER TABLE `bb_topics` DROP `topic_notify` ;

ALTER TABLE `bb_topics` ADD `topic_first_post_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL AFTER `topic_last_post_id` ,
ADD `topic_vote` TINYINT( 1 ) DEFAULT '0' NOT NULL AFTER `topic_first_post_id` ,
ADD `topic_type` TINYINT( 3 ) DEFAULT '0' NOT NULL AFTER `topic_vote` ,
ADD `topic_moved_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL AFTER `topic_type` ;
# Indexe anpassen
ALTER TABLE `bb_topics`
DROP INDEX `topic_id` ,
DROP INDEX `topic_last_post_id` ,
ADD INDEX ( `topic_moved_id` ) ,
ADD INDEX ( `topic_status` ) ,
ADD INDEX ( `topic_type` ) ;



CREATE TABLE `bb_topics_watch` (
`topic_id` mediumint(8) unsigned NOT NULL default '0',
`user_id` mediumint(8) NOT NULL default '0',
`notify_status` tinyint(1) NOT NULL default '0',
KEY `topic_id` (`topic_id`),
KEY `user_id` (`user_id`),
KEY `notify_status` (`notify_status`)
) TYPE=MyISAM;

Tabelle bb_whosonline

Tabelle wird nicht mehr benötigt…

DROP TABLE `bb_whosonline`

Tabelle bb_words

ALTER TABLE `bb_words`
CHANGE `word_id` `word_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
CHANGE `word` `word` VARCHAR( 100 ) NOT NULL ,
CHANGE `replacement` `replacement` VARCHAR( 100 ) NOT NULL ;

Weitere neue Tabellen…



CREATE TABLE `bb_search_results` (
`search_id` int(11) unsigned NOT NULL default '0',
`session_id` varchar(32) NOT NULL default '',
`search_array` text NOT NULL,
PRIMARY KEY  (`search_id`),
KEY `session_id` (`session_id`)
) TYPE=MyISAM;

#

#

CREATE TABLE `bb_search_wordlist` (
`word_text` varchar(50) binary NOT NULL default '',
`word_id` mediumint(8) unsigned NOT NULL auto_increment,
`word_common` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY  (`word_text`),
KEY `word_id` (`word_id`)
) TYPE=MyISAM AUTO_INCREMENT=13 ;

#

#

INSERT INTO `bb_search_wordlist` VALUES ('example', 1, 0);
INSERT INTO `bb_search_wordlist` VALUES ('post', 2, 0);
INSERT INTO `bb_search_wordlist` VALUES ('phpbb', 3, 0);
INSERT INTO `bb_search_wordlist` VALUES ('installation', 4, 0);
INSERT INTO `bb_search_wordlist` VALUES ('delete', 5, 0);
INSERT INTO `bb_search_wordlist` VALUES ('topic', 6, 0);
INSERT INTO `bb_search_wordlist` VALUES ('forum', 7, 0);
INSERT INTO `bb_search_wordlist` VALUES ('since', 8, 0);
INSERT INTO `bb_search_wordlist` VALUES ('everything', 9, 0);
INSERT INTO `bb_search_wordlist` VALUES ('seems', 10, 0);
INSERT INTO `bb_search_wordlist` VALUES ('working', 11, 0);
INSERT INTO `bb_search_wordlist` VALUES ('welcome', 12, 0);

#

#

CREATE TABLE `bb_search_wordmatch` (
`post_id` mediumint(8) unsigned NOT NULL default '0',
`word_id` mediumint(8) unsigned NOT NULL default '0',
`title_match` tinyint(1) NOT NULL default '0',
KEY `post_id` (`post_id`),
KEY `word_id` (`word_id`)
) TYPE=MyISAM;

#

#

INSERT INTO `bb_search_wordmatch` VALUES (1, 1, 0);
INSERT INTO `bb_search_wordmatch` VALUES (1, 2, 0);
INSERT INTO `bb_search_wordmatch` VALUES (1, 3, 0);
INSERT INTO `bb_search_wordmatch` VALUES (1, 4, 0);
INSERT INTO `bb_search_wordmatch` VALUES (1, 5, 0);
INSERT INTO `bb_search_wordmatch` VALUES (1, 6, 0);
INSERT INTO `bb_search_wordmatch` VALUES (1, 7, 0);
INSERT INTO `bb_search_wordmatch` VALUES (1, 8, 0);
INSERT INTO `bb_search_wordmatch` VALUES (1, 9, 0);
INSERT INTO `bb_search_wordmatch` VALUES (1, 10, 0);
INSERT INTO `bb_search_wordmatch` VALUES (1, 11, 0);
INSERT INTO `bb_search_wordmatch` VALUES (1, 12, 1);
INSERT INTO `bb_search_wordmatch` VALUES (1, 3, 1);

#

#

CREATE TABLE `bb_user_group` (
`group_id` mediumint(8) NOT NULL default '0',
`user_id` mediumint(8) NOT NULL default '0',
`user_pending` tinyint(1) default NULL,
KEY `group_id` (`group_id`),
KEY `user_id` (`user_id`)
) TYPE=MyISAM;

#

#

INSERT INTO `bb_user_group` VALUES (1, -1, 0);
INSERT INTO `bb_user_group` VALUES (2, 2, 0);
INSERT INTO `bb_user_group` VALUES (3, 3, 0);

#

#

CREATE TABLE `bb_vote_desc` (
`vote_id` mediumint(8) unsigned NOT NULL auto_increment,
`topic_id` mediumint(8) unsigned NOT NULL default '0',
`vote_text` text NOT NULL,
`vote_start` int(11) NOT NULL default '0',
`vote_length` int(11) NOT NULL default '0',
PRIMARY KEY  (`vote_id`),
KEY `topic_id` (`topic_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#

#

CREATE TABLE `bb_vote_results` (
`vote_id` mediumint(8) unsigned NOT NULL default '0',
`vote_option_id` tinyint(4) unsigned NOT NULL default '0',
`vote_option_text` varchar(255) NOT NULL default '',
`vote_result` int(11) NOT NULL default '0',
KEY `vote_option_id` (`vote_option_id`),
KEY `vote_id` (`vote_id`)
) TYPE=MyISAM;

#

#

CREATE TABLE `bb_vote_voters` (
`vote_id` mediumint(8) unsigned NOT NULL default '0',
`vote_user_id` mediumint(8) NOT NULL default '0',
`vote_user_ip` char(8) NOT NULL default '',
KEY `vote_id` (`vote_id`),
KEY `vote_user_id` (`vote_user_id`),
KEY `vote_user_ip` (`vote_user_ip`)
) TYPE=MyISAM;

Nacharbeiten

Das neue phpBB-2 findet die eigentlichen Posts nicht mehr. Hier bemühe ich wieder ein Shellscript. Sicherlich geht das auch als reines SQL-Statement. Aber die Suche nach der eleganten Lösung hätte wohl etwas länger gedauert…

#!/bin/sh
#

# Scriptaufgabe: Verknuepfung eines Topic-Eintrages mit dem ersten Post
# Grund: phpBB-2.0.12 findet die eigentlichen Posts nicht mehr, da diese nun
# ueber das Feld topics.topic_first_post_id verknuepft werden.
#
# Es muss also von alles Posts jeweils der Erste eines Topics gefunden und in
# topics.topic_first_post_id eingetragen werden...
#
# LUG-Forum mySQL-DB-Tabellen 
#   posts  : post_id, topic_id
#   topics : topic_id, topic_first_post_id
#
# (C) 2005 Thomas Kieschnik * Inspirit Systems


DB="phpBB142lug"
TABLE_P="bb_posts"
TABLE_T="bb_topics"
HOST="hostname"
USER="root"
TEMPFILE=`mktemp /tmp/$0.XXXXXX` || exit 1

echo -n "Bitte mySQL-Password fuer $USER: "
read PASSWD

myEXEC="mysql -u $USER -h $HOST -D $DB -p$PASSWD"

echo "SELECT  post_id, topic_id, post_time FROM $TABLE_P
	GROUP BY topic_id
	ORDER BY topic_id, post_time" | $myEXEC | grep -v post_id > $TEMPFILE

while read POST_ID TOPIC_ID TIME; do

    SQL="UPDATE $TABLE_T SET topic_first_post_id = '$POST_ID' WHERE topic_id = '$TOPIC_ID';"
    echo "$TOPIC_ID -- $POST_ID -- $SQL"
    echo $SQL  | $myEXEC

done < $TEMPFILE

rm $TEMPFILE