1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
# -*- 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)