Conversion from guestbook

I'm going to postpone this until after the full 2.0 release, but this will definitely be useful.

I'd like to get the Status ID sequential in the import, and also have the poster ID and owner ID correct. Is there any way you could test this query to see if it will work? (Obviously make sure to replace the curly braces with table prefixes.)

INSERT INTO {facebook_status} (uid, pid, status, status_time)
SELECT (
    (SELECT
        recipient AS uid,
        author AS pid,
        message AS status,
        created AS status_time
    FROM {guestbook}
    ) AS x
    UNION
    (SELECT
        author AS uid,
        commentauthor AS pid,
        comment AS status,
        created + 100 AS status_time
    FROM {guestbook}
    ) AS y
);

Otherwise, the data will probably have to be transferred to a temporary table first:

INSERT INTO {facebook_status_temp} (uid, pid, status, status_time)
SELECT
    recipient as uid,
    author as pid,
    message as status,
    created as status_time
FROM {guestbook};

INSERT INTO {facebook_status_temp} (uid, pid, status, status_time)
SELECT
    author as uid,
    commentauthor AS pid,
   comment AS status,
   created + 100 AS status_time
FROM {guestbook}
WHERE comment != '';

INSERT INTO {facebook_status} SELECT * FROM {facebook_status_temp};

Well, can you try it without the "AS x"/"AS y" then? Been awhile since I wrote queries with UNION and I forgot the temporary tables don't use aliases.

INSERT INTO {facebook_status} (uid, pid, status, status_time)
SELECT (
    (SELECT
        recipient AS uid,
        author AS pid,
        message AS status,
        created AS status_time
    FROM {guestbook}
    )
    UNION
    (SELECT
        author AS uid,
        commentauthor AS pid,
        comment AS status,
        created + 100 AS status_time
    FROM {guestbook}
    )
);

Thanks

Try running just the UNION part:

    (SELECT
        recipient AS uid,
        author AS pid,
        message AS status,
        created AS status_time
    FROM {guestbook}
    )
    UNION
    (SELECT
        author AS uid,
        commentauthor AS pid,
        comment AS status,
        created + 100 AS status_time
    FROM {guestbook}
    )

If that runs okay, try this:

INSERT INTO {facebook_status} (uid, pid, status, status_time)
    SELECT
        recipient AS uid,
        author AS pid,
        message AS status,
        created AS status_time
    FROM {guestbook}
    UNION
    SELECT
        author AS uid,
        commentauthor AS pid,
        comment AS status,
        created + 100 AS status_time
    FROM {guestbook};

Strange. Can you try UNION ALL instead of UNION? I think the NULLs could be SQL trying to combine rows.

INSERT INTO {facebook_status} (uid, pid, status, status_time)
    SELECT
        recipient AS uid,
        author AS pid,
        message AS status,
        created AS status_time
    FROM {guestbook}
    UNION ALL
    SELECT
        author AS uid,
        commentauthor AS pid,
        comment AS status,
        created + 100 AS status_time
    FROM {guestbook};

That might do it, although the NULL values are probably a result of the UNION rather than being copied out of {guestbook}.

INSERT INTO {facebook_status} (uid, pid, status, status_time)
    SELECT
        recipient AS uid,
        author AS pid,
        message AS status,
        created AS status_time
    FROM {guestbook}
    WHERE status IS NOT NULL
    UNION ALL
    SELECT
        author AS uid,
        commentauthor AS pid,
        comment AS status,
        created + 100 AS status_time
    FROM {guestbook}
    WHERE status IS NOT NULL;

Having NULL values is not a huge problem, because we could always just run a second query to delete them. However, my concern is that the NULL values are an indication that not all of the data got copied. If you can confirm that you have the right amount of records after the NULL records are deleted, that would be helpful.

Hmm. It could be done with a temporary table, or without UNION it could be done in 4 queries (CREATE a temporary table, INSERT guestbook entries and then comments, DROP the temporary table). Speed is a factor here though, because this process could easily time out on very large sites. Do you get the same mismatch of row counts using just UNION instead of UNION ALL?

Good to know. I didn't think about the fact that Guestbook supports anonymous posts. FBSS doesn't because the concepts don't apply cleanly to anonymous users since they're all represented by a single user account. I don't really have a problem with throwing those away, but that means both parts of the query should check for author <> 0.

INSERT INTO facebook_status (uid, pid, status, status_time)  
  SELECT
        recipient AS uid,
        author AS pid,
        message AS status,
        created AS status_time
    FROM guestbook
    WHERE author <> 0
    UNION ALL
    SELECT
        author AS uid,
        commentauthor AS pid,
        comment AS status,
        created + 100 AS status_time
    FROM guestbook
    WHERE commentauthor <> 0;

I guess this means I can start working on actually including this feature then.

The attached module should perform the conversion. It consists of just a .info and .install file and depends on both FBSS and Guestbook. When enabled, it performs the conversion and then disables itself.

Testing is, of course, much appreciated.

Whoops, looks like a .module file is required for Drupal to make the module appear at admin/build/modules.

Alright, final version. Linking to this in the documentation instead of including it in the module per Michelle's suggestion in IRC.

Wow the dynamic duo strike again !! thanks GOD, GOD they sent u both...waiting for the next break thru....

Hi

Could you please add (optional) support for Facebook like statuses comments module?
than all replies on guestbook messages could convert as comments...

thanks
Igor

IMHO all replies on guestbook messages should be converted as status messages to other users rather than status comments, which is something I've been meaning to do for awhile. It doesn't make a lot of sense to use FBSSC right now, because it doesn't work at all in most browsers.

Er, actually, #23 is what the module already does.

Re: #22, again, FBSSC doesn't actually work at the moment, so there's no point importing anything into it.

What is the best pathway for converting from Guestbook to FBSS? I see IceCreamYou mentions that FBSSC does not work at the moment. Does that mean we can't convert from Guestbook for now? What is last C stands for in FBSSC - comments or conversion? Confusing abbreviations...

Use the module attached in #19 to convert from Guestbook to FBSS. As you can see on the FBSSC project page, the "C" stands for "comments." When I said FBSSC didn't work, that was nearly 8 months ago -- it does work now, although you should use the dev version.

The module attached to #19 doesn't seem to work anymore. FBSS itself has changed meanwhile and its table column names changed as well. I've updated the module and it worked for me. I'm uploading it hoping that it might save time to someone else.
Note: the module doesn't disable itself anymore. It must be disabled manually. Otherwise, it works as previously described.