Taparip

[SignalAffairs]

I know one in the UK but I haven't been in touch with him in a while. I'm migrating to a new (phpBB 3.3) forum that I have running. I wanted to merge it with that new database.

[Labster]

Ah, got it.

I am attempting to solve my free time problem.  Going to go to the big boss next week and say I either need to be a programmer or a manager, but not both.  I'm not trying to avoid you, I haven't abandoned it, it's just...

   

... I'm going through some things at work.

[Nick_nl]

Hi, New guy here. I found this board searching a way to convert the current message board away from Tapatalk into a real PHPbb board.
The Taparip tool worked just great, for a novice actually easy to use. But how should the *.db file be converted into something that phpbb can use? Thanks

[Black Aeronaut]

.... Somehow, I suspect that this thread is soon going to start becoming more and more noticeable to the search engines.

Given how people dislike Tapatalk and will likely be looking for a way to get away from it, we might want to consider giving TapaRip a proper home. It might also be a way of attracting others to assist and expand the capabilities of the tool so Labster isn't quite so burdened.

[Bob Schroeck]

That reminds me... I really should go to the old boards and wipe them clean. Three and a half years is long enough.

[robkelk]

Well, I did consult the old boards last week, in order to update a link in one of my posts here. ("Which post was that pointing to again? Oh, right." Then find the linked-to post here, then update the link in the old post.)

[Nick_nl]

This specific thread is easily found through google, so yes, given the really great performance of TT it might get crowded here

[Labster]

I am here, once again.

As lead dev, I have to say, I honestly have never actually run one of these forums, so actually putting it into another database is a little beyond my knowledge right. That was the part that Bob did. There were two parts to converting though. The ripping program pulls the content of each post as HTML, and I'm assuming that what you actually need saved in the phpBB is the BBcode. (I guess it's possible it stores HTML?)

But assuming you need to convert HTML to BBcode, I can do that. I have one parser that converts it to MyCode, since this forum uses MyBB. This parser is actually pretty terrible -- it's just a series of regexes. If you can find me an overview page of BBcode syntax, I can probably write an much better parser now using Parse::RecDescent. Or maybe just tweak the old terrible one a little bit.

The next part involves importing the posts into the new software. Not sure where to get started with that. This was one of those tools that was put together with duct tape and baling wire so that two software devs could use it for a one-time task.

[Bob Schroeck]

Since SQLite didn't support the SQL features I needed to use (cursors, if I recall correctly), I exported the relevant tables from SQLite and imported them into a copy of Microsoft SQL Server I had on my home system.  In the process I added a bit column to the posts and threads tables called "Transferred" which I used to flag what had been moved and what hadn't.

I recreated the forum structure through the MyBB admin panel, and once I had that all set up, I asked everyone to re-register at the new site.  And when that was done, I could start moving things.

I wrote T-SQL scripts that would go through the MS SQL database one thread at a time and using that information would themselves write MySQL scripts to recreate them.  These I would copy into the phpMyAdmin interface to the DB my ISP set up on my account there, and execute to import ten or twenty threads at a time. 

I remember having some problems because of how large some messages were, as well as formatting issues that were at least 85% the fault of Crapatalk's ham-handed import of Yuku, and maybe 15% the fault of Labster's parser at most.  But nearly everything I could fix on the fly, correcting what went wrong in phpMyAdmin. 

I won't say it was all easy -- I made some kind of error early on that accidentally blanked everything I had on the new boards and had to start all over again -- but all in all it took less than a month of an hour or two's effort per evening. 

Here's the script I wrote to read one thread from the MS database and output a MySQL script that would recreate it in the MyBB database:

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Robert Schroeck
-- Create date: 8/13/2017
-- Description: Proc to export thread data to MyBB
--              Currently written to export one thread -- the next unprocessed one in the passed forum id.
-- =============================================
CREATE PROCEDURE ExtractYukuThreadToMyBB
    -- Add the parameters for the stored procedure here
    @YukuForumId int = 5, -- defaulting to 5 (marketplace) for initial testing
    @MyBBForumId int = 6 -- id for Marketplace on MyBB, again for initial testing
AS
BEGIN
    -- Temp table to hold script output
    DECLARE @mybb_script table (Id int NOT NULL identity(1,1), cmd varchar(MAX));

    -- Post record fields we need to retrieve
    DECLARE @yuku_post_author VARCHAR(80);
    DECLARE @yuku_post_utime INT;
    DECLARE @yuku_post_edit_count INT = 0;
    DECLARE @yuku_post_edit_user VARCHAR(80);
    DECLARE @yuku_post_edit_time INT;
    DECLARE @yuku_post_post_title VARCHAR(120);
    DECLARE @yuku_post_content VARCHAR(MAX);
    DECLARE @yuku_post_signature VARCHAR(2048);

    -- Other variables needed for the process
    DECLARE @yuku_tid INT;
    DECLARE @yuku_thread_topic VARCHAR(120);
    DECLARE @reply_count INT = -1;  -- Reply count is one less than number of posts in thread.
    DECLARE @new_line VARCHAR(MAX);
    DECLARE @content_and_sig VARCHAR(MAX);   

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Get next unprocessed thread in this forum.  There's only two
    -- fields of interest in a forum record, so just dump them right
    -- into variables. 
    SELECT TOP 1 @yuku_tid = tid, @yuku_thread_topic = REPLACE(CAST(topic AS VARCHAR(120)), '''', '''''')
    FROM [DW Forums Temp].[dbo].[threads]
    WHERE forum_id = @YukuForumId AND (Transferred is Null) or (Transferred = 0)
    ORDER BY tid ASC;

    -- if @yuku_tid is null, we've exhausted this forum and there are no more
    -- threads in it to process.  Exit.
    IF @yuku_tid IS NULL
    BEGIN
       SELECT '-- No threads left in forum #' + CAST(@YukuForumId AS VARCHAR(6));
       RETURN;
    END;

    -- Okay, we have a thread to process.  Let's get going.

    -- Start creating the output.
    INSERT INTO @mybb_script (cmd) VALUES ('-- Start thread transfer');
    INSERT INTO @mybb_script (cmd) VALUES ('SET @mybb_tid = 0;');
    INSERT INTO @mybb_script (cmd) VALUES ('SET @first_pid = 0;');
    INSERT INTO @mybb_script (cmd) VALUES ('SET @last_pid = 0;');
    INSERT INTO @mybb_script (cmd) VALUES ('SET @user_id = 0;');

    -- Generate the statement that will create the new thread over on MySQL.
    -- This is the bare minimum data needed to create the thread record.
    -- Several fields have dummy values which will be filled in at the end
    -- of the process.
    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- Insert Mybb_threads record');
    SET @new_line = 'INSERT INTO Mybb_threads (fid, subject, username, lastposter, closed, notes, visible) VALUES (' +
                    CAST(@MyBBForumId AS VARCHAR(6)) + ', ''' + @yuku_thread_topic + ''', ''dummy'', ''dummy'', '''', '''', 1);';
    INSERT INTO @mybb_script (cmd) VALUES (@new_line);

    -- Store the id for this insert in MySQL
    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- Get thread ID');
    INSERT INTO @mybb_script (cmd) VALUES ('SET @mybb_tid = LAST_INSERT_ID();');

    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- Beginning post insertion for thread');

    -- Now set up a cursor to get all the post records for this thread id from posts.
    DECLARE posts_in_thread INSENSITIVE SCROLL CURSOR FOR
        SELECT author, utime, edit_count, edit_user, edit_time, post_title, content, signature
        FROM [DW Forums Temp].[dbo].[posts] WHERE topic = @yuku_tid ORDER BY seq;
    OPEN posts_in_thread;

    -- Grab first record in cursor, which should be the first post in the
    -- thread.
    FETCH NEXT FROM posts_in_thread INTO @yuku_post_author, @yuku_post_utime,
        @yuku_post_edit_count, @yuku_post_edit_user, @yuku_post_edit_time,
        @yuku_post_post_title, @yuku_post_content, @yuku_post_signature;
       
    -- Before we work through the cursor, generate statements to update the
    -- MyBB thread record with info needed from first post.
    -- Tell MySQL to retrieve the MyBB user id for the yuku author
    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- Get user id for author of first post');
    INSERT INTO @mybb_script (cmd) VALUES ('SET @user_id = COALESCE((SELECT uid from Mybb_users WHERE username = ''' + @yuku_post_author + '''), 0);');

    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- Update Mybb_threads record with info from first post');       
    -- Now generate the MySQL statement to do the first update on the thread
    -- record.
    SET @new_line = 'UPDATE Mybb_threads ' +
                    'SET uid = @user_id, username = ''' + @yuku_post_author + ''', ' +
                    'dateline = ' + CAST(@yuku_post_utime AS VARCHAR(12)) +
                    ' WHERE tid = @mybb_tid;';
    INSERT INTO @mybb_script (cmd) VALUES (@new_line);
   
    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- Start posts loop');
    WHILE @@FETCH_STATUS = 0 
    BEGIN
        -- Increment local reply count.  (Count is initialized at -1.)
        SET @reply_count = @reply_count + 1;

        -- Look up the MyBB user id for the current yuku author
        -- Yeah, this gets done twice for the first record. 
        INSERT INTO @mybb_script (cmd) VALUES ('');
        INSERT INTO @mybb_script (cmd) VALUES ('-- Get user id for author of current post');
        INSERT INTO @mybb_script (cmd) VALUES ('SET @user_id = COALESCE((SELECT uid from Mybb_users WHERE username = ''' + @yuku_post_author + '''), 0);');

        -- Append the signature to the post content
        if @yuku_post_signature IS NOT NULL
            SET @content_and_sig = @yuku_post_content + @yuku_post_signature
        ELSE
               SET @content_and_sig = @yuku_post_content;
               
        -- Make sure we have no single apostrophes in the content or title
        SET @content_and_sig = REPLACE(CAST(@content_and_sig AS VARCHAR(MAX)), '''', '''''');
        SET @yuku_post_post_title = REPLACE(CAST(@yuku_post_post_title AS VARCHAR(MAX)), '''', '''''');

        INSERT INTO @mybb_script (cmd) VALUES ('');
        INSERT INTO @mybb_script (cmd) VALUES ('-- Create Mybb_posts record for post number ' + CAST(@reply_count as VARCHAR(3)));

        -- Generate the statement to insert this post into Mybb_posts
        SET @new_line = 'INSERT INTO Mybb_posts (tid, fid, subject, uid, username, dateline, message, visible) ' +
                        'VALUES (@mybb_tid, ' + CAST(@MyBBForumId AS VARCHAR(6)) + ', ''' + @yuku_post_post_title + ''', @user_id, ''' +
                        @yuku_post_author + ''', ' +  CAST(@yuku_post_utime AS VARCHAR(12)) + ', ''' + @content_and_sig + ''', 1);';
        INSERT INTO @mybb_script (cmd) VALUES (@new_line);

        -- If reply_count is 0, have MySQL save the post id
        IF @reply_count = 0
            BEGIN
                INSERT INTO @mybb_script (cmd) VALUES ('');
                INSERT INTO @mybb_script (cmd) VALUES ('-- Save first post''s ID.');
                INSERT INTO @mybb_script (cmd) VALUES ('SET @first_tid = LAST_INSERT_ID();');
            END;
       
        -- Get the next post record
        FETCH NEXT FROM posts_in_thread INTO @yuku_post_author, @yuku_post_utime,
            @yuku_post_edit_count, @yuku_post_edit_user, @yuku_post_edit_time,
            @yuku_post_post_title, @yuku_post_content, @yuku_post_signature;
    END;
    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- End posts loop');

    -- Get the pid for the last insert.
    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- Get id of final post');     
    INSERT INTO @mybb_script (cmd) VALUES ('SET @last_pid = LAST_INSERT_ID();');

    -- And now explicitly go to the last record to get info from it for the thread record.
    FETCH LAST FROM posts_in_thread INTO @yuku_post_author, @yuku_post_utime,
        @yuku_post_edit_count, @yuku_post_edit_user, @yuku_post_edit_time,
        @yuku_post_post_title, @yuku_post_content, @yuku_post_signature;

    -- Generate statement to update the thread record for the last time.
    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- Get user id for author of final post');     
    INSERT INTO @mybb_script (cmd) VALUES ('SET @user_id = COALESCE((SELECT uid from Mybb_users WHERE username = ''' + @yuku_post_author + '''), 0);');

    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- Update Mybb_threads record for the last time');
    SET @new_line = 'UPDATE Mybb_threads ' +
                    'SET firstpost = @first_pid, lastpost = ' + CAST(@yuku_post_utime AS VARCHAR(12)) + ', lastposter = ''' + @yuku_post_author + ''', ' +
                    'lastposteruid = @user_id, replies = ' + CAST(@reply_count AS VARCHAR(6)) +
                    ' WHERE tid = @mybb_tid;';
    INSERT INTO @mybb_script (cmd) VALUES (@new_line);   

    -- Nuke the cursor
    CLOSE posts_in_thread;
    DEALLOCATE posts_in_thread;
   
    -- Update the forum record
    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- Update the forum record');
    SET @new_line = 'UPDATE mybb_forums ' +
                    'SET threads = threads + 1, posts = Posts + ' + CAST(@reply_count + 1 AS VARCHAR(6)) + ', '+
                    'lastpost = ' + CAST(@yuku_post_utime AS VARCHAR(12)) + ', ' +
                    'lastposter = ''' + @yuku_post_author + ''', ' +
                    'lastposteruid = @user_id, lastpostsubject = ''' + @yuku_thread_topic + ''' ' +
                    'WHERE fid=' + CAST(@MyBBForumId AS VARCHAR(6)) + ';';
    INSERT INTO @mybb_script (cmd) VALUES (@new_line);

    -- Indicate end of MySQL code.
    INSERT INTO @mybb_script (cmd) VALUES ('');
    INSERT INTO @mybb_script (cmd) VALUES ('-- And we''re done.');
    INSERT INTO @mybb_script (cmd) VALUES ('');

    -- Mark the Yuku posts as transferred.
    UPDATE [DW Forums Temp].[dbo].[posts]
    SET Transferred = 1
    WHERE topic = @yuku_tid

    -- Mark the Yuku thread as transferred.
    UPDATE [DW Forums Temp].[dbo].[threads]
    SET Transferred = 1
    WHERE tid = @yuku_tid;

    -- And output the MySQL code
    SELECT cmd FROM @mybb_script Order by Id
END
GO

I would then call this through a "batching" script that I would pass the Yuku source forum, the MyBB destination forum, and the number of threads to move, generating the MySQL code to transfer (create) however many threads in a single go:

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Robert Schroeck
-- Create date:   8/29/2017
-- Description:   Proc to run ExtractYukuThreadToMyBB multiple times with one call and one output.
-- =============================================
CREATE PROCEDURE YukuThreadBatcher
    -- Add the parameters for the stored procedure here
    @YukuForumId INT = 25, -- defaulting to 25 (Bob's Other Fan Writing) for initial testing
    @MyBBForumId INT = 18, -- id for Bob's Other Fan Writing on MyBB, again for initial testing
    @ThreadCount INT = 5 -- number of threads to export in this run.
AS
BEGIN
    -- Temp table to hold script output
    DECLARE @mybb_script table (Id INT NOT NULL identity(1,1), cmd varchar(MAX));

    -- Counter variable for WHILE loop
    DECLARE @counter INT = 0

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert start of transaction control
    INSERT INTO @mybb_script (cmd) VALUES ('-- Start thread batch');
    INSERT INTO @mybb_script (cmd) VALUES ('START TRANSACTION;');

    WHILE @counter < @ThreadCount
    BEGIN
        -- Have MySQL set a savepoint
        -- Use "ROLLBACK TO (savepoint name)" to rollback a single thread when it fails; commit to save others.
        INSERT INTO @mybb_script (cmd) VALUES ('');
        INSERT INTO @mybb_script (cmd) VALUES ('-- Set savepoint for new thread');
        INSERT INTO @mybb_script (cmd) VALUES ('SAVEPOINT SP_' + CAST(@counter as VARCHAR(4)) + ';');
        INSERT INTO @mybb_script (cmd) VALUES ('');

        -- Insert results of an execution of ExtractYukuThreadToMyBB to @mybb_script.
        INSERT INTO @mybb_script (cmd)
        EXEC ExtractYukuThreadToMyBB @YukuForumId, @MyBBForumId
        SET @counter = @counter + 1;
    END

    -- And end with a commit; if the resulting script fails, use ROLLBACK to undo it.
    INSERT INTO @mybb_script (cmd) VALUES ('COMMIT;');

    -- And output the cumulative results for cut'n'paste
    SELECT cmd FROM @mybb_script Order by Id
END
GO

And that's the heart of it.  Like Labster said, a jerryrig. Running this would produce a one-column "table" that was actually a massive script which I would then cut from MS SQL and paste into phpMyAdmin, as I noted above.

I left out a few things I really shouldn't've -- the SQLite database is amazingly complete and includes things like "last edit" information which I didn't bring over, because by the time I was confident I was doing this right, I was already way too far in to start over again with more information output by the scripts.  If you're reasonably adept at SQL you should be able to adapt this to whatever forum system you choose and probably improve considerably on it.

EDIT: I just remembered one of the other things I didn't do right with these scripts. The timestamps were off by several hours. I don't know if I ever figured out definitively why, but I suspect it had something to the board displaying a local time when it was scraped, and that local time being treated as GMT when it was plugged into the relevant fields in the MyBB database.

[Nick_nl]

Thanks for the information. So far i did the low tech copy and past method. I did copy and past all the members into the new members database and those are now visible, only to find that the accounts are useless since the email and passwords are not available.
I have been using the same method on the posts, but this doesn't work as intended. The PHPBB3.3.2 database looks to be way more complex than the backup.

Granted I'm a mechanical engineer, not a software engineer

Labster, i did find SQLCODE Function Would this be what you are looking for?

[Bob Schroeck]

Yeah, I wouldn't've suggested just cutting and pasting your user table. That wasn't going to work. That's why I asked everyone at my old board to register at the new one under the same name (or if they changed it, to let me know explicitly; I had/have scripts to handle that kind of thing, as LynnInDenver can testify). I suggest you truncate that table and do the same; then once everyone has recreated their accounts, you can write a script to carry over data like original registration dates and whatnot.

As for posts and threads, well, if PHPBB works much like MyBB, it's going to be ... complicated. You're going to have to create thread records and post records in parallel because they reference each other. And you can't just cut and paste from your old board because there's no guarantee that the thread and post IDs are even going to be close; in fact it's almost guaranteed that unless you recreate every thread and post on the board in perfect chronological order they won't be. That's why I needed to use cursors -- I needed to recreate one thread at a time and then insert its posts one post at a time, in proper order to maintain the board integrity.

If I get a chance I'll see if the PHPBB database structure is on line anywhere, and give it a look-see; maybe I can offer you some more specific advice.

[Nick_nl]

I have found this website with a list of Database Tables used within phpBB3.
I do notice that it is a really complex exercise, even the guys a PhPbb told me to start over from scratch.
Thanks for the help

[Bob Schroeck]

Eh. Looks no more complex than MyBB. I don't have the time right now, but if I have a couple hours free over the weekend I'll see if I can't dummy up some pseudo-SQL for you. You'll probably want to do like I did and export the SQLite DB into something else with a more capable SQL dialect because I found it critical to use cursors.