迁移 valine 评论数据至 wordpress 数据库

1803°C 17-11-2022 notbyai
最近更新于:2023-03-01 15:09:00

文章摘要moonshot-v1-8k

Standby API Responsing..

前言

老早就计划着这件事了,知道长期使用第三方提供的数据服务可能那天就会挂掉的几率(比如之前的leancloud限制云引擎及域名回收事件),放在第三方或多或少会影响到部分使用其免费服务的群体,但毕竟人家是免费提供到的服务,我们白嫖的就不要在这里说三道四了,只是就事论事来讲数据还是存放在自己的手中最安全可靠。

数据迁移

一直以来leancloud都提供了数据导出服务,格式为 json line(jsonl),这里其实很方便了,因为市面上大多数评论系统都可以json格式的数据进行导入迁移数据,也就是说我们只需要把导出的 jsonl 格式转为 json 即可兼容大部分评论系统。此前为有考虑过 twikoo 评论,因其部署在 vercel 也比较放心,但无奈导入评论时报错没找到原因,故搁置。

此前开源主题时已经做了 wordpress 评论兼容,只是没有把魔改valine上的ui及功能移植过去,现在也就打算将存放于 leancloud 的 valine 评论数据导入到 wordpress 数据库内,一个为了做备份(几千条数据万一丢了就不好了,虽然经常有在导出备份),另一个就是想后期把 wordpress 评论给改一下,以后如果 leancloud 不再提供服务时就用 wp 评论平替,算是一个备选方案。

这里在做个备份及导入规则等操作记录,方便以后查阅。

数据更新(前提)

在导入 mysql 前,如果有些 valine 生成(或者我们自定义的)的数据项我们不想要(如 ACL/isNotified 等等),这时候我们需要将 jsonl 格式转为 json 格式( convertjson.com可在线转换)。

使用本地编辑器打开 .jsonl 文件删掉第一行的 filetype:JSON-streaming {"type":"Class","class":"Comment"}ctrl+f 查找 }+换行符ctrl+enter\n)替换为 },+换行符,最后使用 [] 将所有对象包裹即可。

利用正则表达式将评论数据中那些不需要的数据一一项剔除掉,下方存档参考用。注意:必须将 ACLinsertedAt 两个数据删掉,否则可能影响后续数据转换操作!tips:貌似 leancloud 导出数据时无法自定义列及删除上述两列

","ACL".*?\}\},"       //访问控制对象
","insertedAt".*?\},"  //插入时间对象
","  //以上替换

"updatedAt".*?,"       //更新时间对象
","  //替换
","isNotified".*?\}    //已发件提醒
"}   //替换

//自定义对象
,"md5mail".*?,"
,"mailMd5".*?,"
,"ip".*?,"
,"ads".*?,"
,"  //以上替换
,"topset":.*?\},
},  //替换
"isEdited".*?,"
"   //替换

<p><a class=\\"at\\".*?/a> , // 替换评论中所有 @人员 信息为空(wp函数加载评论时自动添加@人员)

数据转换(参考)

可无视在线数据转换操作,所有 json to sql 均可在 phpmyadmin 及参考下方数据关联的 navicat 软件中进行数据转换操作

众所周知 wordpress 使用的是 mysql 数据库,那么json是不能直接用的,所以需要再到上述网站将 json 转换为 sql 格式,最后在 phpmyadmin 中导入 sql 数据到 wordpress 数据库。注意:此处数据转换需上外网访问,否则第二步(Step 2: Choose output options)时无法加载出数据导出选项!

此处可无视 key 值选项,后面需要自定义 comment_ID 为主键

完成 json 到 sql 到转换后,将转换后的 sql 文件下载到本地,根据 wp 数据库中自带的 wp_comments 数据表结构进行进一步编辑,以下是我的编辑参考(务必提前将 wp_comments 数据表结构更改为上面转换好的结构)可以先拟个表(valine2wp)导入测试正常上传后,再做其他操作。注意:此处需手动新增设置 comment_ID 字段为递增及主键,否则可能影响后续操作。

DROP TABLE IF EXISTS valine2wp;
  • CREATE TABLE IF NOT EXISTS valine2wp(
  • comment_ID BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
  • ,comment_author VARCHAR(11) NOT NULL
  • ,updatedAt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
  • ,objectId VARCHAR(24) NOT NULL
  • ,comment_author_email VARCHAR(30)
  • ,comment_agent VARCHAR(418) NOT NULL
  • ,comment_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
  • ,md5mail VARCHAR(32)
  • ,comment_author_url VARCHAR(57)
  • ,comment_content VARCHAR(85226) NOT NULL
  • ,url VARCHAR(50)
  • ,comment_post_ID BIGINT(20) DEFAULT 0
  • ,rid VARCHAR(24)
  • ,comment_karma INT(11) NOT NULL DEFAULT 0
  • ,comment_approved VARCHAR(20) NOT NULL DEFAULT 1
  • ,comment_type VARCHAR(20) NOT NULL DEFAULT 'comment'
  • ,comment_parent BIGINT(20) NOT NULL DEFAULT 0
  • ,user_id BIGINT(20) NOT NULL DEFAULT 0 );
  • 当一切正常导入并正常运行后,此时我们需要进行下一步操作评论数据之间的关系(在 Step 3: Generate output 时如果是测试表可以选第一个 JSON To SQL Insert,如果是更新 wp_comments 表可以选第二个 JSON To SQL Update需要注意的是如果存在原生 wp_comments 表内没有的字段,则需在 wp_comments 表内新建或在 Step 3 生成 sql 时前取消勾选该字段 Include 选项)。

    数据关联(主要)

    在关联数据时,我们需要解决以下2个问题:

    1. valine/wordpress 是如何关联评论数据到对应页面的?
    2. valine/wordpress 是如何关联子评论数据到父评论的?

    首先, valine 中通过查看源码或 leancloud 数据结构可以看到评论数据是通过页面 url 来进行关联的,而 wordpress 在其评论数据表中是通过 comment_post_ID 字段来分别对应到各文章 id 的。其次,valine 是通过每条评论中的 objectidrid(reply objectId 主楼) pid(parent objectId 楼中楼) 字段的对应关系来进行父/子评论关联,而 wordpress 则直接在评论数据表中通过 comment_parent(默认 0)字段来关联其对应其评论的 comment_ID 字段。

    了解完双方评论的基本原理后,就可以上手操作关联数据了。

    关联文章页面

    首先我们需要利用 valine 中提供的 url 字段来分别匹配 wordpress 数据库中对应的文章 slug 别名的文章id(也就是把 url 转换为其文章对应的 comment_post_ID),这里我是写了个简单的 php 文件来读取 json 文件并遍历获取每个对象的对应文章 id 后再写入 json 进行新增的(很傻,而且速度不快),其实这里可以直接在 phpmyadmin 中使用 mysql 语句进行批量替换的,就不用这么麻烦了!虽然是个笨办法,不过也还能用,简单做个记录(在wp根目录创建以下php文件,再将之前转换后的 json 数据丢到根目录访问即可。一点需要注意的是需要修改 $url 变量规则为你自己的 url 别名匹配规则)。

    <?php
        define('WP_USE_THEMES', false);  // No need for the template engine
        require_once( 'wp-load.php' );  // Load WordPress Core
    
        // 通过文章别名模糊匹配文章id
        function get_post_like_slug($post_slug) {
            global $wpdb;
            $post_slug = '%' . $post_slug . '%';
            $pid = $wpdb->get_var($wpdb->prepare("SELECT ID FROM $wpdb->posts WHERE post_name LIKE %s", $post_slug));
            return get_post($pid);
        };
        global $wpdb;  //使用 wpdb 查询所有文章id
        $wp2valine = $wpdb->get_results("SELECT url FROM wp2valine WHERE 1 ORDER BY comment_ID ASC"); 
     
        //读取同目录 json 文件
        $filename = "jsonl2json.json";
        $handle = fopen($filename, "r");  //读取二进制文件时,需要将第二个参数设置成'rb'
        $contents = fread($handle, filesize ($filename));  //通过filesize获得文件大小,将整个文件一下子读到一个字符串中
        fclose($handle);
    
        // 写入文章 id 到每个 json 对象
        $decode = json_decode($contents);  //解码 json
        foreach ($wp2valine as $index => $res){
            $urs = $res->url;
            $url = substr($urs, strpos($urs, '_')+1, strlen($urs));  //注意⚠️修改此规则为你文章 url 别名规则
            $uid = get_post_like_slug($url)->ID;
            array_push($comment_post_ID, $uid);
            $decode[$index]->comment_post_ID = $uid;
        };
    
        // 输出新的 json 文件
        ob_start();
        print_r(json_encode($decode,JSON_UNESCAPED_UNICODE));  //使用 JSON_UNESCAPED_UNICODE 参数来正确输出 emoji 表情
        $content = ob_get_contents();
        ob_end_clean();
        file_put_contents(ABSPATH . '/output.json', $content);
    ?>

    完成上述操作后,应该能在wp根目录看到生成的 output.json 文件,打开会发现每条 json 对象已经新增对应的 comment_post_ID 文章id对象;再之后将 output.json 在 convertjson.com/json-to-sql 转换为 sql 数据库文件后重新导入到 wordpress 数据库查看运行测试。

    ⚠️注意!确保非 null 字段不存在 null 值!

    关联父级评论(注意项)

    完成评论数据文章关联后就剩最后一步了,关联子评论到父级评论,利用上面提到的原理操作即可,这次我选择直接使用 mysql 进行数据库更新,这样就告别了繁琐的读写删改 json 及转换步骤了。下面是实现评论关联的 mysql 条件语句及示意图(部分选中数据仅作观察对比)。

    原理很简单,

    通过对比评论数据表中的 objectidpid 字段,如果相同则表示其为父子关系(提取符合的所有数据行),然后提取父级(objectid)的 comment_ID 字段将其写入到 pid 数据行(子评论)中的 comment_parent 字段即可

    此图已不再适用,需替换 rid 为 pid 来关联 objectId 评论父级
    将符合 2617 评论的所有 rid 行设置其 comment_parent 为父评论的 comment_ID 2617

    ⚠️注意⚠️不可直接上传已转换 json 的 sql 文件到服务器上的 wp_comments 数据表执行 comment_parent 数据关联,这样会导致 wordpress 评论数据表发生改变!会影响 comment_parent 字段写入成功后续 wordpress 无法正常读取!我们需要在本地完成所有 comment_parent 字段sql 数据关联操作,可参考以下方案。

    操作环境: windows mysql + navicat for mysql(留意当前需处理的 sql 文件数据结构必须与 wordpress 数据库中导出 wp_comments 数据表保持一致!)建议提前在原数据库内重新设计表,新增 objectIdpiduid 等字段再导出,参考 sql 语句

    ALTER TABLE `wp_comments` ADD `objectId` VARCHAR ( 24 ) CHARACTER 
    SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL AFTER `user_id`,
    ADD `pid` VARCHAR ( 24 ) CHARACTER 
    SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL AFTER `objectId`,
    ADD `rid` VARCHAR ( 24 ) CHARACTER 
    SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL AFTER `pid`,
    ADD `url` VARCHAR ( 255 ) CHARACTER 
    SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL AFTER `rid`;
    <?php
        //... UTC时间格式化参考
        foreach ($decode as $index => $res){
            $utc_date = $decode[$index]->comment_date;
            $utc2date = date('Y-m-d H:i:s',strtotime($utc_date));
            $decode[$index]->comment_date = $utc2date ? $utc2date : '0000-00-00 00:00:00';
        }
        //...
    ?>
    1. 首先将 wordpress 数据库中的 wp_comments 数据表新增字段后导出 sql 文件。
    2. 然后在 navicat mysql 编辑器中右键运行 sql 文件导入 wp_comments.sql 文件(需要将原有数据删除,在设计表选项卡中将自动递增设置为1,之后将已关联 commetn_post_ID 字段的 json 文件导入到刚加载的 sql 文件中(⚠️注意 json 中的 comment_date 日期字段如果是 utc 格式需要使用 php 函数 date(‘Y-m-d H:i:s’,strtotime($utc_date)) 转换为普通日期格式 Y-m-d H:i:s 后再导入到 sql 文件,参考上方UTC时间格式化)⚠️注意:若导入时候数据映射步骤显示不全,则表示 json 对象中的首行中未包含缺失的数据 key,导入数据首行必须包含所有所需字段(包括””空值),否则导入后将缺失该字段 value 值)
    3. 执行下方 sql 语句通过对比 pidobjectId 值将 comment_parent_ID 关联至 comment_ID(2k+数据执行时长大概在 5s)
    4. 导入完成后将处理好的数据表右键转储为 sql 文件(包含数据和结构)导出为 sql 后再导入到 wordpress 数据库即可覆盖 wp_comments 数据表即可。
    # 注意执行 SELECT ... FOR UPDATE 时无法模拟执行,请提前备份好数据再操作!
    SELECT
    	objectId.comment_parent,
    	objectId.pid,
    	pid.comment_ID,
    	pid.objectId 
    FROM
    	`wp_comments` objectId,
    	`wp_comments` pid 
    WHERE
    	objectId.pid = pid.objectId FOR UPDATE;  #保留分号否则报错
    UPDATE `wp_comments` objectId,
           `wp_comments` pid 
    SET objectId.comment_parent = pid.comment_ID 
    WHERE
    	objectId.pid = pid.objectId
    
    #可选执行(更新评论GMT时间)
    UPDATE `wp_comments` SET comment_date_gmt = DATE_SUB(comment_date,INTERVAL 8 HOUR) WHERE 1  #计算GMT时差(DATE_SUB()和DATE_ADD()函数)

    一开始我查了很久,因为不知道单表多字段查询如何通过 update 直接修改,所以选择使用 select for update 进行修改,测试可用,速度也很快。需要注意的就是 for update 后 update 的时候也需要带上 WHERE 条件(同 select 条件),否则修改后 comment_parent 全部变成 1..

    关联评论数量

    完成上述操作后导入 wordpress 的 mysql 数据库应该就能在对应文章/页面中加载出对应评论了,下面是统计文章评论数量,需要先把 wp_posts 表导出至本地(注意备份)。在 valine 中可直接通过 xhr 请求的 json 对象返回长度判断评论数量,而 wordpress 中则是储存在 wp_posts 数据表中的 publish(已发布) 页面/文章下的 comment_count 字段中,这里我们通过交叉查询(cross join)来实现 mysql 中两表数据的查询及更新操作

    使用 GROUP BY 将查询到符合 wp_posts/wp_comments 中符合条件的 COUNT(*) 数量 CROSS JOIN 交叉合集为 res 表,然后对比 res 表 ID 及 wp_posts 表 ID,最后将 res 表中的 count 写入 wp_posts 中的 comment_count

    UPDATE `wp_posts` t1,
           `wp_comments` t2
    CROSS JOIN (
    	SELECT
    		ID,
    		COUNT(*) AS cnt 
    	FROM
    		`wp_posts` t1,
    		`wp_comments` t2 
    	WHERE
    		t1.post_status = "publish" 
    		AND t2.comment_post_ID = t1.ID 
    	GROUP BY
    		t2.comment_post_ID 
    	) AS res 
    SET t1.comment_count = res.cnt 
    WHERE
    	t1.ID = res.ID 
    	AND t1.post_status = "publish";
    #!在 WHERE 条件中需要对比目标 table id 及查询结果 table id,否则执行更新后都是同一个数值
    #需要在交叉查询时返回查询结果 id 用作 update 更新时的条件

    小结

    这次数据迁移持续了几天,总的来说还是比较满意,至少成功把数据对接好了嘛~还了解了几个 mysql 语法顺便也拓展了下 php 处理 json 数据之间的方案。

    1122更新,近几天来来回回修订了好几个版本,现在算是确定了。一开始的 phpmyadmin 转 sql to json 再处理 json 转 sql 再到在线网站设计 sql 数据表后导入 wordpress,到现在直接使用 navicat 编辑、设计、导入转出全程本地化处理(总感觉太依赖线上服务不太好,万一以后要再做迁移不能用就麻烦了),处理数据的逻辑更清晰了,顺便只保留了几个与 valine 关联的字段方便以后使用。一个建议的话就是导入数据能在本地处理的就不要到线上使用 sql 处理,否则可能引发一系列不可预料的问题。毕竟数据提供好了,才方便数据库处理。

    1129更新,处理完评论数量数据关联总算是告一段落了..

    参考文档

    查询一张表中2个字段对应的关系

    通过(选择的结果)mysql更新行


    评论留言

    既来之则留之~ 欢迎在下方留言评论,提交评论后还可以撤销或重新编辑。(Valine 会自动保存您的评论信息到浏览器)