Standing up a phpBB instance on Google App Engine and Cloud SQL

Before we get started

I've been playing around with phpBB on App Engine's new experimental PHP runtime environment, using Cloud SQL for the backend database. While it wasn't too much effort to get it a site up and running I thought it would be useful to document the steps I went through here so others can follow along. Let me know in the comments if these steps don't work for you, or if there's additional changes you ended up making to the phpBB source to address more advanced features.

Overview

This guide follows roughly these steps:

1. Get a basic phpBB instance up and running on the App Engine SDK's dev_appserver, connected to a locally available MySQL instance
2. Walk through phpBB's initial setup process using the local the dev_appserver
3. Make a couple of adjustments to the generated config.php
4. Export the local MySQL database and import it into Google Cloud SQL
5. Deploy our application to the App Engine production environment

TODO: Look into caching template files in memcache.

Here are the step by step instructions:

Setup your Cloud Console Project

• From the Google Cloud Console create a new project. Pick a suitable project id (I used fredsa-phpbb). This cloud console project id will also serve as your App Engine app id.
  
• Enable billing on your Cloud Console project (click the gear icon in the top right and select 'Billing'). Don't forget to click the verification link the email you'll receive. Confirming your contact email will ensure you receive timely billing notifications.
• Also enable billing for your App Engine project. Click the 'Enable Billing' button from the 'Billing Status' page from the App Engine admin console. There's a link to the App Engine console from the Cloud Console.
• Within your cloud console project  create a new Cloud Storage bucket. For convenience I chose my bucket name to be the same as my project id

• Within your cloud console project create a new Cloud SQL instance. Again for convenience I chose and instance id which matches my project id. Instance id fredsa-phpbb in project fredsa-phpbb can be known as fredsa-phpbb:fredsa-phpbb

• Download and install the Cloud SDK or install each of these tools separately":
• google_sql.sh https://developers.google.com/cloud-sql/docs/commandline
• App Engine SDK https://developers.google.com/appengine/downloads

Verify Cloud SQL connectivity

google_sql.py your-project-id:your-cloud-sql-instance-id

Download phpBB 3.0.11

• Download phpBB-3.0.11.zip from https://www.phpbb.com/downloads/
• Extract the zip file

unzip phpBB-3.0.11.zip

cd phpBB3

• To easily track your changes throughout the installation process you may find it convenient to create a local git repository. This step is entirely optional:

git init

git add -A

git commit -m 'initial contents of phpBB-3.0.11.zip'

(Optional) Add a favicon.ico

Copy your favorite favicon.ico into the main phpBB directory (i.e the same directory where common.php can be found)

Modify the app to be App Engine compatible

• Add a 404.php, which we'll use to explicitly disallow access to certain resources, with the following contents:

<?

http_response_code(404);

?>

• Add php.ini with the following contents

output_buffering = "On"

• (Optional) add cron.yaml to run cleanup tasks at a desired interval

# See https://developers.google.com/appengine/docs/php/config/cron

cron:

- description: phpBB cleanup tasks

 url: /cron.php

 schedule: every 24 hours

• Add the App Engine app.yaml configuration file, with the following contents:

application: your-app-id

version: 1

runtime: php

api_version: 1

# see https://developers.google.com/appengine/docs/php/config/appconfig

handlers:

# Prevent users from accessing protected resources, which would otherwise be

# accessible because of wildcard handlers later in this files. This replaces:

# - .htaccess

# - cache/.htaccess

# - files/.htaccess

# - images/avatars/upload/.htaccess

# - includes/.htaccess

# - store/.htaccess

- url: /(common.php|config.php|(cache|files|images/avatars/upload|includes|store)/.*)

 script: 404.php

- url: /

 script: index.php

- url: /favicon\.ico

 static_files: favicon.ico

 upload: favicon\.ico

- url: /(.*\.php)

 script: \1

- url: /adm/images

 static_dir: adm/images

- url: /(adm/style/.*\.(css|js))

 static_files: \1

 upload: adm/style/.*\.(css|js)

- url: /images

 static_dir: images

 application_readable: true

- url: /(styles/.*\.(html))

 static_files: \1

 upload: styles/.*\.(html)

 application_readable: true

- url: /(styles/.*\.(css|gif|jpg|js|png))

 static_files: \1

 upload: styles/.*\.(css|gif|jpg|js|png)

# see https://developers.google.com/appengine/docs/php/config/appconfig

skip_files:

# default entries

- ^(.*/)?#.*#

- ^(.*/)?.*~

- ^(.*/)?.*\.py[co]

- ^(.*/)?.*/RCS/.*

- ^(.*/)?\..*

# custom entries, for resources we don't want to deploy to production

- ^cache/.*$

- ^install/.*$

- ^scripts/.*$

• Review  the contents of  any .htaccess files (which are ignored by App Engine). Ensure that the access restrictions indicated by each file are implemented using one of the following strategies:

• Outright removal of the affected files or directories. Users won't be able to access files which do not exist.
• Use of a login: admin protected URL handler in app.yaml. This allows App Engine admins such as yourself to access administrative functions while denying access to all other users.
• Use of a script: 404.php URL handler in app.yaml. This explicitly denies access to specific resources, including those resources which would otherwise be made accessible by later app.yaml URL handlers.
• Use of a skip_files entry in app.yaml. This prevents access to files by making sure the specified files are not deployed to the production environment.

The above app.yaml should already take care of these phpBB 3.0.11 files, although it wouldn't hurt for you to independently verify this.

git ls-files | grep .htaccess

phpBB/.htaccess

phpBB/cache/.htaccess

phpBB/config/.htaccess

phpBB/files/.htaccess

phpBB/images/avatars/upload/.htaccess

phpBB/includes/.htaccess

phpBB/store/.htaccess

Run and install phpBB locally

• Download and install MySQL, PHP and the App Engine PHP SDK
• Create a local MySQL database named phpbb, which we'll later export and then import into a Cloud SQL database.

mysql -u root

mysql> CREATE DATABASE phpbb DEFAULT CHARACTER SET utf8;

Query OK, 1 row affected (0.00 sec)

• Launch the dev_appserver from the phpBB directory containing the app.yaml. Note the final argument to this command is a '.' indicating the current directory.

dev_appserver.py \

 --php_executable_path=/usr/local/bin/php-cgi \

 --mysql_user root \

 .

• Access the now running local phpBB instance http://localhost:8080/
  

• Click the install tab
  

• Click 'Proceed to next step' and verify the installation compatibility
• Note: In the App Engine production environment, the local file system is not writable. In the dev_appserver we initially allow phpBB to assume that local directories are writable. We'll later modify config.php
  

• Specify the details for the local MySQL instance
  
  

• Configure the admin account
  
  
  
  

• Configure your email settings:
  

• Configure your server URL settings:
  

• Create database tables
  
  

Apply App Engine specific config.php changes

• Modify phpBB/config.php as indicated in bold:

<?php

// phpBB 3.1.x auto-generated configuration file

// Do not change anything in this file!

$dbms = 'phpbb_db_driver_mysql';

$dbhost = '127.0.0.1';

$dbport = '';

$dbname = 'phpbb';

$dbuser = 'root';

$dbpasswd = '';

$table_prefix = 'phpbb_';

$adm_relative_path = 'adm/';

$acm_type = 'phpbb_cache_driver_file';

@define('PHPBB_INSTALLED', true);

// @define('DEBUG', true);

// ************************************************************************

// OUR LOCAL MODIFICATION, DESPITE THE ABOVE WARNING TO NOT CHANGE ANYTHING

// ************************************************************************

$DEVAPPSERVER = preg_match('/^Development/', $_ENV['SERVER_SOFTWARE']);

if ($DEVAPPSERVER) {

 $acm_type = 'null';

} else {

 $dbhost = ':/cloudsql/fredsa-phpbb:fredsa-phpbb';

 $acm_type = 'memcache';

}

• Optionally review the files that have been created during the installation process

git status --ignored

Copy your local MySQL instance to Cloud SQL

• Export the local MySQL phpbb database

mysqldump --databases phpbb -u root --hex-blob > phpbb.sql

• Upload the resulting phpbb.sql file to your Cloud Storage bucket:

gsutil cp phpbb.sql gs://your-cloud-storage-bucket-name/

• From the Cloud SQL console import the MySQL database dump (gs://your-cloud-storage-bucket-name/phpbb.sql)
  
• Confirm in the Operations Log that the import Status indicates Done
  

Initial phpBB test in the App Engine production environment

• Deploy the app (again from the directory containing app.yaml)

appcfg.py --oauth2 update .

• Verify that you can login as the admin using the password you specified during the phpBB installation process. Note not all functionality may be available yet (see next step)

Replace all uses of preg_replace() with /e (PREG_REPLACE_EVAL)

• make_clickable() in includes/functions_content.php (see https://github.com/phpbb/phpbb3/pull/1479)

• Use array_map instead of preg_replace in includes/acp/acp_*.php (see commit 67b243cfc53e3f5f8bf6cd2a5eb80df475a6dd4c)
• Your homework: find and replace remaining uses of preg_replace() with /e, and of course share those changes with the community, preferably by submitting pull requests with your patch to the phpBB committers.

# 3.0.11 release - Your list of TODOs

git grep -l 'preg_replace(\(.\)\(.\).*\2[a-z]*e[a-z]*\1,'

includes/acp/acp_bbcodes.php

includes/bbcode.php

includes/message_parser.php

includes/ucp/ucp_pm_options.php

That's it!