def _do_exec(self, conn, sql):
for retry in range(2):
cursor = conn.cursor()
try:
cursor.execute(sql)
conn.commit()
return True
except pymysql.err.OperationalError as e:
errno, _ = e.args
if retry == 1 or errno not in [2006, 2013]:
log.v('[mysql] SQL={}\n'.format(sql))
log.e('[mysql] _do_exec() failed: {}\n'.format(
e.__str__()))
return None
log.w('[mysql] lost connection, reconnect.\n')
with self._locker:
thread_id = threading.get_ident()
if thread_id not in self._connections:
log.e('[mysql] database pool internal error.\n')
return None
_conn = self._do_connect()
if _conn is not None:
self._connections[thread_id] = _conn
conn = _conn
else:
return None
except Exception as e:
log.e('[mysql] _do_exec() failed: {}\n'.format(e.__str__()))
log.e('[mysql] SQL={}'.format(sql))
return None
finally:
cursor.close()
def _get_core_server_config(self):
try:
req = {'method': 'get_config', 'param': []}
req_data = json.dumps(req)
data = urllib.parse.quote(req_data).encode('utf-8')
req = urllib.request.Request(url=cfg.core_server_rpc, data=data)
rep = urllib.request.urlopen(req, timeout=3)
body = rep.read().decode()
x = json.loads(body)
cfg.update_core(x['data'])
except:
log.w('can not connect to core server for get config, maybe it not start yet, ignore.\n')
def _init_sqlite(self, db_file):
self.db_type = self.DB_TYPE_SQLITE
self.auto_increment = 'AUTOINCREMENT'
self.sqlite_file = db_file
self._table_prefix = 'ts_'
self._conn_pool = TPSqlitePool(db_file)
if not os.path.exists(db_file):
log.w('database need create.\n')
self.need_create = True
return True
return True
def update_core(self, conf_data):
# log.d('update core server config info.\n')
self['core'] = AttrDict()
if conf_data is None:
log.w('core server config info is empty.\n')
self['core']['detected'] = False
return True
try:
self['core']['ssh'] = AttrDict()
self['core']['ssh']['enable'] = False
self['core']['ssh']['port'] = 52189
if 'ssh' in conf_data:
self['core']['ssh']['enable'] = conf_data['ssh']['enable']
self['core']['ssh']['port'] = conf_data['ssh']['port']
self['core']['rdp'] = AttrDict()
self['core']['rdp']['enable'] = False
self['core']['rdp']['port'] = 52089
if 'rdp' in conf_data:
self['core']['rdp']['enable'] = conf_data['rdp']['enable']
self['core']['rdp']['port'] = conf_data['rdp']['port']
self['core']['telnet'] = AttrDict()
self['core']['telnet']['enable'] = False
self['core']['telnet']['port'] = 52389
if 'telnet' in conf_data:
self['core']['telnet']['enable'] = conf_data['telnet'][
'enable']
self['core']['telnet']['port'] = conf_data['telnet']['port']
if 'replay-path' in conf_data:
self['core']['replay_path'] = conf_data['replay-path']
if 'web-server-rpc' in conf_data:
self['core']['web_server_rpc'] = conf_data['web-server-rpc']
if 'version' in conf_data:
self['core']['version'] = conf_data['version']
self['core']['detected'] = True
except IndexError:
log.e('invalid core config.\n')
return False
return True
def init(self):
cfg = app_cfg()
if 'sqlite' == cfg.database.type:
if cfg.database.sqlite_file is None:
cfg.set_default('database::sqlite-file',
os.path.join(cfg.data_path, 'db', 'ts_db.db'))
if not self._init_sqlite(cfg.database.sqlite_file):
return False
elif 'mysql' == cfg.database.type:
if not self._init_mysql(
cfg.database.mysql_host, cfg.database.mysql_port,
cfg.database.mysql_db, cfg.database.mysql_prefix,
cfg.database.mysql_user, cfg.database.mysql_password):
return False
else:
log.e('unknown database type `{}`, support sqlite/mysql now.\n'.
format(cfg.database.type))
return False
# 看看数据库中是否存在指定的数据表（如果不存在，可能是一个空数据库文件），则可能是一个新安装的系统
# ret = self.query('SELECT COUNT(*) FROM `sqlite_master` WHERE `type`="table" AND `name`="{}account";'.format(self._table_prefix))
ret = self.is_table_exists('{}group'.format(self._table_prefix))
if ret is None or not ret:
log.w('database need create.\n')
self.need_create = True
return True
# 尝试从配置表中读取当前数据库版本号（如果不存在，说明是比较旧的版本了）
ret = self.query(
'SELECT `value` FROM `{}config` WHERE `name`="db_ver";'.format(
self._table_prefix))
if ret is None or 0 == len(ret):
self.current_ver = 1
else:
self.current_ver = int(ret[0][0])
if self.current_ver < self.DB_VERSION:
log.w('database need upgrade.\n')
self.need_upgrade = True
return True
# DO TEST
# self.alter_table('ts_account', [['account_id', 'id'], ['account_type', 'type']])
return True
def update_core(self, conf_data):
self.core = AttrDict()
self.core.detected = False
if conf_data is None:
log.w('core server config info is empty.\n')
return True
try:
self.core.ssh = AttrDict()
self.core.ssh.enable = False
self.core.ssh.port = 52189
if 'ssh' in conf_data:
self.core.ssh.enable = conf_data['ssh']['enable']
self.core.ssh.port = conf_data['ssh']['port']
self.core.rdp = AttrDict()
self.core.rdp.enable = False
self.core.rdp.port = 52089
if 'rdp' in conf_data:
self.core.rdp.enable = conf_data['rdp']['enable']
self.core.rdp.port = conf_data['rdp']['port']
self.core.telnet = AttrDict()
self.core.telnet.enable = False
self.core.telnet.port = 52389
if 'telnet' in conf_data:
self.core.telnet.enable = conf_data['telnet']['enable']
self.core.telnet.port = conf_data['telnet']['port']
if 'replay-path' in conf_data:
self.core.replay_path = conf_data['replay-path']
if 'web-server-rpc' in conf_data:
self.core.web_server_rpc = conf_data['web-server-rpc']
if 'version' in conf_data:
self.core.version = conf_data['version']
self.core.detected = True
except IndexError:
log.e('invalid core config.\n')
return False
return True
def init(self, db_source):
self.db_source = db_source
if db_source['type'] == self.DB_TYPE_MYSQL:
log.e('MySQL not supported yet.')
return False
elif db_source['type'] == self.DB_TYPE_SQLITE:
self._table_prefix = 'ts_'
self._conn_pool = TPSqlitePool(db_source['file'])
if not os.path.exists(db_source['file']):
log.w('database need create.\n')
self.need_create = True
return True
else:
log.e('Unknown database type: {}'.format(db_source['type']))
return False
# 看看数据库中是否存在指定的数据表（如果不存在，可能是一个空数据库文件），则可能是一个新安装的系统
# ret = self.query('SELECT COUNT(*) FROM `sqlite_master` WHERE `type`="table" AND `name`="{}account";'.format(self._table_prefix))
ret = self.is_table_exists('{}group'.format(self._table_prefix))
if ret is None or not ret:
log.w('database need create.\n')
self.need_create = True
return True
# 尝试从配置表中读取当前数据库版本号（如果不存在，说明是比较旧的版本了）
ret = self.query(
'SELECT `value` FROM `{}config` WHERE `name`="db_ver";'.format(
self._table_prefix))
if ret is None or 0 == len(ret):
self.current_ver = 1
else:
self.current_ver = int(ret[0][0])
if self.current_ver < self.DB_VERSION:
log.w('database need upgrade.\n')
self.need_upgrade = True
return True
# DO TEST
# self.alter_table('ts_account', [['account_id', 'id'], ['account_type', 'type']])
return True
def alter_table(self, table_names, field_names=None):
"""
修改表名称及字段名称
table_name: 如果是string，则指定要操作的表，如果是list，则第一个元素是要操作的表，第二个元素是此表改名的目标名称
fields_names: 如果为None，则不修改字段名，否则应该是一个list，其中每个元素是包含两个str的list，表示将此list第一个指定的字段改名为第二个指定的名称
@return: None or Boolean
"""
# TODO: 此函数尚未完成
if self.db_type == self.DB_TYPE_SQLITE:
if not isinstance(table_names, list) and field_names is None:
log.w('nothing to do.\n')
return False
if isinstance(table_names, str):
old_table_name = table_names
new_table_name = table_names
elif isinstance(table_names, list) and len(table_names) == 2:
old_table_name = table_names[0]
new_table_name = table_names[1]
else:
log.w('invalid param.\n')
return False
if isinstance(field_names, list):
for i in field_names:
if not isinstance(i, list) or 2 != len(i):
log.w('invalid param.\n')
return False
if field_names is None:
# 仅数据表改名
return self.exec('ALTER TABLE `{}` RENAME TO `{}`;'.format(
old_table_name, new_table_name))
else:
# sqlite不支持字段改名，所以需要通过临时表中转一下
# 先获取数据表的字段名列表
ret = self.query(
'SELECT * FROM `sqlite_master` WHERE `type`="table" AND `name`="{}";'
.format(old_table_name))
log.w('-----\n')
log.w(ret[0][4])
log.w('\n')
# 先将数据表改名，成为一个临时表
# tmp_table_name = '{}_sqlite_tmp'.format(old_table_name)
# ret = self.exec('ALTER TABLE `{}` RENAME TO `{}`;'.format(old_table_name, tmp_table_name))
# if ret is None or not ret:
# return ret
pass
elif self.db_type == self.DB_TYPE_MYSQL:
log.e('mysql not supported yet.\n')
return False
else:
log.e('Unknown database type.\n')
return False