s笔记:dzlite2smf

TC14年前 (2012-09-26)网站与博客761
 先在关卡空间的数据库里建立几个smf的空表,用于完事后从关卡空间导出再导入gmly.info:
(一定注意两个数据库的编码)
smf_messages_dz (结构同 smf_messages,下同,略)
smf_topics_dz
smf_members_dz
转换顺序:
1. 建立上述空表
2. 转换帖子
3. 转换主题
4. 更新板块
5. 转换用户
6. 补充用户数据(貌似没必要了)
7. 更新帖子,主题的id
8. 更新用户的id;
=================== 2 =============================
转换帖子  cdb_posts => smf_messages_dz
INSERT INTO smf_messages_dz
SELECT
    pid AS id_msg,tid AS id_topic, fid AS id_board, dateline AS poster_time, 
    authorid AS id_member, pid AS id_msg_modified, subject AS subject, 
    author AS poster_name, authorid AS poster_email, useip AS poster_ip, 
    1 AS smileys_enabled, 0 AS modified_time, NULL AS modified_name, 
    message AS body, "xx" AS icon, 0 AS hiddenOption, 0,1
FROM cdb_posts;
****  dzlite回复的帖子都没标题,改成 Re:帖子标题的形式
UPDATE `smf_messages_dz` SET `subject`="Re:" + ?? WHERE subject = ""(有错,还不会)
【Post.php 1209行 function Post2() ; 1266行 】
==================== 3 ============================
转换主题  cdb_threads => smf_topics_dz
INSERT INTO smf_topics_dz
SELECT   
    t.tid AS id_topic, 0 AS is_sticky, t.fid AS id_board, 
    MIN(p.pid) AS id_first_msg, MAX(p.pid) AS id_last_msg,
    t.authorid AS id_member_started, NULL id_member_updated,
    0 AS id_poll, 0 AS id_previous_board,
    0 AS id_previous_topic, t.replies AS num_replies, 
    t.views AS num_views,  0 AS locked, 
    0 AS unapproved_posts, 1 AS approved
FROM (cdb_threads AS t, cdb_posts AS p)
WHERE p.tid = t.tid
GROUP BY t.tid;
*******
1. dz里面的moderated是什么意思,
2. 没有更新最后回复人的id,不影响吧。
==================== 4 ==============================
更新板块
dz的板块(cdb_forums)不用导入smf,需要做的是,将所涉及板块fid的表(主题及帖子)里的fid都更新成smf里面对应的id_board。
**注意,如果是先转换主题及帖子,则应更新的是转换后的表。
fid  =>  id_board
3(自制关卡)    9
4(官方游戏)    60 (TC:"全是9代")
6(水)        21
7(站务)    1
UPDATE smf_topics_dz
SET id_board = 9
WHERE id_board = 3;
UPDATE smf_topics_dz
SET id_board = 60
WHERE id_board = 4;
UPDATE smf_topics_dz
SET id_board = 21
WHERE id_board = 6;
UPDATE smf_topics_dz
SET id_board = 1
WHERE id_board = 7;
UPDATE smf_messages_dz
SET id_board = 9
WHERE id_board = 3;
UPDATE smf_messages_dz
SET id_board = 60
WHERE id_board = 4;
UPDATE smf_messages_dz
SET id_board = 21
WHERE id_board = 6;
UPDATE smf_messages_dz
SET id_board = 1
WHERE id_board = 7;
==================== 5 ==============================
转换用户 cdb_members => smf_members_dz
INSERT INTO smf_members_dz
SELECT
    uid AS id_member, username AS member_name, 
    regdate AS date_registered, posts AS posts,
    0 AS id_group, NULL AS lngfile,  
    lastvisit AS last_login, username AS real_name,
    0 AS instant_messages, 0 AS unread_messages,
    NULL AS buddy_list, NULL AS pm_ignore_list, NULL AS message_labels,
    password AS passwd, email AS email_address,
    "gmly.info" AS personal_text, gender AS gender,
    "0001-01-01" AS birthdate, NULL AS website_title,
    NULL AS website_url, NULL AS location,
    NULL AS QQ, NULL AS icq, NULL AS aim, NULL AS yim, NULL AS msn,
    1 AS hide_email, 1 AS show_online, NULL AS time_format,
    NULL AS signature, 0 AS time_offset, NULL AS avatar,
    1 AS pm_email_notify, 0 AS karma_bad, 0 AS karma_good,
    NULL AS usertitle, 1 AS notify_announcements,
    1 AS notify_regularity, 0 AS notify_send_body, 2 AS notify_types,
    regip AS member_ip, NULL AS secret_question,
    NULL AS secret_answer, 0 AS id_theme, 1 AS is_activated,
    NULL AS validation_code, 0 AS id_msg_last_visit,
    NULL AS additional_groups, "Lots_O_Smileys" AS smiley_set,
    4 AS id_post_group, 0 AS total_time_logged_in,
    0 AS password_salt, NULL AS longitude, NULL AS latitude,
    0 AS g2_uid, lastip AS member_ip2, 0 AS is_gallery_user,
    NULL AS mod_prefs, 0 AS warning, NULL AS ignore_boards,
    0 AS new_pm, 0 AS pm_prefs,
    NULL AS openid_uri, NULL AS passwd_flood
FROM cdb_members;
******
1. smf的id_post_group是什么
2. smf的instant_messages是什么,personal_message么,数量暂时用0;短消息相关均不在上面的语句里;
3. member_ip用dz的注册ip,member_ip2用dz的最后登录ip;
4. 总在线时长为0
5. 签名档不在上面语句里,先用空值。
6. password_salt是什么。
======================= 6 ==========================
追加用户数据 (cdb_memberfields里的内容)
4人写了个人网站,12热写了QQ,4人写了msn,17人有签名;头像不知道怎么转换。
不补充数据了吧。
======================= 7 ==========================
更新帖子及主题的id (增加到smf论坛的后面)
gmly现在主题数的最大值 SELECT MAX(id_topic) FROM smf_topics (gmly.info的,下同)
gmly现在帖子数的最大值 SELECT MAX(id_msg) FROM smf_messages
UPDATE smf_topics_dz
SET id_topic = id_topic + [gmly现在主题数的最大值],
    id_first_msg = id_first_msg + [gmly现在帖子数的最大值];
UPDATE smf_messages_dz
SET id_topic = id_topic + [gmly现在主题数的最大值],
    id_msg = id_msg + [gmly现在帖子数的最大值],
    id_msg_modified = id_msg_modified + [gmly现在帖子数的最大值];
======================= 8 ==========================
查找相同用户名的用户(注意相同邮箱);更新所有用户的id;
1. 新建一个表,包含有重名的用户在中文站里及关卡空间里的两个id,及用户名。**注意中文站里member_name和real_name都要检查 Orz**
2. 将关卡空间转换后的数据表(主题,帖子等)里涉及该用户的uid更新为中文站里对应的id_member;
3. 删除关卡空间转换后的用户数据表(smf_members_dz)该名用户的资料;
4. 用户id增大,以便导入到中文站。
(以上顺序第四步把主题帖子里的用户id增大后,重复的那些人会找不到smf里的id了,还要改回来,麻烦)
////////////////////////  新  顺  序 ///////////////////////
1. 用户id增大(member_dz, messages_dz,topics_dz)
2. 建表,插值,删这个表同名马甲的资料。
3. 处理重复用户名的id(member_dz, messages_dz,topics_dz),
4. 删掉重复用户名的资料member_dz
>>>>8.1
UPDATE smf_members_dz, smf_messages_dz, smf_topics_dz
SET smf_members_dz.id_member = smf_members_dz.id_member + [gmly现在用户id的最大值],
    smf_messages_dz.id_member = smf_messages_dz.id_member + [gmly现在用户id的最大值],
    smf_topics_dz.id_member_started = smf_topics_dz.id_member_started + [gmly现在用户id的最大值];
>>>>8.2   在中文站的数据库里新建表,并且需要把smf_members_dz复制到中文站数据库里
CREATE TABLE `数据库名`.`yonghuming` (
`gmly_id_member` mediumint( 8 ) unsigned NOT NULL ,
`cdb_uid` mediumint( 8 ) unsigned NOT NULL ,
`member_name` char( 15 ) CHARACTER SET utf8 NOT NULL ,
`real_name` char( 15 ) CHARACTER SET utf8 NOT NULL
) ENGINE = MYISAM DEFAULT CHARSET = latin1;
    
INSERT INTO yonghuming
SELECT
    s.id_member AS `gmly_id_member`, 
    d.id_member AS `cdb_uid`, 
    s.member_name AS `member_name`,
    s.real_name AS `real_name`
FROM (smf_members AS s, smf_members_dz AS d)
WHERE d.member_name = s.member_name
    OR d.member_name = s.real_name
**发现插入输入后这个yonghuming表里有cdb_uid重复的行(同名马甲 T_T),会影响后面更新。所以需要删掉一些**
手工删。
>>>>8.3
(SELECT * FROM `smf_topics_dz` AS t,  yonghuming AS y WHERE t.id_member_started = y.cdb_uid) 这句用来查看id_member_start重复的主题有多少
8.3.1 更新主题的发帖人id
UPDATE smf_topics_dz, yonghuming
SET smf_topics_dz.id_member_started = yonghuming.gmly_id_member
WHERE smf_topics_dz.id_member_started = yonghuming.cdb_uid;
参考 MySQL 5.1参考手册 13.2.10. UPDATE语法 的后面例子,KAO,SQL的例子语法就不是这样的,难怪一直有问题,坑爹啊。
8.3.2 更新主题的最后回帖人id
8.3.3 更新帖子作者的id
UPDATE smf_messages_dz, yonghuming
SET smf_messages_dz.id_member = yonghuming.gmly_id_member
WHERE smf_messages_dz.id_member = yonghuming.cdb_uid
8.3 删除smf_member_dz里上面重复用户名的行
======================= 9 ==========================
UPDATE smf_members_dz, smf_messages_dz, smf_topics_dz
SET smf_members_dz.id_member = smf_members_dz.id_member + 13139,
    smf_messages_dz.id_member = smf_messages_dz.id_member + 13139,
    smf_topics_dz.id_member_started = smf_topics_dz.id_member_started + 13139; 
标签: 百度空间smf

相关文章

subaru的笔记:SMF修改msg、topic、boardid值(避免覆盖原数据)

subaru的笔记:SMF修改msg、topic、boardid值(避免覆盖原数据)

UPDATE `orz_messages` SET `id_msg` = ( `id_msg` +155555) WHERE 1;UPDATE `orz_messages` SET `id_topic` = ( `id_topic` +8500 ) WHERE 1;UPDATE `orz_messa...

subaru又有事情了

subaru又有事情了

boblog、zina、dokuwiki、smf的模板最近又看了几个php程序。对smf已经腻味了,小小动摇了一下。dz的好处是:太流行了,找模板和插件很方便,也容易和其他程序整合。而且……是个新的开始。但是论坛转换属于没事找事,而且smf很实用,dz太多花里胡哨的东西……中国人编写的程序好像大多是...

这个周末,把博客整合了一下

这个周末,把博客整合了一下

非常意外的是,我以前的旧博客备份非常齐全,加上强大的 wordpress,让整合成为了可能。值的一提的是,很多图片都无法导入wp,但是利用 BLUDIT 的 wp 导入插件,就能在 Bludit 中恢复这些图片,然后从 Bludit 导入 wp,wp就能同步这些图片了。具体整理完了再说吧。...

数据库内容批量替换

数据库内容批量替换

其实以前也记过,不过当时出于谨慎采用了先备份再替换。现在越来越懒了,直接替换了。Wordpress要把图片的“aligncenter”批量替换成“alignnone”在表中输入SQL命令UPDATE wp_posts SET post_content = replace(post_content,&...

结论

结论

经测试,百度博客的速度是最快的,那么……主博客就改到这里了...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法和观点。