def checktable(dbpath: str, ownername: str):
"""
检查数据表是否存在并根据情况创建
"""
if not (ifcreated := getcfpoptionvalue('everwebchat', 'wcdb', ownername)):
print(ifcreated)
dbnameinner = getdbname(dbpath, ownername)
tablename = "wcheadimg"
if istableindb(tablename, dbnameinner):
# 删表操作，危险，谨慎操作
droptablefromdb(dbnameinner, tablename, confirm=True)
csql = f"create table if not exists {tablename} (himgid INTEGER PRIMARY KEY AUTOINCREMENT,username TEXT not null, himguuid TEXT NOT NULL UNIQUE ON CONFLICT IGNORE, headimg BLOB NOT NULL)"
ifnotcreate(tablename, csql, dbnameinner)
logstr = f"数据表{tablename}于{dbnameinner}中被删除并完成重构"
log.critical(logstr)
tablename_cc = "wccontact"
if istableindb(tablename_cc, dbnameinner):
# 删表操作，危险，谨慎操作
droptablefromdb(dbnameinner, tablename_cc, confirm=True)
csql = f"create table if not exists {tablename_cc} (id INTEGER PRIMARY KEY AUTOINCREMENT, contactuuid TEXT NOT NULL UNIQUE ON CONFLICT IGNORE, nickname TEXT, contactflag int, remarkname TEXT, sex int, signature TEXT, starfriend int, attrstatus int, province TEXT, city TEXT, snsflag int, keyword TEXT, imguuid text, appendtime datatime)"
ifnotcreate(tablename_cc, csql, dbnameinner)
logstr = f"数据表{tablename_cc}于{dbnameinner}中被删除并完成重构"
log.critical(logstr)
setcfpoptionvalue('everwebchat', 'wcdb', ownername, str(True))
def checkbatteryinfotable(dbname: str, tablename: str):
"""
检查设备的电池信息数据表是否已经构建，设置相应的ini值避免重复打开关闭数据库文件进行检查
"""
if not (batteryinfocreated := getcfpoptionvalue('everhard', str(getdeviceid()), 'batteryinfodb')):
print(batteryinfocreated)
csql = f"create table if not exists {tablename} (appendtime int PRIMARY KEY, percentage int, temperature float)"
ifnotcreate(tablename, csql, dbname)
setcfpoptionvalue('everhard', str(getdeviceid()),
'batteryinfodb', str(True))
logstr = f"数据表{tablename}在数据库{dbname}中构建成功"
log.info(logstr)
def checkwcdelaytable(dbname: str, tablename: str):
"""
检查和dbname（绝对路径）相对应的延时数据表是否已经构建，设置相应的ini值避免重复打开关闭数据库文件进行检查
"""
if (wcdelaycreated := getcfpoptionvalue(
'everwebchat', os.path.abspath(dbname), tablename)) is None:
print(wcdelaycreated)
csql = f"create table if not exists {tablename} (id INTEGER PRIMARY KEY AUTOINCREMENT, msgtime int, delay int)"
ifnotcreate(tablename, csql, dbname)
setcfpoptionvalue('everwebchat', os.path.abspath(dbname), tablename,
str(True))
logstr = f"数据表{tablename}在数据库{dbname}中构建成功"
log.info(logstr)
def checkwcdelaytable(dbname: str, tablename: str):
"""
检查和dbname（绝对路径）相对应的延时数据表是否已经构建，设置相应的ini值避免重复打开关闭数据库文件进行检查
"""
if not (wcdelaycreated := getcfpoptionvalue(
'everwebchat', os.path.abspath(dbname), 'wcdelay')):
print(wcdelaycreated)
csql = f"create table if not exists {tablename} (time int primary key, delay int)"
ifnotcreate(tablename, csql, dbname)
setcfpoptionvalue('everwebchat', os.path.abspath(dbname), 'wcdelay',
str(True))
logstr = f"数据表{tablename}在数据库{dbname}中构建成功"
log.info(logstr)
def checkphoneinfotable(dbname: str):
"""
检查联系人和短信数据表是否已经构建，设置相应的ini值避免重复打开关闭数据库文件进行检查
"""
# 联系人数据表检查构建
if not (phonecontactdb := getcfpoptionvalue('everpim', str(getdeviceid()),
'phonecontacttable')):
tablename = "phone"
print(phonecontactdb, tablename)
csql = f"create table if not exists {tablename} (number str PRIMARY KEY not null unique on conflict ignore, name str, appendtime datetime)"
ifnotcreate(tablename, csql, dbname)
setcfpoptionvalue('everpim', str(getdeviceid()), 'phonecontacttable',
str(True))
logstr = f"数据表{tablename}在数据库{dbname}中构建成功"
log.info(logstr)
print(phonecontactdb, tablename)
csql = f"create table if not exists {tablename} (number str PRIMARY KEY not null unique on conflict ignore, name str, appendtime datetime)"
ifnotcreate(tablename, csql, dbname)
setcfpoptionvalue('everpim', str(getdeviceid()), 'phonecontacttable',
str(True))
logstr = f"数据表{tablename}在数据库{dbname}中构建成功"
log.info(logstr)
# 短信数据表检查构建
if not (phonecontactdb := getcfpoptionvalue('everpim', str(getdeviceid()),
'phonesmstable')):
tablename = "sms"
print(phonecontactdb, tablename)
# smsdfdone.columns = ['sent', 'sender', 'number', 'time', 'content', 'smsuuid']
csql = f"create table if not exists {tablename} (type str,sent bool, name str, number str, time datetime, content str,smsuuid str PRIMARY KEY not null unique on conflict ignore)"
ifnotcreate(tablename, csql, dbname)
setcfpoptionvalue('everpim', str(getdeviceid()), 'phonesmstable',
str(True))
logstr = f"数据表{tablename}在数据库{dbname}中构建成功"
log.info(logstr)
# 联系人描述数据表检查构建
if not (phonecontactdb := getcfpoptionvalue('everpim', str(getdeviceid()),
'phonectdesctable')):
tablename = "ctdesc"
print(phonecontactdb, tablename)
# smsdfdone.columns = ['sent', 'sender', 'number', 'time', 'content', 'smsuuid']
csql = f"create table if not exists {tablename} (name str,desc str)"
ifnotcreate(tablename, csql, dbname)
setcfpoptionvalue('everpim', str(getdeviceid()), 'phonectdesctable',
str(True))
# lp.print_stats()
# men_wc = 'heart5'
men_wc = '白晔峰'
dbname = getdirmain() / 'data' / 'db' / f"wcdelay_{men_wc}.db"
showdelayimg(dbname)
# #### 重构数据表
# ##### 重构数据结构
# ###### 构建新数据表
tablename_wcdelay_new = "wcdelaynew"
csql = f"create table if not exists {tablename_wcdelay_new} (id INTEGER PRIMARY KEY AUTOINCREMENT, msgtime int, delay int)"
ifnotcreate(tablename_wcdelay_new, csql, dbname)
# ###### 【删除新表】用于新数据表构建时的调试
droptablefromdb(dbname, tablename=tablename_wcdelay_new, confirm=True)
# ###### 从原数据表读入所有数据写入新的数据表
fresh, dfold = getdelaydb(dbname, tablename="wcdelay")
# 去掉附加的最后一条记录（提取数据时的时间和实际的最后一条延时），重置index回数据列
dffromold = dfold.iloc[:-1, :].reset_index()
# 赋值列名，匹配数据表的列名
dffromold.columns = ['msgtime', 'delay']
dffromold
