def is_table_exists(self, table_name):
"""
判断指定的表是否存在
@param table_name: string
@return: None or Boolean
"""
if self.db_type == self.DB_TYPE_SQLITE:
ret = self.query(
'SELECT COUNT(*) FROM `sqlite_master` WHERE `type`="table" AND `name`="{}";'
.format(table_name))
if ret is None:
return None
if len(ret) == 0:
return False
if ret[0][0] == 0:
return False
return True
elif self.db_type == self.DB_TYPE_MYSQL:
ret = self.query(
'SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA="{}" and TABLE_NAME="{}";'
.format(self.mysql_db, table_name))
if ret is None:
return None
if len(ret) == 0:
return False
else:
return True
else:
log.e('Unknown database type.\n')
return None
def read_record_info(record_id, file_id):
record_path = os.path.join(app_cfg().core.replay_path, 'ssh',
'{:06d}'.format(int(record_id)))
file_info = os.path.join(record_path, 'tp-ssh.{:03d}'.format(int(file_id)))
file = None
try:
file = open(file_info, 'rb')
data = file.read()
total_size = len(data)
offset = 0
data_size, = struct.unpack_from('I', data, offset)
offset += 4
data_list = list()
while True:
action, = struct.unpack_from('B', data, offset)
offset += 1
_size, = struct.unpack_from('I', data, offset)
offset += 4
_time, = struct.unpack_from('I', data, offset)
offset += 4
# skip reserved 3 bytes.
offset += 3
_format = '{}s'.format(_size)
_data, = struct.unpack_from(_format, data, offset)
offset += _size
temp = dict()
temp['a'] = action
temp['t'] = _time
if action == 1:
# this is window size changed.
w, h = struct.unpack_from('HH', _data)
temp['w'] = w
temp['h'] = h
elif action == 2:
_data = _data.decode()
# this is ssh data.
temp['d'] = _data
else:
return None
data_list.append(temp)
if offset == total_size:
break
except Exception as e:
log.e('failed to read record file: {}\n'.format(file_info))
return None
finally:
if file is not None:
file.close()
return data_list
def upgrade_database(self, step_begin, step_end):
log.v('start database upgrade process.\n')
if DatabaseUpgrade(self, step_begin, step_end).do_upgrade():
log.v('database upgraded.\n')
self.need_upgrade = False
return True
else:
log.e('database upgrade failed.\n')
return False
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
def _do_exec(self, conn, sql):
try:
with conn:
conn.execute(sql)
return True
except Exception as e:
log.e('[sqlite] _do_exec() failed: {}\n'.format(e.__str__()))
log.e('[sqlite] SQL={}'.format(sql))
return False
def _do_query(self, conn, sql):
cursor = conn.cursor()
try:
cursor.execute(sql)
db_ret = cursor.fetchall()
return db_ret
except Exception as e:
log.e('[sqlite] _do_query() failed: {}\n'.format(e.__str__()))
log.e('[sqlite] SQL={}'.format(sql))
finally:
cursor.close()
def _do_connect(self):
if not os.path.exists(self._db_file):
log.e('[sqlite] can not connect, database file not exists.\n')
return None
try:
return sqlite3.connect(self._db_file)
except:
log.e(
'[sqlite] can not connect, does the database file correct?\n')
return None
def _do_transaction(self, conn, sql_list):
try:
# 使用context manager，发生异常时会自动rollback，正常执行完毕后会自动commit
with conn:
for sql in sql_list:
conn.execute(sql)
return True
except Exception as e:
log.e('[sqlite] _do_transaction() failed: {}\n'.format(
e.__str__()))
return False
def _do_exec(self, conn, sql):
cursor = conn.cursor()
try:
cursor.execute(sql)
conn.commit()
return True
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 _last_insert_id(self, conn):
cursor = conn.cursor()
try:
cursor.execute('SELECT last_insert_rowid();')
db_ret = cursor.fetchall()
return db_ret[0][0]
except Exception as e:
log.e('[sqlite] _last_insert_id() failed: {}\n'.format(
e.__str__()))
return -1
finally:
cursor.close()
def _last_insert_id(self, conn):
cursor = conn.cursor()
try:
cursor.execute('SELECT LAST_INSERT_ID();')
db_ret = cursor.fetchall()
conn.commit()
return db_ret[0][0]
except Exception as e:
log.e('[mysql] _last_insert_id() failed: {}\n'.format(e.__str__()))
return -1
finally:
cursor.close()
def _upgrade_to_v5(self):
_step = self.step_begin('检查数据库版本v5...')
# 服务端升级到版本2.1.0.1时，为解决将来数据库升级的问题，在 ts_config 表中加入 db_ver 指明当前数据结构版本
try:
# 判断依据：
# 如果 config 表中不存在名为db_ver的数据，说明是旧版本，需要升级
if not self.db.is_table_exists('{}config'.format(
self.db.table_prefix)):
if not self.db.exec("""CREATE TABLE `{}config` (
`name` varchar(256) NOT NULL,
`value` varchar(256),
PRIMARY KEY (`name` ASC)
);""".format(self.db.table_prefix)):
self.step_end(_step, -1, 'config表不存在且无法创建')
return False
db_ret = self.db.query(
'SELECT `value` FROM `{}config` WHERE `name`="db_ver";'.format(
self.db.table_prefix))
if db_ret is None:
self.step_end(_step, -1)
return False
if len(db_ret) > 0 and int(db_ret[0][0]) >= self.db.DB_VERSION:
self.step_end(_step, 0, '跳过 v4 到 v5 的升级操作')
return True
self.step_end(_step, 0, '需要升级到v5')
_step = self.step_begin(' - 调整数据表字段名与表名')
if not self.db.exec(
'ALTER TABLE `{}cert` RENAME TO `{}key`;'.format(
self.db.table_prefix, self.db.table_prefix)):
self.step_end(_step, -1)
return False
self.step_end(_step, 0)
_step = self.step_begin(' - 更新数据库版本号')
if not self.db.exec(
'INSERT INTO `{}config` VALUES ("db_ver", "{}");'.format(
self.db.table_prefix, self.db.DB_VERSION)):
self.step_end(_step, -1)
return False
else:
self.step_end(_step, 0)
return True
except:
log.e('failed.\n')
self.step_end(_step, -1)
return False
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 _do_connect(self):
try:
return pymysql.connect(host=self._host,
user=self._user,
passwd=self._password,
db=self._db_name,
port=self._port,
autocommit=False,
connect_timeout=3.0,
charset='utf8')
except Exception as e:
log.e('[mysql] connect [{}:{}] failed: {}\n'.format(
self._host, self._port, e.__str__()))
return None
def _do_transaction(self, conn, sql_list):
cursor = conn.cursor()
try:
conn.begin()
for sql in sql_list:
cursor.execute(sql)
conn.commit()
return True
except Exception as e:
conn.rollback()
log.e('[mysql] _do_transaction() failed: {}\n'.format(e.__str__()))
return False
finally:
cursor.close()
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 load(self, cfg_file):
if not os.path.exists(cfg_file):
log.e(
'configuration file does not exists: [{}]\n'.format(cfg_file))
return False
try:
_cfg = configparser.ConfigParser()
_cfg.read(cfg_file)
except:
log.e('can not load configuration file: [{}]\n'.format(cfg_file))
return False
if not self._on_load(_cfg):
return False
self['_cfg_file'] = cfg_file
return True
def run(self):
settings = {
#
'cookie_secret': '8946svdABGD345fg98uhIaefEBePIfegOIakjFH43oETzK',
'login_url': '/auth/login',
# 指定静态文件的路径，页面模板中可以用 {{ static_url('css/main.css') }} 的方式调用
'static_path': cfg.static_path,
# 指定模板文件的路径
'template_path': cfg.template_path,
# 防止跨站伪造请求，参见 http://old.sebug.net/paper/books/tornado/#_7
'xsrf_cookies': False,
'autoescape': 'xhtml_escape',
# 'ui_modules': ui_modules,
'debug': False,
# 不开启模板和静态文件的缓存，这样一旦模板文件和静态文件变化，刷新浏览器即可看到更新。
'compiled_template_cache': False,
'static_hash_cache': False,
}
from eom_app.controller import controllers
web_app = tornado.web.Application(controllers, **settings)
server = tornado.httpserver.HTTPServer(web_app)
try:
server.listen(cfg.server_port)
log.i('works on [http://127.0.0.1:{}]\n'.format(cfg.server_port))
except:
log.e('Can not listen on port {}, maybe it been used by another application.\n'.format(cfg.server_port))
return 0
# 启动session超时管理
web_session().start()
tornado.ioloop.IOLoop.instance().start()
web_session().stop()
return 0
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):
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 init(self, options):
log.initialize()
cfg.app_path = os.path.abspath(options['app_path'])
cfg.static_path = os.path.abspath(options['static_path'])
cfg.data_path = os.path.abspath(options['data_path'])
cfg.template_path = os.path.abspath(options['template_path'])
cfg.res_path = os.path.abspath(options['res_path'])
cfg.cfg_path = os.path.abspath(options['cfg_path'])
_cfg_file = os.path.join(cfg.cfg_path, 'web.ini')
if not cfg.load(_cfg_file):
return False
cfg.log_path = os.path.abspath(options['log_path'])
cfg.log_file = os.path.join(cfg.log_path, 'tpweb.log')
if not os.path.exists(cfg.log_path):
utils.make_dir(cfg.log_path)
if not os.path.exists(cfg.log_path):
log.e('Can not create log path:{}\n'.format(cfg.log_path))
return False
log.set_attribute(min_level=cfg.log_level, filename=cfg.log_file)
if cfg.debug:
log.set_attribute(trace_error=log.TRACE_ERROR_FULL)
# 尝试通过CORE-JSON-RPC获取core服务的配置（主要是ssh/rdp/telnet的端口）
self._get_core_server_config()
if not web_session().init():
return False
# TODO: 根据配置文件来决定使用什么数据库（初始安装时还没有配置数据库信息）
_db = get_db()
if not _db.init({'type': _db.DB_TYPE_SQLITE, 'file': os.path.join(cfg.data_path, 'ts_db.db')}):
log.e('initialize database interface failed.\n')
return False
if _db.need_create or _db.need_upgrade:
cfg.app_mode = APP_MODE_MAINTENANCE
else:
cfg.app_mode = APP_MODE_NORMAL
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 create_and_init(self, step_begin, step_end):
log.v('start database create and initialization process.\n')
if self.db_type == self.DB_TYPE_SQLITE:
db_path = os.path.dirname(self.sqlite_file)
if not os.path.exists(db_path):
utils.make_dir(db_path)
if not os.path.exists(db_path):
log.e(
'can not create folder `{}` to store database file.\n'.
format(db_path))
return False
# 创建一个空数据文件，这样才能进行connect。
if not os.path.exists(self.sqlite_file):
try:
with open(self.sqlite_file, 'w') as f:
pass
except:
log.e('can not create db file `{}`.\n'.format(
self.sqlite_file))
return False
if create_and_init(self, step_begin, step_end):
log.v('database created.\n')
self.need_create = False
return True
else:
log.e('database create and initialize failed.\n')
return False
def is_field_exists(self, table_name, field_name):
if self.db_type == self.DB_TYPE_SQLITE:
ret = self.query('PRAGMA table_info(`{}`);'.format(table_name))
print(ret)
if ret is None:
return None
if len(ret) == 0:
return False
else:
return True
elif self.db_type == self.DB_TYPE_MYSQL:
ret = self.query('DESC `{}` `{}`;'.format(table_name, field_name))
print(ret)
if ret is None:
return None
if len(ret) == 0:
return False
else:
return True
else:
log.e('Unknown database type.\n')
return None
def is_table_exists(self, table_name):
"""
判断指定的表是否存在
@param table_name: string
@return: None or Boolean
"""
if self.db_source['type'] == self.DB_TYPE_SQLITE:
ret = self.query(
'SELECT COUNT(*) FROM `sqlite_master` WHERE `type`="table" AND `name`="{}";'
.format(table_name))
if ret is None:
return None
if len(ret) == 0:
return False
if ret[0][0] == 0:
return False
return True
elif self.db_source['type'] == self.DB_TYPE_MYSQL:
return None
else:
log.e('Unknown database type.\n')
return None
def _upgrade_to_v6(self):
_step = self.step_begin('检查数据库版本v6...')
# 服务端升级到版本2.2.9时，为增加双因子认证，为account表增加oath_secret字段
db_ret = self.db.is_field_exists(
'{}account'.format(self.db.table_prefix), 'oath_secret')
if db_ret is None:
self.step_end(_step, -1, '无法连接到数据库')
return False
if db_ret:
self.step_end(_step, 0, '跳过 v5 到 v6 的升级操作')
return True
self.step_end(_step, 0, '需要升级到v6')
try:
_step = self.step_begin(' - 在account表中加入oath_secret字段')
if not self.db.exec(
'ALTER TABLE {}account ADD oath_secret VARCHAR(64) DEFAULT ""'
.format(self.db.table_prefix)):
self.step_end(_step, -1, '失败')
return False
_step = self.step_begin(' - 更新数据库版本号')
if not self.db.exec(
'UPDATE `{}config` SET `value`="6" WHERE `name`="db_ver";'.
format(self.db.table_prefix)):
self.step_end(_step, -1, '无法更新数据库版本号')
return False
else:
self.step_end(_step, 0)
return True
except:
log.e('failed.\n')
self.step_end(_step, -1)
return False
def load(self, cfg_file):
if not os.path.exists(cfg_file):
log.e(
'configuration file does not exists: [{}]\n'.format(cfg_file))
return False
try:
_cfg = configparser.ConfigParser()
_cfg.read(cfg_file)
except:
log.e('can not load configuration file: [{}]\n'.format(cfg_file))
return False
if 'common' not in _cfg:
log.e('invalid configuration file: [{}]\n'.format(cfg_file))
return False
_comm = _cfg['common']
self['server_port'] = _comm.getint('port', 7190)
self['log_file'] = _comm.get('log-file', None)
if self['log_file'] is not None:
self['log_path'] = os.path.dirname(self['log_file'])
self['log_level'] = log.LOG_INFO
_level = _comm.getint('log-level', 2)
if _level == 0:
self['log_level'] = log.LOG_DEBUG
elif _level == 1:
self['log_level'] = log.LOG_VERBOSE
elif _level == 2:
self['log_level'] = log.LOG_INFO
elif _level == 3:
self['log_level'] = log.LOG_WARN
elif _level == 4:
self['log_level'] = log.LOG_ERROR
else:
self['log_level'] = log.LOG_VERBOSE
# log.set_attribute(min_level=self['log_level'])
self['debug'] = False
_debug = _comm.getint('debug', 0)
if _debug == 1:
self['log_level'] = log.LOG_DEBUG
self['debug'] = True
self['core_server_rpc'] = _comm.get('core-server-rpc',
'http://127.0.0.1:52080/rpc')
return True
def _upgrade_to_v2(self):
# 服务端升级到版本1.2.102.3时，管理员后台和普通用户后台合并了，数据库略有调整
_step = self.step_begin('检查数据库版本v2...')
try:
# 判断依据：
# 如果存在名为 ${prefix}sys_user 的表，说明是旧版本，需要升级
ret = self.db.is_table_exists('{}sys_user'.format(
self.db.table_prefix))
if ret is None:
self.step_end(_step, -1, '无法连接到数据库')
return False
elif not ret:
self.step_end(_step, 0, '跳过 v1 到 v2 的升级操作')
return True
self.step_end(_step, 0, '需要升级到v2')
if self.db.db_source['type'] == self.db.DB_TYPE_SQLITE:
_step = self.step_begin(' - 备份数据库文件')
_bak_file = '{}.before-v1-to-v2'.format(
self.db.db_source['file'])
if not os.path.exists(_bak_file):
shutil.copy(self.db.db_source['file'], _bak_file)
self.step_end(_step, 0)
# 将原来的普通用户的account_type从 0 改为 1
_step = self.step_begin(' - 调整用户账号类型...')
if not self.db.exec(
'UPDATE `{}account` SET `account_type`=1 WHERE `account_type`=0;'
.format(self.db.table_prefix)):
self.step_end(_step, -1)
return False
else:
self.step_end(_step, 0)
# 将原来的管理员合并到用户账号表中
_step = self.step_begin(' - 合并管理员和普通用户账号...')
db_ret = self.db.query('SELECT * FROM `{}sys_user`;'.format(
self.db.table_prefix))
if db_ret is None:
self.step_end(_step, 0)
return True
for i in range(len(db_ret)):
user_name = db_ret[i][1]
user_pwd = db_ret[i][2]
if not self.db.exec("""INSERT INTO `{}account`
(`account_type`, `account_name`, `account_pwd`, `account_status`, `account_lock`, `account_desc`)
VALUES (100,"{}","{}",0,0,"{}");""".format(self.db.table_prefix, user_name,
user_pwd, '超级管理员')):
self.step_end(_step, -1)
return False
# 移除旧的表（暂时改名而不是真的删除）
_step = self.step_begin(' - 移除不再使用的数据表...')
if not self.db.exec(
'ALTER TABLE `{}sys_user` RENAME TO `_bak_ts_sys_user`;'.
format(self.db.table_prefix)):
self.step_end(_step, 0)
return False
else:
self.step_end(_step, -1)
return True
except:
log.e('failed.\n')
self.step_end(_step, -1)
return False
def _upgrade_to_v4(self):
_step = self.step_begin('检查数据库版本v4...')
# 服务端升级到版本1.6.224.3时，加入telnet支持，数据库有调整
try:
# 判断依据：
# 如果ts_host_info表中还有pro_port字段，说明是旧版本，需要升级
db_ret = self.db.query(
'SELECT `pro_port` FROM `{}host_info` LIMIT 0;'.format(
self.db.table_prefix))
if db_ret is None:
self.step_end(_step, 0, '跳过 v3 到 v4 的升级操作')
return True
self.step_end(_step, 0, '需要升级到v4')
if self.db.db_source['type'] == self.db.DB_TYPE_SQLITE:
_step = self.step_begin(' - 备份数据库文件')
_bak_file = '{}.before-v3-to-v4'.format(
self.db.db_source['file'])
if not os.path.exists(_bak_file):
shutil.copy(self.db.db_source['file'], _bak_file)
self.step_end(_step, 0)
_step = self.step_begin(' - 为telnet增加默认配置')
# 如果ts_config表中没有ts_server_telnet_port项，则增加默认值52389
db_ret = self.db.query(
'SELECT * FROM `{}config` WHERE `name`="ts_server_telnet_port";'
.format(self.db.table_prefix))
if len(db_ret) == 0:
if not self.db.exec(
'INSERT INTO `{}config` (`name`, `value`) VALUES ("ts_server_telnet_port", "52389");'
.format(self.db.table_prefix)):
self.step_end(_step, -1)
return False
self.step_end(_step, 0)
_step = self.step_begin(' - 调整认证数据表数据...')
auth_info_ret = self.db.query(
'SELECT `id`, `host_id`, `pro_type`, `auth_mode`, `user_name`, `user_pswd`, `cert_id`, `encrypt`, `log_time` FROM `{}auth_info`;'
.format(self.db.table_prefix))
auth_ret = self.db.query(
'SELECT `auth_id`, `account_name`, `host_id`, `host_auth_id` FROM `{}auth`;'
.format(self.db.table_prefix))
max_host_id = 0
new_host_info = []
new_auth_info = []
new_auth = []
# 从原来的表中查询数据
host_info_ret = self.db.query(
'SELECT `host_id`, `group_id`, `host_sys_type`, `host_ip`, `pro_port`, `host_lock`, `host_desc` FROM {}host_info;'
.format(self.db.table_prefix))
if host_info_ret is None:
self.step_end(_step, 0, '尚无认证数据，跳过处理')
return True
# 先找出最大的host_id，这样如果要拆分一个host，就知道新的host_id应该是多少了
for i in range(len(host_info_ret)):
if host_info_ret[i][0] > max_host_id:
max_host_id = host_info_ret[i][0]
max_host_id += 1
# 然后构建新的host列表
for i in range(len(host_info_ret)):
host_info = {}
host_info_alt = None
protocol = json.loads(host_info_ret[i][4])
host_info['host_id'] = host_info_ret[i][0]
host_info['group_id'] = host_info_ret[i][1]
host_info['host_sys_type'] = host_info_ret[i][2]
host_info['host_ip'] = host_info_ret[i][3]
host_info['host_lock'] = host_info_ret[i][5]
host_info['host_desc'] = host_info_ret[i][6]
host_info['_old_host_id'] = host_info_ret[i][0]
host_info['host_port'] = 0
host_info['protocol'] = 0
have_rdp = False
have_ssh = False
if auth_info_ret is not None:
for j in range(len(auth_info_ret)):
if auth_info_ret[j][1] == host_info['host_id']:
if auth_info_ret[j][2] == 1: # 用到了此主机的RDP
have_rdp = True
elif auth_info_ret[j][2] == 2: # 用到了此主机的SSH
have_ssh = True
if have_rdp and have_ssh:
# 需要拆分
host_info['protocol'] = 1
host_info['host_port'] = protocol['rdp']['port']
host_info_alt = {}
host_info_alt['host_id'] = max_host_id
max_host_id += 1
host_info_alt['group_id'] = host_info_ret[i][1]
host_info_alt['host_sys_type'] = host_info_ret[i][2]
host_info_alt['host_ip'] = host_info_ret[i][3]
host_info_alt['host_lock'] = host_info_ret[i][5]
host_info_alt['host_desc'] = host_info_ret[i][6]
host_info_alt['_old_host_id'] = host_info_ret[i][0]
host_info_alt['host_port'] = protocol['ssh']['port']
host_info_alt['protocol'] = 2
elif have_rdp:
host_info['protocol'] = 1
host_info['host_port'] = protocol['rdp']['port']
elif have_ssh:
host_info['host_port'] = protocol['ssh']['port']
host_info['protocol'] = 2
new_host_info.append(host_info)
if host_info_alt is not None:
new_host_info.append(host_info_alt)
# 现在有了新的ts_host_info表，重构ts_auth_info表
if auth_info_ret is not None:
for i in range(len(auth_info_ret)):
auth_info = {}
auth_info['id'] = auth_info_ret[i][0]
auth_info['auth_mode'] = auth_info_ret[i][3]
auth_info['user_name'] = auth_info_ret[i][4]
auth_info['user_pswd'] = auth_info_ret[i][5]
auth_info['cert_id'] = auth_info_ret[i][6]
auth_info['encrypt'] = auth_info_ret[i][7]
auth_info['log_time'] = auth_info_ret[i][8]
auth_info['user_param'] = 'ogin:\nassword:'
found = False
for j in range(len(new_host_info)):
if auth_info_ret[i][1] == new_host_info[j][
'_old_host_id'] and auth_info_ret[i][
2] == new_host_info[j]['protocol']:
found = True
auth_info['host_id'] = new_host_info[j]['host_id']
auth_info['_old_host_id'] = new_host_info[j][
'_old_host_id']
break
if found:
new_auth_info.append(auth_info)
# 最后重构ts_auth表
if auth_ret is not None:
for i in range(len(auth_ret)):
auth = {}
auth['auth_id'] = auth_ret[i][0]
auth['account_name'] = auth_ret[i][1]
found = False
for j in range(len(new_auth_info)):
if auth_ret[i][2] == new_auth_info[j][
'_old_host_id'] and auth_ret[i][
3] == new_auth_info[j]['id']:
found = True
auth['host_id'] = new_auth_info[j]['host_id']
auth['host_auth_id'] = new_auth_info[j]['id']
break
if found:
new_auth.append(auth)
self.step_end(_step, 0)
_step = self.step_begin(' - 重新整理认证数据表结构及数据...')
# 将整理好的数据写入新的临时表
# 先创建三个临时表
if not self.db.exec("""CREATE TABLE `{}auth_tmp` (
`auth_id` INTEGER PRIMARY KEY AUTOINCREMENT,
`account_name` varchar(256),
`host_id` INTEGER,
`host_auth_id` int(11) NOT NULL
);""".format(self.db.table_prefix)):
self.step_end(_step, -1, '无法创建认证数据临时表')
return False
if not self.db.exec("""CREATE TABLE `{}host_info_tmp` (
`host_id` integer PRIMARY KEY AUTOINCREMENT,
`group_id` int(11) DEFAULT 0,
`host_sys_type` int(11) DEFAULT 1,
`host_ip` varchar(32) DEFAULT '',
`host_port` int(11) DEFAULT 0,
`protocol` int(11) DEFAULT 0,
`host_lock` int(11) DEFAULT 0,
`host_desc` DEFAULT ''
);""".format(self.db.table_prefix)):
self.step_end(_step, -1, '无法创建主机信息数据临时表')
return False
if not self.db.exec("""CREATE TABLE `{}auth_info_tmp` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`host_id` INTEGER,
`auth_mode` INTEGER,
`user_name` varchar(256),
`user_pswd` varchar(256),
`user_param` varchar(256),
`cert_id` INTEGER,
`encrypt` INTEGER,
`log_time` varchar(60)
);""".format(self.db.table_prefix)):
self.step_end(_step, -1, '无法创建认证信息数据临时表')
return False
for i in range(len(new_host_info)):
sql = 'INSERT INTO `{}host_info_tmp` (`host_id`, `group_id`, `host_sys_type`, `host_ip`, `host_port`, `protocol`, `host_lock`, `host_desc`) ' \
'VALUES ({}, {}, {}, \'{}\', {}, {}, {}, "{}");'.format(
self.db.table_prefix,
new_host_info[i]['host_id'], new_host_info[i]['group_id'], new_host_info[i]['host_sys_type'],
new_host_info[i]['host_ip'], new_host_info[i]['host_port'], new_host_info[i]['protocol'],
new_host_info[i]['host_lock'], new_host_info[i]['host_desc']
)
if not self.db.exec(sql):
self.step_end(_step, -1, '无法调整数据(1)')
return False
for i in range(len(new_auth_info)):
sql = 'INSERT INTO `{}auth_info_tmp` (`id`, `host_id`, `auth_mode`, `user_name`, `user_pswd`, `user_param`, `cert_id`, `encrypt`, `log_time`) ' \
'VALUES ({}, {}, {}, "{}", "{}", "{}", {}, {}, "{}");'.format(
self.db.table_prefix,
new_auth_info[i]['id'], new_auth_info[i]['host_id'], new_auth_info[i]['auth_mode'],
new_auth_info[i]['user_name'], new_auth_info[i]['user_pswd'], new_auth_info[i]['user_param'],
new_auth_info[i]['cert_id'], new_auth_info[i]['encrypt'], '1'
)
if not self.db.exec(sql):
self.step_end(_step, -1, '无法调整数据(2)')
return False
for i in range(len(new_auth)):
sql = 'INSERT INTO `{}auth_tmp` (`auth_id`, `account_name`, `host_id`, `host_auth_id`) ' \
'VALUES ({}, \'{}\', {}, {});'.format(
self.db.table_prefix,
new_auth[i]['auth_id'], new_auth[i]['account_name'], new_auth[i]['host_id'], new_auth[i]['host_auth_id']
)
if not self.db.exec(sql):
self.step_end(_step, -1, '无法调整数据(3)')
return False
# 表改名
if not self.db.exec(
'ALTER TABLE `{}auth` RENAME TO `__bak_{}auth`;'.format(
self.db.table_prefix, self.db.table_prefix)):
self.step_end(_step, -1, '无法处理临时表(1)')
return False
if not self.db.exec(
'ALTER TABLE `{}auth_info` RENAME TO `__bak_{}auth_info`;'.
format(self.db.table_prefix, self.db.table_prefix)):
self.step_end(_step, -1, '无法处理临时表(2)')
return False
if not self.db.exec(
'ALTER TABLE `{}host_info` RENAME TO `__bak_{}host_info`;'.
format(self.db.table_prefix, self.db.table_prefix)):
self.step_end(_step, -1, '无法处理临时表(3)')
return False
if not self.db.exec(
'ALTER TABLE `{}auth_tmp` RENAME TO `{}auth`;'.format(
self.db.table_prefix, self.db.table_prefix)):
self.step_end(_step, -1, '无法处理临时表(4)')
return False
if not self.db.exec(
'ALTER TABLE `{}auth_info_tmp` RENAME TO `{}auth_info`;'.
format(self.db.table_prefix, self.db.table_prefix)):
self.step_end(_step, -1, '无法处理临时表(5)')
return False
if not self.db.exec(
'ALTER TABLE `{}host_info_tmp` RENAME TO `{}host_info`;'.
format(self.db.table_prefix, self.db.table_prefix)):
self.step_end(_step, -1, '无法处理临时表(6)')
return False
self.step_end(_step, 0)
return True
except:
log.e('failed.\n')
self.step_end(_step, -1)
return False
def _upgrade_to_v3(self):
# 服务端升级到版本1.5.217.9时，为了支持一机多用户多协议，数据库结构有较大程度改动
_step = self.step_begin('检查数据库版本v3...')
try:
# 判断依据：
# 如果不存在名为 ts_host_info 的表，说明是旧版本，需要升级
ret = self.db.is_table_exists('{}host_info'.format(
self.db.table_prefix))
if ret is None:
self.step_end(_step, -1)
return False
elif ret:
self.step_end(_step, 0, '跳过 v2 到 v3 的升级操作')
return True
self.step_end(_step, 0, '需要升级到v3')
if self.db.db_source['type'] == self.db.DB_TYPE_SQLITE:
_step = self.step_begin(' - 备份数据库文件')
_bak_file = '{}.before-v2-to-v3'.format(
self.db.db_source['file'])
if not os.path.exists(_bak_file):
shutil.copy(self.db.db_source['file'], _bak_file)
self.step_end(_step, 0)
_step = self.step_begin(' - 调整数据表...')
if not self.db.exec(
'ALTER TABLE `{}auth` ADD `host_auth_id` INTEGER;'.format(
self.db.table_prefix)):
self.step_end(_step, -1, '无法在auth表中加入host_auth_id字段')
return False
if not self.db.exec(
'UPDATE `{}auth` SET `host_auth_id`=`host_id`;'.format(
self.db.table_prefix)):
self.step_end(_step, -1,
'无法将auth表中host_auth_id字段的值均调整为host_id字段的值')
return False
if not self.db.exec(
'ALTER TABLE `{}log` ADD `protocol` INTEGER;'.format(
self.db.table_prefix)):
self.step_end(_step, -1, '无法在log表中加入protocol字段')
return False
if not self.db.exec(
'UPDATE `{}log` SET `protocol`=1 WHERE `sys_type`=1;'.
format(self.db.table_prefix)):
self.step_end(_step, -1, '无法修正log表中的protocol字段数据(1)')
return False
if not self.db.exec(
'UPDATE `{}log` SET `protocol`=2 WHERE `sys_type`=2;'.
format(self.db.table_prefix)):
self.step_end(_step, -1, '无法修正log表中的protocol字段数据(2)')
return False
if not self.db.exec(
'UPDATE `{}log` SET `ret_code`=9999 WHERE `ret_code`=0;'.
format(self.db.table_prefix)):
self.step_end(_step, -1, '无法修正log表中的ret_code字段数据')
return False
self.step_end(_step, 0)
_step = self.step_begin(' - 拆分数据表...')
# 新建两个表，用于拆分原来的 ts_host 表
if not self.db.exec("""CREATE TABLE `{}host_info` (
`host_id` integer PRIMARY KEY AUTOINCREMENT,
`group_id` int(11) DEFAULT 0,
`host_sys_type` int(11) DEFAULT 1,
`host_ip` varchar(32) DEFAULT '',
`pro_port` varchar(256) NULL,
`host_lock` int(11) DEFAULT 0,
`host_desc` varchar(128) DEFAULT ''
);""".format(self.db.table_prefix)):
self.step_end(_step, -1)
return False
if not self.db.exec("""CREATE TABLE `{}auth_info` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`host_id` INTEGER,
`pro_type` INTEGER,
`auth_mode` INTEGER,
`user_name` varchar(256),
`user_pswd` varchar(256),
`cert_id` INTEGER,
`encrypt` INTEGER,
`log_time` varchar(60)
);""".format(self.db.table_prefix)):
self.step_end(_step, -1)
return False
# 将原来的 ts_host 表改名
if not self.db.exec(
'ALTER TABLE `{}host` RENAME TO `_bak_{}host;`'.format(
self.db.table_prefix, self.db.table_prefix)):
self.step_end(_step, -1)
return False
self.step_end(_step, 0)
_step = self.step_begin(' - 调整数据内容...')
# 从原来 ts_host 表中查询出所有数据
db_ret = self.db.query('SELECT * FROM `_bak_{}host;`'.format(
self.db.table_prefix))
if db_ret is not None:
for i in range(len(db_ret)):
host_id = db_ret[i][0]
group_id = db_ret[i][1]
host_sys_type = db_ret[i][2]
host_ip = db_ret[i][3]
host_pro_port = db_ret[i][4]
host_user_name = db_ret[i][5]
host_user_pwd = db_ret[i][6]
host_pro_type = db_ret[i][7]
cert_id = db_ret[i][8]
host_lock = db_ret[i][9]
host_encrypt = db_ret[i][10]
host_auth_mode = db_ret[i][11]
host_desc = db_ret[i][12]
_pro_port = {}
_pro_port['ssh'] = {}
_pro_port['ssh']['enable'] = 0
_pro_port['ssh']['port'] = 22
_pro_port['rdp'] = {}
_pro_port['rdp']['enable'] = 0
_pro_port['rdp']['port'] = 3389
if host_pro_type == 1:
_pro_port['rdp']['enable'] = 1
_pro_port['rdp']['port'] = host_pro_port
elif host_pro_type == 2:
_pro_port['ssh']['enable'] = 1
_pro_port['ssh']['port'] = host_pro_port
pro_port = json.dumps(_pro_port)
sql = 'INSERT INTO `{}host_info` (`host_id`, `group_id`, `host_sys_type`, `host_ip`, `pro_port`, `host_lock`, `host_desc`) ' \
'VALUES ({}, {}, {}, "{}", "{}", {}, "{}");'.format(self.db.table_prefix, host_id, group_id, host_sys_type, host_ip, pro_port, host_lock, host_desc)
if not self.db.exec(sql):
self.step_end(_step, -1)
return False
sql = 'INSERT INTO `{}auth_info` (`host_id`, `pro_type`, `auth_mode`, `user_name`, `user_pswd`, `cert_id`, `encrypt`, `log_time`) ' \
'VALUES ({}, {}, {}, "{}", "{}", {}, {}, "{}");'.format(self.db.table_prefix, host_id, host_pro_type, host_auth_mode, host_user_name, host_user_pwd, cert_id, host_encrypt, '1')
if not self.db.exec(sql):
self.step_end(_step, -1)
return False
self.step_end(_step, 0)
return True
except:
log.e('failed.\n')
self.step_end(_step, -1)
return False