database_migration.py

# -*- coding: utf-8 -*-

'''
Author: your name
Date: 2021-07-20 19:04:27
LastEditTime: 2021-07-21 20:01:56
LastEditors: Please set LastEditors
Description: In User Settings Edit
FilePath: \evm-store\tools\database_migration.py
'''

import os
import sqlite3
import uuid
from datetime import datetime

BASE_DIR = os.path.dirname(os.path.abspath(__file__))

print("####>>>", BASE_DIR)

start = datetime.now()
print("start at:", start.strftime("%Y-%m-%d %H:%M:%S"))

source_conn = sqlite3.connect(os.path.join(BASE_DIR, "app-store.db"))
source_cur = source_conn.cursor()

target_db = os.path.join(BASE_DIR, "evue-store.db")
target_conn = sqlite3.connect(target_db)
target_cur = source_conn.cursor()

with open("database_migration.sql", "w+") as fd:
    # 更新user表
    opts = [
        [2, 39],
        [3, 40],
        [4, 41]
    ]

    sqls = [
        "update evm_store_apps set create_by = {a} where create_by = {b};",
        "update evm_store_apps set update_by = {a} where update_by = {b};",
        "update evm_store_annex set create_by = {a} where create_by = {b};",
        "update evm_store_annex set update_by = {a} where update_by = {b};",
        "update evm_store_app_logs set create_by = {a} where create_by = {b};",
        "update evm_store_build_logs set create_by = {a} where create_by = {b};",
        "update evm_store_build_logs set update_by = {a} where update_by = {b};",
        "update evm_store_device set create_by = {a} where create_by = {b};",
        "update evm_store_device set update_by = {a} where update_by = {b};",
        "update evm_store_login_logs set create_by = {a} where create_by = {b};",
    ]

    for s in sqls:
        for o in opts:
            sql_str = s.format(a=o[0], b=o[1])
            source_cur.execute(sql_str)
    source_conn.commit()

    # 更新app logs 表
    sql_str = "select create_at from evm_store_app_logs"
    source_cur.execute(sql_str)
    res = source_cur.fetchall()
    for line in res:
        sql_str = "select id from evm_store_apps where strftime('%s', evm_store_apps.create_at) - strftime('%s', '{b}') < 2".format(b=line[0])
        source_cur.execute(sql_str)
        tmp = source_cur.fetchone()
        if tmp:
            sql_str = "UPDATE evm_store_app_logs SET remarks = {a};".format(a=tmp[0])
            source_cur.execute(sql_str)
    source_conn.commit()

    # 先插入user表
    source_cur.execute('SELECT account, username, password, email, phone, create_at, create_by, update_at, update_by FROM evm_store_user')
    sql = "insert into evm_user (id, uuid, account, username, password, email, phone, create_at, create_by, update_at, update_by, is_delete, role) values ({a}, '{b}', '{c}', '{d}', '{e}', '{f}', '{g}', '{h}', {i}, '{j}', {k}, 0, 0);"
    res = source_cur.fetchall()
    i = 0
    for line in res:
        i += 1
        sql_str = sql.format(a=i, b=uuid.uuid1().hex, c=line[0], d=line[1], e=line[2], f=line[3], g=line[4], h=line[5], i=line[6], j=line[7], k=line[8])
        print("sql:", sql_str)
        # target_cur.execute(sql_str)
        fd.write(sql_str + "\n")
    target_conn.commit()

    # login logs
    source_cur.execute('SELECT id, username, ip, address, create_at, create_by, remarks FROM evm_store_login_logs')
    res = source_cur.fetchall()
    sql = "insert into evm_login (uuid, user, login_at, user_agent, ip, geo_location, operator, create_at, create_by, update_at, update_by, is_delete) values ('{b}', {c}, '{d}', '{e}', '{f}', '{g}', '{h}', '{i}', {j}, '{k}', {l}, 0);"
    for line in res:
        sql_str = sql.format(b=uuid.uuid1().hex, c=line[5], d=line[6], e="", f=line[2], g=line[3], h="", i=line[4], j=line[5], k=line[4], l=line[5])
        # target_cur.execute(sql_str)
        fd.write(sql_str + "\n")
    target_conn.commit()

    # 更新app_url字段
    sql = "select app, app_path from evm_store_build_logs"
    source_cur.execute(sql)
    res = source_cur.fetchall()
    for line in res:
        sql_str = string = "update evm_store_apps set app_url = '{u}' where id = {a}".format(u=line[1], a=line[0])
        source_cur.execute(sql_str)
        # fd.write(sql_str + "\n")
    target_conn.commit()

    # annex
    source_cur.execute('SELECT id, uuid, app, title, path, size, create_at, create_by, update_at, update_by, is_delete FROM evm_store_annex')
    res = source_cur.fetchall()
    sql = "insert into evm_annex (id, uuid, app, title, path, size, create_at, create_by, update_at, update_by, is_delete) values ({a}, '{b}', {c}, '{d}', '{e}', {f}, '{g}', {h}, '{i}', {j}, 0);"
    for line in res:
        if not line[2]:
            continue

        sql_str = sql.format(a=line[0], b=uuid.uuid1().hex, c=line[2], d=line[3], e=line[4] or "", f=line[5], g=line[6], h=line[7], i=line[8], j=line[9])
        # target_cur.execute(sql_str)
        fd.write(sql_str + "\n")
    target_conn.commit()

    # app
    source_cur.execute('SELECT id, app_name, app_icon, app_version, category, app_url, app_desc, create_at, create_by ,update_at, update_by, is_delete FROM evm_store_apps')
    res = source_cur.fetchall()
    sql = "insert into evm_app (id, uuid, app_name, app_icon, app_version, category, download_url, app_screen_size, app_arch, app_review, meta_data, remarks, create_at, create_by, update_at, update_by, is_delete, launcher, developer, app_file_size) values ({a}, '{b}', '{c}', '{d}', '{e}', '{f}', '{g}', '{h}', '{i}', '{j}', '{k}', '{l}', '{m}', {n}, '{o}', {p}, 0, `""`, `""`, 0);"
    for line in res:
        sql_str = sql.format(a=line[0], b=uuid.uuid1().hex, c=line[1], d=line[2], e=line[3], f=line[4], g=line[5], h="240 * 240", i="ASR3601", j=0, k='{}', l=line[6], m=line[7], n=line[8], o=line[9], p=line[10])
        # target_cur.execute(sql_str)
        fd.write(sql_str + "\n")
    target_conn.commit()

    # device
    source_cur.execute('SELECT id, name, imei, desc, type, create_at, create_by, update_at, update_by, is_delete FROM evm_store_device')
    res = source_cur.fetchall()
    sql = "insert into evm_device (id, uuid, name, imei, desc, type, create_at, create_by, update_at, update_by, is_delete) values ({a}, '{b}', '{c}', '{d}', '{e}', '{f}', '{g}', {h}, '{i}', {j}, {k});"
    for line in res:
        sql_str = sql.format(a=line[0], b=uuid.uuid1().hex, c=line[1], d=line[2], e=line[3], f=line[4], g=line[5], h=line[6], i=line[7], j=line[8], k=line[9])
        # target_cur.execute(sql_str)
        fd.write(sql_str + "\n")
    target_conn.commit()

    # app log & build log
    # 先插入app log
    source_cur.execute('SELECT id, uuid, app_name, app_path, app_version, app_info, create_at, create_by, remarks FROM evm_store_app_logs')
    res = source_cur.fetchall()
    sql = "insert into evm_package (id, uuid, app, app_path, app_version, app_info, create_at, create_by, update_at, update_by, is_delete, source) values ({a}, '{b}', '{c}', '{d}', '{e}', `'{f}'`, '{g}', {h}, '{i}', {j}, {k}, {l});"
    for line in res:
        # 根据时间查找app
        print(">>>>>>>>>>>>", line[6], line[8])
        if not line[8] or (isinstance(line[8], str) and len(line[8]) == 0):
            print("remarks is none")
            continue
        sql_str = "select id from evm_store_apps where id = {a}".format(a=int(line[8]))
        source_cur.execute(sql_str)
        tmp = source_cur.fetchone()
        if not tmp:
            print("app not found")
            continue
        s = 1 if line[5] and isinstance(line[5], str) and line[5].find("evueapps") > -1 else 0
        sql_str = sql.format(a=line[0], b=uuid.uuid1().hex, c=tmp[0], d=line[3], e=line[4], f=line[5], g=line[6], h=line[7], i=line[6], j=line[7], k=0, l=s)
        # target_cur.execute(sql_str)
        fd.write(sql_str + "\n")
    target_conn.commit()

    # 然后查询出所有build log记录，遍历这些记录
    # 在循环里，查询这一条记录是否已经存在（根据app_path），不存在则插入
    source_cur.execute('SELECT id, uuid, app, app_path, app_info, source, create_at, create_by, update_at, update_by, is_delete FROM evm_store_build_logs')
    res = source_cur.fetchall()
    sql = "insert into evm_package (id, uuid, app_name, app_path, app_version, app_info, source, create_at, create_by, update_at, update_by, is_delete) values ({a}, '{b}', '{c}', '{d}', '{e}', '{f}', {g}, '{h}', {i}, '{j}', {k}, {l});"
    for line in res:
        sql_str = "select id, uuid, app_path from evm_store_app_logs where app_path = '{}'".format(line[3])
        source_cur.execute(sql_str)
        tmp = source_cur.fetchone()
        print("=======>", line[0])
        if tmp:
            print("app_path not equal")
            continue

        print("===========>", line)
        sql_str = "select app_name, app_version from evm_store_apps where id == {id}".format(id=line[2])
        source_cur.execute(sql_str)
        app = source_cur.fetchone()
        print("app:", app)
        if app:
            s = 1 if line[5] and isinstance(line[5], str) and line[5].find("evueapps") > -1 else 0
            sql_str = sql.format(a=line[0], b=uuid.uuid1().hex, c=app[0], d=line[3], e=app[1], f=line[4], g=s, h=line[6], i=line[7], j=line[8], k=line[9], l=line[10])
            # target_cur.execute(sql_str)
            fd.write(sql_str + "\n")
            target_conn.commit()
        print("next >>>>>>>>>>>")

    print("finished!!!")

    target_conn.commit()
    target_conn.close()

    source_conn.commit()
    source_conn.close()


print("spend time:", datetime.now() - start)