Taparip

[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.