class TbIscoutTask(TbSqliteBase):
__tb_iscouttask: SqliteTable = SqliteTable(
"iscouttask",
True,
SqliteColumn(
colname="ID",
coltype="INTEGER",
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True,
).set_index_new(),
SqliteColumn(
colname="taskid", coltype="CHAR", length=50, nullable=False
).set_index_new(),
SqliteColumn(
colname="batchid", coltype="CHAR", length=50, nullable=False
).set_index_new(),
SqliteColumn(colname="platform", coltype="CHAR", length=50, nullable=False),
SqliteColumn(colname="clientid", coltype="CHAR", length=50, nullable=False),
SqliteColumn(colname="source", coltype="CHAR", nullable=False),
SqliteColumn(colname="objecttype", coltype="INT", nullable=False),
SqliteColumn(colname="object", coltype="CHAR", nullable=False),
SqliteColumn(colname="createtime", coltype="CHAR"),
SqliteColumn(colname="cmdid", coltype="CHAR", length=50),
SqliteColumn(colname="lastexecutetime", coltype="INT"),
SqliteColumn(colname="failtimes", coltype="INT"),
SqliteColumn(colname="successtimes", coltype="INT"),
SqliteColumn(colname="taskstatus", coltype="INT"),
SqliteColumn(colname="sequence", coltype="INT"),
SqliteColumn(colname="periodnum", coltype="INT"),
)
databasename = "task"
def __init__(self, dbcfg: SqliteConfig):
TbSqliteBase.__init__(
self, TbIscoutTask.__tb_iscouttask._tbname, dbcfg, TbIscoutTask.databasename
)
def _append_tables(self):
self._conn_mngr.append_table(TbIscoutTask.__tb_iscouttask)
@table_locker(__tb_iscouttask._tbname)
def query_iscout_task(self, conds: SqlConditions) -> list:
"""
根据任务状态来取出task表的任务，
根据不同的状态来查询不同的任务数据
:param key:
:param value:
:return:
"""
conn: SqliteConn = None
res = []
sql = """SELECT
taskid,
batchid,
iscouttask.platform,
clientid,
source,
objecttype,
object,
createtime,
cmdid,
cmd,
taskstatus,
sequence,
periodnum
FROM iscouttask LEFT OUTER JOIN idowncmd USING (cmdid)
WHERE {};""".format(
conds.text_normal
)
try:
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
conn._conn.row_factory = self._dict_factory
c = conn._conn.cursor()
c.execute(sql, conds.params)
res_data = c.fetchall()
if len(res_data) > 0:
res.extend(res_data)
except Exception as ex:
conn._conn.rollback()
raise ex
finally:
if conn is not None:
conn.close()
except Exception:
self._logger.error(
f"Query task according to the task status data problem,err:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.close()
return res
def _get_task_update_sql(self, src: dict, new: IscoutTask):
"""拼接更新task的sql，并返回sqlparameters列表"""
params: list = []
sql = "UPDATE iscouttask SET taskstatus=?, "
params.append(new.taskstatus.value)
sql += "objecttype=?, "
params.append(new._objecttype.value)
sql += "object=?, "
params.append(new._object)
# 增加一个存入时间
# sql += 'taskstarttime=?, '
# params.append(new.taskstarttime)
if not helper_str.is_none_or_empty(new.cmd_id):
sql += "cmdid=?, "
params.append(new.cmd_id)
if not helper_str.is_none_or_empty(new.source):
sql += "source=?, "
params.append(new.source)
if not helper_str.is_none_or_empty(new.periodnum):
sql += "periodnum=?, "
params.append(new.periodnum)
sql = sql.rstrip().rstrip(",")
sql += " WHERE batchid=? AND taskid=?"
params.append(new.batchid)
params.append(new.taskid)
return sql, params
@table_locker(__tb_iscouttask._tbname)
def __count_iscouttask(self, taskid, batchid):
"""
对数据库中的任务进行计数
:param taskid:
:param batchid:
:return:
"""
conn: SqliteConn = None
res = []
sql = """
SELECT count(1) FROM iscouttask
WHERE taskid=? AND batchid=?
"""
par = (taskid, batchid)
try:
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
# conn._conn.row_factory = self._dict_factory
c = conn.cursor
c.execute(sql, par)
res_data = c.fetchall()
# print(res_data)
if len(res_data) > 0 and res_data[0][0] > 0:
res.extend(res_data)
except Exception as ex:
conn._conn.rollback()
raise ex
finally:
if conn is not None:
conn.close()
except Exception:
self._logger.error(
f"Query task according to the task status data problem\nerr:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.close()
return res
@table_locker(__tb_iscouttask._tbname)
def _updatesamebatchiddata(self, dt: IscoutTask):
"""
在插入任务数据前，先检查iscouttask表
是否有相同的batchid, 如果batchid相同那么就更新整条数据的状态
:param dt:
:return:
"""
res = False
conn: SqliteConn = None
resdata = self.__count_iscouttask(dt.taskid, dt.batchid)
if len(resdata) == 0:
# 没有重复的id直接返回
return False
elif len(resdata) > 1:
raise Exception(
"The database has duplicated multiple skipids, please check the problem"
)
elif len(resdata) == 1:
self._logger.info(f"Get same task, update scout task info")
sql, par = self._get_task_update_sql(resdata[0], dt)
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, par)
if (
result is not None and result.rowcount > 0
): # or len(result) < 1:
res = True
self._logger.info(f"Update task status success")
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(
f"There was a problem updating the iscouttask table\nerr:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.close()
return res
@table_locker(__tb_iscouttask._tbname)
def insert_iscouttask(self, dt: IscoutTask):
"""
扫描任务目前是直接存入数据库的表
如果是重复的任务那么就更新
:param dt:
:return:
"""
conn: SqliteConn = None
# 在插入数据前查询是否有相同的数据(目前只根据taskid查询)，如果有相同的数据则不插入。
repeat = self._updatesamebatchiddata(dt)
if repeat:
# 如果已经处理了重复的数据直接就结束了
return
sql = """
INSERT INTO iscouttask(
taskid,
batchid,
platform,
clientid,
source,
objecttype,
object,
createtime,
cmdid,
taskstatus,
sequence,
periodnum
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
params = (
dt.taskid,
dt.batchid,
dt.platform,
dt._clientid,
dt.source,
dt._objecttype.value,
dt._object,
dt.createtimestr,
dt.cmd_id,
dt.taskstatus.value,
dt._sequence,
dt.periodnum,
)
try:
conn = self.connect_write(5)
c = conn.cursor
c.execute(sql, params)
except Exception:
self._logger.error(
f"There was a problem inserting data\nerr:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.commit()
conn.close()
return
@table_locker(__tb_iscouttask._tbname)
def update_iscout_status(self, key: str, value: int, batchid, taskid):
"""
更新iscouttask表的任务状态，根据batchid定位数据
:param key:
:param value:
:param taskid:
:return:
"""
res = False
conn: SqliteConn = None
sql = """UPDATE iscouttask set {}=? where taskid=? and batchid=?
""".format(
key
)
pars = (value, taskid, batchid)
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, pars)
if (
result is not None and result.rowcount > 0
): # or len(result) < 1:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(
f"There was a problem inserting data, and duplicate data might have been inserted\nerr:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.close()
return
@table_locker(__tb_iscouttask._tbname)
def update_iscout_info(self, tsk: IscoutTask):
"""
更新下载完成后task的信息，作用于iscout的循环下载
:param tsk:
:return:
"""
res = False
conn: SqliteConn = None
sql = """UPDATE iscouttask set
lastexecutetime=?,
failtimes=?,
successtimes=?,
sequence=?,
periodnum=?
where taskid=? and batchid=?
"""
pars = (
tsk.lastexecutetime,
tsk.failtimes,
tsk.successtimes,
tsk._sequence,
tsk.periodnum,
tsk.taskid,
tsk.batchid,
)
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, pars)
if (
result is not None and result.rowcount > 0
): # or len(result) < 1:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(
f"There was a problem inserting data\nerr:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.close()
return
class TbInputData(TbSqliteBase):
__tb_inputdata: SqliteTable = SqliteTable(
'inputdata',
True,
SqliteColumn(colname='ID',
coltype='INTEGER',
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True).set_index_new(),
SqliteColumn(colname='taskid',
coltype='CHAR',
length=50,
nullable=False).set_index_new(),
SqliteColumn(colname='platform',
coltype='CHAR',
length=50,
nullable=False),
SqliteColumn(colname='parenttaskid',
coltype='CHAR',
length=50,
nullable=False),
SqliteColumn(colname='batchid',
coltype='CHAR',
length=50,
nullable=False),
SqliteColumn(colname='parentbatchid',
coltype='CHAR',
length=50,
nullable=False),
SqliteColumn(colname='input',
coltype='CHAR',
length=50,
nullable=False),
)
databasename = 'idownverify'
def __init__(self, dbcfg: SqliteConfig):
TbSqliteBase.__init__(self, TbInputData.__tb_inputdata._tbname, dbcfg,
TbInputData.databasename)
def _append_tables(self):
self._conn_mngr.append_table(TbInputData.__tb_inputdata)
@table_locker(__tb_inputdata._tbname)
def input_insert(self, tsk: Task):
"""
将交互输入的验证码存入数据库的inputdata表，
验证码在取得后就删除在数据库中的记录
:param tsk:
:return:
"""
conn: SqliteConn = None
sql = '''INSERT INTO inputdata(
taskid,
platform,
parenttaskid,
batchid,
parentbatchid,
input
)VALUES (?,?,?,?,?,?);
'''
params = (tsk.taskid, tsk.platform, tsk.parenttaskid, tsk.batchid,
tsk.parentbatchid, tsk.input)
try:
conn = self.connect_write()
c = conn.cursor
c.execute(sql, params)
except Exception as err:
self._logger.error(
"There was a problem inserting data, err:{}.".format(err))
finally:
if conn is not None:
conn.commit()
conn.close()
return
@table_locker(__tb_inputdata._tbname)
def query_input(self, task: Task):
"""
查询数据库的inputdata表，寻找验证码
根据传入的taskid = parentid,
查询完成后即时删除这条数据
:param taskid:
:return:
"""
conn: SqliteConn = None
input = None
sql = '''SELECT * FROM inputdata
WHERE platform=? AND parenttaskid=? AND parentbatchid=?;
'''
delsql = '''DELETE FROM inputdata
WHERE platform=? AND parenttaskid=? AND parentbatchid=?;
'''
par = (task.platform, task.taskid, task.batchid)
try:
for conn in self.connect_all(5):
conn._conn.row_factory = self._dict_factory
c = conn._conn.cursor()
c.execute(sql, par)
allget: dict = c.fetchall()
# 以防万一一个任务多个验证码
if len(allget) > 1:
c.execute(delsql, par)
conn.commit()
conn.close()
self._logger.error(
"There are multiple verification codes for the same sms task. "
"All verification codes have been deleted. Please re-enter and send a valid verification code."
)
break
if len(allget) == 0:
conn.close()
continue
else:
res = allget[0]
# 获取验证码
input = res.get('input')
if input is not None:
c.execute(delsql, par)
break
except:
self._logger.error(
f"Query verification code error,err:{traceback.format_exc()}")
finally:
if conn is not None:
conn.commit()
conn.close()
return input
class TbUnEXPDBData(TbSqliteBase):
__tb_autodata: SqliteTable = SqliteTable(
'undata',
True,
SqliteColumn(
colname='Id',
coltype='INTEGER',
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True).set_index_new(),
SqliteColumn(colname='UniqueId', nullable=False).set_index_new(),
SqliteColumn(colname='DownloadTime', coltype='DATETIME', nullable=False),
)
databasename = 'expdbdata'
def __init__(self, dbcfg: SqliteConfig):
TbSqliteBase.__init__(self, TbUnEXPDBData.__tb_autodata._tbname, dbcfg, TbUnEXPDBData.databasename)
def _append_tables(self):
self._conn_mngr.append_table(TbUnEXPDBData.__tb_autodata)
@table_locker(__tb_autodata._tbname)
def insert_identify(self, unique_info):
"""
存储数据的唯一标识
:param unique_info:
:return:
"""
sql = '''
INSERT INTO undata(
UniqueId,
DownloadTime
)VALUES (?, ?)
'''
time_str = datetime.now(pytz.timezone('Asia/Shanghai')).strftime('%Y-%m-%d %H:%M:%S')
pars = (unique_info, time_str)
res = False
conn: SqliteConn = None
try:
conn: SqliteConn = self.connect_write(5)
c = conn.cursor
result = c.execute(sql, pars)
if result is None or result.rowcount < 1: # or len(result) < 1:
res = False
else:
res = True
except:
self._logger.error(f"Insert auto unique data error,err:{traceback.format_exc()}")
finally:
if conn is not None:
conn.commit()
conn.close()
return res
@table_locker(__tb_autodata._tbname)
def identify_count(self, unique_info) -> bool:
"""
查询数据库中是否已经下载了该数据
:param unique_info:
:return:
"""
conn: SqliteConn = False
res: bool = False
sql = """select count(1) from undata where UniqueId=?"""
pars = (unique_info,)
try:
for conn in self.connect_all(5):
try:
conn: SqliteConn = conn
c = conn.cursor
result = c.execute(sql, pars)
for c in result:
# print(c)
if len(c) > 0 and c[0] > 0:
res = True
break
except Exception as ex:
conn._conn.rollback()
raise ex
finally:
if conn is not None:
conn.close()
if res:
break
except:
self._logger.error(f"Count auto unique data error,err:{traceback.format_exc()}")
finally:
if conn is not None:
conn.commit()
conn.close()
return res
class TbData(TbSqliteBase):
__tb_data: SqliteTable = SqliteTable(
'data',
True,
SqliteColumn(colname='Id',
coltype='INTEGER',
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True).set_index_new(),
SqliteColumn(colname='UniqueId', nullable=False).set_index_new(),
SqliteColumn(colname='DataType', nullable=False),
SqliteColumn(colname='AppType', coltype='INTEGER', nullable=False),
# SqliteColumn(colname='Account', nullable=False),
SqliteColumn(colname='DownloadTime',
coltype='DATETIME',
nullable=False),
)
databasename = 'idowndata'
def __init__(self, dbcfg: SqliteConfig):
TbSqliteBase.__init__(self, TbData.__tb_data._tbname, dbcfg,
TbData.databasename)
def _append_tables(self):
self._conn_mngr.append_table(TbData.__tb_data)
def _get_execute_sql_pars(self, data: FeedDataBase):
if not isinstance(data, UniqueData):
raise Exception("Param data is invalid.")
sqls = []
if not data._is_muti_seg:
sql = self.__get_sql_pars(data, data._datatype.value)
sqls.append(sql)
else:
for inner in data:
sql = self.__get_sql_pars(inner, data._datatype.value)
sqls.append(sql)
return sqls
def __get_sql_pars(self, data: UniqueData, datatype):
pars = (
data.get_uniqueid(),
datatype,
data._task.apptype,
# data._task.account,
helper_time.get_time_sec_tz(),
)
return pars
def _dump_pars(self, pars):
conn = None
sql = '''
SELECT count(1) FROM data
WHERE UniqueId=?
'''
newdata = []
for par in pars:
try:
# 默认数据不是重复的
dump_res = False
for conn in self.connect_all(5):
try:
conn: SqliteConn = conn
c = conn.cursor
c.execute(sql, (par[0], ))
result = c.fetchall()
# 防止没有查出来
if len(result) > 0 and result[0][0] > 0:
# 数据是重复的
dump_res = True
break
except Exception as ex:
conn._conn.rollback()
raise ex
finally:
if conn is not None:
conn.close()
if not dump_res:
newdata.append(par)
except:
self._logger.error(
f'Dump data error, err:{traceback.format_exc()}')
finally:
if conn is not None:
conn.commit()
conn.close()
return newdata
@table_locker(__tb_data._tbname)
def insert_uniquely_identifies(self, data: FeedDataBase) -> bool:
"""
向数据库存入数据唯一标识，用于去重。返回是否插入成功True/False
由于存在强制下载，所以可能需要更新已有的资源数据
"""
conn: SqliteConn = None
res: bool = False
sql = """insert into data(
UniqueId,
DataType,
AppType,
DownloadTime
) values(?,?,?,?)"""
try:
pars = self._get_execute_sql_pars(data)
if len(pars) == 0:
return res
# 插入前查询数据是否在数据库里
new_pars = self._dump_pars(pars)
if len(new_pars) == 0:
return res
pars = new_pars
conn = self.connect_write()
c = conn._conn.cursor()
if len(pars) == 1:
result = c.execute(sql, pars[0])
else:
result = c.executemany(sql, pars)
if result is None or result.rowcount < 1: # or len(result) < 1:
res = False
else:
res = True
except Exception:
self._logger.error(
f"Insert data to db error:\ndatatype:{data._datatype}\n"
f"datauniqueid:{data.get_uniqueid()}\nerror:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.commit()
conn.close()
return res
@table_locker(__tb_data._tbname)
def is_data_exists(self, data: UniqueData,
datatype: EStandardDataType) -> bool:
"""检查数据是否已存在。返回True/False"""
conn: SqliteConn = False
res: bool = False
try:
if not isinstance(data, UniqueData):
raise Exception("Param data is invalid.")
sql = """select count(1) from data where
UniqueId=? and
DataType=? and
AppType=?"""
for conn in self.connect_all(5):
try:
conn: SqliteConn = conn
c = conn.cursor
result = c.execute(sql, (
data.get_uniqueid(),
datatype.value,
data._task.apptype,
))
for c in result:
# print(c)
if len(c) > 0 and c[0] > 0:
res = True
break
except Exception as ex:
conn._conn.rollback()
raise ex
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(
"Check data duplication error:\ndatatype:{}\ndataid:{}\nerror:{}"
.format(data._datatype.name, data.get_uniqueid(),
traceback.format_exc()))
finally:
if conn is not None:
conn.commit()
conn.close()
return res
class TbIScanTask(TbSqliteBase):
"""Task表及相关操作"""
__tb_IScanTask: SqliteTable = SqliteTable(
'IScanTask',
True,
SqliteColumn(
colname='Id',
coltype='INTEGER',
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True).set_index_new(),
SqliteColumn(colname='ClientId').set_index_new(),
SqliteColumn(colname='TaskId', nullable=False).set_index_new(),
SqliteColumn(colname='CmdId', nullable=False).set_index_new(),
SqliteColumn(colname='Platform', nullable=False).set_index_new(),
SqliteColumn(
colname='Status',
coltype='INTEGER',
nullable=False,
defaultval=ECommandStatus.WaitForSend.value).set_index_new(),
SqliteColumn(
colname='Progress', #0~1浮点数表百分比
coltype='REAL',
nullable=True),
SqliteColumn(colname='ScanType', coltype='INTEGER',
defaultval=1).set_index_new(),
SqliteColumn(
colname='IsPeriod',
coltype='INTEGER',
nullable=False,
defaultval=0).set_index_new(),
SqliteColumn(
colname='PeriodNum',
coltype='INTEGER',
nullable=False,
defaultval=0).set_index_new(),
SqliteColumn(colname='StartTime', coltype='DATETIME'),
SqliteColumn(colname='EndTime', coltype='DATETIME'),
SqliteColumn(colname='Interval', coltype='REAL'),
SqliteColumn(colname='LastStartTime', coltype='DATETIME'),
SqliteColumn(colname='LastEndTime', coltype='DATETIME'),
SqliteColumn(colname='Source').set_index_new(),
SqliteColumn(colname='CmdRcvMsg'),
SqliteColumn(
colname='CreateTime',
coltype='DATETIME',
defaultval=helper_time.get_time_sec_tz()),
SqliteColumn(colname='UpdateTime', coltype='REAL',
nullable=False).set_index_new(),
SqliteColumn(
colname='Sequence',
nullable=False,
coltype='INTEGER',
defaultval=0).set_index_new(),
)
# scantype=scansearch 主键 TaskId
# scantype=scan 主键 TaskId+BatchId
# 所有列，复制粘贴用...：
# ClientId
# TaskId,
# CmdId,
# Platform,
# Status,
# Progress,
# ScanType,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
def __init__(self, dbcfg: SqliteConfig):
""""""
TbSqliteBase.__init__(self, TbIScanTask.__tb_IScanTask._tbname, dbcfg)
def _append_tables(self):
self._conn_mngr.append_table(TbIScanTask.__tb_IScanTask)
@table_locker(__tb_IScanTask._tbname)
def save_new_iscantask(
self,
client: Client,
scantask: IscanTask,
cmdstatus: ECommandStatus = ECommandStatus.WaitForSend,
) -> bool:
"""保存新的批处理任务的令牌资源\n
task:任务对象"""
res = False
conn: SqliteConn = None
cursor = None
client: Client = client
scantask: IscanTask = scantask
try:
# 搜索每个库，看有没有 TokenId一样的，且时间更新
# 的，一样就更新其他所有字段
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE
ClientId=? and TaskId=? and Platform=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
client._statusbasic._clientid,
scantask.taskid,
scantask._platform,
))
result = cursor.fetchall()
if result[0][0] > 0:
res = True
cmd = f'''UPDATE {self._tbname} set
IsPeriod=?,
PeriodNum=?,
StartTime=?,
EndTime=?,
Interval=?,
LastStartTime=?,
LastEndTime=?,
CmdId=?,
Status=?,
Progress=?,
ScanType=?,
Source=?,
UpdateTime=?,
Sequence=?
WHERE ClientId=? and TaskId=? and Platform=?;'''
# and UpdateTime<=? 暂时不判断时间了。因为server端自己也会更新
# 这个UpdateTime字段，中心也会更新这个字段，无法保证任务重新下发的
# 时候的UpdateTime > 当前server更新过的这个UpdateTime，否则会
# 导致明明重新下发了，但server由于任务的UpdateTime小于当前数据库
# 中的UpdateTime而更新任务状态失败。
result = cursor.execute(
cmd,
(
1 if scantask._is_period else 0,
scantask.periodnum,
scantask.cmd.stratagy.time_start,
scantask.cmd.stratagy.time_end,
scantask.cmd.stratagy.interval,
scantask.laststarttime,
scantask.lastendtime,
scantask.cmd_id,
cmdstatus.value,
scantask.progress,
scantask.scantype.value,
scantask.source,
helper_time.get_time_sec_tz(),
0,
client._statusbasic._clientid,
scantask.taskid,
scantask._platform,
# scantask.createtime,
))
# 这句没用，就是调试看看结果..
if result is None or result.rowcount < 1: # or len(result) < 1:
pass
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
# 若没找到，则insert一条到最新的库
# res==True表示至少有一个库里面有一条符合条件的任务，且已更新其字段
if not res:
conn = self.connect_write(5)
try:
# ClientId
# TaskId,
# CmdId,
# Platform,
# Status,
# Progress,
# ScanType,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
# insert
cmd = f'''INSERT INTO {self._tbname}(
ClientId,
TaskId,
CmdId,
Platform,
Status,
Progress,
ScanType,
IsPeriod,
PeriodNum,
StartTime,
EndTime,
Interval,
LastStartTime,
LastEndTime,
Source,
CmdRcvMsg,
CreateTime,
UpdateTime,
Sequence) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'''
# 有几个属于TaskBack的字段在此不更新
cursor = conn.cursor
result = cursor.execute(cmd, (
client._statusbasic._clientid,
scantask.taskid,
scantask.cmd_id,
scantask._platform,
cmdstatus.value,
scantask.progress,
scantask.scantype.value,
1 if scantask._is_period else 0,
scantask.periodnum,
scantask.cmd.stratagy.time_start,
scantask.cmd.stratagy.time_end,
scantask.cmd.stratagy.interval,
scantask.laststarttime,
scantask.lastendtime,
scantask.source,
'',
helper_time.get_time_sec_tz(),
scantask.createtimestr,
0,
))
if result is None or result.rowcount < 1: # or len(result) < 1:
res = False
else:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error(
"save new iscantask error: %s" % traceback.format_exc())
return res
@table_locker(__tb_IScanTask._tbname)
def select_iscantask(self, conds: SqlConditions) -> IscanTask:
"""按条件搜索任务，返回数据行转换成的字段字典"""
conn: SqliteConn = None
cursor = None
conds: SqlConditions = conds
try:
# ClientId
# TaskId,
# CmdId,
# Platform,
# Status,
# Progress,
# ScanType,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
cmd = f'''SELECT
ClientId,
TaskId,
CmdId,
Platform,
Status,
Progress,
ScanType,
IsPeriod,
PeriodNum,
StartTime,
EndTime,
Interval,
LastStartTime,
LastEndTime,
Source,
CmdRcvMsg,
CreateTime,
UpdateTime,
Sequence
FROM {self._tbname} WHERE {conds.text_normal}'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
conn._conn.row_factory = self._dict_factory
try:
cursor = conn.cursor
cursor.execute(cmd, conds.params)
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
fields: dict = {}
for i in range(len(result[0])):
fields[cursor.description[i][0].lower()] = result[0][i]
return fields
except Exception:
self._logger.error("Get IDownCmd error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error(
"Get IScanTask error: %s" % traceback.format_exc())
@table_locker(__tb_IScanTask._tbname)
def select_iscantasks(self, conds: SqlConditions) -> iter:
"""按条件搜索任务，返回数据行转换成的字段字典迭代器"""
conn: SqliteConn = None
cursor = None
conds: SqlConditions = conds
try:
# ClientId
# TaskId,
# CmdId,
# Platform,
# Status,
# Progress,
# ScanType,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
cmd = f'''SELECT
ClientId,
TaskId,
CmdId,
Platform,
Status,
Progress,
ScanType,
IsPeriod,
PeriodNum,
StartTime,
EndTime,
Interval,
LastStartTime,
LastEndTime,
Source,
CmdRcvMsg,
CreateTime,
UpdateTime,
Sequence
FROM {self._tbname} WHERE {conds.text_normal}'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
conn._conn.row_factory = self._dict_factory
try:
cursor = conn.cursor
cursor.execute(cmd, conds.params)
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
for row in result:
fields: dict = {}
for i in range(len(result[0])):
fields[cursor.description[i][0].lower()] = row[i]
yield fields
except Exception:
self._logger.error("Get IScanTask error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error(
"Get IScanTask error: %s" % traceback.format_exc())
@table_locker(__tb_IScanTask._tbname)
def update_iscantask_status(
self,
platform: str,
taskid: str,
cmdstatus: ECommandStatus,
) -> bool:
"""更新cmd的Status状态字段"""
conn: SqliteConn = None
cursor = None
res: bool = False
try:
cmd = f'''UPDATE {self._tbname} set
Status=?
WHERE Platform=? and Taskid=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
result = cursor.execute(cmd, (
cmdstatus.value,
platform,
taskid,
))
if not result is None and result.rowcount > 0:
res = True # 一定只有一个子任务
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update {} Status error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IScanTask._tbname)
def update_iscantask(
self,
scantask: IscanTask,
) -> bool:
"""更新iscantask，条件为platform+taskid，更新其他可更新字段"""
conn: SqliteConn = None
cursor = None
res: bool = False
scantask: IscanTask = scantask
try:
# ClientId
# TaskId,
# CmdId,
# Platform,
# Status,
# Progress,
# ScanType,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
cmd = f'''UPDATE {self._tbname} set
Progress=?,
IsPeriod=?,
PeriodNum=?,
StartTime=?,
EndTime=?,
Interval=?,
LastStartTime=?,
LastEndTime=?,
CmdId=?,
Source=?,
CreateTime=?,
UpdateTime=?
WHERE Platform=? and TaskId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
result = cursor.execute(cmd, (
scantask.progress,
1 if scantask._is_period else 0,
scantask.periodnum,
scantask.cmd.stratagy.time_start,
scantask.cmd.stratagy.time_end,
scantask.cmd.stratagy.interval,
scantask.laststarttime,
scantask.lastendtime,
scantask.cmd_id,
scantask.source,
scantask.createtimestr,
helper_time.get_time_sec_tz(),
scantask._platform,
scantask.taskid,
))
if not result is None and result.rowcount > 0:
res = True
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IScanTask._tbname)
def update_iscantask2(self, platform: str, taskid: str,
updatefields: dict) -> bool:
"""根据taskid+platform更新其他字段\n
task:任务对象"""
res = False
conn: SqliteConn = None
cursor = None
try:
# 搜索每个库，看有没有 TokenId一样的，且时间更新
# 的，一样就更新其他所有字段
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE
TaskId=? and Platform=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (taskid, platform))
result = cursor.fetchall()
if result[0][0] > 0:
# 只根据TaskId、platform作为条件，
# 不考虑 任务文件产生时间与现有数据库中已存在任务的时间，每次直接覆盖，以符合用户操作。
# 若来了TaskId一样的数据，则必然分配给同一个ClientId
sqlset = ''
for k in updatefields.keys():
sqlset = sqlset + '{}=?,'.format(k)
sqlset = sqlset.rstrip(',')
cmd = f'''UPDATE {self._tbname} set {sqlset} WHERE TaskId=? and Platform=?;'''
params = [v for v in updatefields.values()]
params.append(taskid)
params.append(platform)
result = cursor.execute(cmd, params)
if result is None or result.rowcount < 1: # or len(result) < 1:
continue
else:
res = True
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error(
"save new IScanTask error: %s" % traceback.format_exc())
return res
@table_locker(__tb_IScanTask._tbname)
def update_iscantask_back(self, scantaskback: IscanTaskBack) -> bool:
"""更新iscantaskback到数据库，条件为platform+taskid，更新其他可更新字段"""
conn: SqliteConn = None
cursor = None
res: bool = False
scantaskback: IscanTaskBack = scantaskback
try:
if not isinstance(scantaskback,
IscanTaskBack) or scantaskback._platform is None:
raise Exception("Invalid IScanTaskBack obj")
# ClientId
# TaskId,
# CmdId,
# Platform,
# Status,
# Progress,
# ScanType,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
cmd = f'''UPDATE {self._tbname} set
PeriodNum=?,
Status=?,
Progress=?,
CmdRcvMsg=?,
UpdateTime=?,
Sequence=?
WHERE Platform=? and TaskId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
# task:Task = task
result = cursor.execute(cmd, (
scantaskback.periodnum,
scantaskback._cmdstatus.value,
scantaskback.progress,
scantaskback.cmdrcvmsg,
helper_time.get_time_sec_tz(),
scantaskback._sequence,
scantaskback._platform,
scantaskback._taskid,
))
if not result is None and result.rowcount > 0:
res = True
break
else:
self._logger.error(
"IScanTask not found in db:\nplatform={}\ntaskid={}"
.format(scantaskback._platform,
scantaskback.taskid))
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update CmdFeedBack to db {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
class TbIScoutTask(TbSqliteBase):
"""Task表及相关操作"""
__tb_IScoutTask: SqliteTable = SqliteTable(
'IScoutTask',
True,
SqliteColumn(colname='Id',
coltype='INTEGER',
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True).set_index_new(),
SqliteColumn(colname='Platform', nullable=False),
SqliteColumn(colname='TaskId', nullable=False).set_index_new(),
SqliteColumn(colname='IsPeriod',
coltype='INTEGER',
nullable=False,
defaultval=0).set_index_new(),
SqliteColumn(colname='PeriodNum',
coltype='INTEGER',
nullable=False,
defaultval=0).set_index_new(),
SqliteColumn(colname='StartTime', coltype='DATETIME'),
SqliteColumn(colname='EndTime', coltype='DATETIME'),
SqliteColumn(colname='Interval', coltype='REAL'),
SqliteColumn(colname='LastStartTime', coltype='DATETIME'),
SqliteColumn(colname='LastEndTime', coltype='DATETIME'),
SqliteColumn(colname='Status',
coltype='INTEGER',
nullable=False,
description='采集端执行命令的状态，要反给中心的').set_index_new(),
SqliteColumn(
colname='TaskStatus',
coltype='INTEGER',
nullable=False,
description='采集端执行任务的状态（下载状态等），跟CmdStatus不一样').set_index_new(),
SqliteColumn(colname='BatchTotalCount', coltype='INTEGER'),
SqliteColumn(colname='BatchCompleteCount',
coltype='INTEGER').set_index_new(),
SqliteColumn(
colname='Progress', #0~1浮点数表百分比
coltype='REAL',
nullable=True),
SqliteColumn(
colname='Elapsed', #0~1浮点数表百分比
coltype='REAL',
nullable=True),
SqliteColumn(colname='Source'),
SqliteColumn(colname='CmdRcvMsg'),
SqliteColumn(colname='CreateTime',
coltype='DATETIME',
defaultval='1970-01-01 00:00:00'),
SqliteColumn(colname='UpdateTime', coltype='REAL',
nullable=False).set_index_new(),
SqliteColumn(colname='Sequence', coltype='INTEGER',
defaultval=0).set_index_new(),
)
# scantype=scansearch 主键 TaskId
# scantype=scan 主键 TaskId+BatchId
# 所有列，复制粘贴用...：
# Platform,
# TaskId,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime,
# Status,
# TaskStatus,
# BatchTotalCount,
# BatchCompleteCount,
# Progress,
# Elapsed,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
def __init__(self, dbcfg: SqliteConfig):
""""""
TbSqliteBase.__init__(self, TbIScoutTask.__tb_IScoutTask._tbname,
dbcfg)
def _append_tables(self):
self._conn_mngr.append_table(TbIScoutTask.__tb_IScoutTask)
@table_locker(__tb_IScoutTask._tbname)
def save_new_iscouttask(
self,
task: IscoutTask,
cmdstatus: ECommandStatus = ECommandStatus.WaitForSend,
) -> bool:
"""保存新的批处理任务的令牌资源\n
task:任务对象"""
res = False
conn: SqliteConn = None
cursor = None
task: IscoutTask = task
try:
# 搜索每个库，看有没有 TokenId一样的，且时间更新
# 的，一样就更新其他所有字段
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE
TaskId=? and Platform=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
task.taskid,
task._platform,
))
result = cursor.fetchall()
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
if result[0][0] > 0:
res = True
cmd = f'''UPDATE {self._tbname} set
IsPeriod=?,
PeriodNum=?,
StartTime=?,
EndTime=?,
Interval=?,
LastStartTime=?,
LastEndTime=?,
Status=?,
TaskStatus=?,
BatchTotalCount=?,
BatchCompleteCount=?,
Progress=?,
Elapsed=?,
Source=?,
CmdRcvMsg=?,
CreateTime=?,
UpdateTime=?,
Sequence=?
WHERE TaskId=? and Platform=? and CreateTime<=?;'''
result = cursor.execute(
cmd,
(
1 if task._is_period else 0,
task.periodnum,
task.cmd.stratagy.time_start,
task.cmd.stratagy.time_end,
task.cmd.stratagy.interval,
task.laststarttime,
task.lastendtime,
task.cmdstatus.value,
task.taskstatus.value,
task.batchtotalcount,
task.batchcompletecount,
task.progress,
0, # 重置消耗时间
task.source,
task.cmdrcvmsg,
task.createtimestr,
helper_time.get_time_sec_tz(),
0, #重置sequence
task.taskid,
task._platform,
task.createtimestr,
))
# 这句没用，就是调试看看结果..
if result is None or result.rowcount < 1: # or len(result) < 1:
pass
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
# 若没找到，则insert一条到最新的库
# res==True表示至少有一个库里面有一条符合条件的任务，且已更新其字段
if not res:
conn = self.connect_write(5)
try:
# Platform,
# TaskId,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime,
# Status,
# TaskStatus,
# BatchTotalCount,
# BatchCompleteCount,
# Progress,
# Elapsed，
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
# insert
cmd = f'''INSERT INTO {self._tbname}(
Platform,
TaskId,
IsPeriod,
PeriodNum,
StartTime,
EndTime,
Interval,
LastStartTime,
LastEndTime,
Status,
TaskStatus,
BatchTotalCount,
BatchCompleteCount,
Progress,
Elapsed,
Source,
CmdRcvMsg,
CreateTime,
UpdateTime,
Sequence) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'''
# 有几个属于TaskBack的字段在此不更新
cursor = conn.cursor
result = cursor.execute(
cmd,
(
task._platform,
task.taskid,
1 if task._is_period else 0,
task.periodnum,
task.cmd.stratagy.time_start,
task.cmd.stratagy.time_end,
task.cmd.stratagy.interval,
task.laststarttime,
task.lastendtime,
task.cmdstatus.value,
task.taskstatus.value,
task.batchtotalcount,
task.batchcompletecount,
0, # progress
0, # elapsed
task.source,
'',
task.createtimestr,
helper_time.get_time_sec_tz(),
0,
))
if result is None or result.rowcount < 1: # or len(result) < 1:
res = False
else:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("save new IScoutTask error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IScoutTask._tbname)
def select_iscouttask(self, conds: SqlConditions) -> dict:
"""按条件搜索任务，返回数据行转换成的字段字典"""
conn: SqliteConn = None
cursor = None
conds: SqlConditions = conds
try:
cmd = f'''SELECT
Platform,
TaskId,
IsPeriod,
PeriodNum,
StartTime,
EndTime,
Interval,
LastStartTime,
LastEndTime,
Status,
TaskStatus,
BatchTotalCount,
BatchCompleteCount,
Progress,
Elapsed,
Source,
CmdRcvMsg,
CreateTime,
UpdateTime,
Sequence
FROM {self._tbname} WHERE {conds.text_normal}'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
conn._conn.row_factory = self._dict_factory
try:
cursor = conn.cursor
cursor.execute(cmd, conds.params)
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
fields: dict = {}
for i in range(len(result[0])):
fields[cursor.description[i][0].lower()] = result[0][i]
return fields
except Exception:
self._logger.error("Get IScoutTask error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("Get IScanTask error: %s" %
traceback.format_exc())
@table_locker(__tb_IScoutTask._tbname)
def select_iscouttasks(self, conds: SqlConditions) -> iter:
"""按条件搜索任务，返回数据行转换成的字段字典迭代器"""
conn: SqliteConn = None
cursor = None
conds: SqlConditions = conds
try:
# Platform,
# TaskId,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime,
# Status,
# TaskStatus,
# BatchTotalCount,
# BatchCompleteCount,
# Progress,
# Elapsed,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
cmd = f'''SELECT
Platform,
TaskId,
IsPeriod,
PeriodNum,
StartTime,
EndTime,
Interval,
LastStartTime,
LastEndTime,
Status,
TaskStatus,
BatchTotalCount,
BatchCompleteCount,
Progress,
Elapsed,
Source,
CmdRcvMsg,
CreateTime,
UpdateTime,
Sequence
FROM {self._tbname} WHERE {conds.text_normal}'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
conn._conn.row_factory = self._dict_factory
try:
cursor = conn.cursor
cursor.execute(cmd, conds.params)
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
for row in result:
fields: dict = {}
for i in range(len(row)):
fields[cursor.description[i][0].lower()] = row[i]
yield fields
except Exception:
self._logger.error("Get IScoutTask error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("Get IScoutTask error: %s" %
traceback.format_exc())
@table_locker(__tb_IScoutTask._tbname)
def update_iscouttask_status(
self,
platform: str,
taskid: str,
cmdstatus: ECommandStatus,
) -> bool:
"""更新task的Status状态字段"""
conn: SqliteConn = None
cursor = None
res: bool = False
try:
cmd = f'''UPDATE {self._tbname} set
Status=?
WHERE Platform=? and Taskid=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
result = cursor.execute(cmd, (
cmdstatus.value,
platform,
taskid,
))
if not result is None and result.rowcount > 0:
res = True # 一定只有一个子任务
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update {} Status error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IScoutTask._tbname)
def update_iscouttask2(self, task: IscoutTask) -> bool:
"""更新IScoutTask表，根据platform,taskid更新其他所有字段"""
res = False
conn: SqliteConn = None
cursor = None
task: IscoutTask = task
try:
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE Platform=? and TaskId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (task._platform, task.taskid))
result = cursor.fetchall()
# Platform,
# TaskId,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime,
# Status,
# TaskStatus,
# BatchTotalCount,
# BatchCompleteCount,
# Progress,
# Elapsed,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
if result[0][0] > 0:
# 只根据TaskId、platform作为条件，
# 不考虑 任务文件产生时间与现有数据库中已存在任务的时间，每次直接覆盖，以符合用户操作。
# 若来了TaskId一样的数据，则必然分配给同一个ClientId
cmd = f'''UPDATE {self._tbname} set
IsPeriod=?,
PeriodNum=?,
StartTime=?,
EndTime=?,
Interval=?,
LastStartTime=?,
LastEndTime=?,
Status=?,
TaskStatus=?,
BatchTotalCount=?,
BatchCompleteCount=?,
Progress=?,
Elapsed=?,
Source=?,
CmdRcvMsg=?,
Sequence=?,
CreateTime=?,
UpdateTime=? WHERE TaskId=? and Platform=?;'''
result = cursor.execute(cmd, (
1 if task._is_period else 0,
task.periodnum,
task.cmd.stratagy.time_start,
task.cmd.stratagy.time_end,
task.cmd.stratagy.interval,
task.laststarttime,
task.lastendtime,
task.cmdstatus.value,
task.taskstatus.value,
task.batchtotalcount,
task.batchcompletecount,
task.progress,
task.elapsed,
task.source,
task.cmdrcvmsg,
task.sequence,
task.createtimestr,
helper_time.get_time_sec_tz(),
task.taskid,
task.platform,
))
if result is None or result.rowcount < 1: # or len(result) < 1:
continue
else:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("Update IScoutTask error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IScoutTask._tbname)
def update_iscouttask3(self, platform: str, taskid: str,
updatefields: dict) -> bool:
"""更新IScoutTask表，根据platform,taskid更新指定字段"""
res = False
conn: SqliteConn = None
cursor = None
try:
if not isinstance(updatefields, dict) or len(updatefields) < 1:
return True
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE Platform=? and TaskId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (platform, taskid))
result = cursor.fetchall()
if result[0][0] > 0:
# 只根据TaskId、platform作为条件，
# 不考虑 任务文件产生时间与现有数据库中已存在任务的时间，每次直接覆盖，以符合用户操作。
# 若来了TaskId一样的数据，则必然分配给同一个ClientId
sqlset = ''
for k in updatefields.keys():
sqlset = sqlset + '{}=?,'.format(k)
sqlset = sqlset.rstrip(',')
cmd = f'''UPDATE {self._tbname} set {sqlset} WHERE TaskId=? and Platform=?;'''
params = [v for v in updatefields.values()]
params.append(taskid)
params.append(platform)
result = cursor.execute(cmd, params)
if result is None or result.rowcount < 1: # or len(result) < 1:
continue
else:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("Update IScoutTask error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IScoutTask._tbname)
def get_iscouttask_batch_total_count(self, platform: str,
taskid: str) -> int:
"""查询指定task的batchtotalcount，返回-1表示没找到指定的task"""
res: int = -1 #返回-1表示没找到指定的task
conn: SqliteConn = None
cursor = None
try:
cmd = f'''SELECT BatchTotalCount FROM {self._tbname} WHERE Platform=? and TaskId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
platform,
taskid,
))
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
res = result[0][0]
except Exception:
self._logger.error(
"Get IScoutTask batchtotalcount error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("Get IScoutTask batchtotalcount error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IScoutTask._tbname)
def get_iscouttask_batch_complete_count(self, platform: str,
taskid: str) -> int:
"""查询指定task的batchtotalcount，返回-1表示没找到指定的task"""
res: int = -1 #返回-1表示没找到指定的task
conn: SqliteConn = None
cursor = None
try:
cmd = f'''SELECT BatchCompleteCount FROM {self._tbname} WHERE Platform=? and TaskId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
platform,
taskid,
))
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
res = result[0][0]
except Exception:
self._logger.error(
"Get IScoutTask batchcompletecount error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("Get IScoutTask batchcompletecount error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IScoutTask._tbname)
def update_iscouttask_batch_total_count(self, task: IscoutTask) -> bool:
"""更新指定task的batchtotalcount字段，返回bool指示是否成功"""
res: bool = False
conn: SqliteConn = None
cursor = None
try:
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE Platform=? and TaskId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
task._platform,
task.taskid,
))
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
cmd = f'''UPDATE {self._tbname} set BatchTotalCount=? WHERE TaskId=? and Platform=?;'''
result = cursor.execute(cmd, (
task.batchtotalcount,
task.taskid,
task._platform,
))
if result is None or result.rowcount < 1: # or len(result) < 1:
continue
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error(
"Update iscout task batch total count error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IScoutTask._tbname)
def increace_iscouttask_batch_complete_count(self,
platform: str,
taskid: str,
increment: int = 1) -> int:
"""原子操作，提升指定Task的batchcompletecount数量。
task:任务对象\n
increment:在原有基础上要增加多少，默认为1，可以为负数"""
res: bool = False
conn: SqliteConn = None
cursor = None
try:
if not isinstance(increment, int): # 可以为负数
raise Exception(
"Invalid increment count for task batch complete count")
cmd = f'''SELECT BatchCompleteCount FROM {self._tbname} WHERE Platform=? and TaskId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
platform,
taskid,
))
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
src = result[0][0]
target = src + increment
cmd = f'''UPDATE {self._tbname} set BatchCompleteCount=?
WHERE Platform=? and TaskId=?;'''
result = cursor.execute(cmd, (
target,
platform,
taskid,
))
if result is None or result.rowcount < 1:
continue
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error(
"increace_iscouttask_batch_complete_count error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IScoutTask._tbname)
def get_iscouttask_sequence(self, platform: str, taskid: str) -> int:
"""获取指定的总任务的sequence"""
res: int = 0
conn: SqliteConn = None
cursor = None
try:
cmd = f'''SELECT Sequence FROM {self._tbname} WHERE Platform=? and TaskId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
platform,
taskid,
))
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
res = result[0][0]
if not isinstance(res, int):
res = int(res)
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("get_iscouttask_sequence error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IScoutTask._tbname)
def get_iscouttask_elapsed(self, platform: str, taskid: str) -> float:
"""获取指定的总任务的 elapsed"""
res: float = 0
conn: SqliteConn = None
cursor = None
try:
cmd = f'''SELECT Elapsed FROM {self._tbname} WHERE Platform=? and TaskId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
platform,
taskid,
))
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
res = result[0][0]
if type(res) in [int, float]:
res = float(res)
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("Get iscouttask elapsed error: %s" %
traceback.format_exc())
return res
dft_lvl=MsLogLevels.INFO, msficfg=MsFileLogConfig(fi_dir=r'./_serverlog'))
logger = MsLogManager.get_logger("idownserver")
from commonbaby.sql import (SqlConn, SqliteColumn, SqliteConn,
SqliteConnManager, SqliteCursor, SqliteIndex,
SqliteTable, table_locker)
from commonbaby.helpers import helper_time
__locker = threading.RLock()
__locker2 = threading.RLock()
tables: dict = {
"TableA":
SqliteTable(
"TableA",
True,
SqliteColumn("Col1", 'INTEGER', None, False, True, True,
True).set_index_new("Idx1"),
SqliteColumn("Col2", nullable=False, defaultval='DFT'),
SqliteColumn("Col3", 'INTEGER', defaultval=1),
),
}
# tables: dict = {
# "ClientStatus":
# SqliteTable(
# "ClientStatus",
# True,
# SqliteColumn(
# colname="Id",
# coltype='INTEGER',
# nullable=False,
# is_primary_key=True,
class TbIDownBatchToken(TbSqliteBase):
"""Task表及相关操作"""
__tb_IDownBatchToken: SqliteTable = SqliteTable(
'IDownBatchToken',
True,
SqliteColumn(colname='Id',
coltype='INTEGER',
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True).set_index_new(),
SqliteColumn(colname='TokenId', nullable=False).set_index_new(),
SqliteColumn(colname='TokenType', coltype='INTEGER',
nullable=False).set_index_new(),
SqliteColumn(colname='Input'),
SqliteColumn(colname='PreGlobalTelCode'),
SqliteColumn(colname='PreAccount'),
SqliteColumn(colname='GlobalTelCode'),
SqliteColumn(colname='Phone'),
SqliteColumn(colname='Account'),
SqliteColumn(colname='Password'),
SqliteColumn(colname='Url'),
SqliteColumn(colname='Host'),
SqliteColumn(colname='Cookie'),
SqliteColumn(colname='CreateTime',
coltype='DATETIME',
defaultval=helper_time.ts_since_1970_tz()),
SqliteColumn(colname='UpdateTime', coltype='REAL',
nullable=False).set_index_new(),
)
# 所有列，复制粘贴用...：
# TokenId,
# TokenType,
# Input,
# PreGlobalTelCode,
# PreAccount,
# GlobalTelCode,
# Phone,
# Account,
# Password,
# Url,
# Host,
# Cookie,
# UpdateTime,
# CreateTime
def __init__(self, dbcfg: SqliteConfig):
""""""
TbSqliteBase.__init__(self,
TbIDownBatchToken.__tb_IDownBatchToken._tbname,
dbcfg)
def _append_tables(self):
self._conn_mngr.append_table(TbIDownBatchToken.__tb_IDownBatchToken)
@table_locker(__tb_IDownBatchToken._tbname)
def save_new_idownbatchtoken(
self,
task: Task,
) -> bool:
"""保存新的批处理任务的令牌资源\n
task:任务对象"""
res = False
conn: SqliteConn = None
cursor = None
task: Task = task
try:
# 搜索每个库，看有没有 TokenId一样的，且时间更新
# 的，一样就更新其他所有字段
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE
TokenId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (task.tokenid, ))
result = cursor.fetchall()
if result[0][0] > 0:
res = True
# 只根据TaskId、ParentTaskId和ClientId关联，
# 不考虑 任务文件产生时间与现有数据库中已存在任务的时间，每次直接覆盖，以符合用户操作。
# 若来了TaskId一样的数据，则必然分配给同一个ClientId
cmd = f'''UPDATE {self._tbname} set
TokenType=?,
Input=?,
PreGlobalTelCode=?,
PreAccount=?,
GlobalTelCode=?,
Phone=?,
Account=?,
Password=?,
Url=?,
Host=?,
Cookie=?
WHERE TokenId=? and UpdateTime<=?;'''
result = cursor.execute(cmd, (
task.tokentype.value,
task.input,
task.preglobaltelcode,
task.preaccount,
task.globaltelcode,
task.phone,
task.account,
task.password,
task.url,
task.host,
task.cookie,
task.tokenid,
task.time,
))
# 这句没用，就是调试看看结果..
if result is None or result.rowcount < 1: # or len(result) < 1:
pass
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
# 若没找到，则insert一条到最新的库
# res==True表示至少有一个库里面有一条符合条件的任务，且已更新其字段
if not res:
conn = self.connect_write(5)
try:
# insert
cmd = f'''INSERT INTO {self._tbname}(
TokenId,
TokenType,
Input,
PreGlobalTelCode,
PreAccount,
GlobalTelCode,
Phone,
Account,
Password,
Url,
Host,
Cookie,
UpdateTime,
CreateTime) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)'''
# 有几个属于TaskBack的字段在此不更新
cursor = conn.cursor
result = cursor.execute(cmd, (
task.tokenid,
task.tokentype.value,
task.input,
task.preglobaltelcode,
task.preaccount,
task.globaltelcode,
task.phone,
task.account,
task.password,
task.url,
task.host,
task.cookie,
task.time,
helper_time.ts_since_1970_tz(),
))
if result is None or result.rowcount < 1: # or len(result) < 1:
res = False
else:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("save_new_idownbatchtask error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IDownBatchToken._tbname)
def select_token(self, conds: SqlConditions) -> iter:
"""按条件搜索任务，返回数据行转换成的字段字典迭代器"""
conn: SqliteConn = None
cursor = None
conds: SqlConditions = conds
try:
cmd = f'''SELECT
TokenId,
TokenType,
Input,
PreGlobalTelCode,
PreAccount,
GlobalTelCode,
Phone,
Account,
Password,
Url,
Host,
Cookie,
UpdateTime,
CreateTime FROM {self._tbname} WHERE {conds.text_normal}'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
conn._conn.row_factory = self._dict_factory
try:
cursor = conn.cursor
cursor.execute(cmd, conds.params)
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
for row in result:
# if len(row) != 15:
# continue
fields: dict = {}
for i in range(len(row)):
fields[cursor.description[i][0].lower()] = row[i]
yield fields
except Exception:
self._logger.error("save_idown_task error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("Save new idown_task error: %s" %
traceback.format_exc())
@table_locker(__tb_IDownBatchToken._tbname)
def select_token_one(self, conds: SqlConditions) -> dict:
"""按条件搜索任务，返回数据行转换成的字段字典"""
for fields in self.select_token(conds):
return fields
class TbIDownCmd(TbSqliteBase):
"""Task表及相关操作"""
__tb_IDownCmd: SqliteTable = SqliteTable(
'IDownCmd',
True,
SqliteColumn(colname='Id',
coltype='INTEGER',
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True).set_index_new(),
SqliteColumn(colname='ClientId').set_index_new(),
SqliteColumn(colname='CmdId', nullable=False).set_index_new(),
SqliteColumn(colname='Platform', nullable=False).set_index_new(),
SqliteColumn(colname='TaskId').set_index_new(),
SqliteColumn(colname='BatchId').set_index_new(),
SqliteColumn(colname='Cmd', nullable=False).set_index_new(),
SqliteColumn(colname='Status', coltype='INTEGER',
nullable=False).set_index_new(),
SqliteColumn(colname='Source').set_index_new(),
SqliteColumn(colname='CmdRcvMsg'),
SqliteColumn(colname='CreateTime',
coltype='DATETIME',
defaultval=helper_time.ts_since_1970_tz()),
SqliteColumn(colname='UpdateTime', coltype='REAL',
nullable=False).set_index_new(),
SqliteColumn(colname='Sequence',
nullable=False,
coltype='INTEGER',
defaultval=0).set_index_new(),
SqliteColumn(
# 指示当前cmd是否是与任何task关联的，0独立的/1关联的
colname='IsRelative',
nullable=False,
coltype='INTEGER',
defaultval=1).set_index_new(),
)
# 主键 ClientId+CmdId
# 所有列，复制粘贴用...：
# ClientId
# CmdId,
# Platform,
# TaskId,
# BatchId,
# Cmd,
# Status,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
# IsRelative
def __init__(self, dbcfg: SqliteConfig):
""""""
TbSqliteBase.__init__(self, TbIDownCmd.__tb_IDownCmd._tbname, dbcfg)
def _append_tables(self):
self._conn_mngr.append_table(TbIDownCmd.__tb_IDownCmd)
@table_locker(__tb_IDownCmd._tbname)
def save_new_idowncmd(
self,
platform: str,
client: Client,
command: IdownCmd,
cmdtime: float,
cmdstatus: ECommandStatus = ECommandStatus.WaitForSend,
taskid: str = None,
batchid: str = None,
isrelative: int = 1,
) -> bool:
"""保存新的批处理任务的令牌资源\n
task:任务对象"""
res = False
conn: SqliteConn = None
cursor = None
client: Client = client
command: IdownCmd = command
try:
# 搜索每个库，看有没有 TokenId一样的，且时间更新
# 的，一样就更新其他所有字段
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE
ClientId=? and CmdId=? and Platform=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
client._statusbasic._clientid,
command.cmd_id,
platform,
))
result = cursor.fetchall()
if result[0][0] > 0:
res = True
cmd = f'''UPDATE {self._tbname} set
Cmd=?,
TaskId=?,
BatchId=?,
Status=?,
Source=?,
CreateTime=?,
UpdateTime=?,
IsRelative=?
WHERE ClientId=? and CmdId=? and Platform=? and CreateTime<=?;'''
result = cursor.execute(cmd, (
command.cmd_str,
taskid,
batchid,
cmdstatus.value,
command.source,
helper_time.timespan_to_datestr_tz(cmdtime),
helper_time.get_time_sec_tz(),
isrelative,
client._statusbasic._clientid,
command.cmd_id,
platform,
helper_time.timespan_to_datestr_tz(cmdtime),
))
# 这句没用，就是调试看看结果..
if result is None or result.rowcount < 1: # or len(result) < 1:
pass
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
# 若没找到，则insert一条到最新的库
# res==True表示至少有一个库里面有一条符合条件的任务，且已更新其字段
if not res:
conn = self.connect_write(5)
try:
# ClientId
# CmdId,
# Platform,
# TaskId,
# BatchId,
# Cmd,
# Status,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence
# IsRelative
# insert
cmd = f'''INSERT INTO {self._tbname}(
ClientId,
CmdId,
Platform,
TaskId,
BatchId,
Cmd,
Source,
Status,
CreateTime,
UpdateTime,
Sequence,
IsRelative) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)'''
# 有几个属于TaskBack的字段在此不更新
cursor = conn.cursor
result = cursor.execute(cmd, (
client._statusbasic._clientid,
command.cmd_id,
platform,
taskid,
batchid,
command.cmd_str,
command.source,
cmdstatus.value,
helper_time.timespan_to_datestr_tz(cmdtime),
helper_time.get_time_sec_tz(),
0,
isrelative,
))
if result is None or result.rowcount < 1: # or len(result) < 1:
res = False
else:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("save_new_idowncmd error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IDownCmd._tbname)
def select_cmd(self, conds: SqlConditions) -> IdownCmd:
"""按条件搜索任务，返回数据行转换成的字段字典迭代器"""
conn: SqliteConn = None
cursor = None
conds: SqlConditions = conds
try:
cmd = f'''SELECT
ClientId,
CmdId,
Platform,
TaskId,
BatchId,
Cmd,
Status,
Source,
CmdRcvMsg,
CreateTime,
UpdateTime,
Sequence,
IsRelative
FROM {self._tbname} WHERE {conds.text_normal}'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
conn._conn.row_factory = self._dict_factory
try:
cursor = conn.cursor
cursor.execute(cmd, conds.params)
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
fields: dict = {}
for i in range(len(result[0])):
fields[cursor.description[i][0].lower()] = result[0][i]
return fields
except Exception:
self._logger.error("Get IDownCmd error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("Get IDownCmd error: %s" %
traceback.format_exc())
@table_locker(__tb_IDownCmd._tbname)
def select_cmds(self, conds: SqlConditions) -> iter:
"""按条件搜索任务，返回数据行转换成的字段字典迭代器"""
conn: SqliteConn = None
cursor = None
conds: SqlConditions = conds
try:
cmd = f'''SELECT
ClientId,
CmdId,
Platform,
TaskId,
BatchId,
Cmd,
Status,
Source,
CmdRcvMsg,
CreateTime,
UpdateTime,
Sequence,
IsRelative
FROM {self._tbname} WHERE {conds.text_normal}'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
conn._conn.row_factory = self._dict_factory
try:
cursor = conn.cursor
cursor.execute(cmd, conds.params)
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
for row in result:
# if len(row) != 15:
# continue
fields: dict = {}
for i in range(len(row)):
fields[cursor.description[i][0].lower()] = row[i]
yield fields
except Exception:
self._logger.error("save_idown_task error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("Get IDownCmd error: %s" %
traceback.format_exc())
@table_locker(__tb_IDownCmd._tbname)
def update_cmd_status(self, platform: str, cmdid: str, clientid: str,
cmdstatus: ECommandStatus) -> bool:
"""更新cmd的Status状态字段"""
conn: SqliteConn = None
cursor = None
res: bool = False
try:
cmd = f'''UPDATE {self._tbname} set
Status=?
WHERE Platform=? and CmdId=? and ClientId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
result = cursor.execute(
cmd, (cmdstatus.value, platform, cmdid, clientid))
if not result is None and result.rowcount > 0:
res = True # 一定只有一个子任务
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update {} Status error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IDownCmd._tbname)
def update_cmd(
self,
cmd: IdownCmd,
taskid: str = None,
batchid: str = None,
isrelative: int = 1,
) -> bool:
"""更新cmd，条件为platform+cmdid，更新其他可更新字段"""
conn: SqliteConn = None
cursor = None
res: bool = False
cmd: IdownCmd = cmd
try:
# ClientId
# CmdId,
# Platform,
# TaskId,
# BatchId,
# Cmd,
# Status,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
# IsRelative
cmd = f'''UPDATE {self._tbname} set
TaskId=?,
BatchId=?,
Cmd=?,
Status=?,
Source=?,
CmdRcvMsg=?,
CreateTime=?,
UpdateTime=?,
Sequence=?,
IsRelative=?
WHERE Platform=? and CmdId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
# task:Task = task
result = cursor.execute(
cmd,
(
taskid,
batchid,
cmd.cmd_str,
cmd.cmdstatus.value,
cmd.source,
cmd.cmdrcvmsg,
# 假设数据是按时间先后顺序过来的，所以createtime直接取本地时间。
# 若后面有问题，需要加idowncmd命令文件字段createtime字段
helper_time.get_time_sec_tz(),
helper_time.get_time_sec_tz(),
cmd._sequence,
isrelative,
cmd._platform,
cmd.cmd_id,
))
if not result is None and result.rowcount > 0:
res = True
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IDownCmd._tbname)
def update_cmdback(self, cmdback: CmdFeedBack) -> bool:
"""更新cmdback到数据库，条件为clientid+cmdid(+platform?)，更新其他可更新字段"""
conn: SqliteConn = None
cursor = None
res: bool = False
cmdback: CmdFeedBack = cmdback
try:
if cmdback is None or cmdback._platform is None:
raise Exception("Invalid cmdback obj")
# ClientId
# CmdId,
# Platform,
# TaskId,
# BatchId,
# Cmd,
# Status,
# Source,
# CmdRcvMsg,
# CreateTime,
# UpdateTime,
# Sequence,
# IsRelative
cmd = f'''UPDATE {self._tbname} set
Status=?,
CmdRcvMsg=?,
UpdateTime=?,
Sequence=?
WHERE ClientId=? and CmdId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
# task:Task = task
result = cursor.execute(cmd, (
cmdback._cmdstatus.value,
cmdback._cmdrcvmsg,
helper_time.get_time_sec_tz(),
cmdback._sequence,
cmdback.clientid,
cmdback.cmd_id,
))
if not result is None and result.rowcount > 0:
res = True
break
else:
self._logger.error(
"Cmd not found in db:\ncmdid={}\nclientid={}".
format(cmdback.cmd_id, cmdback.clientid))
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update CmdFeedBack to db {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
class TbTaskCmd(TbSqliteBase):
__tb_task_cmd: SqliteTable = SqliteTable(
"idowncmd",
True,
SqliteColumn(
colname="ID",
coltype="INTEGER",
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True,
).set_index_new(),
SqliteColumn(colname="cmdid"),
SqliteColumn(colname="platform", defaultval="zplus"),
SqliteColumn(colname="default_value", coltype="INT", length=5),
SqliteColumn(colname="cmd", nullable=False),
)
databasename = "task"
def __init__(self, dbcfg: SqliteConfig):
TbSqliteBase.__init__(self, TbTaskCmd.__tb_task_cmd._tbname, dbcfg,
TbTaskCmd.databasename)
# 初始化自定义下载策略的默认值
self.cmd_str = json.dumps(cmd_dict)
self._initial_default_confg()
def _append_tables(self):
self._conn_mngr.append_table(TbTaskCmd.__tb_task_cmd)
@table_locker(__tb_task_cmd._tbname)
def _initial_default_confg(self):
"""
初始化cmd，目前来看三个地方都使用了cmd
但是只有idown目前有修改全局的功能，所以为了不互相影响需要放3份
"""
# 只需要查询一个是否有
if self.get_default_idown_cmd() is not None:
return
conn: SqliteConn = None
sql = """
INSERT INTO idowncmd(cmdid, default_value, cmd)
VALUES (?, ?, ?);
"""
params = [
("iscan", 1, self.cmd_str),
("iscout", 1, self.cmd_str),
("idown", 1, self.cmd_str),
]
# params = ('iscan', 1, self.cmd_str)
try:
conn = self.connect_write(5)
c = conn.cursor
c.executemany(sql, params)
# c.execute(sql, params)
except Exception:
self._logger.error(
f"There was a problem inserting default cmd data, err:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.commit()
conn.close()
return
@table_locker(__tb_task_cmd._tbname)
def get_default_cmd(self, cmdid, default_value=1):
"""
获取已存储的默认配置
:return:
"""
res = None
conn: SqliteConn = None
sql = """
SELECT * FROM idowncmd
WHERE cmdid=? and default_value=?;
"""
params = (cmdid, default_value)
try:
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
conn._conn.row_factory = self._dict_factory
c = conn._conn.cursor()
c.execute(sql, params)
res_data = c.fetchall()
if len(res_data) > 0:
res = res_data[0]
break
except Exception as ex:
conn._conn.rollback()
raise ex
finally:
if conn is not None:
conn.close()
except Exception:
self._logger.error(
f"Query default idown cmd error,err:{traceback.format_exc()}.")
finally:
if conn is not None:
conn.close()
return res
def get_default_idown_cmd(self):
"""
只获取idown的默认cmd
modify by judy 2020/08/12
"""
res = self.get_default_cmd("idown")
return res
def get_default_iscan_cmd(self):
"""
只获取iscan的默认cmd
modify by judy 2020/08/12
"""
res = self.get_default_cmd("iscan")
return res
def get_default_iscout_cmd(self):
"""
只获取iscout的默认cmd
modify by judy 2020/08/12
"""
res = self.get_default_cmd("iscout")
return res
@table_locker(__tb_task_cmd._tbname)
def update_cmd_by_cmdid(self, cmdid, cmdr: str):
"""
根据cmdid更新cmd
:param cmdid:
:param cmdr:
:return:
"""
res = False
sql = """
UPDATE idowncmd SET cmd=? WHERE cmdid=?
"""
pars = (cmdr, cmdid)
conn: SqliteConn = None
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, pars)
if (result is not None
and result.rowcount > 0): # or len(result) < 1:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(
f"Update default cmd error,err:{traceback.format_exc()}.")
finally:
if conn is not None:
conn.close()
return
def _update_the_same_cmdid(self, cmdid, cmd_str: str):
"""
更新数据库中相同的cmd
:param cmdid:
:param cmd_str:
:return:
"""
res = False
repeat_data = self.query_cmd_by_cmdid(cmdid)
if repeat_data is not None:
# 表示数据库中已经存储了这个设置，那么就更新数据即可
# fill_dict = self._filling_default_cmd(json.loads(cmd_str), json.loads(repeat_data.get('cmd')))
self.update_cmd_by_cmdid(cmdid, cmd_str)
res = True
return res
@table_locker(__tb_task_cmd._tbname)
def store_task_cmd(self, cmdid, cmd_str):
"""
补齐默认配置，存储cmd数据
:param cmdid:
:param cmd_str:
:return:
"""
# 更新相同的cmdid的信息
repeat = self._update_the_same_cmdid(cmdid, cmd_str)
if repeat:
# 如果更新了即可
return
sql = """
INSERT INTO idowncmd(cmdid, cmd)
VALUES (?, ?)
"""
# cmd_dict = json.loads(cmd_str)
# 补齐默认配置
# fill_dict = self._filling_default_cmd(cmd_dict)
pars = (cmdid, cmd_str)
conn: SqliteConn = None
try:
conn = self.connect_write(5)
c = conn.cursor
c.execute(sql, pars)
except Exception:
self._logger.error(
f"There was a problem inserting data, err:{traceback.format_exc()}."
)
finally:
if conn is not None:
conn.commit()
conn.close()
return
@table_locker(__tb_task_cmd._tbname)
def query_cmd_by_cmdid(self, cmdid):
"""
根据cmdid查询默认的配置
有结果返回{}
没有结果返回None
:return:
"""
conn: SqliteConn = None
sql = """
SELECT * FROM idowncmd
WHERE cmdid=?;
"""
pars = (cmdid, )
try:
for conn in self.connect_all(5):
try:
conn._conn.row_factory = self._dict_factory
c = conn._conn.cursor()
c.execute(sql, pars)
res: list = c.fetchall()
if len(res) > 0:
return res[0]
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
except:
self._logger.error(
f"There was a problem query cmd by cmdid\nerr:{traceback.format_exc()}."
)
finally:
if conn is not None:
conn.close()
return
def _filling_default_idown_cmd(self, icmd: IdownCmd) -> str:
"""
在存储默认配置的时候可能需要补齐一些默认配置
补齐默认的配置后再存储
:param icmd:
:return:
"""
default_cmd: str = self.get_default_idown_cmd().get("cmd")
dcmd = IdownCmd(default_cmd)
# 如果任务自己带有一些设置，那么补齐一些配置即可,只要调用了fill那么cmd就会是完整的
icmd.fill_defcmd(dcmd)
return icmd.filled_cmd_str
@table_locker(__tb_task_cmd._tbname)
def update_default_idown_cmd(self, icmd: IdownCmd):
"""
修改默认的cmd,会自动找寻数据库中最新的cmd补齐
:param icmd:
:return:
"""
res = False
sql = """
UPDATE idowncmd SET cmd=? WHERE cmdid=? and default_value=?
"""
# cmd_dict = json.loads(new_cmdr)
# 补齐默认配置
new_cmdr = self._filling_default_idown_cmd(icmd)
pars = (new_cmdr, "idown", 1)
conn: SqliteConn = None
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, pars)
if (result is not None
and result.rowcount > 0): # or len(result) < 1:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(
f"Update default cmd error,err:{traceback.format_exc()}.")
finally:
if conn is not None:
conn.close()
return
class TbIDownBatchTask(TbSqliteBase):
"""Task表及相关操作"""
__tb_IDownBatchTask: SqliteTable = SqliteTable(
'IDownBatchTask',
True,
SqliteColumn(colname='Id',
coltype='INTEGER',
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True).set_index_new(),
SqliteColumn(colname='Platform', nullable=False).set_index_new(),
SqliteColumn(colname='TaskId', nullable=False).set_index_new(),
SqliteColumn(colname='BatchId', nullable=False).set_index_new(),
SqliteColumn(colname='ParentBatchId', nullable=True).set_index_new(),
SqliteColumn(colname='ClientId', nullable=False).set_index_new(),
SqliteColumn(colname='TokenId', coltype='INTEGER',
nullable=False).set_index_new(),
SqliteColumn(colname='AppType', coltype='INTEGER').set_index_new(),
SqliteColumn(colname='ForceDownload', coltype='INTEGER',
defaultval=0).set_index_new(),
SqliteColumn(colname='CmdRcvMsg'),
SqliteColumn(colname='Result'),
SqliteColumn(colname='CmdStatus', coltype='INTEGER',
nullable=False).set_index_new(),
SqliteColumn(
colname='Progress', #0~1浮点数表百分比
coltype='REAL',
defaultval=0).set_index_new(),
SqliteColumn(colname='Sequence', coltype='INTEGER',
defaultval=0).set_index_new(),
SqliteColumn(colname='OtherFields').set_index_new(),
SqliteColumn(colname='CreateTime',
coltype='DATETIME',
defaultval=helper_time.ts_since_1970_tz()),
SqliteColumn(colname='UpdateTime', coltype='REAL',
nullable=False).set_index_new(),
SqliteColumn(
# 此字段仅在查询 或 操作IDownTask表的 BatchCompleteCount 字段时使用，
# 其他时候不要使用
colname='IsBatchCompleteCountIncreased',
coltype='BOOLEAN',
nullable=False,
defaultval=0),
SqliteColumn(colname="Source"), # 新增
SqliteColumn(colname="CmdId"), #cmdid字段要更新
)
# 所有列，复制粘贴用...：
# Platform,
# TaskId,
# Batchid,
# ParentBatchId,
# ClientId,
# TokenId,
# AppType,
# ForceDownload,
# CmdRcvMsg,
# Result,
# CmdStatus,
# Progress,
# Sequence,
# OtherFields,
# CreateTime,
# UpdateTime,
# IsBatchCompleteCountIncreased,
# Source,
# CmdId
def __init__(self, dbcfg: SqliteConfig):
""""""
TbSqliteBase.__init__(self,
TbIDownBatchTask.__tb_IDownBatchTask._tbname,
dbcfg)
def _append_tables(self):
self._conn_mngr.append_table(TbIDownBatchTask.__tb_IDownBatchTask)
@table_locker(__tb_IDownBatchTask._tbname)
def save_new_idownbatchtask(
self,
task: Task,
client: Client,
cmdstatus: ECommandStatus = ECommandStatus.WaitForSend
) -> Tuple[bool, bool]:
"""保存新的批处理任务的子任务\n
task: 任务对象\n
tokenid：当前批处理子任务对应的登陆令牌存储在IDownBatchTokens表中的tokenid，关联存储\n
client: 当前批处理子任务被分配到的client\n
cmdstatus: 指定当前批处理子任务的命令状态\n
return: （bool是否成功，bool是否为新增(用于计算batchtotalcount)）"""
res: bool = False
isnew: bool = False
conn: SqliteConn = None
cursor = None
client: Client = client
task: Task = task
try:
if not isinstance(task.tokenid, str) or task.tokenid == "":
raise Exception(
"Invalid tokenid while save new idownbatchtask:\ntaskid:{}"
.format(task.taskid))
# 搜索每个库，看有没有 taskid和clientid一样，且时间更新
# 的，一样就更新其他所有字段
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE
Platform=? and TaskId=? and BatchId=? and ClientId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
task._platform,
task.taskid,
task.batchid,
client._statusbasic._clientid,
))
result = cursor.fetchall()
if result[0][0] > 0:
res = True
# 只根据TaskId、ParentTaskId和ClientId关联，
# 不考虑 任务文件产生时间与现有数据库中已存在任务的时间，每次直接覆盖，以符合用户操作。
# 若来了TaskId一样的数据，则必然分配给同一个ClientId
cmd = f'''UPDATE {self._tbname} set
TokenId=?,
AppType=?,
ForceDownload=?,
CmdRcvMsg=?,
Result=?,
CmdStatus=?,
Progress=?,
Sequence=?,
OtherFields=?,
CreateTime=?,
UpdateTime=?,
Source=?,
CmdId=?
WHERE Platform=? and TaskId=? and BatchId=? and ClientId=? and CreateTime<=?;'''
result = cursor.execute(
cmd,
(
task.tokenid,
task.apptype,
task.forcedownload,
task._cmdrcvmsg,
task._result,
cmdstatus.value,
# 进度置零
0,
#批量任务完成数也要重置，因为重置了任务下发状态，变为新任务，需要重新执行
0, #sequence重置
task.other_fields_json,
task.timestr,
helper_time.get_time_sec_tz(),
task.source,
task.cmd_id,
# 0 此字段在其他地方重置 #是否总任务已提升batchcompletecount置零，
task._platform,
task.taskid,
task.batchid,
client._statusbasic._clientid,
task.timestr,
))
# 这句没用，就是调试看看结果..
if result is None or result.rowcount < 1: # or len(result) < 1:
pass
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
# 若没找到，则insert一条到最新的库
# res==True表示至少有一个库里面有一条符合条件的任务，且已更新其字段
if not res:
isnew = True
conn = self.connect_write(5)
try:
# insert
# Platform,
# TaskId,
# Batchid,
# ParentBatchId,
# ClientId,
# TokenId,
# AppType,
# ForceDownload,
# CmdRcvMsg,
# Result,
# CmdStatus,
# Progress,
# Sequence,
# OtherFields,
# CreateTime,
# UpdateTime
# IsBatchCompleteCountIncreased
cmd = f'''INSERT INTO {self._tbname}(
Platform,
TaskId,
Batchid,
ParentBatchId,
ClientId,
TokenId,
AppType,
ForceDownload,
CmdRcvMsg,
Result,
CmdStatus,
Progress,
Sequence,
OtherFields,
CreateTime,
UpdateTime,
Source,
CmdId) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'''
# 有几个属于TaskBack的字段在此不更新
cursor = conn.cursor
result = cursor.execute(
cmd,
(
task._platform,
task.taskid,
task.batchid,
task.parentbatchid,
client._statusbasic._clientid,
task.tokenid,
task.apptype,
task.forcedownload,
task._cmdrcvmsg,
task._result,
cmdstatus.value,
0, # progress初始化为0
0, #sequence初始化为0
task.other_fields_json,
task.timestr,
helper_time.get_time_sec_tz(),
task.source,
task.cmd_id))
if result is None or result.rowcount < 1: # or len(result) < 1:
res = False
else:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("save_new_idownbatchtask error: %s" %
traceback.format_exc())
return (res, isnew)
@table_locker(__tb_IDownBatchTask._tbname)
def get_parent_clientid_of_task(self, task: Task) -> str:
"""获取 指定task的被分配到的采集端 Client对象"""
res: str = None
conn: SqliteConn = None
cursor = None
task: Task = task
try:
if helper_str.is_none_or_empty(task.parenttaskid):
raise Exception(
"Invalid task parent_taskid for task, taskid={} batchid={}"
.format(task.taskid, task.batchid))
if helper_str.is_none_or_empty(task.parentbatchid):
raise Exception(
"Invalid task parent_batchid for task, taskid={} batchid={}"
.format(task.taskid, task.batchid))
# 搜索每个库，看有没有 taskid和clientid一样，且时间更新
# 的，一样就更新其他所有字段
cmd = f'''SELECT ClientId FROM {self._tbname} WHERE
Platform=? and TaskId=? and BatchId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
task._platform,
task.parenttaskid,
task.parentbatchid,
))
result = cursor.fetchall()
if result is None or len(result) < 1 or len(result[0]) < 1:
continue
else:
res = result[0][0]
break
except Exception:
self._logger.error(
"get_parent_client_of_task error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
if not res is None:
break
except Exception:
self._logger.error("get_parent_client_of_task error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IDownBatchTask._tbname)
def get_batch_task(self, conds: SqlConditions) -> dict:
"""按条件搜索指定的一个任务，返回数据行转换成的字段字典迭代器"""
conn: SqliteConn = None
cursor = None
conds: SqlConditions = conds
try:
cmd = f'''SELECT
Platform,
TaskId,
Batchid,
ParentBatchId,
ClientId,
TokenId,
AppType,
ForceDownload,
CmdRcvMsg,
Result,
CmdStatus,
Progress,
Sequence,
OtherFields,
CreateTime,
UpdateTime,
IsBatchCompleteCountIncreased,
Source,
CmdId FROM {self._tbname} WHERE {conds.text_normal}'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
conn._conn.row_factory = self._dict_factory
# conn._conn.text_factory = self._text_factory
try:
cursor = conn.cursor
cursor = cursor.execute(cmd, conds.params)
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
fields: dict = {}
for i in range(len(result[0])):
fields[cursor.description[i][0].lower()] = result[0][i]
return fields
except Exception:
self._logger.error(
"Get BatchTask from {} error: {}".format(
self._tbname, traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("Get BatchTask {} error: {}".format(
self._tbname, traceback.format_exc()))
@table_locker(__tb_IDownBatchTask._tbname)
def get_batch_tasks(self, conds: SqlConditions) -> iter:
"""按条件搜索任务，返回数据行转换成的字段字典迭代器"""
conn: SqliteConn = None
cursor = None
conds: SqlConditions = conds
try:
cmd = f'''SELECT
Platform,
TaskId,
Batchid,
ParentBatchId,
ClientId,
TokenId,
AppType,
ForceDownload,
CmdRcvMsg,
Result,
CmdStatus,
Progress,
Sequence,
OtherFields,
CreateTime,
UpdateTime,
IsBatchCompleteCountIncreased,
Source,
CmdId FROM {self._tbname} WHERE {conds.text_normal}'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
conn._conn.row_factory = self._dict_factory
# conn._conn.text_factory = self._text_factory
try:
cursor = conn.cursor
cursor = cursor.execute(cmd, conds.params)
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
for row in result:
# if len(row) != 15:
# continue
fields: dict = {}
for i in range(len(row)):
fields[cursor.description[i][0].lower()] = row[i]
yield fields
except Exception:
self._logger.error(
"Get BatchTasks from {} error: {}".format(
self._tbname, traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("Get BatchTasks {} error: {}".format(
self._tbname, traceback.format_exc()))
@table_locker(__tb_IDownBatchTask._tbname)
def get_batch_task_count_by_cmdstatus(self, task: Task,
cmdstatus: ECommandStatus) -> int:
"""查询指定总任务taskid下的所有 为指定命令状态的子任务 的数量"""
res: int = 0 #总数量
conn: SqliteConn = None
cursor = None
try:
cmd = f'''SELECT COUNT() FROM {self._tbname} WHERE Platform=? and TaskId=? and CmdStatus=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
task._platform,
task.taskid,
cmdstatus.value,
))
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
tmp = result[0][0]
res = res + tmp
except Exception:
self._logger.error("Select {} error: {}".format(
self._tbname, traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("Save new {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IDownBatchTask._tbname)
def update_batchtask(self, task: Task) -> bool:
"""使用指定task对象更新IDownBatchTask表，
不更新 isbatchcompletecountincreased\n
task: 表示一个子任务对象"""
conn: SqliteConn = None
cursor = None
res: bool = False
try:
task._cmdstatus
cmd = f'''UPDATE {self._tbname} set
ClientId=?,
ParentBatchId=?,
TokenId=?,
AppType=?,
ForceDownload=?,
CmdRcvMsg=?,
Result=?,
CmdStatus=?,
Progress=?,
Sequence=?,
OtherFields=?,
CreateTime=?,
UpdateTime=?,
Source=?,
CmdId=?
WHERE Platform=? and TaskId=? and BatchId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
# task:Task = task
result = cursor.execute(cmd, (
task._clientid,
task.parentbatchid,
task.tokenid,
task.apptype,
task.forcedownload,
task._cmdrcvmsg,
task._result,
task._cmdstatus.value,
task.progress,
task._sequence,
task.other_fields_json,
task.timestr,
helper_time.get_time_sec_tz(),
task.source,
task.cmd_id,
task._platform,
task.taskid,
task.batchid,
))
if not result is None and result.rowcount > 0:
res = True # 一定只有一个子任务
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IDownBatchTask._tbname)
def update_batchtask_status(self, platform: str, taskid: str, batchid: str,
cmdstatus: ECommandStatus) -> bool:
"""使用指定task对象更新IDownBatchTask表，
不更新 isbatchcompletecountincreased\n
task: 表示一个子任务对象"""
conn: SqliteConn = None
cursor = None
res: bool = False
try:
cmd = f'''UPDATE {self._tbname} set
CmdStatus=?
WHERE Platform=? and TaskId=? and BatchId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
# task:Task = task
result = cursor.execute(cmd, (
cmdstatus.value,
platform,
taskid,
batchid,
))
if not result is None and result.rowcount > 0:
res = True # 一定只有一个子任务
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IDownBatchTask._tbname)
def update_batchtask_client(self, platform: str, taskid: str, batchid: str,
clientid: str) -> bool:
"""更新batchtask被分配到的采集端"""
conn: SqliteConn = None
cursor = None
res: bool = False
try:
if not isinstance(clientid, str) or clientid == "":
raise Exception(
"Invalid clientid for update_batchtask_clientid")
cmd = f'''UPDATE {self._tbname} set
ClientId=?
WHERE Platform=? and TaskId=? and BatchId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
# task:Task = task
result = cursor.execute(cmd, (
clientid,
platform,
taskid,
batchid,
))
if not result is None and result.rowcount > 0:
res = True # 一定只有一个子任务
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error(
"update batchtask client to {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IDownBatchTask._tbname)
def update_batchtask_back(self, tb: TaskBatchBack) -> bool:
"""使用指定 TaskBatchBack 对象更新IDownBatchTask表，
不更新 isbatchcompletecountincreased\n"""
conn: SqliteConn = None
cursor = None
res: bool = False
tb: TaskBatchBack = tb
try:
if not isinstance(tb, TaskBatchBack):
self._logger.error(
"Invalid param TaskBatchBack: {}".format(tb))
return res
# Platform
# TaskId,
# Batchid,
# ClientId,
# TokenId,
# AppType,
# CmdRcvMsg,
# Result,
# CmdStatus,
# Progress,
# Sequence,
# OtherFields,
# CreateTime,
# UpdateTime,
# IsBatchCompleteCountIncreased
# CmdId
# 更新策略，先搜一下有没有，并把sequence搜出来，如果
# 本地sequence和新来的任务的sequence一样，则说明
# 采集端的sequence出错了，打一句日志并返回False
cmd = f'''SELECT Sequence FROM {self._tbname} WHERE Platform=? and TaskId=? and BatchId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
tb._platform,
tb._taskid,
tb._batchid,
))
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
oldseq = result[0][0]
if oldseq >= tb._sequence:
self._logger.error(
"The comming TaskBatchBack.sequence is {}, but which in local db is {}:\ntaskid:{}\nbatchid:{}\nsequence:{}"
.format(tb._sequence, oldseq, tb._taskid,
tb._batchid, oldseq))
break
cmd = f'''UPDATE {self._tbname} set
CmdRcvMsg=?,
Result=?,
CmdStatus=?,
Progress=?,
Sequence=?,
UpdateTime=?
WHERE Platform=? and TaskId=? and BatchId=? and Sequence<?;'''
cursor = conn.cursor
result = cursor.execute(cmd, (
tb._cmdrcvmsg,
tb._result,
tb._cmdstatus.value,
tb._progress,
tb._sequence,
helper_time.get_time_sec_tz(),
tb._platform,
tb._taskid,
tb._batchid,
tb._sequence,
))
if not result is None and result.rowcount > 0:
res = True # 一定只有一个子任务
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IDownBatchTask._tbname)
def is_batch_complete_count_increaced(self, platform: str, taskid: str,
batchid: str) -> bool:
"""返回 指定的子任务完成情况 是否已更新到 总任务表的 batchcompletecount 字段"""
conn: SqliteConn = None
cursor = None
res: bool = False
try:
# IsBatchCompleteCountIncreased
cmd = f'''SELECT IsBatchCompleteCountIncreased FROM {self._tbname}
WHERE Platform=? and TaskId=? and BatchId=?'''
found = False
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
platform,
taskid,
batchid,
))
result = cursor.fetchall()
if not result is None and len(result) > 0:
tmp = result[0][0]
res = bool(tmp) # 一定只有一个子任务
found = True
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if found:
break
except Exception:
self._logger.error("update {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IDownBatchTask._tbname)
def update_batch_complete_count_increaced_flag(
self, platform: str, taskid: str, batchid: str,
isbatchcompletecountincreased: bool) -> bool:
"""使用指定 TaskBatchBack 对象的 isbatchcompletecountincreased 字段\n"""
conn: SqliteConn = None
cursor = None
res: bool = False
try:
# Platform
# TaskId,
# Batchid,
# ClientId,
# TokenId,
# AppType,
# CmdRcvMsg,
# Result,
# CmdStatus,
# Sequence,
# OtherFields,
# CreateTime,
# UpdateTime,
# IsBatchCompleteCountIncreased
cmd = f'''UPDATE {self._tbname} set
IsBatchCompleteCountIncreased=?
WHERE Platform=? and TaskId=? and BatchId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
result = cursor.execute(cmd, (
1 if isbatchcompletecountincreased else 0,
platform,
taskid,
batchid,
))
if not result is None and result.rowcount > 0:
res = True # 一定只有一个子任务
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
class TbMail(TbSqliteBase):
__tb_mail_service: SqliteTable = SqliteTable(
'mailservice',
True,
SqliteColumn(
colname='ID',
coltype='INTEGER',
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True).set_index_new(),
SqliteColumn(colname='service_name', nullable=False),
SqliteColumn(colname='imap_host'),
SqliteColumn(colname='imap_port'),
SqliteColumn(colname='pop3_host'),
SqliteColumn(colname='pop3_port'),
)
databasename = 'task'
def __init__(self, dbcfg: SqliteConfig):
TbSqliteBase.__init__(self, TbMail.__tb_mail_service._tbname, dbcfg, TbMail.databasename)
self._xml_path = Path(__file__).parent / 'host.xml'
self._init_default_service()
def _append_tables(self):
self._conn_mngr.append_table(TbMail.__tb_mail_service)
def _init_default_service(self):
"""
初始化数据，读取本地的xml文件，然后存入数据库
:return:
"""
if len(self._get_default_mail_service()) != 0:
return
tree = ET.parse(str(self._xml_path))
root = tree.getroot()
childs = iter(root)
for child in childs:
tag = child.attrib.get('name')
server = iter(child)
imap = next(server)
pop3 = next(server)
imap_host = imap.attrib.get('host')
imap_port = imap.attrib.get('port')
pop3_host = pop3.attrib.get('host')
pop3_port = pop3.attrib.get('port')
self.insert_a_piece_of_data(tag, imap_host, imap_port, pop3_host, pop3_port)
return
@table_locker(__tb_mail_service._tbname)
def _get_default_mail_service(self):
conn: SqliteConn = None
res = []
sql = '''
SELECT * FROM mailservice
'''
try:
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
conn._conn.row_factory = self._dict_factory
c = conn._conn.cursor()
c.execute(sql)
res_data = c.fetchall()
if len(res_data) > 0:
res.extend(res_data)
except Exception as ex:
conn._conn.rollback()
raise ex
finally:
if conn is not None:
conn.close()
except Exception:
self._logger.error(f"Query task according to the task status data problem,err:{traceback.format_exc()}.")
finally:
if conn is not None:
conn.close()
return res
@table_locker(__tb_mail_service._tbname)
def insert_a_piece_of_data(self, service_name, imap_host, imap_port, pop3_host, pop3_port):
"""
向数据库插入一条完整的数据
:param service_name:
:param imap_host:
:param imap_port:
:param pop3_host:
:param pop3_port:
:return:
"""
conn: SqliteConn = None
sql = '''
INSERT INTO mailservice(service_name, imap_host, imap_port, pop3_host, pop3_port)
VALUES (?, ?, ?, ?, ?);
'''
params = (service_name, imap_host, imap_port, pop3_host, pop3_port)
try:
conn = self.connect_write(5)
c = conn.cursor
c.execute(sql, params)
except Exception:
self._logger.error(f"There was a problem inserting data, err:{traceback.format_exc()}.")
finally:
if conn is not None:
conn.commit()
conn.close()
return
@table_locker(__tb_mail_service._tbname)
def update_service_by_sql(self, sql, pars):
"""
通过sql更新数据
:param sql:
:param pars:
:return:
"""
res = False
conn: SqliteConn = None
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, pars)
if result is not None and result.rowcount > 0: # or len(result) < 1:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(f"Update default cmd error,err:{traceback.format_exc()}.")
finally:
if conn is not None:
conn.close()
return
@table_locker(__tb_mail_service._tbname)
def delete_one_mail_service(self, mail):
"""
删除某个mail
:param mail:
:return:
"""
sql = f'''DELETE FROM mailservice WHERE service_name=?'''
pars = (mail,)
res = False
conn: SqliteConn = None
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, pars)
if result is not None and result.rowcount > 0: # or len(result) < 1:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(f"Update default cmd error,err:{traceback.format_exc()}.")
finally:
if conn is not None:
conn.close()
return
class TbTaskUserInfo(TbSqliteBase):
__tb_userinfo: SqliteTable = SqliteTable(
"userinfo",
True,
SqliteColumn(
colname="Id",
coltype="INTEGER",
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True,
).set_index_new(),
SqliteColumn(colname="taskid",
coltype="CHAR",
length=50,
nullable=False).set_index_new(),
SqliteColumn(colname="batchid",
coltype="CHAR",
length=50,
nullable=False).set_index_new(),
SqliteColumn(colname="userid",
coltype="CHAR",
length=50,
nullable=False),
SqliteColumn(colname="clientid",
coltype="CHAR",
length=50,
nullable=False),
# SqliteColumn(colname='Account', nullable=False),
# SqliteColumn(colname='DownloadTime', coltype='DATETIME', nullable=False),
)
databasename = "idownuser"
def __init__(self, dbcfg: SqliteConfig):
TbSqliteBase.__init__(
self,
TbTaskUserInfo.__tb_userinfo._tbname,
dbcfg,
TbTaskUserInfo.databasename,
)
def _append_tables(self):
self._conn_mngr.append_table(TbTaskUserInfo.__tb_userinfo)
@table_locker(__tb_userinfo._tbname)
def __count_userinfo(self, taskid, batchid):
"""
每个taskid和batchid代表唯一的一个任务
一个唯一的任务对应唯一的userid
:param taskid:
:return:
"""
conn: SqliteConn = None
res = []
sql = """
SELECT count(1) FROM userinfo
WHERE taskid=? AND batchid=?
"""
par = (taskid, batchid)
try:
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
# conn._conn.row_factory = self._dict_factory
c = conn.cursor
c.execute(sql, par)
res_data = c.fetchall()
# print(res_data)
if len(res_data) > 0 and res_data[0][0] > 0:
res.extend(res_data)
except Exception as ex:
conn._conn.rollback()
raise ex
finally:
if conn is not None:
conn.close()
except Exception:
self._logger.error(
f"Query user from userinfo error\nerr:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.close()
return res
def _get_update_sql(self, taskid, batchid, userid, clientid):
"""
一个唯一的任务只有唯一的userid和唯一的clientid
所以直接更新就好
"""
params: list = []
sql = "UPDATE userinfo SET userid=?, clientid=?"
params.append(userid)
params.append(clientid)
sql += " WHERE taskid=? and batchid=?"
params.append(taskid)
params.append(batchid)
return sql, params
@table_locker(__tb_userinfo._tbname)
def _updatesameuserinfo(self, taskid, batchid, userid, clientid):
"""
在插入任务数据前，先检查iscantask表
是否有相同的taskid, 如果taskid相同那么就更新整条数据的状态
"""
res = False
conn: SqliteConn = None
resdata = self.__count_userinfo(taskid, batchid)
if len(resdata) == 0:
# 没有重复的id直接返回
return False
elif len(resdata) > 1:
raise Exception(
"The database has duplicated multiple skipids, please check the problem"
)
elif len(resdata) == 1:
sql, par = self._get_update_sql(taskid, batchid, userid, clientid)
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, par)
if result is not None and result.rowcount > 0:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(
f"There was a problem updating the userinfo table\nerr:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.close()
return True
@table_locker(__tb_userinfo._tbname)
def save_idown_userinfo(self, taskid, batchid, userid, clientid):
"""
保存idown的user信息
主要用于关联idown task给后台反馈目前的cookie状态
"""
conn: SqliteConn = None
# 在插入数据前查询是否有相同的数据，一个taskid和一个batchid只能带有一个userid
repeat = self._updatesameuserinfo(taskid, batchid, userid, clientid)
if repeat:
# 如果已经处理了重复的数据直接就结束了
return
sql = """insert into userinfo(
taskid,
batchid,
userid,
clientid
) values(?,?,?,?)"""
pars = (taskid, batchid, userid, clientid)
try:
conn = self.connect_write(5)
c = conn.cursor
c.execute(sql, pars)
except Exception:
self._logger.error(
f"There was a problem inserting data\nerr:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.commit()
conn.close()
return
@table_locker(__tb_userinfo._tbname)
def query_idown_userinfo(self, taskid, batchid) -> dict:
"""
查询每个任务的user信息
每个任务对应唯一的user
:return:
"""
conn: SqliteConn = None
res = None
sql = """SELECT
*
FROM userinfo
WHERE taskid=? and batchid=?;"""
pars = (taskid, batchid)
try:
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
conn._conn.row_factory = self._dict_factory
c = conn._conn.cursor()
c.execute(sql, pars)
res_data = c.fetchall()
if len(res_data) > 0:
res = res_data[0]
except Exception as ex:
conn._conn.rollback()
raise ex
finally:
if conn is not None:
conn.close()
except Exception:
self._logger.error(
f"Query idown userinfo error\nerr:{traceback.format_exc()}")
finally:
if conn is not None:
conn.close()
return res
class TbClient(TbSqliteBase):
"""Client表及相关操作"""
__tb_Clients: SqliteTable = SqliteTable(
'Clients',
True,
SqliteColumn(
colname='Id',
coltype='INTEGER',
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True).set_index_new(),
SqliteColumn(colname='ClientId', nullable=False).set_index_new(),
SqliteColumn(colname='SystemVer'),
SqliteColumn(colname='IP'),
SqliteColumn(colname='Mac'),
SqliteColumn(colname='CrossWall', coltype='INTEGER'),
SqliteColumn(colname='Country'),
SqliteColumn(colname='Platform'),
SqliteColumn(
colname='AppType',
defaultval='[]',
description='一个python列表字符串 "[1001,1002]"，使用apptype的值'),
SqliteColumn(
colname='TaskType',
defaultval='[]',
description='一个python列表字符串 "[1,2]"，使用ETaskType的值'),
SqliteColumn(
colname='AppClassify',
defaultval='[]',
description='一个python列表字符串 "[1,2,3]"，使用appclassify的值'),
SqliteColumn(
colname='ClientBusiness',
defaultval='[]',
description='一个python列表字符串 "[1,2,3]"，使用EClientBusiness的值'),
SqliteColumn(colname='CpuSize', coltype='REAL'),
SqliteColumn(colname='CpuPerc', coltype='REAL'),
SqliteColumn(colname='MemSize', coltype='REAL'),
SqliteColumn(colname='MemPerc', coltype='REAL'),
SqliteColumn(colname='BandWidthd', coltype='REAL'),
SqliteColumn(colname='BandWidthdPerc', coltype='REAL'),
SqliteColumn(colname='DiskSize', coltype='REAL'),
SqliteColumn(colname='DiskPerc', coltype='REAL'),
SqliteColumn(colname='TaskNewCnt', coltype='INTEGER'),
SqliteColumn(colname='TaskWaitingCnt', coltype='INTEGER'),
SqliteColumn(colname='TaskDownloadingCnt', coltype='INTEGER'),
SqliteColumn(colname='UpdateTime', coltype='REAL',
nullable=False).set_index_new(),
)
def __init__(self, dbcfg: SqliteConfig):
""""""
TbSqliteBase.__init__(self, TbClient.__tb_Clients._tbname, dbcfg)
def _append_tables(self):
self._conn_mngr.append_table(TbClient.__tb_Clients)
@table_locker(__tb_Clients._tbname)
def save_client_status_basic(self, client: StatusBasic) -> bool:
"""保存采集端基础状态"""
res = False
conn: SqliteConn = None
cursor = None
client: StatusBasic = client
try:
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE ClientId=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (client._clientid, ))
result = cursor.fetchall()
# 这里只要找到了就是库里有的，然后只更新UpdateTime比当前库中大的
if result[0][0] > 0:
res = True
# update
cmd = f'''UPDATE {self._tbname} set
SystemVer=?,
IP=?,
Mac=?,
CrossWall=?,
Country=?,
Platform=?,
AppType=?,
TaskType=?,
AppClassify=?,
ClientBusiness=?,
CpuSize=?,
CpuPerc=?,
MemSize=?,
MemPerc=?,
BandWidthd=?,
BandWidthdPerc=?,
DiskSize=?,
DiskPerc=?,
UpdateTime=?
WHERE ClientId=? and UpdateTime<=?'''
result = cursor.execute(cmd, (
client.systemver,
client.ip,
client.mac,
client.crosswall,
client.country,
client.platform,
str(client.apptype),
str(client.tasktype),
str(client.appclassify),
str(client.clientbusiness),
client.cpusize,
client.cpuperc,
client.memsize,
client.memperc,
client.bandwidthd,
client.bandwidthdperc,
client.disksize,
client.diskperc,
client.time,
client._clientid,
client.time,
))
if not result is None and result.rowcount > 0:
pass
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
if not res:
try:
conn = self.connect_write(5)
cmd = f'''INSERT INTO {self._tbname}(
ClientId,
SystemVer,
IP,
Mac,
CrossWall,
Country,
Platform,
AppType,
TaskType,
AppClassify,
ClientBusiness,
CpuSize,
CpuPerc,
MemSize,
MemPerc,
BandWidthd,
BandWidthdPerc,
DiskSize,
DiskPerc,
UpdateTime) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'''
cursor = conn.cursor
result = cursor.execute(cmd, (
client._clientid,
client.systemver,
client.ip,
client.mac,
client.crosswall,
client.country,
client.platform,
str(client.apptype),
str(client.tasktype),
str(client.appclassify),
str(client.clientbusiness),
client.cpusize,
client.cpuperc,
client.memsize,
client.memperc,
client.bandwidthd,
client.bandwidthdperc,
client.disksize,
client.diskperc,
client.time,
))
if not result is None and result.rowcount > 0: # or len(result) < 1:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error(
"Save client status basic error: %s" % traceback.format_exc())
finally:
if not conn is None:
conn.close()
return res
@table_locker(__tb_Clients._tbname)
def save_client_status_task(self, client: StatusTask):
"""保存采集端任务统计数据"""
res = False
conn: SqliteConn = None
cursor = None
client: StatusTask = client
try:
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE ClientId=?'''
for conn in self.connect_all(5):
try:
cursor = conn.cursor
cursor.execute(cmd, (client._clientid, ))
result = cursor.fetchall()
if result[0][0] > 0:
res = True
# update
cmd = f'''UPDATE {self._tbname} set
TaskNewCnt=?,
TaskWaitingCnt=?,
TaskDownloadingCnt=?,
UpdateTime=?
WHERE ClientId=? and UpdateTime<=?;'''
result = cursor.execute(cmd, (
client.tasknewcnt,
client.taskwaitingcnt,
client.taskdownloadingcnt,
client.time,
client._clientid,
client.time,
))
# 这句没用，就是调试看看结果..
if result is None or result.rowcount > 1: # or len(result) < 1:
pass
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
if not res:
try:
# insert
conn = self.connect_write(5)
cmd = f'''INSERT INTO {self._tbname}(
ClientId,
TaskNewCnt,
TaskWaitingCnt,
TaskDownloadingCnt,
UpdateTime) VALUES(?,?,?,?,?)'''
cursor = conn.cursor
result = cursor.execute(
cmd, (client._clientid, client.tasknewcnt,
client.taskwaitingcnt, client.taskdownloadingcnt,
client.time))
if not result is None and result.rowcount > 0:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error(
"Save client status task error: %s" % traceback.format_exc())
return res
@table_locker(__tb_Clients._tbname)
def get_client_status(self, clientid: str) -> dict:
"""获取指定采集端信息数据，返回数据行"""
res: dict = None
conn: SqliteConn = None
cursor = None
try:
for conn in self._conn_mngr.connect_all():
try:
conn._conn.row_factory = SqliteConn._row_dict_factory
cursor = conn.cursor
cmd = f'''SELECT
ClientId,
SystemVer,
IP,
Mac,
CrossWall,
Country,
Platform,
AppType,
TaskType,
AppClassify,
ClientBusiness,
CpuSize,
CpuPerc,
MemSize,
MemPerc,
BandWidthd,
BandWidthdPerc,
DiskSize,
DiskPerc,
TaskNewCnt,
TaskWaitingCnt,
TaskDownloadingCnt,
UpdateTime
FROM {self._tbname} WHERE ClientId=?'''
cursor.execute(cmd, (clientid, ))
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
fields: dict = {}
for i in range(len(result[0])):
fields[cursor.description[i][0].lower()] = result[0][i]
if isinstance(fields, dict) and len(fields) > 0:
res = fields
break
# result = cursor.fetchall()
# for row in result:
# res = row # 只取第一行
# break
if not res is None:
break
except Exception:
self._logger.error(
"Get client status error: %s" % traceback.format_exc())
finally:
if not conn is None:
conn.close()
if not res is None:
break
except Exception:
self._logger.error(
"Get client status error: %s" % traceback.format_exc())
return res
@table_locker(__tb_Clients._tbname)
def get_client_status_all(self, interval: float = 15) -> iter:
"""获取所有采集端任务状态数据。\n
interval: 指定心跳间隔，即只读取最近n秒内更新的采集端状态，单位秒。"""
res: Client = None
conn: SqliteConn = None
cursor = None
try:
# 如果心跳间隔不对，则使用默认值
if not type(interval) in [int, float] or interval < 2:
interval = 15
for conn in self._conn_mngr.connect_all():
# conn = DbSqlite._conn_mngr.connect_write()
conn._conn.row_factory = self._dict_factory
cursor = conn.cursor
cmd = f'''SELECT
ClientId,
SystemVer,
IP,
Mac,
CrossWall,
Country,
Platform,
AppType,
TaskType,
AppClassify,
ClientBusiness,
CpuSize,
CpuPerc,
MemSize,
MemPerc,
BandWidthd,
BandWidthdPerc,
DiskSize,
DiskPerc,
TaskNewCnt,
TaskWaitingCnt,
TaskDownloadingCnt,
UpdateTime
FROM {self._tbname} WHERE UpdateTime>=?'''
# helper_time.ts_since_1970_tz()单位为秒，减去心跳间隔15秒，
# 就是15秒内状态有更新的才认为是上线的，有效的客户端
it = helper_time.ts_since_1970_tz() - interval
cursor.execute(cmd, (it, ))
result = cursor.fetchall()
for row in result:
# if len(row) != 15:
# continue
fields: dict = {}
for i in range(len(row)):
fields[cursor.description[i][0].lower()] = row[i]
sb: StatusBasic = StatusBasic(fields)
st: StatusTask = StatusTask(fields)
res: Client = Client(sb, st)
yield res
except Exception:
self._logger.error(
"Get all client status error: %s" % traceback.format_exc())
finally:
if not conn is None:
conn.close()
class TbTask(TbSqliteBase):
__tb_task: SqliteTable = SqliteTable(
"task",
True,
SqliteColumn(
colname="ID",
coltype="INTEGER",
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True,
).set_index_new(),
SqliteColumn(colname="taskid",
coltype="CHAR",
length=50,
nullable=False).set_index_new(),
SqliteColumn(colname="platform",
coltype="CHAR",
length=50,
nullable=False),
SqliteColumn(colname="clientid",
coltype="CHAR",
length=50,
nullable=False),
SqliteColumn(colname="parent_taskid", coltype="CHAR", length=50),
SqliteColumn(colname="batchid",
coltype="CHAR",
length=50,
nullable=False),
SqliteColumn(colname="parentbatchid", coltype="CHAR", length=50),
SqliteColumn(colname="apptype", coltype="INT"),
SqliteColumn(colname="tasktype", coltype="INT"),
SqliteColumn(colname="url", coltype="CHAR", length=50),
SqliteColumn(colname="host", coltype="CHAR", length=50),
SqliteColumn(colname="cookie", coltype="CHAR"),
SqliteColumn(colname="preaccount", coltype="CHAR", length=50),
SqliteColumn(colname="account", coltype="CHAR", length=50),
SqliteColumn(colname="password", coltype="CHAR", length=50),
SqliteColumn(colname="phone", coltype="CHAR", length=50),
SqliteColumn(colname="input", coltype="CHAR", length=50),
SqliteColumn(colname="taskstatus", coltype="INT"),
SqliteColumn(colname="createtime", coltype="INT"),
SqliteColumn(colname="lastexecutetime", coltype="INT"),
SqliteColumn(colname="failtimes", coltype="INT"),
SqliteColumn(colname="successtimes", coltype="INT"),
SqliteColumn(colname="preglobaltelcode", coltype="CHAR", length=10),
SqliteColumn(colname="globaltelcode", coltype="CHAR", length=10),
SqliteColumn(colname="otherfileds", coltype="CHAR"),
SqliteColumn(colname="tokentype", coltype="INT"),
SqliteColumn(colname="sequence", coltype="INT"),
SqliteColumn(colname="progress", coltype="REAL"),
SqliteColumn(colname="forcedownload", coltype="CHAR", length=10),
SqliteColumn(colname="source", coltype="CHAR", nullable=False),
SqliteColumn(colname="cmdid", coltype="CHAR", length=50),
SqliteColumn(colname="cookiealive", coltype="INT"),
SqliteColumn(colname="cookielastkeeptime", coltype="INT"),
)
databasename = "task"
def __init__(self, dbcfg: SqliteConfig):
TbSqliteBase.__init__(self, TbTask.__tb_task._tbname, dbcfg,
TbTask.databasename)
def _append_tables(self):
self._conn_mngr.append_table(TbTask.__tb_task)
@table_locker(__tb_task._tbname)
def query_task(self, conds: SqlConditions) -> list:
"""
根据任务状态来取出task表的任务，
根据不同的状态来查询不同的任务数据
:param key:
:param value:
:return:
"""
conn: SqliteConn = None
res = []
sql = """SELECT
taskid,
task.platform,
clientid,
parent_taskid,
batchid,
parentbatchid,
apptype,
tasktype,
url,
host,
cookie,
preaccount,
account,
password,
phone,
input,
taskstatus,
createtime,
lastexecutetime,
failtimes,
successtimes,
preglobaltelcode,
globaltelcode,
otherfileds,
tokentype,
sequence,
progress,
forcedownload,
source,
cmdid,
cmd,
cookiealive,
cookielastkeeptime
FROM task LEFT OUTER JOIN idowncmd USING (cmdid)
WHERE {};""".format(conds.text_normal)
try:
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
conn._conn.row_factory = self._dict_factory
c = conn._conn.cursor()
c.execute(sql, conds.params)
res_data = c.fetchall()
if len(res_data) > 0:
res.extend(res_data)
except Exception as ex:
conn._conn.rollback()
raise ex
finally:
if conn is not None:
conn.close()
except Exception:
self._logger.error(
f"Query task according to the task status data problem,err:{traceback.format_exc()}."
)
finally:
if conn is not None:
conn.close()
return res
def __modify_task_state(self, oldstate: int, task: Task):
"""
这里的前置是已经为下载过的任务
1、一次性任务，除了正在执行，新任务都会去更新状态
2、循环任务，不会更新状态
:param task:
:return:
"""
# 默认为不更新状态
res = False
circule_modle = task.cmd.stratagy.circulation_mode
# 一次性任务
if circule_modle == 1 and oldstate not in [0, 1, 2, 3, 5]:
res = True
# 循环任务或者其他情况都不去更新状态
return res
def _get_task_update_sql(self, src: dict, new: Task):
"""拼接更新task的sql，并返回sqlparameters列表"""
params: list = []
sql = "UPDATE task SET "
# 特定条件更新任务下载状态
if self.__modify_task_state(src["taskstatus"], new):
sql += "taskstatus=?, "
params.append(new.taskstatus.value)
# 如果cmdid不一致则需要更新下cmd
if src["cmdid"] != new.cmd_id:
sql += "cmdid"
params.append(new.cmd_id)
# sql += 'otherfileds=?, '
# params.append(json.dumps(new._other_fields))
sql += "tasktype=?, "
params.append(new.tasktype.value)
if not helper_str.is_none_or_empty(new.cookie):
sql += "cookie=?, "
params.append(new.cookie)
if helper_str.is_none_or_empty(
src["url"]) and not helper_str.is_none_or_empty(new.url):
sql += "url=?, "
params.append(new.url)
if helper_str.is_none_or_empty(
src["host"]) and not helper_str.is_none_or_empty(new.host):
sql += "host=?, "
params.append(new.host)
if helper_str.is_none_or_empty(
src["account"]) and not helper_str.is_none_or_empty(
new.account):
sql += "account=?, "
params.append(new.account)
if helper_str.is_none_or_empty(
src["password"]) and not helper_str.is_none_or_empty(
new.password):
sql += "password=?, "
params.append(new.password)
if helper_str.is_none_or_empty(
src["phone"]) and not helper_str.is_none_or_empty(
new.password):
sql += "phone=?, "
params.append(new.password)
if helper_str.is_none_or_empty(
src["globaltelcode"]) and not helper_str.is_none_or_empty(
new.globaltelcode):
sql += "globaltelcode=?, "
params.append(new.globaltelcode)
# if not helper_str.is_none_or_empty(new._sequence):
# sql += 'sequence=?, '
# params.append(int(new._sequence))
if not helper_str.is_none_or_empty(new.forcedownload):
sql += "forcedownload=?, "
params.append(int(new.forcedownload))
sql = sql.rstrip().rstrip(",")
sql += " WHERE batchid=? AND taskid=?"
params.append(new.batchid)
params.append(new.taskid)
return (sql, params)
@table_locker(__tb_task._tbname)
def __count_idowntask(self, taskid, batchid):
"""
对数据库中的任务进行计数
:param taskid:
:param batchid:
:return:
"""
conn: SqliteConn = None
res = []
# sql = '''
# SELECT count(1) FROM task
# WHERE taskid=? AND batchid=?
# '''
sql = """
SELECT * FROM task WHERE taskid=? AND batchid=?
"""
par = (taskid, batchid)
try:
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
# 只计数
# c = conn.cursor
# c.execute(sql, par)
# res_data = c.fetchall()
# print(res_data)
# if len(res_data) > 0 and res_data[0][0] > 0:
# res.extend(res_data)
# 将数据库中的源数据查询出来
conn._conn.row_factory = self._dict_factory
c = conn._conn.cursor()
c.execute(sql, par)
fres: list = c.fetchall()
if len(fres) > 0:
res.extend(fres)
except Exception as ex:
conn._conn.rollback()
raise ex
finally:
if conn is not None:
conn.close()
except Exception:
self._logger.error(
f"Query task according to the task status data problem,err:{traceback.format_exc()}."
)
finally:
if conn is not None:
conn.close()
return res
@table_locker(__tb_task._tbname)
def _updatesametaskiddata(self, dt: Task):
"""
在插入任务数据前，先检查task表
是否有相同的batchid, 如果batchid相同那么就更新整条数据的状态
:param dt:
:return:
"""
res = False
conn: SqliteConn = None
resdata = self.__count_idowntask(dt.taskid, dt.batchid)
if len(resdata) == 0:
# 没有重复的id直接返回
return False
elif len(resdata) > 1:
raise Exception(
"The database has duplicated multiple skipids, please check the problem"
)
elif len(resdata) == 1:
sql, par = self._get_task_update_sql(resdata[0], dt)
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, par)
if (result is not None and
result.rowcount > 0): # or len(result) < 1:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(
f"There was a problem updating the task table task data，err:{traceback.format_exc()}!"
)
finally:
if conn is not None:
conn.close()
return True
@table_locker(__tb_task._tbname)
def insert_task_to_sqlit(self, dt: Task):
"""
通过登录测试的任务
将有效的任务放入数据库的task表
只会在这个地方插入一次
:param dt:
:return:
"""
conn: SqliteConn = None
# 在插入数据前查询是否有相同的数据(目前只根据taskid查询)，如果有相同的数据则不插入。
repeat = self._updatesametaskiddata(dt)
if repeat:
# 如果已经处理了重复的数据直接就结束了
return
sql = """INSERT INTO task (
taskid,
platform,
clientid,
parent_taskid,
batchid,
parentbatchid,
apptype,
tasktype,
url,
host,
cookie,
preaccount,
account,
password,
phone,
input,
taskstatus,
createtime,
lastexecutetime,
failtimes,
successtimes,
preglobaltelcode,
globaltelcode,
otherfileds,
tokentype,
sequence,
progress,
forcedownload,
source,
cmdid,
cookiealive,
cookielastkeeptime
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?);
"""
params = (
dt.taskid,
dt.platform,
dt._clientid,
dt.parenttaskid,
dt.batchid,
dt.parentbatchid,
int(dt.apptype),
dt.tasktype.value,
dt.url,
dt.host,
dt.cookie,
dt.preaccount,
dt.account,
dt.password,
dt.phone,
dt.input,
dt.taskstatus.value,
dt.createtime,
dt.lastexecutetime,
dt.failtimes,
dt.successtimes,
dt.preglobaltelcode,
dt.globaltelcode,
json.dumps(dt._other_fields),
dt.tokentype.value,
int(dt._sequence),
dt.progress,
dt.forcedownload,
dt.source,
dt.cmd_id,
dt.cookie_alive,
dt.cookie_last_keep_alive_time,
)
try:
conn = self.connect_write(5)
c = conn.cursor
c.execute(sql, params)
except Exception:
self._logger.error(
f"There was a problem inserting data, err:{traceback.format_exc()}."
)
finally:
if conn is not None:
conn.commit()
conn.close()
return
@table_locker(__tb_task._tbname)
def update_status_by_taskid(self, key: str, value: int, batchid, taskid):
"""
更新task表的任务状态，根据batchid和taskid共同定位数据
:param key:
:param value:
:param taskid:
:return:
"""
res = False
conn: SqliteConn = None
sql = """UPDATE task set {}=? where taskid=? and batchid=?
""".format(key)
pars = (value, taskid, batchid)
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, pars)
if (result is not None
and result.rowcount > 0): # or len(result) < 1:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(
f"There was a problem inserting data, and duplicate "
f"data might have been inserted, err:{traceback.format_exc()}."
)
finally:
if conn is not None:
conn.close()
return
@table_locker(__tb_task._tbname)
def query_task_by_sql(self, sql, pars):
"""
根据sql来查询数据库，提供一个对外查询的接口
:param sql:
:param pars:
:return:
"""
conn: SqliteConn = None
allget = []
try:
for conn in self.connect_all(5):
try:
conn._conn.row_factory = self._dict_factory
c = conn._conn.cursor()
c.execute(sql, pars)
res: list = c.fetchall()
if len(res) > 0:
allget.extend(res)
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
except:
self._logger.error(
f"There was a problem querying data, err:{traceback.format_exc()}."
)
finally:
if conn is not None:
conn.close()
return allget
@table_locker(__tb_task._tbname)
def update_task_resource(self, tsk: Task):
"""更新Task任务信息"""
conn: SqliteConn = None
res = False
sql = """update task set
url=?,
host=?,
cookie=?,
account=?,
password=?,
phone=?,
lastexecutetime=?,
successtimes=?,
failtimes=?,
sequence=?
where batchid=? and taskid=?"""
pars = (
tsk.url,
tsk.host,
tsk.cookie,
tsk.account,
tsk.password,
tsk.phone,
int(datetime.now(pytz.timezone("Asia/Shanghai")).timestamp()),
tsk.successtimes,
tsk.failtimes,
tsk._sequence,
tsk.batchid,
tsk.taskid,
)
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, pars)
if (result is not None
and result.rowcount > 0): # or len(result) < 1:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(
f"Update task error:\nbatchid:{tsk.batchid}\nerror:{traceback.format_exc()}"
)
finally:
if conn is not None:
conn.close()
return
@table_locker(__tb_task._tbname)
def update_task_by_sql(self, sql, pars):
"""
提供一个对外根据sql更新task的接口，就不管拿来干啥了
:param sql:
:param pars:
:return:
"""
conn: SqliteConn = None
res = False
try:
for conn in self.connect_all(5):
try:
c = conn.cursor
result = c.execute(sql, pars)
if (result is not None
and result.rowcount > 0): # or len(result) < 1:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if conn is not None:
conn.close()
if res:
break
except Exception:
self._logger.error(
f"Update task by sql error, err:{traceback.format_exc()}")
finally:
if conn is not None:
conn.close()
return
class TbIScoutBatchTask(TbSqliteBase):
"""Task表及相关操作"""
__tb_IScoutBTask: SqliteTable = SqliteTable(
'IScoutBatchTask',
True,
SqliteColumn(colname='Id',
coltype='INTEGER',
nullable=False,
is_primary_key=True,
is_auto_increament=True,
is_unique=True).set_index_new(),
SqliteColumn(colname='ClientId', nullable=False).set_index_new(),
SqliteColumn(colname='Platform', nullable=False).set_index_new(),
SqliteColumn(colname='Source', nullable=False).set_index_new(),
SqliteColumn(colname='IsPeriod',
coltype='INTEGER',
nullable=False,
defaultval=0).set_index_new(),
SqliteColumn(colname='PeriodNum',
coltype='INTEGER',
nullable=False,
defaultval=0).set_index_new(),
SqliteColumn(colname='StartTime', coltype='DATETIME'),
SqliteColumn(colname='EndTime', coltype='DATETIME'),
SqliteColumn(colname='Interval', coltype='REAL'),
SqliteColumn(colname='LastStartTime', coltype='DATETIME'),
SqliteColumn(colname='LastEndTime', coltype='DATETIME'),
SqliteColumn(colname='TaskId', nullable=False).set_index_new(),
SqliteColumn(colname='BatchId', nullable=False).set_index_new(),
SqliteColumn(colname='CmdId').set_index_new(),
SqliteColumn(colname='Status',
coltype='INTEGER',
nullable=False,
description='采集端执行命令的状态，要反给中心的').set_index_new(),
SqliteColumn(colname='ObjectType', coltype='INTEGER').set_index_new(),
SqliteColumn(colname='Object', ).set_index_new(),
SqliteColumn(
colname='Progress', #0~1浮点数表百分比
coltype='REAL',
nullable=True),
SqliteColumn(
colname='Elapsed', #0~1浮点数表百分比
coltype='REAL',
nullable=True),
SqliteColumn(colname='CmdRcvMsg'),
SqliteColumn(colname='IsBatchCompleteCountIncreased',
coltype='BOOLEAN').set_index_new(),
SqliteColumn(colname='CreateTime',
coltype='DATETIME',
defaultval='1970-01-01 00:00:00'),
SqliteColumn(colname='UpdateTime', coltype='REAL',
nullable=False).set_index_new(),
SqliteColumn(colname='Sequence', coltype='INTEGER',
defaultval=0).set_index_new(),
)
# scantype=scansearch 主键 TaskId
# scantype=scan 主键 TaskId+BatchId
# 所有列，复制粘贴用...：
# ClientId,
# Platform,
# Source,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime
# TaskId,
# BatchId,
# CmdId,
# Status,
# ObjectType,
# Object,
# Progress,
# Elapsed,
# CmdRcvMsg,
# IsBatchCompleteCountIncreased,
# CreateTime,
# UpdateTime,
# Sequence,
def __init__(self, dbcfg: SqliteConfig):
""""""
TbSqliteBase.__init__(self, TbIScoutBatchTask.__tb_IScoutBTask._tbname,
dbcfg)
def _append_tables(self):
self._conn_mngr.append_table(TbIScoutBatchTask.__tb_IScoutBTask)
@table_locker(__tb_IScoutBTask._tbname)
def save_new_iscoutbtask(
self,
task: IscoutTask,
client: Client,
cmdstatus: ECommandStatus = ECommandStatus.WaitForSend,
) -> Tuple[bool, bool]:
"""保存新的批处理任务的令牌资源\n
task:任务对象"""
res = False
isnew: bool = False
conn: SqliteConn = None
cursor = None
task: IscoutTask = task
try:
# 搜索每个库，看有没有 TokenId一样的，且时间更新
# 的，一样就更新其他所有字段
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE
TaskId=? and BatchId=? and Platform=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
task.taskid,
task.batchid,
task._platform,
))
result = cursor.fetchall()
if result[0][0] > 0:
res = True
cmd = f'''UPDATE {self._tbname} set
ClientId=?,
CmdId=?,
Source=?,
IsPeriod=?,
PeriodNum=?,
StartTime=?,
EndTime=?,
Interval=?,
LastStartTime=?,
LastEndTime=?,
Status=?,
ObjectType=?,
Object=?,
Progress=?,
Elapsed=?,
CmdRcvMsg=?,
IsBatchCompleteCountIncreased=?,
UpdateTime=?,
CreateTime=?,
Sequence=?
WHERE TaskId=? and BatchId=? and Platform=? and CreateTime<=?;'''
result = cursor.execute(
cmd,
(
client._statusbasic._clientid,
task.cmd_id,
task.source,
1 if task._is_period else 0,
# 这里的update是来了新的任务文件，需要直接覆盖periodnum
task.periodnum,
task.cmd.stratagy.time_start,
task.cmd.stratagy.time_end,
task.cmd.stratagy.interval,
task.laststarttime,
task.lastendtime,
cmdstatus.value,
task._objecttype.value,
task._object,
task.progress,
0, # elapsed
task.cmdrcvmsg,
task.isbatchcompletecountincreased,
helper_time.get_time_sec_tz(),
task.createtimestr,
0, #重置sequence
task.taskid,
task.batchid,
task._platform,
task.createtimestr,
))
# 这句没用，就是调试看看结果..
if result is None or result.rowcount < 1: # or len(result) < 1:
pass
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
# 若没找到，则insert一条到最新的库
# res==True表示至少有一个库里面有一条符合条件的任务，且已更新其字段
if not res:
isnew = True
conn = self.connect_write(5)
try:
# insert
cmd = f'''INSERT INTO {self._tbname}(
ClientId,
Platform,
Source,
IsPeriod,
PeriodNum,
StartTime,
EndTime,
Interval,
LastStartTime,
LastEndTime,
TaskId,
BatchId,
CmdId,
Status,
ObjectType,
Object,
Progress,
Elapsed,
CmdRcvMsg,
IsBatchCompleteCountIncreased,
CreateTime,
UpdateTime,
Sequence) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'''
# 有几个属于TaskBack的字段在此不更新
cursor = conn.cursor
result = cursor.execute(cmd, (
client._statusbasic._clientid,
task._platform,
task.source,
1 if task._is_period else 0,
task.periodnum,
task.cmd.stratagy.time_start,
task.cmd.stratagy.time_end,
task.cmd.stratagy.interval,
task.laststarttime,
task.lastendtime,
task.taskid,
task.batchid,
task.cmd_id,
cmdstatus.value,
task._objecttype.value,
task._object,
task.progress,
0,
'',
task.isbatchcompletecountincreased,
task.createtimestr,
helper_time.get_time_sec_tz(),
0,
))
if result is None or result.rowcount < 1: # or len(result) < 1:
res = False
else:
res = True
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("save new IScoutTask error: %s" %
traceback.format_exc())
return (res, isnew)
@table_locker(__tb_IScoutBTask._tbname)
def select_iscoutbtask(self, conds: SqlConditions) -> dict:
"""按条件搜索任务，返回数据行转换成的字段字典"""
conn: SqliteConn = None
cursor = None
conds: SqlConditions = conds
try:
# ClientId,
# Platform,
# Source,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime
# TaskId,
# BatchId,
# CmdId,
# Status,
# ObjectType,
# Object,
# Progress,
# Elapsed,
# CmdRcvMsg,
# IsBatchCompleteCountIncreased,
# CreateTime,
# UpdateTime,
# Sequence,
cmd = f'''select
ClientId,
Platform,
Source,
IsPeriod,
PeriodNum,
StartTime,
EndTime,
Interval,
LastStartTime,
LastEndTime,
TaskId,
BatchId,
CmdId,
Status,
ObjectType,
Object,
Progress,
Elapsed,
CmdRcvMsg,
IsBatchCompleteCountIncreased,
CreateTime,
UpdateTime,
Sequence
FROM {self._tbname} WHERE {conds.text_normal}'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
conn._conn.row_factory = self._dict_factory
try:
cursor = conn.cursor
cursor.execute(cmd, conds.params)
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
fields: dict = {}
for i in range(len(result[0])):
fields[cursor.description[i][0].lower()] = result[0][i]
return fields
except Exception:
self._logger.error("Get IScoutTask error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("Get IScoutTask error: %s" %
traceback.format_exc())
@table_locker(__tb_IScoutBTask._tbname)
def select_iscoutbtasks(self, conds: SqlConditions) -> iter:
"""按条件搜索任务，返回数据行转换成的字段字典迭代器"""
conn: SqliteConn = None
cursor = None
conds: SqlConditions = conds
try:
# ClientId,
# Platform,
# Source,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# LastStartTime,
# LastEndTime,
# TaskId,
# BatchId,
# CmdId,
# Status,
# ObjectType,
# Object,
# Progress,
# Elapsed,
# CmdRcvMsg,
# IsBatchCompleteCountIncreased,
# CreateTime,
# UpdateTime,
# Sequence,
cmd = f'''SELECT
ClientId,
Platform,
Source,
IsPeriod,
PeriodNum,
StartTime,
EndTime,
Interval,
LastStartTime,
LastEndTime,
TaskId,
BatchId,
CmdId,
Status,
ObjectType,
Object,
Progress,
Elapsed,
CmdRcvMsg,
IsBatchCompleteCountIncreased,
CreateTime,
UpdateTime,
Sequence
FROM {self._tbname} WHERE {conds.text_normal}'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
conn._conn.row_factory = self._dict_factory
try:
cursor = conn.cursor
cursor.execute(cmd, conds.params)
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
for row in result:
fields: dict = {}
for i in range(len(result[0])):
fields[cursor.description[i][0].lower()] = row[i]
yield fields
except Exception:
self._logger.error("Get IScoutTask error: {}".format(
traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error("Get IScoutTask error: %s" %
traceback.format_exc())
@table_locker(__tb_IScoutBTask._tbname)
def update_iscoutbtask(self, platform: str, taskid: str, batchid: str,
updatefields: dict) -> Tuple[bool, bool]:
"""根据taskid+batchid+platform更新其他字段\n
task:任务对象"""
res = False
conn: SqliteConn = None
cursor = None
try:
# 搜索每个库，看有没有 TokenId一样的，且时间更新
# 的，一样就更新其他所有字段
cmd = f'''SELECT COUNT(1) FROM {self._tbname} WHERE
TaskId=? and BatchId=? and Platform=?'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (taskid, batchid, platform))
result = cursor.fetchall()
if result[0][0] > 0:
# 只根据TaskId、platform作为条件，
# 不考虑 任务文件产生时间与现有数据库中已存在任务的时间，每次直接覆盖，以符合用户操作。
# 若来了TaskId一样的数据，则必然分配给同一个ClientId
sqlset = ''
for k in updatefields.keys():
sqlset = sqlset + '{}=?,'.format(k)
sqlset = sqlset.rstrip(',')
cmd = f'''UPDATE {self._tbname} set {sqlset} WHERE TaskId=? and BatchId=? and Platform=?;'''
params = [v for v in updatefields.values()]
params.append(taskid)
params.append(batchid)
params.append(platform)
result = cursor.execute(cmd, params)
if result is None or result.rowcount < 1: # or len(result) < 1:
continue
else:
res = True
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("save new IScoutTask error: %s" %
traceback.format_exc())
return res
@table_locker(__tb_IScoutBTask._tbname)
def update_iscoutbtask_status(
self,
platform: str,
taskid: str,
batchid: str,
cmdstatus: ECommandStatus,
) -> bool:
"""更新task的Status状态字段"""
conn: SqliteConn = None
cursor = None
res: bool = False
try:
cmd = f'''UPDATE {self._tbname} set
Status=?
WHERE Platform=? and Taskid=? and BatchId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
result = cursor.execute(cmd, (
cmdstatus.value,
platform,
taskid,
batchid,
))
if not result is None and result.rowcount > 0:
res = True # 一定只有一个子任务
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error("update {} Status error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IScoutBTask._tbname)
def update_batch_complete_count_increaced_flag(
self, platform: str, taskid: str, batchid: str,
isbatchcompletecountincreased: bool) -> bool:
"""增加指定iscout子任务isbatchcompletecountincreased 字段\n"""
conn: SqliteConn = None
cursor = None
res: bool = False
try:
cmd = f'''UPDATE {self._tbname} set
IsBatchCompleteCountIncreased=?
WHERE Platform=? and TaskId=? and BatchId=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
result = cursor.execute(cmd, (
isbatchcompletecountincreased,
platform,
taskid,
batchid,
))
if not result is None and result.rowcount > 0:
res = True # 一定只有一个子任务
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error(
"batch {} batch complete count increaced flag error: {}".
format(self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IScoutBTask._tbname)
def update_iscoutbtask_back(
self,
tb: IscoutBtaskBack,
) -> bool:
"""更新iscoutbtask_back"""
conn: SqliteConn = None
cursor = None
res: bool = False
tb: IscoutBtaskBack = tb
try:
if not isinstance(tb, IscoutBtaskBack):
self._logger.error(
"Invalid param IscoutBtaskBack: {}".format(tb))
return res
# 更新策略，先搜一下有没有，并把sequence搜出来，如果
# 本地sequence和新来的任务的sequence一样，则说明
# 采集端的sequence出错了，打一句日志并返回False
cmd = f'''SELECT Sequence FROM {self._tbname} WHERE Platform=? and TaskId=? and BatchId=? and PeriodNum<=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
tb._platform,
tb._taskid,
tb._batchid,
tb.periodnum,
))
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
oldseq = result[0][0]
if oldseq >= tb._sequence:
self._logger.error(
"The comming IscoutBtaskBack.sequence is {}, but which in local db is {}:\ntaskid:{}\nbatchid:{}\nsequence:{}"
.format(tb._sequence, oldseq, tb._taskid,
tb._batchid, oldseq))
break
# ClientId,
# Platform,
# Source,
# IsPeriod,
# PeriodNum,
# StartTime,
# EndTime,
# Interval,
# TaskId,
# BatchId,
# CmdId,
# Status,
# ObjectType,
# Object,
# Progress,
# Elapsed,
# CmdRcvMsg,
# IsBatchCompleteCountIncreased,
# CreateTime,
# Sequence,
cmd = f'''UPDATE {self._tbname} set
PeriodNum=?,
Status=?,
Progress=?,
Elapsed=?,
CmdRcvMsg=?,
Sequence=?,
WHERE Platform=? and TaskId=? and BatchId=? and Sequence<?;'''
cursor = conn.cursor
result = cursor.execute(cmd, (
tb.periodnum,
tb._cmdstatus.value,
tb._progress,
tb.elapsed,
tb._cmdrcvmsg,
tb._sequence,
tb._platform,
tb._taskid,
tb._batchid,
tb._sequence,
))
if not result is None and result.rowcount > 0:
res = True # 一定只有一个子任务
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if res:
break
except Exception:
self._logger.error(
"update IScoutBatchTaskBack to {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IScoutBTask._tbname)
def get_batch_task_count_by_cmdstatus(self, platform: str, taskid: str,
cmdstatus: ECommandStatus) -> int:
"""查询指定总任务taskid下的所有 为指定命令状态的子任务 的数量"""
res: int = 0 #总数量
conn: SqliteConn = None
cursor = None
try:
cmd = f'''SELECT COUNT() FROM {self._tbname} WHERE Platform=? and TaskId=? and Status=?;'''
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
platform,
taskid,
cmdstatus.value,
))
result = cursor.fetchall()
if result is None or len(result) < 1:
continue
tmp = result[0][0]
res = res + tmp
except Exception:
self._logger.error(
"Select iscoutbtask count by status in {} error: {}".
format(self._tbname, traceback.format_exc()))
finally:
if not conn is None:
conn.close()
except Exception:
self._logger.error(
"Select iscoutbtask count by status in {} error: {}".format(
self._tbname, traceback.format_exc()))
return res
@table_locker(__tb_IScoutBTask._tbname)
def is_iscoutbatch_complete_count_increaced(self, platform: str,
taskid: str,
batchid: str) -> bool:
"""返回 指定的子任务完成情况 是否已更新到 总任务表的 batchcompletecount 字段"""
conn: SqliteConn = None
cursor = None
res: bool = False
try:
# IsBatchCompleteCountIncreased
cmd = f'''SELECT IsBatchCompleteCountIncreased FROM {self._tbname}
WHERE Platform=? and TaskId=? and BatchId=?'''
found = False
for conn in self.connect_all(5):
conn: SqliteConn = conn
try:
cursor = conn.cursor
cursor.execute(cmd, (
platform,
taskid,
batchid,
))
result = cursor.fetchall()
if not result is None and len(result) > 0:
tmp = result[0][0]
res = bool(tmp) # 一定只有一个子任务
found = True
break
except Exception as ex:
conn._conn.rollback()
raise ex
else:
conn.commit()
finally:
if not conn is None:
conn.close()
if found:
break
except Exception:
self._logger.error(
"Check {} if IsBatchCompleteCountIncreased error: {}".format(
self._tbname, traceback.format_exc()))
return res