No announcement yet.

Questions regarding migration from UBB.Threads 6.4.1 to vBulletin 4.0.6

  • Filter
  • Time
  • Show
Clear All
new posts

  • Questions regarding migration from UBB.Threads 6.4.1 to vBulletin 4.0.6

    Dear all,

    Yesterday I tried a sample import of a rather large board running UBB.Threads 6.4.1 into a fresh vBulletin 4.0.6 installation. This step will give me confidence whether this migratin effectively makes sense. So far it does for 80%

    First of all, I am happy to see that ImpEx is able to retrieve quite some information from the current board, which is very good.

    Here are a couple findings, recommendations and questions I want to share:

    0. You cannot import users, posts and threads while preserving their IDs. This is a pity since it means there's no simple redirect script that can be written to direct people to the same content in vBulletin. As far as I know, the key publicly identified data are: User ID, Thread ID and Post ID. In UBB.Threads a thread starts with a post having (B_Number = B_Main) AND B_Topic = 1. In vBulletin I believe thread counters and post counters live independent lives.

    The code in the ImpEx module for UBB.Threads explicitly mentions that the UBB.Threads IDs cannot be used as import ID.

    Question 1: why can't the UBB.Threads post/thread/user IDs be used in the vBulletin DB?

    1. You need to rename the Calendar table (UBB.Threads 6.4.1) to Events to get ImpExp do its work on UBB.Threads 6.4.1. Also, table names are case sensitive, which may require additional renames to set the case accordingly.

    2. Importing users takes most of the time. I managed to import 17,000 users per hour, but extrapolating my figures, in the same hour I could import 140,000 posts.

    Recommendation: select the maximum allowed avatar size for the user import, and stay at max 100 user imports per run. This way you will avoid most rejects due to oversized avatar image or due to excess file descriptors in use (!).

    3. If there are banned users on your UBB.Threads board (SELECT u.U_Number, u.U_LoginName, u.U_Username, u.U_Email, b.B_Reason FROM Users u LEFT JOIN Banned b ON u.U_Number = b.B_Uid WHERE u.U_Banned = 1), they do not appear as banned in vBulletin 4.

    Upon importing members, they all are permabanned by default.

    Workaround: Delete the "Users" user group (this should be the group with most members). This way all imported users will become "Registered users" on vBulletin.

    Now it's a matter of banning the users that should be banned.

    Question 2: What do I have to do to correct the "banned" status of my imported members? I.e., unban all members except if they were banned in UBB.Threads.

    4. Importing file attachments to the file system does not work. Apparently this is a known restriction, and only database imports are supported.

    Question 3: can I do the import to DB and move the attachments to the file system afterwards? What will break if I do so?

    5. When your import session ends, you will notice that your forums do not appear anywhere, not even in the admin panel.

    Workaround: create a forum (or forum category - see below) after the import to see all your forums.

    6. ImpEx cannot recover forum categories from UBB.Threads 6.4.1. You would retrieve the forum categories via the following SQL query:

    SELECT Cat_Title, Cat_Number, Cat_Description FROM Category WHERE Cat_Type = 'forum' ORDER BY Cat_Number ASC

    The forums and their category hierarchy can be retrieved from UBB.Threads with:

    SELECT Bo_Cat, Bo_Sorter, Bo_Number, Bo_Title , Bo_Description FROM Boards ORDER BY Bo_Cat, Bo_Sorter;

    You have to manually recreate them after importing the forums. This, in combination with assigning the order of the sub forums in the categories (and setting the forum display options), took me about 1 hour for 50 boards and 8 categories.

    Question 4: is there an easier way to assign the lost forum hierarchy?

    7. The default forum display options are odd: sort per thread title instead of newest thread first. Every single forum had to have its display options updated. Best is to do this while recreating the forum hierarchy in the categories.

    Question 5: is there a way to bulk assign the same settings to all forums?

    8. vBulletin assumes the login name is the same as the display name, as opposed to UBB.Threads. I see that the display names from UBB.Threads are not imported, not even in custom profile fields.

    Question 6: how can I import the display name to vBulletin? Can we prevent members from editing this field?

    9. You must recreate the counters and stats after an import. This can take a while.

    Apologies for this lengthy first message, but I believe it gives an idea to other members willing to assess the migration from UBB.Threads 6.4.1 to vBulletin 4.0.x about what to consider.

    I am looking forward to read your hints, recommendations and answers

    Best regards,

    Last edited by shutterfreak; Sat 4 Sep '10, 3:47am. Reason: Update: all imported users are banned.

  • #2
    Importing smileys (graemlins)

    Extra question: how do I import the smileys from UBB.Threads 6.4.1 to vBulletin?

    The smileys can be found in UBB.Threads in the Graemlins table:

    SELECT G_Number, G_Code, G_Smiley, G_Image FROM Graemlins ORDER BY G_Number ASC;


    • #3
      Bug: importing birthday info from UBB.Threads (+ fix)

      Bug: Importing birthday information from members swaps month and day.

      The information cannot be recovered, since it's translating day via "day % 12" to month (e.g., 15 becomes 3 = March).

      For the record, the UBB.Threads birthday information is stored in Users.U_Birthday, and the default format is M/D/YYYY (e.g., "11/21/1954", "7/31/1990").

      Apparently ImpEx thinks it's in D/M/YYYY format, which is wrong.

      The offending code snippet is in "upload/impex/systems/ubb_threads/004.php":
      PHP Code:
      $bits explode('/',$user_details['U_Birthday']); # DD/MM/YYYY
      $try->set_value('nonmandatory''birthday',            $bits[1] . "-" $bits[0] . "-" $bits[2]); # MM-DD-YYYY
      $try->set_value('nonmandatory''birthday_search',    $bits[2] . "-" $bits[1] . "-" $bits[0]); # YYYY-MM-DD

      I believe it should read:
      PHP Code:
      $bits explode('/',$user_details['U_Birthday']); # MM/DD/YYYY
      $try->set_value('nonmandatory''birthday',             $bits[0] . "-" $bits[1] . "-" $bits[2]); # MM-DD-YYYY
      $try->set_value('nonmandatory''birthday_search',     $bits[2] . "-" $bits[0] . "-" $bits[1]); # YYYY-MM-DD

      Please consider reviewing and including this patch.

      Best regards,



      • #4
        Subtle differences between 6.5 and 6.4.1 for ImpExp

        Ok, I start to understand the differences between UBB.Threads 6.4.1 and 6.5 (supported by ImpEx).

        So far the changes I found, are:

        1. The calendar events are in the "Events" table in UBB.Threads 6.5 and "Calendar" table in UBB.Threads 6.4.

        2. The forum categories are described in the "Categories" table, but apparently UBB.Threads has now an extra "Cat_Entry" column which UBB.Threads 6.4.1 lacks. Hence you have to edit 005.php around line 120:
        PHP Code:
                    foreach ($categories_array as $cat_id => $cat)
        $try = (phpversion() < '5' $category_object : clone($category_object));

        $try->set_value('mandatory''title',                 $cat['Cat_Title']);
        $try->set_value('mandatory''displayorder',        ($cat['Cat_Number'] == $cat['Cat_Number']));
        $try->set_value('mandatory''parentid',            '-1');
        $try->set_value('mandatory''importforumid',        '0');
        $try->set_value('mandatory''options',                $this->_default_cat_permissions);
        // For UBB.Threads 6.5:
                        // $try->set_value('mandatory', 'importcategoryid',    $cat['Cat_Entry']);
                        // For UBB.Threads 6.4.1:
        $try->set_value('mandatory''importcategoryid',    $cat['Cat_Number']); 
        This pretty much answers Question 4: is there an easier way to assign the lost forum hierarchy?
        Last edited by shutterfreak; Sat 4 Sep '10, 11:56am. Reason: Add link to answering question 4


        • #5
          Bulk importing your custom usertitles

          Here's the way to bulk import your custom user titles:

          Step 1. Log in as admin on UBB.Threads, and choose "edit titles" (/admin/edittitles.php). Or grab the file with user titles directly from your web server (./filters/usertitles).

          Step 2. Create an empty Excel sheet, and copy/paste the entries in the first 2 columns (column A = title, column B = min post count), starting at row 1

          Step 3. Paste the following Excel formula in cell C1: ="("&ROW()&", "&B1&", '"&A1&"')," and copy the formula downwards.

          Step 4. Open a text editor, and:
          4.1: Type the following text: INSERT INTO usertitle usertitleid, minposts, title) VALUES
          4.2: Paste the value from the cells in column C right below this text
          4.3: Replace the last comma after the closing parenthesis on the last line with a semicolon (";")
          This is the SQL query to run to recreate your custom user titles.

          Step 5. Log in as super user on your vBulletin installation, go th the admin CP. Make sure you enabled SQL queries for this user (see config.php).

          Step 6. Run the following SQL query to delete the current custom titles:
          DELETE FROM usertitle;

          Step 7. Run the query that you created in step 4 above:
          INSERT INTO usertitle (usertitleid, minposts, title) VALUES
          (1, 0, 'stranger'),
          (2, 1, 'newbie'),
          (10, 10000, 'keyboard strangler');


          • #6
            Re: Question 5 - Bulk setting the thread order on all your boards to newest first

            I found the answer to Question 5 (is there a way to bulk assign the same settings to all forums) above.

            The forum settings are stored in the forum table (makes sense).

            To set all forums to "view newest post/thread first", you must issue the following SQL query:
            UPDATE forum SET defaultsortfield='lastpost', defaultsortorder='desc';
            To always display ALL posts (without 'pruning' X days), issue the following query:
            UPDATE forum SET daysprune=-1;
            In my case both applied (I only have 1-2 boards from the 50 that require different settings), hence you can combine both queries in one:
            UPDATE forum SET defaultsortfield='lastpost', defaultsortorder='desc', daysprune=-1;


            • #7
              Re: Question 6 (how can I import the display name to vBulletin?)

              Regarding Question 6 I found a solution, which turns out to be my first vBulletin 4 hack

              See this thread on for details.

              If you want the real user name to appear after an import, then you should get it from the userfield table in the vBulletin database. In my case, the real user name appeared in field 5, hence I ran the following SQL query to create a list of UPDATE statements:
              SELECT CONCAT('UPDATE user SET username = "', field5, '" where userid = ', userid, ';') FROM userfield WHERE userid>1 ORDER BY userid ASC
              If you want to recover the list of display names from UBB.Threads, then you will have to retrieve it from the UBB.Threads database through the following query:
              SELECT CONCAT( 'UPDATE user SET username = "', U_Username, '" where importuserid = ', U_Number, ';' )
              FROM w3t_Users
              ORDER BY U_Number ASC
              This query will create 1 UPDATE statement per user in your UBB.Threads board! Note: you may have to check the character set used in the MySQL connection to correctly transfer the display names between the UBB.Threads and the vBulletin databases. See for example this blog post.

              Then you still have to import the display names, by running the output of the query above against the vBulletin database.

              Once you've done that, rebuild the thread and forum info via the AdminCP > Maintenance.


              • #8
                Cleaning posts with cleaner.php (with regex support)

                Once the import has been done, it is time to look at the way the imported data is represented. Usually you'll have to run cleaner.php after having tweaked it according to your situation. This was not different to my scenario.

                Here's the string substitution array I used for posts:

                PHP Code:
                $replacer = array(
                '[ QUOTE ]'     => '[QUOTE]',
                '[/ QUOTE ]'        => '[/QUOTE]',
                '<ul type="square">'    => '[LIST]',
                '[img]'         => '[IMG]',
                '[/img]'        => '[/IMG]',
                '[IMG]/'        => '[IMG]http://www.myhost.mydomain/',
                '<blockquote><font class="small">Quote:</font><hr />'
                => '[QUOTE]',
                '<hr /></blockquote>'
                => '[/QUOTE]',
                '<font class="small">Code:</font><hr /><pre>'
                => '[CODE]',
                '</pre><hr />'      => '[/CODE]',
                '&amp;'         => '&',
                '</font>'   => '[/COLOR]',
                Actually, you can hunt for the BBcode for your smileys and replace them. I did not do this yet.

                First thing I noticed is that cleaner.php does not support pattern matching substitutions (regular expression patterns). In order to replace the BBcode for colors you definitely need this, or you can't easily translate <font color="navy"> to [COLOR=navy]

                To provide this, I added a preg_replace() call after the str_replace() call in several parts in cleaner.php, like in the snippet below:

                PHP Code:
                    while ($post $Db_target->fetch_array($posts))
                $text str_replace(array_keys($replacer), $replacer$post['pagetext']);
                $text preg_replace(array_keys($replacer_regex),
                array_values($replacer_regex), $text);
                // Remainder of code goes here... 
                To get this pattern matching working, all that remained was to define the hash array variable $replacer_regex, like in:
                PHP Code:
                $replacer_regex = array(
                '|\<font color\="([a-zA-Z0-9]+)"\>|'    => '[COLOR=${1}]',


                • #9
                  Added identified UBB.Threads ImpEx issues to the tracker

                  I created issue tickets for the items related to this thread:
                  1. Feature request: Provide support for UBB.Threads 6.4.1 import
                  2. Feature request: Providing regex support in cleaner.php
                  3. Bug: Month and Day swapped when importing birthday info from UBB.Threads 6.x
                  4. Bug: UBB.Threads 6.x import - quotes in post titles should not be backslash escaped in the post table

                  Feel free to vote on these issues if they're relevant for you.



                  widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.