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:
# # Daten für Tabelle `bb_users` # 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_id | access_title |
| −1 | Deleted |
| 1 | User |
| 2 | Moderator |
| 3 | Super Moderator |
| 4 | Administrator |
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` # # Tabellenstruktur für Tabelle `bb_auth_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`;
#
# Tabellenstruktur für Tabelle `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;
#
# Daten für Tabelle `bb_config`
#
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:
# # Tabellenstruktur für Tabelle `bb_confirm` # 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:
# # Tabellenstruktur für Tabelle `bb_forum_prune` # 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` ) ; # # Einige Voreinstellungen für neue Felder # 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:
# # Tabellenstruktur für Tabelle `bb_groups` # 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 ; # # Daten für Tabelle `lugbb_groups` # 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
# # poster_ip nach der Umwanldung in Hex-Format kürzen... # 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 ; # # restliche Änderungen # 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 ;
#
# Daten für Tabelle `bb_smilies`
#
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` ; # Tabellenstruktur für Tabelle `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 ; # Daten für Tabelle `bb_themes` 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); # Tabellenstruktur für Tabelle `bb_themes_name` 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; # Daten für Tabelle `bb_themes_name` 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` ) ; # Tabellenstruktur für Tabelle `bb_topics_watch` 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…
# Tabellenstruktur für Tabelle bb_search_results`
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;
#
# Tabellenstruktur für Tabelle `bb_search_wordlist`
#
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 ;
#
# Daten für Tabelle `bb_search_wordlist`
#
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);
#
# Tabellenstruktur für Tabelle `bb_search_wordmatch`
#
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;
#
# Daten für Tabelle `bb_search_wordmatch`
#
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);
#
# Tabellenstruktur für Tabelle `bb_user_group`
#
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;
#
# Daten für Tabelle `bb_user_group`
#
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);
#
# Tabellenstruktur für Tabelle `bb_vote_desc`
#
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 ;
#
# Tabellenstruktur für Tabelle `bb_vote_results`
#
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;
#
# Tabellenstruktur für Tabelle `bb_vote_voters`
#
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