Delphi forums to phpBB – Part One

Most of my work is pretty much the same: upgrades and updates to existing phpBB boards. Lately though I’ve been tackling some unusual projects. One of these is to move a Delphi board to phpBB.

Delphi forums are proprietary and are available only on their website. I understand it has no official way to export a Delphi forum. A board there has twenty years of conversations they did not want to lose. A clever user though has figured out how to export the board using YAML.

Example of YAML file for importing

After looking at it, I figured it was cost prohibitive to do. I did a proof of concept for them, demonstrating that I could read the YAML file and get basic user, forum, topic and post information from it, and display it neatly in HTML. I explained that this would be custom work and that it would take a lot of labor, even at my nonprofit rate. I estimated $2000 – $3000 and assumed it was cost prohibitive.

Proof of concept to display user information from YAML file

That was back in February but I was recently given the go ahead to start this project. Which raises the question: how on earth to do something like this?

What convertors from other forum solutions to phpBB that do exist tend to be for phpBB 3.0, and we’re now on phpBB 3.3. And no one has written a Delphi forums converter because it has no export capability. Since then phpBB has evolved, and due to the way post text is now encoded, it’s very challenging to write any new convertor.

Which is why I said if it can be done I would want to populate an old phpBB 2 forum with its data, and convert that to phpBB 3.3. There’s a convertor built into phpBB that does just this. But phpBB 2 became obsolete 13 years ago. However, its database is relatively simple so it should be straightforward to populate from data elsewhere.

But phpBB 2 was built for versions 4 and 5 of PHP, versions now obsolete and dangerous to use, or even acquire. It’s hard to even stand up a PHP 5 environment now. Most web hosting won’t support PHP 5, and mine doesn’t. So I’d have to roll my own development environment with PHP 5.

There are two ways to do this:

  • Via a virtual machine
  • Via a separate machine

I’ve used VitualBox on my Mac for years for standing up virtual machines. While it works, it doesn’t work well on a Mac. It’s a pain to use, particularly if you need to move files between the Mac and the VM.

Because I need PHP 5 for clients generally at least once a month, I decided a separate machine was more practical. So I bought a Raspberry Pi 4 (4GB) for less than $100. The actual machine is about $35 but I added a keyboard, mouse and a book. It took some help online to figure out how to install a web server and PHP 5 for it. The Pi is cheap because it uses RISC-based processors, so it’s not Intel compatible. Also, I could use FTP to move files to it, and using a free VNC viewer, I could work on it from my Mac desktop.

Processing the YAML turned out not to be easy. I tried various things, including PHP’s PECL YAML class, which I couldn’t get to work. I was able to eventually get Symfony’s YAML class to work.

Given the go ahead, I created a phpBB 2 board on my Pi with PHP 5.6 installed. While this worked great, my script wouldn’t run there. Symfony requires PHP 7.2. So I exported the phpBB 2 database and imported it into my Mac where I run PHP 7.4. I would have to populate the database from the YAML files I was given there, and eventually move it back to the Pi when it was time to try to upgrade the database.

Loading the phpbb_users table was not too challenging. A few fields needed to be escaped using mysqli_escape_string so the data could be loaded into the table. Some fields had to be trimmed to fit inside the maximum field length.

phpBB 2 places user data in a number of tables. Topics and posts haven’t been created yet, but there is a phpbb_user_group table. Each user had to belong to a group. Creating a test user on phpBB 2, I learned that it will create a new personal group. Then I had to write some SQL to put all 600+ users into this table. I also needed to populate a number of other columns in the phpbb_users_table. Each should have a new password, but since these users were added in bulk, they share the same password for now. All share the same bogus email address too, since I didn’t have that in the YAML. They will have to change both after the board is converted. Back in the old days, the password was encrypted with a simple md5 hash.

But the customer’s thread YAML wasn’t 100% clean. It wasn’t a major issue, but 22 threads (which amount to topics) wouldn’t parse. Even the good threads though had some issues. Some threads have no name to them. I will have to create a synthetic topic name for them. Some don’t have a forum name either, so those have to go into some sort of catch all forum.

There is a major issue is that the YAML’s attachment information provided a file upload name, but the name is not in the /files folder provided with the YAML. Files seem to have some sort of hash name. This may mean that attachments cannot be imported. If I want to import any attachments, I will have to install the old phpBB 2 attachment mod.

But at least this approach looks viable, if tedious. More learning experiences ahead.

August 2020 work summary

The economy may be hurting but not my personal economy. Demand for my services was high in August, so high it was hard keeping up with all the work coming in. It slowed a little toward the end of the month giving me a bit of a breather.

  • Long, painful, tedious conversion from a phpBB 2.0.19 board to phpBB 3.3.0, with a rehosting to boot. This project took over 3 days. 3.9M posts were converted. The board used the standard prosilver style. (phpBB 2 is so old there was no prosilver style back then. phpBB 2 was obsolete in 2007!) Customized and centered the logo. The board has 287,000 users, mostly spammers. Installed advertisement management and search on navigation bar extensions. Showed client how to place sample ad. Installed Italian language pack. Created a custom style to make future upgrades and updates easier. Had to optimize tables due to slow performance. Database upgrade took about 30 hours and timed out once. Very tedious, time consuming and not very profitable work. Created a new MySQL fulltext search index.
  • Upgraded board from phpBB 3.2.8 to 3.3.0. Installed updated Danish and German casual language packs. Updated the NavBar Search extension to version 3.2.4 and the Tapatalk extension to version 2.1.8. Put old logo back on prosilver_se style, which was also updated to a 3.3.0 version. Changed spambot countermeasure to use reCaptcha V2 invisible. Later, updated client to phpBB 3.3.1 at no extra charge since it was just released.
  • Client wanted to rehost board on new server. On the new host the database had already been moved (presumably by the new web host) but the files had not. So I made a phpBB files archive and moved them over. I then corrected config.php to use the correct database username and password, and changed the password to match the old password. Downgraded PHP to 7.2 since phpBB 3.2.7 was installed. The WordPress part of domain came up fine.
  • Upgraded a board from phpBB 3.0.13-PL1 to phpBB 3.3.1. Tried to replicate the prosilver_se style but had logo placement issues because part of the logo had black within it. Client will be redo the logo, but changes to the stylesheet happened erratically. I wondered if this might be due to a CloudFlare or CDN issue. Installed my latest version of my Smartfeed extension and Google Analytics extension. I changed PHP to 7.3. I disabled the contact form, and created a reCAPTCHA V3 spambot countermeasure. Client had a script that independently queried the number of posts, topics and users to feed to another system. These did not work with the phpBB upgrade. The easiest solution was to rewrite it to call the database directly with mysqli to get the values needed. The board has 134,000 posts. Later there was an issue where the search index was not being indexed. Recreated the index. But the root problem turned out to be a bad configuration value in the phpbb_config table which had to be escaped to get show the correct class namespace. With it, it could then use the correct search engine.
  • Upgrade and rehosting. Moved phpBB 3.2.2 board and database to new Siteground hosting. Lots of issues importing the data into the database (timeouts). Upgraded board to phpBB 3.3.1. Changed registration text to say not to use real name when registering. Installed Filter by Country extension. Let’s Encrypt security certificate could not be installed until domain is transferred. Transferred domain using the client’s registrar. Installed Advertisement management extension and placed sample ad. Placed Donate button on navigation bar pointing to non-existent donate.html on the web root. Later there was some troubleshooting. Newly registered user did not belong to any group and saw a no forums message. Added him to both Newly registered and Registered users group, advised to change permissions for newly registered users using roles. Client said the notification count was incorrect. Might have been due to new notifications being received. Set up the board’s email settings to use SMTP and created an email forwarder associated with the domain. Also created an admin email address. Added email server as an authorized server for sending email, although first attempt showed emails arriving in my spam folder.
  • Troubleshooting. Users were having sporadic logout issues. Changed a number of cookie and server settings. I also made the server generate https URLs and changed permissions to /cache/production and /cache/production/twig to 777, were 755.
  • Updated a client’s board from phpBB from 3.3.0 to 3.3.1. Updated three extensions: Advanced BBCode Box, Precise Similar Topics and Recent Topics to latest versions. Reapplied logo.
  • Upgraded a board from phpBB 3.0.12 to 3.3.1. 305,000 posts. Smooth upgrade with one minor issue: no active style. Changed to make prosilver the active style in database and then the upgrade was able to complete. Installed topic solved extension but it needs per forum configuration, so I sent the client instructions on how to do this. Installed Stop Forum Spam extension, which is currently a release candidate extension for 3.3. Created a custom style based on prosilver. A number of styling changes were desired. Work to replicate the top of the header template was suspended as I needed link to a stylesheet I didn’t have, so I removed that code and added a Home button for now, which turned out to satisfy the client. Hid the site description. Recreated the search index. Disabled the contact form. Installed reCaptcha V3 spambot countermeasure. Added Google Analytics extension. Had to copy over some images to the custom style. reCaptcha V3 has issues: it couldn’t register users and there were curse words on registering when it failed. Reverted to reCaptcha V2 invisible. The main issue turned out to be that all email addresses were banned, and the defined banned list reasons returned curse words because the was a blocked domain. Also, there were some obsolete Moderator Control Panel modules that I had to remove by hand in the ACP. Apparently the database cleaner did not get them all. Installed the Ban Hammer extension.
  • Investigation for client to see if non phpBB applications break on upgrade to PHP 7.3. Found only one that broke: a Zencart integration that will need an updated version. Upgraded board from phpBB 3.1.10 to 3.3.1. prosilver_se style was used with customizations, so I created a custom style that inherited from prosilver_se. Board has 773,000 posts and 71,000 attachments. There was lots of hassle getting the styling right. One issue encountered during upgrade: a config_name already existed in the phpbb_config table. Removed the row from the table and the upgrade then was able to complete. Changed PHP to 7.3. Updated 16 extensions. Installed reCaptcha V3. The /files folder had the attachments nested in folders inside of folders. Don’t know why this was, but it predated the upgrade, and caused images not to show in posts. The client assumed the onerous chore of moving these files correctly for display. PHP Gallery was linked from the board and on the navigation bar. It came up fine on PHP 7.
  • Upgraded a generic board with no mods from phpBB 3.0.11 to 3.2.10. No PHP beyond 7.0 was available on the web host, so it limited him to 3.2.10. I chose to upgrade PHP to 5.6 (5.2 was installed) in case there was other software on the domain that would not work on PHP 7.0. I had to edit first line of config.php because it loaded a nonexistent antispam program. Disabled contact form and set spambot countermeasure to reCaptcha V2 invisible. Some folders did not have public write permissions set.
  • Update board from phpBB 3.3.0 to 3.3.1, and changed PHP version from 7.1 to 7.3.
  • Made about $60 in book royalties last month.
  • Continued work for a large commercial client looking to upgrade their board to phpBB 3.3 while maintaining their unique look and customizations from the current phpBB 3.0 board. This month worked on two extensions to replicate logic that was previously done with modifications. The first was to allow moderators and administrators to find topics which had no replies by moderators or administrators, using the existing search interface. This took quite a while to puzzle through, but I was eventually successful. The second is to email an email list if any entry is made to the admin log. After some discussion with the client, I added logic to allow them to not email certain notifications by manually adding them in a text area form field, as these will be too common and thus annoying. I started work on an extension to automatically lock topics more than 60 days old. Thinking about it overnight though I realized there probably was an extension to do this and there is, so I installed that instead. Looking through the remaining work, there should only be one more extension to do. This involves a complex connection between phpBB and the accounting system the client has, which determines who can post in the forum and creates accounts in phpBB. It will require some sort of custom Oauth solution because that’s what they had before. Since styling work was done in July, this may be the last big piece of development work. Doing a test conversion and working out kinks will then be possible. So hopefully this project will be finished by the end of the year. This is interesting work and it’s nice to have a client pay my commercial rate and pay very promptly once a month.