maple-bbs/upgrade.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# **************************************************************************
# Copyright © 2017 jianglin
# File Name: upgrade.py
# Author: jianglin
# Email: xiyang0807@gmail.com
# Created: 2017-03-29 23:28:43 (CST)
# Last Update:星期四 2017-3-30 14:42:34 (CST)
#          By:
# Description:
# **************************************************************************
from datetime import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine1 = create_engine('postgresql://postgres:password@localhost/forums1')
session1 = sessionmaker(bind=engine1)()

engine2 = create_engine('postgresql://postgres:password@localhost/forums')
session2 = sessionmaker(bind=engine2)()


def upgrade_board():
    _boards = session1.execute('select * from boards;')
    for i in _boards:
        print(i)
        board = session2.execute(
            "insert into boards (id,name,description) values (:1,:2,:3) RETURNING *",
            {
                '1': i.id,
                '2': i.board,
                '3': i.description
            })
    session2.commit()
    _parent_boards = session1.execute(
        'select parent_board from boards group by parent_board;')
    n = 9
    for i in _parent_boards:
        p = session2.execute(
            "insert into boards (id,name,description) values (:1,:2,:3) RETURNING *",
            {
                '1': n,
                '2': i.parent_board,
                '3': i.parent_board
            })
        session2.commit()
        p = p.fetchone()
        _boards = session1.execute(
            "select * from boards where parent_board = :parent_board", {
                "parent_board": p.name
            })
        for j in _boards:
            q = session2.execute(
                "update boards set parent_id = :1 where id = :2", {
                    '1': p.id,
                    '2': j.id
                })
            session2.commit()
        n += 1


def upgrade_reply():
    _replies = session1.execute('select * from replies')
    for i in _replies:
        print(i)
        reply = session2.execute(
            'insert into replies (id,content,created_at,updated_at,author_id,topic_id) values (:1,:2,:3,:4,:5,:6)',
            {
                '1': i.id,
                '2': i.content,
                '3': i.publish,
                '4': i.publish,
                '5': i.author_id,
                '6': i.topic_id
            })
    session2.commit()


def upgrade_topic():
    _topics = session1.execute('select * from topics')
    for t in _topics:
        print(t)
        content_type = '1' if t.is_markdown else '0'
        topic = session2.execute(
            "insert into topics (id,title,content,content_type,created_at,updated_at,\
            is_good,is_top,author_id,board_id) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10) RETURNING *",
            {
                '1': t.id,
                '2': t.title,
                '3': t.content,
                '4': content_type,
                '5': t.publish,
                '6': t.updated,
                '7': t.is_good,
                '8': t.is_top,
                '9': t.author_id,
                '10': t.board_id
            })
    session2.commit()


def upgrade_user():
    results = session1.execute('select * from users;')
    for i in results:
        print(i)
        p = session2.execute(
            "insert into users (id,username,password,email,is_confirmed,\
            is_superuser,register_time) values (:1,:2,:3,:4,:5,:6,:7) RETURNING *",
            {
                '1': i.id,
                '2': i.username,
                '3': i.password,
                '4': i.email,
                '5': i.is_confirmed,
                '6': i.is_superuser,
                '7': i.register_time
            })
        session2.commit()
        p = p.fetchone()
        _info = session1.execute('select * from userinfor where id = :id', {
            'id': i.infor_id
        })
        _info = _info.fetchone()
        info = session2.execute(
            "insert into userinfo (id,avatar,school,word,introduce,user_id) values (:1,:2,:3,:4,:5,:6) RETURNING *",
            {
                '1': _info.id,
                '2': _info.avatar or '',
                '3': _info.school or '',
                '4': _info.word or '',
                '5': _info.introduce or '',
                '6': p.id
            })
        session2.commit()
        _setting = session1.execute('select * from usersetting where id = :id',
                                    {
                                        'id': i.setting_id
                                    })
        _setting = _setting.fetchone()
        setting = session2.execute(
            "insert into usersetting (id,online_status,topic_list,rep_list,\
             ntb_list,collect_list,locale,timezone,user_id) values (:1,:2,:3,:4,:5,:6,:7,:8,:9) RETURNING *",
            {
                '1': _setting.id,
                '2': _setting.online_status,
                '3': _setting.topic_list,
                '4': _setting.rep_list,
                '5': _setting.ntb_list,
                '6': _setting.collect_list,
                '7': _setting.locale,
                '8': _setting.timezone,
                '9': p.id
            })
        session2.commit()


def upgrade_tags():
    _tags = session1.execute('select * from tags;')
    for i in _tags:
        p = session2.execute(
            "insert into tags (id,name,description) values (:1,:2,:3) RETURNING *",
            {
                '1': i.id,
                '2': i.tagname,
                '3': i.summary or i.tagname
            })
    session2.commit()
    _tag_parents = session1.execute('select * from tags_parents;')
    for i in _tag_parents:
        session2.execute('update tags set parent_id = :1 where id = :2', {
            '1': i.parent_id,
            '2': i.tag_id
        })
    session2.commit()
    _tag_topics = session1.execute('select * from tag_topic;')
    for i in _tag_topics:
        p = session2.execute(
            "insert into tag_topic (tag_id,topic_id) values (:1,:2) RETURNING *",
            {
                '1': i.tags_id,
                '2': i.topics_id,
            })
    session2.commit()


def upgrade_collect():
    _collects = session1.execute('select * from collects;')
    for i in _collects:
        print(i)
        p = session2.execute(
            "insert into collects (id,name,description,is_hidden,author_id,created_at,updated_at) values (:1,:2,:3,:4,:5,:6,:7) RETURNING *",
            {
                '1': i.id,
                '2': i.name,
                '3': i.description,
                '4': i.is_privacy,
                '5': i.author_id,
                '6': datetime.now(),
                '7': datetime.now()
            })
    session2.commit()
    _collect_topics = session1.execute('select * from collect_topic;')
    for i in _collect_topics:
        p = session2.execute(
            "insert into topic_collect (collect_id,topic_id) values (:1,:2) RETURNING *",
            {
                '1': i.collect_id,
                '2': i.topic_id
            })
    session2.commit()


def upgrade_like():
    _likes = session1.execute('select * from likes;')
    for i in _likes:
        p = session2.execute(
            "insert into reply_liker (reply_id,liker_id) values (:1,:2) RETURNING *",
            {
                '1': i.reply_id,
                '2': i.author_id
            })
    session2.commit()


def upgrade_follow():
    _followers = session1.execute('select * from follows;')
    for i in _followers:
        print(i)
        if i.following_user_id:
            p = session2.execute(
                "insert into user_follower (follower_id,user_id) values (:1,:2) RETURNING *",
                {
                    '1': i.follower_id,
                    '2': i.following_user_id
                })
        elif i.following_tag_id:
            p = session2.execute(
                "insert into tag_follower (follower_id,tag_id) values (:1,:2) RETURNING *",
                {
                    '1': i.follower_id,
                    '2': i.following_tag_id
                })
        elif i.followinf_topic_id:
            p = session2.execute(
                "insert into topic_follower (follower_id,topic_id) values (:1,:2) RETURNING *",
                {
                    '1': i.follower_id,
                    '2': i.followinf_topic_id
                })
        elif i.following_collect_id:
            p = session2.execute(
                "insert into collect_follower (follower_id,collect_id) values (:1,:2) RETURNING *",
                {
                    '1': i.follower_id,
                    '2': i.following_collect_id
                })
    session2.commit()


def upgrade_setval():
    '''
    psql (9.6.1)
    输入 "help" 来获取帮助信息.

    forums=# \d
                        关联列表
    架构模式 |        名称        |  类型  |  拥有者
    ----------+--------------------+--------+----------
    public   | boards             | 数据表 | postgres
    public   | boards_id_seq      | 序列数 | postgres
    public   | collect_follower   | 数据表 | postgres
    public   | collects           | 数据表 | postgres
    public   | collects_id_seq    | 序列数 | postgres
    public   | replies            | 数据表 | postgres
    public   | replies_id_seq     | 序列数 | postgres
    public   | reply_liker        | 数据表 | postgres
    public   | tag_follower       | 数据表 | postgres
    public   | tag_topic          | 数据表 | postgres
    public   | tags               | 数据表 | postgres
    public   | tags_id_seq        | 序列数 | postgres
    public   | topic_collect      | 数据表 | postgres
    public   | topic_follower     | 数据表 | postgres
    public   | topics             | 数据表 | postgres
    public   | topics_id_seq      | 序列数 | postgres
    public   | user_follower      | 数据表 | postgres
    public   | userinfo           | 数据表 | postgres
    public   | userinfo_id_seq    | 序列数 | postgres
    public   | users              | 数据表 | postgres
    public   | users_id_seq       | 序列数 | postgres
    public   | usersetting        | 数据表 | postgres
    public   | usersetting_id_seq | 序列数 | postgres
    (23 行记录)

    forums=#
    '''
    session2.execute(
        "select setval('boards_id_seq',(select max(id) from boards))")
    session2.execute(
        "select setval('collects_id_seq',(select max(id) from collects))")
    session2.execute("select setval('tags_id_seq',(select max(id) from tags))")
    session2.execute(
        "select setval('topics_id_seq',(select max(id) from topics))")
    session2.execute(
        "select setval('replies_id_seq',(select max(id) from replies))")
    session2.execute(
        "select setval('users_id_seq',(select max(id) from users))")
    session2.execute(
        "select setval('userinfo_id_seq',(select max(id) from userinfo))")
    session2.execute(
        "select setval('usersetting_id_seq',(select max(id) from usersetting))")


if __name__ == '__main__':
    upgrade_board()
    upgrade_user()
    upgrade_topic()
    upgrade_collect()
    upgrade_tags()
    upgrade_reply()
    upgrade_like()
    upgrade_follow()
    upgrade_setval()