phpBB3导入版面的Python脚本

关联的数据表

在phpBB3中导入版面时, 需要处理的有两张表, 一个是 forums, 一个是 acl_groups. 

如果是干净的论坛, 可以不保留安装时填入的默认分区和版面, 直接用以下语句初始化:

-- 清空 forums 表
TRUNCATE phpbb_forums;
-- 清空 acl_groups 表
TRUNCATE phpbb3015.phpbb_acl_groups;
-- 填入初始化权限
INSERT INTO `phpbb_acl_groups` VALUES (1,0,85,0,1),(1,0,93,0,1),(1,0,111,0,1),(5,0,0,5,0),(5,0,0,1,0),(2,0,0,6,0),(3,0,0,6,0),(4,0,0,5,0),(4,0,0,10,0),(7,0,0,23,0);

如果是已经存在版面, 并且需要保留版面的论坛, 则仅需要记下当前的最大right_id

SELECT MAX(right_id) FROM phpbb_forums

.

需要的最小数据集

需要的最小字段为 `forum_id`, `parent_id`, `left_id`, `right_id`, `forum_name`, `forum_type`

构造版面数据

phpBB3的版面为单个父节点的树状结构, 使用了parent_id, left_id, right_id 来标识版面间的层级关系以及排序顺序. 在构造版面数据时, 需要的就是采集最小数据集, 并正确生成parent_id, left_id和right_id. 下面的例子使用的版面, 原数据是分区 + 版面的结构, 分区有层级关系, 版面有层级关系, 分区的ID与版面的ID有重叠, 并且分区与版面之间存在多个父节点的情况. 需要在生成中进行调整.

创建一个可用的ID序列, 用于将分区ID映射到可用ID序列上

数量不大的话, 这一步可以通过手工完成, 根据分区的数量, 观察版面的ID序列, 列出可用的ID做成list

availableIds = [3, 8, 11, 12, 13, 27, 30, ...]

将分区加入版面列表

遍历分区, 将旧ID映射到新ID上, 需要两次遍历, 第二次遍历时构造父子关系, children变量用于在最后生成left_id和right_id

boardsDict = {} # The mapping between Id => board

# Build the section map
allSections = rbcommon.tb_section.find({}).sort('rank', 1)
boards = [] # Record all boards
topBoards = [] # the root board Ids

sectionMap = {} # The mapping between old section Id => new board Id, for assigning new Ids for the sections
cusor = 0
for section in allSections:
    sectionMap[str(section['_id'])] = availableIds[cusor]
    newId = availableIds[cusor]
    board = {
        'oid': section['_id'], 'oldPid': section['parentId'],
        '_id': newId, 'name2': section['name2'], 'is_folder': 'true',
        'desc': section['desc'],
        'children': []
    }
    boards.append(board)
    boardsDict[board['_id']] = board
    cusor += 1

for board in boards:
    if (board['oldPid'] != 0):
        board['parentId'] = sectionMap[str(board['oldPid'])]
        parent = boardsDict[board['parentId']]
        parent['children'].append(board['_id'])
    else:
        board['parentId'] = 0
        topBoards.append(board['_id'])

for board in boards:
    print('oid:{}, oldPid:{}, _id:{}, parentId:{}, children:{}'.format(board['oid'], board['oldPid'], board['_id'], board['parentId'], board['children']))

将版面加入列表

# Build the boards
mongoBoards = rbcommon.tb_board.find({})
for mongoBoard in mongoBoards:
    board = {
        'oid': mongoBoard['_id'], 'oldPid': 0, 'parentId': 0,
        '_id': mongoBoard['_id'], 'name2': mongoBoard['name2'], 'is_folder': mongoBoard['is_folder'],
        'desc': mongoBoard['name'],
        'children': []
    }
    boards.append(board)
    if (board['_id'] in boardsDict.keys()):
        print('Error: {}'.format(board['_id']))
        exit
    boardsDict[board['_id']] = board

完善版面层级关系

# Build the boards tree
allSectionToBoards = rbcommon.tb_section_to_board.find({})
for s2b in allSectionToBoards:
    if (s2b['parentId'] == 0):
        # parent is section
        parentId = sectionMap[str(s2b['sectionId'])]
        parent = boardsDict[parentId]
        board = boardsDict[s2b['boardId']]
        # avoid the multiple parent
        if (board['parentId'] > 0):
            print('Duplicate {} for {}, board:{}'.format(parentId, board['parentId'], s2b['boardId']))
            continue
        board['parentId'] = parentId
        parent['children'].append(s2b['boardId'])
    else:
        # parent is board
        parent = boardsDict[s2b['parentId']]
        board = boardsDict[s2b['boardId']]
        # avoid the multiple parent
        if (board['parentId'] > 0):
            print('Duplicate {} for {}, board:{}'.format(s2b['parentId'], board['parentId'], s2b['boardId']))
            continue
        board['parentId'] = s2b['parentId']
        parent['children'].append(s2b['boardId'])

print("All boards:")
for board in boards:
    print('oid:{}, oldPid:{}, _id:{}, parentId:{}, folder:{}, children:{}'.format(
        board['oid'], board['oldPid'], board['_id'], board['parentId'], board['is_folder'], board['children']))

使用递归填充left_id和right_id

其中counter的取值, 如果是干净的论坛并且前面已经执行了truncate, 就将counter设成1, 否则设成前面得到的right_id最大值 + 1. 这样新导入的分区和版面都会出现在原有分区和版面的下方

# Build the leftId and rightId
markLeftAndRight(topBoards)
print("Marked boards:")
for board in boards:
    print('_id:{}, parentId:{}, left:{}, right:{}, folder:{}, children:{}'.format(
        board['_id'], board['parentId'], board['leftId'], board['rightId'], board['is_folder'], board['children']))

# 用于递归的方法
def markLeftAndRight(idList):
    global counter
    for id in idList:
        board = boardsDict[id]
        if ('leftId' in board):
            print('Error: {}'.format(id))
            exit
        board['leftId'] = counter
        counter += 1
        if (len(board['children']) > 0):
            markLeftAndRight(board['children'])
        board['rightId'] = counter
        counter += 1

.

写入MySQL

用pymsql写入mysql, 每写入一个版面, 同时写入对应的权限, 注意分区和版面的默认权限数据是不一样的.

# Write it to MySQL
for board in boards:
    try:
        with rbcommon.mysqlclient.cursor() as cursor:
            # insert forum
            sql = '''INSERT INTO `phpbb_forums` (`forum_id`, `parent_id`, `left_id`, `right_id`, `forum_parents`, `forum_name`, `forum_desc`,
                `forum_rules`, `forum_type`) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)'''
            cursor.execute(sql, (
                board['_id'],
                board['parentId'],
                board['leftId'],
                board['rightId'],
                '',
                board['name2'],
                board['desc'],
                '',
                0 if (board['is_folder'] == 'true') else 1))
            rbcommon.mysqlclient.commit()
            # insert acl_group
            if (board['is_folder'] == 'true'):
                sql = 'INSERT INTO `phpbb_acl_groups` VALUES (1,%s,0,17,0),(2,%s,0,17,0),(3,%s,0,17,0),(6,%s,0,17,0)'
                cursor.execute(sql, (
                    board['_id'], board['_id'], board['_id'], board['_id']))
                rbcommon.mysqlclient.commit()
            else:
                sql = 'INSERT INTO `phpbb_acl_groups` VALUES (1,%s,0,17,0),(2,%s,0,15,0),(3,%s,0,15,0),(4,%s,0,21,0),(5,%s,0,14,0),(5,%s,0,10,0),(6,%s,0,19,0),(7,%s,0,24,0)'
                cursor.execute(sql, (
                    board['_id'], board['_id'], board['_id'], board['_id'], board['_id'], board['_id'], board['_id'], board['_id']))
                rbcommon.mysqlclient.commit()

    except Exception as e:
        print(e)

数据导入后, 使用管理员帐号先在后台清空缓存, 再查看和编辑版面