Firefox PHP
phpBB Conversion (was: Database documentation?)
August 11, 2007 11:02AM
Is the database structure documented anywhere? I'm trying to convert a phpBB board to phorum and I it would be very handy to know how each individual column is used and what the valid values for it are, but I've failed to find this information anywhere. Erhm, I mean, I don't include reading the entire phorum code in "anywhere"...



Edited 1 time(s). Last edit at 08/15/2007 09:02AM by brianlmoon.
Re: Database documentation?
August 11, 2007 11:36AM
No, there is no full database-documentation but feel free to ask for single details and provide some docs.


Thomas Seifert
Re: Database documentation?
August 11, 2007 01:57PM
My current problem is that a huge number of postings return "this thread has been moved" and the redirection fails. The postings exist in the database at the original location, not at the redirection target.

Example: http:// phorum.rickross.com/read.php?13,1343 redirects to http:// phorum.rickross.com/read.php?12,1343 . However, the database says

mysql> select message_id, forum_id, thread, parent_id, author, subject, ip, status, msgid, modifystamp, user_id, thread_count, moderator_post, sort, datestamp, meta, viewcount, closed from ph_messages where message_id = '8671';

| 8671 | 13 | 1343 | 0 | marc landeau | Oprah says Make More Babies | 041d4303 | 2 | | 1108453445 | 1742 | 0 | 0 | 2 | 1105524085 | NULL | 0 | 0 |

This is precisely the posting of the first URL above. Note that forum_id is 13, not 12. The entire thread is indeed in 13:

mysql> select forum_id,thread from ph_messages where thread = '1343';
+----------+--------+
| forum_id | thread |
+----------+--------+
| 13 | 1343 |
| 13 | 1343 |
| 13 | 1343 |
| 13 | 1343 |
| 13 | 1343 |
| 13 | 1343 |
| 13 | 1343 |
| 13 | 1343 |
| 13 | 1343 |
| 13 | 1343 |
| 13 | 1343 |
| 13 | 1343 |
+----------+--------+

So I was wondering what causes the redirection, whether it could be triggered by some unset value in this or even in a completely different table. In other words, I have no idea what I'm looking for, I'm chasing a ghost.

As for providing docs, I could start something, but it would take people with a much deeper understanding of phorum than mine to finish it. Shall I start it anyway? On the wiki, or where?

Z



Edited 1 time(s). Last edit at 08/11/2007 02:01PM by zenon.
Re: Database documentation?
August 11, 2007 02:01PM
thread-starters message_id has to be the same as the thread_id, looks like thats not the case with your data.


Thomas Seifert
Re: Database documentation?
August 11, 2007 03:01PM
You mean it should look like this?

message_id forum_id thread_id parent_id
   8671   |    13  |   8671  |   0

In another forum in the same under-conversion database I have this:

mysql> select message_id,forum_id,thread from ph_messages where thread = '3378';
+------------+----------+--------+
| message_id | forum_id | thread |
+------------+----------+--------+
| 25113 | 12 | 3378 |
| 27715 | 12 | 3378 |
| 27717 | 12 | 3378 |
| 27962 | 12 | 3378 |
| 27970 | 12 | 3378 |

This thread is visible and works, no false redirections there. The settings of the two fora are identical.

Could it be that phorum can tolerate thread_starter message_id != thread, but there is yet another factor influencing redirection?

Z
Re: Database documentation?
August 11, 2007 03:07PM
You mean it should look like this?

Yes, that looks okay.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Database documentation?
August 11, 2007 03:15PM
the check is done with
if(!empty($data) && isset($data[$thread]) && isset($data[$message_id])) {

so there should be some data for the thread AND a message with the message_id of the thread AND the message_id of the currently selected message needs to be there.


It really is a strict requirement with the thread-starter having the thread-id as the message-id.
A new thread is posted in the way that the new message is inserted and the message-id returned is used as the thread-id and for all replies to this message.


Thomas Seifert
Re: Database documentation?
August 11, 2007 05:21PM
I still can't make head or tail of it. If one thread works without false redirections and another doesn't, the reason must be specific to that thread, not general. Then again, all the threads are the same and not one of them meets the "thread = thread-starter message_id" requirement.

The code you quote seems to be line 176 in read.php. If the "if" condition is not met, the "else" statement "$remove_threaded_bodies=0;" applies. That wouldn't cause a redirection, would it?

I fixed message_count and thread_count in phorum_forums which had been empty, and the board behaves now much better than before. I had hoped that this would also make the redirection problem go away, but there I hoped too much.

Any more ideas where to look?

Z
Re: Database documentation?
August 11, 2007 05:38PM
if none of your threads meets the thread = thread-starter message_id then your conversion is broken, no matter what.

you looked at the wrong condition. If the given one fails then
elseif($toforum=phorum_check_moved_message($thread)) { // is it a moved thread?

catches.


Thomas Seifert
Re: Database documentation?
August 11, 2007 06:49PM
Heh, something that never worked isn't broken; it's simply not yet made ;)

If I turn the question around, do you have any idea why a thread with "thread-starter-message_id != message_id" can work and display properly? I have hundreds of them, working just fine. If "thread-starter-message_id != message_id" is a definite show-stopper, then these working threads are a bug. A bug that I perhaps could ride on to make the rest of the threads work as well.

Z
Re: Database documentation?
August 12, 2007 02:40AM
It really isn't too interesting why it might work exactly. Without digging into code, I think this might work in case the forum_id for the thread and for the virtually moved thread (don't know a better name for the anomaly) are the same. But I might be wrong. However, feeding the system data which it wouldn't generate on its own and see how it handles that data is not what you should be looking at when doing a conversion.

Fact is that you'll have to fix your conversion code to setup the fields like Phorum uses them. Looking at the table that you provided above, this is not okay.
+------------+----------+--------+ 
| message_id | forum_id | thread | 
+------------+----------+--------+ 
| 25113      | 12       | 3378   | 
| 27715      | 12       | 3378   |
| 27717      | 12       | 3378   | 
| 27962      | 12       | 3378   | 
| 27970      | 12       | 3378   |
+------------+----------+--------+
Maybe this one is not redirected, because the message with message_id 3378 is in forum_id 12 too. To make this thread work inside Phorum, you will have to change it to look like this:
+------------+----------+--------+------------+
| message_id | forum_id | thread | parent_id  |
+------------+----------+--------+------------+
| 25113      | 12       | 25113  | 0          |
| 27715      | 12       | 25113  | 25113      |
| 27717      | 12       | 25113  | 25113      |
| 27962      | 12       | 25113  | 25113      |
| 27970      | 12       | 25113  | 25113      |
+------------+----------+--------+------------+
I took the same parent_id for all reply messages, because I think that is what phpBB does (flat style discussion, no threading).

After doing the conversion, you will have to run some script from the scripts directory, to fill some data which you might have missed in the conversion. These script are:

* scripts/update_postcount.php
* scripts/rebuild_search_table.php
* scripts/rebuild_meta_data_mysql.php (or rebuild_thread_info for Phorum 5.2)

Especialy the last script will fill some important redundant data in the thread starter messages, which is needed to make it all work.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Database documentation?
August 13, 2007 04:48PM
Thank you Thomas, thank you Maurice. It's done and it works: no more rogue redirections, and I haven't even run the update/rebuild scripts yet. I documented what I did in the neighbouring thread.

Now I wonder if you could help me with another couple of things.

- Is phorum_messages supposed to hold the actual email address of the poster, or should it contain a reference to phorum_users.user_id?
- Where does the "Last post by <user>" field fetch the user from? Mine are empty so far, as in "Last post by ".
- What does phorum_users.is_dst do and what is the format for it?

Z
Re: Database documentation?
August 13, 2007 05:16PM
Quote

- Is phorum_messages supposed to hold the actual email address of the poster, or should it contain a reference to phorum_users.user_id?

ref to user_id. email-address is there only for anonymous users.

Quote

- Where does the "Last post by <user>" field fetch the user from? Mine are empty so far, as in "Last post by ".

its fetched on posting ;). run the meta-data upgrade-script maurice told you and it will be fixed.

Quote

- What does phorum_users.is_dst do and what is the format for it?

it sets the user's time to DST (daylight savings time) aka +1 hour if I remember correctly.
can be 0 or 1.


Thomas Seifert
Re: Database documentation?
August 14, 2007 03:30PM
Quote
mmakaay
After doing the conversion, you will have to run some scripts...

I finished the entire board conversion (private messages still left to do) and run the scripts. Most things went exactly as they should, all thread parents got metadata, the "Last post by " fields got filled, everything seems to work fine, except a couple of cosmetic details:

- In the main forum index, all fora have a last post of 1970-01-01, although the thread last post fields inside each forum are correct.
- In the main forum index, all fora have a thread and post count, but only two of them have a "new" flag and count.

Any suggestions how to fix them?

Z
Re: Database documentation?
August 14, 2007 03:33PM
Quote
zenon
- In the main forum index, all fora have a thread and post count, but only two of them have a "new" flag and count.

This one got fixed by setting "count views" to "no" and then back again to "yes".

Z
Re: Database documentation?
August 14, 2007 03:44PM
Quote

- In the main forum index, all fora have a thread and post count, but only two of them have a "new" flag and count.

only forums which you have visited already show a new-count.

Quote

- In the main forum index, all fora have a last post of 1970-01-01, although the thread last post fields inside each forum are correct.

hmm, in 5.2 its in the database integrity panel in the admin. dunno if we have a script for 5.1 too.
Essentially its the following script (untested, taken from admin and out of my head):
<?php
        // this script needs to be run in the phorum-dir
        include './common.php';

        // we need to rebuild the forumstats
        $forums = phorum_db_get_forums();

        // shouldn't be needed but just in case ...
        $old_forum_id = $PHORUM['forum_id'];

        $forums_updated=0;
        foreach ($forums as $fid => $fdata) {

            if($fdata['folder_flag'] == 0) {

                $PHORUM['forum_id'] = $fid;

                phorum_db_update_forum_stats(true);

                $forums_updated++;
            }
        }

        $PHORUM['forum_id'] = $old_forum_id;

        echo "$forums_updated forum(s) updated.<br />\n";
?>


Thomas Seifert



Edited 1 time(s). Last edit at 03/18/2008 11:42AM by ts77.
Re: Database documentation?
August 15, 2007 08:53PM
Quote
ts77
Essentially its the following script (untested, taken from admin and out of my head):

Well, now it is tested. There was a single quote missing at
        // this script needs to be run in the phorum-dir
        include './common.php;
With
        include './common.php';
it works perfectly :)

Thank you again.

Z
Sorry, only registered users may post in this forum.

Click here to login