--
-- Migrate photos from Photopress to the standard Wordpress 2.5.1 gallery.
--
-- WARNING!! This is a complete hack. The database structure was reverse-
-- engineered (purely out of laziness). Running this script may break your
-- Wordpress installation and cause data loss and through it insanity. So BACK
-- UP YOUR DATABASE, and preferably your whole Wordpress installation, before
-- running this. See:
-- http://codex.wordpress.org/WordPress_Backups
--
-- Copyright 2008 Alex Fraser <alex@phatcore.com>
--
--    This program is free software: you can redistribute it and/or modify
--    it under the terms of the GNU General Public License as published by
--    the Free Software Foundation, either version 3 of the License, or
--    (at your option) any later version.
--
--    This program is distributed in the hope that it will be useful,
--    but WITHOUT ANY WARRANTY; without even the implied warranty of
--    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--    GNU General Public License for more details.
--
--    You should have received a copy of the GNU General Public License
--    along with this program.  If not, see <http://www.gnu.org/licenses/>.
--
-- Known issues:
-- 1. Images with commas in their names will break the processing for posts that
--    they appear in.
-- 2. You must have the CREATE FUNCTION privilege to run this script. If you
--    don't, you could install MySQL on your own computer to run it locally.
--    This is much more complicated, though.
-- 3. The default MySQL engine doesn't support transactions, so neither does
--    this script. They could be added if need be. Anyway, if it fails at any
--    point the only way to recover is to restore the backup you made of the
--    database before running it.
-- 4. Images have new posts created for them tagged as attachments. However,
--    they don't display properly because they lack metadata. See the
--    wp_postmeta table for an idea of what needs to be done.
--
-- Preparing to run this script:
-- 1. Take your blog offline.
-- 2. BACKUP your data base. The whole thing; not just the Wordpress tables.
-- 3. Change the option @g_tblPrefix below to match your installation.
--
-- When you're ready to run this, you can EITHER:
-- 1. Type at a mysql prompt `source migrate_pp.sql`, or
-- 2. Run the script through phpMyAdmin or another database management tool.
--

--
-- Options
-- @g_tblPrefix:    The table prefix of this Wordpress installation.
--                  Usually 'wp_'.
--

SET @g_tblPrefix := 'wp_';

--
-- END Options. You shouldn't need to edit any further.
--

--
-- Derived global variables.
-- @g_optVal:   All Photopress options. Used by GetPpOption.
-- @g_baseUrl:  The main blog URL. Used by ConvertPpStaticLinks.
-- pp_migrate_photopress:   A duplication of the wp_photopress table. This is
--              required because ConvertPpDynamicLinks needs to select data from
--              the table, but can't choose the table name dynamically.
--
SET @s := CONCAT(
'SELECT opt.option_value INTO @g_optVal
    FROM ', @g_tblPrefix,'options opt
    WHERE opt.option_name = "pp_options"
        LIMIT 1'
);
PREPARE tmp_stmt FROM @s;
EXECUTE tmp_stmt;
DEALLOCATE PREPARE tmp_stmt;

SET @s := CONCAT(
'SELECT opt.option_value INTO @g_baseUrl
    FROM ', @g_tblPrefix,'options opt
    WHERE opt.option_name = "siteurl"
        LIMIT 1'
);
PREPARE tmp_stmt FROM @s;
EXECUTE tmp_stmt;
DEALLOCATE PREPARE tmp_stmt;

SET @s := CONCAT(
'CREATE TEMPORARY TABLE pp_migrate_photopress
    SELECT imgname, imgfile
    FROM ', @g_tblPrefix,'photopress'
);
PREPARE duplicate_wp_photopress FROM @s;
EXECUTE duplicate_wp_photopress;
DEALLOCATE PREPARE duplicate_wp_photopress;

--
-- GetPpOption()
-- Extract a value from the photopress options. This is used when adding the
-- photos as new posts.
--
-- Everything is stored in a single string delimited by colons. Oh! if only
-- there had been some way to store these options in an inherently relational
-- way!
--
-- [...] "photosaddress";s:42:"http://host/install/wp-content/dir";s:14:"use_permalinks";s:1 [...]
--          ^^name^^       ^^length     ^^value^^
--
-- int @nameLen     -- length of the option name
-- int @nameStart   -- location of the first character of the option name
-- int @lenStart    -- location of first length character
-- int @lenEnd      -- location of last length character
-- int @valLen      -- the length of the option value (see above)
-- int @valStart    -- location of first character of the option value
--
delimiter //
CREATE FUNCTION GetPpOption(pp_optName VARCHAR(32)) RETURNS varchar(256)
DETERMINISTIC       -- uses global variables which may change
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
    DO @nameLen     := CHAR_LENGTH(CONCAT('"', pp_optName, '";s'));
    DO @nameStart   := LOCATE(CONCAT('"', pp_optName, '";s'), @g_optVal);
    DO @lenStart    := LOCATE(':', @g_optVal, @nameStart + @nameLen) + 1;
    DO @lenEnd      := LOCATE(':', @g_optVal, @lenStart);
    DO @valLen      := CAST(SUBSTRING(@g_optVal, @lenStart, @lenEnd - @lenStart) AS UNSIGNED);
    DO @valStart    := LOCATE('"', @g_optVal, @lenEnd) + 1;
    RETURN SUBSTRING(@g_optVal, @valStart, @valLen);
END;
//
delimiter ;

--
-- Get the full URL of an image from the photopress options.
--
delimiter //
CREATE FUNCTION GetImgUrl(fileName VARCHAR(64)) RETURNS varchar(256)
BEGIN
    RETURN CONCAT(GetPpOption('photosaddress'), '/', fileName);
END;
//
delimiter ;

--
-- Get the URL of the thumbnail of an image from the photopress options.
--
delimiter //
CREATE FUNCTION GetThumbUrl(fileName VARCHAR(64)) RETURNS varchar(256)
BEGIN
    RETURN CONCAT(
        GetPpOption('photosaddress'),
        '/',
        GetPpOption('thumbprefix'),
        fileName
    );
END;
//
delimiter ;

--
-- Guess the MIME type of the image based on the file extension.
--
delimiter //
CREATE FUNCTION GetImgMimeType(fileName VARCHAR(64)) RETURNS varchar(64)
BEGIN
    CASE LOWER(SUBSTRING_INDEX(fileName, '.', -1))
        WHEN 'gif'  THEN RETURN 'image/gif';
        WHEN 'jpeg' THEN RETURN 'image/jpeg';
        WHEN 'jpg'  THEN RETURN 'image/jpeg';
        WHEN 'jpe'  THEN RETURN 'image/jpeg';
        WHEN 'pcx'  THEN RETURN 'image/pcx';
        WHEN 'png'  THEN RETURN 'image/png';
        WHEN 'svg'  THEN RETURN 'image/svg+xml';
        WHEN 'svgz' THEN RETURN 'image/svg+xml';
        WHEN 'tiff' THEN RETURN 'image/tiff';
        WHEN 'tif'  THEN RETURN 'image/tiff';
        ELSE RETURN 'application/octet-stream';
    END CASE;
END;
//
delimiter ;

--
-- Convert a string to something suited to the wp_posts.post_name field.
-- All lower-case; no whitespace.
--
delimiter //
CREATE FUNCTION FlattenPostName(name VARCHAR(64)) RETURNS varchar(200)
BEGIN
    RETURN 
        LOWER(CONCAT(
            'photopress-',
            REPLACE(REPLACE(REPLACE(name, '\t', '-'), '\n', '-'), ' ', '-')
        ));
END;
//
delimiter ;

--
-- Convert the Photopress `hidden' field to a Wordpress `post_status' field.
--
delimiter //
CREATE FUNCTION GetPostStatus(hidden VARCHAR(55)) RETURNS varchar(20)
BEGIN
    IF hidden LIKE '%hide%' THEN
        RETURN 'draft';
    ELSE
        -- hidden = display
        RETURN 'publish';
    END IF;
END;
//
delimiter ;

--
-- Create categories (parent posts) to group images.
-- This transfers each row from the wp_pp_cats to a corresponding row in
-- wp_posts such that the new post has the same title as the category. The post
-- is actually a page in which a gallery is embedded. Images that were members
-- of the category will be made children of the page later.
--
SET @s := CONCAT(
'INSERT INTO ', @g_tblPrefix,'posts (
        -- id
        -- post_author,
        -- post_date,
        -- post_date_gmt,
        post_content,
        post_title,
        -- post_category,
        post_excerpt,
        post_status,
        comment_status,
        ping_status,
        -- post_password,
        post_name,
        to_ping,
        pinged,
        post_modified,
        post_modified_gmt,
        post_content_filtered,
        -- post_parent,
        -- guid,
        -- menu_order,
        post_type
        -- post_mime_type,
        -- comment_count
    )
    SELECT
        -- post_content. No gallery ID because the images will be children of
        -- this post/page.
        "<p>[gallery]</p>",
        -- post_title
        CONCAT("[Photopress] ", cat.category),
        -- post_excerpt
        "",
        -- post_status
        GetPostStatus(cat.hidden),
        -- comment_status
        "open",
        -- ping_status
        "open",
        -- post_name (no whitespace)
        FlattenPostName(cat.catslug),
        -- to_ping
        "",
        -- pinged
        "",
        -- post_modified
        LOCALTIMESTAMP(),
        -- post_modified_gmt
        UTC_TIMESTAMP(),
        -- post_content_filtered,
        "",
        -- guid -- this is set next when we know the post ID.
        -- post_type
        "page"

        FROM ', @g_tblPrefix,'photopress pp, ', @g_tblPrefix,'pp_cats cat
        -- Assoicate photo with category. This discards empty groups.
        WHERE pp.catslug = cat.catslug
        -- Only match each category once (not once per photo)
        GROUP BY pp.catslug'
);
PREPARE createCategoryPosts FROM @s;

--
-- Set the GUID for our new posts. This is the permalink to the page (URL + ID).
-- This can't be done earlier as we need the ID now.
-- The new posts are identified as having a title beginning with '[Photopress]'.
--
SET @s := CONCAT(
'UPDATE ', @g_tblPrefix,'posts pst, ', @g_tblPrefix,'options op, ', @g_tblPrefix, 'pp_cats cat
    SET pst.guid = CONCAT(op.option_value, CONCAT("?p=", pst.id))
    WHERE op.option_name = "siteurl"
        AND CONCAT("[Photopress] ", cat.category) = pst.post_title'
);
PREPARE updateCategoryGuids FROM @s;

--
-- Create unique posts for images.
--
SET @s := CONCAT(
'INSERT INTO ', @g_tblPrefix,'posts(
        -- id
        -- post_author,
        -- post_date,
        -- post_date_gmt,
        post_content,
        post_title,
        -- post_category,
        post_excerpt,
        post_status,
        comment_status,
        ping_status,
        -- post_password,
        post_name,
        to_ping,
        pinged,
        post_modified,
        post_modified_gmt,
        post_content_filtered,
        post_parent,
        guid,
        -- menu_order,
        post_type,
        post_mime_type
        -- comment_count
    )
    SELECT
        -- post_content.
        pp.imgdesc,
        -- post_title (displayed as IMG alt text)
        pp.imgfile,
        -- post_excerpt (displayed as the title)
        pp.imgname,
        -- post_status
        "inherit",
        -- comment_status
        "open",
        -- ping_status
        "open",
        -- post_name (no whitespace)
        FlattenPostName(cat.catslug),
        -- to_ping
        "",
        -- pinged
        "",
        -- post_modified
        LOCALTIMESTAMP(),
        -- post_modified_gmt
        UTC_TIMESTAMP(),
        -- post_content_filtered
        "",
        -- post_parent (sets group membership)
        pst.id,
        -- guid
        GetImgUrl(pp.imgfile),
        -- post_type
        "attachment",
        -- post_mime_type
        GetImgMimeType(pp.imgfile)

        FROM ', @g_tblPrefix,'posts pst, ', @g_tblPrefix,'photopress pp, ',
            @g_tblPrefix,'pp_cats cat, ', @g_tblPrefix,'options opt
        -- Associate photo with category
        WHERE pp.catslug = cat.catslug
            -- Associate post parent to category
            AND CONCAT("[Photopress] ", cat.category) = pst.post_title
            -- Options (used by GetPpOption for guid field)
            AND opt.option_name = "pp_options"'
);
PREPARE createImagePosts FROM @s;

--
-- Converts Photopress image query links to plain links for one post. For
-- example:
--  <a href="http://site/blog/?pp_album=foo&pp_cat=bar&pp_image=IMG_1.jpg">...</a> ->
--  <a href="http://site/blog/wp-content/photos/IMG_1.jpg">...</a>
-- The actual image location will be derived from Photopress options.
--
delimiter //
CREATE FUNCTION ConvertPpStaticLinks(post_content longtext) RETURNS longtext
BEGIN
    --
    -- Loop through each URL that belongs to this site. If it looks like a
    -- Photopress image, convert it. The conversion never changes the starting
    -- point of the URL, so searching for the next is easy: just add one and go
    -- from there.
    --
    -- We look for the delimiter explicitly: it might be a quote or double
    -- quote.
    --
    -- Pull the URL out as a new string - further processing will be faster.
    -- Find the start of the image option (pp_image), and then look for the end.
    -- If there are any other options it'll end with an ampersand; otherwise it
    -- will run to the end of the string.
    --
    DO @urlStart := 0; -- strings count from 1, not 0. Increments immediately:
    repetition: LOOP
    DO @urlStart := LOCATE(@g_baseUrl, post_content, @urlStart + 1);
        IF @urlStart <= 0 THEN LEAVE repetition; END IF;

        DO @delimiter := SUBSTRING(post_content, @urlStart - 1, 1);
        IF @delimiter != '"' AND @delimiter != '\'' THEN LEAVE repetition; END IF;

        DO @urlEnd := LOCATE(@delimiter, post_content, @urlStart);
        IF @urlEnd <= 0 THEN LEAVE repetition; END IF;

        DO @url := SUBSTRING(post_content, @urlStart, @urlEnd - @urlStart);
        DO @imgStart := LOCATE('pp_image=', @url);
        IF @imgStart <= 0 THEN LEAVE repetition; END IF;
        DO @imgStart    := @imgStart + CHAR_LENGTH('pp_image=');
        DO @imgEnd      := LOCATE('&', @url, @imgStart);
        IF @imgEnd > 0 THEN
            DO @img := SUBSTRING(@url, @imgStart, @imgEnd - @imgStart);
        ELSE
            DO @img := SUBSTRING(@url, @imgStart);
        END IF;
        IF @img = '' THEN LEAVE repetition; END IF;

        DO @newUrl := GetImgUrl(@img);
        SET post_content = CONCAT(
            LEFT(post_content, @urlStart - 1),
            @newUrl,
            SUBSTRING(post_content, @urlEnd)
        );
    END LOOP repetition;

    RETURN post_content;
END;
//
delimiter ;

--
-- Resolves dynamic Photopress tags into static HTML for one post. For example:
--  [photopress:IMG_1.jpg,thumb,pp_image] ->
--  <a href="http://site/blog/wp-content/photos/IMG_1.jpg">
--      <img src="http://site/blog/wp-content/photos/thumb_IMG_1.jpg" />
--  </a>
-- The actual image location will be derived from Photopress options. Extra
-- info, like the HTML `title' attribute, will be taken from the database too.
--
-- This function assumes there's no comma in the file name as it's used as a
-- delimiter. Commas are rare.
--
delimiter //
CREATE FUNCTION ConvertPpDynamicLinks(post_content longtext) RETURNS longtext
BEGIN
    WHILE post_content LIKE '%[photopress:%' DO
        --
        -- Parse next Photopress tag.
        --
        DO @tagStart    := LOCATE('[photopress:', post_content);
        DO @imgStart    := @tagStart + CHAR_LENGTH('[photopress:');
        DO @imgEnd      := LOCATE(',', post_content, @imgStart);
        DO @dispStart   := @imgEnd + 1;
        DO @dispEnd     := LOCATE(',', post_content, @dispStart);
        DO @classStart  := @dispEnd + 1;
        DO @classEnd    := LOCATE(']', post_content, @classStart);
        DO @tagEnd      := @classEnd + 1;

        DO @tag         := SUBSTRING(post_content, @tagStart, @tagEnd - @tagStart);
        DO @img         := SUBSTRING(post_content, @imgStart, @imgEnd - @imgStart);
        DO @disp        := SUBSTRING(post_content, @dispStart, @dispEnd - @dispStart);
        DO @class       := SUBSTRING(post_content, @classStart, @classEnd - @classStart);

        SELECT imgname INTO @imgTitle FROM pp_migrate_photopress WHERE imgfile = @img LIMIT 1;

        --
        -- Construct (bake) HTML to display image.
        --
        DO @imgHtml := '';
        IF @disp = 'thumb' THEN
            DO @imgHtml := CONCAT(@imgHtml, '<a href="', GetImgUrl(@img),'" title="', @imgTitle, '">');
            DO @imgHtml := CONCAT(@imgHtml, '<img src="', GetThumbUrl(@img),'" alt="', @imgTitle, '" class="', @class, '" />');
            DO @imgHtml := CONCAT(@imgHtml, '</a>');
        ELSE
            DO @imgHtml := CONCAT(@imgHtml, '<img src="', GetImgUrl(@img), '" alt="', @imgTitle, '" class="', @class, '" />');
        END IF;

        --
        -- Replace Photopress tag with baked HTML.
        --
        SET post_content = REPLACE(post_content, @tag, @imgHtml);
    END WHILE;
    RETURN post_content;
END;
//
delimiter ;

--
-- Convert old posts to point to images in the new fashion.
--
delimiter //
CREATE FUNCTION ReplacePpLinks(post_content longtext) RETURNS longtext
BEGIN
    SET post_content = ConvertPpStaticLinks(post_content);
    SET post_content = ConvertPpDynamicLinks(post_content);
    RETURN post_content;
END;
//
delimiter ;

--
-- Convert links within posts to use the new system. Thanks to stevenvu and tyr:
-- http://wordpress.org/support/topic/172415
--
SET @s := CONCAT(
'UPDATE ', @g_tblPrefix,'posts pst
    SET pst.post_content = ReplacePpLinks(pst.post_content)
    WHERE pst.post_content LIKE "%[photopress:%"
        OR pst.post_content LIKE "%pp_album=%"'
);
PREPARE convertPostLinks FROM @s;

SET @s := CONCAT('DROP TABLE ', @g_tblPrefix,'photopress');
PREPARE dropPhotopress FROM @s;
SET @s := CONCAT('DROP TABLE ', @g_tblPrefix,'pp_cats');
PREPARE dropPpCategories FROM @s;
SET @s := CONCAT(
'DELETE FROM ', @g_tblPrefix,'options
    WHERE option_name = "pp_options"'
);
PREPARE deletePpOptions FROM @s;

--
-- Execute it!
--
-- EXECUTE createCategoryPosts;
-- EXECUTE updateCategoryGuids;
-- EXECUTE createImagePosts;
EXECUTE convertPostLinks;
-- EXECUTE dropPhotopress;
-- EXECUTE dropPpCategories;
-- EXECUTE deletePpOptions;

--
-- Clean up functions, prepared statements, and temporary tables.
--
DEALLOCATE PREPARE createCategoryPosts;
DEALLOCATE PREPARE updateCategoryGuids;
DEALLOCATE PREPARE createImagePosts;
DEALLOCATE PREPARE convertPostLinks;
DEALLOCATE PREPARE dropPhotopress;
DEALLOCATE PREPARE dropPpCategories;
DEALLOCATE PREPARE deletePpOptions;
DROP FUNCTION ReplacePpLinks;
DROP FUNCTION ConvertPpDynamicLinks;
DROP FUNCTION ConvertPpStaticLinks;
DROP FUNCTION FlattenPostName;
DROP FUNCTION GetPostStatus;
DROP FUNCTION GetImgMimeType;
DROP FUNCTION GetThumbUrl;
DROP FUNCTION GetImgUrl;
DROP FUNCTION GetPpOption;
DROP TABLE pp_migrate_photopress;