View source for User:TerryE/phpBB3.0.4 Migration/Detailed Implementation Notes

From Apache OpenOffice Wiki
← User:TerryE/phpBB3.0.4 Migration/Detailed Implementation Notes
You do not have permission to edit this page, for the following reasons:
The action you have requested is limited to users in the group: Users.
You must confirm your email address before editing pages. Please set and validate your email address through your user preferences.
You can view and copy the source of this page:
= Detailed Implementation Notes =
:''Activities on this page are now closed''

{| cellspacing="0" cellpadding="0" style="clear: {{{clear|right}}}; margin-bottom: .5em; float: right; padding: .5em 0 .8em 1.4em; background: none; width: {{{width|{{{1|auto}}}}}};" {{#if:{{{limit|}}}|class="toclimit-{{{limit}}}"}}
| __TOC__
|}

== 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 ->                  (*) only set up for database conversion to link to database_update.php
   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.).

== usOOo server scripts ==

The following factors have really driven me to pretty much script the entire cut-over process:
* I am working with 8 separate forums which need cutting over.
* I need to do the cut-over synchronisation a number of times during the dress rehearsal.
* For the final live cut-over I want to do this this the minimum sensible service down-time.
And here are the scripts that I've developed.

=== 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 '''gif'''s, '''jpeg'''s and '''png'''s 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=<nowiki>"http://$usooo/XXXX" # not real directory name</nowiki>
 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'] . ' :: <nowiki><strong>' . $config['version'] . '</strong><br />';</nowiki>
  echo $lang['UPDATED_VERSION'] . ' :: <nowiki><strong>' . $updates_to_version . '</strong></p>';</nowiki>
 @@ -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 <nowiki>http://localhost/$co/forum/install/database_update.php</nowiki>
   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';"
   # remove symlink and temp directory
   rm  /var/www/$co/forum/install/database_update.php
   rmdir /var/www/$co/forum/install
 done

== Other Notes ==

=== Database tweaks ===

What I've tried to do is to converge all the forums where practical and the most different NL Forum is the French forum &mdash; simply because the other where essentially clones of the EN forum, but the French forum was a migration from a previous phpBB V2 site and the NL Admin is understandable reluctant to change the look and feel unless there are positive advantages in doing so.  

In the case of the smilies, this was a one-off since these tables aren't resynchronised.  I decided to use the FR smilies since these were a superset of the other forums. Ihad to change the smilies table by doing a one-off reconciliation in Calc then using this to recreate the EN table then replicate this to the other forums:
  
 <b>mysql&gt;</b> TRUNCATE TABLE en.phpbb_en_smilies;
         INSERT INTO en.phpbb_en_smilies VALUES
          (1,':D','Very Happy','icon_biggrin.gif',15,15,1,1),
          (2,':-D','Very Happy','icon_biggrin.gif',15,15,2,0),
          ...
          (49,':alarm:','Alarm','alerte.gif',16,16,49,1);

 for co in es hu ja vi zh; do mysqlooo $co -e \
   "TRUNCATE TABLE phpbb_${co}_smilies; 
    INSERT INTO phpbb_${co}_smilies select * FROM en.phpbb_en_smilies;"
 done

I have unified the Icon repositories, etc. which means that I've have to change some file locations after each resync, e.g. in the '''phpb_fr_forums''' table:
 <b>mysql&gt;</b> update fr.phpbb_fr_forums 
         set    forum_image=replace(forum_image,'images/','images/OOo_Icons/') 
         where  forum_image like 'image%';

== Final Cleanup ==

Once the databases are primed we can give MySQL a final cleanup and start up the forums.

 mysqlcheck --check --verbose --all-databases --auto-repair --analyze --optimize
 for co in en es fr hu ja nl vi zh; do mysqlooo $co -e \
   "UPDATE phpbb_${co}_config SET config_value=0 where config_name='board_disabled';" 
   \rm /var/www/$co/forum/cache/*.php
 done
Return to User:TerryE/phpBB3.0.4 Migration/Detailed Implementation Notes.
Retrieved from "https://wiki.openoffice.org/wiki/User:TerryE/phpBB3.0.4_Migration/Detailed_Implementation_Notes"
View source for User:TerryE/phpBB3.0.4 Migration/Detailed Implementation Notes

Views

Personal tools

Navigation

Search

Tools
