# -*- 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)