User:TerryE/phpBB3.0.4 Migration/Detailed Implementation Notes

Detailed Implementation Notes

usOOo server scripts

dumpAllDelta.sh

This script is used to unload the current forums. The parameter (an optional "sync") tells the script only to unload the user and post related table. For historic reasons we are still running the postgreSQL 8.1 tools with the 8.2 database. Hence the pg_dump loop for each db. The overhead is quite small and the bulk of the time is taken up by saving the big tables in the EN and FR forums. The output is gz compressed as this the python libraries in Coolstack support this but not the bzip format. The only file directories that need to be backed up are the avatar uploads and file attachments. The tars are done from the directories to loose the path info. The avatars are all gifs, jpegs and pngs so their is no point in compressing them.

#! /bin/bash
#
# Do a delta dump of the forums
#
unalias -a
outDir='/opt/coolstack/apache2/htdocs/XXXX' # not real directory name
appRoot="/opt/coolstack/apache2"
psql="psql -U ooo_oucv_admin"
pg_dump="pg_dump -i -U ooo_oucv_admin -x"
 
dumpDB(){
   co="$1"
   db='en'
   test "$co" == "zh" && db='zh'
   echo Dumping $co from database $db
   if test "$2" = "sync" ; then
      tables="acl_groups acl_options acl_roles acl_roles_data acl_users attachments \
         banlist bookmarks confirm disallow drafts forums forums_access forums_track \
         forums_watch groups log moderator_cache poll_options poll_votes posts privmsgs \
         privmsgs_folder privmsgs_rules privmsgs_to profile_fields profile_fields_data \
         profile_fields_lang profile_lang reports reports_reasons search_results \
         sessions sessions_keys sitelist topics topics_posted topics_track topics_watch \
         user_group users warnings"
   else
      tables="`$psql -c "\d" $db  | perl -ne \"/\w+_${co}_(\w+)\s+\| table/ && print \\\$1.' ';\"`"
   fi
   # pg_dump V8.1 doesn't support pattern wildcards on the -t option :-(
   ( for t in $tables; do $pg_dump -t  phpbb_${co}_$t $db ; done ; ) \
      | gzip -c > $outDir/$co.sql.gz
}
 
dumpFiles() {
   co=$1
   timestamp="-newer $outDir/lastCopy.Timestamp"
   avatars="$appRoot/htdocs/$co/forum/images/avatars/upload"
   files="$appRoot/htdocs/$co/forum/files"
   ( cd $avatars ; find . $timestamp -type f ) | sed -e 's!^\./!!' > $outDir/fileList
   ( cd $avatars ; tar cf - -I $outDir/fileList ) > $outDir/avatars_${co}.tar
   ( cd $files ; find . $timestamp -type f ) | sed -e 's!^\./!!' > $outDir/fileList
   ( cd $files ; tar cf - -I $outDir/fileList ) | bzip2 -c > $outDir/files_${co}.tar.bz2
   rm $outDir/fileList
}
 
for co in en es fr hu ja vi zh; do
   echo "Processing $co ..."
   dumpDB $co sync
   dumpFiles $co
done

pullAllDelta.sh

This script is run on the new system to pull the databases. The convention I use is to create a directory ~/terrye/migration/pullYYMMDD and then execute the pull from there. Since the two servers are on the same datacentre network fabric, this only takes a few seconds.

#! /bin/bash
#
# Pull the delta dump of the forums from u.s.oo.o
#
unalias -a
 
usooo=192.18.196.107
migrationDir="http://$usooo/XXXX" # not real directory name
alias wget=/usr/sfw/bin/wget
 
for co in en es fr hu ja vi zh; do
   wget $migrationDir/avatars_$co.tar
   wget $migrationDir/files_$co.tar.bz2
   wget $migrationDir/$co.sql.gz
done

applyAllDelta.sh

This script blows the SQL into the databases and add the avatars and files to the correct directories. The Python script pg2mysql.py handles the hard PostgreSQL 3.0.1 schema to MySQL 3.0.4 schema conversion. The bulk of the time is taken up in loading the big tables into the EN and FR forums. The whole script runs in about 10 mins. Note that the 3.0.1 schema/dataload include an extra column forum_post_tpl in the table phpbb_en_forums, which we don't want. The easiest way to handle this is to temporarily add it, do the import and then drop it again.

#! /bin/bash
test -e pull$1 || exit

base=`pwd`
schema=/var/www/phpBB-common/install/schemas/mysql_41_schema.sql
mysqlooo="/opt/coolstack/mysql_32bit/bin/mysql -u $user --password=$password"
$mysqlooo -e "ALTER TABLE phpbb_en_forums ADD COLUMN forum_post_tpl text;" en
for co in en es fr hu ja vi zh; do
  echo "Updating $co tables"
  python pg2mysql.py -n $schema $co pull$1/$co.sql.gz mysqlload/$co.sql
  $mysqlooo $co < mysqlload/$co.sql
done
$mysqlooo -e "ALTER TABLE phpbb_en_forums DROP COLUMN forum_post_tpl;" en

for co in en es fr hu ja vi zh; do
  echo "Updating $co files"
  cd /var/www/$co/forum/avatars-upload; tar xf $base/pull$1/avatars_$co.tar
  cd /var/www/$co/forum/files; bzcat $base/pull$1/files_$co.tar.bz2 | tar xf -
done
cd $base

Applying phpBB database_update.php script

The standard phpBB script install/database_update.php is used both to path the DDL to reflect any changes in going from version 3.x to current (in our case 3.0.1 to 3.0.4) and to patch the data content. Because I am using having to use a 3.0.4 MySQL schema as a starting point, I need to comment out the DDL patches but still execute the rest of the script (also since my merge strategy leaves the config tables untouched on the Live synchronisation re-import, I need to force the DB schema version to 3.0.1. Anyway, here is the patch

--- /var/www/phpBB_ref/install/database_update.php      Fri Dec 12 16:20:38 2008
+++ /var/www/phpBB-common/install/database_update.php   Tue May  5 14:46:34 2009
@@ -680,5 +680,5 @@
        $config['version'] = $debug_from_version;
 }*/
-
+$config['version']='3.0.1';                                                ### UPGRADE PATCH ###
 echo $lang['PREVIOUS_VERSION'] . ' :: <strong>' . $config['version'] . '</strong><br />';
 echo $lang['UPDATED_VERSION'] . ' :: <strong>' . $updates_to_version . '</strong></p>';
@@ -1167,5 +1167,5 @@
        }
 }
-
+if (false) {                                                               ### UPGRADE PATCH ###
 // Schema updates
 ?>
@@ -1299,5 +1299,5 @@

 _write_result($no_updates, $errored, $error_ary);
-
+}                                                                          ### UPGRADE PATCH ###
 // Data updates
 $error_ary = array();

Unfortunately this script only works if the forum default language is English so I need to execute this using this wrapper:

alias wget=/usr/sfw/bin/wget
for co in en es fr hu ja vi zh; do 
  # create a temp install directory and symlink to the conversion routine
  mkdir /var/www/$co/forum/install
  ln -s ../../../phpBB-common/install/database_update.php /var/www/$co/forum/install
  # set the forum language to english
  mysqlooo $co -e "update phpbb_${co}_config set config_value ='en' where config_name='default_lang';"
  wget http://localhost/$co/forum/install/database_update.php
  lang=$co; test "$co" = "zh" && lang=zh_cs
  echo "setting NL forum $co language to $lang"
  mysqlooo $co -e "update phpbb_${co}_config set config_value ='$lang' where config_name='default_lang';"
done

Standard NL configuration

All instances have the same content and essentially symlink everything but the avatars-load, cache and files directories. This means that all image sets and code changes are common to all versions. This includes the specific changes to the French forum that Bidouille requires (and in fact these are enabled by the existence of a specific match parameter that they use.) This all works because all of the forum configuration (such as the selection of the forum's main logo) is maintained in the forum database, and this database is private to each NL forum. In the same way, the individual styles are cached in the database so the Vietnamese forum can tweak its CSS to remove the underlines from links in the database (this is needed because accents in Vietnamese also lie under the letters and an underline can obscure these changing the meaning of the text).

Hence each forum instance has exactly the same structure, excepting the three content directories:

forum:
  adm
  avatars-upload
  cache
  common.php -> ../../phpBB-common/common.php
  config.php -> ../../phpBB-common/config.php
  cron.php -> ../../phpBB-common/cron.php
  docs -> ../../phpBB-common/docs
  download
  faq.php -> ../../phpBB-common/faq.php
  files
  images -> ../../phpBB-common/images
  includes -> ../../phpBB-common/includes
  index.php -> ../../phpBB-common/index.php
  install -> ../../phpBB-common/install   (*) only set up for database conversion.
  language -> ../../phpBB-common/language
  mcp.php -> ../../phpBB-common/mcp.php
  memberlist.php -> ../../phpBB-common/memberlist.php
  posting.php -> ../../phpBB-common/posting.php
  report.php -> ../../phpBB-common/report.php
  search.php -> ../../phpBB-common/search.php
  store
  style.php -> ../../phpBB-common/style.php
  styles -> ../../phpBB-common/styles
  ucp.php -> ../../phpBB-common/ucp.php
  viewforum.php -> ../../phpBB-common/viewforum.php
  viewonline.php -> ../../phpBB-common/viewonline.php
  viewtopic.php -> ../../phpBB-common/viewtopic.php

forum/adm:
  images -> ../../../phpBB-common/adm/images
  index.php -> ../../../phpBB-common/adm/index.php
  style -> ../../../phpBB-common/adm/style
  swatch.php -> ../../../phpBB-common/adm/swatch.php

forum/avatars-upload:
  <instance specific uploaded avatars go here>

forum/cache:
  index.htm -> ../../../phpBB-common/cache/index.htm
    <instance specific generate cache files go here>

forum/download:
  file.php -> ../../../phpBB-common/download/file.php
  index.htm -> ../../../phpBB-common/download/index.htm

forum/files:
  index.htm -> ../../../phpBB-common/files/index.htm
  <instance specific uploaded attachment files go here>

Even through the databases are private to each forum, I would like to standardise these configurations where possible (for example the list of languages, BBcode extensions, etc.).