def medias_get_delete_list():
'''
按传入的歌曲编号列表，设置新歌标记
'''
mf_list = {}
session = _mysql.get_session('karaok')
try:
cmd = "select addtime from mediahistory limit 1"
ts = session.execute(cmd).fetchall()
if ts:
first_time = ts[0][0]
else:
logger.error("medias history not found, cannot delete any medias")
return mf_list
first_time = None
time_1 = time.mktime(first_time.timetuple())
time_2 = int(time.time())
if (time_2 - time_1) / (3600 * 24) < 29:
logger.error("medias history not enough 30 days, cannot delete any medias(%s)" % first_time)
return mf_list
cmd = "select media_no, media_file from medias where media_file <> '' and media_no in "\
"(select media_no from medias_allowdel where media_no not in "\
"(select distinct(media_no) from mediahistory)) limit 200"
ts = session.execute(cmd).fetchall()
for tb in ts:
mf_list[str(tb[0])] = tb[1]
except Exception as ex:
logger.error(traceback.format_exc())
finally:
session.close()
return mf_list
def medias_create_deltable():
cmd = "create table if not exists medias_deleted(select media_no, media_file, now() as media_deltime from medias limit 0)"
session = _mysql.get_session('karaok')
try:
ts = session.execute(cmd).fetchall()
return True
except Exception as ex:
logger.error(traceback.format_exc())
finally:
session.close()
return False
def medias_update_fpath(nos):
'''
按传入的歌曲编号列表，设置新歌标记
'''
mf_list = {}
session = _mysql.get_session('karaok')
try:
cmd = "insert into medias_deleted (select media_no, media_file, now() as media_deltime from medias where media_no in (%s))" % ','.join(nos)
ts = session.execute(cmd)
cmd = "update medias set media_file='' where media_no in (%s)" % ','.join(nos)
ts = session.execute(cmd)
logger.info("update delete info: %s " % ts)
session.commit()
return True
except Exception as ex:
session.rollback()
logger.error(traceback.format_exc())
finally:
session.close()
return False
def repair_db_table(tname):
res = '数据库表%s修复成功' % tname
session = _mysql.get_session('karaok')
try:
ts = session.execute(
"select ENGINE from information_schema.TABLES where TABLE_SCHEMA='karaok' and TABLE_NAME='%s'"
% tname).fetchall()
engine = ts[0][0]
if engine != 'MyISAM':
return "%s类型的数据表不支持修复" % engine
ts = session.execute("repair table %s" % tname).fetchall()
if len(ts) > 0:
if ts[0][3] != 'OK':
res = ts[0][3]
else:
res = "没有找到要修复的数据表"
except Exception as ex:
res = str(ex)
logger.error(traceback.format_exc())
pass
finally:
session.close()
return res
def get_all_table_status():
tbs = []
session = _mysql.get_session('karaok')
try:
ts = session.execute("show tables").fetchall()
tblist = []
for tb in ts:
tblist.append(tb[0])
tblist = ','.join(tblist)
status = session.execute("check table %s" % tblist).fetchall()
for row in status:
t = {}
t['name'] = row[0][7:]
t['op'] = row[1]
t['msgtype'] = row[2]
t['msg'] = row[3]
tbs.append(t)
except Exception as ex:
logger.error(traceback.format_exc())
pass
finally:
session.close()
return tbs