BOB 2.0

Dave,

Now my freelancing days are at an end, I have paid for hosting set to run for another 20+ months. I’m happy to trash that and start again with a blank canvas which would just support the latest version of phpbb3 and its supported underlying platform.

It will also give me an excuse to move away from a couple of other sports forums that I post on out of habit; I’ll have something more relevant to work on and can evolve the site as required. My current employer is not using BusinessObjects (yet) so there is no conflict or confluence of interests but I want to keep my hand in as much as possible rather than let over 20 years of work on the tool go to waste.

If anyone else wanted to take on the forum, I’ll happily stand aside; I just think that BI and in particular SAP BO needs an independent platform rather than each vendor telling us that their tool is the best.


Mark P :uk: (BOB member since 2003-02-03)

I would not restrict the options to just phpBB3. With all of the enhancements (phpBB calls them “mods”) the conversion process from phpBB2 to phpBB3 will not work. I’ve actually tested it…years ago, to be sure…but at the time it did not work.

I haven’t watched phpBB3 that much, so I don’t know how many of the tech issues that I think we have (or will have) are fixed.


Dave Rathbun :us: (BOB member since 2002-06-06)

I have been inside phpBB2 a lot (although note recently) and poked around with phpBB3 a little. If we had a clean install of phpBB3 we could write our own database migration script. Some parts of it like Forum creation don’t have to be automated - those are small enough in number that they could be done manually, just to create the proper landing zones for the data. We don’t even have to stay with phpBB.

Another idea has struck me. How large is the BOB DB? Instead of just putting up some website, what if we rolled out a stand alone VM (or Docker or alternative container) so that it could be hosted or mined anywhere.


kbrazell :us: (BOB member since 2003-08-19)

Not a problem. I’ve worked with Invision Power Board too; there are plenty of modules on there that we can use. I’ve also had to work directly with the database on that when two forums merged, which was “fun”.


Mark P :uk: (BOB member since 2003-02-03)

Hello guys,

I think if we go on a BOB 2.0, we need to think it globally.

  • What do we want to do ? simple migration to latest phpBB ? choose a different forum solution ? Do something else ?
  • How to work with SAP Community around the place ? advantages vs SAP tooling ?
  • How many time are we ok to invest ?

I am very interessed to spend time on this project and help on answering all of that.

Starting by the first question, I have xp with Discourse that is a very interesting solution.

My point of view is that BOB data is invaluable, and I am really sad to let it die. It’s a community and it need to be handle by the community. But maybe it need to be expended?

  • Social features to be able to link users ?
  • KB features ?
  • Download section ? (yes I am still in love with the fact that my little xls spreadsheet released in 2007 to export users & groups is still used and have been forked multiple times… This is exactly why community is a really good asset !)

forum whitelist choices
Could we import data to reddit? I did a quick search and found someone with the same question. the answer though was not complete, but it does point out three is an API that can be used.

https://www.reddit.com/r/help/comments/14zoaz/migrating_forum_to_reddit/

forum blacklist choices
I’d avoid Facebook because it is a plague on mankind.

research links
Creating a subreddit

caveat
I’ve heard that you can lose a reddit if new moderators “take over” or a large and hostile community moves in. Avoiding that seems to require a full time permanent moderator - which I think is part of the reason that BOB is retiring.

questions
Mostly if it moves to reddit. While the goal to anonymize the migrated archival content is good, some users may want to retain ownership of their questions or answers. I’ve not checked if the reddit API lets you assign posts to other users. But if it does, perhaps the trnaslation table that connects a known BOB user to a name that is anonymous could allow some users to suggest their own reddit name. (The anonymous name could be in the form of BOB_nnnnn where nnnnn is the ID userlist number when sorted by join date [and have actually posted]. Wow, I just checked over 82,000 registered users! I had to add a fifth “n”) (Supporting this might mean adding a reddit ID column to the Profile with the implication that if you fill it out, it will stick after the transfer [if it can be stuck])


kbrazell :us: (BOB member since 2003-08-19)

The big downside for reddit is that it is often blocked by corporate controls as it is seen as a social media site.


Mark P :uk: (BOB member since 2003-02-03)

Depends on how you want to measure it. The biggest table in the phpBB2 system is the search wordmatch table and any archive would not need to preserve that because it only supports the phpbb2 search process.

The users table would be eliminated due to the user account adjustment process I would be using that I mentioned earlier.

There are a number of non-technical forums that probably don’t need to be preserved. For example we have a hidden forum where all of the spammer posts go, just in case I needed them for legal or technical reasons. There are 433 pages of topics there. :cuss:

So if you just go by the number of posts, we have a database with just under one million rows in it. The number of rows in the topics table (think of that as the post header) is 219207.

There are 1.6GB of attachments.


Dave Rathbun :us: (BOB member since 2002-06-06)

The user profile would be completely wiped. Only the username would be preserved using the “guest poster” function.

Interesting, but would have to create a new field in the database to make that happen. I’ll post more about this in the BOB 2.0 topic.

Here’s the current table structure of the POSTS table.

+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| post_id         | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| topic_id        | mediumint(8) unsigned | NO   | MUL | 0       |                |
| forum_id        | smallint(5) unsigned  | NO   | MUL | 0       |                |
| poster_id       | mediumint(8)          | NO   | MUL | 0       |                |
| post_reason_id  | mediumint(5) unsigned | YES  |     | 0       |                |
| post_time       | int(11)               | NO   | MUL | 0       |                |
| poster_ip       | varchar(8)            | NO   |     |         |                |
| is_mod_post     | tinyint(1) unsigned   | NO   |     | 0       |                |
| best_answer     | tinyint(1) unsigned   | YES  |     | 0       |                |
| post_locked     | tinyint(1) unsigned   | NO   |     | 0       |                |
| post_username   | varchar(25)           | YES  |     | NULL    |                |
| enable_bbcode   | tinyint(1)            | NO   |     | 1       |                |
| enable_html     | tinyint(1)            | NO   |     | 0       |                |
| enable_smilies  | tinyint(1)            | NO   |     | 1       |                |
| enable_sig      | tinyint(1)            | NO   |     | 1       |                |
| post_edit_time  | int(11)               | YES  |     | NULL    |                |
| post_edit_count | smallint(5) unsigned  | NO   |     | 0       |                |
| post_attachment | tinyint(1)            | NO   |     | 0       |                |
+-----------------+-----------------------+------+-----+---------+----------------+

POST_ID is the unique key for the post.
TOPIC_ID is the foreign key for the TOPIC the post belongs to.
FORUM_ID is the foreign key for the FORUM that the TOPIC and the POST belong to. This is essentially for shortcut joins :slight_smile: but also drives the “last post in the forum” logic.
POSTER_ID is the foreign key to the USERS table. It either contains a positive integer that is the USER_ID value, or it contains a -1 indicating that it’s a guest (non-logged in) users. For BOB we never allowed guest posts.

I’ll skip a number of the other fields, and talk about POST_USERNAME next. For a guest poster, they have to enter a username that will be used to display on their post. Since there is only one guest entry in the USERS table (with the ID of -1 as already mentioned) the username has to be stored on the post itself.

So the PII elimination process looks like this:

Step 1:

UPDATE POSTS_TABLE P
SET POST_USERNAME = (SELECT U.USERNAME FROM USERS U WHERE P.POSTER_ID = U.USER_ID)

That, or something like it, would transfer the user name from the USERS table onto the POSTS table.

Step 2:

UPDATE POSTS_TABLE
SET POSTER_ID = -1

That marks all posts as being from guest, showing only the username of the original poster. At this point I can drop the USERS table content and all PII - including the country flag - is gone.

Now if we wanted to preserve the country flag, I would add a flag_id column to the POSTS table and update accordingly. The column, as you might expect, is USERS.FLAG_ID. :slight_smile:

If you look at the POSTS table information I included above, the following columns were added during various code modifications / enhancements here on BOB and are not part of a standard phpBB2 install.

POST_REASON_ID
IS_MOD_POST
BEST_ANSWER
POST_LOCKED

If you want a tech reference for the phpBB2 table structure, I built one years ago here:

http://www.phpbbdoctor.com/doc_tables.php


Dave Rathbun :us: (BOB member since 2002-06-06)

The attached image shows the post count by month from BOB’s launch in August of 2002 until now.
bob-posts.png


Dave Rathbun :us: (BOB member since 2002-06-06)

2008 was a banner year for BOB. Must have been a lot of bad documentation or bugs from BO in those days.

And that initial adoption curve shows how badly this community was needed.

I see the issue with keeping the country flag.

I love that table structure meta data page. Did you build that manually or have a script build it for you? It’s an easier interpretation of the structure that jumping into a full DB tool like phpMyAdmin.


kbrazell :us: (BOB member since 2003-08-19)

:mrgreen:

:wah:


Chris Pohl :us: (BOB member since 2002-06-18)

It’s not impossible though. If the new team decides it’s important, I can make it part of the same script that copies over the username.

I built it (manually) as a reference for the phpBB2 community. I was quite active there back in the day as well. I even got a trip to London to meet some of the developers at a phpBB meetup. My presentation on fighting spam in phpBB2 was recorded and you can probably find the video if you know how to look. 8)

What I probably did was download the table structures into Excel, populate the text there, and then upload it. You can tell by the color coding I had grander plans that never quite got finished. But it will help folks that might not be as familiar with the tables structures.

I also have a list (somewhere) of all of the modifications made to BOB. We have extra columns in some tables, some tables were dropped, and many new tables were added. That’s the main issue with converting to phpBB3.


Dave Rathbun :us: (BOB member since 2002-06-06)

Probably just after XIr2 got going in earnest? I seem to recall we went from the old v6 to the new XI suite in 2007 - I was amazed as for once we were at the front-edge of the technology!

Debbie


Debbie :uk: (BOB member since 2005-03-01)

Hate to disappoint you, Debbie, but XI3 came out in February 2008. :slight_smile:

XIr2 was launched in November 2005.


Mark P :uk: (BOB member since 2003-02-03)

Hi All,

I actually started to take on trying to get 2.0 started prior to me seeing this post. I have created a replica of the forum already, got a domain, and I am in the process of trying to index/save the forums as we speak.

I would love to help. It looks like there is interest in the forums to keep the community alive. How would you all like me to proceed or should I hold off?


RickOShay (BOB member since 2019-07-17)

There’s no need for that. When a formal BOB 2.0 project is launched / approved by the community, I will provide the data.


Dave Rathbun :us: (BOB member since 2002-06-06)

I am sorry that I have posted so much content about Data Services during the peak years…

:wink:


Werner Daehn :de: (BOB member since 2004-12-17)

Is there a consensus that we should continue it as a forum rather than any other sort of site?

That is, keep it simple, with as straight a migration as possible to begin with then evolve it to keep it familiar? Just photoshopping a 2.0 on to the current BOB logo to show that it’s something new?
A basic domain with just a forum or would people want something like Invision offer where we can turn some of the stickies into blog posts?
Would all the moderators want to carry on? We’d still need moderators.

Thoughts?


Mark P :uk: (BOB member since 2003-02-03)

If it’s gonna be the 2.0 version then let it be something new and modern, not just a 1:1 replica.

I can carry on as a moderator, no problem with that.


Marek Chladny :slovakia: (BOB member since 2003-11-27)